-- =============================================================================
-- TRIP 61 — ONE-TIME live fix (backup + Jul 1 + Jul 2 manifest dates)
--
-- Run ONCE in phpMyAdmin on LIVE after full DB export backup.
-- Requires: latest TripJourneyDateHelper.php + Journeylist.php on server.
--
-- Rules:
--   tickets.journeydata     = passenger BOARDING day (midnight = next calendar day)
--   journeylists.journeydate = bus RUN / manifest day (evening of that date)
--
-- Restore if needed (see bottom of file).
-- =============================================================================

-- -----------------------------------------------------------------------------
-- PART 1 — BACKUP affected Trip 61 rows only
-- -----------------------------------------------------------------------------

DROP TABLE IF EXISTS `_backup_trip61_jl_20260702`;
DROP TABLE IF EXISTS `_backup_trip61_tickets_20260702`;

CREATE TABLE `_backup_trip61_jl_20260702` AS
SELECT jl.id, jl.booking_id, jl.trip_id, jl.journeydate AS journeydate_before, NOW() AS backed_up_at
FROM journeylists jl
WHERE jl.trip_id = 61 AND jl.deleted_at IS NULL;

CREATE TABLE `_backup_trip61_tickets_20260702` AS
SELECT t.id, t.booking_id, t.trip_id, t.journeydata AS journeydata_before, NOW() AS backed_up_at
FROM tickets t
WHERE t.trip_id = 61 AND t.deleted_at IS NULL AND t.cancel_status = 0 AND t.refund = 0;

SELECT 'backup journeylists trip 61' AS step, COUNT(*) AS rows_cnt FROM `_backup_trip61_jl_20260702`
UNION ALL
SELECT 'backup tickets trip 61', COUNT(*) FROM `_backup_trip61_tickets_20260702`;

-- -----------------------------------------------------------------------------
-- PART 2 — BEFORE (all known problem bookings)
-- -----------------------------------------------------------------------------

SELECT 'BEFORE FIX' AS step, t.booking_id, pd.time AS pick_time,
       DATE(t.journeydata) AS ticket_boarding,
       DATE(jl.journeydate) AS jl_date
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.trip_id = 61
  AND t.booking_id IN (
    'TB28T6XAHR', 'TBE285MA0Z', 'TB1S90EDAU', 'TBFH4TEPU7', 'TBM3GX4EKH',
    'TBNWFT8PIB', 'TBHW6FJAG5', 'TBHIV3CZBU', 'TB53NQCFPR',
    'TB2TMLWQDK', 'TBK4H2GEUZ', 'TB6IWY8HM4',
    'TB8IFV6793', 'TBQROGBM2K', 'TBUOSVFZ6I'
  )
  AND t.cancel_status = 0
GROUP BY t.booking_id, pd.time, t.journeydata, jl.journeydate
ORDER BY t.booking_id;

-- -----------------------------------------------------------------------------
-- PART 3 — FIX July 1 manifest (bus run 1 Jul, midnight boarding 2 Jul)
-- -----------------------------------------------------------------------------

-- Midnight pickups: passenger date must be 2 July
UPDATE tickets
SET journeydata = '2026-07-02 00:00:00', updated_at = NOW()
WHERE trip_id = 61
  AND booking_id IN ('TB2TMLWQDK', 'TB6IWY8HM4', 'TBK4H2GEUZ', 'TBM3GX4EKH')
  AND cancel_status = 0
  AND DATE(journeydata) <> '2026-07-02';

-- Manifest date = bus run 1 July (jl was wrongly 2 July for some rows)
UPDATE journeylists
SET journeydate = '2026-07-01'
WHERE trip_id = 61
  AND booking_id IN ('TB28T6XAHR', 'TBE285MA0Z')
  AND deleted_at IS NULL
  AND DATE(journeydate) <> '2026-07-01';

-- -----------------------------------------------------------------------------
-- PART 4 — FIX July 2 manifest (bus run 2 Jul)
-- -----------------------------------------------------------------------------

