<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('subcategorias', function (Blueprint $table) {
            if (! Schema::hasColumn('subcategorias', 'slug')) {
                $table->string('slug')->nullable()->after('nome');
            }
        });

        $subcategorias = DB::table('subcategorias')
            ->select('id', 'departamento_id', 'nome')
            ->orderBy('departamento_id')
            ->orderBy('id')
            ->get();

        foreach ($subcategorias as $subcategoria) {
            $base = Str::slug($subcategoria->nome) ?: "subcategoria-{$subcategoria->id}";
            $slug = $base;
            $contador = 2;

            while (DB::table('subcategorias')
                ->where('departamento_id', $subcategoria->departamento_id)
                ->where('slug', $slug)
                ->where('id', '!=', $subcategoria->id)
                ->exists()) {
                $slug = "{$base}-{$contador}";
                $contador++;
            }

            DB::table('subcategorias')
                ->where('id', $subcategoria->id)
                ->update(['slug' => $slug]);
        }

        Schema::table('subcategorias', function (Blueprint $table) {
            $table->unique(['departamento_id', 'slug']);
        });
    }

    public function down(): void
    {
        Schema::table('subcategorias', function (Blueprint $table) {
            $table->dropUnique(['departamento_id', 'slug']);
            $table->dropColumn('slug');
        });
    }
};
