chesscubing/ChessCubing.Server/Stats/MySqlPlayerStatsStore.cs

using ChessCubing.Server.Data;
using ChessCubing.Server.Users;
using Microsoft.Extensions.Options;
using MySqlConnector;

namespace ChessCubing.Server.Stats;

public sealed class MySqlPlayerStatsStore(
    IOptions<SiteDataOptions> options,
    ILogger<MySqlPlayerStatsStore> logger)
{
    private const string MatchResultWhite = "white";
    private const string MatchResultBlack = "black";
    private const string MatchResultStopped = "stopped";
    private const int DefaultElo = 1200;
    private const int EloKFactor = 32;
    private const int RecentMatchLimit = 12;

    private const string CreatePlayerStatsTableSql = """
        CREATE TABLE IF NOT EXISTS site_player_stats (
            subject VARCHAR(190) NOT NULL,
            current_elo INT NOT NULL,
            ranked_games INT NOT NULL,
            casual_games INT NOT NULL,
            wins INT NOT NULL,
            losses INT NOT NULL,
            stopped_games INT NOT NULL,
            white_wins INT NOT NULL,
            black_wins INT NOT NULL,
            white_losses INT NOT NULL,
            black_losses INT NOT NULL,
            total_moves INT NOT NULL,
            total_cube_rounds INT NOT NULL,
            total_cube_entries INT NOT NULL,
            total_cube_time_ms BIGINT NOT NULL,
            best_cube_time_ms BIGINT NULL,
            last_match_utc DATETIME(6) NULL,
            updated_utc DATETIME(6) NOT NULL,
            PRIMARY KEY (subject)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        """;

    private const string CreateMatchResultsTableSql = """
        CREATE TABLE IF NOT EXISTS site_match_results (
            id BIGINT NOT NULL AUTO_INCREMENT,
            match_id VARCHAR(80) NOT NULL,
            collaboration_session_id VARCHAR(80) NULL,
            recorded_by_subject VARCHAR(190) NOT NULL,
            white_subject VARCHAR(190) NULL,
            white_name VARCHAR(120) NOT NULL,
            black_subject VARCHAR(190) NULL,
            black_name VARCHAR(120) NOT NULL,
            winner_subject VARCHAR(190) NULL,
            result VARCHAR(20) NOT NULL,
            mode VARCHAR(40) NOT NULL,
            preset VARCHAR(40) NOT NULL,
            match_label VARCHAR(120) NULL,
            block_number INT NOT NULL,
            white_moves INT NOT NULL,
            black_moves INT NOT NULL,
            cube_rounds INT NOT NULL,
            white_best_cube_ms BIGINT NULL,
            black_best_cube_ms BIGINT NULL,
            white_average_cube_ms BIGINT NULL,
            black_average_cube_ms BIGINT NULL,
            is_ranked TINYINT(1) NOT NULL,
            white_elo_before INT NULL,
            white_elo_after INT NULL,
            black_elo_before INT NULL,
            black_elo_after INT NULL,
            completed_utc DATETIME(6) NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uq_site_match_results_match_id (match_id),
            KEY idx_site_match_results_white_subject (white_subject, completed_utc),
            KEY idx_site_match_results_black_subject (black_subject, completed_utc)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        """;

    private const string EnsurePlayerStatsRowSql = """
        INSERT INTO site_player_stats (
            subject,
            current_elo,
            ranked_games,
            casual_games,
            wins,
            losses,
            stopped_games,
            white_wins,
            black_wins,
            white_losses,
            black_losses,
            total_moves,
            total_cube_rounds,
            total_cube_entries,
            total_cube_time_ms,
            best_cube_time_ms,
            last_match_utc,
            updated_utc
        )
        VALUES (
            @subject,
            @currentElo,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            NULL,
            NULL,
            @updatedUtc
        )
        ON DUPLICATE KEY UPDATE
            subject = VALUES(subject);
        """;

    private const string SelectPlayerStatsForUpdateSql = """
        SELECT
            subject,
            current_elo,
            ranked_games,
            casual_games,
            wins,
            losses,
            stopped_games,
            white_wins,
            black_wins,
            white_losses,
            black_losses,
            total_moves,
            total_cube_rounds,
            total_cube_entries,
            total_cube_time_ms,
            best_cube_time_ms,
            last_match_utc,
            updated_utc
        FROM site_player_stats
        WHERE subject = @subject
        LIMIT 1
        FOR UPDATE;
        """;

    private const string SelectPlayerStatsSql = """
        SELECT
            subject,
            current_elo,
            ranked_games,
            casual_games,
            wins,
            losses,
            stopped_games,
            white_wins,
            black_wins,
            white_losses,
            black_losses,
            total_moves,
            total_cube_rounds,
            total_cube_entries,
            total_cube_time_ms,
            best_cube_time_ms,
            last_match_utc,
            updated_utc
        FROM site_player_stats
        WHERE subject = @subject
        LIMIT 1;
        """;

    private const string UpdatePlayerStatsSql = """
        UPDATE site_player_stats
        SET
            current_elo = @currentElo,
            ranked_games = @rankedGames,
            casual_games = @casualGames,
            wins = @wins,
            losses = @losses,
            stopped_games = @stoppedGames,
            white_wins = @whiteWins,
            black_wins = @blackWins,
            white_losses = @whiteLosses,
            black_losses = @blackLosses,
            total_moves = @totalMoves,
            total_cube_rounds = @totalCubeRounds,
            total_cube_entries = @totalCubeEntries,
            total_cube_time_ms = @totalCubeTimeMs,
            best_cube_time_ms = @bestCubeTimeMs,
            last_match_utc = @lastMatchUtc,
            updated_utc = @updatedUtc
        WHERE subject = @subject;
        """;

    private const string InsertMatchResultSql = """
        INSERT INTO site_match_results (
            match_id,
            collaboration_session_id,
            recorded_by_subject,
            white_subject,
            white_name,
            black_subject,
            black_name,
            winner_subject,
            result,
            mode,
            preset,
            match_label,
            block_number,
            white_moves,
            black_moves,
            cube_rounds,
            white_best_cube_ms,
            black_best_cube_ms,
            white_average_cube_ms,
            black_average_cube_ms,
            is_ranked,
            white_elo_before,
            white_elo_after,
            black_elo_before,
            black_elo_after,
            completed_utc
        )
        VALUES (
            @matchId,
            @collaborationSessionId,
            @recordedBySubject,
            @whiteSubject,
            @whiteName,
            @blackSubject,
            @blackName,
            @winnerSubject,
            @result,
            @mode,
            @preset,
            @matchLabel,
            @blockNumber,
            @whiteMoves,
            @blackMoves,
            @cubeRounds,
            @whiteBestCubeMs,
            @blackBestCubeMs,
            @whiteAverageCubeMs,
            @blackAverageCubeMs,
            @isRanked,
            NULL,
            NULL,
            NULL,
            NULL,
            @completedUtc
        );
        """;

    private const string UpdateMatchResultEloSql = """
        UPDATE site_match_results
        SET
            white_elo_before = @whiteEloBefore,
            white_elo_after = @whiteEloAfter,
            black_elo_before = @blackEloBefore,
            black_elo_after = @blackEloAfter
        WHERE match_id = @matchId;
        """;

    private const string SelectRecentMatchesSql = """
        SELECT
            match_id,
            white_subject,
            white_name,
            black_subject,
            black_name,
            result,
            mode,
            preset,
            match_label,
            white_moves,
            black_moves,
            cube_rounds,
            white_best_cube_ms,
            black_best_cube_ms,
            white_average_cube_ms,
            black_average_cube_ms,
            is_ranked,
            white_elo_before,
            white_elo_after,
            black_elo_before,
            black_elo_after,
            completed_utc
        FROM site_match_results
        WHERE white_subject = @subject OR black_subject = @subject
        ORDER BY completed_utc DESC, id DESC
        LIMIT @limit;
        """;

    private readonly SiteDataOptions _options = options.Value;
    private readonly ILogger<MySqlPlayerStatsStore> _logger = logger;

    public async Task InitializeAsync(CancellationToken cancellationToken)
    {
        for (var attempt = 1; attempt <= _options.InitializationRetries; attempt++)
        {
            try
            {
                await using var connection = new MySqlConnection(_options.BuildConnectionString());
                await connection.OpenAsync(cancellationToken);
                await CreateSchemaAsync(connection, cancellationToken);
                return;
            }
            catch (Exception exception) when (attempt < _options.InitializationRetries)
            {
                _logger.LogWarning(
                    exception,
                    "Initialisation MySQL impossible pour les statistiques joueurs (tentative {Attempt}/{MaxAttempts}).",
                    attempt,
                    _options.InitializationRetries);

                await Task.Delay(TimeSpan.FromSeconds(_options.InitializationDelaySeconds), cancellationToken);
            }
        }

        await using var finalConnection = new MySqlConnection(_options.BuildConnectionString());
        await finalConnection.OpenAsync(cancellationToken);
        await CreateSchemaAsync(finalConnection, cancellationToken);
    }

    public async Task<ReportCompletedMatchResponse> RecordCompletedMatchAsync(
        AuthenticatedSiteUser reporter,
        ReportCompletedMatchRequest request,
        CancellationToken cancellationToken)
    {
        var normalized = NormalizeRequest(reporter, request);

        await using var connection = new MySqlConnection(_options.BuildConnectionString());
        await connection.OpenAsync(cancellationToken);
        await using var transaction = await connection.BeginTransactionAsync(cancellationToken);

        try
        {
            if (!await TryInsertMatchReservationAsync(connection, transaction, normalized, cancellationToken))
            {
                await transaction.RollbackAsync(cancellationToken);
                return new ReportCompletedMatchResponse
                {
                    Recorded = true,
                    IsDuplicate = true,
                    IsRanked = normalized.IsRanked,
                };
            }

            PlayerStatsRow? whiteStats = null;
            PlayerStatsRow? blackStats = null;
            EloSnapshot? elo = null;

            if (normalized.WhiteSubject is not null)
            {
                await EnsurePlayerStatsRowAsync(connection, transaction, normalized.WhiteSubject, normalized.CompletedUtc, cancellationToken);
                whiteStats = await ReadPlayerStatsForUpdateAsync(connection, transaction, normalized.WhiteSubject, cancellationToken);
            }

            if (normalized.BlackSubject is not null)
            {
                await EnsurePlayerStatsRowAsync(connection, transaction, normalized.BlackSubject, normalized.CompletedUtc, cancellationToken);
                blackStats = await ReadPlayerStatsForUpdateAsync(connection, transaction, normalized.BlackSubject, cancellationToken);
            }

            if (normalized.IsRanked && whiteStats is not null && blackStats is not null)
            {
                elo = ComputeElo(whiteStats.CurrentElo, blackStats.CurrentElo, normalized.Result);
                whiteStats = whiteStats with { CurrentElo = elo.WhiteAfter };
                blackStats = blackStats with { CurrentElo = elo.BlackAfter };
            }

            if (whiteStats is not null)
            {
                whiteStats = ApplyMatchToStats(
                    whiteStats,
                    normalized,
                    MatchResultWhite,
                    normalized.WhiteMoves,
                    normalized.WhiteCubeTimes,
                    normalized.IsRanked,
                    elo?.WhiteAfter);

                await UpdatePlayerStatsAsync(connection, transaction, whiteStats, cancellationToken);
            }

            if (blackStats is not null)
            {
                blackStats = ApplyMatchToStats(
                    blackStats,
                    normalized,
                    MatchResultBlack,
                    normalized.BlackMoves,
                    normalized.BlackCubeTimes,
                    normalized.IsRanked,
                    elo?.BlackAfter);

                await UpdatePlayerStatsAsync(connection, transaction, blackStats, cancellationToken);
            }

            await UpdateMatchEloAsync(connection, transaction, normalized.MatchId, elo, cancellationToken);
            await transaction.CommitAsync(cancellationToken);

            return new ReportCompletedMatchResponse
            {
                Recorded = true,
                IsDuplicate = false,
                IsRanked = normalized.IsRanked,
                WhiteEloAfter = elo?.WhiteAfter,
                BlackEloAfter = elo?.BlackAfter,
            };
        }
        catch
        {
            await transaction.RollbackAsync(cancellationToken);
            throw;
        }
    }

    public async Task<UserStatsResponse> GetUserStatsAsync(string subject, CancellationToken cancellationToken)
    {
        var normalizedSubject = NormalizeRequiredValue(subject, "subject", 190);

        await using var connection = new MySqlConnection(_options.BuildConnectionString());
        await connection.OpenAsync(cancellationToken);

        var stats = await ReadPlayerStatsAsync(connection, normalizedSubject, cancellationToken);
        var recentMatches = await ReadRecentMatchesAsync(connection, normalizedSubject, cancellationToken);

        if (stats is null)
        {
            return new UserStatsResponse
            {
                Subject = normalizedSubject,
                CurrentElo = DefaultElo,
                RecentMatches = recentMatches,
            };
        }

        return new UserStatsResponse
        {
            Subject = stats.Subject,
            CurrentElo = stats.CurrentElo,
            RankedGames = stats.RankedGames,
            CasualGames = stats.CasualGames,
            Wins = stats.Wins,
            Losses = stats.Losses,
            StoppedGames = stats.StoppedGames,
            WhiteWins = stats.WhiteWins,
            BlackWins = stats.BlackWins,
            WhiteLosses = stats.WhiteLosses,
            BlackLosses = stats.BlackLosses,
            TotalMoves = stats.TotalMoves,
            TotalCubeRounds = stats.TotalCubeRounds,
            BestCubeTimeMs = stats.BestCubeTimeMs,
            AverageCubeTimeMs = stats.TotalCubeEntries <= 0
                ? null
                : (long?)Math.Round((double)stats.TotalCubeTimeMs / stats.TotalCubeEntries, MidpointRounding.AwayFromZero),
            LastMatchUtc = stats.LastMatchUtc,
            RecentMatches = recentMatches,
        };
    }

    private static async Task CreateSchemaAsync(MySqlConnection connection, CancellationToken cancellationToken)
    {
        await using (var command = connection.CreateCommand())
        {
            command.CommandText = CreatePlayerStatsTableSql;
            await command.ExecuteNonQueryAsync(cancellationToken);
        }

        await using (var command = connection.CreateCommand())
        {
            command.CommandText = CreateMatchResultsTableSql;
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }

    private static async Task EnsurePlayerStatsRowAsync(
        MySqlConnection connection,
        MySqlTransaction transaction,
        string subject,
        DateTime nowUtc,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = EnsurePlayerStatsRowSql;
        command.Parameters.AddWithValue("@subject", subject);
        command.Parameters.AddWithValue("@currentElo", DefaultElo);
        command.Parameters.AddWithValue("@updatedUtc", nowUtc);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private static async Task<bool> TryInsertMatchReservationAsync(
        MySqlConnection connection,
        MySqlTransaction transaction,
        NormalizedCompletedMatch normalized,
        CancellationToken cancellationToken)
    {
        try
        {
            await using var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = InsertMatchResultSql;
            command.Parameters.AddWithValue("@matchId", normalized.MatchId);
            command.Parameters.AddWithValue("@collaborationSessionId", (object?)normalized.CollaborationSessionId ?? DBNull.Value);
            command.Parameters.AddWithValue("@recordedBySubject", normalized.RecordedBySubject);
            command.Parameters.AddWithValue("@whiteSubject", (object?)normalized.WhiteSubject ?? DBNull.Value);
            command.Parameters.AddWithValue("@whiteName", normalized.WhiteName);
            command.Parameters.AddWithValue("@blackSubject", (object?)normalized.BlackSubject ?? DBNull.Value);
            command.Parameters.AddWithValue("@blackName", normalized.BlackName);
            command.Parameters.AddWithValue("@winnerSubject", (object?)normalized.WinnerSubject ?? DBNull.Value);
            command.Parameters.AddWithValue("@result", normalized.Result);
            command.Parameters.AddWithValue("@mode", normalized.Mode);
            command.Parameters.AddWithValue("@preset", normalized.Preset);
            command.Parameters.AddWithValue("@matchLabel", (object?)normalized.MatchLabel ?? DBNull.Value);
            command.Parameters.AddWithValue("@blockNumber", normalized.BlockNumber);
            command.Parameters.AddWithValue("@whiteMoves", normalized.WhiteMoves);
            command.Parameters.AddWithValue("@blackMoves", normalized.BlackMoves);
            command.Parameters.AddWithValue("@cubeRounds", normalized.CubeRounds.Length);
            command.Parameters.AddWithValue("@whiteBestCubeMs", (object?)normalized.WhiteCubeTimes.BestMs ?? DBNull.Value);
            command.Parameters.AddWithValue("@blackBestCubeMs", (object?)normalized.BlackCubeTimes.BestMs ?? DBNull.Value);
            command.Parameters.AddWithValue("@whiteAverageCubeMs", (object?)normalized.WhiteCubeTimes.AverageMs ?? DBNull.Value);
            command.Parameters.AddWithValue("@blackAverageCubeMs", (object?)normalized.BlackCubeTimes.AverageMs ?? DBNull.Value);
            command.Parameters.AddWithValue("@isRanked", normalized.IsRanked);
            command.Parameters.AddWithValue("@completedUtc", normalized.CompletedUtc);
            await command.ExecuteNonQueryAsync(cancellationToken);
            return true;
        }
        catch (MySqlException exception) when (exception.Number == 1062)
        {
            return false;
        }
    }

    private static async Task<PlayerStatsRow?> ReadPlayerStatsAsync(
        MySqlConnection connection,
        string subject,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.CommandText = SelectPlayerStatsSql;
        command.Parameters.AddWithValue("@subject", subject);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        return await reader.ReadAsync(cancellationToken)
            ? MapPlayerStats(reader)
            : null;
    }

    private static async Task<PlayerStatsRow> ReadPlayerStatsForUpdateAsync(
        MySqlConnection connection,
        MySqlTransaction transaction,
        string subject,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = SelectPlayerStatsForUpdateSql;
        command.Parameters.AddWithValue("@subject", subject);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        if (!await reader.ReadAsync(cancellationToken))
        {
            throw new InvalidOperationException("La ligne de statistiques joueur est introuvable.");
        }

        return MapPlayerStats(reader);
    }

    private static async Task UpdatePlayerStatsAsync(
        MySqlConnection connection,
        MySqlTransaction transaction,
        PlayerStatsRow stats,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = UpdatePlayerStatsSql;
        command.Parameters.AddWithValue("@subject", stats.Subject);
        command.Parameters.AddWithValue("@currentElo", stats.CurrentElo);
        command.Parameters.AddWithValue("@rankedGames", stats.RankedGames);
        command.Parameters.AddWithValue("@casualGames", stats.CasualGames);
        command.Parameters.AddWithValue("@wins", stats.Wins);
        command.Parameters.AddWithValue("@losses", stats.Losses);
        command.Parameters.AddWithValue("@stoppedGames", stats.StoppedGames);
        command.Parameters.AddWithValue("@whiteWins", stats.WhiteWins);
        command.Parameters.AddWithValue("@blackWins", stats.BlackWins);
        command.Parameters.AddWithValue("@whiteLosses", stats.WhiteLosses);
        command.Parameters.AddWithValue("@blackLosses", stats.BlackLosses);
        command.Parameters.AddWithValue("@totalMoves", stats.TotalMoves);
        command.Parameters.AddWithValue("@totalCubeRounds", stats.TotalCubeRounds);
        command.Parameters.AddWithValue("@totalCubeEntries", stats.TotalCubeEntries);
        command.Parameters.AddWithValue("@totalCubeTimeMs", stats.TotalCubeTimeMs);
        command.Parameters.AddWithValue("@bestCubeTimeMs", (object?)stats.BestCubeTimeMs ?? DBNull.Value);
        command.Parameters.AddWithValue("@lastMatchUtc", (object?)stats.LastMatchUtc ?? DBNull.Value);
        command.Parameters.AddWithValue("@updatedUtc", stats.UpdatedUtc);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private static async Task UpdateMatchEloAsync(
        MySqlConnection connection,
        MySqlTransaction transaction,
        string matchId,
        EloSnapshot? elo,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = UpdateMatchResultEloSql;
        command.Parameters.AddWithValue("@matchId", matchId);
        command.Parameters.AddWithValue("@whiteEloBefore", (object?)elo?.WhiteBefore ?? DBNull.Value);
        command.Parameters.AddWithValue("@whiteEloAfter", (object?)elo?.WhiteAfter ?? DBNull.Value);
        command.Parameters.AddWithValue("@blackEloBefore", (object?)elo?.BlackBefore ?? DBNull.Value);
        command.Parameters.AddWithValue("@blackEloAfter", (object?)elo?.BlackAfter ?? DBNull.Value);
        await command.ExecuteNonQueryAsync(cancellationToken);
    }

    private static async Task<UserRecentMatchResponse[]> ReadRecentMatchesAsync(
        MySqlConnection connection,
        string subject,
        CancellationToken cancellationToken)
    {
        await using var command = connection.CreateCommand();
        command.CommandText = SelectRecentMatchesSql;
        command.Parameters.AddWithValue("@subject", subject);
        command.Parameters.AddWithValue("@limit", RecentMatchLimit);

        var matches = new List<UserRecentMatchResponse>();
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            matches.Add(MapRecentMatch(reader, subject));
        }

        return matches.ToArray();
    }

    private static PlayerStatsRow ApplyMatchToStats(
        PlayerStatsRow current,
        NormalizedCompletedMatch normalized,
        string playerColor,
        int playerMoves,
        CubeTimeSummary cubeTimes,
        bool isRanked,
        int? eloAfter)
    {
        var isWhite = playerColor == MatchResultWhite;
        var isWin = normalized.Result == playerColor;
        var isLoss = normalized.Result is MatchResultWhite or MatchResultBlack && normalized.Result != playerColor;
        var isStopped = normalized.Result == MatchResultStopped;

        return current with
        {
            RankedGames = current.RankedGames + (isRanked ? 1 : 0),
            CasualGames = current.CasualGames + (isRanked ? 0 : 1),
            Wins = current.Wins + (isWin ? 1 : 0),
            Losses = current.Losses + (isLoss ? 1 : 0),
            StoppedGames = current.StoppedGames + (isStopped ? 1 : 0),
            WhiteWins = current.WhiteWins + (isWhite && isWin ? 1 : 0),
            BlackWins = current.BlackWins + (!isWhite && isWin ? 1 : 0),
            WhiteLosses = current.WhiteLosses + (isWhite && isLoss ? 1 : 0),
            BlackLosses = current.BlackLosses + (!isWhite && isLoss ? 1 : 0),
            TotalMoves = current.TotalMoves + playerMoves,
            TotalCubeRounds = current.TotalCubeRounds + normalized.CubeRounds.Length,
            TotalCubeEntries = current.TotalCubeEntries + cubeTimes.Count,
            TotalCubeTimeMs = current.TotalCubeTimeMs + cubeTimes.TotalMs,
            BestCubeTimeMs = MinNullable(current.BestCubeTimeMs, cubeTimes.BestMs),
            LastMatchUtc = normalized.CompletedUtc,
            UpdatedUtc = normalized.CompletedUtc,
            CurrentElo = eloAfter ?? current.CurrentElo,
        };
    }

    private static EloSnapshot ComputeElo(int whiteRating, int blackRating, string result)
    {
        var whiteScore = result == MatchResultWhite ? 1d : 0d;
        var expectedWhite = 1d / (1d + Math.Pow(10d, (blackRating - whiteRating) / 400d));
        var whiteDelta = (int)Math.Round(EloKFactor * (whiteScore - expectedWhite), MidpointRounding.AwayFromZero);

        return new EloSnapshot(
            whiteRating,
            whiteRating + whiteDelta,
            blackRating,
            blackRating - whiteDelta);
    }

    private static UserRecentMatchResponse MapRecentMatch(MySqlDataReader reader, string subject)
    {
        var whiteSubject = ReadNullableString(reader, "white_subject");
        var blackSubject = ReadNullableString(reader, "black_subject");
        var playerColor = string.Equals(whiteSubject, subject, StringComparison.Ordinal) ? MatchResultWhite : MatchResultBlack;
        var isWhite = playerColor == MatchResultWhite;
        var result = ReadString(reader, "result");
        var isWin = result == playerColor;
        var isLoss = result is MatchResultWhite or MatchResultBlack && result != playerColor;
        var eloBefore = isWhite ? ReadNullableInt(reader, "white_elo_before") : ReadNullableInt(reader, "black_elo_before");
        var eloAfter = isWhite ? ReadNullableInt(reader, "white_elo_after") : ReadNullableInt(reader, "black_elo_after");

        return new UserRecentMatchResponse
        {
            MatchId = ReadString(reader, "match_id"),
            CompletedUtc = ReadDateTime(reader, "completed_utc"),
            Result = result,
            Mode = ReadString(reader, "mode"),
            Preset = ReadString(reader, "preset"),
            MatchLabel = ReadNullableString(reader, "match_label"),
            PlayerColor = playerColor,
            PlayerName = isWhite ? ReadString(reader, "white_name") : ReadString(reader, "black_name"),
            OpponentName = isWhite ? ReadString(reader, "black_name") : ReadString(reader, "white_name"),
            OpponentSubject = isWhite ? blackSubject : whiteSubject,
            IsRanked = ReadBoolean(reader, "is_ranked"),
            IsWin = isWin,
            IsLoss = isLoss,
            PlayerMoves = isWhite ? ReadInt(reader, "white_moves") : ReadInt(reader, "black_moves"),
            OpponentMoves = isWhite ? ReadInt(reader, "black_moves") : ReadInt(reader, "white_moves"),
            CubeRounds = ReadInt(reader, "cube_rounds"),
            PlayerBestCubeTimeMs = isWhite ? ReadNullableLong(reader, "white_best_cube_ms") : ReadNullableLong(reader, "black_best_cube_ms"),
            PlayerAverageCubeTimeMs = isWhite ? ReadNullableLong(reader, "white_average_cube_ms") : ReadNullableLong(reader, "black_average_cube_ms"),
            EloBefore = eloBefore,
            EloAfter = eloAfter,
            EloDelta = eloBefore is not null && eloAfter is not null ? eloAfter.Value - eloBefore.Value : null,
        };
    }

    private static PlayerStatsRow MapPlayerStats(MySqlDataReader reader)
        => new(
            ReadString(reader, "subject"),
            ReadInt(reader, "current_elo"),
            ReadInt(reader, "ranked_games"),
            ReadInt(reader, "casual_games"),
            ReadInt(reader, "wins"),
            ReadInt(reader, "losses"),
            ReadInt(reader, "stopped_games"),
            ReadInt(reader, "white_wins"),
            ReadInt(reader, "black_wins"),
            ReadInt(reader, "white_losses"),
            ReadInt(reader, "black_losses"),
            ReadInt(reader, "total_moves"),
            ReadInt(reader, "total_cube_rounds"),
            ReadInt(reader, "total_cube_entries"),
            ReadLong(reader, "total_cube_time_ms"),
            ReadNullableLong(reader, "best_cube_time_ms"),
            ReadNullableDateTime(reader, "last_match_utc"),
            ReadDateTime(reader, "updated_utc"));

    private static NormalizedCompletedMatch NormalizeRequest(AuthenticatedSiteUser reporter, ReportCompletedMatchRequest request)
    {
        var matchId = NormalizeRequiredValue(request.MatchId, "identifiant de match", 80);
        var collaborationSessionId = NormalizeOptionalValue(request.CollaborationSessionId, "session collaborative", 80);
        var whiteSubject = NormalizeOptionalValue(request.WhiteSubject, "subject blanc", 190);
        var blackSubject = NormalizeOptionalValue(request.BlackSubject, "subject noir", 190);
        var whiteName = NormalizeRequiredValue(request.WhiteName, "joueur blanc", 120);
        var blackName = NormalizeRequiredValue(request.BlackName, "joueur noir", 120);
        var mode = NormalizeRequiredValue(request.Mode, "mode", 40);
        var preset = NormalizeRequiredValue(request.Preset, "preset", 40);
        var matchLabel = NormalizeOptionalValue(request.MatchLabel, "nom de rencontre", 120);
        var result = NormalizeResult(request.Result);
        var blockNumber = Math.Clamp(request.BlockNumber, 1, 999);
        var whiteMoves = Math.Clamp(request.WhiteMoves, 0, 9999);
        var blackMoves = Math.Clamp(request.BlackMoves, 0, 9999);

        if (whiteSubject is null && blackSubject is null)
        {
            throw new PlayerStatsValidationException("Impossible d'enregistrer une partie sans joueur identifie.");
        }

        if (whiteSubject is not null &&
            blackSubject is not null &&
            string.Equals(whiteSubject, blackSubject, StringComparison.Ordinal))
        {
            throw new PlayerStatsValidationException("Les deux cotes ne peuvent pas pointer vers le meme compte.");
        }

        if (!string.Equals(reporter.Subject, whiteSubject, StringComparison.Ordinal) &&
            !string.Equals(reporter.Subject, blackSubject, StringComparison.Ordinal))
        {
            throw new PlayerStatsValidationException("Le compte connecte doit correspondre a l'un des deux joueurs pour enregistrer la partie.");
        }

        var cubeRounds = (request.CubeRounds ?? [])
            .Take(64)
            .Select(round => new NormalizedCubeRound(
                Math.Clamp(round.BlockNumber, 1, 999),
                round.Number is null ? null : Math.Clamp(round.Number.Value, 1, 999),
                NormalizeCubeDuration(round.White),
                NormalizeCubeDuration(round.Black)))
            .ToArray();

        var whiteCubeTimes = SummarizeCubeTimes(cubeRounds.Select(round => round.White));
        var blackCubeTimes = SummarizeCubeTimes(cubeRounds.Select(round => round.Black));
        var isRanked = whiteSubject is not null &&
            blackSubject is not null &&
            result is MatchResultWhite or MatchResultBlack;

        return new NormalizedCompletedMatch(
            matchId,
            collaborationSessionId,
            reporter.Subject,
            whiteSubject,
            whiteName,
            blackSubject,
            blackName,
            result,
            mode,
            preset,
            matchLabel,
            blockNumber,
            whiteMoves,
            blackMoves,
            cubeRounds,
            whiteCubeTimes,
            blackCubeTimes,
            isRanked,
            result == MatchResultWhite
                ? whiteSubject
                : result == MatchResultBlack
                    ? blackSubject
                    : null,
            DateTime.UtcNow);
    }

    private static CubeTimeSummary SummarizeCubeTimes(IEnumerable<long?> values)
    {
        var normalized = values
            .Where(value => value is > 0)
            .Select(value => value!.Value)
            .ToArray();

        if (normalized.Length == 0)
        {
            return new CubeTimeSummary(0, 0, null, null);
        }

        return new CubeTimeSummary(
            normalized.Length,
            normalized.Sum(),
            normalized.Min(),
            (long)Math.Round(normalized.Average(), MidpointRounding.AwayFromZero));
    }

    private static long? NormalizeCubeDuration(long? value)
        => value is > 0
            ? Math.Clamp(value.Value, 1, 3_600_000)
            : null;

    private static string NormalizeResult(string? value)
    {
        var normalized = NormalizeRequiredValue(value, "resultat", 20).ToLowerInvariant();
        return normalized switch
        {
            MatchResultWhite => MatchResultWhite,
            MatchResultBlack => MatchResultBlack,
            MatchResultStopped => MatchResultStopped,
            _ => throw new PlayerStatsValidationException("Le resultat doit etre white, black ou stopped."),
        };
    }

    private static string NormalizeRequiredValue(string? value, string fieldName, int maxLength)
        => NormalizeOptionalValue(value, fieldName, maxLength)
           ?? throw new PlayerStatsValidationException($"Le champ {fieldName} est obligatoire.");

    private static string? NormalizeOptionalValue(string? value, string fieldName, int maxLength)
    {
        var trimmed = value?.Trim();
        if (string.IsNullOrWhiteSpace(trimmed))
        {
            return null;
        }

        if (trimmed.Length > maxLength)
        {
            throw new PlayerStatsValidationException($"Le champ {fieldName} depasse {maxLength} caracteres.");
        }

        return trimmed;
    }

    private static long? MinNullable(long? current, long? candidate)
    {
        if (candidate is null)
        {
            return current;
        }

        return current is null
            ? candidate
            : Math.Min(current.Value, candidate.Value);
    }

    private static string ReadString(MySqlDataReader reader, string column)
        => reader.GetString(reader.GetOrdinal(column));

    private static string? ReadNullableString(MySqlDataReader reader, string column)
    {
        var ordinal = reader.GetOrdinal(column);
        return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
    }

    private static int ReadInt(MySqlDataReader reader, string column)
        => reader.GetInt32(reader.GetOrdinal(column));

    private static int? ReadNullableInt(MySqlDataReader reader, string column)
    {
        var ordinal = reader.GetOrdinal(column);
        return reader.IsDBNull(ordinal) ? null : reader.GetInt32(ordinal);
    }

    private static long ReadLong(MySqlDataReader reader, string column)
        => reader.GetInt64(reader.GetOrdinal(column));

    private static long? ReadNullableLong(MySqlDataReader reader, string column)
    {
        var ordinal = reader.GetOrdinal(column);
        return reader.IsDBNull(ordinal) ? null : reader.GetInt64(ordinal);
    }

    private static bool ReadBoolean(MySqlDataReader reader, string column)
        => reader.GetBoolean(reader.GetOrdinal(column));

    private static DateTime ReadDateTime(MySqlDataReader reader, string column)
        => reader.GetDateTime(reader.GetOrdinal(column));

    private static DateTime? ReadNullableDateTime(MySqlDataReader reader, string column)
    {
        var ordinal = reader.GetOrdinal(column);
        return reader.IsDBNull(ordinal) ? null : reader.GetDateTime(ordinal);
    }

    private sealed record PlayerStatsRow(
        string Subject,
        int CurrentElo,
        int RankedGames,
        int CasualGames,
        int Wins,
        int Losses,
        int StoppedGames,
        int WhiteWins,
        int BlackWins,
        int WhiteLosses,
        int BlackLosses,
        int TotalMoves,
        int TotalCubeRounds,
        int TotalCubeEntries,
        long TotalCubeTimeMs,
        long? BestCubeTimeMs,
        DateTime? LastMatchUtc,
        DateTime UpdatedUtc);

    private sealed record EloSnapshot(
        int WhiteBefore,
        int WhiteAfter,
        int BlackBefore,
        int BlackAfter);

    private sealed record NormalizedCubeRound(
        int BlockNumber,
        int? Number,
        long? White,
        long? Black);

    private sealed record CubeTimeSummary(
        int Count,
        long TotalMs,
        long? BestMs,
        long? AverageMs);

    private sealed record NormalizedCompletedMatch(
        string MatchId,
        string? CollaborationSessionId,
        string RecordedBySubject,
        string? WhiteSubject,
        string WhiteName,
        string? BlackSubject,
        string BlackName,
        string Result,
        string Mode,
        string Preset,
        string? MatchLabel,
        int BlockNumber,
        int WhiteMoves,
        int BlackMoves,
        NormalizedCubeRound[] CubeRounds,
        CubeTimeSummary WhiteCubeTimes,
        CubeTimeSummary BlackCubeTimes,
        bool IsRanked,
        string? WinnerSubject,
        DateTime CompletedUtc);
}