VeloeGameServerDataCollector/VeloeMonitorDataCollector/DatabaseConnectors/MySqlConnector.cs

using System.Globalization;
using System.Text;
using System.Text.Json;
using Microsoft.Extensions.Configuration;
using MinecraftStatus;
using MySqlConnector;
using Serilog;
using VeloeMonitorDataCollector.Dependencies;
using VeloeMonitorDataCollector.Models;

namespace VeloeMonitorDataCollector.DatabaseConnectors
{
    class MySqlConnector : IDataSendable
    {
        MySqlConnection _connection;
        private string _database;
        Serilog.ILogger _logger;

        Dictionary<string, bool?> _gamespyTableCheck = new();

        /// <summary>
        /// Creates connection to MySQL database using config.ini
        /// Checks if server is available and schema is created
        /// </summary>
        /// <param name="section">Configuration section with tag [MySQL]</param>
        /// <exception cref="ArgumentNullException"></exception>
        public MySqlConnector(in IConfigurationSection section, in Serilog.ILogger logger)
        {
            _logger = logger;

            section["port"] ??= "3306";
 
            _database = section["database"][..(section["database"].IndexOfAny(new[] { ' ', ';' }) >= 0 ?
                                                            section["database"].IndexOfAny(new[] { ' ', ';' }) :
                                                            section["database"].Length)];
             
            if (_database is null)
                throw new ArgumentNullException(_database);

            MySqlConnectionStringBuilder connectionString = new MySqlConnectionStringBuilder();
            //connectionString.Database = section["database"];
            connectionString.Server = section["server"];
            connectionString.Port = UInt32.Parse(section["port"]);
            connectionString.UserID = section["uid"];
            connectionString.Password = section["pwd"];
            connectionString.ConnectionReset = true;

            _logger.Information("Connecting to {0} (MySQL)", section["server"]);
            _connection = new MySqlConnection(connectionString.ToString());
            _connection.Open();

            try
            {
                _connection.ChangeDatabase(_database);
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 1049:
                        _logger.Warning(ex.Message);
                        _logger.Information("Create database? Y/N");
                        var key = Console.ReadKey();
                        if (key.KeyChar.Equals('Y'))
                        {
                            CreateDatabase(_database);
                        }
                        break;
                    default:
                        throw;
                }
            }            
        }

        public void Close()
        {
            _connection.Close();
            _connection.Dispose();
        }

