<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

final class SQLiteDialect extends AbstractDatabaseDialect
{
    public function id(): string { return 'sqlite'; }
    public function label(): string { return 'SQLite'; }

    public function quoteIdentifier(string $identifier): string
    {
        return '"' . str_replace('"', '""', $identifier) . '"';
    }

    protected function fieldTypes(): array
    {
        return ['TEXT', 'VARCHAR', 'CHAR', 'INTEGER', 'INT', 'BIGINT', 'REAL', 'NUMERIC', 'DECIMAL', 'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'JSON', 'BLOB'];
    }

    protected function integerTypes(): array { return ['INTEGER', 'INT', 'BIGINT']; }

    protected function sizedTypes(): array { return ['VARCHAR', 'CHAR', 'NUMERIC', 'DECIMAL']; }

    protected function noLengthTypes(): array { return ['TEXT', 'INTEGER', 'INT', 'BIGINT', 'REAL', 'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'JSON', 'BLOB']; }

    protected function capabilities(): array
    {
        $capabilities = parent::capabilities();
        $capabilities['replace_views'] = true;
        $capabilities['table_rebuild_on_edit'] = true;
        return $capabilities;
    }

    protected function typeSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        return match ($type) {
            'INTEGER', 'INT', 'BIGINT' => 'INTEGER',
            'REAL' => 'REAL',
            'NUMERIC', 'DECIMAL' => $type . '(' . max(1, (int)$field['size']) . ', ' . max(0, (int)$field['scale']) . ')',
            'VARCHAR', 'CHAR' => $type . '(' . max(1, (int)$field['size']) . ')',
            'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'JSON' => $type,
            'BLOB' => 'BLOB',
            default => 'TEXT',
        };
    }

    protected function compileColumn(array $field, bool $forAlter): string
    {
        if (!empty($field['auto_increment'])) {
            return $this->quoteIdentifier((string)$field['name']) . ' INTEGER PRIMARY KEY AUTOINCREMENT';
        }
        return parent::compileColumn($field, $forAlter);
    }

    protected function primaryKeyIsInline(array $field): bool
    {
        return !empty($field['auto_increment']);
    }

    protected function columnCheckSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        $name = $this->quoteIdentifier((string)$field['name']);
        if ($type === 'BOOLEAN') {
            return ' CHECK (' . $name . ' IN (0, 1))';
        }
        if ($type === 'JSON') {
            return ' CHECK (' . $name . ' IS NULL OR json_valid(' . $name . '))';
        }
        return '';
    }

    public function listObjects(\PDO $pdo, array $config): array
    {
        $rows = $pdo->query("SELECT name, CASE type WHEN 'view' THEN 'VIEW' ELSE 'TABLE' END AS type FROM sqlite_schema WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY name")->fetchAll(\PDO::FETCH_ASSOC);
        return array_map(static fn(array $row): array => ['name' => (string)$row['name'], 'type' => (string)$row['type']], $rows);
    }

    public function listColumns(\PDO $pdo, string $table, array $config): array
    {
        $createStmt = $pdo->prepare("SELECT sql FROM sqlite_schema WHERE name=? AND type IN ('table','view')");
        $createStmt->execute([$table]);
        $createSql = (string)($createStmt->fetchColumn() ?: '');

        $pragma = $pdo->query('PRAGMA table_info(' . $this->quoteIdentifier($table) . ')');
        $columns = [];
        foreach ($pragma->fetchAll(\PDO::FETCH_ASSOC) as $column) {
            $rawType = strtoupper(trim((string)$column['type']));
            $type = $this->canonicalType($rawType);
            [$length, $precision, $scale] = $this->typeDimensions($rawType);
            $quotedName = preg_quote((string)$column['name'], '/');
            $auto = (int)$column['pk'] > 0
                && preg_match('/(?:["`\[])?' . $quotedName . '(?:["`\]])?\s+INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/i', $createSql) === 1;
            $columns[] = $this->normalizedColumn(
                (string)$column['name'], $type, $rawType ?: $type, !(bool)$column['notnull'] && !(bool)$column['pk'],
                $column['dflt_value'], (int)$column['pk'] > 0, $auto, $length, $precision, $scale
            );
        }
        return $columns;
    }

    public function describeTable(\PDO $pdo, string $table, array $config): array
    {
        $columns = $this->listColumns($pdo, $table, $config);

        $indexes = [];
        foreach ($this->indexDefinitions($pdo, $table) as $definition) {
            foreach ($definition['columns'] as $column) {
                $indexes[] = $this->normalizedIndex($definition['name'], $column, $definition['unique']);
            }
        }

        $foreignKeys = $this->foreignKeys($pdo, $table);
        return ['columns' => $columns, 'indexes' => $indexes, 'foreign_keys' => $foreignKeys];
    }

    public function updateTable(\PDO $pdo, string $oldTable, string $newTable, array $fields, array $currentDetails, array $config): void
    {
        $table = $oldTable;
        if ($oldTable !== $newTable) {
            $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($oldTable) . ' RENAME TO ' . $this->quoteIdentifier($newTable));
            $table = $newTable;
        }
        $foreignKeys = (array)($currentDetails['foreign_keys'] ?? $this->foreignKeys($pdo, $table));
        $renameMap = [];
        foreach ($fields as $field) {
            if ($field['original_name'] !== $field['name']) $renameMap[(string)$field['original_name']] = (string)$field['name'];
        }
        foreach ($foreignKeys as &$fk) {
            if (isset($renameMap[$fk['detail_field']])) $fk['detail_field'] = $renameMap[$fk['detail_field']];
        }
        unset($fk);
        $valid = array_map(static fn(array $field): string => (string)$field['name'], $fields);
        $foreignKeys = array_values(array_filter($foreignKeys, static fn(array $fk): bool => in_array((string)$fk['detail_field'], $valid, true)));
        $this->rebuildTable($pdo, $table, $fields, $foreignKeys, $renameMap);
    }

