app/Console/Commands/RollupAnalyticsPageViews.php

<?php

declare(strict_types=1);

namespace App\Console\Commands;

use App\Infrastructure\Ids\UuidGenerator;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * Aggregates analytics_events page_view rows for a single calendar date
 * into analytics_daily_page_views.
 *
 * Safe to re-run: upsert on (summary_date, path) replaces existing counts.
 * Excludes events with bot_score >= 80 (same threshold as the ingestion discard).
 *
 * Schedule: daily at 02:00 UTC (see bootstrap/app.php →withSchedule).
 * Manual backfill: php artisan mdn:analytics:rollup-page-views --date=2026-07-10
 */
class RollupAnalyticsPageViews extends Command
{
    protected $signature = 'mdn:analytics:rollup-page-views
                            {--date= : Date to aggregate (YYYY-MM-DD). Defaults to yesterday.}';

    protected $description = 'Roll up page_view events into analytics_daily_page_views.';

    public function __construct(private readonly UuidGenerator $ids)
    {
        parent::__construct();
    }

    public function handle(): int
    {
        $date = $this->option('date')
            ? Carbon::parse($this->option('date'))->toDateString()
            : Carbon::yesterday()->toDateString();

        $this->line("Aggregating page views for {$date} …");

        $rows = DB::table('analytics_events')
            ->selectRaw("
                DATE(occurred_at)                  AS summary_date,
                COALESCE(path, '/')                AS path,
                module,
                COUNT(*)                           AS page_views,
                COUNT(DISTINCT visitor_id)         AS unique_visitors,
                COUNT(DISTINCT session_id)         AS sessions
            ")
            ->where('event_type', 'page_view')
            ->where('bot_score', '<', 80)
            ->whereDate('occurred_at', $date)
            ->groupByRaw("DATE(occurred_at), COALESCE(path, '/'), module")
            ->get();

        if ($rows->isEmpty()) {
            $this->line("  No page_view events for {$date} — nothing to write.");
            return self::SUCCESS;
        }

        $now        = Carbon::now()->toDateTimeString();
        $upsertRows = $rows->map(fn (object $r) => [
            'id'              => $this->ids->generate(),
            'summary_date'    => $r->summary_date,
            'path'            => $r->path,
            'module'          => $r->module,
            'page_views'      => (int) $r->page_views,
            'unique_visitors' => (int) $r->unique_visitors,
            'sessions'        => (int) $r->sessions,
            'created_at'      => $now,
            'updated_at'      => $now,
        ])->all();

        DB::table('analytics_daily_page_views')->upsert(
            $upsertRows,
            ['summary_date', 'path'],
            ['page_views', 'unique_visitors', 'sessions', 'updated_at'],
        );

        $this->info("  Done. {$rows->count()} path/module combinations written for {$date}.");

        return self::SUCCESS;
    }
}