<?php

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

/**
 * Item Master 하이브리드 구조 전환 - Phase 3
 *
 * 목적: 기존 FK 기반 관계 데이터를 entity_relationships 테이블로 이관
 *
 * 이관 대상:
 * 1. item_sections.page_id → entity_relationships (page-section)
 * 2. item_fields.section_id → entity_relationships (section-field)
 * 3. item_bom_items.section_id → entity_relationships (section-bom)
 *
 * 주의사항:
 * - 기존 컬럼(page_id, section_id)의 값은 유지 (하위 호환성)
 * - soft delete된 레코드도 이관 대상에 포함
 * - order_no 값을 그대로 복사
 */
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 1. item_sections → entity_relationships (page-section)
        DB::statement("
            INSERT INTO entity_relationships (tenant_id, group_id, parent_type, parent_id, child_type, child_id, order_no, created_by, created_at, updated_at)
            SELECT
                s.tenant_id,
                COALESCE(s.group_id, 1) as group_id,
                'page' as parent_type,
                s.page_id as parent_id,
                'section' as child_type,
                s.id as child_id,
                s.order_no,
                s.created_by,
                NOW(),
                NOW()
            FROM item_sections s
            WHERE s.page_id IS NOT NULL
            ON DUPLICATE KEY UPDATE updated_at = NOW()
        ");

        // 2. item_fields → entity_relationships (section-field)
        DB::statement("
            INSERT INTO entity_relationships (tenant_id, group_id, parent_type, parent_id, child_type, child_id, order_no, created_by, created_at, updated_at)
            SELECT
                f.tenant_id,
                COALESCE(f.group_id, 1) as group_id,
                'section' as parent_type,
                f.section_id as parent_id,
                'field' as child_type,
                f.id as child_id,
                f.order_no,
                f.created_by,
                NOW(),
                NOW()
            FROM item_fields f
            WHERE f.section_id IS NOT NULL
            ON DUPLICATE KEY UPDATE updated_at = NOW()
        ");

        // 3. item_bom_items → entity_relationships (section-bom)
        // BOM 항목은 order_no가 없으므로 id 기준으로 순서 부여
        DB::statement("
            INSERT INTO entity_relationships (tenant_id, group_id, parent_type, parent_id, child_type, child_id, order_no, created_by, created_at, updated_at)
            SELECT
                b.tenant_id,
                COALESCE(b.group_id, 1) as group_id,
                'section' as parent_type,
                b.section_id as parent_id,
                'bom' as child_type,
                b.id as child_id,
                @rownum := @rownum + 1 as order_no,
                b.created_by,
                NOW(),
                NOW()
            FROM item_bom_items b, (SELECT @rownum := 0) r
            WHERE b.section_id IS NOT NULL
            ORDER BY b.section_id, b.id
            ON DUPLICATE KEY UPDATE updated_at = NOW()
        ");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 이관된 데이터 삭제 (page-section, section-field, section-bom 관계만)
        DB::table('entity_relationships')
            ->whereIn('parent_type', ['page', 'section'])
            ->whereIn('child_type', ['section', 'field', 'bom'])
            ->delete();
    }
};
