<?php 
include "/www/wwwroot/bilspro/config/connect.php";

// Set Waktu & Limit
date_default_timezone_set('Asia/Jakarta');
$time = date('Y-m-d H:i:s');

// 1. Ambil data dari tabel setting_mikrotik
$stmt = $pdo->prepare("SELECT id, pppa, id_data_server, nama FROM setting_mikrotik WHERE pppa != '' AND status = 'Connected'");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$rows) {
    echo "[" . $time . "] No data found.\n";
    exit;
}

// 2. Buat Tabel Temporary (Di Memori) untuk menampung data update
// Ini jauh lebih cepat daripada query CASE WHEN
$pdo->exec("CREATE TEMPORARY TABLE temp_pelanggan_update (
    id_pel INT PRIMARY KEY, 
    st VARCHAR(5), 
    ip VARCHAR(20), 
    mac VARCHAR(40)
)");

foreach ($rows as $row) {
    $id_mikrotik = $row['id'];
    $pppa_raw = $row['pppa'];
    
    // Hilangkan karakter terakhir jika itu pemisah (seperti pipe atau koma)
    $pppa = rtrim($pppa_raw, "|");
    $entries = explode('|', $pppa);

    // Parsing data Mikrotik ke array agar mudah dicari
    $mikrotik_online = [];
    foreach ($entries as $entry) {
        $data_part = explode('_', $entry);
        if (count($data_part) >= 3) {
            $mikrotik_online[$data_part[0]] = [
                'ip' => $data_part[1],
                'mac' => $data_part[2]
            ];
        }
    }

    // Ambil semua akun pelanggan yang terikat ke Mikrotik ini
    $stmtPel = $pdo->prepare("SELECT id, akun FROM pelanggan WHERE id_setting_mikrotik = :id");
    $stmtPel->execute([':id' => $id_mikrotik]);
    $pelanggan = $stmtPel->fetchAll(PDO::FETCH_ASSOC);

    $updates_on = [];
    $updates_off = [];
    $data_akun_db = [];

    foreach ($pelanggan as $p) {
        $akun = $p['akun'];
        $id_p = $p['id'];
        $data_akun_db[] = ['akun' => $akun, 'id' => $id_p];

        if (isset($mikrotik_online[$akun])) {
            // Pelanggan Online
            $updates_on[] = [
                'id' => $id_p,
                'ip' => $mikrotik_online[$akun]['ip'],
                'mac' => $mikrotik_online[$akun]['mac']
            ];
        } else {
            // Pelanggan Offline
            $updates_off[] = $id_p;
        }
    }

    // --- PROSES UPDATE DB ---
    $pdo->beginTransaction();
    try {
        // A. Update ppp_unmanage di setting_mikrotik
        $akun_terdaftar = array_column($data_akun_db, 'akun');
        $unmanaged = [];
        foreach ($mikrotik_online as $akun_mik => $val) {
            if (!in_array($akun_mik, $akun_terdaftar)) {
                $unmanaged[] = [
                    'akun' => $akun_mik,
                    'ip_address' => $val['ip'],
                    'mac_address' => $val['mac']
                ];
            }
        }
        $ppp_unmanage_json = json_encode(['akun_unmanage' => $unmanaged]);
        $pdo->prepare("UPDATE setting_mikrotik SET ppp_unmanage = :json WHERE id = :id")
            ->execute([':json' => $ppp_unmanage_json, ':id' => $id_mikrotik]);

        // B. Masukkan data 'On' ke Temporary Table
        if (!empty($updates_on)) {
            $stmtTemp = $pdo->prepare("INSERT INTO temp_pelanggan_update (id_pel, st, ip, mac) VALUES (?, 'On', ?, ?)");
            foreach ($updates_on as $up) {
                $stmtTemp->execute([$up['id'], $up['ip'], $up['mac']]);
            }

            // Jalankan Update Masal via INNER JOIN (Sangat Cepat)
            $pdo->exec("UPDATE pelanggan p 
                        INNER JOIN temp_pelanggan_update t ON p.id = t.id_pel 
                        SET p.status_koneksi = t.st, 
                            p.ip_address = t.ip, 
                            p.mac_address = t.mac, 
                            p.time_update = '$time'");
            
            // Kosongkan temp table untuk mikrotik berikutnya dalam loop
            $pdo->exec("TRUNCATE TABLE temp_pelanggan_update");
        }

        // C. Update status 'Off'
        if (!empty($updates_off)) {
            $ids_off = implode(',', array_map('intval', $updates_off));
            $pdo->exec("UPDATE pelanggan SET status_koneksi = 'Off' WHERE id IN ($ids_off)");
        }

        $pdo->commit();
        echo "[" . date('H:i:s') . "] {$row['nama']} success. (" . count($updates_on) . " On, " . count($updates_off) . " Off)\n";

    } catch (Exception $e) {
        $pdo->rollBack();
        echo "[" . date('H:i:s') . "] Error at {$row['nama']}: " . $e->getMessage() . "\n";
    }
}

// Hapus tabel temporary di akhir script
$pdo->exec("DROP TEMPORARY TABLE IF EXISTS temp_pelanggan_update");
?>