CREATE DOMAIN T_AKTDAT AS
DATE
default 'today'
NOT NULL;

CREATE DOMAIN T_AKTIV AS
INTEGER
default 1;

CREATE DOMAIN T_BNAZEV AS
VARCHAR(80)
COLLATE PXW_CSY;

CREATE DOMAIN T_DATUM AS
TIMESTAMP;

CREATE DOMAIN T_DIC AS
CHAR(12);

CREATE DOMAIN T_ICO AS
CHAR(10);

CREATE DOMAIN T_ICZ AS
CHAR(8);

CREATE DOMAIN T_IDENT AS
INTEGER
NOT NULL;

CREATE DOMAIN T_IDENTZ AS
INTEGER
default 0;

CREATE DOMAIN T_IDNAZ AS
CHAR(12)
NOT NULL
COLLATE PXW_CSY;

CREATE DOMAIN T_INT AS
INTEGER
default 0;

CREATE DOMAIN T_JMENO AS
VARCHAR(30)
COLLATE PXW_CSY;

CREATE DOMAIN T_KOD1 AS
CHAR(1)
default ' '
NOT NULL;

CREATE DOMAIN T_KOD2 AS
CHAR(2)
 DEFAULT '@';

CREATE DOMAIN T_KOD3 AS
CHAR(3);

CREATE DOMAIN T_KOD5 AS
CHAR(5);

CREATE DOMAIN T_KOEF AS
DOUBLE PRECISION
default 0;

CREATE DOMAIN T_LINE AS
VARCHAR(1024)
COLLATE PXW_CSY;

CREATE DOMAIN T_LNAZEV AS
VARCHAR(60)
COLLATE PXW_CSY;

CREATE DOMAIN T_LOGICAL AS
SMALLINT
default 0;

CREATE DOMAIN T_NAJEM AS
DOUBLE PRECISION
default 0;

CREATE DOMAIN T_ODB AS
SMALLINT
default 0;

CREATE DOMAIN T_PENIZE AS
NUMERIC(15,2)
default 0;

CREATE DOMAIN T_PLOCHA AS
NUMERIC(15,2)
default 0;

CREATE DOMAIN T_POCET AS
INTEGER
default 0
check (value >= 0);

CREATE DOMAIN T_POZN AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE DOMAIN T_PROCENTA AS
NUMERIC(15,2)
default 0;

CREATE DOMAIN T_PSC AS
CHAR(6);

CREATE DOMAIN T_RC AS
CHAR(11);

CREATE DOMAIN T_SLINE AS
VARCHAR(240)
COLLATE PXW_CSY;

CREATE DOMAIN T_SNAZEV AS
CHAR(12)
COLLATE PXW_CSY;

CREATE DOMAIN T_ZKRATKA AS
CHAR(4)
COLLATE PXW_CSY;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE CISBOD (
    KOD   T_KOD5 NOT NULL,
    BODY  T_IDENTZ,
    TAG   T_IDENTZ
);


CREATE TABLE CISDG (
    CISDG  T_ZKRATKA NOT NULL,
    TEXT   T_LNAZEV,
    TAG    T_IDENTZ
);


CREATE TABLE CISDMR (
    CISDMR  T_IDENT,
    DAT1    T_DATUM,
    DAT2    T_DATUM,
    MES     T_INT,
    ROK     T_INT,
    OBD     T_SNAZEV,
    MMYYYY  CHAR(6)
);


CREATE TABLE CISKAT (
    CISKAT   T_IDENT NOT NULL,
    ZKRATKA  T_ZKRATKA,
    TEXT     T_LNAZEV
);


CREATE TABLE CISMAT (
    CISMAT   T_IDENT,
    NAZEV    T_JMENO,
    ZKRATKA  T_ZKRATKA,
    TAG      T_IDENTZ
);


CREATE TABLE CISODB (
    CISODB  T_KOD3 NOT NULL,
    NAZEV   T_BNAZEV,
    TAG     T_IDENTZ
);


CREATE TABLE CISPOJ (
    CISPOJ   T_IDENT,
    NAZEV    T_LNAZEV,
    ZKRATKA  T_JMENO,
    TAG      T_IDENTZ,
    ADRESA1  T_LNAZEV COLLATE PXW_CSY,
    ADRESA2  T_LNAZEV COLLATE PXW_CSY,
    PSC      T_PSC,
    MISTO    T_LNAZEV COLLATE PXW_CSY,
    TELEFON  T_LNAZEV COLLATE PXW_CSY,
    MAIL     T_LNAZEV COLLATE PXW_CSY,
    URL      T_LNAZEV COLLATE PXW_CSY
);


