-- odeberu pokud existuje funkce na oodebrání tabulek a sekvencí
DROP FUNCTION IF EXISTS remove_all();

-- vytvořím funkci která odebere tabulky a sekvence
-- chcete také umět psát PLSQL? Zapište si předmět BI-SQL ;-)
CREATE or replace FUNCTION remove_all() RETURNS void AS $$
DECLARE
    rec RECORD;
    cmd text;
BEGIN
    cmd := '';

    FOR rec IN SELECT
            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace
        WHERE
            relkind = 'S' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    FOR rec IN SELECT
            'DROP TABLE ' || quote_ident(n.nspname) || '.'
                || quote_ident(c.relname) || ' CASCADE;' AS name
        FROM
            pg_catalog.pg_class AS c
        LEFT JOIN
            pg_catalog.pg_namespace AS n
        ON
            n.oid = c.relnamespace WHERE relkind = 'r' AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
    LOOP
        cmd := cmd || rec.name;
    END LOOP;

    EXECUTE cmd;
    RETURN;
END;
$$ LANGUAGE plpgsql;
-- zavolám funkci co odebere tabulky a sekvence - Mohl bych dropnout celé schéma a znovu jej vytvořit, použíjeme však PLSQL
select remove_all();


-- Začnu vytvářet své tabulky
CREATE TABLE adresa
  (
    id_adresa SERIAL ,
    id_stat   INTEGER NOT NULL ,
    ulice     VARCHAR (30) ,
    cislo     INTEGER ,
    mesto     VARCHAR (30) NOT NULL ,
    pcs       VARCHAR (5) NOT NULL
  ) ;
ALTER TABLE adresa ADD CONSTRAINT PK_adresa PRIMARY KEY ( id_adresa ) ;

CREATE TABLE asistovane
  (
    id_krmeni INTEGER NOT NULL ,
    kapacita  SMALLINT NOT NULL ,
    nazev     VARCHAR (255) NOT NULL
  ) ;
ALTER TABLE asistovane ADD CONSTRAINT PK_asistovane PRIMARY KEY ( id_krmeni ) ;

CREATE TABLE doktor
  (
    id_zamestnanec INTEGER NOT NULL ,
    specializace   VARCHAR (100) NOT NULL
  ) ;
ALTER TABLE doktor ADD CONSTRAINT PK_doktor PRIMARY KEY ( id_zamestnanec ) ;

