DROP DATABASE IF EXISTS airport;
CREATE DATABASE airport;
USE airport;

-- Създаване на таблица за летища
CREATE TABLE Airports (
  AirportID INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Country VARCHAR(255) NOT NULL,
  City VARCHAR(255) NOT NULL
);

-- Създаване на таблица за самолети
CREATE TABLE Planes (
  PlaneID INT AUTO_INCREMENT PRIMARY KEY,
  Model VARCHAR(255) NOT NULL,
  Capacity INT NOT NULL
);

-- Създаване на таблица за екипаж
CREATE TABLE Crew (
  CrewID INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Role VARCHAR(255) NOT NULL
);

-- Създаване на таблица за полети
CREATE TABLE Flights (
  FlightID INT AUTO_INCREMENT PRIMARY KEY,
  DepartureDate DATE NOT NULL,
  DepartureTime TIME NOT NULL,
  Duration TIME NOT NULL,
  DepartureAirportID INT NOT NULL,
  ArrivalAirportID INT NOT NULL,
  PlaneID INT NOT NULL,
  FOREIGN KEY (DepartureAirportID) REFERENCES Airports(AirportID),
  FOREIGN KEY (ArrivalAirportID) REFERENCES Airports(AirportID),
  FOREIGN KEY (PlaneID) REFERENCES Planes(PlaneID)
);

-- Създаване на таблица за пътници
CREATE TABLE Passengers (
  PassengerID INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL
);

-- Създаване на таблица за полети на пътници
CREATE TABLE PassengerFlights (
  PassengerID INT,
  FlightID INT,
  PRIMARY KEY (PassengerID, FlightID),
  FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID),
  FOREIGN KEY (FlightID) REFERENCES Flights(FlightID)
);

-- Създаване на таблица за видове храна
CREATE TABLE MealTypes (
  MealTypeID INT AUTO_INCREMENT PRIMARY KEY,
  Description VARCHAR(255) NOT NULL
);

-- Създаване на таблица за храна за пътници
CREATE TABLE PassengerMeals (
  PassengerID INT,
  FlightID INT,
  MealTypeID INT,
  PRIMARY KEY (PassengerID, FlightID, MealTypeID),
  FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID),
  FOREIGN KEY (FlightID) REFERENCES PassengerFlights(FlightID),
  FOREIGN KEY (MealTypeID) REFERENCES MealTypes(MealTypeID)
);

CREATE TABLE PilotSchedules (
  PilotID INT,
  FlightID INT,
  ScheduleDate DATE NOT NULL,
  DutyType ENUM('Flight', 'Standby', 'Training', 'Leave') NOT NULL,
  StartTime TIME NOT NULL,
  EndTime TIME NOT NULL,
  FOREIGN KEY (PilotID) REFERENCES Crew(CrewID),
  FOREIGN KEY (FlightID) REFERENCES Flights(FlightID),
  PRIMARY KEY (PilotID, ScheduleDate, StartTime)
);

CREATE TABLE Reservations (
  ReservationID INT AUTO_INCREMENT PRIMARY KEY,
  PassengerID INT,
  FlightID INT,
  ReservationDate DATE NOT NULL,
  Status ENUM('Active', 'Cancelled', 'Completed') NOT NULL,
  FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID),
  FOREIGN KEY (FlightID) REFERENCES Flights(FlightID)
);

CREATE TABLE Tickets (
  TicketID INT AUTO_INCREMENT PRIMARY KEY,
  ReservationID INT,
  Price DECIMAL(10,2) NOT NULL,
  IssuedDate DATE NOT NULL,
  SeatNumber INT NOT NULL,
  Type ENUM('Basic', 'Regular', 'Plus', 'Flexi Plus') NOT NULL,
  FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID)
);

CREATE TABLE Crew_Replacements (
    Replacement_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Original_Crew_Member_ID INT,
    New_Crew_Member_ID INT,
    Replacement_Date DATE,
    Reason VARCHAR(255),
    FOREIGN KEY (Original_Crew_Member_ID) REFERENCES Crew(CrewID),
    FOREIGN KEY (New_Crew_Member_ID) REFERENCES Crew(CrewID)
);

