<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

abstract class AbstractDatabaseDialect implements DatabaseDialectInterface
{
    abstract protected function fieldTypes(): array;

    abstract protected function typeSql(array $field): string;

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

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

    protected function noLengthTypes(): array
    {
        return array_values(array_diff($this->fieldTypes(), $this->sizedTypes()));
    }

    protected function enumTypes(): array
    {
        return [];
    }

    protected function temporalTypes(): array
    {
        return ['DATETIME', 'DATETIME2', 'DATETIMEOFFSET', 'TIMESTAMP', 'TIMESTAMPTZ'];
    }

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

    protected function capabilities(): array
    {
        return [
            'create_table' => true,
            'edit_table' => true,
            'rename_table' => true,
            'drop_table' => true,
            'views' => true,
            'replace_views' => true,
            'foreign_keys' => true,
            'transactional_ddl' => true,
        ];
    }

    public function uiConfig(): array
    {
        return [
            'id' => $this->id(),
            'label' => $this->label(),
            'field_types' => $this->fieldTypes(),
            'integer_types' => $this->integerTypes(),
            'sized_types' => $this->sizedTypes(),
            'no_length_types' => $this->noLengthTypes(),
            'enum_types' => $this->enumTypes(),
            'temporal_types' => $this->temporalTypes(),
            'referential_actions' => $this->referentialActions(),
            'capabilities' => $this->capabilities(),
        ];
    }

    public function createTable(\PDO $pdo, string $table, array $fields, array $config, array $foreignKeys = []): void
    {
        $pdo->exec($this->compileCreateTable($table, $fields, $config, $foreignKeys));
    }

    public function compileCreateTable(string $table, array $fields, array $config = [], array $foreignKeys = []): string
    {
        $columns = [];
        $primary = [];
        foreach ($fields as $field) {
            $columns[] = $this->compileColumn($field, false);
            if (!empty($field['primary_key']) && !$this->primaryKeyIsInline($field)) {
                $primary[] = $field['name'];
            }
        }
        if ($primary !== []) {
            $columns[] = 'PRIMARY KEY (' . implode(', ', array_map([$this, 'quoteIdentifier'], $primary)) . ')';
        }
        foreach ($foreignKeys as $foreignKey) {
            $columns[] = $this->compileForeignKey($foreignKey, $config);
        }
        return 'CREATE TABLE ' . $this->qualifiedTable($table, $config) . " (\n    " . implode(",\n    ", $columns) . "\n)" . $this->createTableSuffix();
    }

    protected function compileColumn(array $field, bool $forAlter): string
    {
        $sql = $this->quoteIdentifier((string)$field['name']) . ' ' . $this->typeSql($field);
        $sql .= $this->autoIncrementSql($field, $forAlter);
        if ($this->primaryKeyIsInline($field)) {
            $sql .= ' PRIMARY KEY';
        }
        $sql .= !empty($field['nullable']) ? ' NULL' : ' NOT NULL';
        $sql .= $this->defaultSql($field);
        $sql .= $this->columnCheckSql($field);
        return $sql;
    }

    protected function autoIncrementSql(array $field, bool $forAlter): string
    {
        return '';
    }

    protected function primaryKeyIsInline(array $field): bool
    {
        return false;
    }

    protected function createTableSuffix(): string
    {
        return '';
    }

    protected function columnCheckSql(array $field): string
    {
        return '';
    }

    protected function defaultSql(array $field): string
    {
        if (!empty($field['auto_increment'])) {
            return '';
        }
        $mode = strtoupper((string)($field['default_mode'] ?? 'NONE'));
        $value = $field['default'] ?? null;
        return match ($mode) {
            'NONE' => '',
            'NULL' => ' DEFAULT NULL',
            'CURRENT_TIMESTAMP' => ' DEFAULT ' . $this->currentTimestampSql(),
            'EXPRESSION' => ' DEFAULT ' . $this->safeExpression((string)($field['default_expression'] ?? $value ?? '')),
            'VALUE' => ' DEFAULT ' . $this->literal($value, (string)($field['type'] ?? 'VARCHAR')),
            default => throw new \InvalidArgumentException('Unsupported default mode.'),
        };
    }

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

    protected function safeExpression(string $expression): string
    {
        $expression = trim($expression);
        if ($expression === '' || str_contains($expression, ';') || str_contains($expression, '--') || str_contains($expression, '/*') || preg_match('/\b(SELECT|INSERT|UPDATE|DELETE|DROP|ALTER|CREATE)\b/i', $expression)) {
            throw new \InvalidArgumentException('Default expression is empty or contains unsafe SQL.');
        }
        return '(' . $expression . ')';
    }

