using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MDL.Models;
using MDL.DTOs;
using DAL.Interfaces;
using DB.Database;
using SqlSugar;

namespace DAL.Repositories
{
    /// <summary>
    /// 标签替换请求的数据访问实现类
    /// </summary>
    public class LabelReplaceRepository : ILabelReplaceRepository
    {
        private readonly ISqlSugarProvider _provider;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="provider">SqlSugar数据库提供程序</param>
        public LabelReplaceRepository(ISqlSugarProvider provider)
        {
            _provider = provider;
        }

        /// <summary>
        /// 创建标签替换请求记录
        /// </summary>
        /// <param name="entity">标签替换请求实体</param>
        /// <returns>创建的记录ID</returns>
        public async Task<int> CreateAsync(LabelReplaceEntity entity)
        {
            var db = _provider.GetClient();

            // 确保表存在
            // 使用更安全的表初始化方式
            if (!db.DbMaintenance.IsAnyTable("label_replace_requests"))
            {
                db.CodeFirst.InitTables(typeof(LabelReplaceEntity));
            }

            // 设置时间戳
            entity.CreatedAt = DateTime.UtcNow;
            entity.UpdatedAt = DateTime.UtcNow;

            // 插入数据并返回自增ID
            return await db.Insertable(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        /// 根据ID获取标签替换请求记录
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns>标签替换请求实体</returns>
        public async Task<LabelReplaceEntity?> GetByIdAsync(int id)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelReplaceEntity>()
                .Where(x => x.Id == id)
                .FirstAsync();
        }

        /// <summary>
        /// 根据中性面单单号获取标签替换请求记录
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <returns>标签替换请求实体</returns>
        public async Task<LabelReplaceEntity?> GetByWaybillNumberAsync(string waybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelReplaceEntity>()
                .Where(x => x.NeutralWaybillNumber == waybillNumber)
                .FirstAsync();
        }

        /// <summary>
        /// 根据跟踪单号获取标签替换请求记录
        /// </summary>
        /// <param name="trackingNumber">跟踪单号</param>
        /// <returns>标签替换请求实体列表</returns>
        public async Task<List<LabelReplaceEntity>> GetByTrackingNumberAsync(string trackingNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelReplaceEntity>()
                .Where(x => x.FinalMileTrackingNumber == trackingNumber)
                .OrderByDescending(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 更新标签替换请求记录
        /// </summary>
        /// <param name="entity">标签替换请求实体</param>
        /// <returns>更新是否成功</returns>
        public async Task<bool> UpdateAsync(LabelReplaceEntity entity)
        {
            var db = _provider.GetClient();

            // 更新时间戳
            entity.UpdatedAt = DateTime.UtcNow;

            // 更新数据并返回影响行数
            var rows = await db.Updateable(entity).ExecuteCommandAsync();
            return rows > 0;
        }

        /// <summary>
        /// 删除标签替换请求记录
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns>删除是否成功</returns>
        public async Task<bool> DeleteAsync(int id)
        {
            var db = _provider.GetClient();
            var rows = await db.Deleteable<LabelReplaceEntity>()
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();
            return rows > 0;
        }

        /// <summary>
        /// 获取所有标签替换请求记录
        /// </summary>
        /// <returns>标签替换请求实体列表</returns>
        public async Task<List<LabelReplaceEntity>> GetAllAsync()
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelReplaceEntity>()
                .OrderByDescending(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 分页获取标签替换请求记录（包含客户信息）
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="sortBy">排序字段</param>
        /// <param name="sortOrder">排序顺序</param>
        /// <param name="billOfLadingNumber">提单号</param>
        /// <param name="masterPackageNumber">大包号</param>
        /// <param name="referenceNumber">参考号</param>
        /// <param name="neutralWaybillNumber">中性面单单号</param>
        /// <param name="finalMileTrackingNumber">尾程跟踪单号</param>
        /// <param name="replaceStatus">换单状态</param>
        /// <param name="customerId">客户ID</param>
        /// <param name="startCreatedAt">创建时间开始</param>
        /// <param name="endCreatedAt">创建时间结束</param>
        /// <param name="startReplacedAt">换单时间开始</param>
        /// <param name="endReplacedAt">换单时间结束</param>
        /// <returns>包含客户信息的标签替换请求DTO列表和总记录数</returns>
        public async Task<(List<MDL.DTOs.LabelReplaceWithCustomerDto>, int)> GetByPageAsync(int page, int pageSize, string sortBy, string sortOrder, string billOfLadingNumber, string masterPackageNumber, string referenceNumber, string neutralWaybillNumber, string finalMileTrackingNumber, string replaceStatus, int? customerId, string startCreatedAt, string endCreatedAt, string startReplacedAt, string endReplacedAt)
        {
            var db = _provider.GetClient();

            // 解析时间筛选参数
            DateTime? parsedStartCreatedAt = null;
            DateTime? parsedEndCreatedAt = null;
            DateTime? parsedStartReplacedAt = null;
            DateTime? parsedEndReplacedAt = null;

            if (!string.IsNullOrEmpty(startCreatedAt) && DateTime.TryParse(startCreatedAt, out DateTime startCreatedAtDate))
            {
                parsedStartCreatedAt = startCreatedAtDate;
            }
            if (!string.IsNullOrEmpty(endCreatedAt) && DateTime.TryParse(endCreatedAt, out DateTime endCreatedAtDate))
            {
                parsedEndCreatedAt = endCreatedAtDate;
            }
            if (!string.IsNullOrEmpty(startReplacedAt) && DateTime.TryParse(startReplacedAt, out DateTime startReplacedAtDate))
            {
                parsedStartReplacedAt = startReplacedAtDate;
            }
            if (!string.IsNullOrEmpty(endReplacedAt) && DateTime.TryParse(endReplacedAt, out DateTime endReplacedAtDate))
            {
                parsedEndReplacedAt = endReplacedAtDate;
            }

            // 先创建基础查询
            var baseQuery = db.Queryable<LabelReplaceEntity>();

            // 应用基础筛选条件
            if (!string.IsNullOrEmpty(billOfLadingNumber))
            {
                baseQuery = baseQuery.Where(l => l.BillOfLadingNumber.Contains(billOfLadingNumber));
            }
            if (!string.IsNullOrEmpty(masterPackageNumber))
            {
                baseQuery = baseQuery.Where(l => l.MasterPackageNumber.Contains(masterPackageNumber));
            }
            if (!string.IsNullOrEmpty(referenceNumber))
            {
                baseQuery = baseQuery.Where(l => l.ReferenceNumber.Contains(referenceNumber));
            }
            if (!string.IsNullOrEmpty(neutralWaybillNumber))
            {
                // 处理多个单号的情况，支持逗号分隔
                var waybillNumbers = neutralWaybillNumber.Split(new[] { ',', '\n', '\r', ' ' }, StringSplitOptions.RemoveEmptyEntries);
                if (waybillNumbers.Length > 1)
                {
                    baseQuery = baseQuery.Where(l => waybillNumbers.Contains(l.NeutralWaybillNumber));
                }
                else if (waybillNumbers.Length == 1)
                {
                    baseQuery = baseQuery.Where(l => l.NeutralWaybillNumber.Contains(waybillNumbers[0]));
                }
            }
            if (!string.IsNullOrEmpty(finalMileTrackingNumber))
            {
                baseQuery = baseQuery.Where(l => l.FinalMileTrackingNumber.Contains(finalMileTrackingNumber));
            }
            if (!string.IsNullOrEmpty(replaceStatus))
            {
                baseQuery = baseQuery.Where(l => l.ReplaceStatus == replaceStatus);
            }
            if (customerId.HasValue)
            {
                baseQuery = baseQuery.Where(l => l.CustomerId == customerId);
            }
            
            // 只应用 CreatedAt 筛选条件，ReplacedAt 稍后在内存中筛选
            if (parsedStartCreatedAt.HasValue)
            {
                baseQuery = baseQuery.Where(l => l.CreatedAt >= parsedStartCreatedAt.Value);
            }
            if (parsedEndCreatedAt.HasValue)
            {
                baseQuery = baseQuery.Where(l => l.CreatedAt <= parsedEndCreatedAt.Value);
            }

            // 先获取所有符合基础条件的记录（不分页）
            var allLabelReplaceEntities = await baseQuery.ToListAsync();

            // 如果没有数据，直接返回
            if (allLabelReplaceEntities.Count == 0)
            {
                return (new List<MDL.DTOs.LabelReplaceWithCustomerDto>(), 0);
            }

            // 提取所有中性面单单号，用于批量查询最新扫描记录
            var scanWaybillNumbers = allLabelReplaceEntities.Select(l => l.NeutralWaybillNumber).Distinct().ToList();

            // 批量查询每个中性面单单号的最新扫描成功记录
            var latestScans = new List<MDL.Models.LabelScanEntity>();
            if (scanWaybillNumbers.Count > 0)
            {
                // 先获取所有符合条件的扫描记录
                var allScans = await db.Queryable<MDL.Models.LabelScanEntity>()
                    .Where(s => scanWaybillNumbers.Contains(s.NeutralWaybillNumber) && s.Result == 0)
                    .OrderByDescending(s => s.CreatedAt)
                    .ToListAsync();

                // 按中性面单单号分组，取每组的第一条记录（最新的）
                var groupedScans = allScans.GroupBy(s => s.NeutralWaybillNumber);
                foreach (var group in groupedScans)
                {
                    latestScans.Add(group.First());
                }
            }

            // 创建中性面单单号到最新扫描时间的映射，确保类型正确
            var latestScanMap = new Dictionary<string, DateTime>();
            foreach (var scan in latestScans)
            {
                latestScanMap[scan.NeutralWaybillNumber] = scan.CreatedAt;
            }

            // 根据 ReplacedAt 筛选记录
            var filteredEntities = new List<LabelReplaceEntity>();
            foreach (var entity in allLabelReplaceEntities)
            {
                bool shouldInclude = true;
                
                // 获取此订单的ReplacedAt时间
                DateTime? replacedAt = null;
                if (latestScanMap.TryGetValue(entity.NeutralWaybillNumber, out var latestCreatedAt))
                {
                    replacedAt = latestCreatedAt;
                }

                // 应用 ReplacedAt 筛选
                if (parsedStartReplacedAt.HasValue)
                {
                    if (!replacedAt.HasValue || replacedAt.Value < parsedStartReplacedAt.Value)
                    {
                        shouldInclude = false;
                    }
                }
                if (parsedEndReplacedAt.HasValue)
                {
                    if (!replacedAt.HasValue || replacedAt.Value > parsedEndReplacedAt.Value)
                    {
                        shouldInclude = false;
                    }
                }

                if (shouldInclude)
                {
                    filteredEntities.Add(entity);
                }
            }

            // 计算筛选后的总记录数
            int totalCount = filteredEntities.Count;

            // 对筛选后的实体进行排序
            if (!string.IsNullOrEmpty(sortBy))
            {
                if (sortOrder.ToLower() == "asc")
                {
                    // 根据常用排序字段排序
                    filteredEntities = sortBy switch
                    {
                        "CreatedAt" => filteredEntities.OrderBy(e => e.CreatedAt).ToList(),
                        "UpdatedAt" => filteredEntities.OrderBy(e => e.UpdatedAt).ToList(),
                        "Id" => filteredEntities.OrderBy(e => e.Id).ToList(),
                        _ => filteredEntities.OrderBy(e => e.CreatedAt).ToList()
                    };
                }
                else
                {
                    filteredEntities = sortBy switch
                    {
                        "CreatedAt" => filteredEntities.OrderByDescending(e => e.CreatedAt).ToList(),
                        "UpdatedAt" => filteredEntities.OrderByDescending(e => e.UpdatedAt).ToList(),
                        "Id" => filteredEntities.OrderByDescending(e => e.Id).ToList(),
                        _ => filteredEntities.OrderByDescending(e => e.CreatedAt).ToList()
                    };
                }
            }
            else
            {
                filteredEntities = filteredEntities.OrderByDescending(e => e.CreatedAt).ToList();
            }

            // 应用分页
            var pagedEntities = filteredEntities
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToList();

            // 如果分页后没有数据，直接返回
            if (pagedEntities.Count == 0)
            {
                return (new List<MDL.DTOs.LabelReplaceWithCustomerDto>(), totalCount);
            }

            // 提取所有非null的客户ID，用于批量查询
            var customerIds = pagedEntities.Where(l => l.CustomerId.HasValue).Select(l => l.CustomerId.Value).Distinct().ToList();

            // 批量查询客户信息
            var customerList = await db.Queryable<MDL.Models.CustomerEntity>()
                .Where(c => customerIds.Contains(c.Id))
                .ToListAsync();

            // 手动构建字典，确保类型正确
            Dictionary<int, string> customers = new Dictionary<int, string>();
            foreach (var customer in customerList)
            {
                customers[customer.Id] = customer.CustomerCode;
            }

            // 构建DTO列表
            var dtos = new List<MDL.DTOs.LabelReplaceWithCustomerDto>();
            foreach (var l in pagedEntities)
            {
                var dto = new MDL.DTOs.LabelReplaceWithCustomerDto
                {
                    Id = l.Id,
                    BillOfLadingNumber = l.BillOfLadingNumber,
                    MasterPackageNumber = l.MasterPackageNumber,
                    ReferenceNumber = l.ReferenceNumber,
                    NeutralWaybillNumber = l.NeutralWaybillNumber,
                    FinalMileTrackingNumber = l.FinalMileTrackingNumber,
                    Label = l.Label,
                    HasLabel = !string.IsNullOrEmpty(l.Label),
                    ReplaceStatus = l.ReplaceStatus,
                    LabelRetrievedAt = l.LabelRetrievedAt,
                    CreatedAt = l.CreatedAt,
                    UpdatedAt = l.UpdatedAt
                };

                // 处理客户代码
                if (l.CustomerId.HasValue)
                {
                    if (customers.TryGetValue(l.CustomerId.Value, out var customerCode))
                    {
                        dto.CustomerCode = customerCode;
                    }
                }

                // 处理扫描时间
                if (latestScanMap.TryGetValue(l.NeutralWaybillNumber, out var latestCreatedAt))
                {
                    dto.ReplacedAt = latestCreatedAt;
                }

                dtos.Add(dto);
            }

            return (dtos, totalCount);
        }

        /// <summary>
        /// 批量查询换单状态
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <param name="waybillNumbers">中性面单单号列表</param>
        /// <param name="trackingNumbers">尾程跟踪单号列表</param>
        /// <returns>标签替换请求实体列表</returns>
        public async Task<(List<LabelReplaceEntity>, Dictionary<string, DateTime>)> GetLabelReplaceStatusAsync(int customerId, List<string> waybillNumbers, List<string> trackingNumbers)
        {
            var db = _provider.GetClient();

            // 构建查询条件
            var query = db.Queryable<LabelReplaceEntity>()
                .Where(x => x.CustomerId == customerId);

            // 添加单号查询条件
            if (waybillNumbers != null && waybillNumbers.Count > 0)
            {
                query = query.Where(x => waybillNumbers.Contains(x.NeutralWaybillNumber));
            }

            if (trackingNumbers != null && trackingNumbers.Count > 0)
            {
                query = query.Where(x => x.FinalMileTrackingNumber != null && trackingNumbers.Contains(x.FinalMileTrackingNumber));
            }

            // 执行查询
            var labelReplaceEntities = await query.ToListAsync();

            // 提取所有中性面单单号，用于批量查询最新扫描记录
            var scanWaybillNumbers = labelReplaceEntities.Select(l => l.NeutralWaybillNumber).Distinct().ToList();

            // 批量查询每个中性面单单号的最新扫描成功记录
            var latestScanMap = new Dictionary<string, DateTime>();
            if (scanWaybillNumbers.Count > 0)
            {
                // 先获取所有符合条件的扫描记录
                var allScans = await db.Queryable<MDL.Models.LabelScanEntity>()
                    .Where(s => scanWaybillNumbers.Contains(s.NeutralWaybillNumber) && s.Result == 0)
                    .OrderByDescending(s => s.CreatedAt)
                    .ToListAsync();

                // 按中性面单单号分组，取每组的第一条记录（最新的）
                var groupedScans = allScans.GroupBy(s => s.NeutralWaybillNumber);
                foreach (var group in groupedScans)
                {
                    var latestScan = group.First();
                    latestScanMap[latestScan.NeutralWaybillNumber] = latestScan.CreatedAt;
                }
            }

            return (labelReplaceEntities, latestScanMap);
        }

        /// <summary>
        /// 根据交接单号列表批量获取标签替换请求记录
        /// </summary>
        /// <param name="handoverNumbers">交接单号列表</param>
        /// <param name="customerId">客户ID</param>
        /// <returns>标签替换请求实体列表</returns>
        public async Task<List<LabelReplaceEntity>> GetByHandoverNumbersAsync(List<string> handoverNumbers, int? customerId)
        {
            var db = _provider.GetClient();

            if (handoverNumbers == null || handoverNumbers.Count == 0)
                return new List<LabelReplaceEntity>();

            var query = db.Queryable<LabelReplaceEntity>()
                .Where(x => handoverNumbers.Contains(x.BillOfLadingNumber) || handoverNumbers.Contains(x.MasterPackageNumber));

            if (customerId.HasValue)
            {
                query = query.Where(x => x.CustomerId == customerId.Value);
            }

            return await query.ToListAsync();
        }

        /// <summary>
        /// 将base64编码转换为PDF文件并保存
        /// </summary>
        /// <param name="base64Content">base64编码的PDF内容</param>
        /// <param name="fileName">文件名</param>
        /// <returns>保存的文件路径</returns>
        public async Task<string> ConvertBase64ToPdfAsync(string base64Content, string fileName)
        {
            // 定义保存目录
            string saveDirectory = @"C:\TESYSTEM\OMS\api-lable\pdf\20260302";

            // 确保目录存在
            if (!System.IO.Directory.Exists(saveDirectory))
            {
                System.IO.Directory.CreateDirectory(saveDirectory);
            }

            // 构建完整的文件路径
            string filePath = System.IO.Path.Combine(saveDirectory, fileName);

            // 处理base64内容，移除可能的前缀
            if (base64Content.StartsWith("data:application/pdf;base64,"))
            {
                base64Content = base64Content.Substring("data:application/pdf;base64,".Length);
            }

            // 转换base64为字节数组
            byte[] pdfBytes = Convert.FromBase64String(base64Content);

            // 保存到文件
            await System.IO.File.WriteAllBytesAsync(filePath, pdfBytes);

            return filePath;
        }

        /// <summary>
        /// 更新Label字段但保持LabelRetrievedAt不变
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="newLabel">新的Label值</param>
        /// <returns>更新是否成功</returns>
        public async Task<bool> UpdateLabelWithoutChangingRetrievedAtAsync(int id, string newLabel)
        {
            var db = _provider.GetClient();

            // 首先获取现有记录，以便获取LabelRetrievedAt的当前值
            var existing = await db.Queryable<LabelReplaceEntity>()
                .Where(x => x.Id == id)
                .FirstAsync();

            if (existing == null)
            {
                return false;
            }

            // 显式更新Label字段，同时将LabelRetrievedAt设置为原值，
            // 这样触发器就不会改变它
            var rows = await db.Updateable<LabelReplaceEntity>()
                .SetColumns(x => x.Label == newLabel)
                .SetColumns(x => x.LabelRetrievedAt == existing.LabelRetrievedAt)
                .SetColumns(x => x.UpdatedAt == DateTime.UtcNow)
                .Where(x => x.Id == id)
                .ExecuteCommandAsync();

            return rows > 0;
        }

        /// <summary>
        /// 获取每日标签统计数据
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <param name="customerId">客户ID</param>
        /// <returns>每日标签统计数据列表</returns>
        public async Task<List<DailyLabelStatsDto>> GetDailyLabelStatsAsync(string startDate, string endDate, int? customerId)
        {
            var db = _provider.GetClient();

            // 构建日期范围条件
            DateTime? start = null;
            DateTime? end = null;

            if (!string.IsNullOrEmpty(startDate))
            {
                start = DateTime.Parse(startDate);
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                end = DateTime.Parse(endDate).AddDays(1).AddSeconds(-1);
            }

            // 获取所有符合条件的 label_replace_requests（label不为空）
            var labelRequestsQuery = db.Queryable<LabelReplaceEntity>()
                .Where(x => x.Label != null && x.Label != "");

            if (customerId.HasValue)
            {
                labelRequestsQuery = labelRequestsQuery.Where(x => x.CustomerId == customerId);
            }

            var labelRequests = await labelRequestsQuery.ToListAsync();
            var waybillNumbers = labelRequests.Select(x => x.NeutralWaybillNumber).Distinct().ToList();

            if (waybillNumbers.Count == 0)
            {
                return new List<DailyLabelStatsDto>();
            }

            // 获取所有相关的扫描记录
            var scansQuery = db.Queryable<LabelScanEntity>()
                .Where(x => waybillNumbers.Contains(x.NeutralWaybillNumber));

            if (customerId.HasValue)
            {
                scansQuery = scansQuery.Where(x => x.CustomerId == customerId);
            }

            var allScans = await scansQuery.OrderBy(x => x.CreatedAt).ToListAsync();

            // 构建统计字典
            var statsDict = new Dictionary<string, DailyLabelStatsDto>();

            // 获取数据拉取时间（当前UTC时间减5小时）
            var dataFetchTime = DateTime.UtcNow.AddHours(-5);

            // 1. 统计换单失败未完结数（所有有扫描记录但未能成功换单的数量）
            // 对于每个中性面单单号，检查是否有Result = 0的记录，如果没有就算失败未完结
            var waybillScanGroups = allScans.GroupBy(x => x.NeutralWaybillNumber);
            var unfinishedFailureSet = new HashSet<string>();

            foreach (var group in waybillScanGroups)
            {
                var hasSuccess = group.Any(x => x.Result == 0);
                if (!hasSuccess)
                {
                    unfinishedFailureSet.Add(group.Key);
                }
            }

            // 2. 按日期统计各项指标
            foreach (var scan in allScans)
            {
                // 转换为UTC-5时区的日期
                var dateKey = scan.CreatedAt.AddHours(-5).ToString("yyyy-MM-dd");

                // 应用日期范围过滤
                if (start.HasValue && scan.CreatedAt.AddHours(-5) < start.Value)
                    continue;
                if (end.HasValue && scan.CreatedAt.AddHours(-5) > end.Value)
                    continue;

                if (!statsDict.ContainsKey(dateKey))
                {
                    statsDict[dateKey] = new DailyLabelStatsDto
                    {
                        Date = dateKey,
                        DataFetchTime = dataFetchTime
                    };
                }

                var stats = statsDict[dateKey];

                // 当日扫描数
                stats.DailyScanCount++;

                // 当日换单成功数（Result = 0）
                if (scan.Result == 0)
                {
                    stats.DailySuccessCount++;

                    // 当日STOP数（Result = 0且描述包含"成功返回STOP标签"）
                    if (!string.IsNullOrEmpty(scan.Description) && scan.Description.Contains("成功返回STOP标签"))
                    {
                        stats.DailyStopCount++;
                    }
                }
                else
                {
                    // 当日换单失败数（Result != 0）
                    stats.DailyFailureCount++;
                }
            }

            // 3. 统计当日标签推送数（按LabelRetrievedAt统计）
            foreach (var request in labelRequests)
            {
                if (request.LabelRetrievedAt.HasValue)
                {
                    var dateKey = request.LabelRetrievedAt.Value.AddHours(-5).ToString("yyyy-MM-dd");

                    // 应用日期范围过滤
                    if (start.HasValue && request.LabelRetrievedAt.Value.AddHours(-5) < start.Value)
                        continue;
                    if (end.HasValue && request.LabelRetrievedAt.Value.AddHours(-5) > end.Value)
                        continue;

                    if (!statsDict.ContainsKey(dateKey))
                    {
                        statsDict[dateKey] = new DailyLabelStatsDto
                        {
                            Date = dateKey,
                            DataFetchTime = dataFetchTime
                        };
                    }

                    statsDict[dateKey].DailyLabelPushCount++;
                }
            }

            // 4. 为每个日期设置换单失败未完结数（这是一个全局统计，适用于所有日期）
            foreach (var stats in statsDict.Values)
            {
                stats.UnfinishedFailureCount = unfinishedFailureSet.Count;
            }

            // 转换为列表并按日期排序
            var result = statsDict.Values.OrderBy(x => x.Date).ToList();

            return result;
        }

        /// <summary>
        /// 获取每日标签统计数据（中文版本，使用自定义SQL）
        /// </summary>
        /// <returns>每日标签统计数据列表</returns>
        public async Task<List<DailyLabelStatsChineseDto>> GetDailyLabelStatsChineseAsync()
        {
            // 使用固定的数据库连接
            string customConnectionString = "server=172.233.222.200;port=6033;user id=oms_user;password=oms_user@pwd;database=lr01mainusa;CharSet=utf8;allow zero datetime=true;Convert Zero Datetime=true;Max Pool Size=100;Min Pool Size=10;Connection Timeout=30;Allow User Variables=True;";

            // 直接使用 MySqlConnection 来执行 SQL，绕过 SqlSugar 初始化问题
            var result = new List<DailyLabelStatsChineseDto>();
            
            using (var connection = new MySql.Data.MySqlClient.MySqlConnection(customConnectionString))
            {
                await connection.OpenAsync();
                
                // 读取 SQL 文件内容
                string sql = @"
WITH 
-- 步骤1：获取所有到货交接单，日期已是UTC-5
ArrivalFormsWithDate AS (
    SELECT 
        a.Id,
        a.HandoverNumber,
        DATE(a.ReceiptTime) AS 到货日期,
        a.ReceiptTime AS 到货时间
    FROM arrival_handover_forms a
),

-- 步骤2：计算交接单级别的标签率（该交接单内所有有标签的订单 / 总订单数）
InterchangeUnitLabelRates AS (
    SELECT 
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        COUNT(DISTINCT l.Id) AS total_requests,
        -- 计算冻结标签率：交接单中有标签的包裹数 / 总包裹数
        -- 注意：这里不再比较扫描时间，而是直接统计是否有标签
        -- 处理逻辑：
        --   1. 如果交接单中有标签的包裹占比 >= 80% → 按高标签率处理
        --   2. 如果没有最早扫描时间（还未开始作业）：标签仍然是可用的，用交接单的标签率判断
        --   3. 标签率的定义：交接单中有标签的包裹数 / 总包裹数
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL AND l.Label != '' 
            THEN l.Id 
        END) AS labeled_requests,
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL AND l.Label != '' 
                THEN l.Id 
            END) * 100.0 / COUNT(DISTINCT l.Id), 
            2
        ) AS label_rate_percent
    FROM label_replace_requests l
    GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
),

-- 步骤2.5：计算交接单的作业时标签率（基于最早扫描时间）
InterchangeUnitLabelRatesAtFirstScan AS (
    SELECT 
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        COUNT(DISTINCT l.Id) AS total_requests,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL AND l.Label != '' 
            THEN l.Id 
        END) AS total_labeled_at_any_time,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL 
                 AND l.Label != '' 
                 AND l.LabelRetrievedAt < (
                     SELECT MIN(lsh2.CreatedAt)
                     FROM label_scan_history lsh2
                     WHERE lsh2.NeutralWaybillNumber = l.NeutralWaybillNumber
                 )
            THEN l.Id 
        END) AS labeled_at_first_scan,
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL 
                     AND l.Label != '' 
                     AND l.LabelRetrievedAt < (
                         SELECT MIN(lsh3.CreatedAt)
                         FROM label_scan_history lsh3
                         WHERE lsh3.NeutralWaybillNumber = l.NeutralWaybillNumber
                     )
                THEN l.Id 
            END) * 100.0 / 
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL AND l.Label != '' 
                THEN l.Id 
            END),
            2
        ) AS label_rate_at_first_scan
    FROM label_replace_requests l
    GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
),

