-- 시퀀스 생성
CREATE SEQUENCE USER_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE DIARY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE DIARY_HISTORY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE PHOTO_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE EMOTION_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE DIARY_EMOTION_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SENTENCE_EMOTION_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE DIARY_ACTIVITY_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE MUSIC_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE BOOKMARK_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE FEEDBACK_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE USER_ACTIVITY_RECORD_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ACTIVITY_EFFECT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE CHATBOT_TYPE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE CHAT_SESSION_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE CHAT_MESSAGE_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE USER_EMOTION_SCORE_SEQ START WITH 1 INCREMENT BY 1;

DB에 시퀀스 추가 안해도 됨

JPA로 자동 생성됨

—————-

-- User 테이블 생성
CREATE TABLE USERS (
    USER_ID NUMBER(19) PRIMARY KEY,
    EMAIL VARCHAR2(100) NOT NULL UNIQUE,
    PASSWORD VARCHAR2(100),
    NICKNAME VARCHAR2(50) NOT NULL,
    TERMS_AGREE NUMBER(1) DEFAULT 0 NOT NULL,
    PRIVACY_AGREE NUMBER(1) DEFAULT 0 NOT NULL,
    MARKETING_AGREE NUMBER(1) DEFAULT 0,
    ENABLED NUMBER(1) DEFAULT 1 NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    PROVIDER VARCHAR2(20),
    PROVIDER_ID VARCHAR2(255),
    ROLE VARCHAR2(20) DEFAULT 'User' NOT NULL,
    CONSTRAINT CHK_TERMS_AGREE CHECK (TERMS_AGREE IN (0, 1)),
    CONSTRAINT CHK_PRIVACY_AGREE CHECK (PRIVACY_AGREE IN (0, 1)),
    CONSTRAINT CHK_MARKETING_AGREE CHECK (MARKETING_AGREE IN (0, 1)),
    CONSTRAINT CHK_ENABLED CHECK (ENABLED IN (0, 1))
);

-- 2. 일기 관리 테이블
CREATE TABLE DIARIES (
    DIARY_ID NUMBER(19) PRIMARY KEY,
    USER_ID NUMBER(19) NOT NULL,
    TITLE VARCHAR2(255) NOT NULL,
    CONTENT CLOB NOT NULL,
    DIARY_DATE DATE DEFAULT SYSDATE NOT NULL,  -- 일기 날짜
    WEATHER VARCHAR2(30),  -- 날씨 정보
    IS_BOOKMARKED NUMBER(1) DEFAULT 0,  -- 북마크 여부
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_DIARIES_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE,
    CONSTRAINT CHK_IS_BOOKMARKED CHECK (IS_BOOKMARKED IN (0, 1))
);

-- 일기에 첨부된 사진 테이블
CREATE TABLE DIARY_PHOTOS (
    PHOTO_ID NUMBER(19) PRIMARY KEY,
    DIARY_ID NUMBER(19) NOT NULL,
    PHOTO_URL VARCHAR2(255) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_DIARY_PHOTOS_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE
);

-- 3. 감정 분석 관련 테이블
-- 감정 종류 테이블
CREATE TABLE EMOTIONS (3
    EMOTION_ID NUMBER(10) PRIMARY KEY,
    EMOTION_NAME VARCHAR2(30) NOT NULL,
    EMOTION_COLOR VARCHAR2(7),  -- HEX 코드 (#RRGGBB)
    EMOTION_EMOJI VARCHAR2(1000),  -- 이모티콘 img url
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 일기 전체 감정 분석 결과 테이블
CREATE TABLE DIARY_EMOTIONS (
    DIARY_EMOTION_ID NUMBER(19) PRIMARY KEY,
    DIARY_ID NUMBER(19) NOT NULL,
    EMOTION_ID NUMBER(10) NOT NULL,
    EMOTION_PERCENTAGE NUMBER(5,2) NOT NULL,  -- 비율 (%)
    IS_PRIMARY_EMOTION NUMBER(1) DEFAULT 0,  -- 대표 감정 여부
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_DIARY_EMOTIONS_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE,
    CONSTRAINT FK_DIARY_EMOTIONS_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID),
    CONSTRAINT CHK_IS_PRIMARY_EMOTION CHECK (IS_PRIMARY_EMOTION IN (0, 1))
);

-- 문장별 감정 분석 결과 테이블
CREATE TABLE SENTENCE_EMOTIONS (
    SENTENCE_EMOTION_ID NUMBER(19) PRIMARY KEY,
    DIARY_ID NUMBER(19) NOT NULL,
    SENTENCE_TEXT CLOB NOT NULL,
    EMOTION_ID NUMBER(10) NOT NULL,4
    ACTIVITY_TYPE VARCHAR2(50),  
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_SENTENCE_EMOTIONS_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE,
    CONSTRAINT FK_SENTENCE_EMOTIONS_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID)
);

