DatabaseCreation: ddl.sql

--DDL 
CREATE TABLE Gragjanin (
    EMBG char(13) PRIMARY KEY,
    ime varchar(50) NOT NULL default 'unknown',
    prezime varchar(50) NOT NULL default'unknown',
    pol varchar(1) ,
    adresa varchar(100),
    datum_ragjanje date ,
    CHECK (pol IN ('M','F')),
    CHECK (LENGTH(EMBG) = 13),
    CHECK (datum_ragjanje <= CURRENT_DATE)
);

CREATE TABLE Kategorija (
    id_kategorija SERIAL PRIMARY KEY,
    kod varchar(3) NOT NULL
);

CREATE TABLE Vozilo (
    broj_na_sasija varchar(17) PRIMARY KEY,
    model varchar(50) ,
    id_kategorija int,
    FOREIGN KEY (id_kategorija) REFERENCES Kategorija(id_kategorija) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Vozacka_dozvola (
    broj_dozvola varchar(30) PRIMARY KEY,
    datum_izdavanje date NOT NULL,
    datum_vaznost_do date NOT NULL,
    EMBG char(13),
    CHECK (datum_vaznost_do > datum_izdavanje),
    FOREIGN KEY (EMBG) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Kategorija_Vozacka_dozvola (
    id_kategorija int,
    broj_dozvola varchar(30),
    datum_polaganje date,
    PRIMARY KEY (id_kategorija, broj_dozvola),
    FOREIGN KEY (id_kategorija) REFERENCES Kategorija(id_kategorija) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (broj_dozvola) REFERENCES Vozacka_dozvola(broj_dozvola) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Boja_vozilo (
    broj_na_sasija varchar(17),
    boja varchar(30),
    PRIMARY KEY (broj_na_sasija, boja),
    FOREIGN KEY (broj_na_sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT
);


CREATE TABLE Sopstvenost (
    id_sopstvenost SERIAL PRIMARY KEY,
    EMBG char(13),
    broj_na_sasija varchar(17),
    datum_steknuvanje date NOT NULL DEFAULT CURRENT_DATE,
    datum_kraj date,
    CHECK (datum_steknuvanje <= CURRENT_DATE),
    CHECK (datum_kraj IS NULL OR datum_kraj >= datum_steknuvanje),
    FOREIGN KEY (EMBG) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (broj_na_sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT
);


CREATE TABLE Registerska_tablica (
    id_tablica SERIAL PRIMARY KEY,
    region varchar(3) NOT NULL,
    broj varchar(4) NOT NULL,
    kod varchar(2) NOT NULL,
    UNIQUE (region, broj, kod)
);


CREATE TABLE Registracija (
    id_registracija SERIAL PRIMARY KEY,
    id_sopstvenost int NOT NULL,
    datum_na_izvadanje date NOT NULL,
    datum_na_istekuvanje date,
    id_tablica int,
    CHECK (datum_na_istekuvanje IS NULL OR datum_na_istekuvanje >= datum_na_izvadanje),
    FOREIGN KEY (id_sopstvenost) REFERENCES Sopstvenost(id_sopstvenost) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (id_tablica) REFERENCES Registerska_tablica(id_tablica) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Policaec (
    EMBG_P char(13) PRIMARY KEY,
    broj_na_znacka varchar(20),
    EMBG_nadreden char(13),
    FOREIGN KEY (EMBG_P) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (EMBG_nadreden) REFERENCES Policaec(EMBG_P) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Sektor (
    id_sektor int PRIMARY KEY,
    ime varchar(100) NOT NULL,
    region varchar(100),
    telefon varchar(13),
    CHECK (
        telefon IS NULL OR
        telefon ~ '^\+389[0-9]{8}$'
    )
);

CREATE TABLE Stanica (
    id_stanica SERIAL PRIMARY KEY,
    ime varchar(100) ,
    adresa varchar(100) ,
    id_sektor int ,
    FOREIGN KEY (id_sektor) REFERENCES Sektor(id_sektor) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Raboti_vo (
    EMBG_P char(13),
    id_stanica int,
    datum_od date not null,
    datum_do date ,
    PRIMARY KEY (EMBG_P, id_stanica),
    CHECK (datum_do IS NULL OR datum_do >= datum_od),
    FOREIGN KEY (EMBG_P) REFERENCES Policaec(EMBG_P) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (id_stanica) REFERENCES Stanica(id_stanica) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Kazna (
    id_kazna int PRIMARY KEY,
    iznos_kazna numeric(10,2) NOT NULL CHECK (iznos_kazna >= 0),
    opis text
);

CREATE TABLE Prekrsok (
    id_prekrsok int PRIMARY KEY,
    ime varchar(100),
    opis text ,
    id_kazna int4,
    FOREIGN KEY (id_kazna) REFERENCES Kazna(id_kazna) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Slucaj (
    id_slucaj SERIAL PRIMARY KEY,
    datum_otvaranje date NOT NULL ,
    status varchar(20) default 'Otvoren',
    tip_nastan text,
    CHECK (status IN ('Otvoren', 'Zatvoren', 'Vo tek')),
    CHECK (datum_otvaranje <= CURRENT_DATE)
);

CREATE TABLE Zapisnik (
    id_na_zapisnik serial PRIMARY KEY,
    vreme time,
    datum date CHECK (datum IS NULL OR  datum <= CURRENT_DATE),
    lokacija varchar(100),
    Potpis boolean DEFAULT false,
    id_slucaj int,
    status_zapisnik varchar(20) default 'Otvoren',
    EMBG_Prekrsuvach char(13),
    Vozilo_Broj_Sasija varchar(17),
    EMBG_Policaec char(13),
    CHECK (status_zapisnik IN ('Otvoren', 'Zatvoren')),
    FOREIGN KEY (id_slucaj) REFERENCES Slucaj(id_slucaj) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (EMBG_Prekrsuvach) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (Vozilo_Broj_Sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (EMBG_Policaec) REFERENCES Policaec(EMBG_P) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Stavka_Zapisnik (
    id_stavka SERIAL PRIMARY KEY,
    reden_broj int,
    id_na_zapisnik int,
    id_na_prekrsok int,
    FOREIGN KEY (id_na_zapisnik) REFERENCES Zapisnik(id_na_zapisnik) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (id_na_prekrsok) REFERENCES Prekrsok(id_prekrsok) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Zadaca (
    id_zadaca SERIAL PRIMARY KEY,
    opis varchar(255),
    status varchar(20) NOT NULL default 'Aktivna',
    EMBG_policaec char(13),
    id_slucaj int,
    FOREIGN KEY (EMBG_policaec) REFERENCES Policaec(EMBG_P) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (id_slucaj) REFERENCES Slucaj(id_slucaj) ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK (status IN ('Aktivna', 'Zavrsena', 'Otkazana'))
);

CREATE TABLE Uplata (
    id_uplata serial PRIMARY KEY,
    iznos numeric(10,2)  NOT NULL ,
    status varchar(30) NOT NULL DEFAULT 'Neplateno',
    datum_uplata date ,
    Uplatil_Gragjanin char(13),
    id_zapisnik int ,
    nacin_plakanje varchar(30),
    kazna_zgolemena boolean,
    FOREIGN KEY (Uplatil_Gragjanin) REFERENCES Gragjanin(EMBG) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (id_zapisnik) REFERENCES Zapisnik(id_na_zapisnik) ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK(iznos >= 0),
    CHECK (datum_uplata IS NULL OR datum_uplata <= CURRENT_DATE),
    CHECK (status IN ('Plateno', 'Neplateno', 'Sudska_postapka'))
);