<?php
namespace Relay\Database;

use Relay\Support\RelayError;

final class TableSchema
{
    public string $name = '';
    /** @var string[] */
    public array $columns = [];
    /** @var array<string, bool> */
    public array $ints = [];
    /** @var array<string, bool> */
    public array $bins = [];
    /** @var array<string, bool> */
    public array $bits = [];
    /** @var array<string, bool> */
    public array $points = [];
    /** @var string[] Cursor columns in index order; empty means offset paging. */
    public array $key = [];

    // Ported from WP Migrate 2.7.11 (GPL) Table::get_structure_info, extended with unique-index fallback.
    public static function load(Db $db, string $table): self
    {
        $s = new self();
        $s->name = $table;
        $nullable = [];
        foreach ($db->results('SHOW FULL COLUMNS FROM ' . Db::ident($table)) as $c) {
            $field = (string) $c->Field;
            $type = strtolower((string) $c->Type);
            // Generated columns are recomputed by the server; inserting values into them is an error.
            if (stripos((string) $c->Extra, 'GENERATED') !== false) {
                continue;
            }
            $s->columns[] = $field;
            if ($c->Null === 'YES') {
                $nullable[$field] = true;
            }
            if (preg_match('/^(tinyint|smallint|mediumint|int|integer|bigint)\b/', $type)) {
                $s->ints[$field] = true;
            } elseif (preg_match('/^(binary|varbinary|tinyblob|blob|mediumblob|longblob)\b/', $type)) {
                $s->bins[$field] = true;
            } elseif (preg_match('/^bit\b/', $type)) {
                $s->bits[$field] = true;
            } elseif (preg_match('/^point\b/', $type)) {
                $s->points[$field] = true;
            }
        }
        if ($s->columns === []) {
            /* translators: %s: table name */
            throw new RelayError('db.no_columns', esc_html(sprintf(__('Table %s has no columns.', 'siterelay'), $table)));
        }
        $s->key = self::choose_key($db, $s, $nullable);
        return $s;
    }

    /** PRIMARY if usable, else the smallest unique NOT NULL full-column index, else none. */
    private static function choose_key(Db $db, self $s, array $nullable): array
    {
        $indexes = [];
        foreach ($db->results('SHOW INDEX FROM ' . Db::ident($s->name)) as $i) {
            $name = (string) $i->Key_name;
            $indexes[$name]['unique'] = ((int) $i->Non_unique === 0);
            $indexes[$name]['partial'] = !empty($indexes[$name]['partial']) || $i->Sub_part !== null;
            $indexes[$name]['cols'][(int) $i->Seq_in_index] = (string) $i->Column_name;
        }
        $best = null;
        foreach ($indexes as $name => $index) {
            if (!$index['unique'] || $index['partial']) {
                continue;
            }
            ksort($index['cols']);
            $cols = array_values($index['cols']);
            foreach ($cols as $col) {
                if (!in_array($col, $s->columns, true) || isset($nullable[$col]) || isset($s->bins[$col]) || isset($s->bits[$col]) || isset($s->points[$col])) {
                    continue 2;
                }
            }
            if ($name === 'PRIMARY') {
                return $cols;
            }
            if ($best === null || count($cols) < count($best)) {
                $best = $cols;
            }
        }
        return $best ?? [];
    }
}