-- ============================================
-- DATABASE SCHEMA UPDATES FOR MODEL SETS QUIZ
-- ============================================

-- 1. ALTER questions table to add marks column
ALTER TABLE questions 
ADD COLUMN marks INT NOT NULL DEFAULT 1 
COMMENT 'Marks/points for this question';

-- Add index for better performance
CREATE INDEX idx_questions_marks ON questions(marks);

-- 2. CREATE modelset table for Model Test Sets
CREATE TABLE modelset (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL COMMENT 'Course this model set belongs to',
    title VARCHAR(255) NOT NULL COMMENT 'Model set title',
    description TEXT COMMENT 'Model set description',
    question_ids JSON NOT NULL COMMENT 'Array of question IDs in this model set',
    time_limit INT NOT NULL DEFAULT 60 COMMENT 'Time limit in minutes',
    total_marks INT NOT NULL DEFAULT 0 COMMENT 'Total marks for this model set',
    total_questions INT NOT NULL DEFAULT 0 COMMENT 'Total number of questions',
    difficulty_level ENUM('easy', 'medium', 'hard', 'mixed') DEFAULT 'mixed' COMMENT 'Overall difficulty level',
    category VARCHAR(100) DEFAULT 'General' COMMENT 'Model set category',
    instructions TEXT COMMENT 'Special instructions for this model set',
    passing_marks INT DEFAULT 0 COMMENT 'Minimum marks to pass',
    negative_marking BOOLEAN DEFAULT FALSE COMMENT 'Whether negative marking is enabled',
    negative_marks_per_wrong DECIMAL(3,2) DEFAULT 0.25 COMMENT 'Marks deducted per wrong answer',
    shuffle_questions BOOLEAN DEFAULT TRUE COMMENT 'Whether to shuffle question order',
    show_result_immediately BOOLEAN DEFAULT FALSE COMMENT 'Show result after quiz completion',
    allow_review BOOLEAN DEFAULT TRUE COMMENT 'Allow reviewing answers after completion',
    max_attempts INT DEFAULT 1 COMMENT 'Maximum attempts allowed per user',
    created_by INT COMMENT 'User ID who created this model set',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Whether this model set is active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_modelset_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    CONSTRAINT fk_modelset_created_by FOREIGN KEY (created_by) REFERENCES user(id) ON DELETE SET NULL,
    
    -- Indexes for better performance
    INDEX idx_modelset_course (course_id),
    INDEX idx_modelset_category (category),
    INDEX idx_modelset_difficulty (difficulty_level),
    INDEX idx_modelset_active (is_active),
    INDEX idx_modelset_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Model test sets with advanced quiz features';

-- 3. CREATE modelset_attempts table to track user attempts
CREATE TABLE modelset_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    modelset_id INT NOT NULL,
    session_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique session identifier',
    score_percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    total_marks_obtained DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    total_marks_possible INT NOT NULL DEFAULT 0,
    total_questions INT NOT NULL DEFAULT 0,
    correct_answers INT NOT NULL DEFAULT 0,
    wrong_answers INT NOT NULL DEFAULT 0,
    unanswered INT NOT NULL DEFAULT 0,
    time_taken INT NOT NULL DEFAULT 0 COMMENT 'Time taken in seconds',
    time_limit INT NOT NULL DEFAULT 0 COMMENT 'Time limit in seconds',
    attempt_number INT NOT NULL DEFAULT 1,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    is_completed BOOLEAN DEFAULT FALSE,
    answers_data JSON COMMENT 'Detailed answers data',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_modelset_attempts_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_modelset_attempts_modelset FOREIGN KEY (modelset_id) REFERENCES modelset(id) ON DELETE CASCADE,
    
    -- Unique constraint to prevent duplicate sessions
    UNIQUE KEY unique_user_modelset_attempt (user_id, modelset_id, attempt_number),
    
    -- Indexes
    INDEX idx_modelset_attempts_user (user_id),
    INDEX idx_modelset_attempts_modelset (modelset_id),
    INDEX idx_modelset_attempts_completed_at (completed_at),
    INDEX idx_modelset_attempts_score (score_percentage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User attempts for model test sets';

-- 4. CREATE modelset_question_responses table for detailed question tracking
CREATE TABLE modelset_question_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    attempt_id INT NOT NULL,
    question_id INT NOT NULL,
    selected_option VARCHAR(10) COMMENT 'Selected option (A, B, C, D, etc.)',
    selected_option_text TEXT COMMENT 'Text of selected option',
    correct_option VARCHAR(10) NOT NULL COMMENT 'Correct option',
    correct_option_text TEXT COMMENT 'Text of correct option',
    is_correct BOOLEAN NOT NULL DEFAULT FALSE,
    marks_awarded DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    marks_possible INT NOT NULL DEFAULT 1,
    time_taken INT DEFAULT 0 COMMENT 'Time taken for this question in seconds',
    question_order INT DEFAULT 0 COMMENT 'Order in which question appeared',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_modelset_responses_attempt FOREIGN KEY (attempt_id) REFERENCES modelset_attempts(id) ON DELETE CASCADE,
    CONSTRAINT fk_modelset_responses_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    
    -- Indexes
    INDEX idx_modelset_responses_attempt (attempt_id),
    INDEX idx_modelset_responses_question (question_id),
    INDEX idx_modelset_responses_correct (is_correct)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Individual question responses for model test attempts';

-- 5. UPDATE quiz_sessions to include model_set information
ALTER TABLE quiz_sessions 
ADD COLUMN modelset_id INT NULL COMMENT 'Reference to modelset if this is a model test',
ADD COLUMN marks_obtained DECIMAL(8,2) DEFAULT 0.00 COMMENT 'Total marks obtained',
ADD COLUMN total_marks_possible INT DEFAULT 0 COMMENT 'Total marks possible',
ADD CONSTRAINT fk_quiz_sessions_modelset FOREIGN KEY (modelset_id) REFERENCES modelset(id) ON DELETE SET NULL;

-- Add index for model set sessions
CREATE INDEX idx_quiz_sessions_modelset ON quiz_sessions(modelset_id);

-- 6. ALTER existing modelset table to add course_id (if table already exists)
-- If you already have a modelset table without course_id, run these commands:
-- ALTER TABLE modelset ADD COLUMN course_id INT NOT NULL DEFAULT 1 AFTER id;
-- ALTER TABLE modelset ADD CONSTRAINT fk_modelset_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE;
-- CREATE INDEX idx_modelset_course ON modelset(course_id);

-- 7. Sample data for testing (optional - adjust course_id values as needed)
INSERT INTO modelset (course_id, title, description, question_ids, time_limit, total_marks, total_questions, difficulty_level, category, instructions, passing_marks) 
VALUES 
(1, 'JEE Main Mock Test 1', 'Comprehensive mock test covering Physics, Chemistry, and Mathematics', 
 JSON_ARRAY(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), 180, 100, 10, 'hard', 'JEE Main', 
 'This is a full-length mock test. Read all instructions carefully before starting.', 40),
 
(2, 'NEET Practice Set A', 'Biology, Physics, and Chemistry practice questions', 
 JSON_ARRAY(11, 12, 13, 14, 15, 16, 17, 18, 19, 20), 120, 80, 10, 'medium', 'NEET', 
 'Focus on conceptual understanding. Negative marking applies.', 32);

-- 7. Update existing questions with marks (example - adjust as needed)
UPDATE questions SET marks = 1 WHERE difficulty = 'easy';
UPDATE questions SET marks = 2 WHERE difficulty = 'medium';  
UPDATE questions SET marks = 3 WHERE difficulty = 'hard';

-- ============================================
-- VERIFICATION QUERIES (run after updates)
-- ============================================

-- Check if marks column was added successfully
-- SELECT * FROM information_schema.columns WHERE table_name = 'questions' AND column_name = 'marks';

-- Check modelset table structure
-- DESCRIBE modelset;

-- Check sample data
-- SELECT * FROM modelset LIMIT 5;

-- Check updated quiz_sessions structure
-- DESCRIBE quiz_sessions;