-- skript pro vytvoreni objektu databaze

-- vymazani objektu -------------------------------------------------------------------------------------

DROP TABLE IF EXISTS osoby;
DROP TABLE IF EXISTS skoly;
DROP TABLE IF EXISTS temata_kurzu;
DROP TABLE IF EXISTS kurzy;
DROP TABLE IF EXISTS osoba_kurz;
DROP TABLE IF EXISTS dotazniky;
DROP TABLE IF EXISTS fond;
DROP TABLE IF EXISTS hodnoceni;
DROP TABLE IF EXISTS logy;

-- vytvoreni tabulek databaze ---------------------------------------------------------------------------

CREATE TABLE osoby (
ID INTEGER AUTO_INCREMENT,
jmeno VARCHAR(128) NOT NULL,
prijmeni VARCHAR(128) NOT NULL,
email VARCHAR(256) NOT NULL UNIQUE,
heslo VARCHAR(256) NOT NULL,
opravneni ENUM('blokovany', 'cekatel', 'instruktor', 'zamestnanec', 'admin') DEFAULT 'cekatel',
max_role SMALLINT DEFAULT 2,
datum_narozeni DATE NOT NULL,
adresa_ulice VARCHAR(64) NOT NULL,
adresa_mesto VARCHAR(64) NOT NULL,
adresa_psc VARCHAR(5) NOT NULL,
telefon VARCHAR(16),
pohlavi ENUM('muz', 'zena') NOT NULL,
poznamky VARCHAR(256),
posledni_prihlaseni DATETIME,
obnova_hesla VARCHAR(256),
cas_zadosti DATETIME,
souhlas_ou BOOLEAN NOT NULL DEFAULT 0,
CONSTRAINT PK_osoby PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE skoly (
ID INTEGER AUTO_INCREMENT,
email VARCHAR(128) NOT NULL UNIQUE,
heslo VARCHAR(256) NOT NULL,
nazev VARCHAR(256) NOT NULL,
osoba_jmeno VARCHAR(256) NOT NULL,
osoba_email VARCHAR(256) NOT NULL,
osoba_telefon VARCHAR(16) NOT NULL,
adresa_ulice VARCHAR(64) NOT NULL,
adresa_mesto VARCHAR(64) NOT NULL,
adresa_psc VARCHAR(5) NOT NULL,
posledni_prihlaseni DATETIME,
souhlas_ou BOOLEAN NOT NULL DEFAULT 0,
CONSTRAINT PK_skoly PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE temata_kurzu (
ID INTEGER AUTO_INCREMENT,
nazev VARCHAR(256) NOT NULL,
popis VARCHAR(1024) NOT NULL,
stav ENUM('aktivni', 'neaktivni') DEFAULT 'aktivni',
CONSTRAINT PK_tema_kurzu PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE kurzy (
ID INTEGER AUTO_INCREMENT,
ID_skoly INTEGER REFERENCES skola(ID),
ID_tema_kurzu INTEGER REFERENCES tema_kurzu(ID),
ID_predchudce INTEGER REFERENCES kurzy(ID),
trida VARCHAR(64) NOT NULL,
datum_zacatku DATE NOT NULL,
datum_konce DATE NOT NULL,
informace VARCHAR(1024) DEFAULT '',
zapis VARCHAR(2048) DEFAULT '',
stav ENUM('zadost', 'zamitnuty', 'aktivni') DEFAULT 'zadost',
CONSTRAINT PK_kurzy PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE osoba_kurz (
ID_kurzu INTEGER REFERENCES skoly(ID),
ID_osoby INTEGER REFERENCES temata_kurzu(ID),
cislo_role INTEGER NOT NULL,
stav ENUM('zadost', 'aktivni') DEFAULT 'zadost',
CONSTRAINT PK_instruktor_kurz PRIMARY KEY (ID_kurzu, ID_osoby)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE dotazniky (
ID INTEGER AUTO_INCREMENT,
ID_kurzu INTEGER REFERENCES kurzy(ID),
cas_prijezdu TIME,
cas_odjezdu TIME,
pocet_muzu SMALLINT,
pocet_zen SMALLINT,
pocet_dopr_muzu SMALLINT,
pocet_dopr_zen SMALLINT,
osoba_jmeno VARCHAR(256),
osoba_email VARCHAR(256),
osoba_telefon VARCHAR(16),
ocekavani VARCHAR(2048),
aktualni_situace VARCHAR(2048),
specificka_strava VARCHAR(2048),
svaciny BOOLEAN NOT NULL,
jine_kurzy VARCHAR(2048),
dalsi_informace VARCHAR(2048),
cas_vyplneni DATETIME DEFAULT NOW(),
cas_zmeny DATETIME DEFAULT NOW(),
souhlas_ou BOOLEAN NOT NULL DEFAULT 0,
CONSTRAINT PK_skoly PRIMARY KEY (ID),
CONSTRAINT unique_ID_kurzu UNIQUE (ID_kurzu)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE fond (
ID INTEGER AUTO_INCREMENT,
ID_osoby INTEGER REFERENCES osoby(ID),
ID_kurzu INTEGER REFERENCES kurzy(ID),
castka INTEGER NOT NULL,
popis VARCHAR(512),
datum DATE,
cas_vyplneni DATETIME DEFAULT NOW(),
CONSTRAINT PK_fond PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE hodnoceni (
ID INTEGER AUTO_INCREMENT,
ID_hodnotitele INTEGER REFERENCES osoby(ID),
ID_hodnoceneho INTEGER REFERENCES osoby(ID),
ID_kurzu INTEGER REFERENCES kurzy(ID),
popis VARCHAR(1024),
cas_vyplneni DATETIME DEFAULT NOW(),
CONSTRAINT PK_hodnoceni PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE logy (
ID INTEGER AUTO_INCREMENT,
ID_osoby INTEGER REFERENCES osoby(ID),
ID_skoly INTEGER REFERENCES skoly(ID),
typ_dat VARCHAR(256),
operace ENUM('vlozeni', 'uprava', 'smazani'),
hodnota VARCHAR(2048),
cas_vyplneni DATETIME DEFAULT NOW(),
CONSTRAINT PK_hodnoceni PRIMARY KEY (ID)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_czech_ci;