<?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('serenatas', function (Blueprint $table) {
            if (!Schema::hasColumn('serenatas', 'mariachi_group_id')) {
                $table->foreignId('mariachi_group_id')
                    ->nullable()
                    ->after('customer_id')
                    ->constrained('mariachi_groups')
                    ->nullOnDelete();
            }
            if (!Schema::hasColumn('serenatas', 'payer_email')) {
                $table->string('payer_email', 255)->nullable()->after('payer_name');
            }
        });

        Schema::table('customers', function (Blueprint $table) {
            if (!Schema::hasColumn('customers', 'mariachi_group_id')) {
                $table->foreignId('mariachi_group_id')
                    ->nullable()
                    ->after('organization_id')
                    ->constrained('mariachi_groups')
                    ->nullOnDelete();
            }
        });

        $defaultGroups = DB::table('mariachi_groups')
            ->select(['id', 'organization_id'])
            ->where('is_default', 1)
            ->get()
            ->keyBy('organization_id');

        $organizations = DB::table('organizations')->select('id')->get();
        foreach ($organizations as $organization) {
            $organizationId = (int) $organization->id;
            $defaultGroup = $defaultGroups->get($organizationId);
            if (!$defaultGroup) {
                $groupId = DB::table('mariachi_groups')->insertGetId([
                    'organization_id' => $organizationId,
                    'code' => 'grupo_1',
                    'name' => 'Mariachi 1',
                    'description' => 'Grupo principal',
                    'is_active' => true,
                    'is_default' => true,
                    'sort_order' => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            } else {
                $groupId = (int) $defaultGroup->id;
            }

            DB::table('serenatas')
                ->where('organization_id', $organizationId)
                ->whereNull('mariachi_group_id')
                ->update(['mariachi_group_id' => $groupId]);

            DB::table('customers')
                ->where('organization_id', $organizationId)
                ->whereNull('mariachi_group_id')
                ->update(['mariachi_group_id' => $groupId]);
        }
    }

    public function down(): void
    {
        Schema::table('serenatas', function (Blueprint $table) {
            if (Schema::hasColumn('serenatas', 'mariachi_group_id')) {
                $table->dropConstrainedForeignId('mariachi_group_id');
            }
            if (Schema::hasColumn('serenatas', 'payer_email')) {
                $table->dropColumn('payer_email');
            }
        });

        Schema::table('customers', function (Blueprint $table) {
            if (Schema::hasColumn('customers', 'mariachi_group_id')) {
                $table->dropConstrainedForeignId('mariachi_group_id');
            }
        });
    }
};

