SkinbaseNova/app/Console/Commands/RecalculateHeatCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Recalculate heat_score for artworks based on hourly metric snapshots.
 *
 * Runs every 10–15 minutes via scheduler.
 *
 * Formula:
 *   raw_heat = views_delta*1 + downloads_delta*3 + favourites_delta*6
 *              + comments_delta*8 + shares_delta*12
 *
 *   age_factor = 1 / (1 + hours_since_upload / 24)
 *
 *   heat_score = raw_heat * age_factor
 *
 * Usage:  php artisan nova:recalculate-heat
 *         php artisan nova:recalculate-heat --days=60 --chunk=1000 --dry-run
 */
class RecalculateHeatCommand extends Command
{
    protected $signature = 'nova:recalculate-heat
                            {--days=60 : Only process artworks created within this many days}
                            {--chunk=1000 : Chunk size for DB queries}
                            {--dry-run : Compute scores without writing to DB}';

    protected $description = 'Recalculate heat/momentum scores for the Rising engine';

    /** Delta weights per the spec */
    private const WEIGHTS = [
        'views'      => 1,
        'downloads'  => 3,
        'favourites' => 6,
        'comments'   => 8,
        'shares'     => 12,
    ];

    public function handle(): int
    {
        $days       = (int) $this->option('days');
        $chunk      = (int) $this->option('chunk');
        $dryRun     = (bool) $this->option('dry-run');
        $now        = now();
        $currentHour = $now->copy()->startOfHour();
        $prevHour    = $currentHour->copy()->subHour();

        $this->info("[nova:recalculate-heat] current_hour={$currentHour->toDateTimeString()} prev_hour={$prevHour->toDateTimeString()} days={$days}" . ($dryRun ? ' (dry-run)' : ''));

        $updatedCount = 0;
        $skippedCount = 0;

        // Process in chunks using artwork IDs that have at least one snapshot in the two hours
        $artworkIds = DB::table('artwork_metric_snapshots_hourly')
            ->whereIn('bucket_hour', [$currentHour, $prevHour])
            ->distinct()
            ->pluck('artwork_id');

        if ($artworkIds->isEmpty()) {
            $this->warn('No snapshots found for the current or previous hour. Run nova:metrics-snapshot-hourly first.');
            return self::SUCCESS;
        }

        // Load all snapshots for the two hours in bulk
        $snapshots = DB::table('artwork_metric_snapshots_hourly')
            ->whereIn('bucket_hour', [$currentHour, $prevHour])
            ->whereIn('artwork_id', $artworkIds)
            ->get()
            ->groupBy('artwork_id');

        // Load artwork published_at dates for age factor (use published_at, fall back to created_at)
        $artworkDates = DB::table('artworks')
            ->whereIn('id', $artworkIds)
            ->whereNull('deleted_at')
            ->where('is_approved', true)
            ->select('id', 'published_at', 'created_at')
            ->get()
            ->mapWithKeys(fn ($row) => [
                $row->id => \Carbon\Carbon::parse($row->published_at ?? $row->created_at),
            ]);

        // Process in chunks
        foreach ($artworkIds->chunk($chunk) as $chunkIds) {
            $upsertRows = [];

            foreach ($chunkIds as $artworkId) {
                $createdAt = $artworkDates->get($artworkId);
                if (!$createdAt) {
                    $skippedCount++;
                    continue;
                }

                $artworkSnapshots = $snapshots->get($artworkId);
                if (!$artworkSnapshots || $artworkSnapshots->isEmpty()) {
                    $skippedCount++;
                    continue;
                }

                $currentSnapshot = $artworkSnapshots->firstWhere('bucket_hour', $currentHour->toDateTimeString());
                $prevSnapshot    = $artworkSnapshots->firstWhere('bucket_hour', $prevHour->toDateTimeString());

                // If we only have one snapshot, use it as current with zero deltas
                if (!$currentSnapshot && !$prevSnapshot) {
                    $skippedCount++;
                    continue;
                }

                // Calculate deltas
                $viewsDelta      = max(0, (int) ($currentSnapshot?->views_count ?? 0)      - (int) ($prevSnapshot?->views_count ?? 0));
                $downloadsDelta  = max(0, (int) ($currentSnapshot?->downloads_count ?? 0)  - (int) ($prevSnapshot?->downloads_count ?? 0));
                $favouritesDelta = max(0, (int) ($currentSnapshot?->favourites_count ?? 0) - (int) ($prevSnapshot?->favourites_count ?? 0));
                $commentsDelta   = max(0, (int) ($currentSnapshot?->comments_count ?? 0)   - (int) ($prevSnapshot?->comments_count ?? 0));
                $sharesDelta     = max(0, (int) ($currentSnapshot?->shares_count ?? 0)     - (int) ($prevSnapshot?->shares_count ?? 0));

                // Raw heat
                $rawHeat = ($viewsDelta * self::WEIGHTS['views'])
                         + ($downloadsDelta * self::WEIGHTS['downloads'])
                         + ($favouritesDelta * self::WEIGHTS['favourites'])
                         + ($commentsDelta * self::WEIGHTS['comments'])
                         + ($sharesDelta * self::WEIGHTS['shares']);

                // Age factor: favors newer works
                $hoursSinceUpload = abs($now->floatDiffInHours($createdAt));
                $ageFactor = 1.0 / (1.0 + ($hoursSinceUpload / 24.0));

                // Final heat score
                $heatScore = max(0, $rawHeat * $ageFactor);

                $upsertRows[] = [
                    'artwork_id'           => $artworkId,
                    'heat_score'           => round($heatScore, 4),
                    'heat_score_updated_at' => $now,
                    'views_1h'             => $viewsDelta,
                    'downloads_1h'         => $downloadsDelta,
                    'favourites_1h'        => $favouritesDelta,
                    'comments_1h'          => $commentsDelta,
                    'shares_1h'            => $sharesDelta,
                ];

                $updatedCount++;
            }

            if (!$dryRun && !empty($upsertRows)) {
                DB::table('artwork_stats')->upsert(
                    $upsertRows,
                    ['artwork_id'],
                    ['heat_score', 'heat_score_updated_at', 'views_1h', 'downloads_1h', 'favourites_1h', 'comments_1h', 'shares_1h']
                );
            }
        }

        $this->info("Heat scores updated: {$updatedCount} | Skipped: {$skippedCount}");

        Log::info('[nova:recalculate-heat] completed', [
            'updated'  => $updatedCount,
            'skipped'  => $skippedCount,
            'dry_run'  => $dryRun,
        ]);

        return self::SUCCESS;
    }
}