<?php

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

return new class extends Migration
{
    /**
     * roles and permissions had created_by/updated_by added via a separate migration
     * without FK constraints. Add them now with SET NULL so audit columns survive when
     * the actor user is force-deleted.
     */
    public function up(): void
    {
        $this->addFkIfMissing('roles_created_by_foreign', 'roles', 'created_by');
        $this->addFkIfMissing('roles_updated_by_foreign', 'roles', 'updated_by');
        $this->addFkIfMissing('permissions_created_by_foreign', 'permissions', 'created_by');
        $this->addFkIfMissing('permissions_updated_by_foreign', 'permissions', 'updated_by');
    }

    public function down(): void
    {
        foreach (['roles_created_by_foreign', 'roles_updated_by_foreign'] as $c) {
            DB::statement("ALTER TABLE roles DROP CONSTRAINT IF EXISTS {$c}");
        }
        foreach (['permissions_created_by_foreign', 'permissions_updated_by_foreign'] as $c) {
            DB::statement("ALTER TABLE permissions DROP CONSTRAINT IF EXISTS {$c}");
        }
    }

    private function addFkIfMissing(string $name, string $table, string $column): void
    {
        $exists = DB::selectOne(
            'SELECT 1 FROM pg_constraint WHERE conname = ?',
            [$name]
        );
        if ($exists) {
            return;
        }

        // Null out any orphan references first so the FK can be created.
        DB::statement("UPDATE {$table} SET {$column} = NULL WHERE {$column} IS NOT NULL AND {$column} NOT IN (SELECT id FROM users)");

        DB::statement(
            "ALTER TABLE {$table} ADD CONSTRAINT {$name} "
            ."FOREIGN KEY ({$column}) REFERENCES users(id) ON DELETE SET NULL"
        );
    }
};
