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

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

model audit {
  auditID          Int       @id @default(autoincrement())
  auditIP          String?   @db.VarChar(255)
  auditURL         String?   @db.VarChar(255)
  auditURLMethod   String?   @db.VarChar(255)
  auditURLPayload  String?   @db.VarChar(255)
  auditCreatedDate DateTime? @db.DateTime(0)
}

model lookup {
  lookupID           Int       @id @default(autoincrement())
  lookupOrder        Int?
  lookupTitle        String?   @db.VarChar(255)
  lookupRefCode      String?   @db.VarChar(255)
  lookupValue        String?   @db.VarChar(255)
  lookupType         String?   @db.VarChar(255)
  lookupStatus       String?   @db.VarChar(255)
  lookupCreatedDate  DateTime? @db.DateTime(0)
  lookupModifiedDate DateTime? @db.DateTime(0)
}

model role {
  roleID           Int                @id @default(autoincrement())
  roleName         String?            @db.VarChar(255)
  roleDescription  String?            @db.VarChar(255)
  roleStatus       String?            @db.VarChar(255)
  roleCreatedDate  DateTime?          @db.DateTime(0)
  roleModifiedDate DateTime?          @db.DateTime(0)
  userrole         userrole[]
  permissions      AccessPermission[]
}

model user {
  userID           Int                @id @default(autoincrement())
  userSecretKey    String?            @db.VarChar(255)
  userUsername     String?            @db.VarChar(255)
  userPassword     String?            @db.VarChar(255)
  userFullName     String?            @db.VarChar(255)
  userEmail        String?            @db.VarChar(255)
  userPhone        String?            @db.VarChar(255)
  userStatus       String?            @db.VarChar(255)
  userCreatedDate  DateTime?          @db.DateTime(0)
  userModifiedDate DateTime?          @db.DateTime(0)
  userrole         userrole[]
  accessRequests   AccessRequest[]
  permissions      AccessPermission[]
  documents        Document[]         @relation("DocumentCreator")
  cabinets         Cabinet[]          @relation("CabinetCreator")
  drawers          Drawer[]           @relation("DrawerCreator")
  folders          Folder[]           @relation("FolderCreator")
  subfolders       Subfolder[]        @relation("SubfolderCreator")
}

model userrole {
  userRoleID          Int      @id @default(autoincrement())
  userRoleUserID      Int      @default(0)
  userRoleRoleID      Int      @default(0)
  userRoleCreatedDate DateTime @db.DateTime(0)
  role                role     @relation(fields: [userRoleRoleID], references: [roleID], onDelete: NoAction, onUpdate: NoAction, map: "FK_userrole_role")
  user                user     @relation(fields: [userRoleUserID], references: [userID], onDelete: NoAction, onUpdate: NoAction, map: "FK_userrole_user")

  @@index([userRoleRoleID], map: "FK_userrole_role")
  @@index([userRoleUserID], map: "FK_userrole_user")
}

model site_settings {
  settingID            Int       @id @default(autoincrement())
  siteName             String?   @db.VarChar(255)
  siteNameFontSize     Int?      @default(18)
  siteDescription      String?   @db.Text
  siteLogo             String?   @db.VarChar(500)
  siteLoadingLogo      String?   @db.VarChar(500)
  siteFavicon          String?   @db.VarChar(500)
  showSiteNameInHeader Boolean?  @default(true)
  primaryColor         String?   @db.VarChar(50)
  secondaryColor       String?   @db.VarChar(50)
  successColor         String?   @db.VarChar(50)
  infoColor            String?   @db.VarChar(50)
  warningColor         String?   @db.VarChar(50)
  dangerColor          String?   @db.VarChar(50)
  customCSS            String?   @db.Text
  themeMode            String?   @db.VarChar(50)
  customThemeFile      String?   @db.VarChar(500)
  currentFont          String?   @db.VarChar(255)
  fontSource           String?   @db.VarChar(500)
  seoTitle             String?   @db.VarChar(255)
  seoDescription       String?   @db.Text
  seoKeywords          String?   @db.Text
  seoAuthor            String?   @db.VarChar(255)
  seoOgImage           String?   @db.VarChar(500)
  seoTwitterCard       String?   @default("summary_large_image") @db.VarChar(50)
  seoCanonicalUrl      String?   @db.VarChar(500)
  seoRobots            String?   @default("index, follow") @db.VarChar(100)
  seoGoogleAnalytics   String?   @db.VarChar(255)
  seoGoogleTagManager  String?   @db.VarChar(255)
  seoFacebookPixel     String?   @db.VarChar(255)
  settingCreatedDate   DateTime? @db.DateTime(0)
  settingModifiedDate  DateTime? @db.DateTime(0)
  siteLoginLogo        String?   @db.VarChar(500)
}

// DMS Models

model Cabinet {
  id          Int        @id @default(autoincrement())
  name        String     @db.VarChar(255)
  description String?    @db.Text
  createdAt   DateTime   @default(now()) @db.DateTime(0)
  updatedAt   DateTime   @updatedAt @db.DateTime(0)
  createdBy   Int
  status      String     @default("active") @db.VarChar(50)
  user        user       @relation("CabinetCreator", fields: [createdBy], references: [userID])
  drawers     Drawer[]
  permissions AccessPermission[]

  @@index([createdBy])
}

