CREATE SCHEMA IF NOT EXISTS isp_erp;

CREATE TABLE IF NOT EXISTS isp_erp.absen (
    id_absen      SERIAL PRIMARY KEY,
    id_users      INTEGER NOT NULL,
    sakit         INTEGER,
    izin          INTEGER,
    telat         INTEGER,
    cuti          INTEGER,
    bulan         VARCHAR(11),
    tahun         INTEGER,
    tgl_update    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    deleted_at    TIMESTAMP
);

CREATE TABLE isp_erp.acc_penyusutan (
    id_acc_penyusutan BIGSERIAL PRIMARY KEY,
    nama_aktiva VARCHAR(255),
    kelompok_aktiva VARCHAR(255),
    tgl_beli_aktiva DATE,
    harga_beli_aktiva BIGINT,
    nilai_residu BIGINT,
    umur_ekonomis BIGINT,
    lokasi_aktiva VARCHAR(255),
    akumulasi_beban BIGINT,
    beban_per_tahun DOUBLE PRECISION,
    terhitung_tanggal DATE,
    nilai_buku DOUBLE PRECISION,
    beban_perbulan DOUBLE PRECISION,
    qty_tersedia BIGINT,
    qty_rusak BIGINT,
    qty_dijual BIGINT,
    id_rekening_aset BIGINT,
    jenis_transaksi_aset VARCHAR(255),
    id_rekening_akumulasi BIGINT,
    jenis_transaksi_akumulasi VARCHAR(255),
    id_rekening_depresiasi BIGINT,
    jenis_transaksi_depresiasi VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    lampiran_1 VARCHAR(255),
    lampiran_2 VARCHAR(255),
    lampiran_3 VARCHAR(255)
);

CREATE TABLE isp_erp.acc_pembayaran_invoice_beli (
    id_acc_pembayaran_invoice_beli BIGSERIAL PRIMARY KEY,
    id_acc_rekening_kredit BIGINT,
    id_acc_rekening_debit BIGINT,
    id_invoice_pembelian BIGINT,
    no_pembayaran_invoice VARCHAR(255),
    tgl_pembayaran DATE,
    nilai_pembayaran BIGINT,
    keterangan_pembayaran VARCHAR(255),
    bukti_transfer VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE isp_erp.acc_klasifikasi_akun (
    id_acc_klasifikasi_akun BIGSERIAL PRIMARY KEY,
    kategori_klasifikasi_akun VARCHAR(255),
    nama_klasifikasi VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE isp_erp.acc_kas_detail (
    id_acc_kas_detail BIGSERIAL PRIMARY KEY,
    id_acc_kas BIGINT,
    id_acc_rekening BIGINT,
    id_divisi BIGINT,
    nominal_kas_detail BIGINT,
    komentar_kas_detail TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 5. Table acc_kas
CREATE TABLE isp_erp.acc_kas (
    id_acc_kas BIGSERIAL PRIMARY KEY,
    id_acc_rekening BIGINT,
    jenis_penerima VARCHAR(255),
    id_penerima_kas BIGINT,
    jenis_kas VARCHAR(255),
    no_transaksi_kas VARCHAR(255),
    nominal_kas BIGINT,
    inclusive_tax VARCHAR(255),
    memo_kas TEXT,
    tgl_kas TIMESTAMP,
    status_kas VARCHAR(255) DEFAULT 'Draft',
    lampiran_kas VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 6. Table acc_jurnal_umum
CREATE TABLE isp_erp.acc_jurnal_umum (
    id_acc_jurnal_umum BIGSERIAL PRIMARY KEY,
    id_acc_rekening BIGINT,
    trx_number VARCHAR(255),
    trx_notes VARCHAR(255),
    transaction_type VARCHAR(255),
    nominal_transaksi DOUBLE PRECISION,
    saldo_akhir_jurnal DOUBLE PRECISION DEFAULT 0,
    tgl_transaksi_jurnal DATE,
    jam_transaksi_jurnal TIME,
    status_buku_besar VARCHAR(255) DEFAULT 'Pending',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 7. Table acc_invoice_pembelian
CREATE TABLE isp_erp.acc_invoice_pembelian (
    id_invoice_pembelian BIGSERIAL PRIMARY KEY,
    id_vendor_pemasok BIGINT,
    id_users BIGINT,
    id_divisi BIGINT,
    no_pembelian VARCHAR(255),
    total_harga_beli BIGINT,
    total_tax_pembelian BIGINT,
    no_po_pembelian VARCHAR(255),
    tgl_faktur_pembelian DATE,
    keterangan_pembelian VARCHAR(255),
    catatan_pembelian VARCHAR(255),
    tgl_pengiriman DATE,
    term_pembayaran VARCHAR(255),
    credit_debit_memo VARCHAR(255),
    biaya_lain_lain BIGINT,
    lampiran_nota VARCHAR(255),
    lampiran_faktur_pajak VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 8. Table acc_detail_invoice_pembelian
CREATE TABLE isp_erp.acc_detail_invoice_pembelian (
    id_acc_detail_invoice_pembelian BIGSERIAL PRIMARY KEY,
    id_invoice_pembelian BIGINT,
    no_barang VARCHAR(255),
    deskripsi_barang VARCHAR(255),
    jumlah_barang BIGINT,
    satuan_barang VARCHAR(255),
    harga_barang BIGINT,
    discount_barang BIGINT,
    pajak_barang BIGINT,
    job_barang VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 9. Table absen_daily
CREATE TABLE isp_erp.absen_daily (
    id_absen_daily BIGSERIAL PRIMARY KEY,
    id_daily BIGINT,
    id_users BIGINT,
    status_kehadiran VARCHAR(255),
    tgl_absen_daily TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 10. Table absensi
CREATE TABLE isp_erp.absensi (
    id_absensi SERIAL PRIMARY KEY,
    id_fingger INT,
    id_karyawan INT,
    tanggal_absen DATE,
    absen_masuk TIME,
    status_masuk VARCHAR(25),
    absen_keluar TIME,
    status_keluar VARCHAR(25),
    keterangan_masuk VARCHAR(100),
    keterangan_keluar TEXT,
    metode_absen_masuk VARCHAR(255),
    lat_long_absen_masuk VARCHAR(255),
    metode_absen_keluar VARCHAR(255),
    lat_long_absen_keluar VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- 1) front_wilayah
CREATE TABLE IF NOT EXISTS isp_erp.front_wilayah (
  id_front_wilayah  SERIAL PRIMARY KEY,
  id_provinsi       INTEGER,
  id_kabupaten      INTEGER,
  judul             VARCHAR(255),
  sub_judul         VARCHAR(255),
  slug              VARCHAR(255),
  created_at        TIMESTAMP,
  updated_at        TIMESTAMP,
  deleted_at        TIMESTAMP
);

-- 2) front_produk
CREATE TABLE IF NOT EXISTS isp_erp.front_produk (
  id_front_produk   SERIAL PRIMARY KEY,
  id_front_wilayah  INTEGER,
  judul_produk      VARCHAR(255),
  harga_produk      VARCHAR(255),
  gambar_produk     VARCHAR(255),
  created_at        DATE,
  updated_at        DATE,
  deleted_at        DATE
);

-- 3) front_kontak
CREATE TABLE IF NOT EXISTS isp_erp.front_kontak (
  id_front_kontak    SERIAL PRIMARY KEY,
  id_unit_instalasi  INTEGER,
  id_front_wilayah   INTEGER,
  id_users_helpdesk  INTEGER,
  id_users_marketing INTEGER,
  created_at         DATE,
  updated_at         DATE,
  deleted_at         DATE
);

-- 4) front_coverage
CREATE TABLE IF NOT EXISTS isp_erp.front_coverage (
  id_front_coverage  SERIAL PRIMARY KEY,
  id_front_wilayah   INTEGER,
  id_kecamatan       INTEGER,
  id_front_kontak    INTEGER,
  nama_desa          VARCHAR(255),
  alamat             TEXT,
  gps                VARCHAR(255),
  created_at         DATE,
  updated_at         DATE,
  deleted_at         DATE
);

-- 5) freelance_withdraw
CREATE TABLE IF NOT EXISTS isp_erp.freelance_withdraw (
  id_freelance_withdraw BIGSERIAL PRIMARY KEY,
  id_users              BIGINT,
  nominal_withdraw      DOUBLE PRECISION,
  biaya_transfer        DOUBLE PRECISION,
  nominal_diterima      DOUBLE PRECISION,
  saldo_awal            DOUBLE PRECISION,
  saldo_akhir           DOUBLE PRECISION,
  rekening_penerima     VARCHAR(255),
  tgl_request_withdraw  TIMESTAMP,
  tgl_approve_withdraw  TIMESTAMP,
  reference_no          VARCHAR(255),
  notes                 VARCHAR(255),
  status                VARCHAR(255) DEFAULT 'Pending',
  signature_key         VARCHAR(255),
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- 6) freelance_trx
CREATE TABLE IF NOT EXISTS isp_erp.freelance_trx (
  id_freelance_trx     BIGSERIAL PRIMARY KEY,
  id_freelance_periode BIGINT,
  id_instalasi         BIGINT,
  id_invoice           BIGINT,
  tgl_transaksi        TIMESTAMP,
  total_tagihan        DOUBLE PRECISION,
  ppn                  DOUBLE PRECISION,
  bhp_uso              DOUBLE PRECISION,
  nilai_dibayar        DOUBLE PRECISION,
  payment_gateway      DOUBLE PRECISION,
  biaya_admin          DOUBLE PRECISION,
  persentase_komisi    DOUBLE PRECISION,
  nilai_komisi         DOUBLE PRECISION,
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);


-- 7) freelance_periode
CREATE TABLE IF NOT EXISTS isp_erp.freelance_periode (
  id_freelance_periode BIGSERIAL PRIMARY KEY,
  id_freelance_fee     BIGINT,
  tgl_periode_awal     DATE,
  tgl_periode_akhir    DATE,
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- 8) freelance_fee
CREATE TABLE IF NOT EXISTS isp_erp.freelance_fee (
  id_freelance_fee          BIGSERIAL PRIMARY KEY,
  id_pic                    BIGINT,
  id_freelance              BIGINT,
  persentase_fee            DOUBLE PRECISION,
  periode_fee               VARCHAR(255),
  target_level1             INTEGER,
  nilai_fee_per_pelanggan1  INTEGER,
  target_level2             INTEGER,
  nilai_fee_per_pelanggan2  INTEGER,
  target_level3             INTEGER,
  nilai_fee_per_pelanggan3  INTEGER,
  biaya_admin               BIGINT,
  notes_fee                 VARCHAR(255),
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  deleted_at                TIMESTAMP
);

-- 9) forgot_password
CREATE TABLE IF NOT EXISTS isp_erp.forgot_password (
  id_forgot_password  BIGSERIAL PRIMARY KEY,
  id_customer         BIGINT,
  forgot_password_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  link_status         VARCHAR(255) DEFAULT 'Pending',
  created_at          TIMESTAMP,
  updated_at          TIMESTAMP,
  deleted_at          TIMESTAMP
);

-- 10) failed_jobs  (Laravel default; uuid UNIQUE)
CREATE TABLE IF NOT EXISTS isp_erp.failed_jobs (
  id          BIGSERIAL PRIMARY KEY,
  uuid        VARCHAR(255) UNIQUE,
  connection  TEXT NOT NULL,
  queue       TEXT NOT NULL,
  payload     TEXT NOT NULL,
  exception   TEXT NOT NULL,
  failed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 1) hashtag
CREATE TABLE IF NOT EXISTS isp_erp.hashtag (
  id_hashtag     BIGSERIAL PRIMARY KEY,
  id_users       BIGINT,
  nama_hashtag   VARCHAR(35) NOT NULL,
  tgl_hashtag    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at     TIMESTAMP,
  updated_at     TIMESTAMP,
  deleted_at     TIMESTAMP
);

-- 2) hashtag_list
CREATE TABLE IF NOT EXISTS isp_erp.hashtag_list (
  id_hashtag_list  BIGSERIAL PRIMARY KEY,
  id_berita        BIGINT,
  id_hashtag       BIGINT,
  tgl_hashtag_list TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at       TIMESTAMP,
  updated_at       TIMESTAMP,
  deleted_at       TIMESTAMP
);

-- 3) fu_calon_pelanggan
CREATE TABLE IF NOT EXISTS isp_erp.fu_calon_pelanggan (
  id_fu_calon_pelanggan BIGSERIAL PRIMARY KEY,
  id_customer           BIGINT,
  id_users              BIGINT,
  posisi_fu             VARCHAR(255),
  catatan_fu            VARCHAR(255),
  tgl_follow_up         DATE,
  lampiran_fu1          VARCHAR(255),
  lampiran_fu2          VARCHAR(255),
  lampiran_fu3          VARCHAR(255),
  lampiran_fu4          VARCHAR(255),
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- 4) halamanstatis
CREATE TABLE IF NOT EXISTS isp_erp.halamanstatis (
  id_halaman    BIGSERIAL PRIMARY KEY,
  judul         VARCHAR(255),
  isi_halaman   TEXT,
  kategori      VARCHAR(255),
  tgl_posting   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  gambar        VARCHAR(255),
  dibaca        BIGINT DEFAULT 0,
  created_at    TIMESTAMP,
  updated_at    TIMESTAMP,
  deleted_at    TIMESTAMP
);

-- 5) hrd_absen
CREATE TABLE IF NOT EXISTS isp_erp.hrd_absen (
  id_absen          BIGSERIAL PRIMARY KEY,
  id_karyawan       BIGINT,
  tgl_absen         DATE NOT NULL,
  jam_mulai         TIME,
  jam_absen         TIME,
  status_absen      VARCHAR(20) DEFAULT 'Berangkat',
  acc_absen         VARCHAR(25) DEFAULT 'Pending',
  longitude         TEXT,
  latitude          TEXT,
  swafoto           TEXT,
  keterangan_absen  VARCHAR(255),
  metode_absen      VARCHAR(50),
  _token            TEXT,
  deleted_at        TIMESTAMP,
  updated_at        TIMESTAMP,
  created_at        TIMESTAMP
);

