drop table if exists Service;
drop table if exists Payment;
drop table if exists Reservation;
drop table if exists Room;
drop table if exists Staff;
drop table if exists Customer;
drop table if exists Manager;
drop table if exists Hotel_Building;
drop table if exists Hotel_User;


create table Hotel_User(
	user_id bigint primary key,
	first_name varchar(30) not null,
	last_name varchar(30) not null,
	phone varchar(9) not null,
	email varchar(100) not null unique,
	password varchar(100) not null
);


create table Staff(
	user_id bigint primary key,
	constraint staff_fk foreign key (user_id) references Hotel_User(user_id) on delete cascade
);


create table Customer(
	user_id bigint primary key,
	constraint customer_fk foreign key (user_id) references Hotel_User(user_id) on delete cascade
);


create table Manager(
	user_id bigint primary key,
	constraint manager_fk foreign key (user_id) references Hotel_User(user_id) on delete cascade
);

create table Hotel_Building(
	building_id varchar(10) primary key,
	address varchar(50) not null,
	city varchar(20) not null,
	floor integer not null,
	num_rooms integer not null
);


create table Room(
	room_number integer,
	room_type varchar(20) not null,
	number_of_beds integer not null,
	price_per_night bigint not null,
	available boolean not null,
	building_id varchar(10),
	constraint room_pkey primary key (room_number, building_id),
	constraint room_fk foreign key (building_id) references Hotel_Building(building_id)
);

create table Reservation(
	reservation_id bigint primary key,
	start_date date not null,
	end_date date,
	status varchar(40) not null,
	reserv_date date not null,
	room_number integer,
	building_id varchar(10),
	customer_id bigint,
	manager_id bigint,
	constraint reservation_fk_room foreign key(room_number, building_id) references Room(room_number, building_id),
	constraint reservation_fk_customer foreign key(customer_id) references Customer(user_id),
	constraint reservation_fk_manager foreign key(manager_id) references Manager(user_id)
);

create table Payment(
	p_id bigint primary key,
	p_method varchar(5) not null,
	amount integer not null,
	p_date date not null,
	customer_id bigint,
	reservation_id bigint,
	constraint payment_fk_reservation foreign key (reservation_id) references Reservation(reservation_id),
	constraint payment_fk foreign key (customer_id) references Customer(user_id)
);


create table Service(
	service_id bigint primary key,
	service_type varchar(40) not null,
	service_date date,
	service_status varchar(70) not null,
	room_number integer,
	building_id varchar(10),
	staff_id bigint,
	CONSTRAINT service_fk_room FOREIGN KEY (room_number, building_id) REFERENCES Room(room_number, building_id),
    CONSTRAINT service_fk_staff FOREIGN KEY (staff_id) REFERENCES Staff(user_id)
);
