AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/S8MonitoringService.cs

using Admin.NET.Plugin.AiDOP.Dto.S8;
using Admin.NET.Plugin.AiDOP.Entity.S0.Warehouse;
using Admin.NET.Plugin.AiDOP.Entity.S8;
using Admin.NET.Plugin.AiDOP.Infrastructure.S8;

namespace Admin.NET.Plugin.AiDOP.Service.S8;

public class S8MonitoringService : ITransient
{
    private readonly SqlSugarRepository<AdoS8Exception> _rep;
    private readonly SqlSugarRepository<AdoS8ExceptionType> _typeRep;
    // S8-OVERVIEW-DEPT-LABEL-HYDRATION-1：部门名称水合改用 AdoS0DepartmentMaster（DepartmentMaster 表，
    // RecID=1/2 映射真实部门），与 S8DashboardService / S8ExceptionService 主数据水合口径一致。
    // SysOrg 的 Id 为雪花，不与 ado_s8_exception.responsible_dept_id (1/2) 匹配，不能作部门名来源。
    private readonly SqlSugarRepository<AdoS0DepartmentMaster> _deptRep;

    public S8MonitoringService(
        SqlSugarRepository<AdoS8Exception> rep,
        SqlSugarRepository<AdoS8ExceptionType> typeRep,
        SqlSugarRepository<AdoS0DepartmentMaster> deptRep)
    {
        _rep = rep;
        _typeRep = typeRep;
        _deptRep = deptRep;
    }

    /// <summary>
    /// 9宫格数据：S1-S7 订单健康分布 + S8业务类别汇总 + S9部门汇总。
    /// 数据来源 ado_s8_exception 聚合；异常表为空时返回全 0（不再返回 Demo 数据）。
    /// </summary>
    public async Task<AdoS8OrderGridDto> GetOrderGridAsync(long tenantId = 1, long factoryId = 1)
    {
        // S8-DASHBOARD-DATA-ALIGN-S1S7-1：统一统计基准 module_code IN S1-S7；
        // module_code IS NULL 的 legacy/demo 行不参与大屏聚合（不依赖 scene_code、不映射 legacy scene）。
        var events = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => S8ModuleCode.All.Contains(e.ModuleCode))
            .Select(e => new
            {
                e.ModuleCode,
                e.Severity,
                e.Status,
                e.TimeoutFlag,
                e.ExceptionTypeCode,
                e.SceneCode,
                e.ResponsibleDeptId,
                e.CreatedAt,
                e.ClosedAt
            })
            .ToListAsync();

        // ── Modules：按 module_code 聚合 ──
        var modules = S8ModuleCode.All.Select(mc =>
        {
            var rows = events.Where(e => e.ModuleCode == mc).ToList();
            var unclosed = rows.Where(e => e.Status != "CLOSED").ToList();
            var red    = unclosed.Count(e => e.Severity == "CRITICAL" || e.Severity == "HIGH");
            var yellow = unclosed.Count(e => e.Severity == "MEDIUM");
            var green  = unclosed.Count(e => e.Severity == "LOW");
            var closed = rows.Count(e => e.Status == "CLOSED");
            var total  = rows.Count;
            return new AdoS8ModuleOrderSummary
            {
                ModuleCode      = mc,
                ModuleLabel     = S8ModuleCode.Label(mc),
                Green           = green,
                Yellow          = yellow,
                Red             = red,
                Total           = total,
                Frequency       = total,
                AvgProcessHours = AvgHours(rows.Select(r => (r.CreatedAt, r.ClosedAt))),
                CloseRate       = total == 0 ? 0 : Math.Round(closed * 100.0 / total, 1),
            };
        }).ToList();

        // ── ByCategory：按异常类型的 type_code 聚合为 5 大业务类别（CategoryOf） ──
        var typeMap = (await _typeRep.AsQueryable()
                .Where(t => (t.TenantId == 0 && t.FactoryId == 0)
                         || (t.TenantId == tenantId && t.FactoryId == factoryId))
                .ToListAsync())
            .GroupBy(t => t.TypeCode)
            .ToDictionary(g => g.Key, g => g.OrderByDescending(x => x.FactoryId).First());

