<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Db;

use Closure;
use JTL\DB\DbInterface;
use PDO;
use PDOStatement;
use Throwable;

/**
 * Layer E: the one connection `run_sql_read` executes on, and the last thing between the caller
 * and the database.
 *
 * There is nothing to configure. The gate opens its **own** PDO with the shop's own credentials —
 * never the shared request connection — so it can set the three things the shop's `NiceDB` does
 * not (includes/src/DB/NiceDB.php:89-112 sets neither of the first two):
 *  - `MYSQL_ATTR_MULTI_STATEMENTS => false`, so stacked queries are impossible at the driver
 *  - a session statement timeout, which is safe here precisely because the session is ours alone
 *  - `START TRANSACTION READ ONLY` around every query, so the server itself refuses a write
 *
 * On top of that {@see read()} re-checks the statement it was handed: exactly one statement, and
 * it must open with SELECT or WITH. That check does not trust {@see SqlReadGuard} having run —
 * it is the same guarantee stated at the connection, where it cannot be bypassed by a future
 * caller that forgets the parser.
 *
 * Nothing connects until {@see read()} is called: `ToolFactory` must stay side-effect-free.
 */
final class ReadGate
{
    /** Reported in every `run_sql_read` response; there is only ever this one mode. */
    public const MODE = 'readonly-transaction';

    public const DEFAULT_TIMEOUT_SECONDS = 5;
    public const MAX_TIMEOUT_SECONDS     = 30;

    private ?PDO $pdo = null;

    /** @var list<string> */
    private array $warnings = [];

    /**
     * @param Closure(): DbInterface $primary the shop's own connection, resolved lazily and used
     *        only for its credentials — its PDO is never executed on
     * @param Closure(string, string, string, array<int, mixed>): PDO|null $pdoFactory test seam;
     *        production passes null and a real PDO is built
     */
    public function __construct(
        private readonly Closure $primary,
        private readonly int $timeoutSeconds = self::DEFAULT_TIMEOUT_SECONDS,
        private readonly ?Closure $pdoFactory = null,
    ) {
    }

    public function mode(): string
    {
        return self::MODE;
    }

    /**
     * Caller-facing notes produced while connecting, e.g. a database server with no statement
     * timeout at all. Meaningful only after {@see read()}.
     *
     * @return list<string>
     */
    public function warnings(): array
    {
        return $this->warnings;
    }

    /**
     * Runs one SELECT and hands the live statement to $consumer, which must fetch everything it
     * needs before returning — the statement is closed, and the read-only transaction rolled
     * back, on the way out.
     *
     * @template T
     * @param callable(PDOStatement): T $consumer
     * @return T
     */
    public function read(string $sql, callable $consumer): mixed
    {
        $this->assertSingleSelect($sql);
        $pdo = $this->pdo();

        // MySQL/MariaDB accept the READ ONLY characteristic only on an explicit START
        // TRANSACTION, not through PDO::beginTransaction().
        $pdo->exec('START TRANSACTION READ ONLY');
        try {
            $stmt = $pdo->query($sql);
            if ($stmt === false) {
                throw new ReadFailed('The database refused the query.');
            }

            try {
                return $consumer($stmt);
            } finally {
                $stmt->closeCursor();
            }
        } finally {
            try {
                // Not PDO::rollBack(): the transaction was started with exec(), so PDO does not
                // consider itself in one and rollBack() would throw.
                $pdo->exec('ROLLBACK');
            } catch (Throwable) {
                // A transaction the server already rolled back (timeout, killed query) is fine —
                // nothing was written either way. Never let cleanup mask the real error.
            }
        }
    }

    /**
     * Exactly one statement, opening with SELECT or WITH. String literals, identifiers and
     * comments are blanked out first, so a `;` inside `WHERE cName = 'a;b'` is not mistaken for a
     * second statement and a `-- ` inside a literal cannot hide the rest of the query.
     */
    private function assertSingleSelect(string $sql): void
    {
        $code = \rtrim(self::codeOnly($sql));
        $code = \rtrim($code, ';');
        $code = \trim($code);

        if ($code === '') {
            throw new SqlReadRejected(SqlReadRejected::EMPTY_QUERY, 'The connection was handed an empty query.');
        }
        if (\str_contains($code, ';')) {
            throw new SqlReadRejected(
                SqlReadRejected::STACKED_QUERY,
                'The connection was handed more than one statement; only a single SELECT may run.',
            );
        }
        if (\preg_match('/^\(*\s*(SELECT|WITH)\b/i', $code) !== 1) {
            throw new SqlReadRejected(
                SqlReadRejected::NOT_A_SELECT,
                'The connection was handed a statement that is not a SELECT; nothing else may run on it.',
            );
        }
    }

