<?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 {
    public function up(): void
    {
        // 1) Create table & columns (without risky FKs yet)
        Schema::create('suppliers', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            $table->id(); // BIGINT UNSIGNED AI

            // FK columns (no constraints yet)
            $table->unsignedBigInteger('group_id');              // NOT NULL
            $table->unsignedBigInteger('supplier_type_id')->nullable(); // NULL (SET NULL FK later)
            $table->unsignedBigInteger('created_by');            // NOT NULL
            $table->unsignedBigInteger('updated_by');            // NOT NULL
            $table->unsignedBigInteger('country_id');            // NOT NULL

            // Data columns
            $table->string('name', 191);
            $table->string('phone', 191)->nullable();
            $table->string('email', 191)->nullable();
            $table->string('website', 191)->nullable();
            $table->string('fax', 191)->nullable();
            $table->text('address')->nullable();
            $table->text('head_office')->nullable();
            $table->text('factory_1')->nullable();
            $table->text('factory_2')->nullable();

            $table->timestamps();

            // Indexes to match your phpMyAdmin view
            $table->index('group_id', 'suppliers_group_id_index');
            $table->index('supplier_type_id', 'suppliers_supplier_type_id_index');
            $table->index('created_by', 'suppliers_created_by_index');
            $table->index('updated_by', 'suppliers_updated_by_index');
            $table->index('country_id', 'suppliers_country_id_index');
        });

        // 2) Add foreign keys safely (only when parents exist), and make sure types match

        // Helper to add a FK where child's column may need to be INT or BIGINT to match parent
        $addFk = function (
            string $childTable,
            string $childColumn,
            string $parentTable,
            string $parentColumn = 'id',
            string $fkName = null,
            string $onDelete = 'RESTRICT',
            string $onUpdate = 'CASCADE',
            bool $nullable = false
        ) {
            // Parent must exist
            if (!Schema::hasTable($parentTable)) {
                return; // Skip FK if parent not ready; you can add a later "add FKs" migration if needed.
            }

            // Detect parent's integer size (INT vs BIGINT)
            $parentCol = DB::selectOne("
                SELECT DATA_TYPE, COLUMN_TYPE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = ?
                  AND COLUMN_NAME = ?
                LIMIT 1
            ", [$parentTable, $parentColumn]);

            if (!$parentCol) {
                return;
            }

            // Align child column type with parent (only for integer types)
            // - If parent DATA_TYPE = 'int'   -> use INT UNSIGNED
            // - If parent DATA_TYPE = 'bigint'-> use BIGINT UNSIGNED
            if (in_array($parentCol->DATA_TYPE, ['int', 'bigint'], true)) {
                if ($parentCol->DATA_TYPE === 'int') {
                    DB::statement(sprintf(
                        "ALTER TABLE `%s` MODIFY `%s` INT UNSIGNED %s",
                        $childTable, $childColumn, $nullable ? "NULL" : "NOT NULL"
                    ));
                } else { // bigint
                    DB::statement(sprintf(
                        "ALTER TABLE `%s` MODIFY `%s` BIGINT UNSIGNED %s",
                        $childTable, $childColumn, $nullable ? "NULL" : "NOT NULL"
                    ));
                }
            }

            // Add the foreign key
            Schema::table($childTable, function (Blueprint $table) use ($childColumn, $parentTable, $parentColumn, $fkName, $onDelete, $onUpdate) {
                $fk = $table->foreign($childColumn, $fkName ?? "{$childTable}_{$childColumn}_foreign")
                    ->references($parentColumn)
                    ->on($parentTable);

                // on delete
                if (strtoupper($onDelete) === 'SET NULL') {
                    $fk->nullOnDelete();
                } elseif (strtoupper($onDelete) === 'CASCADE') {
                    $fk->cascadeOnDelete();
                } else {
                    $fk->restrictOnDelete();
                }

                // on update
                if (strtoupper($onUpdate) === 'CASCADE') {
                    $fk->cascadeOnUpdate();
                } else {
                    $fk->restrictOnUpdate();
                }
            });
        };

        // FKs (names match your snapshot)
        $addFk('suppliers', 'group_id',          'groups',         'id', 'suppliers_group_id_foreign',          'RESTRICT', 'CASCADE', false);
        $addFk('suppliers', 'supplier_type_id',  'supplier_types', 'id', 'suppliers_supplier_type_id_foreign',  'SET NULL', 'CASCADE', true);
        $addFk('suppliers', 'created_by',        'users',          'id', 'suppliers_created_by_foreign',        'RESTRICT', 'CASCADE', false);
        $addFk('suppliers', 'updated_by',        'users',          'id', 'suppliers_updated_by_foreign',        'RESTRICT', 'CASCADE', false);
        $addFk('suppliers', 'country_id',        'countries',      'id', 'suppliers_country_id_foreign',        'RESTRICT', 'CASCADE', false);
    }

    public function down(): void
    {
        // Drop FKs first (ignore if missing), then drop the table
        foreach ([
            'suppliers_group_id_foreign',
            'suppliers_supplier_type_id_foreign',
            'suppliers_created_by_foreign',
            'suppliers_updated_by_foreign',
            'suppliers_country_id_foreign',
        ] as $fk) {
            try {
                Schema::table('suppliers', function (Blueprint $table) use ($fk) {
                    $table->dropForeign($fk);
                });
            } catch (\Throwable $e) {
                // ignore
            }
        }

        Schema::dropIfExists('suppliers');
    }
};


