DDL: kreiranje.sql

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