-- Course Management System Tables for LMS
-- Add comprehensive course-related tables

-- Course categories table (separate from blog categories)
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)
);

-- Courses table
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)
);

-- Course enrollments table
CREATE TABLE IF NOT EXISTS course_enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    student_id INT NOT NULL,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completion_date TIMESTAMP NULL,
    progress_percentage DECIMAL(5,2) DEFAULT 0.00,
    status ENUM('enrolled', 'in_progress', 'completed', 'dropped') DEFAULT 'enrolled',
    payment_status ENUM('pending', 'paid', 'refunded') DEFAULT 'paid',
    payment_amount DECIMAL(10,2) DEFAULT 0.00,
    certificate_issued BOOLEAN DEFAULT FALSE,
    certificate_url VARCHAR(255) NULL,
    notes TEXT,
    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,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_enrollment (course_id, student_id),
    INDEX idx_student (student_id),
    INDEX idx_status (status),
    INDEX idx_enrollment_date (enrollment_date)
);

-- Course lessons table
CREATE TABLE IF NOT EXISTS course_lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    lesson_type ENUM('video', 'text', 'quiz', 'assignment', 'resource') DEFAULT 'text',
    content LONGTEXT,
    video_url VARCHAR(500) NULL,
    video_duration INT DEFAULT 0, -- in seconds
    attachment_url VARCHAR(255) NULL,
    attachment_name VARCHAR(255) NULL,
    sort_order INT DEFAULT 0,
    is_preview BOOLEAN DEFAULT FALSE,
    is_required BOOLEAN DEFAULT TRUE,
    estimated_time INT DEFAULT 0, -- in minutes
    prerequisites JSON, -- lesson IDs that must be completed first
    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,
    INDEX idx_course (course_id),
    INDEX idx_sort_order (sort_order),
    INDEX idx_type (lesson_type),
    UNIQUE KEY unique_lesson_slug (course_id, slug)
);

-- Course lesson progress table
CREATE TABLE IF NOT EXISTS course_lesson_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    enrollment_id INT NOT NULL,
    lesson_id INT NOT NULL,
    status ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started',
    progress_percentage DECIMAL(5,2) DEFAULT 0.00,
    time_spent INT DEFAULT 0, -- in seconds
    completed_at TIMESTAMP NULL,
    last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (enrollment_id) REFERENCES course_enrollments(id) ON DELETE CASCADE,
    FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
    UNIQUE KEY unique_progress (enrollment_id, lesson_id),
    INDEX idx_enrollment (enrollment_id),
    INDEX idx_status (status)
);

-- Course reviews/ratings table
CREATE TABLE IF NOT EXISTS course_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    student_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_title VARCHAR(255),
    review_text TEXT,
    is_verified BOOLEAN DEFAULT FALSE, -- completed the course
    helpful_votes INT DEFAULT 0,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    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,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_review (course_id, student_id),
    INDEX idx_course (course_id),
    INDEX idx_student (student_id),
    INDEX idx_rating (rating),
    INDEX idx_status (status)
);

-- Course analytics table
CREATE TABLE IF NOT EXISTS course_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    date DATE NOT NULL,
    views INT DEFAULT 0,
    enrollments INT DEFAULT 0,
    completions INT DEFAULT 0,
    average_rating DECIMAL(3,2) DEFAULT 0.00,
    revenue DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE KEY unique_analytics (course_id, date),
    INDEX idx_course (course_id),
    INDEX idx_date (date)
);

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

-- Insert sample courses
INSERT INTO courses (title, slug, description, short_description, instructor_id, category_id, level, duration_hours, is_free, status) VALUES
('Introduction to PHP Programming', 'introduction-to-php-programming', 'Learn PHP from basics to advanced concepts including OOP, databases, and web development.', 'Master PHP programming fundamentals and build dynamic web applications.', 1, 1, 'beginner', 20, TRUE, 'published'),
('Web Development with HTML, CSS & JavaScript', 'web-development-html-css-javascript', 'Complete guide to modern web development using HTML5, CSS3, and JavaScript.', 'Build responsive websites with modern web technologies.', 1, 1, 'beginner', 25, TRUE, 'published'),
('Digital Marketing Fundamentals', 'digital-marketing-fundamentals', 'Learn essential digital marketing strategies including SEO, social media, and content marketing.', 'Master digital marketing techniques to grow your online presence.', 1, 4, 'intermediate', 15, FALSE, 'published');

-- Insert sample lessons for first course
INSERT INTO course_lessons (course_id, title, slug, description, lesson_type, content, sort_order, estimated_time) VALUES
(1, 'Getting Started with PHP', 'getting-started-with-php', 'Introduction to PHP basics and environment setup', 'text', '<h2>PHP Basics</h2><p>PHP is a server-side scripting language...</p>', 1, 30),
(1, 'Variables and Data Types', 'variables-and-data-types', 'Understanding PHP variables and data types', 'text', '<h2>Variables in PHP</h2><p>Variables are containers for storing data values...</p>', 2, 45),
(1, 'Control Structures', 'control-structures', 'Learn about if statements, loops, and control flow', 'text', '<h2>Control Structures</h2><p>Control structures allow you to control the flow of execution...</p>', 3, 60);

-- Create indexes for better performance
CREATE INDEX idx_course_enrollments_course_student ON course_enrollments(course_id, student_id);
CREATE INDEX idx_course_lessons_course_order ON course_lessons(course_id, sort_order);
CREATE INDEX idx_course_reviews_course_rating ON course_reviews(course_id, rating);
CREATE INDEX idx_course_analytics_course_date ON course_analytics(course_id, date);