/**
 * SQL CTE resolving the bookings riding a train schedule, as `sched_bookings
 * (schedule_id, booking_id)`. Use as: `WITH ${SCHEDULE_BOOKINGS_CTE} SELECT ...`.
 *
 * A booking reaches a train through WAGON ALLOCATION
 * (train_schedules -> train_sets -> train_set_wagons -> wagon_booking_allocations),
 * which is what the allocation UI writes. `train_schedule_bookings` is only ever
 * written by the demo seeders, so both sources are unioned: real allocations work
 * and the seeded scenarios keep working.
 *
 * Shared so the warehouse loading queue and the train dispatch guard agree on
 * exactly which bookings are on a train — if they drift, a train can be
 * dispatched leaving cargo the warehouse still thinks it should load.
 */
export const SCHEDULE_BOOKINGS_CTE = `
    sched_bookings AS (
      SELECT ts.id AS schedule_id, wba.booking_id
        FROM freight.train_schedules ts
        JOIN freight.train_set_wagons tsw
          ON tsw.train_set_id = ts.train_set_id AND tsw.deleted_at IS NULL
        JOIN freight.wagon_booking_allocations wba
          ON wba.train_set_wagon_id = tsw.id AND wba.deleted_at IS NULL
       WHERE ts.deleted_at IS NULL
      UNION
      SELECT tsb.train_schedule_id, tsb.booking_id
        FROM freight.train_schedule_bookings tsb
       WHERE tsb.deleted_at IS NULL
    )`;
