GroupVerify/Database.cs

using MySqlConnector;

namespace GroupVerify;

// Everything in CS2-SimpleAdmin's MySQL database: our gv_* tables, and its admin and group tables
// (read only). Every method opens its own connection and runs off the game thread.
internal sealed class Database(string connectionString)
{
    private const string MigrationPrefix = "GroupVerify.Migrations.";

    // What a reload hands back to the game thread.
    public sealed record Snapshot(
        Settings Settings,
        Dictionary<ulong, DateTime?> Overrides,
        HashSet<ulong> GroupRows,
        int Expired);

    private async Task<MySqlConnection> OpenAsync()
    {
        var connection = new MySqlConnection(connectionString);
        await connection.OpenAsync();
        return connection;
    }

    private static MySqlCommand Command(MySqlConnection connection, string sql, params (string Name, object? Value)[] parameters)
    {
        var command = new MySqlCommand(sql, connection);
        foreach (var (name, value) in parameters)
            command.Parameters.AddWithValue(name, value ?? DBNull.Value);
        return command;
    }

    // Applies the embedded Migrations/*.sql in name order, each once, the way SimpleAdmin's
    // Migration class does with sa_migrations. Returns the versions applied.
    public async Task<List<string>> MigrateAsync()
    {
        await using var connection = await OpenAsync();
        await using (var create = Command(connection, """
            CREATE TABLE IF NOT EXISTS `gv_migrations` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `version` varchar(128) NOT NULL,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
            """))
            await create.ExecuteNonQueryAsync();

        string last;
        await using (var query = Command(connection, "SELECT version FROM gv_migrations ORDER BY id DESC LIMIT 1"))
            last = (await query.ExecuteScalarAsync())?.ToString() ?? "";

        var assembly = typeof(Database).Assembly;
        var applied = new List<string>();
        foreach (var resource in assembly.GetManifestResourceNames()
                     .Where(n => n.StartsWith(MigrationPrefix) && n.EndsWith(".sql"))
                     .Order(StringComparer.Ordinal))
        {
            var version = resource[MigrationPrefix.Length..^".sql".Length];
            if (string.CompareOrdinal(version, last) <= 0)
                continue;

            using var reader = new StreamReader(assembly.GetManifestResourceStream(resource)!);
            await using (var migrate = Command(connection, await reader.ReadToEndAsync()))
                await migrate.ExecuteNonQueryAsync();
            await using (var record = Command(connection, "INSERT INTO gv_migrations (version) VALUES (@version)", ("@version", version)))
                await record.ExecuteNonQueryAsync();
            applied.Add(version);
        }
        return applied;
    }

    // Rereads settings and overrides, deletes expired rows, and reads the last saved group list.
    public async Task<Snapshot> LoadAsync(Action<string> warn)
    {
        await using var connection = await OpenAsync();

        // Missing settings get their defaults, so new ones show up in the table to be edited.
        var insert = "INSERT IGNORE INTO gv_settings (name, value) VALUES " +
                     string.Join(", ", Settings.Defaults.Select((_, i) => $"(@n{i}, @v{i})"));
        var defaults = Settings.Defaults
            .SelectMany((d, i) => new (string, object?)[] { ($"@n{i}", d.Name), ($"@v{i}", d.Value) })
            .ToArray();
        await using (var command = Command(connection, insert, defaults))
            await command.ExecuteNonQueryAsync();

        var values = new Dictionary<string, string>();
        await using (var command = Command(connection, "SELECT name, value FROM gv_settings"))
        await using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
                values[reader.GetString(0)] = reader.GetString(1);
        }
        var settings = Settings.Parse(values, warn);
        var now = settings.Now();

        int expired;
        await using (var command = Command(connection,
                         "DELETE FROM gv_verified WHERE expires_at IS NOT NULL AND expires_at <= @now", ("@now", now)))
            expired = await command.ExecuteNonQueryAsync();