-- TBE285MA0Z: midnight 2 Jul boarding → bus run 1 Jul (remove from 2 Jul list)
UPDATE journeylists
SET journeydate = '2026-07-01'
WHERE trip_id = 61
  AND booking_id = 'TBE285MA0Z'
  AND deleted_at IS NULL
  AND DATE(journeydate) <> '2026-07-01';

-- TB8IFV6793: midnight 3 Jul boarding → bus run 2 Jul (keep on 2 Jul list)
UPDATE journeylists
SET journeydate = '2026-07-02'
WHERE trip_id = 61
  AND booking_id = 'TB8IFV6793'
  AND deleted_at IS NULL
  AND DATE(journeydate) <> '2026-07-02';

-- Other Jul 1 manifest rows: jl should be 1 Jul when ticket boarding is 2 Jul (12:30 AM)
UPDATE journeylists jl
INNER JOIN tickets t ON t.booking_id = jl.booking_id AND t.trip_id = jl.trip_id
INNER JOIN pickdrops pd ON pd.id = t.pick_stand_id
SET jl.journeydate = '2026-07-01'
WHERE jl.trip_id = 61
  AND jl.deleted_at IS NULL
  AND t.cancel_status = 0
  AND DATE(t.journeydata) = '2026-07-02'
  AND UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM')
  AND DATE(jl.journeydate) = '2026-07-02'
  AND jl.booking_id NOT IN ('TB28T6XAHR', 'TBE285MA0Z');

-- -----------------------------------------------------------------------------
-- PART 5 — AFTER verify
-- -----------------------------------------------------------------------------

SELECT 'AFTER FIX' AS step, t.booking_id, pd.time AS pick_time,
       DATE(t.journeydata) AS ticket_boarding,
       DATE(jl.journeydate) AS jl_manifest,
       IF(
           UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM'),
           DATE_SUB(DATE(t.journeydata), INTERVAL 1 DAY),
           DATE(t.journeydata)
       ) AS expected_manifest,
       IF(
           DATE(jl.journeydate) = IF(
               UPPER(TRIM(pd.time)) IN ('12:30AM', '12:00AM', '11:59PM'),
               DATE_SUB(DATE(t.journeydata), INTERVAL 1 DAY),
               DATE(t.journeydata)
           ),
           'OK',
           'CHECK'
       ) AS status
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.trip_id = 61
  AND t.booking_id IN (
    'TB28T6XAHR', 'TBE285MA0Z', 'TB1S90EDAU', 'TBFH4TEPU7', 'TBM3GX4EKH',
    'TBNWFT8PIB', 'TBHW6FJAG5', 'TBHIV3CZBU', 'TB53NQCFPR',
    'TB2TMLWQDK', 'TBK4H2GEUZ', 'TB6IWY8HM4',
    'TB8IFV6793', 'TBQROGBM2K', 'TBUOSVFZ6I'
  )
  AND t.cancel_status = 0
GROUP BY t.booking_id, pd.time, t.journeydata, jl.journeydate
ORDER BY t.booking_id;

-- Expected journey list counts (after PHP code deployed):
--   Search 01-07-2026 → includes TBE285MA0Z (midnight 2 Jul), NOT TB8IFV6793
--   Search 02-07-2026 → includes TB8IFV6793 (midnight 3 Jul), NOT TBE285MA0Z
--   Search 05-07-2026 → includes TBQROGBM2K + TBUOSVFZ6I

-- -----------------------------------------------------------------------------
-- RESTORE (only if something went wrong — run manually)
-- -----------------------------------------------------------------------------
-- UPDATE journeylists jl
-- INNER JOIN `_backup_trip61_jl_20260702` b ON b.id = jl.id
-- SET jl.journeydate = b.journeydate_before;
--
-- UPDATE tickets t
-- INNER JOIN `_backup_trip61_tickets_20260702` b ON b.id = t.id
-- SET t.journeydata = b.journeydata_before;
