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 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 isp_erp.mutasi_bni ( id_mutasi_bni BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, id_bni_account BIGINT, post_date TIMESTAMP, value_date TIMESTAMP, 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, updated_at TIMESTAMP, deleted_at TIMESTAMP ); CREATE TABLE IF NOT EXISTS isp_erp.mutasi_bni_dummy ( id_mutasi_bni_dummy BIGSERIAL PRIMARY KEY, id_bni_account BIGINT NULL, 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 isp_erp.mutasi_mandiri ( id_mutasi_mandiri BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, account_no VARCHAR(255), "date" TIMESTAMP, val_date TIMESTAMP, transaction_code VARCHAR(255), description1 VARCHAR(255), description2 VARCHAR(255), refrence_no VARCHAR(255), jenis_transaksi VARCHAR(255), nominal DOUBLE PRECISION, verified VARCHAR(255) NOT NULL DEFAULT 'Pending', id_acc_rekening INT, created_at TIMESTAMP, updated_at TIMESTAMP, deleted_at TIMESTAMP ); 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 ); -- report_history CREATE TABLE IF NOT EXISTS isp_erp.report_history ( id_report_history BIGSERIAL 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, updated_at TIMESTAMP, deleted_at TIMESTAMP ); -- 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 ); CREATE TABLE isp_erp.waktu_kerja ( id_waktu_kerja SERIAL PRIMARY KEY, -- SERIAL otomatis auto increment di PostgreSQL tgl_mulai_kerja DATE, tgl_akhir_kerja DATE, jumlah_hari_kerja BIGINT, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL ); CREATE TABLE isp_erp.bni_account ( id_bni_account SERIAL PRIMARY KEY, -- SERIAL otomatis auto increment di PostgreSQL account_number VARCHAR(255), account_name VARCHAR(255), account_notes VARCHAR(255), created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL ); CREATE TABLE isp_erp.bulan_kerja ( id_bulan_kerja INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, tgl_mulai_kerja DATE, tgl_akhir_kerja DATE, total_hari_kerja BIGINT, created_at TIMESTAMP, updated_at TIMESTAMP, deleted_at TIMESTAMP );