<?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
{
    public function up(): void
    {
        // 1. Create the grn_statuses lookup table.
        Schema::create('grn_statuses', function (Blueprint $table) {
            $table->tinyIncrements('id');
            $table->string('code', 30)->unique();
            $table->string('name', 100);
            $table->unsignedTinyInteger('sort_order')->default(0);
        });

        DB::table('grn_statuses')->insert([
            ['id' => 1, 'code' => 'draft',     'name' => 'Draft',     'sort_order' => 1],
            ['id' => 2, 'code' => 'confirmed',  'name' => 'Confirmed', 'sort_order' => 2],
            ['id' => 3, 'code' => 'deleted',    'name' => 'Deleted',   'sort_order' => 3],
        ]);

        // 2. Add grn_status_id as nullable so we can populate before constraining.
        Schema::table('grns', function (Blueprint $table) {
            $table->unsignedTinyInteger('grn_status_id')->nullable()->after('supplier_document_id');
        });

        // 3. Migrate data from enum — soft-deleted rows become status 3 (Deleted).
        if (Schema::hasColumn('grns', 'status')) {
            DB::statement("
                UPDATE grns SET grn_status_id = CASE
                    WHEN deleted_at IS NOT NULL THEN 3
                    WHEN status = 'confirmed'   THEN 2
                    ELSE 1
                END
            ");
        } else {
            // Enum column already gone (re-run scenario).
            DB::statement('UPDATE grns SET grn_status_id = COALESCE(grn_status_id, 1)');
        }

        // 4. Lock in NOT NULL + FK now that every row has a value.
        Schema::table('grns', function (Blueprint $table) {
            $table->unsignedTinyInteger('grn_status_id')->nullable(false)->change();
            $table->index('grn_status_id', 'grns_grn_status_id_index');
            $table->foreign('grn_status_id', 'grns_grn_status_id_foreign')
                ->references('id')->on('grn_statuses');
        });

        // 5. Drop the old enum column.
        if (Schema::hasColumn('grns', 'status')) {
            Schema::table('grns', function (Blueprint $table) {
                $table->dropColumn('status');
            });
        }
    }

    public function down(): void
    {
        // 1. Restore the enum column.
        Schema::table('grns', function (Blueprint $table) {
            $table->enum('status', ['draft', 'confirmed'])->default('draft')->nullable()->after('supplier_document_id');
        });

        DB::statement("
            UPDATE grns SET status = CASE
                WHEN grn_status_id = 2 THEN 'confirmed'
                ELSE 'draft'
            END
        ");

        Schema::table('grns', function (Blueprint $table) {
            $table->enum('status', ['draft', 'confirmed'])->default('draft')->nullable(false)->change();
        });

        // 2. Drop FK, index, and the new column.
        Schema::table('grns', function (Blueprint $table) {
            $table->dropForeign('grns_grn_status_id_foreign');
            $table->dropIndex('grns_grn_status_id_index');
            $table->dropColumn('grn_status_id');
        });

        // 3. Drop the lookup table.
        Schema::dropIfExists('grn_statuses');
    }
};
