-- Carrier table

create table Carrier (
     
carrierID int primary key,

CarrierName varchar(50),
DiscountPercentageThirtyDaysAdvanceBooking int,
DiscountPercentageSixtyDaysAdvanceBooking int,
DiscountPercentageNinteyDaysAdvanceBooking int,
RefundPercentageForTicketCancellation2DaysBeforeTravelDate int,
RefundPercentageForTicketCancellation10DaysBeforeTravelDate int,
RefundPercentageForTicketCancellation20DaysBeforeTravelDate int,
BulkBookingDiscount int,
SilverUserDiscount int,
GoldUserDiscount int,
PlatinumUserDiscount int
);
insert into Carrier 
(carrierID,CarrierName,DiscountPercentageThirtyDaysAdvanceBooking, DiscountPercentageSixtyDaysAdvanceBooking,DiscountPercentageNinteyDaysAdvanceBooking,
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);


-- Flight table

create table Flight(
FlightID int 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));

INSERT INTO Flight (FlightID,carrierID,Origin, Destination, AirFare, SeatCapacityEconomyClass, SeatCapacityBusinessClass, SeatCapacityExecutiveClass)
VALUES
    (123,1,'Delhi', 'Mumbai', 2000, 100, 50, 20),
    (124,2,'Mumbai', 'Pune', 3000, 150, 70, 30),
    (125,1,'Pune', 'Bhopal', 4000, 200, 100, 40),
    (126,4,'Pune', 'Delhi', 25000, 120, 60, 25),
    (127,2,'Mumbai', 'Delhi', 3500, 180, 80, 35);
select * from Carrier;    
select * from Flight;   


-- FlightSchedule Table

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),
    (4, 126, '2024-07-15', 10, 40, 5),
    (5, 127, '2024-08-01', 10, 20, 5);
    
    
select * from FlightSchedule;


-- Flight Booking 

CREATE TABLE FlightBooking(
    BookingID INT primary key,
    FlightId INT ,
    UserID INT ,
    NoOfSeats INT,
    SeatCategory VARCHAR(20) CHECK (SeatCategory IN ('Economy','Executive','Business')),
    DateOfTravel DATE,
    BookingStatus VARCHAR(20) CHECK (BookingStatus IN ('Booked', 'Travel Completed', 'Cancelled')),
    BookingAmount INT);

INSERT INTO FlightBooking (BookingID, FlightID, UserID, NoOfSeats, SeatCategory, DateOfTravel, BookingStatus, BookingAmount)
VALUES
    (1, 123, 1, 2, 'Economy', '2024-07-01', 'Booked', 5000),
    (2, 124, 2, 1, 'Business', '2024-07-17', 'Booked', 10000),
    (3, 125, 3, 3, 'Executive', '2024-07-01', 'Booked', 12000),
    (4, 126, 4, 2, 'Economy', '2024-07-15', 'Booked', 5000),
    (5, 127, 5, 1, 'Business', '2024-08-01', 'Booked', 11000);
    
select * from FlightBooking;




-- 9 Write SQL Query to update Booking status to cancelled . Also after the booking is cancelled, update Flight schedule table to decrement ticked booked count for the given flight ID and booking date


UPDATE FlightBooking
SET BookingStatus = 'Cancelled'
WHERE BookingID = 2;
UPDATE FlightSchedule
SET 
    EconomyClassBookedCount = EconomyClassBookedCount - (
        SELECT SUM(NoOfSeats)
        FROM FlightBooking
        WHERE FlightID = 5
        AND DateOfTravel = '2024-07-15'
        AND SeatCategory = 'Economy'
        AND BookingStatus = 'Cancelled'
    ),
    BusinessClassBookedCount = BusinessClassBookedCount - (
        SELECT SUM(NoOfSeats)
        FROM FlightBooking
        WHERE FlightID = 2
        AND DateOfTravel = '2024-07-17'
        AND SeatCategory = 'Business'
        AND BookingStatus = 'Cancelled'
    ),
    ExecutiveClassBookedCount = ExecutiveClassBookedCount - (
        SELECT SUM(NoOfSeats)
        FROM FlightBooking
        WHERE FlightID = 1
        AND DateOfTravel = '2024-07-07'
        AND SeatCategory = 'Executive'
        AND BookingStatus = 'Cancelled'
    )
