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

using System.Text.Json;
using Admin.NET.Plugin.AiDOP.Dto.S8.OrderFlow;
using Admin.NET.Plugin.AiDOP.Entity.S8;
using Admin.NET.Plugin.AiDOP.Entity.S8.OrderFlow;

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

/// <summary>
/// ORDER-FLOW-S8-INTEGRATED-DOMAIN-RESET-1 t3b：S8 订单执行链路只读 service skeleton。
/// 仅 GetOrders / GetOrder / GetChain 三个方法；procurement-pivot / aggregate / related-exceptions 延后切片。
/// 任何业务真值（订单总数 / 平均时长 / YY 真值矩阵等）只能来自 DB，本文件零硬编码。
/// 异常计数：从 ado_s8_exception 实时聚合（SourceObjectType=SALES_ORDER + RelatedObjectCode in order_codes + IsDeleted=0）。
/// </summary>
public class S8OrderFlowService : ITransient
{
    private const string ExceptionSourceObjectType = "SALES_ORDER";
    private const string ScopeBaselinePpt = "BASELINE_PPT";
    private const string ScopeCurrentFiltered = "CURRENT_FILTERED";
    private const string BaselineSnapshotCode = "CHAIN_AGGREGATE_BASELINE";
    private const string PivotTotalCode = "TOTAL";

    // ORDER-CHAIN-FINAL-ASSEMBLY-DEPT-COLLAB-MVP-1：异常类型 → 末端协同对象 映射常量。
    // 这些 type_code 均来自 ado_s8_exception_type seed 中 enabled=true、monitoring_category_key=FINAL_ASSEMBLY_DELIVERY 的子集；
    // 不新增异常类型、不依赖业务执行表。空数组对象（如 ASSEMBLY_COMPLETION / 包装材料准备）在前端展示 "--"。
    private const string FinalAssemblyObjectGoodsHandover = "GOODS_HANDOVER";
    private const string FinalAssemblyObjectShipmentConfirmation = "SHIPMENT_CONFIRMATION";
    private const string FinalAssemblyObjectShippingPlan = "SHIPPING_PLAN";
    private static readonly string[] GoodsHandoverExceptionTypes = { "PENDING_SHIPMENT" };
    private static readonly string[] ShipmentConfirmationExceptionTypes =
    {
        "DELIVERY_DELAY", "SHIPMENT_ABNORMAL", "DELIVERY_DELAY_WARNING", "FINISHED_GOODS_PENDING_SHIPMENT",
    };
    private static readonly string[] ShippingPlanExceptionTypes = { "FINISHED_GOODS_PENDING_SHIPMENT" };

    private static readonly JsonSerializerOptions JsonOpts = new()
    {
        PropertyNameCaseInsensitive = true,
    };

    private readonly SqlSugarRepository<AdoS8OrderFlowOrder> _orderRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowStage> _stageRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowSubstep> _substepRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowSubstepUnit> _unitRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowSnapshot> _snapshotRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowProcurementPivot> _pivotRep;
    private readonly SqlSugarRepository<AdoS8Exception> _exceptionRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowProductDesignDrawing> _pddRep;
    // S8-ORDER-CHAIN-BODY-PRODUCTION-ORDER-LEVEL-SEED-FIX-1：本体生产 3 表 repository。
    private readonly SqlSugarRepository<AdoS8OrderFlowManufacturingProcess> _mfgProcessRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowManufacturingLossFactor> _mfgLossRep;
    private readonly SqlSugarRepository<AdoS8OrderFlowManufacturingOperator> _mfgOperatorRep;

    public S8OrderFlowService(
        SqlSugarRepository<AdoS8OrderFlowOrder> orderRep,
        SqlSugarRepository<AdoS8OrderFlowStage> stageRep,
        SqlSugarRepository<AdoS8OrderFlowSubstep> substepRep,
        SqlSugarRepository<AdoS8OrderFlowSubstepUnit> unitRep,
        SqlSugarRepository<AdoS8OrderFlowSnapshot> snapshotRep,
        SqlSugarRepository<AdoS8OrderFlowProcurementPivot> pivotRep,
        SqlSugarRepository<AdoS8Exception> exceptionRep,
        SqlSugarRepository<AdoS8OrderFlowProductDesignDrawing> pddRep,
        SqlSugarRepository<AdoS8OrderFlowManufacturingProcess> mfgProcessRep,
        SqlSugarRepository<AdoS8OrderFlowManufacturingLossFactor> mfgLossRep,
        SqlSugarRepository<AdoS8OrderFlowManufacturingOperator> mfgOperatorRep)
    {
        _orderRep = orderRep;
        _stageRep = stageRep;
        _substepRep = substepRep;
        _unitRep = unitRep;
        _snapshotRep = snapshotRep;
        _pivotRep = pivotRep;
        _exceptionRep = exceptionRep;
        _pddRep = pddRep;
        _mfgProcessRep = mfgProcessRep;
        _mfgLossRep = mfgLossRep;
        _mfgOperatorRep = mfgOperatorRep;
    }

    /// <summary>订单档案列表（无分页）。当前 baseline 20 单。</summary>
    public async Task<List<AdoS8OrderFlowOrderListItemDto>> GetOrdersAsync(AdoS8OrderFlowOrderQueryDto query)
    {
        var tenantId = query.TenantId ?? 1;
        var factoryId = query.FactoryId ?? 1;

        var orders = await _orderRep.AsQueryable()
            .Where(o => o.TenantId == tenantId && o.FactoryId == factoryId && !o.IsDeleted)
            .WhereIF(!string.IsNullOrWhiteSpace(query.Keyword),
                o => o.OrderCode.Contains(query.Keyword!)
                     || o.ProductName.Contains(query.Keyword!)
                     || o.CustomerName.Contains(query.Keyword!))
            .WhereIF(!string.IsNullOrWhiteSpace(query.CurrentFlowCode),
                o => o.CurrentOrderFlowCode == query.CurrentFlowCode!)
            .WhereIF(!string.IsNullOrWhiteSpace(query.CustomerCode),
                o => o.CustomerCode == query.CustomerCode!)
            .WhereIF(!string.IsNullOrWhiteSpace(query.ProductLine),
                o => o.ProductLine == query.ProductLine!)
            .WhereIF(!string.IsNullOrWhiteSpace(query.Region),
                o => o.Region == query.Region!)
            .WhereIF(!string.IsNullOrWhiteSpace(query.WorkflowStatus),
                o => o.WorkflowStatus == query.WorkflowStatus!)
            .WhereIF(!string.IsNullOrWhiteSpace(query.ScenarioCode),
                o => o.ScenarioCode == query.ScenarioCode!)
            .OrderBy(o => o.OrderCode)
            .ToListAsync();

        if (orders.Count == 0) return new List<AdoS8OrderFlowOrderListItemDto>();

        var orderCodes = orders.Select(o => o.OrderCode).Distinct().ToList();
        var orderIds = orders.Select(o => o.Id).ToList();

        var currentStages = await _stageRep.AsQueryable()
            .Where(s => s.TenantId == tenantId && s.FactoryId == factoryId && !s.IsDeleted)
            .Where(s => orderIds.Contains(s.OrderId))
            .ToListAsync();
        var currentStageMap = currentStages
            .GroupBy(s => s.OrderId)
            .ToDictionary(g => g.Key, g => g.ToList());

        var exceptionCounts = await MapExceptionCountsAsync(tenantId, factoryId, orderCodes);

        return orders.Select(o => MapOrderListItem(o, currentStageMap.GetValueOrDefault(o.Id), exceptionCounts)).ToList();
    }

