-- KINET MikroTik Admin Module - Update v4 H-7 Isolir + Konfirmasi Bayar
-- Jalankan sekali setelah replace file update.

ALTER TABLE invoices
    ADD COLUMN IF NOT EXISTS isolir_date DATE NULL AFTER due_date,
    ADD COLUMN IF NOT EXISTS h7_sent_at DATETIME NULL AFTER sent_at;

CREATE INDEX IF NOT EXISTS idx_invoices_isolir ON invoices (isolir_date);

UPDATE invoices
SET isolir_date = DATE_ADD(due_date, INTERVAL 7 DAY)
WHERE isolir_date IS NULL;

INSERT INTO settings (setting_key, setting_value) VALUES
('auto_invoice_reminder_before_isolir_days','7')
ON DUPLICATE KEY UPDATE setting_value = setting_value;

UPDATE settings
SET setting_value = '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.'
WHERE setting_key = 'wa_template_invoice'
  AND (setting_value IS NULL OR setting_value NOT LIKE '%{{isolir_date}}%');