AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Manufacturing/S6MdpSyncTransformService.cs

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

namespace Admin.NET.Plugin.AiDOP.Manufacturing;

/// <summary>
/// S6 生产执行 — KPI 计算与刷新转换服务。
/// 双模式：读本地中立标准层（由适配器从贴源→标准），不再直连源库。
/// 计算逻辑沿用方老师 v5.4 KPI J 列口径（等价改写为 MySQL 读 std）。
/// 口径归位（S6-L1-KPI-CONTRACT-RESOLUTION-1，方案 B）：L1=订单级，L2=工单级。
/// L1：订单制造周期/满足率/人效写 l1_day；L2：工单制造满足率/人效写 l2_day。
/// 最终聚合经 KpiCalcDispatcher（LEGACY_CODE/CONFIG_SQL），数据准备(dwd)不变。
/// </summary>
public class S6MdpSyncTransformService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly TransformRunLogFinalizer _runLogFinalizer;
    private readonly SysNoticeService _sysNoticeService;
    private readonly ILogger<S6MdpSyncTransformService> _logger;
    private readonly SmartOps.KpiCalcDispatcher _kpiCalcDispatcher;
    private readonly SmartOps.KpiDimensionRunService _dimensionRun;
    private readonly SmartOps.IKpiTargetResolver _kpiTargetResolver;
    private readonly SmartOps.S9CompositeKpiWriter _s9Composite;
    private readonly SmartOps.KpiResultStatusService _kpiStatus;
    private readonly DataPlatform.MdpTenantStdSourceResolver _stdSource;
    private const string L2ValueTable = "ado_s9_kpi_value_l2_day";
    private const string L3ValueTable = "ado_s9_kpi_value_l3_day";
    private const string JobCode = "S6_MDP_SYNC_TRANSFORM";
    private const string JobName = "S6 生产执行 MDP 同步与转换";
    private const string ModuleCode = "S6";
    // FAILURE-NOTIFICATION-1：超级管理员 superAdmin.NET（AccountType=999）
    private const long NoticeReceiverUserId = 1300000000101L;
    private const string NoticeReceiverUserName = "超级管理员";

    public S6MdpSyncTransformService(
        ISqlSugarClient db,
        SysNoticeService sysNoticeService,
        ILogger<S6MdpSyncTransformService> logger,
        SmartOps.KpiCalcDispatcher kpiCalcDispatcher,
        SmartOps.KpiDimensionRunService dimensionRun,
        SmartOps.IKpiTargetResolver kpiTargetResolver,
        TransformRunLogFinalizer runLogFinalizer,
        SmartOps.S9CompositeKpiWriter s9Composite,
        SmartOps.KpiResultStatusService kpiStatus,
        DataPlatform.MdpTenantStdSourceResolver stdSource)
    {
        _db = db;
        _runLogFinalizer = runLogFinalizer;
        _sysNoticeService = sysNoticeService;
        _logger = logger;
        _kpiCalcDispatcher = kpiCalcDispatcher;
        _dimensionRun = dimensionRun;
        _kpiTargetResolver = kpiTargetResolver;
        _s9Composite = s9Composite;
        _kpiStatus = kpiStatus;
        _stdSource = stdSource;
    }

    private async Task<string> SourceOfAsync(long tenantId, string stdObject, CancellationToken ct)
    {
        var row = await _stdSource.GetSourceAsync(tenantId, stdObject, ct);
        return row?.SourceSystem ?? "";
    }

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

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

        var result = new S6MdpSyncTransformResult
        {
            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 currentBizDate = option.BizDate;
            const int backfillDays = 14;
            for (var dayOffset = backfillDays - 1; dayOffset >= 0; dayOffset--)
            {
                option.BizDate = currentBizDate.AddDays(-dayOffset);

                result.MergeSub("S6_L2_001", await BuildS6L2001WorkOrderMfgCycleAsync(
                    batchId, now, option, normalizedTrigger, cancellationToken));
                result.MergeSub("S6_L2_002", await BuildS6L2002WorkOrderMfgFulfillmentAsync(
                    batchId, now, option, normalizedTrigger, cancellationToken));
                result.MergeSub("S6_L2_003", await BuildS6L2003WorkOrderMfgEfficiencyAsync(
                    batchId, now, option, normalizedTrigger, cancellationToken));
                result.MergeSub("S6_L2_004", await BuildS6L2004WorkOrderWipTurnoverAsync(
                    batchId, now, option, normalizedTrigger, cancellationToken));

                foreach (var metricCode in new[]
                         {
                             "S6_L3_001", "S6_L3_002", "S6_L3_003", "S6_L3_004",
                             "S6_L3_005", "S6_L3_006", "S6_L3_007", "S6_L3_008"
                         })
                {
                    var sub = await BuildS6ExecutionDetailKpiAsync(
                        metricCode, batchId, now, option, normalizedTrigger, cancellationToken);
                    result.MergeSub(metricCode, sub);
                }
            }
            option.BizDate = currentBizDate;

            var sub11 = await BuildS6L1001OrderMfgCycleAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S6_L1_001", sub11);

            var sub12 = await BuildS6L1002OrderMfgFulfillmentAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S6_L1_002", sub12);

            var sub13 = await BuildS6L1003OrderMfgEfficiencyAsync(batchId, now, option, normalizedTrigger, cancellationToken);
            result.MergeSub("S6_L1_003", sub13);

            result.KpiRows += await _s9Composite.WriteRecentAsync(
                option.TargetTenantId, option.TargetFactoryId, option.BizDate, cancellationToken);
            await MarkTransformRunSuccessAsync(runLogId, now, result);
            return result;
        }
        catch (Exception ex)
        {
            if (ex is ModuleRebuildCancelledException)
                throw;
            // 宿主关停不是转换失败：交给 finally 收口为 ABORTED，不污染 FAILED 语义。
            if (!_runLogFinalizer.IsHostStopping)
                await MarkTransformRunFailedAsync(runLogId, now, ex.Message, batchId);
            throw;
        }
        finally
        {
            await _runLogFinalizer.FinalizeIfHostStoppingAsync(runLogId, now);
        }
    }

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

    /// <summary>工单制造周期 = 每个完成工单最晚完工时间 - 最早投产时间，再取工单平均。归位 L2：写 S6_L2_001。</summary>
    private async Task<KpiBuildSubResult> BuildS6L2001WorkOrderMfgCycleAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        const string sql = @"
