using CAPI.Custom.Entities;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Data.Entity.Design.PluralizationServices;
using System.Globalization;
using System.Data.Entity.Validation;
using CAPI.Custom.Security;
using System.Threading;
using CAPI.Custom.Entities.publ.PurchaseOrders;
using System.Data.Entity.Infrastructure;

namespace CAPI.Custom.Data
{
	public partial class QuotesContext : DbContext
	{
        protected bool EnableSurfaceDataSaving = true;
        protected readonly string ConnectionStringName;

        public ICustomPrincipal User => (Thread.CurrentPrincipal as ICustomPrincipal) ?? new CustomPrincipal("");

        public static QuotesContext Create()
        {
            return new QuotesContext();
        }

        #region Constructors

        public QuotesContext() : this("name=Quotes") { }

        public QuotesContext(string nameOrConnectionString) 
            : base(nameOrConnectionString)
        {
            // prevents EF from attempting to create 
            // the database or generate migrations.
            Database.SetInitializer<QuotesContext>(null);
            //Database.Log = s => System.IO.File.AppendAllLines(@"c:\temp\ef.log", new string[] { "", s });

            Database.CommandTimeout = 180;

            if (nameOrConnectionString.StartsWith("name=", StringComparison.OrdinalIgnoreCase))
            {
                ConnectionStringName = nameOrConnectionString.Replace("name=", "");
            }

            RegisterEvents();
        }

        protected override void Dispose(bool disposing)
        {
            UnregisterEvents();
            base.Dispose(disposing);
        }

        #endregion

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            modelBuilder.Configurations.AddFromAssembly(GetType().Assembly);

            modelBuilder.Entity<PurchaseOrderDocumentType>().HasMany(u => u.PurchaseOrderStatuses)
                          .WithMany()
                          .Map(ru =>
                          {
                              ru.MapLeftKey("DocumentTypeId");
                              ru.MapRightKey("PurchaseOrderStatusId");
                              ru.ToTable("publ_PurchaseOrderDocumentTypeStatus");
                          });

            modelBuilder.Entity<PurchaseOrderDocumentType>().HasMany(u => u.DocumentStatuses)
                          .WithMany()
                          .Map(ru =>
                          {
                              ru.MapLeftKey("DocumentTypeId");
                              ru.MapRightKey("DocumentStatusId");
                              ru.ToTable("publ_PurchaseOrderDocumentTypeDocumentStatus");
                          });

            modelBuilder
                .Entity<PurchaseOrder>()
                .Property(p => p.RowVersion)
                .IsConcurrencyToken();

            base.OnModelCreating(modelBuilder);
        }

        #region Mapped Tables

        #region Views

        public DbSet<vUserShared> Users { get; set; }
		public DbSet<vQuote> vQuotes { get; set; }
		public DbSet<vQuoteGridItem> vQuoteGridItems { get; set; }
		//public DbSet<vQuoteItem> vQuoteItems { get; set; }

        #endregion

        public DbSet<Surface> Surfaces { get; set; }
		public DbSet<SurfaceField> SurfaceFields { get; set; }
		public DbSet<SurfaceFieldData> SurfaceFieldDatas { get; set; }
		public DbSet<SurfaceFieldType> SurfaceFieldTypes { get; set; }
		public DbSet<SurfaceItem> SurfaceItems { get; set; }
		public DbSet<Customer> Customers { get; set; }
		public DbSet<Contact> Contacts { get; set; }
		public DbSet<SurfaceLookupCategory> SurfaceLookupCategories { get; set; }
		public DbSet<SurfaceLookup> SurfaceLookups { get; set; }
		public DbSet<Quote> Quotes { get; set; }
		public DbSet<QuoteItem> QuoteItems { get; set; }
		//public DbSet<QuoteItemsPriceCheck> QuoteItemsPriceChecks { get; set; }
		public DbSet<QuoteEnquiryItem> QuoteEnquiryItems { get; set; }
		public DbSet<QuoteRequestItem> QuoteRequestItems { get; set; }
		public DbSet<QuoteBuyoutSupplier> QuoteBuyoutSuppliers { get; set; }
		public DbSet<QuoteBuyoutItem> QuoteBuyoutItems { get; set; }
        public DbSet<QuoteItemsFinal> QuoteItemsFinal { get; set; }
		public DbSet<PartsAlternate> PartsAlternates { get; set; }
		public DbSet<PartsMaster> PartsMasters { get; set; }
		public DbSet<Sale> Sales { get; set; }
		public DbSet<SalesExtraNote> SalesExtraNotes { get; set; }
		public DbSet<Supplier> Suppliers { get; set; }
		public DbSet<ExchangeRateManagement> ExchangeRates { get; set; }
		public DbSet<FreightManagement> Freights { get; set; }
		public DbSet<Brand> Brands { get; set; }
		public DbSet<Condition> Conditions { get; set; }
		public DbSet<SupplierPricelistPart> SupplierPricelistParts { get; set; }
		public DbSet<Setup> Setups { get; set; }
		public DbSet<Currency> Currencies { get; set; }
		public DbSet<InventoryItem> InventoryItems { get; set; }
        public DbSet<Template> Templates { get; set; }
        //public DbSet<InventoryLocation> InventoryLocations { get; set; }
        public DbSet<InventoryManagement> InventoryManagement { get; set; }
        public DbSet<PurchaseOrderStatus> PurchaseOrderStatuses { get; set; }
        public DbSet<PurchaseOrder> PurchaseOrders { get; set; }
        public DbSet<PurchaseOrderType> PurchaseOrderTypes { get; set; }
        public DbSet<PurchaseOrderDocumentStatus> PurchaseOrderDocumentStatuses { get; set; }
        public DbSet<PurchaseOrderDocumentType> PurchaseOrderDocumentTypes { get; set; }
        public DbSet<PurchaseOrderQuoteItemFinal> PurchaseOrderQuoteItemFinalProxy { get; set; }
        public DbSet<PurchaseOrderInvoiceQuoteItemFinal> PurchaseOrderInvoiceQuoteItemFinalProxy { get; set; }
        public DbSet<PurchaseOrderPickingSlipQuoteItemFinal> PurchaseOrderPickingSlipQuoteItemFinalProxy { get; set; }
        public DbSet<SupplierPricelistImportItem> SupplierPriceListImportHistory { get; set; }


