generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
}

// ============================================
// USERS
// ============================================
model User {
  id                        String    @id @default(uuid()) @db.Uuid
  email                     String    @unique
  password_hash             String
  role                      String // "candidate", "employer", "admin"
  name                      String
  first_name                String?
  last_name                 String?
  avatar_url                String?
  reset_token               String?
  reset_token_expiry        DateTime?
  email_verified            DateTime?
  verification_token        String?
  verification_token_expiry DateTime?
  is_active                 Boolean   @default(true)
  last_login_at             DateTime?
  created_at                DateTime  @default(now())
  updated_at                DateTime  @updatedAt
  deleted_at                DateTime? // Soft-delete flag

  // --- OAuth & Onboarding ---
  auth_provider         String    @default("credentials") // "credentials" | "google" | "linkedin"
  provider_id           String?   // OAuth provider user ID
  onboarding_completed  Boolean   @default(false)

  // --- Admin Security & RBAC Fields ---
  custom_permissions    String[] // Array of permission strings granted specifically to this user (overrides or adds to role defaults)
  department            String? // Context-based permission grouping (e.g., "Engineering", "HR")
  requires_2fa          Boolean   @default(false)
  two_factor_secret     String? // Encrypted secret or reference for Authenticator apps
  failed_login_attempts Int       @default(0)
  locked_until          DateTime?

  // Security Policies
  ip_whitelist       String[] // Array of allowed IP addresses for this user (empty means any IP)
  access_start_time  String? // HH:mm format, e.g., "09:00"
  access_end_time    String? // HH:mm format, e.g., "17:00"
  account_expires_at DateTime? // Temporary admin access

  // Relations
  profile        Profile?
  skills         UserSkill[]
  experiences    Experience[]
  education      Education[]
  jobs           Job[]          @relation("EmployerJobs")
  applications   Application[]  @relation("CandidateApplications")
  saved_jobs     SavedJob[]
  admin_sessions AdminSession[] // Links user to their active admin sessions
  audit_logs     AuditLog[] // Links user to their actions

  // Application System features
  sent_messages    ApplicationMessage[]  @relation("SentMessages")
  actor_activities ApplicationActivity[] @relation("ActorActivities")

  // Extended Profile Features
  certifications        Certification[]
  projects              Project[]
  awards                Award[]
  languages             Language[]
  publications          Publication[]
  volunteer_experiences VolunteerExperience[]

  // Notifications
  notifications         Notification[]
  notification_settings NotificationSetting[]

  // Interview Scheduling & Feedback
  interview_feedbacks      InterviewFeedback[]     @relation("InterviewerFeedback")
  created_interviews       Interview[]             @relation("InterviewCreator")
  interview_participations InterviewParticipant[]
  interview_availabilities InterviewAvailability[]
  interview_audit_logs     InterviewAuditLog[]

  // Message Templates
  message_templates MessageTemplate[]

  // Job Search & Discovery
  saved_searches   SavedSearch[]
  search_history   SearchHistory[]
  email_preference EmailPreference?

  // ATS / Pipeline Relations
  hiring_pipelines HiringPipeline[]
  stage_movements  CandidateStageHistory[]
  candidate_notes  CandidateNote[]
  candidate_tags   CandidateTag[]

  // Team Collaboration
  hiring_team_memberships HiringTeamMember[]
  team_comments_authored  TeamComment[]        @relation("CommentAuthor")
  team_comment_mentions   TeamCommentMention[] @relation("MentionedUser")

  // Transition Rules
  transition_overrides TransitionOverride[]

  // Messaging System
  conversations_created    Conversation[]            @relation("ConversationCreator")
  conversation_memberships ConversationParticipant[] @relation("ConversationMemberships")
  messages_sent            Message[]                 @relation("MessagesSent")
  message_read_receipts    MessageReadReceipt[]      @relation("MessageReads")
  message_reactions        MessageReaction[]         @relation("MessageReactions")

  @@map("users")
}

// ============================================
// PROFILES
// ============================================
model Profile {
  id                  String   @id @default(uuid()) @db.Uuid
  user_id             String   @unique @db.Uuid
  user                User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  headline            String?
  bio                 String?
  about_me            String?
  location            String?
  phone               String?
  resume_url          String?
  cover_letter        String?
  portfolio_url       String?
  linkedin_url        String?
  github_url          String?
  twitter_url         String?
  website_url         String?
  // Employer-specific
  company_name        String?
  company_website     String?
  company_size        String?
  company_industry    String?
  company_logo_url    String?
  company_description String?
  // Candidate job preferences (from onboarding)
  job_types            String[]  // e.g. ["Full-time", "Remote"]
  salary_expectation   String?   // e.g. "$80k–$120k"
  // Employer extra fields (from onboarding)
  company_location     String?   // HQ location
  hiring_for           String[]  // roles they want to hire
  created_at          DateTime @default(now())
  updated_at          DateTime @updatedAt

  @@index([user_id])
  @@map("profiles")
}

// ============================================
// SKILLS
// ============================================
model Skill {
  id         String      @id @default(uuid()) @db.Uuid
  name       String      @unique
  category   String?
  created_at DateTime    @default(now())
  users      UserSkill[]
  jobs       JobSkill[]

  @@map("skills")
}

model UserSkill {
  user_id  String @db.Uuid
  skill_id String @db.Uuid
  user     User   @relation(fields: [user_id], references: [id], onDelete: Cascade)
  skill    Skill  @relation(fields: [skill_id], references: [id], onDelete: Cascade)

  @@id([user_id, skill_id])
  @@map("user_skills")
}

