Final Project 2

SET search_path = dadade;
--By David Adade and Mirjam Brielmaier
--in following we have a hotel with guests, employees, rooms, amenities and appointments
--DDL
CREATE TABLE room (
    id serial PRIMARY KEY,
    name text NOT NULL CHECK (LENGTH (name)>0),
    smoke_free BOOLEAN NOT NULL,
    wifi BOOLEAN NOT NULL
);

COMMENT ON TABLE room IS 'All rooms in the hotel';
COMMENT ON COLUMN room.id IS 'The room id';
COMMENT ON COLUMN room.name IS 'The hotel rooms name';
COMMENT ON COLUMN room.smoke_free IS 'Some hotel rooms are smoke free while others are not';
COMMENT ON COLUMN room.wifi IS 'Some hotel rooms have wifi while others do not';


CREATE TABLE employee (
    id serial PRIMARY KEY,
    name text NOT NULL CHECK (LENGTH(name)>0),
    hiring_date DATE NOT NULL CHECK (hiring_date BETWEEN '1900-01-01' AND CURRENT_DATE)
);

COMMENT ON TABLE employee IS 'A list of the employees currently working at the hotel';
COMMENT ON COLUMN employee.id IS 'The id of the employee working at the hotel';
COMMENT ON COLUMN employee.name IS 'The name of the employee of the hotel';
COMMENT ON COLUMN employee.hiring_date IS 'The date when the employee was hired';

CREATE TABLE guest (
    id serial PRIMARY KEY,
    name text NOT NULL CHECK(LENGTH (name) > 0),
    age SMALLINT NOT NULL CHECK(age > 0),
    address text NOT NULL CHECK (LENGTH (address) > 0),
    credit_card_number BIGINT NOT NULL CHECK (credit_card_number BETWEEN 1.000000000000000 AND 9999999999999999),
    employee_id INTEGER NOT NULL REFERENCES employee(id)
       ON DELETE cascade ON UPDATE cascade
);

COMMENT ON TABLE  guest IS 'The guest booking of the hotel with the following information listed';
COMMENT ON COLUMN guest.id IS 'The ID of the guest who is booking the hotel';
COMMENT ON COLUMN guest.name IS 'Name of the guest who is booking the hotel room';
COMMENT ON COLUMN guest.age IS 'Age of the guest who is booking the hotel room';
COMMENT ON COLUMN guest.address IS 'Address of the guest who is booking the hotel room';
COMMENT ON COLUMN guest.credit_card_number IS 'Credit Card Number from the guest who is booking the hotel room';
COMMENT ON COLUMN guest.employee_id IS 'Every guest has a representative employee who responds to the guests needs';

CREATE TABLE amenities(
    id serial PRIMARY KEY,
    name text NOT NULL CHECK(LENGTH (name) > 0),
    price money NOT NULL CHECK (price > '0'::money),
    min_age SMALLINT NOT NULL CHECK (min_age > 0)
);

COMMENT ON TABLE  dadade.amenities IS 'The amenities of the hotel the guests can use';
COMMENT ON COLUMN amenities.id IS 'The ID of the amenity in the hotel';
COMMENT ON COLUMN amenities.name IS 'The name of the amenity in the hotel ';
COMMENT ON COLUMN amenities.price IS 'The rental of the amenity being used';
COMMENT ON COLUMN amenities.min_age IS 'The minimum age required to be able to use the amenities in the hotel';

CREATE TABLE appointment(
    guest_id INTEGER NOT NULL REFERENCES guest(id)
       ON DELETE cascade ON UPDATE cascade,
    check_in_date DATE NOT NULL CHECK (check_in_date BETWEEN '1900-01-01' AND CURRENT_DATE),
    room_id INTEGER NOT NULL REFERENCES room(id)
       ON DELETE cascade ON UPDATE cascade,
    number_of_guests SMALLINT NOT NULL CHECK (number_of_guests > 0),
    extra INTEGER NULL REFERENCES amenities(id)
       ON DELETE cascade ON UPDATE cascade
);