-- hrd_cuti_izin
CREATE TABLE IF NOT EXISTS isp_erp.hrd_cuti_izin (
    id_hrd_cuti_izin     SERIAL PRIMARY KEY,
    id_karyawan          INTEGER NOT NULL,
    id_direktur          BIGINT,
    id_hrd               VARCHAR(50),
    id_supervisor        BIGINT,
    id_manager           BIGINT,
    jenis                VARCHAR(255) NOT NULL,
    keterangan_cuti_izin VARCHAR(255) NOT NULL,
    tgl_pengajuan        DATE NOT NULL,
    jumlah_hari          INTEGER NOT NULL,
    sisa_cuti            BIGINT,
    bonus_cuti           INTEGER,
    tgl_cuti             DATE,
    lampiran             VARCHAR(255),
    status_cuti_izin     VARCHAR(15) DEFAULT 'Pending',
    acc_direktur         VARCHAR(50),
    acc_hrd              VARCHAR(50) DEFAULT 'Pending',
    acc_spv              VARCHAR(50) DEFAULT 'Pending',
    acc_manager          VARCHAR(50),
    acc_hrd_date         TIMESTAMP,
    acc_manager_date     TIMESTAMP,
    acc_direktur_date    TIMESTAMP,
    sisa_cuti_off        BIGINT,
    _token               TEXT,
    deleted_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    created_at           TIMESTAMP
);

-- (Opsional) Set sequence sesuai AUTO_INCREMENT MySQL
-- ALTER SEQUENCE isp_erp.hrd_cuti_izin_id_hrd_cuti_izin_seq RESTART WITH 316;

-- 7) hrd_cuti_izin_detail
CREATE TABLE IF NOT EXISTS isp_erp.hrd_cuti_izin_detail (
  id_hrd_cuti_izin_detail SERIAL PRIMARY KEY,
  id_hrd_cuti_izin        INTEGER,
  id_karyawan             INTEGER,
  tanggal_cuti_izin       DATE,
  created_at              TIMESTAMP,
  updated_at              TIMESTAMP,
  deleted_at              TIMESTAMP
);

-- 8) hrd_lokasi_absen
CREATE TABLE IF NOT EXISTS isp_erp.hrd_lokasi_absen (
  id_hrd_lokasi_absen  BIGSERIAL PRIMARY KEY,
  nama_lokasi          VARCHAR(255),
  lat_long_lokasi      VARCHAR(255),
  jarak_maksimum_absen VARCHAR(255),
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- 9) hrd_peminjaman_kendaraan
CREATE TABLE IF NOT EXISTS isp_erp.hrd_peminjaman_kendaraan (
  id_hrd_peminjaman_kendaraan SERIAL PRIMARY KEY,
  id_tiket               VARCHAR(255),
  id_users               INTEGER,
  kendaraan              VARCHAR(100),
  nama_driver            VARCHAR(255),
  tgl_peminjaman         DATE,
  jam_berangkat          TIME,
  jam_pulang             TIME,
  biaya_transport        BIGINT,
  keterangan_peminjaman  TEXT,
  acc_manager            VARCHAR(255),
  acc_hrd                VARCHAR(255),
  acc_finance            VARCHAR(255),
  acc_finance_date       TIMESTAMP,
  acc_manager_date       TIMESTAMP,
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP,
  foto_kendaraan_sebelum VARCHAR(255),
  foto_kendaraan_sesudah VARCHAR(255)
);


-- hrd_request_barang
CREATE TABLE IF NOT EXISTS isp_erp.hrd_request_barang (
    id_hrd_request              SERIAL PRIMARY KEY,
    id_hrd_request_barang_vendor INTEGER,
    kategori_po                 VARCHAR(255),
    catatan                     TEXT,
    status                      VARCHAR(255),
    jenis_rekening              VARCHAR(100),
    no_po                       VARCHAR(255),
    id_users                    INTEGER,
    id_divisi                   BIGINT,
    no_surat                    VARCHAR(255),
    tgl_request                 DATE,
    tgl_setelment               DATE,
    tujuan_po                   VARCHAR(255),
    nilai_barang                NUMERIC(25,0),
    gross_amount                NUMERIC(25,0),
    tax                         NUMERIC(25,0),
    nilai_dibayar               NUMERIC(25,0),
    belum_dibayar               NUMERIC(25,0),
    tgl_nilai_dibayar           DATE,
    acc_hrd                     VARCHAR(255),
    acc_finance                 VARCHAR(255),
    acc_warehouse               VARCHAR(20),
    acc_direktur                VARCHAR(20),
    id_users_finance            BIGINT,
    id_users_warehouse          VARCHAR(20),
    id_users_direktur           BIGINT,
    acc_direktur_date           TIMESTAMP,
    acc_finance_date            TIMESTAMP,
    catatan_nilai_dibayar       VARCHAR(255),
    created_at                  TIMESTAMP,
    updated_at                  TIMESTAMP,
    deleted_at                  TIMESTAMP
);

-- (Opsional) Set sequence sesuai AUTO_INCREMENT MySQL
-- ALTER SEQUENCE isp_erp.hrd_request_barang_id_hrd_request_seq RESTART WITH 1278;

-- 1) acc_transfer_kas
CREATE TABLE IF NOT EXISTS isp_erp.acc_transfer_kas (
  id_acc_transfer_kas   BIGSERIAL PRIMARY KEY,
  id_acc_rekening_asal  BIGINT,
  id_acc_rekening_tujuan BIGINT,
  no_ref_transfer_kas   VARCHAR(255),
  nominal_transfer      DOUBLE PRECISION,
  id_divisi             BIGINT,
  ket_transfer_kas      VARCHAR(255),
  tgl_transfer_kas      TIMESTAMP,
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- 2) acc_transaksi
CREATE TABLE IF NOT EXISTS isp_erp.acc_transaksi (
  id_acc_transaksi    BIGSERIAL PRIMARY KEY,
  no_transaksi        VARCHAR(255),
  id_acc_rekening     BIGINT,
  menu_transaksi      VARCHAR(255),
  id_menu_transaksi   BIGINT NOT NULL DEFAULT 0,
  debit_rekening_id   DOUBLE PRECISION,
  kredit_rekening_id  DOUBLE PRECISION,
  jenis_transaksi     VARCHAR(255),
  tipe_pembayaran     VARCHAR(255),
  deskripsi_transaksi VARCHAR(99999),
  nominal_transaksi   DOUBLE PRECISION,
  saldo_akhir         DOUBLE PRECISION,
  tgl_transaksi       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at          TIMESTAMP,
  updated_at          TIMESTAMP,
  deleted_at          TIMESTAMP
);

-- (Opsional) jika ingin menyamakan next value AUTO_INCREMENT MySQL (225717):
-- ALTER SEQUENCE isp_erp.acc_transaksi_id_acc_transaksi_seq RESTART WITH 225717;

-- 3) acc_rekening
CREATE TABLE IF NOT EXISTS isp_erp.acc_rekening (
  id_acc_rekening       BIGSERIAL PRIMARY KEY,
  id_acc_klasifikasi_akun BIGINT,
  kode_rekening         VARCHAR(255),
  nama_rekening         VARCHAR(255),
  nama_alias            VARCHAR(255),
  jenis_rekening        VARCHAR(255),
  debit_status          VARCHAR(255),
  kredit_status         VARCHAR(255),
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- 4) acc_piutang_usaha
CREATE TABLE IF NOT EXISTS isp_erp.acc_piutang_usaha (
  id_acc_piutang_usaha  BIGSERIAL PRIMARY KEY,
  id_instalasi          BIGINT,
  total_debet           BIGINT,
  total_piutang_terbayar BIGINT,
  saldo_piutang         BIGINT,
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- 5) acc_penyusutan_akun
CREATE TABLE IF NOT EXISTS isp_erp.acc_penyusutan_akun (
  id_acc_penyusutan_akun BIGSERIAL PRIMARY KEY,
  id_acc_penyusutan      BIGINT,
  id_acc_rekening        BIGINT,
  jenis_transaksi        VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- 1) backup_log_error
CREATE TABLE IF NOT EXISTS isp_erp.backup_log_error (
  id_backup_log_error  SERIAL PRIMARY KEY,
  table_name           VARCHAR(255),
  error_message        TEXT,
  raw_data             TEXT,
  method_backup        VARCHAR(255),
  dbms                 VARCHAR(255),
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- 2) apps_req_uninstall
CREATE TABLE IF NOT EXISTS isp_erp.apps_req_uninstall (
  id_apps_req_uninstall  BIGSERIAL PRIMARY KEY,
  id_customer            BIGINT,
  status_request         VARCHAR(255) DEFAULT 'Pending',
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- api_clients
CREATE TABLE IF NOT EXISTS isp_erp.api_clients (
    id_api_clients BIGSERIAL PRIMARY KEY,
    nama_aplikasi  VARCHAR(255) NOT NULL,
    pic_api_clients VARCHAR(100),
    email          VARCHAR(255),
    client_id      VARCHAR(255) UNIQUE,
    client_secret  VARCHAR(255),
    status         VARCHAR(20) CHECK (status IN ('pending', 'approved', 'rejected')) DEFAULT 'pending',
    scope          VARCHAR(255),
    unit_access    VARCHAR(100),
    expired_at     TIMESTAMP,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at     TIMESTAMP
);

-- 4) api_tokens
CREATE TABLE IF NOT EXISTS isp_erp.api_tokens (
  id_api_tokens  BIGSERIAL PRIMARY KEY,
  api_client_id  BIGINT,
  access_token   VARCHAR(255) NOT NULL,
  expires_at     TIMESTAMP,
  revoked        BOOLEAN DEFAULT FALSE,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP,
  deleted_at     TIMESTAMP
);

-- 5) album
CREATE TABLE IF NOT EXISTS isp_erp.album (
  id_album      BIGSERIAL PRIMARY KEY,
  id_users      BIGINT NOT NULL DEFAULT 3,
  judul_album   VARCHAR(255),
  judul_seo     VARCHAR(255),
  tipe_album    VARCHAR(255),
  caption_album TEXT,
  gambar        VARCHAR(255) NOT NULL DEFAULT 'no-images.jpg',
  tgl_posting   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  aktif         INTEGER NOT NULL DEFAULT 1,
  dibaca        INTEGER NOT NULL DEFAULT 1,
  publikasi     VARCHAR(25),
  created_at    TIMESTAMP,
  updated_at    TIMESTAMP,
  deleted_at    TIMESTAMP
);

-- 6) alat_instalasi
CREATE TABLE IF NOT EXISTS isp_erp.alat_instalasi (
  id_alat_instalasi        SERIAL PRIMARY KEY,
  id_peminjaman            INTEGER,
  id_perangkat_pop         INTEGER,
  nama_alat_instalasi      VARCHAR(255),
  qty_alat_instalasi       INTEGER,
  harga_alat_instalasi     INTEGER,
  deskripsi_alat_instalasi TEXT,
  tgl_alat_instalasi       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at               TIMESTAMP,
  updated_at               TIMESTAMP,
  deleted_at               TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.backup_progress (
    id_backup_progress BIGSERIAL PRIMARY KEY,
    table_name         VARCHAR(255),
    progress_status    VARCHAR(255),
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    deleted_at         TIMESTAMP
);

-- 1) chat_session
CREATE TABLE IF NOT EXISTS isp_erp.chat_session (
  id_chat_session SERIAL PRIMARY KEY,
  id_customer1    INTEGER NOT NULL,
  id_customer2    INTEGER NOT NULL,
  _token          TEXT,
  deleted_at      TIMESTAMP,
  updated_at      TIMESTAMP,
  created_at      TIMESTAMP
);

-- 2) chat_public
CREATE TABLE IF NOT EXISTS isp_erp.chat_public (
  id_chat_public  SERIAL PRIMARY KEY,
  id_customer     INTEGER NOT NULL,
  isi_chat        VARCHAR(255) NOT NULL,
  _token          TEXT,
  deleted_at      TIMESTAMP,
  updated_at      TIMESTAMP,
  created_at      TIMESTAMP
);

-- 3) chat_internal
CREATE TABLE IF NOT EXISTS isp_erp.chat_internal (
  id_chat_internal    BIGSERIAL PRIMARY KEY,
  id_tiket            BIGINT NOT NULL,
  id_users            BIGINT,
  jawaban             TEXT NOT NULL,
  tgl_jawaban         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at          TIMESTAMP,
  updated_at          TIMESTAMP,
  deleted_at          TIMESTAMP
);

-- 4) chat
CREATE TABLE IF NOT EXISTS isp_erp.chat (
  id_chat         BIGSERIAL PRIMARY KEY,
  id_chat_session BIGINT  NOT NULL,
  id_customer     BIGINT  NOT NULL,
  isi_chat        VARCHAR(255) NOT NULL,
  notif_chat      INTEGER NOT NULL DEFAULT 1,
  tgl_chat        DATE,
  waktu_chat      TIME,
  _token          TEXT,
  deleted_at      DATE,
  updated_at      TIMESTAMP,
  created_at      TIMESTAMP,
  read_at         TIMESTAMP
);

-- 5) cache_locks
CREATE TABLE IF NOT EXISTS isp_erp.cache_locks (
  "key"       VARCHAR(255) PRIMARY KEY,
  owner       VARCHAR(255) NOT NULL,
  expiration  INTEGER NOT NULL
);

-- 6) cache
CREATE TABLE IF NOT EXISTS isp_erp.cache (
  "key"       VARCHAR(255) PRIMARY KEY,
  value       TEXT NOT NULL,          -- mediumtext -> text
  expiration  INTEGER NOT NULL
);

