<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

/**
 * Phase 1.3: 데이터 이관
 *
 * products (FG, PT) + materials (SM, RM, CS) → items + item_details
 *
 * ID 매핑 전략:
 * - item_id_mappings 테이블로 기존 ID → 새 ID 매핑 유지
 * - 참조 테이블 업데이트 시 사용 (Phase 5)
 */
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // ID 매핑 테이블 생성 (없으면 생성, 있으면 초기화)
        if (! Schema::hasTable('item_id_mappings')) {
            Schema::create('item_id_mappings', function (Blueprint $table) {
                $table->id();
                $table->string('source_table', 20)->comment('products or materials');
                $table->unsignedBigInteger('source_id')->comment('원본 테이블 ID');
                $table->unsignedBigInteger('item_id')->comment('items 테이블 ID');
                $table->timestamps();

                $table->unique(['source_table', 'source_id']);
                $table->index('item_id');
            });
        } else {
            DB::table('item_id_mappings')->truncate();
        }

        $productCount = 0;
        $materialCount = 0;

        // 1. Products → Items + ItemDetails
        $products = DB::table('products')
            ->whereIn('product_type', ['FG', 'PT'])
            ->get();

        foreach ($products as $product) {
            // items 테이블에 삽입
            $itemId = DB::table('items')->insertGetId([
                'tenant_id' => $product->tenant_id,
                'item_type' => $product->product_type,
                'code' => $product->code,
                'name' => $product->name,
                'unit' => $product->unit,
                'category_id' => $product->category_id,
                'bom' => $product->bom,
                'attributes' => $product->attributes,
                'attributes_archive' => $product->attributes_archive ?? null,
                'options' => $product->options,
                'description' => $product->description,
                'is_active' => $product->is_active,
                'created_by' => $product->created_by,
                'updated_by' => $product->updated_by,
                'deleted_by' => $product->deleted_by,
                'created_at' => $product->created_at,
                'updated_at' => $product->updated_at,
                'deleted_at' => $product->deleted_at,
            ]);

            // ID 매핑 저장
            DB::table('item_id_mappings')->insert([
                'source_table' => 'products',
                'source_id' => $product->id,
                'item_id' => $itemId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            // item_details 테이블에 삽입
            DB::table('item_details')->insert([
                'item_id' => $itemId,
                'is_sellable' => $product->is_sellable ?? true,
                'is_purchasable' => $product->is_purchasable ?? false,
                'is_producible' => $product->is_producible ?? false,
                'safety_stock' => $product->safety_stock,
                'lead_time' => $product->lead_time,
                'is_variable_size' => $product->is_variable_size ?? false,
                'product_category' => $product->product_category,
                'part_type' => $product->part_type,
                'bending_diagram' => $product->bending_diagram,
                'bending_details' => $product->bending_details,
                'specification_file' => $product->specification_file,
                'specification_file_name' => $product->specification_file_name,
                'certification_file' => $product->certification_file,
                'certification_file_name' => $product->certification_file_name,
                'certification_number' => $product->certification_number,
                'certification_start_date' => $product->certification_start_date,
                'certification_end_date' => $product->certification_end_date,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            $productCount++;
        }

        // 2. Materials → Items + ItemDetails
        $materials = DB::table('materials')
            ->whereIn('material_type', ['SM', 'RM', 'CS'])
            ->get();

        foreach ($materials as $material) {
            // NULL 코드 처리: 자동 생성
            $code = $material->material_code;
            if (empty($code)) {
                $code = sprintf('%s-%06d', $material->material_type, $material->id);
            }

            // items 테이블에 삽입
            $itemId = DB::table('items')->insertGetId([
                'tenant_id' => $material->tenant_id,
                'item_type' => $material->material_type,
                'code' => $code,
                'name' => $material->name,
                'unit' => $material->unit,
                'category_id' => $material->category_id,
                'bom' => null,
                'attributes' => $material->attributes,
                'attributes_archive' => null,
                'options' => $material->options,
                'description' => null,
                'is_active' => $material->is_active,
                'created_by' => $material->created_by,
                'updated_by' => $material->updated_by,
                'deleted_by' => $material->deleted_by,
                'created_at' => $material->created_at,
                'updated_at' => $material->updated_at,
                'deleted_at' => $material->deleted_at,
            ]);

            // ID 매핑 저장
            DB::table('item_id_mappings')->insert([
                'source_table' => 'materials',
                'source_id' => $material->id,
                'item_id' => $itemId,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            // item_details 테이블에 삽입
            DB::table('item_details')->insert([
                'item_id' => $itemId,
                'is_inspection' => $material->is_inspection ?? 'N',
                'item_name' => $material->item_name,
                'specification' => $material->specification,
                'search_tag' => $material->search_tag,
                'remarks' => $material->remarks,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            $materialCount++;
        }

        Log::info('Phase 1.3: Data migration complete', [
            'products_migrated' => $productCount,
            'materials_migrated' => $materialCount,
            'total' => $productCount + $materialCount,
        ]);
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // items와 item_details 데이터 삭제
        DB::table('item_details')->truncate();
        DB::table('items')->truncate();

        // ID 매핑 테이블 삭제
        Schema::dropIfExists('item_id_mappings');

        Log::info('Phase 1.3: Data migration rolled back');
    }
};
