<?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
    {
        $duplicateIds = DB::table('jackpot_contributions')
            ->select('ticket_item_id')
            ->whereNotNull('ticket_item_id')
            ->groupBy('ticket_item_id')
            ->havingRaw('count(*) > 1')
            ->pluck('ticket_item_id');

        foreach ($duplicateIds as $ticketItemId) {
            $rows = DB::table('jackpot_contributions')
                ->where('ticket_item_id', $ticketItemId)
                ->orderByDesc('id')
                ->pluck('id');
            $keep = $rows->shift();
            if ($keep !== null && $rows->isNotEmpty()) {
                DB::table('jackpot_contributions')->whereIn('id', $rows->all())->delete();
            }
        }

        Schema::table('jackpot_contributions', function (Blueprint $table): void {
            $table->unique('ticket_item_id', 'uk_jackpot_contributions_ticket_item');
        });
    }

    public function down(): void
    {
        Schema::table('jackpot_contributions', function (Blueprint $table): void {
            $table->dropUnique('uk_jackpot_contributions_ticket_item');
        });
    }
};
