<?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
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Convert states table
        $this->convertTableNameField('states');

        // Convert cities table
        $this->convertTableNameField('cities');

        // Convert centers table
        $this->convertTableNameField('centers');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Restore states table
        $this->restoreTableNameField('states');

        // Restore cities table
        $this->restoreTableNameField('cities');

        // Restore centers table
        $this->restoreTableNameField('centers');
    }

    /**
     * Convert JSON name field to text with Arabic value only
     */
    protected function convertTableNameField(string $tableName): void
    {
        // Add a temporary column
        Schema::table($tableName, function (Blueprint $table) {
            $table->string('name_temp')->nullable();
        });

        // Extract Arabic values and store in temp column
        DB::table($tableName)->orderBy('id')->chunk(100, function ($records) use ($tableName) {
            foreach ($records as $record) {
                $nameJson = json_decode($record->name, true);
                $arabicName = $nameJson['ar'] ?? $nameJson['en'] ?? '';

                DB::table($tableName)
                    ->where('id', $record->id)
                    ->update(['name_temp' => $arabicName]);
            }
        });

        // Drop the old JSON column
        Schema::table($tableName, function (Blueprint $table) {
            $table->dropColumn('name');
        });

        // Rename temp column to name
        Schema::table($tableName, function (Blueprint $table) {
            $table->renameColumn('name_temp', 'name');
        });

        // Make the name column not nullable
        Schema::table($tableName, function (Blueprint $table) {
            $table->string('name')->nullable(false)->change();
        });
    }

    /**
     * Restore text name field to JSON translatable format
     */
    protected function restoreTableNameField(string $tableName): void
    {
        // Add a temporary JSON column
        Schema::table($tableName, function (Blueprint $table) {
            $table->json('name_temp')->nullable();
        });

        // Convert text to JSON format
        DB::table($tableName)->orderBy('id')->chunk(100, function ($records) use ($tableName) {
            foreach ($records as $record) {
                $jsonName = json_encode([
                    'ar' => $record->name,
                    'en' => $record->name,
                ], JSON_UNESCAPED_UNICODE);

                DB::table($tableName)
                    ->where('id', $record->id)
                    ->update(['name_temp' => DB::raw("'{$jsonName}'::json")]);
            }
        });

        // Drop the old text column
        Schema::table($tableName, function (Blueprint $table) {
            $table->dropColumn('name');
        });

        // Rename temp column to name
        Schema::table($tableName, function (Blueprint $table) {
            $table->renameColumn('name_temp', 'name');
        });

        // Make the name column not nullable
        Schema::table($tableName, function (Blueprint $table) {
            $table->json('name')->nullable(false)->change();
        });
    }
};