select task_no, datediff(max(completion_time), min(start_time)) as cycle_days
 from (
   select b.order_no, b.task_no, b.item_code, b.release_time as start_time,
          case when b.closed_flag=1 and b.closed_time is not null then b.closed_time else max(d.completion_time) end as completion_time,
          case when b.closed_flag=1 or sum(ifnull(d.qty_change,0))>=b.qty_planned then 1 else 0 end as completed
     from mdp_std_work_order_line b
     left join (
       select ref_task_no, item_num, qty_change, ifnull(line_closed_time, approved_time) as completion_time
         from mdp_std_inv_trans
        where tenant_id=@tenantId
          and biz_doc_type='PROD_RECEIPT' and summary_flag=0 and void_flag=0 and approved_flag=1 AND source_system=@sourceSystem
     ) d on b.task_no=d.ref_task_no and b.item_code=d.item_num
    where b.tenant_id=@tenantId
      and b.doc_type='PROD_TASK' and b.void_flag=0 and b.approved_flag=1 and b.task_no is not null
    group by b.order_no, b.task_no, b.item_code, b.qty_planned, b.release_time, b.closed_flag, b.closed_time
 ) n
 group by task_no
having min(completed)=1 and min(start_time) is not null and max(completion_time) is not null";

        var rows = await _db.Ado.SqlQueryAsync<S6WorkOrderCycleRow>(sql, new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@sourceSystem", await SourceOfAsync(option.TargetTenantId, "INV_TRANS", ct))
        });
        var cycles = rows.Where(r => r.cycle_days.HasValue && r.cycle_days.Value >= 0)
            .Select(r => (decimal)r.cycle_days!.Value).ToList();
        decimal? value = cycles.Count > 0 ? Math.Round(cycles.Average(), 4) : null;
        var denom = cycles.Count > 0 ? "OK" : "NO_COMPLETED_WORK_ORDER";

        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L2_001", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, value, denom, ct);
        var sub = new KpiBuildSubResult
        {
            T8Rows = rows.Count,
            KpiRows = dispatch.ShouldUpsert
                ? await UpsertKpiValueAsync("S6_L2_001", option.BizDate, dispatch.MetricValue, now, option, L2ValueTable)
                : 0,
            DenominatorStatus = dispatch.DenominatorStatus
        };
        await RunDimensionSafelyAsync("S6_L2_001", option.BizDate, batchId, triggerType, option, dispatch.ShouldUpsert, ct);
        return sub;
    }

    /// <summary>工单制造满足率 = 完工窗口内累计入库 / 工单投产数量。归位 L2：写 S6_L2_002。</summary>
    private async Task<KpiBuildSubResult> BuildS6L2002WorkOrderMfgFulfillmentAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        const string sql = @"
