millions/prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

// User represents the authenticated user of the application
// In V1 there may be only one admin user, but the model allows for future growth
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  // Relations
  receipts   Receipt[]
  createdReceipts Receipt[] @relation("ReceiptCreatedBy")
  statusHistories StatusHistory[]

  @@map("users")
}

// Supplier represents a clothing supplier
model Supplier {
  id          String   @id @default(cuid())
  name        String
  contactName String?  @map("contact_name")
  email       String?
  phone       String?
  notes       String?
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  // Relations
  purchaseOrders PurchaseOrder[]

  @@map("suppliers")
}

// PurchaseOrder represents an order placed with a supplier
model PurchaseOrder {
  id                String   @id @default(cuid())
  supplierId        String   @map("supplier_id")
  externalReference String?  @map("external_reference")
  status            PurchaseOrderStatus @default(DRAFT)
  orderedAt         DateTime @map("ordered_at")
  expectedAt        DateTime? @map("expected_at")
  notes             String?
  createdAt         DateTime @default(now()) @map("created_at")
  updatedAt         DateTime @updatedAt @map("updated_at")

  // Relations
  supplier          Supplier          @relation(fields: [supplierId], references: [id], onDelete: RESTRICT)
  purchaseOrderItems PurchaseOrderItem[]
  receipts          Receipt[]
  inventoryUnits    InventoryUnit[]

  @@map("purchase_orders")
}

// PurchaseOrderItem represents a line item in a purchase order
model PurchaseOrderItem {
  id             String  @id @default(cuid())
  purchaseOrderId String  @map("purchase_order_id")
  productModelId String  @map("product_model_id")
  variantLabel   String? @map("variant_label")
  size           String?
  color          String?
  skuSupplier    String? @map("sku_supplier")
  quantityOrdered Int    @map("quantity_ordered")
  unitCost       Decimal @map("unit_cost") @db.Decimal(10, 2)
  notes          String?

  // Relations
  purchaseOrder PurchaseOrder @relation(fields: [purchaseOrderId], references: [id], onDelete: CASCADE)
  productModel  ProductModel  @relation(fields: [productModelId], references: [id], onDelete: RESTRICT)
  receiptItems  ReceiptItem[]

  @@map("purchase_order_items")
}

// Receipt represents a merchandise receipt event related to a purchase order
model Receipt {
  id           String      @id @default(cuid())
  purchaseOrderId String    @map("purchase_order_id")
  receiptDate  DateTime    @map("receipt_date")
  status       ReceiptStatus @default(PENDING)
  notes        String?
  createdBy    String      @map("created_by")
  createdAt    DateTime    @default(now()) @map("created_at")

  // Relations
  purchaseOrder PurchaseOrder @relation(fields: [purchaseOrderId], references: [id], onDelete: CASCADE)
  createdByUser  User         @relation(fields: [createdBy], references: [id], onDelete: RESTRICT)
  receiptItems  ReceiptItem[]
  inventoryUnits InventoryUnit[]

  @@map("receipts")
}

// ReceiptItem represents the quantity received of each purchase order line
model ReceiptItem {
  id                 String  @id @default(cuid())
  receiptId          String  @map("receipt_id")
  purchaseOrderItemId String  @map("purchase_order_item_id")
  quantityReceived   Int     @map("quantity_received")
  quantityRejected   Int     @default(0) @map("quantity_rejected")
  discrepancyNote    String? @map("discrepancy_note")

  // Relations
  receipt          Receipt          @relation(fields: [receiptId], references: [id], onDelete: CASCADE)
  purchaseOrderItem PurchaseOrderItem @relation(fields: [purchaseOrderItemId], references: [id], onDelete: CASCADE)
  inventoryUnits   InventoryUnit[]

  @@map("receipt_items")
}

// ProductModel represents the commercial article or model
model ProductModel {
  id           String   @id @default(cuid())
  name         String
  brand        String?
  category     String?
  defaultSize  String?  @map("default_size")
  defaultColor String?  @map("default_color")
  internalSku  String?  @map("internal_sku")
  supplierSku  String?  @map("supplier_sku")
  notes        String?
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @updatedAt @map("updated_at")

  // Relations
  purchaseOrderItems PurchaseOrderItem[]
  inventoryUnits     InventoryUnit[]

  @@map("product_models")
}

