<?php
/**
 * Database Migration Script
 */

require_once __DIR__ . '/../vendor/autoload.php';

// Load environment variables
$dotenv = Dotenv\Dotenv::createImmutable(__DIR__ . '/..');
$dotenv->load();

// Load configuration
require_once __DIR__ . '/../config/config.php';

try {
    $pdo = new PDO(
        sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', DB_HOST, DB_PORT, DB_NAME),
        DB_USER,
        DB_PASS,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    echo "Connected to database successfully.\n";

    // Create migrations table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS migrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration VARCHAR(255) NOT NULL,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

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

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

    $executedCount = 0;

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

        if (in_array($migrationName, $executedMigrations)) {
            echo "Migration {$migrationName} already executed.\n";
            continue;
        }

        echo "Executing migration: {$migrationName}\n";

        // Read and execute migration
        $sql = file_get_contents($file);
        
        try {
            $pdo->exec($sql);
            
            // Record migration as executed
            $stmt = $pdo->prepare("INSERT INTO migrations (migration) VALUES (?)");
            $stmt->execute([$migrationName]);
            
            echo "Migration {$migrationName} executed successfully.\n";
            $executedCount++;
            
        } catch (PDOException $e) {
            echo "Error executing migration {$migrationName}: " . $e->getMessage() . "\n";
            exit(1);
        }
    }

    if ($executedCount > 0) {
        echo "Executed {$executedCount} new migrations.\n";
    } else {
        echo "No new migrations to execute.\n";
    }

    echo "Database migration completed successfully.\n";

} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage() . "\n";
    exit(1);
}