COMMENT ON TABLE  appointment IS 'The appointment booked by the guest for the hotel allocating a room to the guest with optional amenity bookings';
COMMENT ON COLUMN appointment.guest_id IS 'The information of the guest booking';
COMMENT ON COLUMN appointment.check_in_date IS 'The check in date of the guest for the hotel booking';
COMMENT ON COLUMN appointment.room_id IS 'The room where the guest is staying';
COMMENT ON COLUMN appointment.number_of_guests IS 'How many guests are staying in the room booked';
COMMENT ON COLUMN appointment.extra IS 'Guest who booked the room can also choose to book the amenities';


--DML
INSERT INTO amenities (name, price, min_age) VALUES
    ('gym', '20.00', 12),
    ('pool', '5.00', 1),
    ('theatre room', '3.00', 6);

INSERT INTO employee (name, hiring_date) VALUES
    ('David', '2014-07-07'),
    ('Mirjam', '2013-08-03'),
    ('Greg', '2015-04-22');

INSERT INTO room (name, smoke_free, wifi) VALUES
    ('Ocean View', '1', '1'),
    ('Street View', TRUE, FALSE),
    ('Penthouse', 't', 'f');

INSERT INTO guest (name, age, address, credit_card_number, employee_id) VALUES
    ('Anna', 32, '372 Street View Thunder Bay ON', 3782473950028432, (SELECT employee.id FROM employee WHERE employee.name = 'David')),
    ('John', 48, '142 Balmoral Street Toronto ON', 3792749302947828, (SELECT employee.id FROM employee WHERE employee.name = 'David')),
    ('Lauren', 22, '282 Oliver Road Thunder Bay ON', 3407300472847346, (SELECT employee.id FROM employee WHERE employee.name = 'Mirjam'));

INSERT INTO appointment (guest_id, check_in_date, room_id, number_of_guests, extra) VALUES
    ((SELECT guest.id FROM guest WHERE guest.name = 'Anna'), '2015-10-07', (SELECT room.id FROM room WHERE room.name = 'Street View'), 3, (SELECT amenities.id FROM amenities WHERE amenities.name='gym')),
    ((SELECT guest.id FROM guest WHERE guest.name = 'John'), '2015-10-05', (SELECT room.id FROM room WHERE room.name = 'Ocean View'), 3, (SELECT amenities.id FROM amenities WHERE amenities.name='gym')),
    ((SELECT guest.id FROM guest WHERE guest.name = 'Lauren'), '2015-10-01', (SELECT room.id FROM room WHERE room.name = 'Penthouse'), 3, NULL);

DELETE FROM amenities WHERE name = 'theatre room';

UPDATE amenities SET price = 7.00 WHERE name = 'pool';

--TEST QUERIES
--WILL PASS
INSERT INTO amenities (name, price, min_age) VALUES
    ('lounge', 1.00, 1);
--WILL FAIL
INSERT INTO amenities (name, price, min_age) VALUES
    ('pool game', -2.00, 6); -- fails because the price is negative and it is supposed to be greater than 0

--WILL PASS
INSERT INTO room (name, smoke_free, wifi) VALUES
    ('suite', TRUE, TRUE);
--WILL FAIL
INSERT INTO room (name, smoke_free, wifi) VALUES
    ('', TRUE, FALSE); -- fails because room.name is an empty string and the length is supposed to be greater than 0

--WILL PASS
INSERT INTO employee (name, hiring_date) VALUES
    ('Michelle', '2014-12-07');
--WILL FAIL
INSERT INTO employee (name, hiring_date) VALUES
    ('Aldo', '2016-12-07'); --fails because hiring_date is in the future and it is supposed to be in the past or in the present


SELECT AVG(min_age)
FROM amenities;

SELECT MIN(price) AS minimum_price
FROM amenities;


SELECT avg(age)
FROM guest;

SELECT guest_id, SUM(number_of_guests) AS Total_Guest
FROM appointment
GROUP BY guest_id;