    public function addForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $table = (string)$relation['detail_table'];
        $details = $this->describeTable($pdo, $table, $config);
        $foreignKeys = (array)$details['foreign_keys'];
        $relation['constraint_name'] = $relation['constraint_name'] ?? $this->constraintName($table, (string)$relation['detail_field']);
        foreach ($foreignKeys as $existing) {
            if ($existing['detail_field'] === $relation['detail_field'] && $existing['master_table'] === $relation['master_table'] && $existing['master_field'] === $relation['master_field']) return;
        }
        $foreignKeys[] = $relation;
        $this->rebuildTable($pdo, $table, $this->fieldsFromColumns($details['columns']), $foreignKeys, []);
    }

    public function dropForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $table = (string)$relation['detail_table'];
        $details = $this->describeTable($pdo, $table, $config);
        $foreignKeys = array_values(array_filter((array)$details['foreign_keys'], static fn(array $fk): bool => !(
            (string)$fk['detail_field'] === (string)$relation['detail_field']
            && (string)$fk['master_table'] === (string)$relation['master_table']
            && (string)$fk['master_field'] === (string)$relation['master_field']
        )));
        $this->rebuildTable($pdo, $table, $this->fieldsFromColumns($details['columns']), $foreignKeys, []);
    }

    private function rebuildTable(\PDO $pdo, string $table, array $fields, array $foreignKeys, array $renameMap): void
    {
        $temp = '__pp_new_' . substr(sha1($table . microtime(true)), 0, 10);
        $dependencies = $this->dependencies($pdo, $table);
        $foreignKeysEnabled = (int)$pdo->query('PRAGMA foreign_keys')->fetchColumn() === 1;
        if ($foreignKeysEnabled) $pdo->exec('PRAGMA foreign_keys=OFF');

        try {
            $this->transaction($pdo, function () use ($pdo, $table, $temp, $fields, $foreignKeys, $renameMap, $dependencies): void {
                $pdo->exec($this->compileCreateTable($temp, $fields, [], $foreignKeys));
                $currentNames = array_column($pdo->query('PRAGMA table_info(' . $this->quoteIdentifier($table) . ')')->fetchAll(\PDO::FETCH_ASSOC), 'name');
                $targets = [];
                $sources = [];
                foreach ($fields as $field) {
                    $source = (string)$field['original_name'];
                    if (in_array($source, $currentNames, true)) {
                        $targets[] = $this->quoteIdentifier((string)$field['name']);
                        $sources[] = $this->quoteIdentifier($source);
                    }
                }
                if ($targets !== []) {
                    $pdo->exec('INSERT INTO ' . $this->quoteIdentifier($temp) . ' (' . implode(', ', $targets) . ') SELECT ' . implode(', ', $sources) . ' FROM ' . $this->quoteIdentifier($table));
                }
                $pdo->exec('DROP TABLE ' . $this->quoteIdentifier($table));
                $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($temp) . ' RENAME TO ' . $this->quoteIdentifier($table));
                foreach ($dependencies as $sql) {
                    $rewritten = $this->rewriteDependencySql($sql, $renameMap);
                    if ($rewritten !== '') $pdo->exec($rewritten);
                }
                $violations = $pdo->query('PRAGMA foreign_key_check')->fetchAll(\PDO::FETCH_ASSOC);
                if ($violations !== []) {
                    throw new \RuntimeException('SQLite foreign-key validation failed while rebuilding the table.');
                }
            });
        } finally {
            if ($foreignKeysEnabled) $pdo->exec('PRAGMA foreign_keys=ON');
        }
    }

    private function dependencies(\PDO $pdo, string $table): array
    {
        $stmt = $pdo->prepare("SELECT sql FROM sqlite_schema WHERE tbl_name=? AND type IN ('index','trigger') AND sql IS NOT NULL ORDER BY type, name");
        $stmt->execute([$table]);
        return array_values(array_filter(array_map('strval', $stmt->fetchAll(\PDO::FETCH_COLUMN))));
    }

    private function rewriteDependencySql(string $sql, array $renameMap): string
    {
        foreach ($renameMap as $old => $new) {
            $sql = str_replace(['"' . $old . '"', '`' . $old . '`', '[' . $old . ']'], ['"' . $new . '"', '`' . $new . '`', '[' . $new . ']'], $sql);
            $sql = preg_replace('/\b' . preg_quote($old, '/') . '\b/', $new, $sql) ?? $sql;
        }
        return $sql;
    }

    private function indexDefinitions(\PDO $pdo, string $table): array
    {
        $definitions = [];
        foreach ($pdo->query('PRAGMA index_list(' . $this->quoteIdentifier($table) . ')')->fetchAll(\PDO::FETCH_ASSOC) as $index) {
            $columns = array_column($pdo->query('PRAGMA index_info(' . $this->quoteIdentifier((string)$index['name']) . ')')->fetchAll(\PDO::FETCH_ASSOC), 'name');
            $definitions[] = ['name' => (string)$index['name'], 'unique' => (bool)$index['unique'], 'columns' => array_map('strval', $columns)];
        }
        return $definitions;
    }

    private function foreignKeys(\PDO $pdo, string $table): array
    {
        $rows = $pdo->query('PRAGMA foreign_key_list(' . $this->quoteIdentifier($table) . ')')->fetchAll(\PDO::FETCH_ASSOC);
        return array_map(fn(array $fk): array => [
            'constraint_name' => $this->constraintName($table, (string)$fk['from']),
            'detail_table' => $table, 'detail_field' => (string)$fk['from'],
            'master_table' => (string)$fk['table'], 'master_field' => (string)$fk['to'],
            'on_update_action' => strtoupper((string)$fk['on_update']), 'on_delete_action' => strtoupper((string)$fk['on_delete']),
        ], $rows);
    }

    private function fieldsFromColumns(array $columns): array
    {
        return array_map(static fn(array $column): array => [
            'name' => $column['name'], 'original_name' => $column['name'], 'type' => $column['type'],
            'size' => $column['size'], 'scale' => $column['scale'] ?? 0, 'nullable' => $column['nullable'],
            'default_mode' => $column['default_mode'], 'default' => $column['default'],
            'default_expression' => $column['default_expression'], 'primary_key' => $column['primary_key'],
            'auto_increment' => $column['auto_increment'], 'enum_values' => $column['enum_values'] ?? '',
        ], $columns);
    }

    private function canonicalType(string $raw): string
    {
        if (preg_match('/^DATETIME\b/', $raw)) return 'DATETIME';
        if (preg_match('/^DATE\b/', $raw)) return 'DATE';
        if (preg_match('/^TIME\b/', $raw)) return 'TIME';
        if (preg_match('/^JSON\b/', $raw)) return 'JSON';
        if (preg_match('/^BOOLEAN\b|^BOOL\b/', $raw)) return 'BOOLEAN';
        if (preg_match('/^VARCHAR\b/', $raw)) return 'VARCHAR';
        if (preg_match('/^CHAR\b/', $raw)) return 'CHAR';
        if (preg_match('/^DECIMAL\b/', $raw)) return 'DECIMAL';
        if (preg_match('/^NUMERIC\b/', $raw)) return 'NUMERIC';
        if (str_contains($raw, 'INT')) return 'INTEGER';
        if (str_contains($raw, 'CHAR') || str_contains($raw, 'CLOB') || str_contains($raw, 'TEXT')) return 'TEXT';
        if (str_contains($raw, 'BLOB')) return 'BLOB';
        if (str_contains($raw, 'REAL') || str_contains($raw, 'FLOA') || str_contains($raw, 'DOUB')) return 'REAL';
        return 'NUMERIC';
    }

    private function typeDimensions(string $raw): array
    {
        if (preg_match('/\((\d+)(?:\s*,\s*(\d+))?\)/', $raw, $match)) {
            return [(int)$match[1], (int)$match[1], isset($match[2]) ? (int)$match[2] : 0];
        }
        return [null, null, null];
    }
}