-- 步骤3：关联到货交接单与换单请求（只取Label有值的），并根据标签率计算考核时间
ArrivalRequests AS (
    SELECT 
        a.到货日期,
        a.到货时间,
        l.Id AS RequestId,
        l.NeutralWaybillNumber,
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        l.Label,
        l.LabelRetrievedAt,
        l.CustomerId,
        COALESCE(iulr.label_rate_percent, 0) AS 标签率,
        COALESCE(iulr_first.label_rate_at_first_scan, 0) AS 作业时标签率,
        -- 考核时间逻辑：根据作业时标签率（最早扫描之前的标签率）和到仓时间判断
        -- 关键：使用作业时标签率而不是现在的标签率，因为决策是在作业开始时做的
        CASE 
            WHEN COALESCE(iulr_first.label_rate_at_first_scan, 0) >= 80 THEN
                -- 作业时标签率>=80%：根据到仓时间确定考核时间
                CASE
                    WHEN HOUR(a.到货时间) < 16 THEN 
                        -- 16点前到仓：考核时间 = 当日16点 ~ 次日16点
                        CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 16:00:00')
                    ELSE 
                        -- 16点后到仓：考核时间 = 当日16点 ~ 次日23:59:59
                        CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 23:59:59')
                END
            ELSE
                -- 作业时标签率<80%：考核时间为NULL，完成即达标
                NULL
        END AS 考核时间
    FROM ArrivalFormsWithDate a
    INNER JOIN label_replace_requests l 
        ON l.BillOfLadingNumber = a.HandoverNumber 
        OR l.MasterPackageNumber = a.HandoverNumber
    LEFT JOIN InterchangeUnitLabelRates iulr 
        ON (l.BillOfLadingNumber = iulr.BillOfLadingNumber OR l.BillOfLadingNumber IS NULL)
        AND (l.MasterPackageNumber = iulr.MasterPackageNumber OR l.MasterPackageNumber IS NULL)
    LEFT JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
        ON (l.BillOfLadingNumber = iulr_first.BillOfLadingNumber OR l.BillOfLadingNumber IS NULL)
        AND (l.MasterPackageNumber = iulr_first.MasterPackageNumber OR l.MasterPackageNumber IS NULL)
    WHERE l.Label IS NOT NULL AND l.Label != ''
),

