-- Skema awal pemulihan Billing; hanya untuk database BARU/kosong.
-- Belum mencakup semua kolom modul Hotspot/FTTH/monitoring. Jangan untuk produksi sebelum tes penuh.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users (
 id_user INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL, nama_lengkap VARCHAR(150) NOT NULL,
 level VARCHAR(40) NOT NULL DEFAULT 'admin', status VARCHAR(20) NOT NULL DEFAULT 'aktif',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS pengaturan_perusahaan (
 id_pengaturan INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_perusahaan VARCHAR(190) NULL,
 alamat TEXT NULL, telepon VARCHAR(50) NULL, email VARCHAR(190) NULL, logo_perusahaan VARCHAR(255) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS paket_internet (
 id_paket INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_paket VARCHAR(120) NOT NULL,
 harga DECIMAL(12,2) NOT NULL DEFAULT 0, profile_name VARCHAR(120) NULL,
 rate_limit_rx VARCHAR(50) NULL, rate_limit_tx VARCHAR(50) NULL, status_paket VARCHAR(30) NOT NULL DEFAULT 'aktif',
 sync_mikrotik VARCHAR(10) DEFAULT 'no', last_sync DATETIME NULL,
 burst_limit_rx VARCHAR(50) NULL, burst_limit_tx VARCHAR(50) NULL, burst_threshold_rx VARCHAR(50) NULL,
 burst_threshold_tx VARCHAR(50) NULL, burst_time_rx VARCHAR(50) NULL, burst_time_tx VARCHAR(50) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS data_pelanggan (
 id_pelanggan INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_pelanggan VARCHAR(180) NOT NULL,
 alamat_pelanggan TEXT NULL, telepon_pelanggan VARCHAR(50) NULL, id_paket INT UNSIGNED NULL, odp_id INT NULL,
 tgl_daftar DATE NULL, tgl_expired DATE NULL, mikrotik_username VARCHAR(120) NULL UNIQUE,
 mikrotik_password VARCHAR(255) NULL, mikrotik_profile VARCHAR(120) NULL, sync_mikrotik VARCHAR(10) DEFAULT 'no',
 last_sync DATETIME NULL, status_aktif VARCHAR(30) NOT NULL DEFAULT 'aktif', mikrotik_disabled VARCHAR(10) DEFAULT 'no',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(id_paket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tagihan (
 id_tagihan VARCHAR(80) PRIMARY KEY, id_pelanggan INT UNSIGNED NOT NULL, bulan_tagihan TINYINT UNSIGNED NOT NULL,
 tahun_tagihan SMALLINT UNSIGNED NOT NULL, jumlah_tagihan DECIMAL(12,2) NOT NULL DEFAULT 0,
 tgl_jatuh_tempo DATE NULL, status_tagihan VARCHAR(30) NOT NULL DEFAULT 'belum_bayar', deskripsi TEXT NULL,
 auto_generated VARCHAR(10) DEFAULT 'no', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(id_pelanggan), INDEX(status_tagihan), UNIQUE KEY uniq_invoice_period (id_pelanggan,bulan_tagihan,tahun_tagihan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS pembayaran (
 id_pembayaran BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_tagihan VARCHAR(80) NOT NULL,
 id_pelanggan INT UNSIGNED NOT NULL, tanggal_bayar DATETIME NOT NULL, jumlah_bayar DECIMAL(12,2) NOT NULL DEFAULT 0,
 metode_bayar VARCHAR(80) NULL, keterangan TEXT NULL, id_user_pencatat INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(id_tagihan), INDEX(id_pelanggan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS transaksi_lain (
 id_transaksi BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tanggal DATE NOT NULL, jenis VARCHAR(50) NOT NULL,
 kategori VARCHAR(120) NULL, keterangan TEXT NULL, jumlah DECIMAL(12,2) NOT NULL DEFAULT 0,
 created_by INT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS log_aktivitas (
 id_log BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_user INT UNSIGNED NULL, username VARCHAR(100) NULL,
 aktivitas TEXT NULL, tabel_terkait VARCHAR(100) NULL, id_data_terkait VARCHAR(100) NULL,
 ip_address VARCHAR(64) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS activity_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NULL, action TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS system_settings (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(150) NOT NULL UNIQUE, setting_value TEXT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;