<?php
require_once __DIR__ . '/config.php';

try {
    $pdo = db();
    
    // First, make sure the migrations table exists
    $pdo->exec("CREATE TABLE IF NOT EXISTS `migrations` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `migration` VARCHAR(255) NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Get all migration files
    $migrations_dir = __DIR__ . '/migrations';
    $migration_files = glob($migrations_dir . '/*.sql');
    sort($migration_files);

    // Get already run migrations
    $stmt = $pdo->query("SELECT migration FROM migrations");
    $run_migrations = $stmt->fetchAll(PDO::FETCH_COLUMN);

    foreach ($migration_files as $file) {
        $migration_name = basename($file);
        
        if (in_array($migration_name, $run_migrations)) {
            continue; // Skip already run migration
        }

        $sql = file_get_contents($file);
        $pdo->exec($sql);

        // Record the migration
        $stmt = $pdo->prepare("INSERT INTO migrations (migration) VALUES (?)");
        $stmt->execute([$migration_name]);

        echo "Executed migration: " . $migration_name . "\n";
    }

    echo "All new migrations successful!\n";

} catch (PDOException $e) {
    die("Migration failed: " . $e->getMessage() . "\n");
}