<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Dialect;

/**
 * MySQL / MariaDB adapter for PDO_MYSQL.
 *
 * NOT `final`: the MySQL wire protocol is the base for a family of
 * MySQL-compatible engines (TiDB, and the managed PlanetScale offering). Those
 * ship as thin subclasses that override id()/label() and any engine-specific
 * quirks — see TiDbDialect. Keep the SQL here standard-MySQL so the subclasses
 * inherit correct behaviour by default.
 */
class MySqlDialect extends AbstractDatabaseDialect
{
    public function __construct(protected readonly string $vendor = 'mysql') {}

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

    public function label(): string
    {
        return match ($this->vendor) {
            'mariadb' => 'MariaDB',
            'tidb'    => 'TiDB',
            default   => 'MySQL',
        };
    }

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

    protected function fieldTypes(): array
    {
        return ['VARCHAR', 'CHAR', 'TEXT', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'INT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT', 'DECIMAL', 'FLOAT', 'DOUBLE', 'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'TIMESTAMP', 'YEAR', 'ENUM', 'SET', 'JSON', 'BINARY', 'VARBINARY', 'BLOB', 'TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB'];
    }

    protected function enumTypes(): array { return ['ENUM', 'SET']; }

    protected function noLengthTypes(): array
    {
        return ['TEXT', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'INT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT', 'FLOAT', 'DOUBLE', 'BOOLEAN', 'DATE', 'TIME', 'DATETIME', 'TIMESTAMP', 'YEAR', 'ENUM', 'SET', 'JSON', 'BLOB', 'TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB'];
    }

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

    protected function typeSql(array $field): string
    {
        $type = strtoupper((string)$field['type']);
        if (in_array($type, ['ENUM', 'SET'], true)) {
            $values = array_map(fn(string $value): string => "'" . str_replace("'", "''", trim($value)) . "'", explode(',', (string)$field['enum_values']));
            return $type . '(' . implode(', ', $values) . ')';
        }
        if (in_array($type, ['VARCHAR', 'CHAR', 'BINARY', 'VARBINARY'], true)) {
            return $type . '(' . max(1, (int)$field['size']) . ')';
        }
        if ($type === 'DECIMAL') {
            return 'DECIMAL(' . max(1, (int)$field['size']) . ', ' . max(0, (int)$field['scale']) . ')';
        }
        return $type;
    }

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

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

    protected function createTableSuffix(): string
    {
        return ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4';
    }

    protected function capabilities(): array
    {
        $capabilities = parent::capabilities();
        $capabilities['transactional_ddl'] = false;
        $capabilities['native_enum'] = true;
        return $capabilities;
    }

    public function listObjects(\PDO $pdo, array $config): array
    {
        $stmt = $pdo->prepare('SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME');
        $stmt->execute([(string)$config['dbname']]);
        return array_map(static fn(array $row): array => [
            'name' => (string)$row['TABLE_NAME'],
            'type' => str_contains(strtoupper((string)$row['TABLE_TYPE']), 'VIEW') ? 'VIEW' : 'TABLE',
        ], $stmt->fetchAll(\PDO::FETCH_ASSOC));
    }

    public function listColumns(\PDO $pdo, string $table, array $config): array
    {
        $columnsStmt = $pdo->prepare(
            'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA, COLUMN_TYPE, COLUMN_COMMENT
             FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION'
        );
        $columnsStmt->execute([(string)$config['dbname'], $table]);
        $columns = [];
        foreach ($columnsStmt->fetchAll(\PDO::FETCH_ASSOC) as $column) {
            $type = strtoupper((string)$column['DATA_TYPE']);
            $enumValues = '';
            if (in_array($type, ['ENUM', 'SET'], true) && preg_match('/^(?:enum|set)\((.*)\)$/i', (string)$column['COLUMN_TYPE'], $match)) {
                $enumValues = implode(',', array_map(static fn(string $value): string => trim($value, " '\""), str_getcsv($match[1], ',', "'", '\\')));
            }
            $columns[] = $this->normalizedColumn(
                (string)$column['COLUMN_NAME'],
                $type,
                (string)$column['COLUMN_TYPE'],
                strtoupper((string)$column['IS_NULLABLE']) === 'YES',
                $column['COLUMN_DEFAULT'],
                (string)$column['COLUMN_KEY'] === 'PRI',
                str_contains(strtolower((string)$column['EXTRA']), 'auto_increment'),
                $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,
                (string)$column['COLUMN_COMMENT'],
                $enumValues
            );
        }
        return $columns;
    }

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

        $indexesStmt = $pdo->prepare('SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY INDEX_NAME, SEQ_IN_INDEX');
        $indexesStmt->execute([(string)$config['dbname'], $table]);
        $indexes = array_map(fn(array $index): array => $this->normalizedIndex((string)$index['INDEX_NAME'], (string)$index['COLUMN_NAME'], (int)$index['NON_UNIQUE'] === 0), $indexesStmt->fetchAll(\PDO::FETCH_ASSOC));

        $foreignStmt = $pdo->prepare(
            'SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.UPDATE_RULE, r.DELETE_RULE
             FROM information_schema.KEY_COLUMN_USAGE k
             JOIN information_schema.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME=k.CONSTRAINT_NAME
             WHERE k.TABLE_SCHEMA=? AND k.TABLE_NAME=? AND k.REFERENCED_TABLE_NAME IS NOT NULL'
        );
        $foreignStmt->execute([(string)$config['dbname'], $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['REFERENCED_TABLE_NAME'],
            'master_field' => (string)$fk['REFERENCED_COLUMN_NAME'],
            'on_update_action' => (string)$fk['UPDATE_RULE'],
            'on_delete_action' => (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
    {
        $table = $oldTable;
        if ($oldTable !== $newTable) {
            $pdo->exec('RENAME TABLE ' . $this->quoteIdentifier($oldTable) . ' TO ' . $this->quoteIdentifier($newTable));
            $table = $newTable;
        }

        $currentColumns = (array)($currentDetails['columns'] ?? []);
        $currentByName = [];
        foreach ($currentColumns as $column) {
            $currentByName[(string)$column['COLUMN_NAME']] = $column;
        }
        $oldPrimary = array_values(array_map(static fn(array $col): string => (string)$col['COLUMN_NAME'], array_filter($currentColumns, static fn(array $col): bool => (string)$col['COLUMN_KEY'] === 'PRI')));
        $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']))));
        $primaryChanged = $oldPrimary !== $newPrimary;
        if ($primaryChanged && $oldPrimary !== []) {
            $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($table) . ' DROP PRIMARY KEY');
        }

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

        foreach ($fields as $field) {
            $original = (string)$field['original_name'];
            $definition = $this->compileColumn($field, true);
            if (isset($currentByName[$original])) {
                if ($original !== $field['name']) {
                    $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($table) . ' CHANGE ' . $this->quoteIdentifier($original) . ' ' . $definition);
                } else {
                    $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($table) . ' MODIFY ' . $definition);
                }
            } else {
                $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($table) . ' ADD COLUMN ' . $definition);
            }
        }

        if ($primaryChanged && $newPrimary !== []) {
            $pdo->exec('ALTER TABLE ' . $this->quoteIdentifier($table) . ' 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->quoteIdentifier($name) . ' AS ' . $selectSql);
    }

    public function dropForeignKey(\PDO $pdo, array $relation, array $config): void
    {
        $constraint = trim((string)($relation['constraint_name'] ?? ''));
        if ($constraint === '') {
            $stmt = $pdo->prepare('SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=? AND REFERENCED_TABLE_NAME=? AND REFERENCED_COLUMN_NAME=? LIMIT 1');
            $stmt->execute([(string)$config['dbname'], (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->quoteIdentifier((string)$relation['detail_table']) . ' DROP FOREIGN KEY ' . $this->quoteIdentifier($constraint));
    }
}
