-- ============================================================================
-- PHASE 5 PATCH — Create Missing Document Tables
-- For: Gmind Institute
-- ============================================================================
-- Creates the 5 tables required by the Certificate/Marksheet generation system:
--   1. certificate_templates       — Super Admin defines JPG templates + field positions
--   2. certificate_template_fields — individual field positions per template
--   3. certificates                — generated documents (one row per cert/marksheet)
--   4. certificate_timeline        — status change history per certificate
--   5. branch_wallet_transactions  — wallet debit/credit log
--
-- COMPATIBILITY: MySQL 5.7+ / MySQL 8.x / MariaDB 10.x (all versions)
-- SAFE TO RE-RUN: Uses CREATE TABLE IF NOT EXISTS.
-- ============================================================================

-- 1. certificate_templates — Super Admin uploads JPG + defines field positions
CREATE TABLE IF NOT EXISTS `certificate_templates` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL COMMENT 'Template name (e.g. "DCA Certificate 2026")',
    `type` ENUM('certificate','marksheet','affiliation','authorization','renewal','franchise_agreement','id_card','admit_card') NOT NULL DEFAULT 'certificate',
    `course_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to courses.id (NULL = applies to all courses)',
    `background_image` VARCHAR(500) NOT NULL COMMENT 'Path to JPG background image',
    `template_width` INT NOT NULL DEFAULT 842 COMMENT 'Width in points (default A4 landscape)',
    `template_height` INT NOT NULL DEFAULT 595 COMMENT 'Height in points',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `description` TEXT 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_cert_templates_type` (`type`),
    INDEX `idx_cert_templates_course` (`course_id`),
    INDEX `idx_cert_templates_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Certificate/Marksheet/ID Card templates';

-- 2. certificate_template_fields — individual field positions per template
CREATE TABLE IF NOT EXISTS `certificate_template_fields` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `template_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to certificate_templates.id',
    `field_key` VARCHAR(50) NOT NULL COMMENT 'e.g. student_name, father_name, roll_no, enrollment_no, course, grade, percentage, division, issue_date, certificate_no, branch_name, dob, admission_date, qr_code, subjects, session_period, from_to_month',
    `label` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Human-readable label',
    `x_pos` INT NOT NULL DEFAULT 0 COMMENT 'X position in points from left',
    `y_pos` INT NOT NULL DEFAULT 0 COMMENT 'Y position in points from top',
    `width` INT NULL DEFAULT NULL COMMENT 'Field width in points (for text wrapping)',
    `font_size` INT NOT NULL DEFAULT 12 COMMENT 'Font size in points',
    `font_family` VARCHAR(50) NOT NULL DEFAULT 'Arial' COMMENT 'Arial, Times, Courier, Helvetica',
    `font_style` VARCHAR(20) NOT NULL DEFAULT 'regular' COMMENT 'regular, bold, italic, bold-italic',
    `font_color` VARCHAR(20) NOT NULL DEFAULT '#000000' COMMENT 'Hex color',
    `align` ENUM('left','center','right') NOT NULL DEFAULT 'left',
    `is_qrcode` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = render as QR code instead of text',
    `qr_size` INT NULL DEFAULT NULL COMMENT 'QR code size in points',
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_cert_fields_template` (`template_id`),
    INDEX `idx_cert_fields_key` (`field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Field positions for certificate templates';

-- 3. certificates — generated documents (one row per cert OR marksheet)
CREATE TABLE IF NOT EXISTS `certificates` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `certificate_no` VARCHAR(60) NOT NULL COMMENT 'Unique document number (e.g. GMIND-2026-00001)',
    `student_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to students.id',
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `course_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to courses.id',
    `student_name` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Snapshot of student name',
    `course_name` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Snapshot of course name',
    `grade` VARCHAR(10) NULL DEFAULT NULL COMMENT 'A+, A, B+, B, C, D, F',
    `percentage` DECIMAL(5,2) NULL DEFAULT NULL,
    `division` VARCHAR(20) NULL DEFAULT NULL COMMENT 'First / Second / Third / Fail',
    `session_from` DATE NULL DEFAULT NULL COMMENT 'Course session start date',
    `session_to` DATE NULL DEFAULT NULL COMMENT 'Course session end date',
    `issue_date` DATE NULL DEFAULT NULL,
    `status` ENUM('pending_approval','generated','printed','delivered','rejected','cancelled') NOT NULL DEFAULT 'pending_approval',
    `template_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to certificate_templates.id',
    `created_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'FK to users.id (branch admin)',
    `qr_hash` VARCHAR(80) NULL DEFAULT NULL COMMENT 'SHA-256 verification hash',
    `verification_url` VARCHAR(500) NULL DEFAULT NULL,
    `pdf_path` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Path to generated PDF file',
    `payment_mode` VARCHAR(20) NULL DEFAULT NULL COMMENT 'online / offline / upi / wallet',
    `transaction_id` VARCHAR(100) NULL DEFAULT NULL COMMENT 'UTR / transaction reference for offline',
    `payment_date` DATE NULL DEFAULT NULL,
    `payment_notes` TEXT NULL DEFAULT NULL,
    `amount_paid` DECIMAL(10,2) NULL DEFAULT NULL,
    `subjects_json` TEXT NULL DEFAULT NULL COMMENT 'JSON of subject-wise marks',
    `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_certificates_no` (`certificate_no`),
    INDEX `idx_certificates_student` (`student_id`),
    INDEX `idx_certificates_branch` (`branch_id`),
    INDEX `idx_certificates_template` (`template_id`),
    INDEX `idx_certificates_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Generated certificates, marksheets, ID cards, admit cards';

-- 4. certificate_timeline — status change history
CREATE TABLE IF NOT EXISTS `certificate_timeline` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `certificate_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to certificates.id',
    `event` VARCHAR(50) NOT NULL COMMENT 'requested / generated / approved / rejected / printed / delivered',
    `description` TEXT NULL DEFAULT NULL,
    `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_cert_timeline_cert` (`certificate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Certificate status change history';

-- 5. branch_wallet_transactions — wallet debit/credit log
CREATE TABLE IF NOT EXISTS `branch_wallet_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` BIGINT UNSIGNED NOT NULL,
    `type` ENUM('credit','debit') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `balance_after` DECIMAL(10,2) NULL DEFAULT NULL,
    `description` VARCHAR(500) NULL DEFAULT NULL,
    `category` VARCHAR(50) NULL DEFAULT NULL COMMENT 'document, referral, topup, refund',
    `reference_type` VARCHAR(50) NULL DEFAULT NULL,
    `reference_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_wallet_branch` (`branch_id`),
    INDEX `idx_wallet_type` (`type`),
    INDEX `idx_wallet_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Branch wallet transaction log';

-- ============================================================================
-- END — verify with:
--   SHOW TABLES LIKE 'certificate%';
--   SHOW TABLES LIKE 'branch_wallet%';
-- ============================================================================
