<?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
    {
        $driver = DB::getDriverName();

        if ($driver === 'pgsql' || $driver === 'sqlite') {
            // Partial unique indexes for non-deleted rows
            DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS unique_emails_person_value_active ON emails(person_id, value) WHERE deleted_at IS NULL');
            DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS unique_person_phones_person_nu_active ON person_phones(person_id, nu) WHERE deleted_at IS NULL');
            DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS unique_person_addresses_person_address_active ON person_addresses(person_id, address) WHERE deleted_at IS NULL');
        } elseif ($driver === 'mysql') {
            // MySQL does not support partial indexes; use a generated stored column to represent active rows
            foreach ([
                ['table' => 'emails', 'col' => 'value', 'idx' => 'unique_emails_person_value_active'],
                ['table' => 'person_phones', 'col' => 'nu', 'idx' => 'unique_person_phones_person_nu_active'],
                ['table' => 'person_addresses', 'col' => 'address', 'idx' => 'unique_person_addresses_person_address_active'],
            ] as $cfg) {
                $table = $cfg['table'];
                $indexName = $cfg['idx'];
                if (! Schema::hasColumn($table, 'active_flag')) {
                    // Use STORED generated column so it can be indexed
                    DB::statement("ALTER TABLE `{$table}` ADD COLUMN `active_flag` TINYINT(1) GENERATED ALWAYS AS (CASE WHEN `deleted_at` IS NULL THEN 1 ELSE 0 END) STORED");
                }
                // Create unique index on (person_id, key, active_flag)
                Schema::table($table, function (Blueprint $t) use ($cfg, $indexName): void {
                    $t->unique(['person_id', $cfg['col'], 'active_flag'], $indexName);
                });
            }
        }
    }

    public function down(): void
    {
        $driver = DB::getDriverName();

        if ($driver === 'pgsql' || $driver === 'sqlite') {
            DB::statement('DROP INDEX IF EXISTS unique_emails_person_value_active');
            DB::statement('DROP INDEX IF EXISTS unique_person_phones_person_nu_active');
            DB::statement('DROP INDEX IF EXISTS unique_person_addresses_person_address_active');
        } elseif ($driver === 'mysql') {
            foreach ([
                ['table' => 'emails', 'col' => 'value', 'idx' => 'unique_emails_person_value_active'],
                ['table' => 'person_phones', 'col' => 'nu', 'idx' => 'unique_person_phones_person_nu_active'],
                ['table' => 'person_addresses', 'col' => 'address', 'idx' => 'unique_person_addresses_person_address_active'],
            ] as $cfg) {
                Schema::table($cfg['table'], function (Blueprint $t) use ($cfg): void {
                    $t->dropUnique($cfg['idx']);
                });
            }
            // Drop generated column
            foreach (['emails', 'person_phones', 'person_addresses'] as $table) {
                if (Schema::hasColumn($table, 'active_flag')) {
                    DB::statement("ALTER TABLE `{$table}` DROP COLUMN `active_flag`");
                }
            }
        }
    }
};
