<?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
    {
        // 1) parent_id 추가 (tenant_id 뒤에 배치)
        if (! Schema::hasColumn('departments', 'parent_id')) {
            Schema::table('departments', function (Blueprint $table) {
                $table->unsignedBigInteger('parent_id')
                    ->nullable()
                    ->after('tenant_id')
                    ->comment('상위 부서 ID');
                // 필요 시 인덱스:
                $table->index('parent_id');
                // 필요 시 FK:
                $table->foreign('parent_id')->references('id')->on('departments')->nullOnDelete();
            });
        }

        // 2) created_by / updated_by / deleted_by 를 sort_order 뒤로 이동
        // (세 컬럼이 존재할 때에만 수행)
        $hasCreated = Schema::hasColumn('departments', 'created_by');
        $hasUpdated = Schema::hasColumn('departments', 'updated_by');
        $hasDeleted = Schema::hasColumn('departments', 'deleted_by');

        if ($hasCreated || $hasUpdated || $hasDeleted) {
            // 컬럼 순서: sort_order -> created_by -> updated_by -> deleted_by
            // 각 컬럼의 정의(타입/NULL/COMMENT)는 기존 스키마와 동일하게 유지
            $sql = [];

            if ($hasCreated) {
                $sql[] = "MODIFY COLUMN `created_by` BIGINT UNSIGNED NULL COMMENT '생성자 사용자 ID' AFTER `sort_order`";
            }
            if ($hasUpdated) {
                $after = $hasCreated ? 'created_by' : 'sort_order';
                $sql[] = "MODIFY COLUMN `updated_by` BIGINT UNSIGNED NULL COMMENT '수정자 사용자 ID' AFTER `{$after}`";
            }
            if ($hasDeleted) {
                $after = $hasUpdated ? 'updated_by' : ($hasCreated ? 'created_by' : 'sort_order');
                $sql[] = "MODIFY COLUMN `deleted_by` BIGINT UNSIGNED NULL COMMENT '삭제자 사용자 ID' AFTER `{$after}`";
            }

            if (! empty($sql)) {
                DB::statement('ALTER TABLE `departments` '.implode(', ', $sql));
            }
        }
    }

    public function down(): void
    {
        // 되돌리기: parent_id 제거, 세 컬럼을 원래 위치(타임스탬프 뒤쪽)로 복귀
        if (Schema::hasColumn('departments', 'parent_id')) {
            Schema::table('departments', function (Blueprint $table) {
                // FK/인덱스가 있었다면 먼저 드롭 필요
                $table->dropForeign(['parent_id']);
                $table->dropIndex(['parent_id']);
                $table->dropColumn('parent_id');
            });
        }

        $hasCreated = Schema::hasColumn('departments', 'created_by');
        $hasUpdated = Schema::hasColumn('departments', 'updated_by');
        $hasDeleted = Schema::hasColumn('departments', 'deleted_by');

        if ($hasCreated || $hasUpdated || $hasDeleted) {
            // 원래 정의로 복귀: 일반적으로 deleted_at 뒤에 세 컬럼이 있었음
            $sql = [];

            if ($hasCreated) {
                $sql[] = "MODIFY COLUMN `created_by` BIGINT UNSIGNED NULL COMMENT '생성자 사용자 ID' AFTER `deleted_at`";
            }
            if ($hasUpdated) {
                $sql[] = "MODIFY COLUMN `updated_by` BIGINT UNSIGNED NULL COMMENT '수정자 사용자 ID' AFTER `created_by`";
            }
            if ($hasDeleted) {
                $sql[] = "MODIFY COLUMN `deleted_by` BIGINT UNSIGNED NULL COMMENT '삭제자 사용자 ID' AFTER `updated_by`";
            }

            if (! empty($sql)) {
                DB::statement('ALTER TABLE `departments` '.implode(', ', $sql));
            }
        }
    }
};
