王明 / Food-Labeling-Management-Platform

using System.Globalization;
using System.Text.Json;
using FoodLabeling.Application.Contracts.Dtos.Common;
using FoodLabeling.Application.Helpers;
using FoodLabeling.Application.Contracts.Dtos.Reports;
using FoodLabeling.Application.Contracts.Dtos.UsAppLabeling;
using FoodLabeling.Application.Contracts.IServices;
using FoodLabeling.Application.Services.DbModels;
using FoodLabeling.Domain.Entities;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using QuestPDF.Fluent;
using QuestPDF.Helpers;
using QuestPDF.Infrastructure;
using SqlSugar;
using Volo.Abp;
using Volo.Abp.Application.Services;
using Yi.Framework.Rbac.Domain.Entities;
using Yi.Framework.SqlSugarCore.Abstractions;

namespace FoodLabeling.Application.Services;

/// <summary>
/// Reports（Print Log / Label Report）
/// </summary>
[Authorize]
public class ReportsAppService : ApplicationService, IReportsAppService
{
    private const int ExportPdfMaxRows = 5000;

    private readonly ISqlSugarDbContext _dbContext;
    private readonly IUsAppLabelingAppService _usAppLabelingAppService;

    public ReportsAppService(ISqlSugarDbContext dbContext, IUsAppLabelingAppService usAppLabelingAppService)
    {
        _dbContext = dbContext;
        _usAppLabelingAppService = usAppLabelingAppService;
    }

