source: prisma/schema.prisma@ 38387ce

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

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

model Client {
  id             String       @id @default(uuid())
  tenantId       String       @map("tenant_id")
  name           String
  email          String       @unique
  address        Json
  logoUrl        String?      @map("logo_url")
  phoneNumber    String?      @map("phone_number")
  vatNumber      String?      @map("vat_number")
  companyNumber  String?      @map("company_number")
  representative String
  status         ClientStatus @default(active)
  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("due_date")
  status        InvoiceStatus
  currency      Currency
  quantityType  QuantityType  @map("quantity_type")
  subTotal      Float         @map("sub_total")
  issueDate     DateTime      @map("issue_date")
  month         Month
  discount      Float?
  taxes         Float?
  totalAmount   Float         @map("total_amount")
  invoiceNumber String        @unique @map("invoice_number")
  pdfRef        String?       @map("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
  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 Tenant {
  id                String     @id @default(cuid())
  name              String
  email             String     @unique
  address           Json
  bankAccounts      Json?      @map("bank_accounts")
  logoUrl           String?    @map("logo_url")
  phoneNumber       String?    @map("phone_number")
  vatNumber         String?    @map("vat_number")
  companyNumber     String?    @map("company_number")
  representative    String
  lastInvoiceNumber String     @default("0") @map("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 Employee {
  id        String         @id @default(uuid())
  name      String
  email     String         @unique
  status    EmployeeStatus @default(active)
  iban      String?
  cv        String?
  photo     String?
  project   String?
  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")
}