influence/ddl/016_fix_terminated_status.sql

-- 1. 기존 제약조건 삭제
SET @constraint_name = (
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'VENDOR_INFLUENCER_PARTNERSHIP'
    AND COLUMN_NAME = 'VENDOR_SEQ'
    AND REFERENCED_TABLE_NAME IS NULL
    AND CONSTRAINT_NAME = 'unique_active_partnership'
    LIMIT 1
);

SET @sql = IF(@constraint_name IS NOT NULL,
    CONCAT('ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP DROP INDEX ', @constraint_name),
    'SELECT "No constraint found to drop"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 데이터 정리
-- 2.1 TERMINATED 상태인 레코드는 모두 비활성화
UPDATE VENDOR_INFLUENCER_PARTNERSHIP
SET IS_ACTIVE = 'N'
WHERE STATUS = 'TERMINATED';

-- 2.2 각 벤더-인플루언서 조합에 대해 가장 최근의 비활성 레코드만 남기고 삭제
DELETE p1 FROM VENDOR_INFLUENCER_PARTNERSHIP p1
INNER JOIN VENDOR_INFLUENCER_PARTNERSHIP p2
WHERE p1.VENDOR_SEQ = p2.VENDOR_SEQ
AND p1.INFLUENCER_SEQ = p2.INFLUENCER_SEQ
AND p1.IS_ACTIVE = 'N'
AND p2.IS_ACTIVE = 'N'
AND p1.SEQ < p2.SEQ;

-- 3. 데이터 상태 확인
SELECT SEQ, VENDOR_SEQ, INFLUENCER_SEQ, STATUS, IS_ACTIVE, REQUEST_TYPE,
       REQUEST_DATE, RESPONSE_DATE, PARTNERSHIP_START_DATE, PARTNERSHIP_END_DATE
FROM VENDOR_INFLUENCER_PARTNERSHIP
WHERE VENDOR_SEQ = 8 AND INFLUENCER_SEQ = 23
ORDER BY SEQ DESC;

-- 4. 새로운 제약조건 추가
ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP
ADD CONSTRAINT unique_active_partnership 
UNIQUE KEY (VENDOR_SEQ, INFLUENCER_SEQ, IS_ACTIVE);