AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/FinishedWarehouse/S7MdpSyncTransformService.cs

using Admin.NET.Core.Service;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Microsoft.Extensions.Logging;
using System.Text.Json;

namespace Admin.NET.Plugin.AiDOP.FinishedWarehouse;

/// <summary>
/// S7 成品仓储 — KPI 计算与刷新转换服务。
/// 双模式：读本地标准层 mdp_std_t8_kc_*（由 T8BaseInboundMdpSyncService 从 T8 贴源→标准），不再直连 T8。
/// 计算逻辑沿用方老师 v5.4 KPI J 列口径（等价改写为 MySQL 读 std）。
/// 包含 KPI：S7_L1_001 订单发货周期 / S7_L1_002 订单发货满足率 / S7_L1_003 成品仓储人效。
/// </summary>
public class S7MdpSyncTransformService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SysNoticeService _sysNoticeService;
    private readonly ILogger<S7MdpSyncTransformService> _logger;
    private readonly SmartOps.KpiCalcDispatcher _kpiCalcDispatcher;
    private readonly SmartOps.KpiDimensionRunService _dimensionRun;
    private readonly SmartOps.IKpiTargetResolver _kpiTargetResolver;
    private const string JobCode = "S7_MDP_SYNC_TRANSFORM";
    private const string JobName = "S7 成品仓储 MDP 同步与转换";
    private const string ModuleCode = "S7";
    // FAILURE-NOTIFICATION-1：超级管理员 superAdmin.NET（AccountType=999）
    private const long NoticeReceiverUserId = 1300000000101L;
    private const string NoticeReceiverUserName = "超级管理员";

    public S7MdpSyncTransformService(
        ISqlSugarClient db,
        SysNoticeService sysNoticeService,
        ILogger<S7MdpSyncTransformService> logger,
        SmartOps.KpiCalcDispatcher kpiCalcDispatcher,
        SmartOps.KpiDimensionRunService dimensionRun,
        SmartOps.IKpiTargetResolver kpiTargetResolver)
    {
        _db = db;
        _sysNoticeService = sysNoticeService;
        _logger = logger;
        _kpiCalcDispatcher = kpiCalcDispatcher;
        _dimensionRun = dimensionRun;
        _kpiTargetResolver = kpiTargetResolver;
    }

    public async Task<S7MdpSyncTransformResult> RunFullAsync(
        CancellationToken cancellationToken = default,
        string triggerType = "AUTO",
        S7MdpRefreshOption? option = null)
    {
        cancellationToken.ThrowIfCancellationRequested();
        option ??= S7MdpRefreshOption.Default();
        NormalizeOption(option);

        var now = DateTime.Now;
        var batchId = $"S7_MDP_FULL_{now:yyyyMMddHHmmss}";
        var normalizedTrigger = NormalizeTriggerType(triggerType);
        var runLogId = await InsertTransformRunLogAsync(batchId, now, normalizedTrigger, option);

        var result = new S7MdpSyncTransformResult
        {
            BatchId = batchId,
            RunLogId = runLogId,
            TriggerType = normalizedTrigger,
            SourceZtid = option.SourceZtid,
            TargetTenantId = option.TargetTenantId,
            TargetFactoryId = option.TargetFactoryId,
            BizDate = option.BizDate,
            BizMonth = option.BizMonth,
            MonthlyPeriodStart = option.MonthlyPeriodStart,
            MonthlyPeriodEnd = option.MonthlyPeriodEnd
        };

        try
        {
            result.StageRows = 0;
            result.StandardRows = 0;

            var sub25 = await BuildS7L1001OrderShipmentCycleAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S7_L1_001", sub25);

            var sub26 = await BuildS7L1002OrderShipmentFulfillmentAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S7_L1_002", sub26);

            var sub27 = await BuildS7L1003FinishedWarehouseEfficiencyAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S7_L1_003", sub27);

            var sub28 = await BuildS9L1001QualityReturnRateAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S9_L1_001", sub28);

            await MarkTransformRunSuccessAsync(runLogId, now, result);
            return result;
        }
        catch (Exception ex)
        {
            await MarkTransformRunFailedAsync(runLogId, now, ex.Message, batchId);
            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────────────────

    /// <summary>S7_L1_001 订单发货周期 = 最晚发货日期 - 最早 FQC 报检日期（5 表 JOIN）。</summary>
    private async Task<KpiBuildSubResult> BuildS7L1001OrderShipmentCycleAsync(
        string batchId, DateTime now, S7MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        // 双模式：读本地标准层 mdp_std_t8_*（源 identity Id→src_id）；datediff(day,a,b)→DATEDIFF(b,a)，IsNull→IFNULL。
        const string sql = @"
select noid as noid,
       datediff(max(shtime), min(shdate)) as scts
 from (
   select a.noid as noid, b.code as code,
          ifnull(c.shdate, b.addtime) as shdate,
          (case when b.gdyn=1 then b.gdtime else d.shtime end) as shtime,
          (case when b.gdyn=1 or b.sl<=b.slzx then 1 else 0 end) as wczt
    from mdp_std_t8_kc_dd_head a
    left join mdp_std_t8_kc_dd_list b on a.src_id=b.idid and b.tenant_id=a.tenant_id
    left join (
      select a.tenant_id as tenant_id, min(b.src_id) as id, b.lynoid as lynoid, b.code as code,
             max(a.shtime) as shtime, sum(b.slzx) as slzx
       from mdp_std_t8_kc_tz_head a
       inner join mdp_std_t8_kc_tz_list b on a.src_id=b.idid and b.tenant_id=a.tenant_id
       where a.tenant_id=@tenantId and a.ztid=@ztid and a.lbs='销售出库' and a.hzyn=0 and a.zfyn=0 and a.shyn=1
       group by a.tenant_id, b.lynoid, b.code
    ) d on b.rwnoid=d.lynoid and b.code=d.code and d.tenant_id=a.tenant_id
    left join mdp_std_t8_kc_zj_list c on a.ztid=c.ztid and c.lyid=b.src_id and c.zjyn=1 and c.tenant_id=a.tenant_id
    where a.tenant_id=@tenantId and a.ztid=@ztid and a.lbs='销售订单' and a.zf=0 and a.shyn=1
 ) n
 group by noid
 having min(wczt)=1";

        var rows = await _db.Ado.SqlQueryAsync<S7CycleRow>(sql, new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@ztid", option.SourceZtid)
        });
        sub.T8Rows = rows.Count;

        var dwdAffected = 0;
        var cycleList = new List<int>();
        foreach (var r in rows)
        {
            ct.ThrowIfCancellationRequested();
            if (string.IsNullOrEmpty(r.noid)) continue;
            if (r.scts.HasValue) cycleList.Add(r.scts.Value);

            dwdAffected += await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_t8_order_shipment_cycle
  (tenant_id, factory_id, biz_date, source_ztid, order_no, cycle_days, batch_id, create_time)
VALUES
  (@tenantId, @factoryId, @bizDate, @ztid, @orderNo, @cycleDays, @batchId, @now)
ON DUPLICATE KEY UPDATE
  cycle_days=VALUES(cycle_days),
  batch_id=VALUES(batch_id), update_time=@now",
                new SugarParameter("@tenantId", option.TargetTenantId),
                new SugarParameter("@factoryId", option.TargetFactoryId),
                new SugarParameter("@bizDate", option.BizDate),
                new SugarParameter("@ztid", option.SourceZtid),
                new SugarParameter("@orderNo", r.noid),
                new SugarParameter("@cycleDays", r.scts),
                new SugarParameter("@batchId", batchId),
                new SugarParameter("@now", now));
        }
        sub.DwdRows = dwdAffected;

        // 数据准备(dwd 逐单 cycle_days)已完成。最终聚合交分发器（日 KPI：period 复用 BizDate，SQL 按 biz_date 圈选）。
        decimal? legacyValue = cycleList.Count > 0
            ? Math.Round((decimal)cycleList.Average(), 4)
            : null;
        var legacyDenom = cycleList.Count > 0 ? "OK" : "NO_COMPLETED_ORDER";
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S7_L1_001", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, legacyValue, legacyDenom, ct);
        sub.KpiRows = dispatch.ShouldUpsert
            ? await UpsertKpiValueAsync("S7_L1_001", option.BizDate, dispatch.MetricValue, now, option)
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;

        // 调度：SUMMARY 成功/NO_DATA 后触发对应 DIMENSION 跑批（共享 BatchId；SUMMARY FAILED 不触发）。
        // 维度失败不影响汇总链路（内部已落 dimension_run_log）。
        if (dispatch.ShouldUpsert)
        {
            try
            {
                await _dimensionRun.RunDimensionAsync(
                    "S7_L1_001", ModuleCode, option.TargetTenantId, option.BizDate, batchId, triggerType, ct);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "S7_L1_001 维度跑批异常（不影响汇总链路）");
            }
        }
        return sub;
    }

    /// <summary>S7_L1_002 订单发货满足率 = (交期前发货行数 / 该订单总行数) × 100%。</summary>
    private async Task<KpiBuildSubResult> BuildS7L1002OrderShipmentFulfillmentAsync(
        string batchId, DateTime now, S7MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        // 双模式：读标准层；convert(varchar(10),shtime,23)→date(shtime)。
        const string sql = @"
select noid as noid,
       count(noid) as total_rows,
       sum(wczt) as in_window_rows
 from (
   select a.noid as noid, b.rwnoid as rwnoid, b.code as code,
          (case when sum(d.slzx)>=b.sl then 1 else 0 end) as wczt
    from mdp_std_t8_kc_dd_head a
    left join mdp_std_t8_kc_dd_list b on a.src_id=b.idid and b.tenant_id=a.tenant_id
    left join (
      select a.tenant_id as tenant_id, b.lynoid as lynoid, b.code as code,
             date(a.shtime) as shtime, b.slzx as slzx
       from mdp_std_t8_kc_tz_head a
       inner join mdp_std_t8_kc_tz_list b on a.src_id=b.idid and b.tenant_id=a.tenant_id
       where a.tenant_id=@tenantId and a.ztid=@ztid and a.lbs='销售出库' and a.hzyn=0 and a.zfyn=0 and a.shyn=1
    ) d on b.rwnoid=d.lynoid and b.code=d.code and d.shtime<=b.jhdate and d.tenant_id=a.tenant_id
    where a.tenant_id=@tenantId and a.ztid=@ztid and a.lbs='销售订单' and a.zf=0 and a.shyn=1
    group by a.noid, b.rwnoid, b.code, b.sl
 ) n
 group by noid";

        var rows = await _db.Ado.SqlQueryAsync<S7FulfillmentRow>(sql, new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@ztid", option.SourceZtid)
        });
        sub.T8Rows = rows.Count;

        var dwdAffected = 0;
        var rateList = new List<decimal>();
        foreach (var r in rows)
        {
            ct.ThrowIfCancellationRequested();
            if (string.IsNullOrEmpty(r.noid)) continue;
            decimal? rate = (r.total_rows > 0)
                ? Math.Round((decimal)r.in_window_rows / r.total_rows * 100m, 4)
                : null;
            if (rate.HasValue) rateList.Add(rate.Value);

            dwdAffected += await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_t8_order_shipment_fulfillment
  (tenant_id, factory_id, biz_date, source_ztid, order_no,
   total_rows, in_window_rows, fulfillment_rate, batch_id, create_time)
VALUES
  (@tenantId, @factoryId, @bizDate, @ztid, @orderNo,
   @total, @inWindow, @rate, @batchId, @now)
ON DUPLICATE KEY UPDATE
  total_rows=VALUES(total_rows), in_window_rows=VALUES(in_window_rows),
  fulfillment_rate=VALUES(fulfillment_rate),
  batch_id=VALUES(batch_id), update_time=@now",
                new SugarParameter("@tenantId", option.TargetTenantId),
                new SugarParameter("@factoryId", option.TargetFactoryId),
                new SugarParameter("@bizDate", option.BizDate),
                new SugarParameter("@ztid", option.SourceZtid),
                new SugarParameter("@orderNo", r.noid),
                new SugarParameter("@total", r.total_rows),
                new SugarParameter("@inWindow", r.in_window_rows),
                new SugarParameter("@rate", rate),
                new SugarParameter("@batchId", batchId),
                new SugarParameter("@now", now));
        }
        sub.DwdRows = dwdAffected;

        // 数据准备(dwd 逐单 fulfillment_rate，已 ×100)已完成。最终聚合交分发器（CONFIG_SQL 直接 AVG 该列不再 ×100）。
        decimal? legacyValue = rateList.Count > 0
            ? Math.Round(rateList.Average(), 4)
            : null;
        var legacyDenom = rateList.Count > 0 ? "OK" : "NO_VALID_ORDER";
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S7_L1_002", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, legacyValue, legacyDenom, ct);
        sub.KpiRows = dispatch.ShouldUpsert
            ? await UpsertKpiValueAsync("S7_L1_002", option.BizDate, dispatch.MetricValue, now, option)
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;

        // 调度：SUMMARY 成功/NO_DATA 后触发对应 DIMENSION 跑批（共享 BatchId；SUMMARY FAILED 不触发）。
        if (dispatch.ShouldUpsert)
        {
            try
            {
                await _dimensionRun.RunDimensionAsync(
                    "S7_L1_002", ModuleCode, option.TargetTenantId, option.BizDate, batchId, triggerType, ct);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "S7_L1_002 维度跑批异常（不影响汇总链路）");
            }
        }
        return sub;
    }

    /// <summary>S7_L1_003 成品仓储人效 = SUM(slzx where lbs=销售出库) / count(gw=仓管)。</summary>
    private async Task<KpiBuildSubResult> BuildS7L1003FinishedWarehouseEfficiencyAsync(
        string batchId, DateTime now, S7MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        const string sqlNumer = @"
select b.lynoid as lynoid, b.code as code,
       date(a.shtime) as shtime, b.slzx as slzx
 from mdp_std_t8_kc_tz_head a
 inner join mdp_std_t8_kc_tz_list b on a.src_id=b.idid and b.tenant_id=a.tenant_id
 where a.tenant_id=@tenantId and a.ztid=@ztid and a.lbs='销售出库' and a.hzyn=0 and a.zfyn=0 and a.shyn=1
  and date(a.shtime) between @startDateText and @endDateText";
        const string sqlDenom = @"
select count(*) as penum
 from mdp_std_t8_sys_pelist
 where tenant_id=@tenantId and ztid=@ztid and zzzt='在职' and gw='仓管'";

        var pNumer = new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@ztid", option.SourceZtid),
            new SugarParameter("@startDateText", option.MonthlyPeriodStart.ToString("yyyy-MM-dd")),
            new SugarParameter("@endDateText", option.MonthlyPeriodEnd.ToString("yyyy-MM-dd"))
        };
        var pDenom = new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@ztid", option.SourceZtid)
        };

        var numerRows = await _db.Ado.SqlQueryAsync<S7ShipmentDetailRow>(sqlNumer, pNumer);
        var denomRows = await _db.Ado.SqlQueryAsync<S7PeNumRow>(sqlDenom, pDenom);
        sub.T8Rows = numerRows.Count + denomRows.Count;

        decimal? shipmentQty = numerRows.Sum(r => r.slzx ?? 0m);
        if (numerRows.Count == 0) shipmentQty = null;

        int? headcount = denomRows.FirstOrDefault()?.penum;

        decimal? efficiency = null;
        string denomStatus;
        if (!headcount.HasValue || headcount.Value <= 0)
            denomStatus = "NO_HEADCOUNT";
        else if (!shipmentQty.HasValue)
            denomStatus = "NO_NUMERATOR";
        else
        {
            efficiency = Math.Round(shipmentQty.Value / headcount.Value, 4);
            denomStatus = "OK";
        }
        sub.DenominatorStatus = denomStatus;

        var dwdAffected = await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_t8_finished_warehouse_efficiency
  (tenant_id, factory_id, biz_month, source_ztid, period_start, period_end,
   shipment_qty, warehouse_headcount, efficiency, denominator_status, batch_id, create_time)
