<?php

declare(strict_types=1);

namespace Atdt;

/**
 * A door-owned table (SPEC §13.4). The physical name is enforced by
 * DoorStore (door_<slug>_<name>); queries are built from validated
 * identifiers + prepared parameters, so a door can express real work
 * (filters, ordering, sums) without ever writing SQL or reaching a core
 * table.
 *
 * The where grammar: ['col' => v] is equality; the key may carry one
 * whitelisted operator ('turns >' => 0, 'id IN' => [1,2,3]).
 */
final class DoorTable
{
    private const OPS = ['=', '!=', '<', '<=', '>', '>=', 'IN', 'LIKE'];

    public function __construct(
        private readonly Db $db,
        private readonly string $table,
    ) {
    }

    /** Create the table (Schema-style spec array) if it does not exist. */
    public function ensure(array $cols, array $indexes = []): void
    {
        if ($this->exists()) {
            return;
        }
        foreach ($this->db->createTableSql($this->table, ['cols' => $cols, 'indexes' => $indexes]) as $stmt) {
            $this->db->exec($stmt);
        }
    }

    public function exists(): bool
    {
        if ($this->db->dialect() === Db::MYSQL) {
            return $this->db->val(
                'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?',
                [$this->table]
            ) > 0;
        }
        return $this->db->val(
            "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?",
            [$this->table]
        ) > 0;
    }

    public function drop(): void
    {
        $this->db->exec($this->db->dropTableSql($this->table));
    }

    public function insert(array $row): int
    {
        return $this->db->insert($this->table, $row);
    }

    /** One prepared statement, many rows — universe generation speed. */
    public function insertMany(array $rows): void
    {
        if ($rows === []) {
            return;
        }
        $cols = array_keys($rows[0]);
        foreach ($cols as $c) {
            self::ident($c);
        }
        $sql = 'INSERT INTO ' . $this->q($this->table) . ' (' . implode(', ', array_map(fn($c) => $this->q($c), $cols)) . ')
                VALUES (' . implode(', ', array_fill(0, count($cols), '?')) . ')';
        foreach ($rows as $row) {
            $this->db->q($sql, array_values($row));
        }
    }

    /** @return list<array<string, mixed>> */
    public function rows(array $where = [], string $orderBy = '', int $limit = 0, int $offset = 0): array
    {
        [$sql, $params] = $this->build('SELECT *', $where, $orderBy, $limit, $offset);
        return $this->db->all($sql, $params);
    }

    public function one(array $where): ?array
    {
        [$sql, $params] = $this->build('SELECT *', $where, '', 1, 0);
        return $this->db->one($sql, $params);
    }

    public function count(array $where = []): int
    {
        [$sql, $params] = $this->build('SELECT COUNT(*)', $where, '', 0, 0);
        return (int)$this->db->val($sql, $params);
    }

    public function sum(string $col, array $where = []): float
    {
        self::ident($col);
        [$sql, $params] = $this->build('SELECT COALESCE(SUM(' . $this->q($col) . '), 0)', $where, '', 0, 0);
        return (float)$this->db->val($sql, $params);
    }

    public function update(array $set, array $where): int
    {
        if ($set === []) {
            return 0;
        }
        $frags = [];
        $params = [];
        foreach ($set as $col => $v) {
            self::ident((string)$col);
            $frags[] = $this->q((string)$col) . ' = ?';
            $params[] = $v;
        }
        [$whereSql, $whereParams] = $this->whereSql($where);
        return $this->db->q(
            'UPDATE ' . $this->q($this->table) . ' SET ' . implode(', ', $frags) . $whereSql,
            array_merge($params, $whereParams)
        )->rowCount();
    }

    public function delete(array $where): int
    {
        [$whereSql, $params] = $this->whereSql($where);
        return $this->db->q('DELETE FROM ' . $this->q($this->table) . $whereSql, $params)->rowCount();
    }

    // ---- builders --------------------------------------------------------

    /** @return array{0: string, 1: list<mixed>} */
    private function build(string $select, array $where, string $orderBy, int $limit, int $offset): array
    {
        [$whereSql, $params] = $this->whereSql($where);
        $sql = $select . ' FROM ' . $this->q($this->table) . $whereSql;
        if ($orderBy !== '') {
            $sql .= ' ORDER BY ' . self::orderSql($orderBy);
        }
        if ($limit > 0) {
            $sql .= ' LIMIT ' . $limit . ($offset > 0 ? ' OFFSET ' . $offset : '');
        }
        return [$sql, $params];
    }

    /** @return array{0: string, 1: list<mixed>} */
    private function whereSql(array $where): array
    {
        if ($where === []) {
            return ['', []];
        }
        $frags = [];
        $params = [];
        foreach ($where as $key => $v) {
            $parts = preg_split('/\s+/', trim((string)$key));
            $col = (string)$parts[0];
            $op = strtoupper((string)($parts[1] ?? '='));
            self::ident($col);
            if (!in_array($op, self::OPS, true)) {
                throw new \InvalidArgumentException("Bad operator '$op'");
            }
            if ($op === 'IN') {
                $list = array_values((array)$v);
                if ($list === []) {
                    $frags[] = '1 = 0';
                    continue;
                }
                $frags[] = $this->q($col) . ' IN (' . implode(', ', array_fill(0, count($list), '?')) . ')';
                foreach ($list as $item) {
                    $params[] = $item;
                }
                continue;
            }
            $frags[] = $this->q($col) . ' ' . $op . ' ?';
            $params[] = $v;
        }
        return [' WHERE ' . implode(' AND ', $frags), $params];
    }

    private static function orderSql(string $orderBy): string
    {
        $out = [];
        foreach (explode(',', $orderBy) as $piece) {
            $parts = preg_split('/\s+/', trim($piece));
            $col = (string)$parts[0];
            $dir = strtoupper((string)($parts[1] ?? 'ASC'));
            self::ident($col);
            if ($dir !== 'ASC' && $dir !== 'DESC') {
                throw new \InvalidArgumentException("Bad sort direction '$dir'");
            }
            $out[] = $col . ' ' . $dir;
        }
        return implode(', ', $out);
    }

    private static function ident(string $name): void
    {
        if (preg_match('/^[A-Za-z_][A-Za-z0-9_]{0,63}$/', $name) !== 1) {
            throw new \InvalidArgumentException("Bad identifier '$name'");
        }
    }

    private function q(string $ident): string
    {
        return $this->db->quoteIdent($ident);
    }
}
