王明 / Food-Labeling-Management-Platform

using System.Globalization;
using System.Text.Json;
using FoodLabeling.Application.Contracts.Dtos.Dashboard;
using FoodLabeling.Application.Contracts.IServices;
using FoodLabeling.Application.Helpers;
using FoodLabeling.Application.Services.DbModels;
using FoodLabeling.Domain.Entities;
using SqlSugar;
using Volo.Abp.Application.Services;
using Yi.Framework.Rbac.Domain.Entities;
using Yi.Framework.SqlSugarCore.Abstractions;

namespace FoodLabeling.Application.Services;

/// <summary>
/// Dashboard 统计服务（美国版）
/// </summary>
public class DashboardAppService : ApplicationService, IDashboardAppService
{
    private readonly ISqlSugarDbContext _dbContext;
    private readonly ISqlSugarRepository<LocationAggregateRoot, Guid> _locationRepository;
    private readonly ISqlSugarRepository<UserAggregateRoot, Guid> _userRepository;

    public DashboardAppService(
        ISqlSugarDbContext dbContext,
        ISqlSugarRepository<LocationAggregateRoot, Guid> locationRepository,
        ISqlSugarRepository<UserAggregateRoot, Guid> userRepository)
    {
        _dbContext = dbContext;
        _locationRepository = locationRepository;
        _userRepository = userRepository;
    }

