schema.prisma

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

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

// Authentication & Authorization Models

enum UserRole {
  REQUESTER // Can view catalogue, create loan requests
  IH // Can view catalogue, approve loans for their IHs
  LOGS // Full access: manage items, approve all loans
  ADMIN // Super admin: manage users, configure system
}

enum IHType {
  INDIVIDUAL // Single person
  GROUP // IG/club/committee
}

enum LoanRequestStatus {
  PENDING
  ONGOING
  COMPLETED
  REJECTED
}

enum LoanItemStatus {
  PENDING
  ON_LOAN
  RETURNED
  RETURNED_LATE
  REJECTED
}

model User {
  userId         Int      @id @default(autoincrement()) @map("user_id")
  telegramId     String?  @unique @map("telegram_id")
  telegramHandle String   @unique @map("telegram_handle")
  firstName      String   @map("first_name")
  lastName       String?  @map("last_name")
  photoUrl       String?  @map("photo_url")
  role           UserRole @default(REQUESTER)
  nusnetId       String?  @unique @map("nusnet_id")
  createdAt      DateTime @default(now()) @map("created_at")
  updatedAt      DateTime @updatedAt @map("updated_at")

  // Relations
  ihMemberships IHMember[] // IHs this user is a member of

  // Relations for LoanRequest migration
  loanRequests LoanRequest[] @relation("RequesterLoanRequests")
  handledLoans LoanRequest[] @relation("LoggieLoanRequests")

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("user")
}

model IHMember {
  id        Int      @id @default(autoincrement())
  userId    Int      @map("user_id")
  ihId      String   @map("ih_id")
  isPrimary Boolean  @default(false) @map("is_primary")
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [userId], onDelete: Cascade)
  ih   IH   @relation(fields: [ihId], references: [ihId], onDelete: Cascade)

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@unique([userId, ihId])
  @@map("ih_member")
}

model IH {
  ihId        String           @id @map("ih_id")
  ihName      String           @map("ih_name")
  ihType      IHType           @default(INDIVIDUAL) @map("ih_type")
  members     IHMember[] // Multiple POCs via IHMember
  items       Item[]           @relation("ItemIH")

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("ih")
}

model Item {
  itemId           Int              @id @default(autoincrement()) @map("item_id")
  itemDesc         String           @map("item_desc")
  itemSloc         String           @map("item_sloc")
  itemIh           String           @map("item_ih")
  itemQty          Int              @map("item_qty")
  itemUom          String           @map("item_uom")
  itemPurchaseDate DateTime?        @map("item_purchase_date")
  itemRfpNumber    String?          @map("item_rfp_number")
  itemImage        String?          @map("item_image")
  itemRemarks      String?          @map("item_remarks")
  itemUnloanable   Boolean          @default(false) @map("item_unloanable")
  itemExpendable   Boolean          @default(false) @map("item_expendable")
  sloc             Sloc             @relation("ItemSloc", fields: [itemSloc], references: [slocId], onDelete: Cascade, onUpdate: Cascade)
  ih               IH               @relation("ItemIH", fields: [itemIh], references: [ihId], onDelete: Cascade, onUpdate: Cascade)
  loanDetails      LoanItemDetail[]

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("item")
}

model LoanItemDetail {
  loanDetailId      Int              @id @default(autoincrement()) @map("loan_detail_id")
  refNo             Int              @map("ref_no")
  itemId            Int              @map("item_id")
  loanQty           Int              @map("loan_qty")
  loanItemStatus    LoanItemStatus   @default(PENDING) @map("loan_status")
  loanRequest       LoanRequest      @relation(fields: [refNo], references: [refNo], onDelete: Cascade, onUpdate: Cascade)
  item              Item             @relation(fields: [itemId], references: [itemId], onDelete: Cascade, onUpdate: Cascade)

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("loan_item_detail")
}

model LoanRequest {
  refNo             Int                @id @default(autoincrement()) @map("ref_no")
  loanDateStart     DateTime           @map("loan_date_start")
  loanDateEnd       DateTime           @map("loan_date_end")

  reqId    Int  @map("req_id")
  loggieId Int? @map("loggie_id")

  organisation      String?
  eventDetails      String?            @map("event_details")
  eventLocation     String?             @map("event_location")
  loanRequestStatus LoanRequestStatus   @default(PENDING) @map("request_status")

  requester User  @relation("RequesterLoanRequests", fields: [reqId], references: [userId], onDelete: Cascade, onUpdate: Cascade)
  loggie    User? @relation("LoggieLoanRequests", fields: [loggieId], references: [userId], onDelete: Cascade, onUpdate: Cascade)

  loanDetails LoanItemDetail[]

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("loan_request")
}

model Sloc {
  slocId      String           @id @map("sloc_id")
  slocName    String           @map("sloc_name")
  items       Item[]           @relation("ItemSloc")

  deletedAt      DateTime?      @map("deleted_at") // Soft delete 

  @@map("sloc")
}

// Audit Log 
model AuditLog {
  id        Int           @id @default(autoincrement())
  action    String        // CREATE or UPDATE or DELETE
  model     String        
  recordId  Int?
  userId    Int?
  timestamp DateTime @default(now())

  @@map("audit_log")
}