<?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 {
    private string $childTable = 'parent_permissions';

    // Try these parent table names; put your real one first if different
    private array $parentCandidates = ['submodules', 'sub_modules'];

    private function pickExisting(array $names): ?string
    {
        foreach ($names as $n) if (Schema::hasTable($n)) return $n;
        return null;
    }

    private function hasForeignOn(string $table, string $col): bool
    {
        $sql = <<<SQL
SELECT 1
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = ?
  AND COLUMN_NAME = ?
  AND REFERENCED_TABLE_NAME IS NOT NULL
LIMIT 1
SQL;
        return !empty(DB::select($sql, [$table, $col]));
    }

    private function pkSpec(string $table): ?array
    {
        $row = DB::selectOne("
            SELECT DATA_TYPE, COLUMN_TYPE, IS_NULLABLE
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
              AND COLUMN_NAME = 'id'
            LIMIT 1
        ", [$table]);
        if (!$row) return null;

        $type = strtolower($row->DATA_TYPE);                  // int | bigint | ...
        $unsigned = str_contains(strtolower($row->COLUMN_TYPE), 'unsigned');
        $nullable = strtolower($row->IS_NULLABLE ?? 'no') === 'yes';
        return ['type'=>$type, 'unsigned'=>$unsigned, 'nullable'=>$nullable];
    }

    private function sqlType(array $spec, bool $nullable): string
    {
        $t = match ($spec['type']) {
            'int' => 'INT',
            'bigint' => 'BIGINT',
            default => strtoupper($spec['type']),
        };
        if ($spec['unsigned']) $t .= ' UNSIGNED';
        $t .= $nullable ? ' NULL' : ' NOT NULL';
        return $t;
    }

    public function up(): void
    {
        if (!Schema::hasTable($this->childTable)) return;

        // Find parent table (or skip if none)
        $parent = $this->pickExisting($this->parentCandidates);
        if (!$parent) return; // no-op: parent missing, avoid errno:150

        // If FK already present, nothing to do
        if ($this->hasForeignOn($this->childTable, 'submodule_id')) return;

        // Align child column type to match parent's PK exactly
        $pk = $this->pkSpec($parent);
        if ($pk) {
            $sql = $this->sqlType($pk, false); // submodule_id is NOT NULL in your dump
            DB::statement("ALTER TABLE `{$this->childTable}` MODIFY `submodule_id` {$sql}");
        }

        // Add FK
        Schema::table($this->childTable, function (Blueprint $table) use ($parent) {
            $table->foreign('submodule_id', 'parent_permissions_submodule_id_foreign')
                  ->references('id')->on($parent)
                  ->cascadeOnUpdate()
                  ->restrictOnDelete(); // change to ->cascadeOnDelete() if desired
        });
    }

    public function down(): void
    {
        if (!Schema::hasTable($this->childTable)) return;

        Schema::table($this->childTable, function (Blueprint $table) {
            try { $table->dropForeign('parent_permissions_submodule_id_foreign'); } catch (\Throwable $e) {}
        });
    }
};
