using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;

using MySql.Data.MySqlClient;

namespace GameServer
{
    public static class DB
    {
        private static string strConnection;

        static void dbConnection_StateChange(object usr, StateChangeEventArgs ev)
        {
            if (ev.CurrentState == ConnectionState.Broken)
            {
                System.Threading.Thread.Sleep(1000);
                dbConnection.Close();
            }

            if (ev.CurrentState == ConnectionState.Closed)
            {
                System.Threading.Thread.Sleep(1000);

                Log.WriteLine("Reconnecting to SQL Server 1...");
                dbConnection = new MySqlConnection(strConnection);
                dbConnection.StateChange += new System.Data.StateChangeEventHandler(dbConnection_StateChange);
                System.Threading.Thread.Sleep(2000);
                dbConnection.Open();
                Log.WriteLine("Reconnection to database 1 successful.");
            }
        }

        private static MySqlConnection dbConnection;
        #region Database connection management
        /// <summary>
        /// Opens connection to the MySQL database with the supplied parameters, and returns a 'true' boolean when the connection has succeeded. Requires MySQL MySql 5.1 driver to be installed.
        /// </summary>
        /// <param name="dbHost">The hostname/IP address where the database server is located.</param>
        /// <param name="dbPort">The port the database server is running on.</param>
        /// <param name="dbName">The name of the data</param>
        /// <param name="dbUsername">The username for authentication with the data</param>
        /// <param name="dbPassword">The pasword for authentication with the data</param>
        public static bool openConnection(string dbHost, int dbPort, string dbName, string dbUsername, string dbPassword, int dbPoolsize)
        {
            try
            {
                Log.WriteLine("Connecting to " + dbName + " at " + dbHost + ":" + dbPort + " for user '" + dbUsername + "'");
                strConnection = "Server=" + dbHost + ";Port=" + dbPort + ";Database=" + dbName + ";User=" + dbUsername + ";Password=" + dbPassword + ";Pooling=Yes;Min pool size=0;Max pool size=" + dbPoolsize + ";Connection timeout=1;";
                dbConnection = new MySqlConnection(strConnection);
                dbConnection.StateChange += new System.Data.StateChangeEventHandler(dbConnection_StateChange);
                dbConnection.Open();
                if (dbConnection.State == ConnectionState.Open)
                {
                    Log.WriteLine("Connection to database successfull.");
                    return true;
                }
                else
                {
                    Log.WriteError("Failed to connect to " + dbName + " at " + dbHost + ":" + dbPort + " for user '" + dbUsername + "'");
                    return false;
                }
            }

            catch (Exception ex)
            {
                Log.WriteError("Failed to connect! Error thrown was: " + ex.Message);
                return false;
            }
        }
        /// <summary>
        /// Closes connection with the MySQL data Any errors are ignored.
        /// </summary>
        public static void closeConnection()
        {
            Log.WriteLine("Closing database connection...");
            try
            {
                dbConnection.Close();
                Log.WriteLine("Database connection closed.");
            }
            catch { Log.WriteError("No database connection."); }
        }
        #endregion

