drop table if exists Dorm_User;
drop table if exists Student;
drop table if exists Employee;
drop table if exists Payment;
drop table if exists Dorm_Document;
drop table if exists Block;
drop table if exists Room;
drop table if exists RoomRequest;
drop table if exists StudentTookRoom;

create table Dorm_User(
	u_id bigint primary key,
	phone_number varchar(10) not null,
	first_name varchar(30) not null,
	last_name varchar(50) not null,
	email varchar(1000) not null unique,
	pass varchar(15) not null
);

create table Student(
	u_id bigint primary key references Dorm_User(u_id),
	faculty_name varchar(255) not null, 
	year_of_studies integer not null,
	is_exempt boolean not null
);

create table Employee(
	u_id bigint primary key references Dorm_User(u_id)
);

create table Payment(
	p_id bigint primary key,
	amount integer not null,
	payment_date date not null,
	student_id bigint,
	payment_month varchar(15) not null,
	constraint payment_fk_kon_student foreign key (student_id) references Student(u_id)
);

create table Dorm_Document(
	d_id bigint primary key,
	d_comment varchar(1000),
	d_status varchar(10) not null,
	upload_date date not null,
	file_path varchar(1000) not null,
	emp_id bigint,
	student_id bigint,
	constraint Dorm_Document_fkey_kon_Employee foreign key(emp_id) references Employee(u_id),
	constraint Dorm_Document_fkey_kon_Student foreign key(student_id) references Student(u_id)
);

create table Block(
	block_id char(1) primary key,
	num_available_rooms integer not null
);


create table Room(
	room_number integer,
	block_id char(1),
	is_available boolean not null,
	capacity integer not null,
	constraint Room_pkey primary key(room_number, block_id),
	constraint Room_fk_kon_Block foreign key(block_id) references Block(block_id)
);

create table RoomRequest(
	status varchar(10) not null,
	roomate_email varchar(1000),
	room_number integer,
	block_id varchar(1),
	employee_id bigint,
	student_id bigint,
	constraint RoomRequest_pkey primary key(room_number, block_id, student_id),
	constraint student_id foreign key(student_id) references Student(u_id),
	constraint StudentTookRoom_fkey_kon_Room foreign key(room_number, block_id) references Room(room_number, block_id),
	constraint employee_id foreign key(employee_id) references Employee(u_id)
);

create table StudentTookRoom(
	start_date date not null,
	end_date date,
	student_id bigint,
	room_num integer, 
	block_id char(1),
	constraint StudentTookRoom_pkey primary key(student_id, room_num, block_id),
	constraint StudentTookRoom_fkey_kon_Student foreign key(student_id) references Student(u_id),
	constraint StudentTookRoom_fkey_kon_Room foreign key(room_num, block_id) references Room(room_number, block_id)
);









