<?php

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

return new class extends Migration {
    private string $table = 'rm_warehouse_types';

    private function fkExists(string $constraint): bool
    {
        $sql = <<<SQL
SELECT 1
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = ?
  AND CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND CONSTRAINT_NAME = ?
LIMIT 1
SQL;
        return !empty(DB::select($sql, [$this->table, $constraint]));
    }

    public function up(): void
    {
        Schema::table($this->table, function (Blueprint $table) {
            // DO NOT add $table->index(...) — causes duplicate index errors

            // Add FK: created_by -> users(id)
            if (!$this->fkExists('rm_warehouse_types_created_by_foreign')) {
                $table->foreign('created_by', 'rm_warehouse_types_created_by_foreign')
                    ->references('id')->on('users')
                    ->cascadeOnUpdate()
                    ->nullOnDelete();
            }

            // Add FK: updated_by -> users(id)
            if (!$this->fkExists('rm_warehouse_types_updated_by_foreign')) {
                $table->foreign('updated_by', 'rm_warehouse_types_updated_by_foreign')
                    ->references('id')->on('users')
                    ->cascadeOnUpdate()
                    ->nullOnDelete();
            }
        });
    }

    public function down(): void
    {
        Schema::table($this->table, function (Blueprint $table) {
            // Drop FKs only if they exist (avoids errors on rollback)
            if ($this->fkExists('rm_warehouse_types_created_by_foreign')) {
                $table->dropForeign('rm_warehouse_types_created_by_foreign');
            }
            if ($this->fkExists('rm_warehouse_types_updated_by_foreign')) {
                $table->dropForeign('rm_warehouse_types_updated_by_foreign');
            }

            // Do NOT drop extra indexes here since we didn't add any
        });
    }
};


