schema.prisma

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

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

// =======================
// Enums
// =======================

enum UserRole {
  USER
  ADMIN
  ORACLE
}

enum NotificationType {
  WIN
  LOSS
  ROUND_START
  BONUS_AVAILABLE
  ANNOUNCEMENT
}

enum GameMode {
  UP_DOWN
  LEGENDS
}

enum RoundStatus {
  PENDING
  ACTIVE
  LOCKED
  RESOLVED
  CANCELLED
}

enum PredictionSide {
  UP
  DOWN
}

enum TransactionType {
  BONUS
  WIN
  LOSS
  WITHDRAWAL
  DEPOSIT
}

enum DispatchChannel {
  NOTIFICATION_CREATE
  WEBSOCKET_EMIT
}

enum DispatchStatus {
  PENDING
  RETRYING
  RESOLVED
  ABANDONED
}

/// Outbox event type — mirrors DispatchChannel but lives on the outbox
/// model so the two concerns (outbox vs. DLQ) stay independent.
enum OutboxEventType {
  NOTIFICATION_CREATE
  WEBSOCKET_EMIT
}

/// Outbox event status lifecycle:
///   PENDING   → picked up by the poller
///   PROCESSING → poller is actively dispatching (prevents double-dispatch)
///   PROCESSED  → successfully dispatched; eligible for cleanup
///   FAILED     → all retry attempts exhausted; escalated to DLQ
enum OutboxEventStatus {
  PENDING
  PROCESSING
  PROCESSED
  FAILED
}

enum TournamentStatus {
  UPCOMING
  ACTIVE
  COMPLETED
  CANCELLED
}

// =======================
// Models
// =======================

model User {
  id                      String    @id @default(uuid())
  walletAddress           String    @unique
  publicKey               String?
  nickname                String?
  avatarUrl               String?
  preferences             Json?
  virtualBalance          Decimal   @default(1000) @db.Decimal(20, 8)
  wins                    Int       @default(0)
  streak                  Int       @default(0)
  role                    UserRole  @default(USER)
  notificationPreferences Json?     @default("{\"win\": true, \"loss\": true, \"roundStart\": false, \"bonus\": true, \"announcement\": true}")
  createdAt               DateTime  @default(now())
  updatedAt               DateTime  @updatedAt
  lastLoginAt             DateTime?

  // Relations
  messages            Message[]
  predictions         Prediction[]
  rounds              Round[]
  notifications       Notification[]
  authChallenges      AuthChallenge[]
  stats               UserStats?
  transactions        Transaction[]
  multiplayerSessions MultiplayerSession[]
  tournamentParticipants TournamentParticipant[]
}

model Tournament {
  id                String                      @id @default(uuid())
  name              String
  description       String
  mode              GameMode
  status            TournamentStatus            @default(UPCOMING)
  entryFee          Decimal                     @db.Decimal(20, 8)
  prizePool         Decimal                     @db.Decimal(20, 8)
  maxParticipants   Int
  currentParticipants Int                     @default(0)
  startTime         DateTime
  endTime           DateTime
  rounds            Int                         @default(1)
  createdAt         DateTime                    @default(now())
  updatedAt         DateTime                    @updatedAt

  participants TournamentParticipant[]

  @@index([status])
  @@index([mode])
}

model TournamentParticipant {
  id           String     @id @default(uuid())
  tournamentId String
  userId       String
  joinedAt     DateTime   @default(now())

  tournament Tournament @relation(fields: [tournamentId], references: [id], onDelete: Cascade)
  user       User       @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([tournamentId, userId])
  @@index([tournamentId])
  @@index([userId])
}

