<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

/** Microsoft SQL Server adapter for PDO_SQLSRV and PDO_DBLIB connections. */
final class SqlServerDialect extends AbstractDatabaseDialect
{
    public function id(): string { return 'sqlsrv'; }

    public function label(): string { return 'Microsoft SQL Server'; }

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

    protected function fieldTypes(): array
    {
        return [
            'VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR', 'TEXT', 'TINYINT', 'SMALLINT', 'INT', 'BIGINT',
            'DECIMAL', 'FLOAT', 'REAL', 'BIT', 'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'DATETIME2',
            'DATETIMEOFFSET', 'UNIQUEIDENTIFIER', 'XML', 'JSON', 'BINARY', 'VARBINARY', 'BLOB',
        ];
    }

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

    protected function noLengthTypes(): array
    {
        return [
            'TEXT', 'TINYINT', 'SMALLINT', 'INT', 'BIGINT', 'FLOAT', 'REAL', 'BIT', 'BOOLEAN',
            'DATE', 'TIME', 'DATETIME', 'DATETIME2', 'DATETIMEOFFSET', 'UNIQUEIDENTIFIER', 'XML',
            'JSON', 'BLOB',
        ];
    }

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

    protected function referentialActions(): array
    {
        return ['CASCADE', 'NO ACTION', 'SET NULL', 'SET DEFAULT'];
    }

