<?php

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

/**
 * FK 컬럼 제거 마이그레이션
 *
 * 목적: 모든 엔티티를 완전히 독립적으로 만들고, 관계는 entity_relationships로만 관리
 *
 * 제거 대상:
 * - item_sections.page_id
 * - item_fields.section_id
 * - item_bom_items.section_id
 *
 * 데이터 이관:
 * - 기존 FK 데이터는 entity_relationships로 이관 후 컬럼 삭제
 */
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 1. item_sections.page_id → entity_relationships 이관
        $sections = DB::table('item_sections')
            ->whereNotNull('page_id')
            ->whereNull('deleted_at')
            ->get();

        foreach ($sections as $section) {
            DB::table('entity_relationships')->insertOrIgnore([
                'tenant_id' => $section->tenant_id,
                'group_id' => $section->group_id ?? 1,
                'parent_type' => 'page',
                'parent_id' => $section->page_id,
                'child_type' => 'section',
                'child_id' => $section->id,
                'order_no' => $section->order_no ?? 0,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        // 2. item_fields.section_id → entity_relationships 이관
        $fields = DB::table('item_fields')
            ->whereNotNull('section_id')
            ->whereNull('deleted_at')
            ->get();

        foreach ($fields as $field) {
            DB::table('entity_relationships')->insertOrIgnore([
                'tenant_id' => $field->tenant_id,
                'group_id' => $field->group_id ?? 1,
                'parent_type' => 'section',
                'parent_id' => $field->section_id,
                'child_type' => 'field',
                'child_id' => $field->id,
                'order_no' => $field->order_no ?? 0,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        // 3. item_bom_items.section_id → entity_relationships 이관
        $bomItems = DB::table('item_bom_items')
            ->whereNotNull('section_id')
            ->whereNull('deleted_at')
            ->get();

        foreach ($bomItems as $bom) {
            DB::table('entity_relationships')->insertOrIgnore([
                'tenant_id' => $bom->tenant_id,
                'group_id' => $bom->group_id ?? 1,
                'parent_type' => 'section',
                'parent_id' => $bom->section_id,
                'child_type' => 'bom',
                'child_id' => $bom->id,
                'order_no' => 0,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

        // 4. FK 컬럼 삭제
        Schema::table('item_sections', function (Blueprint $table) {
            $table->dropColumn('page_id');
        });

        Schema::table('item_fields', function (Blueprint $table) {
            $table->dropColumn('section_id');
        });

        Schema::table('item_bom_items', function (Blueprint $table) {
            $table->dropColumn('section_id');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 1. FK 컬럼 복원
        Schema::table('item_sections', function (Blueprint $table) {
            $table->unsignedBigInteger('page_id')->nullable()->after('group_id')->comment('페이지 ID');
        });

        Schema::table('item_fields', function (Blueprint $table) {
            $table->unsignedBigInteger('section_id')->nullable()->after('group_id')->comment('섹션 ID');
        });

        Schema::table('item_bom_items', function (Blueprint $table) {
            $table->unsignedBigInteger('section_id')->nullable()->after('group_id')->comment('섹션 ID');
        });

        // 2. entity_relationships → FK 복원 (page-section)
        $pageRelations = DB::table('entity_relationships')
            ->where('parent_type', 'page')
            ->where('child_type', 'section')
            ->get();

        foreach ($pageRelations as $rel) {
            DB::table('item_sections')
                ->where('id', $rel->child_id)
                ->update(['page_id' => $rel->parent_id]);
        }

        // 3. entity_relationships → FK 복원 (section-field)
        $fieldRelations = DB::table('entity_relationships')
            ->where('parent_type', 'section')
            ->where('child_type', 'field')
            ->get();

        foreach ($fieldRelations as $rel) {
            DB::table('item_fields')
                ->where('id', $rel->child_id)
                ->update(['section_id' => $rel->parent_id]);
        }

        // 4. entity_relationships → FK 복원 (section-bom)
        $bomRelations = DB::table('entity_relationships')
            ->where('parent_type', 'section')
            ->where('child_type', 'bom')
            ->get();

        foreach ($bomRelations as $rel) {
            DB::table('item_bom_items')
                ->where('id', $rel->child_id)
                ->update(['section_id' => $rel->parent_id]);
        }
    }
};
