prisma/schema.prisma

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

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

model alembic_version {
  version_num String @id @db.VarChar(32)
}

model audit_log {
  id         Int      @id @default(autoincrement())
  user_id    Int?
  aksi       String   @db.VarChar(50)
  entitas    String   @db.VarChar(50)
  entitas_id Int?
  data_lama  Json?
  data_baru  Json?
  ip_address String?  @db.VarChar(45)
  user_agent String?  @db.VarChar(255)
  created_at DateTime @default(now()) @db.DateTime(0)
  user       user?    @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "audit_log_ibfk_1")

  @@index([created_at], map: "idx_audit_created")
  @@index([entitas, entitas_id], map: "idx_audit_entitas_id")
  @@index([user_id], map: "idx_audit_user")
}

model bon {
  id            Int           @id @default(autoincrement())
  transaksi_id  Int
  member_id     Int
  total_bon     Decimal       @db.Decimal(15, 2)
  sisa_bon      Decimal       @db.Decimal(15, 2)
  jatuh_tempo   DateTime?     @db.Date
  status        bon_status    @default(aktif)
  reminder_sent Int           @default(0) @db.SmallInt
  created_at    DateTime      @default(now()) @db.DateTime(0)
  updated_at    DateTime      @default(now()) @db.DateTime(0)
  member        member        @relation(fields: [member_id], references: [id], onUpdate: NoAction, map: "bon_ibfk_1")
  transaksi     transaksi     @relation(fields: [transaksi_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "bon_ibfk_2")
  bon_cicilan   bon_cicilan[]

  @@index([jatuh_tempo], map: "idx_bon_jatuh_tempo")
  @@index([member_id], map: "idx_bon_member")
  @@index([status], map: "idx_bon_status")
  @@index([transaksi_id], map: "idx_bon_transaksi")
}

model bon_cicilan {
  id         Int                @id @default(autoincrement())
  bon_id     Int
  nominal    Decimal            @db.Decimal(15, 2)
  metode     bon_cicilan_metode
  user_id    Int
  created_at DateTime           @default(now()) @db.DateTime(0)
  bon        bon                @relation(fields: [bon_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "bon_cicilan_ibfk_1")
  user       user               @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "bon_cicilan_ibfk_2")

  @@index([bon_id], map: "idx_cicilan_bon")
  @@index([user_id], map: "idx_cicilan_user")
}

model diskon {
  id               Int                @id @default(autoincrement())
  nama             String             @db.VarChar(200)
  tipe             diskon_tipe
  nilai            Decimal            @db.Decimal(15, 2)
  min_belanja      Decimal            @default(0.00) @db.Decimal(15, 2)
  produk_ids       Json?
  member_only      Int                @default(0) @db.SmallInt
  tgl_mulai        DateTime           @db.DateTime(0)
  tgl_selesai      DateTime           @db.DateTime(0)
  jam_mulai        DateTime?          @db.Time(0)
  jam_selesai      DateTime?          @db.Time(0)
  is_aktif         Int                @default(1) @db.SmallInt
  created_at       DateTime           @default(now()) @db.DateTime(0)
  diskon_bundling  diskon_bundling[]
  transaksi_detail transaksi_detail[]
  voucher          voucher[]

  @@index([tgl_mulai, tgl_selesai], map: "idx_diskon_periode")
  @@index([is_aktif], map: "idx_diskon_status")
}

model diskon_bundling {
  id        Int     @id @default(autoincrement())
  diskon_id Int
  produk_id Int
  qty       Decimal @db.Decimal(10, 2)
  diskon    diskon  @relation(fields: [diskon_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "diskon_bundling_ibfk_1")
  produk    produk  @relation(fields: [produk_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "diskon_bundling_ibfk_2")

  @@index([diskon_id], map: "idx_bundling_diskon")
  @@index([produk_id], map: "idx_bundling_produk")
}

model harga_tingkat {
  id         Int                   @id @default(autoincrement())
  produk_id  Int
  tingkat    harga_tingkat_tingkat
  min_qty    Decimal               @db.Decimal(10, 2)
  harga      Decimal               @db.Decimal(15, 2)
  created_at DateTime              @default(now()) @db.DateTime(0)
  produk     produk                @relation(fields: [produk_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "harga_tingkat_ibfk_1")

  @@unique([produk_id, tingkat], map: "uq_produk_tingkat")
}

model kategori {
  id         Int       @id @default(autoincrement())
  nama       String    @unique(map: "nama") @db.VarChar(100)
  icon       String?   @db.VarChar(50)
  aktif      Int       @default(1) @db.SmallInt
  created_at DateTime  @default(now()) @db.DateTime(0)
  updated_at DateTime  @default(now()) @db.DateTime(0)
  deleted_at DateTime? @db.DateTime(0)
  produk     produk[]

  @@index([nama], map: "idx_kategori_nama")
}

model member {
  id         Int         @id @default(autoincrement())
  kode       String      @unique(map: "kode") @db.VarChar(20)
  nama       String      @db.VarChar(200)
  nomor_hp   String      @unique(map: "nomor_hp") @db.VarChar(20)
  alamat     String?     @db.Text
  total_poin Int         @default(0)
  limit_bon  Decimal     @default(0.00) @db.Decimal(15, 2)
  total_bon  Decimal     @default(0.00) @db.Decimal(15, 2)
  foto_url   String?     @db.VarChar(500)
  aktif      Int         @default(1) @db.SmallInt
  created_at DateTime    @default(now()) @db.DateTime(0)
  updated_at DateTime    @default(now()) @db.DateTime(0)
  deleted_at DateTime?   @db.DateTime(0)
  bon        bon[]
  transaksi  transaksi[]

  @@index([kode], map: "idx_pelanggan_kode")
  @@index([nomor_hp], map: "idx_pelanggan_nomor_hp")
}

model notifikasi {
  id           Int             @id @default(autoincrement())
  user_id      Int?
  tipe         notifikasi_tipe
  judul        String          @db.VarChar(255)
  pesan        String          @db.Text
  referensi_id Int?
  is_dibaca    Int             @default(0) @db.SmallInt
  was_sent     Int             @default(0) @db.SmallInt
  created_at   DateTime        @default(now()) @db.DateTime(0)
  user         user?           @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "notifikasi_ibfk_1")

  @@index([created_at], map: "idx_notif_created")
  @@index([user_id, is_dibaca], map: "idx_notif_user_dibaca")
}

model pembayaran {
  id           Int               @id @default(autoincrement())
  transaksi_id Int
  metode       pembayaran_metode
  nominal      Decimal           @db.Decimal(15, 2)
  referensi    String?           @db.VarChar(100)
  created_at   DateTime          @default(now()) @db.DateTime(0)
  transaksi    transaksi         @relation(fields: [transaksi_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "pembayaran_ibfk_1")

  @@index([metode], map: "idx_pembayaran_metode")
  @@index([transaksi_id], map: "idx_pembayaran_transaksi")
}

model pengaturan {
  id         Int      @id @default(autoincrement())
  kunci      String   @unique(map: "kunci") @db.VarChar(100)
  nilai      String   @db.Text
  deskripsi  String?  @db.VarChar(255)
  updated_at DateTime @default(now()) @db.DateTime(0)

  @@index([kunci], map: "idx_pengaturan_kunci")
}

model produk {
  id               Int                @id @default(autoincrement())
  kode             String             @unique(map: "kode") @db.VarChar(50)
  barcode          String?            @unique(map: "barcode") @db.VarChar(50)
  nama             String             @db.VarChar(200)
  kategori_id      Int
  supplier_id      Int?
  satuan           String             @default("pcs") @db.VarChar(20)
  harga_beli       Decimal            @db.Decimal(15, 2)
  harga_jual       Decimal            @db.Decimal(15, 2)
  stok             Decimal            @default(0.00) @db.Decimal(12, 2)
  stok_min         Decimal            @default(5.00) @db.Decimal(12, 2)
  expired_date     DateTime?          @db.Date
  foto_url         String?            @db.LongText
  qr_url           String?            @db.VarChar(500)
  deskripsi        String?            @db.Text
  is_ecer          Int                @default(1) @db.SmallInt
  is_aktif         Int                @default(1) @db.SmallInt
  created_at       DateTime           @default(now()) @db.DateTime(0)
  updated_at       DateTime           @default(now()) @db.DateTime(0)
  deleted_at       DateTime?          @db.DateTime(0)
  diskon_bundling  diskon_bundling[]
  harga_tingkat    harga_tingkat[]
  kategori         kategori           @relation(fields: [kategori_id], references: [id], onUpdate: NoAction, map: "produk_ibfk_1")
  supplier         supplier?          @relation(fields: [supplier_id], references: [id], onUpdate: NoAction, map: "produk_ibfk_2")
  stok_batch       stok_batch[]
  stok_log         stok_log[]
  stok_opname      stok_opname[]
  transaksi_detail transaksi_detail[]
  transaksi_retur  transaksi_retur[]

  @@index([barcode], map: "idx_produk_barcode")
  @@index([kategori_id], map: "idx_produk_kategori")
  @@index([kode], map: "idx_produk_kode")
  @@index([nama], map: "idx_produk_nama")
  @@index([supplier_id], map: "idx_produk_supplier")
}

model shift {
  id              Int          @id @default(autoincrement())
  user_id         Int
  kode            String       @db.VarChar(30)
  modal_awal      Decimal      @default(0.00) @db.Decimal(15, 2)
  waktu_buka      DateTime     @db.DateTime(0)
  waktu_tutup     DateTime?    @db.DateTime(0)
  total_transaksi Int          @default(0)
  total_tunai     Decimal      @default(0.00) @db.Decimal(15, 2)
  total_qris      Decimal      @default(0.00) @db.Decimal(15, 2)
  total_transfer  Decimal      @default(0.00) @db.Decimal(15, 2)
  total_bon       Decimal      @default(0.00) @db.Decimal(15, 2)
  total_diskon    Decimal      @default(0.00) @db.Decimal(15, 2)
  total_selisih   Decimal      @default(0.00) @db.Decimal(15, 2)
  status          shift_status @default(buka)
  catatan         String?      @db.Text
  user            user         @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "shift_ibfk_1")
  transaksi       transaksi[]

  @@index([kode], map: "idx_shift_kode")
  @@index([user_id], map: "idx_shift_user")
}

model stok_batch {
  id               Int                @id @default(autoincrement())
  produk_id        Int
  batch_no         String?            @db.VarChar(50)
  qty_masuk        Decimal            @db.Decimal(12, 2)
  qty_sisa         Decimal            @db.Decimal(12, 2)
  harga_beli       Decimal            @db.Decimal(15, 2)
  expired_date     DateTime?          @db.Date
  supplier_id      Int?
  catatan          String?            @db.Text
  created_at       DateTime           @default(now()) @db.DateTime(0)
  produk           produk             @relation(fields: [produk_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "stok_batch_ibfk_1")
  supplier         supplier?          @relation(fields: [supplier_id], references: [id], onUpdate: NoAction, map: "stok_batch_ibfk_2")
  stok_log         stok_log[]
  transaksi_detail transaksi_detail[]

  @@index([expired_date], map: "idx_batch_expired")
  @@index([produk_id], map: "idx_batch_produk")
  @@index([supplier_id], map: "idx_batch_supplier")
}

model stok_log {
  id           Int           @id @default(autoincrement())
  produk_id    Int
  batch_id     Int?
  tipe         stok_log_tipe
  qty          Decimal       @db.Decimal(12, 2)
  stok_sebelum Decimal       @db.Decimal(12, 2)
  stok_sesudah Decimal       @db.Decimal(12, 2)
  referensi    String?       @db.VarChar(50)
  user_id      Int?
  catatan      String?       @db.Text
  created_at   DateTime      @default(now()) @db.DateTime(0)
  stok_batch   stok_batch?   @relation(fields: [batch_id], references: [id], onUpdate: NoAction, map: "stok_log_ibfk_1")
  produk       produk        @relation(fields: [produk_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "stok_log_ibfk_2")
  user         user?         @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "stok_log_ibfk_3")

  @@index([batch_id], map: "batch_id")
  @@index([created_at], map: "idx_stoklog_date")
  @@index([produk_id], map: "idx_stoklog_produk")
  @@index([tipe], map: "idx_stoklog_tipe")
  @@index([user_id], map: "user_id")
}

model stok_opname {
  id          Int                @id @default(autoincrement())
  produk_id   Int
  user_id     Int
  stok_sistem Decimal            @db.Decimal(12, 2)
  stok_fisik  Decimal            @db.Decimal(12, 2)
  selisih     Decimal            @db.Decimal(12, 2)
  alasan      String?            @db.VarChar(255)
  status      stok_opname_status @default(draft)
  created_at  DateTime           @default(now()) @db.DateTime(0)
  produk      produk             @relation(fields: [produk_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "stok_opname_ibfk_1")
  user        user               @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "stok_opname_ibfk_2")

  @@index([produk_id], map: "idx_opname_produk")
  @@index([status], map: "idx_opname_status")
  @@index([user_id], map: "idx_opname_user")
}

model supplier {
  id         Int          @id @default(autoincrement())
  nama       String       @db.VarChar(200)
  kontak     String?      @db.VarChar(100)
  nomor_hp   String?      @db.VarChar(20)
  alamat     String?      @db.Text
  hutang     Decimal      @default(0.00) @db.Decimal(15, 2)
  aktif      Int          @default(1) @db.SmallInt
  created_at DateTime     @default(now()) @db.DateTime(0)
  updated_at DateTime     @default(now()) @db.DateTime(0)
  deleted_at DateTime?    @db.DateTime(0)
  produk     produk[]
  stok_batch stok_batch[]

  @@index([nama], map: "idx_pemasok_nama")
}

model transaksi {
  id               Int                @id @default(autoincrement())
  no_transaksi     String             @unique(map: "no_transaksi") @db.VarChar(30)
  shift_id         Int
  user_id          Int
  member_id        Int?
  tanggal          DateTime           @db.Date
  waktu            DateTime           @db.Time(0)
  subtotal         Decimal            @db.Decimal(15, 2)
  diskon_total     Decimal            @default(0.00) @db.Decimal(15, 2)
  diskon_member    Decimal            @default(0.00) @db.Decimal(15, 2)
  pajak            Decimal            @default(0.00) @db.Decimal(15, 2)
  total            Decimal            @db.Decimal(15, 2)
  bayar            Decimal            @default(0.00) @db.Decimal(15, 2)
  kembalian        Decimal            @default(0.00) @db.Decimal(15, 2)
  status           transaksi_status   @default(lunas)
  is_synced        Int                @default(0) @db.SmallInt
  sync_at          DateTime?          @db.DateTime(0)
  local_id         String?            @db.VarChar(50)
  catatan          String?            @db.Text
  created_at       DateTime           @default(now()) @db.DateTime(0)
  deleted_at       DateTime?          @db.DateTime(0)
  bon              bon[]
  pembayaran       pembayaran[]
  member           member?            @relation(fields: [member_id], references: [id], onUpdate: NoAction, map: "transaksi_ibfk_1")
  shift            shift              @relation(fields: [shift_id], references: [id], onUpdate: NoAction, map: "transaksi_ibfk_2")
  user             user               @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "transaksi_ibfk_3")
  transaksi_detail transaksi_detail[]
  transaksi_retur  transaksi_retur[]

  @@index([member_id], map: "idx_transaksi_member")
  @@index([no_transaksi], map: "idx_transaksi_no")
  @@index([shift_id], map: "idx_transaksi_shift")
  @@index([tanggal], map: "idx_transaksi_tanggal")
  @@index([user_id], map: "idx_transaksi_user")
}

model transaksi_detail {
  id           Int         @id @default(autoincrement())
  transaksi_id Int
  produk_id    Int
  nama_produk  String      @db.VarChar(200)
  qty          Decimal     @db.Decimal(10, 2)
  satuan       String      @db.VarChar(20)
  harga_satuan Decimal     @db.Decimal(15, 2)
  diskon_item  Decimal     @default(0.00) @db.Decimal(15, 2)
  subtotal     Decimal     @db.Decimal(15, 2)
  diskon_id    Int?
  batch_id     Int?
  created_at   DateTime    @default(now()) @db.DateTime(0)
  stok_batch   stok_batch? @relation(fields: [batch_id], references: [id], onUpdate: NoAction, map: "transaksi_detail_ibfk_1")
  diskon       diskon?     @relation(fields: [diskon_id], references: [id], onUpdate: NoAction, map: "transaksi_detail_ibfk_2")
  produk       produk      @relation(fields: [produk_id], references: [id], onUpdate: NoAction, map: "transaksi_detail_ibfk_3")
  transaksi    transaksi   @relation(fields: [transaksi_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "transaksi_detail_ibfk_4")

  @@index([batch_id], map: "batch_id")
  @@index([diskon_id], map: "diskon_id")
  @@index([produk_id], map: "idx_detail_produk")
  @@index([transaksi_id], map: "idx_detail_transaksi")
}

model transaksi_retur {
  id              Int                  @id @default(autoincrement())
  transaksi_id    Int
  no_retur        String               @unique(map: "no_retur") @db.VarChar(30)
  user_id         Int
  produk_id       Int
  qty             Decimal              @db.Decimal(10, 2)
  alasan          String               @db.VarChar(255)
  tipe            transaksi_retur_tipe
  nominal_kembali Decimal              @default(0.00) @db.Decimal(15, 2)
  stok_kembali    Int                  @default(1) @db.SmallInt
  created_at      DateTime             @default(now()) @db.DateTime(0)
  produk          produk               @relation(fields: [produk_id], references: [id], onUpdate: NoAction, map: "transaksi_retur_ibfk_1")
  transaksi       transaksi            @relation(fields: [transaksi_id], references: [id], onUpdate: NoAction, map: "transaksi_retur_ibfk_2")
  user            user                 @relation(fields: [user_id], references: [id], onUpdate: NoAction, map: "transaksi_retur_ibfk_3")

  @@index([no_retur], map: "idx_retur_no")
  @@index([produk_id], map: "idx_retur_produk")
  @@index([transaksi_id], map: "idx_retur_transaksi")
  @@index([user_id], map: "user_id")
}

model trash {
  id          Int      @id @default(autoincrement())
  entity_type String   @db.VarChar(50)
  entity_id   Int
  data        String   @db.Text
  deleted_by  Int?
  deleted_at  DateTime @default(now()) @db.DateTime(0)

  @@index([deleted_at], map: "idx_trash_date")
  @@index([entity_type, entity_id], map: "idx_trash_entity")
}

model user {
  id              Int               @id @default(autoincrement())
  nama            String            @db.VarChar(100)
  username        String            @unique(map: "username") @db.VarChar(50)
  password_hash   String            @db.VarChar(255)
  role            user_role
  nomor_hp        String?           @db.VarChar(20)
  foto_url        String?           @db.VarChar(500)
  aktif           Int               @default(1) @db.SmallInt
  created_at      DateTime          @default(now()) @db.DateTime(0)
  updated_at      DateTime          @default(now()) @db.DateTime(0)
  audit_log       audit_log[]
  bon_cicilan     bon_cicilan[]
  notifikasi      notifikasi[]
  shift           shift[]
  stok_log        stok_log[]
  stok_opname     stok_opname[]
  transaksi       transaksi[]
  transaksi_retur transaksi_retur[]

  @@index([username], map: "idx_pengguna_username")
}

model voucher {
  id             Int      @id @default(autoincrement())
  kode           String   @unique(map: "kode") @db.VarChar(50)
  diskon_id      Int
  kuota          Int      @default(1)
  terpakai       Int      @default(0)
  tgl_kadaluarsa DateTime @db.Date
  is_aktif       Int      @default(1) @db.SmallInt
  created_at     DateTime @default(now()) @db.DateTime(0)
  diskon         diskon   @relation(fields: [diskon_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "voucher_ibfk_1")

  @@index([diskon_id], map: "idx_voucher_diskon")
  @@index([kode], map: "idx_voucher_kode")
}

enum diskon_tipe {
  per_item_persen
  per_item_nominal
  total_persen
  total_nominal
  bundling
  buy_x_get_y
  member
  flash_sale
}

enum harga_tingkat_tingkat {
  ecer
  grosir
  partai
}

enum notifikasi_tipe {
  stok_rendah
  stok_habis
  expired
  bon_jatuh_tempo
  shift_tutup
  transaksi_void
  info
}

enum pembayaran_metode {
  tunai
  qris
  transfer
  bon
  voucher
}

enum bon_cicilan_metode {
  tunai
  transfer
}

enum stok_log_tipe {
  masuk
  keluar
  opname
  retur
  expired
  adjustment
}

enum user_role {
  admin
  kasir
}

enum bon_status {
  aktif
  lunas
  macet
}

enum stok_opname_status {
  draft
  approved
}

enum transaksi_retur_tipe {
  refund
  exchange
}

enum shift_status {
  buka
  tutup
}

enum transaksi_status {
  lunas
  bon
  void
}