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

try {
    $pdo = getDB();

    // Create course_topics table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS course_topics (
            id INT AUTO_INCREMENT PRIMARY KEY,
            module_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 (module_id) REFERENCES course_modules(id) ON DELETE CASCADE,
            UNIQUE KEY unique_topic_slug (module_id, slug)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_topics_module ON course_topics (module_id)");
    $pdo->exec("CREATE INDEX idx_topics_sort_order ON course_topics (sort_order)");

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