torqvoice/prisma/schema/messaging.prisma

model Notification {
  id             String   @id @default(cuid())
  type           String
  title          String
  message        String
  entityType     String
  entityId       String
  entityUrl      String
  read           Boolean  @default(false)
  organizationId String
  createdAt      DateTime @default(now())

  @@index([organizationId, createdAt(sort: Desc)])
  @@index([organizationId, read])
  @@map("notifications")
}

model SmsMessage {
  id                String    @id @default(cuid())
  direction         String // "inbound" | "outbound"
  fromNumber        String
  toNumber          String
  body              String
  status            String    @default("queued") // queued | sent | delivered | failed | received
  providerMsgId     String?
  errorMessage      String?
  relatedEntityType String?
  relatedEntityId   String?
  /// When the workshop first opened the thread this arrived in. Null on an
  /// inbound message nobody has read yet; never set on an outbound row.
  readAt            DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  customerId String?
  customer   Customer? @relation(fields: [customerId], references: [id], onDelete: SetNull)

  @@index([organizationId, customerId, createdAt(sort: Desc)])
  @@index([organizationId, createdAt(sort: Desc)])
  @@index([toNumber, organizationId])
  @@index([organizationId, readAt])
  @@map("sms_messages")
}

model TelegramMessage {
  id                String    @id @default(cuid())
  direction         String // "inbound" | "outbound"
  chatId            String // Telegram chat ID
  body              String
  status            String    @default("queued") // queued | sent | delivered | failed | received
  telegramMessageId String? // Telegram's message ID for tracking
  errorMessage      String?
  relatedEntityType String?
  relatedEntityId   String?
  /// When the workshop first opened the thread this arrived in. Null on an
  /// inbound message nobody has read yet; never set on an outbound row.
  readAt            DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  customerId String?
  customer   Customer? @relation(fields: [customerId], references: [id], onDelete: SetNull)

  @@index([organizationId, customerId, createdAt(sort: Desc)])
  @@index([organizationId, chatId, createdAt(sort: Desc)])
  @@index([organizationId, readAt])
  @@map("telegram_messages")
}

/// One WhatsApp message in either direction.
///
/// `provider` records which adapter carried it, because a workshop can switch
/// provider without its history becoming unreadable, and a status callback
/// arriving later has to find the row it belongs to.
model WhatsappMessage {
  id        String @id @default(cuid())
  direction String // "inbound" | "outbound"
  provider  String // "meta" | "twilio" | any adapter registered later

  /// E.164 without the "whatsapp:" prefix a provider may use on the wire.
  fromNumber String
  toNumber   String

  body String?

  /// Media the message carried. Inbound URLs may need the provider's
  /// credentials to fetch, so they are not necessarily public.
  mediaUrl      String?
  mediaType     String? // image | document | video | audio | sticker
  mediaFilename String?

  /// Set when the message had to go out as an approved template, which is the
  /// only thing WhatsApp allows outside the 24 hour service window.
  templateName String?

  status            String  @default("queued") // queued | sent | delivered | read | failed | received
  providerMessageId String?
  errorMessage      String?

  relatedEntityType String?
  relatedEntityId   String?

  /// When the workshop first opened the thread this arrived in. Null on an
  /// inbound message nobody has read yet; never set on an outbound row.
  readAt    DateTime?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  customerId String?
  customer   Customer? @relation(fields: [customerId], references: [id], onDelete: SetNull)

  @@index([organizationId, customerId, createdAt(sort: Desc)])
  @@index([organizationId, createdAt(sort: Desc)])
  @@index([providerMessageId])
  @@index([organizationId, readAt])
  @@map("whatsapp_messages")
}

/// A message the workshop lines up to go out on a given day and time, through
/// whichever channel suits the customer. One-off by default; a frequency other
/// than "once" reschedules itself after each send, the way ReportSchedule does.
model ScheduledMessage {
  id      String  @id @default(cuid())
  channel String // "email" | "sms" | "telegram" | "in_app"
  subject String? // email only
  body    String

  /// Where it goes when no customer is attached, or when the workshop typed an
  /// address by hand. Falls back to the customer's email/phone/chat id.
  recipient String?

  status       String    @default("scheduled") // scheduled | sent | failed | cancelled
  sendAt       DateTime
  frequency    String    @default("once") // once | daily | weekly | biweekly | monthly | yearly
  endDate      DateTime?
  lastRunAt    DateTime?
  sentAt       DateTime?
  runCount     Int       @default(0)
  errorMessage String?

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

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  customerId String?
  customer   Customer? @relation(fields: [customerId], references: [id], onDelete: SetNull)

  vehicleId String?
  vehicle   Vehicle? @relation(fields: [vehicleId], references: [id], onDelete: SetNull)

  createdById String

  inspectionReminder InspectionReminderSend?

  @@index([organizationId, sendAt])
  @@index([status, sendAt])
  @@map("scheduled_messages")
}

/// One email between a workshop and a customer, in either direction.
///
/// Only the cloud instance writes these, and only for a workshop that sends
/// through the platform's address: its customer mail carries a Reply-To of
/// `reply-<token>@<reply domain>`, and what comes back to that address is
/// filed here. A workshop with its own mail vendor gets replies in its own
/// mailbox, so nothing of its mail is recorded.
///
/// `kind` tells a document mail (an invoice, a quote, a scheduled message)
/// from a message written in the inbox. Document sends are kept so a reply
/// shows what it answers; a thread is listed only once it holds a message,
/// so sending invoices does not fill the inbox with one-sided threads.
model EmailMessage {
  id        String @id @default(cuid())
  direction String // "inbound" | "outbound"
  kind      String @default("message") // "message" | "document"

  /// The customer's address, lower-cased: who wrote in, or who was written to.
  address  String
  /// The display name the sender's mail client gave, on inbound rows.
  fromName String?
  subject  String?
  /// Plain text. An HTML-only mail is converted on receipt; HTML is never kept.
  body     String

  /// RFC 5322 Message-ID without the angle brackets: ours on outbound rows,
  /// theirs on inbound ones. A reply's In-Reply-To finds the row it answers.
  messageId         String?
  inReplyTo         String?
  /// The receiving service's own id for an inbound mail, which is also the
  /// stored object's key. Unique, so a redelivered notification files nothing.
  providerMessageId String? @unique
  /// What the receiving service's spam filter said: PASS, FAIL or GRAY.
  spamVerdict       String?

  status       String  @default("received") // queued | sent | failed | received
  errorMessage String?

  relatedEntityType String?
  relatedEntityId   String?

  /// When the workshop first opened the thread this arrived in. Null on an
  /// inbound message nobody has read yet; never set on an outbound row.
  readAt    DateTime?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  customerId String?
  customer   Customer? @relation(fields: [customerId], references: [id], onDelete: SetNull)

  attachments EmailAttachment[]

  @@index([organizationId, customerId, createdAt(sort: Desc)])
  @@index([organizationId, address, createdAt(sort: Desc)])
  @@index([organizationId, createdAt(sort: Desc)])
  @@index([organizationId, readAt])
  @@index([organizationId, messageId])
  @@map("email_messages")
}

/// A file that came with an inbound email, kept as one of the workshop's
/// uploads (folder `email`) and released through the file manager when the
/// message goes.
model EmailAttachment {
  id          String   @id @default(cuid())
  /// The name the sender gave it, cleaned for display; the file on disk has
  /// a generated name.
  fileName    String
  contentType String
  size        Int
  url         String
  createdAt   DateTime @default(now())

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

  @@index([messageId])
  @@map("email_attachments")
}