-- 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)
);