// ============================================
// EXPERIENCE
// ============================================
model Experience {
  id          String    @id @default(uuid()) @db.Uuid
  user_id     String    @db.Uuid
  user        User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  title       String
  company     String
  location    String?
  start_date  DateTime  @db.Date
  end_date    DateTime? @db.Date
  is_current  Boolean   @default(false)
  description String?
  created_at  DateTime  @default(now())
  updated_at  DateTime  @updatedAt

  @@index([user_id])
  @@map("experiences")
}

// ============================================
// EDUCATION
// ============================================
model Education {
  id          String    @id @default(uuid()) @db.Uuid
  user_id     String    @db.Uuid
  user        User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  institution String
  degree      String
  field       String?
  start_date  DateTime? @db.Date
  end_date    DateTime? @db.Date
  description String?
  created_at  DateTime  @default(now())
  updated_at  DateTime  @updatedAt

  @@index([user_id])
  @@map("education")
}

// ============================================
// SYSTEM & TAXONOMY (Tags, Categories, Industries)
// ============================================
model Industry {
  id         String     @id @default(uuid()) @db.Uuid
  name       String     @unique
  categories Category[]
  jobs       Job[]
  created_at DateTime   @default(now())

  @@map("industries")
}

model Category {
  id          String    @id @default(uuid()) @db.Uuid
  name        String    @unique
  industry_id String?   @db.Uuid
  industry    Industry? @relation(fields: [industry_id], references: [id])
  jobs        Job[]
  created_at  DateTime  @default(now())

  @@index([industry_id])
  @@map("categories")
}

// ============================================
// JOBS
// ============================================
model Job {
  id          String @id @default(uuid()) @db.Uuid
  employer_id String @db.Uuid
  employer    User   @relation("EmployerJobs", fields: [employer_id], references: [id], onDelete: Cascade)

  // Basic Details
  title              String
  company            String
  category_id        String?   @db.Uuid
  category           Category? @relation(fields: [category_id], references: [id])
  industry_id        String?   @db.Uuid
  industry           Industry? @relation(fields: [industry_id], references: [id])
  experience_level   String? // "entry", "mid", "senior", "executive"
  number_of_openings Int       @default(1)
  type               String // "full-time", "part-time", "contract", "internship"

  // Location & Work Setup
  work_location_type     String  @default("on-site") // "on-site", "remote", "hybrid"
  address                String?
  city                   String?
  state                  String?
  country                String?
  zip_code               String?
  remote_geo_restriction String?
  time_zone_requirements String?
  days_in_office         Int?

  // Compensation
  salary_type             String? // "hourly", "annual", "contract", "commission"
  salary_min              Int?
  salary_max              Int?
  currency                String  @default("USD")
  hide_salary             Boolean @default(false)
  additional_compensation Json? // E.g., ["Bonus", "Equity", "Commission"]

  // Description & Requirements
  summary                  String? @db.VarChar(200)
  description              String? @db.Text // Rich HTML text
  responsibilities         Json? // JSON array of strings
  required_qualifications  Json? // JSON array of strings
  preferred_qualifications Json? // JSON array of strings
  benefits                 Json? // JSON array of strings

  // Application Settings
  application_method String    @default("internal") // "internal", "external", "email", "phone"
  external_url       String?
  contact_email      String?
  deadline           DateTime?

  // Meta & State
  status          String    @default("draft") // "draft", "published", "paused", "closed"
  is_featured     Boolean   @default(false)
  applicant_count Int       @default(0)
  view_count      Int       @default(0)
  posted_at       DateTime  @default(now())
  updated_at      DateTime  @updatedAt
  deleted_at      DateTime? // Soft-delete flag

  // Discovery & SEO
  seo_title        String?
  meta_description String?
  slug             String? @unique

  // Internal Tracking & Pipeline
  internal_notes  String?
  hiring_pipeline HiringPipeline?

  // Relations
  skills              JobSkill[]
  applications        Application[]
  interviews          Interview[]
  saved_by            SavedJob[]
  screening_questions ScreeningQuestion[]
  hiring_team         HiringTeamMember[]
  job_views           JobView[]

  @@index([employer_id])
  @@index([category_id])
  @@index([industry_id])
  @@map("jobs")
}

model JobView {
  id         String   @id @default(uuid()) @db.Uuid
  job_id     String   @db.Uuid
  job        Job      @relation(fields: [job_id], references: [id], onDelete: Cascade)
  user_id    String?  @db.Uuid // Nullable for anonymous viewers
  ip_address String? // Optional for deduplication
  user_agent String?
  source     String? // 'direct', 'search', 'email', 'social'
  created_at DateTime @default(now())

  @@index([job_id, created_at])
  @@map("job_views")
}

model JobSkill {
  job_id      String  @db.Uuid
  skill_id    String  @db.Uuid
  job         Job     @relation(fields: [job_id], references: [id], onDelete: Cascade)
  skill       Skill   @relation(fields: [skill_id], references: [id], onDelete: Cascade)
  is_required Boolean @default(true) // Ability to mark a skill as required vs preferred

  @@id([job_id, skill_id])
  @@map("job_skills")
}

model ScreeningQuestion {
  id            String  @id @default(uuid()) @db.Uuid
  job_id        String  @db.Uuid
  job           Job     @relation(fields: [job_id], references: [id], onDelete: Cascade)
  question_text String  @db.Text
  question_type String // "short_answer", "long_answer", "multiple_choice", "checkbox", "dropdown", "link", "file"
  is_required   Boolean @default(true)
  order         Int     @default(0)
  options       Json? // JSON array of options if type is multiple_choice/dropdown
  ideal_answer  String? // Optional field for AI or internal scoring

  @@map("screening_questions")
}

