-- ============================================================================
-- PHASE 5 PATCH — Student Address Columns + Certificate Payment Columns
-- For: Gmind Institute — Branch Cert Gen Module
-- ============================================================================
-- Adds address columns to students table + ensures certificate payment columns
-- exist for the new "Generate Certificate & Marksheet" workflow.
--
-- COMPATIBILITY: MySQL 5.7+ / MySQL 8.x / MariaDB 10.x (all versions)
-- SAFE TO RE-RUN: Uses INFORMATION_SCHEMA checks.
-- ============================================================================

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

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

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

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

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

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', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ─── certificates table — payment + session + PDF columns ────────────────────
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='certificates' AND COLUMN_NAME='payment_mode');
SET @s := IF(@c=0, 'ALTER TABLE `certificates` ADD COLUMN `payment_mode` VARCHAR(20) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='certificates' AND COLUMN_NAME='amount_paid');
SET @s := IF(@c=0, 'ALTER TABLE `certificates` ADD COLUMN `amount_paid` DECIMAL(10,2) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

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

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

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

-- ─── certificate_template_fields — ensure all columns exist ──────────────────
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='certificate_template_fields' AND COLUMN_NAME='label');
SET @s := IF(@c=0, 'ALTER TABLE `certificate_template_fields` ADD COLUMN `label` VARCHAR(100) NULL DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

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

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

-- ============================================================================
-- END OF PATCH
-- ============================================================================
