<?php
declare(strict_types=1);

if (!function_exists('scp_column_catalog')) {
    function scp_column_catalog(): array
    {
        return [
            'id' => 'الرقم',
            'name' => 'الاسم',
            'profile' => 'PPPoE Profile',
            'sector' => 'القطاع',
            'status' => 'الحالة',
            'last_seen' => 'آخر ظهور',
            'last_cut' => 'آخر فصل',
            'usage' => 'الاستهلاك',
        ];
    }
}

if (!function_exists('scp_parse_ids')) {
    function scp_parse_ids(string $raw, int $limit = 1000): array
    {
        $ids = [];
        foreach (preg_split('/[^0-9]+/', $raw, -1, PREG_SPLIT_NO_EMPTY) as $value) {
            $id = (int)$value;
            if ($id > 0) $ids[$id] = $id;
            if (count($ids) >= $limit) break;
        }
        return array_values($ids);
    }
}

if (!function_exists('scp_parse_columns')) {
    function scp_parse_columns($raw): array
    {
        $catalog = scp_column_catalog();
        $values = is_array($raw) ? $raw : preg_split('/[^a-z_]+/i', (string)$raw, -1, PREG_SPLIT_NO_EMPTY);
        $selected = [];
        foreach ((array)$values as $value) {
            $key = strtolower(trim((string)$value));
            if (isset($catalog[$key])) $selected[$key] = $key;
        }
        return array_values($selected);
    }
}

if (!function_exists('scp_time_display')) {
    function scp_time_display($value, bool $relative = false): string
    {
        $raw = trim((string)$value);
        $timestamp = $raw !== '' ? strtotime($raw) : false;
        if (!$timestamp || $timestamp < 946684800) return '—';
        if (!$relative) return date('Y-m-d H:i', $timestamp);
        $diff = max(0, time() - $timestamp);
        if ($diff < 60) return 'الآن';
        if ($diff < 3600) return 'منذ ' . (int)floor($diff / 60) . ' دقيقة';
        if ($diff < 86400) return 'منذ ' . (int)floor($diff / 3600) . ' ساعة';
        if ($diff < 604800) return 'منذ ' . (int)floor($diff / 86400) . ' يوم';
        return date('Y-m-d H:i', $timestamp);
    }
}

if (!function_exists('scp_is_disabled')) {
    function scp_is_disabled(array $row): bool
    {
        if ((int)($row['billing_paused'] ?? 0) === 1) return true;
        foreach (['ppp_is_disabled', 'mt_disabled', 'is_cut'] as $key) {
            if ((int)($row[$key] ?? 0) === 1) return true;
        }
        if (strtoupper(trim((string)($row['speed_state'] ?? ''))) === 'CUT') return true;
        return in_array(strtoupper(trim((string)($row['status'] ?? ''))), ['CUT', 'DISABLED', 'STOPPED'], true);
    }
}

if (!function_exists('scp_status_label')) {
    function scp_status_label(array $row): string
    {
        if (scp_is_disabled($row)) return 'متوقف';
        $speedState = strtoupper(trim((string)($row['speed_state'] ?? '')));
        if (in_array($speedState, ['LIMIT', 'LIMITED', 'REDUCED', 'THROTTLED'], true)) return 'تخفيض سرعة';
        $current = trim((string)($row['current_speed'] ?? ''));
        $base = trim((string)($row['base_speed'] ?? ''));
        if ($current !== '' && $base !== '' && strcasecmp($current, $base) !== 0) return 'تخفيض سرعة';
        return (int)($row['is_online'] ?? 0) === 1 ? 'متصل' : 'غير متصل';
    }
}

