//
// Copyright (C) Latheesan Kanesamoorthy 2015
//
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading;

namespace Libs
{
    public class DBErrorEventArg : EventArgs
    {
        /// <summary>
        /// Class properties.
        /// </summary>
        public string ErrorMsg { get; private set; }
        public string LastQuery { get; private set; }

        /// <summary>
        /// Class constructor.
        /// </summary>
        /// <param name="errorMsg"></param>
        /// <param name="lastQuery"></param>
        public DBErrorEventArg(string errorMsg, string lastQuery)
        {
            ErrorMsg = errorMsg;
            LastQuery = lastQuery;
        }
    }

    public class MSSQLConnection
    {
        /// <summary>
        /// Class constants.
        /// </summary>
        const int sqlDeadlock = 1205;
        const int sqlTimeout = -2;

        /// <summary>
        /// Private class objects.
        /// </summary>
        private SqlConnection sqlConnection;
        private int sqlCommandTimeout;
        private string lastQuery = string.Empty;

        /// <summary>
        /// Public event related objects & handler.
        /// </summary>
        public event ErrorHandler OnError;
        public delegate void ErrorHandler(MSSQLConnection sender, DBErrorEventArg e);

        /// <summary>
        /// Class constructor.
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="sqlCommandTimeout"></param>
        public MSSQLConnection(SqlConnection sqlConnection, Int32 sqlCommandTimeout = 120)
        {
            if (null == sqlConnection)
                throw new Exception("Invalid MSSQL Database Conection Handle");

            if (sqlConnection.State != System.Data.ConnectionState.Open)
                throw new Exception("MSSQL Database Connection Is Not Open");

            this.sqlConnection = sqlConnection;
            this.sqlCommandTimeout = sqlCommandTimeout;
        }

        /// <summary>
        /// Helper method to emit a database error to event subscribers.
        /// </summary>
        /// <param name="errorMsg"></param>
        internal void EmitError(String errorMsg)
        {
            var errorDelegate = OnError;
            if (errorDelegate != null)
            {
                errorDelegate(this, new DBErrorEventArg(errorMsg, lastQuery));
            }
        }

        /// <summary>
        /// Generic method to execute a query with retries to handle deadlock / timeout errors.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlFunc"></param>
        /// <returns></returns>
        private T Retry<T>(Func<T> sqlFunc)
        {
            try
            {
                int retryCount = 30;
                TimeSpan retryDelay = TimeSpan.FromSeconds(1);

                while (true)
                {
                    try
                    {
                        if (sqlConnection.State == System.Data.ConnectionState.Closed)
                        {
                            sqlConnection.Open();
                        }

                        return sqlFunc();
                    }
                    catch (SqlException sqlException)
                    {
                        --retryCount;

                        if (retryCount <= 0) { throw; }

                        if (sqlException.Number == sqlDeadlock)
                        {
                            EmitError(string.Format("[ Attempts Left : {0} ] Failed to execute Query, SQL Deadlock Error. Retrying again in {1} seconds ...",
                                retryCount,
                                retryDelay.Seconds));
                        }
                        else if (sqlException.Number == sqlTimeout)
                        {
                            EmitError(string.Format("[ Attempts Left : {0} ] Failed to execute Query, SQL Timeout Error. Retrying again in {1} seconds ...",
                                retryCount,
                                retryDelay.Seconds));
                        }
                        else { throw; }

                        Thread.Sleep(retryDelay);
                    }
                }
            }
            catch (Exception e)
            {
                EmitError("Failed to execute SQL Query : " + e.Message);
                return default(T);
            }
        }

        /// <summary>
        /// Retry helper method.
        /// </summary>
        /// <param name="sqlAction"></param>
        private void Retry(Action sqlAction)
        {
            Retry(() => { sqlAction(); return true; });
        }

        /// <summary>
        /// Helper method to populate sql paramters onto sql command.
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="sqlParamters"></param>
        private void AddSqlParameters(SqlCommand sqlCommand, Dictionary<string, object> sqlParamters)
        {
            if (null != sqlParamters && sqlParamters.Count > 0)
            {
                foreach (KeyValuePair<string, object> sqlParamter in sqlParamters)
                {
                    sqlCommand.Parameters.AddWithValue(sqlParamter.Key, sqlParamter.Value);
                }
            }
        }

        /// <summary>
        /// Method to execute a scalar query (select a single field from row).
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sqlQuery, Dictionary<string, object> sqlParamters = null)
        {
            lastQuery = sqlQuery;

            return Retry(() =>
            {
                using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection))
                {
                    sqlCommand.CommandTimeout = sqlCommandTimeout;
                    AddSqlParameters(sqlCommand, sqlParamters);
                    object result = sqlCommand.ExecuteScalar();

                    if (null == result || result is DBNull)
                    {
                        return default(T);
                    }

                    Type type = typeof(T);

                    if (type == typeof(int))
                    {
                        int value;
                        int.TryParse(result.ToString(), out value);
                        result = value;
                    }

                    if (type == typeof(decimal))
                    {
                        decimal value;
                        decimal.TryParse(result.ToString(), out value);
                        result = value;
                    }

                    if (type == typeof(string))
                    {
                        result = result.ToString();
                    }

                    return (T)result;
                }
            });
        }

        /// <summary>
        /// Method to execute a reader query (select one or more rows).
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public List<Dictionary<string, object>> ExecuteReader(string sqlQuery, Dictionary<string, object> sqlParamters = null)
        {
            lastQuery = sqlQuery;

            return Retry(() =>
            {
                using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection))
                {
                    sqlCommand.CommandTimeout = sqlCommandTimeout;
                    AddSqlParameters(sqlCommand, sqlParamters);
                    List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();

                    using (SqlDataReader reader = sqlCommand.ExecuteReader())
                    {
                        if (null == reader) { return results; }

                        while (reader.Read())
                        {
                            Dictionary<string, object> resultData = new Dictionary<string, object>();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i);
                                object columnValue = reader[columnName];
                                resultData.Add(columnName, columnValue);
                            }

                            results.Add(resultData);
                        }
                    }

                    return results;
                }
            });
        }

        /// <summary>
        /// Method to execute a non query (for CRUD operations).
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public Int32 ExecuteNonQuery(string sqlQuery, Dictionary<string, object> sqlParamters = null)
        {
            lastQuery = sqlQuery;

            return Retry(() =>
            {
                int affectedRows = 0;

                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection, sqlTransaction))
                    {
                        AddSqlParameters(sqlCommand, sqlParamters);
                        sqlCommand.CommandTimeout = sqlCommandTimeout;
                        affectedRows = sqlCommand.ExecuteNonQuery();
                        sqlTransaction.Commit();
                    }
                }

                return affectedRows;
            });
        }
    }
}