// ============================================
// APPLICATIONS
// ============================================
model Application {
  id           String @id @default(uuid()) @db.Uuid
  job_id       String @db.Uuid
  job          Job    @relation(fields: [job_id], references: [id], onDelete: Cascade)
  candidate_id String @db.Uuid
  candidate    User   @relation("CandidateApplications", fields: [candidate_id], references: [id], onDelete: Cascade)
  status       String @default("pending") // "pending", "reviewing", "interview", "offered", "rejected"

  // Standard Fields
  cover_letter        String?
  resume_url          String?
  cv_link             String?
  years_of_experience Int?

  // Custom Answers to Screening Questions
  answers Json? // Stores responses relative to question IDs

  notes             String?
  applied_at        DateTime @default(now())
  updated_at        DateTime @updatedAt
  employer_notified Boolean  @default(false)

  // Advanced Features
  match_score            Float?
  is_withdrawn           Boolean   @default(false)
  expected_response_date DateTime?

  // ATS / Employer Review
  rating    Int? // 1-5 star rating from employer
  tags      String[] // Legacy custom tags (will be replaced by ApplicationTag)
  viewed_at DateTime? // When employer first viewed this application
  source    String? // "platform_search", "direct", "referral", "job_board"

  // Pipeline Tracking & Scoring
  current_stage_id String?                 @db.Uuid
  current_stage    HiringStage?            @relation(fields: [current_stage_id], references: [id], onDelete: SetNull)
  stage_history    CandidateStageHistory[]
  candidate_notes  CandidateNote[]
  application_tags ApplicationTag[]
  score            CandidateScore?

  // Relations
  messages      ApplicationMessage[]
  interviews    Interview[]
  offer         ApplicationOffer?
  activities    ApplicationActivity[]
  team_comments TeamComment[]

  // Messaging
  conversation Conversation? @relation("ApplicationConversation")

  @@unique([job_id, candidate_id])
  @@index([job_id])
  @@index([candidate_id])
  @@index([current_stage_id])
  @@map("applications")
}

model ApplicationMessage {
  id             String      @id @default(uuid()) @db.Uuid
  application_id String      @db.Uuid
  application    Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  sender_id      String      @db.Uuid
  sender         User        @relation("SentMessages", fields: [sender_id], references: [id], onDelete: Cascade)
  message        String      @db.Text
  message_type   String      @default("text") // "text", "system", "template"
  attachment_url String?
  is_starred     Boolean     @default(false)
  is_archived    Boolean     @default(false)
  read_at        DateTime?
  created_at     DateTime    @default(now())

  @@map("application_messages")
}

model MessageTemplate {
  id           String   @id @default(uuid()) @db.Uuid
  user_id      String   @db.Uuid
  user         User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  title        String
  content      String   @db.Text
  category     String   @default("custom") // "interview_invite", "status_update", "rejection", "offer", "thank_you", "follow_up", "custom"
  placeholders Json? // Array of placeholder keys: ["name", "position", "company"]
  is_default   Boolean  @default(false)
  created_at   DateTime @default(now())
  updated_at   DateTime @updatedAt

  @@map("message_templates")
}

model Interview {
  id                 String         @id @default(uuid()) @db.Uuid
  uuid               String         @unique @default(uuid()) @db.Uuid
  job_id             String         @db.Uuid
  job                Job            @relation(fields: [job_id], references: [id], onDelete: Cascade)
  application_id     String         @db.Uuid
  application        Application    @relation(fields: [application_id], references: [id], onDelete: Cascade)
  created_by_user_id String         @db.Uuid
  created_by         User           @relation("InterviewCreator", fields: [created_by_user_id], references: [id], onDelete: Cascade)
  interview_type_id  String?        @db.Uuid
  interview_type     InterviewType? @relation(fields: [interview_type_id], references: [id], onDelete: SetNull)

  // Timing
  scheduled_start_datetime DateTime
  scheduled_end_datetime   DateTime
  timezone                 String   @default("UTC")

  // Details
  type                  String  @default("video") // 'phone', 'video', 'in_person', 'panel'
  status                String  @default("scheduled") // 'scheduled', 'confirmed', 'completed', 'cancelled', 'no_show', 'rescheduled'
  location              String? @db.Text
  video_conference_link String?
  phone_number          String?

  // Metadata
  title                    String
  description              String? @db.Text
  internal_notes           String? @db.Text
  preparation_instructions String? @db.Text

  // Tracking
  created_at   DateTime  @default(now())
  updated_at   DateTime  @updatedAt
  confirmed_at DateTime?
  cancelled_at DateTime?
  completed_at DateTime?

  // Relations
  participants InterviewParticipant[]
  feedbacks    InterviewFeedback[]
  audit_logs   InterviewAuditLog[]

  @@index([scheduled_start_datetime])
  @@index([status])
  @@index([application_id])
  @@index([job_id])
  @@map("interviews")
}

model InterviewParticipant {
  id                String    @id @default(uuid()) @db.Uuid
  interview_id      String    @db.Uuid
  interview         Interview @relation(fields: [interview_id], references: [id], onDelete: Cascade)
  user_id           String    @db.Uuid
  user              User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  role              String // 'candidate', 'interviewer', 'coordinator', 'observer'
  required          Boolean   @default(true)
  attendance_status String    @default("pending") // 'pending', 'confirmed', 'attended', 'no_show', 'declined'
  confirmed_at      DateTime?
  response          String    @default("no_response") // 'accepted', 'declined', 'tentative', 'no_response'

  @@unique([interview_id, user_id])
  @@index([interview_id])
  @@index([user_id])
  @@map("interview_participants")
}

