-- =============================================================================
-- Trip 61 — Journey list preview for bus run (manifest) date 2026-06-30
-- Run in phpMyAdmin on database: busvall_old_new_merge (or live)
--
-- Sections:
--   1) Pickup offset reference (11 PM vs 12:30 AM)
--   2) All candidate bookings with BEFORE / AFTER manifest match
--   3) Summary counts
--   4) Rows to ADD / REMOVE on 30 Jun list after backfill
--
-- Note: PHP audit is authoritative (uses TripJourneyDateHelper):
--   php database/scripts/audit_trip61_june30_manifest.php
-- =============================================================================

SET @trip_id       := 61;
SET @manifest_date := '2026-06-30';

-- -----------------------------------------------------------------------------
-- 1) Trip 61 pickup stands and day offset (0 = same calendar day as 11 PM bus run)
-- -----------------------------------------------------------------------------
SELECT
    pd.id AS pick_stand_id,
    pd.time AS pick_time,
    st.name AS stand_name,
    CASE
        WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:45AM', '1:00AM', '01:00AM', '1:15AM', '01:15AM')
            THEN 1
        ELSE 0
    END AS pickup_day_offset
FROM pickdrops pd
LEFT JOIN stands st ON st.id = pd.stand_id
WHERE pd.trip_id IN (
    SELECT id FROM subtrips WHERE trip_id = @trip_id
    UNION
    SELECT @trip_id
)
  AND pd.type = 1
  AND pd.deleted_at IS NULL
ORDER BY pickup_day_offset, pd.id;

-- -----------------------------------------------------------------------------
-- 2) Candidate bookings near 30 Jun (prefetch window: manifest .. manifest+1 day)
-- -----------------------------------------------------------------------------
DROP TEMPORARY TABLE IF EXISTS tmp_trip61_jun30_audit;

CREATE TEMPORARY TABLE tmp_trip61_jun30_audit AS
SELECT
    jl.booking_id,
    MIN(jl.id) AS sample_jl_id,
    MIN(DATE(jl.journeydate)) AS jl_date_now,
    MIN(DATE(t.journeydata)) AS ticket_date_now,
    MIN(jl.pick_stand_id) AS pick_stand_id,
    MIN(pd.time) AS pick_time,
    MIN(st.name) AS pick_stand_name,
    MIN(t.seatnumber) AS seats,
    MAX(
        CASE
            WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:45AM', '1:00AM', '01:00AM', '1:15AM', '01:15AM')
                THEN 1
            ELSE 0
        END
    ) AS pickup_day_offset,
    -- Bus run computed from ticket boarding day (after backfill logic)
    DATE_SUB(
        MIN(DATE(t.journeydata)),
        INTERVAL MAX(
            CASE
                WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:45AM', '1:00AM', '01:00AM', '1:15AM', '01:15AM')
                    THEN 1
                ELSE 0
            END
        ) DAY
    ) AS bus_run_after_backfill,
    -- Expected boarding on 30 Jun bus run for this pickup
    DATE_ADD(
        @manifest_date,
        INTERVAL MAX(
            CASE
                WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:45AM', '1:00AM', '01:00AM', '1:15AM', '01:15AM')
                    THEN 1
                ELSE 0
            END
        ) DAY
    ) AS expected_boarding_on_manifest,
    -- Legacy ticket one day late (Jul 2 → true boarding Jul 1)
    DATE_SUB(MIN(DATE(t.journeydata)), INTERVAL 1 DAY) AS ticket_minus_1_day
FROM journeylists jl
INNER JOIN tickets t
    ON t.booking_id = jl.booking_id
   AND t.trip_id = jl.trip_id
   AND t.deleted_at IS NULL
   AND t.cancel_status = 0
   AND t.refund = 0
LEFT JOIN pickdrops pd ON pd.id = jl.pick_stand_id
LEFT JOIN stands st ON st.id = pd.stand_id
WHERE jl.trip_id = @trip_id
  AND jl.deleted_at IS NULL
  AND (
        DATE(jl.journeydate) BETWEEN DATE_SUB(@manifest_date, INTERVAL 1 DAY) AND DATE_ADD(@manifest_date, INTERVAL 2 DAY)
     OR DATE(t.journeydata) BETWEEN DATE_SUB(@manifest_date, INTERVAL 1 DAY) AND DATE_ADD(@manifest_date, INTERVAL 2 DAY)
  )
GROUP BY jl.booking_id;

ALTER TABLE tmp_trip61_jun30_audit
    ADD COLUMN on_manifest_before TINYINT(1) NOT NULL DEFAULT 0 AFTER expected_boarding_on_manifest,
    ADD COLUMN on_manifest_after  TINYINT(1) NOT NULL DEFAULT 0 AFTER on_manifest_before,
    ADD COLUMN audit_action VARCHAR(10) NOT NULL DEFAULT 'SKIP' AFTER on_manifest_after;

