generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "linux-arm64-openssl-3.0.x", "linux-musl-openssl-3.0.x", "debian-openssl-1.1.x"]
  output        = "./generated/client"
}

datasource db {
  provider = "mysql"
  //  url      = "mysql://root:@localhost:3306/lhdc"
  url      = "mysql://vsrv1196_lhdc:F9.C{G53inZJ@localhost:3306/vsrv1196_lhdc"
}


model User {
  id             Int       @id @default(autoincrement())
  name           String
  email          String?
  phone          String?
  address        String?
  otp            String?
  otp_expires_at DateTime?
  designation_id Int?
  department_id  Int?
  image          String?
  password       String?
  gender         String? // Added gender field
  govt_id_card   String? // Added govt_id_card field

  status     Int      @default(1) // 1 = ACTIVE, 0 = INACTIVE
  is_deleted Int      @default(0) // 0 = NOT_DELETED, 1 = DELETED
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  designation DesignationMaster? @relation(fields: [designation_id], references: [id])
  department  DepartmentMaster?  @relation(fields: [department_id], references: [id])

  userPermissions     UserPermission[]
  applicationLogs     ApplicationLog[]
  applicationTimeline ApplicationTimeline[]
  siteVisits          SiteVisit[]

  @@index([email])
  @@map("tbl_user")
}

model Module {
  id            Int      @id @default(autoincrement())
  name          String   @unique
  slug          String   @unique // e.g., "master", "lead-management"
  icon          String? // Icon name for sidebar
  display_order Int      @default(0)
  parent_id     Int? // For nested modules
  parent        Module?  @relation("ModuleHierarchy", fields: [parent_id], references: [id])
  children      Module[] @relation("ModuleHierarchy")
  status        Int      @default(1)
  is_deleted    Int      @default(0)
  created_on    DateTime @default(now())
  updated_on    DateTime @updatedAt

  sections        Section[]
  userPermissions UserPermission[]

  @@index([slug])
  @@index([parent_id])
}

model Section {
  id            Int      @id @default(autoincrement())
  module_id     Int
  module        Module   @relation(fields: [module_id], references: [id], onDelete: Cascade)
  name          String
  slug          String // e.g., "designation-master"
  path          String // e.g., "/designation-master"
  display_order Int      @default(0)
  status        Int      @default(1)
  is_deleted    Int      @default(0)
  created_on    DateTime @default(now())
  updated_on    DateTime @updatedAt

  userPermissions UserPermission[]

  @@unique([module_id, slug])
  @@index([slug])
}

