using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Interfaces.LqXmzl;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Entitys.Dto.LqXmzl;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_hytk_hytk;
using Yitter.IdGenerator;
using NCC.Common.Helper;
using NCC.JsonSerialization;
using NCC.Common.Model.NPOI;
using NCC.Common.Configuration;
using NCC.DataEncryption;
using NCC.ClayObject;

namespace NCC.Extend.LqXmzl
{
    /// <summary>
    /// 项目资料服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤品项资料服务", Name = "LqXmzl", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqXmzlService : ILqXmzlService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqXmzlEntity> _lqXmzlRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 初始化一个<see cref="LqXmzlService"/>类型的新实例
        /// </summary>
        public LqXmzlService(ISqlSugarRepository<LqXmzlEntity> lqXmzlRepository, IUserManager userManager)
        {
            _lqXmzlRepository = lqXmzlRepository;
            _db = _lqXmzlRepository.Context;
            _userManager = userManager;
        }

        #region 获取品项资料

        /// <summary>
        /// 获取项目资料
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<LqXmzlEntity>().FirstAsync(p => p.Id == id);
            var output = entity.Adapt<LqXmzlInfoOutput>();
            return output;
        }
        #endregion

        #region 获取品项资料列表
        /// <summary>
        /// 获取品项资料列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqXmzlListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var data = await _db.Queryable<LqXmzlEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.xmbh), p => p.Xmbh.Contains(input.xmbh))
                .WhereIF(!string.IsNullOrEmpty(input.xmmc), p => p.Xmmc.Contains(input.xmmc))
                .WhereIF(!string.IsNullOrEmpty(input.bzjg), p => p.Bzjg.Equals(input.bzjg))
                .WhereIF(!string.IsNullOrEmpty(input.xmsc), p => p.Xmsc.Equals(input.xmsc))
                .WhereIF(!string.IsNullOrEmpty(input.jcfwtc), p => p.Jcfwtc.Equals(input.jcfwtc))
                .WhereIF(!string.IsNullOrEmpty(input.fl1), p => p.Fl1.Contains(input.fl1))
                .WhereIF(!string.IsNullOrEmpty(input.fl2), p => p.Fl2.Contains(input.fl2))
                .WhereIF(!string.IsNullOrEmpty(input.fl3), p => p.Fl3.Contains(input.fl3))
                .WhereIF(!string.IsNullOrEmpty(input.fl4), p => p.Fl4.Contains(input.fl4))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Contains(input.fl))
                .WhereIF(!string.IsNullOrEmpty(input.qt1), p => p.Qt1.Contains(input.qt1))
                .WhereIF(!string.IsNullOrEmpty(input.qt2), p => p.Qt2.Contains(input.qt2))
                .WhereIF(!string.IsNullOrEmpty(input.sgf), p => p.Sgf.Equals(input.sgf))
                .WhereIF(!string.IsNullOrEmpty(input.beautyType), p => p.BeautyType.Contains(input.beautyType))
                .WhereIF(input.isEffective != 0, p => p.IsEffective.Equals(input.isEffective))
                .WhereIF(!string.IsNullOrEmpty(input.sourceType), p => p.SourceType.Contains(input.sourceType))
                .Select(it => new LqXmzlListOutput
                {
                    id = it.Id,
                    xmbh = it.Xmbh,
                    xmmc = it.Xmmc,
                    bzjg = it.Bzjg,
                    xmsc = it.Xmsc,
                    jcfwtc = it.Jcfwtc,
                    fl1 = it.Fl1,
                    fl2 = it.Fl2,
                    fl3 = it.Fl3,
                    fl4 = it.Fl4,
                    fl = it.Fl,
                    qt1 = it.Qt1,
                    qt2 = it.Qt2,
                    sgf = it.Sgf,
                    beautyType = it.BeautyType,
                    sourceType = it.SourceType,
                    isEffective = it.IsEffective,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqXmzlListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 新建品项资料
        /// <summary>
        /// 新建品项资料
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] LqXmzlCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<LqXmzlEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1000);
        }
        #endregion

        #region 获取品项资料无分页列表
        /// <summary>
        /// 获取品项资料无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] LqXmzlListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var data = await _db.Queryable<LqXmzlEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.xmbh), p => p.Xmbh.Contains(input.xmbh))
                .WhereIF(!string.IsNullOrEmpty(input.xmmc), p => p.Xmmc.Contains(input.xmmc))
                .WhereIF(!string.IsNullOrEmpty(input.bzjg), p => p.Bzjg.Equals(input.bzjg))
                .WhereIF(!string.IsNullOrEmpty(input.xmsc), p => p.Xmsc.Equals(input.xmsc))
                .WhereIF(!string.IsNullOrEmpty(input.jcfwtc), p => p.Jcfwtc.Equals(input.jcfwtc))
                .WhereIF(!string.IsNullOrEmpty(input.fl1), p => p.Fl1.Contains(input.fl1))
                .WhereIF(!string.IsNullOrEmpty(input.fl2), p => p.Fl2.Contains(input.fl2))
                .WhereIF(!string.IsNullOrEmpty(input.fl3), p => p.Fl3.Contains(input.fl3))
                .WhereIF(!string.IsNullOrEmpty(input.fl4), p => p.Fl4.Contains(input.fl4))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Contains(input.fl))
                .WhereIF(!string.IsNullOrEmpty(input.qt1), p => p.Qt1.Contains(input.qt1))
                .WhereIF(!string.IsNullOrEmpty(input.qt2), p => p.Qt2.Contains(input.qt2))
                .WhereIF(!string.IsNullOrEmpty(input.sgf), p => p.Sgf.Equals(input.sgf))
                .Select(it => new LqXmzlListOutput
                {
                    id = it.Id,
                    xmbh = it.Xmbh,
                    xmmc = it.Xmmc,
                    bzjg = it.Bzjg,
                    xmsc = it.Xmsc,
                    jcfwtc = it.Jcfwtc,
                    fl1 = it.Fl1,
                    fl2 = it.Fl2,
                    fl3 = it.Fl3,
                    fl4 = it.Fl4,
                    fl = it.Fl,
                    qt1 = it.Qt1,
                    qt2 = it.Qt2,
                    sgf = it.Sgf,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToListAsync();
            return data;
        }
        #endregion

        #region 导出品项资料
        /// <summary>
        /// 导出品项资料
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] LqXmzlListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<LqXmzlListOutput>();
            if (input.dataType == 0)
            {
                var data = Clay.Object(await this.GetList(input));
                exportData = data.Solidify<PageResult<LqXmzlListOutput>>().list;
            }
            else
            {
                exportData = await this.GetNoPagingList(input);
            }
            List<ParamsModel> paramList = "[{\"value\":\"主键\",\"field\":\"id\"},{\"value\":\"项目编号\",\"field\":\"xmbh\"},{\"value\":\"项目名称\",\"field\":\"xmmc\"},{\"value\":\"标准价格\",\"field\":\"bzjg\"},{\"value\":\"时长（分）\",\"field\":\"xmsc\"},{\"value\":\"基础服务提成\",\"field\":\"jcfwtc\"},{\"value\":\"分类1汇总表\",\"field\":\"fl1\"},{\"value\":\"分类2汇总表\",\"field\":\"fl2\"},{\"value\":\"分类3工资用\",\"field\":\"fl3\"},{\"value\":\"分类4品项用\",\"field\":\"fl4\"},{\"value\":\"统计类别\",\"field\":\"tjlb\"},{\"value\":\"折扣类别\",\"field\":\"zklb\"},{\"value\":\"分类\",\"field\":\"fl\"},{\"value\":\"其它1\",\"field\":\"qt1\"},{\"value\":\"其它2\",\"field\":\"qt2\"},{\"value\":\"溯源金额\",\"field\":\"syje\"},{\"value\":\"Cell金额\",\"field\":\"cellje\"},{\"value\":\"手工费\",\"field\":\"sgf\"},]".ToList<ParamsModel>();
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = "项目资料.xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();
            List<string> selectKeyList = input.selectKey.Split(',').ToList();
            foreach (var item in selectKeyList)
            {
                var isExist = paramList.Find(p => p.field == item);
                if (isExist != null)
                {
                    excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value });
                }
            }
            var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
            ExcelExportHelper<LqXmzlListOutput>.Export(exportData, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            var output = new
            {
                name = excelconfig.FileName,
                url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC")
            };
            return output;
        }
        #endregion

        #region 批量删除品项资料
        /// <summary>
        /// 批量删除品项资料
        /// </summary>
        /// <param name="ids">主键数组</param>
        /// <returns></returns>
        [HttpPost("batchRemove")]
        public async Task BatchRemove([FromBody] List<string> ids)
        {
            throw NCCException.Oh("不允许删除项目资料");
            var entitys = await _db.Queryable<LqXmzlEntity>().In(it => it.Id, ids).ToListAsync();
            if (entitys.Count > 0)
            {
                try
                {
                    //开启事务
                    _db.BeginTran();
                    //批量删除项目资料
                    await _db.Deleteable<LqXmzlEntity>().In(d => d.Id, ids).ExecuteCommandAsync();
                    //关闭事务
                    _db.CommitTran();
                }
                catch (Exception)
                {
                    //回滚事务
                    _db.RollbackTran();
                    throw NCCException.Oh(ErrorCode.COM1002);
                }
            }
        }
        #endregion

        #region 更新品项资料
        /// <summary>
        /// 更新品项资料
        /// </summary>
        /// <param name="id">主键</param>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] LqXmzlUpInput input)
        {
            var entity = input.Adapt<LqXmzlEntity>();
            var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1001);
        }
        #endregion

        #region 删除品项资料
        /// <summary>
        /// 删除品项资料
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            throw NCCException.Oh("不允许删除项目资料");
        }
        #endregion

        #region 获取字段去重数据
        /// <summary>
        /// 获取指定字段的去重数据
        /// </summary>
        /// <param name="fieldName">字段名称，支持：fl1、fl2、fl3、fl4、fl、qt1、beautyType、sourceType</param>
        /// <returns>去重后的字段数据</returns>
        [HttpGet("GetDistinctFieldData")]
        public async Task<dynamic> GetDistinctFieldData([FromQuery] string fieldName)
        {
            try
            {
                // 验证字段名称
                var validFields = new[] { "fl1", "fl2", "fl3", "fl4", "fl", "qt1", "qt2", "beautyType", "sourceType" };
                if (!validFields.Contains(fieldName))
                {
                    throw NCCException.Oh($"无效的字段名称: {fieldName}。支持的字段: {string.Join(", ", validFields)}");
                }

                List<string> distinctValues = new List<string>();

                switch (fieldName)
                {
                    case "fl1":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl1))
                            .Select(p => p.Fl1)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl2":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl2))
                            .Select(p => p.Fl2)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl3":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl3))
                            .Select(p => p.Fl3)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl4":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl4))
                            .Select(p => p.Fl4)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "fl":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Fl))
                            .Select(p => p.Fl)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "qt1":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Qt1))
                            .Select(p => p.Qt1)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "qt2":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.Qt2))
                            .Select(p => p.Qt2)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "beautyType":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.BeautyType))
                            .Select(p => p.BeautyType)
                            .Distinct()
                            .ToListAsync();
                        break;
                    case "sourceType":
                        distinctValues = await _db.Queryable<LqXmzlEntity>()
                            .Where(p => !string.IsNullOrEmpty(p.SourceType))
                            .Select(p => p.SourceType)
                            .Distinct()
                            .ToListAsync();
                        break;
                }

                return new
                {
                    fieldName = fieldName,
                    values = distinctValues.OrderBy(x => x).ToList()
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取字段去重数据失败：{ex.Message}");
            }
        }
        #endregion

        #region 品项维度统计
        /// <summary>
        /// 品项维度统计
        /// </summary>
        /// <remarks>
        /// 按品项维度统计开卡、消耗、退卡等数据
        /// 包括业绩、人数、占比、复购率等指标
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "startTime": "2024-01-01",
        ///   "endTime": "2024-12-31",
        ///   "storeId": "store001",
        ///   "category": "美容",
        ///   "itemId": "item001"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - startTime: 开始时间（可选）
        /// - endTime: 结束时间（可选）
        /// - storeId: 门店ID（可选）
        /// - category: 品项分类（可选）
        /// - itemId: 品项ID（可选，单个品项统计）
        /// </remarks>
        /// <param name="input">统计输入参数</param>
        /// <returns>品项维度统计数据</returns>
        /// <response code="200">统计成功</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("GetItemStatistics")]
        public async Task<dynamic> GetItemStatistics([FromBody] LqXmzlStatisticsInput input)
        {
            try
            {
                // 第一步：获取品项基础信息
                var itemsQuery = _db.Queryable<LqXmzlEntity>()
                    .Where(x => x.IsEffective == 1);

                if (!string.IsNullOrEmpty(input.ItemId))
                {
                    itemsQuery = itemsQuery.Where(x => x.Id == input.ItemId);
                }

                if (!string.IsNullOrEmpty(input.Category))
                {
                    itemsQuery = itemsQuery.Where(x => x.Fl1 == input.Category || x.Fl2 == input.Category || x.Fl == input.Category);
                }

                var items = await itemsQuery.ToListAsync();

                if (!items.Any())
                {
                    return new
                    {
                        success = true,
                        data = new List<LqXmzlStatisticsOutput>(),
                        message = "未找到符合条件的品项"
                    };
                }

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

                // 第二步：开卡数据统计
                var billingStats = await GetBillingStatistics(itemIds, input);

                // 第三步：消耗数据统计
                var consumeStats = await GetConsumeStatistics(itemIds, input);

                // 第四步：退卡数据统计
                var refundStats = await GetRefundStatistics(itemIds, input);

                // 第五步：计算总数据用于占比计算
                var totalBillingAmount = billingStats.Sum(x => x.BillingAmount);
                var totalConsumeAmount = consumeStats.Sum(x => x.ConsumeAmount);
                var totalBuyers = billingStats.Sum(x => x.TotalBuyers);

                // 第六步：合并数据并计算占比
                var result = new List<LqXmzlStatisticsOutput>();

                foreach (var item in items)
                {
                    var billingData = billingStats.FirstOrDefault(x => x.ItemId == item.Id);
                    var consumeData = consumeStats.FirstOrDefault(x => x.ItemId == item.Id);
                    var refundData = refundStats.FirstOrDefault(x => x.ItemId == item.Id);

                    var output = new LqXmzlStatisticsOutput
                    {
                        ItemId = item.Id,
                        ItemName = item.Xmmc,
                        ItemNumber = item.Xmbh,
                        BillingAmount = billingData?.BillingAmount ?? 0,
                        BillingAmountRatio = totalBillingAmount > 0 ? (billingData?.BillingAmount ?? 0) / totalBillingAmount : 0,
                        TotalBuyers = billingData?.TotalBuyers ?? 0,
                        ItemRatio = totalBuyers > 0 ? (billingData?.TotalBuyers ?? 0) / (decimal)totalBuyers : 0,
                        RepeatBuyers = billingData?.RepeatBuyers ?? 0,
                        RepeatBuyRate = (billingData?.TotalBuyers ?? 0) > 0 ? (billingData?.RepeatBuyers ?? 0) / (decimal)(billingData?.TotalBuyers ?? 1) : 0,
                        ConsumeAmount = consumeData?.ConsumeAmount ?? 0,
                        ConsumeAmountRatio = totalConsumeAmount > 0 ? (consumeData?.ConsumeAmount ?? 0) / totalConsumeAmount : 0,
                        ConsumePurchaseCount = consumeData?.ConsumePurchaseCount ?? 0,
                        ConsumeGiftCount = consumeData?.ConsumeGiftCount ?? 0,
                        ConsumeExperienceCount = consumeData?.ConsumeExperienceCount ?? 0,
                        RefundAmount = refundData?.RefundAmount ?? 0,
                        RefundCount = refundData?.RefundCount ?? 0
                    };

                    result.Add(output);
                }

                return new
                {
                    success = true,
                    data = result,
                    message = "品项维度统计成功"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"品项维度统计失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取开卡统计数据
        /// </summary>
        private async Task<List<ItemStatisticsData>> GetBillingStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            var query = _db.Queryable<LqKdPxmxEntity>()
                .Where(x => itemIds.Contains(x.Px) && x.IsEffective == 1);

            // 时间过滤
            if (input.StartTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme <= input.EndTime.Value);
            }

            // 门店过滤（通过开单记录关联）
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                query = query.Where(x => SqlFunc.Subqueryable<LqKdKdjlbEntity>()
                    .Where(kd => kd.Id == x.Glkdbh && kd.Djmd == input.StoreId && kd.IsEffective == 1)
                    .Any());
            }

            var result = await query
                .GroupBy(x => x.Px)
                .Select(x => new ItemStatisticsData
                {
                    ItemId = x.Px,
                    BillingAmount = SqlFunc.AggregateSum(x.Pxjg * x.ProjectNumber),
                    TotalBuyers = SqlFunc.AggregateCount(x.MemberId),
                    RepeatBuyers = 0 // 复购人数需要单独计算
                })
                .ToListAsync();

            // 单独计算复购人数
            foreach (var item in result)
            {
                var memberCountQuery = _db.Queryable<LqKdPxmxEntity>()
                    .Where(x => x.Px == item.ItemId && x.IsEffective == 1);

                if (input.StartTime.HasValue)
                {
                    memberCountQuery = memberCountQuery.Where(x => x.CreateTIme >= input.StartTime.Value);
                }
                if (input.EndTime.HasValue)
                {
                    memberCountQuery = memberCountQuery.Where(x => x.CreateTIme <= input.EndTime.Value);
                }

                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                    memberCountQuery = memberCountQuery.Where(x => SqlFunc.Subqueryable<LqKdKdjlbEntity>()
                        .Where(kd => kd.Id == x.Glkdbh && kd.Djmd == input.StoreId && kd.IsEffective == 1)
                        .Any());
                }

                var memberStats = await memberCountQuery
                    .GroupBy(x => x.MemberId)
                    .Having(x => SqlFunc.AggregateCount(x.MemberId) > 1)
                    .Select(x => SqlFunc.AggregateCount(x.MemberId))
                    .ToListAsync();

                item.RepeatBuyers = memberStats.Count;
            }

            return result;
        }

        /// <summary>
        /// 获取消耗统计数据
        /// </summary>
        private async Task<List<ItemConsumeStatisticsData>> GetConsumeStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            var query = _db.Queryable<LqXhPxmxEntity>()
                .Where(x => itemIds.Contains(x.Px));

            // 时间过滤
            if (input.StartTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where(x => x.CreateTIme <= input.EndTime.Value);
            }

            // 门店过滤（通过耗卡记录关联）
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                query = query.Where(x => SqlFunc.Subqueryable<LqXhHyhkEntity>()
                    .Where(xh => xh.Id == x.ConsumeInfoId && xh.Md == input.StoreId && xh.IsEffective == 1)
                    .Any());
            }

            var result = await query
                .GroupBy(x => x.Px)
                .Select(x => new ItemConsumeStatisticsData
                {
                    ItemId = x.Px,
                    ConsumeAmount = SqlFunc.AggregateSum(x.Pxjg * x.ProjectNumber),
                    ConsumePurchaseCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "购买", x.ProjectNumber, 0)),
                    ConsumeGiftCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "赠送", x.ProjectNumber, 0)),
                    ConsumeExperienceCount = SqlFunc.AggregateSum(SqlFunc.IIF(x.SourceType == "体验", x.ProjectNumber, 0))
                })
                .ToListAsync();

            return result;
        }

        /// <summary>
        /// 获取退卡统计数据
        /// </summary>
        private async Task<List<ItemRefundStatisticsData>> GetRefundStatistics(List<string> itemIds, LqXmzlStatisticsInput input)
        {
            var query = _db.Queryable<LqHytkMxEntity>()
                .Where(x => itemIds.Contains(x.Px));

            // 时间过滤
            if (input.StartTime.HasValue)
            {
                query = query.Where(x => x.Tksj >= input.StartTime.Value);
            }
            if (input.EndTime.HasValue)
            {
                query = query.Where(x => x.Tksj <= input.EndTime.Value);
            }

            // 门店过滤（通过退卡记录关联）
            if (!string.IsNullOrEmpty(input.StoreId))
            {
                // 使用子查询过滤门店 - 在SQL层面过滤，避免别名问题
                query = query.Where(x => SqlFunc.Subqueryable<LqHytkHytkEntity>()
                    .Where(hytk => hytk.Id == x.RefundInfoId && hytk.Md == input.StoreId && hytk.IsEffective == 1)
                    .Any());
            }

            var result = await query
                .GroupBy(x => x.Px)
                .Select(x => new ItemRefundStatisticsData
                {
                    ItemId = x.Px,
                    RefundAmount = SqlFunc.AggregateSum(x.Tkje ?? 0),
                    RefundCount = SqlFunc.AggregateCount(x.Id)
                })
                .ToListAsync();

            return result;
        }
        #endregion

    }

    /// <summary>
    /// 品项统计数据（内部类）
    /// </summary>
    public class ItemStatisticsData
    {
        public string ItemId { get; set; }
        public decimal BillingAmount { get; set; }
        public int TotalBuyers { get; set; }
        public int RepeatBuyers { get; set; }
    }

    /// <summary>
    /// 品项消耗统计数据（内部类）
    /// </summary>
    public class ItemConsumeStatisticsData
    {
        public string ItemId { get; set; }
        public decimal ConsumeAmount { get; set; }
        public int ConsumePurchaseCount { get; set; }
        public int ConsumeGiftCount { get; set; }
        public int ConsumeExperienceCount { get; set; }
    }

    /// <summary>
    /// 品项退卡统计数据（内部类）
    /// </summary>
    public class ItemRefundStatisticsData
    {
        public string ItemId { get; set; }
        public decimal RefundAmount { get; set; }
        public int RefundCount { get; set; }
    }
}