using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;

using FMSAdmin.Data;
using FMSAdmin.Helpers;
using FMSAdmin.Entities;
using Microsoft.Extensions.Logging;
using FMSAdmin.Models;
using System.Linq.Expressions;
using LinqKit;
using System.Transactions;

namespace FMSAdmin.Services {
    public class MaterialStockService {
        private readonly ILogger<MaterialStockService> _logger;
        private readonly FMSContext _context;
        private readonly StorageHelper _storage;

        public MaterialStockService(
            ILogger<MaterialStockService> logger,
            FMSContext context,
            StorageHelper storage) {
            _logger = logger;
            _context = context;
            _storage = storage;
        }

        public void Create(FmsMaterial data) {
            using (var trans = new TransactionScope()) {
                if (data.CmFile != null && data.CmFile.IsUpload) {
                    data.CmFile.SiteId = data.SiteId;
                    var category = _context.CmFileCategory.First(x => x.Name == "material");
                    data.CmFile.FileCategory = category;
                    data.CmFile.CreatedDate = DateTime.Now;
                } else {
                    data.CmFile = null;
                }

                _context.FmsMaterial.Add(data);
                _context.SaveChanges();

                if (data.CmFile != null && data.CmFile.IsUpload) {
                    // 카피
                    _storage.CopyEntity(data.CmFile.Path, data.CmFile);
                }

                trans.Complete();
            }
        }

        public void Edit(int id, int siteId, FmsMaterial data) {
            using (var trans = new TransactionScope()) {
                if (data.CmFile != null && data.CmFile.IsUpload) {
                    data.CmFile.SiteId = data.SiteId;
                    var category = _context.CmFileCategory.First(x => x.Name == "material");
                    data.CmFile.FileCategory = category;
                    data.CmFile.CreatedDate = DateTime.Now;
                }

                var persist = _context.FmsMaterial
                                .Where(x => x.MaterialId == id && x.SiteId == siteId).FirstOrDefault();
                if (persist == null) {
                    throw new ServiceException("정보를 찾을 수 없습니다.");
                }

                persist.SiteId = data.SiteId;
                persist.BusinessFieldId = data.BusinessFieldId;
                persist.Name = data.Name;
                persist.MaterialTypeId = data.MaterialTypeId;
                persist.Standard = data.Standard;
                persist.FirstClassId = data.FirstClassId;
                persist.SecondClassId = data.SecondClassId;
                persist.ThirdClassId = data.ThirdClassId;
                persist.MaterialCode = data.MaterialCode;
                persist.Unit = data.Unit;
                persist.DurableYears = data.DurableYears;
                persist.Rfid = data.Rfid;
                persist.Manufacturer = data.Manufacturer;
                persist.FinalPrice = data.FinalPrice;
                persist.ReasonableStockCount = data.ReasonableStockCount;
                persist.IsDiscontinued = data.IsDiscontinued;
                persist.IsUse = data.IsUse;
                persist.Note = data.Note;
                _context.FmsMaterial.Update(persist);
                _context.SaveChanges();

                if (data.CmFile != null && data.CmFile.IsUpload) {
                    // 카피
                    _storage.CopyEntity(data.CmFile.Path, persist.CmFile);
                    if (persist.CmFile != null) {
                        // 파일 삭제시 진짜 DB삭제?
                    }
                }

                trans.Complete();
            }
        }

        public IQueryable<FmsMaterial> GetAll() {
            var query = _context.FmsMaterial;
            return query;
        }

        public FmsMaterial Get(int id, int siteId) {
            var data = _context.FmsMaterial.Single(x => x.MaterialId == id && x.SiteId == siteId);
            return data;
        }

        public IQueryable<FmsMaterial> Gets(int id, int siteId) {
            var data = _context.FmsMaterial.Where(x => x.MaterialId == id && x.SiteId == siteId);
            return data;
        }

        public void Delete(int id, int siteId) {
            var data = _context.FmsMaterial.First(x => x.MaterialId == id && x.SiteId == siteId);
            _context.FmsMaterial.Remove(data);
            _context.SaveChanges();
        }

        public IQueryable<MaterialStock> Stock() {
            var query = from x in _context.FmsMaterial.AsExpandable()
                        select new MaterialStock {
                            Material = x,
                            StockCount = FmsMaterial.GetStockCount.Invoke(x),
                            StockAmount = FmsMaterial.GetStockAmount.Invoke(x),
                            Warehouse = x.FmsMaterialStored.OrderByDescending(s => s.StoredDate).FirstOrDefault().FmsMaterialWarehouse
                        };
            return query;
        }

