<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('artwork_medals', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('artwork_id');
            $table->unsignedBigInteger('user_id');
            $table->enum('medal_type', ['gold', 'silver', 'bronze']);
            $table->unsignedTinyInteger('weight')->default(1);
            $table->timestamps();

            $table->unique(['artwork_id', 'user_id'], 'artwork_medals_artwork_user_unique');
            $table->index(['artwork_id', 'medal_type'], 'artwork_medals_artwork_medal_type_index');
            $table->index('user_id', 'artwork_medals_user_id_index');
            $table->index('updated_at', 'artwork_medals_updated_at_index');

            $table->foreign('artwork_id')
                ->references('id')->on('artworks')
                ->cascadeOnDelete();
            $table->foreign('user_id')
                ->references('id')->on('users')
                ->cascadeOnDelete();
        });

        Schema::create('artwork_medal_stats', function (Blueprint $table): void {
            $table->unsignedBigInteger('artwork_id')->primary();
            $table->unsignedInteger('gold_count')->default(0);
            $table->unsignedInteger('silver_count')->default(0);
            $table->unsignedInteger('bronze_count')->default(0);
            $table->unsignedInteger('score_total')->default(0);
            $table->unsignedInteger('score_7d')->default(0);
            $table->unsignedInteger('score_30d')->default(0);
            $table->timestamp('last_medaled_at')->nullable();
            $table->timestamps();

            $table->index('score_total', 'artwork_medal_stats_score_total_index');
            $table->index('score_30d', 'artwork_medal_stats_score_30d_index');
            $table->index('last_medaled_at', 'artwork_medal_stats_last_medaled_at_index');

            $table->foreign('artwork_id')
                ->references('id')->on('artworks')
                ->cascadeOnDelete();
        });

        $this->copyExistingMedals();
        $this->copyExistingMedalStats();
    }

    public function down(): void
    {
        Schema::dropIfExists('artwork_medal_stats');
        Schema::dropIfExists('artwork_medals');
    }

    private function copyExistingMedals(): void
    {
        if (! Schema::hasTable('artwork_awards')) {
            return;
        }

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

        foreach ($rows->chunk(250) as $chunk) {
            DB::table('artwork_medals')->insert($chunk->map(static function ($row): array {
                return [
                    'artwork_id' => (int) $row->artwork_id,
                    'user_id' => (int) $row->user_id,
                    'medal_type' => (string) $row->medal,
                    'weight' => (int) $row->weight,
                    'created_at' => $row->created_at,
                    'updated_at' => $row->updated_at,
                ];
            })->all());
        }
    }

    private function copyExistingMedalStats(): void
    {
        if (! Schema::hasTable('artwork_award_stats')) {
            return;
        }

        $rows = DB::table('artwork_award_stats')->get();

        foreach ($rows->chunk(250) as $chunk) {
            DB::table('artwork_medal_stats')->insert($chunk->map(static function ($row): array {
                $updatedAt = $row->updated_at ?? now();

                return [
                    'artwork_id' => (int) $row->artwork_id,
                    'gold_count' => (int) ($row->gold_count ?? 0),
                    'silver_count' => (int) ($row->silver_count ?? 0),
                    'bronze_count' => (int) ($row->bronze_count ?? 0),
                    'score_total' => (int) ($row->score_total ?? 0),
                    'score_7d' => (int) ($row->score_7d ?? 0),
                    'score_30d' => (int) ($row->score_30d ?? 0),
                    'last_medaled_at' => $row->last_medaled_at ?? null,
                    'created_at' => $updatedAt,
                    'updated_at' => $updatedAt,
                ];
            })->all());
        }
    }
};