using System;
using System.Data.Odbc;
using System.Collections;

using Woodpecker.Core;
namespace Woodpecker.Data
{
    /// <summary> 
    /// Provides high speed data access to the MySQL database.
    /// </summary> 
    public static class Database
    {
        private static OdbcConnection 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 ODBC 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 database.</param> 
        /// <param name="dbUsername">The username for authentication with the database.</param> 
        /// <param name="dbPassword">The pasword for authentication with the database.</param> 
        public static bool openConnection(string dbHost, int dbPort, string dbName, string dbUsername, string dbPassword)
        {
            try
            {
                IO.PrintLine("Connecting to " + dbName + " at " + dbHost + ":" + dbPort + " for user '" + dbUsername + "'", IO.logImportancies.Info);
                dbConnection = new OdbcConnection("Driver={MySQL ODBC 5.1 Driver};Server=" + dbHost + ";Port=" + dbPort + ";Database=" + dbName + ";User=" + dbUsername + ";Password=" + dbPassword + ";Option=3;");
                dbConnection.Open();
                IO.PrintLine("Connection to database successfull.", IO.logImportancies.Info);
                return true;
            }
            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                return false;
            }
        }
        /// <summary> 
        /// Closes connection with the MySQL database. Any errors are ignored. 
        /// </summary> 
        public static void closeConnection()
        {
            IO.PrintLine("Closing database connection...", IO.logImportancies.Info);
            try
            {
                dbConnection.Close();
                IO.PrintLine("Database connection closed.", IO.logImportancies.Info);
            }
            catch { IO.PrintLine("No database connection.", IO.logImportancies.Info); }
        }
        #endregion