        public IQueryable<MaterialStock> WarehouseStock() {
            var query = (
                from x in _context.FmsMaterialStored
                where x.IsApproval == true
                select new {
                    Date = x.StoredDate.Date,
                    x.FmsMaterial,
                    x.StockCount,
                    x.StoredCount,
                    x.UnitCost,
                    x.FmsMaterialWarehouse,
                    MaterialCount = 0,
                    ReleaseCount = 0,
                    ReleaseLossCount = 0,
                    ReleaseReturnCount = 0,
                    ReleaseAmount = 0,
                    ReleaseLossAmount = 0,
                    ReleaseReturnAmount = 0,
                })
                .Concat(
                from x in _context.FmsMaterialReleaseDetail
                where x.FmsMaterialRelease.IsConfirmed == true
                select new {
                    Date = x.FmsMaterialRelease.ReleaseDate.Date,
                    x.FmsMaterialRelease.FmsMaterial,
                    StockCount = 0,
                    StoredCount = 0,
                    UnitCost = 0,
                    x.FmsMaterialStored.FmsMaterialWarehouse,
                    MaterialCount = x.FmsMaterialRelease.MaterialCount.Value,
                    ReleaseCount = x.FmsMaterialRelease.AdjustmentTypeId == 0 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
                    ReleaseLossCount = x.FmsMaterialRelease.AdjustmentTypeId == 1 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
                    ReleaseReturnCount = x.FmsMaterialRelease.AdjustmentTypeId == 2 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
                    ReleaseAmount = x.FmsMaterialRelease.AdjustmentTypeId == 0 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
                    ReleaseLossAmount = x.FmsMaterialRelease.AdjustmentTypeId == 1 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
                    ReleaseReturnAmount = x.FmsMaterialRelease.AdjustmentTypeId == 2 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
                });

            var subquery = from x in query
                           group x by new { x.FmsMaterial.MaterialId, x.FmsMaterialWarehouse.WarehouseId } into g
                           select new {
                               g.Key.MaterialId,
                               g.Key.WarehouseId,
                               StockCount = g.Sum(x => x.StoredCount),
                               StockAmount = g.Sum(x => (long)x.UnitCost * x.StoredCount),
                               ReleaseCount = g.Sum(x => x.ReleaseCount),
                               ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
                               ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
                               ReleaseAmount = g.Sum(x => x.ReleaseAmount),
                               ReleaseLossAmount = g.Sum(x => x.ReleaseLossAmount),
                               ReleaseReturnAmount = g.Sum(x => x.ReleaseReturnAmount),
                           };

            var subquery2 = from stock in subquery
                            join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
                            join wareHouse in _context.FmsMaterialWarehouse on stock.WarehouseId equals wareHouse.WarehouseId
                            select new MaterialStock {
                                Material = material,
                                Warehouse = wareHouse,
                                StockCount = stock.StockCount + stock.ReleaseReturnCount - stock.ReleaseCount - stock.ReleaseLossCount,
                                StockAmount = stock.StockAmount + stock.ReleaseReturnAmount - stock.ReleaseAmount - stock.ReleaseLossAmount
                            };
            return subquery2;
        }
        public IQueryable<MaterialStockHistory> DailyStock() {
            var query = (
                from x in _context.FmsMaterialStored
                where x.IsApproval == true
                select new {
                    Date = x.StoredDate.Date,
                    x.FmsMaterial,
                    x.StockCount,
                    x.StoredCount,
                    MaterialCount = 0,
                    ReleaseCount = 0,
                    ReleaseLossCount = 0,
                    ReleaseReturnCount = 0,
                })
                .Concat(
                from x in _context.FmsMaterialRelease
                where x.IsConfirmed == true
                select new {
                    Date = x.ReleaseDate.Date,
                    x.FmsMaterial,
                    StockCount = 0,
                    StoredCount = 0,
                    MaterialCount = x.MaterialCount.Value,
                    ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
                    ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
                    ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
                });

            var subquery = from x in query
                           group x by new { x.FmsMaterial.MaterialId, x.Date } into g
                           orderby g.Key.Date descending
                           select new {
                               g.Key.Date,
                               g.Key.MaterialId,
                               LastStockCount = _context.FmsMaterialStored
                                                .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                .OrderByDescending(x => x.StoredDate)
                                                .Sum(x => x.StoredCount),
                               LastStockDate = _context.FmsMaterialStored
                                                .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                .OrderByDescending(x => x.StoredDate)
                                                .Select(x => x.StoredDate)
                                                .FirstOrDefault(),
                               //    StockAmount = _context.FmsMaterialStored
                               //                        .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                               //                        .OrderByDescending(x => x.StoredDate)
                               //                        .Select(x => (long)x.UnitCost * x.StockCount)
                               //                        .FirstOrDefault(),
                               StockAmount = _context.FmsMaterialStored
                                                   .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                   .Sum(x => (long)x.UnitCost * x.StoredCount),
                               Name = g.Min(x => x.FmsMaterial.Name),
                               StoredCount = g.Sum(x => x.StoredCount),
                               ReleaseCount = g.Sum(x => x.ReleaseCount),
                               ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
                               ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
                           };

            var subquery2 = from stock in subquery
                            join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
                            select new MaterialStockHistory {
                                Date = stock.Date,
                                Material = material,
                                Warehouse = material.FmsMaterialStored.OrderByDescending(x => x.StoredDate).FirstOrDefault().FmsMaterialWarehouse,
                                StoredCount = stock.StoredCount,
                                ReleaseCount = stock.ReleaseCount,
                                ReleaseLossCount = stock.ReleaseLossCount,
                                ReleaseReturnCount = stock.ReleaseReturnCount,
                                LastStockCount = stock.LastStockCount,
                                LastStockDate = stock.LastStockDate,
                                StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
                                                .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
                                                .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
                                StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
                                                .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
                                                .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0)
                            };

