AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SeedData/S8OrderFlowProcurementPivotSeedData.cs

using Admin.NET.Plugin.AiDOP.Entity.S8.OrderFlow;

namespace Admin.NET.Plugin.AiDOP.SeedData;

/// <summary>
/// ORDER-FLOW-S8-INTEGRATED-DOMAIN-RESET-1 t2c：S8 订单执行链路材料采购透视表 baseline 种子。
/// 3 materials (XX/YY/ZZ) × 4 suppliers (A/B/C/TOTAL) × 4 specs (L4.5*11.2/MT2*6.3/MT1*5.4/TOTAL) = 48 行 baseline。
/// YY 16 单元为 PPT 第 4 页真值；XX/ZZ 16 单元各自按确定性 multiplier 派生（无随机 / 无 Now）。
/// status 在 seed 时确定，不允许 service/前端动态重判；按 cycle_days 三档阈值固化（≤15 green / 15..20 yellow / &gt;20 red）。
/// order_id / order_code 全部 NULL（baseline 行）。
///
/// S8-ORDER-CHAIN-MATERIAL-PURCHASE-ORDER-LEVEL-SEED-FIX-1（叠加）：
/// 在 baseline 之外追加订单级 SEED 行（17 非 pending 订单 × 48 = 816 行），
/// 用于 CURRENT_FILTERED scope 的单 / 多订单下钻；scenario_code='ORDER_LEVEL'，data_source='SEED'。
/// 真实数据源（IMPORT / AGG）接入后，service 端将优先选 IMPORT/AGG，SEED 作为兜底。
/// </summary>
[IncreSeed]
public class S8OrderFlowProcurementPivotSeedData : ISqlSugarEntitySeedData<AdoS8OrderFlowProcurementPivot>
{
    public IEnumerable<AdoS8OrderFlowProcurementPivot> HasData()
        => S8OrderFlowProcurementPivotDataset.BuildRows()
            .Concat(S8OrderFlowProcurementPivotDataset.BuildOrderLevelRows());
}

internal static class S8OrderFlowProcurementPivotDataset
{
    internal const long PivotIdBase = 1329909140000L;
    internal const long OrderLevelPivotIdBase = 1329909150000L;

    internal const string PivotTotalCode = "TOTAL";
    internal const string OrderLevelScenarioCode = "ORDER_LEVEL";

    internal static readonly string[] Suppliers = { "A", "B", "C", "TOTAL" };
    internal static readonly string[] Specs = { "L4.5*11.2", "MT2*6.3", "MT1*5.4", "TOTAL" };

    /// <summary>YY (PPT 第 4 页) 真值矩阵 [supplierIdx, specIdx]；保留 3 位小数精度。</summary>
    private static readonly decimal[,] YyCycleDays =
    {
        // L4.5*11.2 | MT2*6.3 | MT1*5.4 | TOTAL
        { 14.800m,   11.000m,  11.000m,  13.000m  }, // A
        { 14.050m,   17.150m,  17.150m,  15.600m  }, // B
        { 14.050m,   23.925m,  23.850m,  20.100m  }, // C
        { 14.500m,   17.400m,  14.800m,  15.300m  }, // TOTAL
    };

    /// <summary>派生 XX / ZZ 单元值的 multiplier。XX 偏低、ZZ 偏高，便于 DEMO 拉开差异。</summary>
    private const decimal XxMultiplier = 0.85m;
    private const decimal ZzMultiplier = 1.10m;

    /// <summary>
    /// BASELINE_PPT 关键材料 grand 单元的"影响台次 / 准时齐套率"业务基线。
    /// 仅在 supplier_code=TOTAL &amp;&amp; spec_code=TOTAL 行写入，其余 45 行保持 NULL。
    /// </summary>
    private static readonly Dictionary<string, (int ImpactCount, decimal KitRate)> GrandUnitBaseline = new()
    {
        ["XX"] = (55, 0.8500m),
        ["YY"] = (23, 0.8400m),
        ["ZZ"] = (10, 0.9700m),
    };

