-- KINET MikroTik Admin Module - Update v18 Laporan Keuangan, Kalkulator, Jaringan, Voucher Online
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS finance_transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    direction ENUM('income','expense') NOT NULL,
    type ENUM('kewajiban','pembayaran','operasional','modal','gaji','lainnya') NOT NULL DEFAULT 'lainnya',
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    payment_method VARCHAR(80) NULL,
    reference VARCHAR(160) NULL,
    note TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_finance_date (transaction_date),
    INDEX idx_finance_direction (direction),
    INDEX idx_finance_type (type),
    CONSTRAINT fk_finance_created_by FOREIGN KEY (created_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 voucher_online_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(80) NOT NULL UNIQUE,
    voucher_profile_id INT NOT NULL,
    customer_name VARCHAR(160) NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(160) NULL,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(80) NOT NULL DEFAULT 'manual',
    payment_status ENUM('pending','paid','failed','expired','cancelled') NOT NULL DEFAULT 'pending',
    payment_reference VARCHAR(160) NULL,
    payment_url TEXT NULL,
    voucher_id BIGINT NULL,
    voucher_username VARCHAR(120) NULL,
    voucher_password VARCHAR(120) NULL,
    wa_sent_at DATETIME NULL,
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_voucher_online_phone (phone),
    INDEX idx_voucher_online_status (payment_status),
    INDEX idx_voucher_online_profile (voucher_profile_id),
    CONSTRAINT fk_voucher_online_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_voucher_online_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER status;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_sort INT NOT NULL DEFAULT 0 AFTER online_enabled;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_badge VARCHAR(80) NULL AFTER online_sort;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS online_description TEXT NULL AFTER online_badge;

INSERT INTO settings (setting_key, setting_value) VALUES
('voucher_online_enabled', '0'),
('voucher_online_auto_payment', '0'),
('voucher_online_auto_send_wa', '1'),
('voucher_online_title', 'Beli Voucher WiFi Online'),
('voucher_online_subtitle', 'Pilih paket voucher, bayar, lalu kode voucher dikirim otomatis via WhatsApp.'),
('voucher_online_wa_template', 'Halo {{customer_name}}, voucher WiFi Anda sudah aktif.\n\nVoucher: {{voucher_username}}\nPassword: {{voucher_password}}\nPaket: {{profile_name}}\nHarga: {{amount}}\nLogin: {{hotspot_login_url}}\n\nTerima kasih.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;