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>（而不是取全量再在内存里筛）：
/// 订单行表当前 390 行，其中 385 行早已过期、只有 40 行有 ETA。
/// 把「尚未到期 + 有 ETA + 未关闭」这三个条件留在 SQL 里，
/// evaluator 拿到的就只有真正的候选行，既省一次全表搬运，
/// 也让「为什么这一行没被判」在 SQL 层就能解释清楚。</para>
///
/// <para><b>时间比较用 <c>NOW()</c>（数据库时区）而不是应用侧 <c>DateTime.Now</c></b>：
/// <c>plan_date</c> 与 <c>sys_capacity_date</c> 都是库里的日期，比较双方应当同源。
/// 混用应用时钟会在跨时区部署时把「今天到期」判成「已过期」，
/// 而这条规则的整个价值恰恰建立在「还没到期」这个前提上。</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><c>e.plan_date &gt;= NOW()</c> 是本规则与「已逾期」规则的分界线，
    /// 也是它敢叫「预警」的唯一依据：客户交期还没到，风险已经算出来了。</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 e.sys_capacity_date > e.plan_date
          AND e.plan_date >= NOW()
          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; }
    }
}