<?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('person', function (Blueprint $table) {
            $table->text('full_name_search')->nullable();
        });

        $this->backfillSearchColumn();

        if ($this->isPostgres()) {
            DB::statement(<<<'SQL'
                ALTER TABLE person
                ADD COLUMN full_name_search_vector tsvector
                GENERATED ALWAYS AS (to_tsvector('simple', coalesce(full_name_search, '')))
                STORED
            SQL);

            DB::statement('CREATE INDEX person_full_name_search_vector_idx ON person USING GIN (full_name_search_vector)');
        }
    }

    public function down(): void
    {
        if ($this->isPostgres()) {
            DB::statement('DROP INDEX IF EXISTS person_full_name_search_vector_idx');
            DB::statement('ALTER TABLE person DROP COLUMN IF EXISTS full_name_search_vector');
        }

        Schema::table('person', function (Blueprint $table) {
            $table->dropColumn('full_name_search');
        });
    }

    private function backfillSearchColumn(): void
    {
        DB::table('person')
            ->select('id', 'first_name', 'last_name', 'full_name')
            ->lazyById()
            ->each(function ($row): void {
                DB::table('person')
                    ->where('id', $row->id)
                    ->update(['full_name_search' => $this->buildSearchValue($row)]);
            });
    }

    private function buildSearchValue(object $row): string
    {
        $segments = array_filter([
            $this->joinParts($row->first_name ?? null, $row->last_name ?? null),
            $this->joinParts($row->last_name ?? null, $row->first_name ?? null),
            $row->full_name ?? null,
        ]);

        if (empty($segments)) {
            return '';
        }

        $normalized = array_unique(array_map(function (string $value): string {
            $collapsed = preg_replace('/\s+/u', ' ', trim($value)) ?: '';

            return mb_strtolower($collapsed);
        }, $segments));

        return trim(implode(' ', array_filter($normalized)));
    }

    private function joinParts(?string $first, ?string $second): ?string
    {
        $parts = array_filter([$first, $second], fn ($part) => filled($part));

        if (empty($parts)) {
            return null;
        }

        return trim(implode(' ', $parts));
    }

    private function isPostgres(): bool
    {
        return DB::connection()->getDriverName() === 'pgsql';
    }
};
