<?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
    {
        // First, migrate existing categories to the new table
        $existingCategories = DB::table('materials')
            ->whereNotNull('category')
            ->where('category', '!=', '')
            ->distinct()
            ->pluck('category');

        foreach ($existingCategories as $categoryName) {
            DB::table('material_categories')->insert([
                'name' => $categoryName,
                'is_active' => true,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        // Add category_id column to materials
        Schema::table('materials', function (Blueprint $table) {
            $table->foreignId('category_id')->nullable()->after('name')->constrained('material_categories')->onDelete('set null');
        });

        // Update materials with the new category_id
        $categories = DB::table('material_categories')->pluck('id', 'name');
        foreach ($categories as $name => $id) {
            DB::table('materials')
                ->where('category', $name)
                ->update(['category_id' => $id]);
        }

        // Drop the old category column
        Schema::table('materials', function (Blueprint $table) {
            $table->dropColumn('category');
        });
    }

    public function down(): void
    {
        // Re-add category column
        Schema::table('materials', function (Blueprint $table) {
            $table->string('category')->nullable()->after('name');
        });

        // Migrate back from category_id to category text
        $categories = DB::table('material_categories')->pluck('name', 'id');
        foreach ($categories as $id => $name) {
            DB::table('materials')
                ->where('category_id', $id)
                ->update(['category' => $name]);
        }

        // Drop category_id
        Schema::table('materials', function (Blueprint $table) {
            $table->dropForeign(['category_id']);
            $table->dropColumn('category_id');
        });
    }
};
