set search_path = project;

drop table if exists se_naogja2;
drop table if exists se_naogja1;
drop table if exists ja_ima;
drop table if exists so_koi;
drop table if exists opsluzuva;
drop table if exists od_kade;
drop table if exists sodrzi;
drop table if exists pripagja;
drop table if exists zadolzen_za;

drop table if exists komentari;
drop table if exists naracki;
drop table if exists sliki;
drop table if exists posetiteli;
drop table if exists vraboteni;
drop table if exists korisnici;
drop table if exists restorani;
drop table if exists lokacii;
drop table if exists prodavnici;
drop table if exists recepti;
drop table if exists potekla;
drop table if exists sostojki;
drop table if exists kategorii;

drop schema if exists project;

create schema project;

set search_path = project;

create table kategorii(
	kat_id integer,
	kat_ime varchar(400) unique not null,
	opis varchar(2000),
	
	constraint pk_kategorii primary key (kat_id)
);

create table sostojki(
	s_id integer,
	s_naziv varchar(400) unique not null,
	
	constraint pk_sostojki primary key(s_id)
	);
	
create table potekla(
	pot_id integer,
	pot_naziv varchar(400) unique not null,
	
	constraint pk_potekla primary key(pot_id)
);
--added rec_ime
create table recepti(
	rec_id bigint,
	postapka varchar(4000),
	rec_ime varchar(200),
	
	constraint pk_recepti primary key (rec_id)
);

create table prodavnici(
	pro_id integer,
	pro_naziv varchar(200),
	
	constraint pk_prodavnici primary key (pro_id)
);

create table lokacii(
	l_id integer,
	adresa varchar(300) not null,
	
	constraint pk_lokacii primary key (l_id)
);
	
create table restorani(
	res_id integer,
	res_naziv varchar(200),
	
	constraint pk_restorani primary key (res_id)
);

create table korisnici(
	telefon char(9),
	kor_ime varchar(200) not null,
	prezime varchar(400),
	
	constraint pk_korisnici primary key (telefon)
);

create table vraboteni(
	telefon char(9),
	
	constraint pk_vraboteni primary key (telefon),
	constraint fk_vraboteni foreign key (telefon) references korisnici(telefon)
);

create table posetiteli(
	telefon char(9),
	
	constraint pk_posetiteli primary key (telefon),
	constraint fk_posetiteli foreign key (telefon) references korisnici(telefon)
);

create table sliki(
	reden_broj integer,
	rec_id bigint,
	slika bytea not null,
	
	constraint pk_sliki primary key (reden_broj, rec_id),
	constraint fk_sliki foreign key (rec_id) references recepti(rec_id)
);

--vreme, n_data -> vreme
create table naracki(
	vreme timestamp,
	telefon char(9),
	
	constraint pk_naracki primary key (vreme, telefon),
	constraint fk_naracki foreign key (telefon) references posetiteli(telefon)
);

create table komentari(
	kom_data timestamp,
	telefon char(9),
	rec_id bigint,
	ocena smallint not null,
	text varchar(2000),
	
	constraint pk_komentari primary key(kom_data, telefon, rec_id),
	constraint fk_komentari_telefon foreign key (telefon) references posetiteli(telefon),
	constraint fk_komentari_rec_id foreign key (rec_id) references recepti(rec_id),
	constraint in_range_ocena check(ocena >= 1 and ocena <= 10)
	);

--relacii

create table zadolzen_za(
	v_telefon char(9),
	vreme timestamp,
	p_telefon char(9),
	
	constraint pk_zadolzen_za primary key (v_telefon, vreme, p_telefon),
	constraint fk_zadolzen_za_v_telefon foreign key (v_telefon) references vraboteni(telefon),
	constraint fk_zadlozen_za_n_vreme foreign key (vreme, p_telefon) references naracki(vreme, telefon)
	);

create table pripagja(
	kat_id integer,
	rec_id bigint,
	constraint pk_pripagja primary key (kat_id, rec_id),
	constraint fk_pripagja_kat foreign key (kat_id) references kategorii(kat_id),
	constraint fk_pripagja_rec foreign key (rec_id) references recepti(rec_id)
);

create table sodrzi(
	s_id integer,
	rec_id bigint,
	constraint pk_sodrzi primary key (s_id, rec_id),
	constraint fk_sodrzi_s_id foreign key (s_id) references sostojki(s_id),
	constraint fk_sodrzi_rec_id foreign key (rec_id) references recepti(rec_id)
);


create table od_kade(
	pot_id integer,
	rec_id bigint,
	constraint pk_od_kade primary key (pot_id, rec_id),
	constraint fk_od_kade_pot_id foreign key (pot_id) references potekla(pot_id),
	constraint fk_od_kade_rec_id foreign key (rec_id) references recepti(rec_id)
);


create table opsluzuva(
	        res_id integer,
	        rec_id bigint,
	        constraint pk_opsluzuva primary key (res_id, rec_id),
	        constraint rec_id_not_unique unique (rec_id),
	        constraint fk_opsluzuva_res_id foreign key (res_id) references restorani(res_id),
	        constraint fk_opsluzuva_rec_id foreign key (rec_id) references recepti(rec_id)
	);

create table so_koi(
	vreme timestamp,
	telefon char(9),
	rec_id bigint,
	nacin varchar(20),
	constraint pk_so_koi primary key (vreme, telefon, rec_id),
	constraint fk_so_koi_naracki foreign key (vreme, telefon) references naracki(vreme, telefon),
	constraint fk_opsluzuva_rec_id foreign key (rec_id) references recepti(rec_id)
);

create table ja_ima(
	s_id integer,
	pro_id integer,
	kolicina varchar(50),
	cena integer,
	constraint pk_ja_ima primary key (s_id, pro_id),
	constraint fk_ja_ima_sostojki foreign key (s_id) references sostojki(s_id),
	constraint fk_ja_ima_pro foreign key (pro_id) references prodavnici(pro_id)
);

create table se_naogja1(
	res_id integer,
	l_id integer,
	constraint pk_se_naogja1 primary key (res_id, l_id),
	constraint fk_se_naogja1_l_id foreign key (l_id) references lokacii(l_id),
	constraint fk_se_naogja1_res_id foreign key (res_id) references restorani(res_id)
);

create table se_naogja2(
	pro_id integer,
	l_id integer,
	constraint pk_se_naogja2 primary key (pro_id, l_id),
	constraint fk_se_naogja2_l_id foreign key (l_id) references lokacii(l_id),
	constraint fk_se_naogja2_pro_id foreign key (pro_id) references prodavnici(pro_id)
);






