<?php

namespace App\Libraries;

use CodeIgniter\Database\BaseConnection;
use Config\Database;

/**
 * Reliable writes to temporarybooks (seat holds) using explicit SQL + commit verification.
 */
class TemporaryBookHoldService
{
    public function connection(bool $shared = true): BaseConnection
    {
        BookingInventorySupport::syncBookingTimezone();

        return Database::connect('default', $shared);
    }

    /**
     * Remove holds whose expires_at is in the past (Egypt wall clock via MySQL NOW()).
     *
     * Always deletes every expired row. Do not skip rows by ticket_token — an expired
     * hold must not block the seat map or payment even for the same checkout session.
     */
    public function cleanupExpiredHolds(?BaseConnection $db = null): int
    {
        $db = $db ?? $this->connection(false);
        BookingInventorySupport::syncBookingTimezone();
        $db->query('DELETE FROM temporarybooks WHERE expires_at <= NOW()');

        return (int) $db->affectedRows();
    }

    /**
     * Limit queries to holds that are still active (expires_at strictly after NOW()).
     */
    public static function applyActiveHoldExpiryFilter($builder, string $tableAlias = 'temporarybooks'): void
    {
        BookingInventorySupport::syncBookingTimezone();
        $builder->where($tableAlias . '.expires_at > NOW()', null, false);
    }

    /**
     * Replace the checkout-session hold and return the persisted row metadata.
     *
     * When $inventoryContext is provided, locks the trip row and re-checks segment
     * availability inside the same transaction before INSERT (race-safe holds).
     *
     * @param array{
     *   boarding_search_date: string,
     *   pick_stand_id: int,
     *   pick_location_id?: int|string|null,
     *   drop_location_id?: int|string|null,
     * }|null $inventoryContext
     * @return array<string, mixed>
     */
    public function saveHold(
        int $subtripId,
        int $tripId,
        string $ticketToken,
        string $seatNames,
        string $journeyDateYmd,
        int $holdMinutes,
        ?array $inventoryContext = null
    ): array {
        $db = $this->connection(false);

        $ticketToken = trim($ticketToken);
        if ($ticketToken === '') {
            throw new \RuntimeException('ticket_token is required.');
        }

        if ($holdMinutes < 1) {
            $holdMinutes = 10;
        }

        $subtripRow = $db->query('SELECT id FROM subtrips WHERE id = ? LIMIT 1', [$subtripId])->getRow();
        if ($subtripRow === null) {
            throw new \RuntimeException('Invalid subtrip_id (no matching subtrips row).');
        }

        $nowSql = egypt_now_sql();
        $expiresAt = egypt_add_minutes_sql($holdMinutes);
        if (strtotime($expiresAt) <= strtotime($nowSql)) {
            throw new \RuntimeException('Invalid hold expiry time (check server timezone / maxtimes).');
        }

        $journeyDateStored = BookingInventorySupport::normalizeCalendarDate($journeyDateYmd) . ' 00:00:00';

        BookingInventorySupport::logBookingDateContext('temporarybook_saveHold', [
            'trip_id' => $tripId,
            'subtrip_id' => $subtripId,
            'search_date' => null,
            'pickstand' => null,
            'service_date' => BookingInventorySupport::normalizeCalendarDate($journeyDateYmd),
            'boarding_date' => null,
            'stored_ticket_date' => null,
            'stored_manifest_date' => null,
            'ticket_token' => $ticketToken,
            'seat_names' => $seatNames,
        ]);

        $table = $this->qualifiedTable($db);

        if ($db->transDepth > 0) {
            while ($db->transDepth > 0) {
                $db->transComplete();
            }
        }

        $db->transStart();

        try {
            if ($inventoryContext !== null) {
                BookingInventorySupport::lockTripRowForUpdate($db, $tripId);

                $requestedSeats = array_values(array_filter(array_map('trim', explode(',', $seatNames))));
                if ($requestedSeats !== []) {
                    $inventory = new SegmentSeatInventoryService($db);
                    $unavailable = $inventory->collectUnavailableSeatNumbers(
                        $subtripId,
                        (string) $inventoryContext['boarding_search_date'],
                        (int) ($inventoryContext['pick_stand_id'] ?? 0),
                        $ticketToken,
                        $inventoryContext['pick_location_id'] ?? null,
                        $inventoryContext['drop_location_id'] ?? null
                    );
                    $conflict = array_intersect($requestedSeats, $unavailable);
                    if ($conflict !== []) {
                        throw new \RuntimeException(
                            'Seat(s) ' . implode(',', $conflict) . ' are no longer available on this bus run.'
                        );
                    }
                }
            }

            $db->query(
                "DELETE FROM {$table} WHERE subtrip_id = ? AND ticket_token = ? AND DATE(journey_date) = DATE(?)",
                [$subtripId, $ticketToken, $journeyDateStored]
            );

            $db->query(
                "INSERT INTO {$table} (subtrip_id, trip_id, ticket_token, seat_names, journey_date, expires_at, created_at)
                 VALUES (?, ?, ?, ?, ?, ?, ?)",
                [$subtripId, $tripId, $ticketToken, $seatNames, $journeyDateStored, $expiresAt, $nowSql]
            );

            if ((int) $db->affectedRows() < 1) {
                $err = $db->error();
                $msg = is_array($err) && ! empty($err['message']) ? (string) $err['message'] : 'Insert into temporarybooks failed';
                throw new \RuntimeException($msg);
            }

            $holdId = (int) $db->insertID();
            if ($holdId < 1) {
                throw new \RuntimeException('temporarybooks insert did not return an id.');
            }

            $row = $db->query(
                "SELECT id, subtrip_id, trip_id, ticket_token, seat_names, journey_date, expires_at, created_at
                 FROM {$table} WHERE id = ? LIMIT 1",
                [$holdId]
            )->getRow();

            if ($row === null) {
                throw new \RuntimeException('temporarybooks row missing after insert (id ' . $holdId . ').');
            }

            $db->transComplete();
            if ($db->transStatus() === false) {
                throw new \RuntimeException('Database transaction failed while saving seat hold.');
            }
        } catch (\Throwable $e) {
            if ($db->transDepth > 0) {
                $db->transRollback();
            }
            throw $e;
        }

        $dbMeta = $db->query('SELECT DATABASE() AS db_name, @@hostname AS mysql_host, @@port AS mysql_port')->getRow();
        $activeCount = $db->query(
            "SELECT COUNT(*) AS c FROM {$table} WHERE expires_at >= ?",
            [$nowSql]
        )->getRow();

        $persist = $this->confirmPersistedOnFreshConnection($holdId);

        $payload = [
            'id' => $holdId,
            'expires_at' => (string) $row->expires_at,
            'journey_date' => BookingInventorySupport::normalizeCalendarDate((string) $row->journey_date),
            'seat_names' => (string) $row->seat_names,
            'hold_minutes' => $holdMinutes,
            'db_database' => (string) ($dbMeta->db_name ?? ''),
            'db_hostname' => (string) ($db->hostname ?? ''),
            'mysql_host' => (string) ($dbMeta->mysql_host ?? ''),
            'mysql_port' => (string) ($dbMeta->mysql_port ?? ''),
            'server_time_egypt' => $nowSql,
            'active_holds_in_db' => (int) ($activeCount->c ?? 0),
            'persisted_committed' => $persist['found'],
            'persist_verify_connection_id' => $persist['connection_id'] ?? null,
        ];

        $this->logHold(array_merge($payload, ['action' => 'saveHold']));

        if (! $persist['found']) {
            throw new \RuntimeException(
                'Hold id ' . $holdId . ' is not visible on a new database connection (not committed). '
                . 'Database: ' . ($dbMeta->db_name ?? 'unknown') . '. '
                . 'Check writable/logs/seat_holds.log and phpMyAdmin host/database.'
            );
        }

        return $payload;
    }

