using Bp.Sync.BackEnd.Infrastructure.Repositories;
using Bp.Sync.BackEnd.Infrastructure.Repositories.FcBusinesses;
using Bp.Sync.Domain.Elastics.FcBusinesses.Products;
using Bp.Sync.Domain.Elastics.Inventories;
using Bp.Sync.Dto.Results;
using Dapper;
using MediatR;
using Microsoft.Extensions.Configuration;
using Nest;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Vina.Search.Infrastructure.Mappings;

namespace Bp.Sync.BackEnd.Application.Commands.FcBusinesses.Products
{
    public class UpdateFcProductStorageHandler : IRequestHandler<UpdateFcProductStorageCommand, BaseResult>
    {
        private readonly IProductRepository _productRepository;
        private readonly IUnitRepository _unitRepository;
        private readonly IFcUnitExchangeRepository _fcunitExchangeRepository;
        private readonly IFcProductStorageRepository _FcProductStorageRepository;
        private readonly IBatchRepository _batchRepository;
        private readonly IProductInventoryRepository _productInventoryRepository;
        private readonly IDbConnection _dbConnection;

        public UpdateFcProductStorageHandler(IFcProductStorageRepository FcProductStorageRepository,
            IProductInventoryRepository productInventoryRepository,
            IBatchRepository batchRepository, IConfiguration configuration, IFcUnitExchangeRepository fcunitExchangeRepository,
            IProductRepository productRepository, IUnitRepository unitRepository)
        {
            _productRepository = productRepository;
            _unitRepository = unitRepository;
            _fcunitExchangeRepository = fcunitExchangeRepository;
            _FcProductStorageRepository = FcProductStorageRepository;
            _batchRepository = batchRepository;
            var connectionString = configuration.GetConnectionString("FcBusinessConnection");
            _dbConnection = new SqlConnection(connectionString);
            _productInventoryRepository = productInventoryRepository;
        }
        public async Task<BaseResult> Handle(UpdateFcProductStorageCommand request, CancellationToken cancellationToken)
        {
            var rs = new BaseResult();

            var listUpdated = new List<FcProductStorageElastic>();

            //lay ra FcProductStorage k co invoice
            var query = new BoolQueryDescriptor<FcProductStorageElastic>();
            var notProductNameQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notProductNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.ProductName))));
            var notCurentUnitNameQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notCurentUnitNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.CurrentUnitName))));
            var notInboundUnitNameQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notInboundUnitNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.LastInboundUnitName))));
            var notBaseUnitInboundCostQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notBaseUnitInboundCostQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.BaseUnitInboundCost))));
            var notBaseUnitInboundPriceQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notBaseUnitInboundPriceQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.BaseUnitInboundPrice))));
            var notDelete1Q = new QueryContainerDescriptor<FcProductStorageElastic>();
            notDelete1Q.Terms(t => t.Field(f => f.IsDeleted).Terms(false));
            var notSupplierQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            notSupplierQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.SupplierId))));


            var idQ = new QueryContainerDescriptor<FcProductStorageElastic>();
            if (request.Ids.Any()) idQ.Terms(t => t.Field(f => f.Id).Terms(request.Ids));
            if (request.Ids.Any())
                query.Filter(fl => idQ && notDelete1Q);
            else query.Filter(fl => (notProductNameQ || notCurentUnitNameQ || notInboundUnitNameQ || notBaseUnitInboundCostQ || notBaseUnitInboundPriceQ || notSupplierQ) && notDelete1Q);
            var items = await _FcProductStorageRepository.Search(query, 2000);

            if (items.Items == null || !items.Items.Any()) return new BaseResult { Message = "No data update", Result = Dto.Results.Result.Success };


            var ids = items.Items.Select(x => x.Id).ToList();


            var p = new Dto.DapperTypes.IdsParam(ids);
            var FcProductStorages = await _dbConnection.QueryAsync<Domain.Entities.FcBusinesses.Products.ProductStorage>("Sync_ProductStorage_Get_By_List_Id", p, commandType: CommandType.StoredProcedure);

            if (!FcProductStorages.Any()) return new BaseResult { Message = "No data update", Result = Dto.Results.Result.Success };

            var FcProductStorageRs = FcProductStorages.ToFcProductStorages();


            var productIds = FcProductStorageRs.Select(x => x.ProductId).Distinct().ToList();
            var products = await _productRepository.GetByIds(productIds);

            var units = await _unitRepository.GetAll();


            #region exchange
            //var queryExchange = new BoolQueryDescriptor<FcUnitExchangeElastic>();
            //var notDeletedExchangeQ = new QueryContainerDescriptor<FcUnitExchangeElastic>();
            //notDeletedExchangeQ.Terms(t => t.Field(f => f.IsDeleted).Terms(false));
            //var productExchangeQ = new QueryContainerDescriptor<FcUnitExchangeElastic>();
            //productExchangeQ.Terms(t => t.Field(f => f.ProductId).Terms(productIds));
            //var branchIds = FcProductStorageRs.Select(x => x.BranchId).Distinct().ToList();
            //var branchExchangeQ = new QueryContainerDescriptor<FcUnitExchangeElastic>();
            //branchExchangeQ.Terms(t => t.Field(f => f.BranchId).Terms(branchIds));
            //var typeExchangeQ = new QueryContainerDescriptor<FcUnitExchangeElastic>();
            //typeExchangeQ.Terms(t => t.Field(f => f.HeadQuarterParentUnitId).Terms(0));

            //queryExchange.Filter(fl => notDeletedExchangeQ && productExchangeQ && branchExchangeQ && typeExchangeQ);
            //var unitExchanges = await _fcunitExchangeRepository.SearchForExcel(queryExchange);

            #endregion

            //lấy ngày hết hạn

            var queryBatch = new BoolQueryDescriptor<BatchElastic>();
            var inventoryCodes = FcProductStorageRs.Where(x => !string.IsNullOrEmpty(x.InventoryCode) && !string.IsNullOrWhiteSpace(x.InventoryCode)).Select(x => x.InventoryCode).Distinct().ToList();
            var inventoryCodeBatchQ = new QueryContainerDescriptor<BatchElastic>();
            if (inventoryCodes.Any())
                inventoryCodeBatchQ.Terms(e => e.Field(f => f.InventoryCode.Suffix("keyword")).Terms(inventoryCodes));
            var productBatchQ = new QueryContainerDescriptor<BatchElastic>();
            productBatchQ.Terms(e => e.Field(f => f.ProductId).Terms(productIds));
            var notDeleteBatchQ = new QueryContainerDescriptor<BatchElastic>();
            notDeleteBatchQ.Terms(t => t.Field(f => f.IsDeleted).Terms(false));
            queryBatch.Filter(f => inventoryCodeBatchQ && productBatchQ && notDeleteBatchQ);
            var batches = await _batchRepository.Search(queryBatch, 10000);


            #region tồn kho ở tổng
            var notDeleteCenterBacklogQ = new QueryContainerDescriptor<Domain.Elastics.Inventories.Products.ProductInventoryElastic>();
            notDeleteCenterBacklogQ.Terms(t => t.Field(f => f.IsDeleted).Terms(false));

            var inventoryCodeCenterBacklogQ = new QueryContainerDescriptor<Domain.Elastics.Inventories.Products.ProductInventoryElastic>();
            if (inventoryCodes.Any())
                inventoryCodeCenterBacklogQ.Terms(e => e.Field(f => f.InventoryCode.Suffix("keyword")).Terms(inventoryCodes));
            var queryCenterBacklog = new BoolQueryDescriptor<Domain.Elastics.Inventories.Products.ProductInventoryElastic>();
            queryCenterBacklog.Filter(f => inventoryCodeCenterBacklogQ && notDeleteCenterBacklogQ);
            var centerbacklogs = await _productInventoryRepository.Search(queryCenterBacklog, 10000);
            #endregion

            foreach (var item in FcProductStorageRs)
            {
                var unit = units.Items.FirstOrDefault(x => x.Id == item.CurrentUnitId);
                item.CurrentUnitName = unit?.Name;

                var unitOriginal = units.Items.FirstOrDefault(x => x.Id == item.LastInboundUnitId);
                item.LastInboundUnitName = unitOriginal?.Name;

                var prd = products.Items.FirstOrDefault(x => x.Id == item.ProductId);
                item.ProductName = prd?.Name;
                item.ProductCode = prd?.Code;
                item.ProductPartnerCode = prd?.PartnerCode;

                var br = batches.Items.FirstOrDefault(x => x.InventoryCode == item.InventoryCode && x.ProductId == item.ProductId);
                item.ExpiryDate = br?.ExpiryDate;

                if (centerbacklogs.Items.Any())
                {
                    var centerBacklog = centerbacklogs.Items.FirstOrDefault(x => x.InventoryCode == item.InventoryCode && x.SupplierId > 0);
                    if (centerBacklog != null) item.SupplierId = centerBacklog.SupplierId;
                    else item.SupplierId = 0;
                }

                //if (unitExchanges.Items.Any())
                //{
                //    var baseunitExchange = unitExchanges.Items.FirstOrDefault(x => x.ProductId == item.ProductId && x.HeadQuarterParentUnitId == 0 && x.BranchId == item.BranchId);
                //    item.BaseUnitInboundCost = baseunitExchange?.Cost;
                //    item.BaseUnitInboundPrice = baseunitExchange?.Price;
                //}


                listUpdated.Add(item);
            }


            if (listUpdated.Any())
            {
                var rs2 = await _FcProductStorageRepository.AddManyAsync(listUpdated);
                if (rs2.Result == Dto.Results.Result.Success)
                {
                    rs.Message = $"{listUpdated.Count} - updated";
                    rs.Result = Dto.Results.Result.Success;
                }
                else
                {
                    rs.Message = $"can not insert db";
                    rs.Result = Dto.Results.Result.Success;
                }
            }
            return rs;
        }
    }
}