-- 7) buku_tamu
CREATE TABLE IF NOT EXISTS isp_erp.buku_tamu (
  id_bukutamu      BIGSERIAL PRIMARY KEY,
  nama_pengirim    VARCHAR(255),
  telepon          VARCHAR(255),
  subjek           VARCHAR(255),
  pesan            TEXT,
  aktif            INTEGER NOT NULL DEFAULT 1,
  tgl_posting      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at       TIMESTAMP,
  updated_at       TIMESTAMP,
  deleted_at       TIMESTAMP
);

-- 8) berita
CREATE TABLE IF NOT EXISTS isp_erp.berita (
  id_berita              BIGSERIAL PRIMARY KEY,
  id_sub_kategori        BIGINT,
  id_users               BIGINT,
  id_album               BIGINT,
  judul_berita           VARCHAR(99999),
  judul_slug_berita      VARCHAR(99999),
  sub_judul_berita       VARCHAR(99999),
  short_desc_berita      VARCHAR(99999),
  isi_berita             TEXT,
  isi_berita_web         TEXT,
  lokasi_berita          VARCHAR(255),
  jumlah_berita_dibaca   BIGINT,
  tgl_berita             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- 9) beneficiary_banks
CREATE TABLE IF NOT EXISTS isp_erp.beneficiary_banks (
  id_beneficiary_banks BIGSERIAL PRIMARY KEY,
  bank_code            VARCHAR(255),
  bank_name            VARCHAR(255),
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- 10) barcode_inventory
CREATE TABLE IF NOT EXISTS isp_erp.barcode_inventory (
  id_barcode_inventory  SERIAL PRIMARY KEY,
  id_perangkat          INTEGER,
  id_inventaris         INTEGER,
  nama_perangkat        VARCHAR(100),
  kode_barcode          VARCHAR(50),
  tgl_kode_barcode      DATE,
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- Table: isp_erp.daily
CREATE TABLE IF NOT EXISTS isp_erp.daily (
    id_daily BIGSERIAL PRIMARY KEY,
    tgl_daily DATE NOT NULL,
    isi_daily TEXT NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Pastikan schema ada
CREATE SCHEMA IF NOT EXISTS isp_erp;

-- customer
CREATE TABLE IF NOT EXISTS isp_erp.customer (
    id_customer BIGSERIAL PRIMARY KEY,
    id_unit_instalasi BIGINT DEFAULT 1,
    id_customer_mitra_dummy BIGINT,
    nik VARCHAR(255),
    npwp VARCHAR(255),
    jenis_customer VARCHAR(255),
    nama VARCHAR(255),
    nama_belakang VARCHAR(255),
    tempat_lahir VARCHAR(255),
    tgl_lahir DATE,
    jenis_kelamin VARCHAR(255),
    nama_up VARCHAR(255),
    alamat TEXT,
    email TEXT,
    telepon VARCHAR(255),
    handphone VARCHAR(255),
    pekerjaan VARCHAR(255),
    keterangan_kontak TEXT,
    akun_facebook TEXT,
    akun_ig TEXT,
    foto_profil VARCHAR(255),
    password VARCHAR(100),
    verif SMALLINT DEFAULT 1,
    tgl_daftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status_berlangganan SMALLINT DEFAULT 0,
    no_va VARCHAR(255),
    no_va_bni VARCHAR(255),
    no_va_mandiri VARCHAR(255),
    nama_va VARCHAR(255),
    id_desa BIGINT,
    id_kecamatan BIGINT,
    id_kabupaten BIGINT,
    id_provinsi BIGINT,
    remember_token VARCHAR(999999),
    firestore_id VARCHAR(999999),
    fcm_token VARCHAR(999999),
    pay_rules INT DEFAULT 0,
    subscription_type VARCHAR(255) DEFAULT 'Regular',
    token VARCHAR(255),
    token_expired_at VARCHAR(999999),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- (Opsional) set nilai auto increment sesuai MySQL dump
-- ALTER SEQUENCE isp_erp.customer_id_customer_seq RESTART WITH 26939;

-- Table: isp_erp.customer_mitra_dummy
CREATE TABLE IF NOT EXISTS isp_erp.customer_mitra_dummy (
    id_customer_mitra_dummy BIGSERIAL PRIMARY KEY,
    id_unit_instalasi BIGINT,
    nik VARCHAR(255),
    nama VARCHAR(255),
    alamat VARCHAR(255),
    telepon VARCHAR(20),
    id_produk BIGINT,
    status_dummy VARCHAR(255) NOT NULL DEFAULT 'Pending',
    jenis_modem VARCHAR(255),
    ip_address_dummy VARCHAR(255),
    tgl_instalasi_dummy DATE,
    email VARCHAR(255),
    tgl_mitra_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Table: isp_erp.divisi
CREATE TABLE IF NOT EXISTS isp_erp.divisi (
    id_divisi BIGSERIAL PRIMARY KEY,
    status_divisi INT NOT NULL,
    nama_divisi VARCHAR(255) NOT NULL,
    keterangan_divisi VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Table: isp_erp.email_pemberitahuan
CREATE TABLE IF NOT EXISTS isp_erp.email_pemberitahuan (
    id_email_pemberitahuan BIGSERIAL PRIMARY KEY,
    subject_email VARCHAR(255) NOT NULL,
    isi_email TEXT NOT NULL,
    tujuan_pengiriman VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Table: isp_erp.desa
CREATE TABLE IF NOT EXISTS isp_erp.desa (
    id_desa BIGSERIAL PRIMARY KEY,
    id_kecamatan CHAR(7) NOT NULL,
    nama_desa VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- 1) hrd_request_barang_vendor
CREATE TABLE IF NOT EXISTS isp_erp.hrd_request_barang_vendor (
  id_hrd_request_barang_vendor SERIAL PRIMARY KEY,
  nama_vendor   VARCHAR(255),
  alamat        TEXT,
  phone         VARCHAR(100),
  created_at    TIMESTAMP,
  updated_at    TIMESTAMP,
  deleted_at    TIMESTAMP
);

-- 2) hrd_request_barang_retur
CREATE TABLE IF NOT EXISTS isp_erp.hrd_request_barang_retur (
  id_hrd_request_barang_retur SERIAL PRIMARY KEY,
  id_hrd_request_barang_detail INTEGER,
  id_acc_rekening              INTEGER,
  jumlah_retur                 VARCHAR(100),
  keterangan_retur             TEXT,
  tgl_retur                    DATE,
  tipe_retur                   VARCHAR(100),
  status_retur                 VARCHAR(100),
  nilai_refund                 BIGINT,
  is_posted                    SMALLINT,
  created_at                   TIMESTAMP,
  updated_at                   TIMESTAMP,
  deleted_at                   TIMESTAMP
);

-- 3) iklan
CREATE TABLE IF NOT EXISTS isp_erp.iklan (
  id_iklan      BIGSERIAL PRIMARY KEY,
  id_users      BIGINT,
  judul_iklan   VARCHAR(255),
  ukuran_iklan  VARCHAR(255),
  posisi_iklan  VARCHAR(255),
  url_iklan     VARCHAR(255),
  gambar_iklan  VARCHAR(255),
  tgl_mulai     DATE,
  tgl_selesai   DATE,
  tgl_iklan     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at    TIMESTAMP,
  updated_at    TIMESTAMP,
  deleted_at    TIMESTAMP
);



-- hrd_tugas_luar
CREATE TABLE IF NOT EXISTS isp_erp.hrd_tugas_luar (
    id_hrd_tugas_luar     BIGSERIAL PRIMARY KEY,
    id_tiket              BIGINT,
    id_karyawan           BIGINT,
    id_divisi             BIGINT,
    tgl_tugas             DATE,
    kendaraan_tugas       VARCHAR(255),
    jumlah_kendaraan_tugas BIGINT,
    peruntukan_tugas      VARCHAR(255),
    keterangan_tugas      VARCHAR(255),
    km_awal               BIGINT,
    km_akhir              BIGINT,
    jarak_tempuh          VARCHAR(255),
    biaya_transport       BIGINT,
    biaya_konsumsi        BIGINT,
    tgl_tugas_luar        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    deleted_at            TIMESTAMP,
    id_hrd                BIGINT,
    acc_manager           VARCHAR(255),
    acc_manager_date      TIMESTAMP,
    acc_hrd               VARCHAR(255),
    id_spv                BIGINT,
    acc_spv               VARCHAR(255) DEFAULT 'Pending',
    id_finance            BIGINT,
    acc_finance           VARCHAR(255) DEFAULT 'Pending',
    acc_finance_date      TIMESTAMP
);

-- (Opsional) set sequence awal sesuai AUTO_INCREMENT MySQL
-- ALTER SEQUENCE isp_erp.hrd_tugas_luar_id_hrd_tugas_luar_seq RESTART WITH 977;

-- 5) hrd_tugas_luar_customer
CREATE TABLE IF NOT EXISTS isp_erp.hrd_tugas_luar_customer (
  id_hrd_tugas_luar_customer BIGSERIAL PRIMARY KEY,
  id_hrd_tugas_luar          BIGINT,
  id_customer                BIGINT,
  created_at                 TIMESTAMP,
  updated_at                 TIMESTAMP,
  deleted_at                 TIMESTAMP
);

-- hrd_waktu_kerja
CREATE TABLE IF NOT EXISTS isp_erp.hrd_waktu_kerja (
    id_waktu_kerja       SERIAL PRIMARY KEY,
    hari                 VARCHAR(25) NOT NULL,
    toleransi_datang     INTEGER,
    toleransi_terlambat  INTEGER,
    jam_mulai_kerja      TIME NOT NULL,
    jam_selesai_kerja    TIME NOT NULL,
    jam_mulai_lembur     TIME,
    jam_selesai_lembur   TIME,
    shift_kerja          VARCHAR(25),
    _token               TEXT,
    deleted_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    created_at           TIMESTAMP
);

-- 7) hrd_tugas_luar_karyawan
CREATE TABLE IF NOT EXISTS isp_erp.hrd_tugas_luar_karyawan (
  id_hrd_tugas_luar_karyawan BIGSERIAL PRIMARY KEY,
  id_hrd_tugas_luar          BIGINT,
  id_users                   BIGINT,
  created_at                 TIMESTAMP,
  updated_at                 TIMESTAMP,
  deleted_at                 TIMESTAMP
);

-- 8) hrd_request_barang_cicilan
CREATE TABLE IF NOT EXISTS isp_erp.hrd_request_barang_cicilan (
  id_hrd_request_barang_cicilan SERIAL PRIMARY KEY,
  id_hrd_request    INTEGER,
  id_users          INTEGER,
  nominal_bayar_cicilan BIGINT,
  metode_pembayaran_cicilan VARCHAR(50),
  tanggal_bayar_cicilan    DATE,
  catatan_cicilan          TEXT,
  created_at               TIMESTAMP,
  updated_at               TIMESTAMP,
  deleted_at               TIMESTAMP
);


-- instalasi
CREATE TABLE IF NOT EXISTS isp_erp.instalasi (
    id_instalasi                 BIGSERIAL PRIMARY KEY,
    id_customer                  BIGINT NOT NULL,
    id_produk                    BIGINT NOT NULL,
    id_marketing                 BIGINT,
    no_berlangganan              VARCHAR(255),
    instalatur                   VARCHAR(255),
    nomor_virtual                VARCHAR(255),
    alamat_instalasi             TEXT,
    tgl_order                    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tgl_pasang                   DATE,
    ip_radio                     VARCHAR(255),
    ip_publik                    VARCHAR(255),
    power_dbm                    VARCHAR(255),
    user_ppoe                    VARCHAR(255),
    power_joint                  VARCHAR(255),
    gambar_ktp                   TEXT,
    gambar_lokasi                TEXT,
    joint_pop                    TEXT,
    type_radio                   TEXT,
    type_router                  TEXT,
    type_ap                      TEXT,
    sn_modem                     VARCHAR(255),
    titik_gps                    TEXT,
    keterangan                   TEXT,
    lampiran1                    TEXT,
    lampiran2                    TEXT,
    lampiran3                    TEXT,
    lampiran4                    TEXT,
    lampiran5                    TEXT,
    tgl_uninstal                 DATE,
    keterangan_uninstall         VARCHAR(255),
    status_instalasi             VARCHAR(255),
    status_tax                   VARCHAR(255) DEFAULT 'Tax',
    status_pph23                 VARCHAR(255) NOT NULL DEFAULT 'Non PPH 23',
    biaya_instalasi              BIGINT,
    status_pembayaran_instalasi  VARCHAR(255) NOT NULL DEFAULT 'Pending',
    tgl_pembayaran_instalasi     DATE,
    id_invoice_dibayar           BIGINT,
    ttd_customer                 VARCHAR(255),
    tgl_blacklist                DATE,
    keterangan_blacklist         VARCHAR(255),
    created_at                   TIMESTAMP NULL DEFAULT NULL,
    updated_at                   TIMESTAMP NULL DEFAULT NULL,
    deleted_at                   TIMESTAMP NULL DEFAULT NULL
);

-- (Opsional) Set sequence agar mengikuti nilai AUTO_INCREMENT MySQL
-- ALTER SEQUENCE isp_erp.instalasi_id_instalasi_seq RESTART WITH 25083;

-- 2) hrd_request_barang_detail
CREATE TABLE IF NOT EXISTS isp_erp.hrd_request_barang_detail (
  id_hrd_request_barang_detail SERIAL PRIMARY KEY,
  id_hrd_request            INTEGER,
  id_acc_rekening           BIGINT,
  id_acc_rekening_hutang    BIGINT,
  nama_barang               VARCHAR(255),
  jumlah                    VARCHAR(255),
  harga_satuan              BIGINT,
  ppn                       BIGINT,
  keterangan                TEXT,
  status_barang             VARCHAR(255) DEFAULT 'Diterima',
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  deleted_at                TIMESTAMP
);