        /// <summary>
        /// Check if table in database is correct for sending data from game servers
        /// Creates table if schema creates for the first time or empty
        /// </summary>
        /// <param name="name">name of table in database</param>
        /// <param name="type">type of server</param>
        /// <exception cref="ArgumentException">when entered type is not supported or invalid string accepted</exception>
        public bool CheckGameServer(in string name, in string type)
        {
            MySqlCommand command;
            string[] cols;
            string[] types;

            //TODO store cols and types strings somewhere else
            switch (type)
            {
                case "Minecraft":
                    cols = new string[]
                        {
                            "messageofaday",
                            "gametype",
                            "gameid",
                            "version",
                            "plugins",
                            "map",
                            "numplayers",
                            "maxplayers",
                            "players"
                        };

                    types = new string[]
                    {
                            "varchar(255)",
                            "varchar(16)",
                            "varchar(16)",
                            "varchar(16)",
                            "varchar(255)",
                            "varchar(48)",
                            "int",
                            "int",
                            "varchar(255)"
                    };
                    break;

                case "Steam":
                    cols = new string[]
                        {
                            "bots",
                            "environment",
                            "folder",
                            "game",
                            "gameid",
                            "keywords",
                            "map",
                            "maxplayers",
                            "name",
                            "players",
                            "servertype",
                            "vac",
                            "version",
                            "visibility",
                            "playerslist"
                        };

                    types = new string[]
                    {
                            "tinyint unsigned",
                            "varchar(16)",
                            "varchar(48)",
                            "varchar(48)",
                            "bigint",
                            "varchar(255)",
                            "varchar(48)",
                            "tinyint unsigned",
                            "varchar(128)",
                            "tinyint unsigned",
                            "varchar(16)",
                            "varchar(16)",
                            "varchar(16)",
                            "varchar(16)",
                            "varchar(255)"
                        };
                    break;

                case "Gamespy3":
                    _logger.Warning("Table {0} can't be checked before game server quering service init because server uses {1} protocol. Table will be checked after first success connection to the server.", name, "Gamespy3");
                    return true;

                case "Gamespy2":
                    _logger.Warning("Table {0} can't be checked before game server quering service init because server uses {1} protocol. Table will be checked after first success connection to the server.", name, "Gamespy2");
                    return true;

                default:
                    throw new ArgumentException(type);
            }

            try
            {
                bool valid = true;
                command = new MySqlCommand($"SHOW COLUMNS FROM {name};", _connection);
                using var result = command.ExecuteReader();

                _logger.Information(
                    "Checking table {0} in {1} for {2} (MySQL)", name, _connection.Database, _connection.DataSource);
                if (result.Read() is false)
                {
                    CreateTableGameServer(name, type);
                    return true;
                }

                if (result.GetString(0) is not "date" &&
                    result.GetString(1) is not "bigint")
                {
                    _logger.Error("Error at column {0}. Correct is {1} {2}",0,"date","bigint");
                    valid = false;
                }
             
                //check here other columns

                for (int i = 0; i < cols.Length; i++)
                {
                    if (!result.Read())
                    {
                        _logger.Error("End of table on column {0}/{1}",i+1,cols.Length);
                        return false;
                    }
                    if (result.GetString(0) != cols[i] &&
                        result.GetString(1) != types[i])
                    {
                        _logger.Error("Error at colunm {0}. Correct is {1} {2}",i+1,cols[i],types[i]);
                        valid = false;
                    }
                }

                if (valid) _logger.Information("Ok");
                return valid;
            }
            catch (MySqlException e)
            {
                if (e.Number == 1146)
                {
                    CreateTableGameServer(name, type);
                    return true;
                }

                throw;
            }

        }

        public bool CheckHardware(in Dictionary<string, float> input)
        {
            var command = new MySqlCommand("SHOW COLUMNS FROM hardware;", _connection);

            try
            {
                bool valid = true;
                using var result = command.ExecuteReader();
                _logger.Information("Checking table {0} in {1} for {2} (MySQL)", "hardware",_connection.Database, _connection.DataSource);
                if (result.Read() is false)
                {
                    CreateTableHardware(input);
                    return true;
                }

                if (result.GetString(0) is not "date" && result.GetString(1) is not "bigint")
                {
                    _logger.Error("Error at column {0}. Correct is {1} {2}",0,"date","bigint");
                    valid = false;
                }

                int i = 1;
                foreach (var parameter in input)
                {
                    if (result.Read() is false)
                    {
                        _logger.Error("End of table on column {0}",i);
                        return false;
                    }

                    if (result.GetString(0) != parameter.Key || result.GetString(1) != "float")
                    {
                        _logger.Error("Error at colunm {0}. Correct is {1} {2}",i,parameter.Key,"float");
                        valid = false;
                    }
                    i++;
                }

                if (valid) _logger.Information("Ok");
                return valid;
            }
            catch (MySqlException e)
            {
                if (e.Number == 1146)
                {
                    CreateTableHardware(input);
                    return true;
                }
                throw;
            }
        }

