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>
    internal const string Sql =
        """
        SELECT
            CONCAT(e.bill_no, '#', e.entry_seq)                     AS SourceObjectId,
            e.bill_no                                              AS RelatedObjectCode,
            COALESCE(NULLIF(o.custom_name, ''), o.custom_no)       AS RelatedObjectName,
            e.plan_date                                            AS DueAt,
            DATEDIFF(e.sys_capacity_date, e.plan_date)             AS DelayDays,
            e.bill_no          AS BillNo,
            e.entry_seq        AS EntrySeq,
            e.plan_date        AS PlanDate,
            e.sys_capacity_date AS SysCapacityDate,
            e.qty              AS Qty,
            e.item_number      AS ItemNumber,
            e.item_name        AS ItemName,
            o.custom_no        AS CustomNo,
            o.custom_name      AS CustomName,
            o.bill_no          AS OrderBillNo
        FROM crm_seorderentry e
        JOIN crm_seorder o ON o.Id = e.seorder_id
        WHERE e.tenant_id = @TenantId
          AND e.sys_capacity_date IS NOT NULL
          AND e.plan_date IS NOT NULL
          AND o.closed = 0
          AND o.IsDeleted = 0
          AND e.IsDeleted = 0
          AND e.bill_no <> ''
          AND e.entry_seq 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; }
    }
}