model InterviewAvailability {
  id              String   @id @default(uuid()) @db.Uuid
  user_id         String   @db.Uuid
  user            User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  available_start DateTime // Stored in UTC
  available_end   DateTime // Stored in UTC
  recurring       Boolean  @default(false)
  day_of_week     Int? // 0-6 (0 = Sunday)
  timezone        String   @default("UTC")

  @@index([user_id])
  @@index([available_start, available_end])
  @@map("interview_availability")
}

model InterviewType {
  id                         String  @id @default(uuid()) @db.Uuid
  name                       String
  default_duration_minutes   Int     @default(60)
  default_format             String  @default("video")
  default_interviewers_count Int     @default(1)
  description                String? @db.Text
  preparation_template       String? @db.Text
  is_active                  Boolean @default(true)

  interviews Interview[]

  @@map("interview_types")
}

model InterviewFeedback {
  id                      String    @id @default(uuid()) @db.Uuid
  interview_id            String    @db.Uuid
  interview               Interview @relation(fields: [interview_id], references: [id], onDelete: Cascade)
  interviewer_user_id     String    @db.Uuid
  interviewer             User      @relation("InterviewerFeedback", fields: [interviewer_user_id], references: [id], onDelete: Cascade)
  overall_rating          Int? // 1-5
  technical_skills_rating Int? // 1-5
  cultural_fit_rating     Int? // 1-5
  comments                String?   @db.Text
  submitted_at            DateTime  @default(now())

  @@unique([interview_id, interviewer_user_id])
  @@map("interview_feedback")
}

model InterviewAuditLog {
  id           String    @id @default(uuid()) @db.Uuid
  interview_id String    @db.Uuid
  interview    Interview @relation(fields: [interview_id], references: [id], onDelete: Cascade)
  user_id      String    @db.Uuid
  user         User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  action       String // 'created', 'rescheduled', 'cancelled', 'confirmed', 'completed'
  old_value    Json?
  new_value    Json?
  timestamp    DateTime  @default(now())
  ip_address   String?

  @@index([interview_id])
  @@map("interview_audit_log")
}

model ApplicationOffer {
  id                 String      @id @default(uuid()) @db.Uuid
  application_id     String      @unique @db.Uuid
  application        Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  base_salary        Int?
  currency           String      @default("USD")
  benefits           Json?
  full_offer_details String?     @db.Text
  response_deadline  DateTime?
  status             String      @default("pending") // "pending", "accepted", "declined", "negotiated"
  created_at         DateTime    @default(now())
  updated_at         DateTime    @updatedAt

  @@map("application_offers")
}

model ApplicationActivity {
  id             String      @id @default(uuid()) @db.Uuid
  application_id String      @db.Uuid
  application    Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  actor_id       String?     @db.Uuid
  actor          User?       @relation("ActorActivities", fields: [actor_id], references: [id], onDelete: SetNull)
  action         String
  details        Json?
  created_at     DateTime    @default(now())

  @@index([application_id])
  @@map("application_activities")
}

// ============================================
// SAVED JOBS
// ============================================
model SavedJob {
  user_id  String   @db.Uuid
  job_id   String   @db.Uuid
  user     User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  job      Job      @relation(fields: [job_id], references: [id], onDelete: Cascade)
  saved_at DateTime @default(now())

  @@id([user_id, job_id])
  @@map("saved_jobs")
}

// ============================================
// ADMIN SECURITY & TRACKING
// ============================================

model AdminSession {
  id            String   @id @default(uuid()) @db.Uuid
  user_id       String   @db.Uuid
  user          User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  token         String   @unique
  ip_address    String?
  user_agent    String?
  device_id     String?
  status        String   @default("active") // "active", "2fa_required", "expired", "revoked"
  expires_at    DateTime
  last_activity DateTime @default(now())
  created_at    DateTime @default(now())

  @@map("admin_sessions")
}

model AuditLog {
  id            String  @id @default(uuid()) @db.Uuid
  user_id       String? @db.Uuid // Nullable if action was system-generated or by a deleted user
  user          User?   @relation(fields: [user_id], references: [id], onDelete: SetNull)
  action        String // Context-specific action (e.g., "job.approved", "user.suspended", "admin.login")
  resource_type String // e.g., "job", "user", "setting"
  resource_id   String? // ID of the touched resource
  details       Json? // Detailed payload: before/after states, reason, category, severity
  ip_address    String?
  user_agent    String?

  // --- Security & Cryptographic Integrity ---
  hash         String  @default("") // SHA-256 hash of the log contents for WORM compliance
  previousHash String? // Hash of the chronologically previous log to form a blockchain

  // --- Lifecycle & Retention ---
  storageTier String @default("HOT") // "HOT", "WARM", "COLD"

  created_at DateTime @default(now())

  // We add indexes to support chain verification and archival
  @@index([action])
  @@index([resource_type, resource_id])
  @@index([created_at(sort: Desc)]) // Crucial for finding previousHash efficiently
  @@map("audit_logs")
}

// ============================================
// PROFILE EXTENSIONS
// ============================================

model Certification {
  id              String    @id @default(uuid()) @db.Uuid
  user_id         String    @db.Uuid
  user            User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  name            String
  organization    String
  issue_date      DateTime? @db.Date
  expiration_date DateTime? @db.Date
  credential_id   String?
  credential_url  String?
  created_at      DateTime  @default(now())
  updated_at      DateTime  @updatedAt

  @@map("certifications")
}

