source: prisma/schema.prisma@ f021aa0

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

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

model Tenant {
  id                String     @id @default(cuid())
  name              String     @map("company_name")
  email             String     @unique @map("company_email")
  address           Json       @map("company_address")
  bankAccounts      Json?      @map("company_bank_accounts")
  logoUrl           String?    @map("company_logo_url")
  phoneNumber       String?    @map("company_phone_number")
  vatNumber         String?    @map("company_vat_number")
  companyNumber     String?    @map("company_no")
  representative    String     @map("company_representative")
  lastInvoiceNumber String     @default("0") @map("company_last_invoice_number")
  createdAt         DateTime   @default(now()) @map("created_at")
  updatedAt         DateTime   @updatedAt @map("updated_at")
  invoicesSent      Invoice[]
  services          Service[]
  employees         Employee[]
  clients           Client[]
  users             User[]
}

model Client {
  id             String       @id @default(uuid())
  tenantId       String       @map("tenant_id")
  name           String
  email          String       @unique
  address        Json
  logoUrl        String?      @map("client_logo_url")
  phoneNumber    String?      @map("client_phone_number")
  vatNumber      String?      @map("client_vat")
  companyNumber  String?      @map("client_company_no")
  representative String       @map("client_representative")
  status         ClientStatus @default(active) @map("client_status")
  createdAt      DateTime     @default(now()) @map("created_at")
  updatedAt      DateTime     @updatedAt @map("updated_at")

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

  // Note: Advanced indexes (partial, functional, case-insensitive) 
  // are handled via raw SQL in sql/02_client_email_index.sql

  // Basic indexes that Prisma can handle
  @@index([email]) // Basic email index
  @@index([email, tenantId]) // Composite email + tenant index
  @@index([status]) // Status index
  @@index([tenantId]) // Tenant index for multi-tenant queries
}

model Service {
  id       String @id @default(uuid())
  name     String
  sprint   Float
  hour     Float
  month    Float
  tenantId String @map("tenant_id")
  tenant   Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  lineItems LineItem[]
}

model Invoice {
  id            String        @id @default(uuid())
  sent          Int?
  dueDate       DateTime      @map("invoice_due_date")
  status        InvoiceStatus @map("invoice_status")
  currency      Currency      @map("invoice_currency")
  quantityType  QuantityType  @map("invoice_quantity_type")
  subTotal      Float         @map("invoice_sub_total")
  issueDate     DateTime      @map("invoice_issue_date")
  month         Month         @map("invoice_month")
  discount      Float?        @map("invoice_discount")
  taxes         Float?        @map("invoice_taxes")
  totalAmount   Float         @map("invoice_total_amount")
  invoiceNumber String        @unique @map("invoice_number")
  pdfRef        String?       @map("invoice_pdf_ref")
  createdAt     DateTime      @default(now()) @map("created_at")
  updatedAt     DateTime      @updatedAt @map("updated_at")

  invoiceFromId String @map("tenant_id")
  invoiceFrom   Tenant @relation(fields: [invoiceFromId], references: [id], onDelete: Cascade)

  invoiceToId String @map("client_id")
  invoiceTo   Client @relation("InvoiceTo", fields: [invoiceToId], references: [id], onDelete: Cascade)

  items LineItem[]

  // Performance indexes for common queries
  @@index([status]) // Status-based queries
  @@index([invoiceFromId]) // Tenant-based queries
  @@index([invoiceToId]) // Client-based queries
  @@index([issueDate]) // Date-based queries
  @@index([dueDate]) // Due date queries
  @@index([status, invoiceFromId]) // Combined status + tenant
  @@index([status, dueDate]) // Overdue invoice queries
}

model LineItem {
  id          String  @id @default(uuid())
  title       String
  price       Float   @map("unit_pricr")
  total       Float
  quantity    Int
  description String?
  serviceId   String  @map("service_id")
  service     Service @relation(fields: [serviceId], references: [id], onDelete: Cascade)
  invoiceId   String  @map("invoice_id")
  invoice     Invoice @relation(fields: [invoiceId], references: [id], onDelete: Cascade)
}

model Employee {
  id        String         @id @default(uuid())
  name      String         @map("employee_name")
  email     String         @unique @map("employee_email")
  status    EmployeeStatus @default(active) @map("employee_status")
  iban      String?        @map("employee_iban")
  cv        String?        @map("employee_cv_ref")
  photo     String?        @map("employee_photo_ref")
  project   String?        @map("employee_project")
  tenantId  String         @map("tenant_id")
  tenant    Tenant         @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  createdAt DateTime       @default(now()) @map("created_at")
  updatedAt DateTime       @updatedAt @map("updated_at")
}

model User {
  id          String   @id @default(uuid())
  uid         String   @unique // Firebase UID
  email       String   @unique
  displayName String   @map("display_name")
  role        UserRole
  tenantId    String   @map("tenant_id")
  tenant      Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
}

enum UserRole {
  ADMIN   @map("ADMIN")
  MANAGER @map("MANAGER")
  USER    @map("USER")
}

enum ClientStatus {
  active   @map("ACTIVE")
  banned   @map("BANNED")
  inactive @map("INACTIVE")
}

enum InvoiceStatus {
  draft      @map("DRAFT")
  processing @map("PROCESSING")
  pending    @map("PENDING")
  overdue    @map("OVERDUE")
  paid       @map("PAID")
}

enum Currency {
  EUR @map("EUR")
  USD @map("USD")
}

enum QuantityType {
  Unit   @map("UNIT")
  Hour   @map("HOUR")
  Sprint @map("SPRINT")
  Month  @map("MONTH")
}

enum Month {
  January   @map("JANUARY")
  February  @map("FEBRUARY")
  March     @map("MARCH")
  April     @map("APRIL")
  May       @map("MAY")
  June      @map("JUNE")
  July      @map("JULY")
  August    @map("AUGUST")
  September @map("SEPTEMBER")
  October   @map("OCTOBER")
  November  @map("NOVEMBER")
  December  @map("DECEMBER")
}

enum EmployeeStatus {
  active   @map("ACTIVE")
  inactive @map("INACTIVE")
}