<?php

/**
 * Audit which trip 61 bookings belong on the 30 Jun 2026 journey list (bus run day),
 * before vs after journeylists/tickets backfill.
 *
 * Usage (from project root):
 *   php database/scripts/audit_trip61_june30_manifest.php
 *   php database/scripts/audit_trip61_june30_manifest.php --manifest=2026-06-30 --trip=61
 *   php database/scripts/audit_trip61_june30_manifest.php --csv > /tmp/trip61-jun30-audit.csv
 */

declare(strict_types=1);

$manifestDate = '2026-06-30';
$tripId       = 61;

foreach ($argv ?? [] as $arg) {
    if (str_starts_with($arg, '--manifest=')) {
        $manifestDate = substr($arg, 11);
    }
    if (str_starts_with($arg, '--trip=')) {
        $tripId = (int) substr($arg, 7);
    }
}

$csvOutput = in_array('--csv', $argv ?? [], true);

define('FCPATH', dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'public' . DIRECTORY_SEPARATOR);

require dirname(__DIR__, 2) . '/vendor/autoload.php';

$pathsConfig = new Config\Paths();
require rtrim($pathsConfig->systemDirectory, '\\/ ') . '/bootstrap.php';

$app = Config\Services::codeigniter();
$app->initialize();

$db     = \Config\Database::connect();
$helper = new \App\Libraries\TripJourneyDateHelper();

\App\Libraries\BookingInventorySupport::syncBookingTimezone();

$manifestDate = date('Y-m-d', strtotime($manifestDate));

$rows = $db->query(
    'SELECT jl.id AS jl_id,
            jl.booking_id,
            jl.subtrip_id,
            jl.pick_stand_id,
            DATE(jl.journeydate) AS jl_date,
            DATE(t.journeydata) AS ticket_date,
            t.seatnumber,
            pd.time AS pick_time,
            s.name AS pick_stand_name
     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 s ON s.id = pd.stand_id
     WHERE jl.trip_id = ?
       AND jl.deleted_at IS NULL
       AND (
            DATE(jl.journeydate) BETWEEN DATE_SUB(?, INTERVAL 1 DAY) AND DATE_ADD(?, INTERVAL 2 DAY)
         OR DATE(t.journeydata) BETWEEN DATE_SUB(?, INTERVAL 1 DAY) AND DATE_ADD(?, INTERVAL 2 DAY)
       )
     ORDER BY jl.booking_id, jl.id',
    [$tripId, $manifestDate, $manifestDate, $manifestDate, $manifestDate]
)->getResult();

$byBooking = [];

foreach ($rows as $row) {
    $bookingId = (string) ($row->booking_id ?? '');
    if ($bookingId === '') {
        continue;
    }

    $subtripId   = (int) ($row->subtrip_id ?? 0);
    $pickStandId = (int) ($row->pick_stand_id ?? 0);
    $jlDate      = (string) ($row->jl_date ?? '');
    $ticketDate  = (string) ($row->ticket_date ?? '');
    $ticketJourney = $ticketDate !== '' ? $ticketDate . ' 00:00:00' : null;

    $matchesBefore = $helper->rowMatchesJourneyListManifestSearch(
        $tripId,
        $subtripId,
        $jlDate,
        $pickStandId,
        $manifestDate,
        $ticketJourney
    );

    $busRunAfterJl = \App\Libraries\BookingInventorySupport::busRunForBooking(
        $tripId,
        $subtripId,
        $ticketDate !== '' ? $ticketDate : $jlDate,
        $pickStandId
    );

    $boardingAfter = $helper->getBoardingDateFromBusStartDate(
        $tripId,
        $subtripId,
        $busRunAfterJl,
        $pickStandId
    );

    $matchesAfter = $busRunAfterJl === $manifestDate;

  $expectedBoarding = $helper->getBoardingDateFromBusStartDate(
        $tripId,
        $subtripId,
        $manifestDate,
        $pickStandId
    );

    if (! isset($byBooking[$bookingId])) {
        $byBooking[$bookingId] = [
            'booking_id'        => $bookingId,
            'jl_date'           => $jlDate,
            'ticket_date'       => $ticketDate,
            'pick_stand_id'     => $pickStandId,
            'pick_time'         => (string) ($row->pick_time ?? ''),
            'pick_stand_name'   => (string) ($row->pick_stand_name ?? ''),
            'seats'             => (string) ($row->seatnumber ?? ''),
            'expected_boarding' => $expectedBoarding,
            'bus_run_after'     => $busRunAfterJl,
            'boarding_after'    => $boardingAfter,
            'jl_after'          => $busRunAfterJl,
            'ticket_after'      => $boardingAfter,
            'on_manifest_before'=> $matchesBefore,
            'on_manifest_after' => $matchesAfter,
        ];
    } else {
        if ($matchesBefore) {
            $byBooking[$bookingId]['on_manifest_before'] = true;
        }
        if ($matchesAfter) {
            $byBooking[$bookingId]['on_manifest_after'] = true;
        }
    }
}

