-- KINET MikroTik Admin Module - Update v2 Admin Billing -- Jalankan sekali setelah replace file update. CREATE TABLE IF NOT EXISTS customers ( id INT AUTO_INCREMENT PRIMARY KEY, router_id INT NULL, created_by INT NULL, sales_id INT NULL, reseller_id INT NULL, name VARCHAR(160) NOT NULL, phone VARCHAR(50) NULL, email VARCHAR(160) NULL, address TEXT NULL, area VARCHAR(160) NULL, service_type ENUM('pppoe','hotspot','manual') NOT NULL DEFAULT 'pppoe', mikrotik_username VARCHAR(120) NULL, mikrotik_profile VARCHAR(120) NULL, package_name VARCHAR(120) NULL, package_price DECIMAL(14,2) NOT NULL DEFAULT 0, due_day TINYINT UNSIGNED NOT NULL DEFAULT 10, status ENUM('active','inactive','isolated') NOT NULL DEFAULT 'active', activated_at DATETIME NULL, isolated_at DATETIME NULL, note TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_customers_router (router_id), INDEX idx_customers_status (status), INDEX idx_customers_service (service_type), INDEX idx_customers_due (due_day), CONSTRAINT fk_customers_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_customers_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_customers_sales FOREIGN KEY (sales_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_customers_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoices ( id BIGINT AUTO_INCREMENT PRIMARY KEY, invoice_no VARCHAR(80) NOT NULL UNIQUE, customer_id INT NOT NULL, period_month CHAR(7) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, discount DECIMAL(14,2) NOT NULL DEFAULT 0, fee DECIMAL(14,2) NOT NULL DEFAULT 0, total DECIMAL(14,2) NOT NULL DEFAULT 0, due_date DATE NOT NULL, status ENUM('unpaid','paid','cancelled') NOT NULL DEFAULT 'unpaid', payment_method VARCHAR(80) NULL, payment_reference VARCHAR(160) NULL, payment_url TEXT NULL, note TEXT NULL, sent_at DATETIME NULL, paid_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_invoice_customer_period (customer_id, period_month), INDEX idx_invoices_status (status), INDEX idx_invoices_due (due_date), CONSTRAINT fk_invoices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payments ( id BIGINT AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT NOT NULL, customer_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, method VARCHAR(80) NOT NULL DEFAULT 'manual', reference VARCHAR(160) NULL, note TEXT NULL, paid_by INT NULL, paid_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_payments_customer (customer_id), CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_payments_user FOREIGN KEY (paid_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS wa_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id INT NULL, invoice_id BIGINT NULL, phone VARCHAR(60) NOT NULL, message TEXT NOT NULL, gateway VARCHAR(40) NULL, status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued', response TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_wa_status (status), CONSTRAINT fk_wa_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_wa_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS voucher_templates ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, html_template LONGTEXT NOT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_voucher_templates_default (is_default) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO voucher_templates (name, html_template, is_default, status) SELECT 'Default KINET Voucher', '
KINET WIFI
Internet Cerdas Pedesaan
Profile: {{profile}}
{{username}}
Password: {{password}}
Harga: {{price}}
{{validity}}
', 1, 'active' WHERE NOT EXISTS (SELECT 1 FROM voucher_templates); INSERT INTO settings (setting_key, setting_value) VALUES ('wa_enabled','0'), ('wa_gateway','fonnte'), ('wa_fonnte_token',''), ('wa_custom_url',''), ('wa_custom_token',''), ('wa_template_invoice','Halo {{customer_name}}, tagihan internet {{company_name}} periode {{period}} sebesar {{total}} jatuh tempo {{due_date}}. Link bayar: {{payment_url}}. Terima kasih.'), ('wa_template_due','Halo {{customer_name}}, tagihan internet Anda jatuh tempo hari ini sebesar {{total}}. Mohon segera dibayar agar layanan tetap aktif.'), ('wa_template_isolir','Halo {{customer_name}}, layanan internet Anda kami isolir sementara karena tagihan {{period}} belum dibayar. Total: {{total}}.'), ('payment_gateway','manual'), ('payment_enabled','0'), ('payment_midtrans_server_key',''), ('payment_midtrans_client_key',''), ('payment_midtrans_production','0'), ('payment_custom_payment_url',''), ('auto_invoice_enabled','0'), ('auto_invoice_day','1'), ('auto_send_invoice_enabled','0'), ('auto_isolir_enabled','0'), ('auto_isolir_grace_days','7'), ('company_name','KINET') ON DUPLICATE KEY UPDATE setting_value = setting_value;