WHERE 
    FlightID = 5
    AND DateOfTravel = '2024-04-15';


select * from FlightBooking;



-- 6 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
JOIN (
    SELECT Destination, MIN(AirFare) AS MinAirFare
    FROM Flight
    WHERE Destination = 'Pune' 
    GROUP BY Destination
) AS MinFareSubquery ON F.Destination = MinFareSubquery.Destination AND F.AirFare = MinFareSubquery.MinAirFare;




-- 5 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

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;


-- 11 Write SQL Query to view all carrier Names that operates on a given travel route


SELECT C.CarrierName
FROM Carrier C
JOIN Flight F ON C.CarrierID = F.CarrierID
WHERE F.Origin = 'Mumbai' 
AND F.Destination = 'Pune';



-- 12 Write SQL Query to update to increase airfare by 10% for a given travel route and for a given carrier and if the minimum discount % is greater than 3%(Refer Carrier Table for discount percentage0

UPDATE Flight
SET AirFare = AirFare * 1.1
WHERE CarrierID = 2 
AND Origin = 'Mumbai' 
AND Destination = 'Pune'
AND (
    SELECT MIN(
        CASE
            WHEN DiscountPercentageThirtyDaysAdvanceBooking > 3 THEN DiscountPercentageThirtyDaysAdvanceBooking
            WHEN DiscountPercentageSixtyDaysAdvanceBooking > 3 THEN DiscountPercentageSixtyDaysAdvanceBooking
            WHEN DiscountPercentageNinteyDaysAdvanceBooking > 3 THEN DiscountPercentageNinteyDaysAdvanceBooking
            ELSE 0 -- If no discount is greater than 3%, consider it as 0
        END
    )
    FROM Carrier
    WHERE CarrierID = 2 -- Match the carrier ID
) > 3; 

select * from Flight;




-- 7 Write SQL Query to retrieve FlightId, Carrier Name, Origin, Destination, Airfare and total no of seats that are not booked yet in Economy, Business and Executive class for a given travel destination. (Hint use joins with 3 tables - Flight, Carrier and Flight Schedule)

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 = 'Pune';


-- 10 Write SQL Query to remove all flight that belong to a given carrier and for a specific travel destination

DELETE FROM Flight
WHERE CarrierID = 4 
AND Destination = 'Pune'; 





-- Write SQL Query to get Carrier Name and maximum count of Flights that are tagged to each Carrier flying on the date given .  Should display the result in descending order of maximum count of flights 

SELECT C.CarrierName, COUNT(F.FlightID) AS FlightCounts
FROM Carrier C
JOIN Flight F ON C.CarrierID = F.CarrierID
JOIN FlightSchedule FS ON F.FlightID = FS.FlightID
WHERE FS.DateOfTravel = "2024-07-15"
GROUP BY  C.CarrierName, C.CarrierID
ORDER BY FlightCounts DESC;



-- Write SQL Query to retreive FlightId, CarrierName, Origin, Destination, Airfare, DateOfTravel,BookingStatus of the flights for the given user id . Should display the result in descending order of TravelDate .

SELECT 
F.FlightID,
C.CarrierName,
F.Origin,
F.Destination,
F.AirFare,
FB.DateOfTravel,
FB.BookingStatus
FROM
FlightBooking FB
JOIN
Flight F ON FB.FlightID = F.FlightID
JOIN
Carrier C ON F.CarrierID = C.CarrierID
WHERE 
FB.UserID = 1
ORDER BY 
FB.DateOfTravel DESC;