    /// <inheritdoc />
    public async Task<PagedResultWithPageDto<ReportsPrintLogListItemDto>> GetPrintLogListAsync(
        ReportsPrintLogGetListInputVo input)
    {
        if (input is null)
        {
            throw new UserFriendlyException("入参不能为空");
        }

        if (!CurrentUser.Id.HasValue)
        {
            throw new UserFriendlyException("用户未登录");
        }

        var locationIds = await ResolveFilteredLocationIdsAsync(input.PartnerId, input.GroupId, input.LocationId);
        if (locationIds is not null && locationIds.Count == 0)
        {
            return EmptyPrintLogPage(input);
        }

        var (rangeStart, rangeEndExcl) = ResolveDateRange(input.StartDate, input.EndDate);
        var isAdmin = ReportsRoleHelper.IsAdminRole(CurrentUser);
        var currentUserIdStr = CurrentUser.Id.Value.ToString();
        var keyword = input.Keyword?.Trim();

        RefAsync<int> total = 0;

        var query = BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword)
            .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, lc, pc, loc, tpl) => t.TemplateId == tpl.Id)
            .Where((t, l, p, lc, pc, loc, tpl) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= rangeStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < rangeEndExcl);

        if (!string.IsNullOrWhiteSpace(input.Sorting) &&
            input.Sorting.Trim().Equals("PrintedAt asc", StringComparison.OrdinalIgnoreCase))
        {
            query = query.OrderBy((t, l, p, lc, pc, loc, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                OrderByType.Asc);
        }
        else
        {
            query = query.OrderBy((t, l, p, lc, pc, loc, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                OrderByType.Desc);
        }

        var pageRows = await query
            .Select((t, l, p, lc, pc, loc, tpl) => new
            {
                t.Id,
                LabelCode = l.LabelCode,
                ProductName = p.ProductName,
                LabelCategoryName = lc.CategoryName,
                ProductCategoryName = pc.CategoryName,
                tpl.Width,
                tpl.Height,
                tpl.Unit,
                tpl.TemplateName,
                t.PrintInputJson,
                PrintedAt = SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                t.CreatedBy,
                t.LocationId,
                LocName = loc.LocationName,
                LocCode = loc.LocationCode
            })
            .ToPageListAsync(input.SkipCount, input.MaxResultCount, total);

        var userMap = await LoadUserNameMapAsync(pageRows.Select(x => x.CreatedBy).Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x!).Distinct().ToList());

        var dailyLabelIdMap = await ReportsPrintLogDailyLabelIdHelper.ResolveDailyLabelIdsAsync(
            _dbContext.SqlSugarClient,
            pageRows.Select(x => new ReportsPrintLogDailyLabelIdHelper.PrintTaskScopeKey(
                x.Id,
                x.LocationId,
                x.PrintedAt ?? DateTime.MinValue)).ToList());

        var items = pageRows.Select(x =>
        {
            var cat = !string.IsNullOrWhiteSpace(x.ProductCategoryName)
                ? x.ProductCategoryName!.Trim()
                : (string.IsNullOrWhiteSpace(x.LabelCategoryName) ? "无" : x.LabelCategoryName.Trim());
            var templateText = FormatTemplateDisplay(x.Width, x.Height, x.Unit, x.TemplateName);
            var locText = FormatLocationText(x.LocName, x.LocCode);
            var printedAt = x.PrintedAt ?? DateTime.MinValue;
            var labelDisplayId = dailyLabelIdMap.TryGetValue(x.Id, out var dailyId) ? dailyId : "无";
            return new ReportsPrintLogListItemDto
            {
                TaskId = x.Id,
                LabelCode = labelDisplayId,
                ProductName = string.IsNullOrWhiteSpace(x.ProductName) ? "无" : x.ProductName.Trim(),
                ProductCategoryName = string.IsNullOrWhiteSpace(x.ProductCategoryName)
                    ? "无"
                    : x.ProductCategoryName!.Trim(),
                LabelCategoryName = string.IsNullOrWhiteSpace(x.LabelCategoryName)
                    ? "无"
                    : x.LabelCategoryName!.Trim(),
                CategoryName = string.IsNullOrWhiteSpace(cat) ? "无" : cat,
                TemplateText = string.IsNullOrWhiteSpace(templateText) ? "无" : templateText,
                PrintedAt = printedAt,
                PrintedByName = ResolveUserName(userMap, x.CreatedBy),
                LocationText = locText,
                LocationId = x.LocationId?.Trim(),
                ExpiryDateText = ReportsPrintLogExpiryHelper.ExtractExpiryText(x.PrintInputJson)
            };
        }).ToList();

        return BuildPagedResult(input.SkipCount, input.MaxResultCount, total, items);
    }

    /// <inheritdoc />
    public async Task<IActionResult> ExportPrintLogPdfAsync(ReportsPrintLogGetListInputVo input)
    {
        QuestPDF.Settings.License = LicenseType.Community;
        if (input is null)
        {
            throw new UserFriendlyException("入参不能为空");
        }

        if (!CurrentUser.Id.HasValue)
        {
            throw new UserFriendlyException("用户未登录");
        }

        var locationIds = await ResolveFilteredLocationIdsAsync(input.PartnerId, input.GroupId, input.LocationId);
        if (locationIds is not null && locationIds.Count == 0)
        {
            return BuildEmptyPdf("print-log-empty.pdf");
        }

        var (rangeStart, rangeEndExcl) = ResolveDateRange(input.StartDate, input.EndDate);
        var isAdmin = ReportsRoleHelper.IsAdminRole(CurrentUser);
        var currentUserIdStr = CurrentUser.Id.Value.ToString();
        var keyword = input.Keyword?.Trim();

        var query = BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword)
            .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, lc, pc, loc, tpl) => t.TemplateId == tpl.Id)
            .Where((t, l, p, lc, pc, loc, tpl) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= rangeStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < rangeEndExcl)
            .OrderBy((t, l, p, lc, pc, loc, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime), OrderByType.Desc);

        var count = await query.CountAsync();
        if (count > ExportPdfMaxRows)
        {
            throw new UserFriendlyException($"导出数据超过上限 {ExportPdfMaxRows} 条，请缩小筛选范围");
        }

        var rows = await query.Take(ExportPdfMaxRows)
            .Select((t, l, p, lc, pc, loc, tpl) => new
            {
                t.Id,
                LabelCode = l.LabelCode,
                ProductName = p.ProductName,
                LabelCategoryName = lc.CategoryName,
                ProductCategoryName = pc.CategoryName,
                tpl.Width,
                tpl.Height,
                tpl.Unit,
                tpl.TemplateName,
                t.PrintInputJson,
                PrintedAt = SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                t.CreatedBy,
                t.LocationId,
                LocName = loc.LocationName,
                LocCode = loc.LocationCode
            })
            .ToListAsync();

        var userMap = await LoadUserNameMapAsync(rows.Select(x => x.CreatedBy).Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x!).Distinct().ToList());

        var dailyLabelIdMap = await ReportsPrintLogDailyLabelIdHelper.ResolveDailyLabelIdsAsync(
            _dbContext.SqlSugarClient,
            rows.Select(x => new ReportsPrintLogDailyLabelIdHelper.PrintTaskScopeKey(
                x.Id,
                x.LocationId,
                x.PrintedAt ?? DateTime.MinValue)).ToList());

        var fileName = $"print-log_{Clock.Now:yyyy-MM-dd_HH-mm-ss}.pdf";
        var document = Document.Create(container =>
        {
            container.Page(page =>
            {
                page.Margin(22);
                page.DefaultTextStyle(x => x.FontSize(8.5f));
                page.Header().Text("Print Log").SemiBold().FontSize(16);
                page.Content().PaddingTop(10).Table(table =>
                {
                    table.ColumnsDefinition(c =>
                    {
                        c.RelativeColumn(1.1f);
                        c.RelativeColumn(1.2f);
                        c.RelativeColumn(0.9f);
                        c.RelativeColumn(1.1f);
                        c.RelativeColumn(1f);
                        c.RelativeColumn(0.9f);
                        c.RelativeColumn(0.9f);
                        c.RelativeColumn(0.8f);
                    });
                    static IContainer H(IContainer x) =>
                        x.Background(Colors.Grey.Lighten3).Padding(4).DefaultTextStyle(s => s.SemiBold());
                    table.Cell().Element(H).Text("Label ID");
                    table.Cell().Element(H).Text("Product");
                    table.Cell().Element(H).Text("Category");
                    table.Cell().Element(H).Text("Template");
                    table.Cell().Element(H).Text("Printed At");
                    table.Cell().Element(H).Text("Printed By");
                    table.Cell().Element(H).Text("Location");
                    table.Cell().Element(H).Text("Expiry");

                    foreach (var x in rows)
                    {
                        var cat = !string.IsNullOrWhiteSpace(x.ProductCategoryName)
                            ? x.ProductCategoryName!.Trim()
                            : (string.IsNullOrWhiteSpace(x.LabelCategoryName) ? "无" : x.LabelCategoryName.Trim());
                        var labelDisplayId = dailyLabelIdMap.TryGetValue(x.Id, out var dailyId) ? dailyId : "无";
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(labelDisplayId);
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(string.IsNullOrWhiteSpace(x.ProductName) ? "无" : x.ProductName.Trim());
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3).Text(cat);
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(FormatTemplateDisplay(x.Width, x.Height, x.Unit, x.TemplateName));
                        var printedAt = x.PrintedAt ?? DateTime.MinValue;
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(printedAt.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture));
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(ResolveUserName(userMap, x.CreatedBy));
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(FormatLocationText(x.LocName, x.LocCode));
                        table.Cell().BorderBottom(0.5f).BorderColor(Colors.Grey.Lighten2).Padding(3)
                            .Text(ReportsPrintLogExpiryHelper.ExtractExpiryText(x.PrintInputJson));
                    }
                });
            });
        });

        var ms = new MemoryStream();
        document.GeneratePdf(ms);
        ms.Position = 0;
        return new FileStreamResult(ms, "application/pdf") { FileDownloadName = fileName };
    }

    /// <inheritdoc />
    public async Task<IActionResult> ExportPrintLogExcelAsync([FromQuery] ReportsPrintLogGetListInputVo input)
    {
        if (input is null)
        {
            throw new UserFriendlyException("入参不能为空");
        }

        if (!CurrentUser.Id.HasValue)
        {
            throw new UserFriendlyException("用户未登录");
        }

        var locationIds = await ResolveFilteredLocationIdsAsync(input.PartnerId, input.GroupId, input.LocationId);
        if (locationIds is not null && locationIds.Count == 0)
        {
            var emptyMs = ReportsPrintLogExcelHelper.BuildWorkbook(Array.Empty<ReportsPrintLogListItemDto>());
            var emptyName = $"print-log_{Clock.Now:yyyyMMdd-HHmmss}.xlsx";
            return new FileStreamResult(emptyMs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                { FileDownloadName = emptyName };
        }

        var (rangeStart, rangeEndExcl) = ResolveDateRange(input.StartDate, input.EndDate);
        var isAdmin = ReportsRoleHelper.IsAdminRole(CurrentUser);
        var currentUserIdStr = CurrentUser.Id.Value.ToString();
        var keyword = input.Keyword?.Trim();

        var query = BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword)
            .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, lc, pc, loc, tpl) => t.TemplateId == tpl.Id)
            .Where((t, l, p, lc, pc, loc, tpl) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= rangeStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < rangeEndExcl);

        if (!string.IsNullOrWhiteSpace(input.Sorting) &&
            input.Sorting.Trim().Equals("PrintedAt asc", StringComparison.OrdinalIgnoreCase))
        {
            query = query.OrderBy((t, l, p, lc, pc, loc, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                OrderByType.Asc);
        }
        else
        {
            query = query.OrderBy((t, l, p, lc, pc, loc, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                OrderByType.Desc);
        }

        var count = await query.CountAsync();
        if (count > ExportPdfMaxRows)
        {
            throw new UserFriendlyException($"导出数据超过上限 {ExportPdfMaxRows} 条，请缩小筛选范围");
        }

        var pageRows = await query.Take(ExportPdfMaxRows)
            .Select((t, l, p, lc, pc, loc, tpl) => new PrintLogExportRow
            {
                Id = t.Id,
                LabelCode = l.LabelCode,
                ProductName = p.ProductName,
                LabelCategoryName = lc.CategoryName,
                ProductCategoryName = pc.CategoryName,
                Width = tpl.Width,
                Height = tpl.Height,
                Unit = tpl.Unit,
                TemplateName = tpl.TemplateName,
                PrintInputJson = t.PrintInputJson,
                PrintedAt = SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                CreatedBy = t.CreatedBy,
                LocationId = t.LocationId,
                LocName = loc.LocationName,
                LocCode = loc.LocationCode
            })
            .ToListAsync();

        var userMap = await LoadUserNameMapAsync(pageRows.Select(x => x.CreatedBy).Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x!).Distinct().ToList());

        var dailyLabelIdMap = await ReportsPrintLogDailyLabelIdHelper.ResolveDailyLabelIdsAsync(
            _dbContext.SqlSugarClient,
            pageRows.Select(x => new ReportsPrintLogDailyLabelIdHelper.PrintTaskScopeKey(
                x.Id,
                x.LocationId,
                x.PrintedAt ?? DateTime.MinValue)).ToList());

        var items = pageRows.Select(x => MapPrintLogExportRowToListItem(x, userMap, dailyLabelIdMap)).ToList();

        var ms = ReportsPrintLogExcelHelper.BuildWorkbook(items);
        var fileName = $"print-log_{Clock.Now:yyyyMMdd-HHmmss}.xlsx";
        return new FileStreamResult(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            { FileDownloadName = fileName };
    }

    /// <inheritdoc />
    public Task<UsAppLabelPrintOutputDto> ReprintPrintLogAsync(UsAppLabelReprintInputVo input) =>
        _usAppLabelingAppService.ReprintAsync(input);

    /// <inheritdoc />
    public async Task<PagedResultWithPageDto<ReportsTemplatePrintStatListItemDto>> GetTemplatePrintStatListAsync(
        ReportsTemplatePrintStatGetListInputVo input)
    {
        if (input is null)
        {
            throw new UserFriendlyException("入参不能为空");
        }

        if (!CurrentUser.Id.HasValue)
        {
            throw new UserFriendlyException("用户未登录");
        }

        var locationIds = await ReportsLocationScopeHelper.ResolveReportLocationIdsAsync(
            CurrentUser,
            _dbContext.SqlSugarClient,
            input.PartnerId,
            input.GroupId,
            input.LocationId);
        if (locationIds is not null && locationIds.Count == 0)
        {
            return EmptyTemplatePrintStatPage(input);
        }

        var (rangeStart, rangeEndExcl) = ResolveDateRange(input.StartDate, input.EndDate);
        var isAdmin = ReportsRoleHelper.IsAdminRole(CurrentUser);
        var currentUserIdStr = CurrentUser.Id.Value.ToString();
        var templateKeyword = input.Keyword?.Trim();

        var groupedRows = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword: null,
                restrictToCreator: false)
            .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, lc, pc, loc, tpl) => t.TemplateId == tpl.Id)
            .Where((t, l, p, lc, pc, loc, tpl) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= rangeStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < rangeEndExcl)
            .WhereIF(!string.IsNullOrWhiteSpace(templateKeyword),
                (t, l, p, lc, pc, loc, tpl) =>
                    tpl.TemplateName != null && tpl.TemplateName.Contains(templateKeyword!))
            .GroupBy((t, l, p, lc, pc, loc, tpl) => new { t.TemplateId, tpl.TemplateName })
            .Select((t, l, p, lc, pc, loc, tpl) => new
            {
                t.TemplateId,
                tpl.TemplateName,
                Cnt = SqlFunc.AggregateCount(t.Id)
            })
            .ToListAsync();

        var ordered = groupedRows
            .Select(x => new ReportsTemplatePrintStatListItemDto
            {
                TemplateId = string.IsNullOrWhiteSpace(x.TemplateId) ? null : x.TemplateId.Trim(),
                TemplateName = string.IsNullOrWhiteSpace(x.TemplateName) ? "无" : x.TemplateName.Trim(),
                PrintedCount = x.Cnt
            })
            .ToList();

        if (!string.IsNullOrWhiteSpace(input.Sorting) &&
            input.Sorting.Trim().Equals("PrintedCount asc", StringComparison.OrdinalIgnoreCase))
        {
            ordered = ordered.OrderBy(x => x.PrintedCount).ThenBy(x => x.TemplateName).ToList();
        }
        else
        {
            ordered = ordered.OrderByDescending(x => x.PrintedCount).ThenBy(x => x.TemplateName).ToList();
        }

        var total = ordered.Count;
        var pageIndex = PagedQueryConvention.PageIndexFromSkipCount(input.SkipCount);
        var pageSize = input.MaxResultCount <= 0 ? total : input.MaxResultCount;
        var offset = pageSize <= 0 ? 0 : (pageIndex - 1) * pageSize;
        var pageItems = pageSize <= 0
            ? ordered
            : ordered.Skip(offset).Take(pageSize).ToList();

        return BuildPagedResult(input.SkipCount, input.MaxResultCount, total, pageItems);
    }

    /// <inheritdoc />
    public async Task<ReportsLabelReportOutputDto> GetLabelReportAsync(ReportsLabelReportQueryInputVo input)
    {
        if (input is null)
        {
            throw new UserFriendlyException("入参不能为空");
        }

        if (!CurrentUser.Id.HasValue)
        {
            throw new UserFriendlyException("用户未登录");
        }

        var locationIds = await ReportsLocationScopeHelper.ResolveReportLocationIdsAsync(
            CurrentUser,
            _dbContext.SqlSugarClient,
            input.PartnerId,
            input.GroupId,
            input.LocationId);
        if (locationIds is not null && locationIds.Count == 0)
        {
            return new ReportsLabelReportOutputDto();
        }

        var (curStart, curEndExcl) = ResolveDateRange(input.StartDate, input.EndDate);
        var span = curEndExcl - curStart;
        if (span.TotalDays < 1)
        {
            span = TimeSpan.FromDays(1);
        }

        var prevEndExcl = curStart;
        var prevStart = curStart - span;
        var isAdmin = ReportsRoleHelper.IsAdminRole(CurrentUser);
        var currentUserIdStr = CurrentUser.Id.Value.ToString();
        var keyword = input.Keyword?.Trim();

        var totalCur = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword, restrictToCreator: false)
            .Where((t, l, p, lc, pc, loc) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= curStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < curEndExcl)
            .CountAsync();

        var totalPrev = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword, restrictToCreator: false)
            .Where((t, l, p, lc, pc, loc) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= prevStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < prevEndExcl)
            .CountAsync();

        var dayCount = Math.Max(1, (int)Math.Ceiling((curEndExcl - curStart).TotalDays));
        var prevDayCount = Math.Max(1, (int)Math.Ceiling((prevEndExcl - prevStart).TotalDays));
        var avgDaily = Math.Round((decimal)totalCur / dayCount, 2);
        var avgDailyPrev = Math.Round((decimal)totalPrev / prevDayCount, 2);

        var categoryRows = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword, restrictToCreator: false)
            .Where((t, l, p, lc, pc, loc) =>
                l.LabelCategoryId != null &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= curStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < curEndExcl)
            .GroupBy((t, l, p, lc, pc, loc) => new { lc.Id, lc.CategoryName })
            .Select((t, l, p, lc, pc, loc) => new { lc.Id, lc.CategoryName, Cnt = SqlFunc.AggregateCount(t.Id) })
            .ToListAsync();

        var topCat = categoryRows.OrderByDescending(x => x.Cnt).FirstOrDefault();

        var productRows = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword, restrictToCreator: false)
            .Where((t, l, p, lc, pc, loc) =>
                !string.IsNullOrEmpty(p.Id) &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= curStart &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < curEndExcl)
            .GroupBy((t, l, p, lc, pc, loc) => new { p.Id, p.ProductName, Cat = pc.CategoryName })
            .Select((t, l, p, lc, pc, loc) => new { p.Id, p.ProductName, CategoryName = pc.CategoryName, Cnt = SqlFunc.AggregateCount(t.Id) })
            .ToListAsync();

        var topProd = productRows.OrderByDescending(x => x.Cnt).FirstOrDefault();
        var topList = productRows.OrderByDescending(x => x.Cnt).Take(20).ToList();

        var trendEndDay = curEndExcl.Date.AddDays(-1);
        var trendStartDay = trendEndDay.AddDays(-6);
        if (trendStartDay < curStart.Date)
        {
            trendStartDay = curStart.Date;
        }

        var trendEndExcl = trendEndDay.AddDays(1);

        var trendRaw = await BuildReportTaskCore(locationIds, isAdmin, currentUserIdStr, keyword, restrictToCreator: false)
            .Where((t, l, p, lc, pc, loc) =>
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) >= trendStartDay &&
                SqlFunc.IsNull(t.PrintedAt, t.CreationTime) < trendEndExcl)
            .Select((t, l, p, lc, pc, loc) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime))
            .ToListAsync();

        var trendDict = trendRaw
            .Where(x => x.HasValue)
            .GroupBy(x => x!.Value.Date)
            .ToDictionary(g => g.Key, g => g.Count());

        var trend = new List<ReportsDailyCountDto>();
        for (var d = trendStartDay; d <= trendEndDay; d = d.AddDays(1))
        {
            trend.Add(new ReportsDailyCountDto
            {
                Date = d.ToString("yyyy-MM-dd"),
                Count = trendDict.TryGetValue(d, out var c) ? c : 0
            });
        }

        var byCategory = categoryRows
            .OrderByDescending(x => x.Cnt)
            .Select(x => new ReportsCategoryCountDto
            {
                CategoryId = string.IsNullOrWhiteSpace(x.Id) ? null : x.Id.Trim(),
                CategoryName = string.IsNullOrWhiteSpace(x.CategoryName) ? null : x.CategoryName.Trim(),
                Count = x.Cnt
            })
            .ToList();

        var mostUsed = topList.Select(x =>
        {
            var pct = totalCur <= 0 ? 0m : Math.Round(x.Cnt * 100m / totalCur, 2);
            return new ReportsTopProductRowDto
            {
                ProductId = string.IsNullOrWhiteSpace(x.Id) ? null : x.Id.Trim(),
                ProductName = string.IsNullOrWhiteSpace(x.ProductName) ? null : x.ProductName.Trim(),
                CategoryName = string.IsNullOrWhiteSpace(x.CategoryName) ? null : x.CategoryName!.Trim(),
                TotalPrinted = x.Cnt,
                UsagePercent = pct
            };
        }).ToList();

        return new ReportsLabelReportOutputDto
        {
            Summary = new ReportsLabelReportSummaryDto
            {
                TotalLabelsPrinted = totalCur,
                TotalLabelsPrintedPrevPeriod = totalPrev,
                TotalLabelsPrintedChangeRate = CalcChangeRate(totalCur, totalPrev),
                MostPrintedCategoryName = string.IsNullOrWhiteSpace(topCat?.CategoryName) ? null : topCat.CategoryName.Trim(),
                MostPrintedCategoryCount = topCat?.Cnt ?? 0,
                TopProductName = string.IsNullOrWhiteSpace(topProd?.ProductName) ? null : topProd.ProductName.Trim(),
                TopProductCount = topProd?.Cnt ?? 0,
                AvgDailyPrints = avgDaily,
                AvgDailyPrintsPrevPeriod = avgDailyPrev,
                AvgDailyPrintsChangeRate = CalcChangeRate(avgDaily, avgDailyPrev)
            },
            LabelsByCategory = byCategory,
            PrintVolumeTrend = trend,
            MostUsedProducts = mostUsed
        };
    }

    /// <inheritdoc />
    public async Task<IActionResult> ExportLabelReportPdfAsync(ReportsLabelReportQueryInputVo input)
    {
        QuestPDF.Settings.License = LicenseType.Community;
        var data = await GetLabelReportAsync(input);
        var fileName = $"label-report_{Clock.Now:yyyy-MM-dd_HH-mm-ss}.pdf";
        var document = Document.Create(container =>
        {
            container.Page(page =>
            {
                page.Margin(24);
                page.DefaultTextStyle(x => x.FontSize(9));
                page.Header().Text("Label Report").SemiBold().FontSize(16);
                page.Content().Column(col =>
                {
                    col.Spacing(10);
                    col.Item().Text(
                        $"Total printed: {data.Summary.TotalLabelsPrinted} (prev: {data.Summary.TotalLabelsPrintedPrevPeriod}, Δ%: {data.Summary.TotalLabelsPrintedChangeRate:0.##}%)");
                    col.Item().Text(
                        $"Top category: {data.Summary.MostPrintedCategoryName} ({data.Summary.MostPrintedCategoryCount})");
                    col.Item().Text($"Top product: {data.Summary.TopProductName} ({data.Summary.TopProductCount})");
                    col.Item().Text(
                        $"Avg daily: {data.Summary.AvgDailyPrints:0.##} (prev: {data.Summary.AvgDailyPrintsPrevPeriod:0.##}, Δ%: {data.Summary.AvgDailyPrintsChangeRate:0.##}%)");
                    col.Item().Text("By category:").SemiBold();
                    col.Item().Table(t =>
                    {
                        t.ColumnsDefinition(c => { c.RelativeColumn(2); c.RelativeColumn(1); });
                        t.Cell().Element(HeaderCell).Text("Category");
                        t.Cell().Element(HeaderCell).Text("Count");
                        foreach (var r in data.LabelsByCategory)
                        {
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.CategoryName);
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.Count.ToString());
                        }
                    });
                    col.Item().Text("Daily trend:").SemiBold();
                    col.Item().Table(t =>
                    {
                        t.ColumnsDefinition(c => { c.RelativeColumn(1.2f); c.RelativeColumn(1); });
                        t.Cell().Element(HeaderCell).Text("Date");
                        t.Cell().Element(HeaderCell).Text("Count");
                        foreach (var r in data.PrintVolumeTrend)
                        {
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.Date);
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.Count.ToString());
                        }
                    });
                    col.Item().Text("Most used products:").SemiBold();
                    col.Item().Table(t =>
                    {
                        t.ColumnsDefinition(c =>
                        {
                            c.RelativeColumn(1.5f);
                            c.RelativeColumn(1f);
                            c.RelativeColumn(0.8f);
                            c.RelativeColumn(0.7f);
                        });
                        t.Cell().Element(HeaderCell).Text("Product");
                        t.Cell().Element(HeaderCell).Text("Category");
                        t.Cell().Element(HeaderCell).Text("Total");
                        t.Cell().Element(HeaderCell).Text("%");
                        foreach (var r in data.MostUsedProducts)
                        {
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.ProductName);
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.CategoryName);
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.TotalPrinted.ToString());
                            t.Cell().BorderBottom(0.5f).Padding(3).Text(r.UsagePercent.ToString("0.##"));
                        }
                    });
                });
            });
        });

        static IContainer HeaderCell(IContainer x) =>
            x.Background(Colors.Grey.Lighten3).Padding(4).DefaultTextStyle(s => s.SemiBold());

        var ms = new MemoryStream();
        document.GeneratePdf(ms);
        ms.Position = 0;
        return new FileStreamResult(ms, "application/pdf") { FileDownloadName = fileName };
    }

    private ISugarQueryable<FlLabelPrintTaskDbEntity, FlLabelDbEntity, FlProductDbEntity, FlLabelCategoryDbEntity,
            FlProductCategoryDbEntity, LocationAggregateRoot>
        BuildReportTaskCore(
            List<string>? locationIds,
            bool isAdmin,
            string currentUserIdStr,
            string? keyword,
            bool restrictToCreator = true)
    {
        return _dbContext.SqlSugarClient.Queryable<FlLabelPrintTaskDbEntity>()
            .LeftJoin<FlLabelDbEntity>((t, l) => t.LabelId == l.Id)
            .LeftJoin<FlProductDbEntity>((t, l, p) => t.ProductId == p.Id)
            .LeftJoin<FlLabelCategoryDbEntity>((t, l, p, lc) => l.LabelCategoryId == lc.Id)
            .LeftJoin<FlProductCategoryDbEntity>((t, l, p, lc, pc) => p.CategoryId == pc.Id)
            .LeftJoin<LocationAggregateRoot>((t, l, p, lc, pc, loc) =>
                t.LocationId != null && SqlFunc.ToString(loc.Id) == t.LocationId)
            .Where((t, l, p, lc, pc, loc) => !loc.IsDeleted)
            .WhereIF(restrictToCreator && !isAdmin, (t, l, p, lc, pc, loc) => t.CreatedBy == currentUserIdStr)
            .WhereIF(locationIds is not null, (t, l, p, lc, pc, loc) => locationIds!.Contains(t.LocationId!))
            .WhereIF(!string.IsNullOrWhiteSpace(keyword),
                (t, l, p, lc, pc, loc) =>
                    (p.ProductName != null && p.ProductName.Contains(keyword!)) ||
                    (lc.CategoryName != null && lc.CategoryName.Contains(keyword!)) ||
                    (pc.CategoryName != null && pc.CategoryName.Contains(keyword!)));
    }

    private static decimal CalcChangeRate(decimal current, decimal previous)
    {
        if (previous == 0)
        {
            return current > 0 ? 100m : 0m;
        }

        return Math.Round((current - previous) * 100m / previous, 2);
    }

    private static decimal CalcChangeRate(int current, int previous) =>
        CalcChangeRate((decimal)current, (decimal)previous);

    private async Task<List<string>?> ResolveFilteredLocationIdsAsync(string? partnerId, string? groupId,
        string? locationId)
    {
        var locId = locationId?.Trim();
        if (!string.IsNullOrWhiteSpace(locId))
        {
            return new List<string> { locId };
        }

        var gid = groupId?.Trim();
        var pid = partnerId?.Trim();

        if (string.IsNullOrWhiteSpace(pid) && string.IsNullOrWhiteSpace(gid))
        {
            return null;
        }

        var q = _dbContext.SqlSugarClient.Queryable<LocationAggregateRoot>().Where(x => !x.IsDeleted);

        if (!string.IsNullOrWhiteSpace(gid))
        {
            var g = await _dbContext.SqlSugarClient.Queryable<FlGroupDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == gid);
            if (g is null)
            {
                return new List<string>();
            }

            var gName = g.GroupName?.Trim() ?? string.Empty;
            var partner = await _dbContext.SqlSugarClient.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == g.PartnerId);
            var pName = partner?.PartnerName?.Trim() ?? string.Empty;
            q = q.Where(x => x.GroupName == gName && x.Partner == pName);
        }
        else if (!string.IsNullOrWhiteSpace(pid))
        {
            var partner = await _dbContext.SqlSugarClient.Queryable<FlPartnerDbEntity>()
                .FirstAsync(x => !x.IsDeleted && x.Id == pid);
            if (partner is null)
            {
                return new List<string>();
            }

            var pName = partner.PartnerName?.Trim() ?? string.Empty;
            q = q.Where(x => x.Partner == pName);
        }

        var ids = await q.Select(x => SqlFunc.ToString(x.Id)).ToListAsync();
        return ids;
    }

    private static (DateTime rangeStart, DateTime rangeEndExcl) ResolveDateRange(DateTime? startDate,
        DateTime? endDate)
    {
        var endDay = (endDate ?? DateTime.Today).Date;
        var endExcl = endDay.AddDays(1);
        var start = (startDate ?? endDay.AddDays(-29)).Date;
        if (start >= endExcl)
        {
            start = endExcl.AddDays(-1);
        }

        return (start, endExcl);
    }

    private static PagedResultWithPageDto<ReportsPrintLogListItemDto> EmptyPrintLogPage(
        ReportsPrintLogGetListInputVo input)
    {
        var pageSize = input.MaxResultCount <= 0 ? 0 : input.MaxResultCount;
        var pageIndex = pageSize <= 0 ? 1 : PagedQueryConvention.PageIndexFromSkipCount(input.SkipCount);
        return new PagedResultWithPageDto<ReportsPrintLogListItemDto>
        {
            PageIndex = pageIndex,
            PageSize = pageSize,
            TotalCount = 0,
            TotalPages = 0,
            Items = new List<ReportsPrintLogListItemDto>()
        };
    }

    private static PagedResultWithPageDto<ReportsTemplatePrintStatListItemDto> EmptyTemplatePrintStatPage(
        ReportsTemplatePrintStatGetListInputVo input)
    {
        var pageSize = input.MaxResultCount <= 0 ? 0 : input.MaxResultCount;
        var pageIndex = pageSize <= 0 ? 1 : PagedQueryConvention.PageIndexFromSkipCount(input.SkipCount);
        return new PagedResultWithPageDto<ReportsTemplatePrintStatListItemDto>
        {
            PageIndex = pageIndex,
            PageSize = pageSize,
            TotalCount = 0,
            TotalPages = 0,
            Items = new List<ReportsTemplatePrintStatListItemDto>()
        };
    }

    private static PagedResultWithPageDto<T> BuildPagedResult<T>(int skipCount, int maxResultCount, int total,
        List<T> items)
    {
        var pageSize = maxResultCount <= 0 ? items.Count : maxResultCount;
        var pageIndex = pageSize <= 0 ? 1 : PagedQueryConvention.PageIndexFromSkipCount(skipCount);
        var totalPages = pageSize <= 0 ? 0 : (int)Math.Ceiling(total / (double)pageSize);
        return new PagedResultWithPageDto<T>
        {
            PageIndex = pageIndex,
            PageSize = pageSize,
            TotalCount = total,
            TotalPages = totalPages,
            Items = items
        };
    }

    private async Task<Dictionary<string, string>> LoadUserNameMapAsync(List<string> userIdStrings)
    {
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
        if (userIdStrings.Count == 0)
        {
            return map;
        }

        var guids = userIdStrings
            .Select(x => Guid.TryParse(x, out var g) ? g : (Guid?)null)
            .Where(x => x.HasValue)
            .Select(x => x!.Value)
            .Distinct()
            .ToList();
        if (guids.Count == 0)
        {
            return map;
        }

        var users = await _dbContext.SqlSugarClient.Queryable<UserAggregateRoot>()
            .Where(u => !u.IsDeleted && guids.Contains(u.Id))
            .Select(u => new { u.Id, u.Name, u.UserName })
            .ToListAsync();

        foreach (var u in users)
        {
            var display = !string.IsNullOrWhiteSpace(u.Name) ? u.Name.Trim() : u.UserName.Trim();
            map[u.Id.ToString()] = string.IsNullOrWhiteSpace(display) ? "无" : display;
        }

        return map;
    }

    private static string ResolveUserName(Dictionary<string, string> map, string? createdBy)
    {
        if (string.IsNullOrWhiteSpace(createdBy))
        {
            return "无";
        }

        return map.TryGetValue(createdBy.Trim(), out var n) ? n : "无";
    }

    private static string FormatLocationText(string? locName, string? locCode)
    {
        var n = locName?.Trim();
        var c = locCode?.Trim();
        if (string.IsNullOrWhiteSpace(n) && string.IsNullOrWhiteSpace(c))
        {
            return "无";
        }

        if (string.IsNullOrWhiteSpace(c))
        {
            return n ?? "无";
        }

        if (string.IsNullOrWhiteSpace(n))
        {
            return $"({c})";
        }

        return $"{n} ({c})";
    }

    private static string FormatTemplateDisplay(decimal w, decimal h, string? unit, string? templateName)
    {
        var size = FormatLabelSizeWithUnit(w, h, unit ?? "inch");
        var tn = templateName?.Trim();
        if (string.IsNullOrWhiteSpace(tn))
        {
            return size ?? "无";
        }

        return string.IsNullOrWhiteSpace(size) ? tn : $"{size} {tn}";
    }

    private static string? FormatLabelSizeWithUnit(decimal w, decimal h, string unit)
    {
        var u = (unit ?? "inch").Trim().ToLowerInvariant();
        var ws = w.ToString(CultureInfo.InvariantCulture);
        var hs = h.ToString(CultureInfo.InvariantCulture);
        var normalizedUnit = u is "in" ? "inch" : u;
        return $"{ws}x{hs}{normalizedUnit}";
    }

    private static IActionResult BuildEmptyPdf(string fileName)
    {
        QuestPDF.Settings.License = LicenseType.Community;
        var document = Document.Create(c =>
        {
            c.Page(p =>
            {
                p.Margin(30);
                p.Content().Text("No data for current filters.");
            });
        });
        var ms = new MemoryStream();
        document.GeneratePdf(ms);
        ms.Position = 0;
        return new FileStreamResult(ms, "application/pdf") { FileDownloadName = fileName };
    }

    private sealed class PrintLogExportRow
    {
        public string Id { get; set; } = string.Empty;

        public string? LabelCode { get; set; }

        public string? ProductName { get; set; }

        public string? LabelCategoryName { get; set; }

        public string? ProductCategoryName { get; set; }

        public decimal Width { get; set; }

        public decimal Height { get; set; }

        public string? Unit { get; set; }

        public string? TemplateName { get; set; }

        public string? PrintInputJson { get; set; }

        public DateTime? PrintedAt { get; set; }

        public string? CreatedBy { get; set; }

        public string? LocationId { get; set; }

        public string? LocName { get; set; }

        public string? LocCode { get; set; }
    }

    private static ReportsPrintLogListItemDto MapPrintLogExportRowToListItem(
        PrintLogExportRow x,
        Dictionary<string, string> userMap,
        IReadOnlyDictionary<string, string> dailyLabelIdMap)
    {
        var cat = !string.IsNullOrWhiteSpace(x.ProductCategoryName)
            ? x.ProductCategoryName!.Trim()
            : (string.IsNullOrWhiteSpace(x.LabelCategoryName) ? "无" : x.LabelCategoryName.Trim());
        var templateText = FormatTemplateDisplay(x.Width, x.Height, x.Unit, x.TemplateName);
        var locText = FormatLocationText(x.LocName, x.LocCode);
        var printedAt = x.PrintedAt ?? DateTime.MinValue;
        var labelDisplayId = dailyLabelIdMap.TryGetValue(x.Id, out var dailyId) ? dailyId : "无";
        return new ReportsPrintLogListItemDto
        {
            TaskId = x.Id,
            LabelCode = labelDisplayId,
            ProductName = string.IsNullOrWhiteSpace(x.ProductName) ? "无" : x.ProductName.Trim(),
            ProductCategoryName = string.IsNullOrWhiteSpace(x.ProductCategoryName)
                ? "无"
                : x.ProductCategoryName!.Trim(),
            LabelCategoryName = string.IsNullOrWhiteSpace(x.LabelCategoryName)
                ? "无"
                : x.LabelCategoryName!.Trim(),
            CategoryName = string.IsNullOrWhiteSpace(cat) ? "无" : cat,
            TemplateText = string.IsNullOrWhiteSpace(templateText) ? "无" : templateText,
            PrintedAt = printedAt,
            PrintedByName = ResolveUserName(userMap, x.CreatedBy),
            LocationText = locText,
            LocationId = x.LocationId?.Trim(),
            ExpiryDateText = ReportsPrintLogExpiryHelper.ExtractExpiryText(x.PrintInputJson)
        };
    }
}