<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use App\Security\SqlSecurityPolicy;
use PDO;
use RuntimeException;
use Throwable;

final class UpdateMigrationRunner
{
    private PDO $pdo;

    public function __construct()
    {
        $this->pdo = Database::connection();
    }

    /** @return array{status:string,checksum:string,duration_ms:int} */
    public function apply(string $migrationKey, string $path, string $packageVersion): array
    {
        if (!is_file($path)) throw new RuntimeException('Migration absente : ' . $migrationKey);
        $sql = file_get_contents($path);
        if ($sql === false) throw new RuntimeException('Migration illisible : ' . $migrationKey);
        SqlSecurityPolicy::assertMigrationSql($sql, $migrationKey);
        $checksum = hash('sha256', $sql);

        $stmt = $this->pdo->prepare('SELECT checksum_sha256, statut FROM axiom_update_migrations WHERE migration_key=:migration_key LIMIT 1');
        $stmt->execute(['migration_key' => $migrationKey]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($existing && ($existing['statut'] ?? '') === 'appliquee') {
            if (!hash_equals((string)$existing['checksum_sha256'], $checksum)) {
                throw new RuntimeException('La migration déjà appliquée a été modifiée : ' . $migrationKey);
            }
            return ['status' => 'deja_appliquee', 'checksum' => $checksum, 'duration_ms' => 0];
        }

        $started = microtime(true);
        $statementNumber = 0;
        $currentStatement = '';
        try {
            foreach ($this->splitStatements($sql) as $statement) {
                $statementNumber++;
                $currentStatement = $statement;
                if (trim($statement) === '') {
                    continue;
                }

                // Utiliser un PDOStatement plutôt que PDO::exec() afin de pouvoir
                // consommer et fermer tous les jeux de résultats produits par MySQL
                // (notamment PREPARE / EXECUTE et les instructions conditionnelles).
                $migrationStatement = $this->pdo->prepare($statement);
                $migrationStatement->execute();

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

                $migrationStatement->closeCursor();
            }
            $duration = (int)round((microtime(true) - $started) * 1000);
            $save = $this->pdo->prepare("INSERT INTO axiom_update_migrations(migration_key,package_version,checksum_sha256,statut,duration_ms,applied_at,error_message) VALUES(:migration_key,:package_version,:checksum,'appliquee',:duration,NOW(),NULL) ON DUPLICATE KEY UPDATE package_version=VALUES(package_version),checksum_sha256=VALUES(checksum_sha256),statut='appliquee',duration_ms=VALUES(duration_ms),applied_at=NOW(),error_message=NULL");
            $save->execute(['migration_key'=>$migrationKey,'package_version'=>$packageVersion,'checksum'=>$checksum,'duration'=>$duration]);
            return ['status' => 'appliquee', 'checksum' => $checksum, 'duration_ms' => $duration];
        } catch (Throwable $e) {
            $duration = (int)round((microtime(true) - $started) * 1000);
            $save = $this->pdo->prepare("INSERT INTO axiom_update_migrations(migration_key,package_version,checksum_sha256,statut,duration_ms,applied_at,error_message) VALUES(:migration_key,:package_version,:checksum,'echec',:duration,NOW(),:error) ON DUPLICATE KEY UPDATE statut='echec',duration_ms=VALUES(duration_ms),applied_at=NOW(),error_message=VALUES(error_message)");
            $save->execute(['migration_key'=>$migrationKey,'package_version'=>$packageVersion,'checksum'=>$checksum,'duration'=>$duration,'error'=>$e->getMessage()]);
            $details = [
                'Migration' => $migrationKey,
                'Fichier' => $path,
                'Instruction' => (string)$statementNumber,
                'Durée' => $duration . ' ms',
                'Exception' => get_class($e),
                'Code' => (string)$e->getCode(),
                'Message' => $e->getMessage(),
                'SQL' => $this->formatSqlForDiagnostic($currentStatement),
            ];
            if ($e instanceof \PDOException && is_array($e->errorInfo ?? null)) {
                $details['SQLSTATE'] = (string)($e->errorInfo[0] ?? '');
                $details['Code pilote'] = (string)($e->errorInfo[1] ?? '');
                $details['Message pilote'] = (string)($e->errorInfo[2] ?? '');
            }
            $message = "Échec de migration détaillé :\n";
            foreach ($details as $label => $value) {
                $message .= $label . ' : ' . $value . "\n";
            }
            throw new RuntimeException(rtrim($message), (int)$e->getCode(), $e);
        }
    }

    private function formatSqlForDiagnostic(string $sql): string
    {
        $sql = preg_replace('/\s+/', ' ', trim($sql)) ?? trim($sql);
        // Évite qu’un journal démesuré ou une valeur sensible ne soit exposé intégralement.
        $sql = preg_replace("/(password|secret|token|api[_-]?key)\s*=\s*('[^']*'|\"[^\"]*\")/i", '$1=[REDACTED]', $sql) ?? $sql;
        if (strlen($sql) > 4000) {
            $sql = substr($sql, 0, 4000) . ' … [tronqué]';
        }
        return $sql;
    }

    /** @return list<string> */
    private function splitStatements(string $sql): array
    {
        $statements = [];
        $buffer = '';
        $quote = null;
        $length = strlen($sql);
        for ($i = 0; $i < $length; $i++) {
            $char = $sql[$i];
            $next = $i + 1 < $length ? $sql[$i + 1] : '';
            if ($quote === null && $char === '-' && $next === '-' && ($i + 2 >= $length || ctype_space($sql[$i + 2]))) {
                while ($i < $length && $sql[$i] !== "\n") $i++;
                $buffer .= "\n";
                continue;
            }
            if ($quote === null && $char === '#') {
                while ($i < $length && $sql[$i] !== "\n") $i++;
                $buffer .= "\n";
                continue;
            }
            if ($quote === null && $char === '/' && $next === '*') {
                $i += 2;
                while ($i + 1 < $length && !($sql[$i] === '*' && $sql[$i + 1] === '/')) $i++;
                $i++;
                continue;
            }
            if (($char === "'" || $char === '"' || $char === '`')) {
                if ($quote === null) $quote = $char;
                elseif ($quote === $char && ($i === 0 || $sql[$i - 1] !== '\\')) $quote = null;
            }
            if ($char === ';' && $quote === null) {
                if (trim($buffer) !== '') $statements[] = trim($buffer);
                $buffer = '';
                continue;
            }
            $buffer .= $char;
        }
        if (trim($buffer) !== '') $statements[] = trim($buffer);
        return $statements;
    }
}
