prisma/schema.prisma

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

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

enum CustomerGender {
  MALE
  FEMALE
}

enum CaseType {
  CIVIL
  CRIMINAL
  PERSONAL_STATUS
}

enum CaseStatus {
  OPEN
  CLOSED
}

enum CaseFileRagStatus {
  PENDING
  PROCESSING
  READY
  FAILED
  SKIPPED
}

model User {
  id           String     @id @default(cuid())
  firstName    String
  lastName     String
  email        String     @unique
  passwordHash String
  sessions     Session[]
  customers    Customer[] @relation("UserCustomers")
  cases        Case[]
  caseFiles    CaseFile[]
  courtSessions CourtSession[]
  chatTurns     CaseChatTurn[]
  createdAt    DateTime   @default(now())
  updatedAt    DateTime   @updatedAt

  @@index([createdAt])
}

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

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

model Customer {
  id         String         @id @default(cuid())
  ownerId    String
  firstName  String
  middleName String?
  lastName   String
  gender     CustomerGender
  phone      String
  owner      User           @relation("UserCustomers", fields: [ownerId], references: [id], onDelete: Cascade)
  cases      Case[]
  caseFiles  CaseFile[]
  chatTurns  CaseChatTurn[]
  courtSessions CourtSession[]
  createdAt  DateTime       @default(now())
  updatedAt  DateTime       @updatedAt

  @@index([ownerId, updatedAt])
  @@index([ownerId, createdAt])
  @@index([ownerId, phone])
}

model Case {
  id               String      @id @default(cuid())
  customerId       String
  ownerId          String
  caseName         String
  publicId         String
  type             CaseType
  subType          String?
  opponentFullName String
  description      String?
  status           CaseStatus  @default(OPEN)
  closedAt         DateTime?
  customer         Customer    @relation(fields: [customerId], references: [id], onDelete: Cascade)
  owner            User        @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  files            CaseFile[]
  courtSessions    CourtSession[]
  chatTurns        CaseChatTurn[]
  createdAt        DateTime    @default(now())
  updatedAt        DateTime    @updatedAt

  @@unique([ownerId, publicId])
  @@index([ownerId, status, updatedAt])
  @@index([ownerId, customerId, type])
  @@index([customerId, updatedAt])
  @@index([ownerId, opponentFullName])
  @@index([ownerId, caseName])
}

model CaseFile {
  id               String   @id @default(cuid())
  ownerId          String
  customerId       String
  caseId           String
  displayName      String
  description      String?
  originalFileName String
  mimeType         String
  sizeBytes        Int
  storageBucket    String
  storageKey       String   @unique
  ragStatus        CaseFileRagStatus @default(PENDING)
  ragError         String?
  ragSyncedAt      DateTime?
  customer         Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
  case             Case     @relation(fields: [caseId], references: [id], onDelete: Cascade)
  owner            User     @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  @@index([ownerId, caseId, createdAt])
  @@index([ownerId, caseId, displayName])
  @@index([customerId, caseId])
  @@index([ownerId, caseId, ragStatus])
}

model CourtSession {
  id              String   @id @default(cuid())
  ownerId         String
  customerId      String
  caseId          String
  governorate     String
  courtName       String
  courtLocation   String
  locationDetails String?
  scheduledAt     DateTime
  owner           User     @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  customer        Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
  case            Case     @relation(fields: [caseId], references: [id], onDelete: Cascade)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@index([ownerId, scheduledAt])
  @@index([ownerId, customerId, scheduledAt])
  @@index([ownerId, caseId, scheduledAt])
  @@index([customerId, scheduledAt])
  @@index([caseId, scheduledAt])
}

model CaseChatTurn {
  id        String   @id @default(cuid())
  ownerId   String
  customerId String
  caseId    String
  question  String
  answer    String
  sources   Json?
  owner     User     @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  customer  Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
  case      Case     @relation(fields: [caseId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([ownerId, caseId, createdAt])
  @@index([customerId, createdAt])
}