// InventoryUnit represents a concrete or logical traceable unit in inventory
// V1 should favor unit-level traceability whenever possible
model InventoryUnit {
  id                  String        @id @default(cuid())
  productModelId      String        @map("product_model_id")
  purchaseOrderId      String        @map("purchase_order_id")
  receiptItemId       String?       @map("receipt_item_id")
  inventoryStatus     InventoryStatus @default(RECEIVED)
  condition           String?
  storageLocation     String?       @map("storage_location")
  costPrice           Decimal       @map("cost_price") @db.Decimal(10, 2)
  readyForListingAt   DateTime?     @map("ready_for_listing_at")
  createdAt           DateTime      @default(now()) @map("created_at")
  updatedAt           DateTime      @updatedAt @map("updated_at")

  // Relations
  productModel   ProductModel   @relation(fields: [productModelId], references: [id], onDelete: RESTRICT)
  purchaseOrder  PurchaseOrder @relation(fields: [purchaseOrderId], references: [id], onDelete: RESTRICT)
  receiptItem    ReceiptItem?  @relation(fields: [receiptItemId], references: [id], onDelete: SET NULL)
  listing       Listing?
  sale          Sale?

  @@map("inventory_units")
}

// Listing represents a published or prepared advertisement
model Listing {
  id                String       @id @default(cuid())
  inventoryUnitId   String       @map("inventory_unit_id")
  platform          String?
  externalReference String?      @map("external_reference")
  listingStatus     ListingStatus @default(DRAFT)
  listedPrice       Decimal?     @map("listed_price") @db.Decimal(10, 2)
  listedAt          DateTime?    @map("listed_at")
  url               String?
  notes             String?

  // Relations
  inventoryUnit InventoryUnit @relation(fields: [inventoryUnitId], references: [id], onDelete: CASCADE)

  @@map("listings")
}

// Customer represents the buyer associated with a sale
model Customer {
  id              String   @id @default(cuid())
  name            String
  platformUsername String? @map("platform_username")
  shippingName    String?  @map("shipping_name")
  shippingAddress String?  @map("shipping_address")
  phone           String?
  email           String?
  notes           String?
  createdAt       DateTime @default(now()) @map("created_at")
  updatedAt       DateTime @updatedAt @map("updated_at")

  // Relations
  sales Sale[]

  @@map("customers")
}

// Sale represents the sale of a unit or article
model Sale {
  id           String      @id @default(cuid())
  inventoryUnitId String   @map("inventory_unit_id")
  customerId   String      @map("customer_id")
  saleChannel  String?     @map("sale_channel")
  saleStatus   SaleStatus  @default(DRAFT)
  salePrice    Decimal     @map("sale_price") @db.Decimal(10, 2)
  soldAt       DateTime    @map("sold_at")
  notes        String?
  createdAt    DateTime    @default(now()) @map("created_at")

  // Relations
  inventoryUnit InventoryUnit @relation(fields: [inventoryUnitId], references: [id], onDelete: RESTRICT)
  customer      Customer     @relation(fields: [customerId], references: [id], onDelete: RESTRICT)
  shipment      Shipment?

  @@map("sales")
}

// Shipment represents the shipping associated with a sale
model Shipment {
  id              String         @id @default(cuid())
  saleId          String         @map("sale_id")
  shipmentStatus  ShipmentStatus @default(NOT_STARTED)
  shippedAt       DateTime?      @map("shipped_at")
  trackingCode    String?        @map("tracking_code")
  carrier         String?
  labelReference  String?        @map("label_reference")
  notes           String?

  // Relations
  sale Sale @relation(fields: [saleId], references: [id], onDelete: CASCADE)

  @@map("shipments")
}

// StatusHistory represents the history of relevant status transitions
model StatusHistory {
  id         String   @id @default(cuid())
  entityType String   @map("entity_type")
  entityId   String   @map("entity_id")
  fromStatus String?  @map("from_status")
  toStatus   String   @map("to_status")
  changedBy  String   @map("changed_by")
  changedAt  DateTime @default(now()) @map("changed_at")
  note       String?

  // Relations
  user User @relation(fields: [changedBy], references: [id], onDelete: RESTRICT)

  @@index([entityType, entityId])
  @@index([changedAt])
  @@map("status_histories")
}

// Enums for status fields
enum PurchaseOrderStatus {
  DRAFT
  ORDERED
  PARTIALLY_RECEIVED
  RECEIVED
  CLOSED
  CANCELLED
}

enum ReceiptStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  COMPLETED_WITH_DISCREPANCIES
}

enum InventoryStatus {
  ORDERED
  RECEIVED
  REGISTERED
  READY_FOR_LISTING
  LISTED
  RESERVED
  SOLD
  PENDING_SHIPMENT
  SHIPPED
  COMPLETED
  BLOCKED
}

enum ListingStatus {
  DRAFT
  PUBLISHED
  SOLD
  REMOVED
}

enum SaleStatus {
  DRAFT
  CONFIRMED
  PENDING_SHIPMENT
  SHIPPED
  COMPLETED
  CANCELLED
}

enum ShipmentStatus {
  NOT_STARTED
  PENDING
  SHIPPED
  DELIVERED
  ISSUE
}