DROP TABLE TYP_CLENA CASCADE CONSTRAINTS;
DROP TABLE SITOVE_ZARIZENI CASCADE CONSTRAINTS;
DROP TABLE SEMESTR CASCADE CONSTRAINTS;
DROP TABLE TYP_PRISPEVKU CASCADE CONSTRAINTS;
DROP TABLE UZIVATEL CASCADE CONSTRAINTS;
DROP TABLE TYP_KONTA CASCADE CONSTRAINTS;
DROP TABLE OSTATNI_SZ CASCADE CONSTRAINTS;
DROP TABLE SLUZBA CASCADE CONSTRAINTS;
DROP TABLE LOGOVANI CASCADE CONSTRAINTS;
DROP TABLE HOSTNAME_ALIAS CASCADE CONSTRAINTS;
DROP TABLE AKTIVNI_CLEN CASCADE CONSTRAINTS;
DROP TABLE ADMIN CASCADE CONSTRAINTS;
DROP TABLE ADMIN_has_SLUZBA CASCADE CONSTRAINTS;
DROP TABLE ADMIN_has_OSTATNI CASCADE CONSTRAINTS;
DROP TABLE TYP_KONTA_has_TYP_PRISPEVKU CASCADE CONSTRAINTS;
DROP TABLE KONTO CASCADE CONSTRAINTS;
DROP TABLE POCITAC CASCADE CONSTRAINTS;
DROP TABLE AKTIVNI_CLEN_has_TYP_CLENA CASCADE CONSTRAINTS;
DROP TABLE TREST CASCADE CONSTRAINTS;
DROP TABLE PRISPEVEK CASCADE CONSTRAINTS;
DROP TABLE NOVINKY CASCADE CONSTRAINTS;
DROP TABLE EMAIL_ALIAS CASCADE CONSTRAINTS;
DROP TABLE KONTO_TISKARNA CASCADE CONSTRAINTS;

DROP SEQUENCE TYP_CLENA_SEQ;
DROP SEQUENCE SITOVE_ZARIZENI_SEQ;
DROP SEQUENCE SEMESTR_SEQ;
DROP SEQUENCE TYP_PRISPEVKU_SEQ;
DROP SEQUENCE UZIVATEL_SEQ;
DROP SEQUENCE TYP_KONTA_SEQ;
DROP SEQUENCE SLUZBA_SEQ;
DROP SEQUENCE LOGOVANI_SEQ;
DROP SEQUENCE HOSTNAME_ALIAS_SEQ;
DROP SEQUENCE TREST_SEQ;
DROP SEQUENCE PRISPEVEK_SEQ;
DROP SEQUENCE NOVINKY_SEQ;
DROP SEQUENCE EMAIL_ALIAS_SEQ;


CREATE TABLE TYP_CLENA (
  ID INTEGER NOT NULL,
  TYP VARCHAR(45) NOT NULL,

  PRIMARY KEY(ID)
);

CREATE TABLE SITOVE_ZARIZENI (
  ID INTEGER NOT NULL,
  AKTIVNI NUMBER(1,0) NOT NULL,
  MAC_ADDR VARCHAR(17) NOT NULL,
  IP_ADDR VARCHAR(15) NULL,
  IP6_ADDR VARCHAR(64) NULL,
  HOSTNAME VARCHAR(64) NULL,
  POPIS VARCHAR2(4000) NULL,
  ZASUVKA INTEGER  NULL,

  PRIMARY KEY(ID),
  CONSTRAINT UN_SZ_MAC_ADDR UNIQUE(MAC_ADDR),
  CONSTRAINT UN_SZ_IP_ADDR UNIQUE(IP_ADDR),
  CONSTRAINT UN_SZ_IP6_ADDR UNIQUE(IP6_ADDR),
  CONSTRAINT UN_SZ_HOSTNAME UNIQUE(HOSTNAME),
  CONSTRAINT UN_SZ_ZASUVKA UNIQUE(ZASUVKA)
);


CREATE TABLE SEMESTR (
  ID INTEGER NOT NULL,
  DATUM_OD DATE NOT NULL,
  DATUM_DO DATE NOT NULL,
  POPIS VARCHAR2(4000) NULL,

  PRIMARY KEY(ID)
);

CREATE TABLE TYP_PRISPEVKU (
  ID INTEGER NOT NULL,
  VYSE INTEGER  NULL,
  POPIS VARCHAR2(1024) NULL,
  PLATNOST_OD DATE NOT NULL,

  PRIMARY KEY(ID)
);

