DatabaseCreation: views.sql

CREATE VIEW vw_airport_infrastructure AS
SELECT ap.AirportName, ap.IataCode, ap.OperationalStatus,
       COUNT(DISTINCT t.TerminalId) AS Terminals,
       COUNT(DISTINCT g.GateId)     AS Gates,
       COUNT(DISTINCT r.RunwayId)   AS Runways,
       COUNT(DISTINCT tx.TaxiwayId) AS Taxiways
FROM Airport ap
LEFT JOIN Terminal t  ON ap.AirportId = t.AirportId
LEFT JOIN Gate g      ON t.TerminalId = g.TerminalId
LEFT JOIN Runway r    ON ap.AirportId = r.AirportId
LEFT JOIN Taxiway tx  ON ap.AirportId = tx.AirportId
WHERE ap.AirportId IN (1, 2, 3, 5, 8, 17)
GROUP BY ap.AirportId, ap.AirportName, ap.IataCode, ap.OperationalStatus;

CREATE VIEW vw_maintenance_overview AS
SELECT am.MaintenanceId, am.MaintenanceType, am.Status,
       am.StartDate, am.EndDate, am.DurationMinutes, am.MaintenanceCost,
       ac.AircraftModel, ac.RegistrationNumber,
       ap.AirportName,
       e.FirstName || ' ' || e.LastName AS MechanicName
FROM AircraftMaintenance am
JOIN Aircraft ac ON am.AircraftId = ac.AircraftId
LEFT JOIN Airport ap ON am.AirportId = ap.AirportId
LEFT JOIN WorksOn wo ON am.MaintenanceId = wo.MaintenanceId
LEFT JOIN Employee e ON wo.EmployeeId = e.EmployeeId;


CREATE VIEW vw_aircraft_status AS
SELECT ac.AircraftId, ac.AircraftModel, ac.RegistrationNumber,
       ac.Status, ac.LastInspectionDate, ac.Capacity,
       at2.Manifacturer, at2.Category,
       am.MaintenanceType, am.Status AS MaintenanceStatus
FROM Aircraft ac
LEFT JOIN AircraftType at2 ON ac.AircraftTypeId = at2.AircraftTypeId
LEFT JOIN AircraftMaintenance am
       ON ac.AircraftId = am.AircraftId
       AND am.Status IN ('Scheduled', 'In Progress');

CREATE VIEW vw_departures_board AS
SELECT f.FlightNumber, f.DepartureTime, f.OperatingStatus,
       a_dest.AirportName AS Destination,
       g.GateCode, t.TerminalName,
       r.RunwayName, tx.TaxiwayName,
       d.Status AS DepartureStatus
FROM Departures d
JOIN Flights f ON d.FlightId = f.FlightId
JOIN Airport a_dest ON d.DestinationAirportId = a_dest.AirportId
JOIN Gate g ON d.GateId = g.GateId
JOIN Terminal t ON g.TerminalId = t.TerminalId
JOIN Runway r ON d.RunwayId = r.RunwayId
LEFT JOIN Taxiway tx ON d.TaxiwayId = tx.TaxiwayId;

CREATE VIEW vw_arrivals_board AS
SELECT f.FlightNumber, f.ArrivalTime, f.OperatingStatus,
       a_orig.AirportName AS Origin,
       g.GateCode, t.TerminalName,
       r.RunwayName, tx.TaxiwayName,
       ar.Status AS ArrivalStatus
FROM Arrivals ar
JOIN Flights f ON ar.FlightId = f.FlightId
JOIN Airport a_orig ON ar.OriginAirportId = a_orig.AirportId
JOIN Gate g ON ar.GateId = g.GateId
JOIN Terminal t ON g.TerminalId = t.TerminalId
JOIN Runway r ON ar.RunwayId = r.RunwayId
LEFT JOIN Taxiway tx ON ar.TaxiwayId = tx.TaxiwayId;

CREATE OR REPLACE VIEW vw_boarding_pass_details AS
SELECT bp.PassId, bp.BoardingTime, bp.Status AS BoardingStatus,
       p.FirstName, p.LastName,
       t.SeatNumber, t.Class,
       f.FlightNumber, f.DepartureTime,
       g.GateCode, ter.TerminalName,
       l.LuggageType, l.Weight, l.Status AS LuggageStatus
FROM BoardingPass bp
JOIN Ticket t ON bp.TicketId = t.TicketId
JOIN Passenger p ON t.PassengerId = p.PassengerId
JOIN Flights f ON t.FlightId = f.FlightId
JOIN Gate g ON bp.GateId = g.GateId
JOIN Terminal ter ON g.TerminalId = ter.TerminalId
LEFT JOIN Luggage l ON t.LuggageId = l.LuggageId;


CREATE VIEW vw_ticket_info AS
SELECT
    p.FirstName,
    p.LastName,
    p.PassportNumber,
    t.TicketId,
    t.SeatNumber,
    t.Class,
    t.TicketPrice,
    f.FlightNumber,
    a.Name AS AirlineName,
    f.DepartureTime,
    f.ArrivalTime,
    f.OperatingStatus,
    l.status,
    l.weight,
    l.checkedat,
    l.luggagetype,
    l.luggageid
FROM Ticket t
JOIN Passenger p ON t.PassengerId = p.PassengerId
JOIN Flights f ON t.FlightId = f.FlightId
JOIN Airline a ON f.AirlineId = a.AirlineId
JOIN luggage l ON l.luggageid = t.luggageid;

CREATE VIEW vw_flight_overview AS
SELECT f.FlightId, f.FlightNumber, f.FlightClass, f.OperatingStatus,
       f.DepartureTime, f.ArrivalTime,
       a_orig.AirportName AS Origin, a_dest.AirportName AS Destination,
       al.Name AS Airline,
       ac.AircraftModel, ac.RegistrationNumber
FROM Flights f
JOIN Airport a_orig ON f.OriginAirportId = a_orig.AirportId
JOIN Airport a_dest ON f.DestinationAirportId = a_dest.AirportId
JOIN Airline al ON f.AirlineId = al.AirlineId
JOIN Aircraft ac ON f.AircraftId = ac.AircraftId;

CREATE VIEW vw_passenger_flight_history AS
SELECT
    p.FirstName,
    p.LastName,
    p.PassportNumber,

    f.FlightNumber,
    f.DepartureTime,
    f.ArrivalTime,
    f.OperatingStatus,

    t.SeatNumber,
    t.Class,
    t.TicketPrice,
    t.Status AS TicketStatus
FROM Ticket t
JOIN Passenger p ON t.PassengerId = p.PassengerId
JOIN Flights f ON t.FlightId = f.FlightId;

CREATE VIEW GateOccupancyView AS
SELECT
    g.GateId,
    g.GateCode,
    t.TerminalId,
    t.TerminalName,
    t.TerminalCode,
    ap.AirportName,
    f.FlightId,
    f.FlightNumber,
    al.Name                                AS AirlineName,
    d.DepartureTime,
    d.Status                               AS DepartureStatus,
    ap_dest.AirportName                    AS Destination,
    g.Status                               AS GateStatus

FROM Gate g
JOIN Terminal t      ON t.TerminalId          = g.TerminalId
JOIN Airport ap      ON ap.AirportId          = t.AirportId
JOIN Departures d    ON d.GateId              = g.GateId
JOIN Flights f       ON f.FlightId            = d.FlightId
JOIN Airline al      ON al.AirlineId          = f.AirlineId
JOIN Airport ap_dest ON ap_dest.AirportId     = d.DestinationAirportId
WHERE d.Status IN ('Scheduled', 'Go to gate', 'Boarding');