<?php
require __DIR__ . '/../vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__ . '/..');
$dotenv->safeLoad();

use App\Database;

try {
    $pdo = Database::getConnection();

    // Ensure migrations table exists to track applied migrations
    $pdo->exec("CREATE TABLE IF NOT EXISTS migrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        filename VARCHAR(255) NOT NULL UNIQUE,
        applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;");

    // Execute all .sql files in migrations in alphabetical order, but skip applied ones
    $files = glob(__DIR__ . '/*.sql');
    sort($files);
    foreach ($files as $f) {
        $name = basename($f);
        $check = $pdo->prepare('SELECT COUNT(1) FROM migrations WHERE filename = :filename');
        $check->execute([':filename' => $name]);
        if ((int)$check->fetchColumn() > 0) {
            echo "Skipping applied migration: {$name}\n";
            continue;
        }

        $sql = file_get_contents($f);
        if ($sql === false) throw new Exception('Could not read migration file: ' . $f);

        try {
            // Note: DDL statements in MySQL implicitly commit; avoid wrapping in transactions
            $pdo->exec($sql);
            $ins = $pdo->prepare('INSERT IGNORE INTO migrations (filename) VALUES (:filename)');
            $ins->execute([':filename' => $name]);
            echo "Executed migration: " . $name . "\n";
        } catch (\PDOException $e) {
            $errMsg = $e->errorInfo[2] ?? $e->getMessage();
            $driverCode = $e->errorInfo[1] ?? $e->getCode();
            // Common MySQL DDL duplicate/exists codes: 1050 (table exists), 1060 (duplicate column),
            // 1061 (duplicate key), 1062 (duplicate entry), 1091 (can't drop - doesn't exist)
            if (in_array((int)$driverCode, [1050,1060,1061,1062,1091], true)
                || stripos($errMsg, 'already exists') !== false
                || stripos($errMsg, 'Duplicate') !== false) {
                $ins = $pdo->prepare('INSERT IGNORE INTO migrations (filename) VALUES (:filename)');
                $ins->execute([':filename' => $name]);
                echo "Migration {$name} appears already applied (" . ($driverCode ?: 'duplicate/exists') . "). Marking as applied.\n";
                continue;
            }
            throw $e;
        }
    }
    echo "Migrations executed successfully.\n";
} catch (Exception $e) {
    echo "Migration error: " . $e->getMessage() . "\n";
}