    /// <inheritdoc />
    public async Task<DashboardOverviewOutputDto> GetOverviewAsync()
    {
        var now = DateTime.Now;
        var todayStart = now.Date;
        var tomorrowStart = todayStart.AddDays(1);
        var yesterdayStart = todayStart.AddDays(-1);
        var weekStart = todayStart.AddDays(-6);
        var prevWeekStart = todayStart.AddDays(-13);

        var db = _dbContext.SqlSugarClient;
        var scopeLocationIds = await DashboardScopeHelper.ResolveDashboardLocationIdsAsync(CurrentUser, _dbContext);
        var platformAdminUserIds = await DashboardScopeHelper.GetPlatformAdminUserIdStringsAsync(db);

        var printTodayQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
            db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
        var printedToday = await printTodayQuery
            .CountAsync(x => x.CreationTime >= todayStart && x.CreationTime < tomorrowStart);

        var printedYesterday = await DashboardScopeHelper.ApplyPrintTaskLocationScope(
                db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds)
            .CountAsync(x => x.CreationTime >= yesterdayStart && x.CreationTime < todayStart);

        var activeTemplates = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .CountAsync(x => !x.IsDeleted && x.State);
        var activeTemplatesPrevWeek = await LabelTemplateQueryHelper.ProjectListColumns(
                _dbContext.SqlSugarClient.Queryable<FlLabelTemplateDbEntity>())
            .CountAsync(x => !x.IsDeleted && x.State && x.CreationTime < weekStart);

        var activeUsers = await DashboardScopeHelper.CountScopedTeamMembersAsync(
            db, scopeLocationIds, platformAdminUserIds, activeOnly: true, createdBefore: null);
        var activeUsersPrevWeek = await DashboardScopeHelper.CountScopedTeamMembersAsync(
            db, scopeLocationIds, platformAdminUserIds, activeOnly: true, createdBefore: weekStart);

        var locations = await DashboardScopeHelper.CountScopedLocationsAsync(db, scopeLocationIds, null);
        var locationsPrevWeek = await DashboardScopeHelper.CountScopedLocationsAsync(
            db, scopeLocationIds, weekStart);

        var people = await DashboardScopeHelper.CountScopedTeamMembersAsync(
            db, scopeLocationIds, platformAdminUserIds, activeOnly: false, createdBefore: null);
        var peoplePrevWeek = await DashboardScopeHelper.CountScopedTeamMembersAsync(
            db, scopeLocationIds, platformAdminUserIds, activeOnly: false, createdBefore: weekStart);

        var productQuery = db.Queryable<FlProductDbEntity>().Where(x => !x.IsDeleted);
        if (scopeLocationIds is not null)
        {
            if (scopeLocationIds.Count == 0)
            {
                productQuery = productQuery.Where(_ => false);
            }
            else
            {
                productQuery = productQuery.Where(p =>
                    SqlFunc.Subqueryable<FlLocationProductDbEntity>()
                        .Where(lp => lp.ProductId == p.Id && scopeLocationIds.Contains(lp.LocationId))
                        .Any());
            }
        }

        var products = await productQuery.CountAsync();
        // fl_product 当前实体未映射 CreationTime，无法按时间切分对比，先回退为同口径总量对比
        var productsPrevWeek = products;

        var weeklyPrintQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
            db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
        var weeklyPrintRaw = await weeklyPrintQuery
            .Where(x => x.CreationTime >= weekStart && x.CreationTime < tomorrowStart)
            .Select(x => x.CreationTime)
            .ToListAsync();

        var weeklyDict = weeklyPrintRaw
            .GroupBy(x => x.Date)
            .ToDictionary(g => g.Key, g => g.Count());

        var weeklyTrend = Enumerable.Range(0, 7)
            .Select(i =>
            {
                var d = weekStart.AddDays(i).Date;
                return new DashboardDailyTrendPointDto
                {
                    Date = d.ToString("yyyy-MM-dd"),
                    Value = weeklyDict.TryGetValue(d, out var v) ? v : 0
                };
            })
            .ToList();

        var categories = await _dbContext.SqlSugarClient.Queryable<FlLabelCategoryDbEntity>()
            .Where(x => !x.IsDeleted && x.State)
            .ToListAsync();

        var labelCategoryIds = categories.Select(x => x.Id).ToList();
        var labelRows = labelCategoryIds.Count == 0
            ? new List<string?>()
            : await _dbContext.SqlSugarClient.Queryable<FlLabelDbEntity>()
                .Where(x => !x.IsDeleted && x.LabelCategoryId != null && labelCategoryIds.Contains(x.LabelCategoryId))
                .Select(x => x.LabelCategoryId)
                .ToListAsync();

        var labelCountByCategory = labelRows
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .GroupBy(x => x!)
            .ToDictionary(g => g.Key, g => g.Count());

        var categoryDistributionTotal = labelCountByCategory.Values.Sum();
        var categoryDistribution = categories
            .Select(c =>
            {
                var count = labelCountByCategory.TryGetValue(c.Id, out var v) ? v : 0;
                var ratio = categoryDistributionTotal == 0
                    ? 0m
                    : Math.Round(count * 100m / categoryDistributionTotal, 2);
                return new DashboardCategoryDistributionDto
                {
                    CategoryId = c.Id,
                    CategoryName = c.CategoryName,
                    Count = count,
                    Ratio = ratio
                };
            })
            .Where(x => x.Count > 0)
            .OrderByDescending(x => x.Count)
            .ThenBy(x => x.CategoryName)
            .ToList();

        const int recentLabelsTake = 10;
        var recentBaseQuery = DashboardScopeHelper.ApplyPrintTaskLocationScope(
            db.Queryable<FlLabelPrintTaskDbEntity>(), scopeLocationIds);
        var recentRaw = await recentBaseQuery
            .LeftJoin<FlLabelDbEntity>((t, l) => t.LabelId == l.Id)
            .LeftJoin<FlProductDbEntity>((t, l, p) => t.ProductId == p.Id)
            .LeftJoin<FlLabelTemplateDbEntity>((t, l, p, tpl) => t.TemplateId == tpl.Id)
            .OrderBy((t, l, p, tpl) => SqlFunc.IsNull(t.PrintedAt, t.CreationTime), OrderByType.Desc)
            .Take(recentLabelsTake)
            .Select((t, l, p, tpl) => new
            {
                t.Id,
                t.LocationId,
                LabelName = l.LabelName,
                ProductName = p.ProductName,
                tpl.Width,
                tpl.Height,
                tpl.Unit,
                t.PrintInputJson,
                PrintedAt = SqlFunc.IsNull(t.PrintedAt, t.CreationTime),
                t.CreatedBy
            })
            .ToListAsync();

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

        var recentUserIds = recentRaw
            .Select(x => x.CreatedBy)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x!.Trim())
            .Distinct()
            .Select(x => Guid.TryParse(x, out var g) ? g : (Guid?)null)
            .Where(x => x.HasValue)
            .Select(x => x!.Value)
            .ToList();

        var recentUserMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
        if (recentUserIds.Count > 0)
        {
            var users = await _userRepository._DbQueryable
                .Where(u => !u.IsDeleted && recentUserIds.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();
                recentUserMap[u.Id.ToString()] = string.IsNullOrWhiteSpace(display) ? "无" : display;
            }
        }

