<?php

/**
 * PHP Mikrotik Billing (https://github.com/hotspotbilling/phpnuxbill/)
 *
 * Data Usage Plugin
 * Tracks Hotspot + PPPoE bandwidth usage per user
 * Method: Delta/Differential Polling with 32-bit Counter Wrap Detection
 *
 * Installation:
 *   1. data_usage.php        → system/plugin/
 *   2. ui/data_usage.tpl     → system/plugin/ui/
 *   3. ui/data_usage_user.tpl → system/plugin/ui/
 **/

// ─────────────────────────────────────────────────────────
// Register menu
// ─────────────────────────────────────────────────────────
register_menu(
    'Data Usage',
    true,
    'data_usage',
    'AFTER_MESSAGE',
    'ion ion-ios-analytics',
    '',
    ''
);

// ─────────────────────────────────────────────────────────
// Register cron hook
// ─────────────────────────────────────────────────────────
register_hook('cronjob', 'du_cron');

// ─────────────────────────────────────────────────────────
// Register routes
// ─────────────────────────────────────────────────────────
$routes['data_usage']         = 'data_usage';
$routes['data_usage_user']    = 'data_usage_user';
$routes['data_usage_csv']     = 'data_usage_csv';

// ─────────────────────────────────────────────────────────
// MikroTik API Client (socket-based)
// ─────────────────────────────────────────────────────────
class DuMikrotikAPI
{
    private $socket = null;
    private string $host;
    private int $port;
    private int $timeout;

    public function __construct(string $host, int $port = 8728, int $timeout = 5)
    {
        $this->host    = $host;
        $this->port    = $port;
        $this->timeout = $timeout;
    }

    public function connect(string $username, string $password): void
    {
        $this->socket = @fsockopen($this->host, $this->port, $errno, $errstr, $this->timeout);
        if (!$this->socket) {
            throw new RuntimeException("Cannot connect to {$this->host}:{$this->port} — $errstr ($errno)");
        }
        stream_set_timeout($this->socket, $this->timeout);

        // Try plain login first (RouterOS v7+)
        $resp = $this->communicate(['/login', '=name=' . $username, '=password=' . $password]);

        // RouterOS v6 challenge-response
        foreach ($resp as $line) {
            if (strpos($line, '=ret=') === 0) {
                $challenge = substr($line, 5);
                $md5       = md5(chr(0) . $password . pack('H*', $challenge));
                $resp      = $this->communicate(['/login', '=name=' . $username, '=response=00' . $md5]);
                break;
            }
        }

        foreach ($resp as $line) {
            if ($line === '!done') return;
            if (strpos($line, '!trap') === 0) throw new RuntimeException('RouterOS login failed');
        }
    }

    public function disconnect(): void
    {
        if ($this->socket) {
            try { $this->communicate(['/quit']); } catch (Exception $e) {}
            fclose($this->socket);
            $this->socket = null;
        }
    }

    // Hotspot active users — bytes-in/bytes-out
    public function getHotspotActive(): array
    {
        return $this->parse($this->communicate(['/ip/hotspot/active/print']));
    }

    // PPPoE active sessions
    public function getPPPoEActive(): array
    {
        return $this->parse($this->communicate(['/ppp/active/print']));
    }

    // Interface list — for PPPoE byte counters
    public function getInterfaces(): array
    {
        return $this->parse($this->communicate(['/interface/print']));
    }

    // Parse raw response into array of assoc arrays
    public function parse(array $raw): array
    {
        $result  = [];
        $current = [];
        foreach ($raw as $line) {
            if ($line === '!re') {
                if (!empty($current)) { $result[] = $current; $current = []; }
            } elseif ($line === '!done') {
                if (!empty($current)) $result[] = $current;
                break;
            } elseif (strpos($line, '=') === 0) {
                $line  = substr($line, 1);
                $pos   = strpos($line, '=');
                if ($pos !== false) {
                    $current[substr($line, 0, $pos)] = substr($line, $pos + 1);
                }
            }
        }
        // fallback if no !re separators
        if (empty($result) && !empty($current)) $result[] = $current;
        return $result;
    }

    private function communicate(array $words): array
    {
        foreach ($words as $w) $this->writeWord($w);
        $this->writeWord('');
        return $this->readResponse();
    }

    private function writeWord(string $word): void
    {
        $len = strlen($word);
        if      ($len < 0x80)       $this->write(chr($len));
        elseif  ($len < 0x4000)   { $len |= 0x8000;     $this->write(chr(($len >> 8) & 0xFF) . chr($len & 0xFF)); }
        elseif  ($len < 0x200000) { $len |= 0xC00000;   $this->write(chr(($len >> 16) & 0xFF) . chr(($len >> 8) & 0xFF) . chr($len & 0xFF)); }
        else                      { $len |= 0xE0000000; $this->write(chr(($len >> 24) & 0xFF) . chr(($len >> 16) & 0xFF) . chr(($len >> 8) & 0xFF) . chr($len & 0xFF)); }
        if (strlen($word) > 0) $this->write($word);
    }

