<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

/**
 * PostgreSQL adapter for PDO_PGSQL.
 *
 * NOT `final`: the Postgres wire protocol is the base for a family of
 * Postgres-compatible engines (CockroachDB, and the managed Neon/Supabase
 * offerings). Those ship as thin subclasses that override id()/label() and any
 * engine-specific quirks — see CockroachDbDialect. Keep the SQL here strictly
 * standard-Postgres so the subclasses inherit correct behaviour by default.
 */
class PostgreSqlDialect extends AbstractDatabaseDialect
{
    public function id(): string { return 'pgsql'; }
    public function label(): string { return 'PostgreSQL'; }

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

    protected function fieldTypes(): array
    {
        return ['VARCHAR', 'CHAR', 'TEXT', 'SMALLINT', 'INT', 'BIGINT', 'DECIMAL', 'REAL', 'DOUBLE', 'BOOLEAN', 'DATE', 'TIME', 'TIMESTAMP', 'TIMESTAMPTZ', 'UUID', 'JSON', 'JSONB', 'BYTEA'];
    }

    protected function noLengthTypes(): array
    {
        return ['TEXT', 'SMALLINT', 'INT', 'BIGINT', 'REAL', 'DOUBLE', 'BOOLEAN', 'DATE', 'TIME', 'TIMESTAMP', 'TIMESTAMPTZ', 'UUID', 'JSON', 'JSONB', 'BYTEA'];
    }

