-- PostgreSQL

-- FUNCTIONS
CREATE OR REPLACE FUNCTION f_get_schedule_by_date(my_flight_date DATE)
RETURNS TABLE(
  flight_number INTEGER,
  flight_date DATE,
  dept_country CHAR(2),
  dept_city VARCHAR(20),
  dept_time TIME,
  arrv_country CHAR(2),
  arrv_city VARCHAR(20),
  arrv_time TIME
)
AS $$
  SELECT
    f.flight_number,
    f.flight_date,
    s.dept_country,
    s.dept_city,
    s.dept_time,
    s.arrv_country,
    s.arrv_city,
    s.arrv_time
  FROM
    flights f
  INNER JOIN
    schedules s ON (f.flight_number = s.flight_number)
  WHERE
    f.flight_date = my_flight_date
$$ LANGUAGE SQL;

SELECT *
FROM f_get_schedule_by_date('2018-01-14')

SELECT *
FROM f_get_schedule_by_date('2012-05-23')


CREATE OR REPLACE FUNCTION f_get_passengers_between_dates(my_airline_code CHAR(2), start_date DATE, end_date DATE)
RETURNS BIGINT
AS $$
  SELECT
    COALESCE(SUM(occ_econ_cap + occ_buss_cap), 0)
  FROM
    flights
  WHERE
    airline_code = my_airline_code AND
    start_date <= flight_date AND
    end_date >= flight_date
$$ LANGUAGE SQL;

SELECT *
FROM f_get_passengers_between_dates('AA', '2017-12-14', '2018-01-14');


CREATE OR REPLACE FUNCTION f_get_begin_date()
RETURNS DATE
AS $$
  SELECT (date_trunc('month', now()) - interval '1 month')::DATE
$$ LANGUAGE SQL;

SELECT f_get_begin_date()


CREATE OR REPLACE FUNCTION f_get_end_date()
RETURNS DATE
AS $$
  SELECT (date_trunc('month', now()) - interval '1 day')::DATE
$$ LANGUAGE SQL;

SELECT f_get_end_date()


-- VIEW
CREATE OR REPLACE VIEW v_Schedule_Since_Last_Month AS
  SELECT
    a.name,
    s.*,
    f_get_passengers_between_dates(a.code, f_get_begin_date(), f_get_end_date()) AS passengers_last_month
  FROM
    airlines a
  INNER JOIN
    flights f ON (a.code = f.airline_code)
  INNER JOIN
    f_get_schedule_by_date(now()::DATE) s ON (f.flight_number = s.flight_number);

SELECT *
FROM v_Schedule_Since_Last_Month;