drop schema if exists project cascade;
create schema project;

drop table if exists REZERVACIJA_USLUGI;
drop table if exists VRABOTENI_USLUGI;
drop table if exists TERMIN;
drop table if exists REZERVACIJA;
drop table if exists BEAUTYCENTER;
drop table if exists UPLATA;
drop table if exists OCENA;
drop table if exists USLUGI;
drop table if exists KLIENTI;
drop table if exists VRABOTENI;
drop table if exists USERS;


create table USERS(
	User_ID serial primary key,
	Username varchar(100) not null unique,
	UPassword varchar(100) not null
);

create table KLIENTI(
	User_ID integer primary key,
	Ime varchar(100) not null,
	Prezime varchar(100),
	Tel_br varchar(12) not null,
	E_mail varchar(150),

	constraint fk_User_ID_klient foreign key (User_ID) references users(User_ID)
);

create table BEAUTYCENTER(
	Salon_ID varchar(100) primary key,
	Adresa varchar(100) not null,
	Tel_br varchar(12) not null,
	E_mail varchar(100)
);

create table VRABOTENI(
	User_ID integer primary key,
	First_name varchar(100) not null,
	Middle_name varchar(100),
 	Prezime varchar(100),
 	E_mail varchar(100) not null,
 	Tel_br varchar(12),
 	Rab_iskustvo integer,
 	Salon_ID varchar(100) not null,
	raboti_od timestamp not null,
	raboti_do timestamp,

	constraint fk_Salon_ID foreign key (Salon_ID) references beautycenter(Salon_ID)	,
    constraint fk_User_ID_vraboten foreign key (User_ID) references users(User_ID)
);

create table UPLATA(
	 Uplata_ID serial primary key,
	 Informacii varchar(100)	 
);
create table TERMINI(
	Termin_ID serial primary key, 
	Pocetok timestamp not null,
	Vremetraenje integer,
	User_ID_vraboten integer not null,
  
  constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID)
);

create table REZERVACIJA(
	Br_rez serial primary key,
	User_ID_klient integer not null,
	Termin_ID integer not null,
	Uplata_ID integer,
	User_ID_vraboten integer,
	
	  
	constraint fk_User_ID_klient foreign key (User_ID_klient) references klienti(User_ID),
	constraint fk_Termin_ID foreign key (Termin_ID) references termini(Termin_ID),
	constraint fk_Uplata_ID foreign key (Uplata_ID) references uplata(Uplata_ID),
	constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID)
	  
);

create table USLUGI(
	  Usluga_ID varchar(100) primary key,
	  Dejnost varchar(100),
	  Cena varchar(100) not null
);

create table OCENA(
	Ocena_ID serial primary key,
	Vrednost integer not null,
	Komentar varchar(200),
	User_ID_klient integer not null,
	Usluga_ID varchar(100) not null,
	br_rez integer, 
	

	constraint fk_User_ID_klient foreign key (User_ID_klient) references klienti(User_ID),
	constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID),
	constraint fk_Br_rez foreign key (br_rez) references rezervacija(br_rez)
);

create table REZERVACIJA_USLUGI(
	Br_rez integer not null, 
	Usluga_ID varchar(100) not null,

	constraint pk_rezervacija_uslugi primary key (Br_rez, Usluga_ID),
	constraint fk_Br_rez foreign key (br_rez) references rezervacija(br_rez),
	constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID)
);

create table VRABOTENI_USLUGI(
	User_ID_vraboten integer not null,
	Usluga_ID varchar(100) not null,

	constraint pk_vraboteni_uslugi primary key (User_ID_vraboten, Usluga_ID),
	constraint fk_User_ID_vraboten foreign key (User_ID_vraboten) references vraboteni(User_ID),
	constraint fk_Usluga_ID foreign key (Usluga_ID) references uslugi(Usluga_ID)
);



