<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Db;

use PhpMyAdmin\SqlParser\Components\Limit;
use PhpMyAdmin\SqlParser\Lexer;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Statements\SelectStatement;
use PhpMyAdmin\SqlParser\Token;

/**
 * Decides whether one caller-supplied SQL string is a single, harmless, row-capped SELECT — and
 * returns the *rebuilt* statement that may be executed in its place.
 *
 * This is the shop's own DB-manager guard (Router/Controller/Backend/DBManagerController.php:88-135)
 * with the holes closed:
 *  - the shop parses but never counts statements, so `SELECT 1; DROP TABLE x` passes its parse
 *    stage (Utils\Query::getAll() only ever inspects statement #0). We reject anything that is not
 *    exactly one statement.
 *  - the shop ignores `SELECT … INTO OUTFILE`, file/locking functions and `FOR UPDATE`.
 *
 * Layers, in order — each one alone is defeatable, together they are not:
 *  A parse, don't regex; exactly one statement; it must be a SELECT
 *  B reject the escape hatches the SELECT grammar itself allows (INTO, locking reads, functions)
 *  C table denylist + cross-database check
 *  D execute only `SelectStatement::build()` output, with an enforced LIMIT
 * Layer E — a connection that physically cannot write, and that re-states A on its own — is
 * {@see ReadGate}, not this class.
 *
 * Pure: no DB handle, no shop container, no globals. Every rejection is a {@see SqlReadRejected}.
 */
final class SqlReadGuard
{
    /**
     * The shop's own restricted set (DBManagerController.php:90) plus this plugin's credential
     * surfaces. `tnt_mcp_oauth_*` is matched by prefix so future OAuth tables are denied by default.
     *
     * @var list<string>
     */
    public const DENIED_TABLES = [
        'tadminlogin',
        'tbrocken',
        'tsession',
        'tsynclogin',
        'tnt_mcp_token',
        // Secrets that live in *values*, which no column-name rule can ever catch:
        // teinstellungen.cWert holds email_smtp_pass, oauth_client_secret, caching_redis_pass,
        // ftp_pass, sftp_pass and sftp_privkey in cleartext; tstoreauth holds JTL-Store access
        // tokens.
        'teinstellungen',
        'tstoreauth',
        // This plugin's own logs: tnt_mcp_audit.cArgs is json_encode($args) — the complete argument
        // values of every MCP call by every token. One client must not be able to read another's.
        'tnt_mcp_audit',
        'tnt_mcp_request_log',
    ];

    /** @var list<string> */
    public const DENIED_TABLE_PREFIXES = ['tnt_mcp_oauth_'];

    /**
     * Best-effort, and honestly so: a denylist is weaker than an allowlist, and a name reached
     * through a view or a stored function is invisible here. Layer D (rebuild) and layer E (the
     * read-only connection) are what make a miss non-fatal.
     *
     * @var list<string>
     */
    public const DENIED_FUNCTIONS = [
        'LOAD_FILE',
        'SLEEP',
        'BENCHMARK',
        'GET_LOCK',
        'RELEASE_LOCK',
        'IS_USED_LOCK',
        'SYS_EXEC',
        'SYS_EVAL',
        'MASTER_POS_WAIT',
        'SOURCE_POS_WAIT',
    ];

    public const DEFAULT_ROW_LIMIT = 200;
    public const MAX_ROW_LIMIT     = 2000;

    /**
     * @param string       $database           name of the only database that may be referenced
     * @param list<string> $extraDeniedTables  merchant-specific additions, lower-case
     */
    public function __construct(
        private readonly string $database,
        private readonly int $defaultRowLimit = self::DEFAULT_ROW_LIMIT,
        private readonly int $maxRowLimit = self::MAX_ROW_LIMIT,
        private readonly array $extraDeniedTables = [],
    ) {
    }

    /**
     * @param string   $sql      the caller's query
     * @param int|null $rowLimit caller's requested cap; null uses the default. Always clamped to
     *                           $maxRowLimit, and always wins over a larger LIMIT in $sql.
     * @throws SqlReadRejected
     */
    public function check(string $sql, ?int $rowLimit = null): GuardedQuery
    {
        $notes  = [];
        $wanted = $this->resolveRowLimit($rowLimit, $notes);

        if (\trim($sql) === '') {
            throw new SqlReadRejected(SqlReadRejected::EMPTY_QUERY, 'No query was given.');
        }

        $tokens = $this->lex($sql);
        $this->rejectByToken($tokens);
        $tables = $this->checkTables($tokens);
        // After the table check on purpose: a denied table whose NAME matches the secret pattern
        // (tnt_mcp_token) must be reported as the denied table it is, not as a column.
        $this->rejectSecretColumns($tokens, $tables);

        $stmt = $this->parseSingleSelect($sql);
        $this->rejectLockingRead($stmt);

        // Compare BEFORE the LIMIT is injected: otherwise every capped query would claim to have
        // been rewritten, and a note that fires every time is a note nobody reads.
        $asParsed = $stmt->build();
        $this->applyLimit($stmt, $wanted, $notes);

        $rebuilt = $stmt->build();
        if ($this->normalise($asParsed) !== $this->normalise($sql)) {
            $notes[] = 'Your query was rewritten from its parsed form before running; '
                . 'the SQL that actually ran is returned as "sql".';
        }

        return new GuardedQuery($rebuilt, $sql, $tables, $wanted, $notes);
    }

