using System;
using System.Data.Odbc;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Net;

using Server;

namespace Server.Vorspire.MySQL
{
    public partial class Config
    {
        public static bool
            /// <summary>
            /// Extended Errors
            /// If this system, or any components of, throw any exceptions, they will be displayed in the Console window.
            /// </summary>
            ExtendedErrors = false;

        public const string
            SystemName = "MySQL";
    }

    /// <summary>
    /// ODBC Driver Versions
    /// </summary>
    public enum ODBCDriver
    {
        /// <summary>
        /// Version 3.51
        /// </summary>
        _3_51 = 0x00000000,
        /// <summary>
        /// Version 5.1
        /// </summary>
        _5_1 = 0x00000001,
    }

    /// <summary>
    /// Provides a MySQL Result structure for storing MySQL query get-data.
    /// </summary>
    public struct MySQLResult
    {
        private int _ROW;
        /// <summary>
        /// Row ID of this result
        /// </summary>
        public int Row { get { return _ROW; } }

        private string _KEY;
        /// <summary>
        /// Row Column/Field Name of this result
        /// </summary>
        public string Key { get { return _KEY; } }

        private object _VAL;
        /// <summary>
        /// Value of this result
        /// </summary>
        public object Value { get { return _VAL; } }

        /// <summary>
        /// Creates a MySQLResult structure object
        /// </summary>
        /// <param name="rowID">Row ID of this result</param>
        /// <param name="key">Row Column/Field Name of this result</param>
        /// <param name="value">Value of this result</param>
        public MySQLResult(int rowID, string key, object value)
        {
            _ROW = rowID;
            _KEY = key;
            _VAL = value;
        }

        public override string ToString()
        {
            return String.Format("[{0}][{1}] => '{2}'", _ROW, _KEY, _VAL);
        }
    }

    /// <summary>
    /// Provides a MySQL Data structure for storing MySQL query put-data.
    /// </summary>
    public struct MySQLData
    {
        private string _KEY;
        /// <summary>
        /// Row Column/Field Name of this result
        /// </summary>
        public string Key { get { return _KEY; } }

        private string _VAL;
        /// <summary>
        /// Value of this result
        /// </summary>
        public string Value { get { return _VAL; } }

        /// <summary>
        /// Creates a MySQLData structure object
        /// </summary>
        /// <param name="key">Column/Field Name of this data</param>
        /// <param name="value">Value of this data</param>
        public MySQLData(string key, string value)
        {
            _KEY = key;
            _VAL = value;
        }

        public override string ToString()
        {
            return String.Format("[{0}] => '{1}'", _KEY, _VAL);
        }
    }

    /// <summary>
    /// Provides a basic MySQL Connection Class that wraps the OdbcConnection class.
    /// </summary>
    public class MySQLConnection
    {
        private string _IP;
        /// <summary>
        /// MySQL Server IP Address
        /// </summary>
        public string IP { get { return _IP; } }

        private string _USER;
        /// <summary>
        /// MySQL Access User Name
        /// </summary>
        public string User { get { return _USER; } }

        private string _PASS;
        /// <summary>
        /// MySQL Access Password
        /// </summary>
        public string Password { get { return _PASS; } }

        private string _DBNAME;
        /// <summary>
        /// MySQL Database Name
        /// </summary>
        public string DBName { get { return _DBNAME; } }

        private string _MESSAGE;
        /// <summary>
        /// OdbcConnection extension: Message
        /// </summary>
        public string Message { get { return _MESSAGE; } }

        private OdbcError[] _ERRORS;
        /// <summary>
        /// OdbcConnection extension: Errors
        /// </summary>
        public OdbcError[] Errors { get { return _ERRORS; } }

        private ODBCDriver _DRIVER;
        /// <summary>
        /// OdbcConnection Driver Version
        /// </summary>
        public ODBCDriver Driver { get { return _DRIVER; } }

        private OdbcConnection _CONNECTION;
        /// <summary>
        /// OdbcConnection Instance
        /// </summary>
        public OdbcConnection Connection { get { return _CONNECTION; } }