-- 일기에서 추출된 활동 테이블
CREATE TABLE DIARY_ACTIVITIES (
    DIARY_ACTIVITY_ID NUMBER(19) PRIMARY KEY,
    DIARY_ID NUMBER(19) NOT NULL,
    ACTIVITY_NAME VARCHAR2(100) NOT NULL,  -- 추출된 활동 이름 (술자리, 생일파티, 조깅 등)
    ACTIVITY_SENTIMENT NUMBER(3,2),        -- 활동과 연관된 감정 점수 (-5.0 ~ +5.0)
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_DIARY_ACTIVITIES_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE
);

-- 유저의 감정 점수 기록 테이블
CREATE TABLE USER_EMOTION_SCORES (
    SCORE_ID NUMBER(19) PRIMARY KEY,
    USER_ID NUMBER(19) NOT NULL,
    EMOTION_ID NUMBER(10) NOT NULL,
    SCORE NUMBER(5,2) NOT NULL,  -- 누적 점수
    SCORE_DATE DATE DEFAULT SYSDATE NOT NULL,  -- 점수가 기록된 날짜
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_USER_EMOTION_SCORES_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID),
    CONSTRAINT FK_USER_EMOTION_SCORES_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID)
);

-- 4. 활동 추천 관련 테이블
-- 사용자 활동 기록 및 감정 변화 테이블 (사용자가 수행한 활동에 따른 감정 변화 추적)
CREATE TABLE USER_ACTIVITY_RECORDS (
    RECORD_ID NUMBER(19) PRIMARY KEY,
    USER_ID NUMBER(19) NOT NULL,
    ACTIVITY_NAME VARCHAR2(100) NOT NULL,  -- 수행한 활동 이름
    EMOTION_BEFORE_ID NUMBER(10),          -- 활동 전 감정
    EMOTION_AFTER_ID NUMBER(10),           -- 활동 후 감정
    EMOTION_CHANGE_SCORE NUMBER(3,2),      -- 감정 변화 점수
    ACTIVITY_DATE DATE DEFAULT SYSDATE,    -- 활동 수행 날짜
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_USER_ACTIVITY_RECORDS_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE,
    CONSTRAINT FK_USER_ACTIVITY_RECORDS_BEFORE FOREIGN KEY (EMOTION_BEFORE_ID) REFERENCES EMOTIONS (EMOTION_ID),
    CONSTRAINT FK_USER_ACTIVITY_RECORDS_AFTER FOREIGN KEY (EMOTION_AFTER_ID) REFERENCES EMOTIONS (EMOTION_ID)
);

-- 활동 효과성 테이블 (특정 감정 상태에서 특정 활동의 효과성 데이터)
CREATE TABLE ACTIVITY_EFFECTS (
    EFFECT_ID NUMBER(19) PRIMARY KEY,
    EMOTION_ID NUMBER(10) NOT NULL,         -- 시작 감정
    ACTIVITY_NAME VARCHAR2(100) NOT NULL,   -- 활동 이름
    AVERAGE_CHANGE_SCORE NUMBER(3,2),       -- 평균 감정 변화 점수
    USAGE_COUNT NUMBER(10) DEFAULT 0,       -- 사용 횟수
    POSITIVE_EFFECT_COUNT NUMBER(10) DEFAULT 0, -- 긍정적 효과 횟수
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_ACTIVITY_EFFECTS_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID),
    CONSTRAINT UK_EMOTION_ACTIVITY_NAME UNIQUE (EMOTION_ID, ACTIVITY_NAME)
);

