euchre_camp/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
}

model Player {
  id                    Int                @id @default(autoincrement())
  name                  String
  rating                Int                @default(0)
  currentElo            Int                @default(1000)
  gamesPlayed           Int                @default(0)
  wins                  Int                @default(0)
  losses                Int                @default(0)
  createdAt             DateTime           @default(now())
  updatedAt             DateTime           @updatedAt
  normalizedName        String             @unique
  eloSnapshots          EloSnapshot[]
  eventParticipants     EventParticipant[]
  matchesAsP1           Match[]            @relation("MatchPlayer1")
  matchesAsP2           Match[]            @relation("MatchPlayer2")
  matchesAsP3           Match[]            @relation("MatchPlayer3")
  matchesAsP4           Match[]            @relation("MatchPlayer4")
  partnershipGames      PartnershipGame[]  @relation("PartnershipPlayer1")
  partnershipGames2     PartnershipGame[]  @relation("PartnershipPlayer2")
  partnershipStats      PartnershipStat[]  @relation("StatPlayer1")
  partnershipStats2     PartnershipStat[]  @relation("StatPlayer2")
  activities            Activity[]
  user                  User?
  eloRating             EloRating?
  glicko2Rating         Glicko2Rating?
  openSkillRating       OpenSkillRating?
  bracketMatchupsAsP1P1 BracketMatchup[]   @relation("BracketMatchupPlayer1P1")
  bracketMatchupsAsP1P2 BracketMatchup[]   @relation("BracketMatchupPlayer1P2")
  bracketMatchupsAsP2P1 BracketMatchup[]   @relation("BracketMatchupPlayer2P1")
  bracketMatchupsAsP2P2 BracketMatchup[]   @relation("BracketMatchupPlayer2P2")

  @@map("players")
}

model User {
  id               String    @id @default(cuid())
  email            String    @unique
  emailVerified    Boolean   @default(false)
  name             String?
  image            String?
  role             String    @default("player")
  playerId         Int?      @unique
  createdAt        DateTime  @default(now())
  updatedAt        DateTime  @updatedAt
  accounts         Account[]
  ownedTournaments Event[]   @relation("TournamentOwner")
  createdMatches   Match[]   @relation("MatchCreator")
  sessions         Session[]
  activities       Activity[]
  player           Player?   @relation(fields: [playerId], references: [id])

  @@map("users")
}

model Event {
  id              Int                @id @default(autoincrement())
  event_id        Int?
  name            String
  description     String?
  eventDate       DateTime?
  eventType       String             @default("tournament")
  tournamentType  String             @default("individual")
  format          String             @default("round_robin")
  status          String             @default("planned")
  maxParticipants Int?
  ownerId         String?
  createdAt       DateTime           @default(now())
  updatedAt       DateTime           @updatedAt
  targetScore     Int?
  allowTies       Boolean            @default(false)
  bracketMatchups BracketMatchup[]
  participants    EventParticipant[]
  owner           User?              @relation("TournamentOwner", fields: [ownerId], references: [id])
  matches         Match[]
  rounds          TournamentRound[]
  activities      Activity[]

  // Team configuration fields
  teamDurability     String  @default("permanent") // permanent, variable, per_round
  partnerRotation    String  @default("none") // none, minimize_repeat, maximize_even, elo_based
  allowByes          Boolean @default(true)
  teamConfiguration  Json? // Additional configuration options
  maxRosterChanges   Int? // Maximum roster changes per player
  requireAdminVerify Boolean @default(false) // For match score verification

  @@map("events")
}

model EventParticipant {
  id               Int       @id @default(autoincrement())
  eventId          Int
  playerId         Int
  seed             Int?
  status           String    @default("registered")
  registrationDate DateTime?
  createdAt        DateTime  @default(now())
  updatedAt        DateTime  @updatedAt
  event            Event     @relation(fields: [eventId], references: [id])
  player           Player    @relation(fields: [playerId], references: [id])

  @@unique([eventId, playerId])
  @@map("event_participants")
}

model TournamentRound {
  id              Int              @id @default(autoincrement())
  eventId         Int
  roundNumber     Int
  status          String           @default("pending")
  scheduledStart  DateTime?
  actualStart     DateTime?
  completedAt     DateTime?
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
  bracketMatchups BracketMatchup[]
  event           Event            @relation(fields: [eventId], references: [id])

  @@map("tournament_rounds")
}

model BracketMatchup {
  id              Int             @id @default(autoincrement())
  roundId         Int
  eventId         Int
  player1P1Id     Int?
  player1P2Id     Int?
  player2P1Id     Int?
  player2P2Id     Int?
  matchId         Int?
  tableNumber     Int?
  bracketPosition Int?
  winnerTeamId    Int?
  loserTeamId     Int?
  status          String          @default("pending")
  createdAt       DateTime        @default(now())
  updatedAt       DateTime        @updatedAt
  event           Event           @relation(fields: [eventId], references: [id])
  match           Match?          @relation(fields: [matchId], references: [id], onDelete: Cascade)
  round           TournamentRound @relation(fields: [roundId], references: [id])
  player1P1       Player?         @relation("BracketMatchupPlayer1P1", fields: [player1P1Id], references: [id])
  player1P2       Player?         @relation("BracketMatchupPlayer1P2", fields: [player1P2Id], references: [id])
  player2P1       Player?         @relation("BracketMatchupPlayer2P1", fields: [player2P1Id], references: [id])
  player2P2       Player?         @relation("BracketMatchupPlayer2P2", fields: [player2P2Id], references: [id])

  @@map("bracket_matchups")
}

