using Discord;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;

namespace MyBot.Modules.Public
{
    public class Database
    {
        private string table { get; set; }
        private const string server = "localhost";
        private const string database = "Bot";
        private const string username = "aaaa";
        private const string password = "aaaa";
        private MySqlConnection dbConnection;

        public Database(string table)
        {
            this.table = table;
            MySqlConnectionStringBuilder stringBuilder = new MySqlConnectionStringBuilder();
            stringBuilder.Server = server;
            stringBuilder.UserID = username;
            stringBuilder.Password = password;
            stringBuilder.Database = database;
            stringBuilder.SslMode = MySqlSslMode.None;

            var connectionString = stringBuilder.ToString();

            dbConnection = new MySqlConnection(connectionString);

            dbConnection.Open();
        }

        public MySqlDataReader FireCommand(string query)
        {
            if (dbConnection == null)
            {
                return null;
            }

            MySqlCommand command = new MySqlCommand(query, dbConnection);

            var mySqlReader = command.ExecuteReader();

            return mySqlReader;
        }

        public void CloseConnection()
        {
            if (dbConnection != null)
            {
                dbConnection.Close();
            }
        }

        public static List<String> CheckExistingUser(IUser user)
        {
            var result = new List<String>();
            var database = new Database("Bot");

            var str = string.Format($"SELECT * FROM Bot WHERE user_id = '{user.Id}'");
            var tableName = database.FireCommand(str);

            while (tableName.Read())
            {
                var userId = (string)tableName["user_id"];

                result.Add(userId);
            }

            database.CloseConnection();

            return result;
        }

        public static void EnterUser(IUser user)
        {
            var database = new Database("Bot");

            var str = string.Format($"INSERT INTO Bot (user_id, username, tokens, level, xp) VALUES ('{user.Id}', '{user.Username}','100', '1', '1')");
            var table = database.FireCommand(str);

            database.CloseConnection();
        }

        public static List<Bot> GetUserStatus(IUser user)
        {
            var database = new Database("Bot");
            try
            {
                var result = new List<Bot>();

                var str = string.Format($"SELECT * FROM Bot WHERE user_id = '{user.Id}'");
                var tableName = database.FireCommand(str);

                while (tableName.Read())
                {
                    var userId = (string)tableName["user_id"];
                    var username = (string)tableName["username"];
                    var currentTokens = (uint)tableName["tokens"];
                    var currentlevel = (int)tableName["level"];
                    var currentXP = (int)tableName["xp"];
                    var daily = (DateTime)tableName["daily"];

                    result.Add(new Bot
                    {
                        UserId = userId,
                        Username = username,
                        Tokens = currentTokens,
                        Level = currentlevel,
                        XP = currentXP,
                        Daily = daily
                    });
                }
                database.CloseConnection();

                return result;
            }
            catch (Exception)
            {
                database.CloseConnection();
                return null;
            }
        }