-- 5. 피드백 생성 관련
CREATE TABLE FEEDBACK_TEMPLATES (
    FEEDBACK_ID NUMBER(10) PRIMARY KEY,
    EMOTION_ID NUMBER(10) NOT NULL, 
    DIARY_ID NUMBER(19) NOT NULL,     -- 추가됨
    FEEDBACK_TEXT CLOB NOT NULL,
    IS_POSITIVE NUMBER(1) DEFAULT 1,  -- 긍정적 피드백 여부
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_FEEDBACK_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID),
    CONSTRAINT FK_FEEDBACK_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES(DIARY_ID), -- 추가됨
    CONSTRAINT CHK_IS_POSITIVE CHECK (IS_POSITIVE IN (0, 1))
);

-- 6. 음악 추천
CREATE TABLE RECOMMENDED_MUSIC (
    MUSIC_ID NUMBER(10) PRIMARY KEY,
    EMOTION_ID NUMBER(10) NOT NULL,
    TITLE VARCHAR2(255) NOT NULL,
    ARTIST VARCHAR2(255) NOT NULL,
    SPOTIFY_LINK VARCHAR2(255),
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_RECOMMENDED_MUSIC_EMOTION FOREIGN KEY (EMOTION_ID) REFERENCES EMOTIONS (EMOTION_ID)
);

-- 7. 북마크 테이블
CREATE TABLE BOOKMARKS (
    BOOKMARK_ID NUMBER(19) PRIMARY KEY,
    USER_ID NUMBER(19) NOT NULL,
    DIARY_ID NUMBER(19) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_BOOKMARKS_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE,
    CONSTRAINT FK_BOOKMARKS_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE,
    CONSTRAINT UK_USER_DIARY UNIQUE (USER_ID, DIARY_ID)  -- 중복 북마크 방지
);

	-- 챗봇 유형 테이블 (감정형, 현실형, 베프 등)
	CREATE TABLE CHATBOT_TYPES (
	    CHATBOT_TYPE_ID NUMBER(10) PRIMARY KEY,
	    TYPE_NAME VARCHAR2(50) NOT NULL,
	    DESCRIPTION VARCHAR2(500),
	    PERSONA_DESCRIPTION CLOB,  -- 챗봇의 성격, 말투 등에 대한 상세 설명
	    ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
	    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
	    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
	    CONSTRAINT CHK_ACTIVE CHECK (ACTIVE IN (0, 1))
	);
	
	
	-- 채팅 세션 테이블 (사용자와 특정 챗봇 간의 대화 세션)
	CREATE TABLE CHAT_SESSIONS (
	    SESSION_ID NUMBER(19) PRIMARY KEY,
	    USER_ID NUMBER(19) NOT NULL,
	    CHATBOT_TYPE_ID NUMBER(10) NOT NULL,
	    SESSION_NAME VARCHAR2(100),  -- 세션 이름 (예: "오늘의 고민상담")
	    IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,  -- 활성 세션 여부
	    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
	    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
	    LAST_INTERACTION_AT TIMESTAMP DEFAULT SYSTIMESTAMP,  -- 마지막 상호작용 시간
	    CONSTRAINT FK_CHAT_SESSIONS_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE,
	    CONSTRAINT FK_CHAT_SESSIONS_CHATBOT_TYPE FOREIGN KEY (CHATBOT_TYPE_ID) REFERENCES CHATBOT_TYPES (CHATBOT_TYPE_ID),
	    CONSTRAINT CHK_SESSION_ACTIVE CHECK (IS_ACTIVE IN (0, 1))
	);
	
	
	-- 채팅 메시지 테이블 (대화 내용)
	CREATE TABLE CHAT_MESSAGES (
	    MESSAGE_ID NUMBER(19) PRIMARY KEY,
	    SESSION_ID NUMBER(19) NOT NULL,
	    IS_USER_MESSAGE NUMBER(1) NOT NULL,  -- 1: 사용자 메시지, 0: 챗봇 메시지
	    MESSAGE_CONTENT CLOB NOT NULL,
	    CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
	    CONSTRAINT FK_CHAT_MESSAGES_SESSION FOREIGN KEY (SESSION_ID) REFERENCES CHAT_SESSIONS (SESSION_ID) ON DELETE CASCADE,
	    CONSTRAINT CHK_IS_USER_MESSAGE CHECK (IS_USER_MESSAGE IN (0, 1))
	);

