-- Create a Reservation table................................................
CREATE TABLE RESERVATION_TABLE (
  Reservation_ID TEXT PRIMARY KEY,
  Customer_Name TEXT NOT NULL,
  Check_in_Date TEXT NOT NULL,
  Check_out_Date TEXT NOT NULL,
  Reservation_Status TEXT NOT NULL,
  Number_of_Guests INT NOT NULL,
  Special_Requests TEXT NOT NULL
);

-- Insert values to be added
INSERT INTO RESERVATION_TABLE VALUES ('R12345', 'John', 'june1', 'july1', 'confirmed', 5, 'no rice');
INSERT INTO RESERVATION_TABLE VALUES ('R23456', 'jose', 'june2', 'july2', 'confirmed', 4, 'more rice');
INSERT INTO RESERVATION_TABLE VALUES ('R34567', 'jason', 'june3', 'july3', 'confirmed', 9, 'extra sauce');
INSERT INTO RESERVATION_TABLE VALUES ('R45678', 'maynard', 'june4', 'july4', 'confirmed', 7, 'none');
INSERT INTO RESERVATION_TABLE VALUES ('R56789', 'edrial', 'june5', 'july5', 'confirmed', 4, 'none');
INSERT INTO RESERVATION_TABLE VALUES ('R67890', 'roldan', 'june6', 'july6', 'confirmed', 3, 'none');
INSERT INTO RESERVATION_TABLE VALUES ('R78901', 'daiz', 'june7', 'july7', 'confirmed', 7, 'without rice');
INSERT INTO RESERVATION_TABLE VALUES ('R78902', 'roxel', 'june8', 'july8', 'confirmed', 3, 'extra sugar');
INSERT INTO RESERVATION_TABLE VALUES ('R78903', 'densyo', 'june9', 'july9', 'confirmed', 2, 'none');
INSERT INTO RESERVATION_TABLE VALUES ('R78904', 'devans', 'june10', 'july10', 'confirmed', 5, 'none');

-- Fetch updated values
SELECT * FROM RESERVATION_TABLE;





-- Create the Room table.............................................................
CREATE TABLE Room (
    Room_ID TEXT PRIMARY KEY,
    Room_Number INT NOT NULL,
    Room_Type TEXT NOT NULL,
    Occupancy_Status INT NOT NULL,
    Rate_Per_Night INT NOT NULL,
    Cleaning_Status TEXT NOT NULL,
    Special_Request TEXT NOT NULL
);

-- Insert 10 example records
INSERT INTO Room VALUES ('R101', 101, 'Single', 0, 8999, 'clean', 'soft pillow');
INSERT INTO Room VALUES ('R102', 102, 'Double', 1, 12999, 'clean', 'soft pillow');
INSERT INTO Room VALUES ('R103', 103, 'Suite', 0, 19999, 'clean', 'none');
INSERT INTO Room VALUES ('R201', 201, 'Single', 1, 9999, 'clean', 'none');
INSERT INTO Room VALUES ('R202', 202, 'Double', 0, 14999, 'clean', 'none');
INSERT INTO Room VALUES ('R203', 203, 'Suite', 1, 21999, 'clean', 'soft pillow');
INSERT INTO Room VALUES ('R301', 301, 'Single', 1, 10999, 'clean', 'none');
INSERT INTO Room VALUES ('R302', 302, 'Double', 0, 15999, 'clean', 'none');
INSERT INTO Room VALUES ('R303', 303, 'Suite', 1, 22999, 'clean', 'none');
INSERT INTO Room VALUES ('R401', 401, 'Single', 0, 11999, 'clean', 'soft pillow');

-- Fetch updated values
SELECT * FROM Room;





-- Create the Guest table.....................................................
CREATE TABLE Guest (
    Guest_ID INT PRIMARY KEY,
    First_Name TEXT NOT NULL,
    Last_Name TEXT NOT NULL,
    Email TEXT NOT NULL,
    Phone_Number INT NOT NULL,
    Membership_Status TEXT NOT NULL,
    Special_Request TEXT NOT NULL
    
);