-- 3) inventaris
CREATE TABLE IF NOT EXISTS isp_erp.inventaris (
    id_inventaris BIGSERIAL PRIMARY KEY,
    id_sc_gudang BIGINT NOT NULL DEFAULT 1,
    id_perangkat BIGINT NOT NULL,
    nomor_seri TEXT,
    tgl_inventaris DATE,
    kondisi_inventaris VARCHAR(255) NOT NULL DEFAULT 'Baik',
    status_inventaris VARCHAR(255) NOT NULL DEFAULT 'Tersedia',
    jumlah_inventaris BIGINT DEFAULT 1,
    keterangan_inventaris TEXT,
    barcode VARCHAR(255),
    tgl_post_inventaris TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

-- identitas
CREATE TABLE IF NOT EXISTS isp_erp.identitas (
    id_identitas       SERIAL PRIMARY KEY,
    nama_perusahaan    VARCHAR(100),
    tag_line           VARCHAR(255),
    profile_perusahaan TEXT,
    nama_website       VARCHAR(255) NOT NULL,
    url_web            TEXT NOT NULL,
    email_web          VARCHAR(100) NOT NULL,
    alamat_web         VARCHAR(250) NOT NULL,
    no_telp_web        VARCHAR(50) NOT NULL,
    no_wa_web          VARCHAR(50),
    meta_deskripsi     TEXT NOT NULL,
    meta_keyword_web   TEXT NOT NULL,
    favicon_web        VARCHAR(50) NOT NULL,
    logo_web           TEXT NOT NULL,
    sampul_web         TEXT NOT NULL,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    deleted_at         TIMESTAMP
);


-- 5) inventory_peminjaman_barang (header)
CREATE TABLE IF NOT EXISTS isp_erp.inventory_peminjaman_barang (
  id_inventory_peminjaman_barang SERIAL PRIMARY KEY,
  id_users_warehouse  INTEGER,
  id_users_peminjam   INTEGER,
  keterangan          TEXT,
  tgl_peminjaman      DATE,
  approval_warehouse  VARCHAR(50),
  approval_warehouse_date TIMESTAMP,
  created_at          TIMESTAMP,
  updated_at          TIMESTAMP,
  deleted_at          TIMESTAMP
);

-- 6) inventory_peminjaman_detail_barang (detail)
CREATE TABLE IF NOT EXISTS isp_erp.inventory_peminjaman_detail_barang (
  id_inventory_peminjaman_detail_barang SERIAL PRIMARY KEY,
  id_inventory_peminjaman_barang INTEGER,
  id_acc_penyusutan             BIGINT,
  tgl_peminjaman                DATE,
  tgl_dikembalikan              DATE,
  status_barang                 VARCHAR(50),
  created_at                    TIMESTAMP,
  updated_at                    TIMESTAMP,
  deleted_at                    TIMESTAMP
);


-- Pastikan schema isp_erp ada
CREATE SCHEMA IF NOT EXISTS isp_erp;

-- Tabel invoice
CREATE TABLE IF NOT EXISTS isp_erp.invoice (
    id_invoice              SERIAL PRIMARY KEY,
    id_unit_instalasi       BIGINT NOT NULL DEFAULT 1,
    id_kasir                INTEGER,
    no_invoice              VARCHAR(255),
    no_tiket                BIGINT,
    id_instalasi            INTEGER NOT NULL,
    status_invoice          VARCHAR(25) NOT NULL DEFAULT 'Belum Dibayar',
    tgl_overdue             DATE,
    tgl_pembayaran          DATE,
    jam_pembayaran          TIME,
    tgl_invoice             DATE,
    tax                     BIGINT,
    tax_percentage          BIGINT,
    gross_amount            BIGINT,
    first_name              VARCHAR(255),
    last_name               VARCHAR(255),
    phone                   VARCHAR(255),
    email                   VARCHAR(255),
    item_details_id         VARCHAR(255),
    item_details_name       VARCHAR(255),
    item_details_price      VARCHAR(255),
    item_details_quantity   VARCHAR(255) DEFAULT '1',
    nilai_dibayar           BIGINT DEFAULT 0,
    belum_dibayar           BIGINT DEFAULT 0,
    custom_field1           TEXT,
    midtrans_token          TEXT,
    payment_type            VARCHAR(255),
    payment_send            VARCHAR(255),
    tgl_create_invoice      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    faktur_pajak            VARCHAR(255),
    file_pph23              VARCHAR(255),
    token_invoice           TEXT,
    status_broadcast        BIGINT NOT NULL DEFAULT 0,
    no_wo                   VARCHAR(255),
    approval_kasir          VARCHAR(50),
    created_at              TIMESTAMP NULL DEFAULT NULL,
    updated_at              TIMESTAMP NULL DEFAULT NULL,
    deleted_at              TIMESTAMP NULL DEFAULT NULL
);

-- Opsional: Set sequence agar mengikuti AUTO_INCREMENT MySQL
-- ALTER SEQUENCE isp_erp.invoice_id_invoice_seq RESTART WITH 222801;

-- 2) invoice_detail
CREATE TABLE IF NOT EXISTS isp_erp.invoice_detail (
  id_invoice_detail         BIGSERIAL PRIMARY KEY,
  id_unit_instalasi         BIGINT,
  id_invoice                BIGINT NOT NULL,
  id_produk                 VARCHAR(255),
  detail_nama_produk        VARCHAR(255),
  qty_detail                DOUBLE PRECISION,
  harga_detail              DOUBLE PRECISION,
  disc_detail               DOUBLE PRECISION,
  disc_plus_detail          DOUBLE PRECISION,
  sub_total_detail          DOUBLE PRECISION,
  pajak_detail              VARCHAR(255),
  persentase_pajak_detail   DOUBLE PRECISION,
  catatan_detail            TEXT,
  tgl_invoice_detail        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  deleted_at                TIMESTAMP
);

-- 3) invoice_payment
CREATE TABLE IF NOT EXISTS isp_erp.invoice_payment (
  id_invoice_payment  BIGSERIAL PRIMARY KEY,
  id_invoice          BIGINT,
  payment_date        DATE,
  payment_time        TIME,
  nominal_dibayar     BIGINT,
  sisa_tagihan        BIGINT,
  metode_pembayaran   VARCHAR(255),
  created_at          TIMESTAMP,
  updated_at          TIMESTAMP,
  deleted_at          TIMESTAMP
);

-- 4) invoice_payment_bulk
CREATE TABLE  IF NOT EXISTS isp_erp.invoice_payment_bulk (
    id_invoice_payment_bulk BIGSERIAL PRIMARY KEY,
    id_unit_instalasi BIGINT,
    total_customer INT,
    total_tagihan DOUBLE PRECISION,
    total_dibayarkan BIGINT,
    status_invoice_payment VARCHAR(255) NOT NULL,
    tgl_transfer_pembayaran TIMESTAMP NULL DEFAULT NULL,
    bukti_transfer VARCHAR(255),
    metode_transfer VARCHAR(25),
    notes_invoice_payment VARCHAR(255),
    no_bulk_payment VARCHAR(20),
    no_va VARCHAR(255),
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

-- 5) invoice_payment_dummy
CREATE TABLE IF NOT EXISTS isp_erp.invoice_payment_dummy (
  id_invoice_payment_dummy  BIGSERIAL PRIMARY KEY,
  id_invoice_payment_bulk   BIGINT,
  id_invoice                BIGINT,
  nilai_dibayarkan_pd       BIGINT,
  tgl_pembayaran_pd         TIMESTAMP,
  status_broadcast_dummy    VARCHAR(255),
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  deleted_at                TIMESTAMP
);


-- 1) invoice_payment_mt
CREATE TABLE IF NOT EXISTS isp_erp.invoice_payment_mt (
    id_invoice_payment_mt BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_invoice            INTEGER,
    payment_type          VARCHAR(255),
    transaction_id        VARCHAR(255),
    order_id              VARCHAR(255),
    gross_amount          VARCHAR(255),
    transaction_time      VARCHAR(255),
    transaction_status    VARCHAR(255),
    expiry_time           TIMESTAMP,
    bank                  VARCHAR(255),
    va_numbers            VARCHAR(255),
    biller_code           VARCHAR(255),
    acq_bank              VARCHAR(255),
    qris_url              TEXT,
    deeplink_redirect     TEXT,
    payment_code          VARCHAR(255),
    store                 VARCHAR(255),
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    deleted_at            TIMESTAMP
);

-- 2) invoice_plus
CREATE TABLE IF NOT EXISTS isp_erp.invoice_plus (
    id_invoice_plus       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_instalasi          BIGINT,
    kode_plus             VARCHAR(255),
    nama_produk_plus      VARCHAR(255),
    harga_produk_plus     BIGINT,
    qty_produk_plus       VARCHAR(255),
    status_tax_plus       VARCHAR(255),
    disc_plus             VARCHAR(255),
    total_plus            VARCHAR(255),
    tax_plus              VARCHAR(255),
    sub_total_produk_plus VARCHAR(255),
    catatan_produk_plus   TEXT,
    status_invoice_plus   VARCHAR(255) DEFAULT 'Belum Lunas',
    tgl_invoice_plus      TIMESTAMP,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    deleted_at            TIMESTAMP
);

-- 3) invoice_promo
CREATE TABLE IF NOT EXISTS isp_erp.invoice_promo (
    id_invoice_promo  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_instalasi      BIGINT,
    tgl_mulai_promo   DATE,
    tgl_selesai_promo DATE,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    deleted_at        TIMESTAMP
);

-- 4) jam_kantor
CREATE TABLE IF NOT EXISTS isp_erp.jam_kantor (
    id_jam_kantor   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_identitas    BIGINT,
    hari_kerja_jam  VARCHAR(25),
    jam_buka_kantor VARCHAR(25),
    jam_tutup_kantor VARCHAR(25),
    status_kerja    VARCHAR(50),
    nomor_urut      INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    deleted_at      TIMESTAMP
);

-- 5) jawab
CREATE TABLE IF NOT EXISTS isp_erp.jawab (
    id_jawab     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_tiket     BIGINT,
    id_customer  BIGINT,
    id_users     BIGINT,
    jawaban      TEXT,
    tgl_jawaban  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    deleted_at   TIMESTAMP
);

