CREATE TABLE log(
ID INTEGER NOT NULL,
kdy date not null,
kod integer NULL,
jak varchar2(10) not NULL,
radek INTEGER not NULL);

CREATE TABLE ini(
ID INTEGER NOT NULL,
popis varchar2(30) NULL,
dph INTEGER NULL CONSTRAINT ini_dph UNIQUE,
platba CHAR NULL CONSTRAINT ini_platba UNIQUE,
vp char NULL CONSTRAINT ini_vp UNIQUE,
typ varchar2(3) NULL CONSTRAINT ini_typ UNIQUE);

CREATE TABLE KOD(
ID INTEGER NOT NULL,
TEXT VARCHAR2(50) NOT NULL CONSTRAINT UC_KOD2 UNIQUE);

CREATE TABLE JEDN(
IDJ INTEGER NOT NULL,
JMENOJ VARCHAR2(10) NOT NULL CONSTRAINT UC_JEDN2 UNIQUE);

CREATE TABLE DKP(
RADEKDKP INTEGER NOT NULL ,
NAZEVDKP VARCHAR2(50) NOT NULL,
FKR_DENIK INTEGER NOT NULL);

CREATE TABLE POLREZIE(
ID INTEGER NOT NULL ,
JMENO VARCHAR2(50) NOT NULL,
CASTKA NUMBER(10,2) NOT NULL,
DPH INTEGER DEFAULT 0 NOT NULL,
FK_REZIE INTEGER NOT NULL);

CREATE TABLE POLDKP(
ID INTEGER NOT NULL ,
JMENO VARCHAR2(50) NOT NULL,
POCETJ NUMBER(10) NOT NULL,
CASTKA NUMBER(10,2) NOT NULL,
DPH INTEGER DEFAULT 0 NOT NULL,
ZUSTATEK INTEGER NOT NULL,
ZMENA DATE NOT NULL,
FK_DKP INTEGER NOT NULL,
FK_JEDN INTEGER NOT NULL);

CREATE TABLE REZIE(
RADEKR INTEGER NOT NULL ,
NAZEVR VARCHAR2(50) NOT NULL,
FKR_DENIK INTEGER NOT NULL);

CREATE TABLE DENIK(
RADEKD INTEGER NOT NULL ,
DATUM DATE NOT NULL,
TYP VARCHAR2(3) NOT NULL,
DOKLAD INTEGER NOT NULL,
CASTKA NUMBER(10,2) NOT NULL,
VP CHAR(1) NOT NULL,
PLATBA CHAR(1) DEFAULT 'H' NOT NULL,
POPLATEK NUMBER(10,2) DEFAULT 0 NOT NULL,
DPH INTEGER DEFAULT 0 NOT NULL,
FK_DKP INTEGER,
FK_REZIE INTEGER,
FK_KOD INTEGER NULL,
KOD INTEGER NULL);





/* komentare */
COMMENT ON TABLE JEDN IS 'seznam jednotek';
COMMENT ON TABLE DKP IS 'Kniha DKP';
COMMENT ON COLUMN DKP.NAZEVDKP IS 'globalni nazev DKP';
COMMENT ON COLUMN POLREZIE.JMENO IS 'jmeno polozky rezie';
COMMENT ON COLUMN POLREZIE.DPH IS 'sazba DPH';
COMMENT ON TABLE POLDKP IS 'polozky DKP';
COMMENT ON COLUMN POLDKP.JMENO IS 'jmeno polozky DKP';
COMMENT ON COLUMN POLDKP.POCETJ IS 'pocet jednotek';
COMMENT ON COLUMN POLDKP.DPH IS 'sazba DPH';
COMMENT ON COLUMN POLDKP.ZUSTATEK IS 'pocet zbylych jednotek';
COMMENT ON COLUMN POLDKP.ZMENA IS 'datum posledni zmeny';
COMMENT ON TABLE REZIE IS 'kniha rezie a oprav';
COMMENT ON COLUMN REZIE.NAZEVR IS 'globalni nazev rezie';
COMMENT ON TABLE DENIK IS 'Penezni denik';
COMMENT ON COLUMN DENIK.TYP IS 'typ dokladu - FAK/ZFA/PAR/VYP';
COMMENT ON COLUMN DENIK.VP IS 'V/P - Vydej/Prijem';
COMMENT ON COLUMN DENIK.PLATBA IS 'zpusob platby H/U/V Hotove/bezny Ucet/Vymena komodit';
COMMENT ON COLUMN DENIK.POPLATEK IS 'clearingovy poplatek';
COMMENT ON COLUMN DENIK.DPH IS 'sazba DPH';






/* primarni klice */
ALTER TABLE log
ADD CONSTRAINT PK_log 
PRIMARY KEY (ID)
;

ALTER TABLE ini
ADD CONSTRAINT PK_ini 
PRIMARY KEY (ID)
;

ALTER TABLE KOD
ADD CONSTRAINT PK_KOD1 
PRIMARY KEY (ID)
;


ALTER TABLE JEDN
ADD CONSTRAINT PK_JEDN1 
PRIMARY KEY (IDJ)
;

ALTER TABLE DENIK
ADD CONSTRAINT PK_DENIK1 
PRIMARY KEY (RADEKD)
;

ALTER TABLE REZIE
ADD CONSTRAINT PK_REZIE1 
PRIMARY KEY (RADEKR)
;

ALTER TABLE DKP
ADD CONSTRAINT PK_DKP1 
PRIMARY KEY (RADEKDKP)
;

ALTER TABLE POLREZIE
ADD CONSTRAINT PK_POLREZIE1 
PRIMARY KEY (ID)
;

ALTER TABLE POLDKP
ADD CONSTRAINT PK_POLDKP1 
PRIMARY KEY (ID)
;


/* cizi klice */
ALTER TABLE DENIK
ADD CONSTRAINT FK_P_DENIK_K_DKP_1 
FOREIGN KEY (FK_DKP) REFERENCES DKP (RADEKDKP)
;


ALTER TABLE DKP
ADD CONSTRAINT FK_K_DKP_P_DENIK_1 
FOREIGN KEY (FKR_DENIK) REFERENCES DENIK (RADEKD)
;


ALTER TABLE POLREZIE
ADD CONSTRAINT FK_K_OPRAV_P_OPRAVY_1 
FOREIGN KEY (FK_REZIE) REFERENCES REZIE (RADEKR)
;

ALTER TABLE POLDKP
ADD CONSTRAINT FK_K_DKP_P_DKP_1 
FOREIGN KEY (FK_DKP) REFERENCES DKP (RADEKDKP)
;

ALTER TABLE POLDKP
ADD CONSTRAINT FK_P_DKP_JEDNOTKA_2 
FOREIGN KEY (FK_JEDN) REFERENCES JEDN (IDJ)
;

ALTER TABLE REZIE
ADD CONSTRAINT FK_P_DENIK_K_OPRAV_1 
FOREIGN KEY (FKR_DENIK) REFERENCES DENIK (RADEKD)
;



ALTER TABLE DENIK
ADD CONSTRAINT FK_P_DENIK_K_OPRAV_2 
FOREIGN KEY (FK_REZIE) REFERENCES REZIE (RADEKR)
;

ALTER TABLE DENIK
ADD CONSTRAINT FK_P_DENIK_KOD_3 
FOREIGN KEY (FK_KOD) REFERENCES KOD (ID)
;