        /// <summary>
        /// Returns true if the Connection is Open
        /// </summary>
        public bool Connected { get { return (_CONNECTION != null && _CONNECTION.State == System.Data.ConnectionState.Open); } }

        /// <summary>
        /// Returns true if the Connection contains Errors
        /// </summary>
        public bool HasError { get { return (_ERRORS != null && _ERRORS.Length > 0); } }

        /// <summary>
        /// Creates a MySQL Connection object
        /// </summary>
        /// <param name="ip">MySQL Server IP Address</param>
        /// <param name="user">MySQL Access User Name</param>
        /// <param name="pass">MySQL Access Password</param>
        /// <param name="dbName">MySQL Database Name</param>
        /// <param name="driver">OdbcConnection Driver Version</param>
        public MySQLConnection(string ip, string user, string pass, string dbName, ODBCDriver driver)
        {
            _IP = ip;
            _USER = user;
            _PASS = pass;
            _DBNAME = dbName;
            _DRIVER = driver;
        }

        /// <summary>
        /// Called when the Connection instance receives any messages.
        /// Resets the MySQLConnection object Message and Errors members.
        /// </summary>
        /// <param name="sender">Sender object</param>
        /// <param name="e">Odbc Message Event Arguments</param>
        private void OnMessage(object sender, OdbcInfoMessageEventArgs e)
        {
            if (e.Errors != null && e.Errors.Count > 0)
            {
                OdbcErrorCollection errList = e.Errors;

                _ERRORS = new OdbcError[errList.Count];

                for (int i = 0; i < errList.Count; i++)
                {
                    _ERRORS[i] = errList[i];
                }
            }
            else
            {
                _ERRORS = null;
            }

            if (e.Message != null && e.Message.Length > 0)
            {
                _MESSAGE = e.Message;
            }
            else
            {
                _MESSAGE = null;
            }
        }

        /// <summary>
        /// Attempts to connect to the specified MySQL Server with the given settings.
        /// </summary>
        /// <param name="retries">Retry connection attempts this many times if the initial connection fails.</param>
        /// <returns>True if connection successful</returns>
        public bool Connect(int retries)
        {
            string tmp = _DRIVER.ToString();

            tmp = tmp.Remove(0, 1);
            tmp = tmp.Replace('_', '.');

            string driver = "{" + String.Format("MySQL ODBC {0} Driver", tmp) + "}";

            string con = String.Format("DRIVER={0};SERVER={1};DATABASE={2};UID={3};PASSWORD={4};", driver, _IP, _DBNAME, _USER, _PASS);

            try
            {
                if (_CONNECTION == null)
                {
                    _CONNECTION = new OdbcConnection(con);
                    _CONNECTION.InfoMessage += new OdbcInfoMessageEventHandler(OnMessage);
                    _CONNECTION.Open();

                    Console.WriteLine("[" + Config.SystemName + "] Connection Attempt.");
                    while (_CONNECTION.State == System.Data.ConnectionState.Connecting)
                    { }

                    if (_CONNECTION.State == System.Data.ConnectionState.Broken)
                        _CONNECTION.Close();

                    if (_CONNECTION.State == System.Data.ConnectionState.Open)
                    {
                        Console.WriteLine("[" + Config.SystemName + "] Connection Successful.");
                        return true;
                    }
                }

                if (_CONNECTION.State == System.Data.ConnectionState.Broken)
                    _CONNECTION.Close();

                if (_CONNECTION.State == System.Data.ConnectionState.Closed)
                {
                    for (int i = 0; i < retries; i++)
                    {
                        _CONNECTION.Open();

                        Console.WriteLine("[" + Config.SystemName + "] Connection Attempt {0}.", i + 1);
                        while (_CONNECTION.State == System.Data.ConnectionState.Connecting)
                        { }

                        if (_CONNECTION.State == System.Data.ConnectionState.Broken)
                            _CONNECTION.Close();

                        if (_CONNECTION.State == System.Data.ConnectionState.Open)
                        {
                            Console.WriteLine("[" + Config.SystemName + "] Connection Successful.");
                            return true;
                        }
                    }
                }

                if (_CONNECTION.State == System.Data.ConnectionState.Broken)
                    _CONNECTION.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("[" + Config.SystemName + "] Connection Failed.");

                if (Config.ExtendedErrors)
                    Console.WriteLine("[" + Config.SystemName + "] {0}", ex);
            }

            Console.WriteLine("[" + Config.SystemName + "] Connection Failed.");
            Close();
            return false;
        }

