source: schema.sql

-- project.avtori definition

-- Drop table

-- DROP TABLE project.avtori;

CREATE TABLE project.avtori (
        avtor_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        ime varchar(100) NOT NULL,
        prezime varchar(100) NOT NULL,
        biografija text NULL,
        CONSTRAINT avtori_pkey PRIMARY KEY (avtor_id)
);


-- project.kategorii definition

-- Drop table

-- DROP TABLE project.kategorii;

CREATE TABLE project.kategorii (
        kategorija_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        naziv varchar(100) NOT NULL,
        opis text NULL,
        CONSTRAINT kategorii_pkey PRIMARY KEY (kategorija_id)
);


-- project.knigi definition

-- Drop table

-- DROP TABLE project.knigi;

CREATE TABLE project.knigi (
        kniga_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        naslov varchar(255) NOT NULL,
        isbn varchar(20) NOT NULL,
        opis text NULL,
        korica_url text NULL,
        godina_izdavanje int2 NULL,
        kolicina_na_zaliha int4 NOT NULL,
        cena numeric(10, 2) NOT NULL,
        CONSTRAINT knigi_isbn_key UNIQUE (isbn),
        CONSTRAINT knigi_pkey PRIMARY KEY (kniga_id)
);
CREATE INDEX idx_knigi_naslov_trgm ON project.knigi USING gin (lower((naslov)::text) gin_trgm_ops);


-- project.korisnici definition

-- Drop table

-- DROP TABLE project.korisnici;

CREATE TABLE project.korisnici (
        korisnik_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        ime varchar(100) NOT NULL,
        prezime varchar(100) NOT NULL,
        email varchar(255) NOT NULL,
        lozinka varchar(255) NOT NULL,
        datum_registracija date NOT NULL,
        uloga varchar(20) NOT NULL,
        CONSTRAINT korisnici_email_key UNIQUE (email),
        CONSTRAINT korisnici_pkey PRIMARY KEY (korisnik_id)
);


-- project.raspolozenija definition

-- Drop table

-- DROP TABLE project.raspolozenija;

CREATE TABLE project.raspolozenija (
        raspolozenie_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        naziv varchar(100) NOT NULL,
        opis text NULL,
        CONSTRAINT raspolozenija_pkey PRIMARY KEY (raspolozenie_id)
);


-- project.zhanrovi definition

-- Drop table

-- DROP TABLE project.zhanrovi;

CREATE TABLE project.zhanrovi (
        zhanr_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        naziv varchar(100) NOT NULL,
        opis text NULL,
        CONSTRAINT zhanrovi_pkey PRIMARY KEY (zhanr_id)
);


-- project.ima_kategorija definition

-- Drop table

-- DROP TABLE project.ima_kategorija;

CREATE TABLE project.ima_kategorija (
        kniga_id int8 NOT NULL,
        kategorija_id int8 NOT NULL,
        CONSTRAINT ima_kategorija_pkey PRIMARY KEY (kniga_id, kategorija_id),
        CONSTRAINT ima_kategorija_kategorija_id_fkey FOREIGN KEY (kategorija_id) REFERENCES project.kategorii(kategorija_id),
        CONSTRAINT ima_kategorija_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id)
);


-- project.ima_zhanr definition

-- Drop table

-- DROP TABLE project.ima_zhanr;

CREATE TABLE project.ima_zhanr (
        kniga_id int8 NOT NULL,
        zhanr_id int8 NOT NULL,
        CONSTRAINT ima_zhanr_pkey PRIMARY KEY (kniga_id, zhanr_id),
        CONSTRAINT ima_zhanr_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id),
        CONSTRAINT ima_zhanr_zhanr_id_fkey FOREIGN KEY (zhanr_id) REFERENCES project.zhanrovi(zhanr_id)
);


-- project.izbira_raspolozenie definition

-- Drop table

-- DROP TABLE project.izbira_raspolozenie;

CREATE TABLE project.izbira_raspolozenie (
        korisnik_id int8 NOT NULL,
        raspolozenie_id int8 NOT NULL,
        datum_izbor date NOT NULL,
        CONSTRAINT izbira_raspolozenie_pkey PRIMARY KEY (korisnik_id, raspolozenie_id),
        CONSTRAINT izbira_raspolozenie_korisnik_id_fkey FOREIGN KEY (korisnik_id) REFERENCES project.korisnici(korisnik_id),
        CONSTRAINT izbira_raspolozenie_raspolozenie_id_fkey FOREIGN KEY (raspolozenie_id) REFERENCES project.raspolozenija(raspolozenie_id)
);


-- project.napisana_od definition

-- Drop table

-- DROP TABLE project.napisana_od;

CREATE TABLE project.napisana_od (
        kniga_id int8 NOT NULL,
        avtor_id int8 NOT NULL,
        CONSTRAINT napisana_od_pkey PRIMARY KEY (kniga_id, avtor_id),
        CONSTRAINT napisana_od_avtor_id_fkey FOREIGN KEY (avtor_id) REFERENCES project.avtori(avtor_id),
        CONSTRAINT napisana_od_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id)
);


