-- ============================================================================
-- GMIND INSTITUTE — Super Admin & Branch Admin Module Migration (Phase 1)
-- ============================================================================
-- Run this SQL in phpMyAdmin against the `advaysin_gmind` database.
-- IDEMPOTENT — safe to run multiple times.
-- ROBUST against "old backup" schemas — uses INFORMATION_SCHEMA checks
-- before every ALTER TABLE so it won't fail if a column already exists.
--
-- This Phase 1 migration:
--   1. Promotes the existing admin user to role='super_admin'
--   2. Creates a sample branch_admin user for GMBR01 (Main Branch)
--   3. Ensures branches.admin_user_id is set for GMBR01
--   4. Adds `type` ENUM column to certificate_templates (certificate/marksheet/affiliation/authorization/renewal/franchise_agreement)
--   5. Creates table `certificate_template_fields` (x, y, font_size, etc. per field)
--   6. Creates table `document_verifications` (QR verification log)
--   7. Creates table `branch_faculty` (faculty details per branch)
--   8. Creates table `branch_social_links` (Facebook, Instagram, etc. per branch)
--   9. Inserts default franchise plans if none exist
-- ============================================================================


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION A — Promote existing admin to super_admin + create GMBR01 branch admin
-- ══════════════════════════════════════════════════════════════════════════════

-- A.1 Promote existing admin@gmind.in to super_admin role
-- (Update in-place — no password change)
UPDATE `users`
SET `role` = 'super_admin'
WHERE `email` IN ('admin@gmind.in', 'admin@gmindinstitute.in', 'gmindinstitues@gmail.com')
  AND `role` IN ('admin', 'super_admin')
LIMIT 1;

-- A.2 Generate a bcrypt hash for 'Branch@123' if PHP is available on server.
-- Since we can't compute bcrypt in pure SQL, we use a pre-computed hash for
-- 'Branch@123'. (If this doesn't work, the user can reset the password via
-- the Super Admin → Branch Admins → Edit → Set New Password feature.)
-- Pre-computed hash: password_hash('Branch@123', PASSWORD_DEFAULT)
SET @branchAdminPassword = '$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy';

-- A.3 Create sample branch admin user for GMBR01 (Main Branch)
-- Only insert if not already exists (idempotent)
INSERT INTO `users` (`name`, `email`, `password`, `role`, `phone`, `branch_id`, `branch_code`, `is_active`, `status`, `created_at`, `updated_at`)
SELECT
    'Gmind Main Branch Admin',
    'branch@gmindinstitute.in',
    @branchAdminPassword,
    'branch_admin',
    '9305640290',
    1,                          -- branch_id = 1 (GMBR01 Main Branch)
    'GMBR01',
    1,
    'active',
    NOW(),
    NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'branch@gmindinstitute.in' AND `role` = 'branch_admin');

-- A.4 Link branches.admin_user_id to the new branch admin (for GMBR01 = id 1)
UPDATE `branches` b
SET b.`admin_user_id` = (
    SELECT u.`id` FROM `users` u
    WHERE u.`email` = 'branch@gmindinstitute.in' AND u.`role` = 'branch_admin'
    LIMIT 1
), b.`status` = 'active', b.`is_verified` = 1, b.`approved_at` = NOW()
WHERE b.`id` = 1 AND b.`branch_code` = 'GMBR01';

-- A.5 Ensure GMBR01 has a referral_code (used by referral system)
UPDATE `branches` SET `referral_code` = 'REF-GMBR01'
WHERE `id` = 1 AND (`referral_code` IS NULL OR `referral_code` = '');


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION B — Certificate Templates enhancements
-- ══════════════════════════════════════════════════════════════════════════════

-- B.1 Add `type` ENUM column to certificate_templates
-- (Distinguishes between certificate / marksheet / affiliation / authorization / renewal / franchise_agreement)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'certificate_templates' AND COLUMN_NAME = 'type');
SET @sql := IF(@col = 0,
    "ALTER TABLE `certificate_templates` ADD COLUMN `type` ENUM('certificate','marksheet','affiliation','authorization','renewal','franchise_agreement','id_card','admit_card') NOT NULL DEFAULT 'certificate' AFTER `name`",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.2 Add `course_id` column (NULL = template applies to all courses)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'certificate_templates' AND COLUMN_NAME = 'course_id');
