<?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::create('mariachi_groups', function (Blueprint $table) {
            $table->id();
            $table->foreignId('organization_id')->constrained()->cascadeOnDelete();
            $table->string('code', 80);
            $table->string('name', 120);
            $table->string('description', 255)->nullable();
            $table->boolean('is_active')->default(true);
            $table->boolean('is_default')->default(false);
            $table->unsignedSmallInteger('sort_order')->default(100);
            $table->timestamps();

            $table->unique(['organization_id', 'code']);
            $table->index(['organization_id', 'is_active']);
            $table->index(['organization_id', 'sort_order']);
        });

        $organizations = DB::table('organizations')->select('id')->get();
        foreach ($organizations as $organization) {
            $organizationId = (int) $organization->id;
            $baseCode = 'grupo_1';
            $code = $baseCode;
            $suffix = 1;

            while (
                DB::table('mariachi_groups')
                    ->where('organization_id', $organizationId)
                    ->where('code', $code)
                    ->exists()
            ) {
                $suffix++;
                $code = $baseCode . '_' . $suffix;
            }

            DB::table('mariachi_groups')->insert([
                'organization_id' => $organizationId,
                'code' => Str::slug($code, '_'),
                'name' => 'Mariachi 1',
                'description' => 'Grupo principal',
                'is_active' => true,
                'is_default' => true,
                'sort_order' => 1,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('mariachi_groups');
    }
};