-- 초기 데이터 설정
-- 기본 감정 데이터 삽입
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('행복', '#FFEB3B', '😊');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('만족', '#4CAF50', '😌');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('설렘', '#FF9800', '🥰');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('평온', '#03A9F4', '😇');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('불안', '#9C27B0', '😰');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('슬픔', '#3F51B5', '😢');
INSERT INTO EMOTIONS (EMOTION_NAME, EMOTION_COLOR, EMOTION_EMOJI) VALUES ('분노', '#F44336', '😠');

-- 기본 프로필 사진 데이터 삽입
INSERT INTO PROFILE_PICTURES (IMAGE_URL, DESCRIPTION) VALUES ('/assets/profile/default1.png', '기본 프로필 1');
INSERT INTO PROFILE_PICTURES (IMAGE_URL, DESCRIPTION) VALUES ('/assets/profile/default2.png', '기본 프로필 2');
INSERT INTO PROFILE_PICTURES (IMAGE_URL, DESCRIPTION) VALUES ('/assets/profile/default3.png', '기본 프로필 3');
INSERT INTO PROFILE_PICTURES (IMAGE_URL, DESCRIPTION) VALUES ('/assets/profile/default4.png', '기본 프로필 4');
INSERT INTO PROFILE_PICTURES (IMAGE_URL, DESCRIPTION) VALUES ('/assets/profile/default5.png', '기본 프로필 5');

-- 기본 활동 효과성 데이터 (초기 추천용)
-- 슬픔 감정에 대한 활동 효과성
INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (6, '친구에게 연락하기', 2.5, 10, 8);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (6, '산책하기', 1.8, 15, 12);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (6, '좋아하는 음악 듣기', 1.5, 20, 15);

-- 불안 감정에 대한 활동 효과성
INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (5, '명상하기', 2.3, 12, 10);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (5, '심호흡하기', 1.7, 18, 14);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (5, '가벼운 스트레칭', 1.6, 14, 11);

-- 분노 감정에 대한 활동 효과성
INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (7, '운동하기', 2.7, 15, 13);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (7, '일기 쓰기', 1.9, 10, 8);

INSERT INTO ACTIVITY_EFFECTS (EMOTION_ID, ACTIVITY_NAME, AVERAGE_CHANGE_SCORE, USAGE_COUNT, POSITIVE_EFFECT_COUNT)
VALUES (7, '좋아하는 취미활동하기', 2.1, 12, 10);

-- 기본 피드백 템플릿 삽입
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (1, '오늘도 행복한 하루였네요! 계속 좋은 일이 있을 거예요!', 1);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (2, '만족스러운 하루를 보내셨군요. 이 기분을 유지해보세요!', 1);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (3, '설레는 하루였네요! 이 감정을 오래 간직하세요.', 1);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (4, '평온한 하루를 보내셨군요. 마음의 안정이 느껴집니다.', 1);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (5, '불안한 마음이 있으시군요. 심호흡을 천천히 해보세요.', 0);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (6, '힘든 하루였군요. 하지만 내일은 분명히 더 나은 날이 될 거예요.', 0);
INSERT INTO FEEDBACK_TEMPLATES (EMOTION_ID, FEEDBACK_TEXT, IS_POSITIVE) VALUES (7, '화가 나는 일이 있으셨군요. 감정을 표현하는 것도 중요해요.', 0);

-- 추천 음악 예시 데이터
INSERT INTO RECOMMENDED_MUSIC (EMOTION_ID, TITLE, ARTIST, SPOTIFY_LINK)
VALUES (1, 'Happy', 'Pharrell Williams', '<https://open.spotify.com/track/60nZcImufyMA1MKQZ2Bm3n>');

INSERT INTO RECOMMENDED_MUSIC (EMOTION_ID, TITLE, ARTIST, SPOTIFY_LINK)
VALUES (4, 'Weightless', 'Marconi Union', '<https://open.spotify.com/track/5OfhNIZZzHOFknROXxSc7p>');