-- project.naracki definition

-- Drop table

-- DROP TABLE project.naracki;

CREATE TABLE project.naracki (
        naracka_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        datum timestamp NOT NULL,
        status varchar(30) NOT NULL,
        vkupna_cena numeric(10, 2) NOT NULL,
        korisnik_id int8 NOT NULL,
        CONSTRAINT naracki_pkey PRIMARY KEY (naracka_id),
        CONSTRAINT naracki_korisnik_id_fkey FOREIGN KEY (korisnik_id) REFERENCES project.korisnici(korisnik_id)
);
CREATE INDEX idx_naracki_status ON project.naracki USING btree (status);

-- Table Triggers

create trigger trg_proveri_i_namali_zaliha before
update
    of status on
    project.naracki for each row execute function project.proveri_i_namali_zaliha();


-- project.omileni definition

-- Drop table

-- DROP TABLE project.omileni;

CREATE TABLE project.omileni (
        korisnik_id int8 NOT NULL,
        kniga_id int8 NOT NULL,
        datum_dodavanje date NOT NULL,
        CONSTRAINT omileni_pkey PRIMARY KEY (korisnik_id, kniga_id),
        CONSTRAINT omileni_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id),
        CONSTRAINT omileni_korisnik_id_fkey FOREIGN KEY (korisnik_id) REFERENCES project.korisnici(korisnik_id)
);


-- project.plakanja definition

-- Drop table

-- DROP TABLE project.plakanja;

CREATE TABLE project.plakanja (
        plakjanje_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        datum timestamp NOT NULL,
        iznos numeric(10, 2) NOT NULL,
        nacin_na_plakjanje varchar(50) NOT NULL,
        status varchar(30) NOT NULL,
        naracka_id int8 NOT NULL,
        CONSTRAINT plakanja_naracka_id_key UNIQUE (naracka_id),
        CONSTRAINT plakanja_pkey PRIMARY KEY (plakjanje_id),
        CONSTRAINT plakanja_naracka_id_fkey FOREIGN KEY (naracka_id) REFERENCES project.naracki(naracka_id)
);


-- project.povrzana_so definition

-- Drop table

-- DROP TABLE project.povrzana_so;

CREATE TABLE project.povrzana_so (
        kniga_id int8 NOT NULL,
        raspolozenie_id int8 NOT NULL,
        CONSTRAINT povrzana_so_pkey PRIMARY KEY (kniga_id, raspolozenie_id),
        CONSTRAINT povrzana_so_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id),
        CONSTRAINT povrzana_so_raspolozenie_id_fkey FOREIGN KEY (raspolozenie_id) REFERENCES project.raspolozenija(raspolozenie_id)
);


-- project.preferira_kategorija definition

-- Drop table

-- DROP TABLE project.preferira_kategorija;

CREATE TABLE project.preferira_kategorija (
        korisnik_id int8 NOT NULL,
        kategorija_id int8 NOT NULL,
        datum_dodavanje date NOT NULL,
        CONSTRAINT preferira_kategorija_pkey PRIMARY KEY (korisnik_id, kategorija_id),
        CONSTRAINT preferira_kategorija_kategorija_id_fkey FOREIGN KEY (kategorija_id) REFERENCES project.kategorii(kategorija_id),
        CONSTRAINT preferira_kategorija_korisnik_id_fkey FOREIGN KEY (korisnik_id) REFERENCES project.korisnici(korisnik_id)
);


-- project.preferira_zhanr definition

-- Drop table

-- DROP TABLE project.preferira_zhanr;

CREATE TABLE project.preferira_zhanr (
        korisnik_id int8 NOT NULL,
        zhanr_id int8 NOT NULL,
        datum_dodavanje date NOT NULL,
        CONSTRAINT preferira_zhanr_pkey PRIMARY KEY (korisnik_id, zhanr_id),
        CONSTRAINT preferira_zhanr_korisnik_id_fkey FOREIGN KEY (korisnik_id) REFERENCES project.korisnici(korisnik_id),
        CONSTRAINT preferira_zhanr_zhanr_id_fkey FOREIGN KEY (zhanr_id) REFERENCES project.zhanrovi(zhanr_id)
);


-- project.sodrzi definition

-- Drop table

-- DROP TABLE project.sodrzi;

CREATE TABLE project.sodrzi (
        naracka_id int8 NOT NULL,
        kniga_id int8 NOT NULL,
        kolicina int4 NOT NULL,
        edinechna_cena numeric(10, 2) NOT NULL,
        CONSTRAINT sodrzi_pkey PRIMARY KEY (naracka_id, kniga_id),
        CONSTRAINT sodrzi_kniga_id_fkey FOREIGN KEY (kniga_id) REFERENCES project.knigi(kniga_id),
        CONSTRAINT sodrzi_naracka_id_fkey FOREIGN KEY (naracka_id) REFERENCES project.naracki(naracka_id)
);