<?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
{
    /**
     * products, materials 테이블 삭제
     *
     * 전제조건:
     * - items 테이블로 데이터 마이그레이션 완료
     * - item_id_mappings 테이블에 ID 매핑 보존
     * - BOM의 child_item_id가 새 items.id로 업데이트됨
     */
    public function up(): void
    {
        // 1. FK 제약조건 삭제 (products 참조)
        Schema::table('orders', function (Blueprint $table) {
            // FK 존재 여부 확인 후 삭제
            $this->dropForeignKeyIfExists($table, 'orders', 'fk_orders_product');
        });

        Schema::table('order_items', function (Blueprint $table) {
            $this->dropForeignKeyIfExists($table, 'order_items', 'fk_order_items_product');
        });

        // 2. FK 제약조건 삭제 (materials 참조)
        Schema::table('material_receipts', function (Blueprint $table) {
            $this->dropForeignKeyIfExists($table, 'material_receipts', 'fk_receipts_material_id');
        });

        Schema::table('lots', function (Blueprint $table) {
            $this->dropForeignKeyIfExists($table, 'lots', 'fk_lots_material_id');
        });

        // 3. 관련 테이블 삭제 (종속 테이블 먼저)
        Schema::dropIfExists('product_components');  // products의 BOM 테이블

        // 4. products 테이블 삭제
        Schema::dropIfExists('products');

        // 5. materials 테이블 삭제
        Schema::dropIfExists('materials');

        // 6. 삭제 완료 로그
        DB::statement("SELECT 'Dropped: products, materials, product_components tables' AS result");
    }

    /**
     * 롤백: 테이블 재생성 (데이터 복원 불가)
     *
     * 주의: 데이터는 item_id_mappings를 통해 items 테이블에서 복원해야 함
     */
    public function down(): void
    {
        // products 테이블 재생성
        if (! Schema::hasTable('products')) {
            Schema::create('products', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('tenant_id');
                $table->string('code', 100);
                $table->string('name', 255);
                $table->string('unit', 20)->nullable();
                $table->unsignedBigInteger('category_id')->nullable();
                $table->enum('type', ['PRODUCT', 'PART', 'SUBASSEMBLY'])->default('PRODUCT');
                $table->json('attributes')->nullable();
                $table->json('options')->nullable();
                $table->text('description')->nullable();
                $table->boolean('is_active')->default(true);
                $table->unsignedBigInteger('created_by')->nullable();
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->unsignedBigInteger('deleted_by')->nullable();
                $table->timestamps();
                $table->softDeletes();

                $table->index(['tenant_id', 'code']);
                $table->index(['tenant_id', 'type']);
            });
        }

        // materials 테이블 재생성
        if (! Schema::hasTable('materials')) {
            Schema::create('materials', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('tenant_id');
                $table->string('code', 100);
                $table->string('name', 255);
                $table->string('unit', 20)->nullable();
                $table->unsignedBigInteger('category_id')->nullable();
                $table->enum('type', ['SUB_MATERIAL', 'RAW_MATERIAL', 'CONSUMABLE'])->default('SUB_MATERIAL');
                $table->json('attributes')->nullable();
                $table->json('options')->nullable();
                $table->text('description')->nullable();
                $table->boolean('is_active')->default(true);
                $table->unsignedBigInteger('created_by')->nullable();
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->unsignedBigInteger('deleted_by')->nullable();
                $table->timestamps();
                $table->softDeletes();

                $table->index(['tenant_id', 'code']);
                $table->index(['tenant_id', 'type']);
            });
        }

        // product_components 테이블 재생성
        if (! Schema::hasTable('product_components')) {
            Schema::create('product_components', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('tenant_id');
                $table->unsignedBigInteger('product_id');
                $table->string('ref_type', 20);  // MATERIAL, PRODUCT
                $table->unsignedBigInteger('ref_id');
                $table->decimal('quantity', 10, 4)->default(1);
                $table->string('unit', 20)->nullable();
                $table->integer('order_no')->default(0);
                $table->text('note')->nullable();
                $table->timestamps();

                $table->index(['tenant_id', 'product_id']);
            });
        }

        // FK 재생성 (주의: 데이터가 없으면 무결성 오류 가능)
        // 실제 운영에서는 데이터 복원 후 FK를 별도로 추가해야 함
        DB::statement("SELECT 'Tables recreated. FK constraints NOT restored. Restore data from items table using item_id_mappings.' AS warning");
    }

    /**
     * FK 존재 여부 확인 후 삭제
     */
    private function dropForeignKeyIfExists(Blueprint $table, string $tableName, string $fkName): void
    {
        $fkExists = DB::select("
            SELECT COUNT(*) as cnt
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA = ?
            AND TABLE_NAME = ?
            AND CONSTRAINT_NAME = ?
            AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        ", [config('database.connections.mysql.database'), $tableName, $fkName]);

        if ($fkExists[0]->cnt > 0) {
            $table->dropForeign($fkName);
        }
    }
};
