using Bp.Sync.BackEnd.Infrastructure.Repositories;
using Bp.Sync.BackEnd.Infrastructure.Repositories.FcBusinesses;
using Bp.Sync.BackEnd.Infrastructure.Repositories.FcCustomers;
using Bp.Sync.Domain.Elastics.FcBusinesses;
using Bp.Sync.Domain.Elastics.FcBusinesses.Products;
using Bp.Sync.Dto.Results;
using CommonLibrary;
using Dapper;
using MediatR;
using Microsoft.Extensions.Configuration;
using Nest;
using System;
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.RetailStatistics
{
    public class UpdateRetailStatisticHandler : IRequestHandler<UpdateRetailStatisticCommand, BaseResult>
    {
        private readonly IProductRepository _productRepository;
        private readonly IUnitRepository _unitRepository;
        private readonly IFcCustomerRepository _fcCustomerRepository;
        private readonly IRetailStatisticRepository _retailStatisticRepository;
        private readonly IFcReceiptBillRepository _fcReceiptBillRepository;
        private readonly IDbConnection _dbConnection;
        private readonly IFcProductStorageRepository _fcProductStorageRepository;

        public UpdateRetailStatisticHandler(IConfiguration configuration, IFcCustomerRepository fcCustomerRepository,
            IRetailStatisticRepository retailStatisticRepository,
            IFcProductStorageRepository fcProductStorageRepository,
            IProductRepository productRepository, IUnitRepository unitRepository, IFcReceiptBillRepository fcReceiptBillRepository)
        {
            _productRepository = productRepository;
            _unitRepository = unitRepository;
            _fcCustomerRepository = fcCustomerRepository;
            _retailStatisticRepository = retailStatisticRepository;
            _fcReceiptBillRepository = fcReceiptBillRepository;
            var connectionString = configuration.GetConnectionString("FcBusinessConnection");
            _dbConnection = new SqlConnection(connectionString);
            _fcProductStorageRepository = fcProductStorageRepository;
        }
        public async Task<BaseResult> Handle(UpdateRetailStatisticCommand request, CancellationToken cancellationToken)
        {
            var rs = new BaseResult();

            var listUpdated = new List<RetailStatisticElastic>();

            //lay ra RetailStatistic k co invoice
            var query = new BoolQueryDescriptor<RetailStatisticElastic>();
            var notOriginalUnitNameQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notOriginalUnitNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.BaseUnitName))));
            var exitBaseUnitQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            exitBaseUnitQ.Range(e => e.Field(f => f.BaseUnitId).GreaterThan(0));

            var notRetailUnitNameQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notRetailUnitNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.RetailUnitName))));
            var exitRetailUnitQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            exitRetailUnitQ.Range(e => e.Field(f => f.RetailUnitId).GreaterThan(0));

            var notCustomerNameQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notCustomerNameQ.Bool(b => b.MustNot(m => m.Exists(e => e.Field(f => f.CustomerName))));

            var isDeletedQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            isDeletedQ.Terms(t => t.Field(f => f.IsDeleted).Terms(false));
            //var completedStatusQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            //completedStatusQ.Terms(t => t.Field(f => f.StatisticStatus).Terms(2));

            var notStatusQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notStatusQ.Bool(b => b.MustNot(m => m.Exists(t => t.Field(f => f.StatisticStatus))));
            var notbaseRevenuerateQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notbaseRevenuerateQ.Bool(b => b.MustNot(m => m.Exists(t => t.Field(f => f.BaseRevenueRate))));
            var notBillCodeQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notBillCodeQ.Bool(b => b.MustNot(m => m.Exists(t => t.Field(f => f.BillCode))));
            var notBillTypeQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notBillTypeQ.Bool(b => b.MustNot(m => m.Exists(t => t.Field(f => f.BillType))));
            var notRetailBaseUnitCostPriceQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            notRetailBaseUnitCostPriceQ.Bool(b => b.MustNot(m => m.Exists(t => t.Field(f => f.RetailBaseUnitCostPrice))));
            var limitedCreatedTimeQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            limitedCreatedTimeQ.DateRange(dr => dr.Field(f => f.CreatedTime).GreaterThanOrEquals(DateMath.Anchored(DateTimeHelper.MinLimitedTimeWithGMTHour())));

            //những đơn hnay chưa chuyển trạng thái
            var todayPendingStatusQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            todayPendingStatusQ.Terms(t => t.Field(f => f.StatisticStatus).Terms(1));
            var todayCreatedTimeQ = new QueryContainerDescriptor<RetailStatisticElastic>();
            todayCreatedTimeQ.DateRange(dr => dr.Field(f => f.CreatedTime).GreaterThanOrEquals(DateMath.Anchored(DateTime.Now.AddDays(-1))));


            var idQ = new QueryContainerDescriptor<RetailStatisticElastic>();

            var timeNow = DateTimeOffset.Now;
            var minute = timeNow.Minute;
            if ((minute % 15) == 0)
            {
                if (request.Ids.Any()) idQ.Terms(t => t.Field(f => f.Id).Terms(request.Ids));
                if (request.Ids.Any())
                    query.Filter(fl => idQ && isDeletedQ);
                else query.Filter(fl => ((todayPendingStatusQ && todayCreatedTimeQ) || (notOriginalUnitNameQ && exitBaseUnitQ) || (notRetailUnitNameQ && exitRetailUnitQ) || notCustomerNameQ || notStatusQ || notBillCodeQ || notBillTypeQ || notRetailBaseUnitCostPriceQ || notbaseRevenuerateQ) && isDeletedQ && limitedCreatedTimeQ);

            }
            else
            {
                if (request.Ids.Any()) idQ.Terms(t => t.Field(f => f.Id).Terms(request.Ids));
                if (request.Ids.Any())
                    query.Filter(fl => idQ && isDeletedQ);
                else query.Filter(fl => ((notOriginalUnitNameQ && exitBaseUnitQ) || (notRetailUnitNameQ && exitRetailUnitQ) || notCustomerNameQ || notStatusQ || notBillCodeQ || notBillTypeQ || notRetailBaseUnitCostPriceQ || notbaseRevenuerateQ) && isDeletedQ && limitedCreatedTimeQ);

            }

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

            if (true)
            {

                var p = new Dto.DapperTypes.IdsParam(items.Items.Select(x => x.Id).ToList());
                var RetailStatistics = await _dbConnection.QueryAsync<Domain.Entities.FcBusinesses.RetailStatistic>("Sync_RetailStatistic_Get_By_List_Id", p, commandType: CommandType.StoredProcedure);
                var retails = RetailStatistics.ToRetailStatistics();

                var productIds = items.Items.Select(x => x.ProductId).Distinct().ToList();
                var billIds = items.Items.Select(x => x.BillId).Distinct().ToList();
                var products = await _productRepository.GetByIds(productIds);
                var bills = await _fcReceiptBillRepository.GetByIds(billIds);

                var units = await _unitRepository.GetAll();

                var customerids = items.Items.Where(x => x.CustomerId.HasValue).Select(x => x.CustomerId.Value).Distinct().ToList();
                var customers = await _fcCustomerRepository.GetByIds(customerids.Select(x => (long)x).ToList());


                foreach (var item in retails)
                {
                    if (item.RetailBaseUnitCostPrice == 0) item.RetailBaseUnitCostPrice = item.RetailBaseUnitCost;
                    var bill = bills.Items.FirstOrDefault(x => x.Id == item.BillId);
                    if (bill != null)
                    {
                        if (!item.StatisticStatus.HasValue)
                        {

                            if (bill.CompletedDate.HasValue)
                                item.StatisticStatus = Domain.Entities.FcBusinesses.StatisticStatus.Finished;
                            else
                                item.StatisticStatus = Domain.Entities.FcBusinesses.StatisticStatus.Processing;
                        }
                        item.BillPaymentMode = bill.PaymentMode;
                        item.BillCode = bill.Code;
                        item.BillType = bill.BillType;
                        if (item.BillType.HasValue && item.BillType == Domain.Entities.FcBusinesses.Bills.BillType.CustomerReturnBill) item.RetailTotalPrice = item.RetailTotalPrice * (-1);
                    }

                    var unit = units.Items.FirstOrDefault(x => x.Id == item.RetailUnitId);
                    item.RetailUnitName = unit?.Name;

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

                    var prd = products.Items.FirstOrDefault(x => x.Id == item.ProductId);
                    item.ProductName = prd?.Name;
                    item.ProductCode = prd?.Code;
                    if (item.CustomerId.HasValue && item.CustomerId.Value > 0)
                    {
                        var cus = customers.Items.FirstOrDefault(x => x.Id == item.CustomerId.Value);
                        item.CustomerName = cus?.FullName;
                        item.CustomerUserName = cus?.UserName;
                        item.CustomerPhoneNumber = cus?.PhoneNumber;
                    }
                    else
                    {
                        item.CustomerName = "Khách lẻ";
                        item.CustomerUserName = "Khách lẻ";
                        item.CustomerUserName = "";
                    }

                    #region tính giá vốn
                    decimal basecost = 0;
                    decimal quantity = 0;

                    var queryPS = new Nest.BoolQueryDescriptor<FcProductStorageElastic>();
                    var aggPSs = new Nest.AggregationContainerDescriptor<FcProductStorageElastic>();

                    var notDelete1PSQ = new Nest.QueryContainerDescriptor<FcProductStorageElastic>();
                    notDelete1PSQ.Terms(t => t.Field(f => f.IsDeleted).Terms(false));
                    var productPSQ = new Nest.QueryContainerDescriptor<FcProductStorageElastic>();
                    productPSQ.Terms(t => t.Field(f => f.ProductId).Terms(item.ProductId));
                    var branchPSQ = new Nest.QueryContainerDescriptor<FcProductStorageElastic>();
                    branchPSQ.Terms(t => t.Field(f => f.BranchId).Terms(item.BranchId));
                    var quantityPSQ = new Nest.QueryContainerDescriptor<FcProductStorageElastic>();
                    quantityPSQ.Range(t => t.Field(f => f.CurrentQuantity).GreaterThan(0));

                    queryPS.Filter(fl => productPSQ && notDelete1PSQ && quantityPSQ && branchPSQ);
                    aggPSs.Sum("cost", s2 => s2.Field(f2 => f2.TotalCost));
                    var rs2 = await _fcProductStorageRepository.SearchAgg(queryPS, aggPSs);
                    var aggx = rs2.Aggregations.Sum("cost");
                    if (aggx != null && aggx.Value.HasValue)
                    {
                        basecost += (decimal)aggx.Value.Value;
                    }
                    aggPSs.Sum("quantity", s2 => s2.Field(f2 => f2.CurrentQuantity));
                    rs2 = await _fcProductStorageRepository.SearchAgg(queryPS, aggPSs);
                    aggx = rs2.Aggregations.Sum("quantity");
                    if (aggx != null && aggx.Value.HasValue)
                    {
                        quantity += (decimal)aggx.Value.Value;
                    }

                    if (basecost > 0 && quantity > 0) item.RetailBaseUnitCostPrice = basecost / quantity;
                    else item.RetailBaseUnitCostPrice = item.RetailBaseUnitCost;
                    #endregion


                    listUpdated.Add(item);
                }

            }

            if (listUpdated.Any())
            {
                var rs2 = await _retailStatisticRepository.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;
        }
    }
}
