-- One-time fixes: journeylists.journeydate = BUS RUN day; tickets.journeydata = BOARDING day.
--
-- Run on production after deploying overnight date fixes.
-- For bulk correction of all active rows, prefer:
--   php database/scripts/backfill_journeylists_service_dates.php --dry-run
--   php database/scripts/backfill_journeylists_service_dates.php --apply

-- Trip 75 / subtrip 404 midnight (Aug 2026): list was boarding day
UPDATE journeylists jl
INNER JOIN tickets t ON t.booking_id = jl.booking_id AND t.trip_id = jl.trip_id
SET jl.journeydate = '2026-08-02'
WHERE jl.trip_id = 75
  AND jl.journeydate = '2026-08-03'
  AND DATE(t.journeydata) = '2026-08-03'
  AND jl.booking_id IN ('TBDGXIBWVS', 'TB8MYU5QS2');

-- TB1A236ULR (trip 75 / subtrip 238, 12:45 AM on 2026-06-15 bus run)
UPDATE tickets
SET journeydata = '2026-06-16 00:00:00'
WHERE booking_id = 'TB1A236ULR'
  AND trip_id = 75
  AND DATE(journeydata) = '2026-06-15';

UPDATE journeylists
SET journeydate = '2026-06-15'
WHERE booking_id = 'TB1A236ULR'
  AND trip_id = 75
  AND journeydate = '2026-06-14';

-- TBRLPUHAOS (same run / seat 14 — journeydata must be boarding day)
UPDATE tickets
SET journeydata = '2026-06-16 00:00:00'
WHERE booking_id = 'TBRLPUHAOS'
  AND trip_id = 75
  AND DATE(journeydata) = '2026-06-15';

-- TBX1SO6Q0G (trip 75 / subtrip 238, 01:00 AM on 2026-06-18 bus run)
UPDATE tickets
SET journeydata = '2026-06-19 00:00:00'
WHERE booking_id = 'TBX1SO6Q0G'
  AND trip_id = 75
  AND DATE(journeydata) = '2026-06-18';

-- TBQM8AIB4W (trip 75 / subtrip 404 Ras Sudr→El Tor, search 2026-08-17): manifest = bus run 16th
UPDATE journeylists
SET journeydate = '2026-08-16'
WHERE booking_id = 'TBQM8AIB4W'
  AND trip_id = 75
  AND journeydate = '2026-08-17';

-- TBJC5SKYOZ (trip 75 / subtrip 238, pick 8184 01:15 AM): duplicate seat 2 with TB30JAE7YF on Aug 16 manifest.
-- Cancelled/refunded; journeylists had boarding day (16th) instead of bus run (15th).
UPDATE tickets
SET refund = '1'
WHERE booking_id = 'TBJC5SKYOZ'
  AND trip_id = 75
  AND cancel_status = '1';

UPDATE journeylists
SET journeydate = '2026-08-15'
WHERE booking_id = 'TBJC5SKYOZ'
  AND trip_id = 75
  AND journeydate = '2026-08-16';

-- TB30JAE7YF (trip 75 / subtrip 238, pick 8178 11:30 PM): valid booking — boarding 16th, manifest = bus run 16th.
UPDATE journeylists
SET journeydate = '2026-08-16'
WHERE booking_id = 'TB30JAE7YF'
  AND trip_id = 75
  AND pick_stand_id = 8178
  AND journeydate <> '2026-08-16'
  AND deleted_at IS NULL;
