erp-system/prisma/schema.prisma

// /opt/erp-system/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
}

enum TicketStatus {
  OPEN
  IN_PROGRESS
  WAITING_FOR_CUSTOMER
  RESOLVED
  CLOSED
}

enum TicketPriority {
  LOW
  MEDIUM
  HIGH
  CRITICAL
}

model Role {
  id          Int      @id @default(autoincrement())
  name        String   @unique
  permissions String[] 
  createdAt   DateTime @default(now())
  users       User[]
}

model User {
  id           Int           @id @default(autoincrement())
  email        String        @unique
  passwordHash String
  firstName    String
  lastName     String
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
  
  roleId       Int?
  role         Role?         @relation(fields: [roleId], references: [id])
  
  tickets      Ticket[]      @relation("AssignedTickets")
  timeEntries  TimeEntry[]
  notes        TicketNote[]
  createdSalesDocs SalesDocument[] @relation("CreatedSalesDocs")
}

model Customer {
  id                  Int               @id @default(autoincrement())
  companyName         String?
  firstName           String
  lastName            String
  email               String            @unique
  additionalEmails    String[]
  phone               String?
  address             String?
  city                String?
  zipCode             String?
  passwordHash        String?
  forcePasswordChange Boolean           @default(true) // NEU: Zwingt zum Wechsel
  createdAt           DateTime          @default(now())
  updatedAt           DateTime          @updatedAt
  
  tickets             Ticket[]
  contacts            CustomerContact[]
  contracts           Contract[]
  documents           CustomerDocument[]
  credentials         CustomerCredential[]
  salesDocuments      SalesDocument[]
}

model CustomerContact {
  id           Int      @id @default(autoincrement())
  firstName    String
  lastName     String
  email        String   @unique
  phone        String?
  isPortalUser Boolean  @default(false)
  passwordHash String?
  createdAt    DateTime @default(now())
  
  customerId   Int
  customer     Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
}

model Ticket {
  id           Int             @id @default(autoincrement())
  title        String
  description  String
  internalNote String?         
  status       TicketStatus    @default(OPEN)
  priority     TicketPriority  @default(MEDIUM)
  createdAt    DateTime        @default(now())
  updatedAt    DateTime        @updatedAt
  
  customerId   Int
  customer     Customer        @relation(fields: [customerId], references: [id])
  
  assignedToId Int?
  assignedTo   User?           @relation("AssignedTickets", fields: [assignedToId], references: [id])
  
  timeEntries  TimeEntry[]
  messages     TicketMessage[]
  survey       TicketSurvey?
  notes        TicketNote[]
  attachments  Attachment[]
}

model TicketMessage {
  id             Int      @id @default(autoincrement())
  content        String
  isFromCustomer Boolean  @default(false)
  createdAt      DateTime @default(now())
  
  ticketId       Int
  ticket         Ticket   @relation(fields: [ticketId], references: [id])
}

model Attachment {
  id          Int      @id @default(autoincrement())
  fileName    String   
  savedName   String   
  fileSize    Int      
  fileType    String   
  createdAt   DateTime @default(now())

  ticketId    Int
  ticket      Ticket   @relation(fields: [ticketId], references: [id])
}

model TicketSurvey {
  id        Int      @id @default(autoincrement())
  rating    Int      
  comment   String?
  createdAt DateTime @default(now())
  
  ticketId  Int      @unique
  ticket    Ticket   @relation(fields: [ticketId], references: [id])
}

model TimeEntry {
  id           Int           @id @default(autoincrement())
  durationMins Int           
  description  String
  isBilled     Boolean       @default(false)
  billedAt     DateTime?                     
  createdAt    DateTime      @default(now())
  
  ticketId     Int
  ticket       Ticket        @relation(fields: [ticketId], references: [id])
  
  userId       Int
  user         User          @relation(fields: [userId], references: [id])
}

