--Villa(villa_id, villa_location, name, villa_view_type)
create table villa(
	villa_id integer primary key,
	villa_location varchar(255) not null,
	name varchar(255) not null,
	villa_view_type varchar(255) 
);



--Events(event_id, event_type, price, number_of_interested_guests)
create table events(
	event_id serial primary key,
	event_type varchar(255) not null,
	price float not null,
	number_of_interested_guests integer not null
);


--Activities(activity_id, activity_type, price, number_of_interested_guests, location)
create table activities(
	activity_id serial primary key,
	activity_type varchar(255) not null,
	price float not null,
	number_of_interested_guests integer not null,
	activity_location varchar(255) not null
);



--Rooms(room_id, room_type, price,availability, villa_id*)
create table rooms(
	room_id integer primary key,
	room_type varchar(255) not null,
	price integer not null,
	availability boolean not null,
	villa_id integer not null,
	constraint fk_rooms_villa foreign key(villa_id)
	references villa(villa_id)
);


--Beverage(beverage_id, beverage_type, price)
create table beverage(
	beverage_id integer primary key,
	beverage_type varchar(255) not null,
	price float not null
);

--Product(product_id, number_of_products, product_type)
create table product(
	product_id integer primary key, 
	number_of_products integer not null,
	product_type varchar(255) not null
);




--Meal(meal_id, price, meal_type)
create table meal(
	meal_id serial primary key,
	price float not null, 
	meal_type varchar(255) not null
);



--User_table(user_id, EMBG, address, email, username, password, full_name, phone_number)
create table user_table(
	user_id serial primary key,
	embg char(20) unique not null,
	address varchar(255) not null,
	email varchar(255) not null,
	password varchar(255) not null, 
	username varchar(255) not null,	
	full_name varchar(255) not null,
	phone_number varchar(255) not null
);


--Payment(payment_id, total_payment, pay_date, rec_id, customer_id, user_id*)
create table payment(
	payment_id serial primary key,
	total_payment float not null,
	pay_date date not null,
	rec_id varchar(255) not null,
	customer_id varchar(255) not null,
	user_id integer not null,
	constraint fk_payment_user foreign key (user_id)
	references user_table(user_id)
);


--Reservation(reservation_id, start_date, end_date, number_guests, adults, children, villa_id*, payment_id*, room_id*)
create table reservation(
	reservation_id serial primary key,
	start_date date not null,
	end_date date not null,
	number_guests integer not null,
	adults integer not null,
	children integer,
	villa_id integer not null,
	payment_id integer not null,
	room_id integer not null,	
	constraint fk_reservation_villa foreign key (villa_id)
	references villa(villa_id),
	constraint fk_reservation_payment foreign key (payment_id)
	references payment(payment_id),
	constraint fk_resrvation_room foreign key (room_id)
	references rooms(room_id)
);


--Log in(login_id, vreme_na_najava, user_id*)
create table log_in(
	login_id serial primary key,
	vreme_na_najava varchar(255) not null,
	user_id integer not null,
	constraint fk_login_user foreign key(user_id)
	references user_table(user_id)
);



--Service(service_id, service_type, price, user_id*, room_id*)
create table service(
	service_id serial primary key,
	service_type varchar(255) not null,
	price float not null,
	user_id integer not null,
	room_id integer not null,
	constraint fk_service_user foreign key(user_id)
	references user_table(user_id),
	constraint fk_service_rooms foreign key(room_id)
	references rooms(room_id)
);



--Waiter(user_id*, shift, salary)
create table waiter(
	user_id integer primary key,
	constraint fk_waiter_user foreign key(user_id)
		references user_table(user_id),
	shift integer not null,
	salary float not null
);


--Receptionist(user_id*, shift, salary)
create table receptionist(
	user_id integer primary key,
	constraint fk_receptionist_user foreign key(user_id)
		references user_table(user_id),
	shift integer not null,
	salary float not null
);


--Chef(user_id*, shift, salary)
create table chef(
	user_id integer primary key,
	constraint fk_chef_user foreign key(user_id)
		references user_table(user_id),
	shift integer not null,
	salary float not null
);


