<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Catalog;

use JTL\DB\DbInterface;

/**
 * The database half of the schema: everything `list_tables` and `describe_table` read out of
 * `information_schema`, and nothing else.
 *
 * Deliberately on the shop's primary connection rather than {@see \Plugin\tnt_mcp\src\Mcp\Db\ReadGate}:
 * these are fixed, parameter-bound metadata queries with no caller-supplied SQL, so neither the
 * guard nor a second connection buys anything here — it would only be one more socket per request.
 *
 * Every query is scoped to `DATABASE()`, so no other schema on the server is visible.
 */
final class SchemaReader
{
    public function __construct(private readonly DbInterface $db)
    {
    }

    /**
     * @return list<array{table:string,rowsApprox:int,comment:string,engine:string}>
     */
    public function tables(): array
    {
        $rows = $this->db->getArrays(
            'SELECT TABLE_NAME, TABLE_ROWS, TABLE_COMMENT, ENGINE
                FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = \'BASE TABLE\'
                ORDER BY TABLE_NAME'
        );

        $tables = [];
        foreach ($rows as $row) {
            $tables[] = [
                'table'      => (string)($row['TABLE_NAME'] ?? ''),
                // InnoDB's TABLE_ROWS is a sampled estimate, hence the name. Never present as exact.
                'rowsApprox' => (int)($row['TABLE_ROWS'] ?? 0),
                'comment'    => \trim((string)($row['TABLE_COMMENT'] ?? '')),
                'engine'     => (string)($row['ENGINE'] ?? ''),
            ];
        }

        return $tables;
    }

    public function tableExists(string $table): bool
    {
        return (int)$this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t',
            'cnt',
            ['t' => $table]
        ) > 0;
    }

    /**
     * @return list<array{column:string,type:string,nullable:bool,default:string|null,comment:string,key:string}>
     */
    public function columns(string $table): array
    {
        $rows = $this->db->getArrays(
            'SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, COLUMN_KEY
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t
                ORDER BY ORDINAL_POSITION',
            ['t' => $table]
        );

        $columns = [];
        foreach ($rows as $row) {
            $default   = $row['COLUMN_DEFAULT'] ?? null;
            $columns[] = [
                'column'   => (string)($row['COLUMN_NAME'] ?? ''),
                'type'     => (string)($row['COLUMN_TYPE'] ?? ''),
                'nullable' => ($row['IS_NULLABLE'] ?? 'NO') === 'YES',
                'default'  => $default === null ? null : (string)$default,
                // The schema's real comments are worth surfacing: tzahlungslog.nLevel carries
                // '1=Error, 2=Notice, 3=Debug', which is the only place that mapping is written down.
                'comment'  => \trim((string)($row['COLUMN_COMMENT'] ?? '')),
                'key'      => (string)($row['COLUMN_KEY'] ?? ''),
            ];
        }

        return $columns;
    }

    /**
     * @return list<array{name:string,columns:list<string>,unique:bool}> the primary key first, if any
     */
    public function indexes(string $table): array
    {
        $rows = $this->db->getArrays(
            'SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t
                ORDER BY (INDEX_NAME = \'PRIMARY\') DESC, INDEX_NAME, SEQ_IN_INDEX',
            ['t' => $table]
        );

        $indexes = [];
        foreach ($rows as $row) {
            $name = (string)($row['INDEX_NAME'] ?? '');
            $indexes[$name] ??= ['name' => $name, 'columns' => [], 'unique' => (int)($row['NON_UNIQUE'] ?? 1) === 0];
            $indexes[$name]['columns'][] = (string)($row['COLUMN_NAME'] ?? '');
        }

        return \array_values($indexes);
    }

    /**
     * JTL declares almost no real foreign keys — `tbestellung.kKunde` is a plain int with no
     * constraint — so this is usually empty, and `describe_table` says so rather than leaving the
     * caller to wonder.
     *
     * @return list<array{column:string,referencesTable:string,referencesColumn:string}>
     */
    public function foreignKeys(string $table): array
    {
        $rows = $this->db->getArrays(
            'SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t
                    AND REFERENCED_TABLE_NAME IS NOT NULL
                ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION',
            ['t' => $table]
        );

        $keys = [];
        foreach ($rows as $row) {
            $keys[] = [
                'column'           => (string)($row['COLUMN_NAME'] ?? ''),
                'referencesTable'  => (string)($row['REFERENCED_TABLE_NAME'] ?? ''),
                'referencesColumn' => (string)($row['REFERENCED_COLUMN_NAME'] ?? ''),
            ];
        }

        return $keys;
    }
}
