-- Trip 61 — journey list manifest 2026-07-02 (bus run / main trip evening 2 July)
-- Run in phpMyAdmin on LIVE after backup.
--
-- Safari.pdf issue:
--   TBE285MA0Z  midnight boarding 2 Jul → bus run 1 Jul  → REMOVE from 2 Jul list
--   TB8IFV6793  midnight boarding 3 Jul → bus run 2 Jul  → KEEP on 2 Jul list
--
-- Verify BEFORE:
SELECT
    t.booking_id,
    pd.time AS pick_time,
    DATE(t.journeydata) AS ticket_boarding,
    DATE(jl.journeydate) AS jl_date,
    CASE
        WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM')
            THEN DATE_SUB(DATE(t.journeydata), INTERVAL 1 DAY)
        ELSE DATE(t.journeydata)
    END AS expected_bus_run_manifest
FROM tickets t
JOIN journeylists jl ON jl.booking_id = t.booking_id AND jl.trip_id = 61 AND jl.deleted_at IS NULL
LEFT JOIN pickdrops pd ON pd.id = t.pick_stand_id
WHERE t.booking_id IN ('TBE285MA0Z', 'TB8IFV6793')
  AND t.cancel_status = 0
GROUP BY t.booking_id;

-- =============================================================================
-- FIX
-- =============================================================================

-- TBE285MA0Z: manifest = bus run 1 July (midnight Ismailia 2 July)
UPDATE journeylists
SET journeydate = '2026-07-01'
WHERE trip_id = 61
  AND booking_id = 'TBE285MA0Z'
  AND deleted_at IS NULL;

-- tickets.journeydata stays 2026-07-02 (boarding day) — do NOT change

-- TB8IFV6793: jl should store bus run 2 July (boarding 3 July at 12:30 AM)
UPDATE journeylists
SET journeydate = '2026-07-02'
WHERE trip_id = 61
  AND booking_id = 'TB8IFV6793'
  AND deleted_at IS NULL;

-- tickets.journeydata stays 2026-07-03 (boarding day) — do NOT change

-- Verify AFTER (expected):
-- TBE285MA0Z: ticket_boarding=2026-07-02, jl_date=2026-07-01, expected_bus_run=2026-07-01
-- TB8IFV6793: ticket_boarding=2026-07-03, jl_date=2026-07-02, expected_bus_run=2026-07-02

SELECT
    t.booking_id,
    pd.time AS pick_time,
    DATE(t.journeydata) AS ticket_boarding,
    DATE(jl.journeydate) AS jl_date,
    CASE
        WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM')
            THEN DATE_SUB(DATE(t.journeydata), INTERVAL 1 DAY)
        ELSE DATE(t.journeydata)
    END AS expected_bus_run_manifest
FROM tickets t
JOIN journeylists jl ON jl.booking_id = t.booking_id AND jl.trip_id = 61 AND jl.deleted_at IS NULL
LEFT JOIN pickdrops pd ON pd.id = t.pick_stand_id
WHERE t.booking_id IN ('TBE285MA0Z', 'TB8IFV6793')
  AND t.cancel_status = 0
GROUP BY t.booking_id;