CREATE TABLE CISVYS (
    CISVYS    T_IDENT,
    CHILD     T_LOGICAL,
    PARENT    T_IDENTZ,
    USED      T_IDENTZ,
    KEYCODE   T_KOD1,
    KOD       T_KOD5,
    NAZEV     T_BNAZEV,
    ZKRATKA   T_JMENO,
    SKM       T_KOD2,
    NORMA     T_LNAZEV,
    TVIN      T_IDENTZ,
    TVOUT     T_IDENTZ,
    TAG       T_IDENTZ,
    NAZLN1    T_JMENO,
    NAZLZ1    T_JMENO,
    NAZLL1    T_JMENO,
    NAZLN2    T_JMENO,
    NAZLZ2    T_JMENO,
    NAZLL2    T_JMENO,
    JEDN1     T_JMENO,
    JEDN2     T_JMENO,
    KODZ1     T_JMENO,
    SKUPZ1    T_INT,
    KODZ2     T_JMENO,
    SKUPZ2    T_INT,
    DOLMEZ1   T_KOEF,
    PODMEZI1  T_LNAZEV,
    VMEZICH1  T_LNAZEV,
    NADMEZI1  T_LNAZEV,
    HORMEZ1   T_KOEF,
    DOLMEZ2   T_KOEF,
    PODMEZI2  T_LNAZEV,
    VMEZICH2  T_LNAZEV,
    NADMEZI2  T_LNAZEV,
    HORMEZ2   T_KOEF,
    TEXT1     T_SLINE,
    TEXT2     T_SLINE COLLATE PXW_CSY,
    TEXT3     T_SLINE COLLATE PXW_CSY,
    TYPVYSL   T_IDENTZ,
    SPEC      T_IDENTZ,
    ROZTEST   T_IDENTZ
);


CREATE TABLE DATA (
    VCID      T_IDENT,
    CISLO     T_IDENT,
    PRIJMENI  T_LNAZEV,
    JMENO     T_LNAZEV,
    RC1       T_PSC,
    RC2       T_ZKRATKA,
    CP        T_IDENTZ,
    DG        T_ZKRATKA,
    OS        T_LNAZEV,
    MI        T_LNAZEV,
    OO        T_DATUM,
    DATOD     T_DATUM,
    PV        T_IDENTZ,
    PHV       T_IDENTZ,
    OL        T_DATUM,
    UL        T_JMENO,
    ICZO      T_IDENTZ,
    ODBO      T_IDENTZ,
    RC        COMPUTED BY (CAST(RC1 as char(6))||CAST(RC2 as char(4)))
);


CREATE TABLE DATV (
    VCID   T_IDENT,
    VDAT   T_DATUM,
    VKOD   T_KOD5,
    VPOC   T_IDENTZ,
    VN     T_PROCENTA,
    VF     T_PROCENTA,
    VB     T_ICZ,
    VI     T_ICZ,
    VT     T_LINE,
    VG     T_IDENTZ,
    HO     T_LOGICAL,
    OD     T_LOGICAL,
    NAZEV  T_LNAZEV,
    SKM    T_KOD2
);


CREATE TABLE KODPOVED (
    KOD      T_KOD1,
    SKUPINA  T_IDENT,
    TEXT     T_JMENO,
    HODNOTA  T_KOEF,
    POZN     T_LINE
);


CREATE TABLE LABORATORE (
    IDLAB         T_IDENT NOT NULL,
    LABICO        T_DIC,
    LABICZ        T_ICO NOT NULL,
    LABNAZEV      T_LNAZEV,
    LABVEDOUCI    T_LNAZEV,
    LABLEKAR      T_LNAZEV,
    LABADRESA1    T_LNAZEV,
    LABADRESA2    T_LNAZEV,
    LABADRESA3    T_LNAZEV,
    LABTELEFON    T_LNAZEV,
    LABEMAIL      T_LNAZEV,
    LABODBORNOST  T_KOD3,
    LABOBCISLO    T_IDENTZ
);


CREATE TABLE LEKARI (
    IDLEK      T_IDENT,
    LETITPR    T_JMENO,
    LEJMENO    T_LNAZEV,
    LEPRIJM    T_LNAZEV,
    LETITZA    T_JMENO,
    LEODB      T_KOD3 NOT NULL,
    LEIDZA     T_KOD5,
    LEOSCISLO  T_SNAZEV,
    LEADR1     T_LNAZEV,
    LEADR2     T_LNAZEV,
    LEADR3     T_LNAZEV,
    LEPSC      T_PSC,
    LEMISTO    T_LNAZEV,
    LETEL      T_LNAZEV,
    LEMAIL     T_LNAZEV COLLATE PXW_CSY,
    TAG        T_IDENTZ,
    LECISLO    COMPUTED BY (LEIDZA||LEOSCISLO),
    DMR        T_AKTDAT NOT NULL,
    LECAPTION  COMPUTED BY (LETITPR||' '||LEJMENO||' '||LEPRIJM||LETITZA||', '||LEADR1||', '||LEADR2)
);


