-- KINET MikroTik Admin Module - Update v37 Midtrans Invoice Payment
-- Invoice pelanggan sekarang bisa dibayar via Midtrans Snap dan otomatis lunas dari webhook.

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;