<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Tools;

use Closure;
use Plugin\tnt_mcp\src\Mcp\Catalog\SchemaReader;
use Plugin\tnt_mcp\src\Mcp\Catalog\TableCatalog;
use Plugin\tnt_mcp\src\Mcp\Contracts\Tool;
use Plugin\tnt_mcp\src\Mcp\Db\SqlReadGuard;
use Plugin\tnt_mcp\src\Mcp\Mode\EndpointClass;
use RuntimeException;

/**
 * READ tool: one table's columns, keys, related tables and a couple of queries worth copying.
 *
 * The two things the schema alone cannot tell the caller are supplied by
 * `catalog/table-domains.yaml`: the per-table note (currency traps, inverted log levels) and the
 * sample queries. Everything else comes straight from `information_schema`.
 */
final class DescribeTable implements Tool
{
    /**
     * @param Closure(): SchemaReader $readerFactory must not touch the DB until run()
     */
    public function __construct(
        private readonly Closure $readerFactory,
        private readonly TableCatalog $catalog,
    ) {
    }

    public function name(): string
    {
        return 'describe_table';
    }

    public function description(): string
    {
        return 'Columns, types, keys, related tables and example queries for one shop database table.';
    }

    /** @return array<string,mixed> */
    public function inputSchema(): array
    {
        return [
            'type'       => 'object',
            'properties' => [
                'table' => [
                    'type'        => 'string',
                    'description' => 'Exact table name, as returned by list_tables.',
                ],
            ],
            'required'             => ['table'],
            'additionalProperties' => false,
        ];
    }

    public function isWrite(): bool
    {
        return false;
    }

    public function scope(): string
    {
        return 'db:read';
    }

    public function endpointClass(): EndpointClass
    {
        return EndpointClass::Reversible;
    }

    /**
     * @param array<string,mixed> $args
     * @return array<string,mixed>
     */
    public function run(array $args): array
    {
        $table = $args['table'] ?? null;
        if (!\is_string($table) || \trim($table) === '') {
            throw new RuntimeException('describe_table: "table" is required');
        }
        $table = \trim($table);
        if (\preg_match('/^[A-Za-z0-9_]+$/', $table) !== 1) {
            throw new RuntimeException('describe_table: "' . $table . '" is not a valid table name');
        }

        $reader = ($this->readerFactory)();
        $all    = $reader->tables();
        $known  = null;
        foreach ($all as $row) {
            if (\strcasecmp($row['table'], $table) === 0) {
                $known = $row;
                break;
            }
        }
        if ($known === null) {
            throw new RuntimeException(
                'describe_table: there is no table "' . $table . '" in this shop. Use list_tables to find it.'
            );
        }
        $table = $known['table'];

        $columns     = $reader->columns($table);
        $indexes     = $reader->indexes($table);
        $foreignKeys = $reader->foreignKeys($table);
        $notes       = [];

        $note = $this->catalog->note($table);
        if ($note !== null) {
            $notes[] = $note;
        }
        if ($foreignKeys === []) {
            $notes[] = 'This table declares no foreign keys — JTL\'s schema almost never does. '
                . 'The relatedTables entries are INFERRED from column names, not declared.';
        }
        $readable = !SqlReadGuard::isTableDenied($table);
        if (!$readable) {
            $notes[] = 'run_sql_read refuses this table: it holds credentials or session data.';
        }

        return [
            'table'         => $table,
            'domain'        => $this->catalog->domain($table),
            'comment'       => $known['comment'],
            'rowsApprox'    => $known['rowsApprox'],
            'readable'      => $readable,
            'columns'       => $columns,
            'primaryKey'    => $this->primaryKey($indexes),
            'indexes'       => $indexes,
            'foreignKeys'   => $foreignKeys,
            'relatedTables' => $this->relatedTables($table, $columns, $all),
            'sampleQueries' => $this->catalog->sampleQueries($table),
            'notes'         => $notes,
        ];
    }

    /**
     * @param list<array{name:string,columns:list<string>,unique:bool}> $indexes
     * @return list<string>
     */
    private function primaryKey(array $indexes): array
    {
        foreach ($indexes as $index) {
            if ($index['name'] === 'PRIMARY') {
                return $index['columns'];
            }
        }

        return [];
    }

    /**
     * JTL's naming convention is the only join map there is: a `kVersandart` column points at
     * `tversandart`. Inferred, and labelled as such in `notes` — a name that happens to match
     * nothing is simply dropped rather than invented, and the table's own key (`tbestellung`'s
     * `kBestellung`) is not a relation to itself.
     *
     * @param string $table the real table name, used only to drop the self-reference
     * @param list<array{column:string,type:string,nullable:bool,default:string|null,comment:string,key:string}> $columns
     * @param list<array{table:string,rowsApprox:int,comment:string,engine:string}> $all
     * @return list<array{column:string,table:string}>
     */
    private function relatedTables(string $table, array $columns, array $all): array
    {
        $self     = \mb_convert_case($table, \MB_CASE_LOWER);
        $existing = [];
        foreach ($all as $row) {
            $existing[\mb_convert_case($row['table'], \MB_CASE_LOWER)] = $row['table'];
        }

        $related = [];
        $seen    = [];
        foreach ($columns as $column) {
            if (\preg_match('/^k([A-Za-z][A-Za-z0-9]*)$/', $column['column'], $m) !== 1) {
                continue;
            }
            $candidate = 't' . \mb_convert_case($m[1], \MB_CASE_LOWER);
            if ($candidate === $self || !isset($existing[$candidate]) || isset($seen[$candidate])) {
                continue;
            }
            $seen[$candidate] = true;
            $related[]        = ['column' => $column['column'], 'table' => $existing[$candidate]];
        }

        return $related;
    }
}
