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

try {
    $pdo = getDB();

    // Create course_modules table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS course_modules (
            id INT AUTO_INCREMENT PRIMARY KEY,
            course_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL,
            description TEXT,
            sort_order INT DEFAULT 1,
            estimated_time INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
            UNIQUE KEY unique_module_slug (slug)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_modules_course ON course_modules (course_id)");
    $pdo->exec("CREATE INDEX idx_modules_sort_order ON course_modules (sort_order)");

    // Alter unique key to global uniqueness on slug only
    try {
        $pdo->exec("ALTER TABLE course_modules DROP INDEX unique_module_slug");
        $pdo->exec("ALTER TABLE course_modules ADD UNIQUE KEY unique_module_slug (slug)");
    } catch (PDOException $e) {
        // Ignore if index doesn't exist or table doesn't exist
    }

    echo "Course modules migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying course modules migration: " . $e->getMessage();
}
?>