        var recentLabels = recentRaw.Select(x =>
        {
            var displayName = !string.IsNullOrWhiteSpace(x.ProductName)
                ? x.ProductName.Trim()
                : (string.IsNullOrWhiteSpace(x.LabelName) ? "无" : x.LabelName.Trim());
            var printedAt = x.PrintedAt ?? DateTime.MinValue;
            var status = ResolveRecentLabelStatus(x.PrintInputJson);
            var labelDisplayId = dailyLabelIdMap.TryGetValue(x.Id, out var dailyId) ? dailyId : "无";
            return new DashboardRecentLabelItemDto
            {
                TaskId = x.Id,
                LabelCode = labelDisplayId,
                DisplayName = displayName,
                PrintedByUserId = x.CreatedBy?.Trim(),
                PrintedByName = ResolveRecentUserName(recentUserMap, x.CreatedBy),
                PrintedAt = printedAt,
                Status = status,
                LabelTypeBadge = FormatTemplateBadge(x.Width, x.Height, x.Unit)
            };
        }).ToList();

        var labelsPrintedTodayCard = BuildMetricCard("labelsPrintedToday", "Labels Printed Today", printedToday, printedYesterday);
        var activeTemplatesCard = BuildMetricCard("activeTemplates", "Active Templates", activeTemplates, activeTemplatesPrevWeek);
        var activeUsersCard = BuildMetricCard("activeUsers", "Active Users", activeUsers, activeUsersPrevWeek);
        var locationsCard = BuildMetricCard("locations", "Locations", locations, locationsPrevWeek);
        var peopleCard = BuildMetricCard("people", "People", people, peoplePrevWeek);
        var productsCard = BuildMetricCard("products", "Products", products, productsPrevWeek);

        var output = new DashboardOverviewOutputDto
        {
            LabelsPrintedToday = labelsPrintedTodayCard,
            ActiveTemplates = activeTemplatesCard,
            ActiveUsers = activeUsersCard,
            Locations = locationsCard,
            People = peopleCard,
            Products = productsCard,
            MetricCards = new List<DashboardMetricCardDto>
            {
                labelsPrintedTodayCard,
                activeTemplatesCard,
                activeUsersCard,
                locationsCard,
                peopleCard,
                productsCard
            },
            WeeklyPrintVolume = weeklyTrend,
            CategoryDistribution = categoryDistribution,
            CategoryDistributionTotal = categoryDistributionTotal,
            ByCategory = categoryDistribution,
            ByCategoryTotal = categoryDistributionTotal,
            RecentLabels = recentLabels,
            GeneratedAt = now
        };

        return output;
    }

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

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

    /// <summary>
    /// 依据 PrintInputJson 中的保质期字段与「当前日期」比较得到 active/expired。
    /// </summary>
    private static string ResolveRecentLabelStatus(string? printInputJson)
    {
        if (!TryParseExpiryDate(printInputJson, out var expiryDate))
        {
            return "active";
        }

        var today = DateTime.Now.Date;
        return expiryDate.Date < today ? "expired" : "active";
    }

    private static bool TryParseExpiryDate(string? printInputJson, out DateTime expiryDate)
    {
        expiryDate = default;
        if (string.IsNullOrWhiteSpace(printInputJson))
        {
            return false;
        }

        try
        {
            using var doc = JsonDocument.Parse(printInputJson);
            if (doc.RootElement.ValueKind != JsonValueKind.Object)
            {
                return false;
            }

            foreach (var prop in doc.RootElement.EnumerateObject())
            {
                var key = prop.Name.Trim();
                if (!key.Equals("expiryDate", StringComparison.OrdinalIgnoreCase) &&
                    !key.Equals("expiry", StringComparison.OrdinalIgnoreCase) &&
                    !key.Equals("expirationDate", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }

                var v = prop.Value;
                if (v.ValueKind == JsonValueKind.String)
                {
                    var s = v.GetString();
                    if (!string.IsNullOrWhiteSpace(s) &&
                        DateTime.TryParse(s, CultureInfo.InvariantCulture,
                            DateTimeStyles.AssumeLocal | DateTimeStyles.AllowWhiteSpaces, out var dt))
                    {
                        expiryDate = dt;
                        return true;
                    }
                }
                else if (v.ValueKind == JsonValueKind.Number && v.TryGetInt64(out var unix))
                {
                    expiryDate = DateTimeOffset.FromUnixTimeSeconds(unix).LocalDateTime;
                    return true;
                }
            }
        }
        catch
        {
            return false;
        }

        return false;
    }

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

    private static DashboardMetricCardDto BuildMetricCard(string key, string title, int value, int previousValue)
    {
        var changeValue = value - previousValue;
        var changeRate = previousValue <= 0
            ? (value > 0 ? 100m : 0m)
            : Math.Round(changeValue * 100m / previousValue, 2);

        return new DashboardMetricCardDto
        {
            Key = key,
            Title = title,
            Value = value,
            PreviousValue = previousValue,
            ChangeValue = changeValue,
            ChangeRate = changeRate
        };
    }
}