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";

    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;

    public S8OrderFlowService(
        SqlSugarRepository<AdoS8OrderFlowOrder> orderRep,
        SqlSugarRepository<AdoS8OrderFlowStage> stageRep,
        SqlSugarRepository<AdoS8OrderFlowSubstep> substepRep,
        SqlSugarRepository<AdoS8OrderFlowSubstepUnit> unitRep,
        SqlSugarRepository<AdoS8OrderFlowSnapshot> snapshotRep,
        SqlSugarRepository<AdoS8OrderFlowProcurementPivot> pivotRep,
        SqlSugarRepository<AdoS8Exception> exceptionRep)
    {
        _orderRep = orderRep;
        _stageRep = stageRep;
        _substepRep = substepRep;
        _unitRep = unitRep;
        _snapshotRep = snapshotRep;
        _pivotRep = pivotRep;
        _exceptionRep = exceptionRep;
    }

    /// <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);
        return new AdoS8OrderFlowAggregateDto
        {
            Scope = scope,
            TotalOrders = snapshot.TotalOrders,
            TotalCustomers = snapshot.TotalCustomers,
            AvgResponseMinutes = snapshot.AvgResponseMinutes,
            AvgProcessingMinutes = snapshot.AvgProcessingMinutes,
            AvgLossMinutes = snapshot.AvgLossMinutes,
            StageAggregates = ReorderByCanonical(stages),
        };
    }

    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>()));
        }

        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,
        };
    }

    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=NULL）
    /// status 严格只读 DB；cycle_days 保持 decimal(6,3) 不截断。
    /// 本切片正式只支持 BASELINE_PPT；其他 scope 返回空骨架并标注 scope。
    /// </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))
            return new AdoS8OrderFlowProcurementPivotDto { Scope = scope };

        var rows = await _pivotRep.AsQueryable()
            .Where(p => p.TenantId == tenantId && p.FactoryId == factoryId && !p.IsDeleted)
            .Where(p => p.OrderId == null) // baseline 行
            .ToListAsync();

        return BuildPivot(scope, rows);
    }

    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,
                    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;
    }
}