VALUES
  (@tenantId, @factoryId, @bizMonth, @ztid, @periodStart, @periodEnd,
   @shipmentQty, @headcount, @efficiency, @denomStatus, @batchId, @now)
ON DUPLICATE KEY UPDATE
  period_start=VALUES(period_start), period_end=VALUES(period_end),
  shipment_qty=VALUES(shipment_qty), warehouse_headcount=VALUES(warehouse_headcount),
  efficiency=VALUES(efficiency), denominator_status=VALUES(denominator_status),
  batch_id=VALUES(batch_id), update_time=@now",
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@factoryId", option.TargetFactoryId),
            new SugarParameter("@bizMonth", option.BizMonth),
            new SugarParameter("@ztid", option.SourceZtid),
            new SugarParameter("@periodStart", option.MonthlyPeriodStart),
            new SugarParameter("@periodEnd", option.MonthlyPeriodEnd),
            new SugarParameter("@shipmentQty", shipmentQty),
            new SugarParameter("@headcount", headcount),
            new SugarParameter("@efficiency", efficiency),
            new SugarParameter("@denomStatus", denomStatus),
            new SugarParameter("@batchId", batchId),
            new SugarParameter("@now", now));
        sub.DwdRows = dwdAffected;

        // 月度 KPI 最终聚合交分发器；bizDate=月末、period=当月窗口；
        //   legacyValue=efficiency、legacyDenom 保留 NO_HEADCOUNT/NO_NUMERATOR（CONFIG_SQL 下塌缩为 NO_DATA）。
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S7_L1_003", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.MonthlyPeriodEnd, option.MonthlyPeriodStart, option.MonthlyPeriodEnd, option.SourceZtid,
            batchId, triggerType, efficiency, denomStatus, ct);
        sub.KpiRows = dispatch.ShouldUpsert
            ? await UpsertKpiValueAsync("S7_L1_003", option.MonthlyPeriodEnd, dispatch.MetricValue, now, option)
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;

        // 调度：SUMMARY 成功/NO_DATA 后触发人效月度 DIMENSION 跑批（月度：@biz_date=月末派生 biz_month）。
        if (dispatch.ShouldUpsert)
        {
            try
            {
                await _dimensionRun.RunDimensionAsync(
                    "S7_L1_003", ModuleCode, option.TargetTenantId, option.MonthlyPeriodEnd, batchId, triggerType, ct);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "S7_L1_003 维度跑批异常（不影响汇总链路）");
            }
        }
        return sub;
    }

    // ─────────────────────────────────────────────────────────────────────────

    /// <summary>S9_L1_001 质量退货率 = 销售退货数量 / 销售出库数量 × 1,000,000 PPM。</summary>
    private async Task<KpiBuildSubResult> BuildS9L1001QualityReturnRateAsync(
        string batchId, DateTime now, S7MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();
        var summary = await _db.Ado.SqlQuerySingleAsync<S7QualityReturnSummaryRow>(
            """
            SELECT
                SUM(CASE WHEN h.lbs='销售退货' THEN ABS(IFNULL(l.slzx,0)) ELSE 0 END) AS return_qty,
                SUM(CASE WHEN h.lbs='销售出库' THEN ABS(IFNULL(l.slzx,0)) ELSE 0 END) AS shipped_qty
            FROM mdp_std_t8_kc_tz_head h
            INNER JOIN mdp_std_t8_kc_tz_list l
                ON l.tenant_id=h.tenant_id AND l.idid=h.src_id
            WHERE h.tenant_id=@TenantId AND h.ztid=@Ztid
              AND h.hzyn=0 AND h.zfyn=0 AND h.shyn=1
              AND h.lbs IN ('销售退货','销售出库')
              AND h.shtime BETWEEN @PeriodStart AND @PeriodEnd
            """,
            new SugarParameter("@TenantId", option.TargetTenantId),
            new SugarParameter("@Ztid", option.SourceZtid),
            new SugarParameter("@PeriodStart", option.MonthlyPeriodStart),
            new SugarParameter("@PeriodEnd", option.MonthlyPeriodEnd));

        sub.T8Rows = summary == null ? 0 : 1;
        decimal? ppm = summary?.shipped_qty > 0
            ? Math.Round((summary.return_qty ?? 0m) / summary.shipped_qty.Value * 1_000_000m, 4)
            : null;
        var denominatorStatus = summary?.shipped_qty > 0 ? "OK" : "NO_SHIPMENT";
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S9_L1_001", "S9", option.TargetTenantId, option.TargetFactoryId,
            option.MonthlyPeriodEnd, option.MonthlyPeriodStart, option.MonthlyPeriodEnd, option.SourceZtid,
            batchId, triggerType, ppm, denominatorStatus, ct);
        sub.KpiRows = dispatch.ShouldUpsert
            ? await UpsertKpiValueAsync("S9_L1_001", option.MonthlyPeriodEnd, dispatch.MetricValue, now, option, "S9")
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;
        return sub;
    }

    private async Task<int> UpsertKpiValueAsync(
        string metricCode,
        DateTime bizDate,
        decimal? metricValue,
        DateTime now,
        S7MdpRefreshOption option,
        string moduleCode = ModuleCode)
    {
        // 沿用 S3 UpsertS3KpiValueAsync 范式：先查现存行 → UPDATE；不存在 → SELECT MAX(id)+1 显式生成 id 后 INSERT。
        // ado_s9_kpi_value_l1_day.id 为手工分配主键（无 AUTO_INCREMENT），必须显式 set；
        // metric_value 允许 NULL（分母缺失不得伪装真实 0）。
        // FIX-2：截断时分秒（月度 KPI 入参可能为 YYYY-MM-DD 23:59:59），保证 SELECT WHERE biz_date=@BizDate 与 DB date 列匹配，避免重复 INSERT。
        // FIX-1：tenant_id/factory_id 取自 option，默认仍为 1300000000001/1，不破坏 Demo。
        bizDate = bizDate.Date;
        var snap = await _kpiTargetResolver.ResolveAsync(option.TargetTenantId, option.TargetFactoryId, metricCode, moduleCode, bizDate);
        var existingId = await _db.Ado.GetLongAsync(
            "SELECT IFNULL((SELECT id FROM ado_s9_kpi_value_l1_day WHERE tenant_id=@TenantId AND factory_id=@FactoryId " +
            "AND module_code=@ModuleCode AND metric_code=@MetricCode AND biz_date=@BizDate AND is_deleted=0 " +
            "ORDER BY id LIMIT 1), 0)",
            new List<SugarParameter>
            {
                new("@TenantId", option.TargetTenantId),
                new("@FactoryId", option.TargetFactoryId),
                new("@ModuleCode", moduleCode),
                new("@MetricCode", metricCode),
                new("@BizDate", bizDate)
            });

        if (existingId > 0)
        {
            return await _db.Ado.ExecuteCommandAsync(
                "UPDATE ado_s9_kpi_value_l1_day SET metric_value=@MetricValue, target_value=@TargetValue, " +
                "target_config_id=@TargetConfigId, target_source=@TargetSource, target_resolved_at=@TargetResolvedAt, " +
                "calc_time=@Now, update_time=@Now, is_deleted=0, is_active=1 WHERE id=@Id",
                new SugarParameter("@MetricValue", metricValue),
                new SugarParameter("@TargetValue", SmartOps.KpiTargetSnapshotSql.ValueOrDbNull(snap)),
                new SugarParameter("@TargetConfigId", SmartOps.KpiTargetSnapshotSql.ConfigIdOrDbNull(snap)),
                new SugarParameter("@TargetSource", SmartOps.KpiTargetSnapshotSql.SourceOrDbNull(snap)),
                new SugarParameter("@TargetResolvedAt", snap.ResolvedAt),
                new SugarParameter("@Now", now),
                new SugarParameter("@Id", existingId));
        }

        var nextId = await _db.Ado.GetLongAsync(
            "SELECT COALESCE(MAX(id), 0) + 1 FROM ado_s9_kpi_value_l1_day");
        return await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO ado_s9_kpi_value_l1_day
  (id, tenant_id, org_id, company_id, factory_id, status, biz_date,
   create_time, update_time, is_deleted, is_active,
   module_code, metric_code, metric_value, target_value, calc_time,
   target_config_id, target_source, target_resolved_at)
VALUES
  (@Id, @TenantId, NULL, NULL, @FactoryId, NULL, @BizDate,
   @Now, @Now, 0, 1,
   @ModuleCode, @MetricCode, @MetricValue, @TargetValue, @Now,
   @TargetConfigId, @TargetSource, @TargetResolvedAt)",
            new SugarParameter("@Id", nextId),
            new SugarParameter("@TenantId", option.TargetTenantId),
            new SugarParameter("@FactoryId", option.TargetFactoryId),
            new SugarParameter("@BizDate", bizDate),
            new SugarParameter("@Now", now),
            new SugarParameter("@ModuleCode", moduleCode),
            new SugarParameter("@MetricCode", metricCode),
            new SugarParameter("@MetricValue", metricValue),
            new SugarParameter("@TargetValue", SmartOps.KpiTargetSnapshotSql.ValueOrDbNull(snap)),
            new SugarParameter("@TargetConfigId", SmartOps.KpiTargetSnapshotSql.ConfigIdOrDbNull(snap)),
            new SugarParameter("@TargetSource", SmartOps.KpiTargetSnapshotSql.SourceOrDbNull(snap)),
            new SugarParameter("@TargetResolvedAt", snap.ResolvedAt));
    }

    private async Task<long> InsertTransformRunLogAsync(string batchId, DateTime startedAt, string triggerType, S7MdpRefreshOption option)
    {
        await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO mdp_transform_run_log
  (tenant_id, job_code, job_name, trigger_type, batch_id, status, start_time, stage_rows, standard_rows, dwd_rows, create_time, update_time)
VALUES
  (@TenantId, @JobCode, @JobName, @TriggerType, @BatchId, 'RUNNING', @StartTime, 0, 0, 0, @StartTime, @StartTime)",
            new SugarParameter("@TenantId", option.TargetTenantId),
            new SugarParameter("@JobCode", JobCode),
            new SugarParameter("@JobName", JobName),
            new SugarParameter("@TriggerType", triggerType),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@StartTime", startedAt));
        return await _db.Ado.GetLongAsync(
            "SELECT id FROM mdp_transform_run_log WHERE batch_id=@BatchId ORDER BY id DESC LIMIT 1",
            new List<SugarParameter> { new("@BatchId", batchId) });
    }

    private async Task MarkTransformRunSuccessAsync(long runLogId, DateTime startedAt, S7MdpSyncTransformResult result)
    {
        var finishedAt = DateTime.Now;
        await _db.Ado.ExecuteCommandAsync(@"
UPDATE mdp_transform_run_log
SET status='SUCCESS', end_time=@EndTime, duration_ms=@DurationMs,
    stage_rows=@StageRows, standard_rows=@StandardRows, dwd_rows=@DwdRows,
    summary_json=@SummaryJson, update_time=CURRENT_TIMESTAMP
WHERE id=@Id",
            new SugarParameter("@EndTime", finishedAt),
            new SugarParameter("@DurationMs", (int)(finishedAt - startedAt).TotalMilliseconds),
            new SugarParameter("@StageRows", result.StageRows),
            new SugarParameter("@StandardRows", result.StandardRows),
            new SugarParameter("@DwdRows", result.DwdRows),
            new SugarParameter("@SummaryJson", JsonSerializer.Serialize(new
            {
                batchId = result.BatchId,
                sourceZtid = result.SourceZtid,
                bizDate = result.BizDate.ToString("yyyy-MM-dd"),
                bizMonth = result.BizMonth,
                dwdRows = result.DwdRows,
                kpiRows = result.KpiRows,
                perKpiDwdRows = result.PerKpiDwdRows,
                perKpiKpiRows = result.PerKpiKpiRows,
                denominatorStatus = result.KpiDenominatorStatus
            })),
            new SugarParameter("@Id", runLogId));
    }

    private async Task MarkTransformRunFailedAsync(long runLogId, DateTime startedAt, string message, string batchId)
    {
        bool runLogUpdated = false;
        try
        {
            var finishedAt = DateTime.Now;
            await _db.Ado.ExecuteCommandAsync(@"
UPDATE mdp_transform_run_log
SET status='FAILED', end_time=@EndTime, duration_ms=@DurationMs,
    error_message=@ErrorMessage, update_time=CURRENT_TIMESTAMP
WHERE id=@Id",
                new SugarParameter("@EndTime", finishedAt),
                new SugarParameter("@DurationMs", (int)(finishedAt - startedAt).TotalMilliseconds),
                new SugarParameter("@ErrorMessage", Truncate(message, 2000)),
                new SugarParameter("@Id", runLogId));
            runLogUpdated = true;
        }
        catch (Exception ex)
        {
            Console.Error.WriteLine($"[S7MdpSyncTransform] MarkTransformRunFailed write failed (runLogId={runLogId}): {ex.Message}");
        }

        // FAILURE-NOTIFICATION-1：写库 FAILED 成功后发通知给超级管理员；通知失败不影响主流程
        if (!runLogUpdated) return;
        try
        {
            await _sysNoticeService.AddNotice(new AddNoticeInput
            {
                Title = "S7 成品仓储 T8 KPI 跑批失败",
                Content = $"模块：S7 成品仓储\n批次ID：{batchId}\n失败时间：{DateTime.Now:yyyy-MM-dd HH:mm:ss}\n错误信息：{Truncate(message, 1000)}\n\n请查看 mdp_transform_run_log 获取完整错误与重试记录。",
                Type = NoticeTypeEnum.NOTICE,
                PublicTime = DateTime.Now,
                Status = NoticeStatusEnum.PUBLIC,
                PublicUserId = NoticeReceiverUserId,
                PublicUserName = NoticeReceiverUserName
            });
        }
        catch (Exception notifyEx)
        {
            _logger.LogError(notifyEx, "[S7MdpSyncTransform] SysNotice 发送失败 (runLogId={RunLogId}, batchId={BatchId})", runLogId, batchId);
        }
    }

    private static string NormalizeTriggerType(string s) =>
        string.IsNullOrWhiteSpace(s) ? "AUTO" : s.Trim().ToUpperInvariant();

    private static void NormalizeOption(S7MdpRefreshOption option)
    {
        var d = S7MdpRefreshOption.Default();
        if (option.TargetFactoryId <= 0) option.TargetFactoryId = d.TargetFactoryId;
        if (string.IsNullOrWhiteSpace(option.SourceZtid)) option.SourceZtid = d.SourceZtid;
        option.TargetTenantId = AidopSourceTenantMap.ResolveTenantId(option.SourceZtid, option.TargetTenantId);
        if (option.BizDate == default) option.BizDate = d.BizDate;
        if (string.IsNullOrWhiteSpace(option.BizMonth)) option.BizMonth = d.BizMonth;
        if (option.MonthlyPeriodStart == default) option.MonthlyPeriodStart = d.MonthlyPeriodStart;
        if (option.MonthlyPeriodEnd == default) option.MonthlyPeriodEnd = d.MonthlyPeriodEnd;
    }

    private static string Truncate(string s, int max) =>
        string.IsNullOrEmpty(s) ? "" : (s.Length <= max ? s : s.Substring(0, max));
}

