-- 012_add_rating_column_fixed.sql
-- USER_LIST 테이블에 RATING 컬럼 추가 (MariaDB 호환, Prepared Statement 충돌 방지)

-- 1. RATING 컬럼이 이미 존재하는지 확인 후 추가
SET @sql_column = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_SCHEMA = DATABASE() 
         AND TABLE_NAME = 'USER_LIST' 
         AND COLUMN_NAME = 'RATING') = 0,
        'ALTER TABLE USER_LIST ADD COLUMN RATING DECIMAL(3,1) DEFAULT 0.0 COMMENT "사용자 평점 (0.0~5.0)"',
        'SELECT "RATING 컬럼이 이미 존재합니다" as message'
    )
);

PREPARE stmt_column FROM @sql_column;
EXECUTE stmt_column;
DEALLOCATE PREPARE stmt_column;

-- 2. RATING 컬럼 인덱스 생성 (존재하지 않을 경우에만)
SET @sql_index = (
    SELECT IF(
        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
         WHERE TABLE_SCHEMA = DATABASE() 
         AND TABLE_NAME = 'USER_LIST' 
         AND INDEX_NAME = 'idx_user_rating') = 0,
        'CREATE INDEX idx_user_rating ON USER_LIST (RATING DESC)',
        'SELECT "idx_user_rating 인덱스가 이미 존재합니다" as message'
    )
);

PREPARE stmt_index FROM @sql_index;
EXECUTE stmt_index;
DEALLOCATE PREPARE stmt_index;

-- 3. RATING 기본값 업데이트 (NULL인 경우에만)
UPDATE USER_LIST 
SET RATING = 0.0 
WHERE RATING IS NULL;

-- 4. 성공 메시지
SELECT 'USER_LIST 테이블 RATING 컬럼 추가 완료' as result;

-- 5. 검증: 컬럼 생성 확인
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'USER_LIST' 
AND COLUMN_NAME = 'RATING';