-- ============================================================================
-- PHASE 4 PATCH — Exam Results Approval + Marksheet/Certificate
-- For: Gmind Institute — Branch Admin panel
-- ============================================================================
-- Adds approval workflow columns to exam_results table so branch admins can:
--   1. Override a student's auto-graded score (admin_remarks)
--   2. Approve the result (branch_approved, approved_at, approved_by)
--   3. Track marksheet generation (marksheet_generated, marksheet_generated_at)
--
-- COMPATIBILITY: MySQL 5.7+ / MySQL 8.x / MariaDB 10.x (all versions)
-- SAFE TO RE-RUN: Uses INFORMATION_SCHEMA checks (not "IF NOT EXISTS").
-- ============================================================================

-- 1. branch_approved — flag: 1 if branch admin has approved this result
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;

-- 2. approved_at — when branch admin approved
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;

-- 3. approved_by — user_id of the branch admin who approved
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;

-- 4. admin_remarks — notes from branch admin when overriding score (e.g. "grace marks given for Q5")
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;

-- 5. marksheet_generated — flag: 1 if marksheet has been generated/downloaded
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;

-- 6. marksheet_generated_at — when marksheet was generated
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;

-- ============================================================================
-- END OF PATCH — verify with:
--   SHOW COLUMNS FROM exam_results LIKE 'branch_approved';
--   SHOW COLUMNS FROM exam_results LIKE 'marksheet_generated';
-- ============================================================================
