using System.Data.Common;
using Dapper;

namespace Outnumbered.Data;

// Shared Dapper implementation for both backends. SQLite (dev) and Postgres (prod) differ ONLY in how a connection is
// opened and in the schema DDL (INTEGER vs BIGINT, the SQLite-only drop-migration); every query and the row records are
// identical and live here once, so a new persisted column is a one-place change. Subclasses supply OpenConnectionAsync +
// EnsureSchemaAsync. Steam IDs are stored signed via unchecked((long)id), round-tripping identically on both. All methods
// run OFF the game thread. Permanent progression (players/upgrades) is GLOBAL; match_state is PER-SERVER (composite key)
// so many instances share one DB without clobbering each other's round.
public abstract class DapperPlayerRepository(string serverId) : IPlayerRepository
{
    protected readonly string ServerId = string.IsNullOrEmpty(serverId) ? "default" : serverId; // scopes the per-round match_state

    protected abstract Task<DbConnection> OpenConnectionAsync();
    public abstract Task EnsureSchemaAsync();

    public async Task<LoadedPlayer?> LoadAsync(ulong steamId)
    {
        await using var c = await OpenConnectionAsync();
        long sid = unchecked((long)steamId);
        var row = await c.QuerySingleOrDefaultAsync<PlayerRow>(
            """
            SELECT name, xp, level, prestige, points,
                   primary_weapon AS PrimaryWeapon, secondary_weapon AS SecondaryWeapon
            FROM players WHERE steamid = @sid
            """, new { sid });
        if (row is null) return null;

        var ups = await c.QueryAsync<UpgradeRow>(
            "SELECT stat_key, level FROM upgrades WHERE steamid = @sid", new { sid });

        return new LoadedPlayer(row.Name, row.Xp, (int)row.Level, (int)row.Prestige, (int)row.Points,
            ups.ToDictionary(u => u.Stat_Key, u => (int)u.Level), row.PrimaryWeapon, row.SecondaryWeapon);
    }

    public async Task<IReadOnlyList<TopPlayer>> GetTopAsync(int count)
    {
        await using var c = await OpenConnectionAsync();
        var rows = await c.QueryAsync<TopRow>(
            "SELECT name, level, prestige, xp FROM players ORDER BY prestige DESC, level DESC, xp DESC LIMIT @n",
            new { n = count });
        return rows.Select(r => new TopPlayer(r.Name, (int)r.Level, (int)r.Prestige, r.Xp)).ToList();
    }

    // Records: improve-only upserts. The bare INSERT relies on the players-table defaults for a first-ever row (the
    // name backfills on the player's next regular save); the CASE keeps the better value on conflict. Existing-row
    // references MUST be table-qualified: in DO UPDATE both the target row and `excluded` are in scope, and Postgres
    // rejects the unqualified form as ambiguous (42702). SQLite accepts the qualified form too, so the SQL stays shared.
    public async Task TryImproveBestWavesAsync(IReadOnlyList<ulong> steamIds, int wave)
    {
        if (steamIds.Count == 0) return;
        await using var c = await OpenConnectionAsync();
        await using var tx = await c.BeginTransactionAsync();
        foreach (var id in steamIds.Order()) // ascending steamid = the global row-lock order (see SaveManyAsync)
            await c.ExecuteAsync(
                """
                INSERT INTO players(steamid, best_wave) VALUES(@sid, @w)
                ON CONFLICT(steamid) DO UPDATE SET best_wave =
                    CASE WHEN players.best_wave IS NULL OR players.best_wave < excluded.best_wave
                         THEN excluded.best_wave ELSE players.best_wave END;
                """, new { sid = unchecked((long)id), w = wave }, tx);
        await tx.CommitAsync();
    }

    public async Task TryImproveGgBestAsync(ulong steamId, long elapsedMs)
    {
        await using var c = await OpenConnectionAsync();
        await c.ExecuteAsync(
            """
            INSERT INTO players(steamid, gg_best_ms) VALUES(@sid, @ms)
            ON CONFLICT(steamid) DO UPDATE SET gg_best_ms =
                CASE WHEN players.gg_best_ms IS NULL OR players.gg_best_ms > excluded.gg_best_ms
                     THEN excluded.gg_best_ms ELSE players.gg_best_ms END;
            """, new { sid = unchecked((long)steamId), ms = elapsedMs });
    }

    public async Task<IReadOnlyList<TopWave>> GetTopWavesAsync(int count)
    {
        await using var c = await OpenConnectionAsync();
        var rows = await c.QueryAsync<WaveRow>(
            "SELECT name, best_wave AS BestWave FROM players WHERE best_wave IS NOT NULL ORDER BY best_wave DESC, xp DESC LIMIT @n",
            new { n = count });
        return rows.Select(r => new TopWave(r.Name, (int)r.BestWave)).ToList();
    }

    public async Task<IReadOnlyList<TopGgTime>> GetTopGgAsync(int count)
    {
        await using var c = await OpenConnectionAsync();
        var rows = await c.QueryAsync<GgTimeRow>(
            "SELECT name, gg_best_ms AS BestMs FROM players WHERE gg_best_ms IS NOT NULL ORDER BY gg_best_ms ASC, xp DESC LIMIT @n",
            new { n = count });
        return rows.Select(r => new TopGgTime(r.Name, r.BestMs)).ToList();
    }

