<?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 = 'rm_deliveries';

    private function hasForeignOn(string $col): bool
    {
        $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";
        return !empty(DB::select($sql, [$this->table, $col]));
    }

    private function pick(array $candidates): ?string
    {
        foreach ($candidates as $t) if (Schema::hasTable($t)) return $t;
        return null;
    }

    /** parent PK spec (type + unsigned) */
    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); // int | bigint | ...
        $unsigned = str_contains(strtolower($row->COLUMN_TYPE), 'unsigned');
        return ['type'=>$type, 'unsigned'=>$unsigned];
    }

    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->table)) return;

        // Map child col -> parent table(s)
        $map = [
            'sale_id'    => $this->pick(['rm_sales', 'sales']),
            'created_by' => $this->pick(['users']),
            'updated_by' => $this->pick(['users']),
        ];

        // 1) Align child col types to parent PKs (prevents errno:150)
        foreach ($map as $col => $parent) {
            if (!$parent || !Schema::hasColumn($this->table, $col)) continue;
            $spec = $this->pkSpec($parent);
            if (!$spec) continue;

            // NOT NULL for sale_id + created_by; NULL for updated_by
            $nullable = ($col === 'updated_by');
            $sqlType  = $this->sqlType($spec, $nullable);
            DB::statement("ALTER TABLE `{$this->table}` MODIFY `{$col}` {$sqlType}");
        }

        // 2) Add FKs if missing
        Schema::table($this->table, function (Blueprint $t) use ($map) {
            if ($map['sale_id'] && !$this->hasForeignOn('sale_id')) {
                $t->foreign('sale_id', 'rm_deliveries_sale_id_foreign')
                  ->references('id')->on($map['sale_id'])
                  ->cascadeOnUpdate()
                  ->restrictOnDelete();
            }
            if ($map['created_by'] && !$this->hasForeignOn('created_by')) {
                $t->foreign('created_by', 'rm_deliveries_created_by_foreign')
                  ->references('id')->on($map['created_by'])
                  ->cascadeOnUpdate()
                  ->restrictOnDelete();
            }
            if ($map['updated_by'] && !$this->hasForeignOn('updated_by')) {
                $t->foreign('updated_by', 'rm_deliveries_updated_by_foreign')
                  ->references('id')->on($map['updated_by'])
                  ->cascadeOnUpdate()
                  ->nullOnDelete();
            }
        });
    }

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

        Schema::table($this->table, function (Blueprint $t) {
            foreach ([
                'rm_deliveries_sale_id_foreign',
                'rm_deliveries_created_by_foreign',
                'rm_deliveries_updated_by_foreign',
            ] as $fk) {
                try { $t->dropForeign($fk); } catch (\Throwable $e) {}
            }
        });
    }
};

