<?php

declare(strict_types=1);

namespace Atdt;

use PDO;
use PDOStatement;

/**
 * Thin PDO wrapper + SQL dialect layer (SPEC §8.1).
 *
 * All DML goes through prepared statements via q()/one()/all()/val()/insert().
 * DDL for both supported engines (SQLite, MySQL/MariaDB) is generated from the
 * canonical table definitions in Schema::TABLES by createTableSql().
 *
 * Canonical column types: pk, int, text, blob, bool, datetime.
 * Datetimes are stored as UTC ISO-8601 strings in both engines.
 *
 * MySQL notes (see DECISIONS.md):
 *  - 'text' columns that are indexed/unique/part of a PK become VARCHAR(191);
 *    columns flagged 'short' likewise; all other text becomes MEDIUMTEXT.
 *  - MySQL cannot attach a literal DEFAULT to TEXT-family columns, so defaults
 *    on non-short text columns are omitted there. Application code always
 *    supplies those values on INSERT (convention enforced by tests).
 */
final class Db
{
    public const SQLITE = 'sqlite';
    public const MYSQL  = 'mysql';

    private PDO $pdo;
    private string $dialect;

    public function __construct(PDO $pdo, string $dialect)
    {
        if (!in_array($dialect, [self::SQLITE, self::MYSQL], true)) {
            throw new \InvalidArgumentException("Unknown SQL dialect: $dialect");
        }
        $this->pdo = $pdo;
        $this->dialect = $dialect;
    }

    /** Build a Db from the config.php 'db' array. $dataDir anchors relative SQLite paths. */
    public static function fromConfig(array $db, string $dataDir): self
    {
        $driver = $db['driver'] ?? 'sqlite';
        if ($driver === 'mysql') {
            $m = $db['mysql'] ?? [];
            return self::mysql(
                (string)($m['host'] ?? 'localhost'),
                (int)($m['port'] ?? 3306),
                (string)($m['name'] ?? ''),
                (string)($m['user'] ?? ''),
                (string)($m['pass'] ?? '')
            );
        }
        $file = (string)($db['sqlite_file'] ?? 'atdt.sqlite');
        return self::sqlite(rtrim($dataDir, '/') . '/' . $file);
    }

    public static function sqlite(string $path): self
    {
        $pdo = new PDO('sqlite:' . $path, null, null, self::pdoOptions());
        $self = new self($pdo, self::SQLITE);
        $self->tuneSqlite();
        return $self;
    }

    /** In-memory SQLite, used by the test suite. */
    public static function sqliteMemory(): self
    {
        return self::sqlite(':memory:');
    }

    public static function mysql(string $host, int $port, string $name, string $user, string $pass): self
    {
        $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', $host, $port, $name);
        $opts = self::pdoOptions();
        $opts[PDO::ATTR_TIMEOUT] = 5;
        return new self(new PDO($dsn, $user, $pass, $opts), self::MYSQL);
    }

    /** @return array<int, mixed> */
    private static function pdoOptions(): array
    {
        return [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_STRINGIFY_FETCHES => false,
        ];
    }

    /** SPEC §9.5: busy_timeout=3000, WAL if available, silent fallback. */
    private function tuneSqlite(): void
    {
        $this->pdo->exec('PRAGMA busy_timeout = 3000');
        try {
            $this->pdo->exec('PRAGMA journal_mode = WAL');
        } catch (\Throwable $e) {
            // Not available on this filesystem — fine, default journal mode works.
        }
    }

    public function dialect(): string
    {
        return $this->dialect;
    }

    /** Run a prepared statement. The only sanctioned way to execute DML. */
    public function q(string $sql, array $params = []): PDOStatement
    {
        $st = $this->pdo->prepare($sql);
        $i = 1;
        foreach ($params as $k => $v) {
            $key = is_int($k) ? $i++ : $k;
            if (is_bool($v)) {
                $st->bindValue($key, $v ? 1 : 0, PDO::PARAM_INT);
            } elseif (is_int($v)) {
                $st->bindValue($key, $v, PDO::PARAM_INT);
            } elseif ($v === null) {
                $st->bindValue($key, null, PDO::PARAM_NULL);
            } else {
                $st->bindValue($key, (string)$v, PDO::PARAM_STR);
            }
        }
        $st->execute();
        return $st;
    }

    /** @return array<string, mixed>|null */
    public function one(string $sql, array $params = []): ?array
    {
        $row = $this->q($sql, $params)->fetch();
        return $row === false ? null : $row;
    }

    /** @return list<array<string, mixed>> */
    public function all(string $sql, array $params = []): array
    {
        return $this->q($sql, $params)->fetchAll();
    }

