project1

테이블 (19)

content_moderation_logs

  • log_id — INT
  • user_id — INT — NOT NULL
  • content_type — ENUM('POST', 'COMMENT')
  • content_id — INT
  • detected_word — VARCHAR(50)
  • action — ENUM('BLOCKED', 'WARNED')
  • reported_by — INT
  • handled_by — INT
  • created_at — TIMESTAMP

emotion_boards

  • board_id — INT
  • emotion_name — ENUM('JOY', 'SADNESS', 'ANGER', 'COMFORT')
  • description — VARCHAR(100)

mood_checkups

  • checkup_id — INT
  • user_id — INT — NOT NULL
  • total_score — INT
  • test_type — ENUM('PHQ9', 'SCALE_SHORT')
  • taken_at — TIMESTAMP

mood_responses

  • response_id — INT
  • checkup_id — INT — NOT NULL
  • question_number — INT
  • score — INT

reactions

  • reaction_id — INT
  • user_id — INT — NOT NULL
  • target_type — ENUM('POST', 'COMMENT')
  • target_id — INT
  • emotion — ENUM('EMPATHY', 'SUPPORT', 'LIKE')
  • reacted_at — TIMESTAMP

users

  • user_id — INT
  • email — VARCHAR(100) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • role — ENUM('USER', 'ADMIN')
  • level — ENUM('BLACK', 'NAVY', 'SKY', 'WHITE')
  • score — INT
  • created_at — TIMESTAMP

감정게시판 종류 (emotion_categories)

  • category_id — INT — 기본 키
  • name — ENUM('JOY', 'ANGER', 'SADNESS', 'FEAR', 'HURT', 'EMBARRASS') — NOT NULL
  • description — VARCHAR(100)

감정분석 결과ㅣ (emotion_analysis)

  • analysis_id — INT
  • joy_score — FLOAT
  • sadness_score — FLOAT
  • anger_score — FLOAT
  • anxiety_score — FLOAT
  • others_json — JSON
  • model_name — VARCHAR(50)
  • analyzed_at — TIMESTAMP

게시글 (posts)

  • post_id — INT — 기본 키
  • member_id — INT — 기본 키 — NOT NULL
  • category_id — INT — 기본 키 — NOT NULL
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • content — TEXT — NOT NULL
  • is_anonymous — BOOLEAN
  • anon_display_name — VARCHAR(50)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • post_id (post_id2) — INT — 외래 키 → 게시글.post_id
  • member_id (member_id3) — INT — 외래 키 → 게시글.member_id — NOT NULL
  • category_id (category_id2) — INT — 외래 키 → 게시글.category_id — NOT NULL
  • member_id (member_id22) — INT — 외래 키 → 게시글.member_id

댓글 (comments)

  • comment_id — INT — 기본 키
  • post_id — INT — 기본 키 — NOT NULL
  • member_id — INT — 기본 키 — NOT NULL
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • post_id (post_id2) — INT — 외래 키 → 게시글.post_id
  • member_id (member_id3) — INT — 외래 키 → 게시글.member_id — NOT NULL
  • category_id — INT — 외래 키 → 게시글.category_id — NOT NULL
  • member_id (member_id22) — INT — 외래 키 → 게시글.member_id
  • content — TEXT — NOT NULL
  • is_anonymous — BOOLEAN
  • anon_display_name — VARCHAR(50)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

문항별 응답 (phq9_answers)

  • answer_id — INT — 기본 키
  • test_id — INT — 기본 키 — NOT NULL
  • question_number — INT — NOT NULL
  • score — INT — NOT NULL

비속어 감지 (penalty_logs)

  • penalty_id — INT — 기본 키
  • member_id — INT — 기본 키 — NOT NULL
  • content_id — INT — 기본 키 — NOT NULL
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • content_type — ENUM('post', 'comment') — NOT NULL
  • bad_word — VARCHAR(50)
  • detected_at — TIMESTAMP
  • action — VARCHAR(50)

사용자 (members)

  • member_id — INT — 기본 키
  • nickname — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • role — ENUM('USER', 'ADMIN')
  • trust_score — INT
  • trust_level — ENUM('BLACK', 'NAVY', 'BLUE', 'SKYBLUE', 'WHITE')
  • joined_at — TIMESTAMP

신고 내역 (reports)

  • report_id — INT — 기본 키
  • reporter_id — INT — 기본 키 — NOT NULL
  • target_id — INT — 기본 키 — NOT NULL
  • member_id — INT — 외래 키 → 사용자.member_id
  • target_type — ENUM('post', 'comment') — NOT NULL
  • reason — TEXT
  • reported_at — TIMESTAMP
  • is_resolved — BOOLEAN
  • resolved_by — INT
  • resolved_at — TIMESTAMP
  • resolution_note — TEXT

우울증 검사 (phq9_tests)

  • test_id — INT — 기본 키
  • member_id — INT — 기본 키 — NOT NULL
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • total_score — INT — NOT NULL
  • taken_at — TIMESTAMP

이모지 반응 (emoji_reactions)

  • member_id — INT — 기본 키 — NOT NULL
  • target_id — INT — 기본 키 — NOT NULL
  • reaction_type_id — INT — 기본 키 — NOT NULL
  • reaction_id — INT — 기본 키
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • post_id — INT — 외래 키 → 게시글.post_id
  • member_id (member_id3) — INT — 외래 키 → 게시글.member_id — NOT NULL
  • category_id — INT — 외래 키 → 게시글.category_id — NOT NULL
  • member_id (member_id22) — INT — 외래 키 → 게시글.member_id
  • target_type — ENUM('post', 'comment') — NOT NULL
  • reacted_at — TIMESTAMP

이모지 종륲 (reaction_types)

  • reaction_type_id — INT — 기본 키
  • name — VARCHAR(50)
  • icon_url — VARCHAR(255)

챗봇 세션 (chat_sessions)

  • session_id — INT — 기본 키
  • member_id — INT — 기본 키 — NOT NULL
  • member_id (member_id2) — INT — 외래 키 → 사용자.member_id
  • started_at — TIMESTAMP
  • ended_at — TIMESTAMP

챗봇대화 (chat_messages)

  • message_id — INT — 기본 키
  • session_id — INT — 기본 키 — NOT NULL
  • sender — ENUM('USER', 'BOT') — NOT NULL
  • message — TEXT — NOT NULL
  • created_at — TIMESTAMP
  • emotion_analysis_id — INT