select b.order_no as order_no, b.task_no as task_no, b.item_code as item_code, b.qty_planned as qty_planned, sum(d.qty_change) as qty_done
 from mdp_std_work_order_line b
 left join (
   select ref_task_no, item_num,
          date(approved_time) as approved_date, qty_change
    from mdp_std_inv_trans
    where tenant_id=@tenantId
      and biz_doc_type='PROD_RECEIPT' and summary_flag=0 and void_flag=0 and approved_flag=1 AND source_system=@sourceSystem
 ) d on b.task_no=d.ref_task_no and b.item_code=d.item_num
 where b.tenant_id=@tenantId
   and b.doc_type='PROD_TASK' and b.void_flag=0 and b.approved_flag=1 and d.approved_date<=b.plan_finish_date
 group by b.order_no, b.task_no, b.item_code, b.qty_planned";

        var rows = await _db.Ado.SqlQueryAsync<S6MfgFulfillmentRow>(sql, new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@sourceSystem", await SourceOfAsync(option.TargetTenantId, "INV_TRANS", ct))
        });
        sub.T8Rows = rows.Count;

        var dwdAffected = 0;
        var rateList = new List<decimal>();
        foreach (var r in rows)
        {
            ct.ThrowIfCancellationRequested();
            if (string.IsNullOrEmpty(r.order_no)) continue;
            decimal? rate = (r.qty_planned.HasValue && r.qty_planned.Value > 0m && r.qty_done.HasValue)
                ? Math.Round(Math.Clamp(r.qty_done.Value / r.qty_planned.Value, 0m, 1m), 4)
                : null;
            if (rate.HasValue) rateList.Add(rate.Value);

            dwdAffected += await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_work_order_mfg_fulfillment
  (tenant_id, factory_id, biz_date, source_ztid, order_no, task_no, item_code,
   plan_qty, done_qty_in_window, fulfillment_rate, batch_id, create_time)
VALUES
  (@tenantId, @factoryId, @bizDate, @sourceDomain, @orderNo, @taskNo, @itemCode,
   @planQty, @doneQty, @rate, @batchId, @now)
ON DUPLICATE KEY UPDATE
  plan_qty=VALUES(plan_qty), done_qty_in_window=VALUES(done_qty_in_window),
  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("@sourceDomain", option.SourceZtid),
                new SugarParameter("@orderNo", r.order_no),
                new SugarParameter("@taskNo", r.task_no ?? ""),
                new SugarParameter("@itemCode", r.item_code ?? ""),
                new SugarParameter("@planQty", r.qty_planned),
                new SugarParameter("@doneQty", r.qty_done),
                new SugarParameter("@rate", rate),
                new SugarParameter("@batchId", batchId),
                new SugarParameter("@now", now));
        }
        sub.DwdRows = dwdAffected;

        // 数据准备(dwd)已完成。最终聚合交分发器（日 KPI：period 复用 BizDate，SQL 按 biz_date 圈选）。
        decimal? legacyValue = rateList.Count > 0
            ? Math.Round(rateList.Average() * 100m, 4) // 百分号
            : null;
        var legacyDenom = rateList.Count > 0 ? "OK" : "NO_VALID_WORK_ORDER";
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L2_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("S6_L2_002", option.BizDate, dispatch.MetricValue, now, option, L2ValueTable)
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;

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

    /// <summary>工单制造人效 = 完成的 PROD_RECEIPT 行数 / PRODUCTION ACTIVE 人数。归位 L2：写 S6_L2_003。</summary>
    private async Task<KpiBuildSubResult> BuildS6L2003WorkOrderMfgEfficiencyAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var sub = new KpiBuildSubResult();

        const string sqlNumer = @"
select case
  when sum(case when doc_qty is not null or line_closed_flag=1 then 1 else 0 end)=0 then null
  else sum(case when qty_change>0 and (qty_change>=doc_qty or line_closed_flag=1) then 1 else 0 end)
 end as ddnum
 from mdp_std_inv_trans
 where tenant_id=@tenantId
  and biz_doc_type='PROD_RECEIPT' and summary_flag=0 and void_flag=0 and approved_flag=1 AND source_system=@sourceSystem
  and eff_date between @startDate and @endDate";
        const string sqlDenom = @"