CREATE TABLE druh
  (
    id_druh SERIAL ,
    nazev   VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE druh ADD CONSTRAINT PK_druh PRIMARY KEY ( id_druh ) ;


CREATE TABLE klec
  (
    nazev VARCHAR (30) NOT NULL ,
    cislo INTEGER NOT NULL
  ) ;
ALTER TABLE klec ADD CONSTRAINT PK_klec PRIMARY KEY ( nazev, cislo ) ;


CREATE TABLE krmeni
  (
    id_krmeni      SERIAL ,
    id_krmivo      INTEGER NOT NULL ,
    id_zvire       INTEGER NOT NULL ,
    id_typ_krmeni  INTEGER NOT NULL ,
    id_zamestnanec INTEGER NOT NULL ,
    datum          DATE NOT NULL ,
    mnozstvi       NUMERIC (6,2)
  ) ;
ALTER TABLE krmeni ADD CONSTRAINT PK_krmeni PRIMARY KEY ( id_krmeni ) ;


CREATE TABLE krmivo
  (
    id_krmivo SERIAL ,
    nazev     VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE krmivo ADD CONSTRAINT PK_krmivo PRIMARY KEY ( id_krmivo ) ;


CREATE TABLE media
  (
    id_media SERIAL ,
    nazev VARCHAR (30) NOT NULL
  ) ;
COMMENT ON TABLE media
IS
  'Novinari - ti kteréchceme evidovat.' ;
ALTER TABLE media ADD CONSTRAINT PK_media PRIMARY KEY ( id_media ) ;


CREATE TABLE oddeleni
  (
    nazev VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE oddeleni ADD CONSTRAINT PK_oddeleni PRIMARY KEY ( nazev ) ;


CREATE TABLE opatreni
  (
    id_opatreni  SERIAL ,
    id_vysetreni INTEGER NOT NULL ,
    popis        VARCHAR (255) NOT NULL
  ) ;
CREATE UNIQUE INDEX UX_opatreni_id_vysetreni ON opatreni (id_vysetreni ASC) ;
ALTER TABLE opatreni ADD CONSTRAINT PK_opatreni PRIMARY KEY ( id_opatreni ) ;


CREATE TABLE osetrovatel
  (
    id_zamestnanec INTEGER NOT NULL
  ) ;
ALTER TABLE osetrovatel ADD CONSTRAINT PK_osetrovatel PRIMARY KEY ( id_zamestnanec ) ;


CREATE TABLE osobnost
  (
    id_osobnost    SERIAL ,
    jmeno          VARCHAR (30) NOT NULL ,
    prijmeni       VARCHAR (30) NOT NULL ,
    datum_narozeni DATE NOT NULL
  ) ;
ALTER TABLE osobnost ADD CONSTRAINT PK_osobnost PRIMARY KEY ( id_osobnost ) ;


CREATE TABLE pozvana_media
  (
    id_krmeni INTEGER NOT NULL ,
    id_media  INTEGER NOT NULL
  ) ;
ALTER TABLE pozvana_media ADD CONSTRAINT PK_pozvana_media PRIMARY KEY ( id_krmeni, id_media ) ;


CREATE TABLE pozvana_osobnost
  (
    id_osobnost INTEGER NOT NULL ,
    id_krmeni   INTEGER NOT NULL
  ) ;
ALTER TABLE pozvana_osobnost ADD CONSTRAINT PK_pozvana_osobnost PRIMARY KEY ( id_osobnost, id_krmeni ) ;


CREATE TABLE stat
  (
    id_stat SERIAL ,
    nazev   VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE stat ADD CONSTRAINT PK_stat PRIMARY KEY ( id_stat ) ;


CREATE TABLE typ_krmeni
  (
    id_typ_krmeni SERIAL ,
    nazev         VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE typ_krmeni ADD CONSTRAINT PK_typ_krmeni PRIMARY KEY ( id_typ_krmeni ) ;


CREATE TABLE vip
  (
    id_krmeni INTEGER NOT NULL,
    nazev     VARCHAR (30) NOT NULL
  ) ;
ALTER TABLE vip ADD CONSTRAINT PK_vip PRIMARY KEY ( id_krmeni ) ;


CREATE TABLE vstupenka
  (
    id_vstupenka SERIAL ,
    cena         NUMERIC (5,2) NOT NULL ,
    datum        DATE NOT NULL
  ) ;
COMMENT ON TABLE vstupenka
IS
  'Vstupenky zakazniku' ;
ALTER TABLE vstupenka ADD CONSTRAINT PK_vstupenka PRIMARY KEY ( id_vstupenka ) ;


CREATE TABLE vysetreni
  (
    id_vysetreni    SERIAL ,
    id_zvire        INTEGER NOT NULL ,
    id_vysetril     INTEGER NOT NULL ,
    id_asistoval    INTEGER ,
    datum           DATE NOT NULL ,
    ucel            VARCHAR (100)
  ) ;
ALTER TABLE vysetreni ADD CONSTRAINT PK_vysetreni PRIMARY KEY ( id_vysetreni ) ;


CREATE TABLE zamestnanec
  (
    id_zamestnanec  SERIAL ,
    osobni_cislo    INTEGER NOT NULL ,
    id_adresa       INTEGER NOT NULL ,
    id_nadrizeny    INTEGER,
    jmeno           VARCHAR (30) NOT NULL ,
    prijmeni        VARCHAR (30) NOT NULL ,
    plat            NUMERIC (5) NOT NULL
  ) ;
ALTER TABLE zamestnanec ADD CONSTRAINT PK_zamestnanec PRIMARY KEY ( id_zamestnanec ) ;
ALTER TABLE zamestnanec ADD CONSTRAINT UX_zamestnanec_osobni_cislo UNIQUE ( osobni_cislo ) ;


CREATE TABLE zvire
  (
    id_zvire       SERIAL ,
    id_osobnost    INTEGER ,
    id_druh        INTEGER NOT NULL ,
    nazev          VARCHAR (30) NOT NULL ,
    cislo          INTEGER NOT NULL ,
    jmeno          VARCHAR (30) ,
    datum_narozeni DATE
  ) ;
ALTER TABLE zvire ADD CONSTRAINT PK_zvire PRIMARY KEY ( id_zvire ) ;

--ostaní IO včetně cizích klíčů
ALTER TABLE pozvana_media ADD CONSTRAINT FK_pozvana_media_vip FOREIGN KEY ( id_krmeni ) REFERENCES vip ( id_krmeni ) ON DELETE CASCADE;
ALTER TABLE pozvana_media ADD CONSTRAINT FK_pozvana_media_media FOREIGN KEY ( id_media ) REFERENCES media ( id_media ) ON DELETE CASCADE;
ALTER TABLE pozvana_osobnost ADD CONSTRAINT FK_pozvana_osobnost_osobnost FOREIGN KEY ( id_osobnost ) REFERENCES osobnost ( id_osobnost ) ON DELETE CASCADE;
ALTER TABLE pozvana_osobnost ADD CONSTRAINT FK_pozvana_osobnost_vip FOREIGN KEY ( id_krmeni ) REFERENCES vip ( id_krmeni ) ON DELETE CASCADE;
ALTER TABLE adresa ADD CONSTRAINT FK_adresa_stat FOREIGN KEY ( id_stat ) REFERENCES stat ( id_stat ) ON DELETE CASCADE;
ALTER TABLE asistovane ADD CONSTRAINT FK_asistovane_krmeni FOREIGN KEY ( id_krmeni ) REFERENCES krmeni ( id_krmeni )  ON DELETE CASCADE ;
ALTER TABLE doktor ADD CONSTRAINT FK_doktor_zamestnanec FOREIGN KEY ( id_zamestnanec ) REFERENCES zamestnanec ( id_zamestnanec ) ON DELETE CASCADE;
ALTER TABLE klec ADD CONSTRAINT FK_klec_oddeleni FOREIGN KEY ( nazev ) REFERENCES oddeleni ( nazev ) ON DELETE CASCADE;
ALTER TABLE krmeni ADD CONSTRAINT FK_krmeni_krmivo FOREIGN KEY ( id_krmivo ) REFERENCES krmivo ( id_krmivo ) ON DELETE CASCADE;
ALTER TABLE krmeni ADD CONSTRAINT FK_krmeni_osetrovatel FOREIGN KEY ( id_zamestnanec ) REFERENCES osetrovatel ( id_zamestnanec )  ON DELETE CASCADE ;
ALTER TABLE krmeni ADD CONSTRAINT FK_krmeni_typ_krmeni FOREIGN KEY ( id_typ_krmeni ) REFERENCES typ_krmeni ( id_typ_krmeni ) ON DELETE CASCADE;
ALTER TABLE krmeni ADD CONSTRAINT FK_krmeni_zvire FOREIGN KEY ( id_zvire ) REFERENCES zvire ( id_zvire ) ON DELETE CASCADE;
ALTER TABLE opatreni ADD CONSTRAINT FK_opatreni_vysetreni FOREIGN KEY ( id_vysetreni ) REFERENCES vysetreni ( id_vysetreni ) ON DELETE CASCADE;
ALTER TABLE osetrovatel ADD CONSTRAINT FK_osetrovatel_zamestnanec FOREIGN KEY ( id_zamestnanec ) REFERENCES zamestnanec ( id_zamestnanec ) ON DELETE CASCADE ;
ALTER TABLE vip ADD CONSTRAINT FK_vip_krmeni FOREIGN KEY ( id_krmeni ) REFERENCES krmeni ( id_krmeni )  ON DELETE CASCADE;
ALTER TABLE vysetreni ADD CONSTRAINT FK_vysetreni_doktor FOREIGN KEY ( id_vysetril) REFERENCES doktor ( id_zamestnanec ) ON DELETE CASCADE;
ALTER TABLE vysetreni ADD CONSTRAINT FK_vysetreni_doktorv1 FOREIGN KEY ( id_asistoval ) REFERENCES doktor ( id_zamestnanec ) ON DELETE CASCADE;
ALTER TABLE vysetreni ADD CONSTRAINT FK_vysetreni_zvire FOREIGN KEY ( id_zvire ) REFERENCES zvire ( id_zvire ) ON DELETE CASCADE;
ALTER TABLE zamestnanec ADD CONSTRAINT FK_zamestnanec_adresa FOREIGN KEY ( id_adresa ) REFERENCES adresa ( id_adresa ) ON DELETE CASCADE;
ALTER TABLE zamestnanec ADD CONSTRAINT FK_zamestnanec_zamestnanec FOREIGN KEY ( id_nadrizeny) REFERENCES zamestnanec ( id_zamestnanec ) ON DELETE CASCADE;
ALTER TABLE zvire ADD CONSTRAINT FK_zvire_druh FOREIGN KEY ( id_druh ) REFERENCES druh ( id_druh ) ON DELETE CASCADE;
ALTER TABLE zvire ADD CONSTRAINT FK_zvire_klec FOREIGN KEY ( nazev, cislo ) REFERENCES klec ( nazev, cislo ) ON DELETE CASCADE;
ALTER TABLE zvire ADD CONSTRAINT FK_zvire_osobnost FOREIGN KEY ( id_osobnost ) REFERENCES osobnost ( id_osobnost ) ON DELETE CASCADE;

--zde by bylo třeba napsat ještě trigger na výlučnou vazbu, pro zajímavost doplníme v průběhu semestru

--kontrola že zaměstnanec nedělá nadřízeného sám sobě (zaměstnanci bez nadřízeného jej zkrátka nemají) - řeší IO1
ALTER TABLE zamestnanec ADD CONSTRAINT chk_zamestnanec_nadrizeny check ( zamestnanec.id_nadrizeny<>zamestnanec.id_zamestnanec ) ;
--kontrola že doktor který vyšetřuje zároveň neasistuje u stejného vyšetření (nechceme mu platit za oba zákroky ;-) ) - Řeší IO2
ALTER TABLE vysetreni ADD CONSTRAINT chk_vysetreni_asisttoval check ( vysetreni.id_vysetril<>vysetreni.id_asistoval ) ;
--kontrola že plat je ve stanovených mezích
ALTER TABLE zamestnanec ADD CONSTRAINT chk_plat check ( zamestnanec.plat>=5000 and zamestnanec.plat<=60000 ) ;