logicalAndPhysicalDesign: web3EdnevnikDDL_v1.sql

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE Admin_User (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    e_posta VARCHAR(100) NOT NULL UNIQUE,
    lozinka VARCHAR(255) NOT NULL,
    ime VARCHAR(30) NOT NULL,
    prezime VARCHAR(30) NOT NULL
);

CREATE TABLE Mon_Admin (
    id UUID PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES Admin_User (id) ON DELETE CASCADE
);

CREATE TABLE Direktor_Admin (
    id UUID PRIMARY KEY,
    kreiran_Od UUID,
    FOREIGN KEY (id) REFERENCES Admin_User (id) ON DELETE CASCADE,
    FOREIGN KEY (kreiran_Od) REFERENCES Mon_Admin (id) ON DELETE CASCADE
);

CREATE TABLE Korisnik (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    e_posta VARCHAR(100) NOT NULL UNIQUE,
    lozinka VARCHAR(255) NOT NULL,
    ime VARCHAR(30) NOT NULL,
    prezime VARCHAR(30) NOT NULL,
    pol VARCHAR(10) NOT NULL,
    adresa VARCHAR(100),
    kreiranOd_id UUID,
    FOREIGN KEY (kreiranOd_id) REFERENCES Direktor_Admin (id)
);

CREATE TABLE Nastavnik (
    id UUID PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES Korisnik (id) ON DELETE CASCADE
);

CREATE TABLE Klasen_Rakovoditel (
    id UUID PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES Nastavnik (id) ON DELETE CASCADE
);

CREATE TABLE Predmeten_Nastavnik (
    id UUID PRIMARY KEY,
    FOREIGN KEY (id) REFERENCES Nastavnik (id) ON DELETE CASCADE
);

CREATE TABLE Blagajnik (
    id UUID PRIMARY KEY,
    metamusk_adresa VARCHAR(80) NOT NULL,
    FOREIGN KEY (id) REFERENCES Korisnik (id) ON DELETE CASCADE
);

CREATE TABLE Roditel (
    id UUID PRIMARY KEY,
    metamusk_adresa VARCHAR(80) NOT NULL,
    FOREIGN KEY (id) REFERENCES Korisnik (id) ON DELETE CASCADE
);

CREATE TABLE Klas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    godinaNa_Slusanje VARCHAR(10) NOT NULL,
    paralelka CHAR(1) NOT NULL,
    oddelenie INTEGER NOT NULL
);

CREATE TABLE Ucenik (
    id UUID PRIMARY KEY,
    mestoNa_ragjanje VARCHAR(30) NOT NULL,
    mobilen_telefon VARCHAR(15) NOT NULL,
    datumNa_ragjanje DATE NOT NULL,
    staratel_id UUID,
    imeNa_staratel VARCHAR(30) NOT NULL,
    uciVoKlas_id UUID,
    FOREIGN KEY (uciVoKlas_id) REFERENCES Klas (id) ON DELETE CASCADE,
    FOREIGN KEY (staratel_id) REFERENCES Roditel (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES Korisnik (id) ON DELETE CASCADE
);

CREATE TABLE AktivnostZa_NedelenPlan (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(30) NOT NULL,
    opis VARCHAR(80) NOT NULL,
    denVo_Nedelata VARCHAR(10) NOT NULL
);

CREATE TABLE Cas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    reden_cas INTEGER NOT NULL,
    denVo_Nedelata VARCHAR(10) NOT NULL
);

CREATE TABLE Prisustvo (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    datum DATE NOT NULL,
    status VARCHAR(10) NOT NULL,
    zabeleska TEXT,
    evidentiranoOd_Id UUID,
    seOdnesuvaNaUcenikot_Id UUID,
    zaCasot_Id UUID,
    FOREIGN KEY (evidentiranoOd_Id) REFERENCES Nastavnik (id),
    FOREIGN KEY (seOdnesuvaNaUcenikot_Id) REFERENCES Ucenik (id),
    FOREIGN KEY (zaCasot_Id) REFERENCES Cas (id)
);

CREATE TABLE Predmet (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    ime VARCHAR(30) NOT NULL,
    fond_casovi INTEGER NOT NULL,
    zaKlas_Id UUID,
    FOREIGN KEY (zaKlas_Id) REFERENCES Klas (id)
);

CREATE TABLE Ocenka (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    ocenka INTEGER NOT NULL,
    datum DATE NOT NULL,
    zabeleska TEXT,
    vnesena_Od UUID,
    dobienaZaUcenikot_Id UUID,
    eZaPredmetot_Id UUID,
    zaAktivnosta_id UUID,
    FOREIGN KEY (eZaPredmetot_Id) REFERENCES Predmet (id),
    FOREIGN KEY (dobienaZaUcenikot_Id) REFERENCES Ucenik (id),
    FOREIGN KEY (zaAktivnosta_id) REFERENCES AktivnostZa_NedelenPlan (id),
    FOREIGN KEY (vnesena_Od) REFERENCES Nastavnik (id)
);

CREATE TABLE Usna_Ocenka (
    id UUID,
    tema VARCHAR(50) NOT NULL,
    osvoeni_poeni INTEGER NOT NULL,
    max_poeni INTEGER NOT NULL,
    vid_isprasuvanje VARCHAR(20) NOT NULL,
    FOREIGN KEY (id) REFERENCES Ocenka (id) ON DELETE CASCADE
);

CREATE TABLE Pismena_Ocenka (
    id UUID,
    tema VARCHAR(50) NOT NULL,
    osvoeni_poeni INTEGER NOT NULL,
    max_poeni INTEGER NOT NULL,
    vid VARCHAR(20) NOT NULL,
    FOREIGN KEY (id) REFERENCES Ocenka (id) ON DELETE CASCADE
);