            return subquery2;
        }

        public IQueryable<MaterialStockHistory> MonthlyStock() {
            var query = (
                from x in _context.FmsMaterialStored
                where x.IsApproval == true
                select new {
                    Date = x.StoredDate.Date,
                    Year = x.StoredDate.Year,
                    Month = x.StoredDate.Month,
                    x.FmsMaterial,
                    x.StockCount,
                    x.StoredCount,
                    MaterialCount = 0,
                    ReleaseCount = 0,
                    ReleaseLossCount = 0,
                    ReleaseReturnCount = 0,
                })
                .Concat(
                from x in _context.FmsMaterialRelease
                where x.IsConfirmed == true
                select new {
                    Date = x.ReleaseDate.Date,
                    Year = x.ReleaseDate.Year,
                    Month = x.ReleaseDate.Month,
                    x.FmsMaterial,
                    StockCount = 0,
                    StoredCount = 0,
                    MaterialCount = x.MaterialCount.Value,
                    ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
                    ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
                    ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
                });

            var subquery = from x in query
                           group x by new { x.FmsMaterial.MaterialId, x.Year, x.Month } into g
                           orderby g.Key.Year descending, g.Key.Month descending
                           select new {
                               g.Key.Year,
                               g.Key.Month,
                               g.Key.MaterialId,
                               Date = g.Max(x => x.Date),
                               LastStockCount = _context.FmsMaterialStored
                                                .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                .OrderByDescending(x => x.StoredDate)
                                                .Sum(x => x.StoredCount),
                               LastStockDate = _context.FmsMaterialStored
                                                .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                .OrderByDescending(x => x.StoredDate)
                                                .Select(x => x.StoredDate)
                                                .FirstOrDefault(),
                               //    StockAmount = _context.FmsMaterialStored
                               //                     .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                               //                     .OrderByDescending(x => x.StoredDate)
                               //                     .Select(x => (long)x.UnitCost * x.StockCount)
                               //                     .FirstOrDefault(),
                               StockAmount = _context.FmsMaterialStored
                                                .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
                                                .Sum(x => (long)x.UnitCost * x.StoredCount),
                               Name = g.Min(x => x.FmsMaterial.Name),
                               StoredCount = g.Sum(x => x.StoredCount),
                               ReleaseCount = g.Sum(x => x.ReleaseCount),
                               ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
                               ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
                           };

            var subquery2 = from stock in subquery
                            join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
                            select new MaterialStockHistory {
                                Date = stock.Date,
                                Year = stock.Year,
                                Month = stock.Month,
                                Material = material,
                                Warehouse = material.FmsMaterialStored.OrderByDescending(x => x.StoredDate).FirstOrDefault().FmsMaterialWarehouse,
                                StoredCount = stock.StoredCount,
                                ReleaseCount = stock.ReleaseCount,
                                ReleaseLossCount = stock.ReleaseLossCount,
                                ReleaseReturnCount = stock.ReleaseReturnCount,
                                LastStockCount = stock.LastStockCount,
                                LastStockDate = stock.LastStockDate,
                                StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
                                                .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
                                                .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
                                StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
                                                .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
                                                .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0),
                            };

            return subquery2;
        }

    }
}