-- ============================================================================
-- GMIND INSTITUTE — Phase 1 Update: Franchise Plans + Payment Mode
-- ============================================================================
-- Updates franchise_plans with Free/Silver/Gold plans (with original_price
-- for showing strikethrough), adds payment_mode/payment_status to
-- branch_applications, and adds is_trusted flag handling for Gold branches.
-- ============================================================================

-- ══════════════════════════════════════════════════════════════════════════════
-- 1. Add original_price column to franchise_plans (for strikethrough display)
-- ══════════════════════════════════════════════════════════════════════════════
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'franchise_plans' AND COLUMN_NAME = 'original_price');
SET @sql := IF(@col = 0,
    'ALTER TABLE `franchise_plans` ADD COLUMN `original_price` DECIMAL(12,2) DEFAULT NULL AFTER `price`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add is_trusted flag (Gold plan = trusted)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'franchise_plans' AND COLUMN_NAME = 'is_trusted');
SET @sql := IF(@col = 0,
    'ALTER TABLE `franchise_plans` ADD COLUMN `is_trusted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add plan_code for quick identification
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'franchise_plans' AND COLUMN_NAME = 'plan_code');
SET @sql := IF(@col = 0,
    'ALTER TABLE `franchise_plans` ADD COLUMN `plan_code` VARCHAR(20) DEFAULT NULL AFTER `name`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- 2. Replace existing franchise plans with Free / Silver / Gold
-- ══════════════════════════════════════════════════════════════════════════════

-- Delete old plans (ids 1,2,3 from Phase 1 migration)
DELETE FROM `franchise_plans` WHERE `id` IN (1, 2, 3);

-- Insert new plans
INSERT INTO `franchise_plans` (`id`, `plan_code`, `name`, `description`, `price`, `original_price`, `duration_months`, `features`, `is_active`, `is_trusted`, `created_at`, `updated_at`) VALUES
(1, 'FREE', 'Free', 'Free plan — basic features. Auto-approved instantly. Default password sent via email.', 0.00, NULL, 12,
 'Student Management (up to 50);Fee Collection;Branch Profile Page;Email Support',
 1, 0, NOW(), NOW()),

(2, 'SILVER', 'Silver', 'Silver plan — most popular. Adds exam management, certificates, and more.', 1500.00, 7000.00, 12,
 'All Free features (unlimited students);Exam Management;Certificate Generation;Marksheet Generation;ID Cards;Admit Cards;Priority Email Support;Branch Ranking Eligibility',
 1, 0, NOW(), NOW()),

(3, 'GOLD', 'Gold', 'Gold plan — premium. Trusted badge, all features unlocked, top placement in listings.', 2999.00, 10000.00, 12,
 'All Silver features;TRUSTED Badge on all listings;Top placement in branch directory;Custom Branding;Advanced Analytics;Referral System (10% commission);Dedicated Phone Support;WhatsApp Notifications;Free Template Customization',
 1, 1, NOW(), NOW());

-- Reset AUTO_INCREMENT
SET @maxId := (SELECT COALESCE(MAX(id), 0) FROM `franchise_plans`);
SET @sql := CONCAT('ALTER TABLE `franchise_plans` AUTO_INCREMENT = ', @maxId + 1);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- 3. Add payment fields to branch_applications
-- ══════════════════════════════════════════════════════════════════════════════

-- payment_mode: cash, upi, online, offline
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND COLUMN_NAME = 'payment_mode');
SET @sql := IF(@col = 0,
    "ALTER TABLE `branch_applications` ADD COLUMN `payment_mode` ENUM('offline','online','upi','cash','free') DEFAULT 'free' AFTER `investment_range`",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- payment_status: pending, paid, verified, failed
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND COLUMN_NAME = 'payment_status');
SET @sql := IF(@col = 0,
    "ALTER TABLE `branch_applications` ADD COLUMN `payment_status` ENUM('pending','paid','verified','failed','free') DEFAULT 'free' AFTER `payment_mode`",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- payment_transaction_ref (for UPI/online payment reference)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND COLUMN_NAME = 'payment_transaction_ref');
SET @sql := IF(@col = 0,
    'ALTER TABLE `branch_applications` ADD COLUMN `payment_transaction_ref` VARCHAR(200) DEFAULT NULL AFTER `payment_status`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- payment_screenshot (for offline/UPI payment proof upload)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND COLUMN_NAME = 'payment_screenshot');
SET @sql := IF(@col = 0,
    'ALTER TABLE `branch_applications` ADD COLUMN `payment_screenshot` VARCHAR(500) DEFAULT NULL AFTER `payment_transaction_ref`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add center_name, center_address, owner_name, district fields if missing
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_applications' AND COLUMN_NAME = 'center_name');
SET @sql := IF(@col = 0,
    'ALTER TABLE `branch_applications` ADD COLUMN `center_name` VARCHAR(255) DEFAULT NULL AFTER `name`',
    '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 = 'branch_applications' AND COLUMN_NAME = 'district');
SET @sql := IF(@col = 0,
    'ALTER TABLE `branch_applications` ADD COLUMN `district` VARCHAR(100) DEFAULT NULL AFTER `city`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Update column comments to clarify that 'city' now holds the state, and 'district' holds the district
-- (We reuse existing city/state columns: state = Indian state, city = district, district = redundant but kept for safety)


-- ══════════════════════════════════════════════════════════════════════════════
-- 4. Add is_trusted to branches (so we can flag Gold plan branches)
-- ══════════════════════════════════════════════════════════════════════════════
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'is_trusted');
SET @sql := IF(@col = 0,
    'ALTER TABLE `branches` ADD COLUMN `is_trusted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_verified`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Set GMBR01 (id=1) as trusted since it's the main branch
UPDATE `branches` SET `is_trusted` = 1 WHERE `id` = 1 AND `branch_code` = 'GMBR01';

-- Add plan_id to branches if not exists (to know which plan the branch is on)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'plan_id');
-- plan_id already exists in the schema (from the dump). Skip if present.
-- If it doesn't exist, add it.
SET @sql := IF(@col = 0,
    'ALTER TABLE `branches` ADD COLUMN `plan_id` BIGINT UNSIGNED DEFAULT NULL AFTER `branch_code_display`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Set GMBR01 to Gold plan
UPDATE `branches` SET `plan_id` = 3 WHERE `id` = 1 AND `branch_code` = 'GMBR01';


-- ══════════════════════════════════════════════════════════════════════════════
-- 5. Add system settings for payment UPI ID (for displaying to applicants)
-- ══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
('payment_upi_id', 'gmind@upi'),
('payment_upi_name', 'Gmind Institute'),
('payment_qr_code', ''),
('payment_bank_name', 'State Bank of India'),
('payment_account_number', '000000000000'),
('payment_ifsc', 'SBIN0000000'),
('payment_phone', '9305640290')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

INSERT IGNORE INTO `settings` (`key_name`, `value`) VALUES
('payment_upi_id', 'gmind@upi'),
('payment_upi_name', 'Gmind Institute'),
('payment_bank_name', 'State Bank of India'),
('payment_account_number', '000000000000'),
('payment_ifsc', 'SBIN0000000'),
('payment_phone', '9305640290');


-- ══════════════════════════════════════════════════════════════════════════════
-- DONE — verify with:
--   SELECT * FROM franchise_plans;
--   DESCRIBE branch_applications;
--   SELECT id, branch_code, plan_id, is_trusted FROM branches;
-- ══════════════════════════════════════════════════════════════════════════════
