-- =============================================================================
-- RESTORE journeylists + tickets from backup (run in phpMyAdmin if backfill went wrong)
--
-- Requires backup tables from: scripts/backup_overnight_date_fix.sql
--   _backup_journeylists_journeydate_20260603
--   _backup_tickets_journeydata_20260603
-- =============================================================================

-- Preview what will be restored (optional — run this first)
SELECT
    b.booking_id,
    jl.journeydate AS current_jl,
    b.journeydate_before AS restore_jl_to,
    t.journeydata AS current_ticket,
    tb.journeydata_before AS restore_ticket_to
FROM `_backup_journeylists_journeydate_20260603` b
INNER JOIN journeylists jl ON jl.id = b.id
LEFT JOIN `_backup_tickets_journeydata_20260603` tb ON tb.id = (
    SELECT t2.id FROM tickets t2
    WHERE t2.booking_id = b.booking_id AND t2.trip_id = b.trip_id
    LIMIT 1
)
LEFT JOIN tickets t ON t.id = tb.id
WHERE jl.journeydate <> b.journeydate_before
   OR (t.id IS NOT NULL AND t.journeydata <> tb.journeydata_before)
LIMIT 200;

-- Restore journeylists.journeydate
UPDATE journeylists jl
INNER JOIN `_backup_journeylists_journeydate_20260603` b ON b.id = jl.id
SET jl.journeydate = b.journeydate_before;

-- Restore tickets.journeydata
UPDATE tickets t
INNER JOIN `_backup_tickets_journeydata_20260603` b ON b.id = t.id
SET t.journeydata = b.journeydata_before;

SELECT 'Restore complete' AS status,
       (SELECT COUNT(*) FROM `_backup_journeylists_journeydate_20260603`) AS jl_rows_restored,
       (SELECT COUNT(*) FROM `_backup_tickets_journeydata_20260603`) AS ticket_rows_restored;