select count(*) as penum
 from mdp_std_employee
 where tenant_id=@tenantId and employment_status='ACTIVE' and position_code='PRODUCTION' AND source_system=@empSource";

        var pNumer = new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@startDate", option.MonthlyPeriodStart),
            new SugarParameter("@endDate", option.MonthlyPeriodEnd),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@sourceSystem", await SourceOfAsync(option.TargetTenantId, "INV_TRANS", ct))
        };
        var pDenom = new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@empSource", await SourceOfAsync(option.TargetTenantId, "EMPLOYEE", ct))
        };

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

        int? doneCount = numerRows.FirstOrDefault()?.ddnum;
        int? headcount = denomRows.FirstOrDefault()?.penum;

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

        var dwdAffected = await _db.Ado.ExecuteCommandAsync(@"
INSERT INTO dwd_work_order_mfg_efficiency
  (tenant_id, factory_id, biz_month, source_ztid, period_start, period_end,
   done_count, production_headcount, efficiency, denominator_status, batch_id, create_time)
VALUES
  (@tenantId, @factoryId, @bizMonth, @sourceDomain, @periodStart, @periodEnd,
   @doneCount, @headcount, @efficiency, @denomStatus, @batchId, @now)
ON DUPLICATE KEY UPDATE
  period_start=VALUES(period_start), period_end=VALUES(period_end),
  done_count=VALUES(done_count), production_headcount=VALUES(production_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("@sourceDomain", option.SourceZtid),
            new SugarParameter("@periodStart", option.MonthlyPeriodStart),
            new SugarParameter("@periodEnd", option.MonthlyPeriodEnd),
            new SugarParameter("@doneCount", doneCount),
            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。
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L2_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("S6_L2_003", option.MonthlyPeriodEnd, dispatch.MetricValue, now, option, L2ValueTable)
            : 0;
        sub.DenominatorStatus = dispatch.DenominatorStatus;

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

    /// <summary>工单在制周转 = 在制工单成本 / 当期工单入库成本 × 30。</summary>
    private Task<KpiBuildSubResult> BuildS6L2004WorkOrderWipTurnoverAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
        => DispatchExecutionKpiAsync(
            "S6_L2_004",
            """
            SELECT ROUND(
                       30 * SUM(IFNULL(QtyWIP,0) * IFNULL(NULLIF(PerAmt,0),1))
                          / NULLIF(SUM(IFNULL(QtyComplete,0) * IFNULL(NULLIF(PerAmt,0),1)),0),
                       4) AS MetricValue,
                   SUM(CASE WHEN IFNULL(QtyComplete,0)>0 THEN 1 ELSE 0 END) AS RowCount
            FROM WorkOrdRouting
            WHERE tenant_id=@TenantId
            """,
            "NO_WORK_ORDER_COMPLETION",
            L2ValueTable,
            batchId, now, option, triggerType, ct);

    private Task<KpiBuildSubResult> BuildS6ExecutionDetailKpiAsync(
        string metricCode, string batchId, DateTime now, S6MdpRefreshOption option,
        string triggerType, CancellationToken ct)
    {
        var (sql, emptyDenom) = metricCode switch
        {
            "S6_L3_001" => (
                """
                SELECT ROUND(AVG(TIMESTAMPDIFF(MINUTE,StartDate,EndDate)/1440),4) AS MetricValue,
                       COUNT(*) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId AND StartDate IS NOT NULL AND EndDate>=StartDate
                """,
                "NO_COMPLETED_OPERATION"),
            "S6_L3_002" => (
                """
                SELECT ROUND(
                           100 * SUM(CASE WHEN EndDate IS NOT NULL AND DueDate IS NOT NULL AND EndDate<=DueDate
                                          THEN LEAST(IFNULL(QtyComplete,0),IFNULL(QtyOrded,0)) ELSE 0 END)
                               / NULLIF(SUM(IFNULL(QtyOrded,0)),0),
                           4) AS MetricValue,
                       SUM(CASE WHEN DueDate IS NOT NULL AND IFNULL(QtyOrded,0)>0 THEN 1 ELSE 0 END) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId
                """,
                "NO_PLANNED_OPERATION"),
            "S6_L3_003" => (
                """
                SELECT ROUND(
                           (SELECT COUNT(*) FROM WorkOrdRouting r
                             WHERE r.tenant_id=@TenantId AND r.EndDate IS NOT NULL AND IFNULL(r.QtyComplete,0)>0)
                           / NULLIF((
                               SELECT COUNT(DISTINCT NULLIF(TRIM(e.Employee),''))
                               FROM OpTransEmployee e
                               WHERE EXISTS (
                                   SELECT 1 FROM WorkOrdRouting r2
                                   WHERE r2.tenant_id=@TenantId AND r2.WorkOrd=e.WorkOrd
                               )
                           ),0),
                           4) AS MetricValue,
                       (SELECT COUNT(*) FROM WorkOrdRouting r
                         WHERE r.tenant_id=@TenantId AND r.EndDate IS NOT NULL AND IFNULL(r.QtyComplete,0)>0) AS RowCount
                """,
                "NO_OPERATION_OPERATOR"),
            "S6_L3_004" => (
                """
                SELECT ROUND(
                           30 * SUM(IFNULL(QtyWIP,0) * IFNULL(NULLIF(PerAmt,0),1))
                              / NULLIF(SUM(IFNULL(QtyComplete,0) * IFNULL(NULLIF(PerAmt,0),1)),0),
                           4) AS MetricValue,
                       SUM(CASE WHEN IFNULL(QtyComplete,0)>0 THEN 1 ELSE 0 END) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId
                """,
                "NO_OPERATION_COMPLETION"),
            "S6_L3_005" => (
                """
                SELECT ROUND(AVG(cycle_days),4) AS MetricValue, COUNT(*) AS RowCount
                FROM (
                    SELECT Machine,
                           TIMESTAMPDIFF(MINUTE,MIN(StartDate),MAX(EndDate))/1440 AS cycle_days
                    FROM WorkOrdRouting
                    WHERE tenant_id=@TenantId AND NULLIF(TRIM(Machine),'') IS NOT NULL
                      AND StartDate IS NOT NULL AND EndDate>=StartDate
                    GROUP BY Machine
                ) x
                """,
                "NO_COMPLETED_EQUIPMENT_TASK"),
            "S6_L3_006" => (
                """
                SELECT ROUND(
                           100 * SUM(CASE WHEN EndDate IS NOT NULL AND DueDate IS NOT NULL AND EndDate<=DueDate
                                          THEN LEAST(IFNULL(QtyComplete,0),IFNULL(QtyOrded,0)) ELSE 0 END)
                               / NULLIF(SUM(IFNULL(QtyOrded,0)),0),
                           4) AS MetricValue,
                       COUNT(DISTINCT NULLIF(TRIM(Machine),'')) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId AND NULLIF(TRIM(Machine),'') IS NOT NULL
                """,
                "NO_PLANNED_EQUIPMENT_TASK"),
            "S6_L3_007" => (
                """
                SELECT ROUND(
                           100 * SUM(IFNULL(StdRunTime,0)
                                     * GREATEST(IFNULL(QtyComplete,0)-IFNULL(CumRejected,0)-IFNULL(QtyScrap,0),0))
                               / NULLIF(SUM(IFNULL(ActRunTime,0)),0),
                           4) AS MetricValue,
                       SUM(CASE WHEN IFNULL(ActRunTime,0)>0 AND IFNULL(StdRunTime,0)>0
                                THEN 1 ELSE 0 END) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId AND NULLIF(TRIM(Machine),'') IS NOT NULL
                """,
                "NO_OEE_RUNTIME"),
            "S6_L3_008" => (
                """
                SELECT ROUND(
                           SUM(IFNULL(QtyWIP,0)) / NULLIF(SUM(IFNULL(QtyComplete,0)),0),
                           4) AS MetricValue,
                       COUNT(DISTINCT NULLIF(TRIM(Machine),'')) AS RowCount
                FROM WorkOrdRouting
                WHERE tenant_id=@TenantId AND NULLIF(TRIM(Machine),'') IS NOT NULL
                """,
                "NO_EQUIPMENT_COMPLETION"),
            _ => throw new ArgumentOutOfRangeException(nameof(metricCode), metricCode, "不支持的 S6 明细指标")
        };

        return DispatchExecutionKpiAsync(
            metricCode, sql, emptyDenom, L3ValueTable,
            batchId, now, option, triggerType, ct);
    }

    private async Task<KpiBuildSubResult> DispatchExecutionKpiAsync(
        string metricCode, string sql, string emptyDenom, string valueTable,
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        var row = await _db.Ado.SqlQuerySingleAsync<S6ExecutionKpiRow>(
            sql, new SugarParameter("@TenantId", option.TargetTenantId));
        var value = row?.RowCount > 0 ? row.MetricValue : null;
        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            metricCode, ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, value, value.HasValue ? "OK" : emptyDenom, ct);
        var sub = new KpiBuildSubResult
        {
            T8Rows = row?.RowCount ?? 0,
            KpiRows = dispatch.ShouldUpsert
                ? await UpsertKpiValueAsync(metricCode, option.BizDate, dispatch.MetricValue, now, option, valueTable)
                : 0,
            DenominatorStatus = dispatch.DenominatorStatus
        };
        await RunDimensionSafelyAsync(
            metricCode, option.BizDate, batchId, triggerType, option, dispatch.ShouldUpsert, ct);
        return sub;
    }

    /// <summary>订单制造周期 = 每个完成订单最晚完工时间 - 最早投产时间，再取订单平均。</summary>
    private async Task<KpiBuildSubResult> BuildS6L1001OrderMfgCycleAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        const string sql = @"
select order_no, datediff(max(completion_time), min(start_time)) as cycle_days
 from (
   select b.order_no, b.task_no, b.item_code, b.release_time as start_time,
          case when b.closed_flag=1 and b.closed_time is not null then b.closed_time else max(d.completion_time) end as completion_time,
          case when b.closed_flag=1 or sum(ifnull(d.qty_change,0))>=b.qty_planned then 1 else 0 end as completed
     from mdp_std_work_order_line b
     left join (
       select ref_task_no, item_num, qty_change, ifnull(line_closed_time, approved_time) as completion_time
         from mdp_std_inv_trans
        where tenant_id=@tenantId
          and biz_doc_type='PROD_RECEIPT' and summary_flag=0 and void_flag=0 and approved_flag=1 AND source_system=@sourceSystem
     ) d on b.task_no=d.ref_task_no and b.item_code=d.item_num
    where b.tenant_id=@tenantId
      and b.doc_type='PROD_TASK' and b.void_flag=0 and b.approved_flag=1 and b.order_no is not null
    group by b.order_no, b.task_no, b.item_code, b.qty_planned, b.release_time, b.closed_flag, b.closed_time
 ) n
 group by order_no
having min(completed)=1 and min(start_time) is not null and max(completion_time) is not null";

        var rows = await _db.Ado.SqlQueryAsync<S6OrderCycleRow>(sql, new[]
        {
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@sourceSystem", await SourceOfAsync(option.TargetTenantId, "INV_TRANS", ct))
        });
        var cycles = rows.Where(r => r.cycle_days.HasValue && r.cycle_days.Value >= 0)
            .Select(r => (decimal)r.cycle_days!.Value).ToList();
        decimal? value = cycles.Count > 0 ? Math.Round(cycles.Average(), 4) : null;
        var denom = cycles.Count > 0 ? "OK" : "NO_COMPLETED_ORDER";

        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L1_001", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, value, denom, ct);
        var sub = new KpiBuildSubResult
        {
            T8Rows = rows.Count,
            KpiRows = dispatch.ShouldUpsert
                ? await UpsertKpiValueAsync("S6_L1_001", option.BizDate, dispatch.MetricValue, now, option)
                : 0,
            DenominatorStatus = dispatch.DenominatorStatus
        };
        await RunDimensionSafelyAsync("S6_L1_001", option.BizDate, batchId, triggerType, option, dispatch.ShouldUpsert, ct);
        return sub;
    }

    /// <summary>订单制造满足率 = AVG(clamp(SUM(交期内完成量)/SUM(投产量), 0..1)) × 100。</summary>
    private async Task<KpiBuildSubResult> BuildS6L1002OrderMfgFulfillmentAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        const string sql = @"