-- 步骤4：获取每个订单的扫描记录情况（按天）
DailyScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 当日是否成功,
        MAX(CASE WHEN s.Result != 0 THEN 1 ELSE 0 END) AS 当日是否失败
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ''
    GROUP BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
),

-- 步骤5：获取每个订单是否曾经成功，以及首次成功日期和时间
OverallScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
        MIN(CASE WHEN s.Result = 0 THEN DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ELSE NULL END) AS 首次成功日期,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
),

-- 步骤6：从数据中收集所有日期
AllDates AS (
    SELECT 到货日期 AS 日期 FROM ArrivalRequests
    UNION
    SELECT 日期 FROM DailyScanStatus
    UNION
    SELECT DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS 日期
    FROM label_replace_requests l
    WHERE l.LabelRetrievedAt IS NOT NULL
),

-- 步骤7：去重并排序日期
DistinctDates AS (
    SELECT DISTINCT 日期
    FROM AllDates
    ORDER BY 日期
),

-- 步骤8：获取最新日期
LatestDate AS (
    SELECT MAX(日期) AS 日期
    FROM DistinctDates
),

-- 步骤9：每日基础统计 - 当日新增换单数、16点分段统计
DailyBase AS (
    SELECT 
        dd.日期,
        -- 当日新增换单数：当天到货并且推送了标签数据的订单
        COUNT(DISTINCT CASE 
            WHEN ar.到货日期 = dd.日期 AND ar.LabelRetrievedAt IS NOT NULL 
            THEN ar.RequestId 
        END) AS 当日新增换单数,
        -- 当天标签推送数
        COUNT(DISTINCT CASE 
            WHEN DATE(CONVERT_TZ(ar.LabelRetrievedAt, '+00:00', '-05:00')) = dd.日期 
            THEN ar.RequestId 
        END) AS 当日标签推送数,
        -- 新增：16点前到仓的包裹数
        COUNT(DISTINCT CASE 
            WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) < 16
            THEN ar.RequestId 
        END) AS 16点前到仓包裹数,
        -- 新增：16点后到仓的包裹数
        COUNT(DISTINCT CASE 
            WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) >= 16
            THEN ar.RequestId 
        END) AS 16点后到仓包裹数
    FROM DistinctDates dd
    CROSS JOIN ArrivalRequests ar
    GROUP BY dd.日期
),

