using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.FinishedWarehouse;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Manufacturing;
using Admin.NET.Plugin.AiDOP.MaterialWarehouse;
using Admin.NET.Plugin.AiDOP.Order;
using Admin.NET.Plugin.AiDOP.ProcurementExecution;
using Admin.NET.Plugin.AiDOP.Production;
using Admin.NET.Plugin.AiDOP.Supply;
using Admin.NET.Plugin.AiDOP.SmartOps;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Controllers;

[ApiController]
[Route("api/[controller]")]
[AllowAnonymous]
[NonUnify]
public partial class AidopKanbanController : ControllerBase
{
    private readonly ISqlSugarClient _db;
    private readonly S1MdpSyncTransformService _s1MdpSyncTransformService;
    private readonly S2MdpSyncTransformService _s2MdpSyncTransformService;
    private readonly S3MdpSyncTransformService _s3MdpSyncTransformService;
    private readonly S4MdpSyncTransformService _s4MdpSyncTransformService;
    private readonly S5MdpSyncTransformService _s5MdpSyncTransformService;
    private readonly S6MdpSyncTransformService _s6MdpSyncTransformService;
    private readonly S7MdpSyncTransformService _s7MdpSyncTransformService;
    private readonly SmartOpsKpiAtomicQueryService _atomicQuery;
    private readonly OutsourceIssueMdpSyncService _outsourceIssueMdpSyncService;
    private readonly PurchaseReceiptMdpSyncService _purchaseReceiptMdpSyncService;
    private readonly ProductionReturnMdpSyncService _productionReturnMdpSyncService;
    private readonly ProductionReceiptMdpSyncService _productionReceiptMdpSyncService;
    private readonly IpqcInspectionMdpSyncService _ipqcInspectionMdpSyncService;
    private readonly ReportWorkMdpSyncService _reportWorkMdpSyncService;

    public AidopKanbanController(
        ISqlSugarClient db,
        S1MdpSyncTransformService s1MdpSyncTransformService,
        S2MdpSyncTransformService s2MdpSyncTransformService,
        S3MdpSyncTransformService s3MdpSyncTransformService,
        S4MdpSyncTransformService s4MdpSyncTransformService,
        S5MdpSyncTransformService s5MdpSyncTransformService,
        S6MdpSyncTransformService s6MdpSyncTransformService,
        S7MdpSyncTransformService s7MdpSyncTransformService,
        SmartOpsKpiAtomicQueryService atomicQuery,
        OutsourceIssueMdpSyncService outsourceIssueMdpSyncService,
        PurchaseReceiptMdpSyncService purchaseReceiptMdpSyncService,
        ProductionReturnMdpSyncService productionReturnMdpSyncService,
        ProductionReceiptMdpSyncService productionReceiptMdpSyncService,
        IpqcInspectionMdpSyncService ipqcInspectionMdpSyncService,
        ReportWorkMdpSyncService reportWorkMdpSyncService)
    {
        _db = db;
        _s1MdpSyncTransformService = s1MdpSyncTransformService;
        _s2MdpSyncTransformService = s2MdpSyncTransformService;
        _s3MdpSyncTransformService = s3MdpSyncTransformService;
        _s4MdpSyncTransformService = s4MdpSyncTransformService;
        _s5MdpSyncTransformService = s5MdpSyncTransformService;
        _s6MdpSyncTransformService = s6MdpSyncTransformService;
        _s7MdpSyncTransformService = s7MdpSyncTransformService;
        _atomicQuery = atomicQuery;
        _outsourceIssueMdpSyncService = outsourceIssueMdpSyncService;
        _purchaseReceiptMdpSyncService = purchaseReceiptMdpSyncService;
        _productionReturnMdpSyncService = productionReturnMdpSyncService;
        _productionReceiptMdpSyncService = productionReceiptMdpSyncService;
        _ipqcInspectionMdpSyncService = ipqcInspectionMdpSyncService;
        _reportWorkMdpSyncService = reportWorkMdpSyncService;
    }