usort($byBooking, static function (array $a, array $b): int {
    return [$a['on_manifest_after'], $a['booking_id']] <=> [$b['on_manifest_after'], $b['booking_id']];
});

if ($csvOutput) {
    $out = fopen('php://output', 'w');
    fputcsv($out, [
        'booking_id', 'jl_date_now', 'ticket_date_now', 'pick_stand_id', 'pick_time',
        'expected_boarding_on_manifest', 'bus_run_after_backfill', 'boarding_after_backfill',
        'on_manifest_before', 'on_manifest_after', 'action',
    ]);
    foreach ($byBooking as $r) {
        $action = 'KEEP';
        if ($r['on_manifest_after'] && ! $r['on_manifest_before']) {
            $action = 'ADD';
        } elseif ($r['on_manifest_before'] && ! $r['on_manifest_after']) {
            $action = 'REMOVE';
        } elseif (! $r['on_manifest_before'] && ! $r['on_manifest_after']) {
            $action = 'SKIP';
        }
        fputcsv($out, [
            $r['booking_id'],
            $r['jl_date'],
            $r['ticket_date'],
            $r['pick_stand_id'],
            $r['pick_time'],
            $r['expected_boarding'],
            $r['bus_run_after'],
            $r['boarding_after'],
            $r['on_manifest_before'] ? 'YES' : 'NO',
            $r['on_manifest_after'] ? 'YES' : 'NO',
            $action,
        ]);
    }
    fclose($out);
    exit(0);
}

$add = $remove = $keep = $skip = 0;

echo "Trip {$tripId} — manifest (bus run) date: {$manifestDate}\n";
echo str_repeat('=', 100) . "\n";
printf(
    "%-14s %-12s %-12s %-6s %-10s %-12s %-12s %-8s %-8s %s\n",
    'booking_id',
    'jl_now',
    'ticket_now',
    'pick',
    'pick_time',
    'bus_run_new',
    'boarding_new',
    'before',
    'after',
    'action'
);
echo str_repeat('-', 100) . "\n";

foreach ($byBooking as $r) {
    $action = 'KEEP';
    if ($r['on_manifest_after'] && ! $r['on_manifest_before']) {
        $action = 'ADD';
        $add++;
    } elseif ($r['on_manifest_before'] && ! $r['on_manifest_after']) {
        $action = 'REMOVE';
        $remove++;
    } elseif ($r['on_manifest_before'] && $r['on_manifest_after']) {
        $keep++;
    } else {
        $action = 'SKIP';
        $skip++;
    }

    printf(
        "%-14s %-12s %-12s %-6d %-10s %-12s %-12s %-8s %-8s %s\n",
        $r['booking_id'],
        $r['jl_date'],
        $r['ticket_date'],
        $r['pick_stand_id'],
        $r['pick_time'],
        $r['bus_run_after'],
        $r['boarding_after'],
        $r['on_manifest_before'] ? 'YES' : 'NO',
        $r['on_manifest_after'] ? 'YES' : 'NO',
        $action
    );
}

echo str_repeat('=', 100) . "\n";
echo "Summary: KEEP={$keep} ADD={$add} REMOVE={$remove} SKIP={$skip}\n";
echo "\nRun backfill then re-run this script to verify:\n";
echo "  php database/scripts/backfill_journeylists_service_dates.php --apply --trip={$tripId}\n";
echo "  php database/scripts/fix_legacy_overnight_ticket_journeydata.php --apply --trip={$tripId}\n";
