-- 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', '<div style="width:190px;border:1px dashed #111;border-radius:10px;padding:10px;font-family:Arial;text-align:center;background:#fff;color:#111"><b style="font-size:16px">KINET WIFI</b><br><small>Internet Cerdas Pedesaan</small><hr><div style="font-size:13px">Profile: {{profile}}</div><div style="font-size:22px;font-weight:900;margin:8px 0">{{username}}</div><div>Password: <b>{{password}}</b></div><div>Harga: <b>{{price}}</b></div><small>{{validity}}</small></div>', 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;
