-- ============================================================================
-- GMIND INSTITUTE — Fix practice_sets & practice_questions tables
-- ============================================================================
-- Run this SQL in phpMyAdmin against the `advaysin_gmind` database.
-- Idempotent — safe to run multiple times.
--
-- The error "Unknown column 'title_hindi' in 'field list'" happens because
-- the production practice_sets table was created from an OLD migration that
-- didn't have the Hindi bilingual columns. This script adds every missing
-- column to both practice_sets and practice_questions tables.
-- ============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. Ensure practice_sets table exists with full schema
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `practice_sets` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `title_hindi` VARCHAR(255) DEFAULT NULL,
    `course_id` INT UNSIGNED DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `description_hindi` TEXT DEFAULT NULL,
    `duration_minutes` INT NOT NULL DEFAULT 60,
    `total_marks` INT NOT NULL DEFAULT 100,
    `pass_marks` INT NOT NULL DEFAULT 40,
    `negative_marks` DECIMAL(3,1) NOT NULL DEFAULT 0.0,
    `is_published` TINYINT(1) NOT NULL DEFAULT 0,
    `result_published` TINYINT(1) NOT NULL DEFAULT 1,
    `language_mode` ENUM('both','hindi','english') NOT NULL DEFAULT 'both',
    `max_attempts` INT NOT NULL DEFAULT 0,
    `show_correct_answers` TINYINT(1) NOT NULL DEFAULT 1,
    `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;

-- Add missing columns to practice_sets if the table already exists with old schema

-- title_hindi  ★ THE COLUMN THE USER IS MISSING ★
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'title_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `title_hindi` VARCHAR(255) DEFAULT NULL AFTER `title`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- description_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'description_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `description_hindi` TEXT DEFAULT NULL AFTER `description`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- course_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'course_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `course_id` INT UNSIGNED DEFAULT NULL AFTER `title_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- duration_minutes
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'duration_minutes');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `duration_minutes` INT NOT NULL DEFAULT 60 AFTER `description_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- total_marks
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'total_marks');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `total_marks` INT NOT NULL DEFAULT 100 AFTER `duration_minutes`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- pass_marks
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'pass_marks');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `pass_marks` INT NOT NULL DEFAULT 40 AFTER `total_marks`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- negative_marks
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'negative_marks');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `negative_marks` DECIMAL(3,1) NOT NULL DEFAULT 0.0 AFTER `pass_marks`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- is_published
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'is_published');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `is_published` TINYINT(1) NOT NULL DEFAULT 0 AFTER `negative_marks`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- result_published
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'result_published');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `result_published` TINYINT(1) NOT NULL DEFAULT 1 AFTER `is_published`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- language_mode
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'language_mode');
SET @sql := IF(@col = 0, "ALTER TABLE `practice_sets` ADD COLUMN `language_mode` ENUM('both','hindi','english') NOT NULL DEFAULT 'both' AFTER `result_published`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- max_attempts
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'max_attempts');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `max_attempts` INT NOT NULL DEFAULT 0 AFTER `language_mode`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- show_correct_answers
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = 'show_correct_answers');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_sets` ADD COLUMN `show_correct_answers` TINYINT(1) NOT NULL DEFAULT 1 AFTER `max_attempts`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. Ensure practice_questions table exists with full schema
-- ─────────────────────────────────────────────────────────────────────────────
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,
    `question_text_hindi` TEXT DEFAULT NULL,
    `option_a` VARCHAR(500) NOT NULL,
    `option_a_hindi` VARCHAR(500) DEFAULT NULL,
    `option_b` VARCHAR(500) NOT NULL,
    `option_b_hindi` VARCHAR(500) DEFAULT NULL,
    `option_c` VARCHAR(500) DEFAULT NULL,
    `option_c_hindi` VARCHAR(500) DEFAULT NULL,
    `option_d` VARCHAR(500) DEFAULT NULL,
    `option_d_hindi` 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_practice_set_id` (`practice_set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add missing columns to practice_questions (handles ALL old-schema variants)

-- practice_set_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'practice_set_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `practice_set_id` INT UNSIGNED NOT NULL AFTER `id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- question_text
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'question_text');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `question_text` TEXT NOT NULL AFTER `practice_set_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- question_text_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'question_text_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `question_text_hindi` TEXT DEFAULT NULL AFTER `question_text`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_a
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_a');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_a` VARCHAR(500) NOT NULL AFTER `question_text_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_a_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_a_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_a_hindi` VARCHAR(500) DEFAULT NULL AFTER `option_a`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_b
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_b');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_b` VARCHAR(500) NOT NULL AFTER `option_a_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_b_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_b_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_b_hindi` VARCHAR(500) DEFAULT NULL AFTER `option_b`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_c (might be missing in very old schema)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_c');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_c` VARCHAR(500) DEFAULT NULL AFTER `option_b_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_c_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_c_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_c_hindi` VARCHAR(500) DEFAULT NULL AFTER `option_c`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_d (might be missing in very old schema)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_d');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_d` VARCHAR(500) DEFAULT NULL AFTER `option_c_hindi`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- option_d_hindi
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'option_d_hindi');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `option_d_hindi` VARCHAR(500) DEFAULT NULL AFTER `option_d`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- correct_answer
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'correct_answer');
SET @sql := IF(@col = 0, "ALTER TABLE `practice_questions` ADD COLUMN `correct_answer` ENUM('a','b','c','d') NOT NULL DEFAULT 'a' AFTER `option_d_hindi`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ★ marks — THE COLUMN THE USER IS MISSING ★
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'marks');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `marks` INT NOT NULL DEFAULT 1 AFTER `correct_answer`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ★ sort_order — also missing on the user's table (same old-schema pattern) ★
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'sort_order');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0 AFTER `marks`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- created_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_questions` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Useful index on practice_set_id (only if missing)
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND INDEX_NAME = 'idx_practice_set_id');
SET @sql := IF(@idx = 0, 'ALTER TABLE `practice_questions` ADD INDEX `idx_practice_set_id` (`practice_set_id`)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. Ensure practice_attempts table exists with full schema
-- ─────────────────────────────────────────────────────────────────────────────
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,
    `student_email` VARCHAR(255) DEFAULT NULL,
    `student_id` INT UNSIGNED DEFAULT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `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,
    `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;

-- Add user_id column if missing (used by attempt tracking)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_attempts' AND COLUMN_NAME = 'user_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `practice_attempts` ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `student_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_attempts' AND INDEX_NAME = 'idx_user_id');
SET @sql := IF(@idx = 0, 'ALTER TABLE `practice_attempts` ADD INDEX `idx_user_id` (`user_id`)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- DONE — verify with:
--   DESCRIBE practice_sets;          -- should show title_hindi, description_hindi, etc.
--   DESCRIBE practice_questions;     -- should show ALL columns: practice_set_id,
--                                       question_text, question_text_hindi,
--                                       option_a..d + option_*_hindi,
--                                       correct_answer, marks, sort_order, created_at
--   DESCRIBE practice_attempts;      -- should show user_id
--
-- If the user is still getting "Unknown column 'X' in 'field list'" after this,
-- run: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'practice_questions';
-- and compare against the column list above.
-- ══════════════════════════════════════════════════════════════════════════════
