<?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', 'service_country')) {
                $table->string('service_country', 80)->nullable()->after('address_additional');
            }
            if (!Schema::hasColumn('serenatas', 'service_department')) {
                $table->string('service_department', 120)->nullable()->after('service_country');
            }
            if (!Schema::hasColumn('serenatas', 'service_locality')) {
                $table->string('service_locality', 120)->nullable()->after('locality');
            }
            if (!Schema::hasColumn('serenatas', 'service_sector')) {
                $table->string('service_sector', 120)->nullable()->after('service_locality');
            }
        });

        DB::table('serenatas')
            ->select(['id', 'locality', 'service_country', 'service_department'])
            ->orderBy('id')
            ->chunkById(200, function ($serenatas) {
                foreach ($serenatas as $serenata) {
                    $city = trim((string) ($serenata->locality ?? ''));
                    $normalizedCity = preg_replace('/\s+/', ' ', $city) ?: $city;
                    $cityLookup = mb_strtolower(
                        str_replace(
                            ['á', 'é', 'í', 'ó', 'ú'],
                            ['a', 'e', 'i', 'o', 'u'],
                            trim((string) $normalizedCity)
                        ),
                        'UTF-8'
                    );

                    $updates = [];

                    if (in_array($cityLookup, ['bogota', 'bogota d.c.', 'bogota d c', 'bogota dc'], true)) {
                        $updates['locality'] = 'Bogotá';
                        if (empty(trim((string) ($serenata->service_department ?? '')))) {
                            $updates['service_department'] = 'Bogotá D.C.';
                        }
                    }

                    if (empty(trim((string) ($serenata->service_country ?? '')))) {
                        $updates['service_country'] = 'Colombia';
                    }

                    if (!empty($updates)) {
                        DB::table('serenatas')
                            ->where('id', $serenata->id)
                            ->update($updates);
                    }
                }
            });
    }

    public function down(): void
    {
        Schema::table('serenatas', function (Blueprint $table) {
            if (Schema::hasColumn('serenatas', 'service_sector')) {
                $table->dropColumn('service_sector');
            }
            if (Schema::hasColumn('serenatas', 'service_locality')) {
                $table->dropColumn('service_locality');
            }
            if (Schema::hasColumn('serenatas', 'service_department')) {
                $table->dropColumn('service_department');
            }
            if (Schema::hasColumn('serenatas', 'service_country')) {
                $table->dropColumn('service_country');
            }
        });
    }
};
