schema.prisma

generator client {
  provider = "prisma-client-js"
  output   = "./node_modules/.prisma/client"
}

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

model User {
  id            String         @id @default(cuid())
  email         String         @unique
  name          String
  password      String
  role          Role           @default(USER)
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
  employeeId    String?        @unique
  
  // 🏢 COMPANY FIELDS - ADDED FOR PROFILE FUNCTIONALITY:
  companyName               String?
  companyAddress            String?
  companyPhone              String?
  companyEmail              String?
  companyWebsite            String?
  companyVatNumber          String?
  companyLogo               String?  @db.Text  // For large base64 images
  useCompanyDetailsOnQuotes Boolean? @default(false)  // ← CRITICAL: The missing checkbox field!
  
  uploadedDocs  Document[]     @relation("DocumentUser")
  jobHistory    JobHistory[]   @relation("JobHistoryUser")
  createdOrders Order[]        @relation("CreatedBy")
  managedOrders Order[]        @relation("ProjectOwner")
  orderHistory  OrderHistory[] @relation("OrderHistoryUser")
  createdQuotes Quote[]
  quoteHistory  QuoteHistory[] @relation("QuoteHistoryUser")
  timeEntries   TimeEntry[]
  employee      Employee?      @relation(fields: [employeeId], references: [id])
}

model Employee {
  id                      String    @id @default(cuid())
  name                    String
  email                   String?   @unique
  phone                   String?
  jobTitle                String
  technicalQualifications String[]
  isActive                Boolean   @default(true)
  hireDate                DateTime?
  terminationDate         DateTime?
  notes                   String?
  userId                  String?   @unique
  createdAt               DateTime  @default(now())
  updatedAt               DateTime  @updatedAt
  user                    User?

  @@index([jobTitle])
  @@index([isActive])
}

model Job {
  id              String        @id @default(cuid())
  title           String
  customerReference String?      // ADD THIS - Inherited from quote/order
  description     String?
  status          JobStatus     @default(ACTIVE)
  customerId      String
  startDate       DateTime?
  expectedEndDate DateTime?
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt
  currentVersion  Int           @default(1)
  documents       Document[]
  customer        Customer      @relation(fields: [customerId], references: [id])
  costs           JobCost[]
  history         JobHistory[]
  materials       JobMaterial[]
  orders          Order[]
  timeEntries     TimeEntry[]

  @@index([customerId])
}

model JobCost {
  id          String   @id @default(cuid())
  jobId       String
  description String
  amount      Float
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  costDate    DateTime @default(now())
  category    String?
  job         Job      @relation(fields: [jobId], references: [id], onDelete: Cascade)

  @@index([jobId])
}

model JobMaterial {
  id                String    @id @default(cuid())
  jobId             String
  materialId        String
  quantityNeeded    Float     @default(1)
  quantityUsed      Float     @default(0)
  quantityAllocated Float     @default(0)
  unitCost          Float?
  totalCost         Float?
  allocatedAt       DateTime?
  usedAt            DateTime?
  notes             String?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  job               Job       @relation(fields: [jobId], references: [id], onDelete: Cascade)
  material          Material  @relation(fields: [materialId], references: [id], onDelete: Cascade)

  @@unique([jobId, materialId])
  @@index([jobId])
  @@index([materialId])
}

model Customer {
  id                 String          @id @default(cuid())
  name               String
  email              String?         @unique
  phone              String?
  address            String?
  importSource       String?
  status             String?
  totalOrders        Int?
  totalSpent         Float?
  lastOrderDate      DateTime?
  createdAt          DateTime        @default(now())
  updatedAt          DateTime        @updatedAt
  billingAddress     String?
  creditLimit        Float?
  discountPercentage Float?
  paymentTerms       PaymentTerms?
  shippingAddress    String?
  specialTermsNotes  String?
  contactPersons     ContactPerson[]
  jobs               Job[]
  materials          Material[]
  orders             Order[]
  quotes             Quote[]

  @@index([email])
}

