AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/Rules/DataAccess/Providers/S8OrderDeliveryDataProvider.cs

using Microsoft.Extensions.Logging;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Service.S8.Rules.DataAccess.Providers;

/// <summary>
/// S8-RULE02-ORDER-DELIVERY-WARNING-1：S1 订单交付风险取数。
///
/// <para><b>SQL 只负责「哪些订单行值得看」，不负责「哪些算异常」</b>
/// （与 <see cref="S8PurchaseDeliveryDataProvider"/> 同一条纪律）：
/// 过滤只到「有 ETA + 单据还活着 + 身份完整」为止，是否延期一律交给 evaluator 判。</para>
///
/// <para><b>⚠️ 绝不要把时间条件或延期条件加回 WHERE。</b>
/// S8 的恢复判定唯一依据是「dedup_key 还在不在本轮 hits 里」
/// （<c>S8WatchSchedulerService.ReconcileRecoveriesForRuleAsync</c>），
/// 它<b>无法区分</b>「这一行不在结果集里」与「这一行还在但不再越界」。
/// 因此任何被 SQL 滤掉的行都等价于「风险已解除」：
/// <list type="bullet">
///   <item>加回 <c>plan_date &gt;= NOW()</c> → 订单跨过客户交期时行消失 →
///     风险<b>兑现</b>的那一刻异常被标成「已恢复」。这正是 2026-09-07 实测复现并修掉的缺陷
///     （真库两条命中，跨期后数据集返回 0 行，而 ETA 一天未改、仍晚于交期）。</item>
///   <item>加回 <c>sys_capacity_date &gt; plan_date</c> → ETA 修回交期以内时行同样消失，
///     恢复结论虽然碰巧正确，理由却是错的；下次判据变化就不成立了。</item>
/// </list>
/// 「只在到期前立案」这条业务约束由规则定义的
/// <c>CreateOnlyBeforeDueAt</c> 表达，<b>不在这里</b>。</para>
///
/// <para><b>不做任何日期加减补偿</b>：不加 +24h、不补 23:59:59、不提前 N 天。
/// 那些都是在没有业务依据的情况下改判据。</para>
/// </summary>
public sealed class S8OrderDeliveryDataProvider : IS8MonitoringDataProvider
{
    internal const string ExpectedSourceObjectType = "SALES_ORDER_LINE";

    /// <summary>与 <c>S8PurchaseDeliveryDataProvider</c> 同一口径：dedup_key 列宽上限。</summary>
    internal const int DedupKeyMaxLength = 128;

