<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // Resolve duplicates among non-deleted rows by appending the row id to later duplicates
        $dupes = DB::table('contracts')
            ->select('client_case_id', 'reference', DB::raw('COUNT(*) as cnt'))
            ->whereNull('deleted_at')
            ->whereNotNull('reference')
            ->groupBy('client_case_id', 'reference')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        foreach ($dupes as $d) {
            $rows = DB::table('contracts')
                ->where('client_case_id', $d->client_case_id)
                ->where('reference', $d->reference)
                ->whereNull('deleted_at')
                ->orderBy('id')
                ->get(['id', 'reference']);

            $keepFirst = true;
            foreach ($rows as $row) {
                if ($keepFirst) {
                    $keepFirst = false;

                    continue;
                }
                $base = mb_substr($row->reference, 0, 120);
                $newRef = $base.'-'.$row->id;
                DB::table('contracts')->where('id', $row->id)->update(['reference' => $newRef]);
            }
        }

        // Create a partial unique index (Postgres) for non-deleted rows
        DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS contracts_client_case_reference_unique ON contracts (client_case_id, reference) WHERE deleted_at IS NULL');
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS contracts_client_case_reference_unique');
    }
};
