AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/AidopKanbanController.cs

using Admin.NET.Core;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Production;
using Admin.NET.Plugin.AiDOP.Supply;
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 S2MdpSyncTransformService _s2MdpSyncTransformService;
    private readonly S3MdpSyncTransformService _s3MdpSyncTransformService;

    public AidopKanbanController(ISqlSugarClient db, S2MdpSyncTransformService s2MdpSyncTransformService, S3MdpSyncTransformService s3MdpSyncTransformService)
    {
        _db = db;
        _s2MdpSyncTransformService = s2MdpSyncTransformService;
        _s3MdpSyncTransformService = s3MdpSyncTransformService;
    }

    [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 module_code 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 AND module_code=@moduleCode
ORDER BY id DESC, metric_code
LIMIT 30
""";
        const string l3Sql = """
SELECT module_code 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 AND module_code=@moduleCode
ORDER BY id DESC, 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);
        }
        return Ok(new
        {
            moduleCode,
            l2,
            l3,
            syncStatus,
            decomposition,
            branchKpis,
            trend,
            distribution,
            schedules,
            alerts = alerts.Select(x => new
            {
                time = x.Time,
                message = x.Message,
                level = x.LevelCode
            })
        });
    }

    [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
        });
    }

    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<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>
    /// 智慧运营看板基础查询下拉：产品、订单号、产线（来自 Demo 业务表；无租户列时忽略 tenant/factory）。
    /// </summary>
    [HttpGet("smart-ops-filter-options")]
    public async Task<IActionResult> GetSmartOpsFilterOptions([FromQuery] long factoryId = 1)
    {
        _ = factoryId;

        var products = new HashSet<string>(StringComparer.Ordinal);
        var orderNos = new HashSet<string>(StringComparer.Ordinal);
        var lines = new HashSet<string>(StringComparer.Ordinal);

        try
        {
            var op = await _db.Queryable<AdoOrder>()
                .Where(x => x.Product != null && x.Product != "")
                .Select(x => x.Product)
                .Distinct()
                .ToListAsync();
            foreach (var s in op.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var on = await _db.Queryable<AdoOrder>()
                .Where(x => x.OrderNo != null && x.OrderNo != "")
                .Select(x => x.OrderNo)
                .Distinct()
                .ToListAsync();
            foreach (var s in on.Where(s => !string.IsNullOrWhiteSpace(s)))
                orderNos.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var woP = await _db.Queryable<AdoWorkOrder>()
                .Where(x => x.Product != null && x.Product != "")
                .Select(x => x.Product)
                .Distinct()
                .ToListAsync();
            foreach (var s in woP.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());

            var wc = await _db.Queryable<AdoWorkOrder>()
                .Where(x => x.WorkCenter != null && x.WorkCenter != "")
                .Select(x => x.WorkCenter)
                .Distinct()
                .ToListAsync();
            foreach (var s in wc.Where(s => !string.IsNullOrWhiteSpace(s)))
                lines.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        try
        {
            var pn = await _db.Queryable<AdoPlan>()
                .Where(x => x.ProductName != null && x.ProductName != "")
                .Select(x => x.ProductName)
                .Distinct()
                .ToListAsync();
            foreach (var s in pn.Where(s => !string.IsNullOrWhiteSpace(s)))
                products.Add(s.Trim());
        }
        catch
        {
            // ignored
        }

        return Ok(new
        {
            products = products.OrderBy(x => x, StringComparer.Ordinal).ToList(),
            orderNos = orderNos.OrderBy(x => x, StringComparer.Ordinal).ToList(),
            productionLines = lines.OrderBy(x => x, StringComparer.Ordinal).ToList()
        });
    }

    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; }
    }

    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; }
    }
}