<?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
    {
        // 1) 기존 제약/인덱스 해제 (있으면 제거)
        $dropIndexes = [
            'uq_component_row',
            'product_components_tenant_id_child_product_id_index',
            'product_components_tenant_id_parent_product_id_index',
        ];
        foreach ($dropIndexes as $idx) {
            try {
                DB::statement("ALTER TABLE `product_components` DROP INDEX `$idx`");
            } catch (\Throwable $e) {
            }
        }

        $dropFks = [
            'product_components_child_product_id_foreign',
            'product_components_material_id_foreign',
            'product_components_parent_product_id_foreign',
        ];
        foreach ($dropFks as $fk) {
            try {
                DB::statement("ALTER TABLE `product_components` DROP FOREIGN KEY `$fk`");
            } catch (\Throwable $e) {
            }
        }

        // 2) 데이터 손실 허용 → TRUNCATE 로 비우고 진행
        try {
            DB::statement('TRUNCATE TABLE `product_components`');
        } catch (\Throwable $e) {
        }

        // 3) 컬럼 추가/수정
        Schema::table('product_components', function (Blueprint $table) {
            // 프론트 카테고리 메타(선택 저장)
            if (! Schema::hasColumn('product_components', 'category_id')) {
                $table->unsignedBigInteger('category_id')->nullable()->after('parent_product_id')
                    ->comment('프론트 카테고리 ID(선택)');
            }
            if (! Schema::hasColumn('product_components', 'category_name')) {
                $table->string('category_name', 100)->nullable()->after('category_id')
                    ->comment('프론트 카테고리명(선택)');
            }

            // 통합 참조키 ref_id 추가
            if (! Schema::hasColumn('product_components', 'ref_id')) {
                $table->unsignedBigInteger('ref_id')->nullable()->after('ref_type')
                    ->comment('참조 ID (materials.id 또는 products.id)');
            }
        });

        // ref_type: ENUM → VARCHAR(20)
        DB::statement("
            ALTER TABLE `product_components`
            MODIFY COLUMN `ref_type` VARCHAR(20) NOT NULL
            COMMENT '참조 타입: MATERIAL | PRODUCT'
        ");

        // quantity 정밀도 확장
        DB::statement("
            ALTER TABLE `product_components`
            MODIFY COLUMN `quantity` DECIMAL(18,6) NOT NULL DEFAULT 0
            COMMENT '수량(소수 허용, 0 이상)'
        ");

        // ref_id NOT NULL 전환 (TRUNCATE 했으므로 바로 가능)
        DB::statement("
            ALTER TABLE `product_components`
            MODIFY COLUMN `ref_id` BIGINT UNSIGNED NOT NULL
            COMMENT '참조 ID (materials.id 또는 products.id)'
        ");

        // 불필요 컬럼 제거
        Schema::table('product_components', function (Blueprint $table) {
            if (Schema::hasColumn('product_components', 'child_product_id')) {
                $table->dropColumn('child_product_id');
            }
            if (Schema::hasColumn('product_components', 'material_id')) {
                $table->dropColumn('material_id');
            }
            if (Schema::hasColumn('product_components', 'is_default')) {
                $table->dropColumn('is_default');
            }
        });

        // 4) 인덱스 재구성 (FK 최소화 정책, 조회 성능 중심)
        Schema::table('product_components', function (Blueprint $table) {
            $table->index(['tenant_id', 'parent_product_id'], 'idx_tenant_parent');
            $table->index(['tenant_id', 'ref_type', 'ref_id'], 'idx_tenant_ref');
            $table->index(['tenant_id', 'category_id'], 'idx_tenant_category');
            $table->index(['tenant_id', 'sort_order'], 'idx_tenant_sort');
        });
    }

    public function down(): void
    {
        // 인덱스 제거
        foreach (['idx_tenant_parent', 'idx_tenant_ref', 'idx_tenant_category', 'idx_tenant_sort'] as $idx) {
            try {
                DB::statement("ALTER TABLE `product_components` DROP INDEX `$idx`");
            } catch (\Throwable $e) {
            }
        }

        // 데이터 손실 허용: TRUNCATE 후 원형에 가깝게 복원
        try {
            DB::statement('TRUNCATE TABLE `product_components`');
        } catch (\Throwable $e) {
        }

        // 컬럼 복원
        Schema::table('product_components', function (Blueprint $table) {
            // child_product_id, material_id, is_default 복원
            if (! Schema::hasColumn('product_components', 'child_product_id')) {
                $table->unsignedBigInteger('child_product_id')->nullable()->after('ref_type')->comment('하위 제품/부품 ID');
            }
            if (! Schema::hasColumn('product_components', 'material_id')) {
                $table->unsignedBigInteger('material_id')->nullable()->after('child_product_id')->comment('자재 ID');
            }
            if (! Schema::hasColumn('product_components', 'is_default')) {
                $table->tinyInteger('is_default')->default(0)->after('sort_order')->comment('기본 BOM 여부(1/0)');
            }
        });

        // ref_type: VARCHAR → ENUM
        DB::statement("
            ALTER TABLE `product_components`
            MODIFY COLUMN `ref_type` ENUM('PRODUCT','MATERIAL') NOT NULL DEFAULT 'PRODUCT'
            COMMENT '참조 대상 타입(PRODUCT=제품, MATERIAL=자재)'
        ");

        // quantity 정밀도 원복
        DB::statement('
            ALTER TABLE `product_components`
            MODIFY COLUMN `quantity` DECIMAL(18,4) NOT NULL DEFAULT 1.0000
        ');

        // ref_id 제거
        if (Schema::hasColumn('product_components', 'ref_id')) {
            Schema::table('product_components', function (Blueprint $table) {
                $table->dropColumn('ref_id');
            });
        }

        // category 메타 제거
        Schema::table('product_components', function (Blueprint $table) {
            if (Schema::hasColumn('product_components', 'category_name')) {
                $table->dropColumn('category_name');
            }
            if (Schema::hasColumn('product_components', 'category_id')) {
                $table->dropColumn('category_id');
            }
        });

        // 원래 인덱스/제약 복원 (FK 최소화 정책이지만 down에서는 원형 회귀)
        try {
            DB::statement('
                ALTER TABLE `product_components`
                ADD CONSTRAINT `uq_component_row`
                UNIQUE (`tenant_id`,`parent_product_id`,`ref_type`,`child_product_id`,`material_id`,`sort_order`)
            ');
        } catch (\Throwable $e) {
        }

        try {
            DB::statement('
                ALTER TABLE `product_components`
                ADD CONSTRAINT `product_components_child_product_id_foreign`
                FOREIGN KEY (`child_product_id`) REFERENCES `products`(`id`)
            ');
        } catch (\Throwable $e) {
        }

        try {
            DB::statement('
                ALTER TABLE `product_components`
                ADD CONSTRAINT `product_components_material_id_foreign`
                FOREIGN KEY (`material_id`) REFERENCES `materials`(`id`) ON DELETE SET NULL
            ');
        } catch (\Throwable $e) {
        }

        try {
            DB::statement('
                ALTER TABLE `product_components`
                ADD CONSTRAINT `product_components_parent_product_id_foreign`
                FOREIGN KEY (`parent_product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
            ');
        } catch (\Throwable $e) {
        }

        try {
            DB::statement('
                CREATE INDEX `product_components_tenant_id_child_product_id_index`
                ON `product_components`(`tenant_id`,`child_product_id`)
            ');
        } catch (\Throwable $e) {
        }

        try {
            DB::statement('
                CREATE INDEX `product_components_tenant_id_parent_product_id_index`
                ON `product_components`(`tenant_id`,`parent_product_id`)
            ');
        } catch (\Throwable $e) {
        }
    }
};