model Project {
  id          String    @id @default(uuid()) @db.Uuid
  user_id     String    @db.Uuid
  user        User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  title       String
  description String?   @db.Text
  role        String?
  url         String?
  start_date  DateTime? @db.Date
  end_date    DateTime? @db.Date
  created_at  DateTime  @default(now())
  updated_at  DateTime  @updatedAt

  @@map("projects")
}

model Award {
  id           String    @id @default(uuid()) @db.Uuid
  user_id      String    @db.Uuid
  user         User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  title        String
  organization String
  date         DateTime? @db.Date
  description  String?   @db.Text
  created_at   DateTime  @default(now())
  updated_at   DateTime  @updatedAt

  @@map("awards")
}

model Language {
  id          String   @id @default(uuid()) @db.Uuid
  user_id     String   @db.Uuid
  user        User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  name        String
  proficiency String // "Basic", "Conversational", "Professional", "Native"
  created_at  DateTime @default(now())
  updated_at  DateTime @updatedAt

  @@map("languages")
}

model Publication {
  id          String    @id @default(uuid()) @db.Uuid
  user_id     String    @db.Uuid
  user        User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  title       String
  publisher   String
  date        DateTime? @db.Date
  url         String?
  description String?   @db.Text
  created_at  DateTime  @default(now())
  updated_at  DateTime  @updatedAt

  @@map("publications")
}

model VolunteerExperience {
  id           String    @id @default(uuid()) @db.Uuid
  user_id      String    @db.Uuid
  user         User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  organization String
  role         String
  start_date   DateTime? @db.Date
  end_date     DateTime? @db.Date
  description  String?   @db.Text
  created_at   DateTime  @default(now())
  updated_at   DateTime  @updatedAt

  @@map("volunteer_experiences")
}

// ============================================
// JOB SEARCH & DISCOVERY
// ============================================

model SavedSearch {
  id                    String    @id @default(uuid()) @db.Uuid
  user_id               String    @db.Uuid
  user                  User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  name                  String
  filters               Json // Serialized filter state
  alert_frequency       String    @default("none") // "none", "instant", "daily", "weekly"
  new_job_count         Int       @default(0)
  last_checked_at       DateTime  @default(now())
  is_active             Boolean   @default(true)
  paused_until          DateTime?
  max_results_per_alert Int       @default(10)
  min_match_score       Int       @default(70) // 0-100, only send jobs above this score
  created_at            DateTime  @default(now())
  updated_at            DateTime  @updatedAt

  // Relations
  matches JobAlertMatch[]

  @@index([user_id])
  @@index([alert_frequency, is_active])
  @@map("saved_searches")
}

model JobAlertMatch {
  id              String      @id @default(uuid()) @db.Uuid
  saved_search_id String      @db.Uuid
  saved_search    SavedSearch @relation(fields: [saved_search_id], references: [id], onDelete: Cascade)
  job_id          String      @db.Uuid
  match_score     Float // 0-100 relevance score
  matched_at      DateTime    @default(now())
  emailed         Boolean     @default(false)
  emailed_at      DateTime?
  user_action     String? // "viewed", "applied", "ignored"

  @@unique([saved_search_id, job_id])
  @@index([saved_search_id, emailed])
  @@map("job_alert_matches")
}

model SearchHistory {
  id            String   @id @default(uuid()) @db.Uuid
  user_id       String   @db.Uuid
  user          User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  query         String
  filters       Json?
  results_count Int      @default(0)
  created_at    DateTime @default(now())

  @@index([user_id, created_at(sort: Desc)])
  @@map("search_history")
}

// ============================================
// NOTIFICATIONS
// ============================================

model Notification {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @db.Uuid
  user       User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  type       String // "application", "message", "system", "job_alert"
  title      String
  message    String   @db.Text
  link       String?
  is_read    Boolean  @default(false)
  priority   String   @default("medium") // "low", "medium", "high", "critical"
  created_at DateTime @default(now())

  @@map("notifications")
}

model NotificationSetting {
  id       String  @id @default(uuid()) @db.Uuid
  user_id  String  @db.Uuid
  user     User    @relation(fields: [user_id], references: [id], onDelete: Cascade)
  category String // "applications", "jobs", "messages", "security"
  in_app   Boolean @default(true)
  email    String  @default("instant") // "off", "instant", "daily", "weekly"
  sms      Boolean @default(false)
  push     Boolean @default(false)

  @@unique([user_id, category])
  @@map("notification_settings")
}

// ============================================
// APPLICANT TRACKING SYSTEM (ATS)
// ============================================

model HiringPipeline {
  id               String           @id @default(uuid()) @db.Uuid
  employer_id      String           @db.Uuid
  employer         User             @relation(fields: [employer_id], references: [id], onDelete: Cascade)
  job_id           String?          @unique @db.Uuid // If null, it's a default pipeline for the employer
  job              Job?             @relation(fields: [job_id], references: [id], onDelete: Cascade)
  name             String           @default("Default Pipeline")
  is_default       Boolean          @default(false)
  stages           HiringStage[]
  transition_rules TransitionRule[]
  created_at       DateTime         @default(now())
  updated_at       DateTime         @updatedAt

  @@map("hiring_pipelines")
}