        #region Database data manipulation
        /// <summary>
        /// Executes a SQL statement on the data
        /// </summary>
        /// <param name="Query">The SQL statement to be executed. Default SQL syntax.</param>
        public static void runQuery(string Query)
        {
            try
            {
                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex) { Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'"); }
        }
        #endregion

        #region Database data retrieval
        #region runRead
        /// <summary>
        /// Performs a SQL query and returns the first selected field as string. Other fields are ignored.
        /// </summary>
        /// <param name="Query">The SQL query that selects a field.</param>
        public static string runRead(string Query)
        {
            try
            {
                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query + " LIMIT 1";
                        return cmd.ExecuteScalar().ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteLine("Error '" + ex.Message + "' at '" + Query + "'");
                return "";
            }
        }
        /// <summary>
        /// Performs a SQL query and returns the first selected field as integer. Other fields are ignored.
        /// </summary>
        /// <param name="Query">The SQL query that selects a field.</param>
        /// <param name="Tick">Just to differ the runRead functions; supply a null if you want to use this overload.</param>
        public static int runRead(string Query, object Tick)
        {
            try
            {
                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query + " LIMIT 1";
                        return int.Parse(cmd.ExecuteScalar().ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return 0;
            }
        }
        #endregion
        #region runReadColumn
        /// <summary>
        /// Performs a SQL query and returns all vertical matching fields as a String array. Only the first supplied columname is looked for.
        /// </summary>
        /// <param name="Query">The SQL query that selects a column.</param>
        /// <param name="maxResults">Adds as LIMIT to the query. Using this, the array will never return more than xx fields in of the column. When maxResults is supplied as 0, then there is no max limit.</param>
        public static string[] runReadColumn(string Query, int maxResults)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;

            try
            {
                ArrayList builder = new ArrayList();

                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query;
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                try { builder.Add(reader[0].ToString()); }
                                catch { builder.Add(""); }
                            }
                            reader.Close();
                            return (string[])builder.ToArray(typeof(string));
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return new string[0];
            }
        }
        /// <summary>
        /// Performs a SQL query and returns all vertical matching fields as an Integer array. Only the first supplied columname is looked for.
        /// </summary>
        /// <param name="Query">The SQL query that selects a column.</param>
        /// <param name="maxResults">Adds as LIMIT to the query. Using this, the array will never return more than xx fields in of the column. When maxResults is supplied as 0, then there is no max limit.</param>
        /// <param name="Tick">Just to differ the runReadColumn functions; supply a null if you want to use this overload.</param>
        public static int[] runReadColumn(string Query, int maxResults, object Tick)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;
            try
            {
                ArrayList builder = new ArrayList();

                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query;
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                try { builder.Add(reader.GetInt32(0)); }
                                catch { builder.Add(""); }
                            }
                            reader.Close();
                            return (int[])builder.ToArray(typeof(int));
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return new int[0];
            }
        }
        #endregion
        #region runReadRow
        /// <summary>
        /// Performs a SQL query and returns the selected in the first found row as a String array. Useable for only one row.
        /// </summary>
        /// <param name="Query">The SQL query that selects a row and the fields to get. LIMIT 1 is added.</param>
        public static string[] runReadRow(string Query)
        {
            try
            {
                ArrayList builder = new ArrayList();
                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query + " LIMIT 1";
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    try { builder.Add(reader[i].ToString()); }
                                    catch { builder.Add(""); }
                                }
                            }
                            reader.Close();
                            return (string[])builder.ToArray(typeof(string));
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return new string[0];
            }
        }
        /// <summary>
        /// Performs a SQL query and returns the selected in the first found row as an Integer array. Useable for only one row.
        /// </summary>
        /// <param name="Query">The SQL query that selects a row and the fields to get. LIMIT 1 is added.</param>
        /// <param name="Tick">Just to differ the runReadRow functions; supply a null if you want to use this overload.</param>
        public static int[] runReadRow(string Query, object Tick)
        {
            try
            {
                ArrayList builder = new ArrayList();

                using (MySqlConnection c = new MySqlConnection(strConnection))
                {
                    c.Open();
                    using (MySqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = Query + " LIMIT 1";
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    try { builder.Add(reader.GetInt32(i)); }
                                    catch { builder.Add(""); }
                                }
                            }
                            reader.Close();
                            return (int[])builder.ToArray(typeof(int));
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return new int[0];
            }
        }
        #endregion
        #endregion

        #region Data availability checks
        /// <summary>
        /// Tries to find fields matching the query. When there is at least one result, it returns True and stops.
        /// </summary>
        /// <param name="Query">The SQL query that contains the seeked fields and conditions. LIMIT 1 is added.</param>
        public static bool checkExists(string Query)
        {
            try { return new MySqlCommand(Query + " LIMIT 1", dbConnection).ExecuteReader().HasRows; }
            catch (Exception ex)
            {
                Log.WriteError("Error '" + ex.Message + "' at '" + Query + "'");
                return false;
            }
        }
        #endregion

        #region Misc
        /// <summary>
        /// Returns a stripslashed copy of the input string.
        /// </summary>
        /// <param name="Query">The string to add stripslashes to.</param>
        public static string Stripslash(string Query)
        {
            try { return Query.Replace(@"\", "\\").Replace("'", "\'").Replace("'", @"`"); }
            catch { return ""; }
        }
        #endregion
    }
}