bot-28/app/Services/OnlineUserService.php

<?php

namespace App\Services;

use App\Models\UserLogin;
use App\Support\LoginPlatform;
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 时间戳）
 *
 * platform 口径由登录写入侧一次写定；本服务只读、映射，不再二次推断 APP/H5。
 *
 * 注意：本库表前缀 + 多表 collation 不一致，涉及跨表字符串比较一律用带前缀表名 + COLLATE。
 */
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)));
        $offset = ($page - 1) * $limit;

        $p = DB::getTablePrefix();
        $users = $p . 'users';
        $session = $p . 'user_session';
        $wallets = $p . 'wallets';
        $login = $p . 'user_login';

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

        [$onlineSql, $bindings] = self::onlinePredicateSql($session, 'u');
        $where = [$onlineSql, '(u.`from` IS NULL OR u.`from` <> 2)'];

        if (!empty($params['member_id'])) {
            $where[] = '(u.member_id = ? OR u.user_id = ?)';
            $bindings[] = $params['member_id'];
            $bindings[] = $params['member_id'];
        }
        if (!empty($params['first_name'])) {
            $where[] = 'u.first_name LIKE ?';
            $bindings[] = '%' . $params['first_name'] . '%';
        }

        $statusCond = $hasLoginStatus ? 'AND status = ' . (int)UserLogin::STATUS_SUCCESS : '';
        if (!empty($params['login_ip']) || (!empty($params['platform']) && $params['platform'] !== '全部' && $hasPlatform)) {
            $loginFilters = [];
            if (!empty($params['login_ip'])) {
                $loginFilters[] = 'ul.login_ip LIKE ?';
                $bindings[] = '%' . $params['login_ip'] . '%';
            }
            if (!empty($params['platform']) && $params['platform'] !== '全部' && $hasPlatform) {
                $loginFilters[] = 'ul.platform = ?';
                $bindings[] = $params['platform'];
            }
            $loginFilterSql = implode(' AND ', $loginFilters);
            $where[] = "EXISTS (
                SELECT 1 FROM {$login} ul
                INNER JOIN (
                    SELECT user_id, MAX(id) AS max_id
                    FROM {$login}
                    WHERE 1=1 {$statusCond}
                    GROUP BY user_id
                ) latest ON latest.max_id = ul.id
                WHERE ul.user_id COLLATE utf8mb4_general_ci = u.user_id COLLATE utf8mb4_general_ci
                  AND {$loginFilterSql}
            )";
        }

        $whereSql = implode(' AND ', $where);

        $total = (int)DB::selectOne(
            "SELECT COUNT(*) AS c FROM {$users} u WHERE {$whereSql}",
            $bindings
        )->c;

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

        $loginCols = ['ul.login_ip', 'ul.created_at AS login_time'];
        if ($hasCountry) {
            $loginCols[] = 'ul.country';
        }
        if ($hasLoginDomain) {
            $loginCols[] = 'ul.login_domain';
        }
        if ($hasPlatform) {
            $loginCols[] = 'ul.platform';
        }
        if ($hasBrowser) {
            $loginCols[] = 'ul.browser';
        }
        if ($hasOs) {
            $loginCols[] = 'ul.os';
        }
        $loginColSql = implode(', ', $loginCols);

        $listBindings = $bindings;
        $listBindings[] = $offset;
        $listBindings[] = $limit;

        $rows = DB::select(
            "SELECT
                u.id,
                u.member_id,
                u.user_id,
                u.first_name,
                u.last_active_time,
                w.available_balance,
                {$loginColSql}
             FROM {$users} u
             LEFT JOIN {$wallets} w
                ON w.member_id COLLATE utf8mb4_general_ci = u.member_id COLLATE utf8mb4_general_ci
             LEFT JOIN (
                SELECT user_id, MAX(id) AS max_id
                FROM {$login}
                WHERE 1=1 {$statusCond}
                GROUP BY user_id
             ) latest ON latest.user_id COLLATE utf8mb4_general_ci = u.user_id COLLATE utf8mb4_general_ci
             LEFT JOIN {$login} ul ON ul.id = latest.max_id
             WHERE {$whereSql}
             ORDER BY u.last_active_time DESC
             LIMIT ?, ?",
            $listBindings
        );

        $data = array_map(static function ($row) {
            $row = (array)$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 则空，避免与计数口径漂移
                'platform' => (string)($row['platform'] ?? ''),
                '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),
            ];
        }, $rows);

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

    /**
     * 当前在线用户按端计数（与 list 相同在线判定）
     *
     * 信任 user_login.platform 写入值，GROUP BY 后映射：
     * - apple   ← iOS
     * - android ← Android
     * - pc      ← PC
     * - mobile  ← H5 + WeChat（手机 H5）
     *
     * @return array{total:int,apple:int,android:int,pc:int,mobile:int,wechat:int,unknown:int}
     */
    public static function platformCounts(): array
    {
        $p = DB::getTablePrefix();
        $users = $p . 'users';
        $session = $p . 'user_session';
        $login = $p . 'user_login';

        $statusCond = Schema::hasColumn('user_login', 'status')
            ? 'AND status = ' . (int)UserLogin::STATUS_SUCCESS
            : '';

        [$onlineSql, $bindings] = self::onlinePredicateSql($session, 'u');
        $whereSql = $onlineSql . ' AND (u.`from` IS NULL OR u.`from` <> 2)';

        $rows = DB::select(
            "SELECT
                COALESCE(NULLIF(ul.platform, ''), '') AS platform,
                COUNT(*) AS c
             FROM {$users} u
             LEFT JOIN (
                SELECT user_id, MAX(id) AS max_id
                FROM {$login}
                WHERE 1=1 {$statusCond}
                GROUP BY user_id
             ) latest ON latest.user_id COLLATE utf8mb4_general_ci = u.user_id COLLATE utf8mb4_general_ci
             LEFT JOIN {$login} ul ON ul.id = latest.max_id
             WHERE {$whereSql}
             GROUP BY COALESCE(NULLIF(ul.platform, ''), '')",
            $bindings
        );

        $counts = LoginPlatform::emptyCounts();
        foreach ($rows as $row) {
            $platform = (string)($row->platform ?? '');
            $n = (int)($row->c ?? 0);
            $counts['total'] += $n;

            $bucket = LoginPlatform::toBucket($platform);
            $counts[$bucket] += $n;

            if (in_array(strtolower($platform), ['wechat', '微信'], true)) {
                $counts['wechat'] += $n;
            }
        }

        return $counts;
    }

    /**
     * 在线判定 SQL 片段 + bindings（now, activeSince）
     *
     * @return array{0:string,1:list<int>}
     */
    private static function onlinePredicateSql(string $sessionTable, string $userAlias = 'u'): array
    {
        $now = time();
        $activeSince = $now - self::ONLINE_WINDOW_SECONDS;
        $sql = "(
            EXISTS (
                SELECT 1 FROM {$sessionTable} us
                WHERE (
                    us.user_id COLLATE utf8mb4_general_ci = {$userAlias}.user_id COLLATE utf8mb4_general_ci
                    OR us.user_id COLLATE utf8mb4_general_ci = {$userAlias}.member_id COLLATE utf8mb4_general_ci
                )
                AND us.expire_time > ?
            )
            OR ({$userAlias}.last_active_time > 1000000000 AND {$userAlias}.last_active_time >= ?)
        )";
        return [$sql, [$now, $activeSince]];
    }

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