<?php
// update_user_bandwidth.php — sync OpenVPN user traffic to panel
error_reporting(0);
date_default_timezone_set('Asia/Manila');

// --- Database connection ---
$servername = "185.61.137.174";
$username   = "firenetv_kopiko";
$password   = "firenetv_kopiko";
$dbname     = "firenetv_kopiko";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("DB Connection failed: " . $conn->connect_error);
}

// --- Path to your OpenVPN status log ---
$statusFile = "/etc/openvpn/server/tcpserver.log";
if (!file_exists($statusFile)) {
    die("❌ OpenVPN status log not found: $statusFile");
}

$handle = fopen($statusFile, "r");
if (!$handle) {
    die("❌ Cannot open OpenVPN status log.");
}

// --- Parse each user line ---
while (($line = fgets($handle)) !== false) {
    // Typical format:
    // username,10.8.0.2,104857600,52428800,2025-11-07 04:11:21
    if (preg_match('/^([^,]+),([^,]+),([0-9]+),([0-9]+),/', trim($line), $m)) {
        $username = $conn->real_escape_string($m[1]);
        $ipaddr = $conn->real_escape_string($m[2]);
        $bytes_received = (int)$m[3];
        $bytes_sent = (int)$m[4];
        $server_ip = '127.0.0.1'; // or replace with your panel VPS IP

        // ✅ Update if exists, else insert
        $check = $conn->query("SELECT id FROM bandwidth_logs WHERE username='$username' ORDER BY id DESC LIMIT 1");
        if ($check && $check->num_rows > 0) {
            $conn->query("
                UPDATE bandwidth_logs 
                SET bytes_received=$bytes_received, bytes_sent=$bytes_sent, time=NOW()
                WHERE username='$username'
            ");
        } else {
            $conn->query("
                INSERT INTO bandwidth_logs (username, server_ip, bytes_received, bytes_sent, time)
                VALUES ('$username', '$server_ip', $bytes_received, $bytes_sent, NOW())
            ");
        }
    }
}

fclose($handle);
$conn->close();
echo "✅ User bandwidth updated successfully at " . date('Y-m-d H:i:s') . "\n";
?>