        // A player with several override rows keeps the latest end; NULL (never) beats any date.
        var overrides = new Dictionary<ulong, DateTime?>();
        await using (var command = Command(connection, """
                         SELECT steamid64, expires_at FROM gv_verified
                         WHERE `override` = 1 AND (expires_at IS NULL OR expires_at > @now)
                         """, ("@now", now)))
        await using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                var steamId = (ulong)reader.GetInt64(0);
                DateTime? expires = reader.IsDBNull(1) ? null : reader.GetDateTime(1);
                if (!overrides.TryGetValue(steamId, out var current))
                    overrides[steamId] = expires;
                else if (current != null && (expires == null || expires > current))
                    overrides[steamId] = expires;
            }
        }

        var groupRows = new HashSet<ulong>();
        await using (var command = Command(connection, "SELECT steamid64 FROM gv_verified WHERE source = 'group'"))
        await using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
                groupRows.Add((ulong)reader.GetInt64(0));
        }

        return new Snapshot(settings, overrides, groupRows, expired);
    }

    public async Task SetSettingAsync(string name, string value)
    {
        await using var connection = await OpenAsync();
        await using var command = Command(connection, """
            INSERT INTO gv_settings (name, value) VALUES (@name, @value)
            ON DUPLICATE KEY UPDATE value = @value
            """, ("@name", name), ("@value", value));
        await command.ExecuteNonQueryAsync();
    }

    // Records one player's group membership from a Steam check: a member gets or refreshes their
    // 'group' row (keeping created_at, the first time they were seen in it); anyone else loses it.
    public async Task SetGroupMemberAsync(ulong steamId, bool member, DateTime now)
    {
        await using var connection = await OpenAsync();
        var sql = member
            ? """
              INSERT INTO gv_verified (steamid64, source, `override`, expires_at, added_by, created_at, updated_at)
              VALUES (@steamid, 'group', 0, NULL, 'steam-group', @now, @now)
              ON DUPLICATE KEY UPDATE updated_at = @now
              """
            : "DELETE FROM gv_verified WHERE steamid64 = @steamid AND source = 'group'";
        await using var command = Command(connection, sql, ("@steamid", (long)steamId), ("@now", now));
        await command.ExecuteNonQueryAsync();
    }

    // Forgets every 'group' row, when the group changes: they belong to the old one.
    public async Task ClearGroupRowsAsync()
    {
        await using var connection = await OpenAsync();
        await using var command = Command(connection, "DELETE FROM gv_verified WHERE source = 'group'");
        await command.ExecuteNonQueryAsync();
    }

    // Whether a player may use css_verify: one of their SimpleAdmin groups has at least minRank's
    // immunity, or is one of extraRanks. Groups are read the way our SimpleAdmin fork's
    // PermissionManager.GetRank reads ranks: an unexpired sa_admins row for this server whose
    // sa_admins_flags entry names an sa_groups row that applies to this server. Problem is set when
    // minRank isn't in sa_groups (then only extraRanks pass).
    public async Task<(bool Allowed, string? Problem)> CanVerifyAsync(ulong steamId, string minRank, string[] extraRanks,
        int? serverId, DateTime now)
    {
        await using var connection = await OpenAsync();

        int? minImmunity = null;
        await using (var command = Command(connection, "SELECT immunity FROM sa_groups WHERE name = @name ORDER BY immunity LIMIT 1",
                         ("@name", minRank)))
        {
            var result = await command.ExecuteScalarAsync();
            if (result != null && result != DBNull.Value)
                minImmunity = Convert.ToInt32(result);
        }

        var allowed = false;
        await using (var command = Command(connection, """
                         SELECT DISTINCT g.name, g.immunity
                         FROM sa_admins a
                         JOIN sa_admins_flags f ON f.admin_id = a.id
                         JOIN sa_groups g ON g.name = f.flag
                         JOIN sa_groups_servers gs ON gs.group_id = g.id AND (gs.server_id IS NULL OR gs.server_id = @serverid)
                         WHERE a.player_steamid = @steamid AND (a.ends IS NULL OR a.ends > @now)
                           AND (a.server_id IS NULL OR a.server_id = @serverid)
                         """, ("@steamid", (long)steamId), ("@serverid", serverId), ("@now", now)))
        await using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                var name = reader.GetString(0);
                var immunity = reader.GetInt32(1);
                if ((minImmunity != null && immunity >= minImmunity) ||
                    extraRanks.Contains(name, StringComparer.OrdinalIgnoreCase))
                    allowed = true;
            }
        }

        return (allowed, minImmunity == null ? $"verify_min_rank \"{minRank}\" isn't a group in sa_groups" : null);
    }
}