    /// <summary>单订单详情（不含 substep/unit）。</summary>
    public async Task<AdoS8OrderFlowOrderDetailDto?> GetOrderAsync(string orderCode, long tenantId = 1, long factoryId = 1)
    {
        if (string.IsNullOrWhiteSpace(orderCode)) return null;

        var order = await _orderRep.AsQueryable()
            .Where(o => o.TenantId == tenantId && o.FactoryId == factoryId
                     && !o.IsDeleted && o.OrderCode == orderCode)
            .FirstAsync();
        if (order == null) return null;

        var stages = await _stageRep.AsQueryable()
            .Where(s => s.TenantId == tenantId && s.FactoryId == factoryId
                     && !s.IsDeleted && s.OrderId == order.Id)
            .OrderBy(s => s.SortNo)
            .ToListAsync();

        var exceptionCounts = await MapExceptionCountsAsync(tenantId, factoryId, new List<string> { orderCode });
        var listItem = MapOrderListItem(order, stages, exceptionCounts);
        return BuildDetail(listItem, stages, substepsByFlow: null, unitsBySubstepId: null);
    }

    /// <summary>订单链路视图：详情 + lifecycle + L2 substeps + L3 units。procurementPivot 留到 t3c。</summary>
    public async Task<AdoS8OrderFlowChainDto?> GetChainAsync(string orderCode, long tenantId = 1, long factoryId = 1)
    {
        if (string.IsNullOrWhiteSpace(orderCode)) return null;

        var order = await _orderRep.AsQueryable()
            .Where(o => o.TenantId == tenantId && o.FactoryId == factoryId
                     && !o.IsDeleted && o.OrderCode == orderCode)
            .FirstAsync();
        if (order == null) return null;

        var stages = await _stageRep.AsQueryable()
            .Where(s => s.TenantId == tenantId && s.FactoryId == factoryId
                     && !s.IsDeleted && s.OrderId == order.Id)
            .OrderBy(s => s.SortNo)
            .ToListAsync();

        var substeps = await _substepRep.AsQueryable()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId
                     && !x.IsDeleted && x.OrderId == order.Id)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var substepsByFlow = substeps
            .GroupBy(x => x.OrderFlowCode)
            .ToDictionary(g => g.Key, g => g.ToList());

        var substepIds = substeps.Select(x => x.Id).ToList();
        var units = substepIds.Count == 0
            ? new List<AdoS8OrderFlowSubstepUnit>()
            : await _unitRep.AsQueryable()
                .Where(u => u.TenantId == tenantId && u.FactoryId == factoryId
                         && !u.IsDeleted && substepIds.Contains(u.SubstepId))
                .OrderBy(u => u.SortNo)
                .ToListAsync();
        var unitsBySubstepId = units
            .GroupBy(u => u.SubstepId)
            .ToDictionary(g => g.Key, g => g.ToList());

        var exceptionCounts = await MapExceptionCountsAsync(tenantId, factoryId, new List<string> { orderCode });
        var listItem = MapOrderListItem(order, stages, exceptionCounts);
        var detail = BuildDetail(listItem, stages, substepsByFlow, unitsBySubstepId);

