-- Content Management System Tables for LMS
-- Add tables for resources, versioning, analytics, and hierarchical content organization

-- Course modules for hierarchical organization
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 0,
    is_active BOOLEAN DEFAULT TRUE,
    estimated_time INT DEFAULT 0, -- in minutes
    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 (course_id, slug),
    INDEX idx_course (course_id),
    INDEX idx_sort_order (sort_order)
);

-- Update course_lessons to include module_id for hierarchical structure
ALTER TABLE course_lessons ADD COLUMN module_id INT NULL AFTER course_id;
ALTER TABLE course_lessons ADD FOREIGN KEY (module_id) REFERENCES course_modules(id) ON DELETE SET NULL;
ALTER TABLE course_lessons ADD INDEX idx_module (module_id);

-- Course resources table
CREATE TABLE IF NOT EXISTS course_resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    module_id INT NULL,
    lesson_id INT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    resource_type ENUM('document', 'video', 'audio', 'image', 'link', 'archive', 'other') DEFAULT 'document',
    file_path VARCHAR(500) NULL,
    file_name VARCHAR(255) NULL,
    file_size INT DEFAULT 0, -- in bytes
    mime_type VARCHAR(100) NULL,
    external_url VARCHAR(500) NULL,
    thumbnail_path VARCHAR(255) NULL,
    category VARCHAR(100) NULL,
    tags JSON,
    is_shared BOOLEAN DEFAULT FALSE,
    sharing_permissions JSON, -- who can access: ['instructor', 'students', 'public']
    download_count INT DEFAULT 0,
    view_count INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    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 (module_id) REFERENCES course_modules(id) ON DELETE SET NULL,
    FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_resource_slug (course_id, slug),
    INDEX idx_course (course_id),
    INDEX idx_module (module_id),
    INDEX idx_lesson (lesson_id),
    INDEX idx_type (resource_type),
    INDEX idx_category (category),
    INDEX idx_active (is_active),
    INDEX idx_shared (is_shared)
);

-- Lesson content versions for versioning
CREATE TABLE IF NOT EXISTS lesson_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lesson_id INT NOT NULL,
    version_number INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    content LONGTEXT,
    video_url VARCHAR(500) NULL,
    attachment_url VARCHAR(255) NULL,
    attachment_name VARCHAR(255) NULL,
    changes_description TEXT,
    is_published BOOLEAN DEFAULT FALSE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_version (lesson_id, version_number),
    INDEX idx_lesson (lesson_id),
    INDEX idx_published (is_published),
    INDEX idx_created_by (created_by)
);

-- Content analytics table
CREATE TABLE IF NOT EXISTS content_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content_type ENUM('lesson', 'resource', 'module') NOT NULL,
    content_id INT NOT NULL,
    course_id INT NOT NULL,
    date DATE NOT NULL,
    views INT DEFAULT 0,
    unique_views INT DEFAULT 0,
    time_spent INT DEFAULT 0, -- in seconds
    completions INT DEFAULT 0,
    downloads INT DEFAULT 0,
    shares INT DEFAULT 0,
    ratings_count INT DEFAULT 0,
    average_rating DECIMAL(3,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_analytics (content_type, content_id, date),
    INDEX idx_content (content_type, content_id),
    INDEX idx_course (course_id),
    INDEX idx_date (date)
);

-- Bulk operations log
CREATE TABLE IF NOT EXISTS content_operations_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type ENUM('bulk_upload', 'bulk_delete', 'bulk_move', 'bulk_copy', 'bulk_publish') NOT NULL,
    content_type ENUM('lesson', 'resource', 'module') NOT NULL,
    course_id INT NOT NULL,
    user_id INT NOT NULL,
    affected_items JSON, -- array of affected item IDs
    operation_data JSON, -- additional operation-specific data
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    error_message TEXT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_operation (operation_type),
    INDEX idx_course (course_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status)
);

-- SCORM/xAPI preparation tables
CREATE TABLE IF NOT EXISTS scorm_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    lesson_id INT NULL,
    package_name VARCHAR(255) NOT NULL,
    package_path VARCHAR(500) NOT NULL,
    manifest_path VARCHAR(500) NULL,
    version VARCHAR(20) DEFAULT '1.2',
    launch_url VARCHAR(500) NULL,
    mastery_score DECIMAL(5,2) NULL,
    max_time_allowed INT NULL, -- in minutes
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    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 (lesson_id) REFERENCES course_lessons(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_course (course_id),
    INDEX idx_lesson (lesson_id),
    INDEX idx_active (is_active)
);

-- xAPI statements storage (for future analytics)
CREATE TABLE IF NOT EXISTS xapi_statements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    lesson_id INT NULL,
    user_id INT NOT NULL,
    statement_id VARCHAR(255) UNIQUE NOT NULL,
    actor JSON NOT NULL,
    verb JSON NOT NULL,
    object JSON NOT NULL,
    result JSON NULL,
    context JSON NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    stored TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_course (course_id),
    INDEX idx_lesson (lesson_id),
    INDEX idx_user (user_id),
    INDEX idx_timestamp (timestamp)
);

-- Insert sample modules for existing courses
INSERT INTO course_modules (course_id, title, slug, description, sort_order, estimated_time) VALUES
(1, 'PHP Fundamentals', 'php-fundamentals', 'Basic concepts and syntax of PHP programming', 1, 120),
(1, 'Working with Data', 'working-with-data', 'Variables, arrays, and data manipulation', 2, 180),
(1, 'Control Structures', 'control-structures', 'Conditional statements and loops', 3, 150);

-- Update existing lessons to be associated with modules
UPDATE course_lessons SET module_id = 1 WHERE course_id = 1 AND sort_order = 1;
UPDATE course_lessons SET module_id = 2 WHERE course_id = 1 AND sort_order = 2;
UPDATE course_lessons SET module_id = 3 WHERE course_id = 1 AND sort_order = 3;

-- Insert sample resources
INSERT INTO course_resources (course_id, module_id, title, slug, description, resource_type, external_url, category, tags, is_shared, sharing_permissions, created_by) VALUES
(1, 1, 'PHP Official Documentation', 'php-official-documentation', 'Official PHP manual and documentation', 'link', 'https://www.php.net/docs.php', 'documentation', '["php", "documentation", "reference"]', TRUE, '["instructor", "students"]', 1),
(1, 2, 'PHP Cheat Sheet', 'php-cheat-sheet', 'Quick reference guide for PHP syntax', 'document', NULL, 'reference', '["php", "cheat-sheet", "syntax"]', TRUE, '["instructor", "students"]', 1);

-- Create indexes for better performance
CREATE INDEX idx_course_resources_course_module ON course_resources(course_id, module_id);
CREATE INDEX idx_course_resources_course_lesson ON course_resources(course_id, lesson_id);
CREATE INDEX idx_lesson_versions_lesson_version ON lesson_versions(lesson_id, version_number);
CREATE INDEX idx_content_analytics_content_date ON content_analytics(content_type, content_id, date);
CREATE INDEX idx_scorm_packages_course_lesson ON scorm_packages(course_id, lesson_id);