-- Fix Schema Compatibility Issues for Grazuri Migration
-- This script adds missing columns to make the database compatible with Django models

USE acbptxvs_branch_system;

-- Fix 1: Add missing columns to branches table (check first to avoid errors)
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'mpesa_shortcode');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE branches ADD COLUMN mpesa_shortcode VARCHAR(20) NULL AFTER sub_account', 'SELECT "mpesa_shortcode already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'mpesa_passkey');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE branches ADD COLUMN mpesa_passkey VARCHAR(255) NULL AFTER mpesa_shortcode', 'SELECT "mpesa_passkey already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'is_active');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE branches ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER mpesa_passkey', 'SELECT "is_active already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'created_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE branches ADD COLUMN created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) AFTER is_active', 'SELECT "created_at already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE branches ADD COLUMN updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) AFTER created_at', 'SELECT "updated_at already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 2: Add branch_id foreign key to users table
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'branch_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE users ADD COLUMN branch_id INT NULL AFTER portfolio_manager_id', 'SELECT "branch_id already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index for branch_id
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_branch');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE users ADD INDEX idx_users_branch (branch_id)', 'SELECT "idx_users_branch already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint
SET @fk_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'users' AND CONSTRAINT_NAME = 'fk_users_branch');
SET @sql = IF(@fk_exists = 0, 'ALTER TABLE users ADD CONSTRAINT fk_users_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL', 'SELECT "fk_users_branch already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fix 3: Ensure loan_products has all Grazuri columns (should already exist)
-- These were added in previous migrations, so just verify

-- Fix 4: Add indexes for better performance
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND INDEX_NAME = 'idx_branches_code');
SET @sql = IF(@index_exists = 0, 'CREATE INDEX idx_branches_code ON branches(code)', 'SELECT "idx_branches_code already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' AND TABLE_NAME = 'branches' AND INDEX_NAME = 'idx_branches_is_active');
SET @sql = IF(@index_exists = 0, 'CREATE INDEX idx_branches_is_active ON branches(is_active)', 'SELECT "idx_branches_is_active already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verify changes
SELECT 'Schema compatibility fixes applied successfully' AS status;

-- Show updated branches table structure
DESCRIBE branches;

-- Show branch_id in users table
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'acbptxvs_branch_system' 
  AND TABLE_NAME = 'users' 
  AND COLUMN_NAME = 'branch_id';