using System;
using System.Collections.Generic;
using System.Linq;
using CAPI.Custom;
using CAPI.Custom.Data;
using CAPI.Custom.Entities;
using System.Diagnostics;
using NLog;
using System.Reflection;
using System.Configuration;
using CAPI.Custom.Security;
using System.Threading;

namespace CEP._123
{
    class Program
    {
        static ILogger logger = LogManager.GetLogger("InventoryItemsImport");
        static QuotesContext db;

        static void Main(string[] args)
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US", false);
            //#if DEBUG
            //            args = new string[] { "Conn", "Quotes2" };
            //#endif
            AppDomain.CurrentDomain.UnhandledException += (s, a) =>
            {
                LogManager.GetCurrentClassLogger().Error(a.ExceptionObject as Exception, (a.ExceptionObject as Exception).Message);
            };

            var connectionStringName = "Quotes";

            if (args.Any(p => "ConnectionStringName".StartsWith(p, StringComparison.OrdinalIgnoreCase)))
            {
                var index = args.ToList().FindIndex(p => "ConnectionStringName".StartsWith(p, StringComparison.OrdinalIgnoreCase));
                if (args.Length > index + 1)
                {
                    connectionStringName = args[index + 1]?.Trim() ?? string.Empty;
                }
            }

            if (ConfigurationManager.ConnectionStrings[connectionStringName].IsNull())
            {
                var connStringNames = ConfigurationManager.ConnectionStrings
                    .Cast<ConnectionStringSettings>().Select(p => p.Name);

                logger.Error($"Connection String {connectionStringName.ToJson()} could not be found.");
                logger.Error($"Available Connection String Names are: {connStringNames.ToJson()}");
                return;
            }

            var settings = CEP._123.Properties.Settings.Default;
            logger.Info($"{DateTime.Now.ToLongDateString()} {DateTime.Now.ToLongTimeString()}");
            logger.Info("-------------------------------------");
            logger.Info("Importing Inventory Items");
            logger.Info("-------------------------------------");

            #region Set Principal
            int userId = 216;

            var principal = new CustomPrincipal(userId.ToString())
            {
                Id = userId
            };

            Thread.CurrentPrincipal = principal;
            #endregion

            //var items = new List<InventoryManagement>();

            var reader = new InventoryItemCsvReader(config: new CSVImportConfig
            {
                FilePath = settings.ImportFiles,
                HasHeaderRow = true,
            });

            var badItemCount = 0;
            var goodItemCount = 0;
            var totalRead = 0;
            var batchSize = settings.DataReadBatchSize;
            var currentBatch = 0;
            var batches = new Dictionary<int, List<InventoryManagement>>();
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);

            reader.ItemRead += (s, a) =>
            {
                if (settings.LimitDataRead && goodItemCount >= settings.DataReadLimit) return;

                if (IsValid(a.Item))
                {
                    if (goodItemCount % batchSize == 0)
                    {
                        currentBatch++;
                        batches.Add(currentBatch, new List<InventoryManagement>() { a.Item });
                    } else
                    {
                        batches[currentBatch].Add(a.Item);
                    }

                    logger.Info($"{a.LineNumber} Batch {currentBatch} Adding Item: {a.Item.ToJson()}");
                    goodItemCount++;
                }
                else
                {
                    badItemCount++;
                    logger.Warn($"{a.LineNumber} Bad Item ({badItemCount}): {new { a.Item.UniqueNumber }.ToJson()}");
                }
            };

            reader.Done += (s, a) =>
            {
                manualResetEvent.Set();
            };

            reader.StartRead();
            manualResetEvent.WaitOne();
            

            db = QuotesContext.Create();
            Stopwatch wTotal = Stopwatch.StartNew();

            foreach(var key in batches.Keys)
            {
                logger.Info($"Processing batch {key} of {batches.Count()}");
                var items = batches[key];
                var inventoryItems = GetInventoryItemsFromManagementItems(items);
                ImportInventoryItems(inventoryItems, batchSize: settings.InventoryItemBatchSize);
                ImportInventoryManagementItems(items, batchSize: settings.ManagementItemBatchSize);
            }

            db.Database.ExecuteSqlCommand("sp_CAPI1_UpdateMasterInventoryTotals");
            //PopulateInventoryItemTotals();