CREATE TABLE Krajna_Ocenka (
    id UUID,
    tip VARCHAR(20) NOT NULL,
    FOREIGN KEY (id) REFERENCES Ocenka (id)
);

CREATE TABLE Soopstenie (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(30) NOT NULL,
    sodrzina TEXT NOT NULL,
    kreirano_Na DATE NOT NULL,
    zaKlas_Id UUID,
    postiranoOd_Id UUID,
    FOREIGN KEY (postiranoOd_Id) REFERENCES Klasen_Rakovoditel (id),
    FOREIGN KEY (zaKlas_Id) REFERENCES Klas (id)
);

CREATE TABLE Nedelen_Plan (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    datum_Od DATE NOT NULL,
    datum_Do DATE NOT NULL,
    sledenOdKlas_Id UUID,
    FOREIGN KEY (sledenOdKlas_Id) REFERENCES Klas (id)
);

CREATE TABLE Raspored (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(30) NOT NULL,
    tip VARCHAR(10) NOT NULL,
    izgotvenOd_Id UUID,
    FOREIGN KEY (izgotvenOd_Id) REFERENCES Direktor_Admin (id)
);

CREATE TABLE SoopstenieZa_Plakjanje (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(100) NOT NULL,
    sodrzina TEXT NOT NULL,
    rokZa_Plakjanje DATE NOT NULL,
    odobrenoOd_Id UUID,
    sozdadenoOdBlagajnik_Id UUID,
    FOREIGN KEY (sozdadenoOdBlagajnik_Id) REFERENCES Blagajnik (id),
    FOREIGN KEY (odobrenoOd_Id) REFERENCES Direktor_Admin (id)
);

CREATE TABLE Plakjanje (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    status VARCHAR(10) NOT NULL,
    tx_hash VARCHAR(100) NOT NULL,
    valuta VARCHAR(10) NOT NULL,
    plateno_Na DATE NOT NULL,
    iznos DECIMAL(38, 18) NOT NULL,
    platenoOdRoditel_Id UUID,
    FOREIGN KEY (platenoOdRoditel_Id) REFERENCES Roditel (id)
);

CREATE TABLE KlasSlusa_Predmet (
    klas_Id UUID,
    predmet_Id UUID,
    PRIMARY KEY (klas_id, predmet_Id),
    FOREIGN KEY (predmet_Id) REFERENCES Predmet (id),
    FOREIGN KEY (klas_id) REFERENCES Klas (id)
);

CREATE TABLE NastavnikGoPredava_Predmetot (
    nastavnik_Id UUID,
    predmet_Id UUID,
    PRIMARY KEY (nastavnik_Id, predmet_Id),
    FOREIGN KEY (predmet_Id) REFERENCES Predmet (id),
    FOREIGN KEY (nastavnik_Id) REFERENCES Nastavnik (id)
);

CREATE TABLE NedelenPlanOpfakja_Aktivnosti (
    nedelenPlan_Id UUID,
    aktivnostZaNedelenPlan_Id UUID,
    PRIMARY KEY (
        nedelenPlan_Id,
        aktivnostZaNedelenPlan_Id
    ),
    FOREIGN KEY (aktivnostZaNedelenPlan_Id) REFERENCES AktivnostZa_NedelenPlan (id),
    FOREIGN KEY (nedelenPlan_Id) REFERENCES Nedelen_Plan (id)
);

CREATE TABLE KlasenRakovoditelZapisuvaAktivnostZaNedelen_Plan (
    klasenRakovoditel_Id UUID,
    aktivnostZaNedelenPlan_Id UUID,
    PRIMARY KEY (
        klasenRakovoditel_Id,
        aktivnostZaNedelenPlan_Id
    ),
    FOREIGN KEY (aktivnostZaNedelenPlan_Id) REFERENCES AktivnostZa_NedelenPlan (id),
    FOREIGN KEY (klasenRakovoditel_Id) REFERENCES Klasen_Rakovoditel (id)
);

CREATE TABLE RasporedSodrzi_Cas (
    raspored_Id UUID,
    cas_Id UUID,
    PRIMARY KEY (raspored_Id, cas_Id),
    FOREIGN KEY (cas_Id) REFERENCES Cas (id),
    FOREIGN KEY (raspored_Id) REFERENCES Raspored (id)
);

CREATE TABLE PredmetSePredavaNa_Cas (
    predmet_Id UUID,
    cas_Id UUID,
    PRIMARY KEY (predmet_Id, cas_Id),
    FOREIGN KEY (cas_Id) REFERENCES Cas (id),
    FOREIGN KEY (predmet_Id) REFERENCES Predmet (id)
);

CREATE TABLE KlasenRakovoditelRakovodiSo_Klas (
    klasenRakovoditel_Id UUID PRIMARY KEY,
    klas_Id UUID,
    FOREIGN KEY (klas_Id) REFERENCES Klas (id),
    FOREIGN KEY (klasenRakovoditel_Id) REFERENCES Klasen_Rakovoditel (id)
);

CREATE TABLE KlasKoristi_Raspored (
    klas_Id UUID PRIMARY KEY,
    raspored_Id UUID,
    FOREIGN KEY (raspored_Id) REFERENCES Raspored (id),
    FOREIGN KEY (klas_Id) REFERENCES Klas (id)
);

CREATE TABLE SoopstenieZaPlakjanjeVklucuva_Plakjanje (
    soopstenieZaPlakjanje_Id UUID PRIMARY KEY,
    plakjanje_Id UUID,
    FOREIGN KEY (plakjanje_Id) REFERENCES Plakjanje (id),
    FOREIGN KEY (soopstenieZaPlakjanje_Id) REFERENCES SoopstenieZa_Plakjanje (id)
);