emotion_DB

테이블 (17)

chatbot_sessions

  • session_id — INT — 기본 키
  • user_id — INT
  • started_at — DATETIME
  • ended_at — DATETIME

comfort_phrases

  • phrase_id — INT — 기본 키
  • message_id — INT
  • user_id — INT
  • content — TEXT
  • saved_at — DATETIME

emotion_analyses

  • analysis_id — INT — 기본 키
  • sadness_score — FLOAT
  • anxiety_score — FLOAT
  • anger_score — FLOAT
  • joy_score — FLOAT
  • additional_emotion_scores — JSON
  • model_used — VARCHAR(100)
  • analyzed_at — DATETIME

emotion_predictions

  • prediction_id — INT — 기본 키
  • source_type — ENUM('chat')
  • source_id — INT
  • input_text — TEXT
  • predicted_emotion_id — INT
  • confidence_score — FLOAT
  • model_name — VARCHAR(100)
  • predicted_at — DATETIME

emotions

  • emotion_id — INT — 기본 키
  • name — VARCHAR(50) — NOT NULL
  • severity_level — INT
  • color_code — VARCHAR(7)

phq9 테스트 (phq9_tests)

  • phq9_id — INT — 기본 키
  • user_id — INT
  • total_score — INT
  • taken_at — DATETIME

phq9_답변 (phq9_answers)

  • answer_id — INT — 기본 키
  • phq9_id — INT
  • question_number — INT
  • answer — INT

reaction_types

  • reaction_type_id — INT — 기본 키
  • emotion — VARCHAR(50)
  • icon — VARCHAR(255)

reactions

  • reaction_id — INT — 기본 키
  • user_id — INT
  • target_type — ENUM('post', 'comment')
  • target_id — INT
  • reaction_type_id — INT
  • created_at — DATETIME

게시글 (posts)

  • post_id — INT — 기본 키
  • board_id — INT
  • user_id — INT
  • content — TEXT
  • is_anonymous — BOOLEAN
  • anonymous_display_id — VARCHAR(50)
  • created_at — DATETIME
  • updated_at — DATETIME

게시판 (emotion_boards)

  • board_id — INT — 기본 키
  • emotion_category — VARCHAR(50)
  • description — TEXT

댓글 (comments)

  • comment_id — INT — 기본 키
  • post_id — INT
  • user_id — INT
  • content — TEXT
  • is_anonymous — BOOLEAN
  • anonymous_display_id — VARCHAR(50)
  • created_at — DATETIME
  • updated_at — DATETIME

신고 기록 (reports)

  • report_id — INT — 기본 키
  • reporter_id — INT
  • target_type — ENUM('post', 'comment')
  • target_id — INT
  • reason — TEXT
  • reported_at — DATETIME
  • is_resolved — BOOLEAN
  • resolved_by_user_id — INT
  • resolved_at — DATETIME
  • resolution_note — TEXT

유저 (users)

  • user_id — INT — 기본 키
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(100) — NOT NULL
  • role — ENUM('USER', 'ADMIN') — NOT NULL
  • crayon_score — INT
  • crayon_level — VARCHAR(20)
  • created_at — DATETIME

챗봇 메시지 (chatbot_messages)

  • message_id — INT — 기본 키
  • session_id — INT
  • sender — ENUM('USER', 'BOT')
  • message_text — TEXT
  • created_at — DATETIME
  • emotion_analysis_id — INT

페널티 로그 (penalty_logs)

  • log_id — INT — 기본 키
  • user_id — INT
  • content_type — ENUM('post', 'comment')
  • content_id — INT
  • detected_word — VARCHAR(100)
  • detected_at — DATETIME
  • action_taken — ENUM('warned', 'blocked')

프롬프트 기록 (prompt_logs)

  • log_id — INT — 기본 키
  • message_id — INT
  • prompt_text — TEXT
  • model_name — VARCHAR(100)
  • temperature — FLOAT
  • created_at — DATETIME