1. 사용자 테이블 (users)

sql
복사
CREATE TABLE users (
    user_id VARCHAR2(50) PRIMARY KEY, -- UUID 또는 일반 계정 ID
    email VARCHAR2(100) UNIQUE NOT NULL, -- 이메일 (일반 로그인용)
    password VARCHAR2(255), -- 비밀번호 (OAuth 사용자 NULL 가능)
    name VARCHAR2(50) NOT NULL, -- 사용자 이름
    oauth_provider VARCHAR2(20),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP, -- 가입일
    updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

2. OAuth 계정 연동 테이블 (oauth_accounts)

sql
복사
CREATE TABLE oauth_accounts (
    oauth_id VARCHAR2(100) PRIMARY KEY, -- OAuth 제공자의 사용자 ID
    user_id VARCHAR2(50) NOT NULL,
    provider VARCHAR2(20) NOT NULL CHECK (provider IN ('naver', 'kakao', 'google')),
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

3. 문장별 감정 분석 테이블 (diary_sentence_analysis)

일기를 문장 단위로 쪼개어 분석한 결과를 저장합니다.

sql
복사
CREATE TABLE diary_sentence_analysis (
    sentence_id NUMBER PRIMARY KEY,
    diary_id NUMBER NOT NULL,
    sentence_order NUMBER NOT NULL, -- 일기 내 문장 순서
    sentence_text CLOB NOT NULL,
    conjunction VARCHAR2(50), -- 부정적 접속사 예: '그러나', '지만'
    emotion VARCHAR2(20) CHECK (emotion IN ('행복', '만족', '설렘', '평온', '불안', '슬픔', '분노')),
    emotion_score NUMBER(3,0) CHECK (emotion_score BETWEEN -3 AND 3),
    percentage NUMBER CHECK (percentage BETWEEN 0 AND 100),
    analyzed_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (diary_id) REFERENCES diary(diary_id) ON DELETE CASCADE
);

4. 일기 테이블 (diary)

sql
복사
CREATE TABLE diary (
    diary_id      NUMBER PRIMARY KEY,
    user_id       VARCHAR2(50) NOT NULL,
    title         VARCHAR2(255) NOT NULL,
    content       CLOB NOT NULL,
    diary_date    DATE NOT NULL,
    is_bookmarked NUMBER(1) DEFAULT 0 CHECK (is_bookmarked IN (0, 1)), -- 책갈피 여부 (0: 일반, 1: 책갈피)
    summary       CLOB,
    created_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

5. 일기 이미지 테이블 (diary_images)

sql
복사
CREATE TABLE diary_images (
    diary_image_id NUMBER PRIMARY KEY,
    diary_id       NUMBER NOT NULL,
    image_url      VARCHAR2(255) NOT NULL, -- 이미지 저장 URL
    uploaded_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (diary_id) REFERENCES diary(diary_id) ON DELETE CASCADE
);

6. 감정 분석 테이블 (emotion_analysis)

sql
복사
CREATE TABLE emotion_analysis (
    id            NUMBER PRIMARY KEY,
    diary_id      NUMBER NOT NULL,
    emotion       VARCHAR2(20) CHECK (emotion IN ('행복', '만족', '설렘', '평온', '불안', '슬픔', '분노')),
    emotion_score NUMBER(3,0) CHECK (emotion_score BETWEEN -3 AND 3),
    percentage    NUMBER CHECK (percentage BETWEEN 0 AND 100),
    analyzed_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (diary_id) REFERENCES diary(diary_id) ON DELETE CASCADE
);

7. 감정 트래킹 테이블 (최근 30일 요약) (emotion_tracking)

sql
복사
CREATE TABLE emotion_tracking (
    id           NUMBER PRIMARY KEY,
    user_id      VARCHAR2(50) NOT NULL,
    tracked_date DATE NOT NULL,
    summary      CLOB NOT NULL,
    created_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

8. 감정 트렌드 테이블 (주간/월간 감정 리포트 저장) (emotion_trend)

sql
복사
CREATE TABLE emotion_trend (
    id              NUMBER PRIMARY KEY,
    user_id         VARCHAR2(50) NOT NULL,
    period          VARCHAR2(10) CHECK (period IN ('WEEKLY', 'MONTHLY')),
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL,
    emotion_summary CLOB NOT NULL,
    created_at      TIMESTAMP DEFAULT SYSTIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

9. 감정 경고 시스템 (emotion_alerts)

sql
복사
CREATE TABLE emotion_alerts (
    id         NUMBER PRIMARY KEY,
    user_id    VARCHAR2(50) NOT NULL,
    alert_date DATE NOT NULL,
    emotion    VARCHAR2(20) CHECK (emotion IN ('행복', '만족', '설렘', '평온', '불안', '슬픔', '분노')),
    message    CLOB NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

10. 감정별 추천 노래 테이블 (emotion_songs)