<?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::table('meetup_participants', function (Blueprint $table) {
            $table->dropUnique(['user_id']);
        });

        // One Pass/Attend account (same email) keeps one user_id across events.
        $emails = DB::table('meetup_participants')
            ->selectRaw('LOWER(email) as email_key, MIN(id) as keep_id')
            ->whereNotNull('email')
            ->where('email', '!=', '')
            ->groupBy('email_key')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        foreach ($emails as $row) {
            $canonical = DB::table('meetup_participants')->where('id', $row->keep_id)->value('user_id');
            if (! $canonical) {
                continue;
            }

            DB::table('meetup_participants')
                ->whereRaw('LOWER(email) = ?', [$row->email_key])
                ->where(function ($q) use ($canonical) {
                    $q->whereNull('user_id')->orWhere('user_id', '!=', $canonical);
                })
                ->update(['user_id' => $canonical]);
        }

        Schema::table('meetup_participants', function (Blueprint $table) {
            $table->unique(['event_id', 'user_id']);
        });
    }

    public function down(): void
    {
        Schema::table('meetup_participants', function (Blueprint $table) {
            $table->dropUnique(['event_id', 'user_id']);
            $table->unique('user_id');
        });
    }
};