model HiringStage {
  id                     String         @id @default(uuid()) @db.Uuid
  pipeline_id            String         @db.Uuid
  pipeline               HiringPipeline @relation(fields: [pipeline_id], references: [id], onDelete: Cascade)
  name                   String
  order                  Int
  color                  String         @default("#94a3b8") // Default Tailwind slate-400
  stage_type             String         @default("interview")
  description            String?        @db.Text
  expected_duration_days Int?
  actions                Json? // Options like ["require_score", "require_notes"]
  automations            Json? // Triggers like {"auto_email": "template_id"}
  created_at             DateTime       @default(now())
  updated_at             DateTime       @updatedAt

  // Current applications in this stage
  applications Application[]

  // Historical records of applications passing through this stage
  history CandidateStageHistory[]

  @@map("hiring_stages")
}

model CandidateStageHistory {
  id             String      @id @default(uuid()) @db.Uuid
  application_id String      @db.Uuid
  application    Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  stage_id       String      @db.Uuid
  stage          HiringStage @relation(fields: [stage_id], references: [id], onDelete: Cascade)
  actor_id       String?     @db.Uuid // Admin/Employer who moved them
  actor          User?       @relation(fields: [actor_id], references: [id], onDelete: SetNull)
  notes          String?     @db.Text
  entered_at     DateTime    @default(now())
  left_at        DateTime?
  duration_days  Int? // Calculated days spent in this stage

  @@index([application_id])
  @@index([stage_id])
  @@map("candidate_stage_history")
}

model CandidateNote {
  id             String      @id @default(uuid()) @db.Uuid
  application_id String      @db.Uuid
  application    Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  author_id      String      @db.Uuid
  author         User        @relation(fields: [author_id], references: [id], onDelete: Cascade)
  content        String      @db.Text
  is_private     Boolean     @default(false) // Toggle: Private to author or shared with hiring team
  mentions       Json? // Optional array of user IDs mentioned
  attachments    Json? // Links or file URLs
  created_at     DateTime    @default(now())
  updated_at     DateTime    @updatedAt

  @@index([application_id])
  @@map("candidate_notes")
}

model CandidateTag {
  id          String   @id @default(uuid()) @db.Uuid
  employer_id String   @db.Uuid // Tags belong to an employer to reuse across their jobs
  employer    User     @relation(fields: [employer_id], references: [id], onDelete: Cascade)
  name        String
  color       String   @default("#e2e8f0")
  created_at  DateTime @default(now())

  // Relations
  applications ApplicationTag[]

  @@unique([employer_id, name])
  @@map("candidate_tags")
}

model ApplicationTag {
  application_id String       @db.Uuid
  tag_id         String       @db.Uuid
  application    Application  @relation(fields: [application_id], references: [id], onDelete: Cascade)
  tag            CandidateTag @relation(fields: [tag_id], references: [id], onDelete: Cascade)
  added_at       DateTime     @default(now())

  @@id([application_id, tag_id])
  @@map("application_tags")
}

model CandidateScore {
  id              String      @id @default(uuid()) @db.Uuid
  application_id  String      @unique @db.Uuid
  application     Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  overall_score   Float // 0 to 100 percentage
  technical_fit   Float?
  experience_fit  Float?
  education_fit   Float?
  skills_match    Json? // e.g. {"matched": ["React", "Node"], "missing": ["AWS"]}
  justification   String?     @db.Text // AI reasoning for the score
  is_ai_generated Boolean     @default(true)
  created_at      DateTime    @default(now())
  updated_at      DateTime    @updatedAt

  @@index([overall_score])
  @@map("candidate_scores")
}

// ============================================
// HIRING TEAM COLLABORATION
// ============================================
model HiringTeamMember {
  id          String    @id @default(uuid()) @db.Uuid
  job_id      String    @db.Uuid
  job         Job       @relation(fields: [job_id], references: [id], onDelete: Cascade)
  user_id     String    @db.Uuid
  user        User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  role        String    @default("interviewer") // "recruiter", "hiring_manager", "interviewer", "hr_admin", "executive"
  permissions Json? // Optional override permissions JSON
  invited_at  DateTime  @default(now())
  accepted_at DateTime?
  is_active   Boolean   @default(true)

  @@unique([job_id, user_id])
  @@index([user_id])
  @@map("hiring_team_members")
}

model TeamComment {
  id             String               @id @default(uuid()) @db.Uuid
  application_id String               @db.Uuid
  application    Application          @relation(fields: [application_id], references: [id], onDelete: Cascade)
  author_id      String               @db.Uuid
  author         User                 @relation("CommentAuthor", fields: [author_id], references: [id], onDelete: Cascade)
  content        String               @db.Text
  parent_id      String?              @db.Uuid
  parent         TeamComment?         @relation("CommentReplies", fields: [parent_id], references: [id], onDelete: Cascade)
  replies        TeamComment[]        @relation("CommentReplies")
  mentions       TeamCommentMention[]
  is_edited      Boolean              @default(false)
  created_at     DateTime             @default(now())
  updated_at     DateTime             @updatedAt

  @@index([application_id])
  @@map("team_comments")
}

model TeamCommentMention {
  id         String      @id @default(uuid()) @db.Uuid
  comment_id String      @db.Uuid
  comment    TeamComment @relation(fields: [comment_id], references: [id], onDelete: Cascade)
  user_id    String      @db.Uuid
  user       User        @relation("MentionedUser", fields: [user_id], references: [id], onDelete: Cascade)

  @@unique([comment_id, user_id])
  @@map("team_comment_mentions")
}