        public void CreateTableGameServer(in string name,in string type)
        {
            MySqlCommand command;
            StringBuilder query;
            switch (type)
            {
                case "Minecraft":
                    //TODO change it when C#11 releases
                    //TODO query builder
                    query = new StringBuilder();
                    query.AppendFormat("CREATE TABLE {0} (", name);
                    query.Append("date BIGINT NOT NULL,");
                    query.Append("messageofaday VARCHAR(255),");
                    query.Append("gametype VARCHAR(16),");
                    query.Append("gameid VARCHAR(16),");
                    query.Append("version VARCHAR(16),");
                    query.Append("plugins VARCHAR(255),");
                    query.Append("map VARCHAR(48),");
                    query.Append("numplayers INT,");
                    query.Append("maxplayers INT,");
                    query.Append("players VARCHAR(255)");
                    query.Append(",PRIMARY KEY (date))");
                    command = new MySqlCommand(query.ToString(), _connection);

                    break;
                case "Steam":
                    query = new StringBuilder();
                    query.AppendFormat("CREATE TABLE {0} (", name);
                    query.Append("date BIGINT NOT NULL,");
                    query.Append("bots tinyint unsigned,");
                    query.Append("environment VARCHAR(16),");
                    query.Append("folder VARCHAR(48),");
                    query.Append("game VARCHAR(48),");
                    query.Append("gameid bigint,");
                    query.Append("keywords VARCHAR(255),");
                    query.Append("map varchar(48),");
                    query.Append("maxplayers tinyint unsigned,");
                    query.Append("name VARCHAR(128),");
                    query.Append("players tinyint unsigned,");
                    query.Append("servertype VARCHAR(16),");
                    query.Append("vac VARCHAR(16),");
                    query.Append("version VARCHAR(16),");
                    query.Append("visibility VARCHAR(16),");
                    query.Append("playerslist VARCHAR(255)");
                    query.Append(",PRIMARY KEY (date))");
                    command = new MySqlCommand(query.ToString(), _connection);

                    
                    break;

                case "Gamespy3":
                    throw new NotImplementedException("Gamespy3");
                    return;

                case "Gamespy2":
                    throw new NotImplementedException("Gamespy3");
                    return;

                default:    
                    throw new ArgumentException(type);
            }
            command.ExecuteNonQuery();

            _logger.Information("Table {0} created in {1} for {2} (MySQL)", name, _connection.Database, _connection.DataSource);
        }

        public void CreateTableHardware(in Dictionary<string, float> input)
        {           
            StringBuilder columns = new StringBuilder();
            foreach (var parameter in input)
            {
                columns.Append($",{parameter.Key} FLOAT ");
            }
            MySqlCommand command = new MySqlCommand(
                $"CREATE TABLE hardware (date BIGINT NOT NULL {columns} ,PRIMARY KEY (date))", _connection);

            command.ExecuteNonQuery();

            _logger.Information("Table hardware created in {0} for {1} (MySQL)", _connection.Database,_connection.DataSource);
        }

        public void SendHardware(in Dictionary<string,float> data)
        {
            MySqlCommand command;
            CultureInfo oldCultureInfo = CultureInfo.CurrentCulture;
            CultureInfo.CurrentCulture = CultureInfo.InvariantCulture;
            var cols = string.Join(',',data.Keys);
            var values = string.Join(", @",data.Keys);
            CultureInfo.CurrentCulture = oldCultureInfo;

            var query = $"INSERT INTO hardware (date ,{cols}) VALUES (@date ,@{values})";

            //Console.WriteLine(query);
            command = new MySqlCommand(query, _connection);

            command.Parameters.Add("date", MySqlDbType.Int64).Value = DateTimeOffset.Now.ToUnixTimeSeconds();
            foreach (var parameter in data)
            {
                command.Parameters.Add(parameter.Key, MySqlDbType.Float).Value = parameter.Value;
            }

            command.ExecuteNonQueryAsync();
        }

