forward-assist/packages/api/prisma/schema.prisma

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

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

// ============================================================
// Plans
// ============================================================
model Plan {
  id                   String   @id @default(uuid())
  name                 String
  slug                 String   @unique
  maxUsers             Int      @default(3)
  maxEmailAccounts     Int      @default(1)
  maxTicketsPerMonth   Int      @default(500)
  aiDraftsEnabled      Boolean  @default(false)
  knowledgeBaseEnabled Boolean  @default(false)
  priceMonthly         Decimal  @default(0) @db.Decimal(10, 2)
  priceYearly          Decimal  @default(0) @db.Decimal(10, 2)
  createdAt            DateTime @default(now()) @map("created_at")

  tenants Tenant[]

  @@map("plans")
}

// ============================================================
// Tenants (Multi-tenant root)
// ============================================================
model Tenant {
  id        String   @id @default(uuid())
  name      String
  slug      String   @unique
  planId    String   @map("plan_id")
  settings  Json     @default("{}")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  plan                    Plan                    @relation(fields: [planId], references: [id])
  users                   User[]
  emailAccounts           EmailAccount[]
  tickets                 Ticket[]
  messages                Message[]
  aiDrafts                AiDraft[]
  knowledgeBase           KnowledgeBaseEntry[]
  auditLogs               AuditLog[]
  customerProfiles        CustomerProfile[]
  cannedResponses         CannedResponse[]
  notificationPreferences NotificationPreference[]

  @@map("tenants")
}

// ============================================================
// Users
// ============================================================
model User {
  id           String    @id @default(uuid())
  tenantId     String    @map("tenant_id")
  email        String
  passwordHash String    @map("password_hash")
  name         String
  role         String    @default("agent")
  avatarUrl    String?   @map("avatar_url")
  isActive     Boolean   @default(true) @map("is_active")
  lastLoginAt  DateTime? @map("last_login_at")
  refreshToken String?   @map("refresh_token")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")

  tenant                 Tenant                  @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  assignedTickets        Ticket[]                @relation("TicketAssignee")
  auditLogs              AuditLog[]
  notificationPreference NotificationPreference?
  createdCannedResponses CannedResponse[]

  @@unique([tenantId, email])
  @@index([tenantId])
  @@map("users")
}

// ============================================================
// Email Accounts
// ============================================================
model EmailAccount {
  id                  String    @id @default(uuid())
  tenantId            String    @map("tenant_id")
  name                String
  emailAddress        String    @map("email_address")
  imapHost            String    @map("imap_host")
  imapPort            Int       @default(993) @map("imap_port")
  imapUser            String    @map("imap_user")
  imapPassword        String    @map("imap_password")
  imapTls             Boolean   @default(true) @map("imap_tls")
  smtpHost            String    @map("smtp_host")
  smtpPort            Int       @default(587) @map("smtp_port")
  smtpUser            String    @map("smtp_user")
  smtpPassword        String    @map("smtp_password")
  smtpTls             Boolean   @default(true) @map("smtp_tls")
  isActive            Boolean   @default(true) @map("is_active")
  lastPollAt          DateTime? @map("last_poll_at")
  lastError           String?   @map("last_error")
  pollIntervalSeconds Int       @default(60) @map("poll_interval_seconds")
  createdAt           DateTime  @default(now()) @map("created_at")
  updatedAt           DateTime  @updatedAt @map("updated_at")

  tenant  Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  tickets Ticket[]

  @@unique([tenantId, emailAddress])
  @@index([tenantId])
  @@map("email_accounts")
}

// ============================================================
// Tickets
// ============================================================
model Ticket {
  id                String    @id @default(uuid())
  tenantId          String    @map("tenant_id")
  ticketNumber      String    @map("ticket_number")
  emailAccountId    String    @map("email_account_id")
  subject           String
  status            String    @default("incoming")
  priority          String    @default("medium")
  assigneeId        String?   @map("assignee_id")
  customerEmail     String    @map("customer_email")
  customerName      String?   @map("customer_name")
  customerProfileId String?   @map("customer_profile_id")
  tags              String[]  @default([])
  messageCount      Int       @default(1) @map("message_count")
  lastMessageAt     DateTime  @default(now()) @map("last_message_at")
  resolvedAt        DateTime? @map("resolved_at")
  createdAt         DateTime  @default(now()) @map("created_at")
  updatedAt         DateTime  @updatedAt @map("updated_at")

  tenant          Tenant           @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  emailAccount    EmailAccount     @relation(fields: [emailAccountId], references: [id])
  assignee        User?            @relation("TicketAssignee", fields: [assigneeId], references: [id])
  customerProfile CustomerProfile? @relation(fields: [customerProfileId], references: [id])
  messages        Message[]
  aiDrafts        AiDraft[]

  @@unique([tenantId, ticketNumber])
  @@index([tenantId, status])
  @@index([tenantId, assigneeId])
  @@index([tenantId, customerEmail])
  @@index([tenantId, createdAt])
  @@map("tickets")
}

