-- KINET MikroTik Admin Module - Update v6 Profil Berlangganan + Profile Isolir
-- Jalankan sekali setelah replace file update.

ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS router_id INT NULL AFTER id;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS speed_mbps DECIMAL(8,2) NOT NULL DEFAULT 0 AFTER mikrotik_profile;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS ppn_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS ppn_percent DECIMAL(5,2) NOT NULL DEFAULT 11.00 AFTER ppn_enabled;
ALTER TABLE subscription_profiles ADD COLUMN IF NOT EXISTS isolir_mikrotik_profile VARCHAR(160) NULL AFTER ppn_percent;
ALTER TABLE subscription_profiles ADD INDEX IF NOT EXISTS idx_subscription_profiles_router (router_id);

ALTER TABLE customers ADD COLUMN IF NOT EXISTS current_mikrotik_profile VARCHAR(160) NULL AFTER mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS last_mikrotik_profile VARCHAR(160) NULL AFTER current_mikrotik_profile;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ppn_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER package_price;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS ppn_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER ppn_enabled;

UPDATE subscription_profiles SET ppn_percent=11.00 WHERE ppn_percent IS NULL OR ppn_percent=0;
UPDATE customers c
LEFT JOIN subscription_profiles sp ON sp.id=c.subscription_profile_id
SET c.package_name = COALESCE(sp.name, c.package_name),
    c.package_price = COALESCE(sp.price, c.package_price),
    c.mikrotik_profile = COALESCE(NULLIF(sp.mikrotik_profile,''), c.mikrotik_profile),
    c.current_mikrotik_profile = COALESCE(c.current_mikrotik_profile, c.mikrotik_profile),
    c.ppn_enabled = COALESCE(sp.ppn_enabled, c.ppn_enabled),
    c.ppn_amount = CASE WHEN COALESCE(sp.ppn_enabled, c.ppn_enabled)=1 THEN ROUND(COALESCE(sp.price, c.package_price) * COALESCE(sp.ppn_percent, 11.00) / 100, 0) ELSE 0 END
WHERE c.subscription_profile_id IS NOT NULL;