<?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::create('global_categories', function (Blueprint $table) {
            $table->id()->comment('글로벌 카테고리 PK');
            $table->unsignedBigInteger('parent_id')->nullable()->comment('상위 카테고리ID(NULL=최상위)');
            $table->string('code_group', 30)->comment('코드 그룹');
            $table->string('profile_code', 30)->nullable()->comment('역할 프로필 코드');
            $table->string('code', 30)->comment('카테고리 코드(영문, 고유)');
            $table->string('name', 100)->comment('카테고리명');
            $table->boolean('is_active')->default(true)->comment('활성여부');
            $table->integer('sort_order')->default(1)->comment('정렬순서');
            $table->string('description', 255)->nullable()->comment('비고');
            $table->unsignedBigInteger('created_by')->nullable()->comment('등록자');
            $table->unsignedBigInteger('updated_by')->nullable()->comment('수정자');
            $table->unsignedBigInteger('deleted_by')->nullable()->comment('삭제자');
            $table->timestamps();
            $table->softDeletes();

            // 인덱스
            $table->unique(['code_group', 'code'], 'uq_global_codegroup_code');
            $table->index('parent_id', 'idx_global_parent_id');
            $table->index('code_group', 'idx_global_code_group');

            // 자기 참조 FK
            $table->foreign('parent_id', 'fk_global_category_parent')
                ->references('id')
                ->on('global_categories')
                ->onDelete('set null');
        });

        // tenant 1의 카테고리를 글로벌 카테고리로 복사
        $this->seedFromTenant1();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('global_categories');
    }

    /**
     * tenant 1의 카테고리를 글로벌로 복사
     */
    private function seedFromTenant1(): void
    {
        $categories = \DB::table('categories')
            ->where('tenant_id', 1)
            ->whereNull('deleted_at')
            ->orderBy('parent_id')
            ->orderBy('sort_order')
            ->get();

        if ($categories->isEmpty()) {
            return;
        }

        $idMap = []; // old_id => new_id

        // 1단계: parent_id가 NULL인 루트 카테고리
        foreach ($categories->where('parent_id', null) as $cat) {
            $newId = \DB::table('global_categories')->insertGetId([
                'parent_id' => null,
                'code_group' => $cat->code_group,
                'profile_code' => $cat->profile_code,
                'code' => $cat->code,
                'name' => $cat->name,
                'is_active' => $cat->is_active,
                'sort_order' => $cat->sort_order,
                'description' => $cat->description,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
            $idMap[$cat->id] = $newId;
        }

        // 2단계: 자식 카테고리 (재귀적으로 처리)
        $remaining = $categories->whereNotNull('parent_id');
        $maxIterations = 10;
        $iteration = 0;

        while ($remaining->isNotEmpty() && $iteration < $maxIterations) {
            $processed = [];
            foreach ($remaining as $cat) {
                if (isset($idMap[$cat->parent_id])) {
                    $newId = \DB::table('global_categories')->insertGetId([
                        'parent_id' => $idMap[$cat->parent_id],
                        'code_group' => $cat->code_group,
                        'profile_code' => $cat->profile_code,
                        'code' => $cat->code,
                        'name' => $cat->name,
                        'is_active' => $cat->is_active,
                        'sort_order' => $cat->sort_order,
                        'description' => $cat->description,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                    $idMap[$cat->id] = $newId;
                    $processed[] = $cat->id;
                }
            }
            $remaining = $remaining->whereNotIn('id', $processed);
            $iteration++;
        }
    }
};