        var byCategory = events
            .Where(e => !string.IsNullOrEmpty(e.ExceptionTypeCode) && typeMap.ContainsKey(e.ExceptionTypeCode!))
            .GroupBy(e => CategoryOf(typeMap[e.ExceptionTypeCode!]))
            .Where(g => !string.IsNullOrEmpty(g.Key))
            .Select(g =>
            {
                var total  = g.Count();
                var closed = g.Count(e => e.Status == "CLOSED");
                return new AdoS8CategorySummary
                {
                    Category        = g.Key!,
                    Total           = total,
                    AvgProcessHours = AvgHours(g.Select(e => (e.CreatedAt, e.ClosedAt))),
                    CloseRate       = total == 0 ? 0 : Math.Round(closed * 100.0 / total, 1),
                };
            })
            .OrderBy(c => CategoryOrder(c.Category))
            .ToList();

        // ── ByDept：按 ResponsibleDeptId 聚合，JOIN AdoS0DepartmentMaster（DepartmentMaster）取部门名称 ──
        // S8-OVERVIEW-DEPT-LABEL-HYDRATION-1：原使用 SysOrg 但其 Id 是雪花，与 dept_id=1/2 不匹配 → 100% fallback；
        // 改用 DepartmentMaster（RecID=1=质量部 / RecID=2=生产部），与 dashboard 部门口径对齐。
        var deptIds = events.Select(e => e.ResponsibleDeptId).Where(id => id > 0).Distinct().ToList();
        var deptNameMap = deptIds.Count == 0
            ? new Dictionary<long, string>()
            : (await _deptRep.AsQueryable()
                    .Where(d => deptIds.Contains(d.Id))
                    .Select(d => new { d.Id, Name = d.Descr ?? d.Department })
                    .ToListAsync())
                .ToDictionary(d => d.Id, d => d.Name ?? string.Empty);

        var byDept = events
            .Where(e => e.ResponsibleDeptId > 0)
            .GroupBy(e => e.ResponsibleDeptId)
            .Select(g =>
            {
                var total  = g.Count();
                var closed = g.Count(e => e.Status == "CLOSED");
                return new AdoS8DeptSummary
                {
                    DeptName        = deptNameMap.TryGetValue(g.Key, out var n) && !string.IsNullOrEmpty(n) ? n : $"部门{g.Key}",
                    Total           = total,
                    AvgProcessHours = AvgHours(g.Select(e => (e.CreatedAt, e.ClosedAt))),
                    CloseRate       = total == 0 ? 0 : Math.Round(closed * 100.0 / total, 1),
                };
            })
            .OrderByDescending(d => d.Total)
            .ToList();

