DDL: kreiranje.sql

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)
);