        public static void AddTokens(IUser user, uint tokens)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot SET tokens = tokens + '{tokens}' WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Add tokens");
                return;
            }
        }

        public static void DeleteTokens(IUser user, uint tokens)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot SET tokens = tokens - '{tokens}' WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Delete tokens");
                return;
            }
        }

        public static void GetTokens(IUser user, uint tokens)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot GET tokens WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Get tokens");
                return;
            }
        }

        public static void AddXP(IUser user, int xp)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot SET xp = xp + '{xp}' WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Add xp");
                return;
            }
        }

        public static void LevelUp(IUser user, int xp)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot SET level = level + '1', xp = xp + '{xp}' WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Set level");
                return;
            }
        }

        public static void ChangeDaily(IUser user)
        {
            var database = new Database("Bot");
            try
            {
                var strings = string.Format($"UPDATE Bot SET daily = curtime() WHERE user_id = '{user.Id}'");
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Set daily");
                return;
            }
        }

        public static void SetSteamID(IUser user, string steamID)
        {
            var database = new Database("Bot");

            try
            {
                var strings = string.Format("UPDATE Bot SET steamid = '{0}' WHERE user_id = '{1}'", steamID, user.Id);
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
                return;
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Set steamID");
                return;
            }
        }

        public static List<Bot> GetSteamUserStatus(IUser user)
        {
            var result = new List<Bot>();

            var database = new Database("Bot");

            var str = string.Format("SELECT * FROM Bot WHERE user_id = '{0}'", user.Id);
            var tableName = database.FireCommand(str);

            while (tableName.Read())
            {
                var steamid = (string)tableName["steamid"];


                result.Add(new Bot
                {
                    SteamID = steamid
                });
            }
            database.CloseConnection();

            return result;
        }

        public static void SetSummonerID(IUser user, string SummonerID)
        {
            var database = new Database("Bot");

            try
            {
                var strings = string.Format("UPDATE Bot SET SummonerID = '{0}' WHERE user_id = '{1}'", SummonerID, user.Id);
                var reader = database.FireCommand(strings);
                reader.Close();
                database.CloseConnection();
                return;
            }
            catch (Exception)
            {
                database.CloseConnection();
                Console.WriteLine("Couldn't perform the mySQL action: Set SummonerID");
                return;
            }
        }
    }

    public class Databasee
    {
        private string table { get; set; }
        private const string server = "localhost";
        private const string database = "datalol";
        private const string username = "aaaa";
        private const string password = "aaaa";
        private MySqlConnection dbConnection;

        public Databasee(string table)
        {
            this.table = table;
            MySqlConnectionStringBuilder stringBuilder = new MySqlConnectionStringBuilder();
            stringBuilder.Server = server;
            stringBuilder.UserID = username;
            stringBuilder.Password = password;
            stringBuilder.Database = database;
            stringBuilder.SslMode = MySqlSslMode.None;

            var connectionString = stringBuilder.ToString();

            dbConnection = new MySqlConnection(connectionString);

            dbConnection.Open();
        }

        public MySqlDataReader FireCommand(string query)
        {
            if (dbConnection == null)
            {
                return null;
            }

            MySqlCommand command = new MySqlCommand(query, dbConnection);

            var mySqlReader = command.ExecuteReader();

            return mySqlReader;
        }

        public void CloseConnection()
        {
            if (dbConnection != null)
            {
                dbConnection.Close();
            }
        }

        public static List<String> CheckExistingChamp(int Id)
        {
            var result = new List<String>();
            var databasee = new Database("datalol");

            var str = string.Format($"SELECT * FROM datalol WHERE Id = '{Id}'");
            var tableName = databasee.FireCommand(str);

            while (tableName.Read())
            {
                var Idd = (string)tableName["Id"];

                result.Add(Idd);
            }

            databasee.CloseConnection();

            return result;
        }

        public static void EnterChamp(int Id, string name)
        {
            var databasee = new Databasee("datalol");

            var str = string.Format($"INSERT INTO datalol (Id, name) VALUES ('{Id}', '{name}')");
            var table = databasee.FireCommand(str);

            databasee.CloseConnection();
        }
    }
}

/*my error :

MySql.Data.MySqlClient.MySqlException: Authentication to host 'localhost' for user 'aaaa' using method 'mysql_native_password' failed with message: Unknown database 'datalol' ---> MySql.Data.MySqlClient.MySqlException: Unknown database 'datalol'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.ReadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.AuthenticationFailed(Exception ex)
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.ReadPacket()
   at MySql.Data.MySqlClient.Authentication.MySqlAuthenticationPlugin.Authenticate(Boolean reset)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
   at MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at MyBot.Modules.Public.Databasee..ctor(String table) in E:\jeu\VisualStudio\project\MyBot\MyBot\src\MyBot\Modules\Public\Database.cs:line 323
   at MyBot.Modules.Public.Databasee.EnterChamp(Int32 Id, String name) in E:\jeu\VisualStudio\project\MyBot\MyBot\src\MyBot\Modules\Public\Database.cs:line 370
   at MyBot.Modules.Public.Riot.Riot.<SummonersAsync>d__0.MoveNext() in E:\jeu\VisualStudio\project\MyBot\MyBot\src\MyBot\Modules\Public\Riot\Riot.cs:line 59*/