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');

        // 每位用户最近一次成功登录
        $loginBase = DB::table('user_login');
        if ($hasLoginStatus) {
            $loginBase->where('status', UserLogin::STATUS_SUCCESS);
        }
        $latestLogin = $loginBase
            ->selectRaw('user_id, MAX(id) as id')
            ->groupBy('user_id');

        $query = DB::table('users')
            ->leftJoin('wallets', 'wallets.member_id', '=', 'users.member_id')
            ->leftJoinSub($latestLogin, 'latest_login', function ($join) {
                $join->on('latest_login.user_id', '=', 'users.user_id');
            })
            ->leftJoin('user_login as ul', 'ul.id', '=', 'latest_login.id')
            ->where(function ($q) use ($now, $activeSince) {
                // 1) 有效 token 会话
                $q->whereExists(function ($sub) use ($now) {
                    $sub->select(DB::raw(1))
                        ->from('user_session as us')
                        ->where(function ($w) {
                            $w->whereColumn('us.user_id', 'users.user_id')
                                ->orWhereColumn('us.user_id', 'users.member_id');
                        })
                        ->where('us.expire_time', '>', $now);
                })
                // 2) 或最近活跃（unix 秒级时间戳）
                ->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'];
            $query->where(function ($q) use ($mid) {
                $q->where('users.member_id', $mid)->orWhere('users.user_id', $mid);
            });
        }
        if (!empty($params['first_name'])) {
            $query->where('users.first_name', 'like', '%' . $params['first_name'] . '%');
        }
        if (!empty($params['login_ip'])) {
            $query->where('ul.login_ip', 'like', '%' . $params['login_ip'] . '%');
        }
        if (!empty($params['platform']) && $params['platform'] !== '全部' && $hasPlatform) {
            $query->where('ul.platform', $params['platform']);
        }

        $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';
        }
        $query->select($select);

        $total = (int)(clone $query)
            ->reorder()
            ->selectRaw('COUNT(DISTINCT users.id) as aggregate')
            ->value('aggregate');

        $rows = $query
            ->orderByDesc('users.last_active_time')
            ->offset(($page - 1) * $limit)
            ->limit($limit)
            ->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';
    }
}