    public static IEnumerable<AdoS8OrderFlowProcurementPivot> BuildRows()
    {
        long seq = 0;
        foreach (var (material, scenario, mult) in new[]
        {
            ("XX", "DEMO", XxMultiplier),
            ("YY", "PPT",  1.0m),
            ("ZZ", "DEMO", ZzMultiplier),
        })
        {
            for (var s = 0; s < Suppliers.Length; s++)
            {
                for (var p = 0; p < Specs.Length; p++)
                {
                    var rawCycle = YyCycleDays[s, p];
                    var cycleDays = material == "YY"
                        ? rawCycle
                        : decimal.Round(rawCycle * mult, 3);

                    var isGrand = Suppliers[s] == PivotTotalCode && Specs[p] == PivotTotalCode;
                    int? impactCount = null;
                    decimal? kitRate = null;
                    if (isGrand && GrandUnitBaseline.TryGetValue(material, out var baseline))
                    {
                        impactCount = baseline.ImpactCount;
                        kitRate = baseline.KitRate;
                    }

                    yield return new AdoS8OrderFlowProcurementPivot
                    {
                        Id = PivotIdBase + (++seq),
                        OrderId = null,
                        OrderCode = null,
                        MaterialCode = material,
                        SupplierCode = Suppliers[s],
                        SpecCode = Specs[p],
                        CycleDays = cycleDays,
                        Status = ClassifyStatus(cycleDays),
                        ImpactCount = impactCount,
                        KitRate = kitRate,
                        ScenarioCode = scenario,
                        DataSource = "SEED",
                        TenantId = 1,
                        FactoryId = 1,
                        CreatedAt = S8OrderFlowDataset.CreatedAt,
                        UpdatedAt = null,
                        IsDeleted = false,
                    };
                }
            }
        }
    }

    // ──────────────────────────────────────────────────────────────────────
    // S8-ORDER-CHAIN-MATERIAL-PURCHASE-ORDER-LEVEL-SEED-FIX-1：订单级 SEED 行
    // ──────────────────────────────────────────────────────────────────────
    // 对每个 MATERIAL_PURCHASE stage.status != "pending" 的订单：
    //   读 stage.actualDays = A，stage.status ∈ {green, yellow, red}；
    //   按 status 决定三材料 grand offsets：green (-1, 0, +1) / yellow (-2, 0, +2) / red (-3, 0, +3)；
    //   下限保护：若 (A + xxOff) < 6.331（即任一 internal cell < 1），退化为三材料同值 = A；
    //   每 material grand = A + offset；三材料 grand 简单平均 = A（守恒到父级 stage.actual_days）；
    //   内部 9 (s,p) 单元 = baseline YY internal[s,p] + (grand - 16.331)，保持 marginal averages 等于 grand；
    //   supplier=TOTAL 行 / spec=TOTAL 列 由内部 3 单元算术平均（≈ baseline 行/列内部均值 + delta）；
    //   (TOTAL, TOTAL) 直接 = grand，确保父子守恒不受 rounding 漂移；
    //   status 沿用 baseline 三档阈值（≤15 green / ≤20 yellow / >20 red）；
    //   impact_count / kit_rate 仅 grand 单元写入，按 stage.status × material 分档（deterministic）；
    //   scenario_code='ORDER_LEVEL'（VARCHAR(16) 无 enum 约束，与 baseline 的 PPT/DEMO 区分开）；
    //   data_source='SEED'，未来替换为 IMPORT/AGG 时由 service 端按优先级覆盖。
    // ──────────────────────────────────────────────────────────────────────

    /// <summary>baseline 内部 9 单元（s ∈ {0,1,2}, p ∈ {0,1,2}）的算术平均；用于 shift 派生订单级矩阵。</summary>
    private static readonly decimal BaselineInternalGrandAvg = ComputeBaselineInternalGrandAvg();

    /// <summary>baseline 内部 3 个 supplier 行（s ∈ {0,1,2}）的算术平均；用于 supplier=TOTAL 列。</summary>
    private static readonly decimal[] BaselineSupplierAvg = ComputeBaselineSupplierAvg();

    /// <summary>baseline 内部 3 个 spec 列（p ∈ {0,1,2}）的算术平均；用于 spec=TOTAL 行。</summary>
    private static readonly decimal[] BaselineSpecAvg = ComputeBaselineSpecAvg();

    /// <summary>下限保护阈值：grand ≥ 6.331 时，最小 internal cell = 11.000 + (grand - 16.331) ≥ 0.669，再加上 (yyOff - xxOff) 容差保障安全。</summary>
    private const decimal MinSafeGrandCycleDays = 6.331m;