            logger.Info($"> End of line... {wTotal.Elapsed}");
            wTotal.Stop();
            Console.ReadKey();
        }

        //static List<InventoryManagement> ReadManagementItems(string filepath)
        //{
        //    logger.Info("Reading Inventory Management Items...");
        //    var timer = Stopwatch.StartNew();

        //    List<string> headers = new List<string>() {
        //        "Parts Master Number",
        //        "Alternate / Other Number",
        //        "Brand",
        //        "Condition",
        //        "Available",
        //        "Committed",
        //        "On Hand",
        //        "On Order",
        //        "Average Cost",
        //        "Warehouse Name",
        //        "Bin Location(s)"
        //    };

        //    List<InventoryManagement> items = new List<InventoryManagement>();

        //    using (var reader = new StreamReader(filepath))
        //    {
        //        // skip headers line
        //        reader.ReadLine();
        //        int lineNumber = 1;
        //        while (!reader.EndOfStream)
        //        {
        //            var line = reader.ReadLine().Split(',');

        //            // masterNumber same as partNumber
        //            var partNumber = line[headers.IndexOf("Alternate / Other Number")]?.Trim();
        //            var brand = line[headers.IndexOf("Brand")]?.Trim();
        //            var condition = line[headers.IndexOf("Condition")]?.Trim();
        //            var available = line[headers.IndexOf("Available")]?.Trim();
        //            var committed = line[headers.IndexOf("Committed")]?.Trim();
        //            var onHand = line[headers.IndexOf("On Hand")]?.Trim();
        //            var onOrder = line[headers.IndexOf("On Order")]?.Trim();
        //            var averageCost = line[headers.IndexOf("Average Cost")]?.Trim();
        //            var warehouseName = line[headers.IndexOf("Warehouse Name")]?.Trim();
        //            var binLocation = line[headers.IndexOf("Bin Location(s)")]?.Trim();

        //            var uniqueNumber = $"{partNumber}-{brand}-{condition}";
        //            var uniqueManagementNumber = $"{uniqueNumber}-{binLocation}";

        //            var scanned_item = new InventoryManagement()
        //            {
        //                AverageUnitCost = averageCost,
        //                BinLocation = binLocation,
        //                Brand = brand,
        //                Condition = condition,
        //                PartNumber = partNumber,
        //                WarehouseLocationName = warehouseName,
        //                Available = available,
        //                Committed = committed,
        //                OnHand = onHand,
        //                OnOrder = onOrder,
        //                UniqueNumber = uniqueNumber,
        //                TotalOnHandValue = (averageCost.TryParseDecimal().GetValueOrDefault() * onHand.TryParseDecimal().GetValueOrDefault()).ToString()
        //            };

        //            var skipItem = !IsValid(scanned_item);

        //            if (skipItem)
        //            {
        //                logger.Warn($"Found item with no PartNumber, Brand or Condition line: {lineNumber} {scanned_item.ToJson()}");
        //            }
        //            else
        //            {
        //                var item = items.FirstOrDefault(p => p.UniqueNumber == uniqueNumber && p.BinLocation == binLocation);

        //                if (item.IsNull())
        //                {
        //                    items.Add(scanned_item);
        //                }
        //            }

        //            lineNumber++;
        //        }

        //        reader.Close();
        //    }

        //    logger.Info($"Found {items.Count} managment items in {timer.Elapsed}.");
        //    return items;
        //}

        static List<InventoryItem> GetInventoryItemsFromManagementItems(IEnumerable<InventoryManagement> managementItems)
        {
            logger.Info("Creating Inventory Items");
            var timer = Stopwatch.StartNew();

            List<InventoryItem> inventoryItems = new List<InventoryItem>();

            foreach (var mItem in managementItems)
            {
                var exists = inventoryItems.Any(p => p.UniqueNumber == mItem.UniqueNumber);

                if (!exists)
                {
                    inventoryItems.Add(new InventoryItem
                    {
                        Brand = mItem.Brand,
                        Condition = mItem.Condition,
                        PartNumber = mItem.PartNumber,
                        Supplier = "INVENTORY",
                        UniqueNumber = mItem.UniqueNumber
                    });
                }
            }

            logger.Info($"Found {inventoryItems.Count} inventory items in {timer.Elapsed}");
            return inventoryItems;
        }

        static void ImportInventoryItems(IEnumerable<InventoryItem> inventoryItems, int batchSize = 20)
        {
            logger.Info("Importing Inventory Items");
            var timer = Stopwatch.StartNew();

            var stopWatch = new Stopwatch();

            int offsetIndex = 0;

            var batchItems = inventoryItems.Skip(offsetIndex).Take(batchSize).ToList();

            while (batchItems.Any())
            {
                logger.Info($"Processing Inventory Items {offsetIndex + 1} through {offsetIndex + batchSize} of {inventoryItems.Count()}");

                var uniqueNumbers = batchItems.Select(p => p.UniqueNumber).Distinct().ToArray();
                var partNumbers = batchItems.Select(p => p.PartNumber.ToUpper()).Distinct().ToArray();
                var existingInventoryItems = GetInventoryItemsByUniqueNumbers(uniqueNumbers);
                var batchMasters = GetParts(partNumbers);
                var masterNumbers = batchMasters.Select(p => p.MasterNumber.ToUpper()).Distinct().ToArray();
                var index = 1;

                foreach (var item in batchItems)
                {
                    logger.Info($"Processing Inventory Item {item.UniqueNumber.ToJson()}");

                    var part = batchMasters.GetFirstByPartNumber(item.PartNumber);

                    if (part.IsNull())
                    {
                        logger.Warn($"Could not find Part with number {item.PartNumber.ToJson()}");
                        continue;
                    }

                    var existing = existingInventoryItems.FirstOrDefault(p => p.UniqueNumber == item.UniqueNumber);

                    if (existing.IsNotNull())
                    {
                        existing.AverageCost = item.AverageCost;
                        existing.Brand = item.Brand;
                        existing.Condition = item.Condition;
                        existing.Description = CAPI.Custom.Extensions.NullOrEmptyCoalesce(item.Description, part?.Description);
                        existing.LeadTimedays = item.LeadTimedays;
                        existing.ParentSurfaceItemId = part.itemID;
                        existing.PartNumber = item.PartNumber;
                        existing.PartType = part.IsAlternate ? "Alternate" : "Master";
                        existing.ReorderQuantity = item.ReorderQuantity;
                        existing.ReviewCycledays = item.ReviewCycledays;
                        existing.SafetyStock = item.SafetyStock;
                        existing.SixMonthUsagedays = item.SixMonthUsagedays;
                        existing.Supplier = item.Supplier;
                        existing.TotalAvailable = item.TotalAvailable;
                        existing.TotalCommitted = item.TotalCommitted;
                        existing.TotalOnHand = item.TotalOnHand;
                        existing.TotalOnOrder = item.TotalOnOrder;
                        existing.TotalValue = item.TotalValue;
                        existing.UniqueNumber = item.UniqueNumber;
                        db.Entry(existing).State = System.Data.Entity.EntityState.Modified;
                        logger.Info($"{index} Updating item: {new { item.itemID, item.UniqueNumber }.ToJson()}");
                    }
                    else
                    {
                        // Create
                        logger.Info($"{index} Inserting Item: {new { item.UniqueNumber }.ToJson()}");
                        item.ParentSurfaceItemId = part.itemID;
                        item.Description = part?.Description ?? string.Empty;
                        item.PartType = part.IsAlternate ? "Alternate" : "Master";
                        db.InventoryItems.Add(item);
                    }

                    index++;
                }

                logger.Info($"Saving batch of {batchItems.Count}");
                var saveWatch = Stopwatch.StartNew();
                var saved = db.SaveChanges();
                saveWatch.Stop();
                logger.Info($"Saved {saved} rows in {saveWatch.Elapsed}");

                offsetIndex += batchSize;
                batchItems = inventoryItems.Skip(offsetIndex).Take(batchSize).ToList();
            }

            logger.Info($"Imported Inventory Items in {timer.Elapsed}");
        }

        static void ImportInventoryManagementItems(IEnumerable<InventoryManagement> managementItems, int batchSize = 20)
        {
            logger.Info("Importing Management Items...");
            var timer = Stopwatch.StartNew();
            var stopWatch = new Stopwatch();
            int offsetIndex = 0;
            int index = 1;

            var batchItems = managementItems.Skip(offsetIndex).Take(batchSize).ToList();

            while (batchItems.Any())
            {
                logger.Info($"Processing Management Items {offsetIndex + 1} through {offsetIndex + batchSize} of {managementItems.Count()}");
                var uniqueNumbers = batchItems.Select(p => p.UniqueNumber).Distinct().ToArray();
                var partNumbers = batchItems.Select(p => p.PartNumber).Distinct().ToList();
                var uniqueManagementNumbers = batchItems.Select(p => p.UniqueNumber).Distinct().ToArray();
                var existingItems = GetInventoryManagementItemsByUniqueNumbers(uniqueNumbers);

                logger.Info($"Querying Inventory Items in batch...");
                stopWatch.Restart();
                var inventoryItems = db.InventoryItems.Where(p => uniqueNumbers.Contains(p.UniqueNumber)).ToList();
                logger.Info($"Found {inventoryItems.Count} in {stopWatch.Elapsed}");

                stopWatch.Stop();

                int addedOrUpdatedItems = 0;

                foreach (var item in batchItems)
                {
                    var existing = existingItems.FirstOrDefault(p => p.UniqueNumber == item.UniqueNumber && p.BinLocation == item.BinLocation);
                    var inventoryItem = inventoryItems.FirstOrDefault(p => p.UniqueNumber == item.UniqueNumber);

                    if (inventoryItem.IsNull())
                    {
                        logger.Info($"Could not find Inventory Item: {item.UniqueNumber.ToJson()}");
                        continue;
                    }

                    if (existing.IsNotNull())
                    {
                        // Update
                        existing.Area = item.Area;
                        existing.Available = item.Available;
                        existing.AverageUnitCost = item.AverageUnitCost;
                        existing.Bay = item.Bay;
                        existing.Bin = item.Bin;
                        existing.BinLocation = item.BinLocation;
                        existing.Brand = item.Brand;
                        existing.Committed = item.Committed;
                        existing.Condition = item.Condition;
                        existing.Description = inventoryItem?.Description ?? item.Description ?? string.Empty;
                        existing.Isle = item.Isle;
                        existing.OnHand = item.OnHand;
                        existing.OnOrder = item.OnOrder;
                        existing.ParentSurfaceItemId = inventoryItem.itemID;
                        existing.PartNumber = item.PartNumber;
                        existing.Position = item.Position;
                        existing.Shelf = item.Shelf;

                        existing.TotalOnHandValue = (item.OnHand.GetValueOrDefault() * item.AverageUnitCost.GetValueOrDefault());

                        existing.UniqueNumber = item.UniqueNumber;
                        existing.WarehouseLocationName = item.WarehouseLocationName;
                        db.Entry(existing).State = System.Data.Entity.EntityState.Modified;
                        addedOrUpdatedItems++;
                        logger.Info($"{index} Updating item: {existing.ToJson()}");
                    }
                    else
                    {
                        logger.Info($"{index} Inserting Item: {item.ToJson()}");
                        item.ParentSurfaceItemId = inventoryItem.itemID;
                        item.Description = inventoryItem?.Description ?? item.Description ?? string.Empty;
                        db.InventoryManagement.Add(item);
                        addedOrUpdatedItems++;
                    }

                    index++;
                }

                if (addedOrUpdatedItems > 0)
                {
                    logger.Info($"Saving batch of {batchItems.Count}");
                    var saveWatch = Stopwatch.StartNew();
                    var saved = db.SaveChanges();
                    saveWatch.Stop();
                    logger.Info($"Saved {saved} rows in {saveWatch.Elapsed}");
                    addedOrUpdatedItems = 0;
                }
                else
                {
                    logger.Info($"Nothing to do here!");
                }

                offsetIndex += batchSize;
                batchItems = managementItems.Skip(offsetIndex).Take(batchSize).ToList();
               
            }

            logger.Info($"Processed Management Items in {timer.Elapsed}");
        }

        static List<PartResult> GetParts(IEnumerable<string> partNumbers)
        {
            var methodInfo = MethodInfo.GetCurrentMethod();
            logger.Info($"Entering {methodInfo.Name}({string.Join(", ", methodInfo.GetParameters().Select(p => p.Name).ToArray())})");
            var result = new List<PartResult>();

            if (partNumbers.Any())
            {
                var timer = Stopwatch.StartNew();
                logger.Info($"Querying Parts for $partNumbers");
                logger.Info($"$partNumbers: {partNumbers.ToJson()}");
                result = db.GetParts(partNumbers);
                logger.Info($"Returned {result.Count} rows in {timer.Elapsed}");
            }

            return result;
        }

        static PartResult GetPartMaster(string partNumber)
        {
            var part = db.Database.SqlQuery<PartResult>($"SELECT TOP 1 * FROM vw_CAPI1_PartMasters WHERE PartNumber = '{partNumber}'")
                .FirstOrDefault();
            return part;
        }

        static PartResult GetPartAlternate(string partNumber)
        {
            var part = db.Database.SqlQuery<PartResult>($"SELECT TOP 1 * FROM vw_CAPI1_PartAlternates WHERE AlternateNumber = '{partNumber}'")
                .FirstOrDefault();
            return part;
        }

        static List<InventoryItem> GetInventoryItemsByUniqueNumbers(IEnumerable<string> uniqueNumbers)
        {
            var methodInfo = MethodInfo.GetCurrentMethod();
            logger.Info($"Entering {methodInfo.Name}({string.Join(", ", methodInfo.GetParameters().Select(p => p.Name).ToArray())})");
            var result = new List<InventoryItem>();

            if (uniqueNumbers.Any())
            {
                var timer = Stopwatch.StartNew();
                logger.Info($"Querying Inventory Items for $uniqueNumbers");
                logger.Info($"$uniqueNumbers: {uniqueNumbers.ToJson()}");
                result = db.InventoryItems.Where(p => uniqueNumbers.Contains(p.UniqueNumber)).ToList();
                logger.Info($"Returned {result.Count} rows in {timer.Elapsed}");
            }

            return result;
        }

        static List<InventoryManagement> GetInventoryManagementItemsByUniqueNumbers(IEnumerable<string> uniqueNumbers)
        {
            var methodInfo = MethodInfo.GetCurrentMethod();
            logger.Info($"Entering {methodInfo.Name}({string.Join(", ", methodInfo.GetParameters().Select(p => p.Name).ToArray())})");
            var result = new List<InventoryManagement>();

            if (uniqueNumbers.Any())
            {
                var timer = Stopwatch.StartNew();
                logger.Info($"Querying Inventory Management Items for $uniqueNumbers");
                logger.Info($"$uniqueNumbers: {uniqueNumbers.ToJson()}");
                result = db.InventoryManagement.Where(p => uniqueNumbers.Contains(p.UniqueNumber)).ToList();
                logger.Info($"Returned {result.Count} rows in {timer.Elapsed}");
            }

            return result;
        }

        static void PopulateInventoryItemTotals()
        {
            logger.Info("Populate Inventory Item Totals...");
            var timer = Stopwatch.StartNew();

            int offsetIndex = 0;
            int pageSize = 50;

            var totalItems = db.InventoryItems.Count();
            var batch = db.InventoryItems.OrderBy(p => p.recId)
                .Skip(offsetIndex).Take(pageSize).ToList();

            while(batch.Any())
            {
                logger.Info($"Processing items {offsetIndex + 1} through {offsetIndex + pageSize} of {totalItems}");
                foreach(var item in batch)
                {
                    logger.Info($"Processing Item: {item.UniqueNumber.ToJson()}");
                    var childItems = db.InventoryManagement.Where(p => p.UniqueNumber == item.UniqueNumber).ToList();

                    logger.Info($"Found {childItems.Count} Management Items");

                    //decimal averageCost = 0;
                    //int totalAvailable = 0;
                    //int totalCommitted = 0;
                    //int totalOnHand = 0;
                    //int totalOnOrder = 0;

                    if (childItems.Any())
                    {
                        foreach (var cItem in childItems)
                        {
                            cItem.Available = cItem.OnHand.GetValueOrDefault() - cItem.Committed.GetValueOrDefault();
                        }

                        item.AverageCost = childItems.Average(p => p.AverageUnitCost ?? 0m);
                        item.TotalAvailable = childItems.Sum(p => p.Available ?? 0);
                        item.TotalCommitted = childItems.Sum(p => p.Committed ?? 0);
                        item.TotalOnHand = childItems.Sum(p => p.OnHand ?? 0);
                        item.TotalOnOrder = childItems.Sum(p => p.OnOrder ?? 0);

                        item.TotalAvailable -= item.TotalCommitted.GetValueOrDefault();
                        item.TotalValue = item.TotalOnHand.GetValueOrDefault() * item.AverageCost.GetValueOrDefault();
                    }
                }

                db.SaveChanges();

                offsetIndex += pageSize;
                batch = db.InventoryItems.OrderBy(p => p.recId)
                    .Skip(offsetIndex).Take(pageSize).ToList();
            }

            logger.Info($"Populate Inventory Item Totals in {timer.Elapsed}");
        }

        static bool IsValid(InventoryManagement item)
        {
            return item.PartNumber.IsNotEmpty()
                    && item.Brand.IsNotEmpty()
                    && item.Condition.IsNotEmpty();
        }
    }

    public static class Extensions
    {
        public static PartResult GetFirstByPartNumber(this IEnumerable<PartResult> parts, string partNumber)
        {
            return parts.FirstOrDefault(p => new string[] { p.PartNumber, p.AlternateNumber }.Contains(partNumber));
        }
    }
}