INSERT INTO RECOMMENDED_MUSIC (EMOTION_ID, TITLE, ARTIST, SPOTIFY_LINK)
VALUES (6, 'Fix You', 'Coldplay', '<https://open.spotify.com/track/7LVHVU3tWfcxj5aiPFEW4Q>');

INSERT INTO RECOMMENDED_MUSIC (EMOTION_ID, TITLE, ARTIST, SPOTIFY_LINK)
VALUES (7, 'Numb', 'Linkin Park', '<https://open.spotify.com/track/2nLtzopw4rPReszdYBJU6h>');

INSERT INTO RECOMMENDED_MUSIC (EMOTION_ID, TITLE, ARTIST, SPOTIFY_LINK)
VALUES (5, 'Breathe Me', 'Sia', '<https://open.spotify.com/track/5zRrKyUnKRl8G0MFlT1vQH>');

-- 기본 챗봇 유형 데이터 삽입
INSERT INTO CHATBOT_TYPES (TYPE_NAME, DESCRIPTION, PERSONA_DESCRIPTION) 
VALUES ('감정형', '사용자의 말에 공감하고 긍정적인 대답을 해주는 챗봇', 
'당신은 감정형 AI 챗봇으로, 사용자의 감정에 깊이 공감하고 항상 지지적이고 긍정적인 대답을 해주는 성격입니다. 
사용자가 부정적인 감정을 표현할 때는 더욱 깊이 공감하고, 감정의 타당성을 인정해 주며, 희망적인 메시지를 함께 전달합니다. 
당신의 주 목표는 사용자가 정서적으로 안정감을 느끼고 지지받는다고 느끼게 하는 것입니다.');

INSERT INTO CHATBOT_TYPES (TYPE_NAME, DESCRIPTION, PERSONA_DESCRIPTION) 
VALUES ('현실적인', '사용자의 말을 객관적으로 판단하고 현실적인 조언을 해주는 챗봇', 
'당신은 현실적인 AI 챗봇으로, 감정보다는 논리와 분석에 중점을 두며 객관적인 시각으로 상황을 판단합니다. 
사용자에게 솔직하고 직접적인 피드백을 제공하며, 현실적인 해결책과 조언을 제시합니다. 
당신의 주 목표는 사용자가 상황을 객관적으로 이해하고 실질적인 해결책을 찾을 수 있도록 돕는 것입니다.');

INSERT INTO CHATBOT_TYPES (TYPE_NAME, DESCRIPTION, PERSONA_DESCRIPTION) 
VALUES ('베프', '장난도 치고 진짜 친구처럼 대답하는 친구 챗봇', 
'당신은 베프 AI 챗봇으로, 오랜 친구처럼 자연스럽고 편안한 대화를 나눕니다. 
가끔은 재미있는 농담이나 가벼운 장난을 치기도 하지만, 필요할 때는 진지하게 조언해주는 균형 잡힌 성격입니다. 
존댓말보다는 반말을 사용하며, 친구 사이의 대화처럼 솔직하고 꾸밈없는 표현을 사용합니다. 
당신의 주 목표는 사용자에게 진짜 친구와 대화하는 것 같은 편안함과 친밀감을 제공하는 것입니다.');

================================================================= 적용 ㄴㄴ

트리거

-- 트리거
-- User ID 자동 생성 트리거
CREATE OR REPLACE TRIGGER USERS_BEFORE_INSERT
BEFORE INSERT ON USERS
FOR EACH ROW
BEGIN
    SELECT USER_SEQ.NEXTVAL INTO :new.ID FROM dual;
END;
/

-- 날짜 자동 업데이트 트리거
CREATE OR REPLACE TRIGGER USERS_BEFORE_UPDATE
BEFORE UPDATE ON USERS
FOR EACH ROW
BEGIN
    :new.UPDATED_AT := SYSTIMESTAMP;
END;
/

-- 일기 ID 생성 트리거
CREATE OR REPLACE TRIGGER DIARIES_BEFORE_INSERT
BEFORE INSERT ON DIARIES
FOR EACH ROW
BEGIN
    SELECT DIARY_SEQ.NEXTVAL INTO :new.DIARY_ID FROM dual;
END;
/

-- 일기 업데이트 트리거
CREATE OR REPLACE TRIGGER DIARIES_BEFORE_UPDATE
BEFORE UPDATE ON DIARIES
FOR EACH ROW
BEGIN
    :new.UPDATED_AT := SYSTIMESTAMP;
END;
/

-- 일기 수정 히스토리 테이블
CREATE TABLE DIARY_HISTORIES (
    HISTORY_ID NUMBER(19) PRIMARY KEY,
    DIARY_ID NUMBER(19) NOT NULL,
    TITLE VARCHAR2(255) NOT NULL,
    CONTENT CLOB NOT NULL,
    WEATHER VARCHAR2(30),
    UPDATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT FK_DIARY_HISTORIES_DIARY FOREIGN KEY (DIARY_ID) REFERENCES DIARIES (DIARY_ID) ON DELETE CASCADE
);

-- 히스토리 ID 생성 트리거
CREATE OR REPLACE TRIGGER DIARY_HISTORIES_BEFORE_INSERT
BEFORE INSERT ON DIARY_HISTORIES
FOR EACH ROW
BEGIN
    SELECT DIARY_HISTORY_SEQ.NEXTVAL INTO :new.HISTORY_ID FROM dual;
END;
/

-- 사진 ID 생성 트리거
CREATE OR REPLACE TRIGGER DIARY_PHOTOS_BEFORE_INSERT
BEFORE INSERT ON DIARY_PHOTOS
FOR EACH ROW
BEGIN
    SELECT PHOTO_SEQ.NEXTVAL INTO :new.PHOTO_ID FROM dual;
END;
/

-- 감정 ID 생성 트리거
CREATE OR REPLACE TRIGGER EMOTIONS_BEFORE_INSERT
BEFORE INSERT ON EMOTIONS
FOR EACH ROW
BEGIN
    SELECT EMOTION_SEQ.NEXTVAL INTO :new.EMOTION_ID FROM dual;
END;
/

-- 일기 감정 ID 생성 트리거
CREATE OR REPLACE TRIGGER DIARY_EMOTIONS_BEFORE_INSERT
BEFORE INSERT ON DIARY_EMOTIONS
FOR EACH ROW
BEGIN
    SELECT DIARY_EMOTION_SEQ.NEXTVAL INTO :new.DIARY_EMOTION_ID FROM dual;
END;
/

-- 문장 감정 ID 생성 트리거
CREATE OR REPLACE TRIGGER SENTENCE_EMOTIONS_BEFORE_INSERT
BEFORE INSERT ON SENTENCE_EMOTIONS
FOR EACH ROW
BEGIN
    SELECT SENTENCE_EMOTION_SEQ.NEXTVAL INTO :new.SENTENCE_EMOTION_ID FROM dual;
END;
/

-- 다이어리 활동 ID 생성 트리거
CREATE OR REPLACE TRIGGER DIARY_ACTIVITIES_BEFORE_INSERT
BEFORE INSERT ON DIARY_ACTIVITIES
FOR EACH ROW
BEGIN
    SELECT DIARY_ACTIVITY_SEQ.NEXTVAL INTO :new.DIARY_ACTIVITY_ID FROM dual;
END;
/

-- 사용자 활동 기록 ID 생성 트리거
CREATE OR REPLACE TRIGGER USER_ACTIVITY_RECORDS_BEFORE_INSERT
BEFORE INSERT ON USER_ACTIVITY_RECORDS
FOR EACH ROW
BEGIN
    SELECT USER_ACTIVITY_RECORD_SEQ.NEXTVAL INTO :new.RECORD_ID FROM dual;
END;
/

-- 활동 효과성 ID 생성 트리거
CREATE OR REPLACE TRIGGER ACTIVITY_EFFECTS_BEFORE_INSERT
BEFORE INSERT ON ACTIVITY_EFFECTS
FOR EACH ROW
BEGIN
    SELECT ACTIVITY_EFFECT_SEQ.NEXTVAL INTO :new.EFFECT_ID FROM dual;
END;
/

-- 활동 효과성 업데이트 트리거
CREATE OR REPLACE TRIGGER ACTIVITY_EFFECTS_BEFORE_UPDATE
BEFORE UPDATE ON ACTIVITY_EFFECTS
FOR EACH ROW
BEGIN
    :new.UPDATED_AT := SYSTIMESTAMP;
END;
/

-- 피드백 ID 생성 트리거
CREATE OR REPLACE TRIGGER FEEDBACK_BEFORE_INSERT
BEFORE INSERT ON FEEDBACK_TEMPLATES
FOR EACH ROW
BEGIN
    SELECT FEEDBACK_SEQ.NEXTVAL INTO :new.FEEDBACK_ID FROM dual;
END;
/

-- 음악 ID 생성 트리거
CREATE OR REPLACE TRIGGER RECOMMENDED_MUSIC_BEFORE_INSERT
BEFORE INSERT ON RECOMMENDED_MUSIC
FOR EACH ROW
BEGIN
    SELECT MUSIC_SEQ.NEXTVAL INTO :new.MUSIC_ID FROM dual;
END;
/

-- 북마크 ID 생성 트리거
CREATE OR REPLACE TRIGGER BOOKMARKS_BEFORE_INSERT
BEFORE INSERT ON BOOKMARKS
FOR EACH ROW
BEGIN
    SELECT BOOKMARK_SEQ.NEXTVAL INTO :new.BOOKMARK_ID FROM dual;
END;
/

-- 북마크 추가 시 일기 북마크 상태 업데이트 트리거
CREATE OR REPLACE TRIGGER BOOKMARKS_AFTER_INSERT
AFTER INSERT ON BOOKMARKS
FOR EACH ROW
BEGIN
    UPDATE DIARIES
    SET IS_BOOKMARKED = 1
    WHERE DIARY_ID = :new.DIARY_ID;
END;
/

-- 북마크 삭제 시 일기 북마크 상태 업데이트 트리거
CREATE OR REPLACE TRIGGER BOOKMARKS_AFTER_DELETE
AFTER DELETE ON BOOKMARKS
FOR EACH ROW
BEGIN
    UPDATE DIARIES
    SET IS_BOOKMARKED = 0
    WHERE DIARY_ID = :old.DIARY_ID;
END;
/

-- 챗봇 유형 ID 생성 트리거
CREATE OR REPLACE TRIGGER CHATBOT_TYPES_BEFORE_INSERT
BEFORE INSERT ON CHATBOT_TYPES
FOR EACH ROW
BEGIN
    SELECT CHATBOT_TYPE_SEQ.NEXTVAL INTO :new.CHATBOT_TYPE_ID FROM dual;
END;
/

-- 챗봇 유형 업데이트 트리거
CREATE OR REPLACE TRIGGER CHATBOT_TYPES_BEFORE_UPDATE
BEFORE UPDATE ON CHATBOT_TYPES
FOR EACH ROW
BEGIN
    :new.UPDATED_AT := SYSTIMESTAMP;
END;
/

-- 채팅 세션 ID 생성 트리거
CREATE OR REPLACE TRIGGER CHAT_SESSIONS_BEFORE_INSERT
BEFORE INSERT ON CHAT_SESSIONS
FOR EACH ROW
BEGIN
    SELECT CHAT_SESSION_SEQ.NEXTVAL INTO :new.SESSION_ID FROM dual;
    :new.LAST_INTERACTION_AT := SYSTIMESTAMP;
END;
/

-- 채팅 세션 업데이트 트리거
CREATE OR REPLACE TRIGGER CHAT_SESSIONS_BEFORE_UPDATE
BEFORE UPDATE ON CHAT_SESSIONS
FOR EACH ROW
BEGIN
    :new.UPDATED_AT := SYSTIMESTAMP;
END;
/

-- 채팅 메시지 ID 생성 트리거
CREATE OR REPLACE TRIGGER CHAT_MESSAGES_BEFORE_INSERT
BEFORE INSERT ON CHAT_MESSAGES
FOR EACH ROW
BEGIN
    SELECT CHAT_MESSAGE_SEQ.NEXTVAL INTO :new.MESSAGE_ID FROM dual;
END;
/