    protected function literal(mixed $value, string $type): string
    {
        if ($value === null) {
            return 'NULL';
        }
        $type = strtoupper($type);
        if (in_array($type, $this->integerTypes(), true)) {
            if (!preg_match('/^-?\d+$/', (string)$value)) {
                throw new \InvalidArgumentException("{$type} defaults must be whole numbers.");
            }
            return (string)(int)$value;
        }
        if (in_array($type, ['DECIMAL', 'NUMERIC', 'FLOAT', 'DOUBLE', 'REAL', 'MONEY', 'SMALLMONEY'], true)) {
            if (!is_numeric($value)) {
                throw new \InvalidArgumentException("{$type} defaults must be numeric.");
            }
            return trim((string)$value);
        }
        if (in_array($type, ['BOOLEAN', 'BIT'], true)) {
            return $this->booleanLiteral($value);
        }
        return "'" . str_replace("'", "''", (string)$value) . "'";
    }

    protected function booleanLiteral(mixed $value): string
    {
        $normalized = strtolower(trim((string)$value));
        if (in_array($normalized, ['1', 'true', 'yes', 'on'], true)) {
            return '1';
        }
        if (in_array($normalized, ['0', 'false', 'no', 'off'], true)) {
            return '0';
        }
        throw new \InvalidArgumentException('Boolean defaults must be true/false or 1/0.');
    }

    public function dropObject(\PDO $pdo, string $name, string $type, array $config): void
    {
        $kind = strtoupper($type) === 'VIEW' ? 'VIEW' : 'TABLE';
        $pdo->exec('DROP ' . $kind . ' IF EXISTS ' . $this->qualifiedTable($name, $config));
    }

    public function createView(\PDO $pdo, string $name, string $selectSql, bool $replace, array $config): void
    {
        $qualified = $this->qualifiedTable($name, $config);
        if (!$replace) {
            $pdo->exec('CREATE VIEW ' . $qualified . ' AS ' . $selectSql);
            return;
        }
        $this->transaction($pdo, function () use ($pdo, $qualified, $selectSql): void {
            $pdo->exec('DROP VIEW IF EXISTS ' . $qualified);
            $pdo->exec('CREATE VIEW ' . $qualified . ' AS ' . $selectSql);
        });
    }

    public function preview(\PDO $pdo, string $selectSql, int $limit = 10): array
    {
        $statement = $pdo->query($selectSql);
        $rows = [];
        while (count($rows) < $limit && ($row = $statement->fetch(\PDO::FETCH_ASSOC)) !== false) {
            $rows[] = $row;
        }
        $columns = $rows !== [] ? array_keys($rows[0]) : [];
        if ($columns === []) {
            for ($i = 0; $i < $statement->columnCount(); $i++) {
                $meta = $statement->getColumnMeta($i);
                $columns[] = (string)($meta['name'] ?? 'column_' . ($i + 1));
            }
        }
        return ['rows' => $rows, 'columns' => $columns];
    }

