<?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
    {
        Schema::table('expenses', function (Blueprint $table): void {
            if (! Schema::hasColumn('expenses', 'status')) {
                $table->string('status', 30)->default('approved')->after('reference')->index();
            }

            if (! Schema::hasColumn('expenses', 'approval_level_required')) {
                $table->unsignedTinyInteger('approval_level_required')->default(1)->after('status');
            }

            if (! Schema::hasColumn('expenses', 'current_approval_level')) {
                $table->unsignedTinyInteger('current_approval_level')->default(0)->after('approval_level_required');
            }

            if (! Schema::hasColumn('expenses', 'approval_history')) {
                $table->json('approval_history')->nullable()->after('current_approval_level');
            }

            if (! Schema::hasColumn('expenses', 'submitted_at')) {
                $table->timestamp('submitted_at')->nullable()->after('approval_history');
            }

            if (! Schema::hasColumn('expenses', 'approved_by')) {
                $table->foreignId('approved_by')->nullable()->after('submitted_at')->constrained('users')->nullOnDelete();
            }

            if (! Schema::hasColumn('expenses', 'approved_at')) {
                $table->timestamp('approved_at')->nullable()->after('approved_by');
            }

            if (! Schema::hasColumn('expenses', 'rejected_by')) {
                $table->foreignId('rejected_by')->nullable()->after('approved_at')->constrained('users')->nullOnDelete();
            }

            if (! Schema::hasColumn('expenses', 'rejected_at')) {
                $table->timestamp('rejected_at')->nullable()->after('rejected_by');
            }

            if (! Schema::hasColumn('expenses', 'rejected_reason')) {
                $table->text('rejected_reason')->nullable()->after('rejected_at');
            }

            if (! Schema::hasColumn('expenses', 'accounting_posted_at')) {
                $table->timestamp('accounting_posted_at')->nullable()->after('rejected_reason');
            }
        });

        DB::table('expenses')->update([
            'status' => 'approved',
            'approval_level_required' => 1,
            'current_approval_level' => 1,
            'submitted_at' => DB::raw('COALESCE(submitted_at, created_at)'),
            'approved_at' => DB::raw('COALESCE(approved_at, created_at)'),
            'accounting_posted_at' => DB::raw('COALESCE(accounting_posted_at, created_at)'),
        ]);

        $this->addPermissionToRoleProfiles('expenses.approve', ['tenant_admin', 'manager']);

        if (! $this->indexExists('expenses', 'expenses_tenant_status_spent_index')) {
            Schema::table('expenses', function (Blueprint $table): void {
                $table->index(['tenant_id', 'status', 'spent_at'], 'expenses_tenant_status_spent_index');
            });
        }
    }

    public function down(): void
    {
        if ($this->indexExists('expenses', 'expenses_tenant_status_spent_index')) {
            Schema::table('expenses', function (Blueprint $table): void {
                $table->dropIndex('expenses_tenant_status_spent_index');
            });
        }

        $this->removePermissionFromRoleProfiles('expenses.approve');

        Schema::table('expenses', function (Blueprint $table): void {
            foreach (['accounting_posted_at', 'rejected_reason', 'rejected_at', 'approved_at', 'submitted_at', 'approval_history', 'current_approval_level', 'approval_level_required', 'status'] as $column) {
                if (Schema::hasColumn('expenses', $column)) {
                    $table->dropColumn($column);
                }
            }

            foreach (['rejected_by', 'approved_by'] as $column) {
                if (Schema::hasColumn('expenses', $column)) {
                    $table->dropConstrainedForeignId($column);
                }
            }
        });
    }

    private function addPermissionToRoleProfiles(string $permission, array $roles): void
    {
        DB::table('role_profiles')
            ->whereIn('base_role', $roles)
            ->orderBy('id')
            ->chunkById(100, function ($profiles) use ($permission): void {
                foreach ($profiles as $profile) {
                    $permissions = json_decode((string) $profile->permissions, true);
                    $permissions = is_array($permissions) ? $permissions : [];

                    if (! in_array($permission, $permissions, true)) {
                        $permissions[] = $permission;

                        DB::table('role_profiles')
                            ->where('id', $profile->id)
                            ->update(['permissions' => json_encode(array_values($permissions))]);
                    }
                }
            });
    }

    private function removePermissionFromRoleProfiles(string $permission): void
    {
        DB::table('role_profiles')
            ->orderBy('id')
            ->chunkById(100, function ($profiles) use ($permission): void {
                foreach ($profiles as $profile) {
                    $permissions = json_decode((string) $profile->permissions, true);
                    $permissions = is_array($permissions) ? $permissions : [];
                    $permissions = array_values(array_filter($permissions, fn ($item) => $item !== $permission));

                    DB::table('role_profiles')
                        ->where('id', $profile->id)
                        ->update(['permissions' => json_encode($permissions)]);
                }
            });
    }

    private function indexExists(string $table, string $name): bool
    {
        try {
            return collect(Schema::getIndexes($table))
                ->contains(fn (array $index): bool => ($index['name'] ?? null) === $name);
        } catch (Throwable) {
            return false;
        }
    }
};