-- 步骤9：每日扫描统计（扫描次数），换单成功数去重
DailyScanMetrics AS (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
        COUNT(*) AS 当日扫描数,
        -- 当日STOP数（同样去重）
        COUNT(DISTINCT CASE WHEN s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%' THEN s.NeutralWaybillNumber END) AS 当日STOP数
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ''
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
),

-- 步骤9b：每日换单成功数（按首次成功日期分组，必须是有标签的订单）
DailySuccessCount AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日换单成功数
    FROM OverallScanStatus oss
    INNER JOIN label_replace_requests l ON oss.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE oss.曾成功 = 1 AND oss.首次成功时间 IS NOT NULL
        AND l.Label IS NOT NULL AND l.Label != ''
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
),

-- 步骤10：历史日期的换单失败未完结统计（非最新日期）
HistoryUnfinished AS (
    SELECT 
        dd.日期,
        COUNT(DISTINCT CASE
            -- 当天失败并且当天没有成功的订单
            WHEN dss.日期 = dd.日期 AND dss.当日是否失败 = 1 AND dss.当日是否成功 = 0
            THEN dss.NeutralWaybillNumber
        END) AS 换单失败未完结订单
    FROM DistinctDates dd
    LEFT JOIN DailyScanStatus dss ON dd.日期 = dss.日期
    CROSS JOIN LatestDate ld
    WHERE dd.日期 != ld.日期
    GROUP BY dd.日期
),