-- Insert 10 example records
INSERT INTO Guest VALUES (1, 'John', 'Doe', 'john.doe@example.com', 123-456-7890, 'VIP', 'none');
INSERT INTO Guest VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', 987-654-3210, 'regular', 'none');
INSERT INTO Guest VALUES (3, 'Robert', 'Johnson', 'robert.j@example.com', 555-123-4567, 'regular', 'drinks');
INSERT INTO Guest VALUES (4, 'Amanda', 'Taylor', 'amanda.taylor@example.com', 111-222-3333, 'VIP', 'none');
INSERT INTO Guest VALUES (5, 'Michael', 'Brown', 'michael.brown@example.com', 777-888-9999, 'regular', 'none');
INSERT INTO Guest VALUES (6, 'Emily', 'Clark', 'emily.clark@example.com', 444-555-6666, 'regular', 'drinks');
INSERT INTO Guest VALUES (7, 'Daniel', 'Lee', 'daniel.lee@example.com', 888-999-0000, 'VIP', 'drinks');
INSERT INTO Guest VALUES (8, 'Sophia', 'Wilson', 'sophia.w@example.com', 123-321-5555, 'regular', 'none');
INSERT INTO Guest VALUES (9, 'Ryan', 'Miller', 'ryan.m@example.com', 999-888-7777, 'VIP', 'none');
INSERT INTO Guest VALUES (10, 'Olivia', 'Jones', 'olivia.jones@example.com', 555-444-3333, 'regular', 'none');

-- Fetch updated values
SELECT * FROM Guest;




-- Create the Staff table.....................................................
CREATE TABLE Staff (
    Staff_ID INT PRIMARY KEY,
    First_Name TEXT NOT NULL,
    Last_Name TEXT NOT NULL,
    Email TEXT NOT NULL,
    Phone_Number INT NOT NULL,
    Position TEXT NOT NULL,
    Years_of_Experience INT NOT NULL
);

-- Insert 10 example records
INSERT INTO Staff VALUES (1, 'John', 'Doe', 'john.doe@example.com', 123-456-7890, 'Front Desk Clerk', 8);
INSERT INTO Staff VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', 987-654-3210, 'Housekeeping', 5);
INSERT INTO Staff VALUES (3, 'Robert', 'Johnson', 'robert.j@example.com', 555-123-4567, 'Manager', 30);
INSERT INTO Staff VALUES (4, 'Amanda', 'Taylor', 'amanda.taylor@example.com', 111-222-3333, 'Chef', 6);
INSERT INTO Staff VALUES (5, 'Michael', 'Brown', 'michael.brown@example.com', 777-888-9999, 'Concierge', 4);
INSERT INTO Staff VALUES (6, 'Emily', 'Clark', 'emily.clark@example.com', 444-555-6666, 'Maintenance', 4);
INSERT INTO Staff VALUES (7, 'Daniel', 'Lee', 'daniel.lee@example.com', 888-999-0000, 'Receptionist', 8);
INSERT INTO Staff VALUES (8, 'Sophia', 'Wilson', 'sophia.w@example.com', 123-321-5555, 'Security Officer
', 3);
INSERT INTO Staff VALUES (9, 'Ryan', 'Miller', 'ryan.m@example.com', 999-888-7777, 'Event Coordinator
', 6);
INSERT INTO Staff VALUES (10, 'Olivia', 'Jones', 'olivia.jones@example.com', 555-444-3333, 'IT Specialist
', 9);

-- Fetch updated values
SELECT * FROM Staff;



-- Create the Payment table.....................................................
CREATE TABLE Payment (
    Payment_ID INT PRIMARY KEY,
    Reservation_ID INT NOT NULL,
    Guest_ID INT NOT NULL,
    Payment_Method TEXT NOT NULL,
    Amount INT NOT NULL,
    Payment_Date TEXT NOT NULL,
    Status TEXT DEFAULT 'Pending'
);

-- Insert 10 example records
INSERT INTO Payment VALUES (1, 101, 201, 'cash', 110, '2024-02-01', 'successful');
INSERT INTO Payment VALUES (2, 102, 202, 'credit', 200, '2024-02-03', 'pending');
INSERT INTO Payment VALUES (3, 103, 203, 'debit', 230, '2024-02-05', 'failed');
INSERT INTO Payment VALUES (4, 104, 204, 'cash', 220, '2024-02-07', 'successful');
INSERT INTO Payment VALUES (5, 105, 205, 'cash', 170, '2024-02-10', 'pending');
INSERT INTO Payment VALUES (6, 106, 206, 'cash', 160, '2024-02-12', 'pending');
INSERT INTO Payment VALUES (7, 107, 207, 'credit', 160, '2024-02-15', 'failed');
INSERT INTO Payment VALUES (8, 108, 208, 'cash', 150, '2024-02-18', 'failed');
INSERT INTO Payment VALUES (9, 109, 209, 'cash', 140, '2024-02-20', 'successful');
INSERT INTO Payment VALUES (10, 110, 210, 'debit', 180, '2024-02-22', 'successful');

-- Fetch updated values
SELECT * FROM Payment;