company-celebration/packages/server/prisma/schema.prisma

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

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

// Users table
model User {
  id         String   @id @default(cuid())
  name       String   @db.VarChar(100)
  avatar     String?  @db.VarChar(512)
  birthYear  Int?     @map("birth_year")
  zodiac     String?  @db.VarChar(20)
  isActive   Boolean  @default(true) @map("is_active")
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @updatedAt @map("updated_at")

  votes       Vote[]

  @@map("users")
}

// Candidates for voting
model Candidate {
  id          String   @id @default(cuid())
  name        String   @db.VarChar(100)
  department  String   @db.VarChar(100)
  avatar      String?  @db.VarChar(512)
  description String?  @db.Text
  category    String   @db.VarChar(50)
  isActive    Boolean  @default(true) @map("is_active")
  createdAt   DateTime @default(now()) @map("created_at")

  votes      Vote[]
  voteCounts VoteCount[]

  @@index([category])
  @@map("candidates")
}

// Individual votes
model Vote {
  id          String   @id @default(cuid())
  userId      String   @map("user_id")
  candidateId String   @map("candidate_id")
  category    String   @db.VarChar(50)
  localId     String?  @map("local_id") @db.VarChar(64)
  createdAt   DateTime @default(now()) @map("created_at")

  user      User      @relation(fields: [userId], references: [id])
  candidate Candidate @relation(fields: [candidateId], references: [id])

  @@unique([userId, category])
  @@index([category, candidateId])
  @@index([createdAt])
  @@map("votes")
}

// Aggregated vote counts (denormalized for performance)
model VoteCount {
  id          String   @id @default(cuid())
  candidateId String   @map("candidate_id")
  category    String   @db.VarChar(50)
  count       Int      @default(0)
  updatedAt   DateTime @updatedAt @map("updated_at")

  candidate Candidate @relation(fields: [candidateId], references: [id])

  @@unique([candidateId, category])
  @@index([category, count(sort: Desc)])
  @@map("vote_counts")
}

// Lucky draw results
model DrawResult {
  id               String   @id @default(cuid())
  drawId           String   @map("draw_id")
  prizeLevel       String   @map("prize_level") @db.VarChar(20)
  prizeName        String   @map("prize_name") @db.VarChar(100)
  winnerId         String   @map("winner_id") @db.VarChar(100)
  winnerName       String   @map("winner_name") @db.VarChar(100)
  winnerDepartment String   @map("winner_department") @db.VarChar(100)
  drawnAt          DateTime @default(now()) @map("drawn_at")
  drawnBy          String   @map("drawn_by") @db.VarChar(100)

  @@index([drawId])
  @@index([prizeLevel])
  @@index([winnerId])
  @@map("draw_results")
}

// Programs for voting
model Program {
  id        String   @id @default(cuid())
  name      String   @db.VarChar(100)
  teamName  String   @default("") @map("team_name") @db.VarChar(100)
  performer String   @default("") @db.VarChar(200)
  order     Int      @default(0)
  remark    String?  @db.Text
  isActive  Boolean  @default(true) @map("is_active")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@index([order])
  @@map("programs")
}

// Awards for voting
model Award {
  id        String   @id @default(cuid())
  name      String   @db.VarChar(100)
  icon      String   @db.VarChar(20)
  order     Int      @default(0)
  remark    String?  @db.Text
  isActive  Boolean  @default(true) @map("is_active")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@index([order])
  @@map("awards")
}

// Draw sessions
model DrawSession {
  id           String    @id @default(cuid())
  prizeLevel   String    @map("prize_level") @db.VarChar(20)
  prizeName    String    @map("prize_name") @db.VarChar(100)
  totalPrizes  Int       @map("total_prizes")
  drawnCount   Int       @default(0) @map("drawn_count")
  isActive     Boolean   @default(false) @map("is_active")
  filters      Json?
  startedAt    DateTime? @map("started_at")
  endedAt      DateTime? @map("ended_at")
  createdAt    DateTime  @default(now()) @map("created_at")

  @@index([prizeLevel])
  @@index([isActive])
  @@map("draw_sessions")
}