    /// <summary>
    /// 取数 SQL。
    ///
    /// <para><c>DelayDays</c> 就是 <c>measured_value</c>：系统建议交期比客户要求交期晚几天。
    /// 用 <c>DATEDIFF</c> 而不是时间戳差 —— 两个字段在业务上都是「哪一天交」，
    /// 精确到秒的差值没有业务含义，反而会让 12.0 天和 11.6 天表现不同。</para>
    ///
    /// <para>结果集 = <b>全部有 ETA 且单据还活着的订单行</b>，含已过交期的。
    /// 其中 <c>DelayDays &lt;= 0</c> 的行由 evaluator 判为不命中 —— 它们必须留在结果集里，
    /// 「行还在但不越界」才是这条规则唯一合法的恢复形态。</para>
    /// </summary>
    /// <remarks>
    /// <b>数据来源是数据中台标准层，不是业务源表。</b>本 Provider 不再读取
    /// <c>crm_seorderentry</c> / <c>crm_seorder</c>；订单行的入站、租户归一与
    /// 「源侧已删除的行要淘汰」都由 S1 的 <c>mdp_stg_so → mdp_std_so</c> 链路负责。
    ///
    /// <para><b>字段是中台的标准列名</b>：客户要求交期 = <c>plan_delivery_date</c>，
    /// 系统建议交期(ETA) = <c>capacity_date</c>。<b>不要</b>被
    /// <c>customer_request_date</c>（源 <c>rdate</c>）或 <c>promised_delivery_date</c>
    /// （源 <c>date</c>）带偏 —— 那是另外两个字段，与本规则判定无关。</para>
    ///
    /// <para><b>谓词与切源前逐条等价</b>：<c>closed</c> / <c>deleted_flag</c> 对应原来的
    /// <c>o.closed</c> / <c>o.IsDeleted</c> + <c>e.IsDeleted</c>；两个日期的 NOT NULL、
    /// 单号非空、行号非空全部保留。原先的 <c>INNER JOIN crm_seorder</c> 在中台侧已内化：
    /// <c>mdp_std_so</c> 本身就是「订单行 ⋈ 订单头」的产物。
    /// 实测（本地，两租户）连续三轮全量同步：40 行逐行一致，
    /// only_source = 0 / only_mdp = 0 / field_mismatch = 0，Hit 与 CreateEligible 交叉表完全相同。</para>
    ///
    /// <para><b>仍然不按延期天数预筛</b>：<c>DelayDays &lt;= 0</c> 的行必须留在结果集里，
    /// 「行还在但不越界」才是这条规则唯一合法的恢复形态；在取数层滤掉它们会让
    /// 「恢复」退化成「源行消失」。</para>
    ///
    /// <para><b>刻意不写 <c>source_table = '...'</c> 这类判据</b>：
    /// <c>mdp_std_so</c> 只有一个写入方（S1 的标准层构建），按契约只承载订单行，
    /// 该过滤今天恒真、实测加与不加结果逐行相同。写上它等于把源系统的表名
    /// 重新塞回 S8 —— 本次中台化要消除的正是这种源表知识。
    /// 若将来该表被扩展成多来源，应由中台侧提供正式的语义列（而不是让消费者认表名）。</para>
    /// </remarks>
    internal const string Sql =
        """
        SELECT
            CONCAT(s.order_no, '#', s.order_line)                        AS SourceObjectId,
            s.order_no                                                   AS RelatedObjectCode,
            COALESCE(NULLIF(s.customer_name, ''), s.customer_no)         AS RelatedObjectName,
            s.plan_delivery_date                                         AS DueAt,
            DATEDIFF(s.capacity_date, s.plan_delivery_date)              AS DelayDays,
            s.order_no            AS BillNo,
            CASE WHEN s.order_line REGEXP '^-?[0-9]+$'
                 THEN CONVERT(s.order_line, SIGNED) END                  AS EntrySeq,
            s.plan_delivery_date  AS PlanDate,
            s.capacity_date       AS SysCapacityDate,
            s.order_qty           AS Qty,
            s.item_code           AS ItemNumber,
            s.item_name           AS ItemName,
            s.customer_no         AS CustomNo,
            s.customer_name       AS CustomName,
            s.order_no            AS OrderBillNo
        FROM mdp_std_so s
        WHERE s.tenant_id = @TenantId
          AND s.capacity_date IS NOT NULL
          AND s.plan_delivery_date IS NOT NULL
          AND IFNULL(s.closed, 0) = 0
          AND IFNULL(s.deleted_flag, 0) = 0
          AND s.order_no <> ''
          AND s.order_line IS NOT NULL
        """;

    private readonly ISqlSugarClient _db;
    private readonly ILogger<S8OrderDeliveryDataProvider> _logger;

    public S8OrderDeliveryDataProvider(
        ISqlSugarClient db,
        ILogger<S8OrderDeliveryDataProvider> logger)
    {
        _db = db;
        _logger = logger;
    }

    public string DatasetCode => S8OrderDeliveryDatasetDefinitions.OrderDeliveryRiskCode;

    public S8DatasetCapabilities Capabilities =>
        S8OrderDeliveryDatasetDefinitions.OrderDeliveryRiskCapabilities;

