-- Trip 61 — journey list manifest 2026-07-05 (bus run day)
-- Expected: TBQROGBM2K (main, Jul 5) + TBUOSVFZ6I (midnight Ismailia, boarding Jul 6)

SELECT
    t.booking_id,
    t.subtrip_id,
    st_pick.name AS from_city,
    st_drop.name AS to_city,
    pd.time AS pick_time,
    DATE(t.journeydata) AS ticket_boarding,
    DATE(jl.journeydate) AS jl_manifest_date,
    CASE
        WHEN pd.time IN ('10:30PM', '11:00PM') THEN DATE(t.journeydata)
        WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM') THEN DATE(DATE_ADD(jl.journeydate, INTERVAL 1 DAY))
        ELSE DATE(t.journeydata)
    END AS expected_boarding_display
FROM tickets t
JOIN journeylists jl ON jl.booking_id = t.booking_id AND jl.trip_id = t.trip_id AND jl.deleted_at IS NULL
LEFT JOIN pickdrops pd ON pd.id = t.pick_stand_id
LEFT JOIN stands st_pick ON st_pick.id = pd.stand_id
LEFT JOIN pickdrops pd_drop ON pd_drop.id = t.drop_stand_id
LEFT JOIN stands st_drop ON st_drop.id = pd_drop.stand_id
WHERE t.trip_id = 61
  AND t.booking_id IN ('TBQROGBM2K', 'TBUOSVFZ6I')
  AND t.cancel_status = 0
  AND t.refund = 0;
