-- ============================================================================
-- PHASE 4 MIGRATION — Exams / Admit Cards / ID Cards / Results
-- For: Gmind Institute — Branch Admin panel
-- ============================================================================
-- COMPATIBILITY: MySQL 5.7+ / MySQL 8.x / MariaDB 10.x (ALL versions)
-- SAFE TO RE-RUN: Each statement checks INFORMATION_SCHEMA before acting.
--
-- NOTE: This version does NOT use "ADD COLUMN IF NOT EXISTS" because that
-- syntax is only supported in MariaDB 10.5+ and MySQL 8.0.29+. On older
-- MySQL 5.7 / MariaDB 10.4 it throws error #1064. Instead we use the
-- PREPARE/EXECUTE pattern with INFORMATION_SCHEMA checks — works everywhere.
--
-- HOW TO RUN: phpMyAdmin → Import → Choose this file → Go
-- ============================================================================


-- ---------------------------------------------------------------------------
-- 1. exams — add bilingual / scheduling / toggle columns one-by-one
-- ---------------------------------------------------------------------------

-- 1a. branch_id
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='branch_id');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `course_id`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1b. language
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='language');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `language` ENUM(''en'',''hi'',''both'') NOT NULL DEFAULT ''en'' AFTER `branch_id`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1c. title_hindi
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='title_hindi');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `title_hindi` VARCHAR(255) NULL DEFAULT NULL AFTER `title`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1d. instructions_hindi
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='instructions_hindi');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `instructions_hindi` TEXT NULL DEFAULT NULL AFTER `instructions`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

