<?php
// Web-based migration runner
// IMPORTANT: Delete this file after running the migration!

// Simple password protection
$MIGRATION_PASSWORD = 'run_migration_2024'; // Change this!

if (!isset($_GET['password']) || $_GET['password'] !== $MIGRATION_PASSWORD) {
    die('Access denied. Add ?password=run_migration_2024 to URL');
}

require_once 'config.php';

echo "<!DOCTYPE html><html><head><title>Migration Runner</title><style>
body { font-family: monospace; padding: 20px; background: #f5f5f5; }
.success { color: green; }
.error { color: red; }
.info { color: blue; }
pre { background: white; padding: 10px; border: 1px solid #ddd; }
</style></head><body>";

echo "<h1>Access Level Migration Runner</h1>";

try {
    $conn = getDBConnection();
    
    if (!$conn) {
        throw new Exception("Database connection failed");
    }
    
    echo "<p class='success'>✓ Database connected</p>";
    
    // Check if column already exists
    $stmt = $conn->query("SHOW COLUMNS FROM lessons LIKE 'access_level'");
    if ($stmt->rowCount() > 0) {
        echo "<p class='info'>ℹ access_level column already exists. Dropping it first...</p>";
    }
    
    echo "<h2>Executing Migration...</h2><pre>";
    
    $sql = file_get_contents(__DIR__ . '/add_access_level_to_lessons.sql');
    
    if (!$sql) {
        throw new Exception("Could not read migration file");
    }
    
    // Execute the migration
    $statements = explode(';', $sql);
    $executed = 0;
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (empty($statement) || strpos($statement, '--') === 0) {
            continue;
        }
        
        try {
            $conn->exec($statement);
            $executed++;
            echo ".";
            flush();
        } catch (PDOException $e) {
            // Ignore SELECT 1 statements
            if (strpos($statement, 'SELECT 1') === false) {
                echo "\n<span class='error'>Warning: " . htmlspecialchars($e->getMessage()) . "</span>\n";
            }
        }
    }
    
    echo "\n</pre>";
    echo "<p class='success'>✓ Executed {$executed} statements</p>";
    
    // Verify the column exists
    $stmt = $conn->query("SHOW COLUMNS FROM lessons LIKE 'access_level'");
    if ($stmt->rowCount() > 0) {
        echo "<h2 class='success'>✓ Migration Successful!</h2>";
        
        $column = $stmt->fetch();
        echo "<p>Column details:</p><pre>";
        print_r($column);
        echo "</pre>";
        
        // Show current distribution
        $stmt = $conn->query("SELECT access_level, COUNT(*) as count FROM lessons GROUP BY access_level");
        echo "<h3>Current Access Level Distribution:</h3><ul>";
        while ($row = $stmt->fetch()) {
            $level = $row['access_level'] ?: 'NULL (accessible to all)';
            echo "<li>{$level}: {$row['count']} lessons</li>";
        }
        echo "</ul>";
        
        echo "<p class='success'><strong>✓ The access_level column has been successfully added!</strong></p>";
        echo "<p class='error'><strong>⚠ IMPORTANT: Delete this file (run_migration_web.php) now!</strong></p>";
        
    } else {
        echo "<p class='error'>✗ Failed to add access_level column</p>";
    }
    
} catch (Exception $e) {
    echo "<p class='error'>✗ Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}

echo "</body></html>";
?>
