<?php

declare(strict_types=1);

class Migrator
{
    public static function run(PDO $pdo): array
    {
        $results = [];

         
        $migrationsFile = APP_ROOT . '/sql/017_migrations.sql';
        if (file_exists($migrationsFile)) {
            $sql = file_get_contents($migrationsFile);
            if ($sql !== false) {
                $pdo->exec($sql);
            }
        }

         
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM migrations WHERE migration = ?');
        $stmt->execute(['017_migrations.sql']);
        if ((int) $stmt->fetchColumn() === 0) {
            $ins = $pdo->prepare('INSERT INTO migrations (migration) VALUES (?)');
            $ins->execute(['017_migrations.sql']);
        }

         
        $executed = [];
        $stmt = $pdo->query('SELECT migration FROM migrations');
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $executed[] = $row['migration'];
        }

         
        $files = glob(APP_ROOT . '/sql/*.sql');
        if ($files === false) {
            $files = [];
        }
        sort($files);

        foreach ($files as $file) {
            $name = basename($file);

             
            if (in_array($name, $executed, true)) {
                $results[] = ['file' => $name, 'status' => 'skipped'];
                continue;
            }

            $sql = file_get_contents($file);
            if ($sql === false || trim($sql) === '') {
                $results[] = ['file' => $name, 'status' => 'skipped'];
                continue;
            }

            try {
                $statements = self::splitStatements($sql);

                 
                 
                 
                $useTransaction = true;
                foreach ($statements as $st) {
                    $first = strtoupper(strtok(ltrim($st), " \t\n\r"));
                    if (in_array($first, ['CREATE', 'ALTER', 'DROP', 'RENAME', 'TRUNCATE'], true)) {
                        $useTransaction = false;
                        break;
                    }
                }

                if ($useTransaction) {
                    $pdo->beginTransaction();
                }

                foreach ($statements as $statement) {
                    if ($statement !== '') {
                        try {
                            $pdo->exec($statement);
                        } catch (PDOException $e) {
                             
                             
                             
                            $code = (int) ($e->errorInfo[1] ?? 0);
                            $alreadyApplied = in_array($code, [
                                1050,  
                                1060,  
                                1061,  
                                1022,  
                                1826,  
                                1091,  
                                1146,  
                            ], true);
                            if ($useTransaction || !$alreadyApplied) {
                                throw $e;
                            }
                        }
                    }
                }

                if ($useTransaction && $pdo->inTransaction()) {
                    $pdo->commit();
                }

                $ins = $pdo->prepare('INSERT INTO migrations (migration) VALUES (?)');
                $ins->execute([$name]);

                $results[] = ['file' => $name, 'status' => 'success'];
            } catch (PDOException $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $results[] = ['file' => $name, 'status' => 'error', 'message' => $e->getMessage()];
            }
        }

        return $results;
    }

    

    private static function splitStatements(string $sql): array
    {
        $statements = [];
        $current = '';
        $inString = false;
        $stringChar = '';
        $delimiter = ';';
        $atLineStart = true;
        $length = strlen($sql);

        for ($i = 0; $i < $length; $i++) {
            $char = $sql[$i];

             
             
            if ($atLineStart
                && ($char === 'D' || $char === 'd')
                && strtoupper(substr($sql, $i, 9)) === 'DELIMITER'
                && ($i + 9 >= $length || ctype_space($sql[$i + 9]))
            ) {
                $trimmed = trim($current);
                if ($trimmed !== '') {
                    $statements[] = $trimmed;
                }
                $current = '';
                if (preg_match('/^\s+(\S+)/', substr($sql, $i + 9), $m)) {
                    $delimiter = $m[1];
                }
                $newline = strpos($sql, "\n", $i);
                $i = ($newline === false) ? $length - 1 : $newline;
                $atLineStart = true;
                continue;
            }

             
            if ($inString) {
                $current .= $char;
                 
                if ($char === '\\' && $i + 1 < $length) {
                    $i++;
                    $current .= $sql[$i];
                    continue;
                }
                 
                if ($char === $stringChar) {
                    if ($i + 1 < $length && $sql[$i + 1] === $stringChar) {
                        $i++;
                        $current .= $sql[$i];
                        continue;
                    }
                    $inString = false;
                }
                continue;
            }

             
            if ($char === '-' && $i + 1 < $length && $sql[$i + 1] === '-') {
                $endOfLine = strpos($sql, "\n", $i);
                if ($endOfLine === false) {
                    break;
                }
                $i = $endOfLine;
                continue;
            }

             
            if ($char === '/' && $i + 1 < $length && $sql[$i + 1] === '*') {
                $endOfComment = strpos($sql, '*/', $i + 2);
                if ($endOfComment === false) {
                    break;
                }
                $i = $endOfComment + 1;
                continue;
            }

             
            if ($char === '\'' || $char === '"') {
                $inString = true;
                $stringChar = $char;
                $current .= $char;
                continue;
            }

             
            $dlen = strlen($delimiter);
            if ($char === $delimiter[0] && ($dlen === 1 || substr($sql, $i, $dlen) === $delimiter)) {
                $trimmed = trim($current);
                if ($trimmed !== '') {
                    $statements[] = $trimmed;
                }
                $current = '';
                $i += $dlen - 1;
                $atLineStart = false;
                continue;
            }

            $current .= $char;

            if ($char === "\n") {
                $atLineStart = true;
            } elseif ($char !== ' ' && $char !== "\t" && $char !== "\r") {
                $atLineStart = false;
            }
        }

        $trimmed = trim($current);
        if ($trimmed !== '') {
            $statements[] = $trimmed;
        }

        return $statements;
    }
}
