logicalAndPhysicalDesign: web3EdnevnikDDL_v3.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,
    active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE Mon_Admin (
    id UUID PRIMARY KEY,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Admin_User (id)
);

CREATE TABLE Direktor_Admin (
    id UUID PRIMARY KEY,
    kreiran_Od UUID,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Admin_User (id),
    FOREIGN KEY (kreiran_Od) REFERENCES Mon_Admin (id)
);

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,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (kreiranOd_id) REFERENCES Admin_User (id)
);

CREATE TABLE Nastavnik (
    id UUID PRIMARY KEY,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Korisnik (id)
);

CREATE TABLE Klasen_Rakovoditel (
    id UUID PRIMARY KEY,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Nastavnik (id) ON DELETE CASCADE
);

CREATE TABLE Predmeten_Nastavnik (
    id UUID PRIMARY KEY,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Nastavnik (id) ON DELETE CASCADE
);

CREATE TABLE Blagajnik (
    id UUID PRIMARY KEY,
    metamusk_adresa VARCHAR(80) NOT NULL UNIQUE,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (id) REFERENCES Korisnik (id)
);

CREATE TABLE Roditel (
    id UUID PRIMARY KEY,
    active BOOLEAN NOT NULL DEFAULT true,
    metamusk_adresa VARCHAR(80) UNIQUE,
    FOREIGN KEY (id) REFERENCES Korisnik (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,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (izgotvenOd_Id) REFERENCES Direktor_Admin (id)
);

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,
    koristiRasporedId UUID,
    active BOOLEAN NOT NULL DEFAULT true,
    klasen_rakovoditel_id UUID NOT NULL,
    FOREIGN KEY (koristiRasporedId) REFERENCES Raspored (id) ON DELETE RESTRICT,
    FOREIGN KEY (klasen_rakovoditel_id) REFERENCES Klasen_Rakovoditel (id) ON DELETE RESTRICT
);

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,
    uciVoKlas_id UUID,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (uciVoKlas_id) REFERENCES Klas (id) ON DELETE RESTRICT,
    FOREIGN KEY (staratel_id) REFERENCES Roditel (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES Korisnik (id)
);

CREATE TABLE Predmet (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    ime VARCHAR(30) NOT NULL,
    fond_casovi INTEGER NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true
  
);


CREATE TABLE AktivnostZa_NedelenPlan (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(30) NOT NULL,
    opis VARCHAR(80) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    denVo_Nedelata VARCHAR(10) NOT NULL,
    predmet_id UUID NOT NULL,
    FOREIGN KEY(predmet_id) REFERENCES Predmet(id)
);


CREATE TABLE Cas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    ime VARCHAR(30) NOT NULL,
    reden_cas INTEGER NOT NULL,
    predmet_Id UUID NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    denVo_Nedelata VARCHAR(10) NOT NULL,
 FOREIGN KEY (predmet_Id) REFERENCES Predmet(id)
);

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 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) ON DELETE CASCADE
);

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,
    active BOOLEAN NOT NULL DEFAULT true,
    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,
    active BOOLEAN NOT NULL DEFAULT true,
    FOREIGN KEY (sledenOdKlas_Id) REFERENCES Klas (id)
);
CREATE TYPE payment_status AS ENUM (
    'KREIRANO',
    'TX_SUBMITTED',
    'ODOBRENO',
    'ZAVRSENO',
    'FAILED'
);
CREATE TYPE notice_status AS ENUM (
    'PENDING',
    'APPROVED',
    'REJECTED'
);

CREATE TABLE SoopstenieZa_Plakjanje (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    naslov VARCHAR(100) NOT NULL,
    sodrzina TEXT NOT NULL,
    iznos DECIMAL(38, 18) NOT NULL,
    valuta VARCHAR(10) NOT NULL,
    rokZa_Plakjanje DATE NOT NULL,
    status notice_status NOT NULL DEFAULT 'PENDING',
    obrabotenoOd_Id UUID,
    sozdadenoOdBlagajnik_Id UUID NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP DEFAULT now(),
    FOREIGN KEY (sozdadenoOdBlagajnik_Id) REFERENCES Blagajnik (id),
    FOREIGN KEY (obrabotenoOd_Id) REFERENCES Direktor_Admin (id)
);

CREATE TABLE Plakjanje (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    status payment_status NOT NULL,
    tx_hash VARCHAR(100) UNIQUE,
    valuta VARCHAR(10) NOT NULL,
    plateno_Na TIMESTAMP ,
    iznos DECIMAL(38, 18) NOT NULL,
    platenoOdRoditel_Id UUID NOT NULL,
    soopstenie_za_plakjanje_id UUID NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    UNIQUE (platenoOdRoditel_Id, soopstenie_za_plakjanje_id),
    CONSTRAINT chk_tx_hash_when_paid CHECK (
        (status = 'KREIRANO' AND tx_hash IS NULL)
        OR
        (status IN ('TX_SUBMITTED', 'ODOBRENO', 'ZAVRSENO', 'FAILED') AND tx_hash IS NOT NULL)
    ),
    FOREIGN KEY (soopstenie_za_plakjanje_id) REFERENCES SoopstenieZa_Plakjanje (id),
    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 NastavnikZapisuvaAktivnostZaNedelen_Plan (
    nastavnik_Id UUID,
    aktivnostZaNedelenPlan_Id UUID,
    PRIMARY KEY (
        nastavnik_Id,
        aktivnostZaNedelenPlan_Id
    ),
    FOREIGN KEY (aktivnostZaNedelenPlan_Id) REFERENCES AktivnostZa_NedelenPlan (id),
    FOREIGN KEY (nastavnik_Id) REFERENCES Nastavnik(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)
);