    /** First column of the first row, or $default. */
    public function val(string $sql, array $params = [], mixed $default = null): mixed
    {
        $row = $this->q($sql, $params)->fetch(PDO::FETCH_NUM);
        return $row === false ? $default : $row[0];
    }

    /** INSERT one row from an associative array; returns lastInsertId (0 if n/a). */
    public function insert(string $table, array $row): int
    {
        $cols = array_keys($row);
        $sql = 'INSERT INTO ' . $this->quoteIdent($table)
            . ' (' . implode(', ', array_map(fn($c) => $this->quoteIdent((string)$c), $cols)) . ')'
            . ' VALUES (' . implode(', ', array_fill(0, count($cols), '?')) . ')';
        $this->q($sql, array_values($row));
        return (int)$this->pdo->lastInsertId();
    }

    /**
     * Insert the row or, when its unique key already exists, update it
     * in place -- one statement, native on both dialects (1.7.4).
     *
     * The old idiom everywhere was UPDATE, then INSERT if rowCount()
     * came back 0. PDO's MySQL driver reports CHANGED rows for an
     * UPDATE, not matched rows, so writing a value equal to what the
     * row already held looked exactly like "no such row" and the
     * follow-up INSERT died on the primary key (SQLSTATE 23000 #1062
     * -- the field report was every door crashing on its second
     * entry). SQLite reports matched rows, which is why years of
     * SQLite boards never saw it.
     *
     * $keyCols must be covered by a PRIMARY KEY or UNIQUE index, or
     * neither dialect has a conflict to react to. $updateCols defaults
     * to every non-key column; pass a subset to leave columns like
     * created_at untouched on the update path.
     */
    public function upsert(string $table, array $row, array $keyCols, ?array $updateCols = null): void
    {
        $cols = array_keys($row);
        $updateCols ??= array_values(array_diff($cols, $keyCols));
        $ins = 'INSERT INTO ' . $this->quoteIdent($table)
            . ' (' . implode(', ', array_map(fn($c) => $this->quoteIdent((string)$c), $cols)) . ')'
            . ' VALUES (' . implode(', ', array_fill(0, count($cols), '?')) . ')';
        if ($this->dialect === self::MYSQL) {
            // VALUES() in ON DUPLICATE KEY UPDATE: deprecated in late
            // MySQL 8 but universal -- the row-alias replacement is not
            // understood by MariaDB, which is most shared hosting.
            $set = $updateCols === []
                ? $this->quoteIdent((string)$keyCols[0]) . ' = ' . $this->quoteIdent((string)$keyCols[0])
                : implode(', ', array_map(
                    fn($c) => $this->quoteIdent((string)$c) . ' = VALUES(' . $this->quoteIdent((string)$c) . ')',
                    $updateCols
                ));
            $this->q($ins . ' ON DUPLICATE KEY UPDATE ' . $set, array_values($row));
            return;
        }
        $conflict = ' ON CONFLICT(' . implode(', ', array_map(fn($c) => $this->quoteIdent((string)$c), $keyCols)) . ')';
        $sql = $updateCols === []
            ? $ins . $conflict . ' DO NOTHING'
            : $ins . $conflict . ' DO UPDATE SET ' . implode(', ', array_map(
                fn($c) => $this->quoteIdent((string)$c) . ' = excluded.' . $this->quoteIdent((string)$c),
                $updateCols
            ));
        $this->q($sql, array_values($row));
    }

    /** Execute raw DDL (schema statements generated by this class only). */
    public function exec(string $ddl): void
    {
        $this->pdo->exec($ddl);
    }

