-- Trip 117 — Journey list audit for manifest date 2026-11-01 (Safari.pdf case)
-- Run in phpMyAdmin on dev/live DB (read-only)

SET @trip_id       := 117;
SET @manifest_date := '2026-11-01';

SELECT
    t.booking_id,
    t.subtrip_id,
    st_pick.name AS pick_loc,
    pd.time AS pickup_time,
    DATE(t.journeydata) AS ticket_date,
    DATE(jl.journeydate) AS jl_date,
    t.seatnumber,
    CASE
        WHEN DATE(t.journeydata) = @manifest_date THEN 'INCLUDE (01 Nov AA/evening)'
        WHEN DATE(t.journeydata) = DATE_ADD(@manifest_date, INTERVAL 1 DAY) THEN 'INCLUDE (02 Nov midnight)'
        WHEN DATE(t.journeydata) > DATE_ADD(@manifest_date, INTERVAL 1 DAY) THEN 'EXCLUDE (03 Nov+ — wrong run or fix dates)'
        ELSE 'REVIEW'
    END AS expected_on_nov1_manifest
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 locations st_pick ON st_pick.id = t.pick_location_id
WHERE t.trip_id = @trip_id
  AND t.cancel_status = 0
  AND t.refund = 0
  AND DATE(jl.journeydate) IN (
      @manifest_date,
      DATE_ADD(@manifest_date, INTERVAL 1 DAY)
  )
ORDER BY DATE(t.journeydata), t.seatnumber;

-- Safari.pdf bookings (from dev dump 2026-06-30 test)
SELECT 'TBSA1JQNCT' AS booking, '01 Nov ticket' AS note UNION ALL
SELECT 'TBHVJS8D6N', '02 Nov ticket — keep on 01 Nov manifest' UNION ALL
SELECT 'TB3QLUWJAI', '02 Nov ticket — keep on 01 Nov manifest' UNION ALL
SELECT 'TBAHZW2QFC', '03 Nov ticket — REMOVE from 01 Nov manifest (fix to 02 Nov)';
