<aside> 💡

ERD 수정 시 하단의 ERD 변경사항에 토글로 날짜, 변경자, 변경사항과 이유에 대해 남겨주시면 감사하겠습니다.

</aside>


erDiagram
    %% 1. 사용자 및 인증 (User & Auth)
    USERS {
        int id PK
        string email "Nullable (OAuth)"
        string nickname
        string bio
        string profile_image_url
        string currentRefreshToken "Nullable"
        datetime created_at
        datetime updated_at
    }

    USER_OAUTH {
        int id PK
        int user_id FK
        string provider "local, google, kakao"
        string provider_user_id "OAuth ID / Email"
        string password_hash "Nullable"
        datetime created_at
    }

    USER_CREDITS {
        int id PK
        int user_id FK
        int amount "-1, +5"
        string reason "ANSWER, REWARD"
        datetime created_at
    }
  
    USER_STATS {
        int user_id PK,FK "1:1 매핑"
        int level
        int current_xp
        int streak_days
        int total_study_time_sec "누적 학습시간"
        int total_solved_questions "누적 문제수"
        datetime updated_at
    }

    %% 2. 학습 콘텐츠 (Content)
    QUESTIONS {
        int id PK
        text content
        json mustInclude
        varchar topicId
        char contentHash
        string category "OS, NETWORK..."
        string difficulty "EASY, MEDIUM, HARD"
        int time_limit_sec
        datetime created_at
        
			  chapter      Int
			  term         String     
			  conceptLevel Int        "@map(concept_level)"
			  depth        Int
    }

    QUESTION_RUBRIC_ITEMS {
        int id PK
        int question_id FK
        text keywords_text
        float weight
    }
    
    EXTRA_QUESTIONS {
        int id PK
        int session_id FK
        int parent_answer_id FK
        text content
        json must_include
        string category
        string difficulty
        int time_limit_sec
        int depth
        datetime created_at
    }
    
    %% 세션
    SESSIONS {
        int id PK
        int user_id FK
        string status "ACTIVE, COMPLETED"
        int total_score
        int total_time_sec
        json gained_xp "상세 내역 {baseXp:100, difficultyBonus:50, deepDiveBonus:60}"
        int current_question_count 
        string category "OS, NETWORK, DATABASE, STRUCTURE"
        string difficulty "EASY, MEDIUM, HARD"
        datetime started_at
        datetime completed_at
    }

    %% 3. 학습 기록 (Activity)
    USER_ANSWERS {
        int id PK
        int user_id FK
        int session_id FK
        int question_id FK "Nullable"
        int extra_question_id FK "Nullable"
        text answer_text
        int time_spent_sec
        int overall_score
        json feedback_json
        datetime created_at
    }

    %% 4. 게이미피케이션 (Gamification)
    BADGES {
        int id PK
        string name
        string description
        string condition
        int reward_xp
        string icon_url
        string color
    }

    USER_BADGES {
        int id PK
        int user_id FK
        int badge_id FK
        datetime created_at
    }

    XP {
        int level PK
        int required_xp
    }

    %% 관계 정의
    USERS ||--o{ USER_OAUTH : "has_accounts"
    USERS ||--o{ USER_CREDITS : "has_history"
    USERS ||--|| USER_STATS : "has_summary"
    
    USERS ||--o{ USER_ANSWERS : "submits"
    
    QUESTIONS ||--o{ USER_ANSWERS : "answered_in (normal)"
    EXTRA_QUESTIONS ||--o{ USER_ANSWERS : "answered_in (Tail)"
    
    QUESTIONS ||--o{ QUESTION_RUBRIC_ITEMS : "evaluated_by"
    QUESTIONS ||..o{ EXTRA_QUESTIONS : "references"
    
    USERS ||--o{ SESSIONS : "starts"
		SESSIONS ||--o{ USER_ANSWERS : "contains"
    
    USERS ||--o{ USER_BADGES : "earns"
    BADGES ||--o{ USER_BADGES : "awarded_to"

ERD 변경사항