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

<?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::create('reporting_settings', function (Blueprint $t) { $t->unsignedInteger('id')->primary(); $t->decimal('valid_deposit_min', 24, 10)->default(20); $t->dateTime('activity_started_at'); $t->timestamps(); });
        $now = now('Asia/Shanghai')->format('Y-m-d H:i:s');
        DB::table('reporting_settings')->insert(['id' => 1, 'valid_deposit_min' => '20', 'activity_started_at' => $now, 'created_at' => $now, 'updated_at' => $now]);
        Schema::create('reporting_contexts', function (Blueprint $t) {
            $t->id(); $t->string('source', 40); $t->string('source_id', 80); $t->unique(['source', 'source_id']);
            $t->unsignedBigInteger('user_id')->nullable(); $t->unsignedBigInteger('agent_id')->nullable(); $t->unsignedBigInteger('inviter_id')->nullable();
            $t->string('client_platform', 8)->default('unknown'); $t->string('attribution_quality', 32)->default('event_snapshot'); $t->boolean('settlement_verified')->default(false); $t->dateTime('occurred_at'); $t->dateTime('succeeded_at')->nullable();
        });
        Schema::create('reporting_orders', function (Blueprint $t) {
            $t->id(); $t->string('source', 40); $t->string('source_id', 80); $t->unique(['source', 'source_id']);
            $t->string('parent_source', 40); $t->string('parent_id', 80); $t->unsignedBigInteger('user_id')->index();
            $t->unsignedBigInteger('agent_id')->nullable()->index(); $t->unsignedBigInteger('inviter_id')->nullable()->index();
            $t->string('attribution_quality', 32); $t->string('category', 32); $t->string('client_platform', 8)->default('unknown'); $t->string('currency', 8);
            $t->dateTime('bet_at')->index(); $t->dateTime('settled_at')->nullable()->index(); $t->dateTime('settlement_candidate_at')->nullable();
            foreach (['bet_amount', 'payout_amount', 'net_profit', 'refund_amount'] as $f) $t->decimal($f, 24, 10)->default(0);
            $t->boolean('settled'); $t->boolean('voided'); $t->string('time_quality', 24); $t->timestamps();
        });
        Schema::create('reporting_events', function (Blueprint $t) {
            $t->id(); $t->string('source', 40); $t->string('source_id', 80); $t->string('kind', 32); $t->unique(['source', 'source_id', 'kind']);
            $t->unsignedBigInteger('user_id')->nullable()->index(); $t->unsignedBigInteger('agent_id')->nullable()->index(); $t->unsignedBigInteger('inviter_id')->nullable()->index();
            $t->string('attribution_quality', 32); $t->string('category', 32)->nullable(); $t->string('client_platform', 8)->default('unknown');
            $t->string('currency', 8); $t->decimal('amount', 24, 10)->nullable(); $t->decimal('customer_fee', 24, 10)->default(0); $t->decimal('configured_fee', 24, 10)->nullable();
            $t->dateTime('occurred_at')->nullable()->index(); $t->dateTime('candidate_at')->index(); $t->timestamps();
        });
        Schema::create('reporting_reconciliations', function (Blueprint $t) {
            $t->id(); $t->unsignedBigInteger('event_id')->unique(); $t->decimal('actual_amount', 24, 10); $t->decimal('external_fee', 24, 10);
            $t->dateTime('occurred_at'); $t->string('currency', 8); $t->string('evidence', 1000); $t->unsignedBigInteger('operator_id'); $t->timestamps();
        });
        Schema::create('reporting_activity', function (Blueprint $t) {
            $t->id(); $t->unsignedBigInteger('user_id'); $t->unsignedBigInteger('agent_id')->nullable(); $t->unsignedBigInteger('inviter_id')->nullable();
            $t->string('client_platform', 8); $t->string('session_hash', 64); $t->dateTime('observed_at')->index();
            $t->unique(['user_id', 'session_hash', 'observed_at'], 'reporting_activity_dedup');
        });
        Schema::create('reporting_sync_state', function (Blueprint $t) { $t->string('source', 40)->primary(); $t->text('state'); $t->dateTime('updated_at'); });
        Schema::create('reporting_errors', function (Blueprint $t) {
            $t->id(); $t->string('source', 40); $t->string('source_id', 80); $t->unique(['source', 'source_id']);
            $t->dateTime('candidate_at')->nullable(); $t->string('reason', 500); $t->timestamps();
        });
    }
    public function down(): void
    {
        foreach (['reporting_errors', 'reporting_sync_state', 'reporting_activity', 'reporting_reconciliations', 'reporting_events', 'reporting_orders', 'reporting_contexts', 'reporting_settings'] as $table) Schema::dropIfExists($table);
    }
};