        #region Database data manipulation
        #region runQuery
        /// <summary> 
        /// Executes a raw SQL statement on the database. 
        /// </summary> 
        /// <param name="Query">The SQL statement to be executed. Default SQL syntax.</param> 
        public static void runQuery(string Query)
        {
            try { new OdbcCommand(Query, dbConnection).ExecuteScalar(); }
            catch (Exception ex) { IO.PrintLine("Error '" + ex.Message + "' at '" + Query + "'", IO.logImportancies.Error); }
        }
        /// <summary>
        /// Executes a SQL statement with given parameters at the database.
        /// </summary>
        /// <param name="paramIDs">The parameters, eg, name, password etc.</param>
        /// <param name="paramValues">The values, eg, 'Woody', 'pass'.</param>
        /// <param name="Query">The query with the parameters included, eg, UPDATE users SET password = @password WHERE username = @name LIMIT 1.</param>
        public static void runQuery(string[] paramIDs, object[] paramValues, string Query)
        {
            try
            {
                OdbcCommand cmd = new OdbcCommand(Query, dbConnection);
                for (int i = 0; i < paramIDs.Length; i++)
                    cmd.Parameters.AddWithValue("@" + paramIDs[i], paramValues[i]);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { IO.PrintLine(ex.Message + ", query: " + Query,IO.logImportancies.Error); }
        }
        #endregion
        #endregion

        #region Database data retrieval
        #region runRead
        /// <summary> 
        /// Performs a raw 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 { return new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar().ToString(); }
            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                return "";
            }
        }
        /// <summary>
        /// Performs a SQL query with given parameters at the database and returns the first selected field as string. Other fields are ignored.
        /// </summary>
        /// <param name="paramIDs">The parameters, eg, name, @assword etc.</param>
        /// <param name="paramValues">The values, eg, 'Woody', 'pass'.</param>
        /// <param name="Query">The query with the parameters included, eg, SELECT figure FROM users WHERE username = @name AND password = @password.</param>
        public static string runReadString(string[] paramIDs, object[] paramValues, string Query)
        {
            try 
            {
                OdbcCommand cmd = new OdbcCommand(Query + " LIMIT 1", dbConnection);
                for (int i = 0; i < paramIDs.Length; i++)
                    cmd.Parameters.AddWithValue("@" + paramIDs[i], paramValues[i]);
                return cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                return "";
            }
        }
        /// <summary> 
        /// Performs a raw 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> 
        public static int runReadInteger(string Query, object Tick)
        {
            try { return Convert.ToInt32(new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar()); }
            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                return 0;
            }
        }
        /// <summary>
        /// Performs a SQL query with given parameters at the database and returns the first selected field as an integer. Other fields are ignored.
        /// </summary>
        /// <param name="paramIDs">The parameters, eg, name, password etc.</param>
        /// <param name="paramValues">The values, eg, 'Woody', 'pass'.</param>
        /// <param name="Query">The query with the parameters included, eg, SELECT credits FROM users WHERE username = @name AND password = @password.</param>
        public static int runReadInteger(string[] paramIDs, object[] paramValues, string Query)
        {
            try
            {
                OdbcCommand cmd = new OdbcCommand(Query + " LIMIT 1", dbConnection);
                for (int i = 0; i < paramIDs.Length; i++)
                    cmd.Parameters.AddWithValue("@" + paramIDs[i], paramValues[i]);
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                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[] runReadColumnStrings(string Query, int maxResults)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;

            try
            {
                ArrayList columnBuilder = new ArrayList();
                OdbcDataReader columnReader = new OdbcCommand(Query, dbConnection).ExecuteReader();

                while (columnReader.Read())
                {
                    try { columnBuilder.Add(columnReader[0].ToString()); }
                    catch { columnBuilder.Add(""); }
                }
                columnReader.Close();

                return (string[])columnBuilder.ToArray(typeof(string));
            }

            catch (Exception ex)
            {
                IO.PrintLine("Error '" + ex.Message + "' at '" + Query + "'", IO.logImportancies.Error);
                return new string[0];
            }
        }
        /// <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="paramIDs">The parameters, eg, name, motto etc.</param>
        /// <param name="paramValues">The values, eg, 'Woody', 'wut?!'.</param>
        /// <param name="Query">The query with the parameters included, eg, SELECT figure FROM users WHERE username = @name OR motto = @motto.</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[] runReadColumnStrings(string[] paramIDs, object[] paramValues,string Query, int maxResults)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;

            try
            {
                ArrayList columnBuilder = new ArrayList();
                OdbcCommand cmd = new OdbcCommand(Query, dbConnection);
                for (int i = 0; i < paramIDs.Length; i++)
                    cmd.Parameters.AddWithValue("@" + paramIDs[i], paramValues[i]);
                
                OdbcDataReader columnReader = cmd.ExecuteReader();
                while (columnReader.Read())
                {
                    try { columnBuilder.Add(columnReader[0].ToString()); }
                    catch { columnBuilder.Add(""); }
                }
                columnReader.Close();

                return (string[])columnBuilder.ToArray(typeof(string));
            }

            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                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> 
        public static int[] runReadColumnIntegers(string Query, int maxResults)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;

            try
            {
                ArrayList columnBuilder = new ArrayList();
                OdbcDataReader columnReader = new OdbcCommand(Query, dbConnection).ExecuteReader();

                while (columnReader.Read())
                {
                    try { columnBuilder.Add((int)columnReader[0]); }
                    catch { columnBuilder.Add(0); }
                }
                columnReader.Close();

                return (int[])columnBuilder.ToArray(typeof(int));
            }

            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                return new int[0];
            }
        }
        /// <summary> 
        /// Performs a SQL query and returns all vertical matching fields as an integer array array. Only the first supplied columname is looked for. 
        /// </summary> 
        /// <param name="paramIDs">The parameters, eg, name, motto etc.</param>
        /// <param name="paramValues">The values, eg, 'Woody', 'wut?!'.</param>
        /// <param name="Query">The query with the parameters included, eg, SELECT credits FROM users WHERE username = @name OR motto = @motto.</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 int[] runReadColumnIntegers(string[] paramIDs, object[] paramValues, string Query, int maxResults)
        {
            if (maxResults > 0)
                Query += " LIMIT " + maxResults;

            try
            {
                ArrayList columnBuilder = new ArrayList();
                OdbcCommand cmd = new OdbcCommand(Query, dbConnection);
                for (int i = 0; i < paramIDs.Length; i++)
                    cmd.Parameters.AddWithValue("@" + paramIDs[i], paramValues[i]);

                OdbcDataReader columnReader = cmd.ExecuteReader();
                while (columnReader.Read())
                {
                    try { columnBuilder.Add((int)columnReader[0]); }
                    catch { columnBuilder.Add(""); }
                }
                columnReader.Close();

                return (int[])columnBuilder.ToArray(typeof(int));
            }

            catch (Exception ex)
            {
                IO.PrintLine(ex.Message, IO.logImportancies.Error);
                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 rowBuilder = new ArrayList();
                OdbcDataReader rowReader = new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteReader();

                while (rowReader.Read())
                {
                    for (int i = 0; i < rowReader.FieldCount; i++)
                    {
                        try { rowBuilder.Add(rowReader[i].ToString()); }
                        catch { rowBuilder.Add(""); }
                    }
                }
                rowReader.Close();
                return (string[])rowBuilder.ToArray(typeof(string));
            }

            catch (Exception ex)
            {
                IO.PrintLine("Error '" + ex.Message + "' at '" + Query + "'", IO.logImportancies.Error);
                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 rowBuilder = new ArrayList();
                OdbcDataReader rowReader = new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteReader();

                while (rowReader.Read())
                {
                    for (int i = 0; i < rowReader.FieldCount; i++)
                    {
                        try { rowBuilder.Add(rowReader.GetInt32(i)); }
                        catch { rowBuilder.Add(0); }
                    }
                }
                rowReader.Close();
                return (int[])rowBuilder.ToArray(typeof(int));
            }

            catch (Exception ex)
            {
                IO.PrintLine("Error '" + ex.Message + "' at '" + Query + "'", IO.logImportancies.Error);
                return new int[0];
            }
        }
        /// <summary>
        /// Performs a SQL query and returns the result as a string. On error, no error is reported and "" is returned.
        /// </summary>
        /// <param name="Query">The SQL query to run. LIMIT 1 is added.</param>
        public static string runReadUnsafe(string Query)
        {
            try { return new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar().ToString(); }
            catch { return ""; }
        }
        /// <summary>
        /// Performs a SQL query and returns the result as an integer. On error, no error is reported and 0 is returned.
        /// </summary>
        /// <param name="Query">The SQL query to run. LIMIT 1 is added.</param>
        /// <param name="Tick">Just to differ the runReadUnsafe functions; supply a null if you want to use this overload.</param> 
        public static int runReadUnsafe(string Query, object Tick)
        {
            try { return Convert.ToInt32(new OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteScalar()); }
            catch { return 0; }
        }
        #endregion
        #endregion

        #region Data availability checks
        public static bool checkExists(string Table, string Field, string fieldValue)
        {
            try { return new OdbcCommand("SELECT " + Field + " FROM " + Table + " WHERE " + Field + "=" + fieldValue + " LIMIT 1", dbConnection).ExecuteReader().HasRows; }
            catch { return false; }
        }
        /// <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 OdbcCommand(Query + " LIMIT 1", dbConnection).ExecuteReader().HasRows; }
            catch (Exception ex)
            {
                IO.PrintLine("Error '" + ex.Message + "' at '" + Query + "'", IO.logImportancies.Error);
                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("'", @"\'"); }
            catch { return ""; }
        }
        #endregion
    }
}