DatabaseCreation: CrewAvailability.sql

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.arrival < now()
         order by f.arrival 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.arrival < now()
         order by f.arrival desc limit 1)
    ) c
    order by arrival desc limit 1
) lf on true
join gate ldg on ldg.id  = lf.gdep
join terminal ldt on ldt.id  = ldg.terminalid
join airport ldap on ldap.id = ldt.airportid
join gate lag on lag.id  = lf.garr
join terminal lat_ on lat_.id = lag.terminalid
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
join gate ndg  on ndg.id  = nf.gdep
join terminal ndt on ndt.id  = ndg.terminalid
join airport ndap on ndap.id = ndt.airportid
join gate nag on nag.id  = nf.garr
join terminal nat_ on nat_.id = nag.terminalid
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.arrival < now()
    order by f.arrival desc limit 1
) lf on true
join gate ldg on ldg.id  = lf.gdep
join terminal ldt on ldt.id  = ldg.terminalid
join airport ldap on ldap.id = ldt.airportid
join gate lag on lag.id  = lf.garr
join terminal lat_ on lat_.id = lag.terminalid
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
join gate ndg  on ndg.id  = nf.gdep
join terminal ndt on ndt.id  = ndg.terminalid
join airport ndap on ndap.id = ndt.airportid
join gate nag on nag.id  = nf.garr
join terminal nat_ on nat_.id = nag.terminalid
join airport naap on naap.id = nat_.airportid;