-- ============================================================================
-- PHASE 5 PATCH — Certificate Payment Workflow Columns
-- For: Gmind Institute — Branch Admin panel
-- ============================================================================
-- Adds payment + session + subject columns to the `certificates` table so
-- that:
--   1. Branch admin can pay offline (UPI/Bank) and submit a transaction ID
--   2. Super Admin verifies the transaction ID and approves the document
--   3. Once approved, the PDF is generated and the Branch can download it
--   4. Subject-wise marks + session period (from-to-month) + issue date are
--      stored so they can be re-used when re-generating the PDF
--
-- 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. payment_mode — 'online' / 'offline' / 'upi' / 'wallet'
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;

-- 2. transaction_id — UTR / transaction reference number for offline payment
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;

-- 3. payment_date — when the offline payment was made
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;

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

-- 5. amount_paid — total amount paid (₹200 for combined cert+marksheet)
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;

-- 6. division — First / Second / Third / Fail (calculated from percentage)
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;

-- 7. session_from — course session start date (admission date)
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;

-- 8. session_to — course session end date (completion date)
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;

-- 9. pdf_path — file path of the generated PDF (so we can serve it directly)
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;

-- 10. subjects_json — JSON of subject-wise marks (subjects, max_marks, obtained_marks)
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;

-- ============================================================================
-- END OF PATCH — verify with:
--   SHOW COLUMNS FROM certificates LIKE 'payment_%';
--   SHOW COLUMNS FROM certificates LIKE 'transaction_id';
--   SHOW COLUMNS FROM certificates LIKE 'session_%';
--   SHOW COLUMNS FROM certificates LIKE 'subjects_json';
-- ============================================================================

-- NOTE: The BranchDocumentController also has an auto-migration safety net
-- (ensurePaymentColumns method) that will add these columns at runtime if
-- they're missing. So you don't strictly need to run this SQL — but running
-- it is recommended for cleaner schema management.
