-- =====================================================================
-- TRUKCO (SAVAR DINAMIKA TEKNOLOGI): skrip CREATE TABLE (baseline)
-- Sumber: TRUKCO_create_tabel.sql (40 tabel). Tidak diubah; perubahan ada di 0001_celah_tahap1.sql.
-- Mesin InnoDB, utf8mb4_unicode_ci. Urutan tabel mengikuti ketergantungan FK.
-- Koneksi aplikasi menjalankan SET time_zone = '+07:00'.
-- =====================================================================

SET NAMES utf8mb4;

-- 1. pengguna
CREATE TABLE `pengguna` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `peran` ENUM('pelanggan','driver','admin','direktur') NOT NULL COMMENT 'BR-ROL-02',
  `nama` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `no_hp` VARCHAR(20) NOT NULL,
  `kata_sandi_hash` VARCHAR(255) NULL DEFAULT NULL COMMENT 'NULL untuk pelanggan/driver (masuk lewat OTP); wajib untuk admin/direktur',
  `status` ENUM('menunggu_otp','aktif','nonaktif','ditangguhkan','diblokir') NOT NULL COMMENT 'menunggu_otp = registrasi belum selesai OTP',
  `email_terverifikasi_at` DATETIME NULL DEFAULT NULL,
  `hp_terverifikasi_at` DATETIME NULL DEFAULT NULL,
  `dibuat_oleh` BIGINT NULL DEFAULT NULL COMMENT 'NULL = seed/registrasi sendiri; terisi bila Admin membuat akun staf',
  `wajib_ganti_sandi` TINYINT NOT NULL DEFAULT 0 COMMENT 'kata sandi sementara akun staf',
  `login_terakhir_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pengguna_email` (`email`),
  UNIQUE KEY `uq_pengguna_no_hp` (`no_hp`),
  CONSTRAINT `fk_pengguna_dibuat_oleh` FOREIGN KEY (`dibuat_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_pengguna_wajib_ganti_sandi` CHECK (`wajib_ganti_sandi` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Akun semua peran';

-- 2. kode_otp
CREATE TABLE `kode_otp` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NULL DEFAULT NULL COMMENT 'boleh kosong saat registrasi',
  `tujuan` ENUM('registrasi','masuk','ubah_data') NOT NULL,
  `saluran` ENUM('hp','email') NOT NULL,
  `kontak_tujuan` VARCHAR(120) NOT NULL COMMENT 'nomor/email tujuan; wajib karena pengguna_id boleh kosong',
  `kode_hash` VARCHAR(64) NOT NULL,
  `kedaluwarsa_at` DATETIME NOT NULL,
  `percobaan` TINYINT NOT NULL DEFAULT 0,
  `digunakan_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_kode_otp_kontak` (`kontak_tujuan`, `tujuan`, `dibuat_at`),
  CONSTRAINT `fk_kode_otp_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OTP registrasi dan masuk';

-- 3. token_refresh
CREATE TABLE `token_refresh` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `perangkat` VARCHAR(80) NOT NULL,
  `kedaluwarsa_at` DATETIME NOT NULL,
  `dicabut_at` DATETIME NULL DEFAULT NULL,
  `digantikan_oleh_id` BIGINT NULL DEFAULT NULL COMMENT 'rotasi token',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_token_refresh_token_hash` (`token_hash`),
  CONSTRAINT `fk_token_refresh_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_token_refresh_digantikan_oleh_id` FOREIGN KEY (`digantikan_oleh_id`) REFERENCES `token_refresh` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Token refresh JWT';

-- 4. rekening_bank
CREATE TABLE `rekening_bank` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NOT NULL,
  `kode_bank` VARCHAR(10) NOT NULL,
  `nomor_rekening_enc` VARBINARY(255) NOT NULL COMMENT 'terenkripsi',
  `nomor_rekening_akhir` CHAR(4) NOT NULL COMMENT 'empat digit terakhir untuk tampilan',
  `nama_pemilik` VARCHAR(100) NOT NULL,
  `cocok_nama_akun` TINYINT NOT NULL COMMENT 'hasil cek BR-WLT-09, dihitung saat disimpan',
  `status` ENUM('aktif','nonaktif') NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_rekening_bank_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_rekening_bank_cocok_nama_akun` CHECK (`cocok_nama_akun` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rekening bank pengguna';

-- 5. jenis_truk
CREATE TABLE `jenis_truk` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode` ENUM('PUB','PBB','CDE','CDD') NOT NULL,
  `nama` VARCHAR(60) NOT NULL,
  `biaya_driver` BIGINT NOT NULL COMMENT 'Biaya Driver per pesanan menurut jenis',
  `aktif` TINYINT NOT NULL DEFAULT 1,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `chk_jenis_truk_aktif` CHECK (`aktif` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Empat jenis kendaraan aplikasi';

-- 6. jenis_bbm
CREATE TABLE `jenis_bbm` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `bahan_bakar_master` ENUM('Bensin','Solar') NOT NULL COMMENT 'nilai kolom bahan_bakar pada Master_Truk_FIX',
  `nama_produk` VARCHAR(40) NOT NULL COMMENT 'Pertamax (RON 92) atau Dexlite (CN 51)',
  `aktif` TINYINT NOT NULL DEFAULT 1,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `chk_jenis_bbm_aktif` CHECK (`aktif` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bensin dan Solar dipetakan ke Pertamax dan Dexlite';

-- 7. master_truk
CREATE TABLE `master_truk` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_master` VARCHAR(80) NOT NULL COMMENT 'ID_truk pada Excel',
  `model` VARCHAR(120) NOT NULL,
  `jenis_truk_id` BIGINT NOT NULL,
  `jenis_bbm_id` BIGINT NOT NULL,
  `gvw_kg` INT NOT NULL,
  `payload_estimasi_max_ton` DECIMAL(5,2) NOT NULL,
  `box_panjang_m` DECIMAL(4,2) NOT NULL,
  `box_lebar_m` DECIMAL(4,2) NOT NULL,
  `box_tinggi_m` DECIMAL(4,2) NOT NULL,
  `volume_cbm` DECIMAL(6,2) NOT NULL,
  `soj_liter_per_3km` DECIMAL(4,2) NOT NULL,
  `aktif` TINYINT NOT NULL DEFAULT 1,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_master_truk_kode_master` (`kode_master`),
  CONSTRAINT `fk_master_truk_jenis_truk_id` FOREIGN KEY (`jenis_truk_id`) REFERENCES `jenis_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_master_truk_jenis_bbm_id` FOREIGN KEY (`jenis_bbm_id`) REFERENCES `jenis_bbm` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_master_truk_aktif` CHECK (`aktif` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='80 model master (acuan SOJ, dimensi, payload)';

-- 8. driver
CREATE TABLE `driver` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NOT NULL,
  `master_truk_id` BIGINT NOT NULL,
  `nopol` VARCHAR(12) NOT NULL,
  `nama_ktp` VARCHAR(100) NOT NULL,
  `nik_enc` VARBINARY(255) NOT NULL COMMENT 'NIK terenkripsi',
  `nik_hash` CHAR(64) NOT NULL COMMENT 'HMAC-SHA256 NIK, indeks non-unik',
  `nik_hash_aktif` CHAR(64) NULL DEFAULT NULL COMMENT 'terisi hanya bila pendaftaran bukan ditolak/diblokir; menjamin satu NIK aktif satu akun',
  `alamat_domisili` VARCHAR(200) NOT NULL,
  `kota_domisili` VARCHAR(60) NOT NULL COMMENT 'dipakai pemetaan dan laporan wilayah',
  `lat_basis` DECIMAL(9,6) NOT NULL COMMENT 'titik basis truk (domisili)',
  `lon_basis` DECIMAL(9,6) NOT NULL,
  `stnk_berlaku_sampai` DATE NOT NULL,
  `kir_berlaku_sampai` DATE NOT NULL,
  `sim_berlaku_sampai` DATE NOT NULL,
  `status_pendaftaran` ENUM('menunggu_verifikasi','perlu_perbaikan','ditahan_admin','disetujui','ditolak','diblokir') NOT NULL,
  `ronde_pengajuan` TINYINT NOT NULL DEFAULT 1 COMMENT '1 = pertama; naik saat daftar ulang setelah ditolak',
  `disetujui_at` DATETIME NULL DEFAULT NULL,
  `online` TINYINT NOT NULL DEFAULT 0 COMMENT 'toggle manual BR-DRV-08',
  `ditangguhkan_sampai` DATETIME NULL DEFAULT NULL COMMENT 'terisi saat suspend',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_driver_pengguna_id` (`pengguna_id`),
  UNIQUE KEY `uq_driver_nopol` (`nopol`),
  UNIQUE KEY `uq_driver_nik_hash_aktif` (`nik_hash_aktif`),
  KEY `ix_driver_status` (`status_pendaftaran`, `online`),
  KEY `ix_driver_nik_hash` (`nik_hash`),
  KEY `ix_driver_kota` (`kota_domisili`),
  CONSTRAINT `fk_driver_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_driver_master_truk_id` FOREIGN KEY (`master_truk_id`) REFERENCES `master_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_driver_online` CHECK (`online` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Driver sekaligus data truknya (BR-DRV-04)';

-- 9. dokumen_driver
CREATE TABLE `dokumen_driver` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `driver_id` BIGINT NOT NULL,
  `jenis` ENUM('ktp','sim','stnk','kir','skck','foto_depan','foto_belakang','foto_samping','foto_dalam_box') NOT NULL,
  `nomor_dokumen` VARCHAR(40) NULL DEFAULT NULL,
  `berlaku_sampai` DATE NULL DEFAULT NULL COMMENT 'BR-DRV-18',
  `berkas_path` VARCHAR(200) NOT NULL COMMENT 'folder di luar public',
  `status` ENUM('menunggu','sah','ditolak','diganti','kedaluwarsa') NOT NULL COMMENT 'diganti = digantikan berkas ronde berikutnya',
  `ronde` TINYINT NOT NULL DEFAULT 1,
  `catatan` VARCHAR(200) NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_dokumen_driver_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Dokumen dan foto driver';

-- 10. verifikasi_driver
CREATE TABLE `verifikasi_driver` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `driver_id` BIGINT NOT NULL,
  `admin_id` BIGINT NULL DEFAULT NULL COMMENT 'NULL bila keputusan otomatis sistem (NIK daftar hitam)',
  `jenis` ENUM('pendaftaran','dokumen_kedaluwarsa','ubah_data_truk') NOT NULL COMMENT 'BR-DRV-17, BR-DRV-18',
  `ronde` TINYINT NOT NULL DEFAULT 1,
  `keputusan` ENUM('setuju','tolak','minta_perbaikan','tahan','blokir') NOT NULL,
  `diputuskan_oleh` ENUM('admin','sistem') NOT NULL,
  `alasan` VARCHAR(250) NULL DEFAULT NULL,
  `model_dikoreksi_dari_id` BIGINT NULL DEFAULT NULL COMMENT 'BR-DRV-06: model master semula pilihan driver',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_verifikasi_driver_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_verifikasi_driver_admin_id` FOREIGN KEY (`admin_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_verifikasi_driver_model_dikoreksi_dari_id` FOREIGN KEY (`model_dikoreksi_dari_id`) REFERENCES `master_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Riwayat keputusan verifikasi';

-- 11. estimasi_pesanan
CREATE TABLE `estimasi_pesanan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pelanggan_id` BIGINT NOT NULL,
  `jenis_truk_id` BIGINT NOT NULL,
  `lat_jemput` DECIMAL(9,6) NOT NULL,
  `lon_jemput` DECIMAL(9,6) NOT NULL,
  `lat_tujuan` DECIMAL(9,6) NOT NULL,
  `lon_tujuan` DECIMAL(9,6) NOT NULL,
  `jarak_antar_km` DECIMAL(7,1) NOT NULL,
  `hasil` ENUM('tampil','jarak_melebihi_batas','tanpa_driver') NOT NULL,
  `total_tampil` BIGINT NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_estimasi_pesanan_pelanggan_id` FOREIGN KEY (`pelanggan_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_estimasi_pesanan_jenis_truk_id` FOREIGN KEY (`jenis_truk_id`) REFERENCES `jenis_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: setiap tampilan harga, termasuk yang tidak jadi dipesan';

-- 12. pesanan
CREATE TABLE `pesanan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_publik` CHAR(10) NOT NULL,
  `pelanggan_id` BIGINT NOT NULL,
  `driver_id` BIGINT NULL DEFAULT NULL,
  `jenis_truk_id` BIGINT NOT NULL,
  `master_truk_id` BIGINT NOT NULL COMMENT 'model driver acuan harga',
  `driver_acuan_id` BIGINT NULL DEFAULT NULL COMMENT 'driver ready terdekat saat harga dikunci; bisa berbeda dari driver penerima',
  `estimasi_id` BIGINT NULL DEFAULT NULL,
  `status` ENUM('menunggu_driver_dp','dp_terbayar','driver_tiba','menunggu_pelunasan','lunas','dalam_perjalanan','selesai','selesai_final','dibatalkan_sistem','ditolak','dibatalkan_pelanggan','dibatalkan_driver','pelanggan_tidak_melunasi') NOT NULL,
  `driver_diterima` TINYINT NOT NULL DEFAULT 0 COMMENT 'penanda Menunggu Driver/DP',
  `dp_dibayar_at` DATETIME NULL DEFAULT NULL COMMENT 'penanda pembayaran dipisah dari status',
  `lunas_at` DATETIME NULL DEFAULT NULL COMMENT 'pelunasan boleh sebelum driver tiba',
  `jadwal_jemput` DATETIME NOT NULL,
  `jadwal_selesai_estimasi` DATETIME NOT NULL COMMENT 'jemput + antar + buffer; dasar cek bentrok BR-MCH-07',
  `batas_dp_at` DATETIME NOT NULL COMMENT 'dibuat + 1 jam',
  `batas_respons_driver_at` DATETIME NOT NULL COMMENT 'dibuat + 2 jam',
  `alamat_jemput` VARCHAR(200) NOT NULL,
  `lat_jemput` DECIMAL(9,6) NOT NULL,
  `lon_jemput` DECIMAL(9,6) NOT NULL,
  `kota_jemput` VARCHAR(60) NOT NULL,
  `alamat_tujuan` VARCHAR(200) NOT NULL,
  `lat_tujuan` DECIMAL(9,6) NOT NULL,
  `lon_tujuan` DECIMAL(9,6) NOT NULL,
  `kota_tujuan` VARCHAR(60) NOT NULL,
  `jarak_jemput_km` DECIMAL(7,1) NOT NULL,
  `jarak_antar_km` DECIMAL(7,1) NOT NULL,
  `durasi_jemput_menit` INT NOT NULL,
  `durasi_antar_menit` INT NOT NULL,
  `jumlah_satuan` INT NOT NULL COMMENT 'pembulatan ke atas (jemput + antar) / 3',
  `soj_liter_per_3km` DECIMAL(4,2) NOT NULL COMMENT 'salinan SOJ',
  `jenis_bbm_id` BIGINT NOT NULL,
  `harga_bbm_per_liter` BIGINT NOT NULL COMMENT 'salinan harga saat dikunci',
  `biaya_bbm` BIGINT NOT NULL,
  `biaya_driver` BIGINT NOT NULL,
  `biaya_admin` BIGINT NOT NULL,
  `total` BIGINT NOT NULL,
  `dp` BIGINT NOT NULL,
  `pelunasan` BIGINT NOT NULL,
  `hak_driver` BIGINT NOT NULL COMMENT 'biaya_bbm + biaya_driver, disalin agar finalisasi tidak menghitung ulang',
  `keterangan_barang` VARCHAR(200) NOT NULL,
  `selesai_at` DATETIME NULL DEFAULT NULL,
  `final_jadwal_at` DATETIME NULL DEFAULT NULL COMMENT 'selesai_at + masa tunggu',
  `final_at` DATETIME NULL DEFAULT NULL,
  `finalisasi_ditunda` TINYINT NOT NULL DEFAULT 0 COMMENT '1 selama ada laporan terbuka',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pesanan_kode_publik` (`kode_publik`),
  KEY `ix_pesanan_status` (`status`, `jadwal_jemput`),
  KEY `ix_pesanan_bentrok` (`driver_id`, `jadwal_jemput`, `jadwal_selesai_estimasi`),
  KEY `ix_pesanan_batas_dp` (`status`, `batas_dp_at`),
  KEY `ix_pesanan_final` (`status`, `final_jadwal_at`),
  CONSTRAINT `fk_pesanan_pelanggan_id` FOREIGN KEY (`pelanggan_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesanan_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesanan_jenis_truk_id` FOREIGN KEY (`jenis_truk_id`) REFERENCES `jenis_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesanan_master_truk_id` FOREIGN KEY (`master_truk_id`) REFERENCES `master_truk` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesanan_driver_acuan_id` FOREIGN KEY (`driver_acuan_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesanan_estimasi_id` FOREIGN KEY (`estimasi_id`) REFERENCES `estimasi_pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_pesanan_driver_diterima` CHECK (`driver_diterima` IN (0, 1)),
  CONSTRAINT `fk_pesanan_jenis_bbm_id` FOREIGN KEY (`jenis_bbm_id`) REFERENCES `jenis_bbm` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_pesanan_finalisasi_ditunda` CHECK (`finalisasi_ditunda` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pesanan (harga terkunci saat dibuat)';

-- 13. sanksi_driver
CREATE TABLE `sanksi_driver` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `driver_id` BIGINT NOT NULL,
  `pesanan_id` BIGINT NULL DEFAULT NULL,
  `admin_id` BIGINT NOT NULL,
  `tingkat` ENUM('peringatan_1','peringatan_2','suspend') NOT NULL,
  `alasan` VARCHAR(250) NOT NULL,
  `berlaku_mulai` DATETIME NOT NULL,
  `berlaku_sampai` DATETIME NULL DEFAULT NULL COMMENT 'hanya suspend',
  `status` ENUM('aktif','selesai','dicabut') NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_sanksi_driver_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_sanksi_driver_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_sanksi_driver_admin_id` FOREIGN KEY (`admin_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Peringatan dan suspend';

-- 14. daftar_hitam
CREATE TABLE `daftar_hitam` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `nik_hash` CHAR(64) NOT NULL,
  `nik_enc` VARBINARY(255) NOT NULL,
  `nama` VARCHAR(100) NOT NULL,
  `alasan` VARCHAR(250) NOT NULL,
  `sumber_driver_id` BIGINT NULL DEFAULT NULL COMMENT 'driver yang memicu pemblokiran',
  `ditambahkan_oleh` BIGINT NOT NULL,
  `jumlah_percobaan_ulang` INT NOT NULL DEFAULT 0,
  `percobaan_terakhir_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_daftar_hitam_nik_hash` (`nik_hash`),
  CONSTRAINT `fk_daftar_hitam_sumber_driver_id` FOREIGN KEY (`sumber_driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_daftar_hitam_ditambahkan_oleh` FOREIGN KEY (`ditambahkan_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: NIK yang diblokir permanen';

-- 15. posisi_driver_terakhir
CREATE TABLE `posisi_driver_terakhir` (
  `driver_id` BIGINT NOT NULL,
  `lat` DECIMAL(9,6) NOT NULL,
  `lon` DECIMAL(9,6) NOT NULL,
  `akurasi_m` SMALLINT NOT NULL,
  `diperbarui_at` DATETIME NOT NULL,
  PRIMARY KEY (`driver_id`),
  CONSTRAINT `fk_posisi_driver_terakhir_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: satu baris per driver, ditimpa tiap pembaruan GPS';

-- 16. riwayat_status_pesanan
CREATE TABLE `riwayat_status_pesanan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `status_lama` VARCHAR(30) NULL DEFAULT NULL,
  `status_baru` VARCHAR(30) NOT NULL,
  `pelaku_id` BIGINT NULL DEFAULT NULL,
  `pelaku_tipe` ENUM('pelanggan','driver','admin','sistem') NOT NULL,
  `alasan` VARCHAR(200) NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_riwayat_pesanan_waktu` (`pesanan_id`, `dibuat_at`),
  CONSTRAINT `fk_riwayat_status_pesanan_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_riwayat_status_pesanan_pelaku_id` FOREIGN KEY (`pelaku_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jejak perubahan status';

-- 17. penawaran_pesanan
CREATE TABLE `penawaran_pesanan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `driver_id` BIGINT NOT NULL,
  `urutan` TINYINT NOT NULL,
  `jarak_jemput_km` DECIMAL(7,1) NOT NULL,
  `dikirim_at` DATETIME NOT NULL,
  `direspons_at` DATETIME NULL DEFAULT NULL,
  `status` ENUM('menunggu','diterima','ditolak','kedaluwarsa','diambil_lain') NOT NULL,
  `alasan_tolak` VARCHAR(120) NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_penawaran_pesanan_driver` (`pesanan_id`, `driver_id`),
  CONSTRAINT `fk_penawaran_pesanan_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_penawaran_pesanan_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Notifikasi pesanan ke 4-7 driver';

-- 18. pembatalan_pesanan
CREATE TABLE `pembatalan_pesanan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `dibatalkan_oleh` ENUM('pelanggan','driver','sistem','admin') NOT NULL,
  `alasan` VARCHAR(200) NULL DEFAULT NULL,
  `status_saat_batal` VARCHAR(30) NOT NULL,
  `jam_sebelum_jemput` DECIMAL(7,2) NULL DEFAULT NULL COMMENT 'dasar aturan 4 jam',
  `kebijakan` ENUM('tanpa_biaya','refund_penuh','refund_30','hangus') NOT NULL,
  `dp_dibayar` BIGINT NOT NULL,
  `pelunasan_dibayar` BIGINT NOT NULL COMMENT 'pelunasan yang sudah dibayar dan ikut dikembalikan',
  `nominal_refund` BIGINT NOT NULL COMMENT 'total dikembalikan ke dompet pelanggan (DP + pelunasan)',
  `nominal_hangus` BIGINT NOT NULL,
  `bagian_driver` BIGINT NOT NULL,
  `bagian_savar` BIGINT NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pembatalan_pesanan_pesanan_id` (`pesanan_id`),
  CONSTRAINT `fk_pembatalan_pesanan_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rincian pembatalan dan perlakuan DP';

-- 19. pesan_chat
CREATE TABLE `pesan_chat` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `pengirim_id` BIGINT NOT NULL,
  `isi` VARCHAR(500) NOT NULL,
  `dibaca_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_chat_pesanan` (`pesanan_id`, `id`),
  CONSTRAINT `fk_pesan_chat_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_pesan_chat_pengirim_id` FOREIGN KEY (`pengirim_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Chat pelanggan-driver';

-- 20. titik_posisi
CREATE TABLE `titik_posisi` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `driver_id` BIGINT NOT NULL,
  `lat` DECIMAL(9,6) NOT NULL,
  `lon` DECIMAL(9,6) NOT NULL,
  `fase` ENUM('jemput','antar') NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_posisi_pesanan` (`pesanan_id`, `id`),
  CONSTRAINT `fk_titik_posisi_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_titik_posisi_driver_id` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jejak GPS selama pesanan';

-- 21. laporan
CREATE TABLE `laporan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_publik` CHAR(10) NOT NULL,
  `pesanan_id` BIGINT NOT NULL,
  `pelapor_id` BIGINT NOT NULL,
  `penangan_id` BIGINT NULL DEFAULT NULL,
  `jenis` ENUM('pelanggan_tidak_melunasi','driver_tidak_datang','barang_rusak','keterlambatan','lainnya') NOT NULL,
  `status` ENUM('baru','ditinjau','selesai','ditolak') NOT NULL,
  `deskripsi` VARCHAR(400) NOT NULL,
  `keputusan` ENUM('dibenarkan','ditolak','kompensasi') NULL DEFAULT NULL,
  `nominal_kompensasi` BIGINT NULL DEFAULT NULL,
  `menahan_finalisasi` TINYINT NOT NULL DEFAULT 0,
  `catatan_admin` VARCHAR(250) NULL DEFAULT NULL,
  `ditangani_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_laporan_kode_publik` (`kode_publik`),
  KEY `ix_laporan_status` (`status`, `dibuat_at`),
  CONSTRAINT `fk_laporan_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_laporan_pelapor_id` FOREIGN KEY (`pelapor_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_laporan_penangan_id` FOREIGN KEY (`penangan_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_laporan_menahan_finalisasi` CHECK (`menahan_finalisasi` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Laporan sengketa dan kejadian';

-- 22. lampiran_laporan
CREATE TABLE `lampiran_laporan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `laporan_id` BIGINT NOT NULL,
  `jenis` ENUM('foto','dokumen','tangkapan_layar') NOT NULL,
  `berkas_path` VARCHAR(200) NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_lampiran_laporan_laporan_id` FOREIGN KEY (`laporan_id`) REFERENCES `laporan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bukti laporan';

-- 23. rating
CREATE TABLE `rating` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pesanan_id` BIGINT NOT NULL,
  `pemberi_id` BIGINT NOT NULL,
  `penerima_id` BIGINT NOT NULL,
  `arah` ENUM('pelanggan_ke_driver','driver_ke_pelanggan') NOT NULL,
  `skor` TINYINT NOT NULL COMMENT '1 sampai 5',
  `komentar` VARCHAR(200) NULL DEFAULT NULL COMMENT 'komentar singkat',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rating_pesanan_pemberi` (`pesanan_id`, `pemberi_id`),
  CONSTRAINT `fk_rating_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_rating_pemberi_id` FOREIGN KEY (`pemberi_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_rating_penerima_id` FOREIGN KEY (`penerima_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_rating_skor` CHECK (`skor` BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rating dua arah (hanya terlihat Admin)';

-- 24. dompet
CREATE TABLE `dompet` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NULL DEFAULT NULL,
  `jenis` ENUM('pelanggan','driver','perusahaan_tertahan','perusahaan_pendapatan','perusahaan_operasional','kas_bank','piutang_gateway','transit_penarikan') NOT NULL,
  `saldo` BIGINT NOT NULL DEFAULT 0,
  `versi` INT NOT NULL DEFAULT 0 COMMENT 'kunci optimistis saat saldo diubah',
  `status` ENUM('aktif','dibekukan') NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dompet_pengguna_jenis` (`pengguna_id`, `jenis`),
  CONSTRAINT `fk_dompet_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_dompet_saldo` CHECK (`saldo` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Dompet pengguna dan akun sistem';

-- 25. topup
CREATE TABLE `topup` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_publik` CHAR(10) NOT NULL,
  `pengguna_id` BIGINT NOT NULL,
  `dompet_id` BIGINT NOT NULL,
  `nominal` BIGINT NOT NULL,
  `biaya_gateway` BIGINT NOT NULL COMMENT 'ditanggung pelanggan, ditambahkan di atas nominal',
  `total_bayar` BIGINT NOT NULL COMMENT 'nominal + biaya_gateway',
  `metode` VARCHAR(20) NOT NULL,
  `order_id_gateway` VARCHAR(40) NOT NULL,
  `status` ENUM('menunggu','berhasil','gagal','kedaluwarsa','dibatalkan') NOT NULL,
  `kedaluwarsa_at` DATETIME NOT NULL,
  `dibayar_at` DATETIME NULL DEFAULT NULL,
  `settlement_ref` VARCHAR(30) NULL DEFAULT NULL,
  `settlement_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_topup_kode_publik` (`kode_publik`),
  UNIQUE KEY `uq_topup_order_id_gateway` (`order_id_gateway`),
  CONSTRAINT `fk_topup_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_topup_dompet_id` FOREIGN KEY (`dompet_id`) REFERENCES `dompet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pengisian saldo lewat gateway';

-- 26. rekening_perusahaan
CREATE TABLE `rekening_perusahaan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `peran` ENUM('penampung','operasional') NOT NULL COMMENT 'penampung = menerima dana gateway; operasional = sumber dana awal dan tujuan penarikan Direktur',
  `nama_bank` VARCHAR(40) NOT NULL,
  `nomor_rekening_enc` VARBINARY(255) NOT NULL,
  `nomor_rekening_akhir` CHAR(4) NOT NULL,
  `atas_nama` VARCHAR(100) NOT NULL,
  `aktif` TINYINT NOT NULL DEFAULT 1,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `chk_rekening_perusahaan_aktif` CHECK (`aktif` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rekening bank perusahaan';

-- 27. penarikan
CREATE TABLE `penarikan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_publik` CHAR(10) NOT NULL,
  `jenis` ENUM('penarikan_pelanggan','pencairan_driver','penarikan_direktur') NOT NULL,
  `dompet_id` BIGINT NOT NULL,
  `rekening_bank_id` BIGINT NULL DEFAULT NULL,
  `rekening_perusahaan_id` BIGINT NULL DEFAULT NULL,
  `nominal` BIGINT NOT NULL COMMENT 'nilai yang mengurangi dompet',
  `biaya_layanan` BIGINT NOT NULL COMMENT 'tarif flat pelanggan, masuk Pendapatan SAVAR',
  `biaya_bank` BIGINT NOT NULL COMMENT 'biaya transfer bank sebenarnya',
  `ditanggung_oleh` ENUM('pelanggan','driver','perusahaan') NOT NULL COMMENT 'BR-WLT-11',
  `nominal_transfer` BIGINT NOT NULL COMMENT 'yang diterima penerima',
  `status` ENUM('diajukan','diproses','berhasil','gagal','ditolak','perlu_cek') NOT NULL COMMENT 'ditolak = nama rekening tidak cocok; perlu_cek = hasil transfer tidak pasti',
  `alasan_status` VARCHAR(200) NULL DEFAULT NULL,
  `referensi_bank` VARCHAR(40) NULL DEFAULT NULL COMMENT 'BR-ADM-06',
  `diajukan_oleh` BIGINT NOT NULL COMMENT 'pelanggan sendiri, Admin (pencairan), atau Direktur',
  `diproses_oleh` BIGINT NULL DEFAULT NULL COMMENT 'NULL bila otomatis',
  `diajukan_at` DATETIME NOT NULL,
  `selesai_at` DATETIME NULL DEFAULT NULL,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_penarikan_kode_publik` (`kode_publik`),
  KEY `ix_penarikan_status` (`status`, `diajukan_at`),
  CONSTRAINT `fk_penarikan_dompet_id` FOREIGN KEY (`dompet_id`) REFERENCES `dompet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_penarikan_rekening_bank_id` FOREIGN KEY (`rekening_bank_id`) REFERENCES `rekening_bank` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_penarikan_rekening_perusahaan_id` FOREIGN KEY (`rekening_perusahaan_id`) REFERENCES `rekening_perusahaan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_penarikan_diajukan_oleh` FOREIGN KEY (`diajukan_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_penarikan_diproses_oleh` FOREIGN KEY (`diproses_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Penarikan pelanggan, pencairan driver, penarikan Direktur';

-- 28. setoran_perusahaan
CREATE TABLE `setoran_perusahaan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kode_publik` CHAR(10) NOT NULL,
  `jenis` ENUM('dana_awal','tambahan','pengembalian') NOT NULL,
  `rekening_sumber_id` BIGINT NOT NULL,
  `rekening_tujuan_id` BIGINT NOT NULL,
  `nominal` BIGINT NOT NULL,
  `status` ENUM('diajukan','disetujui','ditolak','dibukukan') NOT NULL,
  `referensi_bank` VARCHAR(40) NOT NULL COMMENT 'bukti bank wajib',
  `bukti_path` VARCHAR(200) NOT NULL,
  `diajukan_oleh` BIGINT NOT NULL COMMENT 'Admin',
  `disetujui_oleh` BIGINT NULL DEFAULT NULL COMMENT 'Direktur',
  `alasan` VARCHAR(200) NULL DEFAULT NULL,
  `diajukan_at` DATETIME NOT NULL,
  `disetujui_at` DATETIME NULL DEFAULT NULL,
  `dibukukan_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_setoran_perusahaan_kode_publik` (`kode_publik`),
  CONSTRAINT `fk_setoran_perusahaan_rekening_sumber_id` FOREIGN KEY (`rekening_sumber_id`) REFERENCES `rekening_perusahaan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_setoran_perusahaan_rekening_tujuan_id` FOREIGN KEY (`rekening_tujuan_id`) REFERENCES `rekening_perusahaan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_setoran_perusahaan_diajukan_oleh` FOREIGN KEY (`diajukan_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_setoran_perusahaan_disetujui_oleh` FOREIGN KEY (`disetujui_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: setoran dana awal/tambahan ke dompet Perusahaan';

-- 29. jurnal_dompet
CREATE TABLE `jurnal_dompet` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `jenis` VARCHAR(30) NOT NULL COMMENT 'topup, bayar_dp, refund, finalisasi, dst.',
  `pesanan_id` BIGINT NULL DEFAULT NULL,
  `topup_id` BIGINT NULL DEFAULT NULL,
  `penarikan_id` BIGINT NULL DEFAULT NULL,
  `setoran_id` BIGINT NULL DEFAULT NULL,
  `laporan_id` BIGINT NULL DEFAULT NULL,
  `referensi_eksternal` VARCHAR(60) NULL DEFAULT NULL COMMENT 'mis. nomor settlement gateway',
  `keterangan` VARCHAR(200) NOT NULL,
  `kunci_idempotensi` VARCHAR(80) NOT NULL COMMENT 'mencegah jurnal ganda saat timer/webhook berulang',
  `membalik_jurnal_id` BIGINT NULL DEFAULT NULL COMMENT 'entri pembalik BR-WLT-14',
  `dibuat_oleh` BIGINT NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_jurnal_dompet_kunci_idempotensi` (`kunci_idempotensi`),
  CONSTRAINT `fk_jurnal_dompet_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_topup_id` FOREIGN KEY (`topup_id`) REFERENCES `topup` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_penarikan_id` FOREIGN KEY (`penarikan_id`) REFERENCES `penarikan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_setoran_id` FOREIGN KEY (`setoran_id`) REFERENCES `setoran_perusahaan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_laporan_id` FOREIGN KEY (`laporan_id`) REFERENCES `laporan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_membalik_jurnal_id` FOREIGN KEY (`membalik_jurnal_id`) REFERENCES `jurnal_dompet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_jurnal_dompet_dibuat_oleh` FOREIGN KEY (`dibuat_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kepala jurnal (tidak boleh diubah)';

-- 30. entri_dompet
CREATE TABLE `entri_dompet` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `jurnal_id` BIGINT NOT NULL,
  `dompet_id` BIGINT NOT NULL,
  `arah` ENUM('debit','kredit') NOT NULL,
  `nominal` BIGINT NOT NULL,
  `saldo_setelah` BIGINT NOT NULL COMMENT 'saldo berjalan untuk audit',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_entri_dompet_urut` (`dompet_id`, `id`),
  CONSTRAINT `fk_entri_dompet_jurnal_id` FOREIGN KEY (`jurnal_id`) REFERENCES `jurnal_dompet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_entri_dompet_dompet_id` FOREIGN KEY (`dompet_id`) REFERENCES `dompet` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_entri_dompet_nominal` CHECK (`nominal` >= 0),
  CONSTRAINT `chk_entri_dompet_saldo_setelah` CHECK (`saldo_setelah` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Baris debit/kredit';

-- 31. webhook_pembayaran
CREATE TABLE `webhook_pembayaran` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `topup_id` BIGINT NULL DEFAULT NULL,
  `order_id_gateway` VARCHAR(40) NOT NULL,
  `status_gateway` VARCHAR(20) NOT NULL,
  `gross_amount` BIGINT NOT NULL,
  `signature_valid` TINYINT NOT NULL,
  `kunci_dedup` CHAR(40) NOT NULL COMMENT 'hash(order_id + status + gross + keabsahan tanda tangan) agar notifikasi ganda tidak diproses dua kali',
  `hasil` ENUM('diproses','tidak_valid','diabaikan') NOT NULL COMMENT 'notifikasi ganda tidak membuat baris baru, hanya menaikkan jumlah_diterima',
  `jumlah_diterima` INT NOT NULL COMMENT 'bertambah setiap kali notifikasi yang sama tiba lagi',
  `diterima_at` DATETIME NOT NULL COMMENT 'diterima pertama kali',
  `diterima_terakhir_at` DATETIME NOT NULL,
  `diproses_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_webhook_pembayaran_kunci_dedup` (`kunci_dedup`),
  CONSTRAINT `fk_webhook_pembayaran_topup_id` FOREIGN KEY (`topup_id`) REFERENCES `topup` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `chk_webhook_pembayaran_signature_valid` CHECK (`signature_valid` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Notifikasi gateway';

-- 32. tarif_penarikan
CREATE TABLE `tarif_penarikan` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `jenis` ENUM('penarikan_pelanggan') NOT NULL,
  `batas_bawah` BIGINT NOT NULL,
  `batas_atas` BIGINT NULL DEFAULT NULL,
  `tarif_flat` BIGINT NOT NULL DEFAULT 0,
  `tarif_persen` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `berlaku_mulai` DATE NOT NULL,
  `aktif` TINYINT NOT NULL DEFAULT 1,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `chk_tarif_penarikan_aktif` CHECK (`aktif` IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tarif penarikan pelanggan';

-- 33. riwayat_harga_bbm
CREATE TABLE `riwayat_harga_bbm` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `jenis_bbm_id` BIGINT NOT NULL,
  `harga_per_liter` BIGINT NOT NULL,
  `berlaku_mulai` DATETIME NOT NULL,
  `ditetapkan_oleh` BIGINT NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_riwayat_harga_bbm_jenis_bbm_id` FOREIGN KEY (`jenis_bbm_id`) REFERENCES `jenis_bbm` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_riwayat_harga_bbm_ditetapkan_oleh` FOREIGN KEY (`ditetapkan_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: harga BBM berjadwal';

-- 34. rekonsiliasi
CREATE TABLE `rekonsiliasi` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tanggal` DATE NOT NULL,
  `saldo_bank` BIGINT NOT NULL COMMENT 'menurut mutasi bank',
  `total_kewajiban` BIGINT NOT NULL COMMENT 'jumlah dompet pengguna, tertahan, pendapatan, operasional, transit',
  `piutang_gateway` BIGINT NOT NULL COMMENT 'top-up berhasil yang belum settlement',
  `selisih` BIGINT NOT NULL COMMENT 'saldo_bank + piutang_gateway - total_kewajiban',
  `status` ENUM('sesuai','selisih_dijelaskan','selisih_terbuka') NOT NULL,
  `catatan` VARCHAR(250) NULL DEFAULT NULL,
  `dibuat_oleh` BIGINT NULL DEFAULT NULL COMMENT 'NULL bila dijalankan penjadwal',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rekonsiliasi_tanggal` (`tanggal`),
  CONSTRAINT `fk_rekonsiliasi_dibuat_oleh` FOREIGN KEY (`dibuat_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pencocokan saldo bank dan buku';

-- 35. rekonsiliasi_item
CREATE TABLE `rekonsiliasi_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `rekonsiliasi_id` BIGINT NOT NULL,
  `jenis` VARCHAR(30) NOT NULL,
  `penarikan_id` BIGINT NULL DEFAULT NULL,
  `topup_id` BIGINT NULL DEFAULT NULL,
  `nominal` BIGINT NOT NULL,
  `status` ENUM('terbuka','selesai') NOT NULL,
  `catatan` VARCHAR(250) NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_rekonsiliasi_item_rekonsiliasi_id` FOREIGN KEY (`rekonsiliasi_id`) REFERENCES `rekonsiliasi` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_rekonsiliasi_item_penarikan_id` FOREIGN KEY (`penarikan_id`) REFERENCES `penarikan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_rekonsiliasi_item_topup_id` FOREIGN KEY (`topup_id`) REFERENCES `topup` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TABEL BARU: rincian selisih per transaksi';

-- 36. konfigurasi
CREATE TABLE `konfigurasi` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kunci` VARCHAR(60) NOT NULL,
  `nilai` VARCHAR(100) NOT NULL,
  `satuan` VARCHAR(30) NULL DEFAULT NULL,
  `keterangan` VARCHAR(200) NULL DEFAULT NULL,
  `diubah_oleh` BIGINT NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_konfigurasi_kunci` (`kunci`),
  CONSTRAINT `fk_konfigurasi_diubah_oleh` FOREIGN KEY (`diubah_oleh`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Parameter sistem';

-- 37. notifikasi
CREATE TABLE `notifikasi` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `pengguna_id` BIGINT NOT NULL,
  `jenis` VARCHAR(30) NOT NULL,
  `judul` VARCHAR(100) NOT NULL,
  `pesanan_id` BIGINT NULL DEFAULT NULL COMMENT 'agar aplikasi dapat langsung membuka pesanan',
  `dibaca_at` DATETIME NULL DEFAULT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_notifikasi_pengguna` (`pengguna_id`, `dibaca_at`, `id`),
  CONSTRAINT `fk_notifikasi_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_notifikasi_pesanan_id` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Notifikasi (polling)';

-- 38. kunci_idempotensi
CREATE TABLE `kunci_idempotensi` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `kunci` VARCHAR(80) NOT NULL,
  `pengguna_id` BIGINT NOT NULL,
  `endpoint` VARCHAR(60) NOT NULL,
  `hash_permintaan` CHAR(40) NOT NULL COMMENT 'mendeteksi kunci sama dengan isi berbeda',
  `status` ENUM('proses','selesai') NOT NULL,
  `jumlah_pemakaian` INT NOT NULL COMMENT 'bertambah saat kunci diputar ulang',
  `kedaluwarsa_at` DATETIME NOT NULL,
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_kunci_idempotensi_kunci` (`kunci`),
  CONSTRAINT `fk_kunci_idempotensi_pengguna_id` FOREIGN KEY (`pengguna_id`) REFERENCES `pengguna` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kunci Idempotency-Key permintaan API';

-- 39. kunci_penjadwal
CREATE TABLE `kunci_penjadwal` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `nama_pekerjaan` VARCHAR(40) NOT NULL,
  `dikunci_sampai` DATETIME NULL DEFAULT NULL,
  `terakhir_jalan_at` DATETIME NULL DEFAULT NULL,
  `jumlah_jalan` INT NOT NULL DEFAULT 0,
  `terlambat_maks_menit` INT NOT NULL DEFAULT 0 COMMENT 'selisih terbesar antara jadwal dan eksekusi',
  `dibuat_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `diubah_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_kunci_penjadwal_nama_pekerjaan` (`nama_pekerjaan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kunci pekerjaan terjadwal (tanpa cron)';

-- 40. log_audit
CREATE TABLE `log_audit` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `nama_tabel` VARCHAR(40) NOT NULL,
  `pk_nilai` VARCHAR(40) NOT NULL,
  `aksi` ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  `data_lama` JSON NULL DEFAULT NULL,
  `data_baru` JSON NULL DEFAULT NULL,
  `pelaku_id` BIGINT NOT NULL COMMENT '0 untuk sistem',
  `pelaku_peran` VARCHAR(10) NOT NULL COMMENT 'pelanggan, driver, admin, direktur, sistem',
  `trace_id` CHAR(16) NOT NULL,
  `ip` VARCHAR(45) NOT NULL,
  `waktu` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_audit_tabel_pk` (`nama_tabel`, `pk_nilai`),
  KEY `ix_audit_waktu` (`waktu`),
  KEY `ix_audit_pelaku` (`pelaku_id`, `waktu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jejak DML tanpa relasi';