// ============================================
// STAGE TRANSITION RULES
// ============================================
model TransitionRule {
  id             String         @id @default(uuid()) @db.Uuid
  pipeline_id    String         @db.Uuid
  pipeline       HiringPipeline @relation(fields: [pipeline_id], references: [id], onDelete: Cascade)
  name           String
  description    String?        @db.Text
  rule_type      String         @default("requirement") // "requirement", "auto_transition", "notification", "validation"
  from_stage_id  String?        @db.Uuid // null = any stage
  to_stage_id    String?        @db.Uuid // null = any stage
  conditions     Json // [{field, operator, value}]
  actions        Json? // [{type, params}]
  is_active      Boolean        @default(true)
  priority       Int            @default(0) // Higher = evaluated first
  allow_override Boolean        @default(true)
  override_roles String[] // Roles that can override: ["hiring_manager", "hr_admin"]
  created_at     DateTime       @default(now())
  updated_at     DateTime       @updatedAt

  overrides TransitionOverride[]

  @@index([pipeline_id])
  @@map("transition_rules")
}

model TransitionOverride {
  id             String         @id @default(uuid()) @db.Uuid
  rule_id        String         @db.Uuid
  rule           TransitionRule @relation(fields: [rule_id], references: [id], onDelete: Cascade)
  application_id String         @db.Uuid
  overridden_by  String         @db.Uuid
  user           User           @relation(fields: [overridden_by], references: [id], onDelete: Cascade)
  reason         String         @db.Text
  from_stage     String // Stage name snapshot
  to_stage       String // Stage name snapshot
  created_at     DateTime       @default(now())

  @@index([application_id])
  @@map("transition_overrides")
}

// ============================================
// MESSAGING SYSTEM
// ============================================

model Conversation {
  id             String       @id @default(uuid()) @db.Uuid
  type           String       @default("direct") // "direct", "group", "application"
  title          String? // Optional name for group chats
  application_id String?      @unique @db.Uuid // Links to Application for app-scoped convos
  application    Application? @relation("ApplicationConversation", fields: [application_id], references: [id], onDelete: SetNull)
  created_by     String       @db.Uuid
  creator        User         @relation("ConversationCreator", fields: [created_by], references: [id], onDelete: Cascade)

  // Denormalized for performance (fast conversation list loading)
  last_message_at      DateTime?
  last_message_preview String?   @db.VarChar(200)

  status     String   @default("active") // "active", "archived", "deleted"
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  // Relations
  participants ConversationParticipant[]
  messages     Message[]

  @@index([created_by])
  @@index([last_message_at(sort: Desc)])
  @@index([application_id])
  @@map("conversations")
}

model ConversationParticipant {
  id              String       @id @default(uuid()) @db.Uuid
  conversation_id String       @db.Uuid
  conversation    Conversation @relation(fields: [conversation_id], references: [id], onDelete: Cascade)
  user_id         String       @db.Uuid
  user            User         @relation("ConversationMemberships", fields: [user_id], references: [id], onDelete: Cascade)

  role      String    @default("member") // "creator", "admin", "member"
  joined_at DateTime  @default(now())
  left_at   DateTime?
  added_by  String?   @db.Uuid

  // Per-user settings
  is_muted             Boolean   @default(false)
  is_pinned            Boolean   @default(false)
  unread_count         Int       @default(0)
  last_read_at         DateTime?
  last_read_message_id String?   @db.Uuid

  @@unique([conversation_id, user_id])
  @@index([user_id, last_read_at(sort: Desc)])
  @@map("conversation_participants")
}

model Message {
  id              String       @id @default(uuid()) @db.Uuid
  conversation_id String       @db.Uuid
  conversation    Conversation @relation(fields: [conversation_id], references: [id], onDelete: Cascade)
  sender_id       String       @db.Uuid
  sender          User         @relation("MessagesSent", fields: [sender_id], references: [id], onDelete: Cascade)

  content      String @db.Text
  message_type String @default("text") // "text", "system", "file", "template"

  // Threading
  reply_to_id String?   @db.Uuid
  reply_to    Message?  @relation("MessageReplies", fields: [reply_to_id], references: [id], onDelete: SetNull)
  replies     Message[] @relation("MessageReplies")

  // Edit/Delete tracking
  is_edited  Boolean   @default(false)
  edited_at  DateTime?
  is_deleted Boolean   @default(false)
  deleted_at DateTime?
  deleted_by String?   @db.Uuid

  // Metadata (mentions, link previews, etc.)
  metadata Json?

  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  // Relations
  attachments   MessageAttachment[]
  read_receipts MessageReadReceipt[]
  reactions     MessageReaction[]

  @@index([conversation_id, created_at(sort: Desc)])
  @@index([sender_id])
  @@map("messages")
}

model MessageAttachment {
  id         String  @id @default(uuid()) @db.Uuid
  message_id String  @db.Uuid
  message    Message @relation(fields: [message_id], references: [id], onDelete: Cascade)

  filename      String
  file_url      String // Local path for now, cloud URL after migration
  file_size     Int // bytes
  mime_type     String
  thumbnail_url String?

  uploaded_by String   @db.Uuid
  created_at  DateTime @default(now())

  @@index([message_id])
  @@map("message_attachments")
}

model MessageReadReceipt {
  id         String   @id @default(uuid()) @db.Uuid
  message_id String   @db.Uuid
  message    Message  @relation(fields: [message_id], references: [id], onDelete: Cascade)
  user_id    String   @db.Uuid
  user       User     @relation("MessageReads", fields: [user_id], references: [id], onDelete: Cascade)
  read_at    DateTime @default(now())

  @@unique([message_id, user_id])
  @@index([user_id])
  @@map("message_read_receipts")
}

