/*
* Airport Information for Austin
*/

select *
from airportinfo
where airportCode = 'kaus'

/*
* Flights enroute to austin
*/

select e.ident,
    actualdeparturetime,
    filed_departuretime,
    estimatedarrivaltime,
    origenCity,
    destinationCity,
    latitude,
    longitude,
    altitude
from enroute e
join inflightinfo i on e.ident = i.ident
where airport = 'kaus' and actualdeparturetime != 0 and filter = 'airline'

/*
* Current upcoming Austin departures
* joins to get flightid and uses the flightinfoex to get the status of the flight
* It uses a case statement to determine status
*/

select s.ident,
    e.origen,
    s.origenCity,
    e.destination,
    s.destinationCity,
    s.filed_departuretime,
    s.estimatedarrivaltime,
    case
        when e.actualarrivaltime = -1 and e.actualdeparturetime = -1 and e.estimatedarrivaltime = -1
            then 'cancelled'
        when e.actualdeparturetime != 0 and e.actualarrivaltime = 0
            then 'enroute'
        when e.actualdeparturetime != 0 and e.actualarrivaltime != 0 and e.actualdeparturetime != e.actualarrivaltime
            then 'arrived'
        else 'not departed'
        end as status
from scheduled s
join getflightid f on f.departureTime = s.filed_departuretime and f.ident = s.ident
join flightinfoex e on e.faFlightID = f.faFlightID
where airport = "kaus" and filter = "airline"

/*
* Queries enroute for flights enroute to Austin.  
* Takes 1 result from the inner query and joins to get 
* the historical track of the flight. Results 
* should update if flight is in progress.
*/

select e.ident,
    filed_departuretime,
    departureTime,
    origen,
    destination,
    h.timestamp,
    h.altitude,
    h.groundspeed,
    h.latitude,
    h.longitude
from (
    select ident, filed_departuretime
    from (
        select ident, filed_departuretime
        from enroute e
        where airport = 'kaus' and actualdeparturetime != 0 and filter = 'airline'
    ) e
    limit 1
) e
join inflightinfo i on i.ident = e.ident
join gethistoricaltrack h on h.faFlightID = i.faFlightID