<?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.
     *
     * @return array<string, mixed>
     */
    public function saveHold(
        int $subtripId,
        int $tripId,
        string $ticketToken,
        string $seatNames,
        string $journeyDateYmd,
        int $holdMinutes
    ): 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';
        $table = $this->qualifiedTable($db);

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

        $db->transStart();

        try {
            $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,
        ];
    }

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