create table korisnici(
	user_id integer primary key,
	user_ime varchar(50) not null,
	user_prezime varchar(50) not null,
	user_password varchar(300) not null,
	user_admin_id integer not null
	--treba fk da se dodade do sistemski_admin so alter table
);
create table sistemski_admin(
	user_id integer primary key,
	constraint fk_user foreign key (user_id) references korisnici (user_id)
	on delete cascade on update cascade
);

alter table korisnici add constraint fk_korisnik_administriran foreign key (user_admin_id) references sistemski_admin(user_id)
on delete cascade on update cascade;


create table kupuvaci(
	user_id integer primary key,
	kupuvac_tel_broj varchar(50) not null,
	constraint fk_user foreign key (user_id) references korisnici (user_id)
	on delete cascade on update cascade
);

create table dostavuvaci(
	user_id integer primary key,
	dostavuvac_vraboten_od date not null,
	constraint fk_user foreign key (user_id) references korisnici (user_id)
	on delete cascade on update cascade
);

create table menadzeri(
	user_id integer primary key,
	menadzer_vraboten_od date not null,
	constraint fk_user foreign key (user_id) references korisnici (user_id)
	on delete cascade on update cascade
);

create table restorani(
	restoran_id integer primary key,
	restoran_ime varchar(50) not null,
	restoran_rejting integer not null,
	restoran_adresa varchar(50) not NULL,
	user_id integer NOT null,
	constraint fk_restoran_menadzer foreign key(user_id) references menadzeri(user_id) on delete cascade on update cascade  
);

create table tip(
	tip_id integer primary key,
	tip_ime varchar(50) not null
);

create table meni(
	meni_id integer primary key,
	tip_id integer not null,
	restoran_id integer not null,
	constraint fk_meni_tip foreign key (tip_id) references tip(tip_id) on delete cascade on update cascade,
	constraint fk_meni_restoran foreign key (restoran_id) references restorani(restoran_id) on delete cascade on update cascade
);

create table obroci(
	obrok_id integer primary key,
	obrok_opis varchar(10000) not null,
	obrok_ime varchar(50) not null,
	meni_id integer not null,
	constraint fk_obrok_meni foreign key(meni_id) references meni(meni_id) on delete cascade on update cascade
);

create table rezervacii(
	rezervacija_id integer primary key,
	rezervacija_vreme timestamp not null,
	br_lugje integer not null,
	rezervacija_status varchar(50) not null,
	rezervacija_opis varchar(10000) not null,
	restoran_id integer not null,
	user_id integer not null,
	constraint fk_rezervacija_restoran foreign key(restoran_id) references restorani(restoran_id) on delete cascade on update cascade,
	constraint fk_rezervacija_kupuvac foreign key (user_id) references kupuvaci(user_id) on delete cascade on update cascade
);

create table naracki(
	naracka_id integer primary key,
	naracana_na timestamp not null, 
	naracka_status varchar(50) not null,
	naracka_cena_vkupna integer not null,
	user_id_kupuvac integer not null,
	user_id_dostavuvac integer not null,
	constraint fk_naracka_kupuvac foreign key (user_id_kupuvac) references kupuvaci(user_id) on delete cascade on update cascade,
	constraint fk_naracka_dostavuvac foreign key(user_id_dostavuvac) references dostavuvaci(user_id)on delete cascade on update cascade
);

create table restorani_tel_broevi(
	restoran_id integer not null,
	broj varchar(50),
	constraint pk_restorani_tel_broevi primary key (restoran_id,broj),
	constraint fk_restorani_tel_broevi_restoran foreign key(restoran_id) references restorani(restoran_id) on delete cascade on update cascade
);

create table cena(
	obrok_id integer not null,
	cena_id integer not null,
	cena_iznos integer not null,
	cena_vazi_od date not null,
	cena_vazi_do date not null,
	constraint pk_cena primary key (obrok_id,cena_id),
	constraint fk_cena_obrok foreign key (obrok_id) references obroci(obrok_id) on delete cascade on update cascade
);

create table rezervacija_rezervira_obrok(
	rezervacija_id integer not null,
	obrok_id integer not null,
	constraint pk_rezervacija_rezervira_obrok primary key(rezervacija_id,obrok_id),
	constraint fk_rezervacija_rezervira_obrok_rezervacija foreign key (rezervacija_id) references rezervacii(rezervacija_id) on delete cascade on update cascade,
	constraint fk_rezervacija_rezervira_obrok_obrok foreign key (obrok_id) references obroci(obrok_id) on delete cascade on update cascade	
);

create table naracka_se_sostoi_od_obrok(
	naracka_id integer not null,
	obrok_id integer not null,
	kolicina integer not null,
	cena integer not null,
	constraint pk_naracka_se_sostoi_od_obrok primary key (naracka_id,obrok_id),
	constraint fk_naracka_se_sostoi_od_obrok_naracka foreign key (naracka_id) references naracki(naracka_id) on delete cascade on update cascade,
	constraint fk_naracka_se_sostoi_od_obrok_obrok foreign key (obrok_id) references obroci(obrok_id) on delete cascade on update cascade
);
-- brisenje tabeli
drop table korisnici ;
drop table sistemski_admin ;
drop table kupuvaci ;
drop table dostavuvaci ;
drop table menadzeri ;
drop table restorani ;
drop table tip ;
drop table meni ;
drop table obroci ;
drop table rezervacii ;
drop table naracki ;
drop table restorani_tel_broevi ;
drop table cena ;
drop table rezervacija_rezervira_obrok ;
drop table naracka_se_sostoi_od_obrok ;