CREATE TABLE UZIVATEL (
  ID INTEGER NOT NULL,
  JMENO VARCHAR(45) NOT NULL,
  PRIJMENI VARCHAR(45) NOT NULL,
  RC VARCHAR(10) NULL,
  ADRESA VARCHAR(255) NOT NULL,
  POKOJ INTEGER  NOT NULL,
  ICQ VARCHAR(20) NULL,
  EMAIL VARCHAR(255) NULL,
  JINY_KONTAKT VARCHAR(128) NOT NULL,
  LOGIN VARCHAR(64) NOT NULL,

  PRIMARY KEY(ID),
  CONSTRAINT UN_U_LOGIN UNIQUE(LOGIN)
);

CREATE TABLE TYP_KONTA (
  ID INTEGER,
  NAZEV VARCHAR(128) NOT NULL,

  PRIMARY KEY(ID)
);

CREATE TABLE OSTATNI_SZ (
  SITOVE_ZARIZENI_ID INTEGER  NOT NULL,
  TYP VARCHAR(20) NOT NULL,
  POPIS VARCHAR2(4000) NULL,

  PRIMARY KEY(SITOVE_ZARIZENI_ID),
  CONSTRAINT FK_SZ_ID FOREIGN KEY (SITOVE_ZARIZENI_ID) REFERENCES SITOVE_ZARIZENI(ID)
);

CREATE TABLE SLUZBA (
  ID INTEGER,
  OSTATNI_SZ_SITOVE_ZARIZENI_ID INTEGER  NOT NULL,
  NAZEV VARCHAR(45) NOT NULL,
  POPIS VARCHAR2(4000) NULL,

  PRIMARY KEY(ID, OSTATNI_SZ_SITOVE_ZARIZENI_ID),
  CONSTRAINT FK_OS_SZ_ID FOREIGN KEY (OSTATNI_SZ_SITOVE_ZARIZENI_ID) REFERENCES OSTATNI_SZ(SITOVE_ZARIZENI_ID)
);

CREATE TABLE LOGOVANI (
  ID INTEGER,
  UZIVATEL_ID INTEGER  NOT NULL,
  DATUM DATE NOT NULL,
  AKCE VARCHAR2(128) DEFAULT '' NOT NULL,
  POPIS VARCHAR2(1024) DEFAULT '' NOT NULL,
  TYP VARCHAR(128) NOT NULL,
  F_ID INTEGER  NULL,

  PRIMARY KEY(ID, UZIVATEL_ID),
  CONSTRAINT FK_UZIVATEL_ID FOREIGN KEY (UZIVATEL_ID) REFERENCES UZIVATEL(ID)
);

CREATE TABLE HOSTNAME_ALIAS (
  ID INTEGER,
  SITOVE_ZARIZENI_ID INTEGER  NOT NULL,
  HOSTNAME VARCHAR(45) NOT NULL,

  PRIMARY KEY(ID, SITOVE_ZARIZENI_ID),
  CONSTRAINT FK_HA_SZ_ID FOREIGN KEY (SITOVE_ZARIZENI_ID) REFERENCES SITOVE_ZARIZENI(ID),
  CONSTRAINT UN_HA_HOSTNAME UNIQUE(HOSTNAME)
);

CREATE TABLE AKTIVNI_CLEN (
  UZIVATEL_ID INTEGER  NOT NULL,
  UREDNI_HODINY VARCHAR2(4000) DEFAULT '' NULL,

  PRIMARY KEY(UZIVATEL_ID),
  CONSTRAINT FK_AC_UZIVATEL_ID FOREIGN KEY (UZIVATEL_ID) REFERENCES UZIVATEL(ID)
);

CREATE TABLE ADMIN (
  AKTIVNI_CLEN_UZIVATEL_ID INTEGER  NOT NULL,

  PRIMARY KEY(AKTIVNI_CLEN_UZIVATEL_ID),
  CONSTRAINT FK_A_AC_UZIVATEL_ID FOREIGN KEY (AKTIVNI_CLEN_UZIVATEL_ID) REFERENCES AKTIVNI_CLEN(UZIVATEL_ID)
);

