-- KINET MikroTik Admin Module - Update v20 Voucher Online Payment Gateway Callback
-- Mode aman: voucher online hanya diproses setelah payment gateway mengirim notifikasi paid.

ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS payment_notified_at DATETIME NULL AFTER payment_reference;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS paid_at DATETIME NULL AFTER payment_notified_at;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS gateway_payload LONGTEXT NULL AFTER note;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS provision_status ENUM('waiting_payment','pending','success','failed') NOT NULL DEFAULT 'waiting_payment' AFTER gateway_payload;
ALTER TABLE voucher_online_orders ADD COLUMN IF NOT EXISTS provision_message TEXT NULL AFTER provision_status;

UPDATE voucher_online_orders
SET provision_status = CASE
    WHEN payment_status='paid' AND voucher_id IS NOT NULL THEN 'success'
    WHEN payment_status='paid' AND voucher_id IS NULL THEN 'failed'
    ELSE 'waiting_payment'
END
WHERE provision_status IS NULL OR provision_status='pending';

INSERT INTO settings (setting_key, setting_value) VALUES
('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.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

UPDATE settings SET setting_value='0' WHERE setting_key='voucher_online_auto_payment';
