<?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::table('admin_roles', function (Blueprint $table): void {
            $table->foreignId('owner_agent_id')->nullable()->after('sort_order')->constrained('agent_nodes')->nullOnDelete();
            $table->foreignId('delegated_from_role_id')->nullable()->after('owner_agent_id')->constrained('admin_roles')->nullOnDelete();
            $table->string('scope_type', 16)->default('system')->after('delegated_from_role_id');
        });

        Schema::create('admin_user_agent_roles', function (Blueprint $table): void {
            $table->foreignId('admin_user_id')->constrained('admin_users')->cascadeOnDelete();
            $table->foreignId('agent_node_id')->constrained('agent_nodes')->cascadeOnDelete();
            $table->foreignId('role_id')->constrained('admin_roles')->cascadeOnDelete();
            $table->timestamp('granted_at')->nullable();
            $table->primary(['admin_user_id', 'agent_node_id', 'role_id'], 'pk_admin_user_agent_roles');
        });

        Schema::table('players', function (Blueprint $table): void {
            $table->foreignId('agent_node_id')->nullable()->after('site_code')->constrained('agent_nodes')->nullOnDelete();
            $table->index(['site_code', 'agent_node_id'], 'idx_players_site_agent');
        });

        DB::table('admin_roles')->update(['scope_type' => 'system']);

        $this->backfillAdminUserAgentRoles();
    }

    public function down(): void
    {
        Schema::table('players', function (Blueprint $table): void {
            $table->dropIndex('idx_players_site_agent');
            $table->dropConstrainedForeignId('agent_node_id');
        });

        Schema::dropIfExists('admin_user_agent_roles');

        Schema::table('admin_roles', function (Blueprint $table): void {
            $table->dropConstrainedForeignId('delegated_from_role_id');
            $table->dropConstrainedForeignId('owner_agent_id');
            $table->dropColumn('scope_type');
        });
    }

    private function backfillAdminUserAgentRoles(): void
    {
        $now = Carbon::now();
        $rows = DB::table('admin_user_site_roles as usr')
            ->join('admin_user_agents as uaa', 'uaa.admin_user_id', '=', 'usr.admin_user_id')
            ->join('agent_nodes as an', static function ($join): void {
                $join->on('an.id', '=', 'uaa.agent_node_id')
                    ->on('an.admin_site_id', '=', 'usr.site_id');
            })
            ->select(['usr.admin_user_id', 'uaa.agent_node_id', 'usr.role_id', 'usr.granted_at'])
            ->get();

        foreach ($rows as $row) {
            DB::table('admin_user_agent_roles')->updateOrInsert(
                [
                    'admin_user_id' => (int) $row->admin_user_id,
                    'agent_node_id' => (int) $row->agent_node_id,
                    'role_id' => (int) $row->role_id,
                ],
                [
                    'granted_at' => $row->granted_at ?? $now,
                ],
            );
        }
    }
};
