create view AirplaneAvailability (AirplaneId, RegistrationNumber, Active, AirplaneModelId, AirplaneModel,
    Manufacturer, RequiredAttendants, NumberOfSeats, LastFlightId, LastFlightNumber, LastFlightOrigin,
    LastFlightDestination, LastFlightDeparture, LastFlightArrival, NextFlightId, NextFlightNumber,
    NextFlightOrigin, NextFlightDestination, NextFlightDeparture, NextFlightArrival,
    HoursSinceLastFlight, HoursUntilNextFlight, Availability) as
select airplane.id, airplane.registrationnumber, airplane.active,
       airplanemodel.id, airplanemodel.model, airplanemodel.manufacturer,
       airplanemodel.requiredattendants, airplanemodel.numberofseats,
       lf.flightid, lf.flightnumber, lf.origin, lf.destination,
       lf.departure, lf.arrival,
       nf.flightid, nf.flightnumber, nf.origin, nf.destination,
       nf.departure, nf.arrival,
       round(extract(epoch from (now() - lf.arrival)) / 3600, 1),
       round(extract(epoch from (nf.departure - now())) / 3600, 1),
       case
           when airplane.active = false then 'Inactive'
           when nf.departure < now() and nf.arrival > now() then 'In Flight'
           when lf.flightid is null then 'Available - No Flight History'
           when now() - lf.arrival < interval '4 hours' then 'Turnaround'
           else 'Available'
       end
from airplane
join airplanemodel on airplane.modelid = airplanemodel.id
left join lateral (
    select f.id as flightid, f.flightnumber, f.departure, f.arrival,
           dap.code as origin, aap.code as destination
    from flight f
    join gate dg on dg.id  = f.actualgatedepartureid
    join terminal dt on dt.id  = dg.terminalid
    join airport dap on dap.id = dt.airportid
    join gate ag on ag.id  = f.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aap on aap.id = at_.airportid
    where f.airplaneid = airplane.id and f.departure < now()
    order by f.departure desc
    limit 1
) lf on true
left join lateral (
    select f.id as flightid, f.flightnumber, f.departure, f.arrival,
           dap.code as origin, aap.code as destination
    from flight f
    join gate dg on dg.id  = f.actualgatedepartureid
    join terminal dt on dt.id  = dg.terminalid
    join airport dap on dap.id = dt.airportid
    join gate ag on ag.id  = f.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aap on aap.id = at_.airportid
    where f.airplaneid = airplane.id and f.departure > now()
    order by f.departure asc
    limit 1
) nf on true;