<?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.
     * clients 테이블의 ENUM 컬럼을 VARCHAR로 변환하고 common_codes의 code 값으로 데이터 마이그레이션
     */
    public function up(): void
    {
        // 1. common_codes에서 name → code 매핑 조회
        $clientTypeMap = DB::table('common_codes')
            ->where('code_group', 'client_type')
            ->pluck('code', 'name')
            ->toArray();

        $badDebtProgressMap = DB::table('common_codes')
            ->where('code_group', 'bad_debt_progress')
            ->pluck('code', 'name')
            ->toArray();

        // 2. 임시 컬럼 추가
        Schema::table('clients', function (Blueprint $table) {
            $table->string('client_type_new', 20)->nullable()->after('client_type');
            $table->string('bad_debt_progress_new', 20)->nullable()->after('bad_debt_progress');
        });

        // 3. 데이터 변환 (name → code)
        foreach ($clientTypeMap as $name => $code) {
            DB::table('clients')
                ->where('client_type', $name)
                ->update(['client_type_new' => $code]);
        }

        foreach ($badDebtProgressMap as $name => $code) {
            DB::table('clients')
                ->where('bad_debt_progress', $name)
                ->update(['bad_debt_progress_new' => $code]);
        }

        // 4. 기존 ENUM 컬럼 삭제 및 새 컬럼 이름 변경
        Schema::table('clients', function (Blueprint $table) {
            $table->dropColumn('client_type');
            $table->dropColumn('bad_debt_progress');
        });

        Schema::table('clients', function (Blueprint $table) {
            $table->renameColumn('client_type_new', 'client_type');
            $table->renameColumn('bad_debt_progress_new', 'bad_debt_progress');
        });

        // 5. 컬럼 코멘트 추가
        DB::statement("ALTER TABLE `clients` MODIFY `client_type` VARCHAR(20) NULL COMMENT '거래처 유형 (common_codes.client_type)'");
        DB::statement("ALTER TABLE `clients` MODIFY `bad_debt_progress` VARCHAR(20) NULL COMMENT '악성채권 진행상태 (common_codes.bad_debt_progress)'");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // 1. common_codes에서 code → name 매핑 조회
        $clientTypeMap = DB::table('common_codes')
            ->where('code_group', 'client_type')
            ->pluck('name', 'code')
            ->toArray();

        $badDebtProgressMap = DB::table('common_codes')
            ->where('code_group', 'bad_debt_progress')
            ->pluck('name', 'code')
            ->toArray();

        // 2. 임시 컬럼 추가 (ENUM)
        DB::statement("ALTER TABLE `clients` ADD `client_type_old` ENUM('매입','매출','매입매출') NULL AFTER `client_type`");
        DB::statement("ALTER TABLE `clients` ADD `bad_debt_progress_old` ENUM('협의중','소송중','회수완료','대손처리') NULL AFTER `bad_debt_progress`");

        // 3. 데이터 변환 (code → name)
        foreach ($clientTypeMap as $code => $name) {
            DB::table('clients')
                ->where('client_type', $code)
                ->update(['client_type_old' => $name]);
        }

        foreach ($badDebtProgressMap as $code => $name) {
            DB::table('clients')
                ->where('bad_debt_progress', $code)
                ->update(['bad_debt_progress_old' => $name]);
        }

        // 4. VARCHAR 컬럼 삭제 및 ENUM 컬럼 이름 변경
        Schema::table('clients', function (Blueprint $table) {
            $table->dropColumn('client_type');
            $table->dropColumn('bad_debt_progress');
        });

        Schema::table('clients', function (Blueprint $table) {
            $table->renameColumn('client_type_old', 'client_type');
            $table->renameColumn('bad_debt_progress_old', 'bad_debt_progress');
        });

        // 5. 기본값 설정
        DB::statement("ALTER TABLE `clients` MODIFY `client_type` ENUM('매입','매출','매입매출') NOT NULL DEFAULT '매입' COMMENT '거래처 유형'");
        DB::statement("ALTER TABLE `clients` MODIFY `bad_debt_progress` ENUM('협의중','소송중','회수완료','대손처리') NULL COMMENT '진행 상태'");
    }
};