-- 步骤11：最新日期的换单失败未完结统计（所有历史从未成功的）
LatestUnfinished AS (
    SELECT 
        ld.日期,
        COUNT(DISTINCT CASE
            -- 必须同时满足：
            -- 1. 有过扫描记录（OverallScanStatus中有该订单）
            -- 2. 从未成功（曾成功 = 0）
            -- 3. 并且至少有一次失败记录
            WHEN oss.曾成功 = 0
            THEN ar.NeutralWaybillNumber
        END) AS 换单失败未完结订单
    FROM LatestDate ld
    CROSS JOIN ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    GROUP BY ld.日期
),

-- 步骤12：每日换单失败订单统计（当天失败并且当天没成功的订单数）
DailyFailedOrders AS (
    SELECT 
        日期,
        COUNT(DISTINCT CASE
            WHEN 当日是否失败 = 1 AND 当日是否成功 = 0
            THEN NeutralWaybillNumber
        END) AS 当日换单失败
    FROM DailyScanStatus
    GROUP BY 日期
),

-- 步骤13：每日完成订单统计 - 订单必须在我们关联的ArrivalRequests中
DailyCompletedOrders AS (
    SELECT 
        oss.首次成功日期 AS 日期,
        COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日完成数
    FROM OverallScanStatus oss
    INNER JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
    WHERE oss.曾成功 = 1
    GROUP BY oss.首次成功日期
),

-- 步骤14：24小时换单完成订单统计 - 根据新的考核时间判断
-- 关键：完成时间 <= 考核时间 的包裹视为24小时内完成
-- 步骤14.4：24H内完成数统计（低标签率：完成即达标）
DailyLowLabelRate24HCompleted AS (
    SELECT 
        ar.到货日期 AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 低标签率24H完成数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 低标签率订单：考核时间为NULL（完成即达标）
        AND ar.考核时间 IS NULL
    GROUP BY ar.到货日期
),

Daily24HCompletedOrders AS (
    SELECT 
        ar.到货日期 AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 24H内完成数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 根据标签率判断：
        -- 标签率>=80%：使用ar.考核时间进行比较
        -- 标签率<80%：使用oss.首次成功时间作为考核时间（即完成即达标）
        AND (
            -- 情况1：标签率>=80%，有固定考核时间
            (ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
            OR
            -- 情况2：标签率<80%，完成时间本身就是考核时间，直接算达标
            (ar.考核时间 IS NULL)
        )
    GROUP BY ar.到货日期
),

-- 步骤14.5：16点前到仓考核通过包裹数统计（按到货日期分组，仅包括标签率>=80%的订单）
-- 改为按到货日期分组，使分子和分母维度一致
DailyBeforeNoonPassed AS (
    SELECT 
        DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 16点前到仓
        AND HOUR(ar.到货时间) < 16
        -- 仅统计标签率>=80%的订单（这些订单有固定的考核时间）
        AND ar.考核时间 IS NOT NULL
        -- 完成时间 <= 考核时间（在24小时内完成）
        AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间
    GROUP BY DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00'))
),

-- 步骤14.6：16点后到仓考核通过包裹数统计（按到货日期分组，仅包括标签率>=80%的订单）
-- 改为按到货日期分组，使分子和分母维度一致
DailyAfternoonPassed AS (
    SELECT 
        DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 16点后到仓
        AND HOUR(ar.到货时间) >= 16
        -- 仅统计标签率>=80%的订单（这些订单有固定的考核时间）
        AND ar.考核时间 IS NOT NULL
        -- 完成时间 <= 考核时间（在24小时内完成）
        AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间
    GROUP BY DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00'))
),

-- 步骤14.6b：高标签率考核通过数汇总（16点前+16点后）
DailyHighLabelRateAssessed AS (
    SELECT 
        日期,
        SUM(16点前考核通过包裹数) + SUM(16点后考核通过包裹数) AS 高标签率考核通过数
    FROM (
        SELECT 日期, 16点前考核通过包裹数, 0 AS 16点后考核通过包裹数 FROM DailyBeforeNoonPassed
        UNION ALL
        SELECT 日期, 0 AS 16点前考核通过包裹数, 16点后考核通过包裹数 FROM DailyAfternoonPassed
    ) t
    GROUP BY 日期
),

-- 步骤14.7：标签率<80%考核通过包裹数统计（按首次成功日期分组）
-- 标签率<80%的订单，完成即达标，不受到货时间和考核时间的影响
DailyLowLabelRatePassed AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 低标签率考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 仅统计标签率<80%的订单（这些订单考核时间为NULL）
        AND ar.考核时间 IS NULL
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
),

DailyHighLabelRateShould AS (
    SELECT 
        DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 高标签率应该换单数
    FROM ArrivalRequests ar
    INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
        ON ar.BillOfLadingNumber = iulr_first.BillOfLadingNumber
        AND ar.MasterPackageNumber = iulr_first.MasterPackageNumber
    WHERE ar.到货日期 IS NOT NULL
        AND iulr_first.label_rate_at_first_scan >= 80
    GROUP BY DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00'))
),

-- 步骤14.9：标签率<80%应该换单数统计（按到货日期分组）
-- 统计冻结标签率 < 80% 的交接单中的所有包裹数
-- 这些包裹将按照完成即达标的规则处理
DailyLowLabelRateShould AS (
    SELECT 
        DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00')) AS 日期,
        -- 修改：用 COUNT(DISTINCT BillOfLadingNumber, MasterPackageNumber) 的组合
        -- 原因：ArrivalRequests可能有相同NeutralWaybillNumber的多条记录导致重复计算
        COUNT(DISTINCT CONCAT(ar.BillOfLadingNumber, '|', ar.MasterPackageNumber)) AS 低标签率应该换单数
    FROM ArrivalRequests ar
    INNER JOIN (
        -- 找出所有冻结标签率 < 80% 的交接单
        SELECT DISTINCT 
            BillOfLadingNumber,
            MasterPackageNumber
        FROM InterchangeUnitLabelRates
        WHERE label_rate_percent < 80
    ) low_label_units ON ar.BillOfLadingNumber = low_label_units.BillOfLadingNumber
                      AND ar.MasterPackageNumber = low_label_units.MasterPackageNumber
    GROUP BY DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00'))
),

-- 步骤15：完整的每日统计基础 - 准备每日的新增和完成，并获取前一日数据
DailyStatsWithPrev AS (
    SELECT 
        db.日期,
        db.当日新增换单数,
        db.当日标签推送数,
        db.16点前到仓包裹数,
        db.16点后到仓包裹数,
        COALESCE(dco.当日完成数, 0) AS 当日完成数,
        COALESCE(dc24h.24H内完成数, 0) AS 24H内完成数,
        -- 获取前一日的新增
        LAG(db.当日新增换单数, 1, 0) OVER (ORDER BY db.日期) AS 前一日新增,
        -- 获取前一日的完成数
        LAG(COALESCE(dco.当日完成数, 0), 1, 0) OVER (ORDER BY db.日期) AS 前一日完成数,
        -- 行号
        ROW_NUMBER() OVER (ORDER BY db.日期) AS rn
    FROM DailyBase db
    LEFT JOIN DailyCompletedOrders dco ON db.日期 = dco.日期
    LEFT JOIN Daily24HCompletedOrders dc24h ON db.日期 = dc24h.日期
    ORDER BY db.日期
)

