// NOTE: After adding a migration, update ERP_DB_VERSION in src/dbConfig.ts
// to match the new schema version number in the migration SQL.

datasource db {
  provider = "sqlite"
}

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

enum OrderStatus {
  active
  archived
}

enum RevisionStatus {
  draft
  approved
  obsolete
}

enum OrderRunStatus {
  released
  started
  closed
  cancelled
}

enum OrderRunPriority {
  low
  medium
  high
  critical
}

enum OperationRunStatus {
  blocked
  pending
  in_progress
  completed
  skipped
  failed
}

enum OperationRunCommentType {
  note
  issue
  feedback
}

enum FieldType {
  string
  number
  date
  datetime
  yesNo
  checkbox
  attachment
}

model Order {
  id          Int         @id @default(autoincrement())
  key         String      @unique
  description String      @default("")
  status      OrderStatus @default(active)
  itemId      Int?        @map("item_id")
  createdById Int         @map("created_by")
  createdAt   DateTime    @default(now()) @map("created_at")
  updatedById Int         @map("updated_by")
  updatedAt   DateTime    @updatedAt @map("updated_at")

  item      Item?           @relation(fields: [itemId], references: [id], onDelete: Restrict)
  createdBy User            @relation("orderCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy User            @relation("orderUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  revisions OrderRevision[]
  orderRuns OrderRun[]

  @@index([status]) // LIST filter in orders.ts
  @@index([createdAt]) // LIST orderBy in orders.ts
  @@index([itemId]) // FK lookup for item relation
  @@map("orders")
}

model OrderRevision {
  id            Int            @id @default(autoincrement())
  orderId       Int            @map("order_id")
  revNo         Int            @map("rev_no")
  status        RevisionStatus @default(draft)
  description   String         @default("")
  changeSummary String?        @map("change_summary")
  createdAt     DateTime       @default(now()) @map("created_at")
  createdById   Int            @map("created_by")
  updatedAt     DateTime       @updatedAt @map("updated_at")
  updatedById   Int            @map("updated_by")
  createdBy     User           @relation("orderRevCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy     User           @relation("orderRevUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  order         Order          @relation(fields: [orderId], references: [id], onDelete: Restrict)
  orderRuns     OrderRun[]
  operations    Operation[]

  @@unique([orderId, revNo])
  @@map("order_revisions")
}

model Operation {
  id           Int      @id @default(autoincrement())
  orderRevId   Int      @map("order_rev_id")
  workCenterId Int?     @map("work_center_id")
  seqNo        Int      @map("seq_no")
  title        String
  description  String   @default("")
  createdAt    DateTime @default(now()) @map("created_at")
  createdById  Int      @map("created_by")
  updatedAt    DateTime @updatedAt @map("updated_at")
  updatedById  Int      @map("updated_by")

  createdBy     User                  @relation("opCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy     User                  @relation("opUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  orderRev      OrderRevision         @relation(fields: [orderRevId], references: [id], onDelete: Restrict)
  workCenter    WorkCenter?           @relation(fields: [workCenterId], references: [id], onDelete: SetNull)
  steps         Step[]
  fieldRefs     OperationFieldRef[] @relation("opFieldRefs")
  operationRuns OperationRun[]
  predecessors  OperationDependency[] @relation("opSuccessor")
  successors    OperationDependency[] @relation("opPredecessor")

  @@unique([orderRevId, seqNo])
  @@map("operations")
}

model OperationFieldRef {
  id           Int      @id @default(autoincrement())
  operationId  Int      @map("operation_id")
  seqNo        Int      @map("seq_no")
  title        String
  sourceStepId Int      @map("source_step_id")
  createdAt    DateTime @default(now()) @map("created_at")
  createdById  Int      @map("created_by")

  operation  Operation @relation("opFieldRefs", fields: [operationId], references: [id], onDelete: Cascade)
  sourceStep Step      @relation("fieldRefSource", fields: [sourceStepId], references: [id], onDelete: Restrict)
  createdBy  User      @relation("fieldRefCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)

  @@unique([operationId, seqNo])
  @@unique([operationId, sourceStepId])
  @@map("operation_field_refs")
}

model OperationDependency {
  id            Int      @id @default(autoincrement())
  successorId   Int      @map("successor_id")
  predecessorId Int      @map("predecessor_id")
  createdAt     DateTime @default(now()) @map("created_at")
  createdById   Int      @map("created_by")

  successor   Operation @relation("opSuccessor", fields: [successorId], references: [id], onDelete: Cascade)
  predecessor Operation @relation("opPredecessor", fields: [predecessorId], references: [id], onDelete: Cascade)
  createdBy   User      @relation("opDepCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)

  @@unique([successorId, predecessorId])
  @@map("operation_dependencies")
}

model WorkCenter {
  id          Int      @id @default(autoincrement())
  key         String   @unique
  description String   @default("")
  createdAt   DateTime @default(now()) @map("created_at")
  createdById Int      @map("created_by")
  updatedAt   DateTime @updatedAt @map("updated_at")
  updatedById Int      @map("updated_by")

  createdBy       User             @relation("workCenterCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy       User             @relation("workCenterUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  userAssignments WorkCenterUser[]
  operations      Operation[]

  @@index([createdAt])
  @@map("work_centers")
}

model WorkCenterUser {
  workCenterId Int      @map("work_center_id")
  userId       Int      @map("user_id")
  createdAt    DateTime @default(now()) @map("created_at")
  createdById  Int      @map("created_by")

  workCenter WorkCenter @relation(fields: [workCenterId], references: [id], onDelete: Cascade)
  user       User       @relation("workCenterAssignment", fields: [userId], references: [id], onDelete: Cascade)
  createdBy  User       @relation("workCenterUserCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)

  @@id([workCenterId, userId])
  @@map("work_center_users")
}

model FieldSet {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now()) @map("created_at")
  createdById Int      @map("created_by")

  createdBy    User          @relation("fieldSetCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  fields       Field[]
  fieldRecords FieldRecord[]
  steps        Step[]
  items        Item[]

  @@map("field_sets")
}

model FieldRecord {
  id          Int      @id @default(autoincrement())
  fieldSetId  Int      @map("field_set_id")
  createdAt   DateTime @default(now()) @map("created_at")
  createdById Int      @map("created_by")

  createdBy     User           @relation("fieldRecordCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  fieldSet      FieldSet       @relation(fields: [fieldSetId], references: [id])
  fieldValues   FieldValue[]
  stepRuns      StepRun[]
  itemInstances ItemInstance[]

  @@map("field_records")
}

model Field {
  id          Int       @id @default(autoincrement())
  fieldSetId  Int       @map("field_set_id")
  seqNo       Int       @map("seq_no")
  label       String
  type        FieldType @default(string)
  isArray     Boolean   @default(false) @map("is_array")
  required    Boolean   @default(false)
  createdAt   DateTime  @default(now()) @map("created_at")
  createdById Int       @map("created_by")
  updatedAt   DateTime  @updatedAt @map("updated_at")
  updatedById Int       @map("updated_by")

  createdBy   User         @relation("fieldCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy   User         @relation("fieldUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  fieldSet    FieldSet     @relation(fields: [fieldSetId], references: [id], onDelete: Cascade)
  fieldValues FieldValue[]

  @@unique([fieldSetId, seqNo])
  @@map("fields")
}

model Step {
  id           Int      @id @default(autoincrement())
  operationId  Int      @map("operation_id")
  fieldSetId   Int?     @map("field_set_id")
  seqNo        Int      @map("seq_no")
  title        String   @default("")
  instructions String   @default("")
  multiSet     Boolean  @default(false) @map("multi_set")
  createdAt    DateTime @default(now()) @map("created_at")
  createdById  Int      @map("created_by")
  updatedAt    DateTime @updatedAt @map("updated_at")
  updatedById  Int      @map("updated_by")

  createdBy User       @relation("stepCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy User       @relation("stepUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  operation Operation  @relation(fields: [operationId], references: [id], onDelete: Cascade)
  fieldSet  FieldSet?  @relation(fields: [fieldSetId], references: [id])
  stepRuns  StepRun[]
  fieldRefSources OperationFieldRef[] @relation("fieldRefSource")

  @@unique([operationId, seqNo])
  @@map("steps")
}

model OrderRun {
  id               Int              @id @default(autoincrement())
  runNo            Int              @map("run_no")
  orderId          Int              @map("order_id")
  orderRevId       Int              @map("order_rev_id")
  status           OrderRunStatus   @default(released)
  priority         OrderRunPriority @default(medium)
  cost             Float?
  dueAt            String?          @map("due_at")
  releaseNote      String?        @map("release_note")
  createdAt        DateTime         @default(now()) @map("created_at")
  createdById      Int              @map("created_by")
  updatedAt        DateTime         @updatedAt @map("updated_at")
  updatedById      Int              @map("updated_by")

  createdBy     User           @relation("orderRunCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy     User           @relation("orderRunUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  order         Order          @relation(fields: [orderId], references: [id], onDelete: Restrict)
  orderRev      OrderRevision  @relation(fields: [orderRevId], references: [id], onDelete: Restrict)
  operationRuns OperationRun[]
  itemInstances ItemInstance[]

  @@unique([orderId, runNo])
  @@index([status]) // LIST filter in order-runs.ts
  @@index([priority]) // LIST filter in order-runs.ts
  @@index([createdAt]) // LIST orderBy in order-runs.ts
  @@index([orderRevId]) // FK lookup for orderRev relation
  @@map("order_runs")
}

model OperationRun {
  id          Int                @id @default(autoincrement())
  orderRunId  Int                @map("order_run_id")
  operationId Int                @map("operation_id")
  status       OperationRunStatus @default(pending)
  assignedToId Int?               @map("assigned_to_id")
  cost           Float?
  tokens         Int?
  statusNote     String?          @map("status_note")
  completedAt    DateTime?        @map("completed_at")
  createdAt   DateTime           @default(now()) @map("created_at")
  createdById Int                @map("created_by")
  updatedAt   DateTime           @updatedAt @map("updated_at")
  updatedById Int                @map("updated_by")

  assignedTo   User?         @relation("opRunAssignedTo", fields: [assignedToId], references: [id], onDelete: Restrict)
  createdBy    User          @relation("opRunCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy    User          @relation("opRunUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  orderRun     OrderRun      @relation(fields: [orderRunId], references: [id], onDelete: Restrict)
  operation    Operation     @relation(fields: [operationId], references: [id], onDelete: Restrict)
  stepRuns     StepRun[]
  laborTickets LaborTicket[]
  comments     OperationRunComment[]

  @@unique([orderRunId, operationId])
  @@index([status])
  @@map("operation_runs")
}

model StepRun {
  id             Int      @id @default(autoincrement())
  operationRunId Int      @map("operation_run_id")
  stepId         Int      @map("step_id")
  fieldRecordId  Int?     @map("field_record_id")
  completed      Boolean  @default(false)
  statusNote     String?  @map("status_note")
  createdAt      DateTime @default(now()) @map("created_at")
  createdById    Int      @map("created_by")
  updatedAt      DateTime @updatedAt @map("updated_at")
  updatedById    Int      @map("updated_by")

  createdBy    User          @relation("stepRunCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy    User          @relation("stepRunUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  operationRun OperationRun  @relation(fields: [operationRunId], references: [id], onDelete: Restrict)
  step         Step          @relation(fields: [stepId], references: [id], onDelete: Restrict)
  fieldRecord  FieldRecord?  @relation(fields: [fieldRecordId], references: [id])

  @@unique([operationRunId, stepId])
  @@map("step_runs")
}

model FieldValue {
  id            Int      @id @default(autoincrement())
  fieldRecordId Int      @map("field_record_id")
  fieldId       Int      @map("field_id")
  setIndex      Int      @default(0) @map("set_index")
  value         String   @default("")
  createdAt     DateTime @default(now()) @map("created_at")
  createdById   Int      @map("created_by")
  updatedAt     DateTime @updatedAt @map("updated_at")
  updatedById   Int      @map("updated_by")

  createdBy        User              @relation("fieldValueCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy        User              @relation("fieldValueUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  fieldRecord      FieldRecord       @relation(fields: [fieldRecordId], references: [id], onDelete: Restrict)
  field            Field             @relation(fields: [fieldId], references: [id], onDelete: Restrict)
  fieldAttachments FieldAttachment[]

  @@unique([fieldRecordId, fieldId, setIndex])
  @@map("field_values")
}

model Item {
  id          Int      @id @default(autoincrement())
  key         String   @unique
  description String   @default("")
  fieldSetId  Int?     @map("field_set_id")
  createdAt   DateTime @default(now()) @map("created_at")
  createdById Int      @map("created_by")
  updatedAt   DateTime @updatedAt @map("updated_at")
  updatedById Int      @map("updated_by")

  createdBy     User           @relation("itemCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy     User           @relation("itemUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)
  fieldSet      FieldSet?      @relation(fields: [fieldSetId], references: [id])
  orders        Order[]
  itemInstances ItemInstance[]

  @@index([createdAt])
  @@map("items")
}

model ItemInstance {
  id            Int      @id @default(autoincrement())
  itemId        Int      @map("item_id")
  orderRunId    Int?     @map("order_run_id")
  fieldRecordId Int?     @map("field_record_id")
  key           String
  quantity      Float?
  createdAt     DateTime @default(now()) @map("created_at")
  createdById   Int      @map("created_by")
  updatedAt     DateTime @updatedAt @map("updated_at")
  updatedById   Int      @map("updated_by")

  item        Item          @relation(fields: [itemId], references: [id], onDelete: Restrict)
  orderRun    OrderRun?     @relation(fields: [orderRunId], references: [id], onDelete: Restrict)
  fieldRecord FieldRecord?  @relation(fields: [fieldRecordId], references: [id])
  createdBy   User          @relation("itemInstanceCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy   User          @relation("itemInstanceUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)

  @@unique([itemId, key])
  @@index([orderRunId])
  @@map("item_instances")
}

model User {
  id           Int       @id @default(autoincrement())
  uuid         String    @unique
  username     String    @unique
  passwordHash String?   @map("password_hash")
  apiKeyHash   String?   @unique @map("api_key_hash")
  isAgent      Boolean   @default(false) @map("is_agent")
  createdAt    DateTime  @default(now()) @map("created_at")
  updatedAt    DateTime  @updatedAt @map("updated_at")
  deletedAt    DateTime? @map("deleted_at")

  itemsCreated           Item[]                @relation("itemCreatedBy")
  itemsUpdated           Item[]                @relation("itemUpdatedBy")
  ordersCreated          Order[]               @relation("orderCreatedBy")
  ordersUpdated          Order[]               @relation("orderUpdatedBy")
  orderRevsCreated       OrderRevision[]       @relation("orderRevCreatedBy")
  orderRevsUpdated       OrderRevision[]       @relation("orderRevUpdatedBy")
  orderRunsCreated       OrderRun[]            @relation("orderRunCreatedBy")
  orderRunsUpdated       OrderRun[]            @relation("orderRunUpdatedBy")
  opsCreated             Operation[]           @relation("opCreatedBy")
  opsUpdated             Operation[]           @relation("opUpdatedBy")
  opDepsCreated          OperationDependency[] @relation("opDepCreatedBy")
  fieldRefsCreated       OperationFieldRef[]   @relation("fieldRefCreatedBy")
  fieldSetsCreated       FieldSet[]            @relation("fieldSetCreatedBy")
  stepsCreated           Step[]                @relation("stepCreatedBy")
  stepsUpdated           Step[]                @relation("stepUpdatedBy")
  fieldsCreated          Field[]               @relation("fieldCreatedBy")
  fieldsUpdated          Field[]               @relation("fieldUpdatedBy")
  opRunsCreated          OperationRun[]        @relation("opRunCreatedBy")
  opRunsUpdated          OperationRun[]        @relation("opRunUpdatedBy")
  stepRunsCreated        StepRun[]             @relation("stepRunCreatedBy")
  stepRunsUpdated        StepRun[]             @relation("stepRunUpdatedBy")
  fieldRecordsCreated    FieldRecord[]         @relation("fieldRecordCreatedBy")
  fieldValuesCreated     FieldValue[]          @relation("fieldValueCreatedBy")
  fieldValuesUpdated     FieldValue[]          @relation("fieldValueUpdatedBy")
  itemInstancesCreated   ItemInstance[]        @relation("itemInstanceCreatedBy")
  itemInstancesUpdated   ItemInstance[]        @relation("itemInstanceUpdatedBy")
  laborTicketsWorked     LaborTicket[]         @relation("laborTicketUser")
  laborTicketsCreated    LaborTicket[]         @relation("laborTicketCreatedBy")
  laborTicketsUpdated    LaborTicket[]         @relation("laborTicketUpdatedBy")
  auditLogs              AuditLog[]
  sessions               Session[]
  permissions            UserPermission[]      @relation("userPermissions")
  grantedPermissions     UserPermission[]      @relation("permissionGrantedBy")
  attachmentsUploaded    Attachment[]          @relation("attachmentUploadedBy")
  commentsCreated        OperationRunComment[] @relation("commentCreatedBy")
  opRunsAssigned         OperationRun[]        @relation("opRunAssignedTo")
  workCentersCreated     WorkCenter[]          @relation("workCenterCreatedBy")
  workCentersUpdated     WorkCenter[]          @relation("workCenterUpdatedBy")
  workCenterAssignments  WorkCenterUser[]      @relation("workCenterAssignment")
  workCenterUsersCreated WorkCenterUser[]      @relation("workCenterUserCreatedBy")

  @@map("users")
}

enum ErpPermission {
  erp_admin
  order_planner
  order_executor
  order_manager
  item_manager
}

model UserPermission {
  id         Int           @id @default(autoincrement())
  userId     Int           @map("user_id")
  permission ErpPermission
  grantedAt  DateTime      @default(now()) @map("granted_at")
  grantedBy  Int?          @map("granted_by")

  user          User  @relation("userPermissions", fields: [userId], references: [id], onDelete: Cascade)
  grantedByUser User? @relation("permissionGrantedBy", fields: [grantedBy], references: [id], onDelete: SetNull)

  @@unique([userId, permission])
  @@map("user_permissions")
}

model Session {
  id        Int      @id @default(autoincrement())
  userId    Int      @map("user_id")
  tokenHash String   @unique @map("token_hash")
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

model SchemaVersion {
  id      Int    @id @default(1)
  version Int
  updated String

  @@map("schema_version")
}

model LaborTicket {
  id             Int       @id @default(autoincrement())
  operationRunId Int       @map("operation_run_id")
  userId         Int       @map("user_id")
  runId          Int?      @map("run_id")
  clockIn        DateTime  @map("clock_in")
  clockOut       DateTime? @map("clock_out")
  cost           Float?
  tokens         Int?
  createdAt      DateTime  @default(now()) @map("created_at")
  createdById    Int       @map("created_by")
  updatedAt      DateTime  @updatedAt @map("updated_at")
  updatedById    Int       @map("updated_by")

  operationRun OperationRun @relation(fields: [operationRunId], references: [id], onDelete: Restrict)
  user         User         @relation("laborTicketUser", fields: [userId], references: [id], onDelete: Restrict)
  createdBy    User         @relation("laborTicketCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)
  updatedBy    User         @relation("laborTicketUpdatedBy", fields: [updatedById], references: [id], onDelete: Restrict)

  @@index([operationRunId])
  @@index([userId, clockOut])
  @@map("labor_tickets")
}

model AuditLog {
  id         Int      @id @default(autoincrement())
  entityType String   @map("entity_type")
  entityId   Int      @map("entity_id")
  action     String
  field      String
  oldValue   String?  @map("old_value")
  newValue   String?  @map("new_value")
  userId     Int      @map("user_id")
  createdAt  DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Restrict)

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

model Attachment {
  id           Int      @id @default(autoincrement())
  publicId     String   @unique @map("public_id")
  filepath     String
  filename     String
  fileSize     Int      @map("file_size")
  fileHash     String   @map("file_hash")
  uploadedById Int      @map("uploaded_by")
  createdAt    DateTime @default(now()) @map("created_at")

  uploadedBy       User              @relation("attachmentUploadedBy", fields: [uploadedById], references: [id], onDelete: Restrict)
  fieldAttachments FieldAttachment[]

  @@index([fileHash])
  @@map("attachments")
}

model FieldAttachment {
  fieldValueId Int      @map("field_value_id")
  attachmentId Int      @map("attachment_id")
  createdAt    DateTime @default(now()) @map("created_at")

  fieldValue FieldValue @relation(fields: [fieldValueId], references: [id], onDelete: Cascade)
  attachment Attachment @relation(fields: [attachmentId], references: [id], onDelete: Restrict)

  @@id([fieldValueId, attachmentId])
  @@map("field_attachments")
}

model OperationRunComment {
  id             Int                     @id @default(autoincrement())
  operationRunId Int                     @map("operation_run_id")
  type           OperationRunCommentType @default(note)
  body           String
  createdAt      DateTime                @default(now()) @map("created_at")
  createdById    Int                     @map("created_by")

  operationRun OperationRun @relation(fields: [operationRunId], references: [id], onDelete: Restrict)
  createdBy    User         @relation("commentCreatedBy", fields: [createdById], references: [id], onDelete: Restrict)

  @@index([operationRunId])
  @@map("operation_run_comments")
}