        #endregion

        private Setup _setup;

        public Setup Setup
        {
            get
            {
                if (_setup.IsNull())
                {
                    _setup = Setups.FirstOrDefault(p => p.isActive.Value);
                }

                return _setup;
            }
        }

        #region surface lookups

        public IQueryable<SurfaceLookup> GetSurfaceLookupsByCategoryName(string categoryName)
		{
			return SurfaceLookups.Where(p =>
				p.isActive.Value
				&& p.LookupCategory.lookupCategory.Equals(categoryName,
				StringComparison.OrdinalIgnoreCase))
				.OrderBy(p => p.sequence);
		}

		public IQueryable<SurfaceLookup> GetQuotesStatuses()
		{
			return GetSurfaceLookupsByCategoryName(Constants.LooupCategoryNames.QuoteStatus);
		}

		public IQueryable<SurfaceLookup> GetBuyoutStatuses()
		{
			return GetSurfaceLookupsByCategoryName(Constants.LooupCategoryNames.BuyoutStatus);
		}

		public SurfaceLookup GetBuyoutStatusesQuoteReceived()
		{
			return GetBuyoutStatuses().SingleOrDefault(p => "Quote Received".Equals(p.display));
		}

		public IQueryable<SurfaceLookup> GetBuyoutQuoteTypes()
		{
			return GetSurfaceLookupsByCategoryName("Buyout Quote Type");
		}

        #endregion