model Message {
  id        String   @id @default(uuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  content   String   @db.VarChar(500)
  createdAt DateTime @default(now())

  @@index([userId])
  @@index([createdAt])
}

model AuthChallenge {
  id            String    @id @default(uuid())
  userId        String?
  user          User?     @relation(fields: [userId], references: [id], onDelete: Cascade)
  challenge     String    @unique
  walletAddress String
  createdAt     DateTime  @default(now())
  expiresAt     DateTime
  usedAt        DateTime?
  isUsed        Boolean   @default(false)

  @@index([challenge])
  @@index([walletAddress])
  @@index([expiresAt])
}

model Round {
  id             String      @id @default(uuid())
  mode           GameMode
  status         RoundStatus @default(PENDING)
  startPrice     Decimal     @db.Decimal(18, 8)
  endPrice       Decimal?    @db.Decimal(18, 8)
  startTime      DateTime
  endTime        DateTime
  sorobanRoundId String?     @unique
  isSoroban      Boolean     @default(false)
  poolUp         Decimal     @default(0) @db.Decimal(18, 8)
  poolDown       Decimal     @default(0) @db.Decimal(18, 8)
  priceRanges    Json?
  resolvedAt     DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  predictions Prediction[]
  User        User?        @relation(fields: [userId], references: [id])
  userId      String?

  @@index([status])
  @@index([mode])
  @@index([startTime])
}

model Prediction {
  id         String          @id @default(uuid())
  userId     String
  roundId    String
  side       PredictionSide?
  amount     Decimal         @db.Decimal(18, 8)
  priceRange Json?

  user  User  @relation(fields: [userId], references: [id])
  round Round @relation(fields: [roundId], references: [id])

  won    Boolean?
  payout Decimal? @db.Decimal(18, 8)

  createdAt DateTime @default(now())

  @@unique([roundId, userId])
  @@index([userId])
  @@index([roundId])
}

model Notification {
  id        String           @id @default(uuid())
  userId    String
  user      User             @relation(fields: [userId], references: [id])
  type      NotificationType
  title     String
  message   String
  data      Json?
  isRead    Boolean          @default(false)
  createdAt DateTime         @default(now())

  @@index([userId, createdAt])
  @@index([userId, isRead, createdAt])
}

model UserStats {
  id     String @id @default(uuid())
  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  totalPredictions   Int     @default(0)
  correctPredictions Int     @default(0)
  totalEarnings      Decimal @default(0) @db.Decimal(20, 8)

  upDownWins     Int     @default(0)
  upDownLosses   Int     @default(0)
  upDownEarnings Decimal @default(0) @db.Decimal(20, 8)

  legendsWins     Int     @default(0)
  legendsLosses   Int     @default(0)
  legendsEarnings Decimal @default(0) @db.Decimal(20, 8)

  updatedAt DateTime @updatedAt

  @@index([totalEarnings])
}

model Transaction {
  id     String @id @default(uuid())
  userId String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  amount      Decimal         @db.Decimal(20, 8)
  type        TransactionType
  description String?
  roundId     String?

  createdAt DateTime @default(now())

  @@index([userId])
  @@index([type])
}

/// Multiplayer session metadata persisted across socket disconnects.
///
/// Powers reconnect continuity (Issue #194): when an authenticated client
/// reconnects after a transient drop, the server can look up its prior
/// rooms and resume membership without forcing the client to re-discover
/// state. Rows are upserted by `userId` (one active session row per user),
/// so a fresh login transparently replaces a stale row.
///
/// Storage notes:
///   - `rooms` is a JSON-encoded `string[]` of room names the user joined.
///   - `metadata` is opaque JSON for future per-game state (last round,
///     pending message, etc.); writers must keep it bounded.
///   - `disconnectedAt` is null while the session is live; setting it
///     marks the session as resumable until cleaned up by retention.
model MultiplayerSession {
  id             String    @id @default(uuid())
  userId         String
  user           User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  walletAddress  String
  socketId       String?
  rooms          Json      @default("[]")
  metadata       Json?
  connectedAt    DateTime  @default(now())
  lastSeenAt     DateTime  @default(now())
  disconnectedAt DateTime?

  @@unique([userId])
  @@index([walletAddress])
  @@index([lastSeenAt])
}

/// Dead-letter queue row for a notification or websocket dispatch that
/// failed at runtime. Keeps the original payload so an operator (or a
/// scheduled retry job) can replay the dispatch later without losing the
/// signal. `attempts` and `lastError` track how the entry has aged so a
/// stuck row can be triaged from `/api/admin/dead-letter`.
model FailedDispatch {
  id          String          @id @default(uuid())
  channel     DispatchChannel
  eventName   String?
  userId      String?
  payload     Json
  attempts    Int             @default(1)
  status      DispatchStatus  @default(PENDING)
  lastError   String          @db.VarChar(1000)
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  lastRetryAt DateTime?
  resolvedAt  DateTime?

  @@index([channel])
  @@index([status])
  @@index([createdAt])
  @@index([userId])
}

/// Transactional outbox for notification and websocket side-effects (Issue #18).
///
/// Every row is written *inside* the same Prisma transaction that commits
/// the business state change (payout, prediction, etc.). A background poller
/// reads PENDING rows and dispatches them, then marks them PROCESSED.
/// If dispatch fails after MAX_OUTBOX_ATTEMPTS the row is marked FAILED and
/// escalated to the FailedDispatch DLQ for operator review.
///
/// This guarantees at-least-once delivery: a side-effect can never be
/// silently dropped because the process crashed between the DB commit and
/// the in-process notification call.
///
/// Storage notes:
///   - `aggregateId` is the business entity id (roundId, predictionId, …).
///   - `aggregateType` is a short label ("round", "prediction", …).
///   - `payload` is the full data needed to reconstruct the dispatch.
///   - `attempts` starts at 0 and is incremented on each dispatch attempt.
///   - `processedAt` is set when status transitions to PROCESSED.
///   - Rows older than OUTBOX_RETENTION_DAYS (default 7) can be deleted.
model OutboxEvent {
  id            String            @id @default(uuid())
  eventType     OutboxEventType
  aggregateId   String
  aggregateType String
  payload       Json
  status        OutboxEventStatus @default(PENDING)
  attempts      Int               @default(0)
  lastError     String?           @db.VarChar(1000)
  createdAt     DateTime          @default(now())
  updatedAt     DateTime          @updatedAt
  processedAt   DateTime?

  @@index([status, createdAt])
  @@index([aggregateId])
  @@index([eventType])
}

model RateLimitMetric {
  id        String   @id @default(uuid())
  endpoint  String
  key       String
  ip        String?
  userId    String?
  timestamp DateTime @default(now())

  @@index([endpoint])
  @@index([timestamp])
  @@index([key])
}

/// Idempotency key storage for safe client retries.
/// 
/// Stores the result of idempotent operations (like prediction submission)
/// so that if a client retries with the same idempotency key, the server
/// returns the cached result instead of executing the operation again.
/// 
/// This prevents duplicate predictions when clients retry after network timeouts.
/// Keys are scoped by userId + endpoint to prevent cross-user collisions.
/// 
/// Retention: rows older than 24 hours can be safely deleted.
model IdempotencyKey {
  id             String   @id @default(uuid())
  userId         String
  endpoint       String
  idempotencyKey String
  requestHash    String // Hash of request body to detect mutations
  responseStatus Int // HTTP status code (200, 400, etc.)
  responseBody   Json // Cached response to return on retry
  createdAt      DateTime @default(now())
  expiresAt      DateTime // 24 hours from creation

  @@unique([userId, endpoint, idempotencyKey])
  @@index([userId])
  @@index([expiresAt])
}

/// Security audit log for authentication and authorization events.
/// Stores structured audit events for compliance, security monitoring,
/// and forensic analysis. Retention policy controls how long logs are kept.
model AuditLog {
  id String @id @default(uuid())

  // Event identification
  eventType String @db.VarChar(100)
  severity  String @db.VarChar(20)
  message   String @db.VarChar(500)
  outcome   String @db.VarChar(20)

  // Actor information
  actorType     String  @db.VarChar(50)
  walletAddress String? @db.VarChar(100)
  userId        String? @db.VarChar(100)
  ipAddress     String? @db.VarChar(45)
  userAgent     String? @db.VarChar(500)

  // Context information
  requestId String? @db.VarChar(100)
  sessionId String? @db.VarChar(100)
  endpoint  String? @db.VarChar(200)
  method    String? @db.VarChar(10)

  // Resource information
  resourceType          String? @db.VarChar(50)
  resourceId            String? @db.VarChar(100)
  resourceWalletAddress String? @db.VarChar(100)

  // Additional metadata (JSON)
  metadata Json?

  // Timestamps
  timestamp DateTime @default(now())

  @@index([eventType])
  @@index([severity])
  @@index([timestamp])
  @@index([walletAddress])
  @@index([userId])
  @@index([outcome])
}

// =======================
// Mock Data Models
// =======================

model MockRound {
  id              String @id
  asset           String
  mode            String
  status          String
  startPrice      Float
  poolUp          Float?
  poolDown        Float?
  totalPool       Float?
  predictionCount Int?
  closesAt        String
}

model MockLeaderboard {
  address     String @id
  rank        Int
  totalWins   Int
  totalLosses Int
  winStreak   Int
  xp          Int
  rankTitle   String
  balance         Int    @default(1000)
  pendingWinnings Int    @default(0)
}

model MockBet {
  id             Int      @id @default(autoincrement())
  roundId        String
  address        String
  amount         Float
  side           String?
  predictedPrice Float?
  createdAt      DateTime @default(now())
}

model MockPlatformStat {
  id                   Int   @id @default(1)
  totalRounds          Int
  totalVxlmDistributed Float
  activePlayers        Int
  totalBetsPlaced      Int
}