dozer-config.yaml

app_name: flight-microservices
version: 1
cache_max_map_size: 2147483648
connections:
  - config: !Postgres
      user: postgres
      password: postgres
      host: 0.0.0.0
      port: 5437
      database: flights
    name: bookings_conn
  - config: !Postgres
      user: postgres
      password: postgres
      host: 0.0.0.0
      port: 5437
      database: flights
    name: flights_conn

sql: |
  -- BOOKING DETAILS
  select passenger_id, passenger_name, 
    b.book_ref, book_date, total_amount, 
    t.ticket_no, tf.flight_id, fare_conditions, tf.amount, 
    f.flight_no, f.scheduled_arrival, f.scheduled_departure,  
    f.departure_airport, f.arrival_airport, f.actual_arrival, f.actual_departure
  into bookings_details
  from b_bookings b 
  inner join b_tickets t on t.book_ref = b.book_ref 
  inner join b_ticket_flights tf on tf.ticket_no = t.ticket_no
  inner join f_flights f on tf.flight_id = f.flight_id;

  -- BOOKING SUMMARY
  select passenger_id, passenger_name, 
    b.book_ref, book_date, total_amount, count(tf.ticket_no) as ticket_count, count(f.flight_no) as flight_count, count(passenger_id) as passenger_count
  into bookings_summary
  from b_bookings b 
  inner join b_tickets t on t.book_ref = b.book_ref 
  inner join b_ticket_flights tf on tf.ticket_no = t.ticket_no
  inner join f_flights f on tf.flight_id = f.flight_id
  group by b.book_ref, book_date, total_amount;

  -- FLIGHT ROUTES
  WITH tbl AS (
      SELECT f1.flight_no,
        f1.departure_airport,
        f1.arrival_airport,
        f1.aircraft_code,
        f1.duration,
        f1.days_of_week,
        count(flight_no) as flight_count
      FROM ( 
        SELECT f_flights.flight_no,
          f_flights.departure_airport,
          f_flights.arrival_airport,
          f_flights.aircraft_code,
          (f_flights.scheduled_arrival - f_flights.scheduled_departure) AS duration,
          EXTRACT(DOW FROM f_flights.scheduled_departure) as days_of_week
          FROM f_flights
        ) f1
      GROUP BY f1.flight_no, f1.departure_airport, f1.arrival_airport, f1.aircraft_code, f1.duration, f1.days_of_week        
    )
    SELECT f2.flight_no,
      f2.departure_airport,
      dep.airport_name AS departure_airport_name,
      dep.city AS departure_city,
      f2.arrival_airport,
      arr.airport_name AS arrival_airport_name,
      arr.city AS arrival_city,
      f2.aircraft_code,
      f2.duration,
      f2.days_of_week
    INTO routes  
    FROM tbl f2
      JOIN f_airports dep on f2.departure_airport = dep.airport_code
      JOIN f_airports arr ON f2.arrival_airport = arr.airport_code;

# NOTE: as the table names between the connections are the same,
#       aliasing is needed to avoid ambiguity
sources:
  - name: b_tickets
    table_name: tickets
    connection: bookings_conn

  - name: b_bookings
    table_name: bookings
    connection: bookings_conn

  - name: b_ticket_flights
    table_name: ticket_flights
    connection: bookings_conn

  - name: f_airports
    table_name: airports
    connection: flights_conn

  - name: f_flights
    table_name: flights
    connection: flights_conn

endpoints:
  - name: bookings_details
    path: /bookings/details
    table_name: bookings_details
    index:
      primary_key:
        - book_ref
        - ticket_no
        - flight_no

  - name: bookings_summary
    path: /bookings
    table_name: bookings_summary
    index:
      primary_key:
        - book_ref
        - book_date
        - total_amount

  - name: routes
    path: /routes
    table_name: routes
    index:
      primary_key:
        - flight_no
        - days_of_week