<?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) child_product_id 를 NULL 허용으로 변경 (MySQL 원시 SQL 사용)
        //    (doctrine/dbal 없이 컬럼 수정)
        DB::statement("
            ALTER TABLE product_components
            MODIFY child_product_id BIGINT UNSIGNED NULL COMMENT '하위 제품/부품 ID';
        ");

        Schema::table('product_components', function (Blueprint $table) {
            // 2) ref_type, material_id 추가
            $table->enum('ref_type', ['PRODUCT', 'MATERIAL'])
                ->default('PRODUCT')
                ->after('parent_product_id')
                ->comment('참조 대상 타입(PRODUCT=제품, MATERIAL=자재)');

            $table->unsignedBigInteger('material_id')
                ->nullable()
                ->after('child_product_id')
                ->comment('자재 ID');

            $table->foreign('material_id')
                ->references('id')
                ->on('materials')
                ->nullOnDelete();
        });

        // 3) 기존 유니크 키 재정의:
        //    (tenant_id, parent_product_id, ref_type, child_product_id, material_id, sort_order)
        //    - 제품/자재 타입과 각각의 ID를 모두 포함하도록 변경
        DB::statement('ALTER TABLE product_components DROP INDEX uq_component_row');
        DB::statement('
            ALTER TABLE product_components
            ADD UNIQUE INDEX uq_component_row
            (tenant_id, parent_product_id, ref_type, child_product_id, material_id, sort_order)
        ');

        // 4) (선택) CHECK 제약: MySQL 8.0.16+ 에서만 유효
        //    ref_type=PRODUCT -> child_product_id NOT NULL AND material_id NULL
        //    ref_type=MATERIAL -> material_id NOT NULL AND child_product_id NULL
        try {
            $version = DB::selectOne('SELECT VERSION() AS v')->v ?? '';
            // 매우 단순한 버전체크 (8.0 이상일 때 시도)
            if (preg_match('/^8\./', $version)) {
                DB::statement("
                    ALTER TABLE product_components
                    ADD CONSTRAINT chk_ref_type_consistency
                    CHECK (
                        (ref_type = 'PRODUCT' AND child_product_id IS NOT NULL AND material_id IS NULL) OR
                        (ref_type = 'MATERIAL' AND material_id IS NOT NULL AND child_product_id IS NULL)
                    )
                ");
            }
        } catch (\Throwable $e) {
            // CHECK 미지원(DB버전/엔진)일 경우 무시하고 넘어갑니다.
        }
    }

    public function down(): void
    {
        // CHECK 제약 삭제 (가능한 경우만)
        try {
            DB::statement('
                ALTER TABLE product_components
                DROP CHECK chk_ref_type_consistency
            ');
        } catch (\Throwable $e) {
            // 무시
        }

        // 유니크 키를 원래대로 복구
        try {
            DB::statement('ALTER TABLE product_components DROP INDEX uq_component_row');
        } catch (\Throwable $e) {
            // 무시
        }
        DB::statement('
            ALTER TABLE product_components
            ADD UNIQUE INDEX uq_component_row
            (tenant_id, parent_product_id, child_product_id, sort_order)
        ');

        Schema::table('product_components', function (Blueprint $table) {
            // FK 우선 제거
            $table->dropForeign(['material_id']);
            // 컬럼 삭제
            $table->dropColumn(['ref_type', 'material_id']);
        });

        // child_product_id 를 NOT NULL 로 되돌림
        DB::statement("
            ALTER TABLE product_components
            MODIFY child_product_id BIGINT UNSIGNED NOT NULL COMMENT '하위 제품/부품 ID';
        ");
    }
};