model UserPermission {
  id      Int  @id @default(autoincrement())
  user_id Int
  user    User @relation(fields: [user_id], references: [id], onDelete: Cascade)

  module_id Int?
  module    Module? @relation(fields: [module_id], references: [id], onDelete: Cascade)

  section_id Int?
  section    Section? @relation(fields: [section_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@unique([user_id, module_id, section_id])
  @@index([user_id])
  @@index([module_id])
  @@index([section_id])
}

model Customer {
  id             Int       @id @default(autoincrement())
  name           String
  email          String    @unique
  otp            String?
  otp_expires_at DateTime?
  is_verified    Boolean   @default(false)

  status       Int           @default(1) // 1 = ACTIVE, 0 = INACTIVE
  created_on   DateTime      @default(now())
  updated_on   DateTime      @updatedAt
  is_deleted   Int           @default(0)
  applications Application[]
  tickets      Ticket[]

  @@map("tbl_customer")
}

model Application {
  id               Int     @id @default(autoincrement())
  application_type String  @db.VarChar(50) // 'TenantMaintenance' | 'FTHB' | 'HIP'
  application_no   String? @db.VarChar(50)
  active_step      Int     @default(1)

  customer_id Int
  customer    Customer @relation(fields: [customer_id], references: [id])

  applicant_name      String  @db.VarChar(150)
  coapplicant_name    String? @db.VarChar(150)
  applicant_email     String? @db.VarChar(150)
  applicant_cellphone String? @db.VarChar(20)
  applicant_workphone String? @db.VarChar(20)
  applicant_homephone String? @db.VarChar(20)
  applicant_address   String? @db.VarChar(255)
  applicant_area      String? @db.VarChar(255)

  applicant_country Int?
  applicant_state   Int?
  applicant_city    Int?

  country Country? @relation(fields: [applicant_country], references: [id])
  state   State?   @relation(fields: [applicant_state], references: [id])
  city    City?    @relation(fields: [applicant_city], references: [id])

  status_id Int?
  status    StatusMaster? @relation(fields: [status_id], references: [id])

  submission_status Int      @default(0) // 0 : DRAFT  1 : SUBMITTED
  is_deleted        Int      @default(0)
  created_on        DateTime @default(now())
  updated_on        DateTime @updatedAt

  // Relations
  tenantMaintenance TenantMaintenance?
  hipApplication    HIPApplication?
  fthbApplication   FTHBApplication?
  hipDocuments      HIPApplicationDocument[]
  fthbDocuments     FTHBApplicationDocument[]
  timelines         ApplicationTimeline[]
  siteVisits        SiteVisit[]
  quotationLink     QuotationLink?
  agreement         Agreement?

  @@map("tbl_applications")
}

model ApplicationTimeline {
  id             Int     @id @default(autoincrement())
  application_id Int
  status_id      Int
  remark         String? @db.Text
  updated_by     Int?
  updated_user   User?   @relation(fields: [updated_by], references: [id])

  created_on  DateTime     @default(now())
  application Application  @relation(fields: [application_id], references: [id], onDelete: Cascade)
  status      StatusMaster @relation(fields: [status_id], references: [id])

  @@map("tbl_application_timeline")
}

model TenantMaintenance {
  id             Int         @id @default(autoincrement())
  application_id Int         @unique
  application    Application @relation(fields: [application_id], references: [id])

  // STEP 1 - Tenant Information
  pets_in_unit                     Boolean   @default(false)
  pet_type                         String?   @db.VarChar(150)
  pet_security_plan                String?   @db.VarChar(255)
  authorize_maintenance            Boolean   @default(false)
  contact_to_schedule              Boolean   @default(false)
  preferred_phone                  Boolean   @default(false)
  preferred_text                   Boolean   @default(false)
  preferred_email                  Boolean   @default(false)
  maintenance_heating_cooling      Boolean   @default(false)
  maintenance_plumbing             Boolean   @default(false)
  maintenance_electrical           Boolean   @default(false)
  maintenance_appliances           Boolean   @default(false)
  maintenance_doors_windows        Boolean   @default(false)
  maintenance_flooring_walls       Boolean   @default(false)
  maintenance_pest                 Boolean   @default(false)
  maintenance_safety               Boolean   @default(false)
  maintenance_heating_cooling_text String?   @db.VarChar(255)
  maintenance_plumbing_text        String?   @db.VarChar(255)
  maintenance_electrical_text      String?   @db.VarChar(255)
  maintenance_appliances_text      String?   @db.VarChar(255)
  maintenance_doors_windows_text   String?   @db.VarChar(255)
  maintenance_flooring_walls_text  String?   @db.VarChar(255)
  maintenance_pest_text            String?   @db.VarChar(255)
  maintenance_safety_text          String?   @db.VarChar(255)
  maintenance_other_text           String?   @db.VarChar(255)
  maintenance_description          String?   @db.LongText
  issue_start_time                 String?   @db.VarChar(150)
  is_urgent                        Boolean   @default(false)
  tenant_signature                 String?   @db.LongText
  signer_legal_name                String?   @db.VarChar(150)
  tenant_signature_datetime        DateTime?

  photos TenantMaintenancePhoto[]

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_tenant_maintenance")
}

model HIPApplication {
  id             Int         @id @default(autoincrement())
  application_id Int         @unique
  application    Application @relation(fields: [application_id], references: [id])

  // Property Status
  reside_address          String? @db.VarChar(10)
  property_owned_duration String? @db.VarChar(100)
  taxes_paid              String? @db.VarChar(10)

  // Employment
  is_currently_employed        String? @db.VarChar(10)
  applicant_employer_name      String? @db.VarChar(150)
  applicant_employer_address   String? @db.VarChar(255)
  coapplicant_employer_name    String? @db.VarChar(150)
  coapplicant_employer_address String? @db.VarChar(255)

  // Household
  household_members Json?

  // Income
  gross_annual_income String? @db.VarChar(50)
  total_persons       String? @db.VarChar(10)
  children_under_18   String? @db.VarChar(10)

  // Renovation
  renovation_details               String? @db.LongText
  maintenance_heating_cooling      Boolean @default(false)
  maintenance_plumbing             Boolean @default(false)
  maintenance_electrical           Boolean @default(false)
  maintenance_appliances           Boolean @default(false)
  maintenance_doors_windows        Boolean @default(false)
  maintenance_flooring_walls       Boolean @default(false)
  maintenance_pest                 Boolean @default(false)
  maintenance_safety               Boolean @default(false)
  maintenance_heating_cooling_text String? @db.VarChar(255)
  maintenance_plumbing_text        String? @db.VarChar(255)
  maintenance_electrical_text      String? @db.VarChar(255)
  maintenance_appliances_text      String? @db.VarChar(255)
  maintenance_doors_windows_text   String? @db.VarChar(255)
  maintenance_flooring_walls_text  String? @db.VarChar(255)
  maintenance_pest_text            String? @db.VarChar(255)
  maintenance_safety_text          String? @db.VarChar(255)
  maintenance_other_text           String? @db.VarChar(255)

  // Liabilities
  has_mortgage        String? @db.VarChar(10)
  mortgage_amount     String? @db.VarChar(50)
  mortgage_balance    String? @db.VarChar(50)
  payment_status      String? @db.VarChar(100)
  mortgagee           String? @db.VarChar(150)
  monthly_liabilities Json?

  // Certification
  certification_signature   String?   @db.LongText
  certification_signer_name String?   @db.VarChar(150)
  certification_datetime    DateTime?

  certification_coapplicant_signature   String?   @db.LongText
certification_coapplicant_signer_name String?   @db.VarChar(150)
certification_coapplicant_datetime    DateTime?

  // Authorization to Receive & Verify Employment Information
  employment_auth_signature   String?   @db.LongText
  employment_auth_signer_name String?   @db.VarChar(150)
  employment_auth_datetime    DateTime?

  employment_auth_coapplicant_signature   String?   @db.LongText
  employment_auth_coapplicant_signer_name String?   @db.VarChar(150)
  employment_auth_coapplicant_datetime    DateTime?

  // Authorization of Inspection
  inspection_auth_signature   String?   @db.LongText
  inspection_auth_signer_name String?   @db.VarChar(150)
  inspection_auth_datetime    DateTime?

  inspection_auth_coapplicant_signature   String?   @db.LongText
  inspection_auth_coapplicant_signer_name String?   @db.VarChar(150)
  inspection_auth_coapplicant_datetime    DateTime?

  // Bid Responsibility
  bid_responsibility_signature   String?   @db.LongText
  bid_responsibility_signer_name String?   @db.VarChar(150)
  bid_responsibility_datetime    DateTime?

  // Subordination of AHC Notes & Mortgages
  subordination_signature   String?   @db.LongText
  subordination_signer_name String?   @db.VarChar(150)
  subordination_datetime    DateTime?

    // Subordination of AHC Notes & Mortgages
  subordination_coapplicant_signature   String?   @db.LongText
  subordination_coapplicant_signer_name String?   @db.VarChar(150)
  subordination_coapplicant_datetime    DateTime?

    // Notary Public Acknowledgement
  notary_day            String?   @db.VarChar(10)
  notary_month          String?   @db.VarChar(20)
  notary_year           String?   @db.VarChar(10)
  notary_appeared_name  String?   @db.VarChar(255)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_hip_application")
}

model FTHBApplication {
  id             Int         @id @default(autoincrement())
  application_id Int         @unique
  application    Application @relation(fields: [application_id], references: [id])

  // Employment
  is_currently_employed        String? @db.VarChar(10)
  applicant_employer_name      String? @db.VarChar(150)
  coapplicant_employer_name    String? @db.VarChar(150)
  coapplicant_employer_address String? @db.VarChar(255)

  // Income
  applicant_has_income   String? @db.VarChar(10)
  applicant_incomes      Json?
  coapplicant_has_income String? @db.VarChar(10)
  coapplicant_incomes    Json?

  // Liabilities
  payees Json?

  // Certifications
  certification_signature     String?   @db.LongText
  certification_signer_name   String?   @db.VarChar(150)
  certification_datetime      DateTime?
  authorization_signature     String?   @db.LongText
  authorization_signer_name   String?   @db.VarChar(150)
  authorization_datetime      DateTime?

coapplicant_authorization_signature   String?   @db.LongText
coapplicant_authorization_signer_name String?   @db.VarChar(150)
coapplicant_authorization_datetime    DateTime?


  inspection_signature        String?   @db.LongText
  inspection_signer_name      String?   @db.VarChar(150)
  inspection_datetime         DateTime?

inspection_coapplicant_signature        String?   @db.LongText
inspection_coapplicant_signer_name      String?   @db.VarChar(150)
inspection_coapplicant_datetime         DateTime?


  bid_applicant_signature     String?   @db.LongText
  bid_applicant_signer_name   String?   @db.VarChar(150)
  bid_applicant_datetime      DateTime?
  bid_coapplicant_signature   String?   @db.LongText
  bid_coapplicant_signer_name String?   @db.VarChar(150)
  bid_coapplicant_datetime    DateTime?

  // Notary Public Acknowledgement
  notary_day            String?   @db.VarChar(10)
  notary_month          String?   @db.VarChar(20)
  notary_year           String?   @db.VarChar(10)
  notary_appeared_name  String?   @db.VarChar(255)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_fthb_application")
}

model FTHBApplicationDocument {
  id             Int    @id @default(autoincrement())
  application_id Int
  document_type  String @db.VarChar(255)
  file_name      String @db.VarChar(255)

  original_name String @db.VarChar(255)

  application Application @relation(fields: [application_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_fthb_application_documents")
}

model HIPApplicationDocument {
  id             Int    @id @default(autoincrement())
  application_id Int
  document_type  String @db.VarChar(255)
  file_name      String @db.VarChar(255)

  original_name String @db.VarChar(255)

  application Application @relation(fields: [application_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_hip_application_documents")
}

model TenantMaintenancePhoto {
  id             Int    @id @default(autoincrement())
  maintenance_id Int
  file_name      String @db.VarChar(150)
  file_url       String @db.VarChar(255)

  maintenance TenantMaintenance @relation(fields: [maintenance_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_tenant_maintenance_photos")
}

model SiteVisit {
  id             Int      @id @default(autoincrement())
  application_id Int
  visit_date     DateTime
  remark         String?  @db.Text
  added_by       Int
  added_by_user  User?    @relation(fields: [added_by], references: [id])

  created_by Int?

  application Application      @relation(fields: [application_id], references: [id], onDelete: Cascade)
  photos      SiteVisitPhoto[]

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_site_visit")
}

model SiteVisitPhoto {
  id            Int    @id @default(autoincrement())
  site_visit_id Int
  file_name     String @db.VarChar(255)

  original_name String @db.VarChar(255)

  siteVisit SiteVisit @relation(fields: [site_visit_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_site_visit_photo")
}

model QuotationLink {
  id             Int       @id @default(autoincrement())
  application_id Int       @unique
  token          String    @unique @db.VarChar(255)
  expires_at     DateTime?
  is_active      Int       @default(1)

  application Application @relation(fields: [application_id], references: [id], onDelete: Cascade)
  quotations  Quotation[]

  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_quotation_link")
}

model Quotation {
  id        Int    @id @default(autoincrement())
  link_id   Int
  file_name String @db.VarChar(255)

  original_name String  @db.VarChar(255)
  file_size     Int
  status        String  @default("Pending") @db.VarChar(50)
  remark        String? @db.Text

  link QuotationLink @relation(fields: [link_id], references: [id], onDelete: Cascade)

  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_quotation")
}

model Agreement {
  id                        Int      @id @default(autoincrement())
  application_id            Int      @unique
  total_contract_award      Decimal? @db.Decimal(10, 2)
  lhdc_contribution         Decimal? @db.Decimal(10, 2)
  client_contribution       Decimal? @db.Decimal(10, 2)
  total_dollar_leverage     Decimal? @db.Decimal(10, 2)
  dollar_leveraged_per_lhdc Decimal? @db.Decimal(10, 2)

  application Application         @relation(fields: [application_id], references: [id], onDelete: Cascade)
  documents   AgreementDocument[]

  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_agreement")
}

model AgreementDocument {
  id           Int    @id @default(autoincrement())
  agreement_id Int
  file_name    String @db.VarChar(255)

  original_name String @db.VarChar(255)
  file_size     Int

  agreement Agreement @relation(fields: [agreement_id], references: [id], onDelete: Cascade)

  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_agreement_document")
}

model StatusMaster {
  id           Int      @id @default(autoincrement())
  service_name String   @db.VarChar(50)
  status_name  String   @db.VarChar(50)
  status       Int      @default(1)
  is_deleted   Int      @default(0)
  created_on   DateTime @default(now())
  updated_on   DateTime @updatedAt

  applications Application[]
  timelines    ApplicationTimeline[]

  @@map("tbl_status_master")
}

model DepartmentMaster {
  id              Int      @id @default(autoincrement())
  department_name String   @db.VarChar(50)
  status          Int      @default(1)
  is_deleted      Int      @default(0)
  created_on      DateTime @default(now())
  updated_on      DateTime @updatedAt

  users User[]

  @@map("tbl_department_master")
}

model DesignationMaster {
  id               Int      @id @default(autoincrement())
  designation_name String   @db.VarChar(50)
  status           Int      @default(1)
  is_deleted       Int      @default(0)
  created_on       DateTime @default(now())
  updated_on       DateTime @updatedAt

  users       User[]
  teamMembers OurTeam[]

  @@map("tbl_designation_master")
}

model DocumentMaster {
  id            Int      @id @default(autoincrement())
  service_name  String   @db.VarChar(50)
  document_name String   @db.VarChar(50)
  document_icon String   @db.Text // e.g., "identity_proof", "income_proof", etc.
  is_required   Int      @default(0) // 0 = NOT_REQUIRED, 1 = REQUIRED
  info          String?  @db.Text
  upload_type   String   @default("single") @db.VarChar(20) // "single" or "multiple"
  status        Int      @default(1)
  is_deleted    Int      @default(0)
  created_on    DateTime @default(now())
  updated_on    DateTime @updatedAt

  @@map("tbl_document_master")
}

model BlogCategory {
  id         Int      @id @default(autoincrement())
  name       String   @db.VarChar(100)
  status     Int      @default(1) // 1 = active, 0 = inactive
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt
  blogs      Blog[]

  @@map("tbl_blog_category")
}

model Blog {
  id                Int      @id @default(autoincrement())
  title             String   @db.VarChar(200)
  meta_url          String   @db.VarChar(500)
  meta_keyword      String   @db.VarChar(255)
  author            String   @db.VarChar(100)
  tag               String   @db.VarChar(255)
  meta_description  String?  @db.VarChar(500)
  category_id       Int
  blog_image        String?
  short_description String   @db.VarChar(500)
  blog_content      String   @db.LongText
  status            Int      @default(1) // 1 = ACTIVE (published), 0 = INACTIVE(draft)
  is_deleted        Int      @default(0) // 1 = DELETED , 0 = NOT_DELETED
  created_on        DateTime @default(now())
  updated_on        DateTime @updatedAt

  // Relation
  category BlogCategory @relation(fields: [category_id], references: [id])

  @@map("tbl_blogs")
}

model PressRelease {
  id                    Int     @id @default(autoincrement())
  title                 String  @db.VarChar(200)
  meta_url              String  @db.VarChar(500)
  meta_keyword          String  @db.VarChar(255)
  author                String  @db.VarChar(100)
  tag                   String  @db.VarChar(255)
  meta_description      String? @db.VarChar(500)
  press_release_image   String?
  short_description     String  @db.VarChar(500)
  press_release_content String  @db.LongText

  status     Int      @default(1) // 1 = active, 0 = inactive
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_press_release")
}

model Faq {
  id         Int      @id @default(autoincrement())
  faq_type   Int // 1 = General, 2 = Home Improvement Program, 3 = First Time Home Buyer Program, 4 = Tenant Maintenance Program, etc.
  question   String   @db.VarChar(200)
  answer     String   @db.Text
  status     Int      @default(1) // 1 = active, 0 = inactive
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_faq")
}

model ContactUs {
  id Int @id @default(autoincrement())

  name    String @db.VarChar(100)
  email   String @db.VarChar(150)
  phone   String @db.VarChar(20)
  message String @db.LongText

  status     Int @default(1) // 1 = new/readable, 0 = resolved
  is_deleted Int @default(0)

  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_contact_us")
}

model OurTeam {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(100)

  designation_id Int?
  designation    DesignationMaster? @relation(fields: [designation_id], references: [id])

  our_team_image String?
  status         Int     @default(1) // 1 = new/readable, 0 = resolved
  is_deleted     Int     @default(0)

  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_our_team")
}

model SuccessStories {
  id                 Int     @id @default(autoincrement())
  name               String  @db.VarChar(100)
  organization       String? @db.VarChar(150)
  sucess_story_image String?
  content            String  @db.LongText

  status     Int      @default(1) // 1 = active, 0 = inactive
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt
}

model Country {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(150)

  states       State[]
  cities       City[]
  applications Application[]

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @default(now()) @updatedAt

  @@map("tbl_country")
}

model State {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(150)

  country_id Int
  country    Country @relation(fields: [country_id], references: [id], onDelete: Cascade)

  cities       City[]
  applications Application[]

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_state_master")
}

model City {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(150)

  country_id Int
  state_id   Int

  applications Application[]

  country Country @relation(fields: [country_id], references: [id], onDelete: Cascade)
  state   State   @relation(fields: [state_id], references: [id], onDelete: Cascade)

  status     Int      @default(1)
  is_deleted Int      @default(0)
  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  @@map("tbl_city_master")
}

model Ticket {
  id        Int    @id @default(autoincrement())
  ticket_no String @unique

  customer_id Int?
  customer    Customer? @relation(fields: [customer_id], references: [id], onDelete: SetNull)

  question_type    String
  application_name String?

  title       String
  description String @db.LongText

  status String @default("PENDING")

  created_on DateTime @default(now())
  updated_on DateTime @updatedAt

  replies TicketReply[]

  uploads TicketUpload[] @relation("TicketUploads")

  @@map("tbl_tickets")
}

model TicketUpload {
  id        Int     @id @default(autoincrement())
  ticket_id Int
  file_name String  @db.VarChar(255)
  file_url  String  @db.VarChar(500)
  file_size Int?
  file_type String? @db.VarChar(50)

  created_on DateTime @default(now())

  ticket Ticket @relation("TicketUploads", fields: [ticket_id], references: [id], onDelete: Cascade)

  @@map("tbl_ticket_uploads")
}

model TicketReply {
  id        Int    @id @default(autoincrement())
  ticket_id Int
  message   String @db.LongText
  reply_by  String // ADMIN or USER

  created_on DateTime @default(now())

  ticket Ticket             @relation(fields: [ticket_id], references: [id], onDelete: Cascade)
  images TicketReplyImage[]

  @@map("tbl_ticket_replies")
}

model TicketReplyImage {
  id        Int         @id @default(autoincrement())
  reply_id  Int
  image_url String      @db.LongText
  reply     TicketReply @relation(fields: [reply_id], references: [id], onDelete: Cascade)

  @@map("tbl_ticket_reply_images")
}

model ApplicationLog {
  id             Int      @id @default(autoincrement())
  application_id Int
  action         String   @db.VarChar(255)
  performed_by   String?  @db.VarChar(150)
  user_id        Int?
  user           User?    @relation(fields: [user_id], references: [id])
  created_on     DateTime @default(now())

  @@map("tbl_application_logs")
}