<?php

declare(strict_types=1);

namespace Install\Services;

use PDO;
use Throwable;

final class MigrationRunner
{
    private PDO $pdo;

    public function __construct(
        array $database,
        private readonly SqlParser $parser =
            new SqlParser(),
        private readonly InstallationLogger $logger =
            new InstallationLogger()
    ) {
        $this->pdo = $this->connect($database);
        $this->createMigrationsTable();
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public function discover(): array
    {
        $this->ensureMigrationsTable();
        $directories = [
            INSTALL_PATH . DIRECTORY_SEPARATOR . 'sql',
            PROJECT_PATH
                . DIRECTORY_SEPARATOR
                . 'database'
                . DIRECTORY_SEPARATOR
                . 'migrations',
        ];

        $files = [];

        foreach ($directories as $directory) {
            if (!is_dir($directory)) {
                continue;
            }

            foreach (
                glob($directory . DIRECTORY_SEPARATOR . '*.sql')
                ?: []
                as $filePath
            ) {
                if (
                    is_file($filePath)
                    && is_readable($filePath)
                    && filesize($filePath) > 0
                ) {
                    $files[basename($filePath)] = $filePath;
                }
            }
        }

        ksort($files, SORT_NATURAL);

        $migrations = [];

        foreach ($files as $fileName => $filePath) {
            $migrations[] = [
                'name' => $fileName,
                'path' => $filePath,
                'checksum' => hash_file('sha256', $filePath),
                'size' => (int)filesize($filePath),
            ];
        }

        return $migrations;
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public function pending(): array
    {
        $this->ensureMigrationsTable();
        $applied = $this->appliedMap();
        $pending = [];

        foreach ($this->discover() as $migration) {
            $name = (string)$migration['name'];
            $checksum = (string)$migration['checksum'];

            if (!isset($applied[$name])) {
                $pending[] = $migration;
                continue;
            }

            if (
                $applied[$name]['status'] === 'success'
                && $applied[$name]['checksum'] !== $checksum
            ) {
                throw new \RuntimeException(
                    'La migration déjà appliquée a été modifiée : '
                    . $name
                );
            }
        }

        return $pending;
    }

    /**
     * Pour une base Darlink déjà installée avant l'existence du moteur,
     * enregistre les migrations actuelles comme référence sans les rejouer.
     *
     * @return int nombre de migrations enregistrées
     */
    public function baselineExistingInstallation(): int
    {
        $this->ensureMigrationsTable();
        if ($this->hasRecordedMigrations()) {
            return 0;
        }

        $count = 0;

        foreach ($this->discover() as $migration) {
            $this->record(
                (string)$migration['name'],
                (string)$migration['checksum'],
                0,
                0,
                'baseline',
                null
            );
            $count++;
        }

        $this->logger->write(
            'info',
            $count
            . ' migration(s) enregistrée(s) comme référence existante.'
        );

        return $count;
    }

    /**
     * @param array<string,mixed> $migration
     *
     * @return array<string,mixed>
     */
    public function execute(array $migration): array
    {
        $this->ensureMigrationsTable();
        $name = (string)($migration['name'] ?? '');
        $path = (string)($migration['path'] ?? '');
        $checksum = (string)($migration['checksum'] ?? '');

        if ($name === '' || $path === '' || $checksum === '') {
            throw new \InvalidArgumentException(
                'Migration invalide.'
            );
        }

        $statements = $this->parser->parseFile($path);
        $startedAt = microtime(true);
        $executed = 0;

        $this->logger->write(
            'info',
            'Début de la migration ' . $name
        );

        try {
            foreach ($statements as $statement) {
                $normalizedStatement =
                    $this->normalizeStatement($statement);

                if ($normalizedStatement === null) {
                    continue;
                }

                /*
                 * PDO MySQL peut conserver un jeu de résultats après certaines
                 * instructions (notamment EXECUTE d'une requête préparée qui
                 * résout vers SELECT). Tant que ce résultat n'est pas consommé,
                 * la requête suivante échoue avec l'erreur MySQL 2014.
                 *
                 * query() nous donne le PDOStatement nécessaire pour vider tous
                 * les jeux de résultats et fermer explicitement le curseur.
                 */
                $pdoStatement = $this->pdo->query($normalizedStatement);

                if ($pdoStatement === false) {
                    throw new \RuntimeException(
                        'Impossible d’exécuter une instruction SQL de la migration.'
                    );
                }

                do {
                    if ($pdoStatement->columnCount() > 0) {
                        $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);
                    }
                } while ($pdoStatement->nextRowset());

                $pdoStatement->closeCursor();
                $executed++;
            }

            $durationMs = (int)round(
                (microtime(true) - $startedAt) * 1000
            );

            $this->record(
                $name,
                $checksum,
                $durationMs,
                $executed,
                'success',
                null
            );

            $this->logger->write(
                'success',
                $name
                . ' terminée : '
                . $executed
                . ' instruction(s), '
                . $durationMs
                . ' ms.'
            );

            return [
                'success' => true,
                'name' => $name,
                'statements' => $executed,
                'duration_ms' => $durationMs,
            ];
        } catch (Throwable $exception) {
            $durationMs = (int)round(
                (microtime(true) - $startedAt) * 1000
            );

            $this->record(
                $name,
                $checksum,
                $durationMs,
                $executed,
                'failed',
                $exception->getMessage()
            );

            $this->logger->write(
                'error',
                $name
                . ' interrompue après '
                . $executed
                . ' instruction(s) : '
                . $exception->getMessage()
            );

            throw new \RuntimeException(
                'Échec de la migration '
                . $name
                . ' : '
                . $exception->getMessage(),
                0,
                $exception
            );
        }
    }

    /**
     * @return array<string,mixed>
     */
    public function summary(): array
    {
        $this->ensureMigrationsTable();
        $row = $this->pdo->query(
            "SELECT "
            . "COUNT(*) AS total, "
            . "SUM(status = 'success') AS successes, "
            . "SUM(status = 'failed') AS failures, "
            . "SUM(status = 'baseline') AS baselines, "
            . "COALESCE(SUM(statements_executed), 0) AS statements, "
            . "COALESCE(SUM(duration_ms), 0) AS duration_ms "
            . "FROM system_migrations"
        )->fetch();

        return [
            'total' => (int)($row['total'] ?? 0),
            'successes' => (int)($row['successes'] ?? 0),
            'failures' => (int)($row['failures'] ?? 0),
            'baselines' => (int)($row['baselines'] ?? 0),
            'statements' => (int)($row['statements'] ?? 0),
            'duration_ms' => (int)($row['duration_ms'] ?? 0),
        ];
    }

    public function loggerPath(): string
    {
        return $this->logger->path();
    }

    public function resetLog(): void
    {
        $this->logger->reset();
    }

    public function hasMigrationHistory(): bool
    {
        return $this->hasRecordedMigrations();
    }

    private function normalizeStatement(
        string $statement
    ): ?string {
        $statement = trim($statement);

        if ($statement === '') {
            return null;
        }

        if (
            preg_match(
                '/^\\s*USE\\s+[`\"]?[A-Za-z0-9_\\-]+[`\"]?\\s*$/i',
                $statement
            ) === 1
        ) {
            $this->logger->write(
                'warning',
                'Instruction USE ignorée.'
            );

            return null;
        }

        if (
            preg_match(
                '/^\\s*INSERT\\s+INTO\\s+[`\"]?migrations[`\"]?\\b/i',
                $statement
            ) === 1
        ) {
            $this->logger->write(
                'warning',
                'Ancien INSERT INTO migrations ignoré.'
            );

            return null;
        }

        $statement = preg_replace(
            '/\\bADD\\s+COLUMN\\s+IF\\s+NOT\\s+EXISTS\\b/i',
            'ADD COLUMN',
            $statement
        ) ?? $statement;

        $metadata = $this->extractAddColumnMetadata(
            $statement
        );

        if (
            $metadata !== null
            && $this->columnExists(
                $metadata['table'],
                $metadata['column']
            )
        ) {
            $this->logger->write(
                'warning',
                'Colonne déjà présente, instruction ignorée : '
                . $metadata['table']
                . '.'
                . $metadata['column']
            );

            return null;
        }

        return $statement;
    }

    /**
     * @return array{table:string,column:string}|null
     */
    private function extractAddColumnMetadata(
        string $statement
    ): ?array {
        $pattern = '/^\\s*ALTER\\s+TABLE\\s+'
            . '(?:IF\\s+EXISTS\\s+)?'
            . '([`\"]?)([A-Za-z0-9_]+)\\1'
            . '\\s+ADD\\s+(?:COLUMN\\s+)?'
            . '([`\"]?)([A-Za-z0-9_]+)\\3\\b/is';

        if (
            preg_match(
                $pattern,
                $statement,
                $matches
            ) !== 1
        ) {
            return null;
        }

        return [
            'table' => (string)$matches[2],
            'column' => (string)$matches[4],
        ];
    }

    private function columnExists(
        string $table,
        string $column
    ): bool {
        $statement = $this->pdo->prepare(
            'SELECT COUNT(*) '
            . 'FROM information_schema.columns '
            . 'WHERE table_schema = DATABASE() '
            . 'AND table_name = :table '
            . 'AND column_name = :column'
        );

        $statement->execute([
            'table' => $table,
            'column' => $column,
        ]);

        return (int)$statement->fetchColumn() > 0;
    }

    private function connect(array $database): PDO
    {
        $dsn = 'mysql:host='
            . (string)($database['db_host'] ?? 'localhost')
            . ';port='
            . (int)($database['db_port'] ?? 3306)
            . ';dbname='
            . (string)($database['db_database'] ?? '')
            . ';charset=utf8mb4';

        return new PDO(
            $dsn,
            (string)($database['db_username'] ?? ''),
            (string)($database['db_password'] ?? ''),
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_TIMEOUT => 15,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]
        );
    }