-- 1f. show_result
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='show_result');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `show_result` TINYINT(1) NOT NULL DEFAULT 1 AFTER `is_active`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1g. start_time
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='start_time');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `start_time` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1h. end_time
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='end_time');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD COLUMN `end_time` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1i. Make course_id nullable (branch exams may skip course)
SET @c := (SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND COLUMN_NAME='course_id');
SET @s := IF(@c='NO', 'ALTER TABLE `exams` MODIFY COLUMN `course_id` BIGINT UNSIGNED NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1j. Drop the foreign key fk_exams_course_id if it exists (so nullable course_id works)
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND CONSTRAINT_NAME='fk_exams_course_id');
SET @s := IF(@c>0, 'ALTER TABLE `exams` DROP FOREIGN KEY `fk_exams_course_id`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1k. Add branch filter index
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND INDEX_NAME='idx_exams_branch');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD INDEX `idx_exams_branch` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1l. Add active/schedule composite index
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exams' AND INDEX_NAME='idx_exams_active_schedule');
SET @s := IF(@c=0, 'ALTER TABLE `exams` ADD INDEX `idx_exams_active_schedule` (`is_active`, `is_published`, `start_time`, `end_time`)', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 2. exam_questions — add bilingual columns
-- ---------------------------------------------------------------------------

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

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

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

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

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

-- 2f. Make option_c nullable
SET @c := (SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_questions' AND COLUMN_NAME='option_c');
SET @s := IF(@c='NO', 'ALTER TABLE `exam_questions` MODIFY COLUMN `option_c` VARCHAR(500) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2g. Make option_d nullable
SET @c := (SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_questions' AND COLUMN_NAME='option_d');
SET @s := IF(@c='NO', 'ALTER TABLE `exam_questions` MODIFY COLUMN `option_d` VARCHAR(500) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 3. admit_cards — branch-issued exam admit cards
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admit_cards` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to students.id',
    `exam_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to exams.id (nullable for generic cards)',
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `card_number` VARCHAR(60) NOT NULL COMMENT 'Unique card number',
    `login_username` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Username printed on card (usually enrollment_no)',
    `login_password` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Plain-text password printed on card (also hashed into users.password)',
    `status` ENUM('issued','revoked','used') NOT NULL DEFAULT 'issued',
    `issued_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_admit_cards_card_number` (`card_number`),
    INDEX `idx_admit_cards_student` (`student_id`),
    INDEX `idx_admit_cards_exam` (`exam_id`),
    INDEX `idx_admit_cards_branch` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Branch-issued exam admit cards';

-- 3a. admit_cards.login_username (auto-add if table already existed without this column)
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admit_cards' AND COLUMN_NAME='login_username');
SET @s := IF(@c=0, 'ALTER TABLE `admit_cards` ADD COLUMN `login_username` VARCHAR(100) NULL DEFAULT NULL AFTER `card_number`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3b. admit_cards.login_password
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='admit_cards' AND COLUMN_NAME='login_password');
SET @s := IF(@c=0, 'ALTER TABLE `admit_cards` ADD COLUMN `login_password` VARCHAR(100) NULL DEFAULT NULL AFTER `login_username`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 4. id_cards — branch-issued student ID cards
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `id_cards` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to students.id',
    `course_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to courses.id',
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `card_number` VARCHAR(60) NOT NULL COMMENT 'Unique card number',
    `qr_hash` VARCHAR(80) NULL DEFAULT NULL COMMENT 'SHA-256 hash for QR verification',
    `status` ENUM('generated','revoked','expired') NOT NULL DEFAULT 'generated',
    `generated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expiry_date` DATE NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_id_cards_card_number` (`card_number`),
    INDEX `idx_id_cards_student` (`student_id`),
    INDEX `idx_id_cards_branch` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Branch-issued student ID cards';


-- ---------------------------------------------------------------------------
-- 5. branch_results — branch-published exam results with approval workflow
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `branch_results` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to students.id',
    `exam_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to exams.id (nullable for offline results)',
    `course_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to courses.id',
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `marks_obtained` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `total_marks` DECIMAL(8,2) NOT NULL DEFAULT 100.00,
    `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `grade` VARCHAR(4) NULL DEFAULT NULL,
    `division` VARCHAR(10) NULL DEFAULT NULL,
    `result_status` ENUM('pass','fail') NOT NULL DEFAULT 'pass',
    `status` ENUM('draft','pending_approval','published','rejected') NOT NULL DEFAULT 'draft',
    `fee_cleared` TINYINT(1) NOT NULL DEFAULT 0,
    `approval_required` TINYINT(1) NOT NULL DEFAULT 0,
    `verification_hash` VARCHAR(80) NULL DEFAULT NULL,
    `remarks` TEXT NULL DEFAULT NULL,
    `published_at` DATETIME NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_branch_results_student` (`student_id`),
    INDEX `idx_branch_results_exam` (`exam_id`),
    INDEX `idx_branch_results_branch` (`branch_id`),
    INDEX `idx_branch_results_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Branch-published exam results with approval workflow';


-- ---------------------------------------------------------------------------
-- 6. students — add fee_due, father_name, admission_date, gender if missing
-- ---------------------------------------------------------------------------

-- 6a. fee_due
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='students' AND COLUMN_NAME='fee_due');
SET @s := IF(@c=0, 'ALTER TABLE `students` ADD COLUMN `fee_due` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `status`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6b. father_name
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='students' AND COLUMN_NAME='father_name');
SET @s := IF(@c=0, 'ALTER TABLE `students` ADD COLUMN `father_name` VARCHAR(150) NULL DEFAULT NULL AFTER `dob`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6c. admission_date
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='students' AND COLUMN_NAME='admission_date');
SET @s := IF(@c=0, 'ALTER TABLE `students` ADD COLUMN `admission_date` DATE NULL DEFAULT NULL AFTER `father_name`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6d. gender
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='students' AND COLUMN_NAME='gender');
SET @s := IF(@c=0, 'ALTER TABLE `students` ADD COLUMN `gender` ENUM(''male'',''female'',''other'') NULL DEFAULT NULL AFTER `dob`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 7. exam_results — add attempt_number + answers_json columns if missing
--    (used by the student portal for attempt tracking + answer review)
-- ---------------------------------------------------------------------------

-- 7a. attempt_number
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='attempt_number');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `attempt_number` INT UNSIGNED NOT NULL DEFAULT 1 AFTER `student_id`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7b. answers_json (for storing the student's selected answers per question)
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='answers_json');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `answers_json` TEXT NULL DEFAULT NULL AFTER `status`', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7c. branch_approved — branch admin approval flag (unlocks marksheet/certificate)
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='branch_approved');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `branch_approved` TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7d. approved_at
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='approved_at');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `approved_at` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7e. approved_by
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='approved_by');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `approved_by` BIGINT UNSIGNED NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7f. admin_remarks
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='admin_remarks');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `admin_remarks` TEXT NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7g. marksheet_generated
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='marksheet_generated');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `marksheet_generated` TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7h. marksheet_generated_at
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='exam_results' AND COLUMN_NAME='marksheet_generated_at');
SET @s := IF(@c=0, 'ALTER TABLE `exam_results` ADD COLUMN `marksheet_generated_at` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 8. users — check that role column can hold 'student'
--    (If users.role is VARCHAR, this is already fine. If it's an ENUM that
--    lacks 'student', the SELECT below will show you; then you can manually
--    alter it. We do NOT auto-alter here because MODIFY on ENUM can fail
--    badly if the existing data has values not in the new enum.)
-- ---------------------------------------------------------------------------
-- To check: SELECT DISTINCT role FROM users;
-- If 'student' is missing from the ENUM and you want to add it, uncomment:
-- ALTER TABLE `users` MODIFY COLUMN `role` ENUM('super_admin','admin','branch_admin','staff','student') NOT NULL DEFAULT 'student';


-- ============================================================================
-- END OF MIGRATION — verify with:
--   SHOW COLUMNS FROM exams LIKE '%hindi%';
--   SHOW COLUMNS FROM exams LIKE 'is_active';
--   SHOW COLUMNS FROM exams LIKE 'show_result';
--   SHOW TABLES LIKE '%cards';
--   SHOW TABLES LIKE 'branch_results';
-- ============================================================================
