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

try {
    $pdo = getDB();

    // Create lesson_resources table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS lesson_resources (
            id INT AUTO_INCREMENT PRIMARY KEY,
            lesson_id INT NOT NULL,
            resource_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
            FOREIGN KEY (resource_id) REFERENCES course_resources(id) ON DELETE CASCADE,
            UNIQUE KEY unique_lesson_resource (lesson_id, resource_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_lesson_resources_lesson ON lesson_resources (lesson_id)");
    $pdo->exec("CREATE INDEX idx_lesson_resources_resource ON lesson_resources (resource_id)");

    echo "Lesson resources migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying lesson resources migration: " . $e->getMessage();
}
?>
