using LHDN.CRM.BC.Interfaces;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Data.Entity.Validation;
using LHDN.CRM.BC.Common;

namespace LHDN.CRM.BC.Repositories
{
    public abstract class BaseRepository<E, T> : IBaseRepository<T>, IDisposable
        where T : class
        where E : DbContext, new()
    {
        private E _entities = new E();

        public E Context
        {
            get { return _entities; }
            set { _entities = value; }
        }

        public virtual IEnumerable<TEntity> ExecuteSP<TEntity>(string strSPName, params Object[] args)
        {
            try
            {
                if (Context.Database.Connection.State != System.Data.ConnectionState.Open)
                {
                    Context.Database.Connection.Open();
                }

                int i = 0;
                List<SqlParameter> lstParams = new List<SqlParameter>();
                while (i < args.Length)
                {
                    if ((i + 1) >= args.Length)
                    {
                        break; // TODO: might not be correct. Was : Exit While
                    }
                    string sParamName = (string)(args[i]);
                    object oVal = args[i + 1];
                    lstParams.Add(new SqlParameter(sParamName, oVal));
                    i += 2;
                }

                var result = Context.Database.SqlQuery<TEntity>($"{"dbo." + strSPName} {string.Join(", ", lstParams.Select(p => p.ParameterName).ToArray())}", lstParams.ToArray());
                return result;
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString());
            }

        }

