<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('artwork_awards', function (Blueprint $table): void {
            $table->index(['artwork_id', 'medal'], 'artwork_awards_artwork_medal_index');
            $table->index('updated_at', 'artwork_awards_updated_at_index');
        });

        Schema::table('artwork_award_stats', function (Blueprint $table): void {
            $table->unsignedInteger('score_7d')->default(0)->after('score_total');
            $table->unsignedInteger('score_30d')->default(0)->after('score_7d');
            $table->timestamp('last_medaled_at')->nullable()->after('score_30d');

            $table->index('score_total', 'artwork_award_stats_score_total_index');
            $table->index('score_30d', 'artwork_award_stats_score_30d_index');
            $table->index('last_medaled_at', 'artwork_award_stats_last_medaled_at_index');
        });

        $this->applyWeights([
            'gold' => 5,
            'silver' => 3,
            'bronze' => 1,
        ]);

        $this->rebuildStats([
            'gold' => 5,
            'silver' => 3,
            'bronze' => 1,
        ], true);
    }

    public function down(): void
    {
        $this->applyWeights([
            'gold' => 3,
            'silver' => 2,
            'bronze' => 1,
        ]);

        $this->rebuildStats([
            'gold' => 3,
            'silver' => 2,
            'bronze' => 1,
        ], false);

        Schema::table('artwork_award_stats', function (Blueprint $table): void {
            $table->dropIndex('artwork_award_stats_score_total_index');
            $table->dropIndex('artwork_award_stats_score_30d_index');
            $table->dropIndex('artwork_award_stats_last_medaled_at_index');
            $table->dropColumn(['score_7d', 'score_30d', 'last_medaled_at']);
        });

        Schema::table('artwork_awards', function (Blueprint $table): void {
            $table->dropIndex('artwork_awards_artwork_medal_index');
            $table->dropIndex('artwork_awards_updated_at_index');
        });
    }

    /**
     * @param array<string, int> $weights
     */
    private function applyWeights(array $weights): void
    {
        foreach ($weights as $medal => $weight) {
            DB::table('artwork_awards')
                ->where('medal', $medal)
                ->update(['weight' => $weight]);
        }
    }

    /**
     * @param array<string, int> $weights
     */
    private function rebuildStats(array $weights, bool $includeRecentColumns): void
    {
        DB::table('artwork_award_stats')->delete();

        $rows = DB::table('artwork_awards')
            ->orderBy('artwork_id')
            ->get(['artwork_id', 'medal', 'updated_at']);

        $cutoff7d = Carbon::now()->subDays(7);
        $cutoff30d = Carbon::now()->subDays(30);
        $payload = [];

        foreach ($rows->groupBy('artwork_id') as $artworkId => $artworkRows) {
            $gold = 0;
            $silver = 0;
            $bronze = 0;
            $scoreTotal = 0;
            $score7d = 0;
            $score30d = 0;
            $lastMedaledAt = null;

            foreach ($artworkRows as $row) {
                $medal = (string) $row->medal;
                $updatedAt = $row->updated_at ? Carbon::parse($row->updated_at) : null;
                $weight = (int) ($weights[$medal] ?? 0);

                if ($medal === 'gold') {
                    $gold++;
                } elseif ($medal === 'silver') {
                    $silver++;
                } elseif ($medal === 'bronze') {
                    $bronze++;
                }

                $scoreTotal += $weight;

                if ($updatedAt && $updatedAt->greaterThanOrEqualTo($cutoff7d)) {
                    $score7d += $weight;
                }

                if ($updatedAt && $updatedAt->greaterThanOrEqualTo($cutoff30d)) {
                    $score30d += $weight;
                }

                if ($updatedAt && ($lastMedaledAt === null || $updatedAt->greaterThan($lastMedaledAt))) {
                    $lastMedaledAt = $updatedAt;
                }
            }

            $rowPayload = [
                'artwork_id' => (int) $artworkId,
                'gold_count' => $gold,
                'silver_count' => $silver,
                'bronze_count' => $bronze,
                'score_total' => $scoreTotal,
                'updated_at' => now(),
            ];

            if ($includeRecentColumns) {
                $rowPayload['score_7d'] = $score7d;
                $rowPayload['score_30d'] = $score30d;
                $rowPayload['last_medaled_at'] = $lastMedaledAt;
            }

            $payload[] = $rowPayload;
        }

        foreach (array_chunk($payload, 250) as $chunk) {
            DB::table('artwork_award_stats')->insert($chunk);
        }
    }
};