bot-28/tests/Integration/SyncPerformanceMysqlTest.php

<?php

namespace Tests\Integration;

use App\Services\PromotionFacts;
use App\Services\Reporting\ReportSync;
use App\Support\SyncCommandLock;
use Carbon\Carbon;
use Illuminate\Config\Repository;
use Illuminate\Database\Capsule\Manager;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Foundation\Application;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Facade;
use Illuminate\Support\Facades\Schema;
use PDO;
use PHPUnit\Framework\TestCase;

/** Explicit opt-in only; creates and drops its own database, never loads project .env/providers. */
class SyncPerformanceMysqlTest extends TestCase
{
    private ?PDO $control = null;
    private string $database;
    private array $connection;

    protected function setUp(): void
    {
        $socket = getenv('SYNC_TEST_MYSQL_SOCKET');
        if (!$socket) $this->markTestSkipped('Set SYNC_TEST_MYSQL_SOCKET to an isolated test MySQL socket.');
        $this->control = new PDO('mysql:unix_socket=' . $socket, getenv('SYNC_TEST_MYSQL_USER') ?: 'root', getenv('SYNC_TEST_MYSQL_PASSWORD') ?: '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        $this->database = 'bot28_sync_test_' . bin2hex(random_bytes(8));
        $this->control->exec('CREATE DATABASE `' . $this->database . '` CHARACTER SET utf8mb4');
        Facade::clearResolvedInstances();
        $app = new Application(dirname(__DIR__, 2));
        $app->instance('config', new Repository(['app' => ['timezone' => 'Asia/Shanghai'], 'third_game' => ['currency' => 'CNY']]));
        Facade::setFacadeApplication($app);
        $db = new Manager($app);
        $this->connection = ['driver' => 'mysql', 'unix_socket' => $socket, 'database' => $this->database,
            'username' => getenv('SYNC_TEST_MYSQL_USER') ?: 'root', 'password' => getenv('SYNC_TEST_MYSQL_PASSWORD') ?: '',
            'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => 'bot_'];
        $db->addConnection($this->connection); $db->setAsGlobal(); $db->bootEloquent();
        $app->instance('db', $db->getDatabaseManager()); $app->bind('db.schema', fn () => $db->schema());
        Carbon::setTestNow(Carbon::parse('2026-11-02 16:00:00', 'Asia/Shanghai'));
        Schema::create('users', function (Blueprint $t) {
            $t->id(); $t->string('member_id', 64); $t->integer('from'); $t->unsignedBigInteger('agent_id')->nullable();
        });
        DB::table('users')->insert(['id' => 1, 'member_id' => 'm1', 'from' => 1]);
        Schema::create('balance_logs', function (Blueprint $t) {
            $t->id(); $t->string('member_id', 64); $t->integer('type'); $t->string('change_type', 50);
            $t->decimal('amount', 24, 10); $t->timestamps();
        });
        Schema::create('share_bet_facts', function (Blueprint $t) {
            $t->id(); $t->unsignedBigInteger('user_id'); $t->string('source', 32); $t->string('source_id', 64);
            $t->unique(['source', 'source_id']); $t->dateTime('bet_at'); $t->decimal('bet_amount', 24, 10); $t->decimal('net_profit', 24, 10);
            $t->string('currency', 8); $t->boolean('settled'); $t->boolean('voided');
        });
        foreach (['bets', 'jisu_game_order'] as $source) Schema::create($source, function (Blueprint $t) use ($source) {
            $t->id(); $t->string('member_id', 64); $t->decimal('amount', 24, 10);
            $t->decimal($source === 'bets' ? 'profit' : 'win_amount', 24, 10)->nullable(); $t->integer('status'); $t->timestamps();
        });
    }

    protected function tearDown(): void
    {
        if ($this->control) {
            DB::disconnect(); DB::disconnect('contender');
            $this->control->exec('DROP DATABASE `' . $this->database . '`');
            $this->control = null;
            Facade::clearResolvedInstances(); Carbon::setTestNow();
        }
        parent::tearDown();
    }

    public function test_mysql_daily_aggregates_match_bcmath_including_legacy_and_large_decimal_amounts(): void
    {
        foreach ([['9000000000000.0000000001', '-9000000000000.0000000001'], ['10.1234567891', '2.1234567891'],
            ['50', '100'], ['100', '-25.0000000001'], ['99', '0']] as $index => [$bet, $profit]) {
            DB::table('share_bet_facts')->insert(['user_id' => 1, 'source' => 'sport_order', 'source_id' => (string) ($index + 1),
                'bet_at' => '2026-11-02 10:00:00', 'bet_amount' => $bet, 'net_profit' => $profit, 'currency' => 'CNY', 'settled' => 1, 'voided' => 0]);
        }
        foreach (['bets' => 'profit', 'jisu_game_order' => 'win_amount'] as $source => $payout) {
            DB::table($source)->insert(['member_id' => 'm1', 'amount' => '50.0000000001', $payout => '32.1234567891', 'status' => 2, 'created_at' => '2026-11-02 10:00:00']);
        }
        $facts = new PromotionFacts();
        $aggregate = $facts->betting([1], '2026-11-01 00:00:00');
        $bcmath = $facts->betting([1], '2026-11-01 00:00:00', null, ['share_bet_facts' => 0]);
        $this->assertSame($bcmath, $aggregate);
        foreach (['9000000000000.0000000001', '0.1234567891'] as $amount) DB::table('balance_logs')->insert([
            'member_id' => 'm1', 'type' => 1, 'change_type' => '充值', 'amount' => $amount, 'created_at' => '2026-11-02 10:00:00',
        ]);
        $this->assertSame(['2026-11-02' => '9000000000000.1234567892'], $facts->deposits(['m1'], '2026-11-01 00:00:00'));
        DB::table('share_bet_facts')->where('id', 1)->update(['bet_amount' => '-1']);
        $this->expectException(\InvalidArgumentException::class);
        $facts->betting([1], '2026-11-01 00:00:00');
    }

    public function test_mysql_indexes_projection_idempotence_and_recent_corrections(): void
    {
        (require dirname(__DIR__, 2) . '/database/migrations/2026_10_26_140000_create_reporting_tables.php')->up();
        $migration = require dirname(__DIR__, 2) . '/database/migrations/2026_11_03_130000_add_sync_performance_indexes.php';
        $migration->up(); $migration->up();
        $this->assertTrue(Schema::hasIndex('reporting_orders', 'reporting_orders_parent_lookup'));
        DB::table('bets')->insert(['id' => 1, 'member_id' => 'm1', 'amount' => '100.0000000001', 'profit' => '80', 'status' => 1,
            'created_at' => '2026-11-02 10:00:00', 'updated_at' => '2026-11-02 10:00:00']);
        $sync = new ReportSync(); $this->assertSame(0, $sync->sync()['errors']);
        $before = DB::table('reporting_orders')->first();
        Carbon::setTestNow(now('Asia/Shanghai')->addMinutes(10));
        $this->assertSame(0, $sync->sync(true)['errors']);
        $this->assertEquals($before, DB::table('reporting_orders')->first());
        DB::table('bets')->where('id', 1)->update(['status' => 2, 'updated_at' => now('Asia/Shanghai')->toDateTimeString()]);
        $this->assertSame(0, $sync->sync()['errors']);
        $this->assertSame(1, (int) DB::table('reporting_orders')->value('settled'));
        $this->assertSame('80.0000000000', DB::table('reporting_orders')->value('payout_amount'));
        $migration->down();
        $this->assertSame(1, DB::table('reporting_orders')->count());
    }

    public function test_mysql_third_party_cursor_filters_original_ids_in_the_fact_query(): void
    {
        Schema::create('third_game_orders', function (Blueprint $t) { $t->id(); $t->string('order_key', 64)->unique(); });
        foreach ([1, 2] as $id) {
            $key = str_repeat((string) $id, 64);
            DB::table('third_game_orders')->insert(['id' => $id, 'order_key' => $key]);
            DB::table('share_bet_facts')->insert(['user_id' => 1, 'source' => 'third_game_orders', 'source_id' => $key,
                'bet_at' => '2026-11-02 10:00:00', 'bet_amount' => '10', 'net_profit' => '-10', 'currency' => 'CNY', 'settled' => 1, 'voided' => 0]);
        }
        $result = (new PromotionFacts())->betting([1], '2026-11-01 00:00:00', null, ['third_game_orders' => 1]);
        $this->assertSame('10.0000000000', $result['effective']['2026-11-02']);
    }

    public function test_mysql_aggregation_rejects_missing_payouts_instead_of_ignoring_bad_rows(): void
    {
        foreach (['5', null] as $payout) DB::table('bets')->insert(['member_id' => 'm1', 'amount' => '10', 'profit' => $payout,
            'status' => 2, 'created_at' => '2026-11-02 10:00:00']);
        $this->expectException(\InvalidArgumentException::class);
        (new PromotionFacts())->betting([1], '2026-11-01 00:00:00');
    }

    public function test_mysql_command_lock_excludes_other_connections_and_releases_on_exception(): void
    {
        app('config')->set('database.connections.contender', $this->connection);
        $contender = DB::connection('contender');
        $name = 'bot-sync:' . substr(hash('sha256', $this->database . ':bot_:reporting:sync'), 0, 48);
        $lock = new SyncCommandLock();
        try {
            $lock->run('reporting:sync', function () use ($contender, $name) {
                $this->assertSame(0, (int) $contender->selectOne('SELECT GET_LOCK(?, 0) AS acquired', [$name])->acquired);
                throw new \RuntimeException('expected failure');
            });
            $this->fail('Expected callback failure');
        } catch (\RuntimeException $e) { $this->assertSame('expected failure', $e->getMessage()); }
        $this->assertSame(1, (int) $contender->selectOne('SELECT GET_LOCK(?, 0) AS acquired', [$name])->acquired);
        $contender->selectOne('SELECT RELEASE_LOCK(?)', [$name]);
        $this->assertSame(0, $lock->run('reporting:sync', fn () => 0));
    }
}