    private function write(string $data): void
    {
        if (!fwrite($this->socket, $data)) throw new RuntimeException('Socket write failed');
    }

    private function readResponse(): array
    {
        $response = [];
        $sentence = [];
        while (true) {
            $word = $this->readWord();
            if ($word === '') {
                if (!empty($sentence)) { $response = array_merge($response, $sentence); $sentence = []; }
                $done = false;
                foreach ($response as $r) {
                    if (in_array($r, ['!done', '!trap', '!fatal'])) { $done = true; break; }
                }
                if ($done) break;
            } else {
                $sentence[] = $word;
            }
        }
        return $response;
    }

    private function readWord(): string
    {
        $len = $this->readLength();
        if ($len === 0) return '';
        return $this->read($len);
    }

    private function readLength(): int
    {
        $b = ord($this->read(1));
        if (($b & 0x80) === 0) return $b;
        if (($b & 0xC0) === 0x80) { $b2 = ord($this->read(1)); return (($b & ~0x80) << 8) | $b2; }
        if (($b & 0xE0) === 0xC0) { $b2 = ord($this->read(1)); $b3 = ord($this->read(1)); return (($b & ~0xC0) << 16) | ($b2 << 8) | $b3; }
        $b2 = ord($this->read(1)); $b3 = ord($this->read(1)); $b4 = ord($this->read(1));
        return (($b & ~0xE0) << 24) | ($b2 << 16) | ($b3 << 8) | $b4;
    }

    private function read(int $length): string
    {
        $data = '';
        while (strlen($data) < $length) {
            $chunk = fread($this->socket, $length - strlen($data));
            if ($chunk === false || $chunk === '') throw new RuntimeException('Socket read failed');
            $data .= $chunk;
        }
        return $data;
    }
}

// ─────────────────────────────────────────────────────────
// Helper: Parse MikroTik uptime string to seconds
// e.g. "1d2h3m4s" → 93784
// ─────────────────────────────────────────────────────────
function du_uptimeToSeconds(string $uptime): int
{
    $seconds = 0;
    if (preg_match('/(\d+)d/', $uptime, $m)) $seconds += (int)$m[1] * 86400;
    if (preg_match('/(\d+)h/', $uptime, $m)) $seconds += (int)$m[1] * 3600;
    if (preg_match('/(\d+)m/', $uptime, $m)) $seconds += (int)$m[1] * 60;
    if (preg_match('/(\d+)s/', $uptime, $m)) $seconds += (int)$m[1];
    return $seconds;
}

// ─────────────────────────────────────────────────────────
// Helper: TCP reachability check
// ─────────────────────────────────────────────────────────
function du_isReachable(string $host, int $port, int $timeout = 2): bool
{
    $sock = @fsockopen($host, $port, $errno, $errstr, $timeout);
    if ($sock) { fclose($sock); return true; }
    return false;
}

// ─────────────────────────────────────────────────────────
// Helper: Connect to a tbl_routers row
// ─────────────────────────────────────────────────────────
function du_connect($router): DuMikrotikAPI
{
    $parts = explode(':', $router['ip_address']);
    $host  = $parts[0];
    $port  = isset($parts[1]) ? (int)$parts[1] : 8728;
    $api   = new DuMikrotikAPI($host, $port, 5);
    $api->connect($router['username'], $router['password']);
    return $api;
}

// ─────────────────────────────────────────────────────────
// Helper: Format bytes
// ─────────────────────────────────────────────────────────
function du_fmt(int $bytes): string
{
    if ($bytes <= 0) return '0 B';
    $units = ['B', 'KB', 'MB', 'GB', 'TB'];
    $i = min((int)floor(log($bytes, 1024)), 4);
    return round($bytes / pow(1024, $i), 2) . ' ' . $units[$i];
}

// ─────────────────────────────────────────────────────────
// Helper: JSON response
// ─────────────────────────────────────────────────────────
function du_json($data): void
{
    ob_clean();
    header('Content-Type: application/json');
    echo json_encode($data);
    exit;
}

