CREATE DATABASE IF NOT EXISTS indes_billing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE indes_billing;
CREATE TABLE admins(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) UNIQUE NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 name VARCHAR(100) NOT NULL,
 role ENUM('super_admin','admin') NOT NULL DEFAULT 'admin',
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO admins(username,password_hash,name,role,status) VALUES ('superadmin','$2y$12$SvQ5NggHogusDeUZ8lr/nez7woWKmnpWmVWdQsb8wkS05Zo4725fe','Super Administrator','super_admin','active');
CREATE TABLE settings(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(100) UNIQUE NOT NULL,setting_value TEXT);
INSERT INTO settings(setting_key,setting_value) VALUES
('company_name','Indes.Net'),('company_address','Desa Parung'),('company_phone','081380114473'),('payment_info','Silakan lakukan pembayaran sesuai informasi rekening/QRIS admin.'),
('wa_billing_template','Halo {nama},\n\nPengingat tagihan internet {perusahaan}.\n\nNo. Tagihan: {invoice}\nPaket: {paket}\nPeriode: {periode}\nNominal: {nominal}\nJatuh tempo: {jatuh_tempo}\n\n{info_pembayaran}\n\nTerima kasih.'),('points_per_on_time_payment','10');
CREATE TABLE packages(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(80) NOT NULL,speed_mbps INT NOT NULL,price DECIMAL(14,2) NOT NULL,status ENUM('active','inactive') DEFAULT 'active');
INSERT INTO packages(name,speed_mbps,price) VALUES ('10 Mbps',10,150000),('15 Mbps',15,165000),('20 Mbps',20,200000),('30 Mbps',30,250000),('50 Mbps',50,270000),('100 Mbps',100,310000);
CREATE TABLE customers(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_code VARCHAR(30) UNIQUE NOT NULL,name VARCHAR(120) NOT NULL,phone VARCHAR(30) NOT NULL,address TEXT,package_id INT UNSIGNED NOT NULL,due_day TINYINT UNSIGNED DEFAULT 10,status ENUM('active','inactive','isolated') DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(package_id) REFERENCES packages(id));
CREATE TABLE invoices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,invoice_no VARCHAR(50) UNIQUE NOT NULL,customer_id INT UNSIGNED NOT NULL,period DATE NOT NULL,amount DECIMAL(14,2) NOT NULL,due_date DATE NOT NULL,status ENUM('unpaid','paid','cancelled') DEFAULT 'unpaid',paid_at DATETIME NULL,payment_method VARCHAR(50),notes TEXT,whatsapp_clicked_at DATETIME NULL,whatsapp_clicked_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,UNIQUE KEY uq_customer_period(customer_id,period));
CREATE TABLE whatsapp_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id INT UNSIGNED NULL,invoice_id BIGINT UNSIGNED NULL,phone VARCHAR(30) NOT NULL,message TEXT NOT NULL,mode ENUM('wa_link','cloud_api') NOT NULL,status ENUM('sent','failed') NOT NULL,response TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE expenses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,expense_date DATE NOT NULL,category VARCHAR(100) NOT NULL,description VARCHAR(255) NOT NULL,amount DECIMAL(14,2) NOT NULL,created_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_expense_date(expense_date),FOREIGN KEY(created_by) REFERENCES admins(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE point_transactions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id INT UNSIGNED NOT NULL,invoice_id BIGINT UNSIGNED NULL,points INT NOT NULL,type ENUM('earned','redeemed','adjustment') NOT NULL,description VARCHAR(255) NOT NULL,created_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_points_invoice(invoice_id),INDEX idx_points_customer(customer_id),FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES admins(id) ON DELETE SET NULL) ENGINE=InnoDB;