INSERT INTO Airports (Name, City, Country) VALUES
('John F. Kennedy International Airport', 'New York City', 'United States'),
('Heathrow Airport', 'London', 'United Kingdom'),
('Charles de Gaulle Airport', 'Paris', 'France'),
('Dubai World Central', 'Dubai', 'United Arab Emirates'),
('Beijing Capital International Airport', 'Beijing', 'China'),
('Dubai International Airport', 'Dubai', 'United Arab Emirates'),
('Los Angeles International Airport', 'Los Angeles', 'United States'),
('Tokyo Haneda Airport', 'Tokyo', 'Japan'),
('Beijing Daxing International Airport', 'Beijing', 'China'),
('Al Maktoum Airport', 'Dubai', 'United Arab Emirates');

INSERT INTO Planes (Model, Capacity) VALUES
('Boeing 737', 189),
('Airbus A320', 180),
('Boeing 777', 396);

INSERT INTO Crew (Name, Role) VALUES
('Ivan Ivanov', 'Pilot'),
('Maria Popova', 'Co-Pilot'),
('Test Testov', 'Co-Pilot'),
('Georgi Petrov', 'Pilot'),
('Peter Georgiev', 'Pilot'),
('Georgi Georgiev', 'Flight Attendant');

INSERT INTO Crew_Replacements (Original_Crew_Member_ID, New_Crew_Member_ID, Replacement_Date, Reason) VALUES
(2, 4, '2024-04-30', 'Medical leave replacement'),
(3, 1, '2024-05-01', 'Vacation replacement'),
(1, 3, '2024-05-02', 'Sick leave replacement'),
(4, 2, '2024-05-03', 'Training replacement'),
(2, 1, '2024-05-04', 'Vacation replacement');

INSERT INTO Flights (DepartureDate, DepartureTime, Duration, DepartureAirportID, ArrivalAirportID, PlaneID) VALUES
('2024-05-01', '08:00:00', '01:50:00', 1, 2, 1),
('2024-05-02', '15:30:00', '03:00:00', 2, 3, 2),
('2024-05-02', '22:00:00', '06:30:00', 3, 1, 3),
('2024-05-03', '08:00:00', '01:30:00', 1, 3, 1),
('2024-05-03', '15:00:00', '02:15:00', 1, 2, 1),
('2024-05-04', '06:00:00', '03:30:00', 2, 3, 3),
('2024-05-04', '18:00:00', '02:30:00', 1, 3, 3);

INSERT INTO Passengers (Name) VALUES
('Peter Petrov'),
('Anna Ivanova'),
('John Smith');

INSERT INTO PassengerFlights (PassengerID, FlightID) VALUES
(1, 1),
(2, 2),
(3, 3);

INSERT INTO MealTypes (Description) VALUES
('Vegetarian'),
('Standard'),
('Vegan');

INSERT INTO PassengerMeals (PassengerID, FlightID, MealTypeID) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3);

INSERT INTO PilotSchedules (PilotID, FlightID, ScheduleDate, DutyType, StartTime, EndTime) VALUES
(1, 1, '2024-05-01', 'Flight', '06:00:00', '11:00:00'),
(4, 2, '2024-05-02', 'Flight', '13:30:00', '22:30:00'),
(4, 3, '2024-05-02', 'Flight', '22:00:00', '03:30:00'),
(1, 4, '2024-05-03', 'Flight', '08:00:00', '11:00:00'),
(1, 5, '2024-05-03', 'Flight', '15:00:00', '19:00:00'),
(5, 6, '2024-05-04', 'Flight', '06:00:00', '11:00:00'),
(5, 7, '2024-05-04', 'Flight', '15:00:00', '20:00:00');

INSERT INTO Reservations (PassengerID, FlightID, ReservationDate, Status) VALUES
(1, 1, '2024-04-20', 'Active'),
(2, 2, '2024-04-22', 'Active'),
(3, 3, '2024-04-25', 'Active');

INSERT INTO Tickets (ReservationID, Price, IssuedDate, SeatNumber, Type) VALUES
(1, 299.99, '2024-04-20', 1, 'Basic'),
(2, 450.00, '2024-04-22', 2, 'Regular'),
(3, 1300.00, '2024-04-25', 3, 'Flexi Plus');