-- BEFORE: approximates current journey-list filter (legacy jl + ticket window)
UPDATE tmp_trip61_jun30_audit a
SET on_manifest_before = 1
WHERE
    -- Legacy jl stored boarding day for this bus run (Jul 1 midnight on Jun 30 run)
    a.jl_date_now = a.expected_boarding_on_manifest
    -- Legacy jl one day late (Jul 2)
    OR a.jl_date_now = DATE_ADD(a.expected_boarding_on_manifest, INTERVAL 1 DAY)
    -- Ticket matches expected boarding or legacy late
    OR a.ticket_date_now = a.expected_boarding_on_manifest
    OR a.ticket_date_now = DATE_ADD(a.expected_boarding_on_manifest, INTERVAL 1 DAY)
    -- Canonical jl = bus run day
    OR (
        a.jl_date_now = @manifest_date
        AND (
            a.pickup_day_offset = 0
            OR a.expected_boarding_on_manifest <> a.jl_date_now
        )
    )
    -- Legacy jl = ticket and midnight early storage (ticket/jl = bus run day for offset 1)
    OR (
        a.pickup_day_offset = 1
        AND a.jl_date_now = @manifest_date
        AND a.ticket_date_now = @manifest_date
    );

-- AFTER: jl normalized to bus run day; ticket = boarding day
UPDATE tmp_trip61_jun30_audit a
SET on_manifest_after = IF(a.bus_run_after_backfill = @manifest_date, 1, 0);

UPDATE tmp_trip61_jun30_audit
SET audit_action = CASE
    WHEN on_manifest_after = 1 AND on_manifest_before = 0 THEN 'ADD'
    WHEN on_manifest_before = 1 AND on_manifest_after = 0 THEN 'REMOVE'
    WHEN on_manifest_before = 1 AND on_manifest_after = 1 THEN 'KEEP'
    ELSE 'SKIP'
END;

-- -----------------------------------------------------------------------------
-- 2b) Full audit grid
-- -----------------------------------------------------------------------------
SELECT
    booking_id,
    jl_date_now,
    ticket_date_now,
    pick_stand_id,
    pick_time,
    pick_stand_name,
    seats,
    pickup_day_offset,
    expected_boarding_on_manifest,
    bus_run_after_backfill AS jl_should_be_after,
    expected_boarding_on_manifest AS ticket_should_be_after,
    IF(on_manifest_before, 'YES', 'NO') AS on_list_before,
    IF(on_manifest_after, 'YES', 'NO') AS on_list_after,
    audit_action
FROM tmp_trip61_jun30_audit
ORDER BY
    FIELD(audit_action, 'ADD', 'REMOVE', 'KEEP', 'SKIP'),
    booking_id;

-- -----------------------------------------------------------------------------
-- 3) Summary
-- -----------------------------------------------------------------------------
SELECT
    audit_action,
    COUNT(*) AS booking_count
FROM tmp_trip61_jun30_audit
GROUP BY audit_action
ORDER BY FIELD(audit_action, 'KEEP', 'ADD', 'REMOVE', 'SKIP');

-- -----------------------------------------------------------------------------
-- 4) Should appear on 30 Jun manifest AFTER backfill (target list)
-- -----------------------------------------------------------------------------
SELECT
    booking_id,
    jl_date_now AS jl_before,
    ticket_date_now AS ticket_before,
    bus_run_after_backfill AS jl_after,
    expected_boarding_on_manifest AS ticket_after_expected,
    pick_time,
    seats
FROM tmp_trip61_jun30_audit
WHERE on_manifest_after = 1
ORDER BY ticket_date_now, booking_id;

-- -----------------------------------------------------------------------------
-- 5) Will be REMOVED from 30 Jun list after backfill (were on old list incorrectly)
-- -----------------------------------------------------------------------------
SELECT
    booking_id,
    jl_date_now,
    ticket_date_now,
    bus_run_after_backfill AS correct_bus_run,
    pick_time,
    seats
FROM tmp_trip61_jun30_audit
WHERE audit_action = 'REMOVE'
ORDER BY booking_id;

-- -----------------------------------------------------------------------------
-- 6) Will be ADDED to 30 Jun list after backfill (missing from Safari PDF)
-- -----------------------------------------------------------------------------
SELECT
    booking_id,
    jl_date_now,
    ticket_date_now,
    bus_run_after_backfill AS jl_after,
    expected_boarding_on_manifest AS ticket_after_expected,
    pick_time,
    seats
FROM tmp_trip61_jun30_audit
WHERE audit_action = 'ADD'
ORDER BY booking_id;

DROP TEMPORARY TABLE IF EXISTS tmp_trip61_jun30_audit;
