using CAPI.Custom.Data;
using CAPI.Custom.Entities;
using framework_business;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CAPI.Custom.Services.Quotes
{
    public class BuyoutSupplierService : SurfaceChildItemService<QuoteBuyoutSupplier>
    {
        public BuyoutSupplierService(QuotesContext db = null)
        {
            if (db.IsNotNull())
            {
                this.db = db;
            }
        }

        // Check for updated and 
        //if (updated.IsQuoteReceived())
        //{
        //    db.QuoteBuyoutSuppliers_ExportQuoteItems(model.itemID);
        //}

        public override IQueryable<QuoteBuyoutSupplier> GetByParentId(int parentId) => 
            base.GetByParentId(parentId)
            .OrderBy(p => p.TradingName);

        public QuoteBuyoutSupplier Create(int quoteId, int supplierId)
        {
            var supplier = db.Suppliers.FirstOrDefault(p => p.itemID == supplierId);

            var buyoutSupplier = db.QuoteBuyoutSuppliers
                .FirstOrDefault(p =>
                    p.isActive.Value
                    && p.ParentSurfaceItemId == quoteId
                    && p.TradingName
                        .Equals(supplier.TradingName,
                        StringComparison.OrdinalIgnoreCase)
                );

            if (buyoutSupplier.IsNotNull())
            {
                return buyoutSupplier;
            }

            string currency = supplier.Currency;
            decimal exchangeRate = 0m;

            if (!new string[] { "", "ZAR " }.Contains(currency))
            {
                exchangeRate = db.ExchangeRates.FirstOrDefault(p =>
                     p.ForexCurrency.Equals(currency))
                    ?.BuyExchangeRate.TryParseDecimal()
                    .GetValueOrDefault() ?? 0m;
            }

            var freight = db.Freights
                .FirstOrDefault(p =>
                    p.SupplierTradingName.Equals(
                    supplier.TradingName)
                )?.Freight.TryParseDecimal().GetValueOrDefault()
                ?? 0m;

            buyoutSupplier = new QuoteBuyoutSupplier
            {
                isActive = true,
                ParentSurfaceItemId = quoteId,
                ContactPerson = supplier.Contacts,
                Currency = supplier.Currency,
                DefaultDiscount = supplier.DefaultDiscount,
                DefaultLeadtime = supplier.DefaultLeadtime,
                Duty = 0m,
                Email = supplier.PrimaryEmail,
                ExchangeRate = exchangeRate,
                Freight = freight,
                Notes = supplier.Notes,
                //QuoteBuyoutSuppliers_QuoteBuyoutSuppliers_QuoteBuyoutSupplierStatus = supplier.SupplierDetails_SupplierDetails_SupplierStatus,
                Telephone = supplier.Telephone,
                VATRate = supplier.VATRate.TryParseDecimal(),
                TradingName = supplier.TradingName,
                BuyoutQuoteType = BuyoutQuoteType.SelectedItems, // 1137 // Selected Items
            };

            buyoutSupplier = Create(buyoutSupplier);
            CreateBuyoutItems(buyoutSupplier);

            return buyoutSupplier;
        }

        public IQueryable<QuoteBuyoutItem> CreateBuyoutItems(int buyoutSupplierId)
        {
            return CreateBuyoutItems(GetByItemId(buyoutSupplierId));
        }

        public IQueryable<QuoteBuyoutItem> CreateBuyoutItems(QuoteBuyoutSupplier buyoutSupplier)
        {
            var nonStockOnlyTypeId = db.SurfaceLookups
                .Where(p =>
                    p.isActive.Value
                    && p.LookupCategory.lookupCategory.Equals("Buyout Quote Type", StringComparison.OrdinalIgnoreCase)
                    && "Non-Stock Only".Equals(p.display)
                )
                .Select(p => p.recId)
                .FirstOrDefault();

            //var nonStockOnlyTypeId = GetBuyoutQuoteTypes().FirstOrDefault(p => "Non-Stock Only".Equals(p.Display))?.Id;
            var buyoutSupplierId = buyoutSupplier.itemID;
            var quoteId = buyoutSupplier.ParentSurfaceItemId.GetValueOrDefault();
            var supplierName = buyoutSupplier.TradingName.Trim();
            var exRate = buyoutSupplier.ExchangeRate.GetValueOrDefault();
            var nonStockOnly = nonStockOnlyTypeId.Equals(buyoutSupplier.BuyoutQuoteType.GetValueOrDefault());

            try
            {
                //CVH 2019-03-20 Get buyout items already saved, need to update existing, instead of deleting and inserting new
                var existBuyoutItems = db.QuoteBuyoutItems.Where(p => p.ParentSurfaceItemId == buyoutSupplierId).ToList();
                //ArrayList existBuyoutItems = xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteBuyoutItems), "ParentSurfaceItemId", buyoutSupplierItemId.ToString(), "", "publ_");

                //load supplier quote items, if existing
                var quoteItems = db.QuoteItems
                    .Where(p =>
                        p.isActive.Value
                        && p.ParentSurfaceItemId == quoteId
                        && p.Supplier == supplierName
                    ).ToList();

                //ArrayList quoteItems = xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteItems), "isActive,ParentSurfaceItemId,QuoteItems_QuoteItems_QuoteItemsSupplier", "1," + quoteItemId + "," + supplierName, "", "publ_", false);
                if (quoteItems.Any())
                {
                    foreach (var quoteItem in quoteItems)
                    {
                        var buyObj = new QuoteBuyoutItem();

                        var existingBuyoutItem = existBuyoutItems
                            .Where(p =>
                                p.MasterPartNumber == quoteItem.MasterPartNumber
                                && p.PartNumber == quoteItem.PartNumber
                            ).ToArray();

                        //CVH 2019-03-20 Find existing buyout item (if any)
                        for (int i = existBuyoutItems.Count - 1; i >= 0; i--)
                        {
                            var existItm = existBuyoutItems[i];

                            if (quoteItem.PartNumber == existItm.PartNumber &&
                                quoteItem.MasterPartNumber == existItm.MasterPartNumber)
                            {
                                buyObj = existItm;

                                //remove item from list, will Delete all left over existing items (in case quote items have been removed)
                                existBuyoutItems.RemoveAt(i);
                            }
                        }

                        //create new item if none exist yet, but don't update if it does exist, nothing to update
                        if (buyObj.recId <= 0)
                        {
                            var currency = quoteItem.SupplierCurrency;
                            decimal eRate = 0;

                            if (!new string[] { "", "ZAR" }.Contains(currency))
                            {
                                eRate = db.ExchangeRates.FirstOrDefault(p => p.ForexCurrency == currency)
                                    ?.BuyExchangeRate.TryParseDecimal()
                                    .GetValueOrDefault() ?? 0m;
                            }

                            decimal zar = quoteItem.CostPrice.GetValueOrDefault();
                            decimal forex = 0;

                            if (eRate != 0)
                            {
                                forex = zar / eRate;
                            }

                            decimal supplierDiscount = buyoutSupplier.DefaultDiscount
                                .TryParseDecimal().GetValueOrDefault();
                            decimal supplierDuty = buyoutSupplier.Duty
                                .GetValueOrDefault();
                            decimal supplierFreight = buyoutSupplier.Freight
                                .GetValueOrDefault();

                            decimal dutyCost = BusinessUtils.GetSellingPrice(zar, supplierDuty) - zar;
                            decimal freightCost = BusinessUtils.GetSellingPrice(zar, supplierFreight) - zar;
                            decimal discount = (zar * supplierDiscount / 100m);
                            decimal totalCost = zar + freightCost + dutyCost - discount;

                            //                     oSurfaceItem buyItem = new oSurfaceItem();
                            //buyItem.createdBy = userId;
                            //buyItem.dateCreated = System.DateTime.Now;
                            //buyItem.isActive = true;
                            //buyItem.isDeleted = false;
                            //buyItem.surfaceId = 3059;//Quote Buyout Items
                            //buyItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), buyItem);

                            //buyObj.isActive = true;
                            //buyObj.itemID = buyItem.recId;
                            buyObj.ParentSurfaceItemId = buyoutSupplierId;
                            buyObj.MasterPartNumber = quoteItem.MasterPartNumber;
                            buyObj.Brand = quoteItem.Brand;
                            buyObj.Condition = quoteItem.Condition;

                            buyObj.CostPriceZAR = zar;
                            buyObj.CostPriceForex = forex;
                            buyObj.Description = quoteItem.Description;

                            buyObj.Discount = supplierDiscount;
                            buyObj.DutyCost = dutyCost;
                            buyObj.FreightCost = freightCost;
                            buyObj.Leadtime = buyoutSupplier.DefaultLeadtime;
                            buyObj.PartNumber = quoteItem.PartNumber;
                            buyObj.QuantityAvailable = quoteItem.QuantityAvailable;
                            buyObj.QuantityRequired = quoteItem.QuantitySelected;
                            buyObj.TotalCost = totalCost;
                            buyObj.WeightKG = quoteItem.WeightKG;
                            buyObj.Sequence = quoteItem.Sequence;
                            buyObj.Selected = true;

                            buyObj = BusinessUtils.Create(buyObj);
                            //buyObj.surfaceId = buyItem.surfaceId;
                            //buyObj.recId = xData.SaveTyped("recId", typeof(oQuoteBuyoutItems), buyObj, "publ_", false);
                        }
                    }
                }
                else
                {
                    //string query = "select QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber, " +
                    //                "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription, SUM(QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired) as QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired " +
                    //                "from publ_QuoteRequestItems where isActive = 1 and ParentSurfaceItemId = " + base.ParentSurfaceItemId + " " +
                    //                "group by QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber, " +
                    //                "QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " +
                    //                "order by QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber";

                    // CVH 2019-01-24 Do Row number with order by master number desc, sequence number asc. 
                    // Otherwise it uses the wrong sequence number on the buyout item
                    string query = ";WITH cte AS " +
                                    "( " +
                                    "   SELECT *, " +
                                    "            ROW_NUMBER() " +
                                    "            OVER(PARTITION BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "            ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber DESC, QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC) AS rn " +
                                    "   FROM     publ_QuoteRequestItems " +
                                    "   WHERE    isActive = 1 AND ParentSurfaceItemId = " + quoteId + " " +
                                    ") " +
                                    "SELECT QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " +
                                    "        , SUM(QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired) AS QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired " +
                                    "FROM cte " +
                                    "WHERE rn = 1 " +
                                    "GROUP BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "		,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " +
                                    "		,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " +
                                    "       ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " +
                                    "       ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " +
                                    "ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber ";

                    DataTable dtRequestItems = xData.GetCustomTypedTable(query);

                    //CVH 2019-03-22 If Non-stock only, get the list request items that are CEP Inventory items, need to remove those from buyout items
                    var dtCEPInventoryReqItems = new List<SupplierPricelistPart>();
                    //DataTable dtCEPInventoryReqItems = new DataTable();

                    if (nonStockOnly && dtRequestItems.IsNotNull() && dtRequestItems.Rows.Count > 0)
                    {
                        HashSet<string> requestItemsHash = new HashSet<string>();
                        //string requestItems = string.Empty;

                        foreach (DataRow row in dtRequestItems.Rows)
                        {
                            string partNumber = row.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber");
                            requestItemsHash.Add(partNumber);
                            //if (requestItems.IsEmpty())
                            //	requestItems = "'" + row["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber"].ToString() + "'";
                            //else
                            //	requestItems += ",'" + row["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber"].ToString() + "'";
                        }

                        if (requestItemsHash.Any())
                        {
                            dtCEPInventoryReqItems = db.SupplierPricelistParts
                                .Where(p =>
                                    p.isActive.Value
                                    && "Current" == p.PricelistStatus
                                    && "CEP Inventory" == p.SupplierName
                                    && requestItemsHash.Contains(p.PartNumber)
                                ).ToList();
                            //string nsQuery = "select SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber from publ_SupplierPricelistParts p " +
                            //			" where p.isActive = 1 and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current' " +
                            //			" and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName = 'CEP Inventory' " +
                            //			" and p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber in (" + requestItems + ") ";

                            //                     dtCEPInventoryReqItems = xData.GetCustomTypedTable(nsQuery);
                        }
                    }

                    //load all requested items
                    foreach (DataRow reqItem in dtRequestItems.Rows)
                    {
                        string partNumber = reqItem.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber");
                        string masterNumber = reqItem.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber");

                        //CVH 2019-03-20 Don't create a buyout item for a "blank" item
                        if (partNumber.IsEmpty() && "Not found".Equals(masterNumber))
                            continue;

                        // CVH 2019-03-22 If data in CEP list, non stock has been selected, 
                        // so check if item exists in CEP list, if so skip and don't add as buyout item
                        if (dtCEPInventoryReqItems.Any())
                        {
                            if (dtCEPInventoryReqItems.Any(p => p.PartNumber == partNumber))
                                //if (dtCEPInventoryReqItems.Select("SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber = '" + 
                                //                         reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber"].ToString() + "'").Count() > 0)
                                continue;
                        }

                        var buyObj = new QuoteBuyoutItem();

                        //CVH 2019-03-20 Find existing buyout item (if any)
                        for (int i = existBuyoutItems.Count - 1; i >= 0; i--)
                        {
                            var existItm = existBuyoutItems[i];

                            if (partNumber == existItm.PartNumber &&
                                masterNumber == existItm.MasterPartNumber)
                            {
                                buyObj = existItm;

                                //remove item from list, will Delete all left over existing items (in case quote items have been removed)
                                existBuyoutItems.RemoveAt(i);
                            }
                        }

                        if (buyObj.recId <= 0)
                        {
                            decimal duty = BusinessUtils.GetSellingPrice(0, 0);
                            decimal freight = BusinessUtils.GetSellingPrice(0, 0);
                            decimal supplierDiscount = buyoutSupplier.DefaultDiscount.TryParseDecimal().GetValueOrDefault();

                            //decimal totalCost = zar + freightCost + dutyCost - discount;
                            //oSurfaceItem buyItem = new oSurfaceItem();
                            //buyItem.createdBy = userId;
                            //buyItem.dateCreated = System.DateTime.Now;
                            //buyItem.isActive = true;
                            //buyItem.isDeleted = false;
                            //buyItem.surfaceId = 3059;//Quote Buyout Items
                            //buyItem.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), buyItem);

                            //buyObj.isActive = true;
                            //buyObj.itemID = buyItem.recId;
                            buyObj.ParentSurfaceItemId = buyoutSupplierId;
                            buyObj.MasterPartNumber = masterNumber;
                            buyObj.Brand = "CEP";
                            buyObj.Condition = string.Empty;
                            buyObj.CostPriceForex = 0;
                            buyObj.CostPriceZAR = 0;
                            buyObj.Description = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription"].ToString();

                            buyObj.Discount = supplierDiscount;
                            buyObj.DutyCost = 0;
                            buyObj.FreightCost = 0;
                            buyObj.Leadtime = buyoutSupplier.DefaultLeadtime;
                            buyObj.PartNumber = partNumber;
                            buyObj.QuantityAvailable = 0;
                            int qty = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired"].ToString().TryParseInt().GetValueOrDefault();
                            buyObj.QuantityRequired = qty;
                            buyObj.TotalCost = 0;
                            decimal kg = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG"].ToString().TryParseDecimal().GetValueOrDefault();
                            buyObj.WeightKG = kg;
                            int sequence = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence"].ToString().TryParseInt().GetValueOrDefault();
                            buyObj.Sequence = sequence;
                            buyObj.Selected = true;
                            buyObj = BusinessUtils.Create(buyObj);
                            //buyObj.surfaceId = buyItem.surfaceId;
                            //buyObj.recId = xData.SaveTyped("recId", typeof(oQuoteBuyoutItems), buyObj, "publ_", false);
                        }
                        else
                        {
                            //CVH 2019-03-22 Need to make all selected (in case deselected before clicking the Non-stock only button)
                            buyObj.isActive = true;
                            buyObj.Selected = true;
                            BusinessUtils.Update(buyObj);
                            //xData.UpdateTyped("recId", buyObj.recId.ToString(), typeof(oQuoteBuyoutItems), buyObj, "publ_");
                        }
                    }
                }

                //CVH 2019-03-20 Remove all buyout items no longer in quote items list
                foreach (var remOjb in existBuyoutItems)
                {
                    foreach (oSurfaceItem remItm in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", remOjb.itemID.ToString()))
                    {
                        remItm.isDeleted = true;
                        remItm.isActive = false;
                        xData.UpdateTyped("recId", remItm.recId.ToString(), typeof(oSurfaceItem), remItm);
                    }

                    xData.DeletePublishedSurfaceItem(remOjb.surfaceId, remOjb.itemID);
                }
            }
            catch (Exception ex)
            {
                BusinessUtils.HandleException(ex);
                throw;
            }

            return db.QuoteBuyoutItems.Where(p => p.ParentSurfaceItemId == buyoutSupplierId && p.isActive.Value)
                .OrderBy(p => p.Sequence);
        }

        public void ExportQuoteItems(int buyoutSupplierId)
        {
            var buyoutSupplier = db.QuoteBuyoutSuppliers
                .Where(p => p.itemID == buyoutSupplierId)
                .Select(p => new
                {
                    p.itemID,
                    p.ParentSurfaceItemId,
                    TradingName = p.TradingName,
                    Currency = p.Currency
                }).FirstOrDefault();

            var quote = db.Quotes.GetByItemId(buyoutSupplier.ParentSurfaceItemId.Value)
                .Select(p => new
                {
                    p.itemID,
                    DefaultMargin = p.ItemsDefaultMargin
                }).FirstOrDefault();

            if (quote.IsNotNull())
            {
                var buyoutItems = db.QuoteBuyoutItems
                    .Where(p =>
                        p.isActive.Value
                        && p.ParentSurfaceItemId == buyoutSupplier.itemID
                        && p.Selected.Value
                        && p.QuantityAvailable > 0)
                    .Select(p => new
                    {
                        p.itemID,
                        p.ParentSurfaceItemId,
                        MasterNumber = p.MasterPartNumber,
                        Brand = p.Brand,
                        Condition = p.Condition,
                        Description = p.Description,
                        LeadTime = p.Leadtime,
                        PartNumber = p.PartNumber,
                        QuantityAvailable = p.QuantityAvailable ?? 0,
                        Sequence = p.Sequence,
                        TotalCost = p.TotalCost,
                        WeightKG = p.WeightKG ?? 0m,
                    })
                    .ToList();

                var quoteItems = db.QuoteItems.GetByParentId(buyoutSupplier.ParentSurfaceItemId.Value).ToList();

                foreach (var buyoutItem in buyoutItems)
                {
                    QuoteItem quoteItem = quoteItems.FirstOrDefault(p =>
                        p.MasterPartNumber == buyoutItem.MasterNumber
                        && p.Brand == buyoutItem.Brand
                        && p.Condition == buyoutItem.Condition
                        && p.Supplier == buyoutSupplier.TradingName)
                        ?? new QuoteItem();

                    quoteItem.Margin = quote.DefaultMargin.TryParseDecimal().GetValueOrDefault();
                    quoteItem.LeadTime = buyoutItem.LeadTime;
                    quoteItem.CostPrice = buyoutItem.TotalCost;

                    //decimal divideBy = 1m - (quoteItem.QuoteItems_QuoteItems_QuoteItemsMargin.GetValueOrDefault() / 100m);
                    quoteItem.CalculateSellingPrice(newMargin: quoteItem.Margin);// QuoteItems_QuoteItems_QuoteItemsSellingPrice = divideBy == 0 ? 0 : quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice / divideBy;
                    quoteItem.QuantityAvailable = buyoutItem.QuantityAvailable;

                    if (quoteItem.recId == 0)
                    {
                        quoteItem.ParentSurfaceItemId = quote.itemID;
                        quoteItem.MasterPartNumber = buyoutItem.MasterNumber;
                        quoteItem.Brand = buyoutItem.Brand;
                        quoteItem.Condition = buyoutItem.Condition;
                        quoteItem.Description = buyoutItem.Description;
                        quoteItem.PartNumber = buyoutItem.PartNumber;
                        quoteItem.Supplier = buyoutSupplier.TradingName;
                        quoteItem.WeightKG = buyoutItem.WeightKG;
                        quoteItem.WarehouseLocation = string.Empty;
                        quoteItem.SupplierCurrency = buyoutSupplier.Currency;
                        quoteItem.Sequence = buyoutItem.Sequence;

                        /* CVH 2019-01-22 Set supplier sequence:
                            0 - Inventory Items (not used)
                            1 - CEP Inventory pricelist
                            2 - CAT pricelist
                            3 - Other pricelists in alpha order
                            4 - Buyout supplier lists in alpha order
                            */
                        quoteItem.SupplierSequence = 4;

                        db.QuoteItems.Add(quoteItem);
                    }

                    db.SaveChanges();
                }

                //SupplierItemsExported?.Invoke(this, new QuoteEvent { QuoteId = quote.itemID });
            }
        }
    }
}