app/Console/Commands/RollupAnalyticsFunnels.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_funnel_progress rows started on a single calendar date
 * into analytics_daily_funnels.
 *
 * Safe to re-run: upsert on (summary_date, module) replaces existing counts.
 *
 * Schedule: daily at 02:15 UTC (see bootstrap/app.php →withSchedule).
 * Manual backfill: php artisan mdn:analytics:rollup-funnels --date=2026-07-10
 */
class RollupAnalyticsFunnels extends Command
{
    protected $signature = 'mdn:analytics:rollup-funnels
                            {--date= : Date to aggregate (YYYY-MM-DD). Defaults to yesterday.}';

    protected $description = 'Roll up funnel_progress rows into analytics_daily_funnels.';

    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 funnel progress for {$date} …");

        $rows = DB::table('analytics_funnel_progress')
            ->selectRaw("
                DATE(started_at)                            AS summary_date,
                module,
                COUNT(*)                                    AS starts,
                SUM(IF(highest_step >= 0, 1, 0))           AS step_0_reached,
                SUM(IF(highest_step >= 1, 1, 0))           AS step_1_reached,
                SUM(IF(highest_step >= 2, 1, 0))           AS step_2_reached,
                SUM(IF(highest_step >= 3, 1, 0))           AS step_3_reached,
                SUM(IF(highest_step >= 4, 1, 0))           AS step_4_reached,
                SUM(IF(highest_step >= 5, 1, 0))           AS step_5_reached,
                SUM(IF(highest_step >= 6, 1, 0))           AS step_6_reached,
                SUM(submitted)                             AS submissions,
                SUM(abandoned)                             AS abandonments
            ")
            ->whereDate('started_at', $date)
            ->groupByRaw('DATE(started_at), module')
            ->get();

        if ($rows->isEmpty()) {
            $this->line("  No funnel_progress rows 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,
            'module'         => $r->module,
            'starts'         => (int) $r->starts,
            'step_0_reached' => (int) $r->step_0_reached,
            'step_1_reached' => (int) $r->step_1_reached,
            'step_2_reached' => (int) $r->step_2_reached,
            'step_3_reached' => (int) $r->step_3_reached,
            'step_4_reached' => (int) $r->step_4_reached,
            'step_5_reached' => (int) $r->step_5_reached,
            'step_6_reached' => (int) $r->step_6_reached,
            'submissions'    => (int) $r->submissions,
            'abandonments'   => (int) $r->abandonments,
            'created_at'     => $now,
            'updated_at'     => $now,
        ])->all();

        DB::table('analytics_daily_funnels')->upsert(
            $upsertRows,
            ['summary_date', 'module'],
            [
                'starts', 'step_0_reached', 'step_1_reached', 'step_2_reached',
                'step_3_reached', 'step_4_reached', 'step_5_reached', 'step_6_reached',
                'submissions', 'abandonments', 'updated_at',
            ],
        );

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

        return self::SUCCESS;
    }
}