    private function createMigrationsTable(): void
    {
        $this->ensureMigrationsTable();
    }

    private function ensureMigrationsTable(): void
    {
        if ($this->migrationsTableExists()) {
            return;
        }

        $this->pdo->exec(
            "CREATE TABLE system_migrations ("
            . "id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,"
            . "migration VARCHAR(255) NOT NULL,"
            . "checksum CHAR(64) NOT NULL,"
            . "status VARCHAR(20) NOT NULL,"
            . "statements_executed INT UNSIGNED NOT NULL DEFAULT 0,"
            . "duration_ms INT UNSIGNED NOT NULL DEFAULT 0,"
            . "error_message TEXT NULL,"
            . "installed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            . "PRIMARY KEY (id),"
            . "UNIQUE KEY uk_system_migrations_migration (migration),"
            . "KEY idx_system_migrations_status (status)"
            . ") ENGINE=InnoDB "
            . "DEFAULT CHARSET=utf8mb4 "
            . "COLLATE=utf8mb4_unicode_ci"
        );

        if (!$this->migrationsTableExists()) {
            throw new \RuntimeException(
                'La table system_migrations n’a pas pu être créée.'
            );
        }

        $this->logger->write(
            'success',
            'Table system_migrations créée automatiquement.'
        );
    }