select order_no, sum(plan_qty) as plan_qty, sum(done_qty_in_window) as done_qty_in_window
  from dwd_work_order_mfg_fulfillment
 where tenant_id=@tenantId and factory_id=@factoryId and biz_date=@bizDate and source_ztid=@sourceDomain
 group by order_no";
        var rows = await _db.Ado.SqlQueryAsync<S6OrderFulfillmentRow>(sql,
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@factoryId", option.TargetFactoryId),
            new SugarParameter("@bizDate", option.BizDate),
            new SugarParameter("@sourceDomain", option.SourceZtid));

        decimal? value = CalculateOrderFulfillmentPercent(
            rows.Select(r => (r.plan_qty, r.done_qty_in_window)));
        var denom = value.HasValue ? "OK" : "NO_VALID_ORDER";

        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L1_002", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.BizDate, option.BizDate, option.BizDate, option.SourceZtid,
            batchId, triggerType, value, denom, ct);
        var sub = new KpiBuildSubResult
        {
            T8Rows = rows.Count,
            KpiRows = dispatch.ShouldUpsert
                ? await UpsertKpiValueAsync("S6_L1_002", option.BizDate, dispatch.MetricValue, now, option)
                : 0,
            DenominatorStatus = dispatch.DenominatorStatus
        };
        await RunDimensionSafelyAsync("S6_L1_002", option.BizDate, batchId, triggerType, option, dispatch.ShouldUpsert, ct);
        return sub;
    }

    internal static decimal? CalculateOrderFulfillmentPercent(
        IEnumerable<(decimal? PlanQty, decimal? DoneQty)> orders)
    {
        var rates = orders
            .Where(r => r.PlanQty.HasValue && r.PlanQty.Value > 0m && r.DoneQty.HasValue)
            .Select(r => Math.Clamp(r.DoneQty!.Value / r.PlanQty!.Value, 0m, 1m))
            .ToList();
        return rates.Count > 0 ? Math.Round(rates.Average() * 100m, 4) : null;
    }

    /// <summary>订单制造人效 = 统计期内完成制造的订单数 / PRODUCTION ACTIVE 人数。</summary>
    private async Task<KpiBuildSubResult> BuildS6L1003OrderMfgEfficiencyAsync(
        string batchId, DateTime now, S6MdpRefreshOption option, string triggerType, CancellationToken ct)
    {
        const string sqlNumer = @"
select count(*) as ddnum
 from (
   select order_no
     from (
       select b.order_no, b.task_no, b.item_code,
              case when b.closed_flag=1 and b.closed_time is not null then b.closed_time else max(d.completion_time) end as completion_time,
              case when b.closed_flag=1 or sum(ifnull(d.qty_change,0))>=b.qty_planned then 1 else 0 end as completed
         from mdp_std_work_order_line b
         left join (
           select ref_task_no, item_num, qty_change, ifnull(line_closed_time, approved_time) as completion_time
             from mdp_std_inv_trans
            where tenant_id=@tenantId
              and biz_doc_type='PROD_RECEIPT' and summary_flag=0 and void_flag=0 and approved_flag=1 AND source_system=@sourceSystem
         ) d on b.task_no=d.ref_task_no and b.item_code=d.item_num
        where b.tenant_id=@tenantId
          and b.doc_type='PROD_TASK' and b.void_flag=0 and b.approved_flag=1 and b.order_no is not null
        group by b.order_no, b.task_no, b.item_code, b.qty_planned, b.closed_flag, b.closed_time
     ) order_lines
    group by order_no
   having min(completed)=1 and max(completion_time) between @startDate and @endDate
 ) orders_done";
        const string sqlDenom = @"
select count(*) as penum
  from mdp_std_employee
 where tenant_id=@tenantId and employment_status='ACTIVE' and position_code='PRODUCTION' AND source_system=@empSource";
        var numerRows = await _db.Ado.SqlQueryAsync<S6CountRow>(sqlNumer,
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@startDate", option.MonthlyPeriodStart),
            new SugarParameter("@endDate", option.MonthlyPeriodEnd),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@sourceSystem", await SourceOfAsync(option.TargetTenantId, "INV_TRANS", ct)));
        var denomRows = await _db.Ado.SqlQueryAsync<S6PeNumRow>(sqlDenom,
            new SugarParameter("@tenantId", option.TargetTenantId),
            new SugarParameter("@sourceDomain", option.SourceZtid),
            new SugarParameter("@empSource", await SourceOfAsync(option.TargetTenantId, "EMPLOYEE", ct)));

        int? doneCount = numerRows.FirstOrDefault()?.ddnum;
        int? headcount = denomRows.FirstOrDefault()?.penum;
        decimal? value = null;
        string denom;
        if (!headcount.HasValue || headcount.Value <= 0)
            denom = "NO_HEADCOUNT";
        else if (!doneCount.HasValue)
            denom = "NO_NUMERATOR";
        else
        {
            value = Math.Round((decimal)doneCount.Value / headcount.Value, 4);
            denom = "OK";
        }

        var dispatch = await _kpiCalcDispatcher.DispatchAsync(
            "S6_L1_003", ModuleCode, option.TargetTenantId, option.TargetFactoryId,
            option.MonthlyPeriodEnd, option.MonthlyPeriodStart, option.MonthlyPeriodEnd, option.SourceZtid,
            batchId, triggerType, value, denom, ct);
        var sub = new KpiBuildSubResult
        {
            T8Rows = numerRows.Count + denomRows.Count,
            KpiRows = dispatch.ShouldUpsert
                ? await UpsertKpiValueAsync("S6_L1_003", option.MonthlyPeriodEnd, dispatch.MetricValue, now, option)
                : 0,
            DenominatorStatus = dispatch.DenominatorStatus
        };
        await RunDimensionSafelyAsync("S6_L1_003", option.MonthlyPeriodEnd, batchId, triggerType, option, dispatch.ShouldUpsert, ct);
        return sub;
    }

    private async Task RunDimensionSafelyAsync(
        string metricCode, DateTime bizDate, string batchId, string triggerType,
        S6MdpRefreshOption option, bool shouldRun, CancellationToken ct)
    {
        if (!shouldRun) return;
        try
        {
            await _dimensionRun.RunDimensionAsync(
                metricCode, ModuleCode, option.TargetTenantId, bizDate, batchId, triggerType, ct);
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "{MetricCode} 维度跑批异常（不影响汇总链路）", metricCode);
        }
    }

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

    private async Task<int> UpsertKpiValueAsync(string metricCode, DateTime bizDate, decimal? metricValue, DateTime now, S6MdpRefreshOption option, string valueTable = "ado_s9_kpi_value_l1_day")
    {
        // 沿用 S3 UpsertS3KpiValueAsync 范式：先查现存行 → UPDATE；不存在 → SELECT MAX(id)+1 显式生成 id 后 INSERT。
        // 值表 id 为手工分配主键（无 AUTO_INCREMENT），必须显式 set；metric_value 允许 NULL（分母缺失不得伪装真实 0）。
        // valueTable：L1 KPI 写 ado_s9_kpi_value_l1_day；L2（本服务工单指标 S6_L2_002/003）写 ado_s9_kpi_value_l2_day。
        //   表名为受控常量（非用户输入），可安全内插；各表 id 序列独立，MAX(id) 取目标表。
        // FIX-2：截断时分秒（月度 KPI 入参可能为 YYYY-MM-DD 23:59:59），保证 SELECT WHERE biz_date=@BizDate 与 DB date 列匹配。
        bizDate = bizDate.Date;
        var decision = await _kpiStatus.ResolveAsync(option.TargetTenantId, metricCode, metricValue);
        metricValue = decision.Value;
        var snap = await _kpiTargetResolver.ResolveAsync(option.TargetTenantId, option.TargetFactoryId, metricCode, ModuleCode, bizDate);
        var existingId = await _db.Ado.GetLongAsync(
            $"SELECT IFNULL((SELECT id FROM {valueTable} 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 {valueTable} SET {SmartOps.KpiResultWrite.SetClause}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),
                SmartOps.KpiResultWrite.Status(decision.Status),
                SmartOps.KpiResultWrite.Reason(decision.Reason),
                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 = Yitter.IdGenerator.YitIdHelper.NextId();
        return await _db.Ado.ExecuteCommandAsync($@"
INSERT INTO {valueTable}
  (id, tenant_id, org_id, company_id, factory_id, status, biz_date,
   create_time, update_time, is_deleted, is_active,
   module_code, metric_code, result_status, result_reason, 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, @ResultStatus, @ResultReason, @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),
            SmartOps.KpiResultWrite.Status(decision.Status),
            SmartOps.KpiResultWrite.Reason(decision.Reason),
            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, S6MdpRefreshOption 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, S6MdpSyncTransformResult 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($"[S6MdpSyncTransform] MarkTransformRunFailed write failed (runLogId={runLogId}): {ex.Message}");
        }

        // FAILURE-NOTIFICATION-1：写库 FAILED 成功后发通知给超级管理员；通知失败不影响主流程
        if (!runLogUpdated) return;
        try
        {
            await _sysNoticeService.AddNotice(new AddNoticeInput
            {
                Title = "S6 生产执行 T8 KPI 跑批失败",
                Content = $"模块：S6 生产执行\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, "[S6MdpSyncTransform] SysNotice 发送失败 (runLogId={RunLogId}, batchId={BatchId})", runLogId, batchId);
        }
    }

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

    private static void NormalizeOption(S6MdpRefreshOption option)
    {
        var d = S6MdpRefreshOption.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 S6MdpRefreshOption
{
    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 S6MdpRefreshOption 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 S6MdpRefreshOption
        {
            SourceZtid = "pbxfxp",
            TargetTenantId = 0,
            TargetFactoryId = 1L,
            BizDate = yesterday,
            BizMonth = lastMonth.ToString("yyyy-MM"),
            MonthlyPeriodStart = monthStart,
            MonthlyPeriodEnd = monthEnd.AddDays(1).AddSeconds(-1)
        };
    }
}

public sealed class S6MdpSyncTransformResult
{
    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 S6MfgFulfillmentRow
{
    public string? order_no { get; set; }
    public string? task_no { get; set; }
    public string? item_code { get; set; }
    public decimal? qty_planned { get; set; }
    public decimal? qty_done { get; set; }
}

internal sealed class S6CountRow
{
    public int? ddnum { get; set; }
}

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

internal sealed class S6OrderCycleRow
{
    public string? order_no { get; set; }
    public int? cycle_days { get; set; }
}

internal sealed class S6WorkOrderCycleRow
{
    public string? task_no { get; set; }
    public int? cycle_days { get; set; }
}

internal sealed class S6OrderFulfillmentRow
{
    public string? order_no { get; set; }
    public decimal? plan_qty { get; set; }
    public decimal? done_qty_in_window { get; set; }
}

internal sealed class S6ExecutionKpiRow
{
    public decimal? MetricValue { get; set; }
    public int RowCount { get; set; }
}