<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * item_fields 테이블의 유니크 제약을 변경:
     * - 기존: UNIQUE(tenant_id, field_key)
     * - 변경: UNIQUE(tenant_id, field_key, source_table)
     *
     * 이를 통해 같은 field_key를 다른 source_table에서 사용 가능
     * 예: products.name, materials.name 모두 허용
     */
    public function up(): void
    {
        // 1. 유니크 인덱스 변경 (먼저!)
        Schema::table('item_fields', function (Blueprint $table) {
            // 기존 유니크 인덱스 삭제
            $table->dropUnique('uq_item_fields_tenant_field_key');

            // 새로운 유니크 인덱스 추가 (tenant_id, field_key, source_table)
            $table->unique(
                ['tenant_id', 'field_key', 'source_table'],
                'uq_item_fields_tenant_field_key_source'
            );
        });

        // 2. 기존 데이터의 접두사 제거
        $this->removeFieldKeyPrefixes();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 1. 접두사 복원
        $this->restoreFieldKeyPrefixes();

        // 2. 유니크 인덱스 복원
        Schema::table('item_fields', function (Blueprint $table) {
            // 새 유니크 인덱스 삭제
            $table->dropUnique('uq_item_fields_tenant_field_key_source');

            // 기존 유니크 인덱스 복원
            $table->unique(
                ['tenant_id', 'field_key'],
                'uq_item_fields_tenant_field_key'
            );
        });
    }

    /**
     * 기존 데이터의 접두사 제거
     *
     * materials: materials_name → name, materials_unit → unit, materials_category_id → category_id
     * material_inspections: material_inspections_quantity → quantity, material_inspections_note → note
     * material_receipts: material_receipts_lot_no → lot_no, material_receipts_quantity → quantity, material_receipts_note → note
     */
    private function removeFieldKeyPrefixes(): void
    {
        // materials 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'materials_name')
            ->update(['field_key' => 'name']);

        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'materials_unit')
            ->update(['field_key' => 'unit']);

        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'materials_category_id')
            ->update(['field_key' => 'category_id']);

        // material_inspections 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'material_inspections')
            ->where('field_key', 'material_inspections_quantity')
            ->update(['field_key' => 'quantity']);

        DB::table('item_fields')
            ->where('source_table', 'material_inspections')
            ->where('field_key', 'material_inspections_note')
            ->update(['field_key' => 'note']);

        // material_receipts 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'material_receipts_lot_no')
            ->update(['field_key' => 'lot_no']);

        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'material_receipts_quantity')
            ->update(['field_key' => 'quantity']);

        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'material_receipts_note')
            ->update(['field_key' => 'note']);
    }

    /**
     * 접두사 복원 (롤백용)
     */
    private function restoreFieldKeyPrefixes(): void
    {
        // materials 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'name')
            ->update(['field_key' => 'materials_name']);

        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'unit')
            ->update(['field_key' => 'materials_unit']);

        DB::table('item_fields')
            ->where('source_table', 'materials')
            ->where('field_key', 'category_id')
            ->update(['field_key' => 'materials_category_id']);

        // material_inspections 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'material_inspections')
            ->where('field_key', 'quantity')
            ->update(['field_key' => 'material_inspections_quantity']);

        DB::table('item_fields')
            ->where('source_table', 'material_inspections')
            ->where('field_key', 'note')
            ->update(['field_key' => 'material_inspections_note']);

        // material_receipts 테이블 필드
        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'lot_no')
            ->update(['field_key' => 'material_receipts_lot_no']);

        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'quantity')
            ->update(['field_key' => 'material_receipts_quantity']);

        DB::table('item_fields')
            ->where('source_table', 'material_receipts')
            ->where('field_key', 'note')
            ->update(['field_key' => 'material_receipts_note']);
    }
};
