<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('person_addresses', function (Blueprint $table) {
            $table->dropIndex('person_addresses_search_vector_idx');
            $table->dropColumn('search_vector');


            $table->string('post_code', 50)->nullable()->change();

        });

        // Add a generated tsvector column for fulltext search
        DB::statement("
            ALTER TABLE person_addresses 
            ADD COLUMN search_vector tsvector 
            GENERATED ALWAYS AS (
                to_tsvector('simple', 
                    coalesce(address, '') || ' ' || 
                    coalesce(post_code, '') || ' ' || 
                    coalesce(city, '')
                )
            ) STORED
        ");

        // Create GIN index on the tsvector column for fast fulltext search
        DB::statement('CREATE INDEX person_addresses_search_vector_idx ON person_addresses USING GIN(search_vector)');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('person_addresses', function (Blueprint $table) {
            $table->string('post_code', 20)->change();
        });
    }
};