        public void SendMinecraft(in McStatus data, in string name)
        {
            MySqlCommand command;

            command = new MySqlCommand($"INSERT INTO {name} (date ,messageofaday, gametype, gameid, version, plugins, map, numplayers, maxplayers, players) VALUES (@date, @messageofaday, @gametype, @gameid, @version, @plugins, @map, @numplayers, @maxplayers, @players)", _connection);

            command.Parameters.Clear();
            command.Parameters.Add("date", MySqlDbType.UInt64).Value = DateTimeOffset.Now.ToUnixTimeSeconds();
            command.Parameters.Add("messageofaday", MySqlDbType.VarString).Value = data.MessageOfTheDay;
            command.Parameters.Add("gametype", MySqlDbType.VarString).Value = data.Gametype;
            command.Parameters.Add("gameid", MySqlDbType.VarString).Value = data.GameId;
            command.Parameters.Add("version", MySqlDbType.VarString).Value = data.Version;
            command.Parameters.Add("plugins", MySqlDbType.VarString).Value = data.Plugins;
            command.Parameters.Add("map", MySqlDbType.VarString).Value = data.Map;
            command.Parameters.Add("numplayers", MySqlDbType.Int32).Value = data.NumPlayers;
            command.Parameters.Add("maxplayers", MySqlDbType.Int32).Value = data.MaxPlayers;
            command.Parameters.Add("players", MySqlDbType.VarString).Value = JsonSerializer.Serialize(data.Players);
            
            command.ExecuteNonQueryAsync();
        }

        public void SendSteam(in SteamData data, in string name)
        {
            MySqlCommand command;

            command = new MySqlCommand($"INSERT INTO {name} (date, bots, environment, folder, game, gameid, keywords, map, maxplayers, name, players, servertype, vac, version, visibility, playerslist) VALUES  (@date, @bots, @environment, @folder, @game, @gameid, @keywords, @map, @maxplayers, @name, @players, @servertype, @vac, @version, @visibility, @playerslist)", _connection);

            command.Parameters.Clear();
            command.Parameters.Add("date", MySqlDbType.UInt64).Value = DateTimeOffset.Now.ToUnixTimeSeconds();
            command.Parameters.Add("bots", MySqlDbType.UByte).Value = data.ServerInfo.Bots;
            command.Parameters.Add("environment", MySqlDbType.VarString).Value = data.ServerInfo.Environment;
            command.Parameters.Add("folder", MySqlDbType.VarString).Value = data.ServerInfo.Folder;
            command.Parameters.Add("game", MySqlDbType.VarString).Value = data.ServerInfo.Game;
            command.Parameters.Add("gameid", MySqlDbType.Int64).Value = data.ServerInfo.GameID;
            command.Parameters.Add("keywords", MySqlDbType.VarString).Value = data.ServerInfo.Keywords;
            command.Parameters.Add("map", MySqlDbType.VarString).Value = data.ServerInfo.Map;
            command.Parameters.Add("maxplayers", MySqlDbType.VarString).Value = data.ServerInfo.MaxPlayers;
            command.Parameters.Add("name", MySqlDbType.VarString).Value = data.ServerInfo.Name;
            command.Parameters.Add("players", MySqlDbType.VarString).Value = data.ServerInfo.Players;
            command.Parameters.Add("servertype", MySqlDbType.VarString).Value = data.ServerInfo.ServerType;
            command.Parameters.Add("vac", MySqlDbType.VarString).Value = data.ServerInfo.VAC;
            command.Parameters.Add("version", MySqlDbType.VarString).Value = data.ServerInfo.Version;
            command.Parameters.Add("visibility", MySqlDbType.VarString).Value = data.ServerInfo.Visibility;
            command.Parameters.Add("playerslist", MySqlDbType.VarString).Value = JsonSerializer.Serialize(data.Players);

            command.ExecuteNonQueryAsync();
        }

        public void SendGamespy3(in Gs3Status.Status data,in string name)
        {
            if (_gamespyTableCheck.GetValueOrDefault(name, null) is null)
            {
                _gamespyTableCheck[name] = CheckGamespy3(data, name);
            }

            if (_gamespyTableCheck[name] is false)
                return;

            MySqlCommand command;

            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.Append($"INSERT INTO {name} (date");

            foreach (var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                {
                    queryBuilder.Append(", ");
                    queryBuilder.Append(parameter.Key);
                }
            }

            queryBuilder.Append(", playerslist) VALUES  ( @date ");

            foreach (var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                {
                    queryBuilder.Append(", @");
                    queryBuilder.Append(parameter.Key);

                    //_logger.Debug("'{0}' = '{1}'", parameter.Key, parameter.Value);
                }
            }

            queryBuilder.Append(", @playerslist )");

            command = new MySqlCommand(queryBuilder.ToString(), _connection);

            command.Parameters.Clear();
            command.Parameters.Add("date", MySqlDbType.UInt64).Value = DateTimeOffset.Now.ToUnixTimeSeconds();
            
            foreach(var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                
                    command.Parameters.Add(parameter.Key, MySqlDbType.VarString).Value = parameter.Value;
            }

            command.Parameters.Add("playerslist", MySqlDbType.VarString).Value = JsonSerializer.Serialize(data.Players);
            command.ExecuteNonQueryAsync();
        }

