<?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
{
    public function up(): void
    {
        // 컬럼 추가 (이미 존재하면 건너뜀)
        if (! Schema::hasColumn('equipment_inspection_templates', 'inspection_cycle')) {
            Schema::table('equipment_inspection_templates', function (Blueprint $table) {
                $table->string('inspection_cycle', 20)->default('daily')->after('equipment_id')
                    ->comment('점검주기: daily/weekly/monthly/bimonthly/quarterly/semiannual');
            });
        }

        // FK 삭제 → 유니크 변경 → FK 재생성 (개별 statement)
        $this->dropFkIfExists('equipment_inspection_templates', 'equipment_inspection_templates_equipment_id_foreign');
        $this->dropUniqueIfExists('equipment_inspection_templates', 'uq_equipment_item_no');

        Schema::table('equipment_inspection_templates', function (Blueprint $table) {
            $table->unique(['equipment_id', 'inspection_cycle', 'item_no'], 'uq_equipment_cycle_item_no');
            $table->index('inspection_cycle', 'idx_insp_tmpl_cycle');

            $table->foreign('equipment_id')
                ->references('id')
                ->on('equipments')
                ->onDelete('cascade');
        });
    }

    public function down(): void
    {
        $this->dropFkIfExists('equipment_inspection_templates', 'equipment_inspection_templates_equipment_id_foreign');

        Schema::table('equipment_inspection_templates', function (Blueprint $table) {
            $table->dropIndex('idx_insp_tmpl_cycle');
            $table->dropUnique('uq_equipment_cycle_item_no');
            $table->unique(['equipment_id', 'item_no'], 'uq_equipment_item_no');
            $table->dropColumn('inspection_cycle');

            $table->foreign('equipment_id')
                ->references('id')
                ->on('equipments')
                ->onDelete('cascade');
        });
    }

    private function dropFkIfExists(string $table, string $fkName): void
    {
        $fks = DB::select("SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = ?", [$table, $fkName]);
        if (count($fks) > 0) {
            DB::statement("ALTER TABLE `{$table}` DROP FOREIGN KEY `{$fkName}`");
        }
    }

    private function dropUniqueIfExists(string $table, string $indexName): void
    {
        $indexes = DB::select("SHOW INDEX FROM `{$table}` WHERE Key_name = ?", [$indexName]);
        if (count($indexes) > 0) {
            DB::statement("ALTER TABLE `{$table}` DROP INDEX `{$indexName}`");
        }
    }
};
