<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Journal;

use JTL\DB\DbInterface;

/**
 * The primary-key columns of a table, read once per process from `information_schema`.
 *
 * The journal cannot snapshot rows it cannot address again later: an `UPDATE tland SET
 * bPermitRegistration = …` needs a WHERE that names exactly one row, and the catalog's narrowed
 * `columns: [cISO, bPermitRegistration]` deliberately does not contain one. So the key columns are
 * resolved from the schema and added to every snapshot regardless of what the entry narrowed to.
 *
 * A table without a primary key is not an error here — it is a table the journal declares
 * not-undoable, with that as the reason. Guessing a key for a keyless table is how an undo updates
 * a hundred rows instead of one.
 */
final class TableKeys
{
    /** Table and column names are interpolated into SQL, so nothing outside this may pass. */
    public const IDENTIFIER = '/^[A-Za-z_][A-Za-z0-9_]*$/';

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

    public function __construct(private readonly DbInterface $db)
    {
    }

    /**
     * @return list<string> the PRIMARY KEY columns in ordinal order; empty when the table has none
     */
    public function of(string $table): array
    {
        if (\preg_match(self::IDENTIFIER, $table) !== 1) {
            return [];
        }
        if (isset($this->cache[$table])) {
            return $this->cache[$table];
        }

        $rows = $this->db->getArrays(
            'SELECT COLUMN_NAME
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = :t
                  AND INDEX_NAME = \'PRIMARY\'
                ORDER BY SEQ_IN_INDEX',
            ['t' => $table],
        );

        $columns = [];
        foreach ($rows as $row) {
            $column = (string)($row['COLUMN_NAME'] ?? '');
            if ($column !== '' && \preg_match(self::IDENTIFIER, $column) === 1) {
                $columns[] = $column;
            }
        }

        return $this->cache[$table] = $columns;
    }
}
