<?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
    {
        Schema::table('purchases', function (Blueprint $table) {
            $table->foreignId('supplier_id')
                ->nullable()
                ->after('bill_number')
                ->constrained('purchase_suppliers')
                ->nullOnDelete();
        });

        if (Schema::hasColumn('purchases', 'supplier_name')) {
            $purchases = DB::table('purchases')
                ->whereNotNull('supplier_name')
                ->where('supplier_name', '!=', '')
                ->get();

            foreach ($purchases as $purchase) {
                $supplierId = DB::table('purchase_suppliers')
                    ->where('name', $purchase->supplier_name)
                    ->value('id');

                if ($supplierId === null) {
                    $supplierId = DB::table('purchase_suppliers')->insertGetId([
                        'name' => $purchase->supplier_name,
                        'is_active' => true,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }

                DB::table('purchases')
                    ->where('id', $purchase->id)
                    ->update(['supplier_id' => $supplierId]);
            }

            Schema::table('purchases', function (Blueprint $table) {
                $table->dropColumn('supplier_name');
            });
        }
    }

    public function down(): void
    {
        Schema::table('purchases', function (Blueprint $table) {
            $table->string('supplier_name')->nullable()->after('bill_number');
        });

        $purchases = DB::table('purchases')
            ->whereNotNull('supplier_id')
            ->get();

        foreach ($purchases as $purchase) {
            $name = DB::table('purchase_suppliers')
                ->where('id', $purchase->supplier_id)
                ->value('name');

            DB::table('purchases')
                ->where('id', $purchase->id)
                ->update(['supplier_name' => $name]);
        }

        Schema::table('purchases', function (Blueprint $table) {
            $table->dropForeign(['supplier_id']);
            $table->dropColumn('supplier_id');
        });
    }
};
