-- Create the UserProfile table
CREATE TABLE UserProfile (
    UserID INTEGER NOT NULL PRIMARY KEY, -- Setting UserID as the primary key
    FullName VARCHAR(30) NOT NULL,       -- FullName with a max length of 30 characters
    Email VARCHAR(50) NOT NULL,          -- Email with a max length of 50 characters
    MobileNumber VARCHAR(15) NOT NULL,   -- MobileNumber with variable length
    Password VARCHAR(30) NOT NULL        -- Password with a max length of 30 characters
);

-- Insert user data into UserProfile table
INSERT INTO UserProfile VALUES
(1, 'Praveena', '[email protected]', '1234567890', 'praveena@123'),
(2, 'Praneeth', '[email protected]', '9876543210', 'praneeth_61'),
(3, 'Chinnu', '[email protected]', '5556667777', 'chinnu@98'),
(4, 'Pravalika', '[email protected]', '4445556666', 'wonderwoman'),
(5, 'Hemanth', '[email protected]', '7778889999', 'hemanth44');

SELECT * FROM UserProfile;

-- Create the reservations table
CREATE TABLE reservations (
    reservation_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Auto-incrementing primary key
    user_id INTEGER NOT NULL,                                   -- Foreign key referencing UserProfile
    check_in_date DATE NOT NULL,                                -- Check-in date
    check_out_date DATE NOT NULL,                               -- Check-out date
    room_number VARCHAR(10) NOT NULL,                          -- Room number
    status ENUM('approved', 'cancelled') NOT NULL,              -- Status (approved or canceled)
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES UserProfile(UserID) -- Foreign key constraint
);

-- Insert 5 reservation entries into the reservations table
INSERT INTO reservations (user_id, check_in_date, check_out_date, room_number, status) 
VALUES 
(1, '2024-12-05', '2024-12-10', '401', 'approved'),
(2, '2024-12-06', '2024-12-08', '402', 'approved'),
(3, '2024-12-10', '2024-12-15', '403', 'cancelled'),
(4, '2024-12-07', '2024-12-12', '404', 'approved'),
(5, '2024-12-09', '2024-12-13', '405', 'cancelled');

SELECT * FROM reservations;

-- Create the bills table
CREATE TABLE bills (
    bill_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,      -- Auto-incrementing primary key
    reservation_id INTEGER NOT NULL,                           -- Foreign key referencing reservations
    total_amount DECIMAL(10, 2) NOT NULL,                      -- Total amount for the bill
    additional_charges DECIMAL(10, 2) DEFAULT 0.00,            -- Additional charges (default to 0 if not provided)
    payment_status ENUM('paid', 'pending', 'failed') NOT NULL, -- Payment status (paid, pending, or failed)
    CONSTRAINT fk_reservation FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) -- Foreign key constraint
);

-- Insert billing information into the bills table
INSERT INTO bills (reservation_id, total_amount, additional_charges, payment_status)
VALUES 
(1, 500.00, 20.00, 'paid'),
(2, 300.00, 15.00, 'pending'),
(3, 450.00, 25.00, 'paid'),
(4, 600.00, 30.00, 'failed'),
(5, 400.00, 10.00, 'paid');

SELECT * FROM bills;

-- Retrieve all reservations done from the last 10 days
SELECT * 
FROM reservations
WHERE check_in_date >= CURDATE() - INTERVAL 10 DAY;

-- Delete the details of guests who have canceled the bookings
-- First, delete from the bills table to remove the dependent records
DELETE FROM bills
WHERE reservation_id IN (
    SELECT reservation_id
    FROM reservations
    WHERE status = 'canceled'
);

-- Now delete the canceled reservations
DELETE FROM reservations
WHERE status = 'canceled';

-- Retrieve previous reservations of users whose reservation is approved
SELECT * 
FROM reservations
WHERE user_id IN (
    SELECT user_id
    FROM reservations
    WHERE status = 'approved'
) AND check_in_date < CURDATE();

-- Create the User table
CREATE TABLE User (
    CustomerId INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- CustomerId as the primary key
    CustomerName VARCHAR(50) NOT NULL,                      -- Customer's Name
    BookingDate DATE NOT NULL,                               -- Booking Date
    CheckInDate DATE NOT NULL,                               -- Check-in Date
    CheckOutDate DATE NOT NULL,                              -- Check-out Date
    PackageID INTEGER NOT NULL,                              -- Package ID
    AmountPaid DECIMAL(10, 2) NOT NULL                       -- Amount Paid
);

-- Insert 5 rows of data into the User table
INSERT INTO User (CustomerName, BookingDate, CheckInDate, CheckOutDate, PackageID, AmountPaid) 
VALUES 
('Praveena', '2024-11-01', '2024-12-05', '2024-12-10', 101, 500.00),
('Praneeth', '2024-11-02', '2024-12-06', '2024-12-09', 102, 300.00),
('Chinnu', '2024-11-05', '2024-12-10', '2024-12-15', 103, 600.00),
('Pravalika', '2024-11-10', '2024-12-07', '2024-12-12', 104, 700.00),
('Hemanth', '2024-11-12', '2024-12-08', '2024-12-13', 105, 450.00);