CREATE TABLE OBJEDNAVKY (
    IDOBJ       T_IDENT,
    IDL         T_IDENTZ,
    OBCISLO     T_IDENT,
    OBIDPAC     T_IDENT,
    OBIDLEK     T_IDENT,
    OBODESLANO  T_DATUM,
    OBPRIJEM    T_DATUM,
    OBDG        T_ZKRATKA NOT NULL,
    OBIDMAT     T_IDENTZ,
    OBODBER     T_LOGICAL,
    OBPV        T_POCET,
    OBPHV       T_POCET,
    OBHOTOVO    T_LOGICAL,
    OBPRUVODKA  T_DATUM,
    OBOBDOBI    T_PSC,
    OBPOZN      T_SLINE,
    OBTEMP      T_JMENO COLLATE PXW_CSY,
    TAG         T_IDENTZ,
    USR         T_IDENTZ,
    DMR         T_AKTDAT NOT NULL,
    OBZP        T_IDENT,
    OBDATODB    T_DATUM
);


CREATE TABLE PACIENTI (
    IDPAC      T_IDENT,
    PATITPR    T_JMENO,
    PAJMENO    T_JMENO,
    PAPRIJM    T_JMENO,
    PAPRIJMS   T_JMENO,
    PARC       T_RC NOT NULL,
    PATITZA    T_JMENO,
    PAULICE1   T_JMENO,
    PAULICE2   T_JMENO,
    PAPSC      T_PSC,
    PAMISTO    T_JMENO,
    PATEL      T_LNAZEV,
    PAZP       T_IDENT NOT NULL,
    PAIDLEK    T_IDENTZ,
    TAG        T_IDENTZ,
    PACAPTION  COMPUTED BY (PATITPR||' '||PAJMENO||' '||PAPRIJM||PATITZA),
    PADRESA    COMPUTED BY (PAULICE1||' '||PAULICE2||', '||PAPSC||PAMISTO),
    DMR        T_AKTDAT NOT NULL,
    PAZEME     T_IDENTZ,
    PAKAT      T_IDENTZ
);


CREATE TABLE PARLAB (
    COBDOBI    T_DATUM,
    AOBDOBI    T_DATUM,
    DATARCH    T_DATUM,
    AKTDAT     T_AKTDAT,
    SUPERUSER  T_LNAZEV,
    DATUMVYTV  T_AKTDAT,
    CORG       T_ICO,
    ORGICO     T_ICO,
    ORGDIC     T_DIC,
    CLAB       T_KOD3,
    AKTDATOBJ  T_AKTDAT
);


CREATE TABLE PROFILY (
    IDPF      T_IDENT,
    PFUSER    T_IDENT,
    PFCISVYS  T_IDENTZ,
    PFSHOW    T_LOGICAL
);


CREATE TABLE UZIVATEL (
    IDUZIVATEL  T_IDENTZ NOT NULL,
    USERNAME    T_JMENO,
    HESLO       T_LNAZEV,
    IDL         T_IDENT,
    PRAVA       T_IDENTZ,
    DMR         T_AKTDAT,
    LASTLOGIN   TIMESTAMP
);


CREATE TABLE VYSETRENI (
    IDVYS     T_IDENT,
    VYIDOBJ   T_IDENT,
    VYIDVYS   T_IDENT,
    VYSPEC    T_JMENO,
    VYSETREN  T_DATUM,
    VYHOTOVO  T_LOGICAL,
    VYSLN1    T_KOEF,
    VYSLZ1    T_LNAZEV,
    VYSLK1    T_KOD2,
    VYSLL1    T_LOGICAL,
    VYSLN2    T_KOEF,
    VYSLZ2    T_LNAZEV COLLATE PXW_CSY,
    VYSLK2    T_KOD2,
    VYSLL2    T_LOGICAL,
    VYPOZN    T_LINE,
    TAG       T_IDENTZ,
    VYTEMP    T_KOD5,
    USR       T_IDENTZ,
    DMR       T_AKTDAT NOT NULL,
    VYBODY    T_IDENTZ
);


CREATE TABLE ZEME (
    IDZEME   T_IDENT NOT NULL,
    NAZEV    T_LNAZEV,
    ZKRATKA  T_ZKRATKA
);




/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/

CREATE VIEW PRIJEM_VYSETRENI(
    IDVYS,
    VYSPEC,
    VYHOTOVO,
    VYPOZN,
    BODY,
    USR,
    IDOBJ,
    OBCISLO,
    IDL,
    NAZEV,
    VYIDVYS,
    VYSETREN,
    OBIDPAC,
    OBPRIJEM,
    OBODESLANO,
    ROK)