    private function migrationsTableExists(): bool
    {
        $statement = $this->pdo->query(
            "SELECT COUNT(*) "
            . "FROM information_schema.tables "
            . "WHERE table_schema = DATABASE() "
            . "AND table_name = 'system_migrations'"
        );

        return (int)$statement->fetchColumn() > 0;
    }

    /**
     * @return array<string,array<string,string>>
     */
    private function appliedMap(): array
    {
        $this->ensureMigrationsTable();
        $rows = $this->pdo->query(
            "SELECT migration, checksum, status "
            . "FROM system_migrations "
            . "WHERE status IN ('success', 'baseline')"
        )->fetchAll();

        $map = [];

        foreach ($rows as $row) {
            $map[(string)$row['migration']] = [
                'checksum' => (string)$row['checksum'],
                'status' => (string)$row['status'],
            ];
        }

        return $map;
    }

    private function hasRecordedMigrations(): bool
    {
        $this->ensureMigrationsTable();
        return (int)$this->pdo->query(
            'SELECT COUNT(*) FROM system_migrations'
        )->fetchColumn() > 0;
    }

    private function record(
        string $migration,
        string $checksum,
        int $durationMs,
        int $statementsExecuted,
        string $status,
        ?string $errorMessage
    ): void {
        $this->ensureMigrationsTable();

        $statement = $this->pdo->prepare(
            "INSERT INTO system_migrations ("
            . "migration, checksum, status, statements_executed, "
            . "duration_ms, error_message, installed_at"
            . ") VALUES ("
            . ":migration, :checksum, :status, :statements, "
            . ":duration, :error, NOW()"
            . ") AS new "
            . "ON DUPLICATE KEY UPDATE "
            . "checksum = new.checksum, "
            . "status = new.status, "
            . "statements_executed = new.statements_executed, "
            . "duration_ms = new.duration_ms, "
            . "error_message = new.error_message, "
            . "installed_at = new.installed_at"
        );

        $statement->execute([
            'migration' => $migration,
            'checksum' => $checksum,
            'status' => $status,
            'statements' => $statementsExecuted,
            'duration' => $durationMs,
            'error' => $errorMessage,
        ]);
    }
}