model TicketNote {
  id           Int           @id @default(autoincrement())
  content      String
  createdAt    DateTime      @default(now())
  
  ticketId     Int
  ticket       Ticket        @relation(fields: [ticketId], references: [id])
  
  userId       Int
  user         User          @relation(fields: [userId], references: [id])
}

model SystemSettings {
  id           Int      @id @default(1)
  hourlyRate   Float    @default(95.0)
  taxRate      Float    @default(19.0)
  companyName  String?
  companyInfo  String?
  
  smtpHost     String?
  smtpPort     Int      @default(587)
  smtpUser     String?
  smtpPass     String?
  smtpFrom     String?

  imapHost     String?
  imapPort     Int      @default(993)
  imapUser     String?
  imapPass     String?

  // Nummernkreise
  nextQuoteNumber  Int     @default(1)
  nextOrderNumber  Int     @default(1)
  nextDeliveryNumber Int   @default(1)
  nextInvoiceNumber Int    @default(1)
  nextCreditNoteNumber Int @default(1)
  defaultQuoteValidityDays Int @default(14)

  updatedAt    DateTime @updatedAt
}

model Contract {
  id           Int      @id @default(autoincrement())
  title        String
  description  String?
  startDate    DateTime
  endDate      DateTime?
  monthlyPrice Float    @default(0)
  status       String   @default("ACTIVE")
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  
  customerId   Int
  customer     Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
}

model CustomerDocument {
  id          Int      @id @default(autoincrement())
  fileName    String   
  savedName   String   
  fileSize    Int      
  fileType    String   
  createdAt   DateTime @default(now())

  customerId  Int
  customer    Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
}

model CustomerCredential {
  id          Int      @id @default(autoincrement())
  title       String
  username    String
  password    String
  description String?
  createdAt   DateTime @default(now())

  customerId  Int
  customer    Customer @relation(fields: [customerId], references: [id], onDelete: Cascade)
}

// ── WARENWIRTSCHAFT ──

model Product {
  id              Int      @id @default(autoincrement())
  name            String
  description     String?
  sku             String?  @unique
  imagePath       String?
  purchasePrice   Float    @default(0)
  salePrice       Float    @default(0)
  stock           Int      @default(0)
  reservedStock   Int      @default(0)
  unit            String   @default("Stk")
  taxRate         Float    @default(19)
  trackStock      Boolean  @default(true)
  category        String?
  active          Boolean  @default(true)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  salesItems      SalesDocumentItem[]
}

enum SalesDocType {
  QUOTE
  ORDER_CONFIRMATION
  DELIVERY_NOTE
  INVOICE
  CREDIT_NOTE
}

enum SalesDocStatus {
  DRAFT
  SENT
  ACCEPTED
  REJECTED
  DELIVERED
  PAID
  CANCELLED
  ARCHIVED
}

model SalesDocument {
  id              Int            @id @default(autoincrement())
  type            SalesDocType
  number          String         @unique
  status          SalesDocStatus @default(DRAFT)
  previousStatus  String?
  subtotal        Float          @default(0)
  taxAmount       Float          @default(0)
  total           Float          @default(0)
  notes           String?
  validUntil      DateTime?
  signatureData   String?
  signedAt        DateTime?
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt

  customerId      Int
  customer        Customer       @relation(fields: [customerId], references: [id])

  createdById     Int?
  createdBy       User?          @relation("CreatedSalesDocs", fields: [createdById], references: [id])

  sourceDocumentId Int?

  items           SalesDocumentItem[]
}

model SalesDocumentItem {
  id              Int            @id @default(autoincrement())
  description     String
  quantity        Float          @default(1)
  unitPrice       Float          @default(0)
  taxRate         Float          @default(19)
  total           Float          @default(0)

  salesDocumentId Int
  salesDocument   SalesDocument  @relation(fields: [salesDocumentId], references: [id], onDelete: Cascade)

  productId       Int?
  product         Product?       @relation(fields: [productId], references: [id])
}