-- DDL 010: USER_LIST 테이블 인덱스 추가 (안전 버전)
-- 생성일: 2025-07-22
-- 목적: USER_LIST 추가 컬럼에 대한 검색 최적화 인덱스 (중복 방지)

-- 1. INFLUENCER_TYPE 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_influencer_type') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_influencer_type` (`INFLUENCER_TYPE`)',
    'SELECT "idx_influencer_type already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. PRIMARY_CATEGORY 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_primary_category') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_primary_category` (`PRIMARY_CATEGORY`)',
    'SELECT "idx_primary_category already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. VERIFICATION_STATUS 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_verification_status') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_verification_status` (`VERIFICATION_STATUS`)',
    'SELECT "idx_verification_status already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. FOLLOWER_COUNT 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_follower_count') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_follower_count` (`FOLLOWER_COUNT`)',
    'SELECT "idx_follower_count already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. EMAIL 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_email') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_email` (`EMAIL`)',
    'SELECT "idx_email already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. NICK_NAME 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_nick_name') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_nick_name` (`NICK_NAME`)',
    'SELECT "idx_nick_name already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. 복합 인덱스: 타입, 카테고리, 인증상태 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_type_category_verified') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_type_category_verified` (`INFLUENCER_TYPE`, `PRIMARY_CATEGORY`, `VERIFICATION_STATUS`)',
    'SELECT "idx_type_category_verified already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 8. 복합 인덱스: 카테고리, 팔로워수, 인증상태 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_category_follower_verified') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_category_follower_verified` (`PRIMARY_CATEGORY`, `FOLLOWER_COUNT`, `VERIFICATION_STATUS`)',
    'SELECT "idx_category_follower_verified already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. 복합 인덱스: 멤버타입, 활성상태, 상태 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND INDEX_NAME = 'idx_member_type_active_status') = 0,
    'ALTER TABLE `USER_LIST` ADD INDEX `idx_member_type_active_status` (`MEMBER_TYPE`, `IS_ACT`, `STATUS`)',
    'SELECT "idx_member_type_active_status already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;