influence/ddl/006_create_partnership_history_table.sql

-- DDL 006: 파트너십 이력 테이블 생성
-- 생성일: 2025-07-22
-- 목적: 벤더사-인플루언서 파트너십 활동 이력 추적

CREATE TABLE `PARTNERSHIP_HISTORY` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '기본키',
  `MAPPING_SEQ` int(11) NOT NULL COMMENT '매핑 테이블 SEQ (VENDOR_INFLUENCER_MAPPING.SEQ 참조)',
  `ACTION_TYPE` varchar(50) NOT NULL COMMENT '액션 타입: REQUEST_SENT, REQUEST_APPROVED, REQUEST_REJECTED, REQUEST_CANCELLED, PARTNERSHIP_STARTED, PARTNERSHIP_ENDED, CONTRACT_UPDATED',
  `ACTION_BY` int(11) NOT NULL COMMENT '액션 수행자 SEQ (USER_LIST.SEQ 참조)',
  `ACTION_DATE` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '액션 수행일시',
  `DESCRIPTION` text DEFAULT NULL COMMENT '액션 설명',
  `OLD_VALUE` text DEFAULT NULL COMMENT '변경 전 값 (JSON 형태)',
  `NEW_VALUE` text DEFAULT NULL COMMENT '변경 후 값 (JSON 형태)',
  `IP_ADDRESS` varchar(45) DEFAULT NULL COMMENT '수행자 IP 주소',
  `USER_AGENT` varchar(500) DEFAULT NULL COMMENT '사용자 에이전트',
  `ADD_INFO1` varchar(500) DEFAULT NULL COMMENT '추가정보1',
  `ADD_INFO2` varchar(500) DEFAULT NULL COMMENT '추가정보2',
  `ADD_INFO3` varchar(500) DEFAULT NULL COMMENT '추가정보3',
  `CREATED_AT` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '생성일시',
  PRIMARY KEY (`SEQ`),
  INDEX `idx_mapping_seq` (`MAPPING_SEQ`),
  INDEX `idx_action_type` (`ACTION_TYPE`),
  INDEX `idx_action_by` (`ACTION_BY`),
  INDEX `idx_action_date` (`ACTION_DATE`),
  INDEX `idx_mapping_action_date` (`MAPPING_SEQ`, `ACTION_DATE`),
  CONSTRAINT `fk_partnership_history_mapping` 
    FOREIGN KEY (`MAPPING_SEQ`) REFERENCES `VENDOR_INFLUENCER_MAPPING` (`SEQ`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_partnership_history_user` 
    FOREIGN KEY (`ACTION_BY`) REFERENCES `USER_LIST` (`SEQ`) 
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='파트너십 이력 테이블';