<?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 5: 참조 테이블 마이그레이션
 *
 * 기존 products/materials 참조를 items 참조로 변경
 * item_id_mappings 테이블을 활용하여 ID 변환
 */
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 1. product_components: ref_type + ref_id → item_id
        $this->migrateProductComponents();

        // 2. bom_template_items: ref_type + ref_id → item_id
        $this->migrateBomTemplateItems();

        // 3. orders: product_id → item_id
        $this->migrateOrders();

        // 4. order_items: product_id → item_id
        $this->migrateOrderItems();

        // 5. material_receipts: material_id → item_id
        $this->migrateMaterialReceipts();

        // 6. lots: material_id → item_id
        $this->migrateLots();

        // 7. price_histories: item_type + item_id → item_id
        $this->migratePriceHistories();

        // 8. item_fields: source_table 업데이트
        $this->migrateItemFields();

        Log::info('Phase 5: Reference tables migration complete');
    }

    /**
     * product_components 마이그레이션
     */
    private function migrateProductComponents(): void
    {
        if (! Schema::hasTable('product_components')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('product_components', 'item_id')) {
            Schema::table('product_components', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('ref_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // parent_item_id 컬럼 추가
        if (! Schema::hasColumn('product_components', 'parent_item_id')) {
            Schema::table('product_components', function (Blueprint $table) {
                $table->unsignedBigInteger('parent_item_id')->nullable()->after('parent_product_id')
                    ->comment('상위 품목 ID (items 참조)');
            });
        }

        // ref_type=PRODUCT → products 매핑으로 item_id 업데이트
        DB::statement("
            UPDATE product_components pc
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = pc.ref_id
            SET pc.item_id = m.item_id
            WHERE pc.ref_type = 'PRODUCT'
        ");

        // ref_type=MATERIAL → materials 매핑으로 item_id 업데이트
        DB::statement("
            UPDATE product_components pc
            JOIN item_id_mappings m ON m.source_table = 'materials' AND m.source_id = pc.ref_id
            SET pc.item_id = m.item_id
            WHERE pc.ref_type = 'MATERIAL'
        ");

        // parent_product_id → parent_item_id 업데이트
        DB::statement("
            UPDATE product_components pc
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = pc.parent_product_id
            SET pc.parent_item_id = m.item_id
            WHERE pc.parent_product_id IS NOT NULL
        ");

        Log::info('Phase 5: product_components migrated');
    }

    /**
     * bom_template_items 마이그레이션
     */
    private function migrateBomTemplateItems(): void
    {
        if (! Schema::hasTable('bom_template_items')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('bom_template_items', 'item_id')) {
            Schema::table('bom_template_items', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('ref_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // ref_type=PRODUCT → products 매핑
        DB::statement("
            UPDATE bom_template_items bti
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = bti.ref_id
            SET bti.item_id = m.item_id
            WHERE bti.ref_type = 'PRODUCT'
        ");

        // ref_type=MATERIAL → materials 매핑
        DB::statement("
            UPDATE bom_template_items bti
            JOIN item_id_mappings m ON m.source_table = 'materials' AND m.source_id = bti.ref_id
            SET bti.item_id = m.item_id
            WHERE bti.ref_type = 'MATERIAL'
        ");

        Log::info('Phase 5: bom_template_items migrated');
    }

    /**
     * orders 마이그레이션
     */
    private function migrateOrders(): void
    {
        if (! Schema::hasTable('orders')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('orders', 'item_id')) {
            Schema::table('orders', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('product_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // product_id → item_id 매핑
        DB::statement("
            UPDATE orders o
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = o.product_id
            SET o.item_id = m.item_id
            WHERE o.product_id IS NOT NULL
        ");

        Log::info('Phase 5: orders migrated');
    }

    /**
     * order_items 마이그레이션
     */
    private function migrateOrderItems(): void
    {
        if (! Schema::hasTable('order_items')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('order_items', 'item_id')) {
            Schema::table('order_items', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('product_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // product_id → item_id 매핑
        DB::statement("
            UPDATE order_items oi
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = oi.product_id
            SET oi.item_id = m.item_id
            WHERE oi.product_id IS NOT NULL
        ");

        Log::info('Phase 5: order_items migrated');
    }

    /**
     * material_receipts 마이그레이션
     */
    private function migrateMaterialReceipts(): void
    {
        if (! Schema::hasTable('material_receipts')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('material_receipts', 'item_id')) {
            Schema::table('material_receipts', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('material_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // material_id → item_id 매핑
        DB::statement("
            UPDATE material_receipts mr
            JOIN item_id_mappings m ON m.source_table = 'materials' AND m.source_id = mr.material_id
            SET mr.item_id = m.item_id
            WHERE mr.material_id IS NOT NULL
        ");

        Log::info('Phase 5: material_receipts migrated');
    }

    /**
     * lots 마이그레이션
     */
    private function migrateLots(): void
    {
        if (! Schema::hasTable('lots')) {
            return;
        }

        // item_id 컬럼 추가
        if (! Schema::hasColumn('lots', 'item_id')) {
            Schema::table('lots', function (Blueprint $table) {
                $table->unsignedBigInteger('item_id')->nullable()->after('material_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // material_id → item_id 매핑
        DB::statement("
            UPDATE lots l
            JOIN item_id_mappings m ON m.source_table = 'materials' AND m.source_id = l.material_id
            SET l.item_id = m.item_id
            WHERE l.material_id IS NOT NULL
        ");

        Log::info('Phase 5: lots migrated');
    }

    /**
     * price_histories 마이그레이션
     */
    private function migratePriceHistories(): void
    {
        if (! Schema::hasTable('price_histories')) {
            return;
        }

        // new_item_id 컬럼 추가 (item_id가 이미 있을 수 있음)
        if (! Schema::hasColumn('price_histories', 'new_item_id')) {
            Schema::table('price_histories', function (Blueprint $table) {
                $table->unsignedBigInteger('new_item_id')->nullable()->after('item_id')
                    ->comment('items 테이블 참조 ID');
            });
        }

        // item_type=PRODUCT → products 매핑
        DB::statement("
            UPDATE price_histories ph
            JOIN item_id_mappings m ON m.source_table = 'products' AND m.source_id = ph.item_id
            SET ph.new_item_id = m.item_id
            WHERE ph.item_type = 'PRODUCT'
        ");

        // item_type=MATERIAL → materials 매핑
        DB::statement("
            UPDATE price_histories ph
            JOIN item_id_mappings m ON m.source_table = 'materials' AND m.source_id = ph.item_id
            SET ph.new_item_id = m.item_id
            WHERE ph.item_type = 'MATERIAL'
        ");

        Log::info('Phase 5: price_histories migrated');
    }

    /**
     * item_fields source_table 업데이트
     */
    private function migrateItemFields(): void
    {
        if (! Schema::hasTable('item_fields')) {
            return;
        }

        // source_table 컬럼 값 업데이트
        DB::table('item_fields')
            ->whereIn('source_table', ['products', 'materials'])
            ->update(['source_table' => 'items']);

        Log::info('Phase 5: item_fields migrated');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // item_fields 복원
        // Note: 원본 source_table 값은 item_type으로 추론
        DB::statement("
            UPDATE item_fields if2
            SET if2.source_table = CASE
                WHEN if2.item_type IN ('FG', 'PT') THEN 'products'
                WHEN if2.item_type IN ('SM', 'RM', 'CS') THEN 'materials'
                ELSE if2.source_table
            END
            WHERE if2.source_table = 'items'
        ");

        // 추가된 컬럼 제거
        $columnsToRemove = [
            'product_components' => ['item_id', 'parent_item_id'],
            'bom_template_items' => ['item_id'],
            'orders' => ['item_id'],
            'order_items' => ['item_id'],
            'material_receipts' => ['item_id'],
            'lots' => ['item_id'],
            'price_histories' => ['new_item_id'],
        ];

        foreach ($columnsToRemove as $table => $columns) {
            if (Schema::hasTable($table)) {
                Schema::table($table, function (Blueprint $table) use ($columns) {
                    foreach ($columns as $column) {
                        if (Schema::hasColumn($table->getTable(), $column)) {
                            $table->dropColumn($column);
                        }
                    }
                });
            }
        }

        Log::info('Phase 5: Reference tables migration rolled back');
    }
};
