// =====================================================
// [Project] 모아요 ERD v0
// [Naming] lowercase + snake_case
// [PK] id (bigint)
// [Time] created_at / updated_at 기본 포함
// =====================================================

// 1. 사용자 및 계정
Table users {
  id bigint [pk, increment]
  email varchar [not null, unique, note: "로그인용 이메일"]
  name varchar [not null, note: "사용자 실명/활동명"]
  phone_number varchar [note: "연락처(선택)"]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]
}

// 2. 프로필 정보 (1:1)
Table profiles {
  id bigint [pk, increment]
  user_id bigint [not null, unique, note: "users 1:1"]
  image_url varchar [note: "프로필 대표 이미지(1장 교체형)"]
  bio text [note: "자기소개(자유서술)"]
  university varchar [note: "대학명"]
  major varchar [note: "학과/부가정보"]
  is_public boolean [not null, default: true, note: "전체 프로필 공개 여부"]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]
}

// 2-1. 프로필 증빙 서류 
Table profile_documents {
  id bigint [pk, increment]
  profile_id bigint [not null]
  file_url varchar [not null]
  file_name varchar
  file_type varchar [note: "pdf/image 등"]
  file_size bigint [note: "bytes"]
  status varchar [not null, default: 'pending', note: "pending/verified/rejected"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (profile_id) [name: "idx_profile_documents_profile_id"]
  }
}

// 2-2. 프로필 커스텀 링크/추가정보 
Table profile_extra_fields {
  id bigint [pk, increment]
  profile_id bigint [not null]
  label varchar [not null, note: "항목 이름 (예: github, portfolio)"]
  value varchar [not null, note: "URL 또는 텍스트"]
  value_type varchar [not null, default: 'link', note: "text/link"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (profile_id) [name: "idx_profile_extra_fields_profile_id"]
  }
}

// 3. 관심사 태그
Table interest_tags {
  id bigint [pk, increment]
  name varchar [not null, unique, note: "태그명 (기획/디자인/Spring 등)"]
}

Table user_interest_tags {
  id bigint [pk, increment]
  user_id bigint [not null]
  interest_tag_id bigint [not null]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (user_id, interest_tag_id) [unique, name: "uk_user_interest_tags"]
    (user_id) [name: "idx_user_interest_tags_user_id"]
    (interest_tag_id) [name: "idx_user_interest_tags_interest_tag_id"]
  }
}

// 4. 이력 관리 
Table experiences {
  id bigint [pk, increment]
  user_id bigint [not null]
  activity_name varchar [not null, note: "활동명"]
  organization varchar [note: "주최기관"]
  start_date date
  end_date date
  participation_type varchar [note: "참여형태 (개인/팀 등)"]
  job_role varchar [note: "직무 및 역할"]
  description text [note: "회고 및 성과 요약"]
  status varchar [not null, default: 'published', note: "draft(임시)/published(발행)"]
  is_public boolean [not null, default: true, note: "개별 이력 공개 여부"]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (user_id) [name: "idx_experiences_user_id"]
  }
}

Table experience_files {
  id bigint [pk, increment]
  experience_id bigint [not null]
  file_url varchar [not null]
  file_name varchar
  file_type varchar [note: "pdf/image 등"]
  file_size bigint [note: "bytes"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (experience_id) [name: "idx_experience_files_experience_id"]
  }
}

// 링크 첨부 대응
Table experience_links {
  id bigint [pk, increment]
  experience_id bigint [not null]
  url varchar [not null]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (experience_id) [name: "idx_experience_links_experience_id"]
  }
}

// 5. 게시판(모집글)
// 5-1. 직무 카테고리 (필터)
Table job_categories {
  id bigint [pk, increment]
  name varchar [not null, unique, note: "직무명(필터용)"]
}

// 5-2. 모집글
Table posts {
  id bigint [pk, increment]
  author_id bigint [not null, note: "작성자(users)"]
  job_category_id bigint [not null, note: "직무 카테고리(job_categories)"]
  title varchar [not null]
  content text [not null]
  recruit_count int [note: "모집 인원(선택)"]
  deadline date [note: "null일 경우 상시모집"]
  status varchar [not null, default: 'open', note: "open/closed"]
  created_at timestamp [not null, default: `now()`]
  updated_at timestamp [not null, default: `now()`]

  indexes {
    (job_category_id, created_at) [name: "idx_posts_category_created"]
    (author_id) [name: "idx_posts_author_id"]
  }
}

// 6. 쪽지/채팅
Table chat_rooms {
  id bigint [pk, increment]
  origin_post_id bigint [note: "어떤 모집글을 통해 시작된 대화인지(선택)"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (origin_post_id) [name: "idx_chat_rooms_origin_post_id"]
  }
}

Table chat_participants {
  id bigint [pk, increment]
  chat_room_id bigint [not null]
  user_id bigint [not null]
  last_read_message_id bigint [note: "안읽음 뱃지 계산용(마지막으로 읽은 메시지 id)"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (chat_room_id, user_id) [unique, name: "uk_chat_room_user"]
    (user_id) [name: "idx_chat_participants_user_id"]
  }
}

Table messages {
  id bigint [pk, increment]
  chat_room_id bigint [not null]
  sender_id bigint [not null]
  content text [not null]
  is_deleted boolean [not null, default: false, note: "삭제 정책 대비 플래그(선택)"]
  created_at timestamp [not null, default: `now()`]

  indexes {
    (chat_room_id, created_at) [name: "idx_messages_room_created"]
    (sender_id) [name: "idx_messages_sender_id"]
  }
}

// =====================================================
// Relationships (Foreign Keys)
// =====================================================

Ref: profiles.user_id > users.id

Ref: profile_documents.profile_id > profiles.id
Ref: profile_extra_fields.profile_id > profiles.id

Ref: user_interest_tags.user_id > users.id
Ref: user_interest_tags.interest_tag_id > interest_tags.id

Ref: experiences.user_id > users.id
Ref: experience_files.experience_id > experiences.id
Ref: experience_links.experience_id > experiences.id

Ref: posts.author_id > users.id
Ref: posts.job_category_id > job_categories.id
Ref: post_positions.post_id > posts.id

Ref: chat_rooms.origin_post_id > posts.id
Ref: chat_participants.chat_room_id > chat_rooms.id
Ref: chat_participants.user_id > users.id
Ref: messages.chat_room_id > chat_rooms.id
Ref: messages.sender_id > users.id