        public override int SaveChanges()
        {
            int userId = User.Id;
            int addedCount = 0, modifiedCount = 0, deletedCount = 0;

            this.ChangeTracker.DetectChanges();

            #region Cancel changes for any object that implement IView

            var views = this.ChangeTracker.Entries()
                .Where(t => (t is IViewEntity))
                .ToList();

            foreach (var view in views)
            {
                this.Entry(view).State = EntityState.Unchanged;
            }

            #endregion

            #region Intercept and handle inserting surface items

            var added = this.ChangeTracker.Entries()
                        .Where(t => t.State == EntityState.Added)
                        .Select(t => t.Entity)
                        .ToArray();

            foreach (var entity in added)
            {
                if (entity is ISurfaceEntity)
                {

                    if (EnableSurfaceDataSaving && entity is ISurfaceQueryEntity)
                    {
                        var created = SaveSurfaceData(entity as ISurfaceQueryEntity);
                        (entity as ISurfaceEntity).itemID = created.itemID;
                        (entity as ISurfaceEntity).isActive = true;
                        (entity as ISurfaceEntity).surfaceId = created.surfaceId;
                    }
                    else
                    {
                        int surfaceId = GetSurface(entity.GetType().Name).recId;
                        string sql_new = $@"INSERT INTO [dbo].[pal_SurfaceItem] ([surfaceId],[dateCreated],[isActive],[createdBy],[dateUpdated],[updatedBy]) 
					        OUTPUT INSERTED.recId VALUES ({surfaceId}, GETDATE(), 1, {userId}, GETDATE(), {userId});";

                        var newItemId = Database.SqlQuery<int>(sql_new).SingleOrDefault();

                        (entity as ISurfaceEntity).itemID = newItemId;
                        (entity as ISurfaceEntity).isActive = true;
                        (entity as ISurfaceEntity).surfaceId = surfaceId;
                    }

                    addedCount++;
                }
            }

            #endregion

            #region Intercept and handle updated surface items

            var modified = this.ChangeTracker.Entries()
                        .Where(t => t.State == EntityState.Modified)
                        .Select(t => t.Entity)
                        .ToArray();

            foreach (var entity in modified)
            {
                if (entity is ISurfaceEntity)
                {
                    if (EnableSurfaceDataSaving && entity is ISurfaceQueryEntity)
                    {
                        var updated = SaveSurfaceData(entity as ISurfaceQueryEntity);
                    }
                    else
                    {
                        var itemId = (entity as ISurfaceEntity).itemID;
                        var sql_update = $"UPDATE pal_SurfaceItem SET updatedBy = {userId}, dateUpdated = GETDATE() WHERE recId = {itemId}";
                        Database.ExecuteSqlCommand(sql_update);
                    }

                    modifiedCount++;
                }
            }

            #endregion

            #region Intercept and handle deleted surface items

            var deleted = this.ChangeTracker.Entries()
                .Where(t => t.State == EntityState.Deleted)
                .Select(t => t.Entity)
                .ToArray();

            foreach (var entity in deleted)
            {
                if (entity is ISurfaceEntity)
                {
                    var itemId = entity.GetPropertyValue<int>("itemID");
                    var sql_delete = $@"UPDATE [dbo].[pal_SurfaceItem] 
						SET [isActive] = 0
							,[updatedBy] = {userId}
							,[dateUpdated] = GETDATE()
							,[isDeleted] = 1
						WHERE recId = {itemId}";
                    this.Database.ExecuteSqlCommand(sql_delete);
                    (entity as ISurfaceEntity).isActive = false;
                    this.Entry(entity).State = EntityState.Modified;
                    deletedCount++;
                }
            }

            #endregion

            BeforeUpdateChecks(this.ChangeTracker);

            try
            {
                return base.SaveChanges();
            }
            catch (DbEntityValidationException ex)

            {
                // Retrieve the error messages as a list of strings.
                var errorMessages = ex.EntityValidationErrors
                        .SelectMany(x => x.ValidationErrors)
                        .Select(x => x.ErrorMessage);

                // Join the list to a single string.
                var fullErrorMessage = string.Join("; ", errorMessages);

                // Combine the original exception message with the new one.
                var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage);

                // Throw a new DbEntityValidationException with the improved exception message.
                BusinessUtils.HandleException(ex);
                System.Diagnostics.Debug.WriteLine(exceptionMessage, ex.EntityValidationErrors);
                throw;
            }
        }

        #region Stored Procedures

        public List<PartResult> GetParts(IEnumerable<string> partNumbers, string delimeter = "|")
        {
            var partNumbersParamValue = string.Join(delimeter, partNumbers.ToArray());
            var query = @"sp_CAPI1_GetPartsAndAlternates @partNumbers, @delimeter";
            var result = Database.SqlQuery<PartResult>(query,
                new SqlParameter("@partNumbers", partNumbersParamValue),
                new SqlParameter("@delimeter", delimeter))
                .ToList();

            return result;
        }

        #endregion

        public void BeforeUpdateChecks(DbChangeTracker changeTracker)
        {
            #region Cancel changes for any object that implement IView

            //var views = changeTracker.Entries<IViewEntity>()
            //    .Where(t => t.State != EntityState.Unchanged)
            //    .ToList();

            //foreach (var view in views)
            //{
            //    this.Entry(view).State = EntityState.Unchanged;
            //}

            #endregion

            #region Create Quotes
            var createdQuotes = changeTracker.Entries<Quote>()
                .Where(t => t.State == EntityState.Added);

            if (createdQuotes.Any())
            {
                var user = Users.Find(User.Id);

                foreach (var quote in createdQuotes)
                {
                    quote.Entity.CreatedDate = quote.Entity.CreatedDate ?? DateTime.Now;
                    quote.Entity.CreatedBy = quote.Entity.CreatedBy ?? User.Id;
                    quote.Entity.CreatedByDisplay = quote.Entity.CreatedByDisplay.NullOrEmptyCoalesce(user.userDisplay);
                }
            }

            #endregion

            #region Update Quotes

            var updatedQuotes = changeTracker.Entries<Quote>()
                .Where(t => t.State == EntityState.Modified);

            if (updatedQuotes.Any())
            {
                var user = Users.Find(User.Id);

                foreach (var quote in createdQuotes)
                {
                    quote.Entity.LastUpdatedBy = User.Id;
                    quote.Entity.LastUpdatedByDisplay = Users.Find(User.Id)?.userDisplay ?? string.Empty;
                    quote.Entity.LastUpdatedDate = DateTime.Now;
                }
            }

            #endregion

            #region Update Quote Items

            var updatedQuoteItems = changeTracker.Entries<QuoteItem>()
                .Where(t => t.State == EntityState.Modified);

            foreach(var item in updatedQuoteItems)
            {
                // CEP-141 - Quote Items (Prevent Selections Greater Than Available)
                if (item.Entity.QuantitySelected >
                    item.Entity.QuantityAvailable)
                {
                    item.Entity.QuantitySelected =
                        item.Entity.QuantityAvailable;
                }
            }

            #endregion
        }
    }
}