<?php

use App\Models\MariachiGroup;
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('gift_items', function (Blueprint $table) {
            $table->foreignId('mariachi_group_id')->nullable()->after('organization_id')->constrained('mariachi_groups')->nullOnDelete();
        });
        Schema::table('artist_items', function (Blueprint $table) {
            $table->foreignId('mariachi_group_id')->nullable()->after('organization_id')->constrained('mariachi_groups')->nullOnDelete();
        });
        Schema::table('other_cost_items', function (Blueprint $table) {
            $table->foreignId('mariachi_group_id')->nullable()->after('organization_id')->constrained('mariachi_groups')->nullOnDelete();
        });
        Schema::table('serenata_packages', function (Blueprint $table) {
            $table->foreignId('mariachi_group_id')->nullable()->after('organization_id')->constrained('mariachi_groups')->nullOnDelete();
        });

        DB::table('organizations')->select(['id', 'name'])->orderBy('id')->get()->each(function ($organization) {
            $organizationId = (int) $organization->id;
            $group = MariachiGroup::ensureDefaultForOrganization($organizationId);

            if (!$group) {
                return;
            }

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

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

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

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

        Schema::table('gift_items', function (Blueprint $table) {
            $table->dropUnique('gift_items_organization_id_code_unique');
            $table->unique(['organization_id', 'mariachi_group_id', 'code'], 'gift_items_org_group_code_unique');
            $table->index(['organization_id', 'mariachi_group_id', 'type', 'is_active'], 'gift_items_org_group_type_active_idx');
        });

        Schema::table('artist_items', function (Blueprint $table) {
            $table->dropUnique('artist_items_organization_id_code_unique');
            $table->unique(['organization_id', 'mariachi_group_id', 'code'], 'artist_items_org_group_code_unique');
            $table->index(['organization_id', 'mariachi_group_id', 'type', 'is_active'], 'artist_items_org_group_type_active_idx');
        });

        Schema::table('other_cost_items', function (Blueprint $table) {
            $table->dropUnique('other_cost_items_organization_id_code_unique');
            $table->unique(['organization_id', 'mariachi_group_id', 'code'], 'other_cost_items_org_group_code_unique');
            $table->index(['organization_id', 'mariachi_group_id', 'is_active'], 'other_cost_items_org_group_active_idx');
        });

        Schema::table('serenata_packages', function (Blueprint $table) {
            $table->index(['organization_id', 'mariachi_group_id', 'city', 'is_active'], 'sp_org_group_city_active_idx');
            $table->index(['organization_id', 'mariachi_group_id', 'is_default'], 'sp_org_group_default_idx');
        });
    }

    public function down(): void
    {
        Schema::table('serenata_packages', function (Blueprint $table) {
            $table->dropIndex('sp_org_group_city_active_idx');
            $table->dropIndex('sp_org_group_default_idx');
            $table->dropConstrainedForeignId('mariachi_group_id');
        });

        Schema::table('other_cost_items', function (Blueprint $table) {
            $table->dropUnique('other_cost_items_org_group_code_unique');
            $table->dropIndex('other_cost_items_org_group_active_idx');
            $table->unique(['organization_id', 'code']);
            $table->dropConstrainedForeignId('mariachi_group_id');
        });

        Schema::table('artist_items', function (Blueprint $table) {
            $table->dropUnique('artist_items_org_group_code_unique');
            $table->dropIndex('artist_items_org_group_type_active_idx');
            $table->unique(['organization_id', 'code']);
            $table->dropConstrainedForeignId('mariachi_group_id');
        });

        Schema::table('gift_items', function (Blueprint $table) {
            $table->dropUnique('gift_items_org_group_code_unique');
            $table->dropIndex('gift_items_org_group_type_active_idx');
            $table->unique(['organization_id', 'code']);
            $table->dropConstrainedForeignId('mariachi_group_id');
        });
    }
};
