using System;
using System.Data;
using System.Timers;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace FilterAPI
{
    public class Database
    {
        public Config Conf;

        public SqlConnection Connection;

        public string AccountDB;
        public string CharacterDB;
        public string GameLogDB;
        public string FilterDB;
        public string IDColumn;
        public string AccountTable;
        public string TokenTable;

        public Database(Config config)
        {
            Conf = config;

            AccountDB = Conf.GetConfigValue("AccountDB");
            CharacterDB = Conf.GetConfigValue("CharacterDB");
            GameLogDB = Conf.GetConfigValue("GameLogDB");
            FilterDB = Conf.GetConfigValue("FilterDB");
            IDColumn = Conf.GetConfigValue("UserIDColumn");
            AccountTable = Conf.GetConfigValue("AccountTable");
            TokenTable = Conf.GetConfigValue("TokenTable");

            var connectionSb = new SqlConnectionStringBuilder
            {
                DataSource = Conf.GetConfigValue("SQLDataSource"),
                MultipleActiveResultSets = true,
                UserID = Conf.GetConfigValue("SQLUserID"),
                Password = Conf.GetConfigValue("SQLPassword")
            };

            Connection = new SqlConnection { ConnectionString = connectionSb.ConnectionString };
            Connection.Open();

            var databaseAlive = new Timer { AutoReset = true };
            databaseAlive.Elapsed += DatabaseAlive_Elapsed;
            databaseAlive.Interval = 1500;
            databaseAlive.Start();
        }

        private void DatabaseAlive_Elapsed(object sender, ElapsedEventArgs args)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nCharNo FROM {CharacterDB}..tCharacter";
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public bool DatabaseExists(string database)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM sys.databases WHERE Name = '{database}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool TableExists(string database, string table)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {database}.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{table}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool TableHasColumn(string database, string tableName, string columnName)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 COLUMN_NAME FROM {database}.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}' AND COLUMN_NAME = '{columnName}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows) { return true; }
                }
            }

            return false;
        }

        public bool sUsernameExists(string sUsername)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) sUsername FROM {AccountDB}..{AccountTable} WHERE sUsername = @sUsername";
                command.Parameters.AddWithValue("@sUsername", sUsername);
                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool sUserPassMatchsUsername(string sUsername, string sUserPass)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) sUsername FROM {AccountDB}..{AccountTable} WHERE sUsername = @sUsername AND sUserPass = @sUserPass";
                command.Parameters.AddWithValue("@sUsername", sUsername);
                command.Parameters.AddWithValue("@sUserPass", sUserPass);
                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public int GetnEMIDFromsUsername(string sUsername)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 {IDColumn} sUsername FROM {AccountDB}..{AccountTable} WHERE sUsername = @sUsername";
                command.Parameters.AddWithValue("@sUsername", sUsername);
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public string GetsUserPassFromsUsername(string sUsername)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT sUserPass FROM {AccountDB}..{AccountTable} WHERE sUsername = @sUsername";
                command.Parameters.AddWithValue("@sUsername", sUsername);
                return Convert.ToString(command.ExecuteScalar());
            }

        }

        public bool nEMIDHasToken(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) from {AccountDB}..{TokenTable} WHERE {IDColumn} = @nEMID";
                command.Parameters.AddWithValue("@nEMID", nEmid);
                return Convert.ToInt16(command.ExecuteScalar()) >= 1;
            }
        }

        public void DeletesTokenFornEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {AccountDB}..{TokenTable} WHERE {IDColumn} = @nEMID";
                command.Parameters.AddWithValue("@nEMID", nEmid);
                if (nEMIDHasToken(nEmid))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public string HandleTokenFornEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var spCommand = Connection.CreateCommand())
            {
                spCommand.CommandText = $"[{AccountDB}].[dbo].sp_TokenInsert";
                spCommand.CommandType = CommandType.StoredProcedure;
                spCommand.CommandTimeout = 0;

                var sToken = Convert.ToString(Guid.NewGuid()).Substring(0, 25);

                spCommand.Parameters.AddWithValue("@nEMID", nEmid);
                spCommand.Parameters.AddWithValue("@sToken", sToken);
                spCommand.Parameters.Add("@outToken", SqlDbType.NVarChar, 50);
                spCommand.Parameters["@outToken"].Direction = ParameterDirection.Output;
                spCommand.ExecuteNonQuery();

                return spCommand.Parameters["@outToken"].Value.ToString();
            }
        }

        public string CreateOrUpdatesTokenFornEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                var sToken = Convert.ToString(Guid.NewGuid()).Substring(0, 25);
                if (nEMIDHasToken(nEmid))
                {
                    command.CommandText = $"UPDATE {AccountDB}..{TokenTable} SET {IDColumn} = @nEMID, sToken = @sToken WHERE {IDColumn} = @nEMID";
                }
                else
                {
                    command.CommandText = $"INSERT into {AccountDB}..{TokenTable} ({IDColumn}, sToken) VALUES (@nEMID,@sToken)";
                }
                command.Parameters.AddWithValue("@nEMID", nEmid);
                command.Parameters.AddWithValue("@sToken", sToken);
                command.ExecuteNonQuery();
                return sToken;
            }
        }
        public string CreateAndInsertsTokenFornEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                var sToken = Convert.ToString(Guid.NewGuid());

                DeletesTokenFornEMID(nEmid);

                command.CommandText = $"INSERT INTO {AccountDB}..{TokenTable} ({IDColumn}, sToken) VALUES (@nEMID, @sToken)";
                command.Parameters.AddWithValue("@nEMID", nEmid);
                command.Parameters.AddWithValue("@sToken", sToken);
                command.ExecuteNonQuery();

                return sToken;
            }
        }

        public void InsertNewtAccount(string sUsername, string sUserPass)
        {
            InsertNewtAccount(sUsername, sUserPass, "nobody@nowhere.never", "127.0.0.1", 0);
        }

        public void InsertNewtAccount(string sUsername, string sUserPass, string sEmail, string sIP, int nAuthID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {AccountDB}..{AccountTable} (sUsername, sUserPass, sEmail, nAuthID, sIP, dDate, nAGPoints, nBonusAGPoints, sRID, sNotes) VALUES (@sUsername, @sUserPass, @sEmail, @nAuthID, @sIP, @dDate, '0', '0', '-', '-') ";
                command.Parameters.Add("@sUsername", SqlDbType.NVarChar).Value = sUsername;
                command.Parameters.Add("@sUserPass", SqlDbType.NVarChar).Value = sUserPass;
                command.Parameters.Add("@nAuthID", SqlDbType.Int).Value = nAuthID;
                command.Parameters.Add("@sEmail", SqlDbType.NVarChar).Value = sEmail;
                command.Parameters.Add("@sIP", SqlDbType.NVarChar).Value = sIP;
                command.Parameters.Add("@dDate", SqlDbType.DateTime).Value = DateTime.Now;
                command.ExecuteNonQuery();
            }
        }

        public void SetsUserPassForsUsername(string sUsername, string sUserPass)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {AccountDB}..{AccountTable} SET sUserPass = @sUserPass WHERE sUsername = @sUsername";
                command.Parameters.AddWithValue("@sUsername", sUsername);
                command.Parameters.AddWithValue("@sUserPass", sUserPass);
                command.ExecuteNonQuery();
            }
        }

        public bool sTokenExists(string sToken)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {AccountDB}..{TokenTable} WHERE sToken = @sToken";
                command.Parameters.AddWithValue("@sToken", sToken);
                if (Convert.ToInt32(command.ExecuteScalar()) != 0) { return true; }
            }

            return false;
        }

        public bool GetnEMIDFromsToken(string sToken, out int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 {IDColumn} FROM {AccountDB}..{TokenTable} WHERE sToken = @sToken";
                command.Parameters.AddWithValue("@sToken", sToken);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        nEmid = Convert.ToInt32(reader[IDColumn]);

                        return true;
                    }
                }
            }

            nEmid = -1;

            return false;
        }

        public byte GetnAuthIDFromnEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nAuthID FROM {AccountDB}..{AccountTable} WHERE {IDColumn} = @nEMID";
                command.Parameters.AddWithValue("@nEMID", nEmid);
                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public byte GetnTypeIDfromnAuthID(byte nAuthID, out bool nLoginable)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nLoginable, nTypeID FROM {AccountDB}..tAuth WHERE nAuthID = @nAuthID";
                command.Parameters.AddWithValue("@nAuthID", nAuthID);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    nLoginable = Convert.ToBoolean(reader["nLoginable"]);

                    return Convert.ToByte(reader["nTypeID"]);
                }
            }
        }

        public string GetsUsernameFromnEMID(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 sUsername FROM {AccountDB}..{AccountTable} WHERE {IDColumn} = @nEMID";
                command.Parameters.AddWithValue("@nEMID", nEmid);
                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public byte GetnUsesFromsToken(string sToken)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nUses FROM {AccountDB}..{TokenTable} WHERE sToken = @sToken";
                command.Parameters.AddWithValue("@sToken", sToken);
                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public void SetnUsesForsToken(byte nUses, string sToken)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"UPDATE {AccountDB}..{TokenTable} SET nUses = @nUses WHERE sToken = @sToken";
                command.Parameters.AddWithValue("@nUses", nUses);
                command.Parameters.AddWithValue("@sToken", sToken);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteFromtTokensUsingsToken(string sToken)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {AccountDB}..{TokenTable} WHERE sToken = @sToken";
                command.Parameters.AddWithValue("@sToken", sToken);
                command.ExecuteNonQuery();
            }
        }

        public string GetsIDFromnUserIDAndnSlotNo(int nUserNo, byte nSlotNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 sID FROM {CharacterDB}..tCharacter WHERE nUserNo = @nUserNO AND nSlotNo = @nSlotNo AND bDeleted = '0'";
                command.Parameters.AddWithValue("@nUserNo", nUserNo);
                command.Parameters.AddWithValue("@nSlotNo", nSlotNo);
                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public string GetsIDFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 sID FROM {CharacterDB}..tCharacter WHERE nCharNo = '{nCharNo}'";

                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public void WipetLoggedInChars()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tLoggedInChars";
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public void InserttLoggedInChar(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"INSERT INTO {FilterDB}..tLoggedInChars (sID) VALUES (@sID)";
                command.Parameters.AddWithValue("@sID", sID);
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public void DeleteFromtLoggedInChar(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tLoggedInChars WHERE sID = @sID";
                command.Parameters.AddWithValue("@sID", sID);
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }

        public int GetOnlineCount()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tLoggedInChars";

                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public bool GettLoggedInChar(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 sID FROM {FilterDB}..tLoggedInChars WHERE sID = @sID";
                command.Parameters.AddWithValue("@sID", sID);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    return reader.HasRows;
                }
            }
        }

        public int GetnUserNoFromsID(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nUserNo FROM {CharacterDB}..tCharacter WHERE sID = @sID";
                command.Parameters.AddWithValue("@sID", sID);
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public int GetnCharNoFromsID(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nCharNo FROM {CharacterDB}..tCharacter WHERE sID = @sID";
                command.Parameters.AddWithValue("@sID", sID);
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public string GetsMapFromsID(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 sMap FROM {GameLogDB}..tGameLog WHERE nType = '15' AND nCharNo = '{GetnCharNoFromsID(sID)}' ORDER BY nLogNo DESC";

                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public byte GetnLevelFromsID(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nLevel FROM {CharacterDB}..tCharacter WHERE sID = @sID";
                command.Parameters.AddWithValue("@sID", sID);
                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public bool HasPermission(int nCharNo, string type)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 b{type} FROM {FilterDB}..tPermissions WHERE nCharNo = @nCharNo";
                command.Parameters.AddWithValue("@nCharNo", nCharNo);
                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool IsQuestComplete(int nCharNo, int nQuestNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nQuestNo FROM {CharacterDB}..tQuest WHERE nCharNo = @nCharNo AND nQuestNo = @nQuestNo";
                command.Parameters.AddWithValue("@nCharNo", nCharNo);
                command.Parameters.AddWithValue("@nQuestNo", nQuestNo);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows) { return true; }
                }
            }

            return false;
        }

        public long GetnItemKeyFromnOwnerAndnStorage(int nOwner, int nStorage, int nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nItemKey FROM {CharacterDB}..tItem WHERE nStorageType = '{nStorageType}' AND nOwner = @nOwner AND nStorage = @nStorage";
                command.Parameters.AddWithValue("@nOwner", nOwner);
                command.Parameters.AddWithValue("@nStorage", nStorage);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows) { return Convert.ToInt64(reader["nItemKey"]); }
                }
            }

            return -1;
        }

        public short GetAmountForItem(long nItemKey)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nOptionData FROM {CharacterDB}..tItemOptions WHERE nItemKey = @nItemKey AND nOptionType = '1'";
                command.Parameters.AddWithValue("@nItemKey", nItemKey);
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows) { return Convert.ToInt16(reader["nOptionData"]); }
                }
            }

            return 1;
        }

        public void SetAmountForItem(long nItemKey, byte nOptionData)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tItemOptions SET nOptionData = @nOptionData WHERE nItemKey = @nItemKey AND nOptionType = '1'";
                command.Parameters.AddWithValue("@nItemKey", nItemKey);
                command.Parameters.AddWithValue("@nOptionData", nOptionData);
                command.ExecuteNonQuery();
            }
        }

        public void MinusAmountForItem(long nItemKey, byte amount)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tItemOptions SET nOptionData = (nOptionData - @Amount) WHERE nItemKey = @nItemKey AND nOptionType = '1'";
                command.Parameters.AddWithValue("@nItemKey", nItemKey);
                command.Parameters.AddWithValue("@Amount", amount);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteItem(long nItemKey)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {CharacterDB}..tItem WHERE nItemKey = @nItemKey";
                command.Parameters.AddWithValue("@nItemKey", nItemKey);
                command.ExecuteNonQuery();

                command.CommandText = $"DELETE FROM {CharacterDB}..tItemOptions WHERE nItemKey = @nItemKey";
                command.ExecuteNonQuery();
            }
        }

        public bool GetbDeletedFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 bDeleted FROM {CharacterDB}..tCharacter WHERE nCharNo = '{nCharNo}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public DateTime GetdLastLoginDateFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 dLastLoginDate FROM {CharacterDB}..tCharacter WHERE nCharNo = '{nCharNo}'";

                return Convert.ToDateTime(command.ExecuteScalar());
            }
        }

        public void SetnLuckyNo(int nCharNo, int nLuckyNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nLuckyNo, dDate FROM {FilterDB}..tLuckyNos WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {FilterDB}..tLuckyNos SET nLuckyNo = '{nLuckyNo}', dDate = '{DateTime.Now}' WHERE nCharNo = '{nCharNo}'";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText =
                            $"INSERT INTO {FilterDB}..tLuckyNos (nCharNo, nLuckyNo) VALUES ('{nCharNo}', '{nLuckyNo}')";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public int GetnNoFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nNo FROM {CharacterDB}..tGuildAcademyMember WHERE nCharNo = '{nCharNo}'";

                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public long GetnMoneyFromnNo(int nNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nMoney FROM {CharacterDB}..tGuildAcademy WHERE nNo = '{nNo}'";

                return Convert.ToInt64(command.ExecuteScalar());
            }
        }

        public void WipeStorage(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {CharacterDB}..tUserMoney WHERE nUserNo = '{nUserNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void WipeCharacterMoney(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"UPDATE {CharacterDB}..tCharacter SET nMoney = '0' WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void SetCharacterDeleted(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET bDeleted = '1' WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void SetnAuthIDFornEMID(int nEmid, byte nAuthID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {AccountDB}..{AccountTable} SET nAuthID = '{nAuthID}' WHERE {IDColumn} = '{nEmid}'";
                command.ExecuteNonQuery();
            }
        }

        public byte GetnClassFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nClass FROM {CharacterDB}..tCharacterShape WHERE nCharNo = '{nCharNo}'";

                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public bool HasItemInSlot(int nOwner, int nStorage, int nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nItemKey FROM {CharacterDB}..tItem WHERE nStorageType = '{nStorageType}' AND nOwner = '{nOwner}' AND nStorage = '{nStorage}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    return reader.HasRows;
                }
            }
        }

        public bool HasSkill(int nCharNo, int nSkillNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nSkillNo FROM {CharacterDB}..tSkill WHERE nCharNo = '{nCharNo}' AND nSkillNo = '{nSkillNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    return reader.HasRows;
                }
            }
        }

        public void DeleteSkill(int nCharNo, int nSkillNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"DELETE FROM {CharacterDB}..tSkill WHERE nCharNo = '{nCharNo}' AND nSkillNo = '{nSkillNo}'";
                command.ExecuteNonQuery();
            }
        }

        public bool HasPassive(int nCharNo, int nSkillNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                nSkillNo = nSkillNo + 30000;

                command.CommandText =
                    $"SELECT TOP 1 nSkillNo FROM {CharacterDB}..tSkill WHERE nCharNo = '{nCharNo}' AND nSkillNo = '{nSkillNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    return reader.HasRows;
                }
            }
        }

        public List<ushort> GetnSkillNosFornCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nSkillNo FROM {CharacterDB}..tSkill WHERE nCharNo = '{nCharNo}'";

                var nSkillNos = new List<ushort>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { nSkillNos.Add(Convert.ToUInt16(reader["nSkillNo"])); }
                }

                return nSkillNos;
            }
        }

        public Dictionary<int, List<ushort>> GetnSkillNos()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nCharNo, nSkillNo FROM {CharacterDB}..tSkill";

                var nSkillNos = new Dictionary<int, List<ushort>>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!nSkillNos.ContainsKey(Convert.ToInt32(reader["nCharNo"]))) { nSkillNos.Add(Convert.ToInt32(reader["nCharNo"]), new List<ushort>()); }

                        nSkillNos[Convert.ToInt32(reader["nCharNo"])].Add(Convert.ToUInt16(reader["nSkillNo"]));
                    }
                }

                return nSkillNos;
            }
        }
        public void InsertJoinMessage(int nCharNo, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tJoinMessages (nCharNo, sMessage) VALUES ('{nCharNo}', '{sMessage}')";
                command.ExecuteNonQuery();
            }
        }

        public List<string> GetJoinMessages(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var messages = new List<string>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT sMessage FROM {FilterDB}..tJoinMessages WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { messages.Add(Convert.ToString(reader["sMessage"])); }
                }
            }

            return messages;
        }

        public void DeleteJoinMessages(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tJoinMessages WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void PutMoneyInStorage(string sID, long nMoney)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nUserNo FROM {CharacterDB}..tCharacter WHERE sID = '{sID}'";

                var nUserNo = Convert.ToInt32(command.ExecuteScalar());

                command.CommandText =
                    $"SELECT TOP 1 nUserMoney FROM {CharacterDB}..tUserMoney WHERE nUserNo = '{nUserNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (!reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"INSERT INTO {CharacterDB}..tUserMoney (nUserNo, nUserMoney) VALUES ('{nUserNo}', '{nMoney}')";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {CharacterDB}..tUserMoney SET nUserMoney = (nUserMoney + {nMoney}) WHERE nUserNo = '{nUserNo}'";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public void PutMoneyInStorage(int nUserNo, long nMoney)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nUserMoney FROM {CharacterDB}..tUserMoney WHERE nUserNo = '{nUserNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (!reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"INSERT INTO {CharacterDB}..tUserMoney (nUserNo, nUserMoney) VALUES ('{nUserNo}', '{nMoney}')";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {CharacterDB}..tUserMoney SET nUserMoney = (nUserMoney + {nMoney}) WHERE nUserNo = '{nUserNo}'";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public Dictionary<byte, ushort> GetItemsFromnStorageType(int nOwner, byte nStorageType)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var items = new Dictionary<byte, ushort>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT nStorage, nItemID FROM {CharacterDB}..tItem WHERE nOwner = '{nOwner}' AND nStorageType = '{nStorageType}'";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!items.ContainsKey(Convert.ToByte(reader["nStorage"]))) { items.Add(Convert.ToByte(reader["nStorage"]), Convert.ToUInt16(reader["nItemID"])); }
                    }
                }
            }

            return items;
        }

        public ushort GetnItemIDFromnOwnerAndnStorage(int nOwner, byte nStorage, byte nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT nStorage, nItemID FROM {CharacterDB}..tItem WHERE nOwner = '{nOwner}' AND nStorage = '{nStorage}' AND nStorageType = '{nStorageType}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    return reader.HasRows ? Convert.ToUInt16(reader["nItemID"]) : ushort.MaxValue;
                }
            }
        }

        public int GetnAmountFromsName(string sName)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nAmount FROM {FilterDB}..tServerRates WHERE sName = '{sName}'";

                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public List<string> GetOnlinesIDs()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var sIDs = new List<string>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT sID FROM {FilterDB}..tLoggedInChars";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!sIDs.Contains(Convert.ToString(reader["sID"]))) { sIDs.Add(Convert.ToString(reader["sID"])); }
                    }
                }
            }

            return sIDs;
        }

        public List<string> GetJoinCommands(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var commands = new List<string>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT sCommand FROM {FilterDB}..tJoinCommands WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { commands.Add(Convert.ToString(reader["sCommand"])); }
                }
            }

            return commands;
        }

        public void DeleteJoinCommands(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tJoinCommands WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public bool PermissionRowExists(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tPermissions WHERE nCharNo = '{nCharNo}'";

                if (Convert.ToByte(command.ExecuteScalar()) != 0) { return true; }
                else { return false; }
            }
        }

        public void InserttPermission(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"INSERT INTO {FilterDB}..tPermissions (nCharNo) VALUES ('{nCharNo}')";
                command.ExecuteNonQuery();
            }
        }

        public bool sIDTaken(string sID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {CharacterDB}..tCharacter WHERE sID = '{sID}'";

                if (Convert.ToInt32(command.ExecuteScalar()) != 0) { return true; }
                else { return false; }
            }
        }

        public void InserttHolyPromise(int nCharNo, int nUpCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {CharacterDB}..tHolyPromise WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();

                command.CommandText =
                    $"INSERT INTO {CharacterDB}..tHolyPromise (nCharNo, nUpCharNo, nFlags) VALUES ('{nCharNo}', '{nUpCharNo}', 0)";
                command.ExecuteNonQuery();
            }
        }

        public void InserttFriend(int nCharNo, int nFriendCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {CharacterDB}..tFriend (nCharNo, nFriendCharNo, nFlags) VALUES ('{nCharNo}', '{nFriendCharNo}', 0)";
                command.ExecuteNonQuery();
            }
        }

        public void AddnFameFornCharNo(int nCharNo, int nFame)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nFame = (nFame + {nFame}) WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void AddnMoneyFornCharNo(int nCharNo, long nMoney)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nMoney = (nMoney + {nMoney}) WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void SetnLevelFornCharNo(int nCharNo, byte nLevel)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nLevel = {nLevel} WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void AddnExpFornCharNo(int nCharNo, long nEXP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nExp = (nExp + {nEXP}) WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void SetnAdminLevelFornCharNo(int nCharNo, byte nAdminLevel)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nAdminLevel = {nAdminLevel} WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void CleartItem(int nOwner, int nStorageType)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT nItemKey FROM {CharacterDB}..tItem WHERE nStorageType = '{nStorageType}' AND nOwner = '{nOwner}'";

                var nItemKeys = new List<long>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { nItemKeys.Add(Convert.ToInt64(reader["nItemKey"])); }

                    reader.Close();
                }

                foreach (var nItemKey in nItemKeys)
                {
                    command.CommandText = $"DELETE FROM {CharacterDB}..tItem WHERE nItemKey = '{nItemKey}'";
                    command.ExecuteNonQuery();

                    command.CommandText = $"DELETE FROM {CharacterDB}..tItemMobList WHERE nItemKey = '{nItemKey}'";
                    command.ExecuteNonQuery();

                    command.CommandText = $"DELETE FROM {CharacterDB}..tItemOptions WHERE nItemKey = '{nItemKey}'";
                    command.ExecuteNonQuery();

                    command.CommandText = $"DELETE FROM {CharacterDB}..tItemTitle WHERE nItemKey = '{nItemKey}'";
                    command.ExecuteNonQuery();
                }
            }
        }

        public bool nEMIDExists(int nEmid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {AccountDB}..{AccountTable} WHERE {IDColumn} = '{nEmid}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool nIDExists(long nID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tSRPayments WHERE nID = '{nID}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void InserttSRPayment(long nID, long nNew, long nTotal, long nOid, string sSig, long nUid)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tSRPayments (nID, nNew, nTotal, nOID, sSig, nUID) VALUES ('{nID}', '{nNew}', '{nTotal}', '{nOid}', '{sSig}', '{nUid}')";
                command.ExecuteNonQuery();
            }
        }

        public List<string> GetsIDsFornUserNo(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var sIDs = new List<string>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT sID FROM {CharacterDB}..tCharacter WHERE nUserNo = '{nUserNo}' AND bDeleted = '0'";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { sIDs.Add(Convert.ToString(reader["sID"])); }
                }
            }

            return sIDs;
        }

        public void InserttFilterBan(int nUserNo, string sReason)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tFilterBans (nUserNo, sReason) VALUES ('{nUserNo}', '{sReason}')";
                command.ExecuteNonQuery();
            }
        }

        public void SetbInMaintenanceFornServerID(byte nServerID, bool bInMaintenance)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) bInMaintenance FROM {FilterDB}..tMaintenance WHERE nServerID = '{nServerID}'";
                var count = (int)command.ExecuteScalar();

                if (count > 0)
                {
                    command.CommandText = $"UPDATE {FilterDB}..tMaintenance SET bInMaintenance = '{bInMaintenance}' WHERE nServerID = '{nServerID}'";
                }
                else
                {
                    command.CommandText = $"INSERT INTO {FilterDB}..tMaintenance (nServerID, bInMaintenance) VALUES ({nServerID}, '{bInMaintenance}')";
                }
                command.ExecuteNonQuery();
            }
        }

        public bool GetbInMaintenanceFornServerID(byte nServerID)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 bInMaintenance FROM {FilterDB}..tMaintenance WHERE nServerID = '{nServerID}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public string GetsRecoveryKeyFornUserNo(int nUserNo, out bool ShowMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 sRecoveryKey FROM {FilterDB}..tAccountRecovery WHERE nUserNo = '{nUserNo}'";

                var sRecoveryKey = Convert.ToString(command.ExecuteScalar());

                if (sRecoveryKey == string.Empty)
                {
                    sRecoveryKey = Convert.ToString(Guid.NewGuid());

                    command.CommandText =
                        $"INSERT INTO {FilterDB}..tAccountRecovery (nUserNo, sRecoveryKey) VALUES ('{nUserNo}', '{sRecoveryKey}')";
                    command.ExecuteNonQuery();

                    ShowMessage = true;
                }
                else { ShowMessage = false; }

                return sRecoveryKey;
            }
        }

        public void GetnMinimumAndnMaximum(out int nMinimum, out int nMaximum)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nMinimum, nMaximum FROM {FilterDB}..tCen";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    nMinimum = Convert.ToInt32(reader["nMinimum"]);
                    nMaximum = Convert.ToInt32(reader["nMaximum"]);
                }
            }
        }

        public void InserttWhisper(string sID, string sIDReceive, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Whisper_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sIDReceive", sIDReceive));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttParty(string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Party_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttGuild(string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Guild_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttAcademy(string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Academy_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttNormal(string nZoneID, string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Normal_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@nZoneID", nZoneID));
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttRoar(string nZoneID, string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Roar_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@nZoneID", nZoneID));
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();

                command.CommandText =
                    $"INSERT INTO {FilterDB}..tRoar (nZoneID, sID, sMessage) VALUES ('{nZoneID}', '{sID}', '{sMessage}')";
                command.ExecuteNonQuery();
            }
        }

        public void InserttShout(string nZoneID, string sID, string sMessage)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Shout_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@nZoneID", nZoneID));
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sMessage", sMessage));
                command.ExecuteNonQuery();
            }
        }

        public void InserttCommand(string nZoneID, string sID, string sCommand, string sParamaters)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..cl_Command_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@nZoneID", nZoneID));
                command.Parameters.Add(new SqlParameter("@sID", sID));
                command.Parameters.Add(new SqlParameter("@sCommand", sCommand));
                command.Parameters.Add(new SqlParameter("@sParameters", sParamaters));
                command.ExecuteNonQuery();
            }
        }

        public string GetValue(string database, string table, string column, bool order = true)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 {column} FROM {database}..{table}";

                if (order) { command.CommandText += $" ORDER BY {column} DESC"; }

                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public bool GettMapRate(string sMapIndex, out int nItemRate, out int nExpRate)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nItemRate, nExpRate FROM {FilterDB}..tMapRates WHERE sMapIndex = '{sMapIndex}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        nItemRate = Convert.ToInt32(reader["nItemRate"]);
                        nExpRate = Convert.ToInt32(reader["nExpRate"]);
                    }
                    else
                    {
                        nItemRate = -1;
                        nExpRate = -1;
                    }

                    return reader.HasRows;
                }
            }
        }

        public void InserttSkill(int nCharNo, ushort nSkillNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {nCharNo}..tSkill (nCharNo, nSkillNo, nSkilllevel, nSkillExp, nSkillWriteTime, nSkillCoolTime, nSkillPowerDamage, nSkillPowerSP, nSkillPowerKeepTime, nSkillPowerCoolTime) VALUES ('{nCharNo}', '{nSkillNo}', 1, 0, 0, 0, 0, 0, 0, 0)";
                command.ExecuteNonQuery();
            }
        }

        public bool ShowCenMessage()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 bShowMessage FROM {FilterDB}..tCen";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool InGuild(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {CharacterDB}..tGuildMember WHERE nCharNo = '{nCharNo}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public byte GetnGradeFromnCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nGrade FROM {CharacterDB}..tGuildMember WHERE nCharNo = '{nCharNo}'";

                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public void RemoveFromtGuildMember(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {CharacterDB}..tGuildMember WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public bool ChecksPassword(int nUserNo, string sPassword) // TODO: redo this for BCrypt
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {FilterDB}..tPasswordChange WHERE nUserNo = '{nUserNo}' AND sPassword = '{sPassword}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void InsertIntotPasswordChange(int nUserNo, string sPassword)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tPasswordChange (nUserNo, sPassword) VALUES ('{nUserNo}', '{sPassword}')";
                command.ExecuteNonQuery();
            }
        }

        public bool DeleteMarriage(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {CharacterDB}..tMarriage WHERE nCharNo = '{nCharNo}'";

                var rowsAffected = command.ExecuteNonQuery();

                if (rowsAffected == 0) { return false; }

                command.CommandText = $"DELETE FROM {CharacterDB}..tMarriage WHERE nPartnerCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();

                return true;
            }
        }

        public bool CanCommandForsMap(string sMap)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tAntiCommand WHERE sMap LIKE '%{FilterDB}%'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool CanSeenCharNo(int nUserNo, int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {CharacterDB}..tChatBlock WHERE nUserNo = '{nUserNo}' AND nCharNo = '{nCharNo}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool sIDOwnedBynUserNo(string sID, int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {CharacterDB}..tCharacter WHERE sID = '{sID}' AND nUserNo = '{nUserNo}' AND bDeleted = '0'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void DecreasenMoneyFornCharNo(int nCharNo, long nMoney)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nMoney = (nMoney - {nMoney}) WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public long GetnMoneyFornCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nMoney FROM {CharacterDB}..tCharacter WHERE nCharNo = '{nCharNo}'";

                return Convert.ToInt64(command.ExecuteScalar());
            }
        }

        public void SetnExpFornCharNo(int nCharNo, long nExp)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {CharacterDB}..tCharacter SET nExp = '{nExp}' WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public bool GettSendLocal(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tSendLocal WHERE nUserNo = '{nUserNo}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void DeletenStorageTypeFornCharNo(int nCharNo, byte nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"DELETE FROM {CharacterDB}..tItem WHERE nOwner = '{nCharNo}' AND nStorageType = '{nStorageType}'";
                command.ExecuteNonQuery();
            }
        }

        public void InserttCookieClicker(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tCookieClicker WHERE nUserNo = '{nUserNo}'";

                if (Convert.ToBoolean(command.ExecuteScalar()))
                {
                    command.CommandText =
                        $"UPDATE {FilterDB}..tCookieClicker SET nTotalClicks = (nTotalClicks + 1) WHERE nUserNo = '{nUserNo}'";
                    command.ExecuteScalar();
                }
                else
                {
                    command.CommandText =
                        $"INSERT INTO {FilterDB}..tCookieClicker (nUserNo, nTotalClicks) VALUES ('{nUserNo}', 1)";
                    command.ExecuteScalar();
                }
            }
        }

        public string GetnTotalClicksFornUserNo(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 nTotalClicks FROM {FilterDB}..tCookieClicker WHERE nUserNo = '{nUserNo}'";

                return Convert.ToString(command.ExecuteScalar());
            }
        }

        public void GiveAlltPermissions(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COLUMN_NAME FROM {FilterDB}.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tPermissions'";

                var columnNames = new List<string>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { columnNames.Add(Convert.ToString(reader["COLUMN_NAME"])); }
                }

                columnNames.Remove("nCharNo");

                foreach (var columnName in columnNames)
                {
                    command.CommandText =
                        $"UPDATE {FilterDB}..tPermissions SET {columnName} = '1' WHERE nCharNo = '{nCharNo}'";
                    command.ExecuteNonQuery();
                }
            }
        }

        public bool HastUnlimitedRoars(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tUnlimitedRoars WHERE nCharNo = '{nCharNo}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void InserttUnlimitedRoars(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"INSERT INTO {FilterDB}..tUnlimitedRoars (nCharNo) VALUES ('{nCharNo}')";
                command.ExecuteNonQuery();
            }
        }

        public void InserttLoginQuery(int nUserNo, string sQuery)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"{FilterDB}..l_Query_Insert";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@nUserNo", nUserNo));
                command.Parameters.Add(new SqlParameter("@sQuery", sQuery));
                command.ExecuteNonQuery();
            }
        }

        public List<string> GetLoginQueries(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            var queries = new List<string>();

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT sQuery FROM {FilterDB}..tLoginQueries WHERE nUserNo = '{nUserNo}'";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { queries.Add(Convert.ToString(reader["sQuery"])); }
                }
            }

            return queries;
        }

        public void DeletetLoginQueries(int nUserNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tLoginQueries WHERE nUserNo = '{nUserNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void ExecutesQuery(string sQuery)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = sQuery;
                command.ExecuteNonQuery();
            }
        }

        public bool tDupeDetectRowExists(int nCharNo, string sType)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {FilterDB}..tDupeDetection WHERE nCharNo = '{nCharNo}' AND sType = '{sType}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void InserttDupeDetection(int nCharNo, string sType)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tDupeDetection (nCharNo, sType) VALUES ('{nCharNo}', '{sType}')";
                command.ExecuteNonQuery();
            }
        }

        public bool GettForcePasswordReset(out string sMessage, out DateTime dResetDate)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 * FROM {FilterDB}..tForcePasswordReset ORDER BY dResetDate DESC";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        sMessage = Convert.ToString(reader["sMessage"]);
                        dResetDate = Convert.ToDateTime(reader["dResetDate"]);

                        return true;
                    }
                    else
                    {
                        sMessage = string.Empty;
                        dResetDate = DateTime.MaxValue;

                        return false;
                    }
                }
            }
        }

        public DateTime GetLasttPasswordChange(int nUserNo)
        {
            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 dChangeDate FROM {FilterDB}..tPasswordChange WHERE nUserNo = '{nUserNo}' ORDER BY dChangeDate DESC";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows) { return Convert.ToDateTime(reader["dChangeDate"]); }
                    else { return DateTime.MinValue; }
                }
            }
        }

        public List<ushort> GettSkilllearnerFornCharNo(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nSkillNo FROM {FilterDB}..tSkillLearner WHERE nCharNo = '{nCharNo}'";

                var nSkillNos = new List<ushort>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { nSkillNos.Add(Convert.ToUInt16(reader["nSkillNo"])); }
                }

                return nSkillNos;
            }
        }

        public void DeletetSkillLearner(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tSkilLLearner WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public bool GettAntiDNForsMap(string sMap)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {FilterDB}..tAntiCommand WHERE sMap LIKE '%{FilterDB}%'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void InserttPSC(int nUserNo, string sKey, double nAmount)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tPSC (nUserNo, sKey, nAmount) VALUES ('{nUserNo}', '{sKey}', '{nAmount}')";
                command.ExecuteNonQuery();
            }
        }

        public bool tPSCRowExists(int nUserNo, string sKey)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {FilterDB}..tPSC WHERE nUserNo = '{nUserNo}' AND sKey = '{sKey}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public bool CanRerollItemStats(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nAmount FROM {FilterDB}..tStatReroll WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (!reader.HasRows) { return false; }
                    else
                    {
                        var rerolls = Convert.ToInt64(reader["nAmount"]);

                        return rerolls >= 1;
                    }
                }
            }
        }

        public void MinustStatReroll(int nCharNo, ulong nAmount)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"UPDATE {FilterDB}..tStatReroll SET nAmount = (nAmount - {nAmount}) WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public byte GetnOptionDataFornOptionType(int nOwner, int nStorage, int nOptionType, int nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                var nItemKey = GetnItemKeyFromnOwnerAndnStorage(nOwner, nStorage, nStorageType);

                command.CommandText =
                    $"SELECT TOP 1 nOptionData FROM {FilterDB}..tItem WHERE nItemKey = '{nItemKey}' AND nOptionType = '{nOptionType}''";

                return Convert.ToByte(command.ExecuteScalar());
            }
        }

        public void AddtStatRerollFornCharNo(int nCharNo, long nAmount)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nAmount FROM {FilterDB}..tStatReroll WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {FilterDB}..tStatReroll SET nAmount = (nAmount + {nAmount}) WHERE nCharNo = '{nCharNo}'";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText =
                            $"INSERT INTO {FilterDB}..tStatReroll (nCharNo, nAmount) VALUES ('{nCharNo}', '{nAmount}')";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public void AddnAmountForsIP(string sIP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nAmount FROM {FilterDB}..tIPBlock WHERE sIP = '{sIP}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {FilterDB}..tIPBlock SET nAmount = (nAmount + 1) WHERE sIP = '{sIP}'";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText = $"INSERT INTO {FilterDB}..tIPBlock (sIP, nAmount) VALUES ('{sIP}', '1')";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public bool GetbBlockForsIP(string sIP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 bBlock FROM {FilterDB}..tIPBlock WHERE sIP = '{sIP}'";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public Dictionary<int, Dictionary<byte, ushort>> GetnItemIDsFornStorageType(byte nStorageType = 9)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT nOwner, nStorage, nItemID FROM {CharacterDB}..tItem WHERE nStorageType = '{nStorageType}'";

                var nOwnernItemIDs = new Dictionary<int, Dictionary<byte, ushort>>();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!nOwnernItemIDs.ContainsKey(Convert.ToInt32(reader["nOwner"]))) { nOwnernItemIDs.Add(Convert.ToInt32(reader["nOwner"]), new Dictionary<byte, ushort>()); }

                        if (!nOwnernItemIDs[Convert.ToInt32(reader["nOwner"])].ContainsKey(Convert.ToByte(reader["nStorage"]))) { nOwnernItemIDs[Convert.ToInt32(reader["nOwner"])].Add(Convert.ToByte(reader["nStorage"]), Convert.ToUInt16(reader["nItemID"])); }
                    }
                }

                return nOwnernItemIDs;
            }
        }

        public void InserttUserIP(int nUserNo, string sIP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"INSERT INTO {FilterDB}..tUserIPs (nUserNo, sIP) VALUES ('{nUserNo}', '{sIP}')";
                command.ExecuteNonQuery();
            }
        }

        public void InserttParties(int nCharNo, string sMastersID, List<string> sMembersIDs)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"INSERT INTO {FilterDB}..tParties (nCharNo, sMastersID, sMember1sID, sMember2sID, sMember3sID, sMember4sID) VALUES ('{nCharNo}', '{sMastersID}', '{(sMembersIDs.Count >= 1 ? sMembersIDs[0] : "-")}', '{(sMembersIDs.Count >= 2 ? sMembersIDs[1] : "-")}', '{(sMembersIDs.Count >= 3 ? sMembersIDs[2] : "-")}', '{(sMembersIDs.Count >= 4 ? sMembersIDs[3] : "-")}')";
                command.ExecuteNonQuery();
            }
        }

        public void DeletetParties(int nCharNo)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tParties WHERE nCharNo = '{nCharNo}'";
                command.ExecuteNonQuery();
            }
        }

        public void WipetParties()
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"DELETE FROM {FilterDB}..tParties";
                command.ExecuteNonQuery();
            }
        }

        public List<int> GettPartiesnCharNos()
        {
            var nCharNos = new List<int>();

            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT nCharNo FROM {FilterDB}..tParties";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read()) { nCharNos.Add(Convert.ToInt32(reader["nCharNo"])); }
                }
            }

            return nCharNos;
        }

        public bool GettPartiesFornCharNo(int nCharNo, out string sMastersID, out List<string> sMastersIDs)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT TOP 1 sMastersID, sMember1sID, sMember2sID, sMember3sID, sMember4sID FROM {FilterDB}..tParties WHERE nCharNo = '{nCharNo}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        sMastersID = Convert.ToString(reader["sMastersID"]);

                        sMastersIDs = new List<string>();

                        if (Convert.ToString(reader["sMember1sID"]) != "-") { sMastersIDs.Add(Convert.ToString(reader["sMember1sID"])); }

                        if (Convert.ToString(reader["sMember2sID"]) != "-") { sMastersIDs.Add(Convert.ToString(reader["sMember2sID"])); }

                        if (Convert.ToString(reader["sMember3sID"]) != "-") { sMastersIDs.Add(Convert.ToString(reader["sMember3sID"])); }

                        if (Convert.ToString(reader["sMember4sID"]) != "-") { sMastersIDs.Add(Convert.ToString(reader["sMember4sID"])); }

                        return true;
                    }

                    sMastersID = string.Empty;

                    sMastersIDs = null;

                    return false;
                }
            }
        }

        public long GetnAmountForsIP(string sIP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nAmount FROM {FilterDB}..tIPBlock WHERE sIP = '{sIP}'";

                return Convert.ToInt64(command.ExecuteScalar());
            }
        }

        public bool TableEmpty(string database, string table)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT COUNT(*) FROM {database}..{table}";

                if (Convert.ToInt64(command.ExecuteScalar()) == 0) { return true; }
                else { return false; }
            }
        }

        public bool GettWorldBan(byte nWorldNo, string sUsername)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText =
                    $"SELECT COUNT(*) FROM {FilterDB}..tWorldBans WHERE nWorldNo = '{nWorldNo}' AND sUsername = '{sUsername}'";

                if (Convert.ToInt32(command.ExecuteScalar()) == 0) { return false; }
                else { return true; }
            }
        }

        public void AddnAttemptForsIP(string sIP)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }

            using (var command = Connection.CreateCommand())
            {
                command.CommandTimeout = 0;
                command.CommandText = $"SELECT TOP 1 nAttempt FROM {FilterDB}..tCancer WHERE sIP = '{sIP}'";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    if (reader.HasRows)
                    {
                        reader.Close();

                        command.CommandText =
                            $"UPDATE {FilterDB}..tCancer SET nAttempt = (nAttempt + 1) WHERE sIP = '{sIP}'";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        reader.Close();

                        command.CommandText = $"INSERT INTO {FilterDB}..tCancer (sIP, nAttempt) VALUES ('{sIP}', '1')";
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}