-- Trip 61 + 3 tickets with journey date 2 July — run in phpMyAdmin
-- Bookings: TBE285MA0Z, TB1S90EDAU, TB28T6XAHR

-- =============================================================================
-- A) When does trip 61 start? (schedule + route)
-- =============================================================================
SELECT
    t.id AS trip_id,
    t.company_name,
    t.journey_hour AS trip_duration_hours,
    s.id AS schedule_id,
    s.start_time AS bus_departs,
    s.end_time AS schedule_end,
    pl.name AS origin,
    dl.name AS destination,
    t.stoppage
FROM trips t
JOIN schedules s ON s.id = t.schedule_id
LEFT JOIN locations pl ON pl.id = t.pick_location_id
LEFT JOIN locations dl ON dl.id = t.drop_location_id
WHERE t.id = 61;

-- =============================================================================
-- B) All pickup stands on trip 61 (when passengers board on the SAME bus run)
--    Bus run day = calendar day of 11:00 PM Port Said departure
-- =============================================================================
SELECT
    pd.id AS pick_stand_id,
    st.name AS stand_name,
    pd.time AS pickup_time,
    sub.id AS subtrip_id,
    sub.type AS subtrip_type,
    CASE
        WHEN UPPER(TRIM(pd.time)) IN ('11:00PM', '10:00PM', '10:30PM', '11:30PM') THEN 0
        WHEN UPPER(TRIM(pd.time)) IN ('12:30AM', '12:45AM', '1:00AM', '01:00AM') THEN 1
        WHEN UPPER(TRIM(pd.time)) IN ('4:30AM', '04:30AM', '6:00AM', '06:00AM', '7:00AM', '07:00AM', '7:30AM', '07:30AM', '8:00AM', '08:00AM', '9:00AM', '09:00AM', '9:30AM', '09:30AM') THEN 1
        ELSE 1
    END AS boarding_day_offset_from_bus_run
FROM pickdrops pd
JOIN stands st ON st.id = pd.stand_id
LEFT JOIN subtrips sub ON sub.id = pd.trip_id
WHERE pd.trip_id IN (
    SELECT id FROM subtrips WHERE trip_id = 61
    UNION SELECT 61
)
  AND pd.type = 1
  AND pd.deleted_at IS NULL
ORDER BY boarding_day_offset_from_bus_run, pd.time, pd.id;

-- =============================================================================
-- C) The 3 tickets — full row from tickets + journeylists
-- =============================================================================
SELECT
    t.booking_id,
    t.trip_id,
    t.subtrip_id,
    t.pick_stand_id,
    pd.time AS pickup_time,
    st.name AS pickup_stand,
    DATE(t.journeydata) AS ticket_journey_date,
    DATE(jl.journeydate) AS jl_journey_date,
    t.seatnumber,
    t.created_at AS ticket_created,
    t.updated_at AS ticket_updated,
  -- If midnight pickup: bus run ≈ ticket date minus 1 day
    DATE_SUB(DATE(t.journeydata), INTERVAL 1 DAY) AS bus_run_if_ticket_is_boarding,
    DATE_SUB(DATE(jl.journeydate), INTERVAL 1 DAY) AS bus_run_if_jl_is_boarding
FROM tickets t
LEFT JOIN journeylists jl ON jl.booking_id = t.booking_id AND jl.trip_id = t.trip_id AND jl.deleted_at IS NULL
LEFT JOIN pickdrops pd ON pd.id = t.pick_stand_id
LEFT JOIN stands st ON st.id = pd.stand_id
WHERE t.booking_id IN ('TBE285MA0Z', 'TB1S90EDAU', 'TB28T6XAHR')
  AND t.trip_id = 61
  AND t.cancel_status = 0
  AND t.refund = 0;

-- =============================================================================
-- D) Client model: ONE bus run starting 30 Jun 2026 — what dates SHOULD be?
--    Manifest (journey list header) = 2026-06-30
--    Boarding at 11 PM Port Said     = 2026-06-30
--    Boarding at 12:30 AM Ismailia   = 2026-07-01
--    (There is NO 12:30 AM on 2 Jul pickup on this route — 2 Jul in DB is usually +1 day error)
-- =============================================================================
SELECT
    t.booking_id,
    DATE(t.journeydata) AS ticket_now,
    DATE(jl.journeydate) AS jl_now,
    '2026-06-30' AS manifest_bus_run_jun30,
    '2026-07-01' AS correct_boarding_midnight_pickup,
    CASE
        WHEN DATE(t.journeydata) = '2026-07-02' THEN 'FIX: ticket 2 Jul → should be 1 Jul for 30 Jun bus run'
        WHEN DATE(t.journeydata) = '2026-07-01' THEN 'OK boarding for 30 Jun bus run'
        WHEN DATE(t.journeydata) = '2026-06-30' THEN 'OK if 11 PM same-day pickup (check pick stand)'
        ELSE 'Review manually'
    END AS ticket_fix_hint,
    CASE
        WHEN DATE(jl.journeydate) = '2026-07-02' THEN 'FIX: jl 2 Jul → should be 30 Jun (bus run day)'
        WHEN DATE(jl.journeydate) = '2026-07-01' THEN 'Legacy jl stored boarding day — backfill → 30 Jun'
        WHEN DATE(jl.journeydate) = '2026-06-30' THEN 'OK or legacy boarding — backfill confirms bus run'
        ELSE 'Review manually'
    END AS jl_fix_hint
FROM tickets t
LEFT JOIN journeylists jl ON jl.booking_id = t.booking_id AND jl.trip_id = t.trip_id AND jl.deleted_at IS NULL
WHERE t.booking_id IN ('TBE285MA0Z', 'TB1S90EDAU', 'TB28T6XAHR')
  AND t.trip_id = 61;