    /** Run $fn inside a transaction; commits on return, rolls back on throw. */
    public function tx(callable $fn): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $out = $fn($this);
            $this->pdo->commit();
            return $out;
        } catch (\Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function quoteIdent(string $ident): string
    {
        if ($this->dialect === self::MYSQL) {
            return '`' . str_replace('`', '``', $ident) . '`';
        }
        return '"' . str_replace('"', '""', $ident) . '"';
    }

    // ------------------------------------------------------------------
    // Dialect DDL generation
    // ------------------------------------------------------------------

    /**
     * Emit CREATE TABLE + CREATE INDEX statements for one canonical table def.
     *
     * Table def shape (see Schema::TABLES):
     *   ['cols' => [name => spec, ...], 'pk' => [...], 'indexes' => [[col,...], ...]]
     * Column spec: 'type' string, or [0 => 'type', 'null' => bool, 'default' => scalar,
     *              'unique' => bool, 'nocase' => bool, 'short' => bool].
     *
     * @return list<string>
     */
    public function createTableSql(string $table, array $def): array
    {
        $cols = $def['cols'];
        $indexed = [];
        foreach (($def['pk'] ?? []) as $c) {
            $indexed[$c] = true;
        }
        foreach (array_merge($def['indexes'] ?? [], $def['uniques'] ?? []) as $ix) {
            foreach ($ix as $c) {
                $indexed[$c] = true;
            }
        }
        foreach ($cols as $c => $spec) {
            if (is_array($spec) && !empty($spec['unique'])) {
                $indexed[$c] = true;
            }
        }

        $parts = [];
        foreach ($cols as $name => $spec) {
            $parts[] = $this->columnSql((string)$name, is_array($spec) ? $spec : [$spec], isset($indexed[$name]));
        }
        if (!empty($def['pk'])) {
            $parts[] = 'PRIMARY KEY (' . $this->identList($def['pk']) . ')';
        }

        $sql = 'CREATE TABLE ' . $this->quoteIdent($table) . " (\n  " . implode(",\n  ", $parts) . "\n)";
        if ($this->dialect === self::MYSQL) {
            $sql .= ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        }

        $stmts = [$sql];
        foreach (($def['indexes'] ?? []) as $ix) {
            $stmts[] = 'CREATE INDEX ' . $this->quoteIdent('ix_' . $table . '_' . implode('_', $ix))
                . ' ON ' . $this->quoteIdent($table) . ' (' . $this->identList($ix) . ')';
        }
        foreach (($def['uniques'] ?? []) as $ix) {
            $stmts[] = 'CREATE UNIQUE INDEX ' . $this->quoteIdent('uq_' . $table . '_' . implode('_', $ix))
                . ' ON ' . $this->quoteIdent($table) . ' (' . $this->identList($ix) . ')';
        }
        return $stmts;
    }

    public function dropTableSql(string $table): string
    {
        return 'DROP TABLE IF EXISTS ' . $this->quoteIdent($table);
    }

    /** ALTER ... ADD COLUMN for the migration ladder (same spec grammar). */
    public function addColumnSql(string $table, string $name, array $spec): string
    {
        return 'ALTER TABLE ' . $this->quoteIdent($table)
            . ' ADD COLUMN ' . $this->columnSql($name, is_array($spec) ? $spec : [$spec], false);
    }

    /** @param list<string> $cols */
    private function identList(array $cols): string
    {
        return implode(', ', array_map(fn($c) => $this->quoteIdent((string)$c), $cols));
    }

    private function columnSql(string $name, array $spec, bool $indexed): string
    {
        $type = (string)$spec[0];
        $q = $this->quoteIdent($name);

        if ($type === 'pk') {
            return $this->dialect === self::MYSQL
                ? "$q BIGINT AUTO_INCREMENT PRIMARY KEY"
                : "$q INTEGER PRIMARY KEY AUTOINCREMENT";
        }

        $short = $indexed || !empty($spec['short']);
        $sqlType = $this->typeSql($type, $short);
        $out = "$q $sqlType";

        if (!empty($spec['nocase']) && $this->dialect === self::SQLITE) {
            $out .= ' COLLATE NOCASE';
        }
        if (empty($spec['null'])) {
            $out .= ' NOT NULL';
        }
        if (array_key_exists('default', $spec) && !$this->defaultForbidden($type, $short)) {
            $out .= ' DEFAULT ' . $this->defaultSql($spec['default']);
        }
        if (!empty($spec['unique'])) {
            $out .= ' UNIQUE';
        }
        return $out;
    }

    private function typeSql(string $type, bool $short): string
    {
        if ($this->dialect === self::MYSQL) {
            return match ($type) {
                'int' => 'BIGINT',
                'bool' => 'TINYINT(1)',
                'datetime' => 'VARCHAR(32)',
                'blob' => 'MEDIUMBLOB',
                'text' => $short ? 'VARCHAR(191)' : 'MEDIUMTEXT',
                default => throw new \InvalidArgumentException("Unknown column type: $type"),
            };
        }
        return match ($type) {
            'int', 'bool' => 'INTEGER',
            'datetime', 'text' => 'TEXT',
            'blob' => 'BLOB',
            default => throw new \InvalidArgumentException("Unknown column type: $type"),
        };
    }

    /** MySQL refuses literal DEFAULTs on TEXT/BLOB-family columns. */
    private function defaultForbidden(string $type, bool $short): bool
    {
        return $this->dialect === self::MYSQL && !$short && in_array($type, ['text', 'blob'], true);
    }

    private function defaultSql(mixed $v): string
    {
        if (is_bool($v)) {
            return $v ? '1' : '0';
        }
        if (is_int($v) || is_float($v)) {
            return (string)$v;
        }
        return "'" . str_replace("'", "''", (string)$v) . "'";
    }
}