        /// <summary>
        /// Switches to a different Database Name on the connected MySQL Server
        /// </summary>
        /// <param name="dbName">Database Name to switch to</param>
        /// <returns>True if switch successful</returns>
        public bool SwitchDatabase(string dbName)
        {
            if (!Connected)
            {
                _DBNAME = dbName;
                return true;
            }

            _DBNAME = dbName;

            string query = String.Format("USE `{0}`", _DBNAME);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            bool ret = (cmd.ExecuteNonQuery() > 0);

            cmd.Dispose();

            return ret;
        }

        /// <summary>
        /// Gets the minimum value of a given Column/Field Name within the given Table Name
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="key">Name of the Column/Field to lookup</param>
        /// <returns>The minimum value reported by MySQL</returns>
        public int GetMinValue(string table, string key)
        {
            if (!Connected)
                return 0;

            string query = String.Format("SELECT MIN({0}) FROM `{1}`", key, table);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            OdbcDataReader reader = cmd.ExecuteReader();

            int min = 0;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    min = reader.GetInt32(0);
                }

                reader.Close();
                reader.Dispose();
            }

            cmd.Dispose();

            return min;
        }

        /// <summary>
        /// Gets the maximum value of a given Column/Field Name within the given Table Name
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="key">Name of the Column/Field to lookup</param>
        /// <returns>The maximum value reported by MySQL</returns>
        public int GetMaxValue(string table, string key)
        {
            if (!Connected)
                return 0;

            string query = String.Format("SELECT MAX({0}) FROM `{1}`", key, table);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            OdbcDataReader reader = cmd.ExecuteReader();

            int max = 0;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    object obj = reader.GetValue(0);

                    try
                    {
                        max = Convert.ToInt32(obj);
                    }
                    catch
                    { }
                }

                reader.Close();
                reader.Dispose();
            }

            cmd.Dispose();

            return max;
        }

        /// <summary>
        /// Gets the count value of a given Column/Field Name within the given Table Name
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="key">Name of the Column/Field to lookup</param>
        /// <param name="searchKey">Left-side of the 'WHERE' argument</param>
        /// <param name="searchVal">Right-side of the 'WHERE' argument</param>
        /// <returns>The count value reported by MySQL</returns>
        public int GetCount(string table, string key, string searchKey, string searchVal)
        {
            if (!Connected)
                return 0;

            string query = String.Format("SELECT COUNT({0}) FROM `{1}` WHERE {2}='{3}'", key, table, searchKey, searchVal);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            OdbcDataReader reader = cmd.ExecuteReader();

            int count = 0;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    count = reader.GetInt32(0);
                }

                reader.Close();
                reader.Dispose();
            }

            cmd.Dispose();

            return count;
        }

        /// <summary>
        /// Updates the given Column/Field data within the given Table Name using the given Value
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="key">Name of the Column/Field to lookup</param>
        /// <param name="val">Value to set</param>
        /// <param name="searchKey">Left-side of the 'WHERE' argument</param>
        /// <param name="searchVal">Right-side of the 'WHERE' argument</param>
        /// <returns>True if successful</returns>
        public bool SetData(string table, string key, string val, string searchKey, string searchVal)
        {
            if (!Connected)
                return false;

            string query = String.Format("UPDATE `{0}` SET {1}='{2}' WHERE {3}='{4}'", table, key, val, searchKey, searchVal);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            bool ret = (cmd.ExecuteNonQuery() > 0);

            cmd.Dispose();

            return ret;
        }

        /// <summary>
        /// Sets the given Column/Field data within the given Table Name using the given Data
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="data">List of "Column/Field Name" => "Value" data to insert or replace</param>
        /// <param name="replace">If true, will replace existing data of the same keys, if exists.</param>
        /// <returns>True if successful</returns>
        public bool PutData(string table, MySQLData[] data, bool replace)
        {
            if (!Connected)
                return false;

            string keys = String.Empty;
            string values = String.Empty;

            int count = data.Length;
            int index = 0;
            for (int i = 0; i < count; i++)
            {
                keys += data[i].Key;

                if (index < count - 1)
                    keys += ", ";

                values += "'" + data[i].Value + "'";

                if (index < count - 1)
                    values += ", ";

                index++;
            }

            string query = String.Format("{0} INTO `{1}` ({2}) VALUES ({3})", (replace ? "REPLACE" : "INSERT"), table, keys, values);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            bool ret = (cmd.ExecuteNonQuery() > 0);

            cmd.Dispose();

            return ret;
        }

        /// <summary>
        /// Removes a row from the given Table Name using the given search arguments
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="searchKey">Left-side of the 'WHERE' argument</param>
        /// <param name="searchVal">Right-side of the 'WHERE' argument</param>
        /// <returns></returns>
        public bool RemoveData(string table, string searchKey, string searchVal)
        {
            if (!Connected)
                return false;

            string query = String.Format("DELETE FROM `{0}` WHERE {1}='{2}'", table, searchKey, searchVal);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            bool ret = (cmd.ExecuteNonQuery() > 0);

            cmd.Dispose();

            return ret;
        }

        /// <summary>
        /// Searches and returns a MySQLResult collection within the given Table Name using the given search arguments
        /// </summary>
        /// <param name="table">Name of the table to lookup</param>
        /// <param name="keys">List of Colum/Field names to return data for (Seperated by commas ',')</param>
        /// <param name="searchKey">Left-side of the 'WHERE' argument</param>
        /// <param name="searchVal">Right-side of the 'WHERE' argument</param>
        /// <param name="order">The Column/Field Name to order the results by</param>
        /// <returns>MySQLResult Collection containing the results for this query</returns>
        public MySQLResult[] SearchData(string table, string keys, string searchKey, string searchVal, string order)
        {
            if (!Connected)
                return new MySQLResult[0];

            string query = String.Format("SELECT {0} FROM `{1}` WHERE {2}='{3}' ORDER BY {4}", keys, table, searchKey, searchVal, order);

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            OdbcDataReader reader = cmd.ExecuteReader();

            List<MySQLResult> tmp = new List<MySQLResult>();

            if (reader.HasRows)
            {

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string key = reader.GetName(i);
                        object obj = reader.GetValue(i);

                        tmp.Add(new MySQLResult(i, key, obj));
                    }
                }

                reader.Close();
                reader.Dispose();
            }

            cmd.Dispose();

            MySQLResult[] ret = new MySQLResult[tmp.Count];

            for (int i = 0; i < tmp.Count; i++)
            {
                ret[i] = tmp[i];
            }

            tmp.Clear();

            return ret;
        }

        /// <summary>
        /// Performs a standard MySQL custom query
        /// </summary>
        /// <param name="query">Custom query text</param>
        /// <returns>Total number of affected rows</returns>
        public int Query(string query)
        {
            if (!Connected)
                return 0;

            OdbcCommand cmd = _CONNECTION.CreateCommand();
            cmd.CommandText = query;

            int ret = cmd.ExecuteNonQuery();

            cmd.Dispose();

            return ret;
        }

        /// <summary>
        /// Closes and Disposes the instance of Connection
        /// </summary>
        public void Close()
        {
            if (!Connected)
            {
                _CONNECTION = null;
                return;
            }

            _CONNECTION.Close();
            _CONNECTION.Dispose();
            _CONNECTION = null;
        }

        public override string ToString()
        {
            return String.Format("[{0}] => '{1}'", _IP, Connected ? _CONNECTION.State.ToString() : "Disconnected");
        }
    }
}