        public virtual IEnumerable<TEntity> ExecuteSPDynamic<TEntity>(string strSPName, params dynamic[] args)
        {
            try
            {
                if (Context.Database.Connection.State != System.Data.ConnectionState.Open)
                {
                    Context.Database.Connection.Open();
                }

                int i = 0;
                List<SqlParameter> lstParams = new List<SqlParameter>();
                while (i < args.Length)
                {
                    if ((i + 1) >= args.Length)
                    {
                        break; // TODO: might not be correct. Was : Exit While
                    }
                    string sParamName = (string)(args[i]);
                    dynamic oVal = args[i + 1];
                    SqlParameter sqlParam;
                    if (oVal.GetType().Name.ToUpper() == "DATATABLE")
                    {
                        sqlParam = new SqlParameter(sParamName, (DataTable)oVal);
                        sqlParam.SqlDbType = SqlDbType.Structured;
                        sqlParam.TypeName = "dbo." + ((DataTable)oVal).TableName;
                    }
                    else
                    {
                        sqlParam = new SqlParameter(sParamName, oVal);
                    }
                    //lstParams.Add(new SqlParameter(sParamName, oVal));
                    lstParams.Add(sqlParam);
                    i += 2;
                }

                var result = Context.Database.SqlQuery<TEntity>($"{"dbo." + strSPName} {string.Join(", ", lstParams.Select(p => p.ParameterName).ToArray())}", lstParams.ToArray());
                return result;
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString());
            }

        }

        public virtual DataSet ExecuteSPSingleConn(string strSPName, params Object[] args)
        {
            DataSet dSet = new DataSet();
            try
            {
                string strConn = Utilities.GetConnSetting("LHDN.CRM");
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    //try
                    //{
                        conn.Open();
                        using (var cmdSql = conn.CreateCommand())
                        {

                            cmdSql.CommandText = "dbo." + strSPName;
                            cmdSql.CommandType = CommandType.StoredProcedure;

                            cmdSql.CommandTimeout = 90;
                            int i = 0;
                            while (i < args.Length)
                            {
                                if ((i + 1) >= args.Length)
                                {
                                    break; // TODO: might not be correct. Was : Exit While
                                }
                                string sParamName = (string)(args[i]);
                                object oVal = args[i + 1];
                                cmdSql.Parameters.Add(new SqlParameter(sParamName, oVal));
                                i += 2;
                            }

                            using (var reader = cmdSql.ExecuteReader())
                            {
                                try
                                {
                                    do
                                    {
                                        dSet.Tables.Add(new DataTable());
                                        dSet.Tables[dSet.Tables.Count - 1].Load(reader);
                                    } while (!reader.IsClosed);
                                }
                                catch (Exception ex)
                                {
                                    reader.Close();
                                    cmdSql.Dispose();
                                    conn.Close();
                                    throw new UnhandledError(ex.ToString() + ex.StackTrace);
                                }
                            }
                        }

                    }
                    //catch (Exception ex)
                    //{
                    //    conn.Close();
                    //    //Context.Database.Connection.Close();
                    //    throw new UnhandledError(ex.ToString() + ex.StackTrace);
                    //}
                    //if (Context.Database.Connection.State == ConnectionState.Open)
                    //{
                    //    Context.Database.Connection.Close();
                    //}
                
            }
            catch (Exception ex)
            {
                throw new UnhandledError(ex.ToString() + ex.StackTrace);
            }

            return dSet;
        }

        public virtual DataSet ExecuteSP(string strSPName, params Object[] args)
        {
            DataSet dSet = new DataSet();
            try
            {
                if (Context.Database.Connection.State != System.Data.ConnectionState.Open)
                {
                    Context.Database.Connection.Open();
                }

                using (var cmdSql = Context.Database.Connection.CreateCommand())
                {
                    try
                    {
                        cmdSql.CommandText = "dbo." + strSPName;
                        cmdSql.CommandType = CommandType.StoredProcedure;

                        cmdSql.CommandTimeout = 90;
                        int i = 0;
                        while (i < args.Length)
                        {
                            if ((i + 1) >= args.Length)
                            {
                                break; // TODO: might not be correct. Was : Exit While
                            }
                            string sParamName = (string)(args[i]);
                            object oVal = args[i + 1];
                            cmdSql.Parameters.Add(new SqlParameter(sParamName, oVal));
                            i += 2;
                        }

                        using (var reader = cmdSql.ExecuteReader())
                        {
                            try
                            {
                                do
                                {
                                    dSet.Tables.Add(new DataTable());
                                    dSet.Tables[dSet.Tables.Count - 1].Load(reader);
                                } while (!reader.IsClosed);
                            }
                            catch (Exception ex)
                            {
                                reader.Close();
                                cmdSql.Dispose();
                                Context.Database.Connection.Close();
                                throw new UnhandledError(ex.ToString() + ex.StackTrace);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        cmdSql.Dispose();
                        Context.Database.Connection.Close();
                        throw new UnhandledError(ex.ToString() + ex.StackTrace);
                    }
                }

                //if (Context.Database.Connection.State == ConnectionState.Open)
                {
                    Context.Database.Connection.Close();
                }
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString() + ex.StackTrace);
            }
            return dSet;
        }

        public virtual int ExecuteUpdateSP(string strSPName, params Object[] args)
        {
            try
            {
                var result = 0;
                var message = string.Empty;
                if (Context.Database.Connection.State != System.Data.ConnectionState.Open)
                {
                    Context.Database.Connection.Open();
                }

                using (var cmdSql = Context.Database.Connection.CreateCommand())
                {
                    cmdSql.CommandText = "dbo." + strSPName;
                    cmdSql.CommandType = CommandType.StoredProcedure;
                    cmdSql.CommandTimeout = 90;

                    int i = 0;
                    while (i < args.Length)
                    {
                        if ((i + 1) >= args.Length)
                        {
                            break; // TODO: might not be correct. Was : Exit While
                        }
                        string sParamName = (string)(args[i]);
                        object oVal = args[i + 1];
                        cmdSql.Parameters.Add(new SqlParameter(sParamName, oVal));
                        i += 2;
                    }

                    using (var reader = cmdSql.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader[0]);

                        }

                        reader.Close();

                    }

                }

                //if (Context.Database.Connection.State == ConnectionState.Open)
                {
                    Context.Database.Connection.Close();
                }

                return result;
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString() + ex.StackTrace);
            }
        }

        public virtual int ExecuteSPRetInt(string strSPName, params Object[] args)
        {
            try
            {
                int result = 0;
                var message = string.Empty;
                if (Context.Database.Connection.State != System.Data.ConnectionState.Open)
                {
                    Context.Database.Connection.Open();
                }

                using (var cmdSql = Context.Database.Connection.CreateCommand())
                {
                    cmdSql.CommandText = "dbo." + strSPName;
                    cmdSql.CommandType = CommandType.StoredProcedure;
                    cmdSql.CommandTimeout = 90;

                    int i = 0;
                    while (i < args.Length)
                    {
                        if ((i + 1) >= args.Length)
                        {
                            break; // TODO: might not be correct. Was : Exit While
                        }
                        string sParamName = (string)(args[i]);
                        object oVal = args[i + 1];
                        cmdSql.Parameters.Add(new SqlParameter(sParamName, oVal));
                        i += 2;
                    }

                    using (var reader = cmdSql.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader[0]);
                        }

                        reader.Close();
                    }

                }

                return result;
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString() + ex.StackTrace);
            }
        }

        public virtual long ExecuteUpdateSPRetLong(string strSPName, params Object[] args)
        {
            try
            {
                var result = (long)0;
                var message = string.Empty;
                if (Context.Database.Connection.State == ConnectionState.Closed)
                {
                    Context.Database.Connection.Open();
                }

                using (var cmdSql = Context.Database.Connection.CreateCommand())
                {
                    cmdSql.CommandText = "dbo." + strSPName;
                    cmdSql.CommandType = CommandType.StoredProcedure;
                    cmdSql.CommandTimeout = 90;

                    int i = 0;
                    while (i < args.Length)
                    {
                        if ((i + 1) >= args.Length)
                        {
                            break; // TODO: might not be correct. Was : Exit While
                        }
                        string sParamName = (string)(args[i]);
                        object oVal = args[i + 1];
                        cmdSql.Parameters.Add(new SqlParameter(sParamName, oVal));
                        i += 2;
                    }

                    using (var reader = cmdSql.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt64(reader[0]);
                        }

                        reader.Close();

                    }

                }

                //if (Context.Database.Connection.State == ConnectionState.Open)
                {
                    Context.Database.Connection.Close();
                }

                return result;
            }
            catch (Exception ex)
            {
                Context.Database.Connection.Close();
                throw new UnhandledError(ex.ToString());
            }
        }

        public virtual DataTable ToDataTable<TResult>(List<TResult> data) where TResult : class, new()
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(TResult));

            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            object[] values = new object[props.Count];
            foreach (TResult item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item) ?? DBNull.Value;
                }
                table.Rows.Add(values);
            }

            return table;
        }

        public virtual DataTable ToDataTable<TResult>(TResult data) where TResult : class, new()
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(TResult));

            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            object[] values = new object[props.Count];

            for (int i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(data) ?? DBNull.Value;
            }
            table.Rows.Add(values);

            return table;
        }

        public virtual IList<TResult> DataTableToList<TResult>(DataTable table) where TResult : class, new()
        {
            IList<PropertyInfo> properties = typeof(TResult).GetProperties().ToList();
            IList<TResult> result = new List<TResult>();

            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<TResult>((DataRow)row, properties);
                result.Add(item);
            }

            return result;
        }

        public virtual TResult DataTableToObject<TResult>(DataTable table) where TResult : class, new()
        {
            IList<PropertyInfo> properties = typeof(TResult).GetProperties().ToList();
            TResult result = new TResult();
            if (table != null && table.Rows.Count > 0)
                result = CreateItemFromRow<TResult>((DataRow)table.Rows[0], properties);

            return result;
        }

        private TResult CreateItemFromRow<TResult>(DataRow row, IList<PropertyInfo> properties) where TResult : class, new()
        {
            TResult item = new TResult();

            foreach (var p in properties)
            {
                if (row.Table.Columns.Contains(p.Name))
                {
                    if (string.Equals(row[p.Name].GetType().Name, DBNull.Value))
                    {
                        if (Nullable.GetUnderlyingType(p.PropertyType) != null)
                            p.SetValue(item, row[p.Name], null);
                    }
                    else if (p.PropertyType.IsGenericType &&
                        p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) // Check if DTO defined property is Nullable
                    {
                        if (Nullable.GetUnderlyingType(p.PropertyType) == row[p.Name].GetType())
                            p.SetValue(item, row[p.Name], null);
                    }
                    else if (p.PropertyType == typeof(string))
                        p.SetValue(item, row[p.Name].ToString(), null);
                    else if (p.PropertyType == row[p.Name].GetType())
                        p.SetValue(item, row[p.Name], null);
                    else if (row[p.Name].GetType() == typeof(Byte))
                        p.SetValue(item, Convert.ToInt16(row[p.Name]), null);
                    else if (row[p.Name].GetType() == typeof(Int16))
                        p.SetValue(item, Convert.ToInt16(row[p.Name]), null);
                    //else if (row[p.Name].GetType() == typeof(Boolean))
                    //    p.SetValue(item, Convert.ToBoolean(row[p.Name]), null);
                    //else if (row[p.Name].GetType() == typeof(Int32))
                    //    p.SetValue(item, Convert.ToInt32(row[p.Name]), null);
                    else if (row[p.Name].GetType() == typeof(Int64))
                        p.SetValue(item, Convert.ToInt64(row[p.Name]), null);
                    else if (row[p.Name].GetType() == typeof(Decimal))
                    {
                        p.SetValue(item, Convert.ToDecimal(row[p.Name]), null);
                    }
                }
            }
            return item;
        }

        public virtual IQueryable<T> GetAll()
        {
            IQueryable<T> query = _entities.Set<T>();
            return query;
        }

        public virtual T GetByID(object id)
        {
            return _entities.Set<T>().Find(id);
        }

        public IQueryable<T> FindBy(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
        {

            IQueryable<T> query = _entities.Set<T>().Where(predicate);
            return query;
        }

        public virtual void Add(T entity)
        {
            _entities.Set<T>().Add(entity);
        }

        public virtual void Delete(object id)
        {
            Delete(GetByID(id));
        }

        public virtual void Delete(T entity)
        {
            _entities.Set<T>().Remove(entity);
        }

        public virtual void Edit(T entity)
        {
            _entities.Entry(entity).State = EntityState.Modified;
        }

        public virtual void Save()
        {
            try
            {
                _entities.SaveChanges();
            }
            catch (DbEntityValidationException dbEx)
            {
                Exception ex = dbEx;
                foreach (var validationErrors in dbEx.EntityValidationErrors)
                {
                    string message = string.Format("{0}. Entity of type \"{1}\" in state \"{2}\" has the following validation errors:",
                     ex.Message, validationErrors.Entry.Entity.GetType().Name, validationErrors.Entry.State);
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                        message = message + string.Format("{0}:{1}- Property: \"{2}\"",
                            validationErrors.Entry.Entity.ToString(),
                            validationError.ErrorMessage, validationError.PropertyName);
                        // raise a new exception nesting  
                        // the current instance as InnerException  
                        ex = new InvalidOperationException(message, ex);
                    }
                }
                throw ex;
            }
        }

        #region IDisposable Support
        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    Context.Dispose();
                }

                disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
    }
}
