<?php
// Run the access_level migration
require_once 'config.php';

try {
    $conn = getDBConnection();
    
    if (!$conn) {
        die("Database connection failed\n");
    }
    
    echo "Reading migration file...\n";
    $sql = file_get_contents(__DIR__ . '/add_access_level_to_lessons.sql');
    
    if (!$sql) {
        die("Could not read migration file\n");
    }
    
    echo "Executing migration...\n";
    
    // Split by semicolons and execute each statement
    $statements = explode(';', $sql);
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (empty($statement)) {
            continue;
        }
        
        try {
            $conn->exec($statement);
            echo ".";
        } catch (PDOException $e) {
            // Ignore errors for SELECT 1 statements
            if (strpos($statement, 'SELECT 1') === false) {
                echo "\nWarning: " . $e->getMessage() . "\n";
            }
        }
    }
    
    echo "\n\nMigration completed successfully!\n";
    
    // Verify the column exists
    $stmt = $conn->query("SHOW COLUMNS FROM lessons LIKE 'access_level'");
    if ($stmt->rowCount() > 0) {
        echo "✓ access_level column has been added to lessons table\n";
        
        // Show current access levels
        $stmt = $conn->query("SELECT COUNT(*) as total, access_level FROM lessons GROUP BY access_level");
        echo "\nCurrent access level distribution:\n";
        while ($row = $stmt->fetch()) {
            $level = $row['access_level'] ?: 'NULL (accessible to all)';
            echo "  - {$level}: {$row['total']} lessons\n";
        }
    } else {
        echo "✗ Failed to add access_level column\n";
    }
    
} catch (Exception $e) {
    die("Error: " . $e->getMessage() . "\n");
}
