influence/ddl/005_add_user_list_additional_columns_safe.sql

-- DDL 005: USER_LIST 테이블에 추가 컬럼 추가 (안전 버전)
-- 생성일: 2025-07-22
-- 목적: 인플루언서 분류 및 관리를 위한 추가 정보 컬럼 (중복 방지)

-- 1. 인플루언서 타입 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'INFLUENCER_TYPE') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `INFLUENCER_TYPE` varchar(50) DEFAULT NULL COMMENT ''인플루언서 타입: MICRO, MACRO, MEGA, CELEBRITY''',
    'SELECT "INFLUENCER_TYPE column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 주요 카테고리 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'PRIMARY_CATEGORY') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `PRIMARY_CATEGORY` varchar(50) DEFAULT NULL COMMENT ''주요 활동 카테고리: FASHION_BEAUTY, FOOD_HEALTH, LIFESTYLE, TECH_ELECTRONICS, SPORTS_LEISURE, CULTURE_ENTERTAINMENT''',
    'SELECT "PRIMARY_CATEGORY column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 팔로워 수 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'FOLLOWER_COUNT') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `FOLLOWER_COUNT` int(11) DEFAULT 0 COMMENT ''총 팔로워 수''',
    'SELECT "FOLLOWER_COUNT column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. 평균 조회수 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'AVG_VIEWS') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `AVG_VIEWS` int(11) DEFAULT 0 COMMENT ''평균 조회수''',
    'SELECT "AVG_VIEWS column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. 프로필 이미지 URL 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'PROFILE_IMAGE') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `PROFILE_IMAGE` varchar(500) DEFAULT NULL COMMENT ''프로필 이미지 URL''',
    'SELECT "PROFILE_IMAGE column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. 소개 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'BIO') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `BIO` text DEFAULT NULL COMMENT ''자기소개''',
    'SELECT "BIO column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 7. 인스타그램 링크 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'INSTAGRAM_URL') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `INSTAGRAM_URL` varchar(200) DEFAULT NULL COMMENT ''인스타그램 링크''',
    'SELECT "INSTAGRAM_URL column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 8. 유튜브 링크 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'YOUTUBE_URL') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `YOUTUBE_URL` varchar(200) DEFAULT NULL COMMENT ''유튜브 링크''',
    'SELECT "YOUTUBE_URL column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 9. 틱톡 링크 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'TIKTOK_URL') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `TIKTOK_URL` varchar(200) DEFAULT NULL COMMENT ''틱톡 링크''',
    'SELECT "TIKTOK_URL column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 10. 블로그 링크 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'BLOG_URL') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `BLOG_URL` varchar(200) DEFAULT NULL COMMENT ''블로그 링크''',
    'SELECT "BLOG_URL column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 11. 선호 지역 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'PREFERRED_REGION') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `PREFERRED_REGION` varchar(100) DEFAULT NULL COMMENT ''선호 활동 지역''',
    'SELECT "PREFERRED_REGION column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 12. 최소 수수료율 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'MIN_COMMISSION_RATE') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `MIN_COMMISSION_RATE` decimal(5,2) DEFAULT NULL COMMENT ''최소 수수료율 (%)''',
    'SELECT "MIN_COMMISSION_RATE column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 13. 인증 상태 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'VERIFICATION_STATUS') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `VERIFICATION_STATUS` varchar(20) DEFAULT ''UNVERIFIED'' COMMENT ''인증 상태: UNVERIFIED, PENDING, VERIFIED''',
    'SELECT "VERIFICATION_STATUS column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 14. 인증일 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'VERIFIED_DATE') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `VERIFIED_DATE` timestamp NULL DEFAULT NULL COMMENT ''인증일시''',
    'SELECT "VERIFIED_DATE column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 15. 최종 로그인 일시 컬럼 추가 (존재하지 않는 경우에만)
SET @sql = IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'USER_LIST' AND COLUMN_NAME = 'LAST_LOGIN_DATE') = 0,
    'ALTER TABLE `USER_LIST` ADD COLUMN `LAST_LOGIN_DATE` timestamp NULL DEFAULT NULL COMMENT ''최종 로그인 일시''',
    'SELECT "LAST_LOGIN_DATE column already exists" as info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;