        private bool CheckGamespy3(Gs3Status.Status input, string name)
        {
            var command = new MySqlCommand($"SHOW COLUMNS FROM {name};", _connection);

            try
            {
                bool valid = true;
                using var result = command.ExecuteReaderAsync().Result;
                _logger.Information("Checking table {0} ({1}) in {2} for {3} (MySQL)", name, "Gamespy3" , _connection.Database, _connection.DataSource);
                if (result.Read() is false)
                {
                    CreateTableGamespy3(input, name);
                    return true;
                }

                int colsCount = 1;

                foreach(var parameter in input.Info)
                    if (parameter.Value is not null && parameter.Value != String.Empty)
                        colsCount++;

                if (result.GetString(0) is not "date" && result.GetString(1) is not "bigint")
                {
                    _logger.Error("Error at column {0}. Correct is {1} {2}", 0, "date", "bigint");
                    valid = false;
                }

                int i = 1;
                foreach (var parameter in input.Info)
                {
                    if (result.Read() is false)
                    {
                        _logger.Error("End of table on column {0}/{1}", i, colsCount);
                        return false;
                    }

                    if (result.GetString(0) != parameter.Key || result.GetString(1) != "varchar(127)")
                    {
                        _logger.Error("Error at colunm {0}. Correct is {1} {2}", i, parameter.Key, "varchar(127)");
                        valid = false;
                    }
                    i++;
                }

                if (result.Read() is false)
                {
                    _logger.Error("End of table on column {0}/{0}", colsCount);
                    return false;
                }

                if (result.GetString(0) != "playerslist" || result.GetString(1) != "varchar(255)")
                {
                    _logger.Error("Error at colunm {0}. Correct is {1} {2}", i, "playerslist", "varchar(255)");
                    valid = false;
                }


                if (valid) _logger.Information("Ok");
                //else _logger.Warning("Table {0} isn't receiving any updates to {1} (MySQL)", name, _connection.DataSource);
                return valid;
            }
            catch (MySqlException e)
            {
                if (e.Number == 1146)
                {
                    CreateTableGamespy3(input, name);
                    return true;
                }
                throw;
            }
        }

        private void CreateTableGamespy3(Gs3Status.Status input, string name)
        {
            StringBuilder columns = new StringBuilder();
            foreach (var parameter in input.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                    columns.Append($",{parameter.Key} VARCHAR(127) ");
            }
            MySqlCommand command = new MySqlCommand(
                $"CREATE TABLE {name} (date BIGINT NOT NULL {columns}, playerslist VARCHAR(255) ,PRIMARY KEY (date))", _connection);

            command.ExecuteNonQuery();

            _logger.Information("Table {0} created in {1} for {2} (MySQL)", name ,_connection.Database, _connection.DataSource);
        }

