-- Create the Flights table
CREATE TABLE Flights (
    FlightID INT,
    AirlineName VARCHAR(255),
    DepartureCity VARCHAR(255),
    DestinationCity VARCHAR(255),
    DepartureDateTime DATETIME,
    ArrivalDateTime DATETIME,
    TicketPrice DECIMAL(10, 2)
);

-- Create the User table
CREATE TABLE User (
  UserID INT PRIMARY KEY,
  Name VARCHAR(255),
  Email VARCHAR(255),
  Password VARCHAR(255),
  ContactInformation VARCHAR(255)
);

-- Create the Hotel table
CREATE TABLE Hotel (
  HotelID INT PRIMARY KEY,
  HotelName VARCHAR(255),
  Location VARCHAR(255),
  StarRating INT,
  RoomTypes VARCHAR(255),
  Amenities VARCHAR(255),
  PricePerNight DECIMAL(10, 2)
);

-- Create the Booking table
CREATE TABLE Booking (
  BookingID INT PRIMARY KEY,
  UserID INT,
  FlightID INT,
  HotelID INT,
  BookingDate DATE,
  Status VARCHAR(255),
  FOREIGN KEY (UserID) REFERENCES User(UserID),
  FOREIGN KEY (FlightID) REFERENCES Flight(FlightID),
  FOREIGN KEY (HotelID) REFERENCES Hotel(HotelID)
);

-- Create the Payment table
CREATE TABLE Payment (
  PaymentID INT PRIMARY KEY,
  BookingID INT,
  PaymentMethod VARCHAR(255),
  PaymentDate DATE,
  TransactionAmount DECIMAL(10, 2),
  FOREIGN KEY (BookingID) REFERENCES Booking(BookingID)
);
-- Create the DepartureCities table
CREATE TABLE DepartureCities (
    CityID INT PRIMARY KEY,
    CityName VARCHAR(255)
);


-- Insert sample data into the Flights table
INSERT INTO Flights(FlightID, AirlineName, DepartureCity, DestinationCity, DepartureDateTime, ArrivalDateTime, TicketPrice)
VALUES (123, 'Airline XYZ', 'New York', 'Los Angeles', '2023-05-21 08:00:00', '2023-05-21 10:30:00', 250.00);
INSERT INTO Flights(FlightID, AirlineName, DepartureCity, DestinationCity, DepartureDateTime, ArrivalDateTime, TicketPrice)
VALUES (456, 'Airline ABC', 'London', 'Paris', '2023-05-21 12:00:00', '2023-05-21 14:30:00', 150.50);
INSERT INTO Flights(FlightID, AirlineName, DepartureCity, DestinationCity, DepartureDateTime, ArrivalDateTime, TicketPrice)
VALUES (101, 'Airline ABC', 'Tokyo', 'Beijing', '2023-05-21 15:45:00', '2023-05-21 19:20:00', 200.00);
INSERT INTO Flights(FlightID, AirlineName, DepartureCity, DestinationCity, DepartureDateTime, ArrivalDateTime, TicketPrice)
VALUES (789, 'Airline XYZ', 'Sydney', 'Melbourne', '2023-05-21 09:30:00', '2023-05-21 11:00:00', 120.75);


-- Create a view to retrieve flight details
CREATE VIEW FlightDetailsView AS
SELECT FlightID, AirlineName, DepartureCity, DestinationCity
FROM Flights;
-- This view selects relevant columns from the Flights table to provide flight details.
-- Retrieve the flights departing from a specific city
SELECT Flights.*
FROM Flights
JOIN DepartureCities ON Flights.DepartureCity = DepartureCities.CityName
WHERE DepartureCities.CityName = 'New York';
-- This join combines the Flights table with a separate DepartureCities table to retrieve flights departing from a specific city.

-- Create a view to calculate total revenue generated by each airline
CREATE VIEW AirlineRevenueView AS
SELECT AirlineName, SUM(TicketPrice) AS TotalRevenue
FROM Flights
GROUP BY AirlineName;
-- This view calculates the total revenue generated by each airline by summing up the TicketPrice values for all flights and grouping the results by AirlineName.

-- Retrieve all flights from a specific departure city
SELECT *
FROM Flights
WHERE DepartureCity = 'New York';
-- This query retrieves all flights from the specified departure city.

-- Retrieve flights with ticket prices less than $300
SELECT *
FROM Flights
WHERE TicketPrice < 300.00;
-- This query retrieves flights with ticket prices less than $300.


-- Begin the transaction
BEGIN TRANSACTION;

-- Insert a new flight into the Flights table
INSERT INTO Flights (FlightID, AirlineName, DepartureCity, DestinationCity, DepartureDateTime, ArrivalDateTime, TicketPrice)
VALUES (123, 'Airline XYZ', 'New York', 'Los Angeles', '2023-05-21 08:00:00', '2023-05-21 10:30:00', 250.00);

-- Update the ticket price for a specific flight
UPDATE Flights
SET TicketPrice = 200.00
WHERE FlightID = 123;

-- Commit the transaction
COMMIT;