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.Entity.S8.OrderFlow;
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;
    // TASK-012-S9-QDC-RATIO-MIGRATION-2：result KPI summary 读取 RATIO 指标字典
    private readonly SqlSugarRepository<AdoS8MonitorMetric> _metricRep;
    // S9-RESULT-KPI-DEMO-BASELINE-AND-ORDER-FLOW-CALC-1：ORDER_FLOW_CYCLE_RATIO 半真实计算依赖订单链路阶段表
    private readonly SqlSugarRepository<AdoS8OrderFlowStage> _orderFlowStageRep;

    public S8MonitoringService(
        SqlSugarRepository<AdoS8Exception> rep,
        SqlSugarRepository<AdoS8ExceptionType> typeRep,
        SqlSugarRepository<AdoS0DepartmentMaster> deptRep,
        SqlSugarRepository<AdoS8MonitorMetric> metricRep,
        SqlSugarRepository<AdoS8OrderFlowStage> orderFlowStageRep)
    {
        _rep = rep;
        _typeRep = typeRep;
        _deptRep = deptRep;
        _metricRep = metricRep;
        _orderFlowStageRep = orderFlowStageRep;
    }

    /// <summary>
    /// 9宫格数据：S1-S7 订单健康分布 + S8业务类别汇总 + S9部门汇总。
    /// 数据来源 ado_s8_exception 聚合；异常表为空时返回全 0（不再返回 Demo 数据）。
    /// </summary>
    public async Task<AdoS8OrderGridDto> GetOrderGridAsync(long tenantId = 1, long factoryId = 1, string? period = null)
    {
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);

        // TASK-002-RESET-DIMENSION-MODEL-DEV-4A：聚合源切到 stage_code（DEV-2A 已与 module_code 对齐）；
        // DTO 字段名保持 ModuleCode 以兼容前端，仅来源字段改为 StageCode。
        var events = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => S8ModuleCode.All.Contains(e.StageCode))
            .WhereIF(periodFrom.HasValue, e => e.CreatedAt >= periodFrom!.Value)
            .WhereIF(periodTo.HasValue,   e => e.CreatedAt < periodTo!.Value)
            .Select(e => new
            {
                ModuleCode = e.StageCode,
                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();
            // S8-SEVERITY-FOLLOW-SERIOUS-STANDARDIZE-EXEC-1：red=SERIOUS / yellow=FOLLOW；
            // green 不再来自异常 severity（异常表无「正常」桶），保留 0 兜底，绿色由 useS8StageConfig 兜底。
            var red    = unclosed.Count(e => S8SeverityCode.IsSerious(e.Severity));
            var yellow = unclosed.Count(e => S8SeverityCode.IsFollow(e.Severity));
            var green  = 0;
            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();
        // S8-DEPT-DISPLAY-CONSISTENCY-1（P0-A-3）：部门水合加 factory_ref_id 约束。
        var deptNameMap = deptIds.Count == 0
            ? new Dictionary<long, string>()
            : (await _deptRep.AsQueryable()
                    .Where(d => deptIds.Contains(d.Id) && d.FactoryRefId == factoryId)
                    .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"                       => "本体生产",
        "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,
    };

    // ── QDC 常量映射（不建表，后端常量驱动） ──────────────────────────────────────
    private static readonly (string Code, string Title, string[] Types, string? Remark)[] QdcGroups =
    {
        ("QUALITY",   "质量异常", new[] { "PURCHASE_QUALITY_ABNORMAL", "MFG_QUALITY_ABNORMAL", "PRODUCTION_QUALITY_ABNORMAL" }, null),
        ("DELIVERY",  "交付异常", new[] { "ORDER_DELIVERY_DELAY_WARNING", "SUPPLIER_DELIVERY_DELAY_WARNING", "ORDER_CHANGE", "DELIVERY_DELAY", "PENDING_SHIPMENT" }, null),
        ("INVENTORY", "库存异常", new[] { "MATERIAL_STOCK_ABNORMAL", "INVENTORY_TURNOVER_ABNORMAL", "INVENTORY_AMOUNT_LEVEL_ABNORMAL" }, null),
        ("COST",      "成本异常", Array.Empty<string>(), "待定义成本异常口径"),
    };

    /// <summary>
    /// S9 QDC 四主线运营聚合：质量 / 交付 / 成本 / 库存。
    /// 成本异常当前无 enabled exception_type，返回空桶。
    /// 及时关闭率依赖 sla_deadline，无 SLA 数据时返回 null。
    /// </summary>
    public async Task<AdoS8QdcSummaryDto> GetQdcSummaryAsync(long tenantId = 1, long factoryId = 1, string? period = null)
    {
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);

        var events = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .WhereIF(periodFrom.HasValue, e => e.CreatedAt >= periodFrom!.Value)
            .WhereIF(periodTo.HasValue,   e => e.CreatedAt < periodTo!.Value)
            .Select(e => new
            {
                e.ExceptionTypeCode,
                e.Status,
                e.CreatedAt,
                e.ClosedAt,
                e.SlaDeadline,
            })
            .ToListAsync();

        var items = new List<AdoS8QdcSummaryItemDto>();
        foreach (var (code, title, types, remark) in QdcGroups)
        {
            if (types.Length == 0)
            {
                items.Add(new AdoS8QdcSummaryItemDto { Code = code, Title = title, Total = 0, Remark = remark });
                continue;
            }

            var rows = events
                .Where(e => e.ExceptionTypeCode != null && types.Contains(e.ExceptionTypeCode))
                .ToList();
            var total      = rows.Count;
            var closedRows = rows.Where(e => e.Status == "CLOSED" && e.ClosedAt.HasValue).ToList();

            double? avgHours = closedRows.Count == 0
                ? null
                : Math.Round(closedRows.Average(e => (e.ClosedAt!.Value - e.CreatedAt).TotalHours), 1);

            double? closeRate = total == 0
                ? null
                : Math.Round(closedRows.Count * 100.0 / total, 1);

            double? onTimeCloseRate = null;
            if (closedRows.Count > 0)
            {
                var closedWithSla = closedRows.Where(e => e.SlaDeadline.HasValue).ToList();
                if (closedWithSla.Count > 0)
                    onTimeCloseRate = Math.Round(
                        closedWithSla.Count(e => e.ClosedAt!.Value <= e.SlaDeadline!.Value) * 100.0 / closedRows.Count, 1);
            }

            items.Add(new AdoS8QdcSummaryItemDto
            {
                Code            = code,
                Title           = title,
                Total           = total,
                AvgProcessHours = avgHours,
                OnTimeCloseRate = onTimeCloseRate,
                CloseRate       = closeRate,
                Remark          = remark,
            });
        }
        return new AdoS8QdcSummaryDto { Items = items };
    }

    /// <summary>
    /// 异常监控汇总：按 module_code 分组统计红/黄/绿/超时数。
    /// 供综合全景页顶部徽标和模块汇总表使用。
    /// </summary>
    public async Task<AdoS8MonitoringSummaryDto> GetSummaryAsync(AdoS8MonitoringSummaryQueryDto q)
    {
        // 显式日期范围优先；未传显式范围时尝试从 period 解析。
        var (periodFrom, periodTo) = (!q.BizDateFrom.HasValue && !q.BizDateTo.HasValue)
            ? S8PeriodHelper.Resolve(q.Period)
            : (q.BizDateFrom, q.BizDateTo);
        var effectiveFrom = q.BizDateFrom ?? periodFrom;
        var effectiveTo   = q.BizDateTo   ?? periodTo;

        // 前端三专题页传入逗号分隔模块码（"S1,S7" / "S2,S6" / "S3,S4,S5"），按 IN 过滤；空值/单值保持原语义。
        var moduleCodes = (q.ModuleCode ?? string.Empty)
            .Split(',', StringSplitOptions.RemoveEmptyEntries)
            .Select(s => s.Trim())
            .Where(s => s.Length > 0)
            .Distinct()
            .ToList();

        var query = _rep.AsQueryable()
            .Where(e => e.TenantId == q.TenantId && e.FactoryId == q.FactoryId && !e.IsDeleted)
            // TASK-002-RESET-DIMENSION-MODEL-DEV-4A：聚合源 module_code → stage_code。
            .Where(e => S8ModuleCode.All.Contains(e.StageCode))
            .WhereIF(!string.IsNullOrWhiteSpace(q.SceneCode), e => e.SceneCode == q.SceneCode)
            .WhereIF(moduleCodes.Count > 0, e => moduleCodes.Contains(e.StageCode))
            .WhereIF(effectiveFrom.HasValue, e => e.CreatedAt >= effectiveFrom!.Value)
            .WhereIF(effectiveTo.HasValue,   e => e.CreatedAt < effectiveTo!.Value);

        // 聚合到内存（数据量在可控范围内，避免复杂 GROUP BY 兼容性问题）
        // S8-SLA-TIMEOUT-RUNTIME-1（P3）：投影 SlaDeadline 替代 TimeoutFlag，timeout 改为运行时计算。
        var raw = await query
            .Select(e => new
            {
                ModuleCode = e.StageCode,
                e.SceneCode,
                e.Severity,
                e.SlaDeadline,
                e.Status
            })
            .ToListAsync();
        var nowForTimeout = DateTime.Now;

        // S8-OVERVIEW-STAGE-CARD-LIFECYCLE-COPY-AND-METRIC-FIX-1：超时口径 = SlaDeadline < now AND status NOT IN (CLOSED, RECOVERED)。
        // 按 severity 拆桶用于 stage card 「严重 N / 延误 M」「关注 N / 延误 M」展示。
        bool IsTimeout(DateTime? sla, string? status) =>
            sla != null && sla < nowForTimeout && status != "CLOSED" && status != "RECOVERED";

        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(),
                // S8-SEVERITY-FOLLOW-SERIOUS-STANDARDIZE-EXEC-1：red=SERIOUS / yellow=FOLLOW；green=0（异常表无正常桶）。
                Red     = g.Count(e => S8SeverityCode.IsSerious(e.Severity)),
                Yellow  = g.Count(e => S8SeverityCode.IsFollow(e.Severity)),
                Green   = 0,
                Timeout = g.Count(e => IsTimeout(e.SlaDeadline, e.Status)),
                SeriousTimeout = g.Count(e => S8SeverityCode.IsSerious(e.Severity) && IsTimeout(e.SlaDeadline, e.Status)),
                FollowTimeout  = g.Count(e => S8SeverityCode.IsFollow(e.Severity)  && IsTimeout(e.SlaDeadline, e.Status)),
            })
            // 按 S8ModuleCode.All 顺序排列
            .OrderBy(r => Array.IndexOf(S8ModuleCode.All, r.ModuleCode))
            .ToList();

        return new AdoS8MonitoringSummaryDto
        {
            Total   = raw.Count,
            // S8-SEVERITY-FOLLOW-SERIOUS-STANDARDIZE-EXEC-1：red=SERIOUS / yellow=FOLLOW；green=0。
            Red     = raw.Count(e => S8SeverityCode.IsSerious(e.Severity)),
            Yellow  = raw.Count(e => S8SeverityCode.IsFollow(e.Severity)),
            Green   = 0,
            Timeout = raw.Count(e => IsTimeout(e.SlaDeadline, e.Status)),
            SeriousTimeout = raw.Count(e => S8SeverityCode.IsSerious(e.Severity) && IsTimeout(e.SlaDeadline, e.Status)),
            FollowTimeout  = raw.Count(e => S8SeverityCode.IsFollow(e.Severity)  && IsTimeout(e.SlaDeadline, e.Status)),
            ByModule = byModule
        };
    }

    /// <summary>
    /// S8-DELIVERY-PAGE-TYPE-PRUNE-AND-TREND-ALIGN-1：Delivery 页近 N 日交付异常趋势。
    /// 口径：module_code IN (S1,S7) AND exception_type_code IN (ORDER_DUE_DATE_DELAY / DELIVERY_DELAY_WARNING)；
    /// 时间字段 created_at；不排除 CLOSED；按日聚合，缺失日期补 0；days 限 1-30；
    /// Total = OrderDueDateDelay + DeliveryDelayWarning（避免历史 bucket.Count 含未承载类型导致 total 与图线不一致）。
    /// </summary>
    public async Task<AdoS8DeliveryTrendDto> GetDeliveryTrendAsync(long tenantId = 1, long factoryId = 1, int days = 7, string? period = null)
    {
        DateTime from, toExclusive;
        int effectiveDays;
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);
        if (periodFrom.HasValue && periodTo.HasValue)
        {
            from = periodFrom.Value;
            toExclusive = periodTo.Value;
            effectiveDays = Math.Max(1, Math.Min(90, (int)(toExclusive - from).TotalDays));
        }
        else
        {
            effectiveDays = Math.Clamp(days, 1, 30);
            var today = DateTime.Today;
            from = today.AddDays(-(effectiveDays - 1));
            toExclusive = today.AddDays(1);
        }

        var deliveryModules = new[] { "S1", "S7" };
        var deliveryTypes = new[] { "ORDER_DUE_DATE_DELAY", "DELIVERY_DELAY_WARNING" };

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => deliveryModules.Contains(e.StageCode))
            .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>(effectiveDays);
        for (var i = 0; i < effectiveDays; i++)
        {
            var d = from.AddDays(i);
            var bucket = byDate.TryGetValue(d, out var list) ? list : new();
            var orderDueDateDelay    = bucket.Count(r => r.ExceptionTypeCode == "ORDER_DUE_DATE_DELAY");
            var deliveryDelayWarning = bucket.Count(r => r.ExceptionTypeCode == "DELIVERY_DELAY_WARNING");
            dayList.Add(new AdoS8DeliveryTrendDayDto
            {
                Date                 = d.ToString("MM/dd"),
                RawDate              = d.ToString("yyyy-MM-dd"),
                OrderDueDateDelay    = orderDueDateDelay,
                DeliveryDelayWarning = deliveryDelayWarning,
                Total                = orderDueDateDelay + deliveryDelayWarning,
            });
        }

        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)effectiveDays, 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, string? period = null)
    {
        DateTime from, toExclusive;
        int effectiveDays;
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);
        if (periodFrom.HasValue && periodTo.HasValue)
        {
            from = periodFrom.Value;
            toExclusive = periodTo.Value;
            effectiveDays = Math.Max(1, Math.Min(90, (int)(toExclusive - from).TotalDays));
        }
        else
        {
            effectiveDays = Math.Clamp(days, 1, 30);
            var today = DateTime.Today;
            from = today.AddDays(-(effectiveDays - 1));
            toExclusive = today.AddDays(1);
        }

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

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => prodModules.Contains(e.StageCode))
            .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>(effectiveDays);
        for (var i = 0; i < effectiveDays; 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          = bucket.Count,
            });
        }

        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)effectiveDays, 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, string? period = null)
    {
        DateTime from, toExclusive;
        int effectiveDays;
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);
        if (periodFrom.HasValue && periodTo.HasValue)
        {
            from = periodFrom.Value;
            toExclusive = periodTo.Value;
            effectiveDays = Math.Max(1, Math.Min(90, (int)(toExclusive - from).TotalDays));
        }
        else
        {
            effectiveDays = Math.Clamp(days, 1, 30);
            var today = DateTime.Today;
            from = today.AddDays(-(effectiveDays - 1));
            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",
            "PURCHASE_EXECUTION_DELAY"
        };

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => supplyModules.Contains(e.StageCode))
            .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>(effectiveDays);
        for (var i = 0; i < effectiveDays; 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                      = bucket.Count,
            });
        }

        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)effectiveDays, 2),
            TodayValue      = todayDay.Total,
            TodayChangeRate = changeRate,
        };

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

    /// <summary>
    /// S8-SIDEBAR-TYPE-CARD-WINDOW-TOGGLE-1：专题页右侧类型卡按 (domain, window) 统一聚合。
    /// 单一时间窗口下 total / open / closed / avgProcessHours / closeRate 共用同一分母。
    /// 公共过滤：tenant/factory + is_deleted=0 + module_code IN S1-S7 + exception_type_code IN domain 类型集 + created_at IN window。
    /// </summary>
    public async Task<AdoS8DomainTypeMetricsDto> GetDomainTypeMetricsAsync(string domain, string window, long tenantId = 1, long factoryId = 1, string? period = null)
    {
        var d = (domain ?? string.Empty).Trim().ToUpperInvariant();
        var w = (window ?? string.Empty).Trim().ToUpperInvariant();
        if (w != "LAST_24H" && w != "LAST_7D") w = "LAST_24H";

        (string key, string label, string typeCode)[] specs = d switch
        {
            // S8-DELIVERY-PAGE-TYPE-PRUNE-AND-TREND-ALIGN-1：Delivery 页类型卡收敛为 2 类；
            // 显示名「总装发货延期」仅作大屏展示，不改 ado_s8_exception_type.type_name。
            "DELIVERY" => new (string, string, string)[]
            {
                ("order-due-date-delay",    "订单交期延期", "ORDER_DUE_DATE_DELAY"),
                ("delivery-delay-warning",  "总装发货延期", "DELIVERY_DELAY_WARNING"),
            },
            "PRODUCTION" => new (string, string, string)[]
            {
                ("equipment-fault",                "设备异常",         "EQUIP_FAULT"),
                ("material-fault",                 "物料异常",         "MFG_MATERIAL_ABNORMAL"),
                ("quality-fault",                  "质量异常",         "MFG_QUALITY_ABNORMAL"),
                ("body-production-delay-warning",  "本体生产延期预警", "BODY_PRODUCTION_DELAY_WARNING"),
            },
            "SUPPLY" => new (string, string, string)[]
            {
                ("supplier-reply-delay",   "供应商回复交期异常", "SUPPLIER_ETA_ISSUE"),
                ("supplier-ship-fault",    "供应商发货异常",     "SUPPLIER_SHIP_ISSUE"),
                ("warehouse-receipt",      "仓库收货异常",       "WAREHOUSE_RECEIPT_ABNORMAL"),
                ("iqc-inspection",         "IQC 检验异常",       "IQC_ISSUE"),
                ("warehouse-shelving",     "仓库上架入库异常",   "WH_PUTAWAY_ISSUE"),
                ("work-order-prepare",     "仓库工单备料异常",   "WORK_ORDER_KITTING_ABNORMAL"),
                ("work-order-issue",       "仓库工单发料异常",   "WORK_ORDER_ISSUE_ABNORMAL"),
                ("purchase-execution-delay", "采购执行延期",     "PURCHASE_EXECUTION_DELAY"),
            },
            _ => Array.Empty<(string, string, string)>(),
        };

        var typeCodes = specs.Select(s => s.typeCode).ToArray();
        if (typeCodes.Length == 0)
        {
            return new AdoS8DomainTypeMetricsDto { Domain = d, Window = w, Total = 0, Items = new() };
        }

        DateTime from, to;
        var (periodFrom, periodTo) = S8PeriodHelper.Resolve(period);
        if (periodFrom.HasValue && periodTo.HasValue)
        {
            from = periodFrom.Value;
            to   = periodTo.Value;
        }
        else if (w == "LAST_7D")
        {
            // 与 trend 接口保持一致：[today-6, today+1)
            var today = DateTime.Today;
            from = today.AddDays(-6);
            to   = today.AddDays(1);
        }
        else
        {
            // LAST_24H：滑动窗口 NOW()-24h ~ NOW()
            var now = DateTime.Now;
            from = now.AddHours(-24);
            to   = now.AddMinutes(1);
        }

        var rows = await _rep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => S8ModuleCode.All.Contains(e.StageCode))
            .Where(e => e.ExceptionTypeCode != null && typeCodes.Contains(e.ExceptionTypeCode))
            .Where(e => e.CreatedAt >= from && e.CreatedAt < to)
            .Select(e => new { e.ExceptionTypeCode, e.Status, e.CreatedAt, e.ClosedAt })
            .ToListAsync();

        var byType = rows.GroupBy(r => r.ExceptionTypeCode!).ToDictionary(g => g.Key, g => g.ToList());

        var items = specs.Select(s =>
        {
            var bucket = byType.TryGetValue(s.typeCode, out var list) ? list : new();
            var total       = bucket.Count;
            var closedCount = bucket.Count(r => r.Status == "CLOSED");
            var openCount   = total - closedCount;
            double? avgHours = null;
            var closedSamples = bucket.Where(r => r.ClosedAt.HasValue).ToList();
            if (closedSamples.Count > 0)
            {
                avgHours = Math.Round(closedSamples.Average(r => (r.ClosedAt!.Value - r.CreatedAt).TotalHours), 1);
            }
            double? closeRate = total == 0 ? null : Math.Round(closedCount * 100.0 / total, 1);
            return new AdoS8DomainTypeMetricItemDto
            {
                Key             = s.key,
                Label           = s.label,
                TypeCode        = s.typeCode,
                Total           = total,
                OpenCount       = openCount,
                ClosedCount     = closedCount,
                AvgProcessHours = avgHours,
                CloseRate       = closeRate,
            };
        }).ToList();

        return new AdoS8DomainTypeMetricsDto
        {
            Domain = d,
            Window = w,
            Total  = items.Sum(i => i.Total),
            Items  = items,
        };
    }

    /// <summary>
    /// S9-RESULT-KPI-DEMO-BASELINE-AND-ORDER-FLOW-CALC-1：A+B 混合方案。
    /// 4 个 KPI（ORDER_DELIVERY_RATE/PO_DELIVERY_RATE/IQC_PASS_RATE/WO_COMPLETION_RATE）→ DEMO_BASELINE 演示基线；
    /// 1 个 KPI（ORDER_FLOW_CYCLE_RATIO）→ ORDER_FLOW_CALC 基于 ado_s8_order_flow_stage 半真实计算；
    /// 不写 DB / 不接 period / 不启用 watch_rule。
    /// 来源：ado_s8_monitor_metric WHERE mechanism='RATIO' AND is_result_kpi=1（保留字典顺序与 metricName）。
    /// </summary>
    public async Task<AdoS8ResultKpiSummaryDto> GetResultKpiSummaryAsync(long tenantId, long factoryId)
    {
        var rows = await _metricRep.AsQueryable()
            .Where(x => x.Mechanism == "RATIO" && x.IsResultKpi
                && ((x.TenantId == 0 && x.FactoryId == 0)
                    || (x.TenantId == tenantId && x.FactoryId == factoryId)))
            .ToListAsync();

        var resolved = rows
            .GroupBy(x => x.MetricCode)
            .Select(g => g.OrderByDescending(x => x.FactoryId).ThenByDescending(x => x.TenantId).First())
            .OrderBy(x => x.SortNo)
            .ThenBy(x => x.MetricCode)
            .ToList();

        var items = new List<AdoS8ResultKpiItemDto>(resolved.Count);
        foreach (var m in resolved)
        {
            var unit = string.IsNullOrWhiteSpace(m.Unit) ? "%" : m.Unit!;
            if (_demoBaselineMap.TryGetValue(m.MetricCode, out var baseline))
            {
                items.Add(new AdoS8ResultKpiItemDto
                {
                    MetricCode = m.MetricCode,
                    MetricName = m.MetricName,
                    ObjectCode = m.ObjectCode,
                    Unit = unit,
                    CurrentValue = baseline.CurrentValue,
                    TargetRatio = baseline.TargetRatio,
                    DictionaryEnabled = true,
                    Remark = baseline.Remark,
                    Source = "DEMO_BASELINE",
                });
            }
            else if (m.MetricCode == "ORDER_FLOW_CYCLE_RATIO")
            {
                var (cv, source) = await ComputeOrderFlowCycleRatioAsync(tenantId, factoryId);
                items.Add(new AdoS8ResultKpiItemDto
                {
                    MetricCode = m.MetricCode,
                    MetricName = m.MetricName,
                    ObjectCode = m.ObjectCode,
                    Unit = unit,
                    CurrentValue = cv,
                    TargetRatio = 90.0m,
                    DictionaryEnabled = true,
                    Remark = "基于已完成订单链路阶段 actual_days/planned_days 计算，pending 阶段暂不纳入",
                    Source = source,
                });
            }
            else
            {
                items.Add(new AdoS8ResultKpiItemDto
                {
                    MetricCode = m.MetricCode,
                    MetricName = m.MetricName,
                    ObjectCode = m.ObjectCode,
                    Unit = unit,
                    CurrentValue = null,
                    TargetRatio = m.DefaultTargetRatio,
                    DictionaryEnabled = false,
                    Remark = m.Remark,
                    Source = "PENDING_REAL",
                });
            }
        }

        return new AdoS8ResultKpiSummaryDto
        {
            Items = items,
            Source = "MIXED_BASELINE",
        };
    }

    private static readonly Dictionary<string, (decimal CurrentValue, decimal TargetRatio, string Remark)> _demoBaselineMap = new()
    {
        ["ORDER_DELIVERY_RATE"] = (92.0m, 95.0m, "演示基线，待真实订单交付口径接入"),
        ["PO_DELIVERY_RATE"]    = (88.0m, 95.0m, "演示基线，待采购到货真实表接入"),
        ["IQC_PASS_RATE"]       = (96.0m, 98.0m, "演示基线，待 IQC 检验真实表接入"),
        ["WO_COMPLETION_RATE"]  = (90.0m, 95.0m, "演示基线，待工单完工真实表接入"),
    };

    /// <summary>
    /// 候选 A：actual_days IS NOT NULL 的已完成阶段中 actual_days <= planned_days 的占比。
    /// pending 阶段不纳入 denominator；不依赖 status 字段；按 tenant/factory 过滤。
    /// </summary>
    private async Task<(decimal? CurrentValue, string Source)> ComputeOrderFlowCycleRatioAsync(long tenantId, long factoryId)
    {
        try
        {
            var stages = await _orderFlowStageRep.AsQueryable()
                .Where(x => !x.IsDeleted && x.TenantId == tenantId && x.FactoryId == factoryId
                    && x.ActualDays != null)
                .Select(x => new { x.ActualDays, x.PlannedDays })
                .ToListAsync();

            var denominator = stages.Count;
            if (denominator == 0) return (null, "ORDER_FLOW_CALC_EMPTY");

            var numerator = stages.Count(s => s.ActualDays!.Value <= s.PlannedDays);
            var value = Math.Round(numerator * 100m / denominator, 1);
            return (value, "ORDER_FLOW_CALC");
        }
        catch
        {
            return (null, "ORDER_FLOW_CALC_EMPTY");
        }
    }
}