influence/ddl/016_fix_data_and_model.sql

-- 1. 잘못된 데이터 정리 (TERMINATED 상태인데 IS_ACTIVE='Y'인 레코드 수정)
UPDATE VENDOR_INFLUENCER_PARTNERSHIP
SET IS_ACTIVE = 'N'
WHERE STATUS = 'TERMINATED';

-- 2. 제약조건 재정의
ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP
DROP INDEX unique_active_partnership;

-- 3. 새로운 복합 제약조건 추가
ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP
ADD CONSTRAINT chk_status_active CHECK (
    (STATUS = 'TERMINATED' AND IS_ACTIVE = 'N') OR
    (STATUS IN ('PENDING', 'APPROVED', 'REJECTED') AND IS_ACTIVE IN ('Y', 'N'))
);

ALTER TABLE VENDOR_INFLUENCER_PARTNERSHIP
ADD CONSTRAINT unique_active_partnership 
UNIQUE KEY (VENDOR_SEQ, INFLUENCER_SEQ, IS_ACTIVE);

-- 4. 데이터 정리 (각 벤더-인플루언서 조합에 대해 하나의 활성 레코드만 유지)
CREATE TEMPORARY TABLE tmp_latest_active AS
SELECT MAX(SEQ) as max_seq
FROM VENDOR_INFLUENCER_PARTNERSHIP
WHERE IS_ACTIVE = 'Y'
GROUP BY VENDOR_SEQ, INFLUENCER_SEQ;

UPDATE VENDOR_INFLUENCER_PARTNERSHIP
SET IS_ACTIVE = 'N'
WHERE IS_ACTIVE = 'Y'
AND SEQ NOT IN (SELECT max_seq FROM tmp_latest_active);

DROP TEMPORARY TABLE IF EXISTS tmp_latest_active;