// DTO ────────────────────────────────────────────────────────────────────────

public sealed class S7MdpRefreshOption
{
    public string SourceZtid { get; set; } = "pbxfxp";
    /// <summary>KPI/DWD 落库目标租户；≤0 时由 <see cref="AidopSourceTenantMap"/> 按 SourceZtid 解析。</summary>
    public long TargetTenantId { get; set; }
    /// <summary>KPI/DWD 落库目标工厂；默认 1。</summary>
    public long TargetFactoryId { get; set; } = 1L;
    public DateTime BizDate { get; set; }
    public string BizMonth { get; set; } = "";
    public DateTime MonthlyPeriodStart { get; set; }
    public DateTime MonthlyPeriodEnd { get; set; }

    public static S7MdpRefreshOption Default()
    {
        var today = DateTime.Today;
        var yesterday = today.AddDays(-1);
        var lastMonth = today.AddMonths(-1);
        var monthStart = new DateTime(lastMonth.Year, lastMonth.Month, 1);
        var monthEnd = monthStart.AddMonths(1).AddDays(-1);
        return new S7MdpRefreshOption
        {
            SourceZtid = "pbxfxp",
            TargetTenantId = 0,
            TargetFactoryId = 1L,
            BizDate = yesterday,
            BizMonth = lastMonth.ToString("yyyy-MM"),
            MonthlyPeriodStart = monthStart,
            MonthlyPeriodEnd = monthEnd.AddDays(1).AddSeconds(-1)
        };
    }
}