// ─────────────────────────────────────────────────────────
// DB: Auto-create tables
// ─────────────────────────────────────────────────────────
function du_createTables(): void
{
    $db = ORM::getDb();

    // Daily usage — one row per username+router+date, upserted
    $db->exec("CREATE TABLE IF NOT EXISTS `tbl_du_usage` (
        `id`         BIGINT(20)    NOT NULL AUTO_INCREMENT,
        `username`   VARCHAR(64)   NOT NULL,
        `router`     VARCHAR(64)   NOT NULL DEFAULT '',
        `type`       ENUM('Hotspot','PPPoE') NOT NULL,
        `date`       DATE          NOT NULL,
        `upload`     BIGINT(20)    NOT NULL DEFAULT 0,
        `download`   BIGINT(20)    NOT NULL DEFAULT 0,
        `total`      BIGINT(20)    NOT NULL DEFAULT 0,
        `updated_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_user_router_date` (`username`,`router`,`date`),
        KEY `idx_date`     (`date`),
        KEY `idx_username` (`username`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

    // Session snapshots — current session bytes for delta calculation
    $db->exec("CREATE TABLE IF NOT EXISTS `tbl_du_snapshots` (
        `id`            BIGINT(20)    NOT NULL AUTO_INCREMENT,
        `username`      VARCHAR(64)   NOT NULL,
        `router`        VARCHAR(64)   NOT NULL,
        `type`          ENUM('Hotspot','PPPoE') NOT NULL,
        `session_id`    VARCHAR(64)   NOT NULL DEFAULT '',
        `snap_upload`   BIGINT(20)    NOT NULL DEFAULT 0,
        `snap_download` BIGINT(20)    NOT NULL DEFAULT 0,
        `snap_date`     DATE          NOT NULL,
        `snap_uptime`   INT(11)       NOT NULL DEFAULT 0,
        `snapped_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_session` (`username`,`router`,`type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

    // Add snap_uptime column if not exists (for existing installations)
    try {
        $db->exec("ALTER TABLE tbl_du_snapshots ADD COLUMN `snap_uptime` INT(11) NOT NULL DEFAULT 0 AFTER `snap_date`");
    } catch (Exception $e) {}

    // Cron run log — just one row, always updated
    $db->exec("CREATE TABLE IF NOT EXISTS `tbl_du_cronlog` (
        `id`         INT(11)   NOT NULL DEFAULT 1,
        `last_run`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `routers`    INT(11)   NOT NULL DEFAULT 0,
        `sessions`   INT(11)   NOT NULL DEFAULT 0,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
}

// ─────────────────────────────────────────────────────────
// Delta calculation with 32-bit counter wrap detection
// 32-bit max = 4,294,967,295 (~4.3 GB)
// ─────────────────────────────────────────────────────────
define('DU_COUNTER_MAX', 4294967295);
define('DU_MAX_PER_POLL', 107374182400); // 100 GB sanity limit per poll

function du_delta(int $prev, int $curr): int
{
    if ($curr >= $prev) {
        return $curr - $prev;
    }
    // Counter wrapped around 32-bit boundary
    return (DU_COUNTER_MAX - $prev) + $curr + 1;
}

// ─────────────────────────────────────────────────────────
// DB: Upsert usage
// ─────────────────────────────────────────────────────────
function du_addUsage(string $username, string $router, string $type, int $upload, int $download): void
{
    if ($upload <= 0 && $download <= 0) return;
    $total = $upload + $download;
    $today = date('Y-m-d');
    $db    = ORM::getDb();
    $db->exec("INSERT INTO tbl_du_usage (username, router, type, date, upload, download, total)
               VALUES (
                   " . $db->quote($username) . ",
                   " . $db->quote($router)   . ",
                   " . $db->quote($type)     . ",
                   " . $db->quote($today)    . ",
                   $upload, $download, $total
               )
               ON DUPLICATE KEY UPDATE
                   upload   = upload   + $upload,
                   download = download + $download,
                   total    = total    + $total");
}

// ─────────────────────────────────────────────────────────
// DB: Snapshot CRUD
// ─────────────────────────────────────────────────────────
function du_saveSnapshot(string $username, string $router, string $type,
                         string $sessionId, int $upload, int $download,
                         int $uptime = 0): void
{
    $today = date('Y-m-d');
    $db    = ORM::getDb();
    $db->exec("INSERT INTO tbl_du_snapshots
                    (username, router, type, session_id, snap_upload, snap_download, snap_date, snap_uptime)
               VALUES (
                   " . $db->quote($username)  . ",
                   " . $db->quote($router)    . ",
                   " . $db->quote($type)      . ",
                   " . $db->quote($sessionId) . ",
                   $upload, $download,
                   " . $db->quote($today)     . ",
                   $uptime
               )
               ON DUPLICATE KEY UPDATE
                   session_id    = " . $db->quote($sessionId) . ",
                   snap_upload   = $upload,
                   snap_download = $download,
                   snap_date     = " . $db->quote($today)     . ",
                   snap_uptime   = $uptime");
}

function du_getSnapshot(string $username, string $router, string $type): ?array
{
    $db   = ORM::getDb();
    $stmt = $db->prepare(
        "SELECT * FROM tbl_du_snapshots WHERE username=? AND router=? AND type=?"
    );
    $stmt->execute([$username, $router, $type]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function du_cleanSnapshots(array $activeKeys): void
{
    $db       = ORM::getDb();
    $existing = $db->query(
        "SELECT username, router, type FROM tbl_du_snapshots"
    )->fetchAll(PDO::FETCH_ASSOC);

    foreach ($existing as $snap) {
        $key = $snap['username'] . '|' . $snap['router'] . '|' . $snap['type'];
        if (!in_array($key, $activeKeys)) {
            $stmt = $db->prepare(
                "DELETE FROM tbl_du_snapshots WHERE username=? AND router=? AND type=?"
            );
            $stmt->execute([$snap['username'], $snap['router'], $snap['type']]);
        }
    }
}

// ─────────────────────────────────────────────────────────
// CRON: Poll all enabled routers — called by register_hook
// ─────────────────────────────────────────────────────────
function du_cron(): void
{
    du_createTables();

    $routers    = ORM::for_table('tbl_routers')->where('enabled', 1)->find_many();
    $activeKeys = [];

    foreach ($routers as $router) {
        $routerName = $router['name'];
        $parts      = explode(':', $router['ip_address']);
        $host       = $parts[0];
        $port       = isset($parts[1]) ? (int)$parts[1] : 8728;

        // Skip offline routers
        if (!du_isReachable($host, $port, 2)) {
            _log("du_cron: Router [$routerName] offline, skipping");
            continue;
        }

        try {
            $api = du_connect($router);

            // ── Hotspot ──────────────────────────────────
            $hsItems = $api->getHotspotActive();
            foreach ($hsItems as $item) {
                $username = trim($item['user'] ?? '');
                if (empty($username)) continue;

                // Hotspot customer tracking:
                // If username matches a customer → use phone number (fixed even if MAC changes)
                // Voucher / unknown → use as-is (their username is already fixed)
                $trackUsername = $username;
                $hsCustomer = ORM::for_table('tbl_customers')
                    ->where('username', $username)
                    ->find_one();
                if ($hsCustomer && !empty($hsCustomer['phonenumber'])) {
                    $trackUsername = $hsCustomer['phonenumber'];
                }

                $currUp     = (int)($item['bytes-in']  ?? 0); // upload from user
                $currDown   = (int)($item['bytes-out'] ?? 0); // download to user
                $sessId     = $item['.id'] ?? $username;
                $currUptime = du_uptimeToSeconds($item['uptime'] ?? '0s');

                $key          = "$trackUsername|$routerName|Hotspot";
                $activeKeys[] = $key;

                $snap = du_getSnapshot($trackUsername, $routerName, 'Hotspot');

                if ($snap) {
                    // Session change detection:
                    // 1. .id changed → new session
                    // 2. uptime reset (curr < prev) → reconnect
                    // 3. uptime = 0 → brand new session
                    // 4. bytes went backwards → counter reset (reconnect)
                    $prevUp   = (int)$snap['snap_upload'];
                    $prevDown = (int)$snap['snap_download'];
                    $prevUptime = (int)($snap['snap_uptime'] ?? 0);

                    $sessionChanged = ($snap['session_id'] !== (string)$sessId)
                                   || ($currUptime < $prevUptime)
                                   || ($currUptime === 0)
                                   || ($currUp < $prevUp && $prevUp > 1048576)   // bytes went back > 1MB
                                   || ($currDown < $prevDown && $prevDown > 1048576);

                    if (!$sessionChanged) {
                        $deltaUp   = $currUp   - $prevUp;
                        $deltaDown = $currDown - $prevDown;

                        // Sanity check
                        if ($deltaUp < 0) $deltaUp = 0;
                        if ($deltaDown < 0) $deltaDown = 0;

                        if ($deltaUp <= DU_MAX_PER_POLL && $deltaDown <= DU_MAX_PER_POLL) {
                            du_addUsage($trackUsername, $routerName, 'Hotspot', $deltaUp, $deltaDown);
                        } else {
                            _log("du_cron: Spike ignored for Hotspot [$username] up=$deltaUp down=$deltaDown");
                        }
                    }
                }

                du_saveSnapshot($trackUsername, $routerName, 'Hotspot', (string)$sessId, $currUp, $currDown, $currUptime);
            }

            // ── PPPoE ─────────────────────────────────────
            $pppItems = $api->getPPPoEActive();
            $ifItems  = $api->getInterfaces();

            // Build interface stats map
            $ifStats = [];
            foreach ($ifItems as $iface) {
                $n = $iface['name'] ?? '';
                if ($n) {
                    $ifStats[$n] = [
                        'tx-byte' => (int)($iface['tx-byte'] ?? 0),
                        'rx-byte' => (int)($iface['rx-byte'] ?? 0),
                    ];
                }
            }

            foreach ($pppItems as $item) {
                $username = trim($item['name'] ?? '');
                $service  = strtolower(trim($item['service'] ?? ''));
                $address  = trim($item['address'] ?? '');

                // Validate PPPoE entry
                if (empty($username)) continue;
                if ($service !== 'pppoe') continue;
                if (!filter_var($address, FILTER_VALIDATE_IP)) continue;

                // PPPoE: rx-byte = download to user, tx-byte = upload from user
                $stats    = $ifStats['<pppoe-' . $username . '>']
                         ?? $ifStats['pppoe-'  . $username]
                         ?? $ifStats['pppoe-out-' . $username]
                         ?? ['tx-byte' => 0, 'rx-byte' => 0];

                $currUp   = (int)$stats['rx-byte']; // User→Router = Upload
                $currDown = (int)$stats['tx-byte']; // Router→User = Download
                $sessId     = $item['.id'] ?? $username;
                $currUptime = du_uptimeToSeconds($item['uptime'] ?? '0s');

                $key          = "$username|$routerName|PPPoE";
                $activeKeys[] = $key;

                $snap = du_getSnapshot($username, $routerName, 'PPPoE');

                if ($snap) {
                    $prevUp   = (int)$snap['snap_upload'];
                    $prevDown = (int)$snap['snap_download'];
                    $prevUptime = (int)($snap['snap_uptime'] ?? 0);

                    $sessionChanged = ($snap['session_id'] !== (string)$sessId)
                                   || ($currUptime < $prevUptime)
                                   || ($currUptime === 0)
                                   || ($currUp < $prevUp && $prevUp > 1048576)
                                   || ($currDown < $prevDown && $prevDown > 1048576);

                    if (!$sessionChanged) {
                        $deltaUp   = $currUp   - $prevUp;
                        $deltaDown = $currDown - $prevDown;

                        if ($deltaUp < 0) $deltaUp = 0;
                        if ($deltaDown < 0) $deltaDown = 0;

                        if ($deltaUp <= DU_MAX_PER_POLL && $deltaDown <= DU_MAX_PER_POLL) {
                            du_addUsage($username, $routerName, 'PPPoE', $deltaUp, $deltaDown);
                        } else {
                            _log("du_cron: Spike ignored for PPPoE [$username] up=$deltaUp down=$deltaDown");
                        }
                    }
                }

                du_saveSnapshot($username, $routerName, 'PPPoE', (string)$sessId, $currUp, $currDown, $currUptime);
            }

            $api->disconnect();

        } catch (Exception $e) {
            _log("du_cron: Router [$routerName] error: " . $e->getMessage());
        }
    }

    // Remove snapshots for users no longer active
    du_cleanSnapshots($activeKeys);

    // Log cron run
    $db = ORM::getDb();
    $routerCount  = count($routers);
    $sessionCount = count($activeKeys);
    $db->exec("INSERT INTO tbl_du_cronlog (id, last_run, routers, sessions)
               VALUES (1, NOW(), $routerCount, $sessionCount)
               ON DUPLICATE KEY UPDATE
                   last_run = NOW(),
                   routers  = $routerCount,
                   sessions = $sessionCount");
}

// ─────────────────────────────────────────────────────────
// PAGE: Main data usage list
// ─────────────────────────────────────────────────────────
function data_usage(): void
{
    global $ui;
    _admin();
    du_createTables();

    $admin = Admin::_info();
    $ui->assign('_admin',       $admin);
    $ui->assign('_title',       Lang::T('Data Usage'));
    $ui->assign('_system_menu', 'network');

    if (!in_array($admin['user_type'], ['SuperAdmin', 'Admin', 'Sales'])) {
        _alert(Lang::T('You do not have permission'), 'danger', 'dashboard');
        return;
    }

    // Support both GET and POST (phpNuxBill uses & not ? for params)
    $p       = array_merge($_GET, $_POST);
    $view    = $p['view']     ?? 'daily';
    $date    = $p['date']     ?? date('Y-m-d');
    $month   = $p['month']    ?? date('Y-m');
    $router  = $p['router']   ?? '';
    $type    = $p['type']     ?? '';
    $search  = trim($p['search']    ?? '');
    $sort    = $p['sort']     ?? 'total';
    $dir     = $p['dir']      ?? 'desc';
    $perPage = max(10, min(200, (int)($p['per_page'] ?? 50)));
    $page    = max(1,  (int)($p['page'] ?? 1));

    // Validate sort
    if (!in_array($sort, ['username', 'upload', 'download', 'total'])) $sort = 'total';
    $dir = ($dir === 'asc') ? 'asc' : 'desc';

    $db     = ORM::getDb();
    $where  = '1=1';
    $params = [];

    // Date filter
    if ($view === 'daily') {
        $where .= ' AND date = ?';
        $params[] = $date;
    } elseif ($view === 'weekly') {
        $weekStart = date('Y-m-d', strtotime('monday this week', strtotime($date)));
        $weekEnd   = date('Y-m-d', strtotime('sunday this week', strtotime($date)));
        $where    .= ' AND date BETWEEN ? AND ?';
        $params[]  = $weekStart;
        $params[]  = $weekEnd;
    } elseif ($view === 'monthly') {
        $where   .= " AND DATE_FORMAT(date,'%Y-%m') = ?";
        $params[] = $month;
    }

    if ($router) { $where .= ' AND router = ?'; $params[] = $router; }
    // Hotspot/PPPoE filter
    $userTypeFilter = '';
    if (in_array($type, ['customer', 'voucher', 'unknown', 'hotspot_customer', 'pppoe_customer'])) {
        $userTypeFilter = $type; // handle after rows fetched
    } elseif ($type) {
        $where .= ' AND type = ?';
        $params[] = $type;
    }
    if ($search) { $where .= ' AND username LIKE ?'; $params[] = "%$search%"; }

    // User type pre-filter — get matching usernames first
    if ($userTypeFilter) {
        // Get all usernames for this period
        $stmtAll = $db->prepare("SELECT DISTINCT username, MAX(type) as type FROM tbl_du_usage WHERE $where GROUP BY username");
        $stmtAll->execute($params);
        $allUsers = $stmtAll->fetchAll(PDO::FETCH_ASSOC);

        // Filter by user type
        $matchedUsernames = [];
        foreach ($allUsers as $u) {
            $uname = $u['username'];
            $utype = $u['type'];
            $c = ORM::for_table('tbl_customers')->where('username', $uname)->find_one();
            if (!$c) $c = ORM::for_table('tbl_customers')->where('pppoe_username', $uname)->find_one();
            if (!$c) $c = ORM::for_table('tbl_customers')->where('phonenumber', $uname)->find_one();
            $isCustomer = (bool)$c;
            $v = ORM::for_table('tbl_hotspot_vouchers')->where('code', $uname)->where('is_used', 1)->find_one();
            $isVoucher  = (bool)$v;
            $isUnknown  = !$isCustomer && !$isVoucher;

            $match = false;
            switch ($userTypeFilter) {
                case 'customer':         $match = $isCustomer; break;
                case 'voucher':          $match = $isVoucher; break;
                case 'unknown':          $match = $isUnknown; break;
                case 'hotspot_customer': $match = $isCustomer && $utype === 'Hotspot'; break;
                case 'pppoe_customer':   $match = $isCustomer && $utype === 'PPPoE'; break;
            }
            if ($match) $matchedUsernames[] = $db->quote($uname);
        }

        if (empty($matchedUsernames)) {
            // No matching users
            $where .= ' AND 1=0';
        } else {
            $where .= ' AND username IN (' . implode(',', $matchedUsernames) . ')';
        }
    }

    // Totals
    $stmt = $db->prepare("SELECT
                            COUNT(DISTINCT username)    as cnt,
                            COALESCE(SUM(upload),0)    as total_up,
                            COALESCE(SUM(download),0)  as total_down,
                            COALESCE(SUM(total),0)     as grand_total
                          FROM tbl_du_usage WHERE $where");
    $stmt->execute($params);
    $totals = $stmt->fetch(PDO::FETCH_ASSOC);

    // Avg
    $cnt = max(1, (int)($totals['cnt'] ?? 1));
    $totals['avg_fmt']        = du_fmt((int)(($totals['grand_total'] ?? 0) / $cnt));
    $totals['total_up_fmt']   = du_fmt((int)($totals['total_up']    ?? 0));
    $totals['total_down_fmt'] = du_fmt((int)($totals['total_down']  ?? 0));
    $totals['grand_total_fmt']= du_fmt((int)($totals['grand_total'] ?? 0));

    // Compare vs previous period
    $prevParams = $params;
    if ($view === 'daily') {
        $prevParams[0] = date('Y-m-d', strtotime($date . ' -1 day'));
        $prevWhere = $where;
    } elseif ($view === 'weekly') {
        $prevParams[0] = date('Y-m-d', strtotime($weekStart . ' -7 days'));
        $prevParams[1] = date('Y-m-d', strtotime($weekEnd   . ' -7 days'));
        $prevWhere = $where;
    } else {
        $prevParams[0] = date('Y-m', strtotime($month . '-01 -1 month'));
        $prevWhere = $where;
    }
    $stmtPrev = $db->prepare("SELECT COALESCE(SUM(total),0) as pt FROM tbl_du_usage WHERE $prevWhere");
    $stmtPrev->execute($prevParams);
    $prevTotal = (int)($stmtPrev->fetch(PDO::FETCH_ASSOC)['pt'] ?? 0);
    $currTotal = (int)($totals['grand_total'] ?? 0);
    $changePct = $prevTotal > 0 ? round(($currTotal - $prevTotal) / $prevTotal * 100, 1) : 0;
    $totals['change_pct'] = $changePct;
    $totals['prev_fmt']   = du_fmt($prevTotal);

    // Rows
    $offset = ($page - 1) * $perPage;
    $stmt   = $db->prepare("SELECT username,
                                   MAX(router)   as router,
                                   MAX(type)     as type,
                                   SUM(upload)   as upload,
                                   SUM(download) as download,
                                   SUM(total)    as total
                            FROM tbl_du_usage
                            WHERE $where
                            GROUP BY username
                            ORDER BY $sort $dir
                            LIMIT $perPage OFFSET $offset");
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Heavy user threshold: 2× average
    $avgBytes = $cnt > 0 ? ($currTotal / $cnt) : 0;

    // Filter by user type if needed
    if ($userTypeFilter) {
        $rows = array_filter($rows, function($row) use ($userTypeFilter) {
            $type   = $row['type'] ?? '';
            $c = ORM::for_table('tbl_customers')->where('username', $row['username'])->find_one();
            if (!$c) $c = ORM::for_table('tbl_customers')->where('pppoe_username', $row['username'])->find_one();
            if (!$c) $c = ORM::for_table('tbl_customers')->where('phonenumber', $row['username'])->find_one();
            $isCustomer = (bool)$c;
            $v = ORM::for_table('tbl_hotspot_vouchers')->where('code', $row['username'])->where('is_used', 1)->find_one();
            $isVoucher  = (bool)$v;
            $isUnknown  = !$isCustomer && !$isVoucher;

            switch ($userTypeFilter) {
                case 'customer':         return $isCustomer;
                case 'voucher':          return $isVoucher;
                case 'unknown':          return $isUnknown;
                case 'hotspot_customer': return $isCustomer && $type === 'Hotspot';
                case 'pppoe_customer':   return $isCustomer && $type === 'PPPoE';
                default:                 return true;
            }
        });
        $rows = array_values($rows);
    }

    foreach ($rows as &$row) {
        $row['upload_fmt']   = du_fmt((int)$row['upload']);
        $row['download_fmt'] = du_fmt((int)$row['download']);
        $row['total_fmt']    = du_fmt((int)$row['total']);
        $row['is_heavy']     = ($avgBytes > 0 && (int)$row['total'] > $avgBytes * 2);

        // Customer info — check username, pppoe_username, then phonenumber (reverse lookup for hotspot)
        $c = ORM::for_table('tbl_customers')->where('username', $row['username'])->find_one();
        if (!$c) {
            $c = ORM::for_table('tbl_customers')->where('pppoe_username', $row['username'])->find_one();
        }
        if (!$c) {
            $c = ORM::for_table('tbl_customers')->where('phonenumber', $row['username'])->find_one();
        }
        $row['fullname']    = $c ? $c['fullname']    : '';
        $row['phone']       = $c ? $c['phonenumber'] : '';
        $row['customer_id'] = $c ? $c['id']          : '';

        // User type detection
        if ($c) {
            $row['user_type'] = 'customer';
        } else {
            $v = ORM::for_table('tbl_hotspot_vouchers')
                ->where('code', $row['username'])
                ->where('is_used', 1)
                ->find_one();
            $row['user_type'] = $v ? 'voucher' : 'unknown';
        }
    }
    unset($row);

    // Top 10 for charts
    $stmt2 = $db->prepare("SELECT username,
                                  SUM(upload)   as upload,
                                  SUM(download) as download,
                                  SUM(total)    as total
                           FROM tbl_du_usage WHERE $where
                           GROUP BY username ORDER BY total DESC LIMIT 10");
    $stmt2->execute($params);
    $top10 = $stmt2->fetchAll(PDO::FETCH_ASSOC);

    // Monthly trend for current year
    $year = substr($view === 'monthly' ? $month : $date, 0, 4);
    $trendParams = [$year];
    $trendWhere  = '';
    if ($router) { $trendWhere .= ' AND router=?'; $trendParams[] = $router; }
    if ($type)   { $trendWhere .= ' AND type=?';   $trendParams[] = $type; }
    $stmtTrend = $db->prepare(
        "SELECT DATE_FORMAT(date,'%m') as m,
                SUM(upload)   as upload,
                SUM(download) as download
         FROM tbl_du_usage
         WHERE YEAR(date)=? $trendWhere
         GROUP BY m ORDER BY m ASC"
    );
    $stmtTrend->execute($trendParams);
    $monthly_trend = $stmtTrend->fetchAll(PDO::FETCH_ASSOC);

    // Cron last poll — from cronlog table
    try {
        $cronRow  = $db->query("SELECT last_run, sessions FROM tbl_du_cronlog WHERE id=1")->fetch(PDO::FETCH_ASSOC);
        $cronLast = $cronRow['last_run'] ?? null;
    } catch (Exception $e) {
        $cronLast = null;
    }

    // Routers for filter
    $routers = ORM::for_table('tbl_routers')->where('enabled', 1)->find_array();

    $totalPages = ceil(($totals['cnt'] ?? 0) / $perPage);

    $ui->assign('view',          $view);
    $ui->assign('date',          $date);
    $ui->assign('month',         $month);
    $ui->assign('router_filter', $router);
    $ui->assign('type_filter',   $type);
    $ui->assign('search',        $search);
    $ui->assign('sort',          $sort);
    $ui->assign('dir',           $dir);
    $ui->assign('per_page',      $perPage);
    $ui->assign('page',          $page);
    $ui->assign('total_pages',   $totalPages);
    $ui->assign('rows',          $rows);
    $ui->assign('totals',        $totals);
    $ui->assign('top10',         $top10);
    $ui->assign('monthly_trend', $monthly_trend);
    $ui->assign('year',          $year);
    $ui->assign('cron_last',     $cronLast);
    $ui->assign('routers',       $routers);
    $ui->assign('csrf_token',    Csrf::generateAndStoreToken());
    $ui->display('data_usage.tpl');
}

// ─────────────────────────────────────────────────────────
// PAGE: Per-user detail
// ─────────────────────────────────────────────────────────
function data_usage_user(): void
{
    global $ui, $routes;
    _admin();
    du_createTables();

    $admin = Admin::_info();
    $ui->assign('_admin',       $admin);
    $ui->assign('_system_menu', '');

    $username = urldecode($routes['2'] ?? $_GET['u'] ?? '');
    if (!$username) _alert('Username required', 'danger', 'data_usage');

    $customer = ORM::for_table('tbl_customers')->where('username', $username)->find_one();
    $ui->assign('_title', 'Usage: ' . $username);

    $db    = ORM::getDb();
    $p = array_merge($_GET, $_POST);
    $month = $p['month'] ?? date('Y-m');

    // Daily breakdown this month
    $stmt = $db->prepare(
        "SELECT date, router, type,
                SUM(upload)   as upload,
                SUM(download) as download,
                SUM(total)    as total
         FROM tbl_du_usage
         WHERE username=? AND DATE_FORMAT(date,'%Y-%m')=?
         GROUP BY date, router, type
         ORDER BY date DESC"
    );
    $stmt->execute([$username, $month]);
    $daily = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($daily as &$d) {
        $d['upload_fmt']   = du_fmt((int)$d['upload']);
        $d['download_fmt'] = du_fmt((int)$d['download']);
        $d['total_fmt']    = du_fmt((int)$d['total']);
    }
    unset($d);

    // Monthly summary last 6 months
    $stmt2 = $db->prepare(
        "SELECT DATE_FORMAT(date,'%Y-%m') as month,
                SUM(upload)   as upload,
                SUM(download) as download,
                SUM(total)    as total
         FROM tbl_du_usage
         WHERE username=?
         GROUP BY month ORDER BY month DESC LIMIT 6"
    );
    $stmt2->execute([$username]);
    $monthly = $stmt2->fetchAll(PDO::FETCH_ASSOC);

    foreach ($monthly as &$m) {
        $m['upload_fmt']   = du_fmt((int)$m['upload']);
        $m['download_fmt'] = du_fmt((int)$m['download']);
        $m['total_fmt']    = du_fmt((int)$m['total']);
    }
    unset($m);

    // This month totals
    $stmt3 = $db->prepare(
        "SELECT COALESCE(SUM(upload),0)   as upload,
                COALESCE(SUM(download),0) as download,
                COALESCE(SUM(total),0)    as total
         FROM tbl_du_usage
         WHERE username=? AND DATE_FORMAT(date,'%Y-%m')=?"
    );
    $stmt3->execute([$username, $month]);
    $mt = $stmt3->fetch(PDO::FETCH_ASSOC);
    $mt['upload_fmt']   = du_fmt((int)$mt['upload']);
    $mt['download_fmt'] = du_fmt((int)$mt['download']);
    $mt['total_fmt']    = du_fmt((int)$mt['total']);

    $ui->assign('username',     $username);
    $ui->assign('customer',     $customer ? $customer->as_array() : []);
    $ui->assign('month',        $month);
    $ui->assign('daily',        $daily);
    $ui->assign('monthly',      $monthly);
    $ui->assign('month_totals', $mt);
    $ui->display('data_usage_user.tpl');
}

// ─────────────────────────────────────────────────────────
// CSV Export
// ─────────────────────────────────────────────────────────
function data_usage_csv(): void
{
    _admin();
    du_createTables();

    $p      = array_merge($_GET, $_POST);
    $view   = $p['view']   ?? 'daily';
    $date   = $p['date']   ?? date('Y-m-d');
    $month  = $p['month']  ?? date('Y-m');
    $router = $p['router'] ?? '';
    $type   = $p['type']   ?? '';

    $db     = ORM::getDb();
    $where  = '1=1';
    $params = [];

    if ($view === 'daily')       { $where .= ' AND date=?'; $params[] = $date; }
    elseif ($view === 'weekly')  {
        $ws = date('Y-m-d', strtotime('monday this week', strtotime($date)));
        $we = date('Y-m-d', strtotime('sunday this week', strtotime($date)));
        $where .= ' AND date BETWEEN ? AND ?';
        $params[] = $ws; $params[] = $we;
    }
    elseif ($view === 'monthly') { $where .= " AND DATE_FORMAT(date,'%Y-%m')=?"; $params[] = $month; }
    if ($router) { $where .= ' AND router=?'; $params[] = $router; }
    if ($type)   { $where .= ' AND type=?';   $params[] = $type; }

    $stmt = $db->prepare(
        "SELECT username, MAX(router) as router, MAX(type) as type,
                SUM(upload) as upload, SUM(download) as download, SUM(total) as total
         FROM tbl_du_usage WHERE $where
         GROUP BY username ORDER BY total DESC"
    );
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="data_usage_' . $view . '_' . date('Ymd') . '.csv"');
    echo "Username,Full Name,Router,Type,Upload,Download,Total\n";
    foreach ($rows as $r) {
        $c  = ORM::for_table('tbl_customers')->where('username', $r['username'])->find_one();
        if (!$c) $c = ORM::for_table('tbl_customers')->where('phonenumber', $r['username'])->find_one();
        $fn = $c ? $c['fullname'] : '';
        echo '"' . $r['username'] . '","' . $fn . '","' . $r['router'] . '","' . $r['type'] . '",'
           . du_fmt((int)$r['upload'])   . ','
           . du_fmt((int)$r['download']) . ','
           . du_fmt((int)$r['total'])    . "\n";
    }
    exit;
}