create view CrewAvailability (EmployeeId, Name, Surname, DateOfBirth, Active, EmployeeType, Rank,
    LastFlightId, LastFlightNumber, LastFlightOrigin, LastFlightDestination,
    LastFlightDeparture, LastFlightArrival,
    NextFlightId, NextFlightNumber, NextFlightOrigin, NextFlightDestination,
    NextFlightDeparture, NextFlightArrival,
    HoursSinceLastFlight, HoursUntilNextFlight, Availability) as
select pilot.employeeid, employee.name, employee.surname, employee.dateofbirth, employee.active,
       'Pilot', pilotrank.name,
       lf.flightid, lf.flightnumber, ldap.code, laap.code, lf.departure, lf.arrival,
       nf.flightid, nf.flightnumber, ndap.code, naap.code, nf.departure, nf.arrival,
       extract(epoch from (now() - lf.arrival))   / 3600,
       extract(epoch from (nf.departure - now())) / 3600,
       case
           when lf.arrival > now()                       then 'On Duty'
           when now() - lf.arrival < interval '12 hours' then 'Resting'
           when lf.flightid is null                      then 'Available - No Flight History'
           else 'Available'
       end
from pilot
join employee  on employee.id = pilot.employeeid
join pilotrank on pilot.rank  = pilotrank.id
left join lateral (
    select id as flightid, flightnumber, departure, arrival,
           actualgatedepartureid as gdep, actualgatearrivalid as garr
    from (
        (select f.id, f.flightnumber, f.departure, f.arrival, f.actualgatedepartureid, f.actualgatearrivalid
         from flight f where f.pilot   = pilot.employeeid and f.departure < now()
         order by f.departure desc limit 1)
        union all
        (select f.id, f.flightnumber, f.departure, f.arrival, f.actualgatedepartureid, f.actualgatearrivalid
         from flight f where f.copilot = pilot.employeeid and f.departure < now()
         order by f.departure desc limit 1)
    ) c
    order by departure desc limit 1
) lf on true
left join gate     ldg  on ldg.id  = lf.gdep
left join terminal ldt  on ldt.id  = ldg.terminalid
left join airport  ldap on ldap.id = ldt.airportid
left join gate     lag  on lag.id  = lf.garr
left join terminal lat_ on lat_.id = lag.terminalid
left join airport  laap on laap.id = lat_.airportid
left join lateral (
    select id as flightid, flightnumber, departure, arrival,
           actualgatedepartureid as gdep, actualgatearrivalid as garr
    from (
        (select f.id, f.flightnumber, f.departure, f.arrival, f.actualgatedepartureid, f.actualgatearrivalid
         from flight f where f.pilot   = pilot.employeeid and f.departure > now()
         order by f.departure asc limit 1)
        union all
        (select f.id, f.flightnumber, f.departure, f.arrival, f.actualgatedepartureid, f.actualgatearrivalid
         from flight f where f.copilot = pilot.employeeid and f.departure > now()
         order by f.departure asc limit 1)
    ) c
    order by departure asc limit 1
) nf on true
left join gate     ndg  on ndg.id  = nf.gdep
left join terminal ndt  on ndt.id  = ndg.terminalid
left join airport  ndap on ndap.id = ndt.airportid
left join gate     nag  on nag.id  = nf.garr
left join terminal nat_ on nat_.id = nag.terminalid
left join airport  naap on naap.id = nat_.airportid

union all

select flightattendant.employeeid, employee.name, employee.surname, employee.dateofbirth, employee.active,
       'Flight Attendant', flightattendant.certificationlevel::text,
       lf.flightid, lf.flightnumber, ldap.code, laap.code, lf.departure, lf.arrival,
       nf.flightid, nf.flightnumber, ndap.code, naap.code, nf.departure, nf.arrival,
       extract(epoch from (now() - lf.arrival))   / 3600,
       extract(epoch from (nf.departure - now())) / 3600,
       case
           when lf.arrival > now()                       then 'On Duty'
           when now() - lf.arrival < interval '12 hours' then 'Resting'
           when lf.flightid is null                      then 'Available - No Flight History'
           else 'Available'
       end
from flightattendant
join employee on employee.id = flightattendant.employeeid
left join lateral (
    select f.id as flightid, f.flightnumber, f.departure, f.arrival,
           f.actualgatedepartureid as gdep, f.actualgatearrivalid as garr
    from cabincrew cc
    join flight f on f.id = cc.flightid
    where cc.flightattendantid = flightattendant.employeeid
      and f.departure < now()
    order by f.departure desc limit 1
) lf on true
left join gate     ldg  on ldg.id  = lf.gdep
left join terminal ldt  on ldt.id  = ldg.terminalid
left join airport  ldap on ldap.id = ldt.airportid
left join gate     lag  on lag.id  = lf.garr
left join terminal lat_ on lat_.id = lag.terminalid
left join airport  laap on laap.id = lat_.airportid
left join lateral (
    select f.id as flightid, f.flightnumber, f.departure, f.arrival,
           f.actualgatedepartureid as gdep, f.actualgatearrivalid as garr
    from cabincrew cc
    join flight f on f.id = cc.flightid
    where cc.flightattendantid = flightattendant.employeeid
      and f.departure > now()
    order by f.departure asc limit 1
) nf on true
left join gate     ndg  on ndg.id  = nf.gdep
left join terminal ndt  on ndt.id  = ndg.terminalid
left join airport  ndap on ndap.id = ndt.airportid
left join gate     nag  on nag.id  = nf.garr
left join terminal nat_ on nat_.id = nag.terminalid
left join airport  naap on naap.id = nat_.airportid;