using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Core.Manager;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqPackageInfo;
using NCC.Extend.Entitys.Dto.LqXmzl;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_package_info;
using NCC.Extend.Entitys.lq_package_item_detail;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Interfaces.LqPackageInfo;
using NCC.FriendlyException;
using SqlSugar;
using Yitter.IdGenerator;

namespace NCC.Extend.LqPackageInfo
{
    /// <summary>
    /// 营销活动服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤营销活动服务", Name = "LqPackageInfo", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqPackageInfoService : ILqPackageInfoService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqPackageInfoEntity> _packageInfoRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="packageInfoRepository">营销活动仓储</param>
        /// <param name="userManager">用户管理器</param>
        public LqPackageInfoService(ISqlSugarRepository<LqPackageInfoEntity> packageInfoRepository, IUserManager userManager)
        {
            _packageInfoRepository = packageInfoRepository;
            _db = packageInfoRepository.Context;
            _userManager = userManager;
        }

        #region 添加营销活动
        /// <summary>
        /// 添加营销活动
        /// </summary>
        /// <param name="input">营销活动创建输入</param>
        /// <returns>营销活动ID</returns>
        /// <remarks>
        /// 创建新的营销活动，包含活动基本信息和品项明细
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityName": "春季护肤优惠活动",
        ///   "activityDesc": "春季护肤特惠活动",
        ///   "startTime": "2025-03-01T00:00:00",
        ///   "endTime": "2025-03-31T23:59:59",
        ///   "minItemQuantity": 2,
        ///   "activityRules": "活动规则说明",
        ///   "activityImages": "[\"image1.jpg\"]",
        ///   "sortOrder": 1,
        ///   "activityItems": [
        ///     {
        ///       "itemId": "ITEM001",
        ///       "itemName": "面部清洁",
        ///       "itemCategory": "基础护理",
        ///       "itemRemark": "推荐品项"
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - activityName: 营销活动名称
        /// - activityDesc: 营销活动描述
        /// - startTime: 活动开始时间
        /// - endTime: 活动结束时间
        /// - minItemQuantity: 至少购买品项数量
        /// - activityRules: 活动规则说明
        /// - activityImages: 活动图片(JSON格式)
        /// - sortOrder: 排序
        /// - activityItems: 营销活动品项明细列表
        /// </remarks>
        /// <response code="200">成功创建营销活动，返回营销活动ID</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("CreatePackageInfoAsync")]
        public async Task<string> CreatePackageInfoAsync(LqPackageInfoCrInput input)
        {
            try
            {
                // 验证活动时间
                if (input.StartTime >= input.EndTime)
                {
                    throw NCCException.Oh("活动开始时间必须小于结束时间");
                }

                // 验证品项明细
                if (input.ActivityItems == null || !input.ActivityItems.Any())
                {
                    throw NCCException.Oh("营销活动必须包含至少一个品项");
                }

                // 验证至少购买品项数量
                if (input.ActivityItems.Count < input.MinItemQuantity)
                {
                    throw NCCException.Oh($"品项数量不能少于至少购买品项数量({input.MinItemQuantity})");
                }

                // 开始事务
                _db.BeginTran();

                try
                {
                    // 创建营销活动实体
                    var packageInfo = input.Adapt<LqPackageInfoEntity>();
                    packageInfo.Id = YitIdHelper.NextId().ToString();
                    packageInfo.CreateTime = DateTime.Now;
                    packageInfo.UpdateTime = DateTime.Now;
                    packageInfo.CreateUser = _userManager.UserId;
                    packageInfo.UpdateUser = _userManager.UserId;
                    packageInfo.IsEffective = StatusEnum.有效.GetHashCode();
                    // 保存营销活动
                    await _db.Insertable(packageInfo).ExecuteCommandAsync();
                    // 创建营销活动品项明细
                    var packageItemDetails = input.ActivityItems.Select(item =>
                    {
                        var packageItemDetail = item.Adapt<LqPackageItemDetailEntity>();
                        packageItemDetail.Id = YitIdHelper.NextId().ToString();
                        packageItemDetail.ActivityId = packageInfo.Id;
                        packageItemDetail.CreateTime = DateTime.Now;
                        packageItemDetail.UpdateTime = DateTime.Now;
                        packageItemDetail.IsEffective = StatusEnum.有效.GetHashCode();
                        return packageItemDetail;
                    }).ToList();
                    // 批量保存营销活动品项明细
                    if (packageItemDetails.Any())
                    {
                        await _db.Insertable(packageItemDetails).ExecuteCommandAsync();
                    }
                    // 提交事务
                    _db.CommitTran();

                    return packageInfo.Id;
                }
                catch
                {
                    // 回滚事务
                    _db.RollbackTran();
                    throw;
                }
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"添加营销活动失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取营销活动列表
        /// <summary>
        /// 获取营销活动列表
        /// </summary>
        /// <param name="input">营销活动列表查询输入</param>
        /// <returns>营销活动列表</returns>
        /// <remarks>
        /// 获取营销活动列表，支持按活动名称、时间范围等条件筛选
        /// 
        /// 查询参数说明：
        /// - activityName: 活动名称（模糊查询）
        /// - activityDesc: 活动描述（模糊查询）
        /// - startTimeStart: 活动开始时间范围开始
        /// - startTimeEnd: 活动开始时间范围结束
        /// - endTimeStart: 活动结束时间范围开始
        /// - endTimeEnd: 活动结束时间范围结束
        /// - minItemQuantity: 至少购买品项数量
        /// - createTimeStart: 创建时间范围开始
        /// - createTimeEnd: 创建时间范围结束
        /// </remarks>
        /// <response code="200">成功获取营销活动列表</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageInfoListAsync")]
        public async Task<dynamic> GetPackageInfoListAsync([FromQuery] LqPackageInfoListQueryInput input)
        {
            var sidx = input.sidx == null ? "CreateTime" : input.sidx;
            var data = await _db.Queryable<LqPackageInfoEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.ActivityName), w => w.ActivityName.Contains(input.ActivityName))
                .WhereIF(!string.IsNullOrEmpty(input.ActivityDesc), w => w.ActivityDesc.Contains(input.ActivityDesc))
                .WhereIF(input.StartTimeStart.HasValue, w => w.StartTime >= input.StartTimeStart.Value)
                .WhereIF(input.StartTimeEnd.HasValue, w => w.StartTime <= input.StartTimeEnd.Value)
                .WhereIF(input.EndTimeStart.HasValue, w => w.EndTime >= input.EndTimeStart.Value)
                .WhereIF(input.EndTimeEnd.HasValue, w => w.EndTime <= input.EndTimeEnd.Value)
                .WhereIF(input.MinItemQuantity.HasValue, w => w.MinItemQuantity == input.MinItemQuantity.Value)
                .WhereIF(input.CreateTimeStart.HasValue, w => w.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, w => w.CreateTime <= input.CreateTimeEnd.Value)
                .WhereIF(input.IsEffective.HasValue, w => w.IsEffective == input.IsEffective.Value)
                .Select(it => new LqPackageInfoListOutput
                {
                    id = it.Id,
                    activityName = it.ActivityName,
                    activityDesc = it.ActivityDesc,
                    startTime = it.StartTime,
                    endTime = it.EndTime,
                    minItemQuantity = it.MinItemQuantity,
                    activityRules = it.ActivityRules,
                    sortOrder = it.SortOrder,
                    createTime = it.CreateTime,
                    isEffective = it.IsEffective,
                    createUser = it.CreateUser,
                    updateUser = it.UpdateUser,
                    updateTime = it.UpdateTime,
                    activityImages = it.ActivityImages,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqPackageInfoListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 获取营销活动详情
        /// <summary>
        /// 获取营销活动详情
        /// </summary>
        /// <param name="id">营销活动ID</param>
        /// <returns>营销活动详情（包含品项信息）</returns>
        /// <remarks>
        /// 获取营销活动详情，包括活动基本信息和对应的品项明细列表
        /// 
        /// 返回数据说明：
        /// - 活动基本信息：活动名称、描述、时间、规则等
        /// - 品项明细列表：该活动包含的所有品项信息
        /// </remarks>
        /// <response code="200">成功获取营销活动详情</response>
        /// <response code="404">营销活动不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageInfoDetailAsync")]
        public async Task<dynamic> GetPackageInfoDetailAsync(string id)
        {
            try
            {
                // 获取营销活动基本信息
                var packageInfo = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == id).FirstAsync();
                if (packageInfo == null)
                {
                    throw NCCException.Oh("营销活动不存在");
                }
                // 获取品项明细信息
                var activityItems = await _db.Queryable<LqPackageItemDetailEntity>()
                    .Where(w => w.ActivityId == id && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .OrderBy(w => w.CreateTime)
                    .Select(it => new
                    {
                        id = it.Id,
                        activityId = it.ActivityId,
                        itemId = it.ItemId,
                        itemName = it.ItemName,
                        itemCategory = it.ItemCategory,
                        itemRemark = it.ItemRemark,
                        createTime = it.CreateTime
                    })
                    .ToListAsync();
                // 构建返回结果
                var result = new
                {
                    // 活动基本信息
                    id = packageInfo.Id,
                    activityName = packageInfo.ActivityName,
                    activityDesc = packageInfo.ActivityDesc,
                    startTime = packageInfo.StartTime,
                    endTime = packageInfo.EndTime,
                    minItemQuantity = packageInfo.MinItemQuantity,
                    activityRules = packageInfo.ActivityRules,
                    activityImages = packageInfo.ActivityImages,
                    sortOrder = packageInfo.SortOrder,
                    createTime = packageInfo.CreateTime,
                    updateTime = packageInfo.UpdateTime,
                    createUser = packageInfo.CreateUser,
                    updateUser = packageInfo.UpdateUser,
                    isEffective = packageInfo.IsEffective,

                    // 品项明细列表
                    activityItems = activityItems
                };

                return result;
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动详情失败：{ex.Message}");
            }
        }
        #endregion

        #region 更新营销活动
        /// <summary>
        /// 更新营销活动
        /// </summary>
        /// <param name="input">营销活动更新输入</param>
        /// <returns>营销活动ID</returns>
        /// <remarks>
        /// 更新营销活动信息，包括活动基本信息和品项明细
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "id": "123456789",
        ///   "activityName": "春季护肤优惠活动",
        ///   "activityDesc": "春季护肤特惠活动",
        ///   "startTime": "2025-03-01T00:00:00",
        ///   "endTime": "2025-03-31T23:59:59",
        ///   "minItemQuantity": 2,
        ///   "activityRules": "活动规则说明",
        ///   "activityImages": "[\"image1.jpg\"]",
        ///   "sortOrder": 1,
        ///   "activityItems": [
        ///     {
        ///       "itemId": "ITEM001",
        ///       "itemName": "面部清洁",
        ///       "itemCategory": "基础护理",
        ///       "itemRemark": "推荐品项"
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - id: 营销活动ID（必填）
        /// - activityName: 营销活动名称
        /// - activityDesc: 营销活动描述
        /// - startTime: 活动开始时间
        /// - endTime: 活动结束时间
        /// - minItemQuantity: 至少购买品项数量
        /// - activityRules: 活动规则说明
        /// - activityImages: 活动图片(JSON格式)
        /// - sortOrder: 排序
        /// - activityItems: 营销活动品项明细列表
        /// </remarks>
        /// <response code="200">成功更新营销活动，返回营销活动ID</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="404">营销活动不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("UpdatePackageInfoAsync")]
        public async Task<string> UpdatePackageInfoAsync(LqPackageInfoUpInput input)
        {
            try
            {
                // 验证活动时间
                if (input.StartTime >= input.EndTime)
                {
                    throw NCCException.Oh("活动开始时间必须小于结束时间");
                }

                // 验证品项明细
                if (input.ActivityItems == null || !input.ActivityItems.Any())
                {
                    throw NCCException.Oh("营销活动必须包含至少一个品项");
                }

                // 验证至少购买品项数量
                if (input.ActivityItems.Count < input.MinItemQuantity)
                {
                    throw NCCException.Oh($"品项数量不能少于至少购买品项数量({input.MinItemQuantity})");
                }

                // 检查营销活动是否存在
                var existingActivity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(w => w.Id == input.Id)
                    .FirstAsync();

                if (existingActivity == null)
                {
                    throw NCCException.Oh("营销活动不存在");
                }

                // 开始事务
                _db.BeginTran();

                try
                {
                    // 更新营销活动基本信息
                    var updateResult = await _db.Updateable<LqPackageInfoEntity>()
                        .SetColumns(it => new LqPackageInfoEntity
                        {
                            ActivityName = input.ActivityName,
                            ActivityDesc = input.ActivityDesc,
                            StartTime = input.StartTime,
                            EndTime = input.EndTime,
                            MinItemQuantity = input.MinItemQuantity,
                            ActivityRules = input.ActivityRules,
                            ActivityImages = input.ActivityImages,
                            SortOrder = input.SortOrder,
                            UpdateTime = DateTime.Now,
                            UpdateUser = _userManager.UserId
                        })
                        .Where(w => w.Id == input.Id)
                        .ExecuteCommandAsync();

                    if (updateResult <= 0)
                    {
                        throw NCCException.Oh("更新营销活动失败");
                    }

                    // 删除原有的品项明细
                    await _db.Deleteable<LqPackageItemDetailEntity>()
                        .Where(w => w.ActivityId == input.Id)
                        .ExecuteCommandAsync();

                    // 创建新的品项明细
                    var packageItemDetails = input.ActivityItems.Select(item =>
                    {
                        var packageItemDetail = item.Adapt<LqPackageItemDetailEntity>();
                        packageItemDetail.Id = YitIdHelper.NextId().ToString();
                        packageItemDetail.ActivityId = input.Id;
                        packageItemDetail.CreateTime = DateTime.Now;
                        packageItemDetail.UpdateTime = DateTime.Now;
                        packageItemDetail.IsEffective = 1;
                        return packageItemDetail;
                    }).ToList();

                    // 批量保存新的品项明细
                    if (packageItemDetails.Any())
                    {
                        await _db.Insertable(packageItemDetails).ExecuteCommandAsync();
                    }

                    // 提交事务
                    _db.CommitTran();

                    return input.Id;
                }
                catch
                {
                    // 回滚事务
                    _db.RollbackTran();
                    throw;
                }
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"更新营销活动失败: {ex.Message}");
            }
        }
        #endregion

        #region 标记删除营销活动
        /// <summary>
        /// 标记删除营销活动
        /// </summary>
        /// <param name="id">营销活动ID</param>
        /// <returns>营销活动ID</returns>
        [HttpDelete("MarkDeletePackageInfoAsync")]
        public async Task<string> MarkDeletePackageInfoAsync(string id)
        {
            var entity = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == id).FirstAsync();
            if (entity == null)
            {
                throw NCCException.Oh("营销活动不存在");
            }
            entity.IsEffective = StatusEnum.无效.GetHashCode();
            await _db.Updateable(entity).ExecuteCommandAsync();
            await _db.Updateable<LqPackageItemDetailEntity>().SetColumns(it => new LqPackageItemDetailEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.ActivityId == id).ExecuteCommandAsync();
            return id;
        }
        #endregion

        #region 根据营销活动ID获取品项详情
        /// <summary>
        /// 根据营销活动ID获取品项详情
        /// </summary>
        /// <param name="activityId">营销活动ID</param>
        /// <returns>品项详情列表</returns>
        /// <remarks>
        /// 通过营销活动ID获取对应的品项ID，然后查询品项表获取完整的品项信息
        /// 
        /// 查询流程：
        /// 1. 根据营销活动ID查询营销活动品项明细表，获取品项ID列表
        /// 2. 根据品项ID列表查询项目资料表，获取完整的品项信息
        /// 3. 返回品项详情列表
        /// </remarks>
        /// <response code="200">成功获取品项详情</response>
        /// <response code="404">营销活动不存在或没有品项</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetPackageItemDetailByActivityIdAsync/{activityId}")]
        public async Task<dynamic> GetPackageItemDetailByActivityIdAsync(string activityId)
        {
            try
            {
                // 验证参数
                if (string.IsNullOrEmpty(activityId))
                {
                    throw NCCException.Oh("营销活动ID不能为空");
                }
                // 检查营销活动是否存在
                var activityExists = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.Id == activityId && w.IsEffective == StatusEnum.有效.GetHashCode()).AnyAsync();
                if (!activityExists)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }
                // 获取营销活动下的品项ID列表
                var itemIds = await _db.Queryable<LqPackageItemDetailEntity>().Where(w => w.ActivityId == activityId && w.IsEffective == StatusEnum.有效.GetHashCode()).Select(w => w.ItemId).ToListAsync();
                if (!itemIds.Any())
                {
                    throw NCCException.Oh("该营销活动暂无品项");
                }
                // 根据品项ID查询项目资料表
                var itemDetails = await _db.Queryable<LqXmzlEntity>()
                    .Where(w => itemIds.Contains(w.Id) && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .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,
                        isEffective = it.IsEffective,
                        sourceType = it.SourceType
                    }).MergeTable().ToPagedListAsync(1, 50);
                return PageResult<LqXmzlListOutput>.SqlSugarPageResult(itemDetails);
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取品项详情失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取当前时间有效的活动
        /// <summary>
        /// 获取当前时间有效的活动
        /// </summary>
        /// <returns>当前时间有效的活动</returns>
        [HttpGet("GetCurrentTimeEffectiveActivityAsync")]
        public async Task<dynamic> GetCurrentTimeEffectiveActivityAsync()
        {
            var data = await _db.Queryable<LqPackageInfoEntity>().Where(w => w.StartTime <= DateTime.Now && w.EndTime >= DateTime.Now && w.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
            var output = data.Adapt<List<LqPackageInfoListOutput>>();
            return output;
        }
        #endregion

        #region 营销活动统计
        /// <summary>
        /// 获取营销活动统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定营销活动的开单数据，支持分页
        /// 包括：总记录数、开单总金额、欠款总金额、开单列表
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityId": "营销活动ID",
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31",
        ///   "storeIds": ["门店ID1", "门店ID2"],
        ///   "pageIndex": 1,
        ///   "pageSize": 20
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - activityId: 营销活动ID（必填）
        /// - startTime: 开始时间（可选，默认为活动开始时间）
        /// - endTime: 结束时间（可选，默认为活动结束时间）
        /// - storeIds: 门店ID列表（可选）
        /// - pageIndex: 页码（默认1）
        /// - pageSize: 每页数量（默认20）
        /// 
        /// 返回字段说明：
        /// - ActivityId: 营销活动ID
        /// - ActivityName: 营销活动名称
        /// - BillingCount: 开单数量
        /// - BillingAmount: 开单总金额
        /// - DebtAmount: 欠款总金额
        /// - BillingList: 开单列表（包含开单ID、日期、客户信息、门店信息、金额、欠款等）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>营销活动统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-activity-statistics")]
        public async Task<object> GetActivityStatistics(ActivityStatisticsInput input)
        {
            try
            {
                // 1. 获取营销活动信息
                var activity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(x => x.Id == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (activity == null)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }

                // 2. 设置时间范围（如果未提供，使用活动时间范围）
                var startTime = input.StartTime ?? activity.StartTime;
                var endTime = input.EndTime ?? activity.EndTime;

                // 3. 构建门店过滤条件
                string storeFilter = "";

                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdsStr = string.Join("','", input.StoreIds);
                    storeFilter = $"AND kd.djmd IN ('{storeIdsStr}')";
                }

                // 4. 获取总记录数和总金额
                var totalSql = $@"
                    SELECT 
                        COUNT(*) as total,
                        COALESCE(SUM(CAST(kd.sfyj AS DECIMAL(18,2))), 0) as billing_amount,
                        COALESCE(SUM(CAST(kd.qk AS DECIMAL(18,2))), 0) as debt_amount
                    FROM lq_kd_kdjlb kd
                    WHERE kd.F_IsEffective = 1
                        AND kd.F_ActivityId = '{input.ActivityId}'
                        AND kd.kdrq >= '{startTime:yyyy-MM-dd HH:mm:ss}' 
                        AND kd.kdrq <= '{endTime:yyyy-MM-dd HH:mm:ss}'
                        {storeFilter}";

                var totalData = await _db.Ado.SqlQueryAsync<dynamic>(totalSql);
                var billingCount = Convert.ToInt32(totalData.FirstOrDefault()?.total ?? 0);
                var billingAmount = Convert.ToDecimal(totalData.FirstOrDefault()?.billing_amount ?? 0);
                var debtAmount = Convert.ToDecimal(totalData.FirstOrDefault()?.debt_amount ?? 0);

                // 5. 分页查询开单列表
                var offset = (input.PageIndex - 1) * input.PageSize;
                var billingListSql = $@"
                    SELECT 
                        kd.F_Id as BillingId,
                        kd.kdrq as BillingDate,
                        COALESCE(kh.khmc, '') as CustomerName,
                        COALESCE(kh.sjh, '') as CustomerPhone,
                        kd.djmd as StoreId,
                        COALESCE(md.dm, '') as StoreName,
                        CAST(kd.sfyj AS DECIMAL(18,2)) as Amount,
                        CAST(kd.qk AS DECIMAL(18,2)) as DebtAmount
                    FROM lq_kd_kdjlb kd
                    LEFT JOIN lq_khxx kh ON kd.kdhy = kh.F_Id
                    LEFT JOIN lq_mdxx md ON kd.djmd = md.F_Id
                    WHERE kd.F_IsEffective = 1
                        AND kd.F_ActivityId = '{input.ActivityId}'
                        AND kd.kdrq >= '{startTime:yyyy-MM-dd HH:mm:ss}' 
                        AND kd.kdrq <= '{endTime:yyyy-MM-dd HH:mm:ss}'
                        {storeFilter}
                    ORDER BY kd.kdrq DESC
                    LIMIT {input.PageSize} OFFSET {offset}";

                var billingListData = await _db.Ado.SqlQueryAsync<dynamic>(billingListSql);
                var billingList = billingListData?.Select(item => new ActivityBillingItem
                {
                    BillingId = item.BillingId?.ToString(),
                    BillingDate = item.BillingDate != null ? (DateTime?)item.BillingDate : null,
                    CustomerName = item.CustomerName?.ToString(),
                    CustomerPhone = item.CustomerPhone?.ToString(),
                    StoreId = item.StoreId?.ToString(),
                    StoreName = item.StoreName?.ToString(),
                    Amount = Convert.ToDecimal(item.Amount ?? 0),
                    DebtAmount = Convert.ToDecimal(item.DebtAmount ?? 0)
                }).ToList() ?? new List<ActivityBillingItem>();

                // 6. 返回统计结果
                return new ActivityStatisticsOutput
                {
                    ActivityId = input.ActivityId,
                    ActivityName = activity.ActivityName,
                    BillingCount = billingCount,
                    BillingAmount = billingAmount,
                    DebtAmount = debtAmount,
                    BillingList = billingList
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动统计数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 营销活动按门店统计
        /// <summary>
        /// 获取营销活动按门店统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定营销活动在各个门店的开单数据
        /// 包括：开单数量、开单金额、欠款金额、人头数、项目数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityId": "营销活动ID",
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31",
        ///   "storeIds": ["门店ID1", "门店ID2"]
        /// }
        /// ```
        /// 
        /// 返回字段说明：
        /// - ActivityId: 营销活动ID
        /// - ActivityName: 营销活动名称
        /// - StoreList: 门店统计列表
        ///   - StoreId: 门店ID
        ///   - StoreName: 门店名称
        ///   - BillingCount: 开单数量
        ///   - BillingAmount: 开单金额（实付业绩总和）
        ///   - DebtAmount: 欠款金额
        ///   - CustomerCount: 人头数（去重客户数）
        ///   - ItemCount: 项目数（品项项目次数总和，使用F_ProjectNumber字段）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>按门店统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-activity-statistics-by-store")]
        public async Task<object> GetActivityStatisticsByStore(ActivityStatisticsInput input)
        {
            try
            {
                // 1. 获取营销活动信息
                var activity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(x => x.Id == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (activity == null)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }

                // 2. 设置时间范围（如果未提供，使用活动时间范围）
                var startTime = input.StartTime ?? activity.StartTime;
                var endTime = input.EndTime ?? activity.EndTime;

                // 3. 构建基础查询：开单记录 JOIN 门店表
                var baseQuery = _db.Queryable<LqKdKdjlbEntity, LqMdxxEntity>(
                        (kd, md) => kd.Djmd == md.Id)
                    .Where((kd, md) => kd.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((kd, md) => kd.ActivityId == input.ActivityId)
                    .Where((kd, md) => kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                // 4. 门店筛选
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    baseQuery = baseQuery.Where((kd, md) => input.StoreIds.Contains(kd.Djmd));
                }

                // 5. 按门店分组统计（先获取基础统计数据）
                var storeStatistics = await baseQuery
                    .GroupBy((kd, md) => new { StoreId = kd.Djmd, StoreName = md.Dm })
                    .Select((kd, md) => new
                    {
                        StoreId = kd.Djmd,
                        StoreName = md.Dm ?? "",
                        BillingCount = SqlFunc.AggregateCount(kd.Id),
                        BillingAmount = SqlFunc.AggregateSum(kd.Sfyj),
                        DebtAmount = SqlFunc.AggregateSum(kd.Qk)
                    })
                    .ToListAsync();

                // 6. 单独统计每个门店的人头数和项目数
                var storeList = new List<StoreStatisticsItem>();
                foreach (var stat in storeStatistics)
                {
                    // 统计人头数（去重客户数）
                    var customerCountQuery = _db.Queryable<LqKdKdjlbEntity>()
                        .Where(kd => kd.Djmd == stat.StoreId
                            && kd.ActivityId == input.ActivityId
                            && kd.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                    if (input.StoreIds != null && input.StoreIds.Any())
                    {
                        customerCountQuery = customerCountQuery.Where(kd => input.StoreIds.Contains(kd.Djmd));
                    }

                    var customerCount = await customerCountQuery
                        .GroupBy(kd => kd.Kdhy)
                        .Select(kd => kd.Kdhy)
                        .CountAsync();

                    // 统计项目数（品项项目次数总和，使用F_ProjectNumber字段）
                    var itemCountQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>(
                            (px, kd) => px.Glkdbh == kd.Id)
                        .Where((px, kd) => kd.Djmd == stat.StoreId
                            && px.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.ActivityId == input.ActivityId
                            && kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                    if (input.StoreIds != null && input.StoreIds.Any())
                    {
                        itemCountQuery = itemCountQuery.Where((px, kd) => input.StoreIds.Contains(kd.Djmd));
                    }

                    var itemCount = await itemCountQuery
                        .Select((px, kd) => SqlFunc.AggregateSum(px.ProjectNumber))
                        .FirstAsync();

                    storeList.Add(new StoreStatisticsItem
                    {
                        StoreId = stat.StoreId ?? "",
                        StoreName = stat.StoreName ?? "",
                        BillingCount = stat.BillingCount,
                        BillingAmount = stat.BillingAmount,
                        DebtAmount = stat.DebtAmount,
                        CustomerCount = customerCount,
                        ItemCount = itemCount
                    });
                }

                // 7. 排序
                storeList = storeList.OrderBy(x => x.StoreName).ToList();

                // 7. 返回统计结果
                return new ActivityStatisticsByStoreOutput
                {
                    ActivityId = input.ActivityId,
                    ActivityName = activity.ActivityName,
                    StoreList = storeList
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动按门店统计数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 营销活动按品项统计
        /// <summary>
        /// 获取营销活动按品项统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定营销活动各个品项的销售情况
        /// 包括：销售数量、销售金额、开单数量、销售次数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityId": "营销活动ID",
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31"
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>按品项统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-activity-statistics-by-item")]
        public async Task<object> GetActivityStatisticsByItem(ActivityStatisticsInput input)
        {
            try
            {
                // 1. 获取营销活动信息
                var activity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(x => x.Id == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (activity == null)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }

                // 2. 设置时间范围（如果未提供，使用活动时间范围）
                var startTime = input.StartTime ?? activity.StartTime;
                var endTime = input.EndTime ?? activity.EndTime;

                // 3. 构建基础查询：品项明细 JOIN 开单记录（用于过滤活动ID和时间）
                var baseQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>(
                        (px, kd) => px.Glkdbh == kd.Id)
                    .Where((px, kd) => px.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((px, kd) => kd.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((px, kd) => kd.ActivityId == input.ActivityId)
                    .Where((px, kd) => kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                // 4. 门店筛选（如果提供）
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    baseQuery = baseQuery.Where((px, kd) => input.StoreIds.Contains(kd.Djmd));
                }

                // 5. 按品项分组统计（先获取基础统计数据）
                // 注意：SqlSugar 的 AggregateSum 可能不支持三元运算符，需要先查询数据再计算
                var itemStatistics = await baseQuery
                    .GroupBy((px, kd) => new { ItemId = px.Px, ItemName = px.Pxmc })
                    .Select((px, kd) => new
                    {
                        ItemId = px.Px ?? "",
                        ItemName = px.Pxmc ?? "",
                        SalesQuantity = SqlFunc.AggregateSum(px.ProjectNumber),
                        SalesAmountActual = SqlFunc.AggregateSum(px.ActualPrice),
                        SalesAmountTotal = SqlFunc.AggregateSum(px.TotalPrice),
                        SalesCount = SqlFunc.AggregateCount(px.Id)
                    })
                    .ToListAsync();

                // 计算实际销售金额（ActualPrice > 0 时使用 ActualPrice，否则使用 TotalPrice）
                var itemStatisticsWithAmount = itemStatistics.Select(stat => new
                {
                    stat.ItemId,
                    stat.ItemName,
                    stat.SalesQuantity,
                    SalesAmount = stat.SalesAmountActual > 0 ? stat.SalesAmountActual : stat.SalesAmountTotal,
                    stat.SalesCount
                }).ToList();

                // 6. 单独统计每个品项的开单数量（去重开单ID）
                var itemList = new List<ItemStatisticsItem>();
                foreach (var stat in itemStatisticsWithAmount)
                {
                    var billingCountQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>(
                            (px, kd) => px.Glkdbh == kd.Id)
                        .Where((px, kd) => px.Px == stat.ItemId
                            && px.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.ActivityId == input.ActivityId
                            && kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                    if (input.StoreIds != null && input.StoreIds.Any())
                    {
                        billingCountQuery = billingCountQuery.Where((px, kd) => input.StoreIds.Contains(kd.Djmd));
                    }

                    var billingCount = await billingCountQuery
                        .GroupBy((px, kd) => px.Glkdbh)
                        .Select((px, kd) => px.Glkdbh)
                        .CountAsync();

                    itemList.Add(new ItemStatisticsItem
                    {
                        ItemId = stat.ItemId ?? "",
                        ItemName = stat.ItemName ?? "",
                        SalesQuantity = stat.SalesQuantity,
                        SalesAmount = stat.SalesAmount,
                        BillingCount = billingCount,
                        SalesCount = stat.SalesCount
                    });
                }

                // 7. 排序
                itemList = itemList.OrderBy(x => x.ItemName).ToList();

                // 7. 返回统计结果
                return new ActivityStatisticsByItemOutput
                {
                    ActivityId = input.ActivityId,
                    ActivityName = activity.ActivityName,
                    ItemList = itemList
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动按品项统计数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 营销活动按门店品项统计
        /// <summary>
        /// 获取营销活动按门店品项统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定营销活动在各个门店的各个品项销售情况
        /// 包括：销售数量、销售金额、开单数量、销售次数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "activityId": "营销活动ID",
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31",
        ///   "storeIds": ["门店ID1", "门店ID2"]
        /// }
        /// ```
        /// 
        /// 返回格式（层级结构）：
        /// - ActivityId: 营销活动ID
        /// - ActivityName: 营销活动名称
        /// - StoreList: 门店统计列表
        ///   - StoreId: 门店ID
        ///   - StoreName: 门店名称
        ///   - ItemList: 该门店下的品项统计列表
        ///     - ItemId: 品项ID
        ///     - ItemName: 品项名称
        ///     - SalesQuantity: 销售数量（项目次数总和）
        ///     - SalesAmount: 销售金额
        ///     - BillingCount: 开单数量（包含该品项的开单数）
        ///     - SalesCount: 销售次数（品项明细记录数）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>按门店品项统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-activity-statistics-by-store-item")]
        public async Task<object> GetActivityStatisticsByStoreItem(ActivityStatisticsInput input)
        {
            try
            {
                // 1. 获取营销活动信息
                var activity = await _db.Queryable<LqPackageInfoEntity>()
                    .Where(x => x.Id == input.ActivityId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (activity == null)
                {
                    throw NCCException.Oh("营销活动不存在或已失效");
                }

                // 2. 设置时间范围（如果未提供，使用活动时间范围）
                var startTime = input.StartTime ?? activity.StartTime;
                var endTime = input.EndTime ?? activity.EndTime;

                // 3. 构建基础查询：品项明细 JOIN 开单记录 JOIN 门店表
                var baseQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity, LqMdxxEntity>(
                        (px, kd, md) => px.Glkdbh == kd.Id && kd.Djmd == md.Id)
                    .Where((px, kd, md) => px.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((px, kd, md) => kd.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((px, kd, md) => kd.ActivityId == input.ActivityId)
                    .Where((px, kd, md) => kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                // 4. 门店筛选（如果提供）
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    baseQuery = baseQuery.Where((px, kd, md) => input.StoreIds.Contains(kd.Djmd));
                }

                // 5. 按门店和品项分组统计（先获取基础统计数据）
                // 注意：SqlSugar 的 AggregateSum 可能不支持三元运算符，需要先查询数据再计算
                var storeItemStatistics = await baseQuery
                    .GroupBy((px, kd, md) => new
                    {
                        StoreId = kd.Djmd,
                        StoreName = md.Dm,
                        ItemId = px.Px,
                        ItemName = px.Pxmc
                    })
                    .Select((px, kd, md) => new
                    {
                        StoreId = kd.Djmd ?? "",
                        StoreName = md.Dm ?? "",
                        ItemId = px.Px ?? "",
                        ItemName = px.Pxmc ?? "",
                        SalesQuantity = SqlFunc.AggregateSum(px.ProjectNumber),
                        SalesAmountActual = SqlFunc.AggregateSum(px.ActualPrice),
                        SalesAmountTotal = SqlFunc.AggregateSum(px.TotalPrice),
                        SalesCount = SqlFunc.AggregateCount(px.Id)
                    })
                    .ToListAsync();

                // 计算实际销售金额（ActualPrice > 0 时使用 ActualPrice，否则使用 TotalPrice）
                var storeItemStatisticsWithAmount = storeItemStatistics.Select(stat => new
                {
                    stat.StoreId,
                    stat.StoreName,
                    stat.ItemId,
                    stat.ItemName,
                    stat.SalesQuantity,
                    SalesAmount = stat.SalesAmountActual > 0 ? stat.SalesAmountActual : stat.SalesAmountTotal,
                    stat.SalesCount
                }).ToList();

                // 6. 单独统计每个门店品项的开单数量（去重开单ID），并组装成层级结构
                var storeItemDict = new Dictionary<string, List<StoreItemStatisticsItem>>();

                foreach (var stat in storeItemStatisticsWithAmount)
                {
                    var billingCountQuery = _db.Queryable<LqKdPxmxEntity, LqKdKdjlbEntity>(
                            (px, kd) => px.Glkdbh == kd.Id)
                        .Where((px, kd) => px.Px == stat.ItemId
                            && kd.Djmd == stat.StoreId
                            && px.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.IsEffective == StatusEnum.有效.GetHashCode()
                            && kd.ActivityId == input.ActivityId
                            && kd.Kdrq.HasValue && kd.Kdrq.Value >= startTime && kd.Kdrq.Value <= endTime);

                    if (input.StoreIds != null && input.StoreIds.Any())
                    {
                        billingCountQuery = billingCountQuery.Where((px, kd) => input.StoreIds.Contains(kd.Djmd));
                    }

                    var billingCount = await billingCountQuery
                        .GroupBy((px, kd) => px.Glkdbh)
                        .Select((px, kd) => px.Glkdbh)
                        .CountAsync();

                    var itemStat = new StoreItemStatisticsItem
                    {
                        ItemId = stat.ItemId ?? "",
                        ItemName = stat.ItemName ?? "",
                        SalesQuantity = stat.SalesQuantity,
                        SalesAmount = stat.SalesAmount,
                        BillingCount = billingCount,
                        SalesCount = stat.SalesCount
                    };

                    // 按门店分组
                    var storeKey = stat.StoreId ?? "";
                    if (!storeItemDict.ContainsKey(storeKey))
                    {
                        storeItemDict[storeKey] = new List<StoreItemStatisticsItem>();
                    }
                    storeItemDict[storeKey].Add(itemStat);
                }

                // 7. 组装层级结构：门店 -> 品项列表
                var storeList = new List<StoreStatisticsWithItems>();
                foreach (var kvp in storeItemDict)
                {
                    var firstItem = storeItemStatistics.FirstOrDefault(s => s.StoreId == kvp.Key);
                    if (firstItem != null)
                    {
                        // 对品项列表排序
                        var sortedItemList = kvp.Value.OrderBy(x => x.ItemName).ToList();

                        storeList.Add(new StoreStatisticsWithItems
                        {
                            StoreId = firstItem.StoreId ?? "",
                            StoreName = firstItem.StoreName ?? "",
                            ItemList = sortedItemList
                        });
                    }
                }

                // 8. 按门店名称排序
                storeList = storeList.OrderBy(x => x.StoreName).ToList();

                // 9. 返回统计结果
                return new ActivityStatisticsByStoreItemOutput
                {
                    ActivityId = input.ActivityId,
                    ActivityName = activity.ActivityName,
                    StoreList = storeList
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取营销活动按门店品项统计数据失败: {ex.Message}");
            }
        }
        #endregion

    }
}