using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace LFS_External_Client
{
    class dbProcess
    {

        static public string dbMasterDatabaseName = "oldskool";
        static public string dbTableMasterPrefix = "cpc_";
        static public bool dbSpecialTable = false;
        static public string dbSpecialTableName = "special";
        static public bool dbTableTrackMatters = false;
        static public bool dbTableServerNameMatters = true;
        static public bool dbTableServerNumberMatters = false;

        static public string dbServer = "localhost";
        static public string dbPort = "3306";
        static public string dbUser = "root";
        static public string dbPass = "rootpass";

        static public bool dbConnectCheck()
        {
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be opened for command
                try
                {
                    dbConnection.Close();
                }
                catch
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7Database connection couldn't be closed!");
                }
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                }
                return true;
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
        }

        static public bool userExists(string Username)
        {
            #region dbFailurePrecautions
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            int personalId = 0;
            MySqlCommand cmd = new MySqlCommand("SELECT id FROM osc_users WHERE username='" + Username + "'", dbConnection);
            try
            {
                cmd.Prepare();
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    personalId = reader.GetInt32("id");
                }
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
            }
            dbConnection.Close();

            if (personalId > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        static public bool userRegister(string Username, string Nickname)
        {
            #region dbFailurePrecautions
            if (globalVars.dbBlockOutgoing == true)
            {
                return false;
            }
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                    dbConnection.Close();
                    return false;
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            MySqlCommand cmd = new MySqlCommand("INSERT INTO osc_users (username, nickname, webkey, firstseen, lastseen) VALUES ('" + Username + "', '" + Nickname + "', '" + Username.GetHashCode() + "', '" + DateTime.Now.ToBinary() + "', '" + DateTime.Now.ToBinary() + "')", dbConnection);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
                dbConnection.Close();
                return false;
            }
            dbConnection.Close();
            return true;
        }

        static public bool userUpdateStats(clsConnection C)
        {
            #region dbFailurePrecautions
            if (globalVars.dbBlockOutgoing == true)
            {
                return false;
            }
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                    dbConnection.Close();
                    return false;
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            MySqlCommand cmd = new MySqlCommand("UPDATE osc_users SET " +
                "nickname='" + C.PlayerName + "', " +
                "cars='" + C.cars + "', " +
                "distance='" + C.distanceTotal + "', " +
                "money_pocket='" + C.money + "', " +
                "money_bank='" + C.moneyBank + "', " +
                "health='" + C.health + "', " +
                "WHERE id='" + C.personalId + "'", dbConnection);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
                dbConnection.Close();
                return false;
            }
            dbConnection.Close();
            return true;
        }

        static public bool userResetStats(clsConnection C)
        {
            #region dbFailurePrecautions
            if (globalVars.dbBlockOutgoing == true)
            {
                return false;
            }
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                    dbConnection.Close();
                    return false;
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            MySqlCommand cmd = new MySqlCommand("UPDATE osc_users SET " +
                "nickname='" + C.PlayerName + "', " +
                "cars='000000000000000000000', " +
                "distance='0', " +
                "money_pocket='6300', " +
                "money_bank='1000', " +
                "health='100000', " +
                "WHERE id='" + C.personalId + "'", dbConnection);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
                dbConnection.Close();
                return false;
            }
            dbConnection.Close();
            return true;
        }

        static public bool userGetStats(clsConnection C)
        {
            #region dbFailurePrecautions
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                    dbConnection.Close();
                    return false;
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            MySqlCommand cmd = new MySqlCommand("SELECT * FROM osc_users WHERE username='" + C.Username + "'", dbConnection);
            try
            {
                cmd.Prepare();
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    C.personalId = reader.GetInt32("id");
                    C.cars = reader.GetString("cars");
                    C.distanceTotal = reader.GetInt32("distance");
                    C.money = reader.GetInt32("money_pocket");
                    C.moneyBank = reader.GetInt32("money_bank");
                    C.health = reader.GetInt32("health");

                    C.carsPrev = C.cars;
                    C.moneyInitial = C.money;
                    C.moneyPrev = C.money;
                    C.distanceTotalPrev = C.distanceTotal;
                }
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
                dbConnection.Close();
                return false;
            }
            dbConnection.Close();
            return true;
        }

        static public bool userAddStats(clsConnection C)
        {
            #region dbFailurePrecautions
            string dbConnInfo = "server=" + dbServer + ";userid=" + dbUser + ";password=" + dbPass + ";database=" + dbMasterDatabaseName;
            MySqlConnection dbConnection = new MySqlConnection(dbConnInfo);
            try
            {
                dbConnection.Open(); // connection must be openned for command
                if (globalVars.dbCanConnect == false)
                {
                    globalVars.dbBlockOutgoing = true;
                    globalVars.dbCanConnect = true;
                    Form1.InSim.Send_MST_Message("/msg ^8» ^3Attention: ^7Database opened successfully!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1All outgoing database connections blocked!");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^7Synchronizing users' stats with database!");
                    try
                    {
                        Form1.InSim.Request_NCN_AllConnections(30);
                        globalVars.dbBlockOutgoing = false;
                    }
                    catch
                    {
                        Form1.InSim.Send_MST_Message("/msg ^8» ^1Error: ^7C. list was not be rebuilt!");
                    }
                    dbConnection.Close();
                    return false;
                }
            }
            catch
            {
                if (globalVars.dbCanConnect == true)
                {
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Critical error: ^7Database could not be open.");
                    Form1.InSim.Send_MST_Message("/msg ^8» ^1Warning: ^7Stats system not available.");
                    globalVars.dbCanConnect = false;
                }
                return false;
            }
            #endregion

            MySqlCommand cmd = new MySqlCommand("SELECT * FROM osc_users WHERE username='" + C.Username + "'", dbConnection);
            try
            {
                cmd.Prepare();
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    C.personalId = reader.GetInt32("id");
                    C.cars = misc.matrixMerge(C.cars, reader.GetString("cars"), false);
                    C.distanceTotal += reader.GetInt32("distance");
                    C.money += reader.GetInt32("money_pocket");
                    C.moneyBank += reader.GetInt32("money_bank");
                    C.health += reader.GetInt32("health");

                    C.carsPrev = C.cars;
                    C.moneyPrev = C.money;
                    C.moneyPrev = C.money;
                    C.distanceTotal = C.distanceTotal;
                }
            }
            catch (Exception E)
            {
                misc.manageHandledException(E);
                dbConnection.Close();
                return false;
            }
            dbConnection.Close();
            return true;
        }
    }
}