-- Question Bank and Exam Builder Tables for LMS
-- Add comprehensive assessment creation and management capabilities

-- Question categories for organizing questions
CREATE TABLE IF NOT EXISTS question_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INT NULL,
    color VARCHAR(20) DEFAULT '#007bff',
    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 question_categories(id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_parent (parent_id),
    INDEX idx_active (is_active)
);

-- Questions table supporting multiple types
CREATE TABLE IF NOT EXISTS questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    content LONGTEXT NOT NULL, -- rich text content
    question_type ENUM('mcq_single', 'mcq_multiple', 'true_false', 'essay', 'short_answer', 'matching', 'code', 'fill_blanks') NOT NULL,
    difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'medium',
    points DECIMAL(5,2) DEFAULT 1.00,
    time_limit INT NULL, -- in seconds, NULL for no limit
    category_id INT NULL,
    course_id INT NULL, -- NULL for global questions, specific course_id for course-specific
    instructor_id INT NOT NULL,
    tags JSON, -- array of tag strings
    explanation TEXT, -- explanation for correct answer
    hints JSON, -- array of hint objects
    metadata JSON, -- additional question-specific data
    status ENUM('draft', 'pending_review', 'approved', 'rejected', 'archived') DEFAULT 'draft',
    review_status ENUM('not_submitted', 'pending', 'approved', 'needs_revision') DEFAULT 'not_submitted',
    usage_count INT DEFAULT 0,
    success_rate DECIMAL(5,2) DEFAULT 0.00, -- percentage of correct answers
    is_shared BOOLEAN DEFAULT FALSE, -- can be used by other instructors
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES question_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_type (question_type),
    INDEX idx_difficulty (difficulty),
    INDEX idx_category (category_id),
    INDEX idx_course (course_id),
    INDEX idx_instructor (instructor_id),
    INDEX idx_status (status),
    INDEX idx_review_status (review_status),
    INDEX idx_shared (is_shared)
);

-- Question options for multiple choice and matching questions
CREATE TABLE IF NOT EXISTS question_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    option_text LONGTEXT NOT NULL,
    option_value VARCHAR(255) NULL, -- for matching questions (left side)
    match_value VARCHAR(255) NULL, -- for matching questions (right side)
    is_correct BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    INDEX idx_question (question_id),
    INDEX idx_sort_order (sort_order),
    INDEX idx_correct (is_correct)
);

-- Question tags for better organization
CREATE TABLE IF NOT EXISTS question_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    color VARCHAR(20) DEFAULT '#6c757d',
    description TEXT,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug)
);

-- Junction table for question tags
CREATE TABLE IF NOT EXISTS question_tag_assignments (
    question_id INT NOT NULL,
    tag_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, tag_id),
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES question_tags(id) ON DELETE CASCADE,
    INDEX idx_question (question_id),
    INDEX idx_tag (tag_id)
);

-- Question reviews and approval workflow
CREATE TABLE IF NOT EXISTS question_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    reviewer_id INT NOT NULL,
    review_status ENUM('approved', 'needs_revision', 'rejected') NOT NULL,
    comments TEXT,
    reviewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_question (question_id),
    INDEX idx_reviewer (reviewer_id),
    INDEX idx_status (review_status),
    INDEX idx_reviewed_at (reviewed_at)
);

-- Exams table
CREATE TABLE IF NOT EXISTS exams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description LONGTEXT,
    course_id INT NULL, -- NULL for general exams, specific for course exams
    instructor_id INT NOT NULL,
    exam_type ENUM('quiz', 'midterm', 'final', 'practice', 'certification') DEFAULT 'quiz',
    total_points DECIMAL(6,2) DEFAULT 0.00,
    passing_score DECIMAL(5,2) DEFAULT 50.00, -- percentage
    time_limit INT NULL, -- in minutes, NULL for no limit
    max_attempts INT DEFAULT 1,
    shuffle_questions BOOLEAN DEFAULT FALSE,
    shuffle_options BOOLEAN DEFAULT FALSE,
    show_results BOOLEAN DEFAULT TRUE,
    show_answers BOOLEAN DEFAULT FALSE,
    allow_review BOOLEAN DEFAULT TRUE,
    start_date TIMESTAMP NULL,
    end_date TIMESTAMP NULL,
    is_proctored BOOLEAN DEFAULT FALSE,
    proctoring_settings JSON, -- camera, screen sharing, etc.
    security_settings JSON, -- browser lockdown, copy-paste prevention, etc.
    instructions LONGTEXT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    is_template BOOLEAN DEFAULT FALSE,
    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 (instructor_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_course (course_id),
    INDEX idx_instructor (instructor_id),
    INDEX idx_type (exam_type),
    INDEX idx_status (status),
    INDEX idx_template (is_template)
);

