using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using NCC.ClayObject;
using NCC.Common.Configuration;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Common.Model.NPOI;
using NCC.DataEncryption;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqTkjlb;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_event;
using NCC.Extend.Entitys.lq_eventuser;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_tkjlb;
using NCC.Extend.Interfaces.LqTkjlb;
using NCC.FriendlyException;
using NCC.JsonSerialization;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;

namespace NCC.Extend.LqTkjlb
{
    /// <summary>
    /// 拓客管理服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤拓客管理服务", Name = "LqTkjlb", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqTkjlbService : ILqTkjlbService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqTkjlbEntity> _lqTkjlbRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

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

        #region 获取拓客管理
        /// <summary>
        /// 获取拓客管理
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            // 使用SqlFunc在查询时直接获取用户名和活动名称
            var result = await _db.Queryable<LqTkjlbEntity>()
                .Where(p => p.Id == id)
                .Select(p => new LqTkjlbInfoOutput
                {
                    id = p.Id,
                    expansionTime = p.ExpansionTime,
                    expansionUserId = p.ExpansionUserId,
                    expansionUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == p.ExpansionUserId).Select(u => u.RealName),
                    customerName = p.CustomerName,
                    customerPhone = p.CustomerPhone,
                    buyNumber = p.BuyNumber,
                    paymentMethod = p.PaymentMethod,
                    isAddWeChat = p.IsAddWeChat,
                    remarks = p.Remarks,
                    storeId = p.StoreId,
                    teamName = p.TeamName,
                    eventId = p.EventId,
                    eventName = SqlFunc.Subqueryable<LqEventEntity>().Where(e => e.Id == p.EventId).Select(e => e.EventName),
                    storeName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == p.StoreId).Select(u => u.Dm)
                })
                .FirstAsync();

            return result;
        }
        #endregion

        #region 获取拓客管理列表

        /// <summary>
        /// 获取拓客管理列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqTkjlbListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryTksj = input.expansionTime != null ? input.expansionTime.Split(',').ToObeject<List<string>>() : null;
            DateTime? startTksj = queryTksj != null ? Ext.GetDateTime(queryTksj.First()) : null;
            DateTime? endTksj = queryTksj != null ? Ext.GetDateTime(queryTksj.Last()) : null;
            var data = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(queryTksj != null, p => p.ExpansionTime >= new DateTime(startTksj.ToDate().Year, startTksj.ToDate().Month, startTksj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryTksj != null, p => p.ExpansionTime <= new DateTime(endTksj.ToDate().Year, endTksj.ToDate().Month, endTksj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.expansionUserId), p => p.ExpansionUserId.Equals(input.expansionUserId))
                .WhereIF(!string.IsNullOrEmpty(input.customerName), p => p.CustomerName.Contains(input.customerName))
                .WhereIF(!string.IsNullOrEmpty(input.customerPhone), p => p.CustomerPhone.Contains(input.customerPhone))
                .WhereIF(!string.IsNullOrEmpty(input.buyNumber), p => p.BuyNumber.Equals(input.buyNumber))
                .WhereIF(!string.IsNullOrEmpty(input.paymentMethod), p => p.PaymentMethod.Equals(input.paymentMethod))
                .WhereIF(!string.IsNullOrEmpty(input.isAddWeChat), p => p.IsAddWeChat.Equals(input.isAddWeChat))
                .WhereIF(!string.IsNullOrEmpty(input.remarks), p => p.Remarks.Contains(input.remarks))
                .WhereIF(!string.IsNullOrEmpty(input.storeId), p => p.StoreId.Contains(input.storeId))
                .WhereIF(!string.IsNullOrEmpty(input.teamName), p => p.TeamName.Contains(input.teamName))
                .WhereIF(!string.IsNullOrEmpty(input.eventId), p => p.EventId.Contains(input.eventId))
                .Select(it => new LqTkjlbListOutput
                {
                    id = it.Id,
                    expansionTime = it.ExpansionTime,
                    expansionUserId = it.ExpansionUserId,
                    expansionUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpansionUserId).Select(u => u.RealName),
                    customerName = it.CustomerName,
                    customerPhone = it.CustomerPhone,
                    buyNumber = it.BuyNumber,
                    paymentMethod = it.PaymentMethod,
                    isAddWeChat = it.IsAddWeChat,
                    remarks = it.Remarks,
                    storeId = it.StoreId,
                    storeName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == it.StoreId).Select(u => u.Dm),
                    teamName = it.TeamName,
                    eventId = it.EventId,
                    eventName = SqlFunc.Subqueryable<LqEventEntity>().Where(u => u.Id == it.EventId).Select(u => u.EventName),
                    depId = it.DepId,
                    depName = SqlFunc.Subqueryable<OrganizeEntity>().Where(u => u.Id == it.DepId).Select(u => u.FullName),
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqTkjlbListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 新建拓客管理
        /// <summary>
        /// 新建拓客管理
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task<dynamic> Create([FromBody] LqTkjlbCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            //通过input.dhhm去查询用户信息
            var user = _db.Queryable<LqKhxxEntity>().Where(u => u.Sjh == input.customerPhone).Any();
            if (user)
            {
                return NCCException.Oh("该手机号码已存在于会员或线索池中");
            }
            // 使用事务确保数据一致性
            var result = await _db.Ado.UseTranAsync(async () =>
            {
                //通过input.eventId去查询拓客活动信息
                var eventUserInfoList = await _db.Queryable<LqEventUserEntity>().Where(u => u.EventId == input.eventId && u.UserId == input.expansionUserId).ToListAsync();
                if (eventUserInfoList == null || eventUserInfoList.Count == 0)
                {
                    throw NCCException.Oh("未找到对应的拓客活动用户信息，请确认活动ID和用户ID是否正确");
                }
                var MemberNumber = "LQ" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                var MemberId = YitIdHelper.NextId().ToString();
                var eventUserInfo = eventUserInfoList.First();
                // 创建拓客记录
                var entity = input.Adapt<LqTkjlbEntity>();
                entity.Id = YitIdHelper.NextId().ToString();
                entity.TeamName = eventUserInfo.TeamName;
                entity.StoreId = eventUserInfo.StoreId;
                entity.DepId = eventUserInfo.DepId;
                entity.ExpansionTime = DateTime.Now;
                entity.MemberId = MemberId;
                var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
                if (!(isOk > 0))
                    throw NCCException.Oh("创建拓客记录失败");
                // 创建客户信息
                LqKhxxEntity MemberInfo = new LqKhxxEntity();
                MemberInfo.Id = MemberId;
                MemberInfo.Khmc = entity.CustomerName;
                MemberInfo.Sjh = input.customerPhone; // 设置手机号
                MemberInfo.Khlx = MemberTypeEnum.线索.GetHashCode().ToString();
                MemberInfo.Dah = MemberNumber;
                MemberInfo.Jdqd = "19.9卡";

                //找到input.expansionUserId的用户信息
                var userInfo = await _db.Queryable<UserEntity>().Where(u => u.Id == input.expansionUserId).FirstAsync();
                //判断 岗位是否为健康师，如果是健康师，则设置拓客人员是健康师
                MemberInfo.ExpandUser = input.expansionUserId;
                MemberInfo.Gsmd = eventUserInfo.StoreId;
                var memberResult = await _db.Insertable(MemberInfo).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
                if (!(memberResult > 0))
                {
                    throw NCCException.Oh("创建客户信息失败");
                }
                var storeinfo = await _db.Queryable<LqMdxxEntity>().Where(u => u.Id == eventUserInfo.StoreId).FirstAsync();
                return new { entity = entity, storeinfo = storeinfo };
            });

            if (!result.IsSuccess)
            {
                return NCCException.Oh($"创建拓客记录失败：{result.ErrorMessage}");
            }
            return result.Data;
        }
        #endregion

        #region 获取拓客管理无分页列表
        /// <summary>
        /// 获取拓客管理无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] LqTkjlbListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryExpansionTime = input.expansionTime != null ? input.expansionTime.Split(',').ToObeject<List<string>>() : null;
            DateTime? startExpansionTime = queryExpansionTime != null ? Ext.GetDateTime(queryExpansionTime.First()) : null;
            DateTime? endExpansionTime = queryExpansionTime != null ? Ext.GetDateTime(queryExpansionTime.Last()) : null;
            var data = await _db.Queryable<LqTkjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(
                    queryExpansionTime != null,
                    p => p.ExpansionTime >= new DateTime(startExpansionTime.ToDate().Year, startExpansionTime.ToDate().Month, startExpansionTime.ToDate().Day, 0, 0, 0)
                )
                .WhereIF(queryExpansionTime != null, p => p.ExpansionTime <= new DateTime(endExpansionTime.ToDate().Year, endExpansionTime.ToDate().Month, endExpansionTime.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.expansionUserId), p => p.ExpansionUserId.Equals(input.expansionUserId))
                .WhereIF(!string.IsNullOrEmpty(input.customerName), p => p.CustomerName.Contains(input.customerName))
                .WhereIF(!string.IsNullOrEmpty(input.customerPhone), p => p.CustomerPhone.Contains(input.customerPhone))
                .WhereIF(!string.IsNullOrEmpty(input.buyNumber), p => p.BuyNumber.ToString().Equals(input.buyNumber))
                .WhereIF(!string.IsNullOrEmpty(input.paymentMethod), p => p.PaymentMethod.Equals(input.paymentMethod))
                .WhereIF(!string.IsNullOrEmpty(input.isAddWeChat), p => p.IsAddWeChat.Equals(input.isAddWeChat))
                .WhereIF(!string.IsNullOrEmpty(input.remarks), p => p.Remarks.Contains(input.remarks))
                .WhereIF(!string.IsNullOrEmpty(input.storeId), p => p.StoreId.Contains(input.storeId))
                .WhereIF(!string.IsNullOrEmpty(input.teamName), p => p.TeamName.Contains(input.teamName))
                .WhereIF(!string.IsNullOrEmpty(input.eventId), p => p.EventId.Equals(input.eventId))
                .Select(it => new LqTkjlbListOutput
                {
                    id = it.Id,
                    expansionTime = it.ExpansionTime,
                    expansionUserId = it.ExpansionUserId,
                    expansionUserName = it.ExpansionUserId, // 这里需要根据实际业务逻辑获取用户名
                    customerName = it.CustomerName,
                    customerPhone = it.CustomerPhone,
                    buyNumber = it.BuyNumber,
                    paymentMethod = it.PaymentMethod,
                    isAddWeChat = it.IsAddWeChat,
                    remarks = it.Remarks,
                    storeId = it.StoreId,
                    teamName = it.TeamName,
                    eventId = it.EventId,
                    eventName = it.EventId, // 这里需要根据实际业务逻辑获取活动名称
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToListAsync();
            return data;
        }
        #endregion

        #region 获取拓客管理带中文名称的数据（简化版）
        /// <summary>
        /// 获取拓客管理带中文名称的数据（简化版，用于导出）
        /// </summary>
        /// <param name="eventId">活动编号（可选）</param>
        /// <param name="endTime">截止时间（可选）</param>
        /// <returns></returns>
        [NonAction]
        public async Task<List<TkjlbExportModel>> GetTkjlbWithChineseNamesSimple(string eventId = null, DateTime? endTime = null)
        {
            var data = await _db.Queryable<LqTkjlbEntity, LqMdxxEntity, LqEventEntity, UserEntity>(
                    (tk, md, ev, user) => tk.StoreId == md.Id && tk.EventId == ev.Id && tk.ExpansionUserId == user.Id
                )
                .WhereIF(!string.IsNullOrEmpty(eventId), (tk, md, ev, user) => tk.EventId == eventId)
                .WhereIF(endTime.HasValue, (tk, md, ev, user) => tk.ExpansionTime <= endTime.Value)
                .Select((tk, md, ev, user) => new TkjlbExportModel
                {
                    id = tk.Id,
                    expansionTime = tk.ExpansionTime,
                    expansionUserId = tk.ExpansionUserId,
                    expansionUserName = user.RealName,
                    customerName = tk.CustomerName,
                    customerPhone = tk.CustomerPhone,
                    buyNumber = tk.BuyNumber,
                    paymentMethod = tk.PaymentMethod,
                    isAddWeChat = tk.IsAddWeChat,
                    remarks = tk.Remarks,
                    storeId = tk.StoreId,
                    storeName = md.Dm,
                    teamName = tk.TeamName,
                    eventId = tk.EventId,
                    eventName = ev.EventName,
                })
                .MergeTable()
                .OrderBy("expansionTime desc")
                .ToListAsync();

            return data;
        }
        #endregion

        #region 导出拓客管理
        /// <summary>
        /// 导出拓客管理（固定字段，显示中文名称）
        /// </summary>
        /// <param name="eventId">活动编号（可选）</param>
        /// <param name="endTime">截止时间（可选）</param>
        /// <returns>导出文件信息</returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] string eventId = null, [FromQuery] DateTime? endTime = null)
        {
            try
            {
                var userInfo = await _userManager.GetUserInfo();

                // 使用联合查询获取带中文名称的数据
                var exportData = await GetTkjlbWithChineseNamesSimple(eventId, endTime);

                // 检查数据是否为空
                if (exportData == null || exportData.Count == 0)
                {
                    return new { name = "拓客管理.xls", url = "", message = "没有找到符合条件的数据" };
                }

                // 固定导出字段
                List<ParamsModel> paramList =
                    "[{\"value\":\"拓客编号\",\"field\":\"id\"},{\"value\":\"拓客时间\",\"field\":\"expansionTime\"},{\"value\":\"拓客人员ID\",\"field\":\"expansionUserId\"},{\"value\":\"拓客人员姓名\",\"field\":\"expansionUserName\"},{\"value\":\"顾客姓名\",\"field\":\"customerName\"},{\"value\":\"顾客电话\",\"field\":\"customerPhone\"},{\"value\":\"购买张数\",\"field\":\"buyNumber\"},{\"value\":\"支付方式\",\"field\":\"paymentMethod\"},{\"value\":\"是否加微信\",\"field\":\"isAddWeChat\"},{\"value\":\"备注\",\"field\":\"remarks\"},{\"value\":\"门店ID\",\"field\":\"storeId\"},{\"value\":\"门店名称\",\"field\":\"storeName\"},{\"value\":\"战队名称\",\"field\":\"teamName\"},{\"value\":\"活动ID\",\"field\":\"eventId\"},{\"value\":\"活动名称\",\"field\":\"eventName\"}]".ToList<ParamsModel>();

                // 检查paramList是否为空
                if (paramList == null || paramList.Count == 0)
                {
                    return new { name = "拓客管理.xls", url = "", message = "参数列表解析失败" };
                }

                ExcelConfig excelconfig = new ExcelConfig();
                excelconfig.FileName = "拓客管理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                excelconfig.HeadFont = "微软雅黑";
                excelconfig.HeadPoint = 10;
                excelconfig.IsAllSizeColumn = true;
                excelconfig.ColumnModel = new List<ExcelColumnModel>();

                // 固定字段列表
                List<string> selectKeyList = new List<string> { "id", "expansionTime", "expansionUserId", "expansionUserName", "customerName", "customerPhone", "buyNumber", "paymentMethod", "isAddWeChat", "remarks", "storeId", "storeName", "teamName", "eventId", "eventName" };

                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<TkjlbExportModel>.Export(exportData, excelconfig, addPath);
                var fileName = _userManager.UserId + "|" + excelconfig.FileName + "|temporary";
                var output = new { name = excelconfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC") };
                return output;
            }
            catch (Exception ex)
            {
                return new { name = "拓客管理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", url = "", message = $"导出失败: {ex.Message}", stackTrace = ex.StackTrace };
            }
        }
        #endregion

        #region 批量删除拓客管理

        /// <summary>
        /// 批量删除拓客管理
        /// </summary>
        /// <param name="ids">主键数组</param>
        /// <returns></returns>
        [HttpPost("batchRemove")]
        public async Task BatchRemove([FromBody] List<string> ids)
        {
            var entitys = await _db.Queryable<LqTkjlbEntity>().In(it => it.Id, ids).ToListAsync();
            if (entitys.Count > 0)
            {
                try
                {
                    //开启事务
                    _db.BeginTran();
                    //批量删除拓客管理
                    await _db.Deleteable<LqTkjlbEntity>().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] LqTkjlbUpInput input)
        {
            var entity = input.Adapt<LqTkjlbEntity>();
            var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            if (!(isOk > 0))
                throw NCCException.Oh(ErrorCode.COM1001);
        }
        #endregion

        #region 删除拓客管理
        /// <summary>
        /// 删除拓客管理
        /// </summary>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            var entity = await _db.Queryable<LqTkjlbEntity>().FirstAsync(p => p.Id == id);
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            var isOk = await _db.Deleteable<LqTkjlbEntity>().Where(d => d.Id == id).ExecuteCommandAsync();
            if (!(isOk > 0))
                throw NCCException.Oh(ErrorCode.COM1002);
        }
        #endregion

        #region 获取拓客排行榜
        /// <summary>
        /// 获取拓客排行榜
        /// </summary>
        /// <returns></returns>
        [HttpGet("Actions/GetRanking")]
        public async Task<dynamic> GetRanking()
        {
            var data = await _db.Queryable<LqTkjlbEntity>()
                .GroupBy(it => it.TeamName)
                .Select(it => new LqTkjlbRankingOutput { teamName = it.TeamName, expansionUserCount = SqlFunc.AggregateCount(it.Id) })
                .ToListAsync();

            // 在内存中排序并添加排名
            var sortedData = data.OrderByDescending(it => it.expansionUserCount).ToList();
            for (int i = 0; i < sortedData.Count; i++)
            {
                sortedData[i].ranking = i + 1;
            }

            return sortedData;
        }
        #endregion

        #region 获取战队人员详细报表
        /// <summary>
        /// 获取战队人员详细报表
        /// </summary>
        /// <returns></returns>
        [HttpGet("Actions/GetTeamDetail")]
        public async Task<dynamic> GetTeamDetail()
        {
            var result = new List<LqTkjlbTeamDetailOutput>();

            // 获取所有战队
            var teams = await _db.Queryable<LqTkjlbEntity>().GroupBy(it => it.TeamName).Select(it => it.TeamName).ToListAsync();

            foreach (var team in teams)
            {
                // 获取该战队所有人员的拓客数据
                var teamMembers = await _db.Queryable<LqTkjlbEntity>()
                    .Where(it => it.TeamName == team)
                    .GroupBy(it => it.ExpansionUserId)
                    .Select(it => new
                    {
                        // tkry = it.Tkry,
                        tkry = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpansionUserId).Select(u => u.RealName),
                        tkrs = SqlFunc.AggregateCount(it.Id),
                    })
                    .ToListAsync();

                // 按拓客数量排序
                var sortedMembers = teamMembers.OrderByDescending(m => m.tkrs).ToList();

                // 计算战队总拓客数量
                var totalCount = sortedMembers.Sum(m => m.tkrs);

                // 构建战队详情
                var teamDetail = new LqTkjlbTeamDetailOutput
                {
                    sszd = team,
                    totalCount = totalCount,
                    members = new List<TeamMemberDetail>(),
                };

                // 添加成员详情
                for (int i = 0; i < sortedMembers.Count; i++)
                {
                    var member = sortedMembers[i];
                    var percentage = totalCount > 0 ? (int)Math.Round((double)member.tkrs / totalCount * 100) : 0;

                    teamDetail.members.Add(
                        new TeamMemberDetail
                        {
                            tkry = member.tkry,
                            tkrs = member.tkrs,
                            teamRanking = i + 1,
                            percentage = percentage,
                        }
                    );
                }

                result.Add(teamDetail);
            }

            // 按战队总拓客数量排序
            return result.OrderByDescending(t => t.totalCount).ToList();
        }
        #endregion

        #region 漏斗统计
        /// <summary>
        /// 获取拓客活动漏斗统计数据（优化版本）
        /// </summary>
        /// <param name="eventId">活动ID</param>
        /// <returns>漏斗统计数据</returns>
        [HttpGet("GetFunnelStatistics/{eventId}")]
        public async Task<dynamic> GetFunnelStatistics(string eventId)
        {
            try
            {
                // 优化版本：使用子查询减少JOIN复杂度
                var sql = @"
                    SELECT 
                        md.F_Id as store_id,
                        md.dm as store_name,
                        tk_stats.tk_count,
                        tk_stats.yaoy_count,
                        tk_stats.yy_count,
                        tk_stats.hk_count,
                        tk_stats.kd_count,
                        tk_stats.hk_amount,
                        tk_stats.kd_amount,
                        CASE 
                            WHEN tk_stats.tk_count > 0 
                            THEN ROUND(tk_stats.yy_count * 100.0 / tk_stats.tk_count, 2)
                            ELSE 0 
                        END as yy_conversion_rate,
                        CASE 
                            WHEN tk_stats.yy_count > 0 
                            THEN ROUND(tk_stats.hk_count * 100.0 / tk_stats.yy_count, 2)
                            ELSE 0 
                        END as hk_conversion_rate
                    FROM lq_mdxx md
                    LEFT JOIN (
                        SELECT 
                            tk.F_StoreId,
                            COUNT(DISTINCT tk.F_Id) as tk_count,
                            COUNT(DISTINCT yy.F_Id) as yaoy_count,
                            COUNT(DISTINCT yyjl.F_Id) as yy_count,
                            COUNT(DISTINCT xh.F_Id) as hk_count,
                            COUNT(DISTINCT kd.F_Id) as kd_count,
                            COALESCE(SUM(xh.xfje), 0) as hk_amount,
                            COALESCE(SUM(kd.sfyj), 0) as kd_amount
                        FROM lq_tkjlb tk
                        LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                        LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                        LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                        LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                        WHERE tk.F_EventId = @eventId
                        GROUP BY tk.F_StoreId
                    ) tk_stats ON md.F_Id = tk_stats.F_StoreId
                    WHERE tk_stats.F_StoreId IS NOT NULL
                    ORDER BY tk_stats.tk_count DESC";

                var result = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { eventId });

                return new
                {
                    success = true,
                    data = result,
                    message = "获取漏斗统计数据成功"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh("获取漏斗统计数据失败：" + ex.Message);
            }
        }

        /// <summary>
        /// 获取拓客活动总体漏斗统计（优化版本）
        /// </summary>
        /// <param name="eventId">活动ID</param>
        /// <returns>总体漏斗统计数据</returns>
        [HttpGet("GetOverallFunnelStatistics/{eventId}")]
        public async Task<dynamic> GetOverallFunnelStatistics(string eventId)
        {
            try
            {
                // 第一步：获取拓客总数
                var tkSql = @"
                    SELECT COUNT(DISTINCT F_Id) as tk_count
                    FROM lq_tkjlb 
                    WHERE F_EventId = @eventId";

                var tkResult = await _db.Ado.SqlQueryAsync<dynamic>(tkSql, new { eventId });
                var tkCount = tkResult?.FirstOrDefault()?.tk_count ?? 0;

                // 第二步：获取邀约总数（按会员ID去重）
                var yaoySql = @"
                    SELECT COUNT(DISTINCT tk.F_MemberId) as yaoy_count
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                    WHERE tk.F_EventId = @eventId AND yy.F_Id IS NOT NULL";

                var yaoyResult = await _db.Ado.SqlQueryAsync<dynamic>(yaoySql, new { eventId });
                var yaoyCount = yaoyResult?.FirstOrDefault()?.yaoy_count ?? 0;

                // 第三步：获取预约总数（按会员ID去重）
                var yySql = @"
                    SELECT COUNT(DISTINCT tk.F_MemberId) as yy_count
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                    WHERE tk.F_EventId = @eventId AND yyjl.F_Id IS NOT NULL";

                var yyResult = await _db.Ado.SqlQueryAsync<dynamic>(yySql, new { eventId });
                var yyCount = yyResult?.FirstOrDefault()?.yy_count ?? 0;

                // 第四步：获取耗卡总数和金额（按会员ID去重，但金额不去重）
                var hkSql = @"
                    SELECT 
                        COUNT(DISTINCT tk.F_MemberId) as hk_count,
                        COALESCE(SUM(xh.xfje), 0) as hk_amount
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                    WHERE tk.F_EventId = @eventId AND xh.F_Id IS NOT NULL";

                var hkResult = await _db.Ado.SqlQueryAsync<dynamic>(hkSql, new { eventId });
                var hkCount = hkResult?.FirstOrDefault()?.hk_count ?? 0;
                var hkAmount = hkResult?.FirstOrDefault()?.hk_amount ?? 0m;

                // 第五步：获取开单总数和金额（按会员ID去重，但金额不去重）
                var kdSql = @"
                    SELECT 
                        COUNT(DISTINCT tk.F_MemberId) as kd_count,
                        COALESCE(SUM(kd.sfyj), 0) as kd_amount
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                    WHERE tk.F_EventId = @eventId AND kd.F_Id IS NOT NULL";

                var kdResult = await _db.Ado.SqlQueryAsync<dynamic>(kdSql, new { eventId });
                var kdCount = kdResult?.FirstOrDefault()?.kd_count ?? 0;
                var kdAmount = kdResult?.FirstOrDefault()?.kd_amount ?? 0m;

                // 计算转化率
                var yyConversionRate = tkCount > 0 ? Math.Round(yyCount * 100.0 / tkCount, 2) : 0;
                var hkConversionRate = yyCount > 0 ? Math.Round(hkCount * 100.0 / yyCount, 2) : 0;

                var result = new
                {
                    total_tk_count = tkCount,
                    total_yaoy_count = yaoyCount,
                    total_yy_count = yyCount,
                    total_hk_count = hkCount,
                    total_kd_count = kdCount,
                    total_hk_amount = hkAmount,
                    total_kd_amount = kdAmount,
                    overall_yy_conversion_rate = yyConversionRate,
                    overall_hk_conversion_rate = hkConversionRate
                };

                return new
                {
                    success = true,
                    data = result,
                    message = "获取总体漏斗统计数据成功（高性能版本）"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh("获取总体漏斗统计数据失败：" + ex.Message);
            }
        }

        /// <summary>
        /// 获取拓客活动漏斗统计数据（高性能版本 - 分步查询）
        /// </summary>
        /// <param name="eventId">活动ID</param>
        /// <returns>漏斗统计数据</returns>
        [HttpGet("GetFunnelStatisticsFast/{eventId}")]
        public async Task<dynamic> GetFunnelStatisticsFast(string eventId)
        {
            try
            {
                // 第一步：获取拓客基础数据
                var tkSql = @"
                    SELECT 
                        tk.F_StoreId,
                        md.dm as store_name,
                        COUNT(DISTINCT tk.F_Id) as tk_count
                    FROM lq_tkjlb tk
                    JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                    WHERE tk.F_EventId = @eventId
                    GROUP BY tk.F_StoreId, md.dm";

                var tkData = await _db.Ado.SqlQueryAsync<dynamic>(tkSql, new { eventId });
                var tkDict = tkData.ToDictionary(x => (string)x.F_StoreId, x => x);

                // 第二步：获取邀约数据（按会员ID去重）
                var yaoySql = @"
                    SELECT 
                        tk.F_StoreId,
                        COUNT(DISTINCT tk.F_MemberId) as yaoy_count
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                    WHERE tk.F_EventId = @eventId AND yy.F_Id IS NOT NULL
                    GROUP BY tk.F_StoreId";

                var yaoyData = await _db.Ado.SqlQueryAsync<dynamic>(yaoySql, new { eventId });
                var yaoyDict = yaoyData.ToDictionary(x => (string)x.F_StoreId, x => (int)x.yaoy_count);

                // 第三步：获取预约数据（按会员ID去重）
                var yySql = @"
                    SELECT 
                        tk.F_StoreId,
                        COUNT(DISTINCT tk.F_MemberId) as yy_count
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                    WHERE tk.F_EventId = @eventId AND yyjl.F_Id IS NOT NULL
                    GROUP BY tk.F_StoreId";

                var yyData = await _db.Ado.SqlQueryAsync<dynamic>(yySql, new { eventId });
                var yyDict = yyData.ToDictionary(x => (string)x.F_StoreId, x => (int)x.yy_count);

                // 第四步：获取耗卡数据（按会员ID去重，但金额不去重）
                var hkSql = @"
                    SELECT 
                        tk.F_StoreId,
                        COUNT(DISTINCT tk.F_MemberId) as hk_count,
                        COALESCE(SUM(xh.xfje), 0) as hk_amount
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                    WHERE tk.F_EventId = @eventId AND xh.F_Id IS NOT NULL
                    GROUP BY tk.F_StoreId";

                var hkData = await _db.Ado.SqlQueryAsync<dynamic>(hkSql, new { eventId });
                var hkDict = hkData.ToDictionary(x => (string)x.F_StoreId, x => new { count = (int)x.hk_count, amount = (decimal)x.hk_amount });

                // 第五步：获取开单数据（按会员ID去重，但金额不去重）
                var kdSql = @"
                    SELECT 
                        tk.F_StoreId,
                        COUNT(DISTINCT tk.F_MemberId) as kd_count,
                        COALESCE(SUM(kd.sfyj), 0) as kd_amount
                    FROM lq_tkjlb tk
                    LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                    WHERE tk.F_EventId = @eventId AND kd.F_Id IS NOT NULL
                    GROUP BY tk.F_StoreId";

                var kdData = await _db.Ado.SqlQueryAsync<dynamic>(kdSql, new { eventId });
                var kdDict = kdData.ToDictionary(x => (string)x.F_StoreId, x => new { count = (int)x.kd_count, amount = (decimal)x.kd_amount });

                // 合并结果
                var result = tkDict.Values.Select(tk => new
                {
                    store_id = tk.F_StoreId,
                    store_name = tk.store_name,
                    tk_count = (int)tk.tk_count,
                    yaoy_count = yaoyDict.ContainsKey(tk.F_StoreId) ? yaoyDict[tk.F_StoreId] : 0,
                    yy_count = yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0,
                    hk_count = hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].count : 0,
                    kd_count = kdDict.ContainsKey(tk.F_StoreId) ? kdDict[tk.F_StoreId].count : 0,
                    hk_amount = hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].amount : 0m,
                    kd_amount = kdDict.ContainsKey(tk.F_StoreId) ? kdDict[tk.F_StoreId].amount : 0m,
                    yy_conversion_rate = (int)tk.tk_count > 0 ? Math.Round((yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0) * 100.0 / (int)tk.tk_count, 2) : 0,
                    hk_conversion_rate = (yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0) > 0 ? Math.Round((hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].count : 0) * 100.0 / (yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0), 2) : 0
                }).OrderByDescending(x => x.tk_count).ToList();

                return new
                {
                    success = true,
                    data = result,
                    message = "获取漏斗统计数据成功（高性能版本）"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh("获取漏斗统计数据失败：" + ex.Message);
            }
        }
        #endregion

        #region 门店顾客详情
        /// <summary>
        /// 生成门店顾客详情查询SQL
        /// </summary>
        /// <param name="isPaged">是否分页</param>
        /// <returns>SQL语句</returns>
        private string GetStoreCustomerDetailsSql(bool isPaged = false)
        {
            var baseSql = @"
                SELECT 
                    tk.F_Id as tk_id,                                    -- 拓客记录ID
                    tk.F_CustomerPhone as customer_phone,                 -- 顾客手机号
                    tk.F_MemberId as member_id,                          -- 会员ID
                    tk.F_CustomerName as customer_name,                   -- 顾客姓名
                    tk.F_CreateTime as tk_time,                           -- 拓客时间
                    -- 邀约信息
                    yaoy.F_Id as yaoy_id,                                 -- 邀约ID
                    yaoy.F_CreateTime as yaoy_time,                        -- 邀约时间
                    yaoy.yysj as yaoy_appointment_time,                    -- 邀约时间
                    -- 预约信息
                    yy.F_Id as yy_id,                                     -- 预约ID
                    yy.F_Status as yy_status,                            -- 预约状态
                    yy.F_CreateTime as yy_time,                           -- 预约时间
                    yy.yysj as appointment_time,                         -- 预约到店时间
                    -- 耗卡信息（聚合）
                    xh_summary.total_consume_amount,                      -- 耗卡总金额
                    xh_summary.consume_count,                             -- 耗卡次数
                    xh_summary.first_consume_time,                        -- 首次耗卡时间
                    xh_summary.last_consume_time,                         -- 最后耗卡时间
                    -- 开卡信息（聚合）
                    kd_summary.total_billing_amount,                     -- 开卡总金额
                    kd_summary.total_debt_amount,                         -- 总欠款金额
                    kd_summary.billing_count,                             -- 开卡次数
                    kd_summary.first_billing_time,                        -- 首次开卡时间
                    kd_summary.last_billing_time,                         -- 最后开卡时间
                    -- 状态描述
                    CASE 
                        WHEN yaoy.F_Id IS NOT NULL THEN '已邀约'
                        ELSE '未邀约'
                    END as invitation_status,                             -- 邀约状态描述
                    CASE 
                        WHEN yy.F_Id IS NOT NULL THEN '已预约'
                        ELSE '未预约'
                    END as appointment_status,                           -- 预约状态描述
                    CASE 
                        WHEN xh_summary.total_consume_amount > 0 THEN '已耗卡'
                        ELSE '未耗卡'
                    END as consume_status,                               -- 耗卡状态描述
                    CASE 
                        WHEN kd_summary.total_billing_amount > 0 THEN '已开卡'
                        ELSE '未开卡'
                    END as billing_status                                 -- 开卡状态描述
                FROM lq_tkjlb tk
                LEFT JOIN lq_yaoyjl yaoy ON tk.F_MemberId = yaoy.yykh 
                    AND yaoy.F_StoreId = tk.F_StoreId
                LEFT JOIN lq_yyjl yy ON tk.F_MemberId = yy.gk 
                    AND yy.F_Status = '已确认'
                LEFT JOIN (
                    SELECT 
                        hy as member_id,
                        SUM(COALESCE(xfje, 0)) as total_consume_amount,
                        COUNT(*) as consume_count,
                        MIN(hksj) as first_consume_time,
                        MAX(hksj) as last_consume_time
                    FROM lq_xh_hyhk 
                    WHERE F_IsEffective = 1
                    GROUP BY hy
                ) xh_summary ON tk.F_MemberId = xh_summary.member_id
                LEFT JOIN (
                    SELECT 
                        kdhy as member_id,
                        SUM(COALESCE(sfyj, 0)) as total_billing_amount,
                        SUM(COALESCE(qk, 0)) as total_debt_amount,
                        COUNT(*) as billing_count,
                        MIN(kdrq) as first_billing_time,
                        MAX(kdrq) as last_billing_time
                    FROM lq_kd_kdjlb 
                    WHERE F_IsEffective = 1
                    GROUP BY kdhy
                ) kd_summary ON tk.F_MemberId = kd_summary.member_id
                WHERE tk.F_EventId = @eventId 
                    AND tk.F_StoreId = @storeId
                ORDER BY tk.F_CreateTime DESC";
            return isPaged ? baseSql + " LIMIT @offset, @pageSize" : baseSql;
        }

        /// <summary>
        /// 获取门店拓客活动顾客详情
        /// </summary>
        /// <param name="eventId">活动ID</param>
        /// <param name="storeId">门店ID</param>
        /// <returns>门店顾客详情列表</returns>
        [HttpGet("GetStoreCustomerDetails/{eventId}/{storeId}")]
        public async Task<dynamic> GetStoreCustomerDetails(string eventId, string storeId)
        {
            try
            {
                var sql = GetStoreCustomerDetailsSql(false);
                var result = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { eventId, storeId });

                return new
                {
                    success = true,
                    data = result,
                    message = "获取门店顾客详情成功"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh("获取门店顾客详情失败：" + ex.Message);
            }
        }

        /// <summary>
        /// 获取门店拓客活动顾客详情（分页）
        /// </summary>
        /// <param name="eventId">活动ID</param>
        /// <param name="storeId">门店ID</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <returns>分页的门店顾客详情列表</returns>
        [HttpGet("GetStoreCustomerDetailsPaged/{eventId}/{storeId}")]
        public async Task<dynamic> GetStoreCustomerDetailsPaged(string eventId, string storeId, int pageIndex = 1, int pageSize = 20)
        {
            try
            {
                var sql = GetStoreCustomerDetailsSql(true);
                var countSql = @"
                    SELECT COUNT(*) as total
                    FROM lq_tkjlb tk
                    WHERE tk.F_EventId = @eventId 
                        AND tk.F_StoreId = @storeId";
                var offset = (pageIndex - 1) * pageSize;
                var result = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { eventId, storeId, offset, pageSize });
                var totalResult = await _db.Ado.SqlQueryAsync<dynamic>(countSql, new { eventId, storeId });
                var total = totalResult?.FirstOrDefault()?.total ?? 0;

                return new
                {
                    success = true,
                    data = new
                    {
                        list = result,
                        total = total,
                        pageIndex = pageIndex,
                        pageSize = pageSize,
                        totalPages = (int)Math.Ceiling((double)total / pageSize)
                    },
                    message = "获取门店顾客详情成功"
                };
            }
            catch (Exception ex)
            {
                throw NCCException.Oh("获取门店顾客详情失败：" + ex.Message);
            }
        }
        #endregion

    }
}