model ContactPerson {
  id         String       @id @default(cuid())
  name       String
  email      String?
  phone      String?
  role       ContactRole?
  notes      String?
  isPrimary  Boolean      @default(false)
  customerId String
  createdAt  DateTime     @default(now())
  updatedAt  DateTime     @updatedAt
  customer   Customer     @relation(fields: [customerId], references: [id], onDelete: Cascade)

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

model Order {
  id                 String             @id @default(cuid())
  projectTitle       String
  quoteRef           String
  customerReference  String?            // ADD THIS - Customer's own reference number
  orderType          OrderType          @default(CUSTOMER_LINKED)
  status             OrderStatus        @default(IN_PRODUCTION)
  customerName       String
  contactPerson      String?
  contactPhone       String?
  contactEmail       String?
  projectValue       Float
  marginPercent      Float?
  leadTimeWeeks      Int?
  items              Json
  costBreakdown      Json?
  paymentTerms       PaymentTerms       @default(THIRTY_DAYS)
  customPaymentTerms String?
  currency           String             @default("GBP")
  exchangeRate       Float?
  vatRate            Float?
  subTotal           Float              @default(0)
  totalTax           Float              @default(0)
  totalAmount        Float              @default(0)
  profitMargin       Float              @default(0)
  discounts          Json?
  paymentSchedule    Json?
  budgetAllocations  Json?
  notes              String?
  customerId         String?
  jobId              String?
  projectOwnerId     String
  createdById        String
  createdAt          DateTime           @default(now())
  updatedAt          DateTime           @updatedAt
  sourceQuoteId      String?
  currentVersion     Int                @default(1)
  documents          Document[]
  createdBy          User               @relation("CreatedBy", fields: [createdById], references: [id])
  customer           Customer?          @relation(fields: [customerId], references: [id])
  job                Job?               @relation(fields: [jobId], references: [id])
  projectOwner       User               @relation("ProjectOwner", fields: [projectOwnerId], references: [id])
  sourceQuote        Quote?             @relation(fields: [sourceQuoteId], references: [id])
  history            OrderHistory[]
  paymentMilestones  PaymentMilestone[]

  @@index([customerId])
  @@index([jobId])
  @@index([projectOwnerId])
  @@index([createdById])
  @@index([status])
  @@index([quoteRef])
  @@index([sourceQuoteId])
}

model ImportLog {
  id        String       @id @default(cuid())
  filename  String
  status    ImportStatus
  records   Int
  errors    Json?
  createdAt DateTime     @default(now())
}

model RegionalTaxSetting {
  id              String   @id @default(cuid())
  country         String
  region          String?
  standardVatRate Float
  reducedVatRate  Float?
  taxCode         String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
}

model CurrencyRate {
  id           String   @id @default(cuid())
  fromCurrency String
  toCurrency   String
  rate         Float
  validFrom    DateTime
  validTo      DateTime
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
}

model PaymentMilestone {
  id          String          @id @default(cuid())
  orderId     String
  description String
  amount      Float
  dueDate     DateTime
  status      MilestoneStatus @default(PENDING)
  paidDate    DateTime?
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  order       Order           @relation(fields: [orderId], references: [id], onDelete: Cascade)

  @@index([orderId])
}

model Supplier {
  id        String         @id @default(cuid())
  name      String
  email     String?        @unique
  phone     String?
  address   String?
  rating    Float?
  status    SupplierStatus @default(ACTIVE)
  createdAt DateTime       @default(now())
  updatedAt DateTime       @updatedAt
  materials Material[]

  @@index([email])
}

model Material {
  id                    String          @id @default(cuid())
  code                  String          @unique
  name                  String
  description           String?
  unitPrice             Float
  unit                  String
  supplierId            String?
  createdAt             DateTime        @default(now())
  updatedAt             DateTime        @updatedAt
  currentStock          Int             @default(0)
  minStock              Int             @default(0)
  category              String?
  customerMarkupPercent Float?
  inventoryPurpose      String?         @default("INTERNAL")
  isOrderable           Boolean?        @default(true)
  isQuotable            Boolean?        @default(false)
  leadTimeInDays        Int?            @default(0)
  reorderPoint          Int?            @default(0)
  visibleToCustomers    Boolean?        @default(false)
  customerId            String?
  manufacturer          String?
  productSpecifications String?
  jobMaterials          JobMaterial[]
  customer              Customer?       @relation(fields: [customerId], references: [id])
  supplier              Supplier?       @relation(fields: [supplierId], references: [id])
  quoteLineItems        QuoteLineItem[]

  @@index([supplierId])
  @@index([customerId])
  @@index([code])
}

model CompanySettings {
  id                    String   @id @default(cuid())
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt
  lastQuoteReferenceSeq Int      @default(0)
  quoteReferencePrefix  String   @default("QR")
  quoteNumberPadding    Int      @default(4)  // ADD THIS LINE
  companyAddress        String?
  companyEmail          String?
  companyLogo           String?
  companyName           String?
  companyPhone          String?
  companyVatNumber      String?
  companyWebsite        String?
  defaultVatRate        Float?   @default(20.0)
  lastQuoteNumber       Int      @default(0)
  quoteNumberFormat     String   @default("{PREFIX}-{NUMBER}")
  quoteNumberPrefix     String   @default("Q")
  
  // ADD THESE NEW FIELDS:
  standardWarranty         String?
  standardDeliveryTerms    String? 
  defaultLeadTimeWeeks     Int?
  standardExclusions       String?
  useCompanyDetailsOnQuotes Boolean? @default(false)
}

model Quote {
  id                String          @id @default(cuid())
  customerId        String
  title             String
  description       String?
  notes             String?         // Add this line
  termsAndConditions String?  
  paymentTerms       String?  // ADD THIS
  deliveryTerms      String?  // ADD THIS
  warranty           String?  // ADD THIS
  exclusions         String?  // ADD THIS
  status            QuoteStatus     @default(DRAFT)
  totalAmount       Float           @default(0)
  validUntil        DateTime?
  createdById       String
  createdAt         DateTime        @default(now())
  updatedAt         DateTime        @updatedAt
  customerReference String?
  quoteNumber       String          @unique
  contactEmail      String?
  contactPerson     String?
  contactPhone      String?
  changeReason      String?
  isLatestVersion   Boolean         @default(true)
  parentQuoteId     String?
  quoteReference    String
  versionNumber     Int             @default(1)
  currentVersion    Int             @default(1)
  documents         Document[]
  orders            Order[]
  createdBy         User            @relation(fields: [createdById], references: [id])
  customer          Customer        @relation(fields: [customerId], references: [id], onDelete: Cascade)
  parentQuote       Quote?          @relation("QuoteRevisions", fields: [parentQuoteId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  childQuotes       Quote[]         @relation("QuoteRevisions")
  history           QuoteHistory[]
  lineItems         QuoteLineItem[]

  @@index([quoteReference])
  @@index([customerId])
  @@index([createdById])
  @@index([status])
  @@index([parentQuoteId])
}

model QuoteLineItem {
  id          String    @id @default(cuid())
  quoteId     String
  description String
  quantity    Float     @default(1)
  unitPrice   Float     @default(0)
  materialId  String?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  material    Material? @relation(fields: [materialId], references: [id])
  quote       Quote     @relation(fields: [quoteId], references: [id], onDelete: Cascade)

  @@index([quoteId])
  @@index([materialId])
}

model QuoteHistory {
  id                String    @id @default(cuid())
  quoteId           String
  version           Int
  status            String
  data              Json
  changeType        String
  changedBy         String
  changeReason      String?
  customerApproved  Boolean?
  customerSignature String?
  approvalTimestamp DateTime?
  createdAt         DateTime  @default(now())
  ipAddress         String?
  userAgent         String?
  changedByUser     User      @relation("QuoteHistoryUser", fields: [changedBy], references: [id])
  quote             Quote     @relation(fields: [quoteId], references: [id], onDelete: Cascade)

  @@index([quoteId])
  @@index([changedBy])
  @@index([createdAt])
}

model OrderHistory {
  id                String    @id @default(cuid())
  orderId           String
  version           Int
  status            String
  data              Json
  changeType        String
  changedBy         String
  changeReason      String?
  customerApproved  Boolean?
  customerSignature String?
  approvalTimestamp DateTime?
  createdAt         DateTime  @default(now())
  ipAddress         String?
  userAgent         String?
  changedByUser     User      @relation("OrderHistoryUser", fields: [changedBy], references: [id])
  order             Order     @relation(fields: [orderId], references: [id], onDelete: Cascade)

  @@index([orderId])
  @@index([changedBy])
  @@index([createdAt])
}

model JobHistory {
  id              String   @id @default(cuid())
  jobId           String
  version         Int
  status          String
  data            Json
  changeType      String
  changedBy       String
  changeReason    String?
  materialChanges Json?
  progressNotes   String?
  attachments     Json?
  createdAt       DateTime @default(now())
  ipAddress       String?
  userAgent       String?
  changedByUser   User     @relation("JobHistoryUser", fields: [changedBy], references: [id])
  job             Job      @relation(fields: [jobId], references: [id], onDelete: Cascade)

  @@index([jobId])
  @@index([changedBy])
  @@index([createdAt])
}

model Document {
  id              String   @id @default(cuid())
  name            String
  originalName    String
  mimeType        String
  fileSize        Int
  fileHash        String
  storagePath     String
  storageType     String   @default("LOCAL")
  category        String
  quoteId         String?
  orderId         String?
  jobId           String?
  isLegalDocument Boolean  @default(false)
  retentionPeriod Int?
  uploadedBy      String
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  job             Job?     @relation(fields: [jobId], references: [id])
  order           Order?   @relation(fields: [orderId], references: [id])
  quote           Quote?   @relation(fields: [quoteId], references: [id])
  uploadedByUser  User     @relation("DocumentUser", fields: [uploadedBy], references: [id])

  @@index([quoteId])
  @@index([orderId])
  @@index([jobId])
  @@index([uploadedBy])
  @@index([category])
}

model TimeEntry {
  id            String   @id @default(cuid())
  employeeId    String   @map("employee_id")
  jobId         String   @map("job_id")
  date          DateTime @db.Date
  hours         Float
  isRdActivity  Boolean  @default(false) @map("is_rd_activity")
  rdDescription String?  @map("rd_description")
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  employee      User     @relation(fields: [employeeId], references: [id])
  job           Job      @relation(fields: [jobId], references: [id])

  @@index([employeeId])
  @@index([jobId])
  @@index([date])
  @@index([isRdActivity])
}

enum Role {
  USER
  ADMIN
}

enum OrderType {
  JOB_LINKED
  CUSTOMER_LINKED
  INTERNAL
}

enum OrderStatus {
  IN_PRODUCTION
  ON_HOLD
  READY_FOR_DELIVERY
  DELIVERED
  COMPLETED
}

enum JobStatus {
  ACTIVE
  DRAFT
  PENDING
  IN_PROGRESS
  CANCELED
}

enum ImportStatus {
  SUCCESS
  PARTIAL
  FAILED
}

enum PaymentTerms {
  WITH_ORDER
  PRIOR_TO_DISPATCH
  THIRTY_DAYS
  SIXTY_DAYS
  NINETY_DAYS
  CUSTOM
  SPLIT_50_50
  SPLIT_50_40_10
}

enum SupplierStatus {
  ACTIVE
  INACTIVE
  SUSPENDED
  UNDER_REVIEW
  BLACKLISTED
}

enum MilestoneStatus {
  PENDING
  OVERDUE
  PAID
  CANCELLED
}

enum QuoteStatus {
  DRAFT
  SENT
  APPROVED
  DECLINED
  EXPIRED
  CONVERTED
  PENDING
}

enum ContactRole {
  ACCOUNTS
  DELIVERIES
  PRIMARY_BUYER
  TECHNICAL_CONTACT
  SITE_CONTACT
  PROJECT_MANAGER
  GENERAL_INQUIRY
  OTHER
  PRIMARY
  BILLING
  TECHNICAL
  SALES
  SUPPORT
}