    public static IEnumerable<AdoS8OrderFlowProcurementPivot> BuildOrderLevelRows()
    {
        long seq = 0;
        foreach (var spec in S8OrderFlowDataset.Specs)
        {
            var lifecycle = S8OrderFlowStageDataset.BuildLifecycleValues(spec);
            // MATERIAL_PURCHASE 在 5 阶段流水中固定为索引 2（order_review→product_design→material_procurement→body_production→final_assembly_shipping）。
            var stage = lifecycle[2];
            if (stage.status == "pending") continue;

            var A = stage.actualDays;
            var (xxOff, yyOff, zzOff) = stage.status switch
            {
                "green"  => (-1m,  0m, +1m),
                "yellow" => (-2m,  0m, +2m),
                "red"    => (-3m,  0m, +3m),
                _        => ( 0m,  0m,  0m),
            };

            // 下限保护：当 A 过小导致 XX grand 进入 internal < 1 风险区时，退化为三材料同值 = A。
            if (A + xxOff < MinSafeGrandCycleDays)
            {
                xxOff = 0m; yyOff = 0m; zzOff = 0m;
            }

            var orderId = S8OrderFlowDataset.OrderIdBase + spec.Idx;

            foreach (var (material, off) in new[] { ("XX", xxOff), ("YY", yyOff), ("ZZ", zzOff) })
            {
                var grand = A + off;
                var delta = grand - BaselineInternalGrandAvg;

                for (var s = 0; s < Suppliers.Length; s++)
                {
                    for (var p = 0; p < Specs.Length; p++)
                    {
                        var supplier = Suppliers[s];
                        var specCode = Specs[p];
                        var supplierIsTotal = supplier == PivotTotalCode;
                        var specIsTotal = specCode == PivotTotalCode;

                        decimal cycleDays;
                        if (supplierIsTotal && specIsTotal)
                        {
                            // (TOTAL, TOTAL) = grand：父子守恒锚点。
                            cycleDays = decimal.Round(grand, 3);
                        }
                        else if (supplierIsTotal)
                        {
                            // (TOTAL, p)：3 个供应商在该 spec 的算术平均。
                            cycleDays = decimal.Round(BaselineSpecAvg[p] + delta, 3);
                        }
                        else if (specIsTotal)
                        {
                            // (s, TOTAL)：该供应商在 3 个 spec 的算术平均。
                            cycleDays = decimal.Round(BaselineSupplierAvg[s] + delta, 3);
                        }
                        else
                        {
                            // 9 内部单元：baseline + delta。
                            cycleDays = decimal.Round(YyCycleDays[s, p] + delta, 3);
                        }

                        var isGrand = supplierIsTotal && specIsTotal;
                        int? impactCount = null;
                        decimal? kitRate = null;
                        if (isGrand)
                        {
                            var (ic, kr) = OrderLevelGrandBaseline(stage.status, material);
                            impactCount = ic;
                            kitRate = kr;
                        }

                        yield return new AdoS8OrderFlowProcurementPivot
                        {
                            Id = OrderLevelPivotIdBase + (++seq),
                            OrderId = orderId,
                            OrderCode = spec.OrderCode,
                            MaterialCode = material,
                            SupplierCode = supplier,
                            SpecCode = specCode,
                            CycleDays = cycleDays,
                            Status = ClassifyStatus(cycleDays),
                            ImpactCount = impactCount,
                            KitRate = kitRate,
                            ScenarioCode = OrderLevelScenarioCode,
                            DataSource = "SEED",
                            TenantId = 1,
                            FactoryId = 1,
                            CreatedAt = S8OrderFlowDataset.CreatedAt,
                            UpdatedAt = null,
                            IsDeleted = false,
                        };
                    }
                }
            }
        }
    }

    /// <summary>
    /// 订单级 grand 单元 impact_count / kit_rate 业务基线（deterministic，无随机）：
    /// stage.status × material 共 9 档；cycle_days 越高 → kit_rate 越低 / impact_count 越高。
    /// </summary>
    private static (int ImpactCount, decimal KitRate) OrderLevelGrandBaseline(string stageStatus, string material)
    {
        return (stageStatus, material) switch
        {
            ("green",  "XX") => (8,  0.9700m),
            ("green",  "YY") => (5,  0.9800m),
            ("green",  "ZZ") => (10, 0.9500m),
            ("yellow", "XX") => (15, 0.9000m),
            ("yellow", "YY") => (12, 0.8500m),
            ("yellow", "ZZ") => (20, 0.8200m),
            ("red",    "XX") => (30, 0.7500m),
            ("red",    "YY") => (25, 0.6800m),
            ("red",    "ZZ") => (40, 0.6500m),
            _                => (0,  1.0000m),
        };
    }

    private static decimal ComputeBaselineInternalGrandAvg()
    {
        decimal sum = 0m;
        for (var s = 0; s < 3; s++)
            for (var p = 0; p < 3; p++)
                sum += YyCycleDays[s, p];
        return decimal.Round(sum / 9m, 3);
    }

    private static decimal[] ComputeBaselineSupplierAvg()
    {
        var arr = new decimal[3];
        for (var s = 0; s < 3; s++)
            arr[s] = decimal.Round((YyCycleDays[s, 0] + YyCycleDays[s, 1] + YyCycleDays[s, 2]) / 3m, 3);
        return arr;
    }

    private static decimal[] ComputeBaselineSpecAvg()
    {
        var arr = new decimal[3];
        for (var p = 0; p < 3; p++)
            arr[p] = decimal.Round((YyCycleDays[0, p] + YyCycleDays[1, p] + YyCycleDays[2, p]) / 3m, 3);
        return arr;
    }

    /// <summary>fixture 级 status 分类（seed-time once），运行期不再重算。</summary>
    private static string ClassifyStatus(decimal cycleDays)
    {
        if (cycleDays <= 15.0m) return "green";
        if (cycleDays <= 20.0m) return "yellow";
        return "red";
    }
}