<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('associations', function (Blueprint $table) {
            // Add new location fields
            $table->decimal('lat', 10, 8)->nullable()->after('city');
            $table->decimal('lng', 11, 8)->nullable()->after('lat');

            // Remove old location fields
            $table->dropColumn(['regions', 'governorates', 'centers', 'villages']);
        });

        // Create pivot tables for many-to-many relationships
        Schema::create('association_state', function (Blueprint $table) {
            $table->id();
            $table->foreignId('association_id')->constrained()->onDelete('cascade');
            $table->foreignId('state_id')->constrained()->onDelete('cascade');
            $table->timestamps();

            $table->unique(['association_id', 'state_id']);
        });

        Schema::create('association_city', function (Blueprint $table) {
            $table->id();
            $table->foreignId('association_id')->constrained()->onDelete('cascade');
            $table->foreignId('city_id')->constrained()->onDelete('cascade');
            $table->timestamps();

            $table->unique(['association_id', 'city_id']);
        });

        Schema::create('association_center', function (Blueprint $table) {
            $table->id();
            $table->foreignId('association_id')->constrained()->onDelete('cascade');
            $table->foreignId('center_id')->constrained()->onDelete('cascade');
            $table->timestamps();

            $table->unique(['association_id', 'center_id']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Drop pivot tables
        Schema::dropIfExists('association_center');
        Schema::dropIfExists('association_city');
        Schema::dropIfExists('association_state');

        Schema::table('associations', function (Blueprint $table) {
            // Drop new fields
            $table->dropColumn(['lat', 'lng']);

            // Add back old fields
            $table->json('regions')->nullable();
            $table->json('governorates')->nullable();
            $table->json('centers')->nullable();
            $table->json('villages')->nullable();
        });
    }
};