    [HttpGet("home-l1")]
    public async Task<IActionResult> GetHomeL1([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const string sql = """
SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode,
       v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag,
       k.Direction, k.YellowThreshold, k.RedThreshold
FROM ado_s9_kpi_value_l1_day v
LEFT JOIN ado_smart_ops_kpi_master k
  ON k.MetricCode = v.metric_code COLLATE utf8mb4_general_ci AND k.TenantId = v.tenant_id
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.is_deleted=0
  AND v.biz_date = (SELECT MAX(biz_date) FROM ado_s9_kpi_value_l1_day
                    WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0)
ORDER BY v.module_code
""";
        var raw = await _db.Ado.SqlQueryAsync<HomeL1RawDto>(sql, new { tenantId, factoryId });
        var rows = raw.Select(r => new HomeL1Dto
        {
            ModuleCode = r.ModuleCode,
            MetricCode = r.MetricCode,
            MetricValue = r.MetricValue,
            TargetValue = r.TargetValue,
            TrendFlag = r.TrendFlag,
            StatusColor = AidopS4KpiMerge.AchievementLevel(
                r.MetricValue, r.TargetValue,
                r.Direction ?? "higher_is_better",
                r.YellowThreshold, r.RedThreshold)
        }).ToList();
        return Ok(rows);
    }

    [HttpGet("s8-alerts")]
    public async Task<IActionResult> GetS8Alerts([FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const string sql = """
SELECT DATE_FORMAT(alert_time, '%H:%i') AS Time,
       IFNULL(SUBSTRING_INDEX(metric_code, '_', 1), 'S8') AS Module,
       IFNULL(message, '异常告警') AS Message,
       IFNULL(level_code, 'medium') AS LevelCode
FROM ado_s8_alert_record
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY alert_time DESC
LIMIT 6
""";
        var rows = await _db.Ado.SqlQueryAsync<S8AlertDto>(sql, new { tenantId, factoryId });
        return Ok(rows.Select(x => new
        {
            time = x.Time,
            module = x.Module,
            message = x.Message,
            level = x.LevelCode,
            levelLabel = x.LevelCode switch
            {
                "critical" => "严重",
                "high" => "高",
                "medium" => "中",
                _ => "一般"
            }
        }));
    }

    [HttpGet("module-detail")]
    public async Task<IActionResult> GetModuleDetail([FromQuery] string moduleCode = "S1", [FromQuery] long factoryId = 1)
    {
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        moduleCode = string.IsNullOrWhiteSpace(moduleCode) ? "S1" : moduleCode.ToUpperInvariant();
        const string l2Sql = """
SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode,
       COALESCE(NULLIF(k.MetricName,''), v.metric_code) AS MetricName,
       v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag, v.biz_date AS StatDate
FROM ado_s9_kpi_value_l2_day v
LEFT JOIN ado_smart_ops_kpi_master k ON k.TenantId=v.tenant_id AND k.MetricCode=v.metric_code AND k.ModuleCode=v.module_code
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.is_deleted=0 AND v.module_code=@moduleCode
  AND v.biz_date=(SELECT MAX(biz_date) FROM ado_s9_kpi_value_l2_day
                  WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode AND is_deleted=0)
ORDER BY v.metric_code
LIMIT 30
""";
        const string l3Sql = """
SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode,
       COALESCE(NULLIF(k.MetricName,''), v.metric_code) AS MetricName,
       v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag, v.biz_date AS StatDate,
       p.MetricCode AS ParentMetricCode
FROM ado_s9_kpi_value_l3_day v
LEFT JOIN ado_smart_ops_kpi_master k ON k.TenantId=v.tenant_id AND k.MetricCode=v.metric_code AND k.ModuleCode=v.module_code
LEFT JOIN ado_smart_ops_kpi_master p ON p.Id=k.ParentId
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.is_deleted=0 AND v.module_code=@moduleCode
  AND v.biz_date=(SELECT MAX(biz_date) FROM ado_s9_kpi_value_l3_day
                  WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode AND is_deleted=0)
ORDER BY v.metric_code
LIMIT 60
""";
        const string alertSql = """
SELECT DATE_FORMAT(alert_time, '%H:%i:%s') AS Time,
       IFNULL(level_code, 'medium') AS LevelCode,
       IFNULL(message, '异常告警') AS Message
FROM ado_s8_alert_record
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
  AND (UPPER(IFNULL(module_code,''))=@moduleCode OR UPPER(IFNULL(SUBSTRING_INDEX(metric_code, '_', 1), ''))=@moduleCode)
ORDER BY alert_time DESC
LIMIT 20
""";
        const string l2FallbackSql = """
SELECT '' AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l2_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY id DESC
LIMIT 30
""";
        const string l3FallbackSql = """
SELECT '' AS ModuleCode, metric_code AS MetricCode, metric_code AS MetricName, metric_value AS MetricValue,
       target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag, NULL AS StatDate
FROM ado_s9_kpi_value_l3_day
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
ORDER BY id DESC
LIMIT 60
""";
        var l2 = new List<KpiDetailDto>();
        var l3 = new List<KpiDetailDto>();
        var alerts = new List<S8AlertDto>();
        try { l2 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l2Sql, new { moduleCode, tenantId, factoryId }); }
        catch { l2 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l2FallbackSql, new { tenantId, factoryId }); }
        try { l3 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l3Sql, new { moduleCode, tenantId, factoryId }); }
        catch { l3 = await _db.Ado.SqlQueryAsync<KpiDetailDto>(l3FallbackSql, new { tenantId, factoryId }); }
        try { alerts = await _db.Ado.SqlQueryAsync<S8AlertDto>(alertSql, new { moduleCode, tenantId, factoryId }); }
        catch { alerts = new List<S8AlertDto>(); }
        var schedules = new List<S2ScheduleDto>();
        var s2Alerts = new List<S8AlertDto>();
        var decomposition = new List<S2DecompositionDto>();
        var trend = new List<S2TrendDto>();
        var distribution = new List<S2DistributionDto>();
        var branchKpis = new List<S3BranchKpiGroupDto>();
        S2SyncStatusDto? syncStatus = null;
        if (moduleCode == "S2")
        {
            syncStatus = await GetS2SyncStatusAsync();
            s2Alerts = await GetS2DerivedAlertsAsync(tenantId, factoryId);
            if (s2Alerts.Count > 0)
                alerts = s2Alerts;
            decomposition = await GetS2DecompositionAsync(tenantId, factoryId, l2);
            trend = await GetS2TrendAsync(tenantId, factoryId);
            distribution = await GetS2DistributionAsync(tenantId, factoryId);
            try
            {
                schedules = await _db.Ado.SqlQueryAsync<S2ScheduleDto>(
                    """
                    SELECT work_order AS OrderNo,
                           COALESCE(NULLIF(item_name, ''), item_code, work_order) AS Product,
                           COALESCE(NULLIF(sales_order_no, ''), work_order) AS Customer,
                           prod_line AS ProductionLine,
                           qty_ordered AS Quantity,
                           due_date AS DeliveryDate,
                           first_start_time AS StartTime,
                           schedule_cycle_days AS CycleDays,
                           CASE WHEN schedule_satisfaction_flag = 1 THEN 100
                                WHEN due_date IS NULL OR last_plan_date IS NULL THEN 70
                                ELSE 65 END AS Satisfaction,
                           CASE WHEN schedule_satisfaction_flag = 1 THEN '已锁定'
                                WHEN last_plan_date IS NULL THEN '待排程'
                                ELSE '资源异常' END AS Status
                    FROM dwd_order_schedule_trans
                    WHERE tenant_id=@tenantId AND factory_id=@factoryId
                      AND calc_batch_id=(SELECT calc_batch_id FROM dwd_order_schedule_trans
                                         WHERE tenant_id=@tenantId AND factory_id=@factoryId
                                         ORDER BY calc_time DESC, id DESC LIMIT 1)
                    ORDER BY urgent_flag DESC, due_date IS NULL, due_date, id DESC
                    LIMIT 30
                    """,
                    new { tenantId, factoryId });
            }
            catch
            {
                schedules = new List<S2ScheduleDto>();
            }
        }
        if (moduleCode == "S3")
        {
            syncStatus = await GetMdpSyncStatusAsync("S3_MDP_SYNC_TRANSFORM");
            var s3Alerts = await GetS3DerivedAlertsAsync(tenantId, factoryId);
            if (s3Alerts.Count > 0)
                alerts = s3Alerts;
            decomposition = await GetS3DecompositionAsync(tenantId, factoryId, l2);
            branchKpis = await GetS3BranchKpisAsync(tenantId, factoryId, l2);
            trend = await GetS3TrendAsync(tenantId, factoryId);
            distribution = await GetS3DistributionAsync(tenantId, factoryId);
        }
        if (moduleCode == "S4")
        {
            syncStatus = await GetMdpSyncStatusAsync("S4_MDP_SYNC_TRANSFORM");
            var s4Alerts = await GetS4DerivedAlertsAsync(tenantId, factoryId);
            if (s4Alerts.Count > 0)
                alerts = s4Alerts;
            trend = await GetS4TrendAsync(tenantId, factoryId);
            distribution = await GetS4DistributionAsync(tenantId, factoryId);
        }
        var drillGroups = BuildKpiDrillGroups(moduleCode, l2, l3);
        if (moduleCode == "S2")
            decomposition = EnrichS2DecompositionWithL3(decomposition, l3);
        return Ok(new
        {
            moduleCode,
            l2,
            l3,
            drillGroups,
            syncStatus,
            decomposition,
            branchKpis,
            trend,
            distribution,
            schedules,
            alerts = alerts.Select(x => new
            {
                time = x.Time,
                message = x.Message,
                level = x.LevelCode
            })
        });
    }

    private static List<KpiDrillGroupDto> BuildKpiDrillGroups(string moduleCode, List<KpiDetailDto> l2, List<KpiDetailDto> l3)
    {
        if (l3.Count == 0) return new List<KpiDrillGroupDto>();

        var parentTitles = l2.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First().MetricName ?? g.Key, StringComparer.OrdinalIgnoreCase);

        return l3.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => string.IsNullOrWhiteSpace(u.ParentMetricCode) ? "_root" : u.ParentMetricCode!, StringComparer.OrdinalIgnoreCase)
            .Select(g =>
            {
                var parentCode = g.Key == "_root" ? null : g.Key;
                var title = parentCode != null && parentTitles.TryGetValue(parentCode, out var parentName)
                    ? parentName
                    : parentCode ?? $"{moduleCode} L3";
                return new KpiDrillGroupDto
                {
                    ParentMetricCode = parentCode,
                    Title = title,
                    Items = g.Select(item => new KpiDrillItemDto
                    {
                        MetricCode = item.MetricCode,
                        MetricName = item.MetricName ?? item.MetricCode,
                        MetricValue = item.MetricValue,
                        TargetValue = item.TargetValue,
                        StatusColor = item.StatusColor,
                        TrendFlag = item.TrendFlag,
                        StatDate = item.StatDate?.ToString("yyyy-MM-dd")
                    }).ToList()
                };
            })
            .OrderBy(x => x.ParentMetricCode)
            .ToList();
    }

    private static List<S2DecompositionDto> EnrichS2DecompositionWithL3(List<S2DecompositionDto> decomposition, List<KpiDetailDto> l3)
    {
        if (l3.Count == 0) return decomposition;

        var latestL3 = l3.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

        foreach (var section in decomposition)
        {
            if (section.Title == "工序排程")
            {
                section.Metrics = new List<string>
                {
                    $"1. 周期：{FormatMetric(latestL3, "S2_L3_001", "天")}",
                    $"2. 满足率：{FormatMetric(latestL3, "S2_L3_002", "%")}",
                    $"3. 人效：{FormatMetric(latestL3, "S2_L3_003", "道/人")}"
                };
            }
            else if (section.Title == "资源排程")
            {
                section.Metrics = new List<string>
                {
                    $"1. 设备周期：{FormatMetric(latestL3, "S2_L3_004", "天")}",
                    $"2. 设备满足率：{FormatMetric(latestL3, "S2_L3_005", "%")}",
                    $"3. 设备人效：{FormatMetric(latestL3, "S2_L3_006", "台/人")}"
                };
            }
        }

        return decomposition;
    }

    [HttpPost("s1-mdp/refresh")]
    public async Task<IActionResult> RefreshS1Mdp(CancellationToken cancellationToken)
    {
        var result = await _s1MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows,
            result.AtomicRows
        });
    }

    [HttpPost("s2-mdp/refresh")]
    public async Task<IActionResult> RefreshS2Mdp(CancellationToken cancellationToken)
    {
        var result = await _s2MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows
        });
    }

    [HttpPost("s3-mdp/refresh")]
    public async Task<IActionResult> RefreshS3Mdp(CancellationToken cancellationToken)
    {
        var result = await _s3MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows
        });
    }

    [HttpPost("s4-mdp/refresh")]
    public async Task<IActionResult> RefreshS4Mdp(CancellationToken cancellationToken)
    {
        var result = await _s4MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows
        });
    }

    [HttpPost("s1-mdp/inbound")]
    public async Task<IActionResult> InboundS1Mdp(
        [FromQuery] bool fullRefresh = false,
        [FromQuery] long tenantId = 0,
        CancellationToken cancellationToken = default)
    {
        var result = await _s1MdpSyncTransformService.RunInboundAsync(
            null, tenantId, fullRefresh, cancellationToken);
        return Ok(new { ok = true, result.BatchId, result.StageRows, result.StandardRows, result.DwdRows, result.KpiRows });
    }

    [HttpPost("s2-mdp/inbound")]
    public async Task<IActionResult> InboundS2Mdp(
        [FromQuery] bool fullRefresh = false,
        [FromQuery] long tenantId = 0,
        CancellationToken cancellationToken = default)
    {
        var result = await _s2MdpSyncTransformService.RunInboundAsync(
            null, tenantId, fullRefresh, cancellationToken);
        return Ok(new { ok = true, result.BatchId, result.StageRows, result.StandardRows, result.DwdRows, result.KpiRows });
    }

    [HttpPost("s3-mdp/inbound")]
    public async Task<IActionResult> InboundS3Mdp(
        [FromQuery] bool fullRefresh = false,
        [FromQuery] long tenantId = 0,
        CancellationToken cancellationToken = default)
    {
        var result = await _s3MdpSyncTransformService.RunInboundAsync(
            null, tenantId, fullRefresh, cancellationToken);
        return Ok(new { ok = true, result.BatchId, result.StageRows, result.StandardRows, result.DwdRows, result.KpiRows });
    }

    [HttpPost("s4-mdp/inbound")]
    public async Task<IActionResult> InboundS4Mdp(
        [FromQuery] bool fullRefresh = false,
        [FromQuery] long tenantId = 0,
        CancellationToken cancellationToken = default)
    {
        var result = await _s4MdpSyncTransformService.RunInboundAsync(
            null, tenantId, fullRefresh, cancellationToken);
        return Ok(new { ok = true, result.BatchId, result.StageRows, result.StandardRows, result.DwdRows, result.KpiRows });
    }

    /// <summary>
    /// S5 物料仓储 KPI 手动刷新（T8 SQL Server 跨库读 → DOP DWD/KPI）。
    /// 路径 A：方老师 v5.4 KPI #16/#17/#18/#19 原 SQL 直发 T8。
    /// 边界：只读 SELECT；仅 refresh 内触发；禁止看板实时查 T8；禁止高频调度。
    /// </summary>
    [HttpPost("s5-mdp/refresh")]
    public async Task<IActionResult> RefreshS5Mdp(
        [FromQuery] long? targetTenantId,
        [FromQuery] long? targetFactoryId,
        CancellationToken cancellationToken)
    {
        var option = S5MdpRefreshOption.Default();
        if (targetTenantId is > 0) option.TargetTenantId = targetTenantId.Value;
        if (targetFactoryId is > 0) option.TargetFactoryId = targetFactoryId.Value;
        var result = await _s5MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL", option);
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows,
            result.PerKpiDwdRows,
            result.PerKpiKpiRows,
            result.KpiDenominatorStatus,
            result.SourceZtid,
            result.TargetTenantId,
            result.TargetFactoryId
        });
    }

    /// <summary>
    /// S5 委外发料单 数据中台只读链路手动刷新（DOP 内部：NbrMaster/NbrDetail Type='CA' → mdp_stg → mdp_std）。
    /// 独立于 s5-mdp/refresh 的 KPI 管线；只读源、只写 mdp 表；不碰 T8；CA 为 0 行时成功、处理数 0。
    /// </summary>
    [HttpPost("s5-outsource-issue-mdp/refresh")]
    public async Task<IActionResult> RefreshS5OutsourceIssueMdp(CancellationToken cancellationToken)
    {
        var result = await _outsourceIssueMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.HeadStageRows,
            result.DetailStageRows,
            result.HeadStandardRows,
            result.DetailStandardRows
        });
    }

    /// <summary>
    /// S6 过程检验单（IPQC）数据中台只读链路手动刷新（DOP 内部：归口源 qms_gcjyd → mdp_stg_ipqc_inspection → mdp_std_ipqc_inspection）。
    /// 独立于 s6-mdp/refresh 的 KPI 管线；只读源、只写 mdp 表；不碰 T8；源为 0 行时成功、处理数 0。
    /// </summary>
    [HttpPost("s6-ipqc-inspection-mdp/refresh")]
    public async Task<IActionResult> RefreshS6IpqcInspectionMdp(CancellationToken cancellationToken)
    {
        var result = await _ipqcInspectionMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.HeadStageRows,
            result.HeadStandardRows
        });
    }

    /// <summary>
    /// S6 IPQC 双模式入站：MdpDbPullExecutor → mdp_stg_ipqc_pull，再跑标准层转换。
    /// </summary>
    [HttpPost("s6-ipqc-inspection-mdp/inbound")]
    public async Task<IActionResult> InboundS6IpqcInspectionMdp(
        [FromQuery] long? tenantId,
        [FromQuery] bool fullRefresh = false,
        CancellationToken cancellationToken = default)
    {
        var result = await _ipqcInspectionMdpSyncService.RunInboundAsync(
            tenantId ?? 0,
            fullRefresh,
            cancellationToken);
        return Ok(new
        {
            ok = true,
            result.PullBatchId,
            result.RowsPulled,
            result.RowsWrittenStg,
            result.NewCursor,
            result.TransformBatchId,
            result.HeadStageRows,
            result.HeadStandardRows,
            result.DetailStageRows,
            result.DetailStandardRows
        });
    }

    /// <summary>S6 报工双模式入站：S6_REPORT / S6_REPORT_API → mdp_stg_s6_report → mdp_std_s6_report。</summary>
    [HttpPost("s6-report-mdp/inbound")]
    public async Task<IActionResult> InboundS6ReportMdp(
        [FromQuery] long? tenantId,
        [FromQuery] bool fullRefresh = false,
        [FromQuery] string? entityCode = null,
        CancellationToken cancellationToken = default)
    {
        var result = await _reportWorkMdpSyncService.RunInboundAsync(
            tenantId ?? 0,
            fullRefresh,
            entityCode,
            cancellationToken);
        return Ok(new
        {
            ok = true,
            result.PullBatchId,
            result.RowsPulled,
            result.RowsWrittenStg,
            result.TransformBatchId,
            result.StandardRows
        });
    }

    /// <summary>
    /// S5 采购收货单 数据中台只读链路手动刷新（DOP 内部方案 1：PurOrdRctDetail/Master RctType='rc' → mdp_std_purchase_receipt）。
    /// 独立于 s5-mdp/refresh 的 KPI 管线；只读源、只写 mdp_std_purchase_receipt；不读/不改 S3 mdp_stg_receipt、S4 ado_s4_receipt；不碰 T8；rc 为 0 行时成功、处理数 0。
    /// </summary>
    [HttpPost("s5-purchase-receipt-mdp/refresh")]
    public async Task<IActionResult> RefreshS5PurchaseReceiptMdp(CancellationToken cancellationToken)
    {
        var result = await _purchaseReceiptMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            batchId = result.BatchId,
            stdRows = result.StdRows
        });
    }

    /// <summary>
    /// S5 采购收货双模式入站：MdpDbPullExecutor → mdp_stg_purchase_receipt，再跑标准层转换。
    /// 独立作业模式仍可用 s5-purchase-receipt-mdp/refresh（直读源表建 std）。
    /// </summary>
    [HttpPost("s5-purchase-receipt-mdp/inbound")]
    public async Task<IActionResult> InboundS5PurchaseReceiptMdp(
        [FromQuery] long? tenantId,
        [FromQuery] bool fullRefresh = false,
        CancellationToken cancellationToken = default)
    {
        var result = await _purchaseReceiptMdpSyncService.RunInboundAsync(
            tenantId ?? 0,
            fullRefresh,
            cancellationToken);
        return Ok(new
        {
            ok = true,
            result.PullBatchId,
            result.RowsPulled,
            result.RowsWrittenStg,
            result.NewCursor,
            result.PullMessage,
            result.TransformBatchId,
            result.StdRows
        });
    }

    /// <summary>
    /// S5 生产退料单 数据中台只读链路手动刷新（DOP 内部 head-detail：NbrMaster/NbrDetail Type='WOD' → mdp_std_production_return(_detail)）。
    /// 独立于 s5-mdp/refresh 的 KPI 管线；只读源、只写 mdp_std 表；不建 stg/dwd；不碰 S2 写路径、S3/S4、T8；WOD 为 0 行时成功、处理数 0。
    /// </summary>
    [HttpPost("s5-production-return-mdp/refresh")]
    public async Task<IActionResult> RefreshS5ProductionReturnMdp(CancellationToken cancellationToken)
    {
        var result = await _productionReturnMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            batchId = result.BatchId,
            headRows = result.HeadRows,
            detailRows = result.DetailRows
        });
    }

    /// <summary>
    /// S5 生产入库单 数据中台只读链路手动刷新（DOP 内部扁平：NbrMaster/NbrDetail Type='WOI' → mdp_std_production_receipt）。
    /// 独立于 s5-mdp/refresh 的 KPI 管线；只读源、只写 mdp_std 表；不建 stg/dwd；不碰 S2 写路径、S7、S3/S4、T8；WOI 为 0 行时成功、处理数 0。
    /// </summary>
    [HttpPost("s5-production-receipt-mdp/refresh")]
    public async Task<IActionResult> RefreshS5ProductionReceiptMdp(CancellationToken cancellationToken)
    {
        var result = await _productionReceiptMdpSyncService.RunFullAsync(cancellationToken, "MANUAL");
        return Ok(new
        {
            ok = true,
            batchId = result.BatchId,
            rows = result.Rows
        });
    }

    /// <summary>
    /// S7 成品入库双模式入站：NbrMaster → mdp_stg_production_receipt，再跑 WOI→mdp_std_production_receipt。
    /// </summary>
    [HttpPost("s7-production-receipt-mdp/inbound")]
    public async Task<IActionResult> InboundS7ProductionReceiptMdp(
        [FromQuery] long? tenantId,
        [FromQuery] bool fullRefresh = false,
        CancellationToken cancellationToken = default)
    {
        var result = await _productionReceiptMdpSyncService.RunInboundAsync(
            tenantId ?? 0,
            fullRefresh,
            cancellationToken);
        return Ok(new
        {
            ok = true,
            result.PullBatchId,
            result.RowsPulled,
            result.RowsWrittenStg,
            result.NewCursor,
            result.TransformBatchId,
            result.StdRows
        });
    }

    /// <summary>
    /// S6 生产执行 KPI 手动刷新（T8 SQL Server 跨库读 → DOP DWD/KPI）。
    /// 路径 A：方老师 v5.4 KPI #22/#23 原 SQL 直发 T8。
    /// </summary>
    [HttpPost("s6-mdp/refresh")]
    public async Task<IActionResult> RefreshS6Mdp(
        [FromQuery] long? targetTenantId,
        [FromQuery] long? targetFactoryId,
        CancellationToken cancellationToken)
    {
        var option = S6MdpRefreshOption.Default();
        if (targetTenantId is > 0) option.TargetTenantId = targetTenantId.Value;
        if (targetFactoryId is > 0) option.TargetFactoryId = targetFactoryId.Value;
        var result = await _s6MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL", option);
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows,
            result.PerKpiDwdRows,
            result.PerKpiKpiRows,
            result.KpiDenominatorStatus,
            result.SourceZtid,
            result.TargetTenantId,
            result.TargetFactoryId
        });
    }

    /// <summary>
    /// S7 成品仓储 KPI 手动刷新（T8 SQL Server 跨库读 → DOP DWD/KPI）。
    /// 路径 A：方老师 v5.4 KPI #25/#26/#27 原 SQL 直发 T8。
    /// </summary>
    [HttpPost("s7-mdp/refresh")]
    public async Task<IActionResult> RefreshS7Mdp(
        [FromQuery] long? targetTenantId,
        [FromQuery] long? targetFactoryId,
        CancellationToken cancellationToken)
    {
        var option = S7MdpRefreshOption.Default();
        if (targetTenantId is > 0) option.TargetTenantId = targetTenantId.Value;
        if (targetFactoryId is > 0) option.TargetFactoryId = targetFactoryId.Value;
        var result = await _s7MdpSyncTransformService.RunFullAsync(cancellationToken, "MANUAL", option);
        return Ok(new
        {
            ok = true,
            result.BatchId,
            result.StageRows,
            result.StandardRows,
            result.DwdRows,
            result.KpiRows,
            result.PerKpiDwdRows,
            result.PerKpiKpiRows,
            result.KpiDenominatorStatus,
            result.SourceZtid,
            result.TargetTenantId,
            result.TargetFactoryId
        });
    }

    [HttpPost("mdp/refresh/{moduleCode}")]
    public async Task<IActionResult> RefreshModuleMdp(string moduleCode, CancellationToken cancellationToken)
    {
        var mc = string.IsNullOrWhiteSpace(moduleCode) ? string.Empty : moduleCode.Trim().ToUpperInvariant();
        return mc switch
        {
            "S1" => await RefreshS1Mdp(cancellationToken),
            "S2" => await RefreshS2Mdp(cancellationToken),
            "S3" => await RefreshS3Mdp(cancellationToken),
            "S4" => await RefreshS4Mdp(cancellationToken),
            _ => Ok(new { ok = true, moduleCode = mc, message = "当前模块暂无 MDP 刷新任务，仅刷新动态看板数据。" })
        };
    }

    private async Task<S2SyncStatusDto?> GetS2SyncStatusAsync()
    {
        return await GetMdpSyncStatusAsync("S2_MDP_SYNC_TRANSFORM");
    }

    private async Task<S2SyncStatusDto?> GetMdpSyncStatusAsync(string jobCode)
    {
        try
        {
            return await _db.Ado.SqlQuerySingleAsync<S2SyncStatusDto>(
                """
                SELECT batch_id AS BatchId, status AS Status, stage_rows AS StageRows,
                       standard_rows AS StandardRows, dwd_rows AS DwdRows,
                       start_time AS StartTime, end_time AS EndTime, error_message AS ErrorMessage
                FROM mdp_transform_run_log
                WHERE job_code=@JobCode
                ORDER BY start_time DESC, id DESC
                LIMIT 1
                """,
                new SugarParameter("@JobCode", jobCode));
        }
        catch
        {
            return null;
        }
    }

    private async Task<List<S2DecompositionDto>> GetS2DecompositionAsync(long tenantId, long factoryId, List<KpiDetailDto> l2)
    {
        var latestL1 = new Dictionary<string, KpiDetailDto>(StringComparer.OrdinalIgnoreCase);
        try
        {
            var rows = await _db.Ado.SqlQueryAsync<KpiDetailDto>(
                """
                SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode, k.MetricName AS MetricName,
                       v.metric_value AS MetricValue, v.target_value AS TargetValue,
                       v.status_color AS StatusColor, v.trend_flag AS TrendFlag, v.biz_date AS StatDate
                FROM ado_s9_kpi_value_l1_day v
                LEFT JOIN ado_smart_ops_kpi_master k ON k.TenantId=v.tenant_id AND k.MetricCode=v.metric_code
                WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.module_code='S2' AND v.is_deleted=0
                  AND v.biz_date=(SELECT MAX(biz_date) FROM ado_s9_kpi_value_l1_day
                                  WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code='S2' AND is_deleted=0)
                """,
                new { tenantId, factoryId });
            latestL1 = rows.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
                .ToDictionary(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase);
        }
        catch
        {
            latestL1 = new Dictionary<string, KpiDetailDto>(StringComparer.OrdinalIgnoreCase);
        }

        var latestL2 = l2.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

        var operationSummary = await GetS2OperationSummaryAsync(tenantId, factoryId);
        var resourceSummary = await GetS2ResourceSummaryAsync(tenantId, factoryId);

        return new List<S2DecompositionDto>
        {
            new()
            {
                Title = "订单排程",
                Active = true,
                Metrics = new List<string>
                {
                    $"1. 周期：{FormatMetric(latestL1, "S2_L1_001", "天")}",
                    $"2. 满足率：{FormatMetric(latestL1, "S2_L1_002", "%")}",
                    $"3. 在制库存：{FormatMetric(latestL1, "S2_L1_004", "天")}",
                    $"4. 人效：{FormatMetric(latestL1, "S2_L1_003", "单/人")}"
                }
            },
            new()
            {
                Title = "工单排程",
                Metrics = new List<string>
                {
                    $"1. 周期：{FormatMetric(latestL2, "S2_L2_001", "天")}",
                    $"2. 满足率：{FormatMetric(latestL2, "S2_L2_002", "%")}",
                    $"3. 人效：{FormatMetric(latestL2, "S2_L2_003", "单/人")}"
                }
            },
            new()
            {
                Title = "工序排程",
                Metrics = new List<string>
                {
                    $"1. 工序数：{operationSummary.OperationCount}",
                    $"2. 完成量：{Math.Round(operationSummary.CompletedQty ?? 0, 2)}",
                    $"3. 排程量：{Math.Round(operationSummary.ScheduledQty ?? 0, 2)}"
                }
            },
            new()
            {
                Title = "资源排程",
                Metrics = new List<string>
                {
                    $"1. 人员数：{Math.Round(resourceSummary.PersonCount ?? 0, 2)}",
                    $"2. 异常数：{resourceSummary.RiskCount}",
                    $"3. 产线数：{resourceSummary.LineCount}"
                }
            }
        };
    }

    private async Task<S2OperationSummaryDto> GetS2OperationSummaryAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQuerySingleAsync<S2OperationSummaryDto>(
                """
                SELECT SUM(operation_count) AS OperationCount,
                       SUM(completed_op_qty) AS CompletedQty,
                       SUM(scheduled_qty) AS ScheduledQty
                FROM dwd_order_schedule_trans
                WHERE tenant_id=@tenantId AND factory_id=@factoryId
                  AND calc_batch_id=(SELECT calc_batch_id FROM dwd_order_schedule_trans
                                     WHERE tenant_id=@tenantId AND factory_id=@factoryId
                                     ORDER BY calc_time DESC, id DESC LIMIT 1)
                """,
                new { tenantId, factoryId }) ?? new S2OperationSummaryDto();
        }
        catch
        {
            return new S2OperationSummaryDto();
        }
    }

    private async Task<S2ResourceSummaryDto> GetS2ResourceSummaryAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQuerySingleAsync<S2ResourceSummaryDto>(
                """
                SELECT SUM(resource_person_count) AS PersonCount,
                       SUM(CASE WHEN schedule_satisfaction_flag=0 THEN 1 ELSE 0 END) AS RiskCount,
                       COUNT(DISTINCT COALESCE(NULLIF(prod_line,''), NULLIF(site_code,''), '未分配')) AS LineCount
                FROM dwd_order_schedule_trans
                WHERE tenant_id=@tenantId AND factory_id=@factoryId
                  AND calc_batch_id=(SELECT calc_batch_id FROM dwd_order_schedule_trans
                                     WHERE tenant_id=@tenantId AND factory_id=@factoryId
                                     ORDER BY calc_time DESC, id DESC LIMIT 1)
                """,
                new { tenantId, factoryId }) ?? new S2ResourceSummaryDto();
        }
        catch
        {
            return new S2ResourceSummaryDto();
        }
    }

    private async Task<List<S2TrendDto>> GetS2TrendAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQueryAsync<S2TrendDto>(
                """
                SELECT DATE_FORMAT(d.biz_date, '%m-%d') AS DateLabel,
                       MAX(CASE WHEN d.metric_code='S2_L1_001' THEN d.metric_value END) AS CycleDays,
                       MAX(CASE WHEN d.metric_code='S2_L1_002' THEN d.metric_value END) AS SatisfactionPct
                FROM ado_s9_kpi_value_l1_day d
                WHERE d.tenant_id=@tenantId AND d.factory_id=@factoryId AND d.module_code='S2' AND d.is_deleted=0
                  AND d.metric_code IN ('S2_L1_001','S2_L1_002')
                GROUP BY d.biz_date
                ORDER BY d.biz_date DESC
                LIMIT 7
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S2TrendDto>();
        }
    }

    private async Task<List<S2DistributionDto>> GetS2DistributionAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQueryAsync<S2DistributionDto>(
                """
                SELECT COALESCE(NULLIF(prod_line,''), NULLIF(site_code,''), '未分配') AS Name,
                       ROUND(100 * SUM(schedule_satisfaction_flag) / NULLIF(COUNT(1), 0), 2) AS SatisfactionPct,
                       COUNT(1) AS TotalCount,
                       SUM(CASE WHEN schedule_satisfaction_flag=0 THEN 1 ELSE 0 END) AS RiskCount
                FROM dwd_order_schedule_trans
                WHERE tenant_id=@tenantId AND factory_id=@factoryId
                  AND calc_batch_id=(SELECT calc_batch_id FROM dwd_order_schedule_trans
                                     WHERE tenant_id=@tenantId AND factory_id=@factoryId
                                     ORDER BY calc_time DESC, id DESC LIMIT 1)
                GROUP BY COALESCE(NULLIF(prod_line,''), NULLIF(site_code,''), '未分配')
                ORDER BY SatisfactionPct, TotalCount DESC
                LIMIT 8
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S2DistributionDto>();
        }
    }

    private async Task<List<S8AlertDto>> GetS2DerivedAlertsAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQueryAsync<S8AlertDto>(
                """
                SELECT DATE_FORMAT(calc_time, '%H:%i:%s') AS Time,
                       CASE
                         WHEN last_plan_date IS NULL THEN 'high'
                         WHEN due_date IS NOT NULL AND last_plan_date > due_date THEN 'critical'
                         WHEN IFNULL(resource_person_count, 0) <= 0 THEN 'medium'
                         ELSE 'info'
                       END AS LevelCode,
                       CONCAT('工单 ', work_order, ' ',
                         CASE
                           WHEN last_plan_date IS NULL THEN '尚未形成排程'
                           WHEN due_date IS NOT NULL AND last_plan_date > due_date THEN '排程晚于交期'
                           WHEN IFNULL(resource_person_count, 0) <= 0 THEN '缺少资源人员配置'
                           ELSE '排程状态正常'
                         END) AS Message
                FROM dwd_order_schedule_trans
                WHERE tenant_id=@tenantId AND factory_id=@factoryId
                  AND calc_batch_id=(SELECT calc_batch_id FROM dwd_order_schedule_trans
                                     WHERE tenant_id=@tenantId AND factory_id=@factoryId
                                     ORDER BY calc_time DESC, id DESC LIMIT 1)
                  AND (last_plan_date IS NULL OR (due_date IS NOT NULL AND last_plan_date > due_date) OR IFNULL(resource_person_count, 0) <= 0)
                ORDER BY FIELD(LevelCode, 'critical', 'high', 'medium', 'info'), due_date IS NULL, due_date
                LIMIT 20
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S8AlertDto>();
        }
    }

    private async Task<List<S2DecompositionDto>> GetS3DecompositionAsync(long tenantId, long factoryId, List<KpiDetailDto> l2)
    {
        var l1 = await GetLatestModuleL1Async("S3", tenantId, factoryId);
        var latestL2 = l2.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
        var readiness = await GetS3ReadinessSummaryAsync(tenantId);
        var delivery = await GetS3DeliverySummaryAsync(tenantId);

        return new List<S2DecompositionDto>
        {
            new()
            {
                Title = "物料需求计划",
                Active = true,
                Metrics = new List<string>
                {
                    $"1. 周期：{FormatMetric(l1, "S3_L1_001", "天")}",
                    $"2. 满足率：{Math.Round(readiness.ReadyRatePct ?? GetMetricValue(l1, "S3_L1_002") ?? 0, 2)}%",
                    $"3. 覆盖SKU：{readiness.ComponentCount}",
                    $"4. 缺口数量：{Math.Round(readiness.ShortageQty ?? 0, 2)}"
                }
            },
            new()
            {
                Title = "物料交货计划",
                Metrics = new List<string>
                {
                    $"1. 周期：{FormatMetric(latestL2, "S3_L2_004", "天")}",
                    $"2. 满足率：{FormatMetric(latestL2, "S3_L2_005", "%")}",
                    $"3. 风险供应商：{delivery.RiskSupplierCount}",
                    $"4. 待交数量：{Math.Round(delivery.RemainingQty ?? 0, 2)}"
                }
            }
        };
    }

    private async Task<List<S3BranchKpiGroupDto>> GetS3BranchKpisAsync(long tenantId, long factoryId, List<KpiDetailDto> l2)
    {
        var l1 = await GetLatestModuleL1Async("S3", tenantId, factoryId);
        var latestL2 = l2.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
            .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
        var readiness = await GetS3ReadinessSummaryAsync(tenantId);
        var delivery = await GetS3DeliverySummaryAsync(tenantId);

        var demandCycle = GetMetricValue(l1, "S3_L1_001");
        var demandReadyRate = readiness.ReadyRatePct ?? GetMetricValue(l1, "S3_L1_002");
        var deliveryCycle = GetMetricValue(latestL2, "S3_L2_004");
        var deliveryRate = GetMetricValue(latestL2, "S3_L2_005");

        return new List<S3BranchKpiGroupDto>
        {
            new()
            {
                Branch = "MRP",
                Title = "物料需求计划",
                Items = new List<S3BranchKpiDto>
                {
                    BuildBranchKpi("需求计划周期", demandCycle, "d", InverseBar(demandCycle, 15m)),
                    BuildBranchKpi("需求计划满足率", demandReadyRate, "%", PercentBar(demandReadyRate)),
                    BuildBranchKpi("计划覆盖SKU", readiness.ComponentCount, "SKU", PercentBar(readiness.ComponentCount, Math.Max(readiness.ComponentCount, 1)))
                }
            },
            new()
            {
                Branch = "MDP",
                Title = "物料交货计划",
                Items = new List<S3BranchKpiDto>
                {
                    BuildBranchKpi("交货计划周期", deliveryCycle, "d", InverseBar(deliveryCycle, 10.5m)),
                    BuildBranchKpi("交货计划满足率", deliveryRate, "%", PercentBar(deliveryRate)),
                    BuildBranchKpi("风险供应商", delivery.RiskSupplierCount, "家", InverseBar(delivery.RiskSupplierCount, Math.Max(delivery.SupplierCount, 1)))
                }
            }
        };
    }

    private async Task<List<S2TrendDto>> GetS3TrendAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQueryAsync<S2TrendDto>(
                """
                SELECT DATE_FORMAT(d.biz_date, '%m-%d') AS DateLabel,
                       MAX(CASE WHEN d.metric_code='S3_L1_001' THEN d.metric_value END) AS CycleDays,
                       MAX(CASE WHEN d.metric_code='S3_L1_002' THEN d.metric_value END) AS SatisfactionPct
                FROM ado_s9_kpi_value_l1_day d
                WHERE d.tenant_id=@tenantId AND d.factory_id=@factoryId AND d.module_code='S3' AND d.is_deleted=0
                  AND d.metric_code IN ('S3_L1_001','S3_L1_002')
                GROUP BY d.biz_date
                ORDER BY d.biz_date DESC
                LIMIT 7
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S2TrendDto>();
        }
    }

    private async Task<List<S2DistributionDto>> GetS3DistributionAsync(long tenantId, long factoryId)
    {
        _ = factoryId;
        try
        {
            return await _db.Ado.SqlQueryAsync<S2DistributionDto>(
                """
                SELECT COALESCE(NULLIF(supplier_name,''), NULLIF(supplier_code,''), '未分配') AS Name,
                       ROUND(100 * SUM(CASE WHEN IFNULL(schedule_qty,0) >= IFNULL(order_qty,0) AND IFNULL(order_qty,0) > 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(1), 0), 2) AS SatisfactionPct,
                       COUNT(1) AS TotalCount,
                       SUM(CASE WHEN risk_level IN ('HIGH','MEDIUM') THEN 1 ELSE 0 END) AS RiskCount
                FROM dwd_supplier_delivery
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_supplier_delivery WHERE tenant_id=@tenantId)
                GROUP BY COALESCE(NULLIF(supplier_name,''), NULLIF(supplier_code,''), '未分配')
                ORDER BY RiskCount DESC, SatisfactionPct, TotalCount DESC
                LIMIT 8
                """,
                new { tenantId });
        }
        catch
        {
            return new List<S2DistributionDto>();
        }
    }

    private async Task<List<S8AlertDto>> GetS3DerivedAlertsAsync(long tenantId, long factoryId)
    {
        _ = factoryId;
        try
        {
            return await _db.Ado.SqlQueryAsync<S8AlertDto>(
                """
                SELECT DATE_FORMAT(calc_time, '%H:%i:%s') AS Time,
                       CASE WHEN risk_level='HIGH' THEN 'critical'
                            WHEN risk_level='MEDIUM' THEN 'warning'
                            ELSE 'info' END AS LevelCode,
                       CONCAT('供应商 ', COALESCE(NULLIF(supplier_name,''), supplier_code, '未分配'),
                              ' 物料 ', COALESCE(NULLIF(item_code,''), '未指定'),
                              '：', COALESCE(NULLIF(risk_reason,''), risk_type, '供应风险')) AS Message
                FROM dwd_supplier_risk
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_supplier_risk WHERE tenant_id=@tenantId)
                ORDER BY FIELD(risk_level, 'HIGH', 'MEDIUM', 'LOW'), risk_count DESC, calc_time DESC
                LIMIT 20
                """,
                new { tenantId });
        }
        catch
        {
            return new List<S8AlertDto>();
        }
    }

    private async Task<List<S2TrendDto>> GetS4TrendAsync(long tenantId, long factoryId)
    {
        try
        {
            return await _db.Ado.SqlQueryAsync<S2TrendDto>(
                """
                SELECT DATE_FORMAT(d.biz_date, '%m-%d') AS DateLabel,
                       MAX(CASE WHEN d.metric_code='S4_L1_001' THEN d.metric_value END) AS CycleDays,
                       MAX(CASE WHEN d.metric_code='S4_L1_002' THEN d.metric_value END) AS SatisfactionPct
                FROM ado_s9_kpi_value_l1_day d
                WHERE d.tenant_id=@tenantId AND d.factory_id=@factoryId AND d.module_code='S4' AND d.is_deleted=0
                  AND d.metric_code IN ('S4_L1_001','S4_L1_002')
                GROUP BY d.biz_date
                ORDER BY d.biz_date DESC
                LIMIT 7
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S2TrendDto>();
        }
    }

    private async Task<List<S2DistributionDto>> GetS4DistributionAsync(long tenantId, long factoryId)
    {
        _ = factoryId;
        try
        {
            return await _db.Ado.SqlQueryAsync<S2DistributionDto>(
                """
                SELECT COALESCE(NULLIF(supplier_name,''), NULLIF(supplier_code,''), '未分配') AS Name,
                       ROUND(100 * SUM(CASE WHEN IFNULL(receipt_qty,0) >= IFNULL(order_qty,0) AND IFNULL(order_qty,0) > 0 THEN 1
                            WHEN delivery_status='COMPLETED' THEN 1 ELSE 0 END) / NULLIF(COUNT(1), 0), 2) AS SatisfactionPct,
                       COUNT(1) AS TotalCount,
                       SUM(CASE WHEN risk_level IN ('HIGH','MEDIUM') THEN 1 ELSE 0 END) AS RiskCount
                FROM dwd_supplier_delivery
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_supplier_delivery WHERE tenant_id=@tenantId)
                GROUP BY COALESCE(NULLIF(supplier_name,''), NULLIF(supplier_code,''), '未分配')
                ORDER BY RiskCount DESC, SatisfactionPct, TotalCount DESC
                LIMIT 8
                """,
                new { tenantId });
        }
        catch
        {
            return new List<S2DistributionDto>();
        }
    }

    private async Task<List<S8AlertDto>> GetS4DerivedAlertsAsync(long tenantId, long factoryId)
    {
        _ = factoryId;
        try
        {
            var fromDwd = await _db.Ado.SqlQueryAsync<S8AlertDto>(
                """
                SELECT DATE_FORMAT(calc_time, '%H:%i:%s') AS Time,
                       CASE WHEN risk_level='HIGH' THEN 'critical'
                            WHEN risk_level='MEDIUM' THEN 'warning'
                            ELSE 'info' END AS LevelCode,
                       CONCAT('采购订单 ', COALESCE(po_no, '—'), ' 供应商 ', COALESCE(NULLIF(supplier_name,''), supplier_code, '未分配'),
                              '：', COALESCE(delivery_status, 'OPEN'), ' 剩余 ', CAST(IFNULL(remaining_qty,0) AS CHAR)) AS Message
                FROM dwd_supplier_delivery
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_supplier_delivery WHERE tenant_id=@tenantId)
                  AND risk_level IN ('HIGH','MEDIUM')
                ORDER BY FIELD(risk_level, 'HIGH', 'MEDIUM'), remaining_qty DESC
                LIMIT 15
                """,
                new { tenantId });
            if (fromDwd.Count > 0)
                return fromDwd;
        }
        catch
        {
            // fall through to S8 alerts
        }

        try
        {
            return await _db.Ado.SqlQueryAsync<S8AlertDto>(
                """
                SELECT DATE_FORMAT(alert_time, '%H:%i:%s') AS Time,
                       IFNULL(level_code, 'medium') AS LevelCode,
                       IFNULL(message, '采购执行异常') AS Message
                FROM ado_s8_alert_record
                WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0
                  AND (UPPER(IFNULL(module_code,''))='S4' OR metric_code LIKE 'S4%')
                ORDER BY alert_time DESC
                LIMIT 20
                """,
                new { tenantId, factoryId });
        }
        catch
        {
            return new List<S8AlertDto>();
        }
    }

    private async Task<Dictionary<string, KpiDetailDto>> GetLatestModuleL1Async(string moduleCode, long tenantId, long factoryId)
    {
        try
        {
            var rows = await _db.Ado.SqlQueryAsync<KpiDetailDto>(
                """
                SELECT v.module_code AS ModuleCode, v.metric_code AS MetricCode, k.MetricName AS MetricName,
                       v.metric_value AS MetricValue, v.target_value AS TargetValue,
                       v.status_color AS StatusColor, v.trend_flag AS TrendFlag, v.biz_date AS StatDate
                FROM ado_s9_kpi_value_l1_day v
                LEFT JOIN ado_smart_ops_kpi_master k ON k.TenantId=v.tenant_id AND k.MetricCode=v.metric_code
                WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.module_code=@moduleCode AND v.is_deleted=0
                  AND v.biz_date=(SELECT MAX(biz_date) FROM ado_s9_kpi_value_l1_day
                                  WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode AND is_deleted=0)
                """,
                new { moduleCode, tenantId, factoryId });
            return rows.Where(u => !string.IsNullOrWhiteSpace(u.MetricCode))
                .GroupBy(u => u.MetricCode!, StringComparer.OrdinalIgnoreCase)
                .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
        }
        catch
        {
            return new Dictionary<string, KpiDetailDto>(StringComparer.OrdinalIgnoreCase);
        }
    }

    private async Task<S3ReadinessSummaryDto> GetS3ReadinessSummaryAsync(long tenantId)
    {
        try
        {
            return await _db.Ado.SqlQuerySingleAsync<S3ReadinessSummaryDto>(
                """
                SELECT COUNT(DISTINCT component_item_code) AS ComponentCount,
                       ROUND(100 * SUM(CASE WHEN IFNULL(shortage_qty,0) <= 0 OR UPPER(IFNULL(ready_status,'')) IN ('READY','SUFFICIENT','OK') THEN 1 ELSE 0 END) / NULLIF(COUNT(1), 0), 2) AS ReadyRatePct,
                       SUM(IFNULL(shortage_qty, 0)) AS ShortageQty
                FROM dwd_material_readiness
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_material_readiness WHERE tenant_id=@tenantId)
                """,
                new { tenantId }) ?? new S3ReadinessSummaryDto();
        }
        catch
        {
            return new S3ReadinessSummaryDto();
        }
    }

    private async Task<S3DeliverySummaryDto> GetS3DeliverySummaryAsync(long tenantId)
    {
        try
        {
            return await _db.Ado.SqlQuerySingleAsync<S3DeliverySummaryDto>(
                """
                SELECT COUNT(DISTINCT supplier_code) AS SupplierCount,
                       COUNT(DISTINCT CASE WHEN risk_level IN ('HIGH','MEDIUM') THEN supplier_code END) AS RiskSupplierCount,
                       SUM(IFNULL(remaining_qty, 0)) AS RemainingQty
                FROM dwd_supplier_delivery
                WHERE tenant_id=@tenantId
                  AND stat_date=(SELECT MAX(stat_date) FROM dwd_supplier_delivery WHERE tenant_id=@tenantId)
                """,
                new { tenantId }) ?? new S3DeliverySummaryDto();
        }
        catch
        {
            return new S3DeliverySummaryDto();
        }
    }

    private static string FormatMetric(Dictionary<string, KpiDetailDto> rows, string metricCode, string unit)
    {
        if (!rows.TryGetValue(metricCode, out var row) || row.MetricValue == null)
            return $"--{unit}";
        return $"{Math.Round(row.MetricValue.Value, 2)}{unit}";
    }

    private static decimal? GetMetricValue(Dictionary<string, KpiDetailDto> rows, string metricCode)
    {
        return rows.TryGetValue(metricCode, out var row) ? row.MetricValue : null;
    }

    private static S3BranchKpiDto BuildBranchKpi(string label, decimal? value, string unit, decimal barPct)
    {
        return new S3BranchKpiDto
        {
            Label = label,
            Value = value,
            Unit = unit,
            BarPct = barPct,
            Status = barPct >= 90 ? "success" : barPct >= 70 ? "warning" : "danger"
        };
    }

    private static decimal PercentBar(decimal? value, decimal max = 100m)
    {
        if (value == null || max <= 0)
            return 0;
        return Math.Round(Math.Max(0, Math.Min(100, value.Value / max * 100)), 2);
    }

    private static decimal InverseBar(decimal? value, decimal target)
    {
        if (value == null || value <= 0 || target <= 0)
            return 0;
        return Math.Round(Math.Max(0, Math.Min(100, target / value.Value * 100)), 2);
    }

    /// <summary>
    /// 智慧运营看板基础查询下拉：产品、订单号、产线（兼容旧接口；优先 S0 主数据）。
    /// </summary>
    [HttpGet("smart-ops-filter-options")]
    public async Task<IActionResult> GetSmartOpsFilterOptions([FromQuery] long factoryId = 1)
    {
        _ = factoryId;
        var tenantId = AidopTenantHelper.GetTenantId(HttpContext);
        const int cap = 200;

        var productItems = await SearchProductsAsync(tenantId, null, cap);
        var orderItems = await SearchOrderNosAsync(tenantId, null, cap);
        var lineItems = await SearchProductionLinesAsync(tenantId, null, cap);

        return Ok(new
        {
            products = productItems.Select(x => x.Value).Distinct(StringComparer.Ordinal).OrderBy(x => x, StringComparer.Ordinal).ToList(),
            orderNos = orderItems.Select(x => x.Value).Distinct(StringComparer.Ordinal).OrderBy(x => x, StringComparer.Ordinal).ToList(),
            productionLines = lineItems.Select(x => x.Value).Distinct(StringComparer.Ordinal).OrderBy(x => x, StringComparer.Ordinal).ToList()
        });
    }

    private sealed class SmartOpsOptionRow
    {
        public string? Value { get; set; }
    }

    private sealed class HomeL1Dto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
    }

    private sealed class HomeL1RawDto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
        public string? Direction { get; set; }
        public decimal? YellowThreshold { get; set; }
        public decimal? RedThreshold { get; set; }
    }

    private sealed class S8AlertDto
    {
        public string? Time { get; set; }
        public string? Module { get; set; }
        public string? Message { get; set; }
        public string? LevelCode { get; set; }
    }

    private sealed class KpiDetailDto
    {
        public string? ModuleCode { get; set; }
        public string? MetricCode { get; set; }
        public string? MetricName { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
        public DateTime? StatDate { get; set; }
        public string? ParentMetricCode { get; set; }
    }

    private sealed class KpiDrillGroupDto
    {
        public string? ParentMetricCode { get; set; }
        public string Title { get; set; } = string.Empty;
        public List<KpiDrillItemDto> Items { get; set; } = new();
    }

    private sealed class KpiDrillItemDto
    {
        public string? MetricCode { get; set; }
        public string? MetricName { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
        public string? StatDate { get; set; }
    }

    private sealed class S2ScheduleDto
    {
        public string? OrderNo { get; set; }
        public string? Customer { get; set; }
        public string? Product { get; set; }
        public string? ProductionLine { get; set; }
        public decimal? Quantity { get; set; }
        public DateTime? DeliveryDate { get; set; }
        public DateTime? StartTime { get; set; }
        public decimal? CycleDays { get; set; }
        public decimal? Satisfaction { get; set; }
        public string? Status { get; set; }
    }

    private sealed class S2SyncStatusDto
    {
        public string? BatchId { get; set; }
        public string? Status { get; set; }
        public int? StageRows { get; set; }
        public int? StandardRows { get; set; }
        public int? DwdRows { get; set; }
        public DateTime? StartTime { get; set; }
        public DateTime? EndTime { get; set; }
        public string? ErrorMessage { get; set; }
    }

    private sealed class S2DecompositionDto
    {
        public string Title { get; set; } = string.Empty;
        public bool Active { get; set; }
        public List<string> Metrics { get; set; } = new();
    }

    private sealed class S2TrendDto
    {
        public string? DateLabel { get; set; }
        public decimal? CycleDays { get; set; }
        public decimal? SatisfactionPct { get; set; }
    }

    private sealed class S2DistributionDto
    {
        public string? Name { get; set; }
        public decimal? SatisfactionPct { get; set; }
        public int TotalCount { get; set; }
        public int RiskCount { get; set; }
    }

    private sealed class S2OperationSummaryDto
    {
        public int OperationCount { get; set; }
        public decimal? CompletedQty { get; set; }
        public decimal? ScheduledQty { get; set; }
    }

    private sealed class S2ResourceSummaryDto
    {
        public decimal? PersonCount { get; set; }
        public int RiskCount { get; set; }
        public int LineCount { get; set; }
    }

    private sealed class S3BranchKpiGroupDto
    {
        public string Branch { get; set; } = string.Empty;
        public string Title { get; set; } = string.Empty;
        public List<S3BranchKpiDto> Items { get; set; } = new();
    }

    private sealed class S3BranchKpiDto
    {
        public string Label { get; set; } = string.Empty;
        public decimal? Value { get; set; }
        public string Unit { get; set; } = string.Empty;
        public decimal BarPct { get; set; }
        public string Status { get; set; } = "info";
    }

    private sealed class S3ReadinessSummaryDto
    {
        public int ComponentCount { get; set; }
        public decimal? ReadyRatePct { get; set; }
        public decimal? ShortageQty { get; set; }
    }

    private sealed class S3DeliverySummaryDto
    {
        public int SupplierCount { get; set; }
        public int RiskSupplierCount { get; set; }
        public decimal? RemainingQty { get; set; }
    }
}