<?php

namespace Tests\Integration;

use Illuminate\Database\Capsule\Manager;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\QueryException;
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;

// Run against a disposable MySQL server via AGENT_MIGRATION_TEST_SOCKET.
// Each test creates its own database; the project .env is never loaded.
class AgentProfitAssignmentMigrationTest extends TestCase
{
    private PDO $admin;
    private string $database;
    private Migration $migration;

    protected function setUp(): void
    {
        $socket = getenv('AGENT_MIGRATION_TEST_SOCKET');
        if (!$socket) $this->markTestSkipped('Set AGENT_MIGRATION_TEST_SOCKET to a disposable MySQL server.');

        $this->admin = new PDO('mysql:unix_socket=' . $socket, 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        $database = 'profit_migration_test_' . bin2hex(random_bytes(8));
        $this->admin->exec('CREATE DATABASE `' . $database . '`');
        $this->database = $database;

        Facade::clearResolvedInstances();
        $app = new Application(dirname(__DIR__, 2));
        Facade::setFacadeApplication($app);
        $db = new Manager($app);
        $db->addConnection([
            'driver' => 'mysql', 'unix_socket' => $socket, 'database' => $database,
            'username' => 'root', 'password' => '', 'prefix' => 'bot_', 'prefix_indexes' => true,
            'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
        ]);
        $app->instance('db', $db->getDatabaseManager());
        $app->bind('db.schema', fn () => $db->getConnection()->getSchemaBuilder());
        $this->migration = require dirname(__DIR__, 2)
            . '/database/migrations/2026_08_28_120000_create_agent_profit_profiles_and_rebate_records.php';
    }

    protected function tearDown(): void
    {
        if (isset($this->database)) {
            DB::disconnect();
            $this->admin->exec('DROP DATABASE `' . $this->database . '`');
            Facade::clearResolvedInstances();
        }
        parent::tearDown();
    }

    public function test_fresh_migration_can_be_retried_and_rolled_back(): void
    {
        $this->migration->up();
        $indexes = $this->assertAssignmentIndexes();
        $this->migration->up();
        $this->assertSame($indexes, $this->assertAssignmentIndexes());
        $this->assertSame(1, DB::table('agent_profit_commission_profiles')->count());

        $this->migration->down();
        foreach (['agent_profit_commission_assignments', 'agent_profit_commission_profiles',
            'agent_daily_game_stats', 'agent_rebate_records'] as $table) {
            $this->assertFalse(Schema::hasTable($table));
        }
    }

    public function test_original_1059_failure_can_be_resumed_without_losing_rows(): void
    {
        try {
            // Reproduce the original fluent indexes, including the overlong name.
            Schema::create('agent_profit_commission_assignments', function (Blueprint $table) {
                $table->id();
                $table->unsignedBigInteger('agent_id')->index();
                $table->unsignedBigInteger('profit_commission_profile_id')->nullable()->index();
                $table->date('effective_from')->index();
                $table->unique(['agent_id', 'effective_from'], 'uniq_agent_profit_assignment_date');
            });
            $this->fail('Expected MySQL error 1059 from the original index name.');
        } catch (QueryException $exception) {
            $this->assertSame(1059, $exception->errorInfo[1]);
        }
        $this->assertTrue(Schema::hasTable('agent_profit_commission_assignments'));
        DB::table('agent_profit_commission_assignments')->insert(['agent_id' => 7, 'effective_from' => '2026-09-15']);
        $before = (array) DB::table('agent_profit_commission_assignments')->first();

        $this->migration->up();
        $this->assertAssignmentIndexes();
        $this->migration->up();
        $this->assertAssignmentIndexes();
        $this->assertSame($before, (array) DB::table('agent_profit_commission_assignments')->first());
    }

    public function test_equivalent_indexes_are_reused_and_unique_constraint_is_restored(): void
    {
        Schema::create('agent_profit_commission_assignments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('agent_id')->index('legacy_agent');
            $table->unsignedBigInteger('profit_commission_profile_id')->nullable()->index('legacy_profile');
            $table->date('effective_from')->index('legacy_effective');
            $table->index(['agent_id', 'effective_from'], 'legacy_lookup');
        });
        $this->migration->up();
        $this->migration->up();
        $indexes = $this->assertAssignmentIndexes(6);
        foreach (['legacy_agent', 'legacy_profile', 'legacy_effective', 'legacy_lookup'] as $index) {
            $this->assertArrayHasKey($index, $indexes);
        }

        $row = ['agent_id' => 7, 'effective_from' => '2026-09-15'];
        DB::table('agent_profit_commission_assignments')->insert($row);
        try {
            DB::table('agent_profit_commission_assignments')->insert($row);
            $this->fail('Expected the agent/date unique constraint to reject a duplicate.');
        } catch (QueryException $exception) {
            $this->assertSame(1062, $exception->errorInfo[1]);
        }
    }

    private function assertAssignmentIndexes(int $count = 5): array
    {
        $indexes = [];
        foreach (DB::select('SHOW INDEX FROM bot_agent_profit_commission_assignments') as $row) {
            $this->assertLessThanOrEqual(64, strlen($row->Key_name));
            $indexes[$row->Key_name]['columns'][] = $row->Column_name;
            $indexes[$row->Key_name]['unique'] = (int) $row->Non_unique === 0;
        }
        $this->assertCount($count, $indexes);
        foreach ([['agent_id'], ['profit_commission_profile_id'], ['effective_from']] as $columns) {
            $this->assertContains(['columns' => $columns, 'unique' => false], $indexes);
        }
        $this->assertContains(['columns' => ['agent_id', 'effective_from'], 'unique' => true], $indexes);
        ksort($indexes);
        return $indexes;
    }
}