model MessageReaction {
  id         String   @id @default(uuid()) @db.Uuid
  message_id String   @db.Uuid
  message    Message  @relation(fields: [message_id], references: [id], onDelete: Cascade)
  user_id    String   @db.Uuid
  user       User     @relation("MessageReactions", fields: [user_id], references: [id], onDelete: Cascade)
  reaction   String // emoji short code: "thumbsup", "heart", etc.
  created_at DateTime @default(now())

  @@unique([message_id, user_id, reaction])
  @@index([message_id])
  @@map("message_reactions")
}

// ============================================
// EMAIL PREFERENCES & SUPPRESSION
// ============================================

model EmailPreference {
  id                 String    @id @default(uuid()) @db.Uuid
  user_id            String    @unique @db.Uuid
  user               User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  global_pause_until DateTime?
  quiet_hours_start  String? // HH:mm format e.g. "22:00"
  quiet_hours_end    String? // HH:mm format e.g. "08:00"
  unsubscribed_all   Boolean   @default(false)
  digest_time        String    @default("09:00") // Preferred time for daily digests
  timezone           String    @default("Africa/Lagos")
  created_at         DateTime  @default(now())
  updated_at         DateTime  @updatedAt

  @@map("email_preferences")
}

model EmailSuppression {
  id            String   @id @default(uuid()) @db.Uuid
  email         String   @unique
  reason        String // "unsubscribe", "bounce", "complaint"
  is_permanent  Boolean  @default(false)
  suppressed_at DateTime @default(now())

  @@map("email_suppressions")
}

// ============================================
// EMAIL NOTIFICATION SYSTEM
// ============================================

model AdminEmailTemplate {
  id         String   @id @default(uuid()) @db.Uuid
  type       String   @unique // The internal identifier: "welcome", "password_reset", "job_approved", etc.
  name       String // Human readable name
  subject    String // The email subject line (can contain {{variables}})
  html_body  String   @db.Text // The HTML body of the email with Handlebars variables
  is_active  Boolean  @default(true) // Whether to use this or fallback to code templates
  variables  Json? // Array of available variables for this template type e.g. ["firstName", "resetUrl"]
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  @@map("admin_email_templates")
}

model EmailQueue {
  id            String    @id @default(uuid()) @db.Uuid
  type          String // The template type to use
  to            String // The recipient email
  user_id       String?   @db.Uuid // Optional link to user who triggered it
  payload       Json // The dynamic data to inject into the template
  status        String    @default("PENDING") // PENDING, PROCESSING, SENT, FAILED, SKIPPED, DEAD
  priority      Int       @default(3) // 1=critical, 2=high, 3=normal, 4=low
  category      String    @default("transactional") // transactional, marketing, alert, digest
  attempts      Int       @default(0) // Number of times tried
  max_attempts  Int       @default(3) // Per-email retry limit
  last_error    String?   @db.Text
  scheduled_for DateTime? // For delayed/backoff sends (null = send immediately)
  created_at    DateTime  @default(now())
  updated_at    DateTime  @updatedAt

  @@index([status, priority, scheduled_for]) // Optimize priority-ordered fetching
  @@index([status, created_at])
  @@map("email_queue")
}

model EmailLog {
  id              String  @id @default(uuid()) @db.Uuid
  queue_id        String? @db.Uuid // Link to the original queue item
  type            String
  to              String
  user_id         String? @db.Uuid
  subject         String?
  status          String // SENT, FAILED, DELIVERED, OPENED, CLICKED, BOUNCED, COMPLAINED
  delivery_status String  @default("sent") // created, queued, sent, delivered, opened, clicked, bounced, complained
  provider_id     String? // ID returned by the ESP
  error           String? @db.Text

  // Delivery tracking timestamps
  sent_at        DateTime  @default(now())
  delivered_at   DateTime?
  opened_at      DateTime?
  last_opened_at DateTime?
  clicked_at     DateTime?
  bounced_at     DateTime?
  complained_at  DateTime?

  // Engagement metrics
  open_count  Int @default(0)
  click_count Int @default(0)

  // Bounce classification
  bounce_type   String? // "hard", "soft"
  bounce_reason String? @db.Text

  // Client info (from tracking pixel / click)
  user_agent String?
  ip_address String?

  // Relations
  events EmailEvent[]

  @@index([to, type])
  @@index([user_id, sent_at])
  @@index([delivery_status, sent_at])
  @@index([type, sent_at])
  @@map("email_logs")
}

// Time-series event log for granular tracking
model EmailEvent {
  id           String   @id @default(uuid()) @db.Uuid
  email_log_id String   @db.Uuid
  event_type   String // "delivered", "opened", "clicked", "bounced", "complained", "unsubscribed"
  metadata     Json? // { url, user_agent, ip, bounce_reason, bounce_code, etc. }
  created_at   DateTime @default(now())

  email_log EmailLog @relation(fields: [email_log_id], references: [id], onDelete: Cascade)

  @@index([email_log_id, event_type])
  @@index([event_type, created_at])
  @@map("email_events")
}

// Daily aggregated stats for dashboard charts
model EmailDeliveryStats {
  id           String   @id @default(uuid()) @db.Uuid
  date         DateTime @db.Date
  email_type   String // "welcome", "job_alert", "all", etc.
  sent         Int      @default(0)
  delivered    Int      @default(0)
  opened       Int      @default(0)
  clicked      Int      @default(0)
  bounced_hard Int      @default(0)
  bounced_soft Int      @default(0)
  complained   Int      @default(0)
  unsubscribed Int      @default(0)
  failed       Int      @default(0)
  skipped      Int      @default(0)

  @@unique([date, email_type])
  @@index([date])
  @@map("email_delivery_stats")
}
