admin_api/app/Http/Controllers/Admin/GameEnterLogController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\GameEnterLog;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class GameEnterLogController extends Controller
{
    /**
     * Average game enter duration statistics per game.
     */
    public function statistics(Request $request)
    {
        $startDate = $request->input('start_date', date('Y-m-d', strtotime('-7 days')));
        $endDate = $request->input('end_date', date('Y-m-d'));

        $rows = DB::table(GameEnterLog::TABLE)
            ->whereBetween('log_date', [$startDate, $endDate])
            ->selectRaw('game_id, COUNT(*) as enter_count, AVG(CAST(duration_ms AS FLOAT)) as avg_duration_ms')
            ->groupBy('game_id')
            ->orderByDesc('enter_count')
            ->get();

        $gameIds = $rows->pluck('game_id')->filter()->unique()->values()->toArray();
        $games = [];
        if (!empty($gameIds)) {
            $gamesData = DB::connection('mysql')
                ->table('webgame.games')
                ->whereIn('id', $gameIds)
                ->select('id', 'brand', 'title')
                ->get();
            foreach ($gamesData as $game) {
                $games[$game->id] = $game;
            }
        }

        $statistics = [];
        foreach ($rows as $row) {
            $gameId = (int) $row->game_id;
            $avgMs = (float) $row->avg_duration_ms;
            $gameName = __('auto.未知游戏');
            if (isset($games[$gameId])) {
                $game = $games[$gameId];
                $gameName = $game->brand . ' - ' . $game->title;
            }

            $statistics[] = [
                'game_id' => $gameId,
                'game_name' => $gameName,
                'enter_count' => (int) $row->enter_count,
                'avg_duration_ms' => round($avgMs, 2),
                'avg_duration_sec' => round($avgMs / 1000, 3),
            ];
        }

        return view('admin.game_enter_log.statistics', [
            'statistics' => $statistics,
            'start_date' => $startDate,
            'end_date' => $endDate,
        ]);
    }
}