/* 2 */
SELECT
    F.FlightID,
    F.DepartureDate,
    F.DepartureTime,
    F.Duration,
    A.Name AS AirportName,
    P.Model AS PlaneModel
FROM
    Flights F
JOIN
    Airports A ON F.DepartureAirportID = A.AirportID
JOIN
    Planes P ON F.PlaneID = P.PlaneID
WHERE
    A.Name = 'Sofia Airport' AND
    F.DepartureDate = '2024-05-01';

/* 3 */
SELECT
    A.Name AS AirportName,
    COUNT(F.FlightID) AS NumberOfFlights
FROM
    Flights F
JOIN
    Airports A ON F.DepartureAirportID = A.AirportID
GROUP BY
    A.Name
ORDER BY
    NumberOfFlights DESC;

/* 4 */
SELECT
    P.Name AS PassengerName,
    F.DepartureDate,
    F.DepartureTime,
    R.Status AS ReservationStatus
FROM
    Passengers P
INNER JOIN
    Reservations R ON P.PassengerID = R.PassengerID
INNER JOIN
    Flights F ON R.FlightID = F.FlightID
WHERE
    F.DepartureDate = '2024-05-01';

/* 5 */
SELECT
    C.Name AS OriginalCrewName,
    C.Role AS OriginalCrewRole,
    CR.Replacement_Date,
    R.Name AS ReplacementName,
    R.Role AS ReplacementRole,
    CR.Reason
FROM
    Crew C
LEFT OUTER JOIN
    Crew_Replacements CR ON C.CrewID = CR.Original_Crew_Member_ID
LEFT OUTER JOIN
    Crew R ON CR.New_Crew_Member_ID = R.CrewID
ORDER BY
    C.Name;

/* 6 */
SELECT
    F.FlightID,
    F.DepartureDate,
    F.DepartureTime,
    F.Duration
FROM
    Flights F
WHERE
    F.Duration > (SELECT AVG(Duration) FROM Flights);

/* 7 */
SELECT
    C.Name AS PilotName,
    COUNT(PS.FlightID) AS TotalFlights
FROM
    Crew C
JOIN
    PilotSchedules PS ON C.CrewID = PS.PilotID
WHERE
    C.Role = 'Pilot'
GROUP BY
    C.Name
ORDER BY
    TotalFlights DESC;

/* 8 */
ALTER TABLE Flights
ADD COLUMN arrival_time TIME AFTER Duration;

DELIMITER //

CREATE TRIGGER calculate_arrival_time
BEFORE UPDATE ON Flights
FOR EACH ROW
BEGIN
    IF NEW.DepartureTime != OLD.DepartureTime THEN
        SET NEW.arrival_time = ADDTIME(NEW.DepartureTime, NEW.Duration);
    END IF;
END;
//
DELIMITER ;

Update Flights
SET DepartureTime = '09:00:00'
Where FlightID in (1);

Select * from Flights;

/* 9 */
DROP PROCEDURE IF EXISTS CityAirports;
DROP TABLE IF EXISTS tempAirports3;
DELIMITER |
CREATE PROCEDURE CityAirports(IN INCountry VARCHAR(255))
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE tempName VARCHAR(255);
    DECLARE tempCity VARCHAR(255);

    DECLARE airportsCursor CURSOR FOR
        SELECT name, city
        FROM Airports
        WHERE country = INCountry;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    -- Check if the temporary table exists, and drop it if it does
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'tempAirports3') THEN
        DROP TEMPORARY TABLE tempAirports3;
    END IF;

    CREATE TEMPORARY TABLE tempAirports3 (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tempName VARCHAR(255),
        tempCity VARCHAR(255)
    ) ENGINE = Memory;

    OPEN airportsCursor;

    airports_loop: WHILE finished = 0 DO
        FETCH airportsCursor INTO tempName, tempCity;

        IF finished = 1 THEN
            LEAVE airports_loop;
        END IF;

        INSERT INTO tempAirports3(tempName, tempCity)
        VALUES (tempName, tempCity);
    END WHILE;

    CLOSE airportsCursor;

    SELECT * FROM tempAirports3;

    DROP TEMPORARY TABLE tempAirports3;

END |
DELIMITER ;

CALL CityAirports('United Arab Emirates');