-- DDL 012: VENDOR_LIST 필수 컬럼 추가
-- 목적: 벤더사 검색 기능에 필요한 컬럼들 추가

-- 현재 테이블 구조 확인 (참고용)
-- DESCRIBE VENDOR_LIST;

-- 1. CATEGORY 컬럼 추가 (이미 존재하면 오류 발생하므로 개별 실행)
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `CATEGORY` varchar(50) DEFAULT NULL COMMENT '사업 카테고리: FASHION_BEAUTY, FOOD_HEALTH, LIFESTYLE, TECH_ELECTRONICS, SPORTS_LEISURE, CULTURE_ENTERTAINMENT';

-- 2. REGION 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `REGION` varchar(50) DEFAULT NULL COMMENT '지역: SEOUL, GYEONGGI, INCHEON, BUSAN, DAEGU, DAEJEON, GWANGJU, ULSAN, OTHER';

-- 3. DESCRIPTION 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `DESCRIPTION` text DEFAULT NULL COMMENT '벤더사 설명';

-- 4. LOGO 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `LOGO` varchar(500) DEFAULT NULL COMMENT '로고 이미지 URL';

-- 5. TAGS 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `TAGS` varchar(500) DEFAULT NULL COMMENT '검색 태그 (콤마 구분)';

-- 6. APPROVAL_STATUS 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `APPROVAL_STATUS` varchar(20) DEFAULT 'PENDING' COMMENT '승인 상태: PENDING, APPROVED, REJECTED';

-- 7. APPROVED_DATE 컬럼 추가
-- ALTER TABLE `VENDOR_LIST` ADD COLUMN `APPROVED_DATE` timestamp NULL DEFAULT NULL COMMENT '승인일시';

-- ========================================
-- 실행 방법 (DBeaver에서 개별 실행)
-- ========================================

-- 1단계: 현재 컬럼 확인
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND TABLE_SCHEMA = DATABASE()
ORDER BY ORDINAL_POSITION;

-- 2단계: 없는 컬럼만 개별 실행
-- 위 주석 처리된 ALTER TABLE 문들을 필요에 따라 개별 실행

-- ========================================
-- 한번에 실행하는 버전 (컬럼이 없는 경우에만)
-- ========================================

-- CATEGORY 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'CATEGORY' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `CATEGORY` varchar(50) DEFAULT NULL COMMENT ''사업 카테고리''', 
    'SELECT ''CATEGORY column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- REGION 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'REGION' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `REGION` varchar(50) DEFAULT NULL COMMENT ''지역''', 
    'SELECT ''REGION column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- DESCRIPTION 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'DESCRIPTION' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `DESCRIPTION` text DEFAULT NULL COMMENT ''벤더사 설명''', 
    'SELECT ''DESCRIPTION column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- TAGS 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'TAGS' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `TAGS` varchar(500) DEFAULT NULL COMMENT ''검색 태그''', 
    'SELECT ''TAGS column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- LOGO 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'LOGO' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `LOGO` varchar(500) DEFAULT NULL COMMENT ''로고 이미지 URL''', 
    'SELECT ''LOGO column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- APPROVAL_STATUS 컬럼 존재 확인 및 추가
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'VENDOR_LIST' 
AND COLUMN_NAME = 'APPROVAL_STATUS' 
AND TABLE_SCHEMA = DATABASE();

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `VENDOR_LIST` ADD COLUMN `APPROVAL_STATUS` varchar(20) DEFAULT ''PENDING'' COMMENT ''승인 상태''', 
    'SELECT ''APPROVAL_STATUS column already exists'' as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;