-- KINET MikroTik Admin Module - Update v5 Customer Popup + MikroTik Provisioning
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL UNIQUE,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO areas (name, description, status)
SELECT 'Default', 'Wilayah default jika data wilayah belum dibuat.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM areas WHERE name='Default');

CREATE TABLE IF NOT EXISTS subscription_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    service_type ENUM('pppoe','hotspot','static') NOT NULL DEFAULT 'pppoe',
    mikrotik_profile VARCHAR(160) NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    note TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscription_profiles_service (service_type),
    INDEX idx_subscription_profiles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default PPPoE 10Mbps', 'pppoe', '10Mbps', 0, 'Ubah harga/profile sesuai profile MikroTik asli.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default PPPoE 10Mbps');

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default Hotspot 1Mbps', 'hotspot', '1Mbps', 0, 'Ubah harga/profile sesuai profile MikroTik asli.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default Hotspot 1Mbps');

INSERT INTO subscription_profiles (name, service_type, mikrotik_profile, price, note, status)
SELECT 'Default Static', 'static', '', 0, 'Paket static/manual tanpa command MikroTik otomatis.', 'active'
WHERE NOT EXISTS (SELECT 1 FROM subscription_profiles WHERE name='Default Static');

ALTER TABLE customers MODIFY service_type ENUM('pppoe','hotspot','static','manual') NOT NULL DEFAULT 'pppoe';
ALTER TABLE customers ADD COLUMN IF NOT EXISTS area_id INT NULL AFTER reseller_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS subscription_profile_id INT NULL AFTER area_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ktp_photo VARCHAR(255) NULL AFTER activated_at;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_password VARCHAR(160) NULL AFTER mikrotik_username;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_create_mode ENUM('existing','auto','manual_create','none') NOT NULL DEFAULT 'none' AFTER mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS extra_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER package_price;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS fee_note VARCHAR(255) NULL AFTER extra_fee;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER fee_note;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS discount_note VARCHAR(255) NULL AFTER discount_amount;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_sync_status ENUM('pending','success','failed','linked','skipped') NOT NULL DEFAULT 'skipped' AFTER note;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS mikrotik_sync_message TEXT NULL AFTER mikrotik_sync_status;
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_area_id (area_id);
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_subscription_profile_id (subscription_profile_id);

UPDATE customers SET service_type='static' WHERE service_type='manual';
UPDATE customers SET area='Default' WHERE area IS NULL OR area='';
UPDATE customers SET mikrotik_sync_status='linked' WHERE mikrotik_sync_status='skipped' AND mikrotik_username IS NOT NULL AND mikrotik_username<>'';