        return new AdoS8OrderGridDto
        {
            Modules    = modules,
            ByCategory = byCategory,
            ByDept     = byDept,
        };
    }

    private static double AvgHours(IEnumerable<(DateTime createdAt, DateTime? closedAt)> items)
    {
        var closed = items.Where(x => x.closedAt.HasValue).ToList();
        if (closed.Count == 0) return 0;
        var avg = closed.Average(x => (x.closedAt!.Value - x.createdAt).TotalHours);
        return Math.Round(avg, 1);
    }

    /// <summary>异常类型 → 业务类别（与 Overview 页 5 张类别卡对应）。
    /// 优先级：配置 (AdoS8ExceptionType.MonitoringCategoryKey → KeyToLabel) → legacy hardcode fallback → string.Empty。
    /// 配置缺失或非法 key 时由 CategoryOfLegacy 兜底，保证演示链路不丢类别卡。</summary>
    private static string CategoryOf(AdoS8ExceptionType? t)
    {
        if (t is null) return string.Empty;
        var configured = S8MonitoringCategory.KeyToLabel(t.MonitoringCategoryKey);
        if (!string.IsNullOrEmpty(configured)) return configured;
        return CategoryOfLegacy(t.TypeCode);
    }

    /// <summary>Legacy hardcode 映射，保留现有 15 条 enabled + 7 条 deprecated 兼容分支。
    /// 不在此处补新映射；新 type_code 通过 monitoring_category_key 配置驱动。</summary>
    private static string CategoryOfLegacy(string? typeCode) => typeCode switch
    {
        // 订单评审
        "ORDER_CHANGE"                              => "订单评审",
        // 总装发货
        "DELIVERY_DELAY"                            => "总装发货",
        "PENDING_SHIPMENT"                          => "总装发货",
        // 本体生产（新基线）
        "EQUIP_FAULT"                               => "本体生产",
        "MFG_MATERIAL_ABNORMAL"                     => "本体生产",
        "MFG_QUALITY_ABNORMAL"                      => "本体生产",
        "PRODUCTION_MATERIAL_ABNORMAL"              => "本体生产",
        "PRODUCTION_QUALITY_ABNORMAL"               => "本体生产",
        "WORK_ORDER_KITTING_ABNORMAL"               => "本体生产",
        "WORK_ORDER_ISSUE_ABNORMAL"                 => "本体生产",
        // 材料采购（新基线）
        "SUPPLIER_ETA_ISSUE"                        => "材料采购",
        "SUPPLIER_SHIP_ISSUE"                       => "材料采购",
        "IQC_ISSUE"                                 => "材料采购",
        "WH_PUTAWAY_ISSUE"                          => "材料采购",
        "WAREHOUSE_RECEIPT_ABNORMAL"                => "材料采购",
        // 旧 7 条 deprecated 仍保留兼容（DB 已迁移；此分支防 Overview 临时回放历史 active）
        "MATERIAL_SHORTAGE"                         => "本体生产",
        "QUALITY_DEFECT"                            => "本体生产",
        "DIMENSION_DEVIATION"                       => "本体生产",
        "YIELD_DEFICIT"                             => "本体生产",
        "WH_KIT_ISSUE"                              => "本体生产",
        "WH_ISSUE_OUT_ISSUE"                        => "本体生产",
        "WH_INBOUND_ISSUE"                          => "材料采购",
        _                                           => string.Empty,
    };

    private static int CategoryOrder(string category) => category switch
    {
        "订单评审" => 1,
        "产品设计" => 2,
        "材料采购" => 3,
        "本体生产" => 4,
        "总装发货" => 5,
        _         => 99,
    };

    /// <summary>
    /// 异常监控汇总：按 module_code 分组统计红/黄/绿/超时数。
    /// 供综合全景页顶部徽标和模块汇总表使用。
    /// </summary>
    public async Task<AdoS8MonitoringSummaryDto> GetSummaryAsync(AdoS8MonitoringSummaryQueryDto q)
    {
        var query = _rep.AsQueryable()
            .Where(e => e.TenantId == q.TenantId && e.FactoryId == q.FactoryId && !e.IsDeleted)
            // S8-DASHBOARD-DATA-ALIGN-S1S7-1：统一统计基准 module_code IN S1-S7。
            .Where(e => S8ModuleCode.All.Contains(e.ModuleCode))
            .WhereIF(!string.IsNullOrWhiteSpace(q.SceneCode), e => e.SceneCode == q.SceneCode)
            .WhereIF(!string.IsNullOrWhiteSpace(q.ModuleCode), e => e.ModuleCode == q.ModuleCode)
            .WhereIF(q.BizDateFrom.HasValue, e => e.CreatedAt >= q.BizDateFrom!.Value)
            .WhereIF(q.BizDateTo.HasValue, e => e.CreatedAt <= q.BizDateTo!.Value);

        // 聚合到内存（数据量在可控范围内，避免复杂 GROUP BY 兼容性问题）
        var raw = await query
            .Select(e => new
            {
                e.ModuleCode,
                e.SceneCode,
                e.Severity,
                e.TimeoutFlag,
                e.Status
            })
            .ToListAsync();

        var byModule = raw
            .GroupBy(e => new { mc = e.ModuleCode ?? string.Empty, sc = e.SceneCode ?? string.Empty })
            .Select(g => new AdoS8ModuleSummaryItem
            {
                ModuleCode  = g.Key.mc,
                ModuleLabel = S8ModuleCode.Label(g.Key.mc),
                SceneCode   = g.Key.sc,
                SceneLabel  = S8SceneCode.Label(g.Key.sc),
                Total   = g.Count(),
                Red     = g.Count(e => e.Severity == "CRITICAL" || e.Severity == "HIGH"),
                Yellow  = g.Count(e => e.Severity == "MEDIUM"),
                Green   = g.Count(e => e.Severity == "LOW"),
                Timeout = g.Count(e => e.TimeoutFlag && e.Status != "CLOSED")
            })
            // 按 S8ModuleCode.All 顺序排列
            .OrderBy(r => Array.IndexOf(S8ModuleCode.All, r.ModuleCode))
            .ToList();

        return new AdoS8MonitoringSummaryDto
        {
            Total   = raw.Count,
            Red     = raw.Count(e => e.Severity == "CRITICAL" || e.Severity == "HIGH"),
            Yellow  = raw.Count(e => e.Severity == "MEDIUM"),
            Green   = raw.Count(e => e.Severity == "LOW"),
            Timeout = raw.Count(e => e.TimeoutFlag && e.Status != "CLOSED"),
            ByModule = byModule
        };
    }

    /// <summary>
    /// S8-DELIVERY-TREND-CHART-REPLACE-DUPLICATE-SECTION-1：Delivery 页近 N 日交付异常趋势。
    /// 口径：module_code IN (S1,S7) AND exception_type_code IN (ORDER_CHANGE / DELIVERY_DELAY / PENDING_SHIPMENT)；
    /// 时间字段 created_at；不排除 CLOSED；按日聚合，缺失日期补 0；days 限 1-30。
    /// </summary>
    public async Task<AdoS8DeliveryTrendDto> GetDeliveryTrendAsync(long tenantId = 1, long factoryId = 1, int days = 7)
    {
        days = Math.Clamp(days, 1, 30);
        var today = DateTime.Today;
        var from = today.AddDays(-(days - 1));
        var toExclusive = today.AddDays(1);

        var deliveryModules = new[] { "S1", "S7" };
        var deliveryTypes = new[] { "ORDER_CHANGE", "DELIVERY_DELAY", "PENDING_SHIPMENT" };

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => deliveryModules.Contains(e.ModuleCode))
            .Where(e => e.ExceptionTypeCode != null && deliveryTypes.Contains(e.ExceptionTypeCode))
            .Where(e => e.CreatedAt >= from && e.CreatedAt < toExclusive)
            .Select(e => new { e.ExceptionTypeCode, e.CreatedAt })
            .ToListAsync();

        var byDate = rows
            .GroupBy(r => r.CreatedAt.Date)
            .ToDictionary(g => g.Key, g => g.ToList());

        var dayList = new List<AdoS8DeliveryTrendDayDto>(days);
        for (var i = 0; i < days; i++)
        {
            var d = from.AddDays(i);
            var bucket = byDate.TryGetValue(d, out var list) ? list : new();
            var oc = bucket.Count(r => r.ExceptionTypeCode == "ORDER_CHANGE");
            var dd = bucket.Count(r => r.ExceptionTypeCode == "DELIVERY_DELAY");
            var ps = bucket.Count(r => r.ExceptionTypeCode == "PENDING_SHIPMENT");
            dayList.Add(new AdoS8DeliveryTrendDayDto
            {
                Date           = d.ToString("MM/dd"),
                RawDate        = d.ToString("yyyy-MM-dd"),
                OrderChange    = oc,
                DeliveryDelay  = dd,
                PendingShipment = ps,
                Total          = oc + dd + ps,
            });
        }

        var totalSum = dayList.Sum(d => d.Total);
        var peak = dayList.OrderByDescending(d => d.Total).FirstOrDefault();
        var todayDay = dayList.Last();
        var yesterday = dayList.Count >= 2 ? dayList[^2] : null;
        double? changeRate = (yesterday is null || yesterday.Total == 0)
            ? (double?)null
            : Math.Round((todayDay.Total - yesterday.Total) * 100.0 / yesterday.Total, 1);

        var summary = new AdoS8DeliveryTrendSummaryDto
        {
            PeakValue       = peak?.Total ?? 0,
            PeakDate        = (peak is null || peak.Total == 0) ? null : peak.RawDate,
            AvgValue        = Math.Round(totalSum / (double)days, 2),
            TodayValue      = todayDay.Total,
            TodayChangeRate = changeRate,
        };

        return new AdoS8DeliveryTrendDto { Days = dayList, Summary = summary };
    }

    /// <summary>
    /// S8-PROD-SUPPLY-TREND-CHART-REPLACE-DUPLICATE-SECTION-1：Production 页近 N 日生产异常趋势。
    /// 口径：module_code IN (S2,S6) AND exception_type_code IN (EQUIP_FAULT / MFG_MATERIAL_ABNORMAL / MFG_QUALITY_ABNORMAL)；
    /// 时间字段 created_at；不排除 CLOSED；按日聚合，缺失日期补 0；days 限 1-30。
    /// </summary>
    public async Task<AdoS8ProductionTrendDto> GetProductionTrendAsync(long tenantId = 1, long factoryId = 1, int days = 7)
    {
        days = Math.Clamp(days, 1, 30);
        var today = DateTime.Today;
        var from = today.AddDays(-(days - 1));
        var toExclusive = today.AddDays(1);

        var prodModules = new[] { "S2", "S6" };
        var prodTypes = new[] { "EQUIP_FAULT", "MFG_MATERIAL_ABNORMAL", "MFG_QUALITY_ABNORMAL" };

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => prodModules.Contains(e.ModuleCode))
            .Where(e => e.ExceptionTypeCode != null && prodTypes.Contains(e.ExceptionTypeCode))
            .Where(e => e.CreatedAt >= from && e.CreatedAt < toExclusive)
            .Select(e => new { e.ExceptionTypeCode, e.CreatedAt })
            .ToListAsync();

        var byDate = rows.GroupBy(r => r.CreatedAt.Date).ToDictionary(g => g.Key, g => g.ToList());

        var dayList = new List<AdoS8ProductionTrendDayDto>(days);
        for (var i = 0; i < days; i++)
        {
            var d = from.AddDays(i);
            var bucket = byDate.TryGetValue(d, out var list) ? list : new();
            var ef = bucket.Count(r => r.ExceptionTypeCode == "EQUIP_FAULT");
            var mf = bucket.Count(r => r.ExceptionTypeCode == "MFG_MATERIAL_ABNORMAL");
            var qf = bucket.Count(r => r.ExceptionTypeCode == "MFG_QUALITY_ABNORMAL");
            dayList.Add(new AdoS8ProductionTrendDayDto
            {
                Date           = d.ToString("MM/dd"),
                RawDate        = d.ToString("yyyy-MM-dd"),
                EquipmentFault = ef,
                MaterialFault  = mf,
                QualityFault   = qf,
                Total          = ef + mf + qf,
            });
        }

        var totalSum = dayList.Sum(d => d.Total);
        var peak = dayList.OrderByDescending(d => d.Total).FirstOrDefault();
        var todayDay = dayList.Last();
        var yesterday = dayList.Count >= 2 ? dayList[^2] : null;
        double? changeRate = (yesterday is null || yesterday.Total == 0)
            ? (double?)null
            : Math.Round((todayDay.Total - yesterday.Total) * 100.0 / yesterday.Total, 1);

        var summary = new AdoS8DeliveryTrendSummaryDto
        {
            PeakValue       = peak?.Total ?? 0,
            PeakDate        = (peak is null || peak.Total == 0) ? null : peak.RawDate,
            AvgValue        = Math.Round(totalSum / (double)days, 2),
            TodayValue      = todayDay.Total,
            TodayChangeRate = changeRate,
        };

        return new AdoS8ProductionTrendDto { Days = dayList, Summary = summary };
    }

    /// <summary>
    /// S8-PROD-SUPPLY-TREND-CHART-REPLACE-DUPLICATE-SECTION-1：Supply 页近 N 日供应异常趋势。
    /// 口径：module_code IN (S3,S4,S5) AND exception_type_code IN 7 类（SUPPLIER_ETA_ISSUE / SUPPLIER_SHIP_ISSUE
    /// / WAREHOUSE_RECEIPT_ABNORMAL / IQC_ISSUE / WH_PUTAWAY_ISSUE / WORK_ORDER_KITTING_ABNORMAL / WORK_ORDER_ISSUE_ABNORMAL）；
    /// 时间字段 created_at；不排除 CLOSED；按日聚合，缺失日期补 0；days 限 1-30。
    /// </summary>
    public async Task<AdoS8SupplyTrendDto> GetSupplyTrendAsync(long tenantId = 1, long factoryId = 1, int days = 7)
    {
        days = Math.Clamp(days, 1, 30);
        var today = DateTime.Today;
        var from = today.AddDays(-(days - 1));
        var toExclusive = today.AddDays(1);

        var supplyModules = new[] { "S3", "S4", "S5" };
        var supplyTypes = new[]
        {
            "SUPPLIER_ETA_ISSUE", "SUPPLIER_SHIP_ISSUE", "WAREHOUSE_RECEIPT_ABNORMAL",
            "IQC_ISSUE", "WH_PUTAWAY_ISSUE", "WORK_ORDER_KITTING_ABNORMAL", "WORK_ORDER_ISSUE_ABNORMAL"
        };

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => supplyModules.Contains(e.ModuleCode))
            .Where(e => e.ExceptionTypeCode != null && supplyTypes.Contains(e.ExceptionTypeCode))
            .Where(e => e.CreatedAt >= from && e.CreatedAt < toExclusive)
            .Select(e => new { e.ExceptionTypeCode, e.CreatedAt })
            .ToListAsync();

        var byDate = rows.GroupBy(r => r.CreatedAt.Date).ToDictionary(g => g.Key, g => g.ToList());

        var dayList = new List<AdoS8SupplyTrendDayDto>(days);
        for (var i = 0; i < days; i++)
        {
            var d = from.AddDays(i);
            var bucket = byDate.TryGetValue(d, out var list) ? list : new();
            var s1 = bucket.Count(r => r.ExceptionTypeCode == "SUPPLIER_ETA_ISSUE");
            var s2 = bucket.Count(r => r.ExceptionTypeCode == "SUPPLIER_SHIP_ISSUE");
            var s3 = bucket.Count(r => r.ExceptionTypeCode == "WAREHOUSE_RECEIPT_ABNORMAL");
            var s4 = bucket.Count(r => r.ExceptionTypeCode == "IQC_ISSUE");
            var s5 = bucket.Count(r => r.ExceptionTypeCode == "WH_PUTAWAY_ISSUE");
            var s6 = bucket.Count(r => r.ExceptionTypeCode == "WORK_ORDER_KITTING_ABNORMAL");
            var s7 = bucket.Count(r => r.ExceptionTypeCode == "WORK_ORDER_ISSUE_ABNORMAL");
            dayList.Add(new AdoS8SupplyTrendDayDto
            {
                Date                       = d.ToString("MM/dd"),
                RawDate                    = d.ToString("yyyy-MM-dd"),
                SupplierEtaIssue           = s1,
                SupplierShipIssue          = s2,
                WarehouseReceiptAbnormal   = s3,
                IqcIssue                   = s4,
                WarehousePutawayIssue      = s5,
                WorkOrderKittingAbnormal   = s6,
                WorkOrderIssueAbnormal     = s7,
                Total                      = s1 + s2 + s3 + s4 + s5 + s6 + s7,
            });
        }

        var totalSum = dayList.Sum(d => d.Total);
        var peak = dayList.OrderByDescending(d => d.Total).FirstOrDefault();
        var todayDay = dayList.Last();
        var yesterday = dayList.Count >= 2 ? dayList[^2] : null;
        double? changeRate = (yesterday is null || yesterday.Total == 0)
            ? (double?)null
            : Math.Round((todayDay.Total - yesterday.Total) * 100.0 / yesterday.Total, 1);

        var summary = new AdoS8DeliveryTrendSummaryDto
        {
            PeakValue       = peak?.Total ?? 0,
            PeakDate        = (peak is null || peak.Total == 0) ? null : peak.RawDate,
            AvgValue        = Math.Round(totalSum / (double)days, 2),
            TodayValue      = todayDay.Total,
            TodayChangeRate = changeRate,
        };

        return new AdoS8SupplyTrendDto { Days = dayList, Summary = summary };
    }
}