-- 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);