-- Table: jc_gaji_pokok
CREATE TABLE IF NOT EXISTS isp_erp.jc_gaji_pokok (
    id_jc_gaji_pokok BIGSERIAL PRIMARY KEY,
    id_users BIGINT NULL,
    grade_karyawan VARCHAR(255) NULL,
    level_karyawan VARCHAR(20) NULL,
    gaji_pokok BIGINT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Table: jc_bonus_absen
CREATE TABLE IF NOT EXISTS isp_erp.jc_bonus_absen (
    id_jc_bonus_absen BIGSERIAL PRIMARY KEY,
    id_users BIGINT,
    tgl_gajian_absen DATE,
    nilai_bonus_absen BIGINT,
    tgl_bonus_absen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

-- Table: jc_angsuran_kasbon
CREATE TABLE IF NOT EXISTS isp_erp.jc_angsuran_kasbon (
    id_jc_angsuran_kasbon BIGSERIAL PRIMARY KEY,
    id_jc_gaji_bulanan BIGINT,
    id_jc_kasbon BIGINT,
    id_users BIGINT,
    ket_angsuran_kasbon VARCHAR(255),
    nominal_angsuran BIGINT,
    tgl_pembayaran DATE,
    sisa_kasbon BIGINT,
    tgl_angsuran_kasbon TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

-- Table: jc_bonus_thr
CREATE TABLE IF NOT EXISTS isp_erp.jc_bonus_thr (
    id_jc_bonus_thr BIGSERIAL PRIMARY KEY,
    id_users INT NULL,
    tahun_thr INT NULL,
    nilai_thr INT NULL,
    ket_bonus_thr VARCHAR(255) NULL,
    status_thr INT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Table: jc_gaji_bulanan
CREATE TABLE IF NOT EXISTS isp_erp.jc_gaji_bulanan (
    id_jc_gaji_bulanan BIGSERIAL PRIMARY KEY,
    bulan_gajian BIGINT,
    tahun_gajian BIGINT,
    id_users BIGINT,
    nominal_gaji_pokok BIGINT,
    nominal_tunjangan BIGINT,
    nominal_lembur BIGINT,
    nominal_komisi_teknisi BIGINT,
    nominal_komisi_marketing BIGINT,
    nominal_bonus_absensi BIGINT,
    nominal_potongan_bulanan BIGINT,
    nominal_potongan_kasbon BIGINT,
    nominal_gaji_bersih BIGINT,
    status_gaji VARCHAR(255),
    keterangan_gaji_bulanan VARCHAR(255),
    tgl_gaji_bulanan TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

-- Table: jc_gaji_bulanan_detail
CREATE TABLE IF NOT EXISTS isp_erp.jc_gaji_bulanan_detail (
    id_jc_gaji_bulanan_detail BIGSERIAL PRIMARY KEY,
    id_jc_gaji_bulanan BIGINT NULL,
    kategori_detail_gaji VARCHAR(255) NULL,
    keterangan_detail_gaji VARCHAR(255) NULL,
    nilai_detail_gaji BIGINT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_lembur (
    id_jc_lembur SERIAL PRIMARY KEY,
    id_creator BIGINT,
    id_tiket BIGINT,
    tgl_lembur DATE,
    id_divisi BIGINT,
    lokasi_lembur VARCHAR(255),
    lembur_pada VARCHAR(255),
    uraian_lembur VARCHAR(99999),
    id_supervisor BIGINT,
    acc_supervisor VARCHAR(255),
    id_direktur BIGINT,
    acc_direktur VARCHAR(255),
    acc_manager VARCHAR(50),
    acc_hrd VARCHAR(50),
    acc_direktur_date TIMESTAMP NULL DEFAULT NULL,
    acc_manager_date TIMESTAMP NULL DEFAULT NULL,
    acc_hrd_date TIMESTAMP NULL DEFAULT NULL,
    id_hrd VARCHAR(50),
    id_manager BIGINT,
    tgl_jc_lembur TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_jabatan (
    id_jc_jabatan SERIAL PRIMARY KEY,
    status_jabatan INT NOT NULL DEFAULT 1,
    nama_jabatan VARCHAR(255),
    tuposi_jabatan VARCHAR(255),
    keterangan_jabatan VARCHAR(255),
    tgl_jabatan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_kasbon (
    id_jc_kasbon BIGSERIAL PRIMARY KEY,
    id_users BIGINT,
    id_users_finance INTEGER,
    keterangan_kasbon VARCHAR(255),
    jumlah_kasbon BIGINT,
    status_kasbon VARCHAR(255),
    lama_angsuran BIGINT,
    nominal_angsuran BIGINT,
    tgl_mulai_angsuran DATE,
    acc_finance VARCHAR(255),
    id_users_direktur BIGINT,
    acc_direktur VARCHAR(255),
    tgl_kasbon DATE,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_jenis_tunjangan (
    id_jc_jenis_tunjangan BIGSERIAL PRIMARY KEY,
    nama_tunjangan VARCHAR(255),
    keterangan_tunjangan VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_lembur_detail (
    id_jc_detail_lembur BIGSERIAL PRIMARY KEY,
    id_jc_lembur BIGINT,
    id_users BIGINT,
    jam_mulai_lembur TIMESTAMP,
    jam_selesai_lembur TIMESTAMP,
    waktu_lembur VARCHAR(255),
    upah_lembur VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_point_sales (
    id_jc_point_sales BIGSERIAL PRIMARY KEY,
    pos_dari_tgl DATE,
    pos_sampai_tgl DATE,
    status_pencapaian VARCHAR(255) DEFAULT 'Pending',
    omzet_bandwith BIGINT,
    omzet_instalasi BIGINT,
    omzet_penambahan_kabel BIGINT,
    omzet_upgrade BIGINT,
    omzet_kotor BIGINT,
    presentase_bonus DOUBLE PRECISION,
    potongan_new_customer BIGINT,
    potongan_upgrade BIGINT,
    netto_new_customer BIGINT,
    netto_upgrade BIGINT,
    grandtotal_netto BIGINT,
    komisi_marketing BIGINT,
    keterangan_pos VARCHAR(255),
    tgl_point_sales TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);


CREATE TABLE IF NOT EXISTS isp_erp.jc_point_sales_detail (
    id_jc_point_sales_detail BIGSERIAL PRIMARY KEY,
    id_jc_point_sales BIGINT,
    id_instalasi BIGINT,
    id_invoice BIGINT,
    id_upgrade_paket BIGINT,
    id_produk BIGINT,
    jenis_komisi VARCHAR(255),
    biaya_instalasi BIGINT,
    harga_produk BIGINT,
    potongan_biaya BIGINT,
    presentase FLOAT,
    total_komisi BIGINT,
    status_point VARCHAR(255),
    keterangan_point_detail VARCHAR(255),
    tgl_point_sales_detail TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Tabel: jc_potongan_gaji
CREATE TABLE IF NOT EXISTS isp_erp.jc_potongan_gaji (
    id_jc_potongan_gaji BIGSERIAL PRIMARY KEY,
    id_users BIGINT NULL,
    jenis_potongan VARCHAR(255) NULL,
    nilai_potongan BIGINT NULL,
    satuan_potongan VARCHAR(255) NULL,
    status_potongan VARCHAR(255) NULL,
    kategori_gaji VARCHAR(255) NULL,
    keterangan_potongan VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.jc_point_teknisi (
    id_jc_point_teknisi BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_tiket            BIGINT,
    point_teknisi       DOUBLE PRECISION,
    keterangan_point    VARCHAR(255),
    tgl_point_teknisi   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at          TIMESTAMP NULL,
    updated_at          TIMESTAMP NULL,
    deleted_at          TIMESTAMP NULL
);

-- Tabel: jc_potongan_gaji_riwayat
CREATE TABLE IF NOT EXISTS isp_erp.jc_potongan_gaji_riwayat (
    id_jc_potongan_gaji_riwayat BIGSERIAL PRIMARY KEY,
    id_jc_gaji_bulanan BIGINT NULL,
    id_jc_potongan_gaji BIGINT NULL,
    riwayat_nilai_potongan VARCHAR(20) NULL,
    riwayat_nominal_potongan BIGINT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Tabel: jc_tunjangan_karyawan
CREATE TABLE IF NOT EXISTS isp_erp.jc_tunjangan_karyawan (
    id_jc_tunjangan_karyawan BIGSERIAL PRIMARY KEY,
    id_users BIGINT NULL,
    id_jc_jenis_tunjangan BIGINT NULL,
    nilai_tunjangan_karyawan BIGINT NULL,
    status_tunjangan_karyawan VARCHAR(255) NULL,
    keterangan_tunjangan_karyawan VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- Tabel: jobs
CREATE TABLE IF NOT EXISTS isp_erp.jobs (
    id BIGSERIAL PRIMARY KEY,
    queue VARCHAR(255) NOT NULL,
    payload TEXT NOT NULL,
    attempts SMALLINT NOT NULL,
    reserved_at INT NULL,
    available_at INT NOT NULL,
    created_at INT NOT NULL
);

-- Tabel: job_batches
CREATE TABLE IF NOT EXISTS isp_erp.job_batches (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    total_jobs INT NOT NULL,
    pending_jobs INT NOT NULL,
    failed_jobs INT NOT NULL,
    failed_job_ids TEXT NOT NULL,
    options TEXT NULL,
    cancelled_at INT NULL,
    created_at INT NOT NULL,
    finished_at INT NULL
);


-- 1) kecamatan
CREATE TABLE IF NOT EXISTS isp_erp.kecamatan (
  id_kecamatan      CHAR(7)    PRIMARY KEY,
  id_kabupaten      CHAR(4)    NOT NULL,
  nama_kecamatan    VARCHAR(255) NOT NULL,
  status_kecamatan  VARCHAR(255) NOT NULL DEFAULT 'Aktif',
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  deleted_at        TIMESTAMP NULL
);

-- 2) kabupaten
CREATE TABLE IF NOT EXISTS isp_erp.kabupaten (
  id_kabupaten      CHAR(4)    PRIMARY KEY,
  id_provinsi       CHAR(2)    NOT NULL,
  nama_kabupaten    VARCHAR(255) NOT NULL,
  status_kabupaten  VARCHAR(255) NOT NULL DEFAULT 'Aktif',
  created_at        TIMESTAMP NULL,
  updated_at        TIMESTAMP NULL,
  deleted_at        TIMESTAMP NULL
);

-- 3) kategori
CREATE TABLE IF NOT EXISTS isp_erp.kategori (
  id_kategori           BIGSERIAL PRIMARY KEY,
  nama_kategori         VARCHAR(255) NOT NULL,
  keterangan_kategori   VARCHAR(255),
  tgl_kategori          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.keluhan (
    id_keluhan         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_customer        BIGINT,
    pic_pelapor        VARCHAR(255),
    telepon_pelapor    VARCHAR(255),
    isi_keluhan        TEXT,
    hasil_pengecekan   TEXT,
    status_keluhan     VARCHAR(255) DEFAULT 'Pending',
    keterangan_keluhan TEXT,
    shift              BIGINT,
    id_tiket           VARCHAR(255),
    id_teknisi         BIGINT NOT NULL,
    id_users           BIGINT NOT NULL,
    tgl_keluhan        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    pic_keluhan1       TEXT,
    pic_keluhan2       TEXT,
    pic_keluhan3       TEXT,
    created_at         TIMESTAMP NULL,
    updated_at         TIMESTAMP NULL,
    deleted_at         TIMESTAMP NULL
);

-- 5) komentar_berita
CREATE TABLE IF NOT EXISTS isp_erp.komentar_berita (
  id_komentar_berita    BIGSERIAL PRIMARY KEY,
  id_berita             BIGINT,
  id_pengunjung         BIGINT,
  isi_komentar          VARCHAR(255),
  rating_komentar       VARCHAR(255),
  tgl_komentar_berita   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

-- 6) kontrak
CREATE TABLE IF NOT EXISTS isp_erp.kontrak (
  id_kontrak            BIGSERIAL PRIMARY KEY,
  id_peminjaman         INT,
  id_customer           BIGINT,
  id_instalasi          BIGINT,
  nomor_kontrak         VARCHAR(255),
  type_kontrak          VARCHAR(255),
  jenis_kontrak         VARCHAR(255),
  keterangan_kontrak    TEXT,
  tgl_mulai_kontrak     DATE,
  tgl_akhir_kontrak     DATE,
  file_kontrak          VARCHAR(255),
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

-- 7) log_tiket
CREATE TABLE IF NOT EXISTS isp_erp.log_tiket (
  id_log_tiket          SERIAL PRIMARY KEY,
  id_tiket              INT,
  id_users              INT,
  divisi_tiket          VARCHAR(100),
  status_tiket          VARCHAR(100),
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

-- 8) marketing_target
CREATE TABLE IF NOT EXISTS isp_erp.marketing_target (
  id_marketing_target   BIGSERIAL PRIMARY KEY,
  id_users              BIGINT,
  jenis_target          VARCHAR(255),
  nilai_target          BIGINT,
  wilayah_target        VARCHAR(255) DEFAULT 'Pusat',
  notes_target          VARCHAR(255),
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

-- 9) mandiri_dummy
CREATE TABLE IF NOT EXISTS isp_erp.mandiri_dummy (
  id_mandiri_dummy      SERIAL PRIMARY KEY,
  no_va                 VARCHAR(255),
  no_invoice            VARCHAR(255)
);

-- 10) macro
CREATE TABLE IF NOT EXISTS isp_erp.macro (
  id_macro              BIGSERIAL PRIMARY KEY,
  kategori_macro        VARCHAR(255),
  judul_macro           VARCHAR(255),
  isi_macro             TEXT,
  tgl_macro             TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

-- 11) lokasi_pop
CREATE TABLE IF NOT EXISTS isp_erp.lokasi_pop (
  id_lokasi_pop         BIGSERIAL PRIMARY KEY,
  nama_lokasi           TEXT,
  alamat_lokasi         TEXT,
  gps_lokasi            TEXT,
  telepon_lokasi        TEXT,
  status_lokasi         TEXT,
  gambar_lokasi_pop     TEXT,
  tgl_lokasi_pop        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.midtrans_account (
    id_midtrans_account          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    midtrans_id                  VARCHAR(255),
    pic_name                     VARCHAR(255),
    email_address                VARCHAR(255),
    phone_number                 VARCHAR(255),
    email_approver_disbursement  VARCHAR(255),
    phone_approver_disbursement  VARCHAR(255),
    payment_gateway_feature      VARCHAR(255),
    disbursement_feature         VARCHAR(255),
    merchant_name                VARCHAR(255),
    official_merch_name          VARCHAR(255),
    director_name                VARCHAR(255),
    director_phone               VARCHAR(255),
    npwp_name                    VARCHAR(255),
    merchant_email               VARCHAR(255),
    merchant_address             VARCHAR(255),
    merchant_status              VARCHAR(255),
    account_number_payout        VARCHAR(255),
    bank_name_account            VARCHAR(255),
    account_name_payout          VARCHAR(255),
    va_disbursement              VARCHAR(255),
    merchant_notes               VARCHAR(255),
    created_at                   TIMESTAMP NULL,
    updated_at                   TIMESTAMP NULL,
    deleted_at                   TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.midtrans_rekap (
    id_midtrans_rekap     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_midtrans_account   BIGINT,
    "Date_Created"          TIMESTAMP NULL,
    "Order_ID"              VARCHAR(255),
    "Transaction_Type"      VARCHAR(255),
    "Channel"               VARCHAR(255),
    "Status"                VARCHAR(255),
    "Reference_Id"          VARCHAR(255),
    "Amount"                BIGINT,
    "Total_Fee"             BIGINT,
    "Notes"                 VARCHAR(255),
    created_at            TIMESTAMP NULL,
    updated_at            TIMESTAMP NULL,
    deleted_at            TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.midtrans_transaction (
    id_midtrans_transaction BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_midtrans_account     BIGINT,
    id_midtrans_rekap       BIGINT,
    mid_service             VARCHAR(255),
    mid_categories          VARCHAR(255),
    mid_trx_date_created    TIMESTAMP NULL,
    mid_order_id            VARCHAR(255),
    mid_trx_notes           VARCHAR(255),
    mid_transaction_type    VARCHAR(255),
    mid_amount              DOUBLE PRECISION,
    mid_balance             DOUBLE PRECISION,
    created_at              TIMESTAMP NULL,
    updated_at              TIMESTAMP NULL,
    deleted_at              TIMESTAMP NULL
);

CREATE TABLE isp_erp.mutasi_bni (
    id_mutasi_bni     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_bni_account    BIGINT,
    post_date         TIMESTAMP NULL,
    value_date        TIMESTAMP NULL,
    branch            VARCHAR(255),
    journal           VARCHAR(255),
    description       VARCHAR(255),
    jenis_transaksi   VARCHAR(255),
    nominal           DOUBLE PRECISION,
    verified          VARCHAR(50) DEFAULT 'Pending',
    id_acc_transaksi  BIGINT,
    created_at        TIMESTAMP NULL,
    updated_at        TIMESTAMP NULL,
    deleted_at        TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.migrations (
  id        SERIAL PRIMARY KEY,
  migration VARCHAR(255) NULL,
  batch     INTEGER NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.mikrotik (
    id_mikrotik SERIAL PRIMARY KEY,
    id_unit_instalasi BIGINT NOT NULL DEFAULT 1,
    nama_mikrotik VARCHAR(255),
    model_mikrotik VARCHAR(255),
    nama_pj VARCHAR(255),
    lokasi_mikrotik VARCHAR(255),
    ip_local VARCHAR(255),
    ip_public VARCHAR(255),
    api_port INTEGER,
    www_port INTEGER,
    winbox_port INTEGER,
    ether_mms VARCHAR(255),
    username VARCHAR(255),
    password VARCHAR(255),
    komentar VARCHAR(255),
    tgl_mikrotik TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.mm_fu_blokir (
    id_mm_fu_blokir BIGSERIAL PRIMARY KEY,
    id_instalasi BIGINT,
    id_users INTEGER,
    id_invoice BIGINT,
    tgl_fu1 DATE,
    hasil_fu1 VARCHAR(255),
    tgl_fu2 DATE,
    hasil_fu2 VARCHAR(255),
    tgl_fu3 DATE,
    hasil_fu3 VARCHAR(255),
    tgl_fu4 DATE,
    hasil_fu4 VARCHAR(255),
    tgl_fu5 DATE,
    hasil_fu5 VARCHAR(255),
    keputusan_fu VARCHAR(255),
    keterangan_fu_akhir VARCHAR(255),
    tgl_tarik_alat DATE,
    tgl_mm_fu_blokir DATE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

-- 5) mutasi_bni_balance

CREATE TABLE IF NOT EXISTS isp_erp.mutasi_bni_balance (
  id_mutasi_bni_balance BIGSERIAL PRIMARY KEY,
  id_mutasi_bni         BIGINT NULL,
  date_mutasi           TIMESTAMP NULL,
  balance               DOUBLE PRECISION NULL,
  created_at            TIMESTAMP NULL,
  updated_at            TIMESTAMP NULL,
  deleted_at            TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.mutasi_bni_dummy (
  id_mutasi_bni_dummy BIGSERIAL PRIMARY KEY,
  post_date           VARCHAR(255) NULL,
  value_date          VARCHAR(255) NULL,
  branch              VARCHAR(255) NULL,
  journal             VARCHAR(255) NULL,
  description         VARCHAR(255) NULL,
  debit               DOUBLE PRECISION NULL,
  credit              DOUBLE PRECISION NULL,
  created_at          TIMESTAMP NULL,
  updated_at          TIMESTAMP NULL,
  deleted_at          TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.mutasi_mandiri (
    id_mutasi_mandiri BIGSERIAL PRIMARY KEY,
    account_no VARCHAR(255),
    date TIMESTAMP NULL,
    val_date TIMESTAMP NULL,
    transaction_code VARCHAR(255),
    description1 VARCHAR(9999),
    description2 VARCHAR(9999),
    refrence_no VARCHAR(9999),
    jenis_transaksi VARCHAR(9999),
    nominal DOUBLE PRECISION,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.mutasi_mandiri_balance (
  id_mutasi_mandiri_balance BIGSERIAL PRIMARY KEY,
  id_mutasi_mandiri         BIGINT NULL,
  date_mutasi               TIMESTAMP NULL,
  balance                   DOUBLE PRECISION NULL,
  created_at                TIMESTAMP NULL,
  updated_at                TIMESTAMP NULL,
  deleted_at                TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.odp_coverage (
    id_odp_coverage BIGSERIAL PRIMARY KEY,
    lat_long VARCHAR(255),
    id_odp BIGINT,
    jarak DOUBLE PRECISION,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.odp (
    id_odp BIGSERIAL PRIMARY KEY,
    id_odp_jalur BIGINT,
    kategori_odp VARCHAR(255),
    vendor_odp VARCHAR(255),
    splitter_odp VARCHAR(255),
    nama_odp VARCHAR(255),
    gps_odp VARCHAR(255),
    qty_port INT,
    tgl_odp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.mutasi_mandiri_dummy (
    id_mutasi_mandiri_dummy BIGSERIAL PRIMARY KEY,
    account_no VARCHAR(255),
    date VARCHAR(255),
    val_date VARCHAR(255),
    transaction_code VARCHAR(255),
    description1 VARCHAR(255),
    description2 VARCHAR(255),
    refrence_no VARCHAR(255),
    debit DOUBLE PRECISION,
    credit DOUBLE PRECISION,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS isp_erp.odp_terdekat (
    id_odp_terdekat BIGSERIAL PRIMARY KEY,
    id_posisi BIGINT,
    id_customer BIGINT,
    id_odp BIGINT,
    jarak DOUBLE PRECISION,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.odp_jalur (
    id_odp_jalur SERIAL PRIMARY KEY,
    id_unit_instalasi BIGINT,
    wilayah_odp VARCHAR(255),
    jalur_odp VARCHAR(255),
    pon_odp VARCHAR(255),
    tgl_jalur_odp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.password_resets (
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.password_reset_tokens (
    email VARCHAR(255) PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP
);


-- payout_notif
CREATE TABLE IF NOT EXISTS isp_erp.payout_notif (
  id_payout_notif BIGSERIAL PRIMARY KEY,
  reference_no VARCHAR(255),
  amount VARCHAR(255),
  status VARCHAR(255),
  date_update VARCHAR(255),
  signature_key TEXT,
  signature_key_local TEXT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP
);

-- pekerjaan_anggota
CREATE TABLE IF NOT EXISTS isp_erp.pekerjaan_anggota (
  id_pekerjaan_anggota SERIAL PRIMARY KEY,
  id_tiket INTEGER,
  id_anggota INTEGER,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.peminjaman (
    id_peminjaman BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_tiket INTEGER,
    id_customer BIGINT,
    id_odp_jalur BIGINT,
    id_pop BIGINT,
    id_pic BIGINT,
    status_perangkat VARCHAR(25) NOT NULL DEFAULT 'Dipinjamkan',
    jenis_ttb VARCHAR(25) NOT NULL DEFAULT 'Instalasi',
    nomor_ttb VARCHAR(255),
    catatan TEXT,
    tgl_dipinjamkan DATE,
    tgl_dikembalikan DATE,
    tgl_peminjaman TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    id_sc_gudang BIGINT NOT NULL DEFAULT 1
);

-- pengunjung
CREATE TABLE IF NOT EXISTS isp_erp.pengunjung (
  id_pengunjung BIGSERIAL PRIMARY KEY,
  nama_pengunjung VARCHAR(255),
  email_pengunjung VARCHAR(255),
  telepon_pengunjung VARCHAR(255),
  password VARCHAR(255),
  tgl_pengunjung TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  remember_token VARCHAR(255),
  firestore_id VARCHAR(255),
  fcm_token VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- perangkat
CREATE TABLE IF NOT EXISTS isp_erp.perangkat (
  id_perangkat BIGSERIAL PRIMARY KEY,
  id_users BIGINT,
  id_perangkat_lokasi_inventory INTEGER,
  kode_perangkat VARCHAR(255),
  nama_perangkat VARCHAR(255),
  merek_perangkat VARCHAR(255),
  satuan_perangkat VARCHAR(25) DEFAULT 'PCS',
  max_lot BIGINT,
  min_qty BIGINT DEFAULT 0,
  status VARCHAR(25) DEFAULT 'non-prioritas',
  level_prioritas VARCHAR(255),
  deskripsi_perangkat TEXT,
  foto_perangkat TEXT,
  tgl_perangkat TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- perangkat_kontrak
CREATE TABLE IF NOT EXISTS isp_erp.perangkat_kontrak (
  id_perangkat_kontrak SERIAL PRIMARY KEY,
  id_kontrak INTEGER,
  nama_perangkat VARCHAR(255),
  kode_perangkat VARCHAR(255),
  jumlah_perangkat BIGINT,
  deskripsi_perangkat VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
  -- Catatan: MySQL "ON UPDATE current_timestamp()" tidak tersedia langsung di PG
);

-- perangkat_lokasi_inventory
CREATE TABLE IF NOT EXISTS isp_erp.perangkat_lokasi_inventory (
  id_perangkat_lokasi_inventory SERIAL PRIMARY KEY,
  lokasi VARCHAR(255),
  keterangan TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- pekerjaan
CREATE TABLE IF NOT EXISTS isp_erp.pekerjaan (
  id_pekerjaan BIGSERIAL PRIMARY KEY,
  id_users BIGINT NOT NULL,
  id_tiket BIGINT,
  tgl_mulai DATE,
  tgl_selesai DATE,
  status_pekerjaan VARCHAR(25),
  laporan TEXT,
  lampiran_laporan1 TEXT,
  lampiran_laporan2 TEXT,
  lampiran_laporan3 TEXT,
  lampiran_laporan4 TEXT,
  lampiran_laporan5 TEXT,
  keterangan_lampiran1 VARCHAR(255),
  keterangan_lampiran2 VARCHAR(255),
  keterangan_lampiran3 VARCHAR(255),
  keterangan_lampiran4 VARCHAR(255),
  keterangan_lampiran5 VARCHAR(255),
  tgl_pekerjaan TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tgl_pekerjaan_overdue TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- pemberitahuan
CREATE TABLE IF NOT EXISTS isp_erp.pemberitahuan (
  id_pemberitahuan BIGSERIAL PRIMARY KEY,
  judul_pemberitahuan VARCHAR(255),
  isi_pemberitahuan VARCHAR(255),
  gambar_pemberitahuan VARCHAR(255),
  divisi_pemberitahuan VARCHAR(255),
  tgl_pemberitahuan TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- perangkat_log_inventory
CREATE TABLE IF NOT EXISTS isp_erp.perangkat_log_inventory (
  id_perangkat_log_inventory SERIAL PRIMARY KEY,
  id_users INTEGER,
  id_inventaris INTEGER,
  tanggal_log DATE,
  jumlah_inventaris_log INTEGER,
  status_log VARCHAR(100),
  keterangan_log TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- perangkat_pop
CREATE TABLE IF NOT EXISTS isp_erp.perangkat_pop (
  id_perangkat_pop BIGSERIAL PRIMARY KEY,
  id_pop BIGINT NOT NULL,
  id_users BIGINT NOT NULL,
  keterangan_perangkat_pop TEXT,
  jenis_ttb VARCHAR(255),
  tgl_pemasangan DATE,
  tgl_perangkat_pop TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- perawatan_pop
CREATE TABLE IF NOT EXISTS isp_erp.perawatan_pop (
  id_perawatan_pop BIGSERIAL PRIMARY KEY,
  id_pop BIGINT,
  id_users BIGINT,
  masalah_pop TEXT,
  laporan_perawatan TEXT,
  tgl_perawatan DATE,
  tgl_pelaksanaan DATE,
  tgl_perawatan_pop TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- personal_access_tokens
CREATE TABLE IF NOT EXISTS isp_erp.personal_access_tokens (
  id BIGSERIAL PRIMARY KEY,
  tokenable_type VARCHAR(255) NOT NULL,
  tokenable_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  abilities TEXT,
  last_used_at TIMESTAMP,
  expires_at TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- photo
CREATE TABLE IF NOT EXISTS isp_erp.photo (
  id_photo BIGSERIAL PRIMARY KEY,
  id_album BIGINT NOT NULL,
  id_users BIGINT NOT NULL DEFAULT 3,
  judul_photo TEXT,
  judul_seo TEXT,
  keterangan TEXT,
  tgl_posting TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gambar VARCHAR(255),
  aktif SMALLINT NOT NULL DEFAULT 1,
  dibaca BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- pop
CREATE TABLE IF NOT EXISTS isp_erp.pop (
  id_pop SERIAL PRIMARY KEY,
  nama_pop VARCHAR(255),
  pic_pop VARCHAR(255),
  pic_telepon VARCHAR(255),
  titik_gps TEXT,
  tinggi_tower VARCHAR(255),
  jenis_tower VARCHAR(255),
  status_kepemilikan VARCHAR(255),
  daya_arus_listrik VARCHAR(255),
  foto_pop TEXT,
  alamat_tower TEXT,
  keterangan_pop TEXT,
  tgl_pop TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- posisi
CREATE TABLE IF NOT EXISTS isp_erp.posisi (
  id_posisi BIGSERIAL PRIMARY KEY,
  id_customer BIGINT,
  latitude_posisi VARCHAR(255),
  longitude_posisi VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- pppoe_active
CREATE TABLE IF NOT EXISTS isp_erp.pppoe_active (
  id_pppoe_active BIGSERIAL PRIMARY KEY,
  id_mikrotik BIGINT,
  id_instalasi BIGINT,
  id_active VARCHAR(255),
  id_pppoe VARCHAR(255),
  name_active VARCHAR(255),
  address_active VARCHAR(255),
  uptime VARCHAR(255),
  id_address_list VARCHAR(255),
  status_firewall VARCHAR(255),
  disabled VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- pppoe_dummy
CREATE TABLE IF NOT EXISTS isp_erp.pppoe_dummy (
  id_pppoe_dummy BIGSERIAL PRIMARY KEY,
  id_unit_instalasi BIGINT,
  id_mikrotik BIGINT,
  id_pppoe VARCHAR(255),
  id_customer BIGINT,
  id_instalasi BIGINT,
  user_pppoe VARCHAR(255),
  local_address VARCHAR(255),
  password_pppoe VARCHAR(255),
  service_pppoe VARCHAR(255),
  profile_pppoe VARCHAR(255),
  remote_address VARCHAR(255),
  disabled VARCHAR(255),
  status_firewall VARCHAR(255) DEFAULT 'Open',
  id_active VARCHAR(255),
  uptime VARCHAR(255),
  tgl_pppoe_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- produk
CREATE TABLE IF NOT EXISTS isp_erp.produk (
  id_produk BIGSERIAL PRIMARY KEY,
  id_unit_instalasi INTEGER NOT NULL DEFAULT 1,
  id_provinsi BIGINT,
  id_kabupaten BIGINT,
  id_acc_rekening BIGINT,
  kategori_produk VARCHAR(255),
  nama_produk VARCHAR(255),
  kapasitas BIGINT,
  harga_produk BIGINT,
  biaya_metroe BIGINT DEFAULT 0,
  sewa_lainnya BIGINT DEFAULT 0,
  jenis_pembayaran VARCHAR(255),
  isi_produk TEXT,
  gambar_produk TEXT,
  status_produk VARCHAR(255),
  tgl_produk TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- promo
CREATE TABLE IF NOT EXISTS isp_erp.promo (
  id_promo SERIAL PRIMARY KEY,
  nama VARCHAR(50),
  phone VARCHAR(50),
  produk VARCHAR(50),
  tgl_input TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- provinsi
CREATE TABLE IF NOT EXISTS isp_erp.provinsi (
  id_provinsi CHAR(2) PRIMARY KEY,
  nama_provinsi VARCHAR(255) NOT NULL,
  status_provinsi VARCHAR(255) NOT NULL DEFAULT 'Aktif',
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);


-- rekap_bni
CREATE TABLE IF NOT EXISTS isp_erp.rekap_bni (
  id_rekap_bni BIGSERIAL PRIMARY KEY,
  trx_id VARCHAR(255),
  virtual_account VARCHAR(255),
  customer_name VARCHAR(255),
  trx_amount VARCHAR(255),
  payment_amount VARCHAR(255),
  cumulative_payment_amount VARCHAR(255),
  payment_ntb VARCHAR(255),
  datetime_payment VARCHAR(255),
  datetime_payment_iso8601 VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- rekap_mandiri
CREATE TABLE IF NOT EXISTS isp_erp.rekap_mandiri (
  id_rekap_mandiri BIGSERIAL PRIMARY KEY,
  date_rekap DATE,
  no_virtual_rekap VARCHAR(255),
  nama_rekap VARCHAR(255),
  no_id_rekap VARCHAR(255),
  tagihan_rekap BIGINT,
  keterangan_rekap VARCHAR(255),
  amount_rekap BIGINT,
  channel_rekap VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- rekap_mandiri_report
CREATE TABLE IF NOT EXISTS isp_erp.rekap_mandiri_report (
  id_rekap_mandiri_report BIGSERIAL PRIMARY KEY,
  date_rekap TIMESTAMP,
  no_virtual_rekap VARCHAR(255),
  nama_rekap VARCHAR(255),
  no_id_rekap VARCHAR(255),
  tagihan_rekap BIGINT,
  keterangan_rekap VARCHAR(255),
  amount_rekap BIGINT,
  channel_rekap VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.rekap_midtrans (
    id_rekap_midtrans BIGSERIAL PRIMARY KEY,
    "Withdrawal_ID" VARCHAR(255),
    "Payment_Type" VARCHAR(255),
    "Paid_Date" TIMESTAMP NULL,
    "Requested_Created_Date" TIMESTAMP NULL,
    "Transaction_Time" TIMESTAMP NULL,
    "Settlement_Time" TIMESTAMP NULL,
    "Invoice_ID" VARCHAR(255),
    "Customer_Email" VARCHAR(255),
    "Credit_Card_Number_Mask" VARCHAR(255),
    "Refund_Amount" VARCHAR(255),
    "Amount" BIGINT,
    "3D_Secure" VARCHAR(255),
    "Card_Association" VARCHAR(255),
    "Acquiring_Bank" VARCHAR(255),
    "Bank_On_Us_Off_Us" VARCHAR(255),
    "Transaction_Fee" BIGINT,
    "Merchant_Has" BIGINT,
    "Refund" VARCHAR(255),
    "Payment_Options" VARCHAR(255),
    "Coins_Share" VARCHAR(255),
    "Transaction_ID" VARCHAR(255),
    "QRIS_Reference_ID" VARCHAR(255),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE isp_erp.report_history (
    id_report_history   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_unit_instalasi   BIGINT,
    id_users            BIGINT,
    menu_report         VARCHAR(255),
    tipe_report         VARCHAR(10),
    file_report         VARCHAR(255),
    start_report        DATE,
    end_report          DATE,
    created_at          TIMESTAMP NULL,
    updated_at          TIMESTAMP NULL,
    deleted_at          TIMESTAMP NULL,
    tgl_mulai_data      DATE,
    tgl_akhir_data      DATE,
    unit_instalasi_data VARCHAR(255),
    status              VARCHAR(255) NOT NULL DEFAULT 'Selesai'
);

-- report_komisi_sales
CREATE TABLE IF NOT EXISTS isp_erp.report_komisi_sales (
  id_report_komisi_sales BIGSERIAL PRIMARY KEY,
  tgl_periode_start DATE,
  tgl_periode_end DATE,
  id_marketing DOUBLE PRECISION,
  target_omzet DOUBLE PRECISION,
  omzet_bandwith DOUBLE PRECISION,
  omzet_instalasi DOUBLE PRECISION,
  omzet_business_solution DOUBLE PRECISION,
  omzet_penambahan_kabel DOUBLE PRECISION,
  omzet_marketing_freelance DOUBLE PRECISION,
  grandtotal_omzet_kotor DOUBLE PRECISION,
  persentase_bonus DOUBLE PRECISION,
  potongan_new_customer DOUBLE PRECISION,
  potongan_business_solution DOUBLE PRECISION,
  potongan_blokir_uninstall DOUBLE PRECISION,
  netto_omzet DOUBLE PRECISION,
  total_komisi_penjualan DOUBLE PRECISION,
  persentase_pencapaian_target DOUBLE PRECISION,
  omzet_upgrade DOUBLE PRECISION,
  potongan_upgrade DOUBLE PRECISION,
  netto_upgrade DOUBLE PRECISION,
  persentase_komisi_upgrade DOUBLE PRECISION,
  total_komisi_upgrade DOUBLE PRECISION,
  omzet_berlangganan_more1years DOUBLE PRECISION,
  persentase_berlangganan_more1years DOUBLE PRECISION,
  komisi_berlangganan_more1years DOUBLE PRECISION,
  grandtotal_komsi_sales DOUBLE PRECISION,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS isp_erp.reposisi (
    id_reposisi        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_inventaris      BIGINT NOT NULL,
    tgl_reposisi       DATE,
    keterangan_reposisi TEXT,
    pic_reposisi       VARCHAR(255),
    status_reposisi    VARCHAR(255),
    jumlah_reposisi    BIGINT DEFAULT 1,
    harga_jual         BIGINT,
    disc_plus          BIGINT,
    disc               BIGINT,
    pajak_detail       BIGINT,
    sub_total_detail   BIGINT,
    id_peminjaman      BIGINT,
    id_perangkat_pop   BIGINT,
    tgl_post_reposisi  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at         TIMESTAMP NULL,
    updated_at         TIMESTAMP NULL,
    deleted_at         TIMESTAMP NULL,
    id_reposisi_asal   BIGINT
);

-- (Opsional) set nilai awal sequence sesuai dump MySQL
-- ALTER SEQUENCE isp_erp.reposisi_id_reposisi_seq RESTART WITH 15699;

-- riwayat_baca
CREATE TABLE IF NOT EXISTS isp_erp.riwayat_baca (
  id_riwayat_baca BIGSERIAL PRIMARY KEY,
  id_pengunjung BIGINT NOT NULL,
  id_berita BIGINT NOT NULL,
  tgl_riwayat_baca TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sc_gudang
CREATE TABLE IF NOT EXISTS isp_erp.sc_gudang (
  id_sc_gudang BIGSERIAL PRIMARY KEY,
  nama_gudang VARCHAR(255),
  kategori_gudang VARCHAR(255),
  alamat_gudang VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sc_reposisi_gudang
CREATE TABLE IF NOT EXISTS isp_erp.sc_reposisi_gudang (
  id_sc_reposisi_gudang BIGSERIAL PRIMARY KEY,
  id_sc_gudang_asal BIGINT,
  id_sc_gudang_tujuan BIGINT,
  tgl_pemindahan DATE,
  tgl_pengiriman DATE,
  tgl_diterima DATE,
  keterangan_reposisi VARCHAR(255),
  pic_reposisi VARCHAR(255),
  status_reposisi VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sc_reposisi_gudang_detail
CREATE TABLE IF NOT EXISTS isp_erp.sc_reposisi_gudang_detail (
  id_sc_reposisi_gudang_detail BIGSERIAL PRIMARY KEY,
  id_sc_reposisi_gudang BIGINT,
  id_inventaris BIGINT,
  qty BIGINT,
  keterangan VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sessions
CREATE TABLE IF NOT EXISTS isp_erp.sessions (
  id VARCHAR(255) PRIMARY KEY,
  user_id BIGINT,
  ip_address VARCHAR(45),
  user_agent TEXT,
  payload TEXT NOT NULL,
  last_activity INTEGER NOT NULL
);

-- shift
CREATE TABLE IF NOT EXISTS isp_erp.shift (
  id_shift SERIAL PRIMARY KEY,
  id_divisi INTEGER,
  nama_shift VARCHAR(255),
  jam_in TIME,
  jam_out TIME,
  lintas_hari VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- shift_detail
CREATE TABLE IF NOT EXISTS isp_erp.shift_detail (
  id_shift_detail SERIAL PRIMARY KEY,
  id_shift INTEGER,
  id_karyawan INTEGER,
  tanggal_shift DATE,
  tanggal_input TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sosmed
CREATE TABLE IF NOT EXISTS isp_erp.sosmed (
  id_sosmed BIGSERIAL PRIMARY KEY,
  id_identitas BIGINT NOT NULL,
  jenis_sosmed VARCHAR(255),
  nama_sosmed VARCHAR(255),
  url_sosmed TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sosmed_link
CREATE TABLE IF NOT EXISTS isp_erp.sosmed_link (
  id_sosmed_link BIGSERIAL PRIMARY KEY,
  kategori_sosmed VARCHAR(255),
  link_sosmed TEXT,
  id_berita BIGINT,
  tgl_sosmed_link TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- statistik
CREATE TABLE IF NOT EXISTS isp_erp.statistik (
  id_statistik BIGSERIAL PRIMARY KEY,
  ip VARCHAR(255),
  tgl TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  browser VARCHAR(255),
  negara VARCHAR(255),
  perujuk TEXT,
  keyword TEXT,
  os VARCHAR(255),
  latitude VARCHAR(255),
  longitude VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- stok_opname_inventory
CREATE TABLE IF NOT EXISTS isp_erp.stok_opname_inventory (
  id_stok_opname_inventory SERIAL PRIMARY KEY,
  id_users INTEGER,
  id_perangkat INTEGER,
  stok_sistem_sebelumnya INTEGER,
  stok_sistem_saat_ini INTEGER,
  masuk INTEGER,
  keluar INTEGER,
  stok_fisik INTEGER,
  selisih INTEGER,
  tgl_opname DATE,
  keterangan TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP,
  id_sc_gudang INTEGER
);

-- sub_kategori
CREATE TABLE IF NOT EXISTS isp_erp.sub_kategori (
  id_sub_kategori BIGSERIAL PRIMARY KEY,
  id_kategori BIGINT NOT NULL,
  nama_sub_kategori VARCHAR(255),
  keterangan_sub_kategori VARCHAR(255),
  bg_sub_kategori VARCHAR(255),
  tgl_sub_kategori TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- sumber_berita
CREATE TABLE IF NOT EXISTS isp_erp.sumber_berita (
  id_sumber_berita SERIAL PRIMARY KEY,
  nama_sumber_berita VARCHAR(255),
  logo_sumber_berita VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- =======================
-- tiket
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tiket (
  id_tiket          BIGSERIAL PRIMARY KEY,
  id_customer       BIGINT NOT NULL,
  id_produk         BIGINT NOT NULL,
  id_creator        BIGINT,
  prioritas         VARCHAR(255),
  subject           VARCHAR(255),
  isi_tiket         TEXT,
  gambar_tiket1     TEXT,
  gambar_tiket2     TEXT,
  gambar_tiket3     TEXT,
  gambar_tiket4     TEXT,
  gambar_tiket5     TEXT,
  tgl_tiket         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status_tiket      VARCHAR(255) NOT NULL DEFAULT 'Open',
  status_jawaban    VARCHAR(255) NOT NULL DEFAULT 'Pending',
  divisi_tiket      VARCHAR(255),
  tipe_tiket        VARCHAR(255),
  created_at        TIMESTAMP,
  updated_at        TIMESTAMP,
  deleted_at        TIMESTAMP
);

-- =======================
-- tm_history_block
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tm_history_block (
  id_tm_history_block  BIGSERIAL PRIMARY KEY,
  id_instalasi         BIGINT,
  id_invoice           BIGINT,
  tgl_blokir           DATE,
  jam_blokir           TIME,
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- =======================
-- tm_olt
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tm_olt (
  id_tm_olt          BIGSERIAL PRIMARY KEY,
  id_unit_instalasi  BIGINT,
  merek_olt          VARCHAR(255),
  tipe_olt           VARCHAR(255),
  jenis_modem_olt    VARCHAR(255),
  lokasi_olt         VARCHAR(255),
  host_olt           VARCHAR(255),
  port_olt           VARCHAR(255),
  username_olt       VARCHAR(255),
  password_olt       VARCHAR(255),
  status_olt         VARCHAR(255),
  created_at         TIMESTAMP,
  updated_at         TIMESTAMP,
  deleted_at         TIMESTAMP
);

-- =======================
-- tm_radius
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tm_radius (
  id_tm_radius       BIGSERIAL PRIMARY KEY,
  id_unit_instalasi  BIGINT,
  nama_radius        VARCHAR(255),
  tipe_radius        VARCHAR(255),
  ip_radius          VARCHAR(255),
  port_radius        VARCHAR(255),
  username_radius    VARCHAR(255),
  password_radius    VARCHAR(255),
  status_radius      VARCHAR(255),
  notes_radius       VARCHAR(255),
  created_at         TIMESTAMP,
  updated_at         TIMESTAMP,
  deleted_at         TIMESTAMP
);

-- =======================
-- tm_radius_users
-- (catatan: kolom "group" adalah keyword, maka dikutip)
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tm_radius_users (
  id_tm_radius_users     BIGSERIAL PRIMARY KEY,
  id_tm_radius           BIGINT NOT NULL DEFAULT 1,
  id_unit_instalasi      BIGINT NOT NULL DEFAULT 1,
  id_instalasi           BIGINT,
  id_users_radius        VARCHAR(255),
  id_active              VARCHAR(255),
  id_mikrotik            BIGINT,
  attributes_ip_address  VARCHAR(255),
  disabled               VARCHAR(255),
  "group"                VARCHAR(255),
  name                   VARCHAR(255),
  otp_secret             VARCHAR(255),
  password               VARCHAR(255),
  shared_users           VARCHAR(255),
  notes_radius           VARCHAR(255),
  status_modem           VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- tm_session_radius
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.tm_session_radius (
  id_tm_session_radius   BIGSERIAL PRIMARY KEY,
  id_session_radius      VARCHAR(255),
  acct_session_id        VARCHAR(255),
  active_session         VARCHAR(255),
  calling_station_id     VARCHAR(255),
  download_session       VARCHAR(255),
  last_accounting_packet VARCHAR(255),
  nas_ip_address         VARCHAR(255),
  nas_port_id            VARCHAR(255),
  started_session        VARCHAR(255),
  status_session         VARCHAR(255),
  upload_session         VARCHAR(255),
  uptime_session         VARCHAR(255),
  user_session           VARCHAR(255),
  user_address           VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- ui_hutang_reseller
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_hutang_reseller (
  id_ui_hutang_reseller  BIGSERIAL PRIMARY KEY,
  id_unit_instalasi      BIGINT,
  ket_hutang_reseller    VARCHAR(255),
  nilai_dp               BIGINT,
  tgl_hutang             DATE,
  tgl_mulai_angsuran     DATE,
  dpt                    BIGINT,
  tenor                  BIGINT,
  angsuran               BIGINT,
  status_hutang          VARCHAR(255),
  notes_ui_hutang_reseller VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- ui_komisi_reseller
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_komisi_reseller (
  id_ui_komisi_reseller    BIGSERIAL PRIMARY KEY,
  id_unit_instalasi        BIGINT,
  id_produk_komisi         BIGINT,
  tgl_mulai_perhitungan    DATE,
  tgl_maksimal_perhitungan DATE,
  refund_ke                BIGINT,
  jumlah_pelanggan         BIGINT,
  netto_omzet              BIGINT,
  biaya_ppn11              BIGINT,
  biaya_bhp_uso            BIGINT,
  biaya_bandwith           BIGINT,
  biaya_payment_gateway    BIGINT,
  biaya_adm_system         BIGINT,
  biaya_wa_blast           BIGINT,
  netto_biaya              BIGINT,
  pph25                    BIGINT,
  net_komisi_reseller      BIGINT,
  status_komisi            VARCHAR(255) DEFAULT 'Pending',
  tgl_transfer             DATE,
  keterangan_komisi_reseller VARCHAR(255),
  faktur_pajak             VARCHAR(255),
  created_at               TIMESTAMP,
  updated_at               TIMESTAMP,
  deleted_at               TIMESTAMP
);

-- =======================
-- ui_komisi_reseller_detail
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_komisi_reseller_detail (
  id_ui_komisi_reseller_detail BIGSERIAL PRIMARY KEY,
  id_ui_komisi_reseller  BIGINT,
  id_invoice             BIGINT,
  status_detail          VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- ui_request_refund
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_request_refund (
  id_ui_request_refund     BIGSERIAL PRIMARY KEY,
  id_unit_instalasi        BIGINT,
  nominal_resquest_refund  BIGINT,
  status_request_refund    VARCHAR(255) DEFAULT 'Pending',
  bukti_transfer_request_refund VARCHAR(255),
  tgl_request_refund       TIMESTAMP,
  tgl_approval_refund      TIMESTAMP,
  nomor_rekening           VARCHAR(255),
  atas_nama                VARCHAR(255),
  nama_bank                VARCHAR(255),
  kode_bank_refund         VARCHAR(255),
  reference_no             VARCHAR(255),
  notes                    VARCHAR(255),
  status                   VARCHAR(255),
  signature_key            VARCHAR(255),
  manager_approval         VARCHAR(255),
  direktur_approval        VARCHAR(255),
  created_at               TIMESTAMP,
  updated_at               TIMESTAMP,
  deleted_at               TIMESTAMP
);

-- =======================
-- ui_tagihan_lainnya
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_tagihan_lainnya (
  id_ui_tagihan_lainnya  BIGSERIAL PRIMARY KEY,
  id_ui_komisi_reseller  BIGINT,
  keterangan_tagihan_ui  VARCHAR(255),
  nominal_tagihan_ui     BIGINT,
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- ui_transaksi_reseller
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.ui_transaksi_reseller (
  id_ui_transaksi_reseller BIGSERIAL PRIMARY KEY,
  id_unit_instalasi        BIGINT,
  keterangan_ui_transaksi  TEXT,
  menu_ui_transaksi        VARCHAR(255),
  id_menu_ui_transaksi     BIGINT,
  jenis_ui_transaksi       VARCHAR(255),
  nominal_ui_transaksi     DOUBLE PRECISION,
  saldo_akhir              DOUBLE PRECISION,
  tgl_transaksi            TIMESTAMP,
  created_at               TIMESTAMP,
  updated_at               TIMESTAMP,
  deleted_at               TIMESTAMP
);

-- =======================
-- unit_instalasi
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.unit_instalasi (
  id_unit_instalasi     BIGSERIAL PRIMARY KEY,
  id_customer_ui        BIGINT,
  kategori_ui           VARCHAR(255),
  nama_ui               VARCHAR(255),
  pic_ui                VARCHAR(255),
  alamat_ui             VARCHAR(255),
  lat_long_ui           VARCHAR(255),
  id_provinsi           BIGINT,
  id_kabupaten          BIGINT,
  id_kecamatan          BIGINT,
  id_desa               BIGINT,
  id_produk             BIGINT,
  email_ui              VARCHAR(255),
  whatsapp_ui           VARCHAR(255),
  telepon_ui            VARCHAR(255),
  nib                   VARCHAR(255),
  berkas_nib            VARCHAR(255),
  berkas_pks            VARCHAR(255),
  status_ui             VARCHAR(255),
  wa_helpdesk_ui        VARCHAR(255),
  wa_billing_ui         VARCHAR(255),
  activated_at          DATE,
  deactivated_at        DATE,
  broadcast_invoice_at  VARCHAR(11),
  overdue_invoice_at    VARCHAR(11),
  refund_komisi_at      VARCHAR(11),
  tgl_blokir_ui         VARCHAR(11) NOT NULL DEFAULT '20',
  bank_ui               VARCHAR(255),
  no_rek_ui             VARCHAR(255),
  nama_akun_bank_ui     VARCHAR(255),
  prorate_status        INTEGER NOT NULL DEFAULT 0,
  auto_block_status     INTEGER NOT NULL DEFAULT 0,
  auto_broadacst        VARCHAR(3) NOT NULL DEFAULT 'No',
  jarak_maksimum_absen  BIGINT,
  bill_payment_gateway  VARCHAR(255) NOT NULL DEFAULT 'Yes',
  bill_admin_system     VARCHAR(255) NOT NULL DEFAULT 'Yes',
  voucher_product       VARCHAR(255) NOT NULL DEFAULT 'No',
  bulk_payment          VARCHAR(255) NOT NULL DEFAULT 'No',
  created_at            TIMESTAMP,
  updated_at            TIMESTAMP,
  deleted_at            TIMESTAMP
);

-- =======================
-- upgrade_paket
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.upgrade_paket (
  id_upgrade_paket       BIGSERIAL PRIMARY KEY,
  id_tiket               BIGINT,
  id_customer            BIGINT,
  id_instalasi           BIGINT,
  id_paket_lama          BIGINT,
  id_paket_baru          BIGINT,
  id_invoice             BIGINT,
  biaya_upgrade          BIGINT NOT NULL DEFAULT 0,
  selisih_harga          BIGINT,
  tgl_request_upgrade    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  approval_customer      VARCHAR(255) NOT NULL DEFAULT 'Pending',
  approval_customer_date DATE,
  approval_noc           VARCHAR(255) NOT NULL DEFAULT 'Pending',
  approval_noc_date      DATE,
  approval_finance       VARCHAR(255) NOT NULL DEFAULT 'Pending',
  approval_finance_date  DATE,
  approval_direktur      VARCHAR(255) NOT NULL DEFAULT 'Pending',
  approval_direktur_date DATE,
  notes_upgrade          VARCHAR(255),
  tgl_upgrade_disetujui  DATE,
  status_upgrade         VARCHAR(255) NOT NULL DEFAULT 'Pending',
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- url_sumber_berita
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.url_sumber_berita (
  id_url_sumber_berita BIGSERIAL PRIMARY KEY,
  id_sumber_berita     BIGINT,
  id_berita            BIGINT,
  url_sumber_berita    TEXT,
  created_at           TIMESTAMP,
  updated_at           TIMESTAMP,
  deleted_at           TIMESTAMP
);

-- =======================
-- users
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.users (
  id_users               SERIAL PRIMARY KEY,
  id_unit_instalasi      BIGINT,
  id_divisi              BIGINT,
  id_jc_jabatan          BIGINT,
  nip                    VARCHAR(255),
  npwp                   VARCHAR(255),
  nama                   VARCHAR(255),
  tempat_lahir           VARCHAR(255),
  tgl_lahir              DATE,
  jenis_kelamin          VARCHAR(255),
  telepon                VARCHAR(255),
  telepon_keluarga       VARCHAR(255),
  email                  VARCHAR(255),
  alamat_lengkap         VARCHAR(255),
  level                  VARCHAR(255),
  pp                     TEXT,
  ttd                    VARCHAR(255),
  kartu_keluarga         VARCHAR(255),
  ktp                    VARCHAR(255),
  ijazah_anak            VARCHAR(255),
  id_beneficiary_banks   VARCHAR(20),
  bank_name_users        VARCHAR(255),
  nama_pemilik_rekening  VARCHAR(255),
  no_rek_karyawan        VARCHAR(255),
  status_karyawan        VARCHAR(255),
  username               VARCHAR(255),
  password               TEXT,
  password_apps          VARCHAR(255),
  aktif                  VARCHAR(255),
  tgl_mulai_kerja        DATE,
  tgl_berhenti_kerja     DATE,
  no_rek_bni             VARCHAR(255),
  no_bpjs_tk             VARCHAR(255),
  no_bpjs_kes            VARCHAR(255),
  jatah_cuti             INTEGER,
  shift_kerja            VARCHAR(255),
  remember_token         VARCHAR(255),
  fcm_token              VARCHAR(255),
  firestore_id           VARCHAR(255),
  last_activity          TIMESTAMP,
  id_fingger             BIGINT,
  token                  VARCHAR(255),
  token_expired_at       VARCHAR(255),
  created_at             TIMESTAMP,
  updated_at             TIMESTAMP,
  deleted_at             TIMESTAMP
);

-- =======================
-- va_mandiri
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.va_mandiri (
  id_va_mandiri  BIGSERIAL PRIMARY KEY,
  va             VARCHAR(255),
  nama           VARCHAR(255),
  no_id          VARCHAR(255),
  tagihan        VARCHAR(255)
);

-- =======================
-- video
-- =======================
CREATE TABLE IF NOT EXISTS isp_erp.video (
  id_video     BIGSERIAL PRIMARY KEY,
  id_album     BIGINT NOT NULL DEFAULT 1,
  id_users     BIGINT NOT NULL DEFAULT 3,
  judul_video  VARCHAR(255),
  judul_seo    VARCHAR(255),
  keterangan   TEXT,
  tgl_posting  TIMESTAMP,
  gambar       VARCHAR(255),
  url_video    TEXT,
  aktif        SMALLINT NOT NULL DEFAULT 1,
  dibaca       INTEGER NOT NULL DEFAULT 0,
  created_at   TIMESTAMP,
  updated_at   TIMESTAMP,
  deleted_at   TIMESTAMP
);

-- wa_broadcast_logs
CREATE TABLE IF NOT EXISTS isp_erp.wa_broadcast_logs (
  id_wa_broadcast_logs BIGSERIAL PRIMARY KEY,
  kategori_broadcast VARCHAR(255),
  id_invoice BIGINT,
  id_users BIGINT,
  id_customer BIGINT,
  wa_phone_number VARCHAR(255),
  template_name VARCHAR(255),
  broadcast_job_id VARCHAR(255),
  broadcast_logs_id VARCHAR(255),
  message_id VARCHAR(255),
  sent_at VARCHAR(255),
  status VARCHAR(255),
  notes VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_general_notif
CREATE TABLE IF NOT EXISTS isp_erp.wa_general_notif (
  id_wa_general_notif SERIAL PRIMARY KEY,
  id_users INTEGER,
  nama_notif VARCHAR(100),
  action VARCHAR(100),
  location VARCHAR(100),
  impact VARCHAR(100),
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  estimated_duration VARCHAR(100),
  notes TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_general_notif_detail
CREATE TABLE IF NOT EXISTS isp_erp.wa_general_notif_detail (
  id_wa_general_notif_detail SERIAL PRIMARY KEY,
  id_wa_general_notif INTEGER,
  id_users INTEGER,
  id_unit_instalasi INTEGER,
  id_instalasi INTEGER,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_general_notif_log
CREATE TABLE IF NOT EXISTS isp_erp.wa_general_notif_log (
  id_wa_general_notif_log SERIAL PRIMARY KEY,
  id_wa_general_notif_detail INTEGER,
  status VARCHAR(100),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_log_chat
CREATE TABLE IF NOT EXISTS isp_erp.wa_log_chat (
  id_wa_log_chat BIGSERIAL PRIMARY KEY,
  id_customer BIGINT,
  chat_id VARCHAR(255),
  room_id VARCHAR(255),
  last_comment_sender_type VARCHAR(255),
  last_comment_text TEXT,
  last_comment_timestamp TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_rooms
CREATE TABLE IF NOT EXISTS isp_erp.wa_rooms (
  id_wa_rooms BIGSERIAL PRIMARY KEY,
  id_customer BIGINT,
  room_id VARCHAR(255),
  user_id VARCHAR(255),
  source VARCHAR(255),
  last_message TIMESTAMP,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_rooms_not_found
CREATE TABLE IF NOT EXISTS isp_erp.wa_rooms_not_found (
  id_wa_rooms_not_found BIGSERIAL PRIMARY KEY,
  id_customer BIGINT,
  handphone VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);

-- wa_token
CREATE TABLE IF NOT EXISTS isp_erp.wa_token (
  id_wa_token BIGSERIAL PRIMARY KEY,
  tokens VARCHAR(255) NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  deleted_at TIMESTAMP
);