<?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
{
    private const LEGACY_INDEX = 'messages_conversation_client_temp_idx';

    private const UNIQUE_INDEX = 'messages_sender_client_temp_unique';

    public function up(): void
    {
        if (! Schema::hasTable('messages') || ! Schema::hasColumn('messages', 'client_temp_id')) {
            return;
        }

        DB::table('messages')
            ->where('client_temp_id', '')
            ->update(['client_temp_id' => null]);

        $duplicates = DB::table('messages')
            ->select(['conversation_id', 'sender_id', 'client_temp_id'])
            ->whereNotNull('client_temp_id')
            ->groupBy('conversation_id', 'sender_id', 'client_temp_id')
            ->havingRaw('count(*) > 1')
            ->get();

        foreach ($duplicates as $duplicate) {
            $idsToClear = DB::table('messages')
                ->where('conversation_id', $duplicate->conversation_id)
                ->where('sender_id', $duplicate->sender_id)
                ->where('client_temp_id', $duplicate->client_temp_id)
                ->orderBy('id')
                ->pluck('id')
                ->slice(1)
                ->all();

            if ($idsToClear === []) {
                continue;
            }

            DB::table('messages')
                ->whereIn('id', $idsToClear)
                ->update(['client_temp_id' => null]);
        }

        Schema::table('messages', function (Blueprint $table): void {
            $schema = Schema::getConnection()->getSchemaBuilder();

            if ($schema->hasIndex('messages', self::LEGACY_INDEX)) {
                $table->dropIndex(self::LEGACY_INDEX);
            }

            if (! $schema->hasIndex('messages', self::UNIQUE_INDEX)) {
                $table->unique(['conversation_id', 'sender_id', 'client_temp_id'], self::UNIQUE_INDEX);
            }
        });
    }

    public function down(): void
    {
        if (! Schema::hasTable('messages') || ! Schema::hasColumn('messages', 'client_temp_id')) {
            return;
        }

        Schema::table('messages', function (Blueprint $table): void {
            $schema = Schema::getConnection()->getSchemaBuilder();

            if ($schema->hasIndex('messages', self::UNIQUE_INDEX)) {
                $table->dropUnique(self::UNIQUE_INDEX);
            }

            if (! $schema->hasIndex('messages', self::LEGACY_INDEX)) {
                $table->index(['conversation_id', 'client_temp_id'], self::LEGACY_INDEX);
            }
        });
    }
};