    public Task SaveAsync(PersistPlayer player) => SaveManyAsync(new[] { player });

    public async Task SaveManyAsync(IReadOnlyList<PersistPlayer> players)
    {
        if (players.Count == 0) return;
        await using var c = await OpenConnectionAsync();
        await using var tx = await c.BeginTransactionAsync();
        // Ascending steamid in EVERY multi-row transaction (here, the match twin, the records improves) = one global
        // row-lock order, so a wave-clear improve and the periodic flush can't deadlock each other on Postgres.
        foreach (var p in players.OrderBy(x => x.SteamId))
        {
            long sid = unchecked((long)p.SteamId);
            await c.ExecuteAsync(
                """
                INSERT INTO players(steamid, name, xp, level, prestige, points, primary_weapon, secondary_weapon, last_seen)
                VALUES(@sid, @name, @xp, @level, @prestige, @points, @primary, @secondary, CURRENT_TIMESTAMP)
                ON CONFLICT(steamid) DO UPDATE SET
                    xp = @xp, name = @name, level = @level, prestige = @prestige, points = @points,
                    primary_weapon = @primary, secondary_weapon = @secondary, last_seen = CURRENT_TIMESTAMP;
                """,
                new
                {
                    sid,
                    name = p.Name,
                    xp = p.Xp,
                    level = p.Level,
                    prestige = p.Prestige,
                    points = p.Points,
                    primary = p.PrimaryWeapon,
                    secondary = p.SecondaryWeapon
                }, tx);

            // Full-replace the upgrade set so a prestige reset's removals actually clear (a plain UPSERT leaves stale rows
            // that reload as if the reset never happened).
            await c.ExecuteAsync("DELETE FROM upgrades WHERE steamid = @sid;", new { sid }, tx);
            foreach (var kv in p.Upgrades)
                await c.ExecuteAsync("INSERT INTO upgrades(steamid, stat_key, level) VALUES(@sid, @k, @l);",
                    new { sid, k = kv.Key, l = kv.Value }, tx);
        }
        await tx.CommitAsync();
    }

    public async Task<MatchState?> LoadMatchAsync(ulong steamId)
    {
        await using var c = await OpenConnectionAsync();
        long sid = unchecked((long)steamId);
        var row = await c.QuerySingleOrDefaultAsync<MatchRow>(
            "SELECT kills, deaths, streak, headshot_kills AS HeadshotKills, gg_run_started_at AS GgRunStartedAtMs FROM match_state WHERE server_id = @srv AND steamid = @sid",
            new { srv = ServerId, sid });
        return row is null ? null
            : new MatchState(steamId, (int)row.Kills, (int)row.Deaths, (int)row.Streak, (int)row.HeadshotKills, row.GgRunStartedAtMs ?? 0);
    }

    public Task SaveMatchAsync(MatchState state) => SaveManyMatchAsync(new[] { state });

    public async Task SaveManyMatchAsync(IReadOnlyList<MatchState> states)
    {
        if (states.Count == 0) return;
        await using var c = await OpenConnectionAsync();
        await using var tx = await c.BeginTransactionAsync();
        foreach (var s in states.OrderBy(x => x.SteamId)) // global row-lock order (see SaveManyAsync)
        {
            long sid = unchecked((long)s.SteamId);
            await c.ExecuteAsync(
                """
                INSERT INTO match_state(server_id, steamid, kills, deaths, streak, headshot_kills, gg_run_started_at)
                VALUES(@srv, @sid, @kills, @deaths, @streak, @hs, @ggStart)
                ON CONFLICT(server_id, steamid) DO UPDATE SET
                    kills = @kills, deaths = @deaths, streak = @streak, headshot_kills = @hs, gg_run_started_at = @ggStart;
                """,
                new { srv = ServerId, sid, kills = s.Kills, deaths = s.Deaths, streak = s.Streak, hs = s.HeadshotKills, ggStart = s.GgRunStartedAtMs }, tx);
        }
        await tx.CommitAsync();
    }

    public async Task WipeMatchAsync()
    {
        await using var c = await OpenConnectionAsync();
        await c.ExecuteAsync("DELETE FROM match_state WHERE server_id = @srv;", new { srv = ServerId });
    }

    // Dapper maps columns by name (case-insensitive). Integer columns -> Int64, so these are `long` (cast to int at the
    // call site). The aliases + the Stat_Key param name map the snake_case columns (Postgres folds unquoted to lowercase).
    protected sealed record PlayerRow(string Name, long Xp, long Level, long Prestige, long Points,
        string? PrimaryWeapon, string? SecondaryWeapon);
    protected sealed record UpgradeRow(string Stat_Key, long Level);
    protected sealed record TopRow(string Name, long Level, long Prestige, long Xp);
    protected sealed record WaveRow(string Name, long BestWave);
    protected sealed record GgTimeRow(string Name, long BestMs);
    protected sealed record MatchRow(long Kills, long Deaths, long Streak, long HeadshotKills, long? GgRunStartedAtMs);
}