<?php

/**
 * Backfill tickets.journeydata to the correct passenger boarding day for overnight trips.
 *
 * Finds rows where stored journeydata disagrees with getPassengerBoardingDateForDisplay()
 * (e.g. service-day search stored as boarding: 17 Jun instead of 18 Jun for 01:00 AM pickup).
 * Optionally aligns journeylists.journeydate to the bus run (service) day.
 *
 * Usage (from project root):
 *   php database/scripts/backfill_tickets_overnight_boarding_dates.php --dry-run
 *   php database/scripts/backfill_tickets_overnight_boarding_dates.php --apply
 *   php database/scripts/backfill_tickets_overnight_boarding_dates.php --dry-run --booking=TB8Z0EPD94
 *   php database/scripts/backfill_tickets_overnight_boarding_dates.php --apply --since=2026-06-01
 */

declare(strict_types=1);

$argv = $argv ?? [];
$dryRun  = in_array('--dry-run', $argv, true) || ! in_array('--apply', $argv, true);
$booking = null;
$since   = null;

foreach ($argv as $arg) {
    if (str_starts_with($arg, '--booking=')) {
        $booking = trim(substr($arg, strlen('--booking=')));
    }
    if (str_starts_with($arg, '--since=')) {
        $since = trim(substr($arg, strlen('--since=')));
    }
}

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();

$sql = 'SELECT t.id, t.booking_id, t.trip_id, t.subtrip_id, t.pick_stand_id, t.journeydata,
               t.cancel_status, t.refund, t.created_at,
               jl.id AS jl_id, jl.journeydate AS jl_journeydate
        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.pick_stand_id IS NOT NULL
          AND t.pick_stand_id > 0
          AND t.cancel_status = 0
          AND t.refund = 0';

$params = [];
if ($booking !== null && $booking !== '') {
    $sql .= ' AND t.booking_id = ?';
    $params[] = $booking;
}
if ($since !== null && $since !== '') {
    $sql .= ' AND DATE(t.created_at) >= ?';
    $params[] = $since;
}

$sql .= ' ORDER BY t.id ASC';

$rows = $params === []
    ? $db->query($sql)->getResult()
    : $db->query($sql, $params)->getResult();

$seenBookings   = [];
$ticketUpdates  = 0;
$jlUpdates      = 0;
$skipped        = 0;

foreach ($rows as $row) {
    $bookingId = (string) ($row->booking_id ?? '');
    if ($bookingId === '' || isset($seenBookings[$bookingId])) {
        continue;
    }
    $seenBookings[$bookingId] = true;

    $tripId    = (int) ($row->trip_id ?? 0);
    $subtripId = (int) ($row->subtrip_id ?? 0);
    $pickStand = (int) ($row->pick_stand_id ?? 0);
    $stored    = \App\Libraries\BookingInventorySupport::normalizeCalendarDate((string) ($row->journeydata ?? ''));

    if ($stored === '1970-01-01' || $tripId <= 0 || $pickStand <= 0) {
        $skipped++;
        continue;
    }

    $manifest = null;
    if ($row->jl_journeydate !== null && trim((string) $row->jl_journeydate) !== '') {
        $manifest = \App\Libraries\BookingInventorySupport::normalizeCalendarDate((string) $row->jl_journeydate);
        if ($manifest === '1970-01-01') {
            $manifest = null;
        }
    }

    $correctBoarding = $helper->getPassengerBoardingDateForDisplay(
        $tripId,
        $subtripId > 0 ? $subtripId : 0,
        $stored,
        $pickStand,
        $manifest
    );

    if ($correctBoarding === '1970-01-01' || $correctBoarding === $stored) {
        continue;
    }

    $correctManifest = \App\Libraries\BookingInventorySupport::busRunForBooking(
        $tripId,
        $subtripId,
        $correctBoarding,
        $pickStand
    );

    echo sprintf(
        "%s ticket id=%d booking=%s pick=%d boarding %s -> %s",
        $dryRun ? '[dry-run]' : '[apply]',
        (int) $row->id,
        $bookingId,
        $pickStand,
        $stored,
        $correctBoarding
    );

    if ($manifest !== null && $manifest !== $correctManifest) {
        echo sprintf(' | manifest %s -> %s', $manifest, $correctManifest);
    }
    echo PHP_EOL;

    if (! $dryRun) {
        $db->query(
            'UPDATE tickets SET journeydata = ? WHERE id = ?',
            [$correctBoarding, (int) $row->id]
        );
        $ticketUpdates++;

        if ($row->jl_id !== null && $manifest !== null && $manifest !== $correctManifest) {
            $db->query(
                'UPDATE journeylists SET journeydate = ? WHERE id = ?',
                [$correctManifest, (int) $row->jl_id]
            );
            $jlUpdates++;
        }
    } else {
        $ticketUpdates++;
        if ($manifest !== null && $manifest !== $correctManifest) {
            $jlUpdates++;
        }
    }
}

echo PHP_EOL . sprintf(
    "Done. %s ticket_candidates=%d journeylist_candidates=%d skipped=%d\n",
    $dryRun ? 'would_update' : 'updated',
    $ticketUpdates,
    $jlUpdates,
    $skipped
);

if ($dryRun) {
    echo "Re-run with --apply to persist changes.\n";
    echo "After apply, optionally run: php database/scripts/backfill_journeylists_service_dates.php --apply\n";
}