model Match {
  id               Int               @id @default(autoincrement())
  eventId          Int?
  playedAt         DateTime?
  player1P1Id      Int?
  player1P2Id      Int?
  player2P1Id      Int?
  player2P2Id      Int?
  team1Score       Int
  team2Score       Int
  status           String            @default("completed")
  createdById      String?
  createdAt        DateTime          @default(now())
  updatedAt        DateTime          @updatedAt
  isCasual         Boolean           @default(false)
  bracketMatchups  BracketMatchup[]
  eloSnapshots     EloSnapshot[]
  activities       Activity[]
  createdBy        User?             @relation("MatchCreator", fields: [createdById], references: [id])
  event            Event?            @relation(fields: [eventId], references: [id], onDelete: Cascade)
  player1P1        Player?           @relation("MatchPlayer1", fields: [player1P1Id], references: [id])
  player1P2        Player?           @relation("MatchPlayer2", fields: [player1P2Id], references: [id])
  player2P1        Player?           @relation("MatchPlayer3", fields: [player2P1Id], references: [id])
  player2P2        Player?           @relation("MatchPlayer4", fields: [player2P2Id], references: [id])
  partnershipGames PartnershipGame[]

  @@map("matches")
}

model EloSnapshot {
  id           Int      @id @default(autoincrement())
  playerId     Int
  matchId      Int
  ratingBefore Int
  ratingAfter  Int
  ratingChange Int
  createdAt    DateTime @default(now())
  match        Match    @relation(fields: [matchId], references: [id], onDelete: Cascade)
  player       Player   @relation(fields: [playerId], references: [id])

  @@map("elo_snapshots")
}

model PartnershipGame {
  id               Int      @id @default(autoincrement())
  player1Id        Int
  player2Id        Int
  matchId          Int
  teamNumber       Int?
  wonMatch         Int?
  player1EloChange Int?
  player2EloChange Int?
  createdAt        DateTime @default(now())
  match            Match    @relation(fields: [matchId], references: [id], onDelete: Cascade)
  player1          Player   @relation("PartnershipPlayer1", fields: [player1Id], references: [id])
  player2          Player   @relation("PartnershipPlayer2", fields: [player2Id], references: [id])

  @@map("partnership_games")
}

model PartnershipStat {
  id             Int       @id @default(autoincrement())
  player1Id      Int
  player2Id      Int
  gamesPlayed    Int       @default(0)
  wins           Int       @default(0)
  losses         Int       @default(0)
  totalEloChange Int       @default(0)
  lastPlayed     DateTime?
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt
  player1        Player    @relation("StatPlayer1", fields: [player1Id], references: [id])
  player2        Player    @relation("StatPlayer2", fields: [player2Id], references: [id])

  @@unique([player1Id, player2Id], name: "partnership_stats_player1Id_player2Id_key")
  @@map("partnership_stats")
}

model Session {
  id        String   @id
  expiresAt DateTime
  token     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  ipAddress String?
  userAgent String?
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("session")
}

model Account {
  id                    String    @id
  accountId             String
  providerId            String
  userId                String
  accessToken           String?
  refreshToken          String?
  idToken               String?
  accessTokenExpiresAt  DateTime?
  refreshTokenExpiresAt DateTime?
  scope                 String?
  password              String?
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt
  user                  User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("account")
}

model Verification {
  id         String   @id
  identifier String
  value      String
  expiresAt  DateTime
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  @@index([identifier])
  @@map("verification")
}

model EloRating {
  id          Int      @id @default(autoincrement())
  playerId    Int      @unique
  rating      Int      @default(1000)
  volatility  Float    @default(0) // Optional: for advanced Elo variants
  gamesPlayed Int      @default(0)
  wins        Int      @default(0)
  losses      Int      @default(0)
  draws       Int      @default(0)
  lastUpdated DateTime @default(now())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  player      Player   @relation(fields: [playerId], references: [id], onDelete: Cascade)

  @@map("elo_ratings")
}

model Glicko2Rating {
  id          Int      @id @default(autoincrement())
  playerId    Int      @unique
  rating      Float    @default(1500.0) // mu
  deviation   Float    @default(350.0) // phi
  volatility  Float    @default(0.05) // sigma
  gamesPlayed Int      @default(0)
  wins        Int      @default(0)
  losses      Int      @default(0)
  draws       Int      @default(0)
  lastUpdated DateTime @default(now())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  player      Player   @relation(fields: [playerId], references: [id], onDelete: Cascade)

  @@map("glicko2_ratings")
}

model OpenSkillRating {
  id          Int      @id @default(autoincrement())
  playerId    Int      @unique
  rating      Float    @default(25.0) // mu
  deviation   Float    @default(8.33) // sigma (std dev)
  gamesPlayed Int      @default(0)
  wins        Int      @default(0)
  losses      Int      @default(0)
  draws       Int      @default(0)
  lastUpdated DateTime @default(now())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  player      Player   @relation(fields: [playerId], references: [id], onDelete: Cascade)

  @@map("open_skill_ratings")
}

model Activity {
  id          Int       @id @default(autoincrement())
  type        String    // "player_registration", "tournament_created", "match_completed", "partnership_recorded"
  description String
  userId      String?
  playerId    Int?
  eventId     Int?
  matchId     Int?
  createdAt   DateTime  @default(now())
  
  user        User?     @relation(fields: [userId], references: [id])
  player      Player?   @relation(fields: [playerId], references: [id])
  event       Event?    @relation(fields: [eventId], references: [id])
  match       Match?    @relation(fields: [matchId], references: [id])
  
  @@map("activities")
}

model ClubSettings {
  id                    Int     @id @default(autoincrement())
  clubName              String  @default("Euchre Club")
  defaultEloRating      Int     @default(1200)
  partnershipEnabled    Boolean @default(true)
  notificationsEnabled  Boolean @default(true)
  matchVerification     Boolean @default(false)
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt
  
  @@map("club_settings")
}