<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('agent_nodes', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('admin_site_id')->constrained('admin_sites')->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('agent_nodes')->nullOnDelete();
            $table->string('path', 512);
            $table->unsignedSmallInteger('depth')->default(0);
            $table->string('code', 64);
            $table->string('name', 128);
            $table->unsignedTinyInteger('status')->default(1)->comment('1=enabled,0=disabled');
            $table->foreignId('created_by')->nullable()->constrained('admin_users')->nullOnDelete();
            $table->json('extra_json')->nullable();
            $table->timestamps();

            $table->unique(['admin_site_id', 'code'], 'uk_agent_nodes_site_code');
            $table->index(['admin_site_id', 'parent_id'], 'idx_agent_nodes_site_parent');
            $table->index('path', 'idx_agent_nodes_path');
        });

        Schema::create('admin_user_agents', function (Blueprint $table): void {
            $table->foreignId('admin_user_id')->primary()->constrained('admin_users')->cascadeOnDelete();
            $table->foreignId('agent_node_id')->constrained('agent_nodes')->cascadeOnDelete();
            $table->boolean('is_primary')->default(true);
            $table->timestamp('granted_at')->nullable();
        });

        $this->seedRootAgentNodes();
        $this->backfillAdminUserAgents();
    }

    public function down(): void
    {
        Schema::dropIfExists('admin_user_agents');
        Schema::dropIfExists('agent_nodes');
    }

    private function seedRootAgentNodes(): void
    {
        $now = Carbon::now();
        $sites = DB::table('admin_sites')->orderBy('id')->get(['id', 'code', 'name']);

        foreach ($sites as $site) {
            if (DB::table('agent_nodes')->where('admin_site_id', (int) $site->id)->where('depth', 0)->exists()) {
                continue;
            }

            $code = 'root-'.(string) $site->code;
            $nodeId = DB::table('agent_nodes')->insertGetId([
                'admin_site_id' => (int) $site->id,
                'parent_id' => null,
                'path' => '/',
                'depth' => 0,
                'code' => $code,
                'name' => (string) $site->name,
                'status' => 1,
                'created_by' => null,
                'extra_json' => null,
                'created_at' => $now,
                'updated_at' => $now,
            ]);

            DB::table('agent_nodes')->where('id', $nodeId)->update([
                'path' => '/'.$nodeId.'/',
            ]);
        }
    }

    private function backfillAdminUserAgents(): void
    {
        $superRoleId = DB::table('admin_roles')->where('slug', 'super_admin')->value('id');
        $now = Carbon::now();

        $userIds = DB::table('admin_users')->pluck('id');
        foreach ($userIds as $userId) {
            $userId = (int) $userId;
            if (DB::table('admin_user_agents')->where('admin_user_id', $userId)->exists()) {
                continue;
            }

            if ($superRoleId !== null) {
                $isSuper = DB::table('admin_user_site_roles')
                    ->where('admin_user_id', $userId)
                    ->where('role_id', (int) $superRoleId)
                    ->exists();
                if ($isSuper) {
                    continue;
                }
            }

            $siteId = DB::table('admin_user_site_roles')
                ->where('admin_user_id', $userId)
                ->orderBy('site_id')
                ->value('site_id');

            if ($siteId === null) {
                $siteId = DB::table('admin_sites')->where('is_default', true)->value('id')
                    ?? DB::table('admin_sites')->orderBy('id')->value('id');
            }

            if ($siteId === null) {
                continue;
            }

            $rootId = DB::table('agent_nodes')
                ->where('admin_site_id', (int) $siteId)
                ->where('depth', 0)
                ->value('id');

            if ($rootId === null) {
                continue;
            }

            DB::table('admin_user_agents')->insert([
                'admin_user_id' => $userId,
                'agent_node_id' => (int) $rootId,
                'is_primary' => true,
                'granted_at' => $now,
            ]);
        }
    }
};
