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

try {
    $pdo = getDB();

    // Create courses table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS courses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) UNIQUE NOT NULL,
            description LONGTEXT,
            short_description TEXT,
            instructor_id INT NOT NULL,
            category_id INT,
            thumbnail VARCHAR(255),
            banner_image VARCHAR(255),
            price DECIMAL(10,2) DEFAULT 0.00,
            currency VARCHAR(3) DEFAULT 'USD',
            level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
            duration_hours INT DEFAULT 0,
            language VARCHAR(50) DEFAULT 'English',
            max_students INT DEFAULT NULL,
            enrollment_deadline DATE NULL,
            prerequisites TEXT,
            learning_objectives JSON,
            tags JSON,
            status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
            is_featured BOOLEAN DEFAULT FALSE,
            is_free BOOLEAN DEFAULT TRUE,
            certificate_enabled BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (category_id) REFERENCES course_categories(id) ON DELETE SET NULL,
            INDEX idx_instructor (instructor_id),
            INDEX idx_category (category_id),
            INDEX idx_status (status),
            INDEX idx_featured (is_featured),
            INDEX idx_slug (slug)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create course_categories table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS course_categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            slug VARCHAR(100) UNIQUE NOT NULL,
            description TEXT,
            icon VARCHAR(50),
            color VARCHAR(20),
            parent_id INT NULL,
            sort_order INT DEFAULT 0,
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES course_categories(id) ON DELETE SET NULL,
            INDEX idx_slug (slug),
            INDEX idx_parent (parent_id),
            INDEX idx_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Insert sample course categories
    $pdo->exec("
        INSERT IGNORE INTO course_categories (name, slug, description, icon, color, sort_order) VALUES
        ('Technology', 'technology', 'Courses about programming, software development, and tech skills', 'fas fa-code', '#007bff', 1),
        ('Business', 'business', 'Business management, entrepreneurship, and professional development', 'fas fa-briefcase', '#28a745', 2),
        ('Design', 'design', 'Graphic design, UI/UX, and creative skills', 'fas fa-palette', '#e83e8c', 3),
        ('Marketing', 'marketing', 'Digital marketing, SEO, and advertising strategies', 'fas fa-bullhorn', '#fd7e14', 4),
        ('Data Science', 'data-science', 'Data analysis, machine learning, and statistics', 'fas fa-chart-line', '#6f42c1', 5),
        ('Languages', 'languages', 'Learn new languages and communication skills', 'fas fa-language', '#20c997', 6)
    ");

    echo "Courses and course categories migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying courses migration: " . $e->getMessage();
}
?>