    protected function typeSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        return match ($type) {
            'VARCHAR', 'CHAR' => $type . '(' . max(1, (int)$field['size']) . ')',
            'DECIMAL' => 'NUMERIC(' . max(1, (int)$field['size']) . ', ' . max(0, (int)$field['scale']) . ')',
            'INT' => 'INTEGER',
            'DOUBLE' => 'DOUBLE PRECISION',
            'TIMESTAMPTZ' => 'TIMESTAMP WITH TIME ZONE',
            default => $type,
        };
    }

    protected function autoIncrementSql(array $field, bool $forAlter): string
    {
        return !empty($field['auto_increment']) ? ' GENERATED BY DEFAULT AS IDENTITY' : '';
    }

    protected function booleanLiteral(mixed $value): string
    {
        $literal = parent::booleanLiteral($value);
        return $literal === '1' ? 'TRUE' : 'FALSE';
    }

    protected function qualifiedTable(string $table, array $config): string
    {
        return $this->quoteIdentifier($this->schema($config)) . '.' . $this->quoteIdentifier($table);
    }

    private function schema(array $config): string
    {
        $schema = trim((string)($config['schema'] ?? 'public'));
        return $schema !== '' ? $schema : 'public';
    }

    public function listObjects(\PDO $pdo, array $config): array
    {
        $stmt = $pdo->prepare("SELECT c.relname AS name, CASE WHEN c.relkind='v' THEN 'VIEW' ELSE 'TABLE' END AS type
            FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
            WHERE n.nspname=? AND c.relkind IN ('r','p','v') ORDER BY c.relname");
        $stmt->execute([$this->schema($config)]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function listColumns(\PDO $pdo, string $table, array $config): array
    {
        $schema = $this->schema($config);
        $stmt = $pdo->prepare("SELECT c.column_name, c.data_type, c.udt_name, c.character_maximum_length, c.numeric_precision, c.numeric_scale,
                c.is_nullable, c.column_default, c.is_identity,
                EXISTS (SELECT 1 FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
                    ON tc.constraint_name=kcu.constraint_name AND tc.table_schema=kcu.table_schema
                    WHERE tc.constraint_type='PRIMARY KEY' AND tc.table_schema=c.table_schema AND tc.table_name=c.table_name AND kcu.column_name=c.column_name) AS is_primary
            FROM information_schema.columns c WHERE c.table_schema=? AND c.table_name=? ORDER BY c.ordinal_position");
        $stmt->execute([$schema, $table]);
        $columns = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $column) {
            $type = $this->canonicalType((string)$column['data_type'], (string)$column['udt_name']);
            $auto = strtoupper((string)$column['is_identity']) === 'YES' || str_starts_with(strtolower((string)$column['column_default']), 'nextval(');
            $columns[] = $this->normalizedColumn(
                (string)$column['column_name'], $type, $this->typeDisplay($type, $column),
                strtoupper((string)$column['is_nullable']) === 'YES', $auto ? null : $column['column_default'],
                $this->toBool($column['is_primary']), $auto,
                $column['character_maximum_length'] !== null ? (int)$column['character_maximum_length'] : null,
                $column['numeric_precision'] !== null ? (int)$column['numeric_precision'] : null,
                $column['numeric_scale'] !== null ? (int)$column['numeric_scale'] : null
            );
        }
        return $columns;
    }

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

        $indexStmt = $pdo->prepare("SELECT idx.relname AS index_name, att.attname AS column_name, ix.indisunique
            FROM pg_catalog.pg_index ix JOIN pg_catalog.pg_class tbl ON tbl.oid=ix.indrelid
            JOIN pg_catalog.pg_namespace ns ON ns.oid=tbl.relnamespace JOIN pg_catalog.pg_class idx ON idx.oid=ix.indexrelid
            JOIN LATERAL unnest(ix.indkey) WITH ORDINALITY AS keys(attnum, ord) ON TRUE
            JOIN pg_catalog.pg_attribute att ON att.attrelid=tbl.oid AND att.attnum=keys.attnum
            WHERE ns.nspname=? AND tbl.relname=? ORDER BY idx.relname, keys.ord");
        $indexStmt->execute([$schema, $table]);
        $indexes = array_map(fn(array $index): array => $this->normalizedIndex((string)$index['index_name'], (string)$index['column_name'], $this->toBool($index['indisunique'])), $indexStmt->fetchAll(\PDO::FETCH_ASSOC));

        $fkStmt = $pdo->prepare("SELECT tc.constraint_name, kcu.column_name, ccu.table_name AS master_table, ccu.column_name AS master_field,
                rc.update_rule, rc.delete_rule
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu ON tc.constraint_name=kcu.constraint_name AND tc.constraint_schema=kcu.constraint_schema
            JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name=tc.constraint_name AND ccu.constraint_schema=tc.constraint_schema
            JOIN information_schema.referential_constraints rc ON rc.constraint_name=tc.constraint_name AND rc.constraint_schema=tc.constraint_schema
            WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema=? AND tc.table_name=?");
        $fkStmt->execute([$schema, $table]);
        $foreignKeys = array_map(static fn(array $fk): array => [
            'constraint_name' => (string)$fk['constraint_name'], 'detail_table' => $table, 'detail_field' => (string)$fk['column_name'],
            'master_table' => (string)$fk['master_table'], 'master_field' => (string)$fk['master_field'],
            'on_update_action' => (string)$fk['update_rule'], 'on_delete_action' => (string)$fk['delete_rule'],
        ], $fkStmt->fetchAll(\PDO::FETCH_ASSOC));

        return ['columns' => $columns, 'indexes' => $indexes, 'foreign_keys' => $foreignKeys];
    }

    public function updateTable(\PDO $pdo, string $oldTable, string $newTable, array $fields, array $currentDetails, array $config): void
    {
        $schema = $this->schema($config);
        $this->transaction($pdo, function () use ($pdo, $oldTable, $newTable, $fields, $currentDetails, $config, $schema): void {
            $table = $oldTable;
            if ($oldTable !== $newTable) {
                $pdo->exec('ALTER TABLE ' . $this->qualifiedTable($oldTable, $config) . ' RENAME TO ' . $this->quoteIdentifier($newTable));
                $table = $newTable;
            }
            $qualified = $this->qualifiedTable($table, $config);
            $current = [];
            foreach ((array)$currentDetails['columns'] as $column) $current[(string)$column['COLUMN_NAME']] = $column;
            $oldPrimary = array_values(array_map(static fn(array $column): string => (string)$column['COLUMN_NAME'], array_filter($current, static fn(array $column): bool => !empty($column['primary_key']))));
            $newPrimary = array_values(array_map(static fn(array $field): string => (string)$field['name'], array_filter($fields, static fn(array $field): bool => !empty($field['primary_key']))));
            if ($oldPrimary !== $newPrimary && $oldPrimary !== []) {
                $pk = $pdo->prepare("SELECT tc.constraint_name FROM information_schema.table_constraints tc WHERE tc.table_schema=? AND tc.table_name=? AND tc.constraint_type='PRIMARY KEY'");
                $pk->execute([$schema, $table]);
                if ($name = $pk->fetchColumn()) $pdo->exec('ALTER TABLE ' . $qualified . ' DROP CONSTRAINT ' . $this->quoteIdentifier((string)$name));
            }

            foreach ($fields as $field) {
                $original = (string)$field['original_name'];
                if (isset($current[$original]) && $original !== $field['name']) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' RENAME COLUMN ' . $this->quoteIdentifier($original) . ' TO ' . $this->quoteIdentifier((string)$field['name']));
                    $current[(string)$field['name']] = $current[$original];
                    unset($current[$original]);
                }
            }

            $kept = array_map(static fn(array $field): string => (string)$field['name'], $fields);
            foreach (array_keys($current) as $name) {
                if (!in_array($name, $kept, true)) $pdo->exec('ALTER TABLE ' . $qualified . ' DROP COLUMN ' . $this->quoteIdentifier($name));
            }

            foreach ($fields as $field) {
                $name = (string)$field['name'];
                if (!isset($current[$name])) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ADD COLUMN ' . $this->compileColumn($field, true));
                    continue;
                }
                $old = $current[$name];
                $oldAuto = !empty($old['auto_increment']);
                $newAuto = !empty($field['auto_increment']);
                if ($oldAuto && !$newAuto) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' DROP IDENTITY IF EXISTS');
                }
                $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' TYPE ' . $this->typeSql($field) . ' USING ' . $this->quoteIdentifier($name) . '::' . $this->typeSql($field));
                if (!$oldAuto && $newAuto) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' DROP DEFAULT');
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' ADD GENERATED BY DEFAULT AS IDENTITY');
                }
                $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . (!empty($field['nullable']) ? ' DROP NOT NULL' : ' SET NOT NULL'));
                if (!$newAuto) {
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' DROP DEFAULT');
                    $defaultSql = $this->defaultSql($field);
                    if ($defaultSql !== '') $pdo->exec('ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' SET' . $defaultSql);
                }
            }
            if ($oldPrimary !== $newPrimary && $newPrimary !== []) {
                $pdo->exec('ALTER TABLE ' . $qualified . ' ADD PRIMARY KEY (' . implode(', ', array_map([$this, 'quoteIdentifier'], $newPrimary)) . ')');
            }
        });
    }

    public function createView(\PDO $pdo, string $name, string $selectSql, bool $replace, array $config): void
    {
        $pdo->exec('CREATE ' . ($replace ? 'OR REPLACE ' : '') . 'VIEW ' . $this->qualifiedTable($name, $config) . ' AS ' . $selectSql);
    }

    public function dropForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $constraint = trim((string)($relation['constraint_name'] ?? ''));
        if ($constraint === '') {
            $stmt = $pdo->prepare("SELECT tc.constraint_name
                FROM information_schema.table_constraints tc
                JOIN information_schema.key_column_usage kcu ON kcu.constraint_name=tc.constraint_name AND kcu.constraint_schema=tc.constraint_schema
                JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name=tc.constraint_name AND ccu.constraint_schema=tc.constraint_schema
                WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema=? AND tc.table_name=?
                  AND kcu.column_name=? AND ccu.table_name=? AND ccu.column_name=?");
            $stmt->execute([$this->schema($config), (string)$relation['detail_table'], (string)$relation['detail_field'], (string)$relation['master_table'], (string)$relation['master_field']]);
            $constraint = (string)($stmt->fetchColumn() ?: '');
        }
        if ($constraint === '') return;
        $pdo->exec('ALTER TABLE ' . $this->qualifiedTable((string)$relation['detail_table'], $config) . ' DROP CONSTRAINT IF EXISTS ' . $this->quoteIdentifier($constraint));
    }

    private function canonicalType(string $dataType, string $udt): string
    {
        return match (strtolower($udt ?: $dataType)) {
            'varchar', 'character varying' => 'VARCHAR', 'bpchar', 'character' => 'CHAR', 'text' => 'TEXT',
            'int2', 'smallint' => 'SMALLINT', 'int4', 'integer' => 'INT', 'int8', 'bigint' => 'BIGINT',
            'numeric', 'decimal' => 'DECIMAL', 'float4', 'real' => 'REAL', 'float8', 'double precision' => 'DOUBLE',
            'bool', 'boolean' => 'BOOLEAN', 'date' => 'DATE', 'time', 'time without time zone' => 'TIME',
            'timestamp', 'timestamp without time zone' => 'TIMESTAMP', 'timestamptz', 'timestamp with time zone' => 'TIMESTAMPTZ',
            'uuid' => 'UUID', 'json' => 'JSON', 'jsonb' => 'JSONB', 'bytea' => 'BYTEA', default => 'TEXT',
        };
    }

    private function typeDisplay(string $type, array $column): string
    {
        if (in_array($type, ['VARCHAR', 'CHAR'], true) && $column['character_maximum_length'] !== null) return $type . '(' . $column['character_maximum_length'] . ')';
        if ($type === 'DECIMAL' && $column['numeric_precision'] !== null) return 'DECIMAL(' . $column['numeric_precision'] . ',' . ($column['numeric_scale'] ?? 0) . ')';
        return $type;
    }

    private function toBool(mixed $value): bool
    {
        return in_array(strtolower((string)$value), ['1', 't', 'true', 'yes', 'y'], true);
    }
}