    /**
     * Lexes once and hands back the meaningful tokens — whitespace and comments dropped, so a
     * comment can never hide between a keyword and its operand.
     *
     * Lexer errors (an unterminated quote, a stray backslash) live on the lexer and are lost when
     * `Query::getAll()` builds its own; the shop only ever sees the parser's half of the errors.
     *
     * @return list<Token>
     * @throws SqlReadRejected
     */
    private function lex(string $sql): array
    {
        $lexer = new Lexer($sql);
        if (\count($lexer->errors) > 0) {
            throw new SqlReadRejected(
                SqlReadRejected::PARSE_ERROR,
                'The query could not be read: ' . $lexer->errors[0]->getMessage()
            );
        }

        $tokens = [];
        foreach ($lexer->list->tokens as $token) {
            if ($token->type === Token::TYPE_WHITESPACE || $token->type === Token::TYPE_COMMENT) {
                continue;
            }
            if ($token->type === Token::TYPE_DELIMITER && $token->value === null) {
                continue; // the list's terminating sentinel
            }
            $tokens[] = $token;
        }

        return $tokens;
    }

    /**
     * Layer A's tail: exactly one statement, and it is a SELECT.
     *
     * @throws SqlReadRejected
     */
    private function parseSingleSelect(string $sql): SelectStatement
    {
        $parser = new Parser($sql);
        if (\count($parser->statements) > 1) {
            throw new SqlReadRejected(
                SqlReadRejected::STACKED_QUERY,
                \sprintf(
                    'Send exactly one statement — %d were found. Semicolon-separated queries are refused.',
                    \count($parser->statements)
                )
            );
        }
        if (\count($parser->errors) > 0) {
            throw new SqlReadRejected(
                SqlReadRejected::PARSE_ERROR,
                'The query could not be read: ' . $parser->errors[0]->getMessage()
            );
        }

        $stmt = $parser->statements[0] ?? null;
        if (!$stmt instanceof SelectStatement) {
            throw new SqlReadRejected(
                SqlReadRejected::NOT_A_SELECT,
                'Only SELECT statements can be run here. Use invoke_admin_action to change anything.'
            );
        }

        return $stmt;
    }

    /**
     * Layer B, done on the token stream rather than the AST — deliberately.
     *
     * `SELECT … INTO OUTFILE` and several statement types the parser does not model make the
     * parser emit an error, which would surface as an unhelpful "could not be read" instead of
     * the real reason. The token stream is also the only place where a first-keyword check is
     * possible at all: an unsupported statement (GRANT, CALL) parses to nothing.
     *
     * @param list<Token> $tokens
     * @throws SqlReadRejected
     */
    private function rejectByToken(array $tokens): void
    {
        $first = $tokens[0] ?? null;
        if ($first === null) {
            throw new SqlReadRejected(SqlReadRejected::EMPTY_QUERY, 'No query was given.');
        }
        $firstValue = \strtoupper((string)$first->value);
        if ($firstValue !== 'SELECT' && $firstValue !== '(' && $firstValue !== 'WITH') {
            throw new SqlReadRejected(
                SqlReadRejected::NOT_A_SELECT,
                \sprintf(
                    'Only SELECT statements can be run here (this one starts with %s). '
                    . 'Use invoke_admin_action to change anything.',
                    $firstValue
                )
            );
        }

        $denied = \array_flip(self::DENIED_FUNCTIONS);
        foreach ($tokens as $token) {
            if ($token->type !== Token::TYPE_KEYWORD && $token->type !== Token::TYPE_NONE) {
                continue; // never look inside string literals
            }
            $name = \strtoupper((string)$token->value);
            if ($name === 'INTO') {
                // Inside a statement that starts with SELECT, INTO is only ever OUTFILE/DUMPFILE/@var.
                throw new SqlReadRejected(
                    SqlReadRejected::SELECT_INTO,
                    'SELECT … INTO is refused — it writes a file or a variable.'
                );
            }
            if (isset($denied[$name])) {
                throw new SqlReadRejected(
                    SqlReadRejected::DENIED_FUNCTION,
                    \sprintf('The function %s() is not available here.', $name)
                );
            }
        }
    }