    /**
     * Read hold using a brand-new connection (proves the row is committed for phpMyAdmin too).
     *
     * @return array<string, mixed>
     */
    public function confirmPersistedOnFreshConnection(int $holdId): array
    {
        $db = $this->connection(false);
        $table = $this->qualifiedTable($db);

        $meta = $db->query(
            'SELECT DATABASE() AS db_name, @@hostname AS mysql_host, @@port AS mysql_port, CONNECTION_ID() AS connection_id'
        )->getRow();

        $row = $db->query("SELECT * FROM {$table} WHERE id = ? LIMIT 1", [$holdId])->getRow();

        return [
            'found' => $row !== null,
            'hold_id' => $holdId,
            'db_database' => (string) ($meta->db_name ?? ''),
            'mysql_host' => (string) ($meta->mysql_host ?? ''),
            'mysql_port' => (string) ($meta->mysql_port ?? ''),
            'connection_id' => (int) ($meta->connection_id ?? 0),
            'row' => $row ? [
                'id' => (int) $row->id,
                'subtrip_id' => (int) $row->subtrip_id,
                'ticket_token' => (string) $row->ticket_token,
                'seat_names' => (string) $row->seat_names,
                'journey_date' => (string) $row->journey_date,
                'expires_at' => (string) $row->expires_at,
                'created_at' => (string) ($row->created_at ?? ''),
            ] : null,
        ];
    }

    /**
     * Normalize seat_names for comparison (single row may store "11,3" or "11, 3").
     */
    public static function normalizeSeatNamesForMatch(string $seatNames): string
    {
        $parts = array_values(array_filter(array_map('trim', explode(',', $seatNames)), static fn ($s) => $s !== ''));

        return implode(',', $parts);
    }

