<?php

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

/**
 * Phase 1: 중요하지 않은 FK 제약조건 제거
 * - classifications.tenant_id → tenants
 * - departments.parent_id → departments (자기참조)
 *
 * 목적: 관리 편의성 향상 및 성능 최적화
 * 영향: 없음 (Eloquent 관계는 유지, 비즈니스 로직 무결성은 앱 레벨에서 관리)
 */
return new class extends Migration
{
    /**
     * 기존 FK 제약조건 이름을 동적으로 찾는 헬퍼 함수
     */
    private function findForeignKeyName(string $table, string $column): ?string
    {
        $result = DB::selectOne('
            SELECT CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
              AND COLUMN_NAME = ?
              AND REFERENCED_TABLE_NAME IS NOT NULL
            LIMIT 1
        ', [$table, $column]);

        return $result ? $result->CONSTRAINT_NAME : null;
    }

    /**
     * FK 제약조건을 안전하게 제거하는 헬퍼 함수
     */
    private function dropForeignKeyIfExists(string $table, string $column): void
    {
        $fkName = $this->findForeignKeyName($table, $column);
        if ($fkName) {
            DB::statement("ALTER TABLE `{$table}` DROP FOREIGN KEY `{$fkName}`");
            echo "✅ Dropped FK: {$table}.{$column} ({$fkName})\n";
        } else {
            echo "ℹ️  No FK found: {$table}.{$column}\n";
        }
    }

    public function up(): void
    {
        echo "🚀 Phase 1: 중요하지 않은 FK 제약조건 제거 시작\n\n";

        // 1. classifications.tenant_id → tenants 제거
        echo "1️⃣  Classifications 테이블 FK 제거...\n";
        $this->dropForeignKeyIfExists('classifications', 'tenant_id');

        // 2. departments.parent_id → departments (자기참조) 제거
        echo "\n2️⃣  Departments 자기참조 FK 제거...\n";
        $this->dropForeignKeyIfExists('departments', 'parent_id');

        // 3. 인덱스는 유지 (쿼리 성능을 위해)
        echo "\n3️⃣  인덱스 상태 확인 및 유지...\n";

        // classifications.tenant_id 인덱스 확인
        $classificationIndexExists = DB::selectOne("
            SHOW INDEX FROM classifications WHERE Column_name = 'tenant_id'
        ");
        if (! $classificationIndexExists) {
            DB::statement('CREATE INDEX idx_classifications_tenant_id ON classifications (tenant_id)');
            echo "✅ Added index: classifications.tenant_id\n";
        } else {
            echo "ℹ️  Index exists: classifications.tenant_id\n";
        }

        // departments.parent_id 인덱스 확인
        $departmentIndexExists = DB::selectOne("
            SHOW INDEX FROM departments WHERE Column_name = 'parent_id'
        ");
        if (! $departmentIndexExists) {
            DB::statement('CREATE INDEX idx_departments_parent_id ON departments (parent_id)');
            echo "✅ Added index: departments.parent_id\n";
        } else {
            echo "ℹ️  Index exists: departments.parent_id\n";
        }

        echo "\n🎉 Phase 1 FK 제거 완료!\n";
        echo "📋 제거된 FK:\n";
        echo "   - classifications.tenant_id → tenants\n";
        echo "   - departments.parent_id → departments\n";
        echo "📈 예상 효과: 분류 코드 관리 및 부서 구조 변경 시 유연성 증가\n";
    }

    public function down(): void
    {
        echo "🔄 Phase 1 FK 제거 롤백 시작...\n\n";

        // 1. departments.parent_id → departments FK 복구
        echo "1️⃣  Departments 자기참조 FK 복구...\n";
        try {
            Schema::table('departments', function (Blueprint $table) {
                $table->foreign('parent_id')
                    ->references('id')
                    ->on('departments')
                    ->nullOnDelete();
            });
            echo "✅ Restored FK: departments.parent_id → departments\n";
        } catch (\Throwable $e) {
            echo '⚠️  Could not restore FK: departments.parent_id - '.$e->getMessage()."\n";
        }

        // 2. classifications.tenant_id → tenants FK 복구
        echo "\n2️⃣  Classifications 테넌트 FK 복구...\n";
        try {
            Schema::table('classifications', function (Blueprint $table) {
                $table->foreign('tenant_id')
                    ->references('id')
                    ->on('tenants')
                    ->cascadeOnUpdate()
                    ->restrictOnDelete();
            });
            echo "✅ Restored FK: classifications.tenant_id → tenants\n";
        } catch (\Throwable $e) {
            echo '⚠️  Could not restore FK: classifications.tenant_id - '.$e->getMessage()."\n";
        }

        echo "\n🔄 Phase 1 FK 복구 완료!\n";
    }
};
