influence/ddl/007_create_status_history_table.sql

-- DDL 007: STATUS 히스토리 테이블 분리 방안
-- 생성일: 2024-12-20
-- 목적: 메인 테이블과 히스토리 테이블을 분리하여 UNIQUE 제약조건 문제 해결

-- 1. 파트너십 상태 히스토리 테이블 생성
CREATE TABLE `VENDOR_INFLUENCER_STATUS_HISTORY` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '기본키',
  `MAPPING_SEQ` int(11) NOT NULL COMMENT '매핑 테이블 SEQ 참조',
  `STATUS` varchar(20) NOT NULL COMMENT '상태: PENDING, APPROVED, REJECTED, TERMINATED',
  `PREVIOUS_STATUS` varchar(20) DEFAULT NULL COMMENT '이전 상태',
  `STATUS_MESSAGE` text DEFAULT NULL COMMENT '상태 변경 메시지',
  `CHANGED_BY` int(11) NOT NULL COMMENT '상태 변경자 SEQ',
  `CHANGED_DATE` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '상태 변경일시',
  `IS_CURRENT` varchar(1) NOT NULL DEFAULT 'Y' COMMENT '현재 상태 여부: Y(현재), N(이전)',
  `REG_DATE` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '등록일시',
  PRIMARY KEY (`SEQ`),
  INDEX `idx_mapping_seq` (`MAPPING_SEQ`),
  INDEX `idx_status` (`STATUS`),
  INDEX `idx_is_current` (`IS_CURRENT`),
  INDEX `idx_changed_date` (`CHANGED_DATE`),
  UNIQUE INDEX `unique_current_mapping` (`MAPPING_SEQ`, `IS_CURRENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci 
COMMENT='벤더사-인플루언서 상태 히스토리 테이블';

-- 2. 메인 테이블 구조 단순화
-- STATUS 컬럼을 제거하고 현재 상태는 히스토리 테이블에서 조회
ALTER TABLE `VENDOR_INFLUENCER_MAPPING` 
DROP COLUMN `STATUS`;

-- 3. 외래키 제약조건 추가
ALTER TABLE `VENDOR_INFLUENCER_STATUS_HISTORY`
ADD CONSTRAINT `fk_status_history_mapping`
FOREIGN KEY (`MAPPING_SEQ`) REFERENCES `VENDOR_INFLUENCER_MAPPING`(`SEQ`)
ON DELETE CASCADE ON UPDATE CASCADE;

-- 4. 기존 데이터 마이그레이션 (백업 후 실행)
-- 주의: 실제 운영 환경에서는 백업 후 실행 필요
INSERT INTO `VENDOR_INFLUENCER_STATUS_HISTORY` 
(`MAPPING_SEQ`, `STATUS`, `STATUS_MESSAGE`, `CHANGED_BY`, `CHANGED_DATE`, `IS_CURRENT`)
SELECT 
  `SEQ` as `MAPPING_SEQ`,
  'PENDING' as `STATUS`, -- 기본값으로 설정
  `REQUEST_MESSAGE` as `STATUS_MESSAGE`,
  `REQUESTED_BY` as `CHANGED_BY`,
  `REG_DATE` as `CHANGED_DATE`,
  'Y' as `IS_CURRENT`
FROM `VENDOR_INFLUENCER_MAPPING`
WHERE `IS_ACT` = 'Y';

-- 5. 현재 상태 조회를 위한 VIEW 생성
CREATE VIEW `V_VENDOR_INFLUENCER_CURRENT_STATUS` AS
SELECT 
  m.`SEQ`,
  m.`VENDOR_SEQ`,
  m.`INFLUENCER_SEQ`,
  m.`REQUEST_TYPE`,
  h.`STATUS` as `CURRENT_STATUS`,
  h.`STATUS_MESSAGE` as `CURRENT_STATUS_MESSAGE`,
  m.`REQUEST_MESSAGE`,
  m.`RESPONSE_MESSAGE`,
  m.`REQUESTED_BY`,
  m.`APPROVED_BY`,
  m.`REQUEST_DATE`,
  m.`RESPONSE_DATE`,
  m.`EXPIRED_DATE`,
  m.`PARTNERSHIP_START_DATE`,
  m.`PARTNERSHIP_END_DATE`,
  m.`COMMISSION_RATE`,
  m.`SPECIAL_CONDITIONS`,
  m.`IS_ACT`,
  m.`REG_DATE`,
  m.`MOD_DATE`,
  m.`ADD_INFO1`,
  m.`ADD_INFO2`,
  m.`ADD_INFO3`
FROM `VENDOR_INFLUENCER_MAPPING` m
JOIN `VENDOR_INFLUENCER_STATUS_HISTORY` h 
  ON m.`SEQ` = h.`MAPPING_SEQ` 
  AND h.`IS_CURRENT` = 'Y'
WHERE m.`IS_ACT` = 'Y';

-- 6. 상태 변경을 위한 저장 프로시저
DELIMITER //
CREATE PROCEDURE `SP_CHANGE_PARTNERSHIP_STATUS`(
  IN p_mapping_seq INT,
  IN p_new_status VARCHAR(20),
  IN p_status_message TEXT,
  IN p_changed_by INT
)
BEGIN
  DECLARE v_current_status VARCHAR(20);
  
  -- 트랜잭션 시작
  START TRANSACTION;
  
  -- 현재 상태 조회
  SELECT `STATUS` INTO v_current_status
  FROM `VENDOR_INFLUENCER_STATUS_HISTORY`
  WHERE `MAPPING_SEQ` = p_mapping_seq AND `IS_CURRENT` = 'Y';
  
  -- 기존 현재 상태를 이전 상태로 변경
  UPDATE `VENDOR_INFLUENCER_STATUS_HISTORY`
  SET `IS_CURRENT` = 'N'
  WHERE `MAPPING_SEQ` = p_mapping_seq AND `IS_CURRENT` = 'Y';
  
  -- 새로운 상태 히스토리 추가
  INSERT INTO `VENDOR_INFLUENCER_STATUS_HISTORY`
  (`MAPPING_SEQ`, `STATUS`, `PREVIOUS_STATUS`, `STATUS_MESSAGE`, `CHANGED_BY`, `IS_CURRENT`)
  VALUES
  (p_mapping_seq, p_new_status, v_current_status, p_status_message, p_changed_by, 'Y');
  
  -- 메인 테이블의 MOD_DATE 업데이트
  UPDATE `VENDOR_INFLUENCER_MAPPING`
  SET `MOD_DATE` = NOW()
  WHERE `SEQ` = p_mapping_seq;
  
  COMMIT;
END //
DELIMITER ;