using System;
using System.Net;
using MySql.Data.MySqlClient;
using System.Net.Mail;
using System.Threading.Tasks;
using System.Collections;

namespace MMO_SERVER
{
    public class Database
    {
        string _dbHostName = "freemysqlhosting.net";
        string _dbName = "";
        string _dbUserName = "";
        string _dbPassword = "*";
        string _dbTableName = "players";

        public Queue connectionList = new Queue ();
        public int connectionPoolSize = 10;

        MySqlConnection _dbConnection;
        static object locker = new object();

        public Database ()
        {
            try
            {
                for(int i = 0; i < connectionPoolSize; i++)
                {
                    _dbConnection = new MySqlConnection(

                        "server="   + _dbHostName + ";" +
                        "user="     + _dbUserName + ";" +
                        "database=" + _dbName     + ";" +
                        "password=" +_dbPassword  + "; " +
                        "Pooling = false;"
                    );

                    _dbConnection.OpenAsync ();
                    connectionList.Enqueue(_dbConnection);
                }

                Console.WriteLine("######### DB CONNECTION INFO ##########");
                Console.WriteLine ("DataBase: {0}", _dbName);
                Console.WriteLine("State: {0}", _dbConnection.State);
                Console.WriteLine("Timeout: {0}", _dbConnection.ConnectionTimeout);
                Console.WriteLine("Created connections: {0}", connectionPoolSize);
                Console.WriteLine("########################################");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error connection to DataBase: " + ex.Message);
            }

            //RegisterNewPlayer ("Tonny", "treeetata", "veldwolfs@gmail.com");
            //LoginUser("Tonny", "veldwolfsBAyPL");
            //ChangePasswordAsync("veldwolfs@gmail.com").GetAwaiter();

            Console.ReadKey ();
            _dbConnection.Close ();
            Console.WriteLine ("Database connection closed!");

        }

        public void GetConnnect()
        {
            lock (locker)
            {
                _dbConnection = (MySqlConnection)connectionList.Dequeue ();
            }
        }

        public void ReturnConnect()
        {
            lock (locker)
            {
                connectionList.Enqueue (_dbConnection);
            }
        }

        private bool CheckUserName(string _userName)
        {
            string checkSQL = "SELECT `name` FROM `"+ _dbTableName +"`" +
                "WHERE `name` = @Name";

            MySqlCommand checkCommand = new MySqlCommand (checkSQL, _dbConnection);

            checkCommand.Parameters.Add("@Name", MySqlDbType.VarChar, 20);
            checkCommand.Parameters["@Name"].Value = _userName;

            string result = (String)checkCommand.ExecuteScalar ();

            if (result == _userName)
                return true;
            else
                return false;
        }

        private bool CheckUserEmail(string _userEmail)
        {
            string checkSQL = "SELECT `email` FROM `"+ _dbTableName +"`" +
                "WHERE `email` = @Email";

            MySqlCommand checkCommand = new MySqlCommand (checkSQL, _dbConnection);

            checkCommand.Parameters.Add("@Email", MySqlDbType.VarChar, 30);
            checkCommand.Parameters["@Email"].Value = _userEmail;

            string result = (String)checkCommand.ExecuteScalar ();

            if (result == _userEmail)
                return true;
            else
                return false;
        }

        private bool CheckUser(string _userName, string _userPassword)
        {

            string checkSQL = "SELECT `name`, `password` FROM `"+ _dbTableName +"`" +
                "WHERE `name` = @Name";

            MySqlCommand checkCommand = new MySqlCommand (checkSQL, _dbConnection);

            checkCommand.Parameters.Add("@Name", MySqlDbType.VarChar, 20);
            checkCommand.Parameters["@Name"].Value = _userName;

            string _readName = null;
            string _readPass = null;

            MySqlDataReader MyDataReader = checkCommand.ExecuteReader();

            while (MyDataReader.Read())
            {
                _readName = MyDataReader.GetString(0);
                _readPass = MyDataReader.GetString(1);
            }


            MyDataReader.Close();

            if (_readName == _userName && SecurityManager.VerifyHashedPassword(_readPass, _userPassword))
                return true;
            else
                return false;
        }

        /*
            Сделать на стороне клиета проверку заполняемых полей на наличие пробелов и запрещенных символов,
            наличие в пароле не мение 6 символов
            наличие в имени не мение 3 символов
            наличие @ в емейле
        */
        public void RegisterNewPlayer(string _name, string _password, string _email)
        {
            if (CheckUserName (_name))
                Console.WriteLine ("User name already registered!");
            else if (CheckUserEmail (_email))
                Console.WriteLine ("User email already registered!");
            else
            {
                _password = SecurityManager.HashPassword (_password);

                string registerSQL = "INSERT INTO `"+ _dbTableName +"`(`name`, `password`, `email`) " +
                    "VALUES (@Name, @Password, @Email)";

                MySqlCommand regCommand = new MySqlCommand(registerSQL, _dbConnection);

                regCommand.Parameters.Add("@Name", MySqlDbType.VarChar, 20);
                regCommand.Parameters["@Name"].Value = _name;
                regCommand.Parameters.Add("@Password", MySqlDbType.VarChar, 255);
                regCommand.Parameters["@Password"].Value = _password;
                regCommand.Parameters.Add("@Email", MySqlDbType.VarChar, 30);
                regCommand.Parameters["@Email"].Value = _email;

                regCommand.ExecuteNonQuery ();

                Console.WriteLine (string.Format("Registered new player: nickname = {0}, email = {1}", _name, _email));
            }
        }

        public void LoginUser(string _name, string _password)
        {
            if (CheckUser (_name, _password))
            {
                Console.WriteLine ("User: [" + _name + "] join to server!");
            }
            else
                Console.WriteLine ("Incorrect name or password!");
        }

        private async Task ChangePasswordAsync(string _mail)
        {
            MailAddress from = new MailAddress("v", "");
            MailAddress to = new MailAddress(_mail);
            MailMessage m = new MailMessage(from, to);
            m.Subject = "Reset password";
            m.Body = "Your request on change password is apply. Your new password is: " + SetNewPassword(_mail);
            SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587);
            smtp.Credentials = new NetworkCredential("", "");
            smtp.EnableSsl = true;
            await smtp.SendMailAsync(m);
            Console.WriteLine("Message send. User [" + _mail + "] changed password!");
        }

        private string SetNewPassword(string _user)
        {
            string newPassword = _user.Split("@"[0])[0] + GetPass (5);

            string changePasswordSQL = "UPDATE `"+ _dbTableName +"` SET `password` = @Password WHERE `email` = @Email";

            MySqlCommand regCommand = new MySqlCommand(changePasswordSQL, _dbConnection);
            regCommand.Parameters.Add("@Password", MySqlDbType.VarChar, 255);
            regCommand.Parameters["@Password"].Value = SecurityManager.HashPassword (newPassword);
            regCommand.Parameters.Add("@Email", MySqlDbType.VarChar, 30);
            regCommand.Parameters["@Email"].Value = _user;
            regCommand.ExecuteNonQuery ();

            return newPassword;
        }

        private string GetPass(int x)
        {
            string pass = "";
            var r = new Random();
            while (pass.Length < x)
            {
                Char c = (char)r.Next(33, 125);
                if (Char.IsLetterOrDigit(c))
                    pass += c;
            }
            return pass;
        }
    }
}