using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Valg.EVA.Skanning.Entity.SqlConnection
{
    public static class DataAccessExtensions
    {
        public const int defaultCommandTimeOut = 30; 

        public static IEnumerable<T> SP_GetList<T>(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut));
            }
        }

        public static T Query<T>(this ISqlConnectionFactory factory, string query, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query<T>(query, parameters, commandTimeout: factory.GetCommandTimeOut(timeOut)).SingleOrDefault();
            }
        }

        public static dynamic Query(this ISqlConnectionFactory factory, string query, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query(query, parameters, commandTimeout: factory.GetCommandTimeOut(timeOut)).SingleOrDefault();
            }
        }

        public static void Execute(this ISqlConnectionFactory factory, string query, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            ExecuteAndCheckForConcurrencyExceptions(() =>
            {
                using (var connection = factory.CreateSqlConnection())
                {
                    connection.Open();
                    connection.Execute(query, parameters, commandTimeout: factory.GetCommandTimeOut(timeOut));
                }
            });
        }

        public static T SP_Get<T>(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut)).SingleOrDefault();
            }
        }

        public static IEnumerable<dynamic> SP_GetList(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut));
            }
        }

        public static dynamic SP_Get(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut)).SingleOrDefault();
            }
        }

        public static IEnumerable<dynamic> SP_Execute(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                return connection.Query(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut));
            }
        }

        public static T SP_Execute<T>(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            T result = default(T);
            ExecuteAndCheckForConcurrencyExceptions(() =>
            {
                using (var connection = factory.CreateSqlConnection())
                {
                    connection.Open();
                    result = connection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut)).SingleOrDefault();
                }
            });

            return result;
        }

        public static T SP_ExecuteScalar<T>(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            using (var connection = factory.CreateSqlConnection())
            {
                connection.Open();
                var value = connection.ExecuteScalar(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut));
                if (value == null)
                {
                    return default(T);
                }

                return (T) value;
            }
        }

        public static void SP_ExecuteVoid(this ISqlConnectionFactory factory, string spName, object parameters = null, int? timeOut = defaultCommandTimeOut)
        {
            ExecuteAndCheckForConcurrencyExceptions(() =>
            {
                using (var connection = factory.CreateSqlConnection())
                {
                    connection.Open();
                    connection.Execute(spName, parameters, commandType: CommandType.StoredProcedure, commandTimeout: factory.GetCommandTimeOut(timeOut));
                }
            });
        }

        private static void ExecuteAndCheckForConcurrencyExceptions(Action action)
        {
            try
            {
                action.Invoke();
            }
            catch (SqlException ex)
            {
                if (ex.Class == 16 && ex.Number == 50000 && ex.Message == "ConcurrencyError")
                {
                    throw new DBConcurrencyException($"Concurrency error in procedure {ex.Procedure}", ex);
                }
                else
                {
                    throw;
                }
            }
        }
    }
}
