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

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

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

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

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

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

-- 6. APPROVAL_STATUS, APPROVED_DATE 복합 인덱스 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'VENDOR_LIST' AND INDEX_NAME = 'idx_approval_status_date') = 0,
    'ALTER TABLE `VENDOR_LIST` ADD INDEX `idx_approval_status_date` (`APPROVAL_STATUS`, `APPROVED_DATE`)',
    'SELECT "idx_approval_status_date 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 = 'VENDOR_LIST' AND INDEX_NAME = 'idx_region_category_active') = 0,
    'ALTER TABLE `VENDOR_LIST` ADD INDEX `idx_region_category_active` (`REGION`, `CATEGORY`, `IS_ACT`, `STATUS`)',
    'SELECT "idx_region_category_active already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;