<?php

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

/**
 * item_master_fields 테이블 통합
 *
 * 목적: item_master_fields의 속성을 item_fields에 추가하고 데이터 이관
 *
 * 변경 내용:
 * 1. item_fields의 section_id를 nullable로 변경 (독립 필드 지원)
 * 2. item_fields에 category, description, is_common 컬럼 추가
 * 3. item_master_fields 데이터를 item_fields로 이관
 * 4. item_master_fields 테이블은 유지 (하위 호환성, 추후 삭제)
 *
 * 참고: FK (fk_item_fields_section)는 2025_11_26_100001에서 이미 제거됨
 */
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 1. section_id를 nullable로 변경 (이미 nullable이면 스킵)
        $sectionIdColumn = DB::selectOne("SHOW COLUMNS FROM item_fields WHERE Field = 'section_id'");
        if ($sectionIdColumn && $sectionIdColumn->Null === 'NO') {
            Schema::table('item_fields', function (Blueprint $table) {
                $table->unsignedBigInteger('section_id')->nullable()->comment('섹션 ID (NULL = 독립 필드)')->change();
            });
        }

        // 2. item_fields에 컬럼 추가 (이미 있으면 스킵)
        if (! Schema::hasColumn('item_fields', 'category')) {
            Schema::table('item_fields', function (Blueprint $table) {
                $table->string('category', 100)->nullable()->after('properties')->comment('카테고리');
                $table->text('description')->nullable()->after('category')->comment('설명');
                $table->boolean('is_common')->default(false)->after('description')->comment('공통 필드 여부');
            });
        }

        // 3. item_master_fields 데이터를 item_fields로 이관
        DB::statement('
            INSERT INTO item_fields (
                tenant_id, group_id, section_id, field_name, field_type,
                order_no, is_required, default_value, placeholder,
                display_condition, validation_rules, options, properties,
                category, description, is_common,
                created_by, updated_by, deleted_by,
                created_at, updated_at, deleted_at
            )
            SELECT
                m.tenant_id,
                COALESCE(m.group_id, 1) as group_id,
                NULL as section_id,
                m.field_name,
                m.field_type,
                0 as order_no,
                0 as is_required,
                m.default_value,
                NULL as placeholder,
                NULL as display_condition,
                m.validation_rules,
                m.options,
                m.properties,
                m.category,
                m.description,
                m.is_common,
                m.created_by,
                m.updated_by,
                m.deleted_by,
                m.created_at,
                m.updated_at,
                m.deleted_at
            FROM item_master_fields m
        ');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 이관된 데이터 삭제 (section_id = null인 레코드)
        DB::table('item_fields')
            ->whereNull('section_id')
            ->delete();

        // 컬럼 삭제
        Schema::table('item_fields', function (Blueprint $table) {
            $table->dropColumn(['category', 'description', 'is_common']);
        });

        // section_id를 NOT NULL로 복원
        Schema::table('item_fields', function (Blueprint $table) {
            $table->unsignedBigInteger('section_id')->nullable(false)->comment('섹션 ID')->change();
        });
    }
};
