-- =====================================================
-- OLD USERS PERFORMANCE OPTIMIZATION
-- Execute this in phpMyAdmin SQL tab
-- Safe to run - only adds indexes, no data changes
-- =====================================================

-- 1. Add index on profile.reg_id for faster UID lookups
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'profile' AND INDEX_NAME = 'idx_reg_id');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `profile` ADD INDEX `idx_reg_id` (`reg_id`)', 'SELECT "✓ idx_reg_id already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. Add composite index on portal_users for migration checks
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'portal_users' AND INDEX_NAME = 'idx_old_uid_migration');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `portal_users` ADD INDEX `idx_old_uid_migration` (`old_uid`, `is_old_user`)', 'SELECT "✓ idx_old_uid_migration already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. Add composite index on profile for common filter combinations
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'profile' AND INDEX_NAME = 'idx_profile_filters');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `profile` ADD INDEX `idx_profile_filters` (`profile`, `state`(50), `created_at`)', 'SELECT "✓ idx_profile_filters already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4. Add index on profile.created_date as fallback
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'profile' AND INDEX_NAME = 'idx_created_date');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `profile` ADD INDEX `idx_created_date` (`created_date`)', 'SELECT "✓ idx_created_date already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5. Add index on profile email for search functionality
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'profile' AND INDEX_NAME = 'idx_email');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `profile` ADD INDEX `idx_email` (`email`(100))', 'SELECT "✓ idx_email already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6. Add index on profile contact for search functionality
SET @index_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'profile' AND INDEX_NAME = 'idx_contact');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `profile` ADD INDEX `idx_contact` (`contact`(20))', 'SELECT "✓ idx_contact already exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================
-- VERIFICATION QUERIES
-- Run these to confirm indexes were created
-- =====================================================

-- Check profile table indexes
SHOW INDEX FROM `profile`;

-- Check portal_users table indexes
SHOW INDEX FROM `portal_users`;

-- =====================================================
-- PERFORMANCE TEST QUERY
-- Run this before and after to see improvement
-- =====================================================

-- Before indexes: May take 5-30 seconds
-- After indexes: Should take < 1 second
SELECT COUNT(*) as total_old_users
FROM `profile` p
WHERE p.reg_id NOT IN (
    SELECT old_uid 
    FROM portal_users 
    WHERE is_old_user = 1 
    AND old_uid IS NOT NULL
);

-- =====================================================
-- CLEANUP (Optional - only if you need to remove indexes)
-- =====================================================

-- Uncomment these lines ONLY if you need to remove indexes:
-- ALTER TABLE `profile` DROP INDEX `idx_reg_id`;
-- ALTER TABLE `profile` DROP INDEX `idx_profile_filters`;
-- ALTER TABLE `profile` DROP INDEX `idx_created_date`;
-- ALTER TABLE `profile` DROP INDEX `idx_email`;
-- ALTER TABLE `profile` DROP INDEX `idx_contact`;
-- ALTER TABLE `portal_users` DROP INDEX `idx_old_uid_migration`;
