bot-28/database/migrations/2026_08_24_140000_create_agent_system_tables.php

<?php

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

return new class extends Migration {
    public function up(): void
    {
        if (!Schema::hasTable('agents')) {
            Schema::create('agents', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('parent_id')->nullable()->index();
                $table->string('path', 512)->default('/')->index();
                $table->unsignedTinyInteger('level')->default(1);
                $table->string('username', 64)->unique();
                $table->string('password');
                $table->string('withdraw_password')->default('');
                $table->string('real_name', 128)->default('');
                $table->string('invitation_code', 32)->unique();
                $table->decimal('balance', 18, 4)->default(0);
                $table->decimal('frozen_balance', 18, 4)->default(0);
                $table->decimal('deposit_fee_rate', 8, 4)->default(0);
                $table->decimal('withdraw_fee_rate', 8, 4)->default(0);
                $table->decimal('flow_commission_rate', 8, 4)->default(0);
                $table->decimal('profit_commission_rate', 8, 4)->default(0);
                $table->unsignedTinyInteger('status')->default(1)->index();
                $table->timestamp('last_login_at')->nullable();
                $table->string('last_login_ip', 64)->default('');
                $table->unsignedBigInteger('created_by')->nullable();
                $table->timestamps();

                $table->index(['level', 'status']);
            });
        }

        if (!Schema::hasTable('agent_tokens')) {
            Schema::create('agent_tokens', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->unique();
                $table->char('token_hash', 64)->unique();
                $table->timestamp('expires_at')->index();
                $table->timestamp('last_used_at')->nullable();
                $table->string('ip', 64)->default('');
                $table->string('device', 16)->default('');
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('agent_domains')) {
            Schema::create('agent_domains', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->string('type', 16)->comment('pc/h5')->index();
                $table->string('domain', 255)->index();
                $table->string('host', 255)->index();
                $table->unsignedTinyInteger('status')->default(1);
                $table->timestamps();
                $table->unique(['agent_id', 'type'], 'uniq_agent_domain_type');
            });
        }

        if (!Schema::hasTable('agent_login_logs')) {
            Schema::create('agent_login_logs', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->string('username', 64);
                $table->string('domain', 255)->default('');
                $table->string('ip', 64)->default('')->index();
                $table->string('country', 128)->default('');
                $table->string('region', 128)->default('');
                $table->string('city', 128)->default('');
                $table->string('device', 16)->default('')->index();
                $table->string('user_agent', 512)->default('');
                $table->timestamp('login_at')->index();
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('agent_balance_logs')) {
            Schema::create('agent_balance_logs', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->string('type', 32)->index();
                $table->decimal('amount', 18, 4);
                $table->decimal('before_balance', 18, 4);
                $table->decimal('after_balance', 18, 4);
                $table->string('reference_type', 64)->default('');
                $table->unsignedBigInteger('reference_id')->nullable();
                $table->string('operator_type', 16)->default('system');
                $table->unsignedBigInteger('operator_id')->nullable();
                $table->string('idempotency_key', 96)->unique();
                $table->string('remark', 500)->default('');
                $table->timestamps();
                $table->index(['agent_id', 'created_at']);
            });
        }

        if (!Schema::hasTable('agent_withdraw_accounts')) {
            Schema::create('agent_withdraw_accounts', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->string('type', 32)->index();
                $table->string('name', 128)->default('');
                $table->string('account', 255);
                $table->string('bank_name', 128)->default('');
                $table->string('network', 32)->default('');
                $table->json('details')->nullable();
                $table->unsignedTinyInteger('status')->default(1);
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('agent_withdrawals')) {
            Schema::create('agent_withdrawals', function (Blueprint $table) {
                $table->id();
                $table->string('order_no', 40)->unique();
                $table->string('request_key', 96)->unique();
                $table->unsignedBigInteger('agent_id')->index();
                $table->unsignedBigInteger('account_id')->nullable();
                $table->string('account_type', 32)->default('');
                $table->text('account_snapshot');
                $table->decimal('amount', 18, 4);
                $table->decimal('fee', 18, 4)->default(0);
                $table->decimal('actual_amount', 18, 4);
                $table->string('status', 24)->default('pending')->index();
                $table->string('remark', 500)->default('');
                $table->string('audit_remark', 500)->default('');
                $table->unsignedBigInteger('audit_admin_id')->nullable();
                $table->timestamp('audited_at')->nullable();
                $table->timestamp('paid_at')->nullable();
                $table->timestamps();
                $table->index(['agent_id', 'created_at']);
            });
        }

        if (!Schema::hasTable('agent_commission_rules')) {
            Schema::create('agent_commission_rules', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->decimal('flow_rate', 8, 4)->default(0);
                $table->decimal('profit_rate', 8, 4)->default(0);
                $table->date('effective_from')->index();
                $table->unsignedTinyInteger('status')->default(1);
                $table->unsignedBigInteger('created_by')->nullable();
                $table->timestamps();
                $table->index(['agent_id', 'status', 'effective_from'], 'idx_agent_commission_rule');
                $table->unique(['agent_id', 'effective_from'], 'uniq_agent_commission_effective');
            });
        }

        if (!Schema::hasTable('agent_commissions')) {
            Schema::create('agent_commissions', function (Blueprint $table) {
                $table->id();
                $table->date('settlement_date');
                $table->unsignedBigInteger('agent_id')->index();
                $table->string('type', 16)->comment('flow/profit');
                $table->decimal('base_amount', 18, 4)->default(0);
                $table->decimal('rate', 8, 4)->default(0);
                $table->decimal('amount', 18, 4)->default(0);
                $table->unsignedBigInteger('bet_count')->default(0);
                $table->string('status', 16)->default('pending')->index();
                $table->json('snapshot')->nullable();
                $table->timestamp('credited_at')->nullable();
                $table->timestamps();
                $table->unique(['settlement_date', 'agent_id', 'type'], 'uniq_agent_commission_daily');
            });
        }

        if (!Schema::hasTable('agent_daily_stats')) {
            Schema::create('agent_daily_stats', function (Blueprint $table) {
                $table->id();
                $table->date('stat_date');
                $table->unsignedBigInteger('agent_id')->index();
                $table->unsignedInteger('direct_agents')->default(0);
                $table->unsignedInteger('direct_members')->default(0);
                $table->decimal('member_balance', 18, 4)->default(0);
                $table->decimal('deposit_amount', 18, 4)->default(0);
                $table->decimal('withdraw_amount', 18, 4)->default(0);
                $table->decimal('manual_credit', 18, 4)->default(0);
                $table->decimal('manual_debit', 18, 4)->default(0);
                $table->decimal('bonus_amount', 18, 4)->default(0);
                $table->decimal('rebate_amount', 18, 4)->default(0);
                $table->decimal('bet_amount', 18, 4)->default(0);
                $table->decimal('valid_bet_amount', 18, 4)->default(0);
                $table->unsignedBigInteger('bet_count')->default(0);
                $table->decimal('win_loss', 18, 4)->default(0);
                $table->decimal('commission_amount', 18, 4)->default(0);
                $table->decimal('company_profit', 18, 4)->default(0);
                $table->timestamps();
                $table->unique(['stat_date', 'agent_id'], 'uniq_agent_daily_stat');
            });
        }

        if (Schema::hasTable('users') && !Schema::hasColumn('users', 'agent_id')) {
            Schema::table('users', function (Blueprint $table) {
                $table->unsignedBigInteger('agent_id')->nullable()->index();
            });
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('users') && Schema::hasColumn('users', 'agent_id')) {
            Schema::table('users', function (Blueprint $table) {
                $table->dropColumn('agent_id');
            });
        }
        foreach ([
            'agent_daily_stats', 'agent_commissions', 'agent_commission_rules',
            'agent_withdrawals', 'agent_withdraw_accounts', 'agent_balance_logs',
            'agent_login_logs', 'agent_domains', 'agent_tokens', 'agents',
        ] as $table) {
            Schema::dropIfExists($table);
        }
    }
};