if (!function_exists('scp_fetch_rows')) {
    function scp_usage_batch(mysqli $conn, array $rows, string $cycle): array
    {
        $usage = ['id' => [], 'user' => []];
        if (!$rows || !function_exists('q_table_exists') || !q_table_exists($conn, 'ppp_usage_monthly')) return $usage;

        $ids = [];
        $users = [];
        foreach ($rows as $row) {
            $id = (int)($row['id'] ?? 0);
            $username = trim((string)($row['ppp_username'] ?? $row['username'] ?? ''));
            $username = preg_replace('/@.*/', '', $username);
            if ($id > 0) $ids[$id] = $id;
            if ($username !== '') $users[$username] = $username;
        }
        $month = $conn->real_escape_string($cycle);

        if ($ids && function_exists('q_col_exists') && q_col_exists($conn, 'ppp_usage_monthly', 'subscriber_id') && q_col_exists($conn, 'ppp_usage_monthly', 'month_key')) {
            $idList = implode(',', array_map('intval', array_values($ids)));
            $result = @$conn->query("SELECT subscriber_id,COALESCE(SUM(used_bytes),0) used_bytes FROM ppp_usage_monthly WHERE month_key='{$month}' AND subscriber_id IN ({$idList}) GROUP BY subscriber_id");
            if ($result) while ($item = $result->fetch_assoc()) $usage['id'][(int)$item['subscriber_id']] = (int)($item['used_bytes'] ?? 0);
        }

        if ($users && function_exists('q_col_exists') && q_col_exists($conn, 'ppp_usage_monthly', 'username') && q_col_exists($conn, 'ppp_usage_monthly', 'cycle_month')) {
            $userList = "'" . implode("','", array_map([$conn, 'real_escape_string'], array_values($users))) . "'";
            $result = @$conn->query("SELECT SUBSTRING_INDEX(username,'@',1) username_key,COALESCE(SUM(used_bytes),0) used_bytes FROM ppp_usage_monthly WHERE cycle_month='{$month}' AND SUBSTRING_INDEX(username,'@',1) IN ({$userList}) GROUP BY SUBSTRING_INDEX(username,'@',1)");
            if ($result) while ($item = $result->fetch_assoc()) $usage['user'][trim((string)$item['username_key'])] = (int)($item['used_bytes'] ?? 0);
        }
        return $usage;
    }

    function scp_fetch_rows(mysqli $conn, array $ids, string $credentialMode = 'current', array $requiredFields = []): array
    {
        if (!$ids) return [];
        $idList = implode(',', array_map('intval', $ids));
        $hasSectors = function_exists('table_exists') && table_exists($conn, 'sectors');
        $sectorSelect = $hasSectors ? ", COALESCE(sec.name,'') AS _sector_name" : ", '' AS _sector_name";
        $sectorJoin = $hasSectors ? ' LEFT JOIN sectors sec ON sec.id=s.sector_id' : '';
        $result = @$conn->query("SELECT s.*{$sectorSelect} FROM subscribers s{$sectorJoin} WHERE s.id IN ({$idList}) AND COALESCE(s.is_deleted,0)=0 ORDER BY FIELD(s.id,{$idList})");
        if (!$result) return [];

        $sourceRows = [];
        while ($row = $result->fetch_assoc()) $sourceRows[] = $row;

        $rows = [];
        $cycle = function_exists('q_cycle_month') ? q_cycle_month() : date('Y-m');
        $usageMap = scp_usage_batch($conn, $sourceRows, $cycle);
        $realityMap = function_exists('mt_ppp_secret_reality_map_cached') ? mt_ppp_secret_reality_map_cached($conn, 60, false) : [];
        $pppSnapshot = function_exists('ua_ppp_cache_snapshot') ? ua_ppp_cache_snapshot(86400) : [];
        $activeMap = function_exists('sub_status_ppp_active_map') ? sub_status_ppp_active_map($pppSnapshot) : [];
        foreach ($sourceRows as $row) {
            $username = trim((string)($row['ppp_username'] ?? ''));
            if ($username === '') $username = trim((string)($row['username'] ?? ''));
            $username = preg_replace('/@.*/', '', $username);
            $reality = $username !== '' && isset($realityMap[$username]) && is_array($realityMap[$username]) ? $realityMap[$username] : null;
            if ($reality !== null && array_key_exists('disabled', $reality)) $row['ppp_is_disabled'] = !empty($reality['disabled']) ? 1 : 0;
            $activeRow = $username !== '' && isset($activeMap[$username]) && is_array($activeMap[$username]) ? $activeMap[$username] : null;
            $usage = max(
                (int)($usageMap['id'][(int)$row['id']] ?? 0),
                (int)($usageMap['user'][$username] ?? 0)
            );
            $displayName = trim((string)($row['full_name'] ?? ''));
            if ($displayName === '' || preg_match('/^SUB#(?:AUTO|\d+)/iu', $displayName)) {
                $displayName = $username !== '' ? $username : trim((string)($row['username'] ?? ''));
            }
            $lastCut = trim((string)($row['ppp_last_cut_at'] ?? ''));
            if ($lastCut === '') $lastCut = trim((string)($row['last_disconnect_at'] ?? ''));
            $routerLogout = $reality !== null ? trim((string)($reality['last_logged_out'] ?? '')) : '';
            $lastCutTs = $lastCut !== '' ? strtotime($lastCut) : false;
            $routerLogoutTs = $routerLogout !== '' ? strtotime($routerLogout) : false;
            if ($routerLogoutTs && $routerLogoutTs >= 946684800 && (!$lastCutTs || $routerLogoutTs >= $lastCutTs)) $lastCut = $routerLogout;
            $lastSeen = trim((string)($row['last_seen_at'] ?? ''));
            $lastSeenTs = $lastSeen !== '' ? strtotime($lastSeen) : false;
            if ((int)($row['is_online'] ?? 0) !== 1 && $routerLogoutTs && $routerLogoutTs >= 946684800 && (!$lastSeenTs || $routerLogoutTs >= $lastSeenTs)) $lastSeen = $routerLogout;
            $sector = trim((string)($row['airmax_device_name'] ?? ''));
            if ($sector === '') $sector = trim((string)($row['_sector_name'] ?? ''));
            $isDisabled = scp_is_disabled($row);
            $statusInfo = function_exists('sub_status_info')
                ? sub_status_info((int)($row['is_online'] ?? 0) === 1, $isDisabled, $row, $activeRow)
                : ['label' => scp_status_label($row)];
            $profile = trim((string)($row['ppp_profile'] ?? $row['pppoe_profile'] ?? $row['mt_profile'] ?? ''));
            if ($credentialMode === 'encrypted') {
                $stageStatus = function_exists('subs_policy_staged_account_status')
                    ? subs_policy_staged_account_status($conn, $row)
                    : ['ready' => false];
                $profile = !empty($stageStatus['ready']) ? trim((string)($row['pending_ppp_profile'] ?? '')) : '';
            }
            $complete = !function_exists('sc_contract_missing_fields') || sc_contract_missing_fields($row, $requiredFields) === [];
            $rows[] = [
                'id' => (string)(int)$row['id'],
                'name' => $displayName !== '' ? $displayName : '—',
                'profile' => $profile !== '' ? $profile : '—',
                'sector' => $sector !== '' ? $sector : '—',
                'status' => (string)($statusInfo['label'] ?? scp_status_label($row)),
                'last_seen' => (int)($row['is_online'] ?? 0) === 1 ? 'الآن' : scp_time_display($lastSeen, true),
                'last_cut' => scp_time_display($lastCut),
                'usage' => number_format($usage / 1073741824, 2) . ' GB',
                '_complete' => $complete,
            ];
        }
        return $rows;
    }
}