-- 步骤16：计算累计数据并最终输出
SELECT 
    日期,
    MAX(当日新增换单数) AS 当日新增换单数,
    MAX(16点前到仓包裹数) AS 16点前到仓包裹数,
    MAX(16点后到仓包裹数) AS 16点后到仓包裹数,
    MAX(累计要换的总单数) AS 累计要换的总单数,
    MAX(当天应该换单数) AS 当天应该换单数,
    MAX(换单失败未完结订单) AS 换单失败未完结订单,
    MAX(当日换单失败) AS 当日换单失败,
    MAX(当日换单成功数) AS 当日换单成功数,
    MAX(当日STOP数) AS 当日STOP数,
    MAX(24H内完成数) AS 24H内完成数,
    MAX(当日完成数) AS 当日完成数,
    MAX(当日标签推送数) AS 当日标签推送数,
    MAX(当日扫描数) AS 当日扫描数,
    MAX(数据拉取时间（UTC_5）) AS 数据拉取时间（UTC_5）,
    CASE 
        WHEN MAX(当天应该换单数) = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(
            MAX(当日完成数) / MAX(当天应该换单数) * 100, 2), '%')
    END AS 当天换单完成率,
    CASE 
        WHEN MAX(高标签率应该换单数) = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(
            MAX(高标签率考核通过数) / MAX(高标签率应该换单数) * 100, 2), '%')
    END AS 24H换单率
