<?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 $table = 'purchase_receive_details';

    private function hasForeignOn(string $column): 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, [$this->table, $column]));
    }

    /** parent PK spec: ['type'=>'int'|'bigint', 'unsigned'=>bool] */
    private function pkSpec(string $table): ?array
    {
        $row = DB::selectOne("
            SELECT DATA_TYPE, COLUMN_TYPE
            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);
        $unsigned = str_contains(strtolower($row->COLUMN_TYPE), 'unsigned');
        return ['type'=>$type, 'unsigned'=>$unsigned];
    }

    private function sqlTypeFromSpec(array $spec, bool $nullable = false): 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->table)) return;

        // Map child column -> parent table (সবগুলোই NOT NULL আপনার ডাম্পে)
        $parents = [
            'company_id'          => 'companies',
            'created_by'          => 'users',
            'updated_by'          => 'users',
            'purchase_receive_id' => 'purchase_receives',
            'purchase_details_id' => 'purchase_details',
            'item_id'             => 'items',
            'supplier_id'         => 'suppliers',
        ];

        // 1) Child column type = parent PK type (INT/BIGINT + UNSIGNED) — prevents errno:150
        foreach ($parents as $col => $parentTable) {
            if (!Schema::hasTable($parentTable)) continue;            // parent missing -> skip
            if (!Schema::hasColumn($this->table, $col)) continue;     // safety

            $spec = $this->pkSpec($parentTable);
            if (!$spec) continue;

            $sqlType = $this->sqlTypeFromSpec($spec, false);          // all NOT NULL here
            DB::statement("ALTER TABLE `{$this->table}` MODIFY `{$col}` {$sqlType}");
        }

        // 2) Add only missing FKs (no-throw if parent missing)
        Schema::table($this->table, function (Blueprint $t) use ($parents) {
            foreach ($parents as $col => $parentTable) {
                if (!Schema::hasTable($parentTable)) continue;
                // already has FK?
                $exists = !empty(DB::select("
                    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
                ", [$this->table, $col]));
                if ($exists) continue;

                $fkName = "purchase_receive_details_{$col}_foreign";
                $t->foreign($col, $fkName)
                  ->references('id')->on($parentTable)
                  ->cascadeOnUpdate()
                  ->restrictOnDelete(); // আপনার ডাম্প মতো
            }
        });
    }

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

        Schema::table($this->table, function (Blueprint $t) {
            foreach ([
                'purchase_receive_details_company_id_foreign',
                'purchase_receive_details_created_by_foreign',
                'purchase_receive_details_updated_by_foreign',
                'purchase_receive_details_purchase_receive_id_foreign',
                'purchase_receive_details_purchase_details_id_foreign',
                'purchase_receive_details_item_id_foreign',
                'purchase_receive_details_supplier_id_foreign',
            ] as $fk) {
                try { $t->dropForeign($fk); } catch (\Throwable $e) {}
            }
        });
    }
};

