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

using MySql.Data.MySqlClient;

namespace Blizzard
{
    /// <summary>
    /// Represents an active or inactive connection to a MySQL Server.
    /// Dispose releases the instance of this object to a stack allowing it to be reused for GC reasons.
    /// The actual open connection to MySQL is not maintained here, it is maintained by the MySQL pool itself.
    /// </summary>
    class MySQLConnection : IDisposable
    {
        /// <summary>
        /// The reference to the Manager instance which manages this connection.
        /// </summary>
        private MySQLManager Manager;

        /// <summary>
        /// The connection link to the MySQL Database Server.
        /// </summary>
        private MySqlConnection Connection;

        /// <summary>
        /// The MySQL Command used for performing MySQL operations.
        /// </summary>
        private MySqlCommand Command;

        /// <summary>
        /// Stores a collection of Parameters to be sent and processed by the MySQL Server.
        /// </summary>
        private List<MySqlParameter> Parameters;

        public MySQLConnection(MySQLManager mgr, string con)
        {
            this.Manager = mgr;

            this.Connection = new MySqlConnection(con);
            this.Command = Connection.CreateCommand();
            this.Parameters = new List<MySqlParameter>(5);
        }

        /// <summary>
        /// Opens the connection to a MySQL Server.
        /// </summary>
        public void Open()
        {
            try
            {
                this.Connection.Open();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("[MySQL]: " + ex.Message);
            }
        }

        /// <summary>
        /// Disposes the used MySQLConnection instance, allowing it to be re-used when required.
        /// </summary>
        public void Dispose()
        {
            // Clear up resources used
            this.Command.Dispose();
            this.Connection.Dispose();

            // Clear nescessary stuff ready for re-use
            this.Parameters.Clear();

            // Release this instance back to the manager.
            this.Manager.ReleaseConnection(this);
        }

        /// <summary>
        /// Adds a parameter with a value ready for use by MySQL.
        /// </summary>
        /// <param name="param">The parameter.</param>
        /// <param name="val">The value.</param>
        public void AddParameterWithValue(string param, object val)
        {
            this.Parameters.Add(new MySqlParameter(param, val));
        }

        /// <summary>
        /// Executes a query against the MySQL Server and returns a DataSet.
        /// </summary>
        /// <param name="query">Query to run.</param>
        /// <returns>DataSet.</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            this.Command.CommandText = commandText;

            if (this.Parameters.Count > 0)
            {
                foreach (MySqlParameter param in this.Parameters)
                {
                    this.Command.Parameters.Add(param);
                }
            }

            MySqlDataAdapter da = new MySqlDataAdapter(this.Command);
            DataSet ds = new DataSet();

            da.Fill(ds);

            this.Command.CommandText = string.Empty;
            this.Command.Parameters.Clear();

            return ds;
        }

        public DataRow ReadDataRow(string query)
        {
            MySqlCommand mCommand = new MySqlCommand();
            MySqlDataAdapter mAdapter;

            DataSet tmpResult = new DataSet();
            DataRow dResult = new DataTable().NewRow();

            try
            {
                this.Command.CommandText = query;

                mAdapter = new MySqlDataAdapter(this.Command);
                mAdapter.Fill(tmpResult);
                dResult = tmpResult.Tables[0].Rows[0];
            }
            catch
            { }
            finally
            {
                this.Command.Parameters.Clear();
            }

            return dResult;
        }

        /// <summary>
        /// Executes a Non-Query against a MySQL Server.
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText)
        {
            this.Command.CommandText = commandText;

            if (this.Parameters.Count > 0)
            {
                foreach (MySqlParameter param in this.Parameters)
                {
                    this.Command.Parameters.Add(param);
                }
            }

            if (!string.IsNullOrEmpty(commandText))
            {
                return this.Command.ExecuteNonQuery();
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// Executes a query against a MySQL Server and returns a DataReader.
        /// </summary>
        /// <param name="commandText">The query to run.</param>
        /// <returns>MySqlDataReader.</returns>
        public MySqlDataReader ExecuteReader(string commandText)
        {
            this.Command.CommandText = commandText;

            if (this.Parameters.Count > 0)
            {
                foreach (MySqlParameter param in this.Parameters)
                {
                    this.Command.Parameters.Add(param);
                }
            }

            MySqlDataReader dr;

            dr = this.Command.ExecuteReader(CommandBehavior.CloseConnection);

            this.Command.CommandText = string.Empty;
            this.Command.Parameters.Clear();

            dr.Read();
            return dr;
        }
    }
}