-- ============================================================
-- Practice Set System — Database Schema
-- ============================================================
-- Run this SQL in phpMyAdmin to create all required tables.
-- ============================================================

-- 1. Practice Sets (exam definitions)
CREATE TABLE IF NOT EXISTS `practice_sets` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'English title',
    `title_hindi` VARCHAR(255) DEFAULT NULL COMMENT 'Hindi title',
    `course_id` INT UNSIGNED DEFAULT NULL COMMENT 'Linked course (CCC, O-Level etc)',
    `description` TEXT DEFAULT NULL COMMENT 'English description',
    `description_hindi` TEXT DEFAULT NULL COMMENT 'Hindi description',
    `duration_minutes` INT NOT NULL DEFAULT 60 COMMENT 'Exam duration in minutes',
    `total_marks` INT NOT NULL DEFAULT 100 COMMENT 'Total marks for the exam',
    `pass_marks` INT NOT NULL DEFAULT 40 COMMENT 'Minimum marks to pass',
    `negative_marks` DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT 'Negative marks per wrong answer (0 = no negative)',
    `is_published` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=visible on frontend, 0=draft',
    `result_published` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=show result immediately, 0=show submitted only',
    `language_mode` ENUM('both','hindi','english') NOT NULL DEFAULT 'both' COMMENT 'Which language to show on frontend',
    `max_attempts` INT NOT NULL DEFAULT 0 COMMENT 'Max attempts allowed (0=unlimited)',
    `show_correct_answers` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=show correct answers in result, 0=hide',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_course_id` (`course_id`),
    KEY `idx_is_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Practice Questions (bilingual MCQ questions)
CREATE TABLE IF NOT EXISTS `practice_questions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `practice_set_id` INT UNSIGNED NOT NULL,
    `question_text` TEXT NOT NULL COMMENT 'English question text',
    `question_text_hindi` TEXT DEFAULT NULL COMMENT 'Hindi question text',
    `option_a` VARCHAR(500) NOT NULL COMMENT 'English option A',
    `option_a_hindi` VARCHAR(500) DEFAULT NULL COMMENT 'Hindi option A',
    `option_b` VARCHAR(500) NOT NULL COMMENT 'English option B',
    `option_b_hindi` VARCHAR(500) DEFAULT NULL COMMENT 'Hindi option B',
    `option_c` VARCHAR(500) DEFAULT NULL COMMENT 'English option C',
    `option_c_hindi` VARCHAR(500) DEFAULT NULL COMMENT 'Hindi option C',
    `option_d` VARCHAR(500) DEFAULT NULL COMMENT 'English option D',
    `option_d_hindi` VARCHAR(500) DEFAULT NULL COMMENT 'Hindi option D',
    `correct_answer` ENUM('a','b','c','d') NOT NULL DEFAULT 'a',
    `marks` INT NOT NULL DEFAULT 1 COMMENT 'Marks for this question',
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_practice_set_id` (`practice_set_id`),
    CONSTRAINT `fk_pq_practice_set` FOREIGN KEY (`practice_set_id`) REFERENCES `practice_sets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Practice Attempts (student attempt records with results)
CREATE TABLE IF NOT EXISTS `practice_attempts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `practice_set_id` INT UNSIGNED NOT NULL,
    `student_name` VARCHAR(255) DEFAULT NULL COMMENT 'Guest name (not required)',
    `student_email` VARCHAR(255) DEFAULT NULL COMMENT 'Guest email (not required)',
    `student_id` INT UNSIGNED DEFAULT NULL COMMENT 'Logged-in student ID (if any)',
    `user_id` INT UNSIGNED DEFAULT NULL COMMENT 'Logged-in user ID from users table (reliable tracking)',
    `total_obtained` DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    `total_marks` INT NOT NULL DEFAULT 0,
    `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `correct_count` INT NOT NULL DEFAULT 0,
    `wrong_count` INT NOT NULL DEFAULT 0,
    `unanswered_count` INT NOT NULL DEFAULT 0,
    `status` ENUM('pass','fail') NOT NULL DEFAULT 'fail',
    `answers_json` JSON DEFAULT NULL COMMENT 'Full answer details stored as JSON',
    `started_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_practice_set_id` (`practice_set_id`),
    KEY `idx_student_id` (`student_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migration: Add user_id column to existing practice_attempts table (if not exists)
-- Run this if you already have the practice_attempts table without user_id column:
-- ALTER TABLE `practice_attempts` ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `student_id`;
-- ALTER TABLE `practice_attempts` ADD KEY `idx_user_id` (`user_id`);