CREATE TABLE ADMIN_has_SLUZBA (
  ADMIN_ID INTEGER  NOT NULL,
  SLUZBA_SZ_ID INTEGER  NOT NULL,
  SLUZBA_ID INTEGER  NOT NULL,

  PRIMARY KEY(ADMIN_ID, SLUZBA_SZ_ID, SLUZBA_ID),
  CONSTRAINT FK_S_OSZ_SZ_ID FOREIGN KEY (SLUZBA_SZ_ID,SLUZBA_ID) REFERENCES SLUZBA(OSTATNI_SZ_SITOVE_ZARIZENI_ID,ID),
  CONSTRAINT FK_ADMIN_AC_U_ID FOREIGN KEY (ADMIN_ID) REFERENCES ADMIN(AKTIVNI_CLEN_UZIVATEL_ID)
);

CREATE TABLE ADMIN_has_OSTATNI (
  ADMIN_ID INTEGER  NOT NULL,
  OSTATNI_SZ_SITOVE_ZARIZENI_ID INTEGER  NOT NULL,

  PRIMARY KEY(ADMIN_ID, OSTATNI_SZ_SITOVE_ZARIZENI_ID),
  CONSTRAINT FK_AO_A_AC_U_ID FOREIGN KEY(ADMIN_ID) REFERENCES ADMIN(AKTIVNI_CLEN_UZIVATEL_ID),
  CONSTRAINT FK_AO_OSZ_SZ_ID FOREIGN KEY(OSTATNI_SZ_SITOVE_ZARIZENI_ID) REFERENCES OSTATNI_SZ(SITOVE_ZARIZENI_ID)
);

CREATE TABLE TYP_KONTA_has_TYP_PRISPEVKU (
  TYP_KONTA_ID INTEGER  NOT NULL,
  TYP_PRISPEVKU_ID INTEGER  NOT NULL,

  PRIMARY KEY(TYP_KONTA_ID, TYP_PRISPEVKU_ID),
  CONSTRAINT FK_TKTP_TK_ID FOREIGN KEY(TYP_KONTA_ID) REFERENCES TYP_KONTA(ID),
  CONSTRAINT FK_TKTP_TP_ID FOREIGN KEY(TYP_PRISPEVKU_ID) REFERENCES TYP_PRISPEVKU(ID)
);

CREATE TABLE KONTO (
  UZIVATEL_ID INTEGER  NOT NULL,
  TYP_KONTA_ID INTEGER  NOT NULL,
  LOGIN VARCHAR(45) NOT NULL,
  HESLO VARCHAR(45) NULL,

  PRIMARY KEY(UZIVATEL_ID, TYP_KONTA_ID),
  CONSTRAINT FK_K_TK_ID FOREIGN KEY(TYP_KONTA_ID) REFERENCES TYP_KONTA(ID),
  CONSTRAINT UN_K_LOGIN UNIQUE(LOGIN)
);

CREATE TABLE POCITAC (
  SITOVE_ZARIZENI_ID INTEGER  NOT NULL,
  UZIVATEL_ID INTEGER  NOT NULL,

  PRIMARY KEY(SITOVE_ZARIZENI_ID, UZIVATEL_ID),
  CONSTRAINT FK_P_SZ_ID FOREIGN KEY(SITOVE_ZARIZENI_ID) REFERENCES SITOVE_ZARIZENI(ID),
  CONSTRAINT FK_P_U_ID FOREIGN KEY(UZIVATEL_ID) REFERENCES UZIVATEL(ID)
);

CREATE TABLE TREST (
  ID INTEGER,
  UZIVATEL_ID INTEGER  NOT NULL,
  ADMIN_ID INTEGER  NOT NULL,
  DATUM DATE NOT NULL,
  DATUM_OD DATE NOT NULL,
  DATUM_DO DATE NOT NULL,
  DUVOD VARCHAR2(1024) DEFAULT '' NOT NULL,
  TYP VARCHAR(255) NOT NULL,

  PRIMARY KEY(ID, UZIVATEL_ID, ADMIN_ID),
  CONSTRAINT FK_T_U_ID FOREIGN KEY (UZIVATEL_ID) REFERENCES UZIVATEL(ID),
  CONSTRAINT FK_T_A_ID FOREIGN KEY(ADMIN_ID) REFERENCES ADMIN(AKTIVNI_CLEN_UZIVATEL_ID)
);