model Drawer {
  id          Int        @id @default(autoincrement())
  name        String     @db.VarChar(255)
  description String?    @db.Text
  cabinetId   Int
  createdAt   DateTime   @default(now()) @db.DateTime(0)
  updatedAt   DateTime   @updatedAt @db.DateTime(0)
  createdBy   Int
  status      String     @default("active") @db.VarChar(50)
  cabinet     Cabinet    @relation(fields: [cabinetId], references: [id], onDelete: Cascade)
  user        user       @relation("DrawerCreator", fields: [createdBy], references: [userID])
  folders     Folder[]
  permissions AccessPermission[]

  @@index([cabinetId])
  @@index([createdBy])
}

model Folder {
  id          Int        @id @default(autoincrement())
  name        String     @db.VarChar(255)
  description String?    @db.Text
  drawerId    Int
  createdAt   DateTime   @default(now()) @db.DateTime(0)
  updatedAt   DateTime   @updatedAt @db.DateTime(0)
  createdBy   Int
  status      String     @default("active") @db.VarChar(50)
  drawer      Drawer     @relation(fields: [drawerId], references: [id], onDelete: Cascade)
  user        user       @relation("FolderCreator", fields: [createdBy], references: [userID])
  subfolders  Subfolder[]
  documents   Document[] @relation("FolderDocuments")
  permissions AccessPermission[]

  @@index([drawerId])
  @@index([createdBy])
}

model Subfolder {
  id          Int        @id @default(autoincrement())
  name        String     @db.VarChar(255)
  description String?    @db.Text
  folderId    Int
  createdAt   DateTime   @default(now()) @db.DateTime(0)
  updatedAt   DateTime   @updatedAt @db.DateTime(0)
  createdBy   Int
  status      String     @default("active") @db.VarChar(50)
  folder      Folder     @relation(fields: [folderId], references: [id], onDelete: Cascade)
  user        user       @relation("SubfolderCreator", fields: [createdBy], references: [userID])
  documents   Document[] @relation("SubfolderDocuments")
  permissions AccessPermission[]

  @@index([folderId])
  @@index([createdBy])
}

model Document {
  id              Int        @id @default(autoincrement())
  name            String     @db.VarChar(255)
  description     String?    @db.Text
  fileSize        Int        @default(0)
  fileType        String     @db.VarChar(100)
  fileExtension   String     @db.VarChar(20)
  filePath        String     @db.VarChar(500)
  version         Int        @default(1)
  isTemplate      Boolean    @default(false)
  isPublic        Boolean    @default(false)
  folderId        Int?
  subfolderId     Int?
  createdAt       DateTime   @default(now()) @db.DateTime(0)
  updatedAt       DateTime   @updatedAt @db.DateTime(0)
  createdBy       Int
  status          String     @default("active") @db.VarChar(50)
  folder          Folder?    @relation("FolderDocuments", fields: [folderId], references: [id], onDelete: SetNull)
  subfolder       Subfolder? @relation("SubfolderDocuments", fields: [subfolderId], references: [id], onDelete: SetNull)
  user            user       @relation("DocumentCreator", fields: [createdBy], references: [userID])
  accessRequests  AccessRequest[]
  permissions     AccessPermission[]
  versions        DocumentVersion[]

  @@index([folderId])
  @@index([subfolderId])
  @@index([createdBy])
}

model DocumentVersion {
  id          Int       @id @default(autoincrement())
  documentId  Int
  version     Int
  filePath    String    @db.VarChar(500)
  fileSize    Int       @default(0)
  createdAt   DateTime  @default(now()) @db.DateTime(0)
  createdBy   Int
  document    Document  @relation(fields: [documentId], references: [id], onDelete: Cascade)

  @@index([documentId])
}

model AccessRequest {
  id              Int       @id @default(autoincrement())
  documentId      Int
  userId          Int
  requestedLevel  String    @db.VarChar(50) // view, download, print, edit, full
  justification   String?   @db.Text
  status          String    @default("pending") @db.VarChar(50) // pending, approved, rejected
  responseNote    String?   @db.Text
  requestedAt     DateTime  @default(now()) @db.DateTime(0)
  respondedAt     DateTime? @db.DateTime(0)
  document        Document  @relation(fields: [documentId], references: [id], onDelete: Cascade)
  user            user      @relation(fields: [userId], references: [userID])

  @@index([documentId])
  @@index([userId])
}

model AccessPermission {
  id              Int       @id @default(autoincrement())
  userId          Int?
  roleId          Int?
  documentId      Int?
  cabinetId       Int?
  drawerId        Int?
  folderId        Int?
  subfolderId     Int?
  permissionLevel String    @db.VarChar(50) // view, download, print, edit, full
  createdAt       DateTime  @default(now()) @db.DateTime(0)
  updatedAt       DateTime  @updatedAt @db.DateTime(0)
  expiresAt       DateTime? @db.DateTime(0)
  user            user?     @relation(fields: [userId], references: [userID], onDelete: SetNull)
  role            role?     @relation(fields: [roleId], references: [roleID], onDelete: SetNull)
  document        Document? @relation(fields: [documentId], references: [id], onDelete: Cascade)
  cabinet         Cabinet?  @relation(fields: [cabinetId], references: [id], onDelete: Cascade)
  drawer          Drawer?   @relation(fields: [drawerId], references: [id], onDelete: Cascade)
  folder          Folder?   @relation(fields: [folderId], references: [id], onDelete: Cascade)
  subfolder       Subfolder? @relation(fields: [subfolderId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([roleId])
  @@index([documentId])
  @@index([cabinetId])
  @@index([drawerId])
  @@index([folderId])
  @@index([subfolderId])
}