SET @sql := IF(@col = 0,
    'ALTER TABLE `certificate_templates` ADD COLUMN `course_id` BIGINT UNSIGNED DEFAULT NULL AFTER `type`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.3 Add `template_width` and `template_height` (in points for FPDF)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'certificate_templates' AND COLUMN_NAME = 'template_width');
SET @sql := IF(@col = 0,
    'ALTER TABLE `certificate_templates` ADD COLUMN `template_width` INT NOT NULL DEFAULT 842 AFTER `background_image`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'certificate_templates' AND COLUMN_NAME = 'template_height');
SET @sql := IF(@col = 0,
    'ALTER TABLE `certificate_templates` ADD COLUMN `template_height` INT NOT NULL DEFAULT 595 AFTER `template_width`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.4 Add `description` column (admin notes about the template)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'certificate_templates' AND COLUMN_NAME = 'description');
SET @sql := IF(@col = 0,
    'ALTER TABLE `certificate_templates` ADD COLUMN `description` TEXT DEFAULT NULL AFTER `template_height`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION C — Certificate Template Fields table (NEW)
-- ══════════════════════════════════════════════════════════════════════════════
-- Stores position (x, y) + font details for each field on a template.
-- Used by FPDF when generating certificates/marksheets to overlay text on JPG.

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, duration, grade, percentage, issue_date, qr_code, certificate_no, branch_name',
    `field_label` VARCHAR(100) DEFAULT NULL COMMENT 'Human-readable label for admin UI',
    `x_pos` DECIMAL(6,2) NOT NULL DEFAULT '0.00' COMMENT 'X coordinate in points (from left)',
    `y_pos` DECIMAL(6,2) NOT NULL DEFAULT '0.00' COMMENT 'Y coordinate in points (from top)',
    `font_size` INT NOT NULL DEFAULT '14' COMMENT 'Font size in points',
    `font_family` VARCHAR(50) DEFAULT 'Arial' COMMENT 'Arial, Times, Helvetica, Courier',
    `font_style` VARCHAR(20) DEFAULT '' COMMENT 'B=bold, I=italic, U=underline, or empty',
    `font_color` VARCHAR(20) DEFAULT '#000000' COMMENT 'Hex color code',
    `align` ENUM('L','C','R') NOT NULL DEFAULT 'C' COMMENT 'L=left, C=center, R=right',
    `width` INT DEFAULT NULL COMMENT 'Width in points (for text wrapping)',
    `is_qrcode` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1=this field is a QR code image, not text',
    `qr_size` INT DEFAULT '50' COMMENT 'QR code size in points (if is_qrcode=1)',
    `sort_order` INT NOT NULL DEFAULT '0',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ctf_template` (`template_id`),
    INDEX `idx_ctf_key` (`template_id`, `field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Field positions for certificate/marksheet templates';


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION D — Document Verifications table (NEW)
-- ══════════════════════════════════════════════════════════════════════════════
-- Logs every verification lookup (via QR code scan or document number lookup)
-- on the public verification portal.

CREATE TABLE IF NOT EXISTS `document_verifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `document_type` ENUM('certificate','marksheet','affiliation','authorization','renewal','franchise_agreement','id_card','admit_card') NOT NULL,
    `document_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to the relevant document table (certificates.id etc.)',
    `document_number` VARCHAR(100) DEFAULT NULL COMMENT 'Certificate No / Marksheet No / Document No',
    `verification_hash` VARCHAR(64) DEFAULT NULL COMMENT 'Hash stored in QR code',
    `verified_ip` VARCHAR(45) DEFAULT NULL,
    `verified_user_agent` VARCHAR(500) DEFAULT NULL,
    `is_valid` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1=document found & valid, 0=not found',
    `verified_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_dv_doc` (`document_type`, `document_id`),
    INDEX `idx_dv_hash` (`verification_hash`),
    INDEX `idx_dv_number` (`document_number`),
    INDEX `idx_dv_date` (`verified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Public verification log';


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION E — Branch Faculty table (NEW)
-- ══════════════════════════════════════════════════════════════════════════════
-- Faculty members per branch — displayed on the public branch profile page.

CREATE TABLE IF NOT EXISTS `branch_faculty` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `designation` VARCHAR(150) DEFAULT NULL,
    `qualification` VARCHAR(255) DEFAULT NULL,
    `specialization` VARCHAR(255) DEFAULT NULL,
    `photo` VARCHAR(500) DEFAULT NULL,
    `bio` TEXT DEFAULT NULL,
    `sort_order` INT NOT NULL DEFAULT '0',
    `is_active` TINYINT(1) NOT NULL DEFAULT '1',
    `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_bf_branch` (`branch_id`),
    INDEX `idx_bf_active` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Faculty members per branch';


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION F — Branch Social Links table (NEW)
-- ══════════════════════════════════════════════════════════════════════════════
-- Social media links per branch — displayed on the public branch profile page.

CREATE TABLE IF NOT EXISTS `branch_social_links` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` BIGINT UNSIGNED NOT NULL,
    `platform` ENUM('facebook','twitter','instagram','youtube','linkedin','whatsapp','telegram','website') NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT '1',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_bsl_branch_platform` (`branch_id`, `platform`),
    INDEX `idx_bsl_branch` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Social media links per branch';


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION G — Default Franchise Plans (sample data)
-- ══════════════════════════════════════════════════════════════════════════════

INSERT IGNORE INTO `franchise_plans` (`id`, `name`, `description`, `price`, `duration_months`, `features`, `is_active`, `created_at`, `updated_at`)
VALUES
(1, 'Starter', 'Basic franchise plan for new branches. Includes student management, fee collection, and certificate generation.', 25000.00, 12, 'Student Management;Fee Collection;Certificate Generation;Email Support;Branch Profile Page', 1, NOW(), NOW()),
(2, 'Professional', 'Most popular plan — adds exam management, ID cards, marksheets, and referral income.', 50000.00, 12, 'All Starter features;Exam Management;Admit Cards;ID Cards;Marksheet Generation;Referral System;Priority Support', 1, NOW(), NOW()),
(3, 'Enterprise', 'Full-featured plan with custom branding, advanced analytics, and dedicated account manager.', 100000.00, 12, 'All Professional features;Custom Branding;Advanced Analytics;Dedicated Account Manager;API Access;Multi-Branch Support;Training Sessions', 1, NOW(), NOW());

-- Ensure AUTO_INCREMENT continues after our explicit IDs (in case plans already exist)
SET @maxPlanId := (SELECT COALESCE(MAX(id), 0) FROM `franchise_plans`);
SET @sql := CONCAT('ALTER TABLE `franchise_plans` AUTO_INCREMENT = ', @maxPlanId + 1);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE S;


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION H — Useful indexes on existing tables (only add if missing)
-- ══════════════════════════════════════════════════════════════════════════════

-- Index on users.branch_id (for super admin to fetch branch admin users by branch)
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_branch_id');
SET @sql := IF(@idx = 0,
    'ALTER TABLE `users` ADD INDEX `idx_users_branch_id` (`branch_id`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Index on users.role (for super admin to list all branch_admin users)
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_role_status');
SET @sql := IF(@idx = 0,
    'ALTER TABLE `users` ADD INDEX `idx_users_role_status` (`role`, `status`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Index on branch_applications.status (for super admin to filter pending applications)
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND INDEX_NAME = 'idx_ba_status');
SET @sql := IF(@idx = 0,
    'ALTER TABLE `branch_applications` ADD INDEX `idx_ba_status` (`status`, `created_at`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- DONE — verify with:
--   SELECT id, name, email, role, branch_id, branch_code, status FROM users WHERE role IN ('super_admin','branch_admin');
--   SELECT id, name, branch_code, admin_user_id, status, is_verified FROM branches;
--   DESCRIBE certificate_templates;          -- should show type, course_id, template_width, template_height, description
--   SHOW TABLES LIKE 'certificate_template_fields';
--   SHOW TABLES LIKE 'document_verifications';
--   SHOW TABLES LIKE 'branch_faculty';
--   SHOW TABLES LIKE 'branch_social_links';
--   SELECT * FROM franchise_plans;
-- ══════════════════════════════════════════════════════════════════════════════

-- Test logins after running this migration + uploading the PHP files:
--   Super Admin:  https://gmindinstitute.in/gmind/super-admin/login
--                 Email: admin@gmind.in (your existing admin email)
--                 Password: (your existing admin password — unchanged)
--   Branch Admin: https://gmindinstitute.in/gmind/branch/login
--                 Branch Code / Email: GMBR01  OR  branch@gmindinstitute.in
--                 Password: Branch@123
