-- Create the Carrier table
CREATE TABLE Carrier (
    carrierID INT PRIMARY KEY,
    CarrierName VARCHAR(50),
    DiscountPercentageThirtyDaysAdvanceBooking INT,
    DiscountPercentageSixtyDaysAdvanceBooking INT,
    DiscountPercentageNinetyDaysAdvanceBooking INT,
    RefundPercentageForTicketCancellation2DaysBeforeTravelDate INT,
    RefundPercentageForTicketCancellation10DaysBeforeTravelDate INT,
    RefundPercentageForTicketCancellation20DaysBeforeTravelDate INT,
    BulkBookingDiscount INT,
    SilverUserDiscount INT,
    GoldUserDiscount INT,
    PlatinumUserDiscount INT
);

-- Insert data into the Carrier table
INSERT INTO Carrier (
    carrierID, CarrierName, DiscountPercentageThirtyDaysAdvanceBooking,
    DiscountPercentageSixtyDaysAdvanceBooking, DiscountPercentageNinetyDaysAdvanceBooking,
    RefundPercentageForTicketCancellation2DaysBeforeTravelDate, RefundPercentageForTicketCancellation10DaysBeforeTravelDate,
    RefundPercentageForTicketCancellation20DaysBeforeTravelDate, BulkBookingDiscount, SilverUserDiscount, GoldUserDiscount, PlatinumUserDiscount
) VALUES
    (1, 'Indigo', 10, 15, 20, 60, 80, 100, 25, 10, 20, 25),
    (2, 'JetAirways', 15, 20, 25, 50, 70, 90, 25, 15, 20, 25),
    (3, 'KingFisher', 5, 10, 15, 40, 75, 90, 20, 10, 15, 20),
    (4, 'AirIndia', 8, 15, 20, 50, 75, 90, 30, 15, 20, 25);

-- Select all records from the Carrier table to verify data
SELECT * FROM Carrier;

-- Create the Flight table
CREATE TABLE Flight (
    FlightID INT AUTO_INCREMENT PRIMARY KEY,
    CarrierID INT,
    Origin VARCHAR(50),
    Destination VARCHAR(50),
    Airfare INT,
    SeatCapacityEconomyClass INT CHECK (SeatCapacityEconomyClass >= 20),
    SeatCapacityBusinessClass INT CHECK (SeatCapacityBusinessClass >= 10),
    SeatCapacityExecutiveClass INT CHECK (SeatCapacityExecutiveClass >= 10),
    FOREIGN KEY (CarrierID) REFERENCES Carrier(carrierID)
);

-- Insert data into the Flight table with corrected CarrierID
INSERT INTO Flight (FlightID, CarrierID, Origin, Destination, AirFare, SeatCapacityEconomyClass, SeatCapacityBusinessClass, SeatCapacityExecutiveClass)
VALUES
    (123, 1, 'Origin1', 'Destination1', 200, 100, 50, 20),
    (124, 2, 'Origin2', 'Destination2', 300, 150, 70, 30),
    (125, 3, 'Origin3', 'Destination3', 400, 200, 100, 40),
    (126, 4, 'Origin4', 'Destination4', 250, 120, 60, 25);

-- Select all records from the Flight table to verify data
SELECT * FROM Flight;


-- Write SQL Query to get Carrier Name and total count of Flights that are tagged to each Carrier. Should display the result in the ascending order of flights count for each carrier
-- As an Admin, I should be able to search count of flights that are tagged to a each carrier

SELECT C.CarrierName, COUNT(F.FlightID) AS TotalFlights
FROM Carrier C
LEFT JOIN Flight F ON C.CarrierID = F.CarrierID
GROUP BY C.CarrierID, C.CarrierName
ORDER BY TotalFlights ASC;

-- Write SQL Query to get FligthID, Carrier Name, Origin, Destination and Airfare for a given travel destination that has the lowest airfare for the given trave destination

SELECT F.FlightID, C.CarrierName, F.Origin, F.Destination, F.Airfare
FROM Flight F
JOIN Carrier C ON F.CarrierID = C.CarrierID
WHERE F.Destination = 'Destination1'
ORDER BY F.Airfare ASC
LIMIT 1;