-- SQL query to retrieve user details where CustomerName ends with 'a' and sort by CustomerName in descending order
SELECT 
    CustomerId, 
    CustomerName, 
    BookingDate, 
    CheckInDate, 
    CheckOutDate, 
    PackageID, 
    AmountPaid
FROM User
WHERE CustomerName LIKE '%a'  -- Customer name ends with 'a'
ORDER BY CustomerName DESC;  -- Sort by CustomerName in descending order

-- Create the RoomStatus table
CREATE TABLE RoomStatus (
    CustomerID BIGINT NOT NULL PRIMARY KEY,     -- CustomerID as primary key (13-digit number)
    CustomerName VARCHAR(50) NOT NULL,          -- CustomerName with a max length of 50 characters
    Status ENUM('Vacant', 'Occupied') NOT NULL, -- Status (Vacant or Occupied)
    DateAvailability DATE NOT NULL,            -- Date when the room is available
    Price DECIMAL(10, 2) NOT NULL              -- Price of the room (suite, single room, double room)
);

-- Insert customer data into the RoomStatus table
INSERT INTO RoomStatus (CustomerID, CustomerName, Status, DateAvailability, Price) VALUES
(1234567890123, 'Praveena', 'Occupied', '2024-12-05', 150.00),  -- Example for an occupied room
(2345678901234, 'Praneeth', 'Vacant', '2024-12-06', 100.00),        -- Example for a vacant room
(3456789012345, 'Chinnu', 'Occupied', '2024-12-07', 200.00),  -- Example for an occupied room
(4567890123456, 'Pravalika', 'Vacant', '2024-12-08', 180.00),     -- Example for a vacant room
(5678901234567, 'Hemanth', 'Occupied', '2024-12-09', 220.00);     -- Example for an occupied room

Select* from RoomStatus;

-- Create the Complaint table
CREATE TABLE Complaint (
    ComplaintID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- ComplaintID as the primary key, auto-incremented
    CustomerID BIGINT NOT NULL,                                -- CustomerID (13-digit number) referencing the customer
    CustomerContactNumber VARCHAR(15) NOT NULL,                 -- Customer's Contact Number
    RoomNumber VARCHAR(10) NOT NULL,                           -- Room Number
    ComplaintType ENUM('Poor housekeeping', 'Noisy guests', 'Uncomfortable beds', 'Slow service', 'Lack of amenities', 'Unfriendly staff') NOT NULL, -- Type of complaint
    Feedback ENUM('great', 'good') NOT NULL,                   -- Feedback (great or good)
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES RoomStatus(CustomerID)  -- Foreign key constraint referencing the RoomStatus table
);

-- Insert 5 complaint records into the Complaint table
INSERT INTO Complaint (CustomerID, CustomerContactNumber, RoomNumber, ComplaintType, Feedback) VALUES
(1234567890123, '1234567890', '101', 'Poor housekeeping', 'great'),   -- Complaint 1
(2345678901234, '9876543210', '102', 'Noisy guests', 'good'),        -- Complaint 2
(3456789012345, '5556667777', '103', 'Uncomfortable beds', 'great'), -- Complaint 3
(4567890123456, '4445556666', '104', 'Slow service', 'good'),        -- Complaint 4
(5678901234567, '7778889999', '105', 'Lack of amenities', 'great');  -- Complaint 5

sELECT*FROM Complaint;

-- Alter the reservations table to add a column for Upcoming bookings date
ALTER TABLE reservations
ADD upcoming_booking_date DATE;

-- Update the `upcoming_booking_date` for existing reservations (Example)
UPDATE reservations
SET upcoming_booking_date = '2024-12-20'  -- Replace with appropriate date for upcoming bookings
WHERE reservation_id = 1;  -- Use appropriate condition to update specific rows

UPDATE reservations
SET upcoming_booking_date = '2024-12-21'  -- Replace with appropriate date
WHERE reservation_id = 2;

-- You can continue updating for other reservation_id's similarly.
select*from reservations;

-- Update the payment status for a particular customer ID in the bills table
UPDATE bills
SET payment_status = 'paid'  -- Change this to the appropriate status (e.g., 'paid', 'pending', 'failed')
WHERE reservation_id = 1;  -- Replace with the specific reservation_id or customer condition

-- Display the details of the customer after the payment status update
SELECT b.bill_id, b.reservation_id, b.total_amount, b.additional_charges, b.payment_status,
       r.user_id, u.FullName, r.check_in_date, r.check_out_date, r.room_number
FROM bills b
JOIN reservations r ON b.reservation_id = r.reservation_id
JOIN UserProfile u ON r.user_id = u.UserID
WHERE r.reservation_id = 1;  -- Use the same reservation_id or customer condition for filtering