CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO roles (id, code, name, description) VALUES
(1, 'admin', 'Admin Billing/ISP', 'Akses penuh sistem billing dan MikroTik'),
(2, 'reseller', 'Reseller', 'Pelaku jual kembali internet'),
(3, 'agen', 'Agen Voucher', 'Agen penjualan voucher'),
(4, 'sales', 'Sales', 'Tim pendaftaran pelanggan'),
(5, 'teknisi', 'Teknisi', 'Tim teknis, tiket, online/offline, ACS'),
(6, 'pelanggan', 'Pelanggan', 'Portal pelanggan untuk status layanan, tagihan, pembayaran, dan keluhan');

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(150) NULL UNIQUE,
    phone VARCHAR(40) NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    referral_code VARCHAR(60) NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_roles FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS routers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    branch VARCHAR(150) NULL,
    host VARCHAR(120) NOT NULL,
    api_port INT NOT NULL DEFAULT 8728,
    api_ssl TINYINT(1) NOT NULL DEFAULT 0,
    api_username VARCHAR(100) NOT NULL,
    api_password_enc TEXT NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_router_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_user_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    router_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_router (user_id, router_id),
    CONSTRAINT fk_rua_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_rua_router FOREIGN KEY (router_id) REFERENCES routers(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    role_code VARCHAR(30) NULL,
    action VARCHAR(120) NOT NULL,
    detail TEXT NULL,
    ip_address VARCHAR(80) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_user (user_id),
    INDEX idx_logs_action (action),
    CONSTRAINT fk_logs_user FOREIGN KEY (user_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 settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 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,
    customer_code VARCHAR(40) NULL UNIQUE,
    router_id INT NULL,
    created_by INT NULL,
    sales_id INT NULL,
    reseller_id INT NULL,
    portal_user_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,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customers_code (customer_code),
    INDEX idx_customers_router (router_id),
    INDEX idx_customers_portal_user_id (portal_user_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,
    isolir_date DATE 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,
    h7_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),
    INDEX idx_invoices_isolir (isolir_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 Mikhmon KINET Voucher', '<?php
// Template default KINET Voucher - style Mikhmon.

if(substr($validity,-1) == "d"){
  $validity = "Aktif:".substr($validity,0,-1)."Hari";
}else if(substr($validity,-1) == "h"){
  $validity = "Aktif:".substr($validity,0,-1)."Jam";
}
if(substr($timelimit,-1) == "d" & strlen($timelimit) >3){
  $timelimit = "Durasi:".((substr($timelimit,0,-1)*7) +  substr($timelimit, 2,1))."Hari";
}else if(substr($timelimit,-1) == "d"){
  $timelimit = "Durasi:".substr($timelimit,0,-1)."Hari";
}else if(substr($timelimit,-1) == "h"){
  $timelimit = "Durasi:".substr($timelimit,0,-1)."Jam";
}else if(substr($timelimit,-1) == "w"){
  $timelimit = "Durasi:".(substr($timelimit,0,-1)*7)."Hari";
}

/*
Sesuikan harga dan warna masing-masing.
Jika warna profile voucher sudah diisi dari billing, nilai $color akan otomatis memakai warna profile.
*/
if($getsprice == "1000"){ $color = "#2196F3";}
elseif($getsprice == "3000"){ $color = "#009688";}
elseif($getsprice == "5000"){ $color = "#FF9800";}
elseif(!empty($profilecolor)){ $color = $profilecolor;}
else{ $color = "#FFFFFF";}
?>

<style type="text/css">
.rotate {
  vertical-align: bottom;
  text-align: center;
}
.rotate span {
  -ms-writing-mode: tb-rl;
  -webkit-writing-mode: vertical-rl;
  writing-mode: vertical-rl;
  transform: rotate(180deg);
  white-space: nowrap;
}
.qrcode{
  height:60px;
  width:60px;
}
</style>

<table class="voucher" style="width: 230px; border-collapse:collapse; font-family:Arial, sans-serif; color:#111; background:#fff; border:1px solid #111;">
  <tbody>
    <tr>
      <td class="rotate" style="font-weight: bold; border-right: 1px solid black; background-color:<?php echo $color;?>; -webkit-print-color-adjust: exact; print-color-adjust: exact;" rowspan="4"><span><?= $price; ?></span></td>
      <td style="font-weight: bold" colspan="2"><?= $hotspotname; ?> </td>
      <?php if ($qr == "yes") { ?>
      <td style="" rowspan="3"><?= $qrcode ?></td>
      <?php } else { ?>
      <td style="" rowspan="3"><img style="width: 60px; height: 60px;" src="<?= $logo ?>" alt="logo"></td>
      <?php } ?>
    </tr>
    <tr>
      <?php if ($usermode == "vc") { ?>
      <td style="width: 100%; font-weight: bold; font-size: 20px; text-align: center;"><?= $username; ?></td>
      <?php } elseif ($usermode == "up") { ?>
      <td style="width: 100%; font-weight: bold; font-size: 15px; text-align: center;"><?= "User: " . $username . "<br>Pass: " . $password; ?></td>
      <?php } ?>
    </tr>
    <tr>
      <td style="font-size: 10px;"><?= $validity; ?> <?= $timelimit; ?> <?= $datalimit; ?></td>
    </tr>
    <tr>
      <td colspan="3" style="font-size: 10px;">Login: http://<?= $dnsname; ?> <span id="num"> <?= " [$num]"; ?></span></td>
    </tr>
  </tbody>
</table>', 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}}. Tanggal isolir {{isolir_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_midtrans_snap_expiry_minutes','60'),
('payment_custom_payment_url',''),
('auto_invoice_enabled','0'),
('auto_invoice_day','1'),
('auto_send_invoice_enabled','0'),
('auto_invoice_reminder_before_isolir_days','7'),
('auto_isolir_enabled','0'),
('auto_isolir_grace_days','7'),
('company_name','KINET')
ON DUPLICATE KEY UPDATE setting_value = setting_value;
-- KINET MikroTik Admin Module - Update v5 Customer Popup + MikroTik Provisioning
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL UNIQUE,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO areas (name, description, status)
SELECT 'Default', 'Wilayah default jika data wilayah belum dibuat.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM areas WHERE name='Default');

CREATE TABLE IF NOT EXISTS subscription_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    service_type ENUM('pppoe','hotspot','static') NOT NULL DEFAULT 'pppoe',
    mikrotik_profile VARCHAR(160) NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    note TEXT NULL,
    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_subscription_profiles_service (service_type),
    INDEX idx_subscription_profiles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default PPPoE 10Mbps', 'pppoe', '10Mbps', 0, 'Ubah harga/profile sesuai profile MikroTik asli.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default PPPoE 10Mbps');

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default Hotspot 1Mbps', 'hotspot', '1Mbps', 0, 'Ubah harga/profile sesuai profile MikroTik asli.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default Hotspot 1Mbps');

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default Static', 'static', '', 0, 'Paket static/manual tanpa command MikroTik otomatis.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default Static');

ALTER TABLE customers MODIFY service_type ENUM('pppoe','hotspot','static','manual') NOT NULL DEFAULT 'pppoe';
ALTER TABLE customers ADD COLUMN IF NOT EXISTS area_id INT NULL AFTER reseller_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS subscription_profile_id INT NULL AFTER area_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ktp_photo VARCHAR(255) NULL AFTER activated_at;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_password VARCHAR(160) NULL AFTER mikrotik_username;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_create_mode ENUM('existing','auto','manual_create','none') NOT NULL DEFAULT 'none' AFTER mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS extra_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER package_price;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS fee_note VARCHAR(255) NULL AFTER extra_fee;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER fee_note;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS discount_note VARCHAR(255) NULL AFTER discount_amount;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_sync_status ENUM('pending','success','failed','linked','skipped') NOT NULL DEFAULT 'skipped' AFTER note;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_sync_message TEXT NULL AFTER mikrotik_sync_status;
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_area_id (area_id);
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_subscription_profile_id (subscription_profile_id);

UPDATE customers SET service_type='static' WHERE service_type='manual';
UPDATE customers SET area='Default' WHERE area IS NULL OR area='';
UPDATE customers SET mikrotik_sync_status='linked' WHERE mikrotik_sync_status='skipped' AND mikrotik_username IS NOT NULL AND mikrotik_username<>'';

-- KINET MikroTik Admin Module - Update v6 Profil Berlangganan + Profile Isolir
-- Jalankan sekali setelah replace file update.

ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS router_id INT NULL AFTER id;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS speed_mbps DECIMAL(8,2) NOT NULL DEFAULT 0 AFTER mikrotik_profile;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS ppn_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS ppn_percent DECIMAL(5,2) NOT NULL DEFAULT 11.00 AFTER ppn_enabled;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS isolir_mikrotik_profile VARCHAR(160) NULL AFTER ppn_percent;
ALTER TABLE subscription_profiles ADD INDEX IF NOT EXISTS idx_subscription_profiles_router (router_id);

ALTER TABLE customers ADD COLUMN IF NOT EXISTS current_mikrotik_profile VARCHAR(160) NULL AFTER mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS last_mikrotik_profile VARCHAR(160) NULL AFTER current_mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ppn_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER package_price;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ppn_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER ppn_enabled;

UPDATE subscription_profiles SET ppn_percent=11.00 WHERE ppn_percent IS NULL OR ppn_percent=0;
UPDATE customers c
LEFT JOIN subscription_profiles sp ON sp.id=c.subscription_profile_id
SET c.package_name = COALESCE(sp.name, c.package_name),
    c.package_price = COALESCE(sp.price, c.package_price),
    c.mikrotik_profile = COALESCE(NULLIF(sp.mikrotik_profile,''), c.mikrotik_profile),
    c.current_mikrotik_profile = COALESCE(c.current_mikrotik_profile, c.mikrotik_profile),
    c.ppn_enabled = COALESCE(sp.ppn_enabled, c.ppn_enabled),
    c.ppn_amount = CASE WHEN COALESCE(sp.ppn_enabled, c.ppn_enabled)=1 THEN ROUND(COALESCE(sp.price, c.package_price) * COALESCE(sp.ppn_percent, 11.00) / 100, 0) ELSE 0 END
WHERE c.subscription_profile_id IS NOT NULL;
-- KINET MikroTik Admin Module - Update v7 Generate Voucher
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS voucher_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id INT NULL,
    name VARCHAR(160) NOT NULL,
    mikrotik_profile VARCHAR(160) NOT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    validity VARCHAR(120) NULL,
    template_color VARCHAR(20) NOT NULL DEFAULT '#2563eb',
    description TEXT NULL,
    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_profiles_router (router_id),
    INDEX idx_voucher_profiles_status (status),
    CONSTRAINT fk_voucher_profiles_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS voucher_batches (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_comment VARCHAR(180) NOT NULL UNIQUE,
    router_id INT NULL,
    voucher_profile_id INT NULL,
    agent_id INT NULL,
    created_by INT NULL,
    amount_requested INT NOT NULL DEFAULT 0,
    amount_success INT NOT NULL DEFAULT 0,
    amount_failed INT NOT NULL DEFAULT 0,
    status ENUM('processing','success','partial','failed') NOT NULL DEFAULT 'processing',
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_voucher_batches_comment (batch_comment),
    INDEX idx_voucher_batches_agent (agent_id),
    CONSTRAINT fk_voucher_batches_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_creator FOREIGN KEY (created_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 vouchers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT NULL,
    router_id INT NULL,
    voucher_profile_id INT NULL,
    agent_id INT NULL,
    created_by INT NULL,
    username VARCHAR(120) NOT NULL,
    password VARCHAR(120) NOT NULL,
    mikrotik_profile VARCHAR(160) NOT NULL,
    batch_comment VARCHAR(180) NOT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    template_color VARCHAR(20) NOT NULL DEFAULT '#2563eb',
    status ENUM('available','sold','used','expired','cancelled','disabled') NOT NULL DEFAULT 'available',
    mikrotik_sync_status ENUM('pending','success','failed','skipped') NOT NULL DEFAULT 'pending',
    mikrotik_sync_message TEXT NULL,
    mikrotik_last_status VARCHAR(80) NULL,
    last_checked_at DATETIME NULL,
    sold_at DATETIME NULL,
    used_at DATETIME NULL,
    printed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vouchers_username (username),
    INDEX idx_vouchers_batch_comment (batch_comment),
    INDEX idx_vouchers_status (status),
    INDEX idx_vouchers_profile (voucher_profile_id),
    INDEX idx_vouchers_agent (agent_id),
    INDEX idx_vouchers_last_checked (last_checked_at),
    CONSTRAINT fk_vouchers_batch FOREIGN KEY (batch_id) REFERENCES voucher_batches(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- KINET MikroTik Admin Module - Update v10 Agen + Profile Voucher Pokok/Jual
-- Jalankan sekali setelah replace file update.

ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS cost_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS sale_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER cost_price;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS agent_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER sale_price;
ALTER TABLE voucher_profiles ADD INDEX IF NOT EXISTS idx_voucher_profiles_price (sale_price, cost_price);

UPDATE voucher_profiles
SET sale_price = CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END,
    cost_price = COALESCE(cost_price, 0),
    agent_fee = GREATEST(CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END - COALESCE(cost_price,0), 0),
    price = CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END;

ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS cost_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS sale_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER cost_price;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS agent_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER sale_price;
ALTER TABLE vouchers ADD INDEX IF NOT EXISTS idx_vouchers_sold_agent (agent_id, status, created_at);

UPDATE vouchers v
LEFT JOIN voucher_profiles vp ON vp.id = v.voucher_profile_id
SET v.sale_price = CASE WHEN v.sale_price IS NULL OR v.sale_price = 0 THEN COALESCE(vp.sale_price, vp.price, v.price, 0) ELSE v.sale_price END,
    v.cost_price = CASE WHEN v.cost_price IS NULL OR v.cost_price = 0 THEN COALESCE(vp.cost_price, 0) ELSE v.cost_price END,
    v.agent_fee = CASE WHEN v.agent_fee IS NULL OR v.agent_fee = 0 THEN GREATEST(COALESCE(vp.sale_price, vp.price, v.price, 0) - COALESCE(vp.cost_price, 0), 0) ELSE v.agent_fee END,
    v.price = CASE WHEN v.sale_price IS NULL OR v.sale_price = 0 THEN COALESCE(vp.sale_price, vp.price, v.price, 0) ELSE v.sale_price END;

CREATE TABLE IF NOT EXISTS agent_report_resets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NULL,
    period_type ENUM('weekly','monthly') NOT NULL,
    reset_at DATETIME NOT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_agent_report_resets_agent_period (agent_id, period_type, reset_at),
    CONSTRAINT fk_agent_report_resets_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_agent_report_resets_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- KINET MikroTik Admin Module - Update v13 Menu, Search, Bank Account Popup
-- Jalankan sekali setelah replace file v13.

CREATE TABLE IF NOT EXISTS bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(120) NOT NULL,
    type ENUM('bank','ewallet') NOT NULL DEFAULT 'bank',
    bank_name VARCHAR(120) NOT NULL,
    account_number VARCHAR(120) NOT NULL,
    account_name VARCHAR(160) NOT NULL,
    code VARCHAR(80) NOT NULL,
    note TEXT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bank_code (code),
    INDEX idx_bank_status (status),
    INDEX idx_bank_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO bank_accounts (label, type, bank_name, account_number, account_name, code, note, is_default, status, sort_order)
SELECT 'Default Manual', 'bank', 'BANK', '0000000000', 'KINET', 'default', 'Ganti rekening ini dari menu Rekening Bank.', 1, 'active', 0
WHERE NOT EXISTS (SELECT 1 FROM bank_accounts);

INSERT INTO settings (setting_key, setting_value) VALUES
('template_bank_accounts_enabled','1')
ON DUPLICATE KEY UPDATE setting_value = setting_value;
-- KINET MikroTik Admin Module - Update v15 Company + Landing Settings
-- Jalankan sekali setelah replace file v15.

INSERT INTO settings (setting_key, setting_value) VALUES
('company_profile', '{"name":"KINET","legal_name":"KINET Digital Nusantara","tagline":"Internet Cerdas Pedesaan","logo":"","favicon":"","address":"","phone":"","whatsapp":"","email":"","website":""}'),
('landing_settings', '{"enabled":1,"style":"cyberpunk","hero_title":"Internet Fiber Cepat untuk Rumah dan Usaha","hero_subtitle":"Nikmati koneksi stabil, support cepat, dan paket internet yang fleksibel untuk area Anda.","hero_badge":"Promo pemasangan terbatas","cta_text":"Daftar Sekarang","cta_url":"login.php","hero_image":"","show_vision_mission":1,"vision":"Menjadi penyedia internet lokal yang stabil, transparan, dan dekat dengan pelanggan.","mission":"Membangun jaringan fiber yang rapi, layanan pelanggan yang cepat, dan sistem billing yang modern.","show_history":1,"history":"Berawal dari kebutuhan koneksi internet yang lebih stabil di lingkungan sekitar, KINET hadir untuk menghadirkan layanan internet cerdas bagi wilayah pedesaan dan perumahan.","contact_title":"Hubungi kami untuk cek coverage","facebook":"","instagram":"","tiktok":"","youtube":""}'),
('landing_packages', '{}')
ON DUPLICATE KEY UPDATE setting_value = setting_value;
-- KINET MikroTik Admin Module - Update v17 Infrastructure Fiber + Customer ODP/Map
-- Jalankan sekali setelah replace file v17.

CREATE TABLE IF NOT EXISTS olt_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    brand VARCHAR(120) NULL,
    model VARCHAR(120) NULL,
    ip_address VARCHAR(80) NULL,
    location VARCHAR(255) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    pon_ports INT NOT NULL DEFAULT 0,
    status ENUM('online','offline','maintenance','inactive') NOT NULL DEFAULT 'online',
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_olt_status (status),
    INDEX idx_olt_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS odc_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    olt_id INT NULL,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    total_splitter INT NOT NULL DEFAULT 0,
    status ENUM('active','full','maintenance','inactive') NOT NULL DEFAULT 'active',
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_odc_olt (olt_id),
    INDEX idx_odc_status (status),
    INDEX idx_odc_code (code),
    CONSTRAINT fk_odc_olt FOREIGN KEY (olt_id) REFERENCES olt_devices(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS odp_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    odc_id INT NULL,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    total_ports INT NOT NULL DEFAULT 8,
    used_ports INT NOT NULL DEFAULT 0,
    status ENUM('active','full','maintenance','inactive') NOT NULL DEFAULT 'active',
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_odp_odc (odc_id),
    INDEX idx_odp_status (status),
    INDEX idx_odp_code (code),
    CONSTRAINT fk_odp_odc FOREIGN KEY (odc_id) REFERENCES odc_devices(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO olt_devices (code, name, brand, location, status, note)
SELECT 'DEFAULT-OLT', 'Default OLT', 'Default', 'Default', 'online', 'Data default. Ubah dari menu Infrastruktur Fiber > OLT.'
WHERE NOT EXISTS (SELECT 1 FROM olt_devices WHERE code='DEFAULT-OLT');

INSERT INTO odc_devices (olt_id, code, name, address, status, note)
SELECT (SELECT id FROM olt_devices WHERE code='DEFAULT-OLT' LIMIT 1), 'DEFAULT-ODC', 'Default ODC', 'Default', 'active', 'Data default. Ubah dari menu Infrastruktur Fiber > ODC.'
WHERE NOT EXISTS (SELECT 1 FROM odc_devices WHERE code='DEFAULT-ODC');

INSERT INTO odp_devices (odc_id, code, name, address, total_ports, used_ports, status, note)
SELECT (SELECT id FROM odc_devices WHERE code='DEFAULT-ODC' LIMIT 1), 'DEFAULT-ODP', 'Default ODP', 'Default', 8, 0, 'active', 'Data default jika ODP belum dibuat.'
WHERE NOT EXISTS (SELECT 1 FROM odp_devices WHERE code='DEFAULT-ODP');

ALTER TABLE customers ADD COLUMN IF NOT EXISTS odp_id INT NULL AFTER subscription_profile_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS installation_latitude DECIMAL(10,7) NULL AFTER address;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS installation_longitude DECIMAL(10,7) NULL AFTER installation_latitude;
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_odp_id (odp_id);

UPDATE customers SET odp_id = (SELECT id FROM odp_devices WHERE code='DEFAULT-ODP' LIMIT 1) WHERE odp_id IS NULL;

CREATE TABLE IF NOT EXISTS customer_tickets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ticket_no VARCHAR(80) NOT NULL UNIQUE,
    customer_id INT NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'Keluhan',
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    status ENUM('open','process','pending','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_to INT NULL,
    opened_by INT NULL,
    resolved_at DATETIME NULL,
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ticket_customer (customer_id),
    INDEX idx_ticket_status (status),
    INDEX idx_ticket_assigned (assigned_to),
    CONSTRAINT fk_ticket_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ticket_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ticket_opened FOREIGN KEY (opened_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- KINET MikroTik Admin Module - Update v18 Laporan Keuangan, Kalkulator, Jaringan, Voucher Online
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS finance_transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    direction ENUM('income','expense') NOT NULL,
    type ENUM('kewajiban','pembayaran','operasional','modal','gaji','lainnya') NOT NULL DEFAULT 'lainnya',
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    payment_method VARCHAR(80) NULL,
    reference VARCHAR(160) NULL,
    note TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_finance_date (transaction_date),
    INDEX idx_finance_direction (direction),
    INDEX idx_finance_type (type),
    CONSTRAINT fk_finance_created_by FOREIGN KEY (created_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 voucher_online_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(80) NOT NULL UNIQUE,
    voucher_profile_id INT NOT NULL,
    customer_name VARCHAR(160) NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(160) NULL,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(80) NOT NULL DEFAULT 'manual',
    payment_provider VARCHAR(40) NOT NULL DEFAULT 'manual',
    payment_status ENUM('pending','paid','failed','expired','cancelled') NOT NULL DEFAULT 'pending',
    payment_reference VARCHAR(160) NULL,
    payment_notified_at DATETIME NULL,
    paid_at DATETIME NULL,
    payment_url TEXT NULL,
    midtrans_order_id VARCHAR(80) NULL,
    midtrans_snap_token VARCHAR(160) NULL,
    midtrans_redirect_url TEXT NULL,
    voucher_id BIGINT NULL,
    voucher_username VARCHAR(120) NULL,
    voucher_password VARCHAR(120) NULL,
    wa_sent_at DATETIME NULL,
    note TEXT NULL,
    gateway_payload LONGTEXT NULL,
    provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment',
    provision_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_voucher_online_phone (phone),
    INDEX idx_voucher_online_status (payment_status),
    INDEX idx_voucher_online_provider (payment_provider),
    INDEX idx_voucher_online_midtrans_order (midtrans_order_id),
    INDEX idx_voucher_online_profile (voucher_profile_id),
    CONSTRAINT fk_voucher_online_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_voucher_online_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER status;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_sort INT NOT NULL DEFAULT 0 AFTER online_enabled;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_badge VARCHAR(80) NULL AFTER online_sort;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_description TEXT NULL AFTER online_badge;

INSERT INTO settings (setting_key, setting_value) VALUES
('voucher_online_enabled', '0'),
('voucher_online_auto_payment', '0'),
('payment_notification_secret', ''),
('voucher_online_wait_gateway_note', 'Voucher online hanya diproses setelah payment gateway mengirim notifikasi paid ke endpoint callback.'),
('voucher_online_auto_send_wa', '1'),
('voucher_online_title', 'Beli Voucher WiFi Online'),
('voucher_online_subtitle', 'Pilih paket voucher, bayar, lalu kode voucher dikirim otomatis via WhatsApp.'),
('voucher_online_wa_template', 'Halo {{customer_name}}, voucher WiFi Anda sudah aktif.\n\nVoucher: {{voucher_username}}\nPassword: {{voucher_password}}\nPaket: {{profile_name}}\nHarga: {{amount}}\nLogin: {{hotspot_login_url}}\n\nTerima kasih.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
-- KINET MikroTik Admin Module - Update v21 Voucher Online Generate On Paid
INSERT INTO settings (setting_key, setting_value) VALUES
('voucher_online_generate_mode', 'generate_on_paid'),
('voucher_online_prefix', 'ON'),
('voucher_online_format', '123ABC'),
('voucher_online_code_length', '6'),
('voucher_online_wait_gateway_note', 'Order pending. Voucher baru digenerate otomatis ke MikroTik setelah payment gateway mengirim notifikasi paid. Tidak mengambil stok voucher.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- KINET MikroTik Admin Module - Update v22 Voucher Online Midtrans Snap Otomatis
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS payment_provider VARCHAR(40) NOT NULL DEFAULT 'manual' AFTER payment_method;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS midtrans_order_id VARCHAR(80) NULL AFTER payment_url;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS midtrans_snap_token VARCHAR(160) NULL AFTER midtrans_order_id;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS midtrans_redirect_url TEXT NULL AFTER midtrans_snap_token;
ALTER TABLE voucher_online_orders ADD INDEX IF NOT EXISTS idx_voucher_online_provider (payment_provider);
ALTER TABLE voucher_online_orders ADD INDEX IF NOT EXISTS idx_voucher_online_midtrans_order (midtrans_order_id);
INSERT INTO settings (setting_key, setting_value) VALUES
('payment_midtrans_snap_expiry_minutes', '60'),
('voucher_online_payment_mode', 'midtrans_snap_on_paid'),
('voucher_online_wait_gateway_note', 'Voucher online memakai Midtrans Snap. Order hanya pending saat checkout; voucher baru digenerate setelah webhook Midtrans settlement/capture valid.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
-- KINET MikroTik Admin Module - Update v24 Landing Pendaftaran + Onboarding PSB
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS psb_onboardings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    registration_no VARCHAR(80) NOT NULL UNIQUE,
    source VARCHAR(60) NOT NULL DEFAULT 'landing',
    name VARCHAR(160) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(160) NULL,
    address TEXT NOT NULL,
    area VARCHAR(160) NULL,
    subscription_profile_id INT NULL,
    package_name VARCHAR(160) NULL,
    service_type VARCHAR(40) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    status ENUM('new','contacted','survey','approved','rejected','converted') NOT NULL DEFAULT 'new',
    note TEXT NULL,
    admin_note TEXT NULL,
    assigned_to INT NULL,
    converted_customer_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_psb_status (status),
    INDEX idx_psb_phone (phone),
    INDEX idx_psb_profile (subscription_profile_id),
    INDEX idx_psb_created (created_at),
    CONSTRAINT fk_psb_profile FOREIGN KEY (subscription_profile_id) REFERENCES subscription_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_psb_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_psb_converted_customer FOREIGN KEY (converted_customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key, setting_value) VALUES
('landing_registration_enabled', '1'),
('landing_registration_title', 'Daftar Pemasangan Baru'),
('landing_registration_subtitle', 'Isi data singkat. Tim kami akan menghubungi Anda untuk cek coverage dan jadwal pemasangan.'),
('landing_registration_success_message', 'Pendaftaran berhasil masuk. Tim kami akan menghubungi Anda melalui WhatsApp.'),
('landing_chat_button_text', 'Chat Sekarang')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
-- KINET MikroTik Admin Module - Update v31 Reseller Portal + Permissions

CREATE TABLE IF NOT EXISTS reseller_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_reseller_module (reseller_id, module_key),
    INDEX idx_reseller_perm_module (module_key),
    CONSTRAINT fk_reseller_perm_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_reseller_perm_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO reseller_module_permissions (reseller_id, module_key, can_view, can_create, can_edit, can_delete, can_action)
SELECT u.id, x.module_key, x.can_view, x.can_create, x.can_edit, x.can_delete, x.can_action
FROM users u
JOIN roles r ON r.id=u.role_id
JOIN (
    SELECT 'customers' module_key, 1 can_view, 1 can_create, 1 can_edit, 0 can_delete, 0 can_action
    UNION ALL SELECT 'invoices', 1, 0, 0, 0, 1
    UNION ALL SELECT 'confirm_payment', 0, 0, 0, 0, 0
    UNION ALL SELECT 'vouchers', 1, 1, 0, 0, 1
    UNION ALL SELECT 'mikrotik', 1, 0, 0, 0, 0
    UNION ALL SELECT 'reports', 1, 0, 0, 0, 0
) x
WHERE r.code='reseller'
ON DUPLICATE KEY UPDATE module_key=VALUES(module_key);

-- KINET MikroTik Admin Module - Update v32 Customer Portal

-- KINET MikroTik Admin Module - Update v32 Customer Portal

INSERT IGNORE INTO roles (id, code, name, description) VALUES
(6, 'pelanggan', 'Pelanggan', 'Portal pelanggan untuk melihat status layanan, tagihan, pembayaran, dan ticket keluhan');

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at DATETIME NULL AFTER status;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS portal_user_id INT NULL AFTER reseller_id;
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_portal_user_id (portal_user_id);

INSERT INTO settings (setting_key, setting_value) VALUES
('customer_portal_enabled', '1'),
('customer_portal_login_template', 'Halo {{customer_name}}, akun portal pelanggan Anda sudah aktif.\n\nURL Login: {{login_url}}\nUsername: {{username}}\nPassword: {{password}}\n\nSilakan login untuk melihat tagihan dan membuat keluhan.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- KINET MikroTik Admin Module - Update v37 Midtrans Invoice Payment
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS payment_provider VARCHAR(40) NULL AFTER payment_method;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS midtrans_order_id VARCHAR(100) NULL AFTER payment_url;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS midtrans_snap_token VARCHAR(160) NULL AFTER midtrans_order_id;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS midtrans_redirect_url TEXT NULL AFTER midtrans_snap_token;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS payment_notified_at DATETIME NULL AFTER midtrans_redirect_url;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS gateway_payload LONGTEXT NULL AFTER payment_notified_at;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS gateway_note TEXT NULL AFTER gateway_payload;
ALTER TABLE invoices ADD INDEX IF NOT EXISTS idx_invoices_midtrans_order (midtrans_order_id);
ALTER TABLE invoices ADD INDEX IF NOT EXISTS idx_invoices_payment_provider (payment_provider);
INSERT INTO settings (setting_key, setting_value) VALUES
('payment_invoice_midtrans_enabled', '1'),
('payment_invoice_midtrans_mode', 'snap_webhook_auto_paid'),
('payment_invoice_note', 'Invoice pelanggan memakai Midtrans Snap. Invoice otomatis lunas hanya setelah webhook Midtrans settlement/capture valid diterima.'),
('wa_template_invoice', 'Halo {{customer_name}}, tagihan internet periode {{period}} sebesar {{total}} jatuh tempo {{due_date}}.\n\nBayar online via Midtrans:\n{{payment_url}}\n\nTanggal isolir: {{isolir_date}}')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- KINET MikroTik Admin Module - Update v38 Agen Portal
-- Jalankan sekali setelah replace file v38.

ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS buyer_name VARCHAR(160) NULL AFTER sold_at;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS buyer_phone VARCHAR(60) NULL AFTER buyer_name;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS sold_by INT NULL AFTER buyer_phone;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS sale_note TEXT NULL AFTER sold_by;
ALTER TABLE vouchers ADD INDEX IF NOT EXISTS idx_vouchers_agent_portal (agent_id, status, sold_at);

CREATE TABLE IF NOT EXISTS agent_settlements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NOT NULL,
    type ENUM('setoran','topup','penyesuaian') NOT NULL DEFAULT 'setoran',
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(80) NULL,
    reference_no VARCHAR(120) NULL,
    note TEXT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    created_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agent_settlements_agent (agent_id, status, created_at),
    INDEX idx_agent_settlements_status (status),
    CONSTRAINT fk_agent_settlements_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_agent_settlements_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_agent_settlements_confirmed FOREIGN KEY (confirmed_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key, setting_value) VALUES
('agent_voucher_wa_template', 'Voucher WiFi KINET\n\nPaket: {{profile}}\nHarga: {{price}}\nUsername: {{username}}\nPassword: {{password}}\nLogin: {{login_url}}\n\nTerima kasih.'),
('agent_portal_enabled', '1')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
-- KINET MikroTik Admin Module - Update v40 Sales + Teknisi + Centralized Access
-- Jalankan sekali setelah replace file v40.

CREATE TABLE IF NOT EXISTS role_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_code VARCHAR(30) NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_module (role_code, module_key),
    INDEX idx_role_module_role (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_module (user_id, module_key),
    INDEX idx_user_module_user (user_id),
    CONSTRAINT fk_user_module_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acs_devices (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    router_id INT NULL,
    device_name VARCHAR(160) NOT NULL,
    serial_number VARCHAR(160) NULL,
    mac_address VARCHAR(80) NULL,
    ip_address VARCHAR(80) NULL,
    ont_model VARCHAR(160) NULL,
    ssid VARCHAR(160) NULL,
    status ENUM('online','offline','maintenance','unknown') NOT NULL DEFAULT 'unknown',
    last_seen_at DATETIME NULL,
    note TEXT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_acs_customer (customer_id),
    INDEX idx_acs_router (router_id),
    INDEX idx_acs_status (status),
    CONSTRAINT fk_acs_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_acs_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_acs_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS coverage_status ENUM('unchecked','covered','not_covered','need_survey') NOT NULL DEFAULT 'unchecked' AFTER longitude;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS nearest_odp_id INT NULL AFTER coverage_status;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS coverage_distance_m DECIMAL(12,2) NULL AFTER nearest_odp_id;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS sales_id INT NULL AFTER assigned_to;
ALTER TABLE psb_onboardings ADD INDEX IF NOT EXISTS idx_psb_sales_id (sales_id);

INSERT INTO settings (setting_key, setting_value) VALUES
('coverage_default_radius_m', '300'),
('sales_portal_enabled', '1'),
('teknisi_portal_enabled', '1'),
('central_access_enabled', '1')
ON DUPLICATE KEY UPDATE setting_key = setting_key;