bot-28/app/Services/OnlineUserService.php

<?php

namespace App\Services;

use App\Models\User;
use App\Models\UserLogin;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 在线用户列表（只读）
 *
 * 在线判定（满足其一即可）：
 * 1. 存在未过期 user_session（expire_time 为 unix 时间戳）
 * 2. last_active_time 在最近 ONLINE_WINDOW_SECONDS 内（unix 时间戳）
 *
 * 说明：last_active_time 多半只在登录时更新，仅靠它会导致「有会话却查不到」。
 */
class OnlineUserService
{
    /** 最近活跃兜底窗口（秒） */
    public const ONLINE_WINDOW_SECONDS = 1800;

    /**
     * @param array{page?:int,limit?:int,member_id?:string,first_name?:string,login_ip?:string,platform?:string} $params
     * @return array{total:int,data:list<array>}
     */
    public static function list(array $params): array
    {
        $page = max(1, (int)($params['page'] ?? 1));
        $limit = min(200, max(1, (int)($params['limit'] ?? 15)));
        $now = time();
        $activeSince = $now - self::ONLINE_WINDOW_SECONDS;

        $hasLoginStatus = Schema::hasColumn('user_login', 'status');
        $hasLoginDomain = Schema::hasColumn('user_login', 'login_domain');
        $hasPlatform = Schema::hasColumn('user_login', 'platform');
        $hasBrowser = Schema::hasColumn('user_login', 'browser');
        $hasOs = Schema::hasColumn('user_login', 'os');
        $hasCountry = Schema::hasColumn('user_login', 'country');

        // 先筛在线用户 ID（避免 join 干扰 count / 前缀别名问题）
        // user_session.user_id 与 users.user_id 排序规则可能不一致（unicode_ci vs general_ci），比较时统一 COLLATE
        $onlineIdsQuery = DB::table('users')
            ->where(function ($q) use ($now, $activeSince) {
                $q->whereExists(function ($sub) use ($now) {
                    $sub->select(DB::raw(1))
                        ->from('user_session')
                        ->whereRaw(
                            '(user_session.user_id COLLATE utf8mb4_general_ci = users.user_id COLLATE utf8mb4_general_ci'
                            . ' OR user_session.user_id COLLATE utf8mb4_general_ci = users.member_id COLLATE utf8mb4_general_ci)'
                        )
                        ->where('user_session.expire_time', '>', $now);
                })->orWhere(function ($q2) use ($activeSince) {
                    $q2->where('users.last_active_time', '>', 1000000000)
                        ->where('users.last_active_time', '>=', $activeSince);
                });
            })
            ->where(function ($q) {
                $q->whereNull('users.from')->orWhere('users.from', '<>', 2);
            });

        if (!empty($params['member_id'])) {
            $mid = $params['member_id'];
            $onlineIdsQuery->where(function ($q) use ($mid) {
                $q->where('users.member_id', $mid)->orWhere('users.user_id', $mid);
            });
        }
        if (!empty($params['first_name'])) {
            $onlineIdsQuery->where('users.first_name', 'like', '%' . $params['first_name'] . '%');
        }

        // 需要按登录 IP / 平台筛选时，再关联最近登录
        $needLoginFilter = !empty($params['login_ip'])
            || (!empty($params['platform']) && $params['platform'] !== '全部' && $hasPlatform);

        if ($needLoginFilter) {
            $loginFilter = DB::table('user_login as ul')
                ->select('ul.user_id')
                ->whereIn('ul.id', function ($sub) use ($hasLoginStatus) {
                    $sub->from('user_login')
                        ->selectRaw('MAX(id)')
                        ->when($hasLoginStatus, fn ($q) => $q->where('status', UserLogin::STATUS_SUCCESS))
                        ->groupBy('user_id');
                });
            if (!empty($params['login_ip'])) {
                $loginFilter->where('ul.login_ip', 'like', '%' . $params['login_ip'] . '%');
            }
            if (!empty($params['platform']) && $params['platform'] !== '全部' && $hasPlatform) {
                $loginFilter->where('ul.platform', $params['platform']);
            }
            $filteredUserIds = $loginFilter->pluck('ul.user_id')->all();
            if ($filteredUserIds === []) {
                return ['total' => 0, 'data' => []];
            }
            $onlineIdsQuery->where(function ($q) use ($filteredUserIds) {
                $q->whereIn('users.user_id', $filteredUserIds)
                    ->orWhereIn('users.member_id', $filteredUserIds);
            });
        }

        $total = (int)(clone $onlineIdsQuery)->count('users.id');

        $pageIds = (clone $onlineIdsQuery)
            ->orderByDesc('users.last_active_time')
            ->offset(($page - 1) * $limit)
            ->limit($limit)
            ->pluck('users.id')
            ->all();

        if ($pageIds === []) {
            return ['total' => $total, 'data' => []];
        }

        // 最近登录子查询
        $latestLoginSql = DB::table('user_login')
            ->selectRaw('user_id, MAX(id) as max_id')
            ->when($hasLoginStatus, fn ($q) => $q->where('status', UserLogin::STATUS_SUCCESS))
            ->groupBy('user_id');

        $detailQuery = DB::table('users')
            ->leftJoin('wallets', function ($join) {
                $join->whereRaw('wallets.member_id COLLATE utf8mb4_general_ci = users.member_id COLLATE utf8mb4_general_ci');
            })
            ->leftJoinSub($latestLoginSql, 'll', function ($join) {
                $join->whereRaw('ll.user_id COLLATE utf8mb4_general_ci = users.user_id COLLATE utf8mb4_general_ci');
            })
            ->leftJoin('user_login as ul', 'ul.id', '=', 'll.max_id')
            ->whereIn('users.id', $pageIds);

        $select = [
            'users.id',
            'users.member_id',
            'users.user_id',
            'users.first_name',
            'users.last_active_time',
            'wallets.available_balance',
            'ul.login_ip',
            'ul.created_at as login_time',
        ];
        if ($hasCountry) {
            $select[] = 'ul.country';
        }
        if ($hasLoginDomain) {
            $select[] = 'ul.login_domain';
        }
        if ($hasPlatform) {
            $select[] = 'ul.platform';
        }
        if ($hasBrowser) {
            $select[] = 'ul.browser';
        }
        if ($hasOs) {
            $select[] = 'ul.os';
        }

        $rows = $detailQuery
            ->select($select)
            ->orderByDesc('users.last_active_time')
            ->get();

        $data = $rows->map(function ($row) {
            return [
                'member_id' => $row->member_id ?: $row->user_id,
                'first_name' => (string)($row->first_name ?? ''),
                'available_balance' => (float)($row->available_balance ?? 0),
                'login_ip' => (string)($row->login_ip ?? ''),
                'location' => (string)($row->country ?? ''),
                'login_domain' => (string)($row->login_domain ?? ''),
                'platform' => (string)(
                    ($row->platform ?? '') !== ''
                        ? $row->platform
                        : self::guessPlatformFromOs((string)($row->os ?? ''))
                ),
                'browser' => (string)($row->browser ?? ''),
                'os' => (string)($row->os ?? ''),
                'login_time' => self::formatTime($row->login_time ?? null),
                'last_active_time' => self::formatLastActive($row->last_active_time ?? null),
            ];
        })->values()->all();

        return ['total' => $total, 'data' => $data];
    }

    private static function formatTime($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }
        try {
            return Carbon::parse($value)->setTimezone('Asia/Shanghai')->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return (string)$value;
        }
    }

    private static function formatLastActive($value): string
    {
        if ($value === null || $value === '' || $value === 0 || $value === '0') {
            return '';
        }
        if (is_numeric($value) && (int)$value > 1000000000) {
            return Carbon::createFromTimestamp((int)$value, 'Asia/Shanghai')->format('Y-m-d H:i:s');
        }
        return self::formatTime($value);
    }

    private static function guessPlatformFromOs(string $os): string
    {
        $os = strtolower($os);
        if (str_contains($os, 'android')) {
            return 'Android';
        }
        if (str_contains($os, 'ios') || str_contains($os, 'iphone') || str_contains($os, 'ipad')) {
            return 'iOS';
        }
        return 'PC';
    }
}