<?php
// Database Migration Runner
// WARNING: This is a temporary script for a one-time migration.

header('Content-Type: text/plain; charset=utf-8');

echo "Database Migration Runner\n";

// --- Database Connection ---
$configPath = __DIR__ . '/db/config.php';
if (!file_exists($configPath)) {
    die("✘ Error: Database config file not found at 'db/config.php'.");
}
require_once $configPath;

try {
    $pdo = db();
    echo "Successfully connected to the database.\n";
} catch (PDOException $e) {
    die("✘ Database connection failed: " . $e->getMessage());
}

// --- Migration Setup ---
$migrationsTable = 'migrations';
$migrationsDir = __DIR__ . '/db/migrations/';

// Create migrations table if it doesn't exist
try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS `$migrationsTable` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `migration` VARCHAR(255) NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
} catch (PDOException $e) {
    die("✘ Error creating migrations table: " . $e->getMessage());
}

// Get applied migrations
$appliedMigrations = $pdo->query("SELECT `migration` FROM `$migrationsTable`")->fetchAll(PDO::FETCH_COLUMN);

echo "Processing Migrations...\n";

// --- Run Migrations ---
$migrationFiles = glob($migrationsDir . '*.sql');
sort($migrationFiles);

$allGood = true;

foreach ($migrationFiles as $file) {
    $migrationName = basename($file);

    if (in_array($migrationName, $appliedMigrations)) {
        echo "[SKIPPED] Migration $migrationName has already been applied.\n";
        continue;
    }

    echo "Applying migration: $migrationName... ";
    $sql = file_get_contents($file);

    try {
        $pdo->exec($sql);
        
        // Mark as applied
        $stmt = $pdo->prepare("INSERT INTO `$migrationsTable` (`migration`) VALUES (?)");
        $stmt->execute([$migrationName]);
        
        echo "✔ Done.\n";

    } catch (PDOException $e) {
        $errorMessage = $e->getMessage();

        // Check for non-critical errors to ignore and continue
        $isDuplicateColumn = strpos($errorMessage, 'Duplicate column name') !== false;
        $isDuplicateKey = strpos($errorMessage, 'Duplicate entry') !== false;
        $isColumnNotFound = strpos($errorMessage, "Unknown column") !== false;
        $isCantDropField = strpos($errorMessage, "Can't DROP COLUMN") !== false;

        if ($isDuplicateColumn || $isDuplicateKey || $isColumnNotFound || $isCantDropField) {
            $reason = "Already applied";
            if ($isDuplicateColumn) $reason = "Duplicate Column";
            if ($isDuplicateKey) $reason = "Duplicate Entry";
            if ($isColumnNotFound) $reason = "Column Not Found (will be created later)";
            if ($isCantDropField) $reason = "Column already dropped";
            
            echo "✔ Skipped ($reason - marking as run).\n";
            // Mark as applied even if it failed with a recoverable error
            $stmt = $pdo->prepare("INSERT INTO `$migrationsTable` (`migration`) VALUES (?)");
            $stmt->execute([$migrationName]);
        } else {
            echo "✘ Error applying $migrationName: " . $e->getMessage() . "\n";
            $allGood = false;
            // We will now continue instead of breaking
            // break; 
        }
    }
}

if ($allGood) {
    echo "\n🎉 All migrations have been applied successfully!\n";
} else {
    echo "\nAn error occurred. Not all migrations could be applied. Please review the errors above.\n";
}
?>