    public function addForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $sql = 'ALTER TABLE ' . $this->qualifiedTable((string)$relation['detail_table'], $config)
            . ' ADD ' . $this->compileForeignKey($relation, $config);
        $pdo->exec($sql);
    }

    protected function compileForeignKey(array $relation, array $config = []): string
    {
        $constraint = $relation['constraint_name'] ?? $this->constraintName((string)$relation['detail_table'], (string)$relation['detail_field']);
        return 'CONSTRAINT ' . $this->quoteIdentifier((string)$constraint)
            . ' FOREIGN KEY (' . $this->quoteIdentifier((string)$relation['detail_field']) . ')'
            . ' REFERENCES ' . $this->qualifiedTable((string)$relation['master_table'], $config)
            . ' (' . $this->quoteIdentifier((string)$relation['master_field']) . ')'
            . ' ON DELETE ' . $this->referentialAction((string)($relation['on_delete_action'] ?? 'CASCADE'))
            . ' ON UPDATE ' . $this->referentialAction((string)($relation['on_update_action'] ?? 'CASCADE'));
    }

    public function countRows(\PDO $pdo, string $table, array $config): int
    {
        return (int)$pdo->query('SELECT COUNT(*) FROM ' . $this->qualifiedTable($table, $config))->fetchColumn();
    }

    /**
     * Safe default: fall back to a full describeTable() and return only its
     * columns. Dialects override this with a single columns-only query so that
     * bulk schema introspection doesn't also pay for index/foreign-key lookups.
     */
    public function listColumns(\PDO $pdo, string $table, array $config): array
    {
        return $this->describeTable($pdo, $table, $config)['columns'] ?? [];
    }

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

    protected function constraintName(string $table, string $field): string
    {
        $base = preg_replace('/[^A-Za-z0-9_]/', '_', 'fk_' . $table . '_' . $field) ?: 'fk_relation';
        return substr($base, 0, 54) . '_' . substr(sha1($base), 0, 8);
    }

    protected function referentialAction(string $action): string
    {
        $action = strtoupper(trim($action));
        return in_array($action, ['CASCADE', 'RESTRICT', 'NO ACTION', 'SET NULL', 'SET DEFAULT'], true) ? $action : 'NO ACTION';
    }

    protected function transaction(\PDO $pdo, callable $callback): void
    {
        $started = !$pdo->inTransaction();
        if ($started) {
            $pdo->beginTransaction();
        }
        try {
            $callback();
            if ($started && $pdo->inTransaction()) {
                $pdo->commit();
            }
        } catch (\Throwable $e) {
            if ($started && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    protected function normalizedColumn(
        string $name,
        string $dataType,
        string $columnType,
        bool $nullable,
        mixed $default,
        bool $primary,
        bool $autoIncrement,
        ?int $length = null,
        ?int $precision = null,
        ?int $scale = null,
        string $comment = '',
        string $enumValues = ''
    ): array {
        $defaultMode = 'NONE';
        $defaultValue = $default;
        $defaultExpression = null;
        if ($default !== null) {
            $raw = trim((string)$default);
            if (strtoupper($raw) === 'NULL') {
                $defaultMode = 'NULL';
                $defaultValue = null;
            } elseif (preg_match('/^(CURRENT_TIMESTAMP|NOW\(\)|GETDATE\(\)|SYSUTCDATETIME\(\))(\(\d+\))?$/i', trim($raw, '()'))) {
                $defaultMode = 'CURRENT_TIMESTAMP';
                $defaultValue = null;
            } elseif ($this->looksLikeExpressionDefault($raw)) {
                $defaultMode = 'EXPRESSION';
                $defaultExpression = $raw;
            } else {
                $defaultMode = 'VALUE';
                $defaultValue = $this->stripDefaultQuotes($raw);
            }
        }

        return [
            'COLUMN_NAME' => $name,
            'DATA_TYPE' => strtoupper($dataType),
            'CHARACTER_MAXIMUM_LENGTH' => $length,
            'NUMERIC_PRECISION' => $precision,
            'NUMERIC_SCALE' => $scale,
            'IS_NULLABLE' => $nullable ? 'YES' : 'NO',
            'COLUMN_DEFAULT' => $default,
            'COLUMN_KEY' => $primary ? 'PRI' : '',
            'EXTRA' => $autoIncrement ? 'auto_increment' : '',
            'COLUMN_TYPE' => strtoupper($columnType),
            'COLUMN_COMMENT' => $comment,
            'name' => $name,
            'original_name' => $name,
            'type' => strtoupper($dataType),
            'size' => $length ?? $precision ?? 0,
            'scale' => $scale ?? 0,
            'nullable' => $nullable,
            'default_mode' => $defaultMode,
            'default' => $defaultValue,
            'default_expression' => $defaultExpression,
            'primary_key' => $primary,
            'auto_increment' => $autoIncrement,
            'enum_values' => $enumValues,
        ];
    }

    protected function normalizedIndex(string $name, string $column, bool $unique): array
    {
        return [
            'INDEX_NAME' => $name,
            'COLUMN_NAME' => $column,
            'NON_UNIQUE' => $unique ? 0 : 1,
        ];
    }

    protected function looksLikeExpressionDefault(string $value): bool
    {
        return (bool)preg_match('/(^\(|::|\b(nextval|uuid_generate|gen_random_uuid|newid|dateadd)\s*\()/i', $value);
    }

    protected function stripDefaultQuotes(string $value): string
    {
        $value = preg_replace('/::[A-Za-z0-9_\s\[\]"]+$/', '', $value) ?? $value;
        if (strlen($value) >= 2 && (($value[0] === "'" && str_ends_with($value, "'")) || ($value[0] === '"' && str_ends_with($value, '"')))) {
            $value = substr($value, 1, -1);
        }
        return str_replace("''", "'", $value);
    }
}
