namespace NeonSprawl.Server.Game.Gigs;

/// <summary>PostgreSQL-backed gig XP totals keyed by normalized player id (NEO-44).</summary>
public sealed class PostgresPlayerGigProgressionStore(Npgsql.NpgsqlDataSource dataSource) : IPlayerGigProgressionStore
{
    /// <inheritdoc />
    public IReadOnlyDictionary<string, int> GetXpTotals(string playerId)
    {
        var norm = NormalizePlayerId(playerId);
        if (norm.Length == 0)
        {
            return new Dictionary<string, int>(StringComparer.Ordinal);
        }

        PostgresGigProgressionBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        using var cmd = new Npgsql.NpgsqlCommand(
            """
            SELECT gig_id, xp
            FROM player_gig_progression
            WHERE player_id = @pid;
            """,
            conn);
        cmd.Parameters.AddWithValue("pid", norm);
        var map = new Dictionary<string, int>(StringComparer.Ordinal);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            map[reader.GetString(0)] = reader.GetInt32(1);
        }

        return map;
    }

    /// <inheritdoc />
    public bool TryApplyXpDelta(string playerId, string gigId, int amount, out int previousXp, out int newXp)
    {
        previousXp = 0;
        newXp = 0;
        var norm = NormalizePlayerId(playerId);
        var gid = gigId.Trim();
        if (norm.Length == 0 || gid.Length == 0)
        {
            return false;
        }

        PostgresGigProgressionBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        using var tx = conn.BeginTransaction();
        if (!PlayerExists(conn, norm, tx))
        {
            tx.Rollback();
            return false;
        }

        using (var sel = new Npgsql.NpgsqlCommand(
                   """
                   SELECT xp
                   FROM player_gig_progression
                   WHERE player_id = @pid AND gig_id = @gid
                   LIMIT 1
                   FOR UPDATE;
                   """,
                   conn,
                   tx))
        {
            sel.Parameters.AddWithValue("pid", norm);
            sel.Parameters.AddWithValue("gid", gid);
            var scalar = sel.ExecuteScalar();
            previousXp = scalar is null || Equals(scalar, DBNull.Value)
                ? 0
                : scalar is int xi
                    ? xi
                    : Convert.ToInt32(scalar, System.Globalization.CultureInfo.InvariantCulture);
        }

        newXp = previousXp + amount;
        if (newXp < 0)
        {
            tx.Rollback();
            return false;
        }

        using var upsert = new Npgsql.NpgsqlCommand(
            """
            INSERT INTO player_gig_progression (player_id, gig_id, xp, updated_at)
            VALUES (@pid, @gid, @xp, now())
            ON CONFLICT (player_id, gig_id)
            DO UPDATE SET xp = EXCLUDED.xp, updated_at = now();
            """,
            conn,
            tx);
        upsert.Parameters.AddWithValue("pid", norm);
        upsert.Parameters.AddWithValue("gid", gid);
        upsert.Parameters.AddWithValue("xp", newXp);
        upsert.ExecuteNonQuery();
        tx.Commit();
        return true;
    }

    private static bool PlayerExists(Npgsql.NpgsqlConnection conn, string playerIdNormalized, Npgsql.NpgsqlTransaction tx)
    {
        using var cmd = new Npgsql.NpgsqlCommand(
            "SELECT 1 FROM player_position WHERE player_id = @pid LIMIT 1;",
            conn,
            tx);
        cmd.Parameters.AddWithValue("pid", playerIdNormalized);
        return cmd.ExecuteScalar() is not null;
    }

    private static string NormalizePlayerId(string? playerId)
    {
        var t = playerId?.Trim();
        if (string.IsNullOrEmpty(t))
        {
            return string.Empty;
        }

        return t.ToLowerInvariant();
    }
}
