<?php

use Daylight\Core\Models\Category;
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('category_translations', function (Blueprint $table) {
            $table->string('nested_slug')->nullable()->after('slug');
            $table->index('nested_slug');
        });

        // Populate nested_slug for existing records
        $this->populateNestedSlugs();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('category_translations', function (Blueprint $table) {
            $table->dropIndex(['nested_slug']);
            $table->dropColumn('nested_slug');
        });
    }

    /**
     * Populate nested_slug values for all existing category translations.
     */
    private function populateNestedSlugs(): void
    {
        $categories = Category::with(['ancestorsAndSelf', 'translations'])->get();

        foreach ($categories as $category) {
            foreach ($category->translations as $translation) {
                $nestedSlug = $category->ancestorsAndSelf
                    ->reverse()
                    ->map(function ($ancestor) use ($translation) {
                        $ancestorTranslation = $ancestor->translations
                            ->where('locale', $translation->locale)
                            ->first();

                        return $ancestorTranslation?->slug;
                    })
                    ->filter()
                    ->join('/');

                $translation->nested_slug = $nestedSlug;
                $translation->save();
            }
        }
    }
};