AS
select v.IDVYS, v.VYSPEC, v.VYHOTOVO, v.VYPOZN, b.BODY, v.USR, v.VYIDOBJ,
       o.OBCISLO, o.IDL, c.NAZEV, v.VYIDVYS, v.VYSETREN, o.OBIDPAC,
       o.OBPRIJEM, o.OBODESLANO, dmr.ROK
from CISVYS c, CISBOD b, VYSETRENI v
left outer join OBJEDNAVKY o on v.VYIDOBJ=o.IDOBJ
left outer join CISDMR dmr on (dmr.DAT1<=o.OBPRIJEM and dmr.DAT2>=o.OBPRIJEM)
where b.KOD=c.KOD and v.IDVYS>0 and v.VYIDVYS=c.CISVYS;




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CISBOD ADD CONSTRAINT PK_CISBOD PRIMARY KEY (KOD);
ALTER TABLE CISDG ADD CONSTRAINT PK_CISDG PRIMARY KEY (CISDG);
ALTER TABLE CISDMR ADD CONSTRAINT PK_CISDMR PRIMARY KEY (CISDMR);
ALTER TABLE CISKAT ADD CONSTRAINT PK_CISKAT PRIMARY KEY (CISKAT);
ALTER TABLE CISMAT ADD CONSTRAINT PK_CISMAT PRIMARY KEY (CISMAT);
ALTER TABLE CISODB ADD CONSTRAINT PK_CISODB PRIMARY KEY (CISODB);
ALTER TABLE CISPOJ ADD CONSTRAINT PK_CISPOJ PRIMARY KEY (CISPOJ);
ALTER TABLE CISVYS ADD CONSTRAINT PK_CISVYS PRIMARY KEY (CISVYS);
ALTER TABLE KODPOVED ADD CONSTRAINT FK_KODPOVED PRIMARY KEY (SKUPINA, KOD);
ALTER TABLE LABORATORE ADD CONSTRAINT PK_LABORATORE PRIMARY KEY (IDLAB);
ALTER TABLE LEKARI ADD CONSTRAINT PK_LEKARI PRIMARY KEY (IDLEK);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT PK_OBJEDNAVKY PRIMARY KEY (IDOBJ);
ALTER TABLE PACIENTI ADD CONSTRAINT PK_PACIENTI PRIMARY KEY (IDPAC);
ALTER TABLE PARLAB ADD CONSTRAINT PK_PARLAB PRIMARY KEY (DATUMVYTV);
ALTER TABLE PROFILY ADD CONSTRAINT PK_PROFILE PRIMARY KEY (IDPF);
ALTER TABLE UZIVATEL ADD CONSTRAINT PK_UZIVATEL PRIMARY KEY (IDUZIVATEL);
ALTER TABLE VYSETRENI ADD CONSTRAINT PK_VYSETRENI PRIMARY KEY (IDVYS);
ALTER TABLE ZEME ADD CONSTRAINT PK_ZEME PRIMARY KEY (IDZEME);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE CISVYS ADD CONSTRAINT FK_CISVYS2BOD FOREIGN KEY (KOD) REFERENCES CISBOD (KOD);
ALTER TABLE LEKARI ADD CONSTRAINT FK_LEK2ODB FOREIGN KEY (LEODB) REFERENCES CISODB (CISODB);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2DG FOREIGN KEY (OBDG) REFERENCES CISDG (CISDG);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2LAB FOREIGN KEY (IDL) REFERENCES LABORATORE (IDLAB);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2LEK FOREIGN KEY (OBIDLEK) REFERENCES LEKARI (IDLEK);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2MAT FOREIGN KEY (OBIDMAT) REFERENCES CISMAT (CISMAT);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2PAC FOREIGN KEY (OBIDPAC) REFERENCES PACIENTI (IDPAC);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2USR FOREIGN KEY (USR) REFERENCES UZIVATEL (IDUZIVATEL);
ALTER TABLE PACIENTI ADD CONSTRAINT FK_PAC2LEK FOREIGN KEY (PAIDLEK) REFERENCES LEKARI (IDLEK);
ALTER TABLE PACIENTI ADD CONSTRAINT FK_PAC2POJ FOREIGN KEY (PAZP) REFERENCES CISPOJ (CISPOJ);
ALTER TABLE PROFILY ADD CONSTRAINT FK_PF2USR FOREIGN KEY (PFUSER) REFERENCES UZIVATEL (IDUZIVATEL) USING INDEX FK_PROFILY_1;
ALTER TABLE PROFILY ADD CONSTRAINT FK_PF2VYS FOREIGN KEY (PFCISVYS) REFERENCES CISVYS (CISVYS);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2OBJ FOREIGN KEY (VYIDOBJ) REFERENCES OBJEDNAVKY (IDOBJ);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2USR FOREIGN KEY (USR) REFERENCES UZIVATEL (IDUZIVATEL);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2VYS FOREIGN KEY (VYIDVYS) REFERENCES CISVYS (CISVYS);