-- creating flight shedule
CREATE TABLE FlightSchedule(
    FlightScheduleID INT PRIMARY KEY,
    FlightID INT,
    DateOfTravel DATE,
    BusinessClassBookedCount INT,
    EconomyClassBookedCount INT,
    ExecutiveClassBookedCount INT,
    FOREIGN KEY (FlightID) REFERENCES Flight(FlightID)
);

INSERT INTO FlightSchedule (FlightScheduleID, FlightID, DateOfTravel, BusinessClassBookedCount, EconomyClassBookedCount, ExecutiveClassBookedCount)
VALUES
    (1, 123, '2024-06-01', 5, 50, 10),
    (2, 124, '2024-06-15', 10, 30, 5),
    (3, 125, '2024-07-01', 15, 20, 5),
    (5, 126, '2024-08-01', 10, 20, 5);
-- displaying flight shedule 
  select * from FlightSchedule;
  
-- total no of seats that are not booked yet in Economy, Business and executive class for a given travel destination 

SELECT F.FlightID, C.CarrierName, F.Origin, F.destination, F.Airfare, (F.SeatCapacityEconomyClass - FS.EconomyClassBookedCount) AS AvailableEconomySeats,
(F.SeatCapacityBusinessClass - FS.BusinessClassBookedCount) AS AvailableBusinessSeats,
(F.SeatCapacityExecutiveClass - FS.ExecutiveClassBookedCount) AS AvailableEconomySeats FROM Flight F

JOIN Carrier C on F.CarrierID = C.CarrierID
JOIN FlightSchedule FS ON F.FlightID = FS.FlightID
WHERE F.destination = 'Destination1';

-- Create the FLightBooking TABLE

CREATE TABLE FlightBooking(
   BookingID INT PRIMARY KEY,
   FlightID INT,
   UserID INT,
   NoOfSeats INT,
   SeatCategory VARCHAR(50) CHECK (SeatCategory IN('Economy','Executive','Business')),
   DateOfTravel DATE,
   BookingStatus VARCHAR(50) CHECK (BookingStatus IN('Booked', 'Travel Completed','Cancelled')),
   BookingAmount INT,
   FOREIGN KEY (FlightID) REFERENCES Flight(FlightID)
);

-- Inserting data into the flightbooking TABLE

INSERT INTO Flightbooking(BookingID,FlightID,UserID,NoOfSeats,SeatCategory,DateOfTravel,BookingStatus,BookingAmount)
VALUES (1,123,101,2,'Economy','2024-06-01','Booked',400),
(2,124,102,1,'Business','2024-06-15','Booked',300),
(3,125,103,3,'Executive','2024-07-01','Booked',600),
(4,126,104,1,'Economy','2024-08-01','Travel Completed',200);


SELECT * FROM FlightBooking;

-- sql query to remove all flight that belongto given carrier and for a specific travel destination 
DELETE FS 
FROM FlightSchedule FS
JOIN Flight F on FS.FlightID = F.FlightID
WHERE F.CarrierID =1 and F.destination= 'Destination1';

DELETE FB 
FROM FlightBooking FB
JOIN Flight F on FB.FlightID = F.FlightID
WHERE F.CarrierID =1 and F.destination= 'Destination1';

DELETE FROM Flight
WHERE CarrierID = 1 and Destination= 'Destination1';

select *from Flight;


-- sql query to view allcarrier names that operate on a given travel rote 

select distinct C.CarrierName
from Carrier C
Join Flight F on C.CarrierID = F.CarrierID
where F.Origin = 'Origin3' and F.Destination = 'Destination3';

-- uodate airfare by 10% for a given travel route and carrier if the minimum discount percentage 
-- is greater than 3% 


update Flight 
SET Airfare = Airfare * 1.10
where CarrierID = (select CarrierID
from Carrier 
where CarrierID = 1
AND LEAST (DiscountPercentageThirtyDaysAdvanceBooking,DiscountPercentageNinetyDaysAdvanceBooking
,DiscountPercentageSixtyDaysAdvanceBooking,RefundPercentageForTicketCancellation20DaysBeforeTravelDate,
RefundPercentageForTicketCancellation10DaysBeforeTravelDate,RefundPercentageForTicketCancellation2DaysBeforeTravelDate,
BulkBookingDiscount,SilverUserDiscount,GoldUserDiscount,PlatinumUserDiscount) > 3)

AND Origin = 'Origin4'
and Destination = 'Destination4';