--Guests(user_id*, passport_number)
create table guests(
	user_id integer primary key,
	constraint fk_guests_user foreign key(user_id)
		references user_table(user_id),
	passport_number varchar(225) not null
);


--Prepared_Meal(prepared_meal_id, meal_id*,  user_id*)
create table prepared_meal(
	prepared_meal_id serial,
	meal_id integer not null,
	user_id integer not null,
	constraint fk_prepared_meal_meal foreign key(meal_id)
		references meal(meal_id),
	constraint fk_prepared_meal_user foreign key(user_id)
		references user_table(user_id),
	constraint pk_prepared_meal primary key(meal_id, prepared_meal_id)
);



--On_Site(payment_id*, currency, payment_type, user_id* )
create table on_site(
	payment_id integer primary key,
	constraint fk_onsite_payment foreign key(payment_id)
		references payment(payment_id),
	currency varchar(255) not null,
	payment_type varchar(255) not null,
	user_id integer not null,
   	constraint fk_onsite_user foreign key(user_id)
		references user_table(user_id)
);

--Online(payment_id*,  card_number)
create table online(
	payment_id integer primary key,
	constraint fk_online_payment foreign key(payment_id)
		references payment(payment_id),
	card_number varchar(255) unique not null
);


--reservation_for_events (reservation_id*, event_id*)
create table  reservation_for_events(
	reservation_id integer,
	event_id integer,
	constraint pk_reservation_for_events primary key(reservation_id, event_id),
	constraint fk_reservation_for_events_r foreign key(reservation_id)
		references reservation(reservation_id),
	constraint fk_reservation_for_events_e foreign key(event_id)
		references events(event_id)
);

--reservation_for_activity(reservation_id*, activity_id*)
create table reservation_for_activity(
	reservation_id integer,
	activity_id integer,
	constraint pk_reservation_for_activity primary key(reservation_id, activity_id),
	constraint fk_reservation_for_activity_r foreign key(reservation_id)
		references reservation(reservation_id),
	constraint fk_reservation_for_activity_a foreign key(activity_id)
		references activities(activity_id)
);



--reservation_for_prepared_meal(reservation_id*, (prepared_meal_id*, meal_id*), quantity) 
create table reservation_for_prepared_meal(
	reservation_id integer,
	prepared_meal_id integer,
	meal_id integer,
	quantity integer not null
	constraint ck_quantity_gt_0 check (quantity >= 0),
	constraint pk_reservation_for_prepared_meal primary key(reservation_id, prepared_meal_id, meal_id),
	constraint fk_reservation_for_prepared_meal_r foreign key(reservation_id)
		references reservation(reservation_id),
	constraint fk_reservation_for_prepared_meal_pm foreign key(prepared_meal_id, meal_id)
		references prepared_meal(prepared_meal_id, meal_id)
);

--service_for_beverages(service_id*, beverage_id*, quantity) 
create table service_for_beverages(
	service_id integer,
	beverage_id integer,
	quantity integer not null
	constraint ck_quantity_gt_0 check (quantity >= 0),
	constraint pk_service_for_beverages primary key(service_id, beverage_id),
	constraint fk_service_for_beverages_s foreign key(service_id)
		references service(service_id),
	constraint fk_service_for_beverages_b foreign key(beverage_id)
		references beverage(beverage_id)
);

--meal_madeof_product(meal_id*,product_id* )
create table meal_made_of_product(
	meal_id integer,
	product_id integer,
	constraint pk_meal_madeof_product primary key(meal_id, product_id),
	constraint fk_meal_madeof_product_m foreign key(meal_id)
		references meal(meal_id),
	constraint fk_meal_madeof_product_p foreign key(product_id)
		references product(product_id)
);

-- guests_make_reservation(user_id*,reservation_id* )
create table guests_make_reservation(
	user_id integer,
	reservation_id integer,
	constraint pk_guests_make_reservation primary key(user_id,reservation_id),
	constraint fk_guests_make_reservation_u foreign key(user_id)
		references user_table(user_id),
	constraint fk_guests_make_reservation_r foreign key(reservation_id)
		references reservation(reservation_id)
);