CREATE TABLE AKTIVNI_CLEN_has_TYP_CLENA (
  AKTIVNI_CLEN_UZIVATEL_ID INTEGER  NOT NULL,
  TYP_CLENA_ID INTEGER  NOT NULL,

  PRIMARY KEY(AKTIVNI_CLEN_UZIVATEL_ID, TYP_CLENA_ID),
  CONSTRAINT FK_ACTC_AC_U_ID FOREIGN KEY(AKTIVNI_CLEN_UZIVATEL_ID) REFERENCES AKTIVNI_CLEN(UZIVATEL_ID),
  CONSTRAINT FK_ACTC_TC_ID FOREIGN KEY(TYP_CLENA_ID) REFERENCES TYP_CLENA(ID)
);

CREATE TABLE PRISPEVEK (
  ID INTEGER,
  SEMESTR_ID INTEGER  NOT NULL,
  UZIVATEL_ID INTEGER  NOT NULL,
  TYP_PRISPEVKU_ID INTEGER  NOT NULL,
  DATUM DATE NOT NULL,
  PROMINUT NUMBER(1,0) NOT NULL,
  POPIS VARCHAR2(1024) NULL,

  PRIMARY KEY(ID, SEMESTR_ID, UZIVATEL_ID, TYP_PRISPEVKU_ID),
  CONSTRAINT FK_P_S_ID FOREIGN KEY(SEMESTR_ID) REFERENCES SEMESTR(ID),
  CONSTRAINT FK_PR_U_ID FOREIGN KEY(UZIVATEL_ID) REFERENCES UZIVATEL(ID),
  CONSTRAINT FK_P_TP_ID FOREIGN KEY(TYP_PRISPEVKU_ID) REFERENCES TYP_PRISPEVKU(ID)
);

CREATE TABLE NOVINKY (
  ID INTEGER,
  AKTIVNI_CLEN_UZIVATEL_ID INTEGER  NOT NULL,
  DATUM DATE NOT NULL,
  TEXT VARCHAR2(4000) NOT NULL,

  PRIMARY KEY(ID, AKTIVNI_CLEN_UZIVATEL_ID),
  CONSTRAINT FK_N_AC_U_ID FOREIGN KEY(AKTIVNI_CLEN_UZIVATEL_ID) REFERENCES AKTIVNI_CLEN(UZIVATEL_ID)
);

CREATE TABLE EMAIL_ALIAS (
  ID INTEGER,
  KONTO_UZIVATEL_ID INTEGER  NOT NULL,
  KONTO_TYP_KONTA_ID INTEGER  NOT NULL,
  NAZEV VARCHAR(45) NOT NULL,

  PRIMARY KEY(ID, KONTO_UZIVATEL_ID, KONTO_TYP_KONTA_ID),
  CONSTRAINT FK_EA_K_U_ID FOREIGN KEY(KONTO_UZIVATEL_ID,KONTO_TYP_KONTA_ID) REFERENCES KONTO(UZIVATEL_ID,TYP_KONTA_ID),
  CONSTRAINT UN_EA_NAZEV UNIQUE(NAZEV)
);

CREATE TABLE KONTO_TISKARNA (
  KONTO_UZIVATEL_ID INTEGER  NOT NULL,
  KONTO_TYP_KONTA_ID INTEGER  NOT NULL,
  KREDIT FLOAT NOT NULL,

  PRIMARY KEY(KONTO_UZIVATEL_ID, KONTO_TYP_KONTA_ID),
  CONSTRAINT FK_KT_K_U_ID FOREIGN KEY(KONTO_UZIVATEL_ID,KONTO_TYP_KONTA_ID) REFERENCES KONTO(UZIVATEL_ID,TYP_KONTA_ID)
);

/* vytvoreni sekvenci pro generovani primarnich klicu */
CREATE SEQUENCE TYP_CLENA_SEQ;
CREATE SEQUENCE SITOVE_ZARIZENI_SEQ;
CREATE SEQUENCE SEMESTR_SEQ;
CREATE SEQUENCE TYP_PRISPEVKU_SEQ;
CREATE SEQUENCE UZIVATEL_SEQ;
CREATE SEQUENCE TYP_KONTA_SEQ;
CREATE SEQUENCE SLUZBA_SEQ;
CREATE SEQUENCE LOGOVANI_SEQ;
CREATE SEQUENCE HOSTNAME_ALIAS_SEQ;
CREATE SEQUENCE TREST_SEQ;
CREATE SEQUENCE PRISPEVEK_SEQ;
CREATE SEQUENCE NOVINKY_SEQ;
CREATE SEQUENCE EMAIL_ALIAS_SEQ;

COMMIT;