        public void SendGamespy2(in Gs2Status.Status data, in string name)
        {
            if (_gamespyTableCheck.GetValueOrDefault(name, null) is null)
            {
                _gamespyTableCheck[name] = CheckGamespy2(data, name);
            }

            if (_gamespyTableCheck[name] is false)
                return;

            MySqlCommand command;

            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.Append($"INSERT INTO {name} (date");

            foreach (var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                {
                    queryBuilder.Append(", ");
                    queryBuilder.Append(parameter.Key);
                }
            }

            queryBuilder.Append(", playerslist) VALUES  ( @date ");

            foreach (var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                {
                    queryBuilder.Append(", @");
                    queryBuilder.Append(parameter.Key);

                    //_logger.Debug("'{0}' = '{1}'", parameter.Key, parameter.Value);
                }
            }

            queryBuilder.Append(", @playerslist )");

            command = new MySqlCommand(queryBuilder.ToString(), _connection);

            command.Parameters.Clear();
            command.Parameters.Add("date", MySqlDbType.UInt64).Value = DateTimeOffset.Now.ToUnixTimeSeconds();

            foreach (var parameter in data.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)

                    command.Parameters.Add(parameter.Key, MySqlDbType.VarString).Value = parameter.Value;
            }

            command.Parameters.Add("playerslist", MySqlDbType.VarString).Value = JsonSerializer.Serialize(data.Players);
            command.ExecuteNonQueryAsync();
        }

        private bool CheckGamespy2(Gs2Status.Status input, string name)
        {
            var command = new MySqlCommand($"SHOW COLUMNS FROM {name};", _connection);

            try
            {
                bool valid = true;
                using var result = command.ExecuteReaderAsync().Result;
                _logger.Information("Checking table {0} ({1}) in {2} for {3} (MySQL)", name, "Gamespy2", _connection.Database, _connection.DataSource);
                if (result.Read() is false)
                {
                    CreateTableGamespy2(input, name);
                    return true;
                }

                int colsCount = 1;

                foreach (var parameter in input.Info)
                    if (parameter.Value is not null && parameter.Value != String.Empty)
                        colsCount++;

                if (result.GetString(0) is not "date" && result.GetString(1) is not "bigint")
                {
                    _logger.Error("Error at column {0}. Correct is {1} {2}", 0, "date", "bigint");
                    valid = false;
                }

                int i = 1;
                foreach (var parameter in input.Info)
                {
                    if (result.Read() is false)
                    {
                        _logger.Error("End of table on column {0}/{1}", i, colsCount);
                        return false;
                    }

                    if (result.GetString(0) != parameter.Key || result.GetString(1) != "varchar(127)")
                    {
                        _logger.Error("Error at colunm {0}. Correct is {1} {2}", i, parameter.Key, "varchar(127)");
                        valid = false;
                    }
                    i++;
                }

                if (result.Read() is false)
                {
                    _logger.Error("End of table on column {0}/{0}", colsCount);
                    return false;
                }

                if (result.GetString(0) != "playerslist" || result.GetString(1) != "varchar(255)")
                {
                    _logger.Error("Error at colunm {0}. Correct is {1} {2}", i, "playerslist", "varchar(255)");
                    valid = false;
                }


                if (valid) _logger.Information("Ok");
                //else _logger.Warning("Table {0} isn't receiving any updates to {1} (MySQL)", name, _connection.DataSource);
                return valid;
            }
            catch (AggregateException e)
            {
                if (e.InnerException is MySqlException)
                {                   
                    if (((MySqlException)e.InnerException).Number == 1146)
                    {
                        CreateTableGamespy2(input, name);
                        return true;
                    }                 
                }
                throw;
            }
        }

        private void CreateTableGamespy2(Gs2Status.Status input, string name)
        {
            StringBuilder columns = new StringBuilder();
            foreach (var parameter in input.Info)
            {
                if (parameter.Value != String.Empty && parameter.Value is not null)
                    columns.Append($",{parameter.Key} VARCHAR(127) ");
            }
            MySqlCommand command = new MySqlCommand(
                $"CREATE TABLE {name} (date BIGINT NOT NULL {columns}, playerslist VARCHAR(255) ,PRIMARY KEY (date))", _connection);

            command.ExecuteNonQuery();

            _logger.Information("Table {0} created in {1} for {2} (MySQL)", name, _connection.Database, _connection.DataSource);
        }

        private void CreateDatabase(string name)
        {
            MySqlCommand command = new MySqlCommand($"CREATE DATABASE {name}", _connection);
            command.ExecuteNonQuery();
        }
    }
}