prisma/schema.prisma

// PostgreSQL 16 Schema
// Run: npx prisma migrate dev --name init

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

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

// ── Users & Auth ──

model User {
  id            String   @id @default(uuid()) @db.Uuid
  email         String   @unique
  emailVerified DateTime?
  passwordHash  String?  // bcrypt/Argon2 — null for OIDC users
  fullName      String
  phone         String?
  avatarUrl     String?

  // Multi-role support
  roles         UserRole[]

  // OIDC identities
  identities    OidcIdentity[]

  // Profile type (for account switching)
  clientProfile  ClientProfile?
  vendorProfile  VendorProfile?
  adminProfile   AdminProfile?

  // Sessions
  sessions      Session[]

  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  deletedAt     DateTime? // soft delete for GDPR

  @@index([email])
  @@index([deletedAt])
}

model UserRole {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @db.Uuid
  role      Role     // CLIENT | VENDOR | ADMIN
  isPrimary Boolean  @default(false)
  createdAt DateTime @default(now())

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

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

enum Role {
  CLIENT
  VENDOR
  ADMIN
}

model OidcIdentity {
  id          String   @id @default(uuid()) @db.Uuid
  userId      String   @db.Uuid
  provider    String   // google | apple | cognito | keycloak
  subject     String   // OIDC sub claim
  idToken     String?
  accessToken String?
  refreshToken String?
  expiresAt   DateTime?
  createdAt   DateTime @default(now())

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

  @@unique([provider, subject])
  @@index([userId])
}

model Session {
  id        String    @id @default(uuid()) @db.Uuid
  userId    String    @db.Uuid
  token     String    @unique
  portal    String    // client | vendor | admin
  ipAddress String?
  userAgent String?
  expiresAt DateTime
  revokedAt DateTime?
  createdAt DateTime  @default(now())

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

  @@index([token])
  @@index([userId, portal])
}

// ── Client Profile ──

model ClientProfile {
  id          String   @id @default(uuid()) @db.Uuid
  userId      String   @unique @db.Uuid
  partnerName String?
  weddingDate DateTime?
  location    String?
  guestCount  Int?
  budget      Decimal? @db.Decimal(12, 2)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  user         User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  bookings     Booking[]
  shortlists   Shortlist[]
  workspaces   Workspace[]
}

// ── Vendor Profile ──

model VendorProfile {
  id              String   @id @default(uuid()) @db.Uuid
  userId          String   @unique @db.Uuid
  businessName    String
  categoryId      String   @db.Uuid
  description     String?
  district        String?
  address         String?
  website         String?
  socialLinks     Json?    // {facebook, instagram, etc}
  isVerified      Boolean  @default(false)
  isPublished     Boolean  @default(false)
  kycStatus       KycStatus @default(PENDING)
  kycSubmittedAt  DateTime?
  kycApprovedAt   DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  user        User            @relation(fields: [userId], references: [id], onDelete: Cascade)
  category    Category         @relation(fields: [categoryId], references: [id])
  packages    Package[]
  bookings    Booking[]
  leads       Lead[]
  availability VendorAvailability[]
}

enum KycStatus {
  PENDING
  SUBMITTED
  APPROVED
  REJECTED
}

// ── Admin Profile ──

model AdminProfile {
  id           String   @id @default(uuid()) @db.Uuid
  userId       String   @unique @db.Uuid
  permissions  String[] // MANAGE_VENDORS | MANAGE_CONTRACTS | MANAGE_USERS | SYSTEM_ADMIN
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

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

// ── Categories ──

model Category {
  id          String   @id @default(uuid()) @db.Uuid
  name        String   @unique
  slug        String   @unique
  emoji       String?
  status      ContentStatus @default(PUBLISHED)
  sortOrder   Int      @default(0)
  parentId    String?  @db.Uuid
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  parent   Category?   @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children Category[]  @relation("CategoryHierarchy")
  vendors  VendorProfile[]
}

enum ContentStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

// ── Packages ──

model Package {
  id          String   @id @default(uuid()) @db.Uuid
  vendorId    String   @db.Uuid
  name        String
  description String?
  price       Decimal  @db.Decimal(12, 2)
  guestCapacity Int?
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  vendor VendorProfile @relation(fields: [vendorId], references: [id], onDelete: Cascade)

  @@index([vendorId])
}

// ── Vendor Availability ──

model VendorAvailability {
  id        String   @id @default(uuid()) @db.Uuid
  vendorId  String   @db.Uuid
  date      DateTime @db.Date
  type      AvailabilityType
  label     String?
  createdAt DateTime @default(now())

  vendor VendorProfile @relation(fields: [vendorId], references: [id], onDelete: Cascade)

  @@unique([vendorId, date])
  @@index([vendorId, date])
}

enum AvailabilityType {
  AVAILABLE
  BOOKED
  BLOCKED
}

// ── Bookings ──

model Booking {
  id              String   @id @default(uuid()) @db.Uuid
  clientId        String   @db.Uuid
  vendorId        String   @db.Uuid
  packageId       String?  @db.Uuid
  status          BookingStatus @default(PENDING)
  idempotencyKey  String   @unique // prevent duplicate writes
  date            DateTime
  guestCount      Int?
  notes           String?
  totalAmount     Decimal? @db.Decimal(12, 2)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  client    ClientProfile @relation(fields: [clientId], references: [id])
  vendor    VendorProfile @relation(fields: [vendorId], references: [id])
  package   Package?      @relation(fields: [packageId], references: [id])
  contracts Contract[]

  @@index([clientId])
  @@index([vendorId])
  @@index([status])
}

enum BookingStatus {
  PENDING
  CONFIRMED
  CANCELLED
  COMPLETED
}

// ── Shortlist ──

model Shortlist {
  id        String   @id @default(uuid()) @db.Uuid
  clientId  String   @db.Uuid
  vendorId  String   @db.Uuid
  notes     String?
  createdAt DateTime @default(now())

  client ClientProfile @relation(fields: [clientId], references: [id], onDelete: Cascade)

  @@unique([clientId, vendorId])
  @@index([clientId])
}

// ── Workspaces (client planning) ──

model Workspace {
  id        String   @id @default(uuid()) @db.Uuid
  clientId  String   @db.Uuid
  name      String
  tasks     Json?    // embedded checklist items
  timeline  Json?    // embedded timeline events
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  client ClientProfile @relation(fields: [clientId], references: [id], onDelete: Cascade)

  @@index([clientId])
}

// ── Leads ──

model Lead {
  id        String   @id @default(uuid()) @db.Uuid
  vendorId  String   @db.Uuid
  clientId  String?  @db.Uuid
  name      String
  type      String
  status    LeadStatus @default(NEW)
  notes     String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  vendor VendorProfile @relation(fields: [vendorId], references: [id])

  @@index([vendorId, status])
}

enum LeadStatus {
  NEW
  VIEWED
  QUOTED
  CONVERTED
  LOST
}

// ── Reviews ──

model Review {
  id        String   @id @default(uuid()) @db.Uuid
  vendorId  String   @db.Uuid
  clientId  String   @db.Uuid
  rating    Int      // 1-5
  content   String?
  isFlagged Boolean  @default(false)
  createdAt DateTime @default(now())

  vendor VendorProfile @relation(fields: [vendorId], references: [id])

  @@unique([vendorId, clientId])
  @@index([vendorId])
}

// ── CONTRACTS ──

model Contract {
  id            String   @id @default(uuid()) @db.Uuid
  bookingId     String   @db.Uuid
  vendorId      String   @db.Uuid
  clientId      String   @db.Uuid

  // Contract metadata
  title         String
  serviceType   String?
  totalAmount   Decimal  @db.Decimal(12, 2)
  status        ContractStatus @default(DRAFT)
  version       Int      @default(1)

  // Timestamps
  sentAt        DateTime?
  viewedAt      DateTime?
  signedAt      DateTime?
  vendorSignedAt DateTime?
  clientSignedAt DateTime?
  completedAt   DateTime?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Relations
  booking     Booking          @relation(fields: [bookingId], references: [id])
  sections    ContractSection[]
  versions    ContractVersion[]
  signatures  ContractSignature[]
  auditLogs   ContractAuditLog[]
  deliverables ContractDeliverable[]

  @@index([bookingId])
  @@index([vendorId, status])
  @@index([clientId, status])
  @@index([status])
}

enum ContractStatus {
  DRAFT
  SENT
  VIEWED
  SIGNED
  ACTIVE
  AMENDED
  DISPUTED
  COMPLETED
  CANCELLED
}

model ContractSection {
  id          String   @id @default(uuid()) @db.Uuid
  contractId  String   @db.Uuid
  sortOrder   Int
  title       String
  content     String
  createdAt   DateTime @default(now())

  contract Contract @relation(fields: [contractId], references: [id], onDelete: Cascade)

  @@index([contractId])
}

model ContractDeliverable {
  id          String   @id @default(uuid()) @db.Uuid
  contractId  String   @db.Uuid
  description String
  dueDate     DateTime?
  quantity    Int?
  acceptanceCriteria String?
  isCompleted Boolean  @default(false)
  completedAt DateTime?
  createdAt   DateTime @default(now())

  contract Contract @relation(fields: [contractId], references: [id], onDelete: Cascade)

  @@index([contractId])
}

// Immutable contract version snapshot
model ContractVersion {
  id            String   @id @default(uuid()) @db.Uuid
  contractId    String   @db.Uuid
  version       Int
  reason        String?  // reason for amendment
  snapshot      Json     // full frozen copy of contract + sections + deliverables
  createdBy     String   // userId
  createdAt     DateTime @default(now())

  contract Contract @relation(fields: [contractId], references: [id])

  @@unique([contractId, version])
  @@index([contractId])
}

model ContractSignature {
  id          String   @id @default(uuid()) @db.Uuid
  contractId  String   @db.Uuid
  contractVersionId String @db.Uuid
  signerId    String   // userId
  signerRole  Role
  signature   String?  // digital signature data
  ipAddress   String?
  userAgent   String?
  signedAt    DateTime @default(now())

  contract        Contract        @relation(fields: [contractId], references: [id])
  contractVersion ContractVersion @relation(fields: [contractVersionId], references: [id])

  @@index([contractId])
  @@index([signerId])
}

// Append-only audit log — rows are NEVER updated or deleted
model ContractAuditLog {
  id          String   @id @default(uuid()) @db.Uuid
  contractId  String   @db.Uuid
  action      String   // created | sent | viewed | signed | declined | amended | disputed | resolved | completed
  actorId     String?  // userId
  actorRole   Role?
  detail      String?
  metadata    Json?    // version diff, IP, etc
  createdAt   DateTime @default(now())

  contract Contract @relation(fields: [contractId], references: [id])

  @@index([contractId, createdAt])
}

// ── Contract Templates (vendor-submitted, admin-approved) ──

model ContractTemplate {
  id          String   @id @default(uuid()) @db.Uuid
  vendorId    String?  @db.Uuid
  name        String
  category    String
  status      ContentStatus @default(DRAFT) // admin approval workflow
  structure   Json     // frozen section structure + deliverable templates
  submittedAt DateTime @default(now())
  approvedAt  DateTime?
  approvedBy  String?  // admin userId
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  vendor VendorProfile? @relation(fields: [vendorId], references: [id])

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

// ── Admin Audit Log ──

model AdminAuditLog {
  id        String   @id @default(uuid()) @db.Uuid
  adminId   String   @db.Uuid
  action    String
  target    String?  // resource acted on
  detail    String?
  createdAt DateTime @default(now())

  @@index([adminId, createdAt])
}

// ── Notifications ──

model Notification {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @db.Uuid
  title     String
  body      String
  type      String   // booking | contract | review | system
  isRead    Boolean  @default(false)
  link      String?
  createdAt DateTime @default(now())

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

// ── Rate Limiting (backed by Redis; schema for reference) ──
// Key pattern: ratelimit:{ip}:{method}:{path}:{window}
// Redis INCR + EXPIRE