    protected function typeSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        return match ($type) {
            'VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR', 'BINARY', 'VARBINARY' => $type . '(' . max(1, (int)$field['size']) . ')',
            'DECIMAL' => 'DECIMAL(' . max(1, (int)$field['size']) . ', ' . max(0, (int)$field['scale']) . ')',
            'TEXT', 'JSON' => 'NVARCHAR(MAX)',
            'BLOB' => 'VARBINARY(MAX)',
            'BOOLEAN' => 'BIT',
            default => $type,
        };
    }

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

    protected function currentTimestampSql(): string
    {
        return 'SYSDATETIME()';
    }

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

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

    protected function referentialAction(string $action): string
    {
        $action = parent::referentialAction($action);
        return $action === 'RESTRICT' ? 'NO ACTION' : $action;
    }

    public function listObjects(\PDO $pdo, array $config): array
    {
        $stmt = $pdo->prepare(
            "SELECT o.name, CASE o.type WHEN 'V' THEN 'VIEW' ELSE 'TABLE' END AS type
             FROM sys.objects o
             JOIN sys.schemas s ON s.schema_id=o.schema_id
             WHERE s.name=? AND o.type IN ('U','V') AND o.is_ms_shipped=0
             ORDER BY o.name"
        );
        $stmt->execute([$this->schema($config)]);
        return array_map(static fn(array $row): array => [
            'name' => (string)$row['name'],
            'type' => strtoupper((string)$row['type']) === 'VIEW' ? 'VIEW' : 'TABLE',
        ], $stmt->fetchAll(\PDO::FETCH_ASSOC));
    }

    public function listColumns(\PDO $pdo, string $table, array $config): array
    {
        $schema = $this->schema($config);
        $columnsStmt = $pdo->prepare(
            "SELECT c.name AS column_name, t.name AS data_type, c.max_length, c.precision, c.scale,
                    c.is_nullable, dc.definition AS column_default, c.is_identity,
                    CASE WHEN pk.column_id IS NULL THEN 0 ELSE 1 END AS is_primary
             FROM sys.columns c
             JOIN sys.types t ON t.user_type_id=c.user_type_id
             JOIN sys.objects tb ON tb.object_id=c.object_id AND tb.type IN ('U','V')
             JOIN sys.schemas s ON s.schema_id=tb.schema_id
             LEFT JOIN sys.default_constraints dc ON dc.object_id=c.default_object_id
             LEFT JOIN (
                 SELECT ic.object_id, ic.column_id
                 FROM sys.indexes i
                 JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
                 WHERE i.is_primary_key=1
             ) pk ON pk.object_id=c.object_id AND pk.column_id=c.column_id
             WHERE s.name=? AND tb.name=? ORDER BY c.column_id"
        );
        $columnsStmt->execute([$schema, $table]);
        $jsonColumns = $this->jsonColumns($pdo, $schema, $table);
        $columns = [];
        foreach ($columnsStmt->fetchAll(\PDO::FETCH_ASSOC) as $column) {
            $type = $this->canonicalType((string)$column['data_type'], (int)$column['max_length']);
            if (isset($jsonColumns[strtolower((string)$column['column_name'])])) {
                $type = 'JSON';
            }
            $length = $this->characterLength($type, (int)$column['max_length']);
            $precision = in_array($type, ['DECIMAL'], true) ? (int)$column['precision'] : null;
            $scale = in_array($type, ['DECIMAL'], true) ? (int)$column['scale'] : null;
            $columns[] = $this->normalizedColumn(
                (string)$column['column_name'],
                $type,
                $this->typeDisplay($type, $length, $precision, $scale),
                (bool)$column['is_nullable'],
                $this->unwrapDefault($column['column_default']),
                (bool)$column['is_primary'],
                (bool)$column['is_identity'],
                $length,
                $precision,
                $scale
            );
        }
        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 i.name AS index_name, c.name AS column_name, i.is_unique
             FROM sys.indexes i
             JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
             JOIN sys.columns c ON c.object_id=ic.object_id AND c.column_id=ic.column_id
             JOIN sys.tables tb ON tb.object_id=i.object_id
             JOIN sys.schemas s ON s.schema_id=tb.schema_id
             WHERE s.name=? AND tb.name=? AND i.name IS NOT NULL AND ic.key_ordinal>0
             ORDER BY i.name, ic.key_ordinal"
        );
        $indexStmt->execute([$schema, $table]);
        $indexes = array_map(
            fn(array $index): array => $this->normalizedIndex((string)$index['index_name'], (string)$index['column_name'], (bool)$index['is_unique']),
            $indexStmt->fetchAll(\PDO::FETCH_ASSOC)
        );

        $foreignStmt = $pdo->prepare(
            "SELECT fk.name AS constraint_name, child_col.name AS detail_field,
                    parent_table.name AS master_table, parent_col.name AS master_field,
                    fk.update_referential_action_desc AS update_rule,
                    fk.delete_referential_action_desc AS delete_rule
             FROM sys.foreign_keys fk
             JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id=fk.object_id
             JOIN sys.tables child_table ON child_table.object_id=fk.parent_object_id
             JOIN sys.schemas child_schema ON child_schema.schema_id=child_table.schema_id
             JOIN sys.columns child_col ON child_col.object_id=fkc.parent_object_id AND child_col.column_id=fkc.parent_column_id
             JOIN sys.tables parent_table ON parent_table.object_id=fk.referenced_object_id
             JOIN sys.columns parent_col ON parent_col.object_id=fkc.referenced_object_id AND parent_col.column_id=fkc.referenced_column_id
             WHERE child_schema.name=? AND child_table.name=? ORDER BY fk.name, fkc.constraint_column_id"
        );
        $foreignStmt->execute([$schema, $table]);
        $foreignKeys = array_map(static fn(array $fk): array => [
            'constraint_name' => (string)$fk['constraint_name'],
            'detail_table' => $table,
            'detail_field' => (string)$fk['detail_field'],
            'master_table' => (string)$fk['master_table'],
            'master_field' => (string)$fk['master_field'],
            'on_update_action' => str_replace('_', ' ', strtoupper((string)$fk['update_rule'])),
            'on_delete_action' => str_replace('_', ' ', strtoupper((string)$fk['delete_rule'])),
        ], $foreignStmt->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("EXEC sp_rename N'" . $this->escapeSqlString($schema . '.' . $oldTable) . "', N'" . $this->escapeSqlString($newTable) . "'");
                $table = $newTable;
            }
            $qualified = $this->qualifiedTable($table, $config);
            $current = [];
            foreach ((array)($currentDetails['columns'] ?? []) as $column) {
                $current[(string)$column['COLUMN_NAME']] = $column;
            }

            foreach ($fields as $field) {
                $original = (string)$field['original_name'];
                if (!isset($current[$original])) {
                    continue;
                }
                $oldAuto = !empty($current[$original]['auto_increment']);
                $newAuto = !empty($field['auto_increment']);
                if ($oldAuto !== $newAuto) {
                    throw new \RuntimeException("SQL Server cannot add or remove IDENTITY on existing field '{$original}'. Add a new identity field or rebuild the table.");
                }
                if ($original !== $field['name']) {
                    $pdo->exec("EXEC sp_rename N'" . $this->escapeSqlString($schema . '.' . $table . '.' . $original) . "', N'" . $this->escapeSqlString((string)$field['name']) . "', 'COLUMN'");
                    $current[(string)$field['name']] = $current[$original];
                    unset($current[$original]);
                }
            }

            $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 !== []) {
                $this->dropPrimaryKey($pdo, $schema, $table, $qualified);
            }

            $kept = array_map(static fn(array $field): string => (string)$field['name'], $fields);
            foreach (array_keys($current) as $name) {
                if (!in_array($name, $kept, true)) {
                    $this->dropDefaultConstraint($pdo, $schema, $table, $name, $qualified);
                    $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 ' . $this->compileColumn($field, true));
                    continue;
                }
                if (!empty($current[$name]['auto_increment']) && !empty($field['auto_increment'])) {
                    continue;
                }
                $this->dropDefaultConstraint($pdo, $schema, $table, $name, $qualified);
                $this->dropJsonConstraints($pdo, $schema, $table, $name, $qualified);
                $pdo->exec(
                    'ALTER TABLE ' . $qualified . ' ALTER COLUMN ' . $this->quoteIdentifier($name) . ' '
                    . $this->typeSql($field) . (!empty($field['nullable']) ? ' NULL' : ' NOT NULL')
                );
                $default = $this->defaultSql($field);
                if ($default !== '') {
                    $constraint = $this->constraintName($table, 'df_' . $name);
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ADD CONSTRAINT ' . $this->quoteIdentifier($constraint) . $default . ' FOR ' . $this->quoteIdentifier($name));
                }
                $check = $this->columnCheckSql($field);
                if ($check !== '') {
                    $constraint = $this->constraintName($table, 'json_' . $name);
                    $pdo->exec('ALTER TABLE ' . $qualified . ' ADD CONSTRAINT ' . $this->quoteIdentifier($constraint) . $check);
                }
            }
            if ($oldPrimary !== $newPrimary && $newPrimary !== []) {
                $constraint = $this->constraintName($table, 'pk');
                $pdo->exec('ALTER TABLE ' . $qualified . ' ADD CONSTRAINT ' . $this->quoteIdentifier($constraint)
                    . ' PRIMARY KEY (' . implode(', ', array_map([$this, 'quoteIdentifier'], $newPrimary)) . ')');
            }
        });
    }

    public function createView(\PDO $pdo, string $name, string $selectSql, bool $replace, array $config): void
    {
        $qualified = $this->qualifiedTable($name, $config);
        $exists = $pdo->prepare(
            "SELECT 1 FROM sys.views v JOIN sys.schemas s ON s.schema_id=v.schema_id WHERE s.name=? AND v.name=?"
        );
        $exists->execute([$this->schema($config), $name]);
        if ($replace && $exists->fetchColumn()) {
            $pdo->exec('ALTER VIEW ' . $qualified . ' AS ' . $selectSql);
            return;
        }
        $pdo->exec('CREATE VIEW ' . $qualified . ' AS ' . $selectSql);
    }

    public function dropForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $constraint = trim((string)($relation['constraint_name'] ?? ''));
        if ($constraint === '') {
            $constraint = $this->findForeignKeyName($pdo, $relation, $config);
        }
        if ($constraint === '') {
            return;
        }
        $pdo->exec('ALTER TABLE ' . $this->qualifiedTable((string)$relation['detail_table'], $config)
            . ' DROP CONSTRAINT ' . $this->quoteIdentifier($constraint));
    }

    public function dropObject(\PDO $pdo, string $name, string $type, array $config): void
    {
        $kind = strtoupper($type) === 'VIEW' ? 'VIEW' : 'TABLE';
        $qualified = $this->qualifiedTable($name, $config);
        $objectType = $kind === 'VIEW' ? 'V' : 'U';
        $pdo->exec("IF OBJECT_ID(N'" . $this->escapeSqlString($this->schema($config) . '.' . $name) . "', N'{$objectType}') IS NOT NULL DROP {$kind} {$qualified}");
    }

    private function dropDefaultConstraint(\PDO $pdo, string $schema, string $table, string $column, string $qualified): void
    {
        $stmt = $pdo->prepare(
            "SELECT dc.name FROM sys.default_constraints dc
             JOIN sys.columns c ON c.object_id=dc.parent_object_id AND c.column_id=dc.parent_column_id
             JOIN sys.tables t ON t.object_id=c.object_id JOIN sys.schemas s ON s.schema_id=t.schema_id
             WHERE s.name=? AND t.name=? AND c.name=?"
        );
        $stmt->execute([$schema, $table, $column]);
        $name = $stmt->fetchColumn();
        if ($name !== false) {
            $pdo->exec('ALTER TABLE ' . $qualified . ' DROP CONSTRAINT ' . $this->quoteIdentifier((string)$name));
        }
    }

    private function dropPrimaryKey(\PDO $pdo, string $schema, string $table, string $qualified): void
    {
        $stmt = $pdo->prepare(
            "SELECT kc.name FROM sys.key_constraints kc JOIN sys.tables t ON t.object_id=kc.parent_object_id
             JOIN sys.schemas s ON s.schema_id=t.schema_id WHERE kc.type='PK' AND s.name=? AND t.name=?"
        );
        $stmt->execute([$schema, $table]);
        $name = $stmt->fetchColumn();
        if ($name !== false) {
            $pdo->exec('ALTER TABLE ' . $qualified . ' DROP CONSTRAINT ' . $this->quoteIdentifier((string)$name));
        }
    }

    private function dropJsonConstraints(\PDO $pdo, string $schema, string $table, string $column, string $qualified): void
    {
        $stmt = $pdo->prepare(
            "SELECT cc.name, cc.definition FROM sys.check_constraints cc
             JOIN sys.tables t ON t.object_id=cc.parent_object_id JOIN sys.schemas s ON s.schema_id=t.schema_id
             WHERE s.name=? AND t.name=? AND UPPER(cc.definition) LIKE '%ISJSON%'"
        );
        $stmt->execute([$schema, $table]);
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $constraint) {
            $definition = (string)$constraint['definition'];
            if (preg_match('/ISJSON\s*\(\s*(?:\[' . preg_quote($column, '/') . '\]|' . preg_quote($column, '/') . ')\s*\)/i', $definition)) {
                $pdo->exec('ALTER TABLE ' . $qualified . ' DROP CONSTRAINT ' . $this->quoteIdentifier((string)$constraint['name']));
            }
        }
    }

    private function findForeignKeyName(\PDO $pdo, array $relation, array $config): string
    {
        $stmt = $pdo->prepare(
            "SELECT fk.name FROM sys.foreign_keys fk
             JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id=fk.object_id
             JOIN sys.tables t ON t.object_id=fk.parent_object_id JOIN sys.schemas s ON s.schema_id=t.schema_id
             JOIN sys.columns c ON c.object_id=fkc.parent_object_id AND c.column_id=fkc.parent_column_id
             JOIN sys.tables rt ON rt.object_id=fk.referenced_object_id
             JOIN sys.columns rc ON rc.object_id=fkc.referenced_object_id AND rc.column_id=fkc.referenced_column_id
             WHERE s.name=? AND t.name=? AND c.name=? AND rt.name=? AND rc.name=?"
        );
        $stmt->execute([
            $this->schema($config), (string)$relation['detail_table'], (string)$relation['detail_field'],
            (string)$relation['master_table'], (string)$relation['master_field'],
        ]);
        return (string)($stmt->fetchColumn() ?: '');
    }

    private function jsonColumns(\PDO $pdo, string $schema, string $table): array
    {
        $stmt = $pdo->prepare(
            "SELECT cc.definition FROM sys.check_constraints cc
             JOIN sys.tables t ON t.object_id=cc.parent_object_id
             JOIN sys.schemas s ON s.schema_id=t.schema_id
             WHERE s.name=? AND t.name=? AND UPPER(cc.definition) LIKE '%ISJSON%'"
        );
        $stmt->execute([$schema, $table]);
        $columns = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_COLUMN) as $definition) {
            if (preg_match_all('/ISJSON\s*\(\s*(?:\[([^\]]+)\]|([A-Za-z_][A-Za-z0-9_]*))\s*\)/i', (string)$definition, $matches, PREG_SET_ORDER)) {
                foreach ($matches as $match) {
                    $columns[strtolower((string)($match[1] !== '' ? $match[1] : $match[2]))] = true;
                }
            }
        }
        return $columns;
    }

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

    private function canonicalType(string $type, int $maxLength): string
    {
        return match (strtolower($type)) {
            'varchar' => 'VARCHAR', 'nvarchar' => $maxLength === -1 ? 'TEXT' : 'NVARCHAR',
            'char' => 'CHAR', 'nchar' => 'NCHAR', 'text', 'ntext' => 'TEXT',
            'tinyint' => 'TINYINT', 'smallint' => 'SMALLINT', 'int' => 'INT', 'bigint' => 'BIGINT',
            'decimal', 'numeric', 'money', 'smallmoney' => 'DECIMAL', 'float' => 'FLOAT', 'real' => 'REAL',
            'bit' => 'BIT', 'date' => 'DATE', 'time' => 'TIME', 'datetime', 'smalldatetime' => 'DATETIME',
            'datetime2' => 'DATETIME2', 'datetimeoffset' => 'DATETIMEOFFSET',
            'uniqueidentifier' => 'UNIQUEIDENTIFIER', 'xml' => 'XML',
            'binary' => 'BINARY', 'varbinary' => $maxLength === -1 ? 'BLOB' : 'VARBINARY',
            'image' => 'BLOB', default => 'TEXT',
        };
    }

    private function characterLength(string $type, int $maxLength): ?int
    {
        if (!in_array($type, ['VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR', 'BINARY', 'VARBINARY'], true)) {
            return null;
        }
        if ($maxLength < 0) {
            return 0;
        }
        return in_array($type, ['NVARCHAR', 'NCHAR'], true) ? (int)($maxLength / 2) : $maxLength;
    }

    private function typeDisplay(string $type, ?int $length, ?int $precision, ?int $scale): string
    {
        if ($length !== null && $length > 0) {
            return $type . '(' . $length . ')';
        }
        if ($type === 'DECIMAL' && $precision !== null) {
            return 'DECIMAL(' . $precision . ',' . ($scale ?? 0) . ')';
        }
        return $type;
    }

    private function unwrapDefault(mixed $default): mixed
    {
        if ($default === null) {
            return null;
        }
        $value = trim((string)$default);
        while (strlen($value) >= 2 && $value[0] === '(' && str_ends_with($value, ')')) {
            $value = trim(substr($value, 1, -1));
        }
        if (str_starts_with($value, "N'") && str_ends_with($value, "'")) {
            $value = substr($value, 1);
        }
        return $value;
    }

    private function escapeSqlString(string $value): string
    {
        return str_replace("'", "''", $value);
    }
}