FROM (
    SELECT 
        t.日期,
        t.当日新增换单数,
        t.16点前到仓包裹数,
        t.16点后到仓包裹数,
        -- 使用变量保持状态，每次计算前一天的累计
        -- 公式：累计 = MAX(0, 前一日累计 + 前一日新增 - 前一日完成)
        -- 第1天直接用当日新增
        @running_total := GREATEST(0, 
            CASE 
                WHEN t.rn = 1 THEN t.当日新增换单数
                ELSE @running_total + t.前一日新增 - t.前一日完成数
            END) AS 累计要换的总单数,
        -- 当天应该换单数 = 累计要换的总单数 + 当日新增换单数
        @当天应该换单数 := @running_total + t.当日新增换单数 AS 当天应该换单数,
        -- 根据是否是最新日期选择不同的未完结统计
        COALESCE(
            CASE 
                WHEN t.日期 = (SELECT 日期 FROM LatestDate) THEN lu.换单失败未完结订单
                ELSE hu.换单失败未完结订单
            END, 
            0
        ) AS 换单失败未完结订单,
        COALESCE(dfo.当日换单失败, 0) AS 当日换单失败,
        COALESCE(dsc.当日换单成功数, 0) AS 当日换单成功数,
        COALESCE(dsm.当日STOP数, 0) AS 当日STOP数,
        t.24H内完成数,
        t.当日完成数,
        t.当日标签推送数,
        COALESCE(dsm.当日扫描数, 0) AS 当日扫描数,
        (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）,
        -- 16点前考核通过的包裹数（16点前到仓 + 完成时间<=考核时间）
        COALESCE(dbc.16点前考核通过包裹数, 0) AS 16点前考核通过包裹数,
        -- 16点后考核通过的包裹数（16点后到仓 + 完成时间<=考核时间）
        COALESCE(dac.16点后考核通过包裹数, 0) AS 16点后考核通过包裹数,
        -- 低标签率考核通过的包裹数（标签率<80% + 曾成功）
        COALESCE(dllrp.低标签率考核通过包裹数, 0) AS 低标签率考核通过包裹数,
        -- 低标签率24H完成数（标签率<80% 完成即达标的24H完成）
        COALESCE(dllr24h.低标签率24H完成数, 0) AS 低标签率24H完成数,
        -- 高标签率考核通过数（16点前+16点后）
        COALESCE(dhras.高标签率考核通过数, 0) AS 高标签率考核通过数,
        -- 标签率≥80%应该换单数
        COALESCE(dhlrs.高标签率应该换单数, 0) AS 高标签率应该换单数,
        -- 标签率<80%应该换单数
        COALESCE(dllrs.低标签率应该换单数, 0) AS 低标签率应该换单数
    FROM DailyStatsWithPrev t
    LEFT JOIN DailyScanMetrics dsm ON t.日期 = dsm.日期
    LEFT JOIN DailySuccessCount dsc ON t.日期 = dsc.日期
    LEFT JOIN HistoryUnfinished hu ON t.日期 = hu.日期
    LEFT JOIN LatestUnfinished lu ON t.日期 = lu.日期
    LEFT JOIN DailyFailedOrders dfo ON t.日期 = dfo.日期
    LEFT JOIN DailyBeforeNoonPassed dbc ON t.日期 = dbc.日期
    LEFT JOIN DailyAfternoonPassed dac ON t.日期 = dac.日期
    LEFT JOIN DailyLowLabelRatePassed dllrp ON t.日期 = dllrp.日期
    LEFT JOIN DailyLowLabelRate24HCompleted dllr24h ON t.日期 = dllr24h.日期
    LEFT JOIN DailyHighLabelRateAssessed dhras ON t.日期 = dhras.日期
    LEFT JOIN DailyHighLabelRateShould dhlrs ON t.日期 = dhlrs.日期
    LEFT JOIN DailyLowLabelRateShould dllrs ON t.日期 = dllrs.日期
    -- 初始化变量
    CROSS JOIN (SELECT @running_total := 0, @当天应该换单数 := 0) AS init
    ORDER BY t.日期
) AS subquery
-- 修复：加GROUP BY确保每个日期只有一行返回，避免JOIN导致的笛卡尔积
GROUP BY 日期
ORDER BY 日期 DESC
";
                
                using (var command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection))
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var dto = new DailyLabelStatsChineseDto
                        {
                            Date = reader["日期"] != DBNull.Value ? Convert.ToDateTime(reader["日期"]).ToString("yyyy-MM-dd") : string.Empty,
                            DailyNewReplaceCount = reader["当日新增换单数"] != DBNull.Value ? Convert.ToInt32(reader["当日新增换单数"]) : 0,
                            CumulativeTotalReplaceCount = reader["累计要换的总单数"] != DBNull.Value ? Convert.ToInt32(reader["累计要换的总单数"]) : 0,
                            ShouldReplaceCount = reader["当天应该换单数"] != DBNull.Value ? Convert.ToInt32(reader["当天应该换单数"]) : 0,
                            UnfinishedFailureCount = reader["换单失败未完结订单"] != DBNull.Value ? Convert.ToInt32(reader["换单失败未完结订单"]) : 0,
                            DailyFailureCount = reader["当日换单失败"] != DBNull.Value ? Convert.ToInt32(reader["当日换单失败"]) : 0,
                            DailySuccessCount = reader["当日换单成功数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单成功数"]) : 0,
                            DailyStopCount = reader["当日STOP数"] != DBNull.Value ? Convert.ToInt32(reader["当日STOP数"]) : 0,
                            BeforeNoonArrivedCount = reader["16点前到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点前到仓包裹数"]) : 0,
                            AfternoonArrivedCount = reader["16点后到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点后到仓包裹数"]) : 0,
                            BeforeNoonPassedCount = reader["16点前考核通过包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点前考核通过包裹数"]) : 0,
                            AfternoonPassedCount = reader["16点后考核通过包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点后考核通过包裹数"]) : 0,
                            Rate24Hour = reader["24H换单率"] as string,
                            DailyCompletionRate = reader["当天换单完成率"] as string,
                            DailyLabelPushCount = reader["当日标签推送数"] != DBNull.Value ? Convert.ToInt32(reader["当日标签推送数"]) : 0,
                            DailyScanCount = reader["当日扫描数"] != DBNull.Value ? Convert.ToInt32(reader["当日扫描数"]) : 0,
                            DataFetchTime = reader["数据拉取时间（UTC_5）"] != DBNull.Value ? Convert.ToDateTime(reader["数据拉取时间（UTC_5）"]) : DateTime.Now
                        };
                        result.Add(dto);
                    }
                }
            }
            
            return result;
        }

        /// <summary>
        /// 获取订单表中有标签的所有订单
        /// </summary>
        /// <param name="limit">限制返回的最大数量</param>
        /// <returns>有标签的订单列表</returns>
        public async Task<List<LabelReplaceEntity>> GetAllOrdersWithLabelsAsync(int limit = 1000)
        {
            var db = _provider.GetClient();
            
            return await db.Queryable<LabelReplaceEntity>()
                .Where(lr => !string.IsNullOrEmpty(lr.Label))
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取指定日期范围内有标签的订单
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <param name="limit">限制返回的最大数量</param>
        /// <returns>有标签的订单列表</returns>
        public async Task<List<LabelReplaceEntity>> GetOrdersWithLabelsByDateRangeAsync(DateTime startDate, DateTime endDate, int limit = 1000)
        {
            var db = _provider.GetClient();
            
            return await db.Queryable<LabelReplaceEntity>()
                .Where(lr => !string.IsNullOrEmpty(lr.Label) 
                    && lr.CreatedAt >= startDate 
                    && lr.CreatedAt <= endDate)
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取指定客户有标签的订单
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <param name="limit">限制返回的最大数量</param>
        /// <returns>有标签的订单列表</returns>
        public async Task<List<LabelReplaceEntity>> GetOrdersWithLabelsByCustomerAsync(int customerId, int limit = 1000)
        {
            var db = _provider.GetClient();
            
            return await db.Queryable<LabelReplaceEntity>()
                .Where(lr => !string.IsNullOrEmpty(lr.Label) 
                    && lr.CustomerId == customerId)
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取客户维度每日标签换单统计数据
        /// </summary>
        /// <returns>客户维度每日统计数据列表</returns>
        public async Task<List<CustomerDailyLabelStatsDto>> GetCustomerDailyLabelStatsAsync()
        {
            string customConnectionString = "server=172.233.222.200;port=6033;user id=oms_user;password=oms_user@pwd;database=lr01mainusa;CharSet=utf8;allow zero datetime=true;Convert Zero Datetime=true;Max Pool Size=100;Min Pool Size=10;Connection Timeout=30;Allow User Variables=True;";

            var result = new List<CustomerDailyLabelStatsDto>();
            
            using (var connection = new MySql.Data.MySqlClient.MySqlConnection(customConnectionString))
            {
                await connection.OpenAsync();
                
                string sql = @"
WITH 
-- 步骤1：获取所有到货交接单，日期已是UTC-5
ArrivalFormsWithDate AS (
    SELECT 
        a.Id,
        a.HandoverNumber,
        DATE(a.ReceiptTime) AS 到货日期,
        a.ReceiptTime AS 到货时间
    FROM arrival_handover_forms a
),

-- 步骤2：计算客户级别的标签率（所有有标签的订单 / 总订单数）
CustomerLabelRates AS (
    SELECT 
        l.CustomerId,
        COUNT(DISTINCT l.Id) AS total_requests,
        COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN l.Id END) AS labeled_requests,
        ROUND(
            COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN l.Id END) * 100.0 / 
            COUNT(DISTINCT l.Id), 
            2
        ) AS label_rate_percent
    FROM label_replace_requests l
    GROUP BY l.CustomerId
),

-- 步骤3：关联到货交接单与换单请求（只取Label有值的），并根据标签率计算新的考核时间
ArrivalRequests AS (
    SELECT 
        a.到货日期,
        a.到货时间,
        l.Id AS RequestId,
        l.NeutralWaybillNumber,
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        l.Label,
        l.LabelRetrievedAt,
        l.CustomerId,
        COALESCE(clr.label_rate_percent, 0) AS 客户标签率,
        CASE 
            WHEN COALESCE(clr.label_rate_percent, 0) >= 80 THEN
                CASE
                    WHEN HOUR(a.到货时间) < 16 THEN 
                        CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 16:00:00')
                    ELSE 
                        CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 23:59:59')
                END
            ELSE
                NULL
        END AS 考核时间
    FROM ArrivalFormsWithDate a
    INNER JOIN label_replace_requests l 
        ON l.BillOfLadingNumber = a.HandoverNumber 
        OR l.MasterPackageNumber = a.HandoverNumber
    LEFT JOIN CustomerLabelRates clr ON l.CustomerId = clr.CustomerId
    WHERE l.Label IS NOT NULL AND l.Label != ''
),

-- 步骤4：获取每个订单的扫描记录情况（按天）
DailyScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 当日是否成功,
        MAX(CASE WHEN s.Result != 0 THEN 1 ELSE 0 END) AS 当日是否失败
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ''
    GROUP BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
),

-- 步骤5：获取每个订单是否曾经成功，以及首次成功日期和时间
OverallScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
        MIN(CASE WHEN s.Result = 0 THEN DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ELSE NULL END) AS 首次成功日期,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
),

-- 步骤6：按客户日期分组的到仓分布
CustomerDailyArrival AS (
    SELECT 
        ar.CustomerId,
        ar.到货日期 AS 日期,
        COUNT(DISTINCT CASE WHEN HOUR(ar.到货时间) < 16 THEN ar.RequestId END) AS 16点前到仓包裹数,
        COUNT(DISTINCT CASE WHEN HOUR(ar.到货时间) >= 16 THEN ar.RequestId END) AS 16点后到仓包裹数
    FROM ArrivalRequests ar
    GROUP BY ar.CustomerId, ar.到货日期
),

-- 步骤7：按客户日期的16点前考核通过统计
CustomerDailyBeforeNoonPassed AS (
    SELECT 
        ar.CustomerId,
        ar.到货日期 AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        AND HOUR(ar.到货时间) < 16
        AND (
            (ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
            OR (ar.考核时间 IS NULL)
        )
    GROUP BY ar.CustomerId, ar.到货日期
),

-- 步骤8：按客户日期的16点后考核通过统计
CustomerDailyAfternoonPassed AS (
    SELECT 
        ar.CustomerId,
        ar.到货日期 AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        AND HOUR(ar.到货时间) >= 16
        AND (
            (ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
            OR (ar.考核时间 IS NULL)
        )
    GROUP BY ar.CustomerId, ar.到货日期
),

-- 步骤9：按客户日期的换单完成统计
CustomerDailyCompletion AS (
    SELECT 
        ar.CustomerId,
        ar.到货日期 AS 日期,
        COUNT(DISTINCT CASE 
            WHEN ar.到货日期 = ar.到货日期 AND ar.LabelRetrievedAt IS NOT NULL 
            THEN ar.RequestId 
        END) AS 当日新增换单数,
        COUNT(DISTINCT CASE 
            WHEN oss.曾成功 = 1
            THEN ar.NeutralWaybillNumber 
        END) AS 当日完成数,
        COUNT(DISTINCT CASE 
            WHEN dss.当日是否成功 = 1 AND dss.当日是否失败 = 0
            THEN ar.NeutralWaybillNumber 
        END) AS 当日换单成功数,
        COUNT(DISTINCT CASE 
            WHEN dss.当日是否失败 = 1 AND dss.当日是否成功 = 0
            THEN ar.NeutralWaybillNumber 
        END) AS 当日换单失败数
    FROM ArrivalRequests ar
    LEFT JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    LEFT JOIN DailyScanStatus dss ON ar.NeutralWaybillNumber = dss.NeutralWaybillNumber AND ar.到货日期 = dss.日期
    GROUP BY ar.CustomerId, ar.到货日期
),

-- 步骤10：最终聚合数据
FinalCustomerStats AS (
    SELECT 
        cda.CustomerId,
        cl.label_rate_percent,
        cda.日期,
        cda.16点前到仓包裹数,
        cda.16点后到仓包裹数,
        COALESCE(cdbnp.16点前考核通过包裹数, 0) AS 16点前考核通过包裹数,
        COALESCE(cdanp.16点后考核通过包裹数, 0) AS 16点后考核通过包裹数,
        COALESCE(cdc.当日新增换单数, 0) AS 当日新增换单数,
        COALESCE(cdc.当日完成数, 0) AS 当日完成数,
        COALESCE(cdc.当日换单成功数, 0) AS 当日换单成功数,
        COALESCE(cdc.当日换单失败数, 0) AS 当日换单失败数,
        cl.total_requests,
        cl.labeled_requests
    FROM CustomerDailyArrival cda
    LEFT JOIN CustomerLabelRates cl ON cda.CustomerId = cl.CustomerId
    LEFT JOIN CustomerDailyBeforeNoonPassed cdbnp ON cda.CustomerId = cdbnp.CustomerId AND cda.日期 = cdbnp.日期
    LEFT JOIN CustomerDailyAfternoonPassed cdanp ON cda.CustomerId = cdanp.CustomerId AND cda.日期 = cdanp.日期
    LEFT JOIN CustomerDailyCompletion cdc ON cda.CustomerId = cdc.CustomerId AND cda.日期 = cdc.日期
)

SELECT 
     fcs.CustomerId,
     c.CustomerCode,
     DATE_FORMAT(fcs.日期, '%Y-%m-%d') AS 日期,
    CONCAT(ROUND(fcs.label_rate_percent, 2), '%') AS 客户标签率,
    fcs.total_requests AS 总订单数,
    fcs.labeled_requests AS 有标签订单数,
    fcs.16点前到仓包裹数,
    fcs.16点后到仓包裹数,
    fcs.16点前考核通过包裹数,
    fcs.16点后考核通过包裹数,
    fcs.当日新增换单数,
    fcs.当日换单成功数,
    fcs.当日换单失败数,
    fcs.当日完成数,
    CASE 
        WHEN fcs.当日新增换单数 = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(fcs.当日完成数 / fcs.当日新增换单数 * 100, 2), '%')
    END AS 当天换单完成率,
    CASE 
        WHEN fcs.当日新增换单数 = 0 THEN '0.00%'
        ELSE CONCAT(ROUND((fcs.16点前考核通过包裹数 + fcs.16点后考核通过包裹数) / fcs.当日新增换单数 * 100, 2), '%')
    END AS 24H换单率,
    (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间
FROM FinalCustomerStats fcs
 LEFT JOIN customers c ON fcs.CustomerId = c.id
 ORDER BY fcs.日期 DESC, fcs.CustomerId
";

                using (var command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection))
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var dto = new CustomerDailyLabelStatsDto
                        {
                            CustomerId = reader["CustomerId"] != DBNull.Value ? Convert.ToInt32(reader["CustomerId"]) : 0,
                            CustomerCode = reader["CustomerCode"] as string ?? string.Empty,
                            Date = reader["日期"] != DBNull.Value ? Convert.ToDateTime(reader["日期"]).ToString("yyyy-MM-dd") : string.Empty,
                            CustomerLabelRate = reader["客户标签率"] as string ?? "0.00%",
                            TotalRequests = reader["总订单数"] != DBNull.Value ? Convert.ToInt32(reader["总订单数"]) : 0,
                            LabeledRequests = reader["有标签订单数"] != DBNull.Value ? Convert.ToInt32(reader["有标签订单数"]) : 0,
                            BeforeNoonArrivedCount = reader["16点前到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点前到仓包裹数"]) : 0,
                            AfternoonArrivedCount = reader["16点后到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点后到仓包裹数"]) : 0,
                            BeforeNoonPassedCount = reader["16点前考核通过包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点前考核通过包裹数"]) : 0,
                            AfternoonPassedCount = reader["16点后考核通过包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点后考核通过包裹数"]) : 0,
                            DailyNewReplaceCount = reader["当日新增换单数"] != DBNull.Value ? Convert.ToInt32(reader["当日新增换单数"]) : 0,
                            DailySuccessCount = reader["当日换单成功数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单成功数"]) : 0,
                            DailyFailureCount = reader["当日换单失败数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单失败数"]) : 0,
                            DailyCompletedCount = reader["当日完成数"] != DBNull.Value ? Convert.ToInt32(reader["当日完成数"]) : 0,
                            DailyCompletionRate = reader["当天换单完成率"] as string ?? "0.00%",
                            Rate24Hour = reader["24H换单率"] as string ?? "0.00%",
                            DataFetchTime = reader["数据拉取时间"] != DBNull.Value ? Convert.ToDateTime(reader["数据拉取时间"]) : DateTime.Now
                        };
                        result.Add(dto);
                    }
                }
            }
            
            return result;
        }

        /// <summary>
        /// 获取定时任务新订单（仅2026-05-10之后的订单）
        /// </summary>
        /// <param name="limit">限制返回的最大数量</param>
        /// <returns>有标签的新订单列表（仅>=2026-05-10）</returns>
        public async Task<List<string>> GetNewOrdersWithLabelsForBackgroundTaskAsync(int limit)
        {
            var db = _provider.GetClient();
            var cutoffDate = new DateTime(2026, 5, 10, 0, 0, 0);
            
            return await db.Queryable<LabelReplaceEntity>()
                .Where(o => !string.IsNullOrEmpty(o.Label))
                .Where(o => o.CreatedAt >= cutoffDate)
                .Where(o => !SqlFunc.Subqueryable<LabelPdfCache>()
                    .Where(c => c.NeutralWaybillNumber == o.NeutralWaybillNumber)
                    .Any())
                .Select(o => o.NeutralWaybillNumber)
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取指定交接单号对应的所有订单
        /// </summary>
        /// <param name="handoverNumber">交接单号</param>
        /// <returns>订单列表</returns>
        public async Task<List<LabelReplaceEntity>> GetOrdersByHandoverNumberAsync(string handoverNumber)
        {
            var db = _provider.GetClient();

            if (string.IsNullOrWhiteSpace(handoverNumber))
                return new List<LabelReplaceEntity>();

            return await db.Queryable<LabelReplaceEntity>()
                .Where(x => x.BillOfLadingNumber == handoverNumber || x.MasterPackageNumber == handoverNumber)
                .OrderByDescending(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 获取指定交接单号列表对应的所有订单
        /// </summary>
        /// <param name="handoverNumbers">交接单号列表</param>
        /// <returns>订单列表</returns>
        public async Task<List<LabelReplaceEntity>> GetOrdersByHandoverNumbersAsync(List<string> handoverNumbers)
        {
            var db = _provider.GetClient();

            if (handoverNumbers == null || handoverNumbers.Count == 0)
                return new List<LabelReplaceEntity>();

            return await db.Queryable<LabelReplaceEntity>()
                .Where(x => handoverNumbers.Contains(x.BillOfLadingNumber) || handoverNumbers.Contains(x.MasterPackageNumber))
                .OrderByDescending(x => x.CreatedAt)
                .ToListAsync();
        }

        public async Task<List<OpsMonitorDto>> GetOpsMonitorDataAsync()
        {
            string customConnectionString = "server=172.233.222.200;port=6033;user id=oms_user;password=oms_user@pwd;database=lr01mainusa;CharSet=utf8;allow zero datetime=true;Convert Zero Datetime=true;Max Pool Size=100;Min Pool Size=10;Connection Timeout=30;Allow User Variables=True;";
            var result = new List<OpsMonitorDto>();

            using (var connection = new MySql.Data.MySqlClient.MySqlConnection(customConnectionString))
            {
                await connection.OpenAsync();
                using (var command = new MySql.Data.MySqlClient.MySqlCommand("CALL sp_GetOperationsMonitor();", connection))
                {
                    command.CommandTimeout = 120;
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var dto = new OpsMonitorDto
                            {
                                Date = reader["日期"] != DBNull.Value ? Convert.ToDateTime(reader["日期"]).ToString("yyyy-MM-dd") : string.Empty,
                                DailyNewReplaceCount = reader["当天新增换单数"] != DBNull.Value ? Convert.ToInt32(reader["当天新增换单数"]) : 0,
                                CumulativeTotalCount = reader["累计要换的总单数"] != DBNull.Value ? Convert.ToInt32(reader["累计要换的总单数"]) : 0,
                                ShouldReplaceCount = reader["当天应该换单数"] != DBNull.Value ? Convert.ToInt32(reader["当天应该换单数"]) : 0,
                                DailySuccessCount = reader["当日换单完成数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单完成数"]) : 0,
                                DailyFailureCount = reader["当日换单失败数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单失败数"]) : 0,
                                DailyStopCount = reader["当日STOP数"] != DBNull.Value ? Convert.ToInt32(reader["当日STOP数"]) : 0,
                                Rate24HourCount = reader["24小时换单成功数"] != DBNull.Value ? Convert.ToInt32(reader["24小时换单成功数"]) : 0,
                                DailyLabelPushCount = reader["当日标签推送数"] != DBNull.Value ? Convert.ToInt32(reader["当日标签推送数"]) : 0,
                                DailyScanCount = reader["当日扫描数"] != DBNull.Value ? Convert.ToInt32(reader["当日扫描数"]) : 0,
                                DailyCompletionRate = reader["当天换单完成率"] as string,
                                Rate24Hour = reader["24小时换单率"] as string,
                                DataFetchTime = reader["数据拉取时间（UTC_5）"] != DBNull.Value ? Convert.ToDateTime(reader["数据拉取时间（UTC_5）"]) : DateTime.Now
                            };
                            result.Add(dto);
                        }
                    }
                }
            }

            return result;
        }
    }
}
