-- ============================================================
-- GMIND INSTITUTE - PRACTICE TEST & EXAM SYSTEM - FULL SQL
-- ============================================================
-- Run this SQL in phpMyAdmin (database: advaysin_gmind)
-- This creates ALL required tables for both Practice Test and Exam systems
-- ============================================================

-- 1. Practice Sets (exam definitions with bilingual support)
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',
    `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',
    `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`)
) 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',
    `student_email` VARCHAR(255) DEFAULT NULL COMMENT 'Guest email',
    `student_id` INT UNSIGNED DEFAULT NULL COMMENT 'Logged-in student ID',
    `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 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Exams (online exam definitions)
CREATE TABLE IF NOT EXISTS `exams` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `course_id` INT UNSIGNED DEFAULT NULL,
    `duration_minutes` INT NOT NULL DEFAULT 60,
    `total_marks` INT NOT NULL DEFAULT 100,
    `pass_marks` INT NOT NULL DEFAULT 40,
    `is_published` TINYINT(1) NOT NULL DEFAULT 0,
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Exam Questions (MCQ questions for online exams)
CREATE TABLE IF NOT EXISTS `exam_questions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `exam_id` INT UNSIGNED NOT NULL,
    `question_text` TEXT NOT NULL,
    `option_a` VARCHAR(500) NOT NULL,
    `option_b` VARCHAR(500) NOT NULL,
    `option_c` VARCHAR(500) DEFAULT NULL,
    `option_d` VARCHAR(500) DEFAULT NULL,
    `correct_answer` ENUM('a','b','c','d') NOT NULL DEFAULT 'a',
    `marks` INT NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_exam_id` (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Exam Results (student exam results)
CREATE TABLE IF NOT EXISTS `exam_results` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `exam_id` INT UNSIGNED NOT NULL,
    `student_id` INT UNSIGNED NOT NULL,
    `total_obtained` INT NOT NULL DEFAULT 0,
    `total_marks` INT NOT NULL DEFAULT 0,
    `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('pass','fail') NOT NULL DEFAULT 'fail',
    `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_exam_id` (`exam_id`),
    KEY `idx_student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
