using CAPI.Custom.Entities;
using framework_business;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Design.PluralizationServices;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CAPI.Custom.Data
{
    public partial class QuotesContext
    {
        #region surface utils

        private string[] _cachedSurfaceNames;
        protected string[] CachedSurfaceNames
        {
            get
            {
                if (_cachedSurfaceNames.IsEmpty())
                {
                    _cachedSurfaceNames = Surfaces
                        .Select(p => p.name)
                        .ToArray();
                }

                return _cachedSurfaceNames;
            }
        }

        public string GetSurfaceName(string typeName)
        {
            if (typeName.IsEmpty()) return string.Empty;

            // if the type's name comes from the old classes
            //  - oPartsAlternates
            //  - oPartsMaster
            // then just remove the prefix 'o'

            if (typeName[0] == 'o') return typeName.Substring(1);

            // First try matching the pluralized typeName which is most likely the case

            var pluralizedName = PluralizationService.CreateService(CultureInfo.CurrentCulture)
                .Pluralize(typeName);

            var found = CachedSurfaceNames
                .FirstOrDefault(p => pluralizedName.Equals(p, StringComparison.OrdinalIgnoreCase))
                ?? string.Empty;

            if (found.IsNotEmpty()) return found;

            // Next try matching the typeName

            found = CachedSurfaceNames.FirstOrDefault(p => typeName.Equals(p, StringComparison.OrdinalIgnoreCase));

            if (found.IsNotEmpty()) return found;

            //if (typeof(InventoryLocation).Name.Equals(typeName, StringComparison.OrdinalIgnoreCase))
            //    return typeof(InventoryManagement).Name;

            return string.Empty;
        }

        public Surface GetSurface(string typeName)
        {
            string surfaceName = GetSurfaceName(typeName);
            return Surfaces.SingleOrDefault(p => surfaceName.Equals(p.name, StringComparison.OrdinalIgnoreCase));
        }

        public string GetSurfaceName<TEntity>()
            where TEntity : ISurfaceEntity
        {
            var typeName = typeof(TEntity).Name;
            return GetSurfaceName(typeof(TEntity).Name);
        }

        public Surface GetSurface<TEntity>()
             where TEntity : ISurfaceEntity
        {
            var surfaceName = GetSurfaceName<TEntity>();
            return Surfaces.SingleOrDefault(p => surfaceName.Equals(p.name, StringComparison.OrdinalIgnoreCase));
        }

        #endregion

        #region Surface Queries

        public Surface GetSurfaceByName(string surfaceName)
        {
            return Surfaces.SingleOrDefault(p => surfaceName.Equals(p.name, StringComparison.OrdinalIgnoreCase));
        }

        #endregion

        public SurfaceGridOptions GetSurfaceGridOptions<TEntity>()
             where TEntity : ISurfaceEntity
        {
            var surfaceName = GetSurfaceName<TEntity>();
            var result = Database.SqlQuery<SurfaceGridOptions>(@"SELECT 
					SurfaceId = surface.recId,
					SurfaceName = surface.name,
					GridOptions_Id = gridOptions.recId,
					AllowAdd = gridOptions.allowAdd,
					PagingCount = gridOptions.pagingCount,
					DefaultSort = gridOptions.defaultSort,
					AddAccessType = gridOptions.addAccessType,
					EditAccessType = gridOptions.editAccessType,
					ViewAccessType = gridOptions.viewAccessType,
					RemoveAccessType = gridOptions.removeAccessType,
					Columns = (
						select stuff((
							Select ',publ.' + sf.surfaceFieldName 
								from pal_SurfaceField sf 
								where sf.surfaceFieldTypeId NOT IN (1,2,16,11,19) 
									and sf.isActive = 1 
									and sf.surfaceId = surface.recId 
									and (sf.isGrid = 1 
										or sf.isFilter = 1 
										or sf.surfaceFieldName = 'ParentSurfaceItemId' 
										or sf.isGrouped = 1)  
									order by sf.gridSequence
							FOR XML PATH(''))
						,1,1,'')
					)
				FROM pal_Surface surface join pal_SurfaceGridOptions gridOptions on surface.recId = gridOptions.surfaceId
				WHERE surface.name = @surfaceName",
                new SqlParameter("@surfaceName", surfaceName))
                .FirstOrDefault();

            return result;
        }

        #region published tables

        public IEnumerable<dynamic> GetPublishedSurfaceDataPaged<TSurface>(int userId, int startIndex = 0, int maxRows = int.MaxValue,
            string orderBy = "", string search = "", string filter = "")
            where TSurface : ISurfaceEntity
        {
            var gridOptions = GetSurfaceGridOptions<TSurface>();
            var query = @"sp_GetPublishedSurfaceDataPaged @SurfaceID, @StartIndex, @MaxRows, 
        					@OrderBy, @Search, @UserLink, @Filter, @UserId, @ShowAllFields, @Cols";

            return this.DynamicListFromSql(query,
                new Dictionary<string, object> {
                    { "SurfaceID", gridOptions.SurfaceId },
                    { "StartIndex", startIndex },
                    { "MaxRows", maxRows },
                    { "OrderBy", !orderBy.IsEmpty() ? orderBy : gridOptions.DefaultSort },
                    { "Search", search },
                    { "UserLink", 0 },
                    { "Filter", filter },
                    { "UserId", userId },
                    { "ShowAllFields", true },
                    { "Cols", gridOptions.Columns },
                });
        }

        public IEnumerable<dynamic> GetChildPublishedSurfaceData<TSurface>(int userId, int parentItemId)
            where TSurface : ISurfaceEntity
        {
            var gridOptions = GetSurfaceGridOptions<TSurface>();
            var sqlQuery = "sp_GetChildPublishedSurfaceData @SurfaceID, @ParentSurfaceItemId, @RowLimit, @GridOnly, @UserId, @Cols, @OrderBy";
            var paramters = new Dictionary<string, object> {
                { "SurfaceID", gridOptions.SurfaceId },
                { "ParentSurfaceItemId", parentItemId },
                { "RowLimit", int.MaxValue },
                { "GridOnly", false },
                { "UserId", userId },
                { "Cols", gridOptions.Columns },
                { "OrderBy", gridOptions.DefaultSort }
            };

            return this.DynamicListFromSql(sqlQuery, paramters);
        }

        #endregion

        #region query tables

        public IEnumerable<dynamic> GetSurfaceQueryData<TSurface>(int userId, int startIndex = 0, int maxRows = int.MaxValue,
            string orderBy = "", string search = "", string filter = "", bool showAllFields = false)
            where TSurface : ISurfaceEntity
        {
            var surface = GetSurface<TSurface>();
            var query = @"sp_GetSurfaceQueryDataPaged @SurfaceID, @StartIndex, @MaxRows, @OrderBy, @Search, @UserLink, @Filter, @UserId, @ShowAllFields";

            Dictionary<string, object> parameters = new Dictionary<string, object>
                {
                    { "SurfaceID", surface.recId },
                    { "StartIndex", startIndex },
                    {  "MaxRows", maxRows },
                    { "OrderBy", orderBy },
                    { "Search", search },
                    { "UserLink", 0 },
                    { "Filter", filter },
                    { "UserId", userId },
                    { "ShowAllFields", showAllFields }
                };

            return this.DynamicListFromSql(query, parameters);
        }

        public dynamic GetSurfaceQueryItemData<TSurface>(int itemId)
            where TSurface : ISurfaceEntity
        {
            var surface = GetSurface<TSurface>();
            var sqlQuery = "sp_GetSurfaceQueryItemData @SurfaceID, @SurfaceItemID";
            var parameters = new Dictionary<string, object>
            {
                { "SurfaceID", surface.recId },
                { "SurfaceItemID", itemId }
            };

            return this.DynamicListFromSql(sqlQuery, parameters)
                .FirstOrDefault();
        }

        #endregion

        #region picklists

        public IEnumerable<dynamic> GetSurfaceModulePickList(int surfaceFieldId)
        {
            var sqlQuery = "sp_GetSurfaceModulePicklist @surfaceFieldId";
            var parameters = new Dictionary<string, object>
            {
                { "surfaceFieldId", surfaceFieldId }
            };

            return this.DynamicListFromSql(sqlQuery, parameters);
        }

        #endregion

        #region EXPERIMENTAL

        protected TSurfaceItem SaveSurfaceData<TSurfaceItem>(TSurfaceItem entity)
            where TSurfaceItem : class, ISurfaceQueryEntity
        {
            var surfaceName = GetSurfaceName(entity.GetType().Name);

            var surface = Surfaces
                    .Include(p => p.SurfaceFields)
                    .Include(p => p.SurfaceFields.Select(q => q.SurfaceFieldType))
                    .Where(p => p.name == surfaceName)
                    .FirstOrDefault();

            var lookupCatetoryIds = surface.SurfaceFields
                .Where(p => p.lookupCategory.HasValue && p.lookupCategory > 0)
                .Select(p => p.lookupCategory.GetValueOrDefault())
                .Distinct().ToList();

            var lookups = SurfaceLookups.Where(p => lookupCatetoryIds.Contains(p.categoryId)).ToList();

            var entityProperties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            // 3 Text, 4 Number, 5 Decimal. 6 Picklist, 7 Date, 8 Checkbox, 9 RadioButtonList, 10 Button, 14 Formula Field, 
            // 15 Time, 17 Image, 20 Caption, 25 Address, 26 CheckboxList, 29 Label, 30 MultiPicklist
            var typeIds = new int[] { 3, 4, 5, 6, 7, 8, 9, 10, 14, 15, 17, 20, 25, 26, 29, 30 };
            var commandQueries = new Dictionary<string, Dictionary<string, SqlParameter[]>>();
            var data = new SurfaceFieldData();
            var paramIndex = 0;

            if (entity.itemID <= 0)
            {
                // INSERTS
                var surfaceItem = new SurfaceItem()
                {
                    createdBy = User.Id,
                    surfaceId = surface.recId
                };

                surfaceItem = Create(surfaceItem);
                entity.itemID = surfaceItem.recId;
                entity.surfaceId = surface.recId;
            }

            foreach (var sField in surface.SurfaceFields)
            {
                if (!typeIds.Contains(sField.surfaceFieldTypeId)) continue;

                var matchProperties = new Dictionary<string, object>()
                {
                    { nameof(data.surfaceId), entity.surfaceId },
                    { nameof(data.surfaceItemId), entity.itemID },
                    { nameof(data.surfaceFieldID), sField.recId },
                };

                var queryProperties = new Dictionary<string, object>()
                {
                    { nameof(data.surfaceFieldValueDate), data.surfaceFieldValueDate },
                    { nameof(data.surfaceFieldValueDecimal), data.surfaceFieldValueDecimal },
                    { nameof(data.surfaceFieldValueChar), data.surfaceFieldValueChar },
                    { nameof(data.surfaceFieldValueNum), data.surfaceFieldValueNum },
                    { nameof(data.surfaceFieldValueBool), data.surfaceFieldValueBool },
                    { nameof(data.surfaceFieldValueTime), data.surfaceFieldValueTime },
                    { nameof(data.surfaceFieldLookupID), data.surfaceFieldLookupID },
                };

                var property = entityProperties.FirstOrDefault(p =>
                    sField.surfaceFieldName.EqualsIgnoreCase(p.Name)
                    || sField.surfaceFieldName.EqualsIgnoreCase(p.GetCustomAttribute<ColumnAttribute>()?.Name ?? ""));

                if (property.IsNull()) continue;

                var propertyValue = property.GetValue(entity);

                var fieldType = (pNums.FieldType)sField.surfaceFieldTypeId;

                switch (fieldType)
                {
                    case pNums.FieldType.Checkbox:
                        queryProperties[nameof(data.surfaceFieldValueBool)] = propertyValue ?? false;
                        break;
                    case pNums.FieldType.Date:
                        queryProperties[nameof(data.surfaceFieldValueDate)] = propertyValue ?? new DateTime(1900, 1, 1);
                        break;
                    case pNums.FieldType.Decimal:
                        queryProperties[nameof(data.surfaceFieldValueDecimal)] = propertyValue ?? 0;
                        break;
                    case pNums.FieldType.Number:
                        queryProperties[nameof(data.surfaceFieldValueNum)] = propertyValue ?? 0;
                        break;
                    case pNums.FieldType.Time:
                        queryProperties[nameof(data.surfaceFieldValueTime)] = propertyValue ?? new DateTime(1900, 1, 1);
                        break;
                    //case pNums.FieldType.Address: ??
                    //    break;
                    default:
                        queryProperties[nameof(data.surfaceFieldValueChar)] = propertyValue ?? string.Empty;
                        break;
                }

                // lookupId
                if (sField.lookupCategory.HasValue && sField.lookupCategory.Value > 0)
                {
                    var lookupId = lookups.FirstOrDefault(p => p.display.EqualsIgnoreCase(propertyValue.ToString()))?.recId;
                    queryProperties[nameof(data.surfaceFieldLookupID)] = lookupId ?? 0;
                }

                var commandText = new StringBuilder("IF NOT EXISTS (SELECT 1 FROM pal_SurfaceFieldData ")
                    .Append($"WHERE surfaceId = @surfaceId AND surfaceItemId = @surfaceItemId AND surfaceFieldID = @surfaceFieldID)")
                    .AppendLine()
                    .Append("INSERT INTO [dbo].[pal_SurfaceFieldData] (")
                    .Append(string.Join(",", queryProperties.Keys.Select(p => $"[{p}]").ToArray()))
                    .Append(",")
                    .Append(string.Join(",", matchProperties.Keys.Select(p => $"[{p}]").ToArray()))
                    .Append(") VALUES (")
                    .Append(string.Join(",", queryProperties.Keys.Select(p => $"@{p}").ToArray()))
                    .Append(",")
                    .Append(string.Join(",", matchProperties.Keys.Select(p => $"@{p}").ToArray()))
                    .Append(")")
                    .AppendLine()
                    .Append("ELSE")
                    .AppendLine()
                    .Append("UPDATE [dbo].[pal_SurfaceFieldData] SET")
                    .Append(string.Join(",", queryProperties.Keys.Select(p => $" [{p}] = @{p}")))
                    .Append(" WHERE ")
                    .Append(string.Join(" AND ", matchProperties.Keys.Select(p => $"[{p}] = @{p}")));

                var commandParameters = matchProperties.Concat(queryProperties)
                    .Select(p => new SqlParameter(p.Key, p.Value)).ToArray();


                commandQueries.Add(sField.surfaceFieldName, new Dictionary<string, SqlParameter[]>() { { commandText.ToString(), commandParameters } });

                paramIndex++;
            }

            // Execute all inserts or updates (need to test speed and locks if using transactions)
            //using (var transaction = Database.BeginTransaction())
            //{
            if (entity.recId > 0)
            {
                var sql_update = $"UPDATE pal_SurfaceItem SET updatedBy = {User.Id}, dateUpdated = GETDATE() WHERE recId = {entity.itemID}";
                Database.ExecuteSqlCommand(sql_update);
            }

            foreach (var cmd in commandQueries.Values)
            {
                foreach (var pair in cmd)
                {
                    Database.ExecuteSqlCommand(pair.Key, pair.Value);
                }
            }

            //    transaction.Commit();
            //}

            //this.Set<TSurfaceItem>().Add()

            return entity;
        }

        #region Active Record Style CRUD

        public TEntity Create<TEntity>(TEntity entity)
            where TEntity : class//, IEntity
        {
            using (var db = new QuotesContext())
            {
                db.Set<TEntity>().Attach(entity);
                db.Entry(entity).State = EntityState.Added;
                db.SaveChanges();
            }

            return entity;
        }

        public IEnumerable<TEntity> Create<TEntity>(IEnumerable<TEntity> entities)
            where TEntity : class, IEntity
        {
            using (var db = new QuotesContext())
            {
                foreach (var entity in entities)
                {
                    db.Set<TEntity>().Attach(entity);
                    db.Entry(entity).State = EntityState.Added;
                }

                db.SaveChanges();
            }

            return entities;
        }

        public TEntity Update<TEntity>(TEntity entity)
            where TEntity : class
        {
            using (var db = new QuotesContext())
            {
                db.Set<TEntity>().Attach(entity);
                db.Entry(entity).State = EntityState.Modified;
                db.SaveChanges();
            }

            return entity;
        }

        public IEnumerable<TEntity> Update<TEntity>(IEnumerable<TEntity> entities)
            where TEntity : class, IEntity
        {
            using (var db = new QuotesContext())
            {
                foreach (var entity in entities)
                {
                    db.Set<TEntity>().Attach(entity);
                    db.Entry(entity).State = EntityState.Modified;
                }

                db.SaveChanges();
            }

            return entities;
        }

        public int Delete<TEntity>(TEntity entity)
           where TEntity : class//, IEntity
        {
            int result = 0;

            using (var db = new QuotesContext())
            {
                db.Set<TEntity>().Attach(entity);
                db.Entry(entity).State = EntityState.Deleted;
                result = db.SaveChanges();
            }

            return result;
        }

        public int Delete<TEntity>(IEnumerable<TEntity> entities)
           where TEntity : class, IEntity
        {
            int result = 0;

            using (var db = new QuotesContext())
            {
                foreach (var entity in entities)
                {
                    db.Set<TEntity>().Attach(entity);
                    db.Entry(entity).State = EntityState.Deleted;
                }

                result = db.SaveChanges();
            }

            return result;
        }

        #endregion

        #endregion
    }
}