    /**
     * {@see ResultShaper} masks a secret by the name PDO reports for the column — and PDO reports
     * the **alias**, not the source column. So `SELECT cPasswort AS x FROM tkunde` came back
     * unmasked: `tkunde` is not a denied table and `x` matches no secret pattern. Rather than try to
     * resolve aliases (the parser cannot, for expressions), refuse the query outright when any bare
     * identifier in it looks like a credential column.
     *
     * `SELECT *` is unaffected and stays safe: it yields the real column names, which the shaper's
     * name-based redaction still catches.
     *
     * @param list<Token>  $tokens
     * @param list<string> $tables the statement's own table names, which are exempt: an allowed
     *                             table whose name happens to match the pattern is not a column
     * @throws SqlReadRejected
     */
    private function rejectSecretColumns(array $tokens, array $tables): void
    {
        $exempt = \array_flip(\array_map('\strtolower', $tables));
        foreach ($tokens as $token) {
            // TYPE_NONE is a bare identifier, TYPE_SYMBOL a backticked one. String literals
            // (TYPE_STRING) are deliberately not inspected — a WHERE value is not a projection.
            if ($token->type !== Token::TYPE_NONE && $token->type !== Token::TYPE_SYMBOL) {
                continue;
            }
            $name = (string)$token->value;
            if ($name !== ''
                && !isset($exempt[\strtolower($name)])
                && \preg_match(ResultShaper::SECRET_COLUMN_PATTERN, $name) === 1
            ) {
                throw new SqlReadRejected(
                    SqlReadRejected::DENIED_COLUMN,
                    \sprintf(
                        'The identifier %s looks like a credential column and cannot be selected. '
                        . 'Use SELECT * if you need the row — secret values are masked there.',
                        $name
                    )
                );
            }
        }
    }

    /**
     * `FOR UPDATE` / `LOCK IN SHARE MODE` take row locks — a read that blocks the shop.
     *
     * @throws SqlReadRejected
     */
    private function rejectLockingRead(SelectStatement $stmt): void
    {
        $options = $stmt->end_options->options ?? [];
        if ($options !== []) {
            throw new SqlReadRejected(
                SqlReadRejected::LOCKING_READ,
                \sprintf('Locking reads are refused (%s).', \implode(', ', \array_map(
                    static fn ($option): string => \is_array($option) ? (string)($option['name'] ?? '') : (string)$option,
                    $options
                )))
            );
        }
    }

    /**
     * Layer C — the denylist and the cross-database check of DBManagerController.php:107-114,
     * with its actual coverage fixed.
     *
     * The shop drives both off `Utils\Query::getAll()['select_tables']`, which only reports the
     * FROM expressions of the outermost statement: it returns just `tkunde` for
     * `FROM tkunde k JOIN tadminlogin a …`, for `… UNION SELECT … FROM tadminlogin`, and nothing
     * at all for `FROM (SELECT * FROM tsession) s`. A denylist that a JOIN walks straight past is
     * not a denylist, so we read the table names off the token stream instead — every FROM and
     * every JOIN at every nesting depth.
     *
     * Views are still opaque here (the name of a view is what appears, not the tables behind it);
     * the read-only connection of layer E is what covers that.
     *
     * @param list<Token> $tokens
     * @return list<string> lower-cased table names
     * @throws SqlReadRejected
     */
    private function checkTables(array $tokens): array
    {
        $tables = [];
        foreach ($this->referencedTables($tokens) as [$dbName, $table]) {
            if ($dbName !== null && \strcasecmp($dbName, $this->database) !== 0) {
                throw new SqlReadRejected(
                    SqlReadRejected::FOREIGN_DATABASE,
                    \sprintf('Only the shop database `%s` can be read.', $this->database)
                );
            }
            $lower = \mb_convert_case($table, \MB_CASE_LOWER);
            if ($this->isDenied($lower)) {
                throw new SqlReadRejected(
                    SqlReadRejected::DENIED_TABLE,
                    \sprintf('The table `%s` holds credentials or session data and cannot be read.', $table)
                );
            }
            if (!\in_array($lower, $tables, true)) {
                $tables[] = $lower;
            }
        }

        return $tables;
    }

