-- DDL 007: 알림 테이블 생성
-- 생성일: 2025-07-22
-- 목적: 벤더사-인플루언서 간 알림 관리

CREATE TABLE `NOTIFICATION` (
  `SEQ` int(11) NOT NULL AUTO_INCREMENT COMMENT '기본키',
  `RECIPIENT_SEQ` int(11) NOT NULL COMMENT '수신자 SEQ (USER_LIST.SEQ 참조)',
  `SENDER_SEQ` int(11) DEFAULT NULL COMMENT '발신자 SEQ (USER_LIST.SEQ 참조)',
  `TYPE` varchar(50) NOT NULL COMMENT '알림 타입: PARTNERSHIP_REQUEST, PARTNERSHIP_APPROVED, PARTNERSHIP_REJECTED, PARTNERSHIP_CANCELLED, PARTNERSHIP_EXPIRED, SYSTEM_NOTICE',
  `TITLE` varchar(200) NOT NULL COMMENT '알림 제목',
  `MESSAGE` text NOT NULL COMMENT '알림 메시지',
  `RELATED_TYPE` varchar(50) DEFAULT NULL COMMENT '관련 테이블 타입: VENDOR_INFLUENCER_MAPPING, PARTNERSHIP_HISTORY',
  `RELATED_SEQ` int(11) DEFAULT NULL COMMENT '관련 테이블 SEQ',
  `IS_READ` varchar(1) NOT NULL DEFAULT 'N' COMMENT '읽음 상태: Y(읽음), N(안읽음)',
  `READ_DATE` timestamp NULL DEFAULT NULL COMMENT '읽은 일시',
  `PRIORITY` varchar(20) DEFAULT 'NORMAL' COMMENT '우선순위: HIGH, NORMAL, LOW',
  `PUSH_SENT` varchar(1) DEFAULT 'N' COMMENT '푸시 발송 여부: Y(발송), N(미발송)',
  `EMAIL_SENT` varchar(1) DEFAULT 'N' COMMENT '이메일 발송 여부: Y(발송), N(미발송)',
  `SMS_SENT` varchar(1) DEFAULT 'N' COMMENT 'SMS 발송 여부: Y(발송), N(미발송)',
  `EXPIRES_AT` timestamp NULL DEFAULT NULL COMMENT '만료일시',
  `CREATED_AT` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '생성일시',
  `UPDATED_AT` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() 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',
  PRIMARY KEY (`SEQ`),
  INDEX `idx_recipient_seq` (`RECIPIENT_SEQ`),
  INDEX `idx_sender_seq` (`SENDER_SEQ`),
  INDEX `idx_type` (`TYPE`),
  INDEX `idx_is_read` (`IS_READ`),
  INDEX `idx_priority` (`PRIORITY`),
  INDEX `idx_created_at` (`CREATED_AT`),
  INDEX `idx_recipient_read_created` (`RECIPIENT_SEQ`, `IS_READ`, `CREATED_AT`),
  INDEX `idx_related_type_seq` (`RELATED_TYPE`, `RELATED_SEQ`),
  CONSTRAINT `fk_notification_recipient` 
    FOREIGN KEY (`RECIPIENT_SEQ`) REFERENCES `USER_LIST` (`SEQ`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_notification_sender` 
    FOREIGN KEY (`SENDER_SEQ`) REFERENCES `USER_LIST` (`SEQ`) 
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='알림 테이블';