    public async Task<S8MonitoringRowSet> LoadAsync(
        S8MonitoringDataRequest request,
        CancellationToken cancellationToken = default)
    {
        request.EnsureValid();

        var rows = await _db.Ado.SqlQueryAsync<OrderDeliveryRow>(
            Sql,
            new SugarParameter[] { new("@TenantId", request.TenantId) });

        var budget = ResolveSourceObjectIdBudget(request);
        var mapped = new List<S8MonitoringRow>(rows.Count);
        var skipped = 0;

        foreach (var row in rows)
        {
            var sourceObjectId = row.SourceObjectId ?? string.Empty;
            if (sourceObjectId.Length > budget)
            {
                // 超长的 source_object_id 会让 dedup_key 被截断 —— 截断后两个不同订单行
                // 可能算出同一个键，于是第二行永远建不出异常且没有任何报错。
                // 宁可跳过并留证，也不要制造一个静默丢单的去重键。
                skipped++;
                _logger.LogWarning(
                    "s8_order_delivery_soid_over_dedup_budget rule={RuleCode} tenant={Tenant} " +
                    "soid={Soid} length={Length} budget={Budget}",
                    request.RuleCode, request.TenantId, sourceObjectId, sourceObjectId.Length, budget);
                continue;
            }

            mapped.Add(S8MonitoringRow.FromValues(ToCanonicalValues(row)));
        }

        _logger.LogDebug(
            "s8_order_delivery_loaded rule={RuleCode} tenant={Tenant} rows={Rows} skipped={Skipped}",
            request.RuleCode, request.TenantId, mapped.Count, skipped);

        return S8MonitoringRowSet.FromRows(mapped);
    }

    /// <summary>dedup_key 前缀之外还能留给 source_object_id 的长度。与采购交付 Provider 同一算法。</summary>
    internal static int ResolveSourceObjectIdBudget(S8MonitoringDataRequest request)
    {
        var prefixLength =
            $"T{request.TenantId}:R{request.RuleCode}:{ExpectedSourceObjectType}:".Length;
        return Math.Max(0, DedupKeyMaxLength - prefixLength);
    }

    /// <summary>
    /// 行 → canonical 列 + 展示字段。
    /// <para>canonical 的 <c>measured_value</c> 是 <c>DelayDays</c>；
    /// <c>due_at</c> 放客户要求交期，只用于展示与排序，<b>不参与判定</b>。</para>
    /// </summary>
    internal static Dictionary<string, object> ToCanonicalValues(OrderDeliveryRow row) => new(StringComparer.OrdinalIgnoreCase)
    {
        [S8CanonicalColumns.SourceObjectId] = row.SourceObjectId,
        [S8CanonicalColumns.RelatedObjectCode] = row.RelatedObjectCode,
        [S8CanonicalColumns.RelatedObjectName] = row.RelatedObjectName,
        [S8CanonicalColumns.DueAt] = row.DueAt,
        [S8CanonicalColumns.MeasuredValue] = row.DelayDays,
        ["bill_no"] = row.BillNo,
        ["entry_seq"] = row.EntrySeq,
        ["plan_date"] = row.PlanDate,
        ["sys_capacity_date"] = row.SysCapacityDate,
        ["delay_days"] = row.DelayDays,
        ["qty"] = row.Qty,
        ["item_number"] = row.ItemNumber,
        ["item_name"] = row.ItemName,
        ["custom_no"] = row.CustomNo,
        ["custom_name"] = row.CustomName,
        ["order_bill_no"] = row.OrderBillNo
    };

    /// <summary>SQL 投影行。字段名与 SELECT 别名一一对应。</summary>
    internal sealed class OrderDeliveryRow
    {
        public string SourceObjectId { get; set; } = string.Empty;
        public string RelatedObjectCode { get; set; } = string.Empty;
        public string? RelatedObjectName { get; set; }
        public DateTime? DueAt { get; set; }
        public decimal? DelayDays { get; set; }
        public string? BillNo { get; set; }
        public int? EntrySeq { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? SysCapacityDate { get; set; }
        public decimal? Qty { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? CustomNo { get; set; }
        public string? CustomName { get; set; }
        public string? OrderBillNo { get; set; }
    }
}