    /**
     * Every table named after a FROM or a JOIN, at any nesting depth, with its optional database
     * qualifier. Aliases and derived tables are skipped; a subquery's own FROM is picked up when
     * the walk reaches it.
     *
     * @param list<Token> $tokens
     * @return list<array{0: string|null, 1: string}> [database|null, table]
     */
    private function referencedTables(array $tokens): array
    {
        $found    = [];
        $expect   = false;
        $count    = \count($tokens);
        for ($i = 0; $i < $count; $i++) {
            $token = $tokens[$i];
            $value = \strtoupper((string)$token->value);
            if ($token->type === Token::TYPE_KEYWORD && ($value === 'FROM' || \str_contains($value, 'JOIN'))) {
                $expect = true;
                continue;
            }
            if (!$expect) {
                continue;
            }
            if (!$this->isIdentifier($token)) {
                $expect = false; // a derived table "(SELECT …)", or a keyword — nothing to name here
                continue;
            }

            $name = (string)$token->value;
            $db   = null;
            if (($tokens[$i + 1] ?? null)?->value === '.' && $this->isIdentifier($tokens[$i + 2] ?? null)) {
                $db   = $name;
                $name = (string)$tokens[$i + 2]->value;
                $i   += 2;
            }
            $found[] = [$db, $name];

            // Skip an alias ("t", "AS t") and stay in table position across "FROM a, b".
            $next = $tokens[$i + 1] ?? null;
            if ($next !== null && \strtoupper((string)$next->value) === 'AS') {
                $i++;
                $next = $tokens[$i + 1] ?? null;
            }
            if ($next !== null && $this->isIdentifier($next)) {
                $i++;
                $next = $tokens[$i + 1] ?? null;
            }
            $expect = $next !== null && $next->value === ',';
            if ($expect) {
                $i++;
            }
        }

        return $found;
    }

    private function isIdentifier(?Token $token): bool
    {
        return $token !== null
            && ($token->type === Token::TYPE_NONE || $token->type === Token::TYPE_SYMBOL)
            && \is_string($token->value)
            && $token->value !== '';
    }

    private function isDenied(string $lowerTable): bool
    {
        return \in_array($lowerTable, $this->extraDeniedTables, true) || self::isTableDenied($lowerTable);
    }

    /**
     * The instance-independent half of the denylist, so `list_tables` can mark a table
     * `readable: false` with the same rule that would reject a query against it. Showing it as
     * unreadable beats hiding it: an LLM that cannot see `tadminlogin` keeps re-deriving it.
     */
    public static function isTableDenied(string $table): bool
    {
        $lower = \mb_convert_case(\trim($table), \MB_CASE_LOWER);
        if (\in_array($lower, self::DENIED_TABLES, true)) {
            return true;
        }
        foreach (self::DENIED_TABLE_PREFIXES as $prefix) {
            if (\str_starts_with($lower, $prefix)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Layer D's cap. The shop injects a LIMIT the same way (DBManagerController.php:118-120);
     * we additionally clamp a caller-supplied one, and treat a non-numeric row count
     * (a placeholder, an expression) as "no usable limit".
     *
     * @param list<string> $notes
     */
    private function applyLimit(SelectStatement $stmt, int $wanted, array &$notes): void
    {
        $current = $stmt->limit;
        if (!$current instanceof Limit) {
            $stmt->limit = new Limit($wanted, 0);
            $notes[]     = \sprintf('A LIMIT %d was added because your query had none.', $wanted);

            return;
        }

        $offset = \is_numeric($current->offset) ? (int)$current->offset : 0;
        if (!\is_numeric($current->rowCount)) {
            $stmt->limit = new Limit($wanted, $offset);
            $notes[]     = \sprintf('Your LIMIT was not a plain number and was replaced with %d.', $wanted);

            return;
        }
        if ((int)$current->rowCount > $wanted) {
            $stmt->limit = new Limit($wanted, $offset);
            $notes[]     = \sprintf('Your LIMIT %d was reduced to the cap of %d.', (int)$current->rowCount, $wanted);
        }
    }

    /**
     * @param list<string> $notes
     */
    private function resolveRowLimit(?int $rowLimit, array &$notes): int
    {
        if ($rowLimit === null) {
            return $this->defaultRowLimit;
        }
        if ($rowLimit < 1) {
            $notes[] = \sprintf('A row limit below 1 is not usable; %d was used.', $this->defaultRowLimit);

            return $this->defaultRowLimit;
        }
        if ($rowLimit > $this->maxRowLimit) {
            $notes[] = \sprintf('The requested %d rows exceed the cap of %d.', $rowLimit, $this->maxRowLimit);

            return $this->maxRowLimit;
        }

        return $rowLimit;
    }

    private function normalise(string $sql): string
    {
        return \strtolower(\trim((string)\preg_replace('/\s+/', ' ', \trim($sql)), " \t\n\r;"));
    }
}
