<?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') {
            // Create PostgreSQL enum type for phone_type if not exists
            DB::statement(<<<'SQL'
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'phone_type_enum') THEN
        CREATE TYPE phone_type_enum AS ENUM ('mobile', 'landline', 'voip');
    END IF;
END$$;
SQL);
        }

        Schema::table('person_phones', function (Blueprint $table): void {
            // validated flag (default false)
            if (! Schema::hasColumn('person_phones', 'validated')) {
                $table->boolean('validated')->default(false)->after('active');
            }
        });

        // Add phone_type column depending on driver
        $hasPhoneType = $driver === 'pgsql'
            ? (bool) DB::selectOne("SELECT 1 as exists FROM information_schema.columns WHERE table_name = 'person_phones' AND column_name = 'phone_type'")
            : Schema::hasColumn('person_phones', 'phone_type');
        if (! $hasPhoneType) {
            if ($driver === 'pgsql') {
                // enum-typed column for Postgres
                DB::statement('ALTER TABLE person_phones ADD COLUMN phone_type phone_type_enum NULL');
            } else {
                // Fallback for sqlite/mysql in tests/dev: simple string column
                Schema::table('person_phones', function (Blueprint $table): void {
                    $table->string('phone_type', 20)->nullable()->after('validated');
                });
            }
        }
    }

    public function down(): void
    {
        $driver = DB::getDriverName();
        // Drop column phone_type if exists
        $hasPhoneType = $driver === 'pgsql'
            ? (bool) DB::selectOne("SELECT 1 as exists FROM information_schema.columns WHERE table_name = 'person_phones' AND column_name = 'phone_type'")
            : Schema::hasColumn('person_phones', 'phone_type');
        if ($hasPhoneType) {
            if ($driver === 'pgsql') {
                DB::statement('ALTER TABLE person_phones DROP COLUMN phone_type');
            } else {
                Schema::table('person_phones', function (Blueprint $table): void {
                    $table->dropColumn('phone_type');
                });
            }
        }

        Schema::table('person_phones', function (Blueprint $table): void {
            if (Schema::hasColumn('person_phones', 'validated')) {
                $table->dropColumn('validated');
            }
        });

        if ($driver === 'pgsql') {
            // Drop type if no longer used (best-effort)
            DB::statement(<<<'SQL'
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_type WHERE typname = 'phone_type_enum') THEN
        -- ensure no remaining dependencies
        EXECUTE 'DROP TYPE phone_type_enum';
    END IF;
END$$;
SQL);
        }
    }
};
