HomeController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Carbon\Carbon;
use Illuminate\Support\Facades\Route;
use Illuminate\Support\Str;
use Illuminate\View\View;
use SlProjects\LaravelRequestLogger\app\Models\LoggedRequest;

class HomeController extends Controller
{
    public function index(): View
    {
        $publicRoutes = collect(Route::getRoutes()->getRoutes())
            ->filter(fn ($route) => ! Str::startsWith($route->uri, ['admin', 'api']) && ! empty($route->uri) && $route->uri !== '/')
            ->pluck('uri');

        $visits = LoggedRequest::selectRaw('
                logged_requests.url_id,
                logged_requests.ip_address_id,
                logged_requests.country_code,
                logged_requests.created_at,
                urls.url
            ')
            ->distinct('logged_requests.url_id', 'logged_requests.ip_address_id')
            ->join('urls', 'logged_requests.url_id', '=', 'urls.id')
            ->join('user_agent_metadata', 'logged_requests.user_agent_id', '=', 'user_agent_metadata.user_agent_id')
            ->where(function ($query) use ($publicRoutes) {
                foreach ($publicRoutes as $uri) {
                    $query->orWhere('urls.url', 'like', config('app.url').'/'.$uri.'%');
                }
            })
            ->where('user_agent_metadata.is_bot', false)
            ->where('status_code', 200)
            ->get();

        $now = now();
        $totalVisitsPastTwentyFourHours = $visits->where('created_at', '>=', $now->copy()->subDay())->count();
        $totalVisitsPastSevenDays = $visits->where('created_at', '>=', $now->copy()->subDays(7))->count();
        $totalVisitsPastThirtyDays = $visits->where('created_at', '>=', $now->copy()->subDays(30))->count();
        $totalVisitsAllTime = $visits->count();

        $visitsPerDay = $visits->groupBy(fn ($visit) => Carbon::parse($visit->created_at)->format('Y-m-d'))
            ->map(fn ($group) => ['date' => $group->first()->created_at->format('Y-m-d'), 'count' => $group->count()])
            ->values();

        $visitsByCountry = $visits->groupBy('country_code')
            ->map(fn ($group, $country) => ['country_code' => $country, 'count' => $group->count()])
            ->values();

        $mostVisitedPages = $visits->groupBy('url')
            ->map(fn ($group, $url) => ['url' => $url, 'count' => $group->count()])
            ->sortByDesc('count')
            ->values();

        $mostVisitedPagesForPastTwentyFourHours = $visits->where('created_at', '>=', $now->copy()->subDay())
            ->groupBy('url')
            ->map(fn ($group, $url) => ['url' => $url, 'count' => $group->count()])
            ->sortByDesc('count')
            ->values();

        return view('admin.home', [
            'totalVisitsPastTwentyFourHours' => $totalVisitsPastTwentyFourHours,
            'totalVisitsPastSevenDays' => $totalVisitsPastSevenDays,
            'totalVisitsPastThirtyDays' => $totalVisitsPastThirtyDays,
            'totalVisitsAllTime' => $totalVisitsAllTime,
            'visitsPerDay' => $visitsPerDay,
            'visitsByCountry' => $visitsByCountry,