public sealed class S7MdpSyncTransformResult
{
    public string BatchId { get; set; } = "";
    public long RunLogId { get; set; }
    public string TriggerType { get; set; } = "AUTO";
    public string SourceZtid { get; set; } = "";
    public long TargetTenantId { get; set; }
    public long TargetFactoryId { get; set; }
    public DateTime BizDate { get; set; }
    public string BizMonth { get; set; } = "";
    public DateTime MonthlyPeriodStart { get; set; }
    public DateTime MonthlyPeriodEnd { get; set; }

    public int StageRows { get; set; }
    public int StandardRows { get; set; }
    public int DwdRows { get; set; }
    public int KpiRows { get; set; }

    public Dictionary<string, int> PerKpiDwdRows { get; } = new();
    public Dictionary<string, int> PerKpiKpiRows { get; } = new();
    public List<string> KpiDenominatorStatus { get; } = new();

    public void MergeSub(string kpiCode, KpiBuildSubResult sub)
    {
        PerKpiDwdRows[kpiCode] = sub.DwdRows;
        PerKpiKpiRows[kpiCode] = sub.KpiRows;
        DwdRows += sub.DwdRows;
        KpiRows += sub.KpiRows;
        KpiDenominatorStatus.Add($"{kpiCode}:{sub.DenominatorStatus}");
    }
}

public sealed class KpiBuildSubResult
{
    public int T8Rows { get; set; }
    public int DwdRows { get; set; }
    public int KpiRows { get; set; }
    public string DenominatorStatus { get; set; } = "OK";
}

// T8 result set 投影类型 ──────────────────────────────────────────────────────

internal sealed class S7CycleRow
{
    public string? noid { get; set; }
    public int? scts { get; set; }
}

internal sealed class S7FulfillmentRow
{
    public string? noid { get; set; }
    public int total_rows { get; set; }
    public int in_window_rows { get; set; }
}

internal sealed class S7ShipmentDetailRow
{
    public string? lynoid { get; set; }
    public string? code { get; set; }
    public string? shtime { get; set; }
    public decimal? slzx { get; set; }
}

internal sealed class S7PeNumRow
{
    public int penum { get; set; }
}

internal sealed class S7QualityReturnSummaryRow
{
    public decimal? return_qty { get; set; }
    public decimal? shipped_qty { get; set; }
}