        return new AdoS8OrderFlowChainDto
        {
            Order = detail,
            ProcurementPivot = null, // t3c 切片接入
        };
    }

    // ─────────────── private mappers ───────────────

    /// <summary>从 ado_s8_exception 实时聚合每个 order_code 的异常计数 + 状态。</summary>
    private async Task<Dictionary<string, (int Count, string Status)>> MapExceptionCountsAsync(
        long tenantId, long factoryId, List<string> orderCodes)
    {
        var result = orderCodes.ToDictionary(c => c, _ => (Count: 0, Status: string.Empty));
        if (orderCodes.Count == 0) return result;

        var rows = await _exceptionRep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => e.SourceObjectType == ExceptionSourceObjectType)
            .Where(e => orderCodes.Contains(e.RelatedObjectCode!))
            .Select(e => new { e.RelatedObjectCode, e.Severity })
            .ToListAsync();

        foreach (var grp in rows.Where(r => !string.IsNullOrEmpty(r.RelatedObjectCode))
                                .GroupBy(r => r.RelatedObjectCode!))
        {
            var items = grp.ToList();
            var status = items.Any(x => string.Equals(x.Severity, "SERIOUS", StringComparison.OrdinalIgnoreCase))
                ? "SERIOUS"
                : (items.Count > 0 ? "FOLLOW" : string.Empty);
            result[grp.Key] = (items.Count, status);
        }

        return result;
    }

    private static AdoS8OrderFlowOrderListItemDto MapOrderListItem(
        AdoS8OrderFlowOrder o,
        List<AdoS8OrderFlowStage>? lifecycle,
        Dictionary<string, (int Count, string Status)> exceptionCounts)
    {
        var current = lifecycle?.FirstOrDefault(s => s.OrderFlowCode == o.CurrentOrderFlowCode);
        var counts = exceptionCounts.TryGetValue(o.OrderCode, out var c) ? c : (Count: 0, Status: string.Empty);

        return new AdoS8OrderFlowOrderListItemDto
        {
            OrderCode = o.OrderCode,
            ProductName = o.ProductName,
            ProductLine = o.ProductLine,
            CustomerCode = o.CustomerCode,
            CustomerName = o.CustomerName,
            CustomerType = o.CustomerType,
            Region = o.Region,
            Priority = o.Priority,
            WorkflowStatus = o.WorkflowStatus,
            CurrentOrderFlowCode = o.CurrentOrderFlowCode,
            CurrentOrderFlowName = OrderFlowConstants.ResolveName(o.CurrentOrderFlowCode),
            CurrentStatus = current?.Status ?? string.Empty,
            ReleaseAt = o.ReleaseAt,
            TargetCycleDays = o.TargetCycleDays,
            ActualCycleDays = o.ActualCycleDays,
            CurrentCycleDays = current?.ActualDays,
            NodeVarianceDays = current?.NodeVarianceDays,
            CumulativeVarianceDays = current?.CumulativeVarianceDays,
            ExceptionCount = counts.Count,
            ExceptionStatus = counts.Status,
            ResponseMinutes = o.ResponseMinutes,
            ProcessingMinutes = o.ProcessingMinutes,
            TotalLossMinutes = o.TotalLossMinutes,
            ScenarioCode = o.ScenarioCode,
            DataSource = o.DataSource,
        };
    }

    private static AdoS8OrderFlowOrderDetailDto BuildDetail(
        AdoS8OrderFlowOrderListItemDto listItem,
        List<AdoS8OrderFlowStage> stages,
        Dictionary<string, List<AdoS8OrderFlowSubstep>>? substepsByFlow,
        Dictionary<long, List<AdoS8OrderFlowSubstepUnit>>? unitsBySubstepId)
    {
        var detail = new AdoS8OrderFlowOrderDetailDto
        {
            OrderCode = listItem.OrderCode,
            ProductName = listItem.ProductName,
            ProductLine = listItem.ProductLine,
            CustomerCode = listItem.CustomerCode,
            CustomerName = listItem.CustomerName,
            CustomerType = listItem.CustomerType,
            Region = listItem.Region,
            Priority = listItem.Priority,
            WorkflowStatus = listItem.WorkflowStatus,
            CurrentOrderFlowCode = listItem.CurrentOrderFlowCode,
            CurrentOrderFlowName = listItem.CurrentOrderFlowName,
            CurrentStatus = listItem.CurrentStatus,
            ReleaseAt = listItem.ReleaseAt,
            TargetCycleDays = listItem.TargetCycleDays,
            ActualCycleDays = listItem.ActualCycleDays,
            CurrentCycleDays = listItem.CurrentCycleDays,
            NodeVarianceDays = listItem.NodeVarianceDays,
            CumulativeVarianceDays = listItem.CumulativeVarianceDays,
            ExceptionCount = listItem.ExceptionCount,
            ExceptionStatus = listItem.ExceptionStatus,
            ResponseMinutes = listItem.ResponseMinutes,
            ProcessingMinutes = listItem.ProcessingMinutes,
            TotalLossMinutes = listItem.TotalLossMinutes,
            ScenarioCode = listItem.ScenarioCode,
            DataSource = listItem.DataSource,
            Lifecycle = stages.Select(s => MapStage(s, substepsByFlow, unitsBySubstepId)).ToList(),
        };
        return detail;
    }

    private static AdoS8OrderFlowStageDto MapStage(
        AdoS8OrderFlowStage s,
        Dictionary<string, List<AdoS8OrderFlowSubstep>>? substepsByFlow,
        Dictionary<long, List<AdoS8OrderFlowSubstepUnit>>? unitsBySubstepId)
    {
        var substeps = substepsByFlow != null && substepsByFlow.TryGetValue(s.OrderFlowCode, out var list)
            ? list.Select(x => MapSubstep(x, unitsBySubstepId)).ToList()
            : new List<AdoS8OrderFlowSubstepDto>();

        return new AdoS8OrderFlowStageDto
        {
            OrderFlowCode = s.OrderFlowCode,
            OrderFlowName = string.IsNullOrEmpty(s.OrderFlowName)
                ? OrderFlowConstants.ResolveName(s.OrderFlowCode)
                : s.OrderFlowName,
            OwnerDept = s.OwnerDept,
            SortNo = s.SortNo,
            PlannedDays = s.PlannedDays,
            ActualDays = s.ActualDays,
            TargetAt = s.TargetAt,
            ActualStartAt = s.ActualStartAt,
            ActualEndAt = s.ActualEndAt,
            Status = s.Status,
            NodeVarianceDays = s.NodeVarianceDays,
            CumulativeVarianceDays = s.CumulativeVarianceDays,
            Substeps = substeps,
        };
    }

    private static AdoS8OrderFlowSubstepDto MapSubstep(
        AdoS8OrderFlowSubstep x,
        Dictionary<long, List<AdoS8OrderFlowSubstepUnit>>? unitsBySubstepId)
    {
        var units = unitsBySubstepId != null && unitsBySubstepId.TryGetValue(x.Id, out var list)
            ? list.Select(MapUnit).ToList()
            : new List<AdoS8OrderFlowSubstepUnitDto>();

        return new AdoS8OrderFlowSubstepDto
        {
            SubstepCode = x.SubstepCode,
            SubstepName = x.SubstepName,
            PiHours = x.PiHours,
            ActualHours = x.ActualHours,
            Status = x.Status,
            SortNo = x.SortNo,
            Units = units,
        };
    }

    private static AdoS8OrderFlowSubstepUnitDto MapUnit(AdoS8OrderFlowSubstepUnit u)
        => new()
        {
            UnitCode = u.UnitCode,
            UnitName = u.UnitName,
            PiHours = u.PiHours,
            ActualHours = u.ActualHours,
            Status = u.Status,
            SortNo = u.SortNo,
        };

    // ──────────────────────────────────────────────────────────────────────
    // t3c：Aggregate + ProcurementPivot
    // ──────────────────────────────────────────────────────────────────────

    /// <summary>
    /// 链路聚合视图：
    ///   scope=BASELINE_PPT      → 只读 ado_s8_order_flow_snapshot.stage_snapshots_json
    ///   scope=CURRENT_FILTERED  → 从 order + stage 实时聚合
    /// snapshot 缺失时返回空骨架（不做 fallback 硬编码）。
    /// </summary>
    public async Task<AdoS8OrderFlowAggregateDto> GetAggregateAsync(AdoS8OrderFlowAggregateQueryDto query)
    {
        var tenantId = query.TenantId ?? 1;
        var factoryId = query.FactoryId ?? 1;
        var scope = string.IsNullOrWhiteSpace(query.Scope) ? ScopeBaselinePpt : query.Scope;

        if (string.Equals(scope, ScopeBaselinePpt, StringComparison.OrdinalIgnoreCase))
            return await BuildBaselineAggregateAsync(tenantId, factoryId, scope);

        if (string.Equals(scope, ScopeCurrentFiltered, StringComparison.OrdinalIgnoreCase))
            return await BuildCurrentAggregateAsync(tenantId, factoryId, scope, query.OrderCodes);

        return EmptyAggregate(scope);
    }

    private async Task<AdoS8OrderFlowAggregateDto> BuildBaselineAggregateAsync(long tenantId, long factoryId, string scope)
    {
        var snapshot = await _snapshotRep.AsQueryable()
            .Where(s => s.TenantId == tenantId && s.FactoryId == factoryId && !s.IsDeleted)
            .Where(s => s.ScopeCode == ScopeBaselinePpt && s.SnapshotCode == BaselineSnapshotCode)
            .FirstAsync();

        if (snapshot == null) return EmptyAggregate(scope);

        var stages = ParseStageSnapshots(snapshot.StageSnapshotsJson);
        var collab = await BuildFinalAssemblyCollabSummaryAsync(tenantId, factoryId, orderCodes: null);
        return new AdoS8OrderFlowAggregateDto
        {
            Scope = scope,
            TotalOrders = snapshot.TotalOrders,
            TotalCustomers = snapshot.TotalCustomers,
            AvgResponseMinutes = snapshot.AvgResponseMinutes,
            AvgProcessingMinutes = snapshot.AvgProcessingMinutes,
            AvgLossMinutes = snapshot.AvgLossMinutes,
            StageAggregates = ReorderByCanonical(stages),
            FinalAssemblyCollabSummary = collab,
        };
    }

    private async Task<AdoS8OrderFlowAggregateDto> BuildCurrentAggregateAsync(
        long tenantId, long factoryId, string scope, List<string>? orderCodes)
    {
        var orders = await _orderRep.AsQueryable()
            .Where(o => o.TenantId == tenantId && o.FactoryId == factoryId && !o.IsDeleted)
            .WhereIF(orderCodes != null && orderCodes.Count > 0, o => orderCodes!.Contains(o.OrderCode))
            .ToListAsync();

        if (orders.Count == 0) return EmptyAggregate(scope);

        var orderIds = orders.Select(o => o.Id).ToList();
        var stages = await _stageRep.AsQueryable()
            .Where(s => s.TenantId == tenantId && s.FactoryId == factoryId && !s.IsDeleted)
            .Where(s => orderIds.Contains(s.OrderId))
            .ToListAsync();

        var responseList = orders.Where(o => o.ResponseMinutes.HasValue).Select(o => (decimal)o.ResponseMinutes!.Value).ToList();
        var processingList = orders.Where(o => o.ProcessingMinutes.HasValue).Select(o => (decimal)o.ProcessingMinutes!.Value).ToList();
        var lossList = orders.Where(o => o.TotalLossMinutes.HasValue).Select(o => (decimal)o.TotalLossMinutes!.Value).ToList();

        var stagesByFlow = stages.GroupBy(s => s.OrderFlowCode).ToDictionary(g => g.Key, g => g.ToList());
        var stageAggregates = new List<AdoS8OrderFlowStageAggregateDto>();
        foreach (var code in OrderFlowConstants.All)
        {
            stagesByFlow.TryGetValue(code, out var rows);
            stageAggregates.Add(BuildStageAggregateFromRows(code, rows ?? new List<AdoS8OrderFlowStage>()));
        }

        var collab = await BuildFinalAssemblyCollabSummaryAsync(tenantId, factoryId, orderCodes);
        return new AdoS8OrderFlowAggregateDto
        {
            Scope = scope,
            TotalOrders = orders.Count,
            TotalCustomers = orders.Select(o => o.CustomerCode).Where(c => !string.IsNullOrEmpty(c)).Distinct().Count(),
            AvgResponseMinutes = AvgOrZero(responseList),
            AvgProcessingMinutes = AvgOrZero(processingList),
            AvgLossMinutes = AvgOrZero(lossList),
            StageAggregates = stageAggregates,
            FinalAssemblyCollabSummary = collab,
        };
    }

    // ORDER-CHAIN-FINAL-ASSEMBLY-DEPT-COLLAB-MVP-1：FINAL_ASSEMBLY_DELIVERY 末端协同派生。
    // 仅查 ado_s8_exception 现有数据；只覆盖有真实映射的 2 个主门禁 + 1 个并行准备，其它 6 个对象前端展示 "--"。
    // 不读取 S0|S4 业务表；不新增表 / seed / 异常类型。
    private async Task<AdoS8FinalAssemblyCollabSummaryDto> BuildFinalAssemblyCollabSummaryAsync(
        long tenantId, long factoryId, List<string>? orderCodes)
    {
        var allTypes = GoodsHandoverExceptionTypes
            .Concat(ShipmentConfirmationExceptionTypes)
            .Concat(ShippingPlanExceptionTypes)
            .Distinct()
            .ToList();

        var rows = await _exceptionRep.AsQueryable()
            .Where(e => e.TenantId == tenantId && e.FactoryId == factoryId && !e.IsDeleted)
            .Where(e => e.SourceObjectType == ExceptionSourceObjectType)
            .Where(e => e.ExceptionTypeCode != null && allTypes.Contains(e.ExceptionTypeCode!))
            .WhereIF(orderCodes != null && orderCodes.Count > 0,
                e => e.RelatedObjectCode != null && orderCodes!.Contains(e.RelatedObjectCode!))
            .Select(e => new ExceptionAttribution
            {
                ExceptionTypeCode = e.ExceptionTypeCode,
                RelatedObjectCode = e.RelatedObjectCode,
                Title = e.Title,
                Severity = e.Severity,
                CreatedAt = e.CreatedAt,
            })
            .ToListAsync();

        return new AdoS8FinalAssemblyCollabSummaryDto
        {
            Gates = new Dictionary<string, AdoS8FinalAssemblyObjectStatDto>
            {
                [FinalAssemblyObjectGoodsHandover] = BuildObjectStat(rows, GoodsHandoverExceptionTypes, isGate: true),
                [FinalAssemblyObjectShipmentConfirmation] = BuildObjectStat(rows, ShipmentConfirmationExceptionTypes, isGate: true),
            },
            Parallels = new Dictionary<string, AdoS8FinalAssemblyObjectStatDto>
            {
                [FinalAssemblyObjectShippingPlan] = BuildObjectStat(rows, ShippingPlanExceptionTypes, isGate: false),
            },
        };
    }

    private static AdoS8FinalAssemblyObjectStatDto BuildObjectStat(
        List<ExceptionAttribution> rows, string[] candidateTypes, bool isGate)
    {
        var subset = rows.Where(r => r.ExceptionTypeCode != null && candidateTypes.Contains(r.ExceptionTypeCode!)).ToList();
        var count = subset
            .Where(r => !string.IsNullOrEmpty(r.RelatedObjectCode))
            .Select(r => r.RelatedObjectCode!)
            .Distinct()
            .Count();
        var top = subset
            .OrderByDescending(r => string.Equals(r.Severity, "SERIOUS", StringComparison.OrdinalIgnoreCase) ? 1 : 0)
            .ThenByDescending(r => r.CreatedAt)
            .FirstOrDefault();
        return new AdoS8FinalAssemblyObjectStatDto
        {
            ImpactedOrderCount = isGate ? count : (int?)null,
            RiskOrderCount = isGate ? (int?)null : count,
            TopRiskTitle = top?.Title,
            TopRiskSeverity = top?.Severity,
        };
    }

    private sealed class ExceptionAttribution
    {
        public string? ExceptionTypeCode { get; set; }
        public string? RelatedObjectCode { get; set; }
        public string? Title { get; set; }
        public string? Severity { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    private static AdoS8OrderFlowStageAggregateDto BuildStageAggregateFromRows(string flowCode, List<AdoS8OrderFlowStage> rows)
    {
        var dto = new AdoS8OrderFlowStageAggregateDto
        {
            OrderFlowCode = flowCode,
            OrderFlowName = OrderFlowConstants.ResolveName(flowCode),
        };
        if (rows.Count == 0) return dto;

        var plannedList = rows.Select(r => r.PlannedDays).ToList();
        var actualList = rows.Where(r => r.ActualDays.HasValue && !string.Equals(r.Status, "pending", StringComparison.OrdinalIgnoreCase))
                             .Select(r => r.ActualDays!.Value).ToList();
        var green = rows.Count(r => string.Equals(r.Status, "green", StringComparison.OrdinalIgnoreCase));
        var yellow = rows.Count(r => string.Equals(r.Status, "yellow", StringComparison.OrdinalIgnoreCase));
        var red = rows.Count(r => string.Equals(r.Status, "red", StringComparison.OrdinalIgnoreCase));
        var pending = rows.Count(r => string.Equals(r.Status, "pending", StringComparison.OrdinalIgnoreCase));
        var total = rows.Count;

        dto.KpiAvgDays = AvgOrZero(plannedList);
        dto.ActualAvgDays = AvgOrZero(actualList);
        dto.Green = green;
        dto.Yellow = yellow;
        dto.Red = red;
        dto.Pending = pending;
        // 整数百分比；total 不含 0 因为上面已 short-circuit。 percent 因子 100 是公式系数，非业务真值。
        dto.OnTimeRate = total > 0 ? (int)Math.Round(green * 100.0 / total) : 0;
        return dto;
    }

    private static List<AdoS8OrderFlowStageAggregateDto> ParseStageSnapshots(string? json)
    {
        if (string.IsNullOrWhiteSpace(json)) return new List<AdoS8OrderFlowStageAggregateDto>();
        try
        {
            var parsed = JsonSerializer.Deserialize<List<AdoS8OrderFlowStageAggregateDto>>(json, JsonOpts);
            return parsed ?? new List<AdoS8OrderFlowStageAggregateDto>();
        }
        catch (JsonException)
        {
            return new List<AdoS8OrderFlowStageAggregateDto>();
        }
    }

    /// <summary>按 OrderFlowConstants.All 重排；缺失 code 补空骨架；多余 code 追加在尾部以避免静默丢数据。</summary>
    private static List<AdoS8OrderFlowStageAggregateDto> ReorderByCanonical(List<AdoS8OrderFlowStageAggregateDto> input)
    {
        var byCode = input.Where(x => !string.IsNullOrEmpty(x.OrderFlowCode))
                          .GroupBy(x => x.OrderFlowCode)
                          .ToDictionary(g => g.Key, g => g.First());
        var ordered = new List<AdoS8OrderFlowStageAggregateDto>();
        foreach (var code in OrderFlowConstants.All)
        {
            if (byCode.TryGetValue(code, out var hit))
            {
                if (string.IsNullOrEmpty(hit.OrderFlowName))
                    hit.OrderFlowName = OrderFlowConstants.ResolveName(code);
                ordered.Add(hit);
                byCode.Remove(code);
            }
            else
            {
                ordered.Add(new AdoS8OrderFlowStageAggregateDto
                {
                    OrderFlowCode = code,
                    OrderFlowName = OrderFlowConstants.ResolveName(code),
                });
            }
        }
        ordered.AddRange(byCode.Values);
        return ordered;
    }

    private static AdoS8OrderFlowAggregateDto EmptyAggregate(string scope)
    {
        var stages = OrderFlowConstants.All
            .Select(code => new AdoS8OrderFlowStageAggregateDto
            {
                OrderFlowCode = code,
                OrderFlowName = OrderFlowConstants.ResolveName(code),
            })
            .ToList();
        return new AdoS8OrderFlowAggregateDto { Scope = scope, StageAggregates = stages };
    }

    private static decimal AvgOrZero(List<decimal> values)
        => values.Count == 0 ? 0m : values.Average();

    /// <summary>
    /// 采购透视视图：
    ///   scope=BASELINE_PPT      → 读 ado_s8_order_flow_procurement_pivot baseline 行（order_id IS NULL）。
    ///   scope=CURRENT_FILTERED  → 读订单级行（order_id IS NOT NULL）并按 orderCodes 过滤后聚合：
    ///                             cycle_days 简单平均；impact_count 求和（语义：多订单合计影响台次）；
    ///                             kit_rate 简单平均；status 按聚合后 cycle_days 三档阈值重判
    ///                             （与 baseline seed 一致：≤15 green / ≤20 yellow / &gt;20 red）。
    ///   其它 scope → 返回空骨架并回显 scope。
    /// orderCodes 为空时返回空骨架，不静默 fallback baseline（避免 baseline 泄漏到单订单态）。
    /// data_source 优先级：IMPORT / AGG &gt; SEED；同一 (order_code, material, supplier, spec) 上
    ///   若 IMPORT/AGG 行存在，只采用 IMPORT/AGG；SEED 仅作过渡兜底，便于真实数据源接入后无缝替换。
    /// status 来源：baseline 直接读 row.Status；CURRENT_FILTERED 聚合后按阈值重判（运行期重新分类，
    ///   不修改 seed 行）。cycle_days 保持 decimal(6,3) 不截断。
    /// </summary>
    public async Task<AdoS8OrderFlowProcurementPivotDto> GetProcurementPivotAsync(AdoS8OrderFlowProcurementPivotQueryDto query)
    {
        var tenantId = query.TenantId ?? 1;
        var factoryId = query.FactoryId ?? 1;
        var scope = string.IsNullOrWhiteSpace(query.Scope) ? ScopeBaselinePpt : query.Scope;

        if (string.Equals(scope, ScopeBaselinePpt, StringComparison.OrdinalIgnoreCase))
        {
            var baselineRows = await _pivotRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId == null) // baseline 行
                .ToListAsync();
            return BuildPivot(scope, baselineRows);
        }

        if (string.Equals(scope, ScopeCurrentFiltered, StringComparison.OrdinalIgnoreCase))
        {
            // 与产品设计 drawings API 一致：orderCodes 由 CSV 解析（trim / 去空 / Distinct）。
            var orderCodes = ParseOrderCodesCsv(query.OrderCodes);

            if (orderCodes.Count == 0)
                return new AdoS8OrderFlowProcurementPivotDto { Scope = scope };

            var orderLevelRows = await _pivotRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId != null && p.OrderCode != null)
                .Where(p => orderCodes.Contains(p.OrderCode!))
                .ToListAsync();

            if (orderLevelRows.Count == 0)
                return new AdoS8OrderFlowProcurementPivotDto { Scope = scope };

            var preferred = SelectPreferredDataSourceRows(orderLevelRows);
            var aggregated = AggregateOrderLevelRows(preferred);
            return BuildPivot(scope, aggregated);
        }

        return new AdoS8OrderFlowProcurementPivotDto { Scope = scope };
    }

    /// <summary>
    /// data_source 优先级：同一 (order_code, material, supplier, spec) 同时存在 IMPORT/AGG 与 SEED 时，
    /// 只保留 IMPORT/AGG 行；SEED 仅在 IMPORT/AGG 缺失时作为兜底。便于真实数据源接入后无缝替换 SEED。
    /// </summary>
    private static List<AdoS8OrderFlowProcurementPivot> SelectPreferredDataSourceRows(
        List<AdoS8OrderFlowProcurementPivot> rows)
    {
        var grouped = rows.GroupBy(r => (r.OrderCode, r.MaterialCode, r.SupplierCode, r.SpecCode));
        var result = new List<AdoS8OrderFlowProcurementPivot>(rows.Count);
        foreach (var g in grouped)
        {
            var preferred = g.FirstOrDefault(r =>
                string.Equals(r.DataSource, "IMPORT", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(r.DataSource, "AGG",    StringComparison.OrdinalIgnoreCase));
            result.Add(preferred ?? g.First());
        }
        return result;
    }

    /// <summary>
    /// 多订单聚合：按 (material, supplier, spec) 分组后简单平均；单订单时分组内只 1 行，聚合等于原值。
    /// cycle_days 平均；impact_count 求和；kit_rate 平均；status 按聚合后 cycle_days 阈值重判。
    /// 不补造缺位单元；聚合产物为非持久化 Pivot 对象供 BuildPivot 复用。
    /// </summary>
    private static List<AdoS8OrderFlowProcurementPivot> AggregateOrderLevelRows(
        List<AdoS8OrderFlowProcurementPivot> rows)
    {
        return rows
            .GroupBy(r => (r.MaterialCode, r.SupplierCode, r.SpecCode))
            .Select(g =>
            {
                var cycle = decimal.Round(g.Average(r => r.CycleDays), 3);
                var impactRows = g.Where(r => r.ImpactCount.HasValue).ToList();
                int? impactSum = impactRows.Count == 0 ? null : impactRows.Sum(r => r.ImpactCount!.Value);
                var kitRows = g.Where(r => r.KitRate.HasValue).ToList();
                decimal? kitAvg = kitRows.Count == 0
                    ? null
                    : decimal.Round(kitRows.Average(r => r.KitRate!.Value), 4);
                return new AdoS8OrderFlowProcurementPivot
                {
                    Id = 0,
                    OrderId = null,
                    OrderCode = null,
                    MaterialCode = g.Key.MaterialCode,
                    SupplierCode = g.Key.SupplierCode,
                    SpecCode = g.Key.SpecCode,
                    CycleDays = cycle,
                    Status = ClassifyAggregatedStatus(cycle),
                    ImpactCount = impactSum,
                    KitRate = kitAvg,
                    ScenarioCode = string.Empty,
                    DataSource = string.Empty,
                    TenantId = 0,
                    FactoryId = 0,
                    IsDeleted = false,
                };
            })
            .ToList();
    }

    private static string ClassifyAggregatedStatus(decimal cycleDays)
    {
        if (cycleDays <= 15.0m) return "green";
        if (cycleDays <= 20.0m) return "yellow";
        return "red";
    }

    private static AdoS8OrderFlowProcurementPivotDto BuildPivot(string scope, List<AdoS8OrderFlowProcurementPivot> rows)
    {
        var result = new AdoS8OrderFlowProcurementPivotDto { Scope = scope };
        if (rows.Count == 0) return result;

        var byMaterial = rows.GroupBy(r => r.MaterialCode)
                             .ToDictionary(g => g.Key, g => g.ToList());

        foreach (var (material, list) in byMaterial.OrderBy(kv => kv.Key, StringComparer.Ordinal))
        {
            // KeyMaterials：取 (supplier=TOTAL, spec=TOTAL) 单元作为该 material 的概览
            var grand = list.FirstOrDefault(r => r.SupplierCode == PivotTotalCode && r.SpecCode == PivotTotalCode);
            if (grand != null)
            {
                result.KeyMaterials.Add(new AdoS8OrderFlowKeyMaterialDto
                {
                    MaterialCode = material,
                    AvgCycleDays = grand.CycleDays,
                    CycleStatus = grand.Status,
                    ImpactCount = grand.ImpactCount,
                    KitRate = grand.KitRate,
                    ResultStatus = grand.Status,
                });
            }

            // SupplierBreakdown：spec=TOTAL 的所有 supplier 行（含 supplier=TOTAL）
            foreach (var row in list.Where(r => r.SpecCode == PivotTotalCode)
                                    .OrderBy(r => r.SupplierCode == PivotTotalCode ? 1 : 0)
                                    .ThenBy(r => r.SupplierCode, StringComparer.Ordinal))
            {
                result.SupplierBreakdown.Add(new AdoS8OrderFlowSupplierBreakdownDto
                {
                    MaterialCode = material,
                    SupplierCode = row.SupplierCode,
                    AvgCycleDays = row.CycleDays,
                    Status = row.Status,
                });
            }

            // SpecBreakdown：supplier=TOTAL 的所有 spec 行（含 spec=TOTAL）
            foreach (var row in list.Where(r => r.SupplierCode == PivotTotalCode)
                                    .OrderBy(r => r.SpecCode == PivotTotalCode ? 1 : 0)
                                    .ThenBy(r => r.SpecCode, StringComparer.Ordinal))
            {
                result.SpecBreakdown.Add(new AdoS8OrderFlowSpecBreakdownDto
                {
                    MaterialCode = material,
                    SpecCode = row.SpecCode,
                    AvgCycleDays = row.CycleDays,
                    Status = row.Status,
                });
            }

            // MatrixByMaterial：每个 supplier 一行，cells keyed by spec_code（保留全部 cycle_days 精度）
            var matrix = list.GroupBy(r => r.SupplierCode)
                             .OrderBy(g => g.Key == PivotTotalCode ? 1 : 0)
                             .ThenBy(g => g.Key, StringComparer.Ordinal)
                             .Select(g => new AdoS8OrderFlowProcurementMatrixRowDto
                             {
                                 SupplierCode = g.Key,
                                 Cells = g.ToDictionary(
                                     r => r.SpecCode,
                                     r => new AdoS8OrderFlowProcurementMatrixCellDto
                                     {
                                         CycleDays = r.CycleDays,
                                         Status = r.Status,
                                     }),
                             })
                             .ToList();
            result.MatrixByMaterial[material] = matrix;
        }

        return result;
    }

    // ──────────────────────────────────────────────────────────────────────
    // S8-ORDER-CHAIN-PRODUCT-DESIGN-DRAWING-PERSIST-1：产品设计图号粒度聚合。
    // 单一数据源 = ado_s8_order_flow_product_design_drawing 表；
    // 台数 / 占比 / 加权达成率 全部由 product_quantity 字段驱动；
    // 平均设计周期 = actual_days 算术平均（非 quantity 加权）；
    // summary.categories 固定返回 STANDARD / NON_STANDARD / TOTAL 三档；
    // drawings 按 productType 过滤；未达标优先 + actualDays 降序 + orderCode/drawingNo 升序。
    // ──────────────────────────────────────────────────────────────────────

    private const string ProductDesignTypeStandard = "STANDARD";
    private const string ProductDesignTypeNonStandard = "NON_STANDARD";
    private const string ProductDesignTypeTotal = "TOTAL";
    private const decimal ProductDesignStageKpiDays = 3m;

    // S8-ORDER-CHAIN-PRODUCT-DESIGN-PPT-STATIC-AND-SINGLE-ORDER-ALIGN-1：
    // 非标产品 KPI 与常规产品同阶段标准 3 天；非标 actual_days 超 3 时记 red/未达标。
    private const decimal ProductDesignNonStandardKpiDays = 3m;

    public async Task<AdoS8OrderFlowProductDesignDrawingsDto> GetProductDesignDrawingsAsync(
        AdoS8OrderFlowProductDesignDrawingsQueryDto query)
    {
        var tenantId = query.TenantId ?? 1;
        var factoryId = query.FactoryId ?? 1;

        var orderCodes = ParseOrderCodesCsv(query.OrderCodes);
        var productTypeFilter = NormalizeProductDesignType(query.ProductType);
        var scope = orderCodes.Count == 0 ? ScopeBaselinePpt : ScopeCurrentFiltered;

        var rows = await _pddRep.AsQueryable()
            .Where(d => d.TenantId == tenantId && d.FactoryId == factoryId && !d.IsDeleted)
            .WhereIF(orderCodes.Count > 0, d => orderCodes.Contains(d.OrderCode))
            .ToListAsync();

        // summary 始终基于命中订单范围的全分类汇总（不受 productType 过滤影响），让前端可同时渲染三档汇总。
        var summary = BuildProductDesignSummary(rows);

        var drawingItems = rows.AsEnumerable();
        if (productTypeFilter != null)
            drawingItems = drawingItems.Where(d => d.ProductType == productTypeFilter);

        var orderedDrawings = drawingItems
            .OrderBy(d => d.IsAchieved)
            .ThenByDescending(d => d.ActualDays ?? decimal.MinValue)
            .ThenBy(d => d.OrderCode, StringComparer.Ordinal)
            .ThenBy(d => d.DrawingNo, StringComparer.Ordinal)
            .Select(MapProductDesignDrawingItem)
            .ToList();

        return new AdoS8OrderFlowProductDesignDrawingsDto
        {
            Scope = scope,
            Filter = new AdoS8OrderFlowProductDesignFilterDto
            {
                OrderCodes = orderCodes,
                ProductType = productTypeFilter,
            },
            Summary = summary,
            Drawings = orderedDrawings,
        };
    }

    private static List<string> ParseOrderCodesCsv(string? raw)
    {
        if (string.IsNullOrWhiteSpace(raw)) return new List<string>();
        return raw.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                  .Where(s => !string.IsNullOrWhiteSpace(s))
                  .Distinct(StringComparer.Ordinal)
                  .ToList();
    }

    private static string? NormalizeProductDesignType(string? raw)
    {
        if (string.IsNullOrWhiteSpace(raw)) return null;
        var s = raw.Trim().ToUpperInvariant();
        return s == ProductDesignTypeStandard || s == ProductDesignTypeNonStandard ? s : null;
    }

    private static AdoS8OrderFlowProductDesignSummaryDto BuildProductDesignSummary(
        List<AdoS8OrderFlowProductDesignDrawing> rows)
    {
        var stdRows = rows.Where(r => r.ProductType == ProductDesignTypeStandard).ToList();
        var nonStdRows = rows.Where(r => r.ProductType == ProductDesignTypeNonStandard).ToList();
        var grandTotalQty = rows.Sum(r => r.ProductQuantity);

        return new AdoS8OrderFlowProductDesignSummaryDto
        {
            Overall = BuildProductDesignOverall(rows),
            Categories = new List<AdoS8OrderFlowProductDesignCategorySummaryDto>
            {
                BuildProductDesignCategory(ProductDesignTypeStandard,    "常规产品", ProductDesignStageKpiDays,        stdRows,    grandTotalQty),
                BuildProductDesignCategory(ProductDesignTypeNonStandard, "非标产品", ProductDesignNonStandardKpiDays,  nonStdRows, grandTotalQty),
                BuildProductDesignCategory(ProductDesignTypeTotal,       "合计",     ProductDesignStageKpiDays,        rows,       grandTotalQty),
            },
        };
    }

    private static AdoS8OrderFlowProductDesignOverallSummaryDto BuildProductDesignOverall(
        List<AdoS8OrderFlowProductDesignDrawing> rows)
    {
        var totalQty = rows.Sum(r => r.ProductQuantity);
        var achievedQty = rows.Where(r => r.IsAchieved).Sum(r => r.ProductQuantity);
        var actualDays = rows.Where(r => r.ActualDays.HasValue).Select(r => r.ActualDays!.Value).ToList();

        return new AdoS8OrderFlowProductDesignOverallSummaryDto
        {
            DrawingCount = rows.Select(r => r.DrawingNo).Distinct(StringComparer.Ordinal).Count(),
            TotalQuantity = totalQty,
            KpiDays = ProductDesignStageKpiDays,
            AvgActualDays = actualDays.Count == 0 ? 0m : actualDays.Average(),
            AchievementRate = totalQty == 0 ? 0m : (decimal)achievedQty / totalQty,
        };
    }

    private static AdoS8OrderFlowProductDesignCategorySummaryDto BuildProductDesignCategory(
        string productType,
        string name,
        decimal kpiDays,
        List<AdoS8OrderFlowProductDesignDrawing> rows,
        int grandTotalQty)
    {
        var totalQty = rows.Sum(r => r.ProductQuantity);
        var achievedQty = rows.Where(r => r.IsAchieved).Sum(r => r.ProductQuantity);
        var actualDays = rows.Where(r => r.ActualDays.HasValue).Select(r => r.ActualDays!.Value).ToList();
        var rate = totalQty == 0 ? 0m : (decimal)achievedQty / totalQty;

        // S8-ORDER-CHAIN-PRODUCT-DESIGN-20ORDER-GENERATION-CONSISTENCY-AUDIT-1：
        // STANDARD / NON_STANDARD / TOTAL 三档统一返回真实指标，不再用 null 掩盖。
        // - KpiDays：始终返回常量 KPI=3，方便前端展示阈值。
        // - AvgActualDays：actualDays 简单平均（pending 行已经被 HasValue 过滤）。TOTAL 平均 == 父 stage.actual_days（守恒）。
        // - AchievementRate：以 product_quantity 加权达成率（与 Overall 一致），保留业务台数权重直觉。
        // - Status：达成率 ≥0.95 green / ≥0.80 yellow / 否则 red；无完工行（all pending）时返回空串。
        return new AdoS8OrderFlowProductDesignCategorySummaryDto
        {
            ProductType = productType,
            Name = name,
            DrawingCount = rows.Select(r => r.DrawingNo).Distinct(StringComparer.Ordinal).Count(),
            TotalQuantity = totalQty,
            Ratio = grandTotalQty == 0 ? 0m : (decimal)totalQty / grandTotalQty,
            KpiDays = kpiDays,
            AvgActualDays = actualDays.Count == 0 ? (decimal?)null : actualDays.Average(),
            AchievementRate = rate,
            Status = actualDays.Count == 0
                ? string.Empty
                : ClassifyProductDesignCategoryStatus(rate, totalQty),
        };
    }

    /// <summary>达标率 ≥ 0.95 绿 / ≥ 0.80 黄 / 否则 红；无数据返回空串以表达未取数而非状态判定。</summary>
    private static string ClassifyProductDesignCategoryStatus(decimal achievementRate, int totalQty)
    {
        if (totalQty == 0) return string.Empty;
        if (achievementRate >= 0.95m) return "green";
        if (achievementRate >= 0.80m) return "yellow";
        return "red";
    }

    private static AdoS8OrderFlowProductDesignDrawingItemDto MapProductDesignDrawingItem(
        AdoS8OrderFlowProductDesignDrawing d)
        => new()
        {
            OrderCode = d.OrderCode,
            DrawingNo = d.DrawingNo,
            ProductType = d.ProductType,
            ResponsiblePerson = d.ResponsiblePerson,
            PlannedStartDate = d.PlannedStartDate,
            PlannedEndDate = d.PlannedEndDate,
            ActualStartDate = d.ActualStartDate,
            ActualEndDate = d.ActualEndDate,
            KpiDays = d.KpiDays,
            ActualDays = d.ActualDays,
            IsAchieved = d.IsAchieved,
            Status = d.Status,
            ProductQuantity = d.ProductQuantity,
        };

    // ──────────────────────────────────────────────────────────────────────
    // S8-ORDER-CHAIN-BODY-PRODUCTION-ORDER-LEVEL-SEED-FIX-1：本体生产透视。
    // 单一数据源 = ado_s8_order_flow_manufacturing_{process,loss_factor,operator} 三表。
    // BASELINE_PPT 读 (order_id IS NULL) baseline 行；CURRENT_FILTERED 读订单级行（order_id IS NOT NULL），
    //   按 IMPORT/AGG > SEED 优先级去重后，按业务口径聚合：
    //     process actual_days：AVG（多订单按行平均）
    //     process plan_qty：SUM（多订单累计计划台次）
    //     process achievement_rate：AVG（多订单平均达成率），TOTAL 行特殊处理见 BuildManufacturingProcessAggregated。
    //     process cycle_status / achievement_status：聚合后按阈值重判（与 baseline 同口径，运行期不修改 seed 行）。
    //     loss factor count：SUM；ratio_pct / loss_hours：AVG。
    //     operator avg_hours：AVG；status：取首单的 status（baseline 每操作员固定，AVG 后语义不变）。
    //   orderCodes 为空时返回空骨架（不静默 fallback baseline，避免 baseline 泄漏到单订单态）。
    // status 阈值与 seed 同步：cycle_status (≤pi green / ≤pi*1.2 yellow / 否则 red)，
    //   achievement_status (≥0.95 green / ≥0.80 yellow / 否则 red)。
    // ──────────────────────────────────────────────────────────────────────

    private const string ManufacturingProcessTotalCode = "TOTAL";

    // S8-...-FIX-1R：order-level TOTAL.achievement_rate / achievement_status 锚定 fixture，
    // 与 baseline TOTAL（0.90 / yellow）保持一致，不再用 plan_qty 加权计算（避免 65% red 与 90% yellow 冲突）。
    // SEED 过渡口径；真实 IMPORT/AGG 接入后由真实生产达成数据替换。
    private const decimal ManufacturingTotalAchievementRate = 0.9000m;
    private const string ManufacturingTotalAchievementStatus = "yellow";

    public async Task<AdoS8OrderFlowManufacturingPivotDto> GetManufacturingPivotAsync(
        AdoS8OrderFlowManufacturingPivotQueryDto query)
    {
        var tenantId = query.TenantId ?? 1;
        var factoryId = query.FactoryId ?? 1;
        var scope = string.IsNullOrWhiteSpace(query.Scope) ? ScopeBaselinePpt : query.Scope;

        if (string.Equals(scope, ScopeBaselinePpt, StringComparison.OrdinalIgnoreCase))
        {
            var processRows = await _mfgProcessRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId == null)
                .ToListAsync();
            var lossRows = await _mfgLossRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId == null)
                .ToListAsync();
            var operatorRows = await _mfgOperatorRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId == null)
                .ToListAsync();

            return BuildManufacturingPivotFromBaseline(scope, processRows, lossRows, operatorRows);
        }

        if (string.Equals(scope, ScopeCurrentFiltered, StringComparison.OrdinalIgnoreCase))
        {
            var orderCodes = ParseOrderCodesCsv(query.OrderCodes);
            if (orderCodes.Count == 0)
                return new AdoS8OrderFlowManufacturingPivotDto { Scope = scope };

            var processRows = await _mfgProcessRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId != null && p.OrderCode != null)
                .Where(p => orderCodes.Contains(p.OrderCode!))
                .ToListAsync();
            var lossRows = await _mfgLossRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId != null && p.OrderCode != null)
                .Where(p => orderCodes.Contains(p.OrderCode!))
                .ToListAsync();
            var operatorRows = await _mfgOperatorRep.AsQueryable()
                .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
                .Where(p => p.OrderId != null && p.OrderCode != null)
                .Where(p => orderCodes.Contains(p.OrderCode!))
                .ToListAsync();

            if (processRows.Count == 0 && lossRows.Count == 0 && operatorRows.Count == 0)
                return new AdoS8OrderFlowManufacturingPivotDto { Scope = scope };

            // IMPORT/AGG > SEED 去重：同 (order_code, process_code/factor_code/operator_code) 上 IMPORT/AGG 优先。
            var preferredProcess = SelectPreferredMfgProcess(processRows);
            var preferredLoss = SelectPreferredMfgLossFactor(lossRows);
            var preferredOperator = SelectPreferredMfgOperator(operatorRows);

            return new AdoS8OrderFlowManufacturingPivotDto
            {
                Scope = scope,
                Processes = BuildManufacturingProcessAggregated(preferredProcess),
                LossFactors = BuildManufacturingLossFactorAggregated(preferredLoss),
                Operators = BuildManufacturingOperatorAggregated(preferredOperator),
            };
        }

        return new AdoS8OrderFlowManufacturingPivotDto { Scope = scope };
    }

    private static AdoS8OrderFlowManufacturingPivotDto BuildManufacturingPivotFromBaseline(
        string scope,
        List<AdoS8OrderFlowManufacturingProcess> processRows,
        List<AdoS8OrderFlowManufacturingLossFactor> lossRows,
        List<AdoS8OrderFlowManufacturingOperator> operatorRows)
    {
        var processes = processRows
            .OrderBy(r => r.SortNo)
            .Select(r => new AdoS8OrderFlowManufacturingProcessDto
            {
                ProcessCode = r.ProcessCode,
                ProcessName = r.ProcessName,
                PiDays = r.PiDays,
                ActualDays = r.ActualDays,
                CycleStatus = r.CycleStatus,
                PlanQty = r.PlanQty,
                AchievementRate = r.AchievementRate,
                AchievementStatus = r.AchievementStatus,
                SortNo = r.SortNo,
            })
            .ToList();

        var losses = lossRows
            .OrderBy(r => r.SortNo)
            .Select(r => new AdoS8OrderFlowManufacturingLossFactorDto
            {
                FactorCode = r.FactorCode,
                FactorName = r.FactorName,
                CountValue = r.CountValue,
                RatioPct = r.RatioPct,
                LossHours = r.LossHours,
                SortNo = r.SortNo,
            })
            .ToList();

        var operators = operatorRows
            .OrderBy(r => r.SortNo)
            .Select(r => new AdoS8OrderFlowManufacturingOperatorDto
            {
                OperatorCode = r.OperatorCode,
                OperatorName = r.OperatorName,
                AvgHours = r.AvgHours,
                Status = r.Status,
                SortNo = r.SortNo,
            })
            .ToList();

        return new AdoS8OrderFlowManufacturingPivotDto
        {
            Scope = scope,
            Processes = processes,
            LossFactors = losses,
            Operators = operators,
        };
    }

    private static List<AdoS8OrderFlowManufacturingProcess> SelectPreferredMfgProcess(
        List<AdoS8OrderFlowManufacturingProcess> rows)
    {
        var grouped = rows.GroupBy(r => (r.OrderCode, r.ProcessCode));
        var result = new List<AdoS8OrderFlowManufacturingProcess>(rows.Count);
        foreach (var g in grouped)
        {
            var preferred = g.FirstOrDefault(r =>
                string.Equals(r.DataSource, "IMPORT", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(r.DataSource, "AGG", StringComparison.OrdinalIgnoreCase));
            result.Add(preferred ?? g.First());
        }
        return result;
    }

    private static List<AdoS8OrderFlowManufacturingLossFactor> SelectPreferredMfgLossFactor(
        List<AdoS8OrderFlowManufacturingLossFactor> rows)
    {
        var grouped = rows.GroupBy(r => (r.OrderCode, r.FactorCode));
        var result = new List<AdoS8OrderFlowManufacturingLossFactor>(rows.Count);
        foreach (var g in grouped)
        {
            var preferred = g.FirstOrDefault(r =>
                string.Equals(r.DataSource, "IMPORT", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(r.DataSource, "AGG", StringComparison.OrdinalIgnoreCase));
            result.Add(preferred ?? g.First());
        }
        return result;
    }

    private static List<AdoS8OrderFlowManufacturingOperator> SelectPreferredMfgOperator(
        List<AdoS8OrderFlowManufacturingOperator> rows)
    {
        var grouped = rows.GroupBy(r => (r.OrderCode, r.OperatorCode));
        var result = new List<AdoS8OrderFlowManufacturingOperator>(rows.Count);
        foreach (var g in grouped)
        {
            var preferred = g.FirstOrDefault(r =>
                string.Equals(r.DataSource, "IMPORT", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(r.DataSource, "AGG", StringComparison.OrdinalIgnoreCase));
            result.Add(preferred ?? g.First());
        }
        return result;
    }

    /// <summary>
    /// 多订单工序聚合：
    ///   非 TOTAL 行：actual_days = AVG；plan_qty = SUM；achievement_rate = AVG（保留 4 位小数）；
    ///                cycle_status 按聚合 actual vs pi 重判；achievement_status 按聚合 rate 重判。
    ///   TOTAL 行：pi=6（取 baseline 常量）；actual = AVG（与 4 工序 AVG 之和守恒，差异为 rounding 容差）；
    ///             plan_qty = NULL；
    ///             S8-...-FIX-1R：achievement_rate / achievement_status 锚定 fixture（0.90 / yellow），
    ///             不再做 plan_qty 加权计算（避免与 baseline TOTAL 视觉冲突）；
    ///             cycle_status 按聚合 actual vs pi 重判。
    /// </summary>
    private static List<AdoS8OrderFlowManufacturingProcessDto> BuildManufacturingProcessAggregated(
        List<AdoS8OrderFlowManufacturingProcess> rows)
    {
        var grouped = rows
            .GroupBy(r => r.ProcessCode)
            .ToDictionary(g => g.Key, g => g.ToList());

        // S8-...-FIX-1R：TOTAL 行 rate / status 锚定 fixture，不再做 4 工序 plan_qty 加权计算，
        // 不需要维护中间 aggregatedRates 字典。
        var processDtos = new List<AdoS8OrderFlowManufacturingProcessDto>();

        foreach (var kv in grouped.OrderBy(g => g.Value.First().SortNo))
        {
            var code = kv.Key;
            var list = kv.Value;
            var sample = list[0];
            var isTotal = string.Equals(code, ManufacturingProcessTotalCode, StringComparison.OrdinalIgnoreCase);

            var actualAvg = decimal.Round(list.Average(r => r.ActualDays), 3);
            var pi = sample.PiDays;

            decimal? aggregatedRate;
            int? planQtySum;
            string achStatus;
            if (isTotal)
            {
                // S8-...-FIX-1R：TOTAL 行 rate / status 锚定 fixture（0.90 / yellow），
                // 不再做 plan_qty 加权计算；非 TOTAL 行的 plan_qty 仍保留供 UI 显示，但不参与 TOTAL.rate。
                planQtySum = null;
                aggregatedRate = ManufacturingTotalAchievementRate;
                achStatus = ManufacturingTotalAchievementStatus;
            }
            else
            {
                planQtySum = list.Where(r => r.PlanQty.HasValue).Sum(r => r.PlanQty!.Value);
                var rateRows = list.Where(r => r.AchievementRate.HasValue).ToList();
                aggregatedRate = rateRows.Count == 0
                    ? null
                    : decimal.Round(rateRows.Average(r => r.AchievementRate!.Value), 4);
                achStatus = aggregatedRate.HasValue
                    ? ClassifyMfgAchievementStatus(aggregatedRate.Value)
                    : string.Empty;
            }

            var cycleStatus = ClassifyMfgCycleStatus(actualAvg, pi);

            processDtos.Add(new AdoS8OrderFlowManufacturingProcessDto
            {
                ProcessCode = sample.ProcessCode,
                ProcessName = sample.ProcessName,
                PiDays = pi,
                ActualDays = actualAvg,
                CycleStatus = cycleStatus,
                PlanQty = planQtySum,
                AchievementRate = aggregatedRate,
                AchievementStatus = achStatus,
                SortNo = sample.SortNo,
            });
        }

        return processDtos;
    }

    private static List<AdoS8OrderFlowManufacturingLossFactorDto> BuildManufacturingLossFactorAggregated(
        List<AdoS8OrderFlowManufacturingLossFactor> rows)
    {
        return rows
            .GroupBy(r => r.FactorCode)
            .OrderBy(g => g.First().SortNo)
            .Select(g =>
            {
                var sample = g.First();
                var countList = g.Where(r => r.CountValue.HasValue).ToList();
                var ratioList = g.Where(r => r.RatioPct.HasValue).ToList();
                var hoursList = g.Where(r => r.LossHours.HasValue).ToList();
                return new AdoS8OrderFlowManufacturingLossFactorDto
                {
                    FactorCode = sample.FactorCode,
                    FactorName = sample.FactorName,
                    CountValue = countList.Count == 0 ? null : countList.Sum(r => r.CountValue!.Value),
                    RatioPct = ratioList.Count == 0
                        ? null
                        : decimal.Round(ratioList.Average(r => r.RatioPct!.Value), 2),
                    LossHours = hoursList.Count == 0
                        ? null
                        : decimal.Round(hoursList.Average(r => r.LossHours!.Value), 2),
                    SortNo = sample.SortNo,
                };
            })
            .ToList();
    }

    private static List<AdoS8OrderFlowManufacturingOperatorDto> BuildManufacturingOperatorAggregated(
        List<AdoS8OrderFlowManufacturingOperator> rows)
    {
        return rows
            .GroupBy(r => r.OperatorCode)
            .OrderBy(g => g.First().SortNo)
            .Select(g =>
            {
                var sample = g.First();
                return new AdoS8OrderFlowManufacturingOperatorDto
                {
                    OperatorCode = sample.OperatorCode,
                    OperatorName = sample.OperatorName,
                    AvgHours = decimal.Round(g.Average(r => r.AvgHours), 2),
                    // 每操作员 baseline status 固化，多订单聚合时各订单值一致；取首单不引入歧义。
                    Status = sample.Status,
                    SortNo = sample.SortNo,
                };
            })
            .ToList();
    }

    private static string ClassifyMfgCycleStatus(decimal actual, decimal pi)
    {
        if (actual <= pi) return "green";
        if (actual <= pi * 1.2m) return "yellow";
        return "red";
    }

    private static string ClassifyMfgAchievementStatus(decimal rate)
    {
        if (rate >= 0.95m) return "green";
        if (rate >= 0.80m) return "yellow";
        return "red";
    }
}