Index: schema.sql
===================================================================
--- schema.sql	(revision e6c1c769343b065e0fa02b5bcfd8d83e2c365d8e)
+++ schema.sql	(revision e6c1c769343b065e0fa02b5bcfd8d83e2c365d8e)
@@ -0,0 +1,280 @@
+-- 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)
+);