<?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
{
    /**
     * 목적
     * - 권한 저장을 Spatie 표준(model_has_permissions 등)으로 단일화
     * - 개인/부서 단위의 DENY(및 선택적 임시 ALLOW)를 위해 permission_overrides 신설
     * - 혼재됐던 매트릭스/전용 테이블 정리(요청에 따라 데이터 무시하고 드롭)
     */
    public function up(): void
    {
        // 1) permission_overrides (DENY 최우선, 선택적 ALLOW 지원)
        if (! Schema::hasTable('permission_overrides')) {
            Schema::create('permission_overrides', function (Blueprint $table) {
                $table->bigIncrements('id')->comment('PK');
                $table->unsignedBigInteger('tenant_id')->comment('테넌트 ID');
                $table->string('model_type', 255)->comment('App\\Models\\User | App\\Models\\Department 등');
                $table->unsignedBigInteger('model_id')->comment('모델 PK');
                $table->unsignedBigInteger('permission_id')->comment('permissions.id');
                $table->tinyInteger('effect')->comment('1=ALLOW, -1=DENY');
                $table->string('reason', 255)->nullable()->comment('사유/메모');
                $table->timestamp('effective_from')->nullable()->comment('효력 시작');
                $table->timestamp('effective_to')->nullable()->comment('효력 종료');
                $table->timestamps();
                $table->softDeletes();

                $table->unique(['tenant_id', 'model_type', 'model_id', 'permission_id'], 'uq_perm_override');
                $table->index(['tenant_id', 'permission_id'], 'idx_po_perm');

                $table->foreign('permission_id')
                    ->references('id')->on('permissions')
                    ->onDelete('cascade');
            });
        }

        // 2) 혼재 테이블 드롭(존재하면)
        foreach ([
            'user_menu_permissions',
            'role_menu_permissions',
            'department_permissions',
            'user_permission_overrides',
        ] as $legacyTable) {
            if (Schema::hasTable($legacyTable)) {
                Schema::drop($legacyTable);
            }
        }

        // 3) 보조 인덱스(선택) : model_has_permissions (tenant_id, permission_id)
        if (Schema::hasTable('model_has_permissions')) {
            if (! $this->mysqlIndexExists('model_has_permissions', 'idx_mhp_tenant_perm')) {
                Schema::table('model_has_permissions', function (Blueprint $table) {
                    $table->index(['tenant_id', 'permission_id'], 'idx_mhp_tenant_perm');
                });
            }
        }
    }

    public function down(): void
    {
        // 1) permission_overrides 제거
        if (Schema::hasTable('permission_overrides')) {
            Schema::drop('permission_overrides');
        }

        // 2) 드롭했던 테이블 복구(원본 스키마로)
        if (! Schema::hasTable('user_menu_permissions')) {
            Schema::create('user_menu_permissions', function (Blueprint $table) {
                $table->bigIncrements('id')->comment('PK: 사용자-메뉴 권한 ID');
                $table->unsignedBigInteger('user_id')->comment('FK: 사용자 ID');
                $table->unsignedBigInteger('menu_id')->comment('FK: 메뉴 ID');
                $table->tinyInteger('access')->default(0)->comment('메뉴 접근 권한');
                $table->tinyInteger('read')->default(0)->comment('조회 권한');
                $table->tinyInteger('write')->default(0)->comment('등록/수정/삭제 권한');
                $table->tinyInteger('export')->default(0)->comment('다운로드 권한');
                $table->tinyInteger('approve')->default(0)->comment('승인/반려 권한');
                $table->timestamps();

                $table->unique(['user_id', 'menu_id'], 'user_menu_permissions_user_id_menu_id_unique');
                $table->foreign('menu_id')->references('id')->on('menus')->onDelete('cascade');
                $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            });
        }

        if (! Schema::hasTable('role_menu_permissions')) {
            Schema::create('role_menu_permissions', function (Blueprint $table) {
                $table->bigIncrements('id')->comment('PK: 역할-메뉴 권한 ID');
                $table->unsignedBigInteger('role_id')->comment('FK: 역할 ID');
                $table->unsignedBigInteger('menu_id')->comment('FK: 메뉴 ID');
                $table->tinyInteger('access')->default(0)->comment('메뉴 접근 권한');
                $table->tinyInteger('read')->default(0)->comment('조회 권한');
                $table->tinyInteger('write')->default(0)->comment('등록/수정/삭제 권한');
                $table->tinyInteger('export')->default(0)->comment('다운로드 권한');
                $table->tinyInteger('approve')->default(0)->comment('승인/반려 권한');
                $table->timestamps();

                $table->unique(['role_id', 'menu_id'], 'role_menu_permissions_role_id_menu_id_unique');
                $table->foreign('menu_id')->references('id')->on('menus')->onDelete('cascade');
                $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
            });
        }

        if (! Schema::hasTable('department_permissions')) {
            Schema::create('department_permissions', function (Blueprint $table) {
                $table->bigIncrements('id')->comment('PK');
                $table->unsignedBigInteger('tenant_id')->comment('테넌트 ID');
                $table->unsignedBigInteger('department_id')->comment('부서 ID');
                $table->unsignedBigInteger('permission_id')->comment('Spatie permissions.id');
                $table->unsignedBigInteger('menu_id')->nullable()->comment('메뉴 ID (선택), 특정 메뉴 범위 권한');
                $table->tinyInteger('is_allowed')->default(1)->comment('허용여부(1=ALLOW,0=DENY)');
                $table->timestamps();
                $table->softDeletes();

                $table->unique(['tenant_id', 'department_id', 'permission_id', 'menu_id'], 'dept_perm_unique');
                $table->index(['tenant_id', 'department_id'], 'dept_perm_dept_idx');
                $table->index(['tenant_id', 'menu_id'], 'dept_perm_menu_idx');
                $table->index(['tenant_id', 'permission_id'], 'dept_perm_perm_idx');
            });
        }

        if (! Schema::hasTable('user_permission_overrides')) {
            Schema::create('user_permission_overrides', function (Blueprint $table) {
                $table->bigIncrements('id')->comment('PK');
                $table->unsignedBigInteger('tenant_id')->comment('테넌트 ID');
                $table->unsignedBigInteger('user_id')->comment('사용자 ID');
                $table->unsignedBigInteger('permission_id')->comment('Spatie permissions.id');
                $table->unsignedBigInteger('menu_id')->nullable()->comment('메뉴 ID (선택)');
                $table->tinyInteger('is_allowed')->default(1)->comment('허용여부(1=ALLOW,0=DENY)');
                $table->string('reason', 255)->nullable()->comment('사유/메모');
                $table->timestamp('effective_from')->nullable()->comment('효력 시작');
                $table->timestamp('effective_to')->nullable()->comment('효력 종료');
                $table->timestamps();
                $table->softDeletes();

                $table->unique(['tenant_id', 'user_id', 'permission_id', 'menu_id'], 'user_perm_override_unique');
                $table->index(['tenant_id', 'permission_id'], 'user_perm_perm_idx');
                $table->index(['tenant_id', 'user_id'], 'user_perm_user_idx');
            });
        }

        // 3) 보조 인덱스 롤백
        if (Schema::hasTable('model_has_permissions')) {
            if ($this->mysqlIndexExists('model_has_permissions', 'idx_mhp_tenant_perm')) {
                Schema::table('model_has_permissions', function (Blueprint $table) {
                    $table->dropIndex('idx_mhp_tenant_perm');
                });
            }
        }
    }

    /**
     * MySQL: 특정 테이블에 인덱스 존재 여부 확인
     */
    private function mysqlIndexExists(string $table, string $indexName): bool
    {
        // 스키마(데이터베이스)명
        $schema = DB::getDatabaseName();

        $exists = DB::table('information_schema.STATISTICS')
            ->where('TABLE_SCHEMA', $schema)
            ->where('TABLE_NAME', $table)
            ->where('INDEX_NAME', $indexName)
            ->exists();

        return (bool) $exists;
    }
};
