<?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
{
    /** 유니크 인덱스가 정확히 $columns 조합이면 드롭 */
    private function dropUniqueIfColumns(string $table, array $columns): void
    {
        $indexes = collect(DB::select("SHOW INDEX FROM `{$table}`"))->groupBy('Key_name');
        foreach ($indexes as $name => $rows) {
            $nonUnique = (int) ($rows->first()->Non_unique ?? 1);
            if ($nonUnique !== 0) {
                continue;
            }
            $cols = collect($rows)->sortBy('Seq_in_index')->pluck('Column_name')->values()->all();
            if ($cols === $columns) {
                DB::statement("ALTER TABLE `{$table}` DROP INDEX `{$name}`");
            }
        }
    }

    /** products.category_id 의 기존 FK 이름을 찾아 드롭 */
    private function dropProductsCategoryFk(): void
    {
        $row = DB::selectOne("
            SELECT CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'products'
              AND COLUMN_NAME = 'category_id'
              AND REFERENCED_TABLE_NAME IS NOT NULL
            LIMIT 1
        ");
        if ($row && isset($row->CONSTRAINT_NAME)) {
            $name = $row->CONSTRAINT_NAME;
            DB::statement("ALTER TABLE `products` DROP FOREIGN KEY `{$name}`");
        }
    }

    public function up(): void
    {
        /**
         * A) categories 정리
         *   - is_active: TINYINT(1) 통일 (Y/N → 1/0 안전 변환)
         *   - 유니크키: (tenant_id, code_group, code) 표준화
         *   - profile_code 컬럼 추가(+ 인덱스)
         */
        // A-1. 값 변환(Y/N → 1/0) 시도 (이미 숫자여도 무해)
        try {
            DB::statement("UPDATE categories SET is_active = 1 WHERE is_active = 'Y'");
        } catch (\Throwable $e) {
        }
        try {
            DB::statement("UPDATE categories SET is_active = 0 WHERE is_active = 'N'");
        } catch (\Throwable $e) {
        }

        // A-2. 타입 변경
        DB::statement("ALTER TABLE categories
            MODIFY COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '활성여부(1=활성,0=비활성)'");

        // A-3. code_group 컬럼 추가 (유니크 인덱스 추가 전에 필요)
        Schema::table('categories', function (Blueprint $table) {
            if (! Schema::hasColumn('categories', 'code_group')) {
                $table->string('code_group', 30)
                    ->default('category')
                    ->after('code')
                    ->comment('코드 그룹');
            }
        });

        // A-4. 유니크 (tenant_id, code) → (tenant_id, code_group, code)
        $this->dropUniqueIfColumns('categories', ['tenant_id', 'code']);
        $idx = collect(DB::select('SHOW INDEX FROM `categories`'))->groupBy('Key_name');
        $hasTarget = false;
        foreach ($idx as $name => $rows) {
            $nonUnique = (int) ($rows->first()->Non_unique ?? 1);
            $cols = collect($rows)->sortBy('Seq_in_index')->pluck('Column_name')->values()->all();
            if ($nonUnique === 0 && $cols === ['tenant_id', 'code_group', 'code']) {
                $hasTarget = true;
                break;
            }
        }
        if (! $hasTarget) {
            DB::statement('ALTER TABLE `categories`
                ADD UNIQUE KEY `uq_tenant_codegroup_code` (`tenant_id`,`code_group`,`code`)');
        }

        // A-5. profile_code 추가 (common_codes.code, code_group='capability_profile')
        Schema::table('categories', function (Blueprint $table) {
            if (! Schema::hasColumn('categories', 'profile_code')) {
                $table->string('profile_code', 30)
                    ->nullable()
                    ->after('code_group')
                    ->comment("역할 프로필 코드 (common_codes.code, code_group='capability_profile')");
            }
        });
        // 인덱스 (tenant_id, profile_code)
        $hasIdx = collect(DB::select('SHOW INDEX FROM `categories`'))
            ->contains(fn ($r) => $r->Key_name === 'idx_categories_tenant_profilecode');
        if (! $hasIdx) {
            DB::statement('CREATE INDEX `idx_categories_tenant_profilecode` ON `categories` (`tenant_id`, `profile_code`)');
        }

        /**
         * B) products 정리
         *   - category_id FK: common_codes → categories 로 전환
         *   - 역할/라벨 컬럼 추가: product_type + is_sellable/is_purchasable/is_producible
         */
        $this->dropProductsCategoryFk();
        Schema::table('products', function (Blueprint $table) {
            $table->foreign('category_id', 'products_category_id_foreign')
                ->references('id')->on('categories')
                ->onUpdate('cascade')->onDelete('restrict');

            if (! Schema::hasColumn('products', 'product_type')) {
                $table->string('product_type', 30)
                    ->default('PRODUCT')
                    ->comment("제품유형: PRODUCT/PART/SUBASSEMBLY 등 (common_codes.code_group='product_type')")
                    ->after('category_id');
            }
            if (! Schema::hasColumn('products', 'is_sellable')) {
                $table->tinyInteger('is_sellable')->default(1)->comment('판매가능(1/0)')->after('description');
            }
            if (! Schema::hasColumn('products', 'is_purchasable')) {
                $table->tinyInteger('is_purchasable')->default(0)->comment('구매가능(1/0)')->after('is_sellable');
            }
            if (! Schema::hasColumn('products', 'is_producible')) {
                $table->tinyInteger('is_producible')->default(1)->comment('제조가능(1/0)')->after('is_purchasable');
            }
        });

        /**
         * C) BOM 자기참조 테이블 신설 (product_components)
         *    - parts/boms/bom_items 레거시 제거
         */
        if (! Schema::hasTable('product_components')) {
            Schema::create('product_components', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->unsignedBigInteger('tenant_id')->comment('테넌트 ID');
                $table->unsignedBigInteger('parent_product_id')->comment('상위 제품 ID');
                $table->unsignedBigInteger('child_product_id')->comment('하위 제품/부품 ID');
                $table->decimal('quantity', 18, 4)->default(1.0000);
                $table->integer('sort_order')->default(0);
                $table->tinyInteger('is_default')->default(0)->comment('기본 BOM 여부(1/0)');
                $table->unsignedBigInteger('created_by')->nullable();
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->softDeletes();
                $table->timestamps();

                $table->index(['tenant_id', 'parent_product_id']);
                $table->index(['tenant_id', 'child_product_id']);
                $table->unique(['tenant_id', 'parent_product_id', 'child_product_id', 'sort_order'], 'uq_component_row');

                $table->foreign('parent_product_id')->references('id')->on('products')->onDelete('cascade');
                $table->foreign('child_product_id')->references('id')->on('products')->onDelete('restrict');
            });
        }

        foreach (['bom_items', 'boms', 'parts'] as $tbl) {
            if (Schema::hasTable($tbl)) {
                Schema::drop($tbl);
            }
        }
    }

    public function down(): void
    {
        /**
         * C) product_components 제거 + 레거시 복구(최소 스키마)
         */
        if (Schema::hasTable('product_components')) {
            Schema::drop('product_components');
        }
        if (! Schema::hasTable('parts')) {
            Schema::create('parts', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->unsignedBigInteger('tenant_id');
                $table->string('code', 30);
                $table->string('name', 100);
                $table->unsignedBigInteger('category_id');
                $table->unsignedBigInteger('part_type_id')->nullable();
                $table->string('unit', 20)->nullable();
                $table->json('attributes')->nullable();
                $table->string('description', 255)->nullable();
                $table->tinyInteger('is_active')->default(1);
                $table->unsignedBigInteger('created_by');
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->timestamps();
                $table->softDeletes();
                $table->unique(['tenant_id', 'code']);
            });
        }
        if (! Schema::hasTable('boms')) {
            Schema::create('boms', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->unsignedBigInteger('tenant_id');
                $table->unsignedBigInteger('product_id');
                $table->string('code', 30);
                $table->string('name', 100);
                $table->unsignedBigInteger('category_id');
                $table->json('attributes')->nullable();
                $table->string('description', 255)->nullable();
                $table->tinyInteger('is_default')->default(0);
                $table->tinyInteger('is_active')->default(1);
                $table->unsignedBigInteger('image_file_id')->nullable();
                $table->unsignedBigInteger('created_by');
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->timestamps();
                $table->softDeletes();
                $table->unique(['tenant_id', 'product_id', 'code']);
            });
        }
        if (! Schema::hasTable('bom_items')) {
            Schema::create('bom_items', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->unsignedBigInteger('tenant_id');
                $table->unsignedBigInteger('bom_id');
                $table->unsignedBigInteger('parent_id')->nullable();
                $table->string('item_type', 20);
                $table->unsignedBigInteger('ref_id');
                $table->decimal('quantity', 18, 4)->default(1.0000);
                $table->json('attributes')->nullable();
                $table->integer('sort_order')->default(0);
                $table->unsignedBigInteger('created_by');
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->timestamps();
                $table->softDeletes();
            });
        }

        /**
         * B) products 롤백
         *   - 추가 컬럼 제거
         *   - FK: categories → common_codes
         */
        Schema::table('products', function (Blueprint $table) {
            if (Schema::hasColumn('products', 'is_sellable')) {
                $table->dropColumn('is_sellable');
            }
            if (Schema::hasColumn('products', 'is_purchasable')) {
                $table->dropColumn('is_purchasable');
            }
            if (Schema::hasColumn('products', 'is_producible')) {
                $table->dropColumn('is_producible');
            }
            if (Schema::hasColumn('products', 'product_type')) {
                $table->dropColumn('product_type');
            }
        });

        $this->dropProductsCategoryFk();
        Schema::table('products', function (Blueprint $table) {
            $table->foreign('category_id', 'products_category_id_foreign')
                ->references('id')->on('common_codes')
                ->onUpdate('cascade')->onDelete('restrict');
        });

        /**
         * A) categories 롤백
         *   - profile_code 인덱스/컬럼 제거
         *   - 유니크: (tenant_id, code_group, code) → (tenant_id, code)
         *   - is_active: CHAR('Y'/'N') 복구 + 값 재변환
         */
        $hasIdx = collect(DB::select('SHOW INDEX FROM `categories`'))
            ->contains(fn ($r) => $r->Key_name === 'idx_categories_tenant_profilecode');
        if ($hasIdx) {
            DB::statement('DROP INDEX `idx_categories_tenant_profilecode` ON `categories`');
        }
        Schema::table('categories', function (Blueprint $table) {
            if (Schema::hasColumn('categories', 'profile_code')) {
                $table->dropColumn('profile_code');
            }
        });

        $idx = collect(DB::select('SHOW INDEX FROM `categories`'))->groupBy('Key_name');
        if ($idx->has('uq_tenant_codegroup_code')) {
            DB::statement('ALTER TABLE `categories` DROP INDEX `uq_tenant_codegroup_code`');
        }
        // (tenant_id, code) 유니크 복구(없을 때만)
        $idx = collect(DB::select('SHOW INDEX FROM `categories`'))->groupBy('Key_name');
        $hasOld = false;
        foreach ($idx as $name => $rows) {
            $nonUnique = (int) ($rows->first()->Non_unique ?? 1);
            $cols = collect($rows)->sortBy('Seq_in_index')->pluck('Column_name')->values()->all();
            if ($nonUnique === 0 && $cols === ['tenant_id', 'code']) {
                $hasOld = true;
                break;
            }
        }
        if (! $hasOld) {
            DB::statement('ALTER TABLE `categories`
                ADD UNIQUE KEY `categories_tenant_id_code_unique` (`tenant_id`,`code`)');
        }

        // 타입 복구 + 값 재변환
        DB::statement("ALTER TABLE categories
            MODIFY COLUMN is_active CHAR(1) NOT NULL DEFAULT 'Y' COMMENT '활성여부(Y/N)'");
        try {
            DB::statement("UPDATE categories SET is_active = 'Y' WHERE is_active = 1");
        } catch (\Throwable $e) {
        }
        try {
            DB::statement("UPDATE categories SET is_active = 'N' WHERE is_active = 0");
        } catch (\Throwable $e) {
        }
    }
};