    /**
     * The statement with every quoted literal, quoted identifier and comment replaced by a space,
     * leaving only the parts that decide *what kind* of statement this is. A dumb scanner on
     * purpose: it must be obvious that it cannot itself be talked into skipping a `;`.
     */
    private static function codeOnly(string $sql): string
    {
        $out    = '';
        $length = \strlen($sql);

        for ($i = 0; $i < $length; $i++) {
            $c    = $sql[$i];
            $next = $sql[$i + 1] ?? '';

            if ($c === '-' && $next === '-') {
                $i = self::skipTo($sql, $i, "\n");
                $out .= ' ';
                continue;
            }
            if ($c === '#') {
                $i = self::skipTo($sql, $i, "\n");
                $out .= ' ';
                continue;
            }
            if ($c === '/' && $next === '*') {
                $end = \strpos($sql, '*/', $i + 2);
                $i   = $end === false ? $length : $end + 1;
                $out .= ' ';
                continue;
            }
            if ($c === "'" || $c === '"' || $c === '`') {
                $i = self::skipQuoted($sql, $i, $c);
                $out .= ' ';
                continue;
            }

            $out .= $c;
        }

        return $out;
    }

    /** Index of the last character consumed (the terminator, or the end of the string). */
    private static function skipTo(string $sql, int $from, string $terminator): int
    {
        $end = \strpos($sql, $terminator, $from);

        return $end === false ? \strlen($sql) : $end;
    }

    /**
     * Index of the closing quote. Handles both MySQL escape forms — a backslash escape and a
     * doubled quote character — and treats an unterminated literal as running to the end, which
     * makes the whole rest of the string invisible rather than parseable.
     */
    private static function skipQuoted(string $sql, int $from, string $quote): int
    {
        $length = \strlen($sql);

        for ($i = $from + 1; $i < $length; $i++) {
            $c = $sql[$i];
            if ($c === '\\' && $quote !== '`') {
                $i++;
                continue;
            }
            if ($c === $quote) {
                if (($sql[$i + 1] ?? '') === $quote) {
                    $i++;
                    continue;
                }

                return $i;
            }
        }

        return $length;
    }

    private function pdo(): PDO
    {
        if ($this->pdo !== null) {
            return $this->pdo;
        }

        $config = ($this->primary)()->getConfig();
        $dsn    = 'mysql:dbname=' . (string)($config['database'] ?? '')
            . (\defined('DB_SOCKET') ? ';unix_socket=' . \DB_SOCKET : ';host=' . (string)($config['host'] ?? ''));

        $options = [
            PDO::ATTR_ERRMODE                => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES       => false,
            PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
            PDO::ATTR_STRINGIFY_FETCHES      => false,
        ];
        if (\defined('DB_CHARSET')) {
            $options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES '" . \DB_CHARSET . "'";
        }

        $factory = $this->pdoFactory ?? static fn (
            string $dsn,
            string $user,
            string $pass,
            array $opts
        ): PDO => new PDO($dsn, $user, $pass, $opts);

        try {
            $pdo = $factory(
                $dsn,
                (string)($config['username'] ?? ''),
                (string)($config['password'] ?? ''),
                $options,
            );
        } catch (Throwable $e) {
            // No fallback to the shared connection: running there would be a weaker guarantee than
            // the one this tool advertises, and silently so.
            throw new ReadFailed('The read-only database connection could not be opened: ' . $e->getMessage());
        }

        $this->applyStatementTimeout($pdo);

        return $this->pdo = $pdo;
    }

    /**
     * MariaDB and MySQL spell the server-side statement timeout differently and neither accepts
     * the other's name, so try the MariaDB form first (docker/docker-compose.yml:61 is
     * `mariadb:latest`) and fall back to MySQL's. If neither exists, degrade with an explicit
     * warning rather than pretending there is a cap.
     */
    private function applyStatementTimeout(PDO $pdo): void
    {
        $seconds = \max(1, \min($this->timeoutSeconds, self::MAX_TIMEOUT_SECONDS));

        try {
            $pdo->exec('SET SESSION max_statement_time = ' . $seconds); // MariaDB >= 10.1, seconds

            return;
        } catch (Throwable) {
            // not MariaDB — fall through
        }

        try {
            $pdo->exec('SET SESSION max_execution_time = ' . ($seconds * 1000)); // MySQL, milliseconds

            return;
        } catch (Throwable) {
            // neither dialect accepted it
        }

        $this->warnings[] = 'This database server supports neither max_statement_time nor '
            . 'max_execution_time, so no server-side query timeout is in force.';
    }
}