// ============================================================
// Messages
// ============================================================
model Message {
  id         String   @id @default(uuid())
  ticketId   String   @map("ticket_id")
  tenantId   String   @map("tenant_id")
  direction  String   // 'inbound' | 'outbound'
  fromEmail  String   @map("from_email")
  fromName   String?  @map("from_name")
  toEmail    String   @map("to_email")
  subject    String
  bodyText   String   @map("body_text")
  bodyHtml   String?  @map("body_html")
  messageId  String?  @map("message_id")
  inReplyTo  String?  @map("in_reply_to")
  references String?
  headers    Json?
  sentAt     DateTime @default(now()) @map("sent_at")
  createdAt  DateTime @default(now()) @map("created_at")

  tenant      Tenant       @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  ticket      Ticket       @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  attachments Attachment[]
  aiDrafts    AiDraft[]

  @@index([ticketId])
  @@index([tenantId])
  @@index([messageId])
  @@map("messages")
}

// ============================================================
// Attachments
// ============================================================
model Attachment {
  id          String @id @default(uuid())
  messageId   String @map("message_id")
  filename    String
  contentType String @map("content_type")
  size        Int
  storageKey  String @map("storage_key")

  message Message @relation(fields: [messageId], references: [id], onDelete: Cascade)

  @@map("attachments")
}

// ============================================================
// AI Drafts
// ============================================================
model AiDraft {
  id         String    @id @default(uuid())
  ticketId   String    @map("ticket_id")
  tenantId   String    @map("tenant_id")
  messageId  String    @map("message_id")
  draftBody  String    @map("draft_body")
  confidence Float     @default(0)
  model      String    @default("gpt-4o")
  tokensUsed Int       @default(0) @map("tokens_used")
  status     String    @default("pending")
  editedBody String?   @map("edited_body")
  reviewedBy String?   @map("reviewed_by")
  reviewedAt DateTime? @map("reviewed_at")
  createdAt  DateTime  @default(now()) @map("created_at")

  tenant  Tenant  @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  ticket  Ticket  @relation(fields: [ticketId], references: [id], onDelete: Cascade)
  message Message @relation(fields: [messageId], references: [id], onDelete: Cascade)

  @@index([ticketId])
  @@index([tenantId])
  @@map("ai_drafts")
}

// ============================================================
// Knowledge Base
// ============================================================
model KnowledgeBaseEntry {
  id        String   @id @default(uuid())
  tenantId  String   @map("tenant_id")
  title     String
  content   String
  category  String   @default("general")
  isActive  Boolean  @default(true) @map("is_active")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@index([tenantId])
  @@map("knowledge_base")
}

// ============================================================
// Audit Log
// ============================================================
model AuditLog {
  id        String   @id @default(uuid())
  tenantId  String   @map("tenant_id")
  userId    String?  @map("user_id")
  action    String
  entity    String
  entityId  String?  @map("entity_id")
  details   Json?
  ipAddress String?  @map("ip_address")
  userAgent String?  @map("user_agent")
  createdAt DateTime @default(now()) @map("created_at")

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

  @@index([tenantId, createdAt])
  @@index([tenantId, entity])
  @@map("audit_log")
}

// ============================================================
// Customer Profiles
// ============================================================
model CustomerProfile {
  id             String   @id @default(uuid())
  tenantId       String   @map("tenant_id")
  email          String
  name           String?
  phone          String?
  company        String?
  notes          String?
  tags           String[] @default([])
  ticketCount    Int      @default(0) @map("ticket_count")
  firstContactAt DateTime @default(now()) @map("first_contact_at")
  lastContactAt  DateTime @default(now()) @map("last_contact_at")
  createdAt      DateTime @default(now()) @map("created_at")
  updatedAt      DateTime @updatedAt @map("updated_at")

  tenant  Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  tickets Ticket[]

  @@unique([tenantId, email])
  @@index([tenantId])
  @@map("customer_profiles")
}

// ============================================================
// Canned Responses
// ============================================================
model CannedResponse {
  id         String   @id @default(uuid())
  tenantId   String   @map("tenant_id")
  title      String
  body       String
  category   String   @default("general")
  shortcut   String?
  createdBy  String   @map("created_by")
  isShared   Boolean  @default(true) @map("is_shared")
  usageCount Int      @default(0) @map("usage_count")
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @updatedAt @map("updated_at")

  tenant  Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  creator User   @relation(fields: [createdBy], references: [id])

  @@index([tenantId])
  @@map("canned_responses")
}

// ============================================================
// Notification Preferences
// ============================================================
model NotificationPreference {
  id                 String   @id @default(uuid())
  userId             String   @unique @map("user_id")
  tenantId           String   @map("tenant_id")
  newTicket          Boolean  @default(true) @map("new_ticket")
  ticketAssigned     Boolean  @default(true) @map("ticket_assigned")
  ticketReply        Boolean  @default(true) @map("ticket_reply")
  aiDraftReady       Boolean  @default(true) @map("ai_draft_ready")
  dailyDigest        Boolean  @default(false) @map("daily_digest")
  emailNotifications Boolean  @default(true) @map("email_notifications")
  createdAt          DateTime @default(now()) @map("created_at")
  updatedAt          DateTime @updatedAt @map("updated_at")

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

  @@index([tenantId])
  @@map("notification_preferences")
}