    /**
     * Release a seat hold. Priority: temporary_book_id, then ticket_token + subtrip_id, then seat_names + subtrip_id.
     *
     * @return int Rows deleted (0 = no matching hold).
     */
    public function releaseHold(
        ?int $temporaryBookId = null,
        ?int $subtripId = null,
        ?string $ticketToken = null,
        ?string $seatNames = null,
        ?string $journeyDateYmd = null,
        int $pickStandId = 0
    ): int {
        $db = $this->connection(false);
        $table = $this->qualifiedTable($db);

        if ($temporaryBookId !== null && $temporaryBookId > 0) {
            $db->query("DELETE FROM {$table} WHERE id = ?", [$temporaryBookId]);

            return (int) $db->affectedRows();
        }

        if ($subtripId === null || $subtripId < 1) {
            return 0;
        }

        $ticketToken = trim((string) $ticketToken);
        $seatNames = self::normalizeSeatNamesForMatch(trim((string) $seatNames));
        $journeyStored = $this->resolveStoredJourneyDate($subtripId, $journeyDateYmd, $pickStandId);

        if ($ticketToken !== '') {
            if ($journeyStored !== null) {
                $db->query(
                    "DELETE FROM {$table} WHERE subtrip_id = ? AND ticket_token = ? AND DATE(journey_date) = DATE(?)",
                    [$subtripId, $ticketToken, $journeyStored]
                );
                $deleted = (int) $db->affectedRows();
                if ($deleted > 0) {
                    return $deleted;
                }
            }

            $db->query(
                "DELETE FROM {$table} WHERE subtrip_id = ? AND ticket_token = ?",
                [$subtripId, $ticketToken]
            );

            return (int) $db->affectedRows();
        }

        if ($seatNames === '') {
            return 0;
        }

        $compactSeatNames = str_replace(' ', '', $seatNames);
        if ($journeyStored !== null) {
            $db->query(
                "DELETE FROM {$table} WHERE subtrip_id = ? AND REPLACE(seat_names, ' ', '') = ? AND DATE(journey_date) = DATE(?)",
                [$subtripId, $compactSeatNames, $journeyStored]
            );
            $deleted = (int) $db->affectedRows();
            if ($deleted > 0) {
                return $deleted;
            }
        }

        $db->query(
            "DELETE FROM {$table} WHERE subtrip_id = ? AND REPLACE(seat_names, ' ', '') = ?",
            [$subtripId, $compactSeatNames]
        );

        return (int) $db->affectedRows();
    }

    /**
     * Map client journey/search date to the stored temporarybooks.journey_date (bus run date).
     */
    private function resolveStoredJourneyDate(int $subtripId, ?string $journeyDateYmd, int $pickStandId): ?string
    {
        $raw = trim((string) $journeyDateYmd);
        if ($raw === '') {
            return null;
        }

        $subtripRow = $this->connection(false)
            ->query('SELECT trip_id FROM subtrips WHERE id = ? LIMIT 1', [$subtripId])
            ->getRow();
        if ($subtripRow === null) {
            return BookingInventorySupport::normalizeCalendarDate($raw) . ' 00:00:00';
        }

        $boardingDate = BookingInventorySupport::normalizeCalendarDate($raw);
        $busStartDate = BookingInventorySupport::busRunForBooking(
            (int) $subtripRow->trip_id,
            $subtripId,
            $boardingDate,
            $pickStandId
        );

        return BookingInventorySupport::normalizeCalendarDate($busStartDate) . ' 00:00:00';
    }

    public function verifyHoldRow(int $holdId, string $ticketToken, int $subtripId, ?BaseConnection $db = null): bool
    {
        $db = $db ?? $this->connection(false);
        $table = $this->qualifiedTable($db);

        $row = $db->query(
            "SELECT id FROM {$table} WHERE id = ? AND ticket_token = ? AND subtrip_id = ? LIMIT 1",
            [$holdId, trim($ticketToken), $subtripId]
        )->getRow();

        return $row !== null;
    }

    private function qualifiedTable(BaseConnection $db): string
    {
        $name = trim((string) ($db->database ?? ''));
        if ($name !== '') {
            return '`' . str_replace('`', '``', $name) . '`.`temporarybooks`';
        }

        return 'temporarybooks';
    }

    /**
     * @param array<string, mixed> $data
     */
    private function logHold(array $data): void
    {
        $line = egypt_now_sql() . ' ' . json_encode($data, JSON_UNESCAPED_UNICODE) . PHP_EOL;
        @file_put_contents(WRITEPATH . 'logs/seat_holds.log', $line, FILE_APPEND | LOCK_EX);
    }
}