-- Exam questions (linking exams to questions with configuration)
CREATE TABLE IF NOT EXISTS exam_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exam_id INT NOT NULL,
    question_id INT NOT NULL,
    sort_order INT DEFAULT 0,
    points DECIMAL(5,2) DEFAULT 1.00,
    time_limit INT NULL, -- override question time limit
    is_required BOOLEAN DEFAULT TRUE,
    section_name VARCHAR(100) NULL, -- for organizing exam into sections
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_exam_question (exam_id, question_id),
    INDEX idx_exam (exam_id),
    INDEX idx_question (question_id),
    INDEX idx_sort_order (sort_order),
    INDEX idx_section (section_name)
);

-- Question usage analytics
CREATE TABLE IF NOT EXISTS question_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    exam_id INT NULL,
    course_id INT NULL,
    date DATE NOT NULL,
    times_used INT DEFAULT 0,
    correct_answers INT DEFAULT 0,
    incorrect_answers INT DEFAULT 0,
    average_time INT DEFAULT 0, -- in seconds
    difficulty_rating DECIMAL(3,2) DEFAULT 0.00, -- 1-5 scale
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE KEY unique_analytics (question_id, exam_id, date),
    INDEX idx_question (question_id),
    INDEX idx_exam (exam_id),
    INDEX idx_course (course_id),
    INDEX idx_date (date)
);

-- Exam attempts (for future student functionality)
CREATE TABLE IF NOT EXISTS exam_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exam_id INT NOT NULL,
    student_id INT NOT NULL,
    attempt_number INT DEFAULT 1,
    status ENUM('in_progress', 'completed', 'abandoned', 'timed_out') DEFAULT 'in_progress',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    time_spent INT DEFAULT 0, -- in seconds
    score DECIMAL(6,2) DEFAULT 0.00,
    max_score DECIMAL(6,2) DEFAULT 0.00,
    percentage DECIMAL(5,2) DEFAULT 0.00,
    is_passed BOOLEAN DEFAULT FALSE,
    answers JSON, -- student's answers
    feedback TEXT,
    ip_address VARCHAR(45),
    browser_info JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attempt (exam_id, student_id, attempt_number),
    INDEX idx_exam (exam_id),
    INDEX idx_student (student_id),
    INDEX idx_status (status),
    INDEX idx_started_at (started_at)
);

-- Insert sample question categories
INSERT INTO question_categories (name, slug, description, color) VALUES
('General Knowledge', 'general-knowledge', 'General knowledge and trivia questions', '#007bff'),
('Mathematics', 'mathematics', 'Math problems and calculations', '#28a745'),
('Science', 'science', 'Physics, Chemistry, Biology questions', '#dc3545'),
('Programming', 'programming', 'Coding and programming concepts', '#6f42c1'),
('Language', 'language', 'Grammar, vocabulary, and language skills', '#fd7e14'),
('Business', 'business', 'Business and management concepts', '#20c997');

-- Insert sample question tags
INSERT INTO question_tags (name, slug, description, color) VALUES
('Basic', 'basic', 'Fundamental concepts', '#6c757d'),
('Intermediate', 'intermediate', 'Moderate difficulty', '#17a2b8'),
('Advanced', 'advanced', 'Complex topics', '#dc3545'),
('Conceptual', 'conceptual', 'Understanding-based', '#007bff'),
('Practical', 'practical', 'Application-based', '#28a745'),
('Problem Solving', 'problem-solving', 'Analytical thinking', '#ffc107');

-- Create indexes for better performance
CREATE INDEX idx_questions_type_difficulty ON questions(question_type, difficulty);
CREATE INDEX idx_questions_course_instructor ON questions(course_id, instructor_id);
CREATE INDEX idx_exams_course_instructor ON exams(course_id, instructor_id);
CREATE INDEX idx_exam_questions_exam_order ON exam_questions(exam_id, sort_order);
CREATE INDEX idx_question_analytics_question_date ON question_analytics(question_id, date);
CREATE INDEX idx_exam_attempts_exam_student ON exam_attempts(exam_id, student_id);