AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/SmartOps/SmartDiagnosisEvidenceRegistry.cs

namespace Admin.NET.Plugin.AiDOP.SmartOps;

public enum SmartDiagnosisEvidenceDomain
{
    Delivery,
    Supply,
    Manufacturing,
    Quality,
    Exception
}

public sealed record SmartDiagnosisEvidenceFilterColumn(string QueryKey, string SqlColumn);

public sealed record SmartDiagnosisEvidenceSource(
    string Key,
    SmartDiagnosisEvidenceDomain Domain,
    IReadOnlyList<string> Modules,
    string ObjectType,
    string DrillPath,
    string CountSql,
    string ItemSql,
    IReadOnlyList<SmartDiagnosisEvidenceFilterColumn> Filters)
{
    public IReadOnlyList<string> MetricCodes { get; init; } = Array.Empty<string>();
    public string ContributionUnit { get; init; } = "qty";
    public string? DateColumn { get; init; }
    public string? DrillQueryKey { get; init; }
}

public static class SmartDiagnosisEvidenceRegistry
{
    public const int DefaultLimit = 20;
    public const int MaxLimit = 50;

    private static readonly SmartDiagnosisEvidenceSource[] Sources =
    {
        new(
            "s1_contract_review_flow",
            SmartDiagnosisEvidenceDomain.Delivery,
            new[] { "S1" },
            "contract_review_flow",
            "/aidop/s1/order-mgmt/contract-review",
            """
            SELECT COUNT(*) FROM mdp_stg_so
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND source_table='ado_contract_review_flow'
              AND (
                (JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageNo')) =
                  CASE
                    WHEN @metricCode IN ('S1_L3_001','S1_L3_101') THEN '1'
                    WHEN @metricCode IN ('S1_L3_002','S1_L3_102') THEN '2'
                    WHEN @metricCode IN ('S1_L3_003','S1_L3_103') THEN '3'
                    WHEN @metricCode IN ('S1_L3_004','S1_L3_104') THEN '4'
                    WHEN @metricCode IN ('S1_L3_005','S1_L3_105') THEN '5'
                    ELSE ''
                  END)
                OR
                (JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageNo'))='1'
                  AND CASE COALESCE(
                    NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.DeptNo')),''),
                    JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Department'))
                  )
                    WHEN '法律事务部' THEN 'LAW'
                    WHEN '技术售前组' THEN 'PRE_SALES'
                    WHEN '综合主计划' THEN 'MPS'
                    WHEN '试验站' THEN 'TEST'
                    ELSE COALESCE(
                      NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.DeptNo')),''),
                      JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Department'))
                    )
                  END =
                  CASE
                    WHEN @metricCode IN ('S1_L4_001','S1_L4_101') THEN 'LAW'
                    WHEN @metricCode IN ('S1_L4_002','S1_L4_102') THEN 'PRE_SALES'
                    WHEN @metricCode IN ('S1_L4_003','S1_L4_103') THEN 'MPS'
                    WHEN @metricCode IN ('S1_L4_004','S1_L4_104') THEN 'TEST'
                    ELSE ''
                  END)
                OR @metricCode IN ('S1_L1_001','S1_L2_004','S1_L4_202')
              )
            """,
            """
            SELECT
              COALESCE(
                NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.FlowNo')),''),
                NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ReviewNo')),''),
                NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ContractNo')),''),
                NULLIF(source_biz_key,''),
                source_row_id
              ) AS ObjectCode,
              CONCAT(
                COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Department')),''),'评审环节'),
                ' / ',
                COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageName')),''),'阶段 ',
                  JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageNo')))
              ) AS Title,
              COALESCE(
                TIMESTAMPDIFF(
                  HOUR,
                  STR_TO_DATE(REPLACE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StartTime')),'null'),''),'T',' '), '%Y-%m-%d %H:%i:%s.%f'),
                  STR_TO_DATE(REPLACE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.CompleteTime')),'null'),''),'T',' '), '%Y-%m-%d %H:%i:%s.%f')
                ),
                CASE
                  WHEN JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ActualDays')) REGEXP '^-?[0-9]+(\\.[0-9]+)?$'
                  THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ActualDays')) AS DECIMAL(18,6)) * 24
                END,
                0
              ) AS Contribution,
              CASE
                WHEN JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Status')) IN ('逾期','超时','REJECTED','FAILED') THEN 'red'
                WHEN JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.CompleteTime')) IS NULL THEN 'yellow'
                ELSE 'green'
              END AS Status,
              COALESCE(
                STR_TO_DATE(REPLACE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.CompleteTime')),'null'),''),'T',' '), '%Y-%m-%d %H:%i:%s.%f'),
                STR_TO_DATE(REPLACE(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StartTime')),'null'),''),'T',' '), '%Y-%m-%d %H:%i:%s.%f'),
                sync_time
              ) AS OccurredAt
            FROM mdp_stg_so
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND source_table='ado_contract_review_flow'
              AND (
                (JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageNo')) =
                  CASE
                    WHEN @metricCode IN ('S1_L3_001','S1_L3_101') THEN '1'
                    WHEN @metricCode IN ('S1_L3_002','S1_L3_102') THEN '2'
                    WHEN @metricCode IN ('S1_L3_003','S1_L3_103') THEN '3'
                    WHEN @metricCode IN ('S1_L3_004','S1_L3_104') THEN '4'
                    WHEN @metricCode IN ('S1_L3_005','S1_L3_105') THEN '5'
                    ELSE ''
                  END)
                OR
                (JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.StageNo'))='1'
                  AND CASE COALESCE(
                    NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.DeptNo')),''),
                    JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Department'))
                  )
                    WHEN '法律事务部' THEN 'LAW'
                    WHEN '技术售前组' THEN 'PRE_SALES'
                    WHEN '综合主计划' THEN 'MPS'
                    WHEN '试验站' THEN 'TEST'
                    ELSE COALESCE(
                      NULLIF(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.DeptNo')),''),
                      JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.Department'))
                    )
                  END =
                  CASE
                    WHEN @metricCode IN ('S1_L4_001','S1_L4_101') THEN 'LAW'
                    WHEN @metricCode IN ('S1_L4_002','S1_L4_102') THEN 'PRE_SALES'
                    WHEN @metricCode IN ('S1_L4_003','S1_L4_103') THEN 'MPS'
                    WHEN @metricCode IN ('S1_L4_004','S1_L4_104') THEN 'TEST'
                    ELSE ''
                  END)
                OR @metricCode IN ('S1_L1_001','S1_L2_004','S1_L4_202')
              )
            ORDER BY Contribution DESC, OccurredAt DESC
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "小时",
            MetricCodes = new[]
            {
                "S1_L3_001", "S1_L3_002", "S1_L3_003", "S1_L3_004", "S1_L3_005",
                "S1_L3_101", "S1_L3_102", "S1_L3_103", "S1_L3_104", "S1_L3_105",
                "S1_L4_001", "S1_L4_002", "S1_L4_003", "S1_L4_004",
                "S1_L4_101", "S1_L4_102", "S1_L4_103", "S1_L4_104",
                "S1_L1_001", "S1_L2_004", "S1_L4_202",
            }
        },
        new(
            "order_schedule",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S1", "S2", "S6" },
            "work_order",
            "/aidop/s2/work-order-scheduling",
            """
            SELECT COUNT(*) FROM dwd_order_schedule_trans
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
            """,
            """
            SELECT work_order AS ObjectCode,
                   CONCAT(IFNULL(sales_order_no,''),' / ',IFNULL(work_order,''),' / ',IFNULL(item_code,'')) AS Title,
                   GREATEST(IFNULL(qty_ordered,0)-IFNULL(qty_completed,0),0) AS Contribution,
                   CASE
                     WHEN IFNULL(schedule_satisfaction_flag,1)=0 OR (due_date IS NOT NULL AND due_date<CURDATE()) THEN 'red'
                     WHEN IFNULL(wip_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(due_date, stat_date) AS OccurredAt
            FROM dwd_order_schedule_trans
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "sales_order_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("productionLine", "prod_line"),
            }),
        new(
            "supplier_delivery",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3", "S4" },
            "purchase_order",
            "/aidop/s4/purchase-execution",
            """
            SELECT COUNT(*) FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
            """,
            """
            SELECT po_no AS ObjectCode,
                   CONCAT(IFNULL(supplier_name,supplier_code),' / ',IFNULL(item_code,''),' / ',IFNULL(po_no,'')) AS Title,
                   GREATEST(IFNULL(remaining_qty,0),0) AS Contribution,
                   CASE
                     WHEN UPPER(IFNULL(risk_level,'')) IN ('HIGH','RED')
                       OR UPPER(IFNULL(delivery_status,'')) IN ('OVERDUE','DELAYED','LATE') THEN 'red'
                     WHEN IFNULL(remaining_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(due_date, stat_date) AS OccurredAt
            FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            }),
        new(
            "material_readiness",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "work_order",
            "/aidop/s3/material-readiness",
            """
            SELECT COUNT(*) FROM dwd_material_readiness
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(shortage_qty,0)>0
            """,
            """
            SELECT work_order AS ObjectCode,
                   CONCAT('缺料 ',IFNULL(work_order,''),' / ',IFNULL(supplier_code,'')) AS Title,
                   IFNULL(shortage_qty,0) AS Contribution,
                   'red' AS Status,
                   stat_date AS OccurredAt
            FROM dwd_material_readiness
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(shortage_qty,0)>0
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
            }),
        new(
            "s6_report",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S6" },
            "work_order",
            "/aidop/s6/report-work",
            """
            SELECT COUNT(*) FROM mdp_std_s6_report
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(source_system,'')<>''
              AND IFNULL(work_order_no,'')<>''
              AND report_qty IS NOT NULL
            """,
            """
            SELECT work_order_no AS ObjectCode,
                   CONCAT('报工 ',IFNULL(work_order_no,'')) AS Title,
                   IFNULL(report_qty,0) AS Contribution,
                   'yellow' AS Status,
                   report_date AS OccurredAt
            FROM mdp_std_s6_report
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(source_system,'')<>''
              AND IFNULL(work_order_no,'')<>''
              AND report_qty IS NOT NULL
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order_no"),
            }),
        new(
            "iqc_bill",
            SmartDiagnosisEvidenceDomain.Quality,
            new[] { "S5" },
            "inspection_bill",
            "/aidop/s5/iqc",
            """
            SELECT COUNT(*) FROM qms_qcp_inspbill
            WHERE tenant_id=@tenantId AND IFNULL(FBILLNO,'')<>''
            """,
            """
            SELECT FBILLNO AS ObjectCode,
                   CONCAT('IQC ',IFNULL(FBILLNO,''),' / ',IFNULL(FBILLSTATUS,'')) AS Title,
                   1 AS Contribution,
                   CASE WHEN UPPER(IFNULL(FBILLSTATUS,'')) IN ('NG','REJECT','UNQUALIFIED') THEN 'red' ELSE 'yellow' END AS Status,
                   NULL AS OccurredAt
            FROM qms_qcp_inspbill
            WHERE tenant_id=@tenantId AND IFNULL(FBILLNO,'')<>''
            ORDER BY id DESC
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>()),
        new(
            "fqc_result",
            SmartDiagnosisEvidenceDomain.Quality,
            new[] { "S7" },
            "inspection_bill",
            "/aidop/s7/fqc",
            """
            SELECT COUNT(*) FROM mdp_std_fqc_result
            WHERE tenant_id=@tenantId AND IFNULL(bill_no,'')<>''
            """,
            """
            SELECT bill_no AS ObjectCode,
                   CONCAT('FQC ',IFNULL(bill_no,''),' / ',IFNULL(material_code,'')) AS Title,
                   GREATEST(IFNULL(unqualified_qty,0),0) AS Contribution,
                   CASE WHEN IFNULL(unqualified_qty,0)>0 OR UPPER(IFNULL(judgment,'')) IN ('NG','FAIL','UNQUALIFIED') THEN 'red' ELSE 'green' END AS Status,
                   inspect_time AS OccurredAt
            FROM mdp_std_fqc_result
            WHERE tenant_id=@tenantId AND IFNULL(bill_no,'')<>''
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("material", "material_code"),
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "sap_work_order_no"),
            }),
        new(
            "s2_order_schedule_metric",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S2" },
            "work_order",
            "/aidop/s2/production-scheduling/work-order-scheduling",
            """
            SELECT COUNT(*) FROM dwd_order_schedule_trans
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S2_L1_001','S2_L1_002','S2_L1_003','S2_L1_004','S2_L2_001','S2_L2_002','S2_L2_003')
              AND (
                (@metricCode IN ('S2_L1_001','S2_L2_001') AND schedule_cycle_days IS NOT NULL)
                OR (@metricCode IN ('S2_L1_002','S2_L2_002') AND (IFNULL(schedule_satisfaction_flag,1)=0 OR (due_date IS NOT NULL AND due_date<CURDATE())))
                OR (@metricCode IN ('S2_L1_003','S2_L2_003'))
                OR (@metricCode='S2_L1_004' AND IFNULL(wip_qty,0)>0)
              )
            """,
            """
            SELECT work_order AS ObjectCode,
                   CONCAT(IFNULL(sales_order_no,''),' / ',IFNULL(work_order,''),' / ',IFNULL(item_code,'')) AS Title,
                   CASE
                     WHEN @metricCode IN ('S2_L1_001','S2_L2_001') THEN IFNULL(schedule_cycle_days,0)
                     WHEN @metricCode='S2_L1_004' THEN GREATEST(IFNULL(wip_qty,0),0)
                     WHEN @metricCode IN ('S2_L1_003','S2_L2_003') THEN 1
                     ELSE GREATEST(IFNULL(qty_ordered,0)-IFNULL(qty_completed,0),0)
                   END AS Contribution,
                   CASE
                     WHEN IFNULL(schedule_satisfaction_flag,1)=0 OR (due_date IS NOT NULL AND due_date<CURDATE()) THEN 'red'
                     WHEN IFNULL(wip_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(due_date, stat_date) AS OccurredAt
            FROM dwd_order_schedule_trans
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S2_L1_001','S2_L1_002','S2_L1_003','S2_L1_004','S2_L2_001','S2_L2_002','S2_L2_003')
              AND (
                (@metricCode IN ('S2_L1_001','S2_L2_001') AND schedule_cycle_days IS NOT NULL)
                OR (@metricCode IN ('S2_L1_002','S2_L2_002') AND (IFNULL(schedule_satisfaction_flag,1)=0 OR (due_date IS NOT NULL AND due_date<CURDATE())))
                OR (@metricCode IN ('S2_L1_003','S2_L2_003'))
                OR (@metricCode='S2_L1_004' AND IFNULL(wip_qty,0)>0)
              )
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "sales_order_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("productionLine", "prod_line"),
            })
        {
            ContributionUnit = "贡献",
            DateColumn = "stat_date",
            DrillQueryKey = "workOrder",
            MetricCodes = new[] { "S2_L1_001", "S2_L1_002", "S2_L1_003", "S2_L1_004", "S2_L2_001", "S2_L2_002", "S2_L2_003" }
        },
        new(
            "s2_operation_schedule_metric",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S2" },
            "operation",
            "/aidop/s2/operation-plan/executable-daily-plan",
            """
            SELECT COUNT(*) FROM mdp_std_operation_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S2_L3_001','S2_L3_002','S2_L3_003','S2_L3_004','S2_L3_005','S2_L3_006')
              AND (
                @metricCode IN ('S2_L3_001','S2_L3_002','S2_L3_003')
                OR IFNULL(COALESCE(NULLIF(work_center,''), NULLIF(line_code,'')), '')<>''
              )
            """,
            """
            SELECT CONCAT(IFNULL(work_order,''),':',IFNULL(op_no,'')) AS ObjectCode,
                   CONCAT(IFNULL(work_order,''),' / 工序 ',IFNULL(op_no,''),' / ',IFNULL(COALESCE(NULLIF(work_center,''), line_code),'')) AS Title,
                   CASE
                     WHEN @metricCode IN ('S2_L3_001','S2_L3_004') THEN COALESCE(
                       TIMESTAMPDIFF(HOUR, start_time, end_time) / 24,
                       ABS(TIMESTAMPDIFF(DAY, plan_date, prod_date)),
                       0)
                     ELSE 1
                   END AS Contribution,
                   CASE
                     WHEN plan_date IS NOT NULL AND prod_date IS NOT NULL AND prod_date < plan_date THEN 'red'
                     WHEN prod_date IS NULL THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(prod_date, plan_date, sync_time) AS OccurredAt
            FROM mdp_std_operation_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S2_L3_001','S2_L3_002','S2_L3_003','S2_L3_004','S2_L3_005','S2_L3_006')
              AND (
                @metricCode IN ('S2_L3_001','S2_L3_002','S2_L3_003')
                OR IFNULL(COALESCE(NULLIF(work_center,''), NULLIF(line_code,'')), '')<>''
              )
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("productionLine", "line_code"),
            })
        {
            ContributionUnit = "贡献",
            DateColumn = "plan_date",
            DrillQueryKey = "workOrder",
            MetricCodes = new[] { "S2_L3_001", "S2_L3_002", "S2_L3_003", "S2_L3_004", "S2_L3_005", "S2_L3_006" }
        },
        new(
            "s3_supplier_delivery_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "purchase_order",
            "/aidop/s3/procurement/purchase-order",
            """
            SELECT COUNT(*) FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S3_L1_002','S3_L2_005','S3_L3_005')
              AND (IFNULL(remaining_qty,0)>0 OR UPPER(IFNULL(delivery_status,'')) IN ('OVERDUE','DELAYED','LATE'))
            """,
            """
            SELECT po_no AS ObjectCode,
                   CONCAT(IFNULL(supplier_name,supplier_code),' / ',IFNULL(item_code,''),' / ',IFNULL(po_no,'')) AS Title,
                   GREATEST(IFNULL(remaining_qty,0),0) AS Contribution,
                   CASE
                     WHEN UPPER(IFNULL(risk_level,'')) IN ('HIGH','RED')
                       OR UPPER(IFNULL(delivery_status,'')) IN ('OVERDUE','DELAYED','LATE') THEN 'red'
                     WHEN IFNULL(remaining_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(due_date, stat_date) AS OccurredAt
            FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S3_L1_002','S3_L2_005','S3_L3_005')
              AND (IFNULL(remaining_qty,0)>0 OR UPPER(IFNULL(delivery_status,'')) IN ('OVERDUE','DELAYED','LATE'))
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "stat_date",
            DrillQueryKey = "poNo",
            MetricCodes = new[] { "S3_L1_002", "S3_L2_005", "S3_L3_005" }
        },
        new(
            "s3_material_readiness_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "work_order",
            "/aidop/s3/supply-kanban/work-order-material-readiness",
            """
            SELECT COUNT(*) FROM dwd_material_readiness
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S3_L3_003','S3_L3_006')
              AND (
                (@metricCode='S3_L3_003' AND UPPER(IFNULL(ready_status,'')) NOT IN ('READY','SUFFICIENT','OK'))
                OR (@metricCode='S3_L3_006' AND IFNULL(shortage_qty,0)>0)
              )
            """,
            """
            SELECT work_order AS ObjectCode,
                   CONCAT(IFNULL(work_order,''),' / ',IFNULL(component_item_code,parent_item_code),' / 缺料 ',IFNULL(shortage_qty,0)) AS Title,
                   GREATEST(IFNULL(shortage_qty,0),1) AS Contribution,
                   'red' AS Status,
                   COALESCE(need_date, stat_date) AS OccurredAt
            FROM dwd_material_readiness
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order,'')<>''
              AND @metricCode IN ('S3_L3_003','S3_L3_006')
              AND (
                (@metricCode='S3_L3_003' AND UPPER(IFNULL(ready_status,'')) NOT IN ('READY','SUFFICIENT','OK'))
                OR (@metricCode='S3_L3_006' AND IFNULL(shortage_qty,0)>0)
              )
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("material", "component_item_code"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "stat_date",
            DrillQueryKey = "workOrder",
            MetricCodes = new[] { "S3_L3_003", "S3_L3_006" }
        },
        new(
            "s3_purchase_request_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "purchase_request",
            "/aidop/s3/procurement/purchase-request",
            """
            SELECT COUNT(*) FROM mdp_std_purchase_request
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(pr_no,'')<>''
              AND @metricCode='S3_L3_001'
            """,
            """
            SELECT pr_no AS ObjectCode,
                   CONCAT('PR ',IFNULL(pr_no,''),' / ',IFNULL(item_code,'')) AS Title,
                   GREATEST(IFNULL(request_qty,0),0) AS Contribution,
                   CASE WHEN send_date IS NULL THEN 'yellow' ELSE 'green' END AS Status,
                   COALESCE(request_date, sync_time) AS OccurredAt
            FROM mdp_std_purchase_request
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(pr_no,'')<>''
              AND @metricCode='S3_L3_001'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "request_date",
            DrillQueryKey = "prNo",
            MetricCodes = new[] { "S3_L3_001" }
        },
        new(
            "s3_purchase_order_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "purchase_order",
            "/aidop/s3/procurement/purchase-order",
            """
            SELECT COUNT(*) FROM mdp_std_purchase_order
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode='S3_L3_002'
            """,
            """
            SELECT po_no AS ObjectCode,
                   CONCAT('PO ',IFNULL(po_no,''),' / ',IFNULL(item_code,'')) AS Title,
                   GREATEST(IFNULL(order_qty,0),0) AS Contribution,
                   CASE WHEN due_date IS NOT NULL AND due_date<CURDATE() AND IFNULL(received_qty,0)<IFNULL(order_qty,0) THEN 'red' ELSE 'yellow' END AS Status,
                   COALESCE(order_date, due_date, sync_time) AS OccurredAt
            FROM mdp_std_purchase_order
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode='S3_L3_002'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "order_date",
            DrillQueryKey = "poNo",
            MetricCodes = new[] { "S3_L3_002" }
        },
        new(
            "s3_delivery_schedule_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S3" },
            "delivery_schedule",
            "/aidop/s3/material-plan/delivery-schedule",
            """
            SELECT COUNT(*) FROM mdp_std_delivery_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(delivery_plan_no,'')<>''
              AND @metricCode IN ('S3_L1_001','S3_L2_004','S3_L3_004')
            """,
            """
            SELECT delivery_plan_no AS ObjectCode,
                   CONCAT(IFNULL(delivery_plan_no,''),' / ',IFNULL(po_no,''),' / ',IFNULL(item_code,'')) AS Title,
                   CASE
                     WHEN @metricCode='S3_L3_004' THEN GREATEST(IFNULL(rest_qty,0),1)
                     ELSE GREATEST(IFNULL(DATEDIFF(IFNULL(last_sent_date,need_date), request_date),0),0)
                   END AS Contribution,
                   CASE
                     WHEN IFNULL(rest_qty,0)>0 AND need_date IS NOT NULL AND need_date<CURDATE() THEN 'red'
                     WHEN IFNULL(rest_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(need_date, request_date, sync_time) AS OccurredAt
            FROM mdp_std_delivery_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(delivery_plan_no,'')<>''
              AND @metricCode IN ('S3_L1_001','S3_L2_004','S3_L3_004')
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
            })
        {
            ContributionUnit = "贡献",
            DateColumn = "need_date",
            DrillQueryKey = "planNo",
            MetricCodes = new[] { "S3_L1_001", "S3_L2_004", "S3_L3_004" }
        },
        new(
            "s4_supplier_delivery_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S4" },
            "purchase_order",
            "/aidop/s4/delivery/supplier-delivery-management",
            """
            SELECT COUNT(*) FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S4_L1_002','S4_L2_002','S4_L3_002','S4_L1_004')
              AND IFNULL(remaining_qty,0)>0
            """,
            """
            SELECT po_no AS ObjectCode,
                   CONCAT(IFNULL(supplier_name,supplier_code),' / ',IFNULL(item_code,''),' / ',IFNULL(po_no,'')) AS Title,
                   GREATEST(IFNULL(remaining_qty,0),0) AS Contribution,
                   CASE
                     WHEN UPPER(IFNULL(risk_level,'')) IN ('HIGH','RED')
                       OR UPPER(IFNULL(delivery_status,'')) IN ('OVERDUE','DELAYED','LATE') THEN 'red'
                     ELSE 'yellow'
                   END AS Status,
                   COALESCE(due_date, stat_date) AS OccurredAt
            FROM dwd_supplier_delivery
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S4_L1_002','S4_L2_002','S4_L3_002','S4_L1_004')
              AND IFNULL(remaining_qty,0)>0
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "stat_date",
            DrillQueryKey = "poNo",
            MetricCodes = new[] { "S4_L1_002", "S4_L2_002", "S4_L3_002", "S4_L1_004" }
        },
        new(
            "s4_delivery_cycle_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S4" },
            "delivery_schedule",
            "/aidop/s4/delivery/supplier-shipment",
            """
            SELECT COUNT(*) FROM mdp_std_delivery_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(delivery_plan_no,'')<>''
              AND @metricCode IN ('S4_L1_001','S4_L2_001','S4_L3_001')
            """,
            """
            SELECT delivery_plan_no AS ObjectCode,
                   CONCAT(IFNULL(delivery_plan_no,''),' / ',IFNULL(po_no,''),' / ',IFNULL(item_code,'')) AS Title,
                   GREATEST(IFNULL(DATEDIFF(IFNULL(last_sent_date,need_date), request_date),0),0) AS Contribution,
                   CASE
                     WHEN need_date IS NOT NULL AND last_sent_date IS NOT NULL AND last_sent_date>need_date THEN 'red'
                     WHEN last_sent_date IS NULL THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(last_sent_date, need_date, sync_time) AS OccurredAt
            FROM mdp_std_delivery_schedule
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(delivery_plan_no,'')<>''
              AND @metricCode IN ('S4_L1_001','S4_L2_001','S4_L3_001')
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
            })
        {
            ContributionUnit = "天",
            DateColumn = "need_date",
            DrillQueryKey = "planNo",
            MetricCodes = new[] { "S4_L1_001", "S4_L2_001", "S4_L3_001" }
        },
        new(
            "s4_purchase_execution_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S4" },
            "purchase_order",
            "/aidop/s4/execution-kanban/supplier-shortage-kanban",
            """
            SELECT COUNT(*) FROM dwd_s4_purchase_execution
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S4_L2_003','S4_L3_003','S4_L2_004','S4_L3_004')
            """,
            """
            SELECT po_no AS ObjectCode,
                   CONCAT(IFNULL(supplier_code,''),' / ',IFNULL(item_code,''),' / ',IFNULL(po_no,'')) AS Title,
                   CASE
                     WHEN @metricCode IN ('S4_L2_004','S4_L3_004') THEN GREATEST(IFNULL(shortage_qty,0), GREATEST(IFNULL(order_qty,0)-IFNULL(received_qty,0),0))
                     ELSE 1
                   END AS Contribution,
                   CASE
                     WHEN UPPER(IFNULL(risk_level,'')) IN ('HIGH','RED') THEN 'red'
                     WHEN IFNULL(shortage_qty,0)>0 THEN 'yellow'
                     ELSE 'green'
                   END AS Status,
                   COALESCE(due_date, actual_arrival_date, stat_date) AS OccurredAt
            FROM dwd_s4_purchase_execution
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(po_no,'')<>''
              AND @metricCode IN ('S4_L2_003','S4_L3_003','S4_L2_004','S4_L3_004')
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("poNo", "po_no"),
                new SmartDiagnosisEvidenceFilterColumn("supplier", "supplier_code"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            })
        {
            ContributionUnit = "贡献",
            DateColumn = "stat_date",
            DrillQueryKey = "poNo",
            MetricCodes = new[] { "S4_L2_003", "S4_L3_003", "S4_L2_004", "S4_L3_004" }
        },
        new(
            "s5_material_online_cycle_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S5" },
            "material",
            "/aidop/s5/inventory/inout-query",
            """
            SELECT COUNT(*) FROM dwd_material_online_cycle
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(item_code,'')<>''
              AND @metricCode='S5_L1_001'
              AND cycle_days IS NOT NULL
            """,
            """
            SELECT item_code AS ObjectCode,
                   CONCAT('物料上线周期 ',IFNULL(item_code,''),' / ',IFNULL(cycle_days,0),'天') AS Title,
                   IFNULL(cycle_days,0) AS Contribution,
                   CASE WHEN IFNULL(cycle_days,0)>7 THEN 'red' WHEN IFNULL(cycle_days,0)>3 THEN 'yellow' ELSE 'green' END AS Status,
                   biz_date AS OccurredAt
            FROM dwd_material_online_cycle
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(item_code,'')<>''
              AND @metricCode='S5_L1_001'
              AND cycle_days IS NOT NULL
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            })
        {
            ContributionUnit = "天",
            DateColumn = "biz_date",
            DrillQueryKey = "material",
            MetricCodes = new[] { "S5_L1_001" }
        },
        new(
            "s5_material_online_fulfillment_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S5" },
            "work_order",
            "/aidop/s5/warehouse/production-issue",
            """
            SELECT COUNT(*) FROM dwd_material_online_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order_no,'')<>''
              AND @metricCode='S5_L1_002'
            """,
            """
            SELECT work_order_no AS ObjectCode,
                   CONCAT('工单 ',IFNULL(work_order_no,''),' / 上线 ',IFNULL(before_kgdate_rows,0),'/',IFNULL(total_rows,0)) AS Title,
                   GREATEST(IFNULL(total_rows,0)-IFNULL(before_kgdate_rows,0),0) AS Contribution,
                   CASE WHEN IFNULL(fulfillment_rate,1)<1 THEN 'red' ELSE 'green' END AS Status,
                   biz_date AS OccurredAt
            FROM dwd_material_online_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(work_order_no,'')<>''
              AND @metricCode='S5_L1_002'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "work_order_no"),
            })
        {
            ContributionUnit = "行",
            DateColumn = "biz_date",
            DrillQueryKey = "workOrder",
            MetricCodes = new[] { "S5_L1_002" }
        },
        new(
            "s5_iqc_bill_metric",
            SmartDiagnosisEvidenceDomain.Quality,
            new[] { "S5" },
            "inspection_bill",
            "/aidop/s5/iqc/result-list",
            """
            SELECT COUNT(*) FROM qms_qcp_inspbill
            WHERE tenant_id=@tenantId AND IFNULL(FBILLNO,'')<>''
              AND @metricCode='S5_L2_004'
            """,
            """
            SELECT FBILLNO AS ObjectCode,
                   CONCAT('IQC ',IFNULL(FBILLNO,''),' / ',IFNULL(FBILLSTATUS,'')) AS Title,
                   1 AS Contribution,
                   CASE WHEN UPPER(IFNULL(FBILLSTATUS,'')) IN ('NG','REJECT','UNQUALIFIED') THEN 'red' ELSE 'yellow' END AS Status,
                   NULL AS OccurredAt
            FROM qms_qcp_inspbill
            WHERE tenant_id=@tenantId AND IFNULL(FBILLNO,'')<>''
              AND @metricCode='S5_L2_004'
            ORDER BY id DESC
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "单",
            DrillQueryKey = "billNo",
            MetricCodes = new[] { "S5_L2_004" }
        },
        new(
            "s6_work_order_fulfillment_metric",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S6" },
            "work_order",
            "/aidop/s6/production-record/order-list",
            """
            SELECT COUNT(*) FROM dwd_work_order_mfg_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode IN ('S6_L1_002','S6_L2_002')
            """,
            """
            SELECT COALESCE(NULLIF(task_no,''), order_no) AS ObjectCode,
                   CONCAT(IFNULL(order_no,''),' / ',IFNULL(task_no,''),' / ',IFNULL(item_code,'')) AS Title,
                   GREATEST(IFNULL(plan_qty,0)-IFNULL(done_qty_in_window,0),0) AS Contribution,
                   CASE WHEN IFNULL(fulfillment_rate,1)<1 THEN 'red' ELSE 'green' END AS Status,
                   biz_date AS OccurredAt
            FROM dwd_work_order_mfg_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode IN ('S6_L1_002','S6_L2_002')
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "order_no"),
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "task_no"),
                new SmartDiagnosisEvidenceFilterColumn("material", "item_code"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "biz_date",
            DrillQueryKey = "orderNo",
            MetricCodes = new[] { "S6_L1_002", "S6_L2_002" }
        },
        new(
            "s7_shipment_cycle_metric",
            SmartDiagnosisEvidenceDomain.Delivery,
            new[] { "S7" },
            "sales_order",
            "/aidop/s7/finished-outbound/sales-shipment-notice",
            """
            SELECT COUNT(*) FROM dwd_order_shipment_cycle
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode='S7_L1_001'
              AND cycle_days IS NOT NULL
            """,
            """
            SELECT order_no AS ObjectCode,
                   CONCAT('发货周期 ',IFNULL(order_no,''),' / ',IFNULL(cycle_days,0),'天') AS Title,
                   IFNULL(cycle_days,0) AS Contribution,
                   CASE WHEN IFNULL(cycle_days,0)>7 THEN 'red' WHEN IFNULL(cycle_days,0)>3 THEN 'yellow' ELSE 'green' END AS Status,
                   biz_date AS OccurredAt
            FROM dwd_order_shipment_cycle
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode='S7_L1_001'
              AND cycle_days IS NOT NULL
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "order_no"),
            })
        {
            ContributionUnit = "天",
            DateColumn = "biz_date",
            DrillQueryKey = "orderNo",
            MetricCodes = new[] { "S7_L1_001" }
        },
        new(
            "s7_shipment_fulfillment_metric",
            SmartDiagnosisEvidenceDomain.Delivery,
            new[] { "S7" },
            "sales_order",
            "/aidop/s7/finished-outbound/sales-shipment-notice",
            """
            SELECT COUNT(*) FROM dwd_order_shipment_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode='S7_L1_002'
            """,
            """
            SELECT order_no AS ObjectCode,
                   CONCAT('发货满足 ',IFNULL(order_no,''),' / ',IFNULL(in_window_rows,0),'/',IFNULL(total_rows,0)) AS Title,
                   GREATEST(IFNULL(total_rows,0)-IFNULL(in_window_rows,0),0) AS Contribution,
                   CASE WHEN IFNULL(fulfillment_rate,100)<100 THEN 'red' ELSE 'green' END AS Status,
                   biz_date AS OccurredAt
            FROM dwd_order_shipment_fulfillment
            WHERE tenant_id=@tenantId
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
              AND IFNULL(order_no,'')<>''
              AND @metricCode='S7_L1_002'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "order_no"),
            })
        {
            ContributionUnit = "行",
            DateColumn = "biz_date",
            DrillQueryKey = "orderNo",
            MetricCodes = new[] { "S7_L1_002" }
        },
        new(
            "s7_fqc_result_metric",
            SmartDiagnosisEvidenceDomain.Quality,
            new[] { "S7" },
            "inspection_bill",
            "/aidop/s7/fqc/result-list",
            """
            SELECT COUNT(*) FROM mdp_std_fqc_result
            WHERE tenant_id=@tenantId AND IFNULL(bill_no,'')<>''
              AND @metricCode='S7_L2_002'
            """,
            """
            SELECT bill_no AS ObjectCode,
                   CONCAT('FQC ',IFNULL(bill_no,''),' / ',IFNULL(material_code,'')) AS Title,
                   GREATEST(IFNULL(unqualified_qty,0),0) AS Contribution,
                   CASE WHEN IFNULL(unqualified_qty,0)>0 OR UPPER(IFNULL(judgment,'')) IN ('NG','FAIL','UNQUALIFIED') THEN 'red' ELSE 'green' END AS Status,
                   inspect_time AS OccurredAt
            FROM mdp_std_fqc_result
            WHERE tenant_id=@tenantId AND IFNULL(bill_no,'')<>''
              AND @metricCode='S7_L2_002'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("material", "material_code"),
                new SmartDiagnosisEvidenceFilterColumn("workOrder", "sap_work_order_no"),
            })
        {
            ContributionUnit = "件",
            DateColumn = "inspect_time",
            DrillQueryKey = "billNo",
            MetricCodes = new[] { "S7_L2_002" }
        },
        new(
            "s9_quality_return_txn_metric",
            SmartDiagnosisEvidenceDomain.Quality,
            new[] { "S9" },
            "sales_return",
            "/aidop/s7/finished-outbound/sales-shipment-notice",
            """
            SELECT COUNT(*)
            FROM mdp_std_inv_trans
            WHERE tenant_id=@tenantId
              AND summary_flag=0 AND void_flag=0 AND approved_flag=1
              AND biz_doc_type='SALES_RETURN'
              AND @metricCode='S9_L1_001'
            """,
            """
            SELECT COALESCE(NULLIF(ref_task_no,''), item_num, src_rec_id) AS ObjectCode,
                   CONCAT('退货 ',IFNULL(ref_task_no,src_rec_id),' / ',IFNULL(item_num,'')) AS Title,
                   ABS(IFNULL(qty_change,0)) AS Contribution,
                   'red' AS Status,
                   approved_time AS OccurredAt
            FROM mdp_std_inv_trans
            WHERE tenant_id=@tenantId
              AND summary_flag=0 AND void_flag=0 AND approved_flag=1
              AND biz_doc_type='SALES_RETURN'
              AND @metricCode='S9_L1_001'
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "件",
            DateColumn = "approved_time",
            DrillQueryKey = "orderNo",
            MetricCodes = new[] { "S9_L1_001" }
        },
        new(
            "s9_order_delivery_cycle_metric",
            SmartDiagnosisEvidenceDomain.Delivery,
            new[] { "S9" },
            "segment_cycle",
            "/aidop/smart-ops/grid",
            """
            SELECT COUNT(*)
            FROM ado_s9_kpi_value_l1_day
            WHERE tenant_id=@tenantId AND is_deleted=0 AND metric_value IS NOT NULL
              AND metric_code IN ('S1_L1_001','S2_L1_001','S3_L1_001','S5_L1_001','S6_L1_001','S7_L1_001')
              AND @metricCode='S9_L1_002'
            """,
            """
            SELECT metric_code AS ObjectCode,
                   metric_code AS Title,
                   metric_value AS Contribution,
                   'blue' AS Status,
                   biz_date AS OccurredAt
            FROM ado_s9_kpi_value_l1_day
            WHERE tenant_id=@tenantId AND is_deleted=0 AND metric_value IS NOT NULL
              AND metric_code IN ('S1_L1_001','S2_L1_001','S3_L1_001','S5_L1_001','S6_L1_001','S7_L1_001')
              AND @metricCode='S9_L1_002'
            ORDER BY metric_code
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "天",
            DateColumn = "biz_date",
            DrillQueryKey = "metricCode",
            MetricCodes = new[] { "S9_L1_002" }
        },
        new(
            "s9_order_delivery_fulfillment_metric",
            SmartDiagnosisEvidenceDomain.Delivery,
            new[] { "S9" },
            "sales_order_line",
            "/aidop/smart-ops/grid",
            """
            SELECT COUNT(*)
            FROM mdp_std_so
            WHERE tenant_id=@tenantId AND deleted_flag=0
              AND IFNULL(order_qty,0) > 0
              AND IFNULL(delivered_qty,0) >= order_qty
              AND @metricCode='S9_L1_003'
            """,
            """
            SELECT CONCAT(order_no, ':', IFNULL(order_line,'')) AS ObjectCode,
                   CONCAT(order_no, ' / ', IFNULL(item_code,'')) AS Title,
                   delivered_qty AS Contribution,
                   'blue' AS Status,
                   order_date AS OccurredAt
            FROM mdp_std_so
            WHERE tenant_id=@tenantId AND deleted_flag=0
              AND IFNULL(order_qty,0) > 0
              AND IFNULL(delivered_qty,0) >= order_qty
              AND @metricCode='S9_L1_003'
            ORDER BY order_date DESC
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "件",
            DateColumn = "order_date",
            DrillQueryKey = "orderNo",
            MetricCodes = new[] { "S9_L1_003" }
        },
        new(
            "s9_production_efficiency_metric",
            SmartDiagnosisEvidenceDomain.Manufacturing,
            new[] { "S9" },
            "line_status",
            "/aidop/smart-ops/grid",
            """
            SELECT COUNT(*)
            FROM mdp_stg_line_status
            WHERE tenant_id=@tenantId
              AND (JSON_EXTRACT(raw_data,'$.RestTime') IS NOT NULL
                   OR JSON_EXTRACT(raw_data,'$.ProdDownTime') IS NOT NULL)
              AND @metricCode='S9_L1_004'
            """,
            """
            SELECT source_row_id AS ObjectCode,
                   source_biz_key AS Title,
                   CAST(JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ProdTime')) AS DECIMAL(18,4)) AS Contribution,
                   'blue' AS Status,
                   sync_time AS OccurredAt
            FROM mdp_stg_line_status
            WHERE tenant_id=@tenantId
              AND (JSON_EXTRACT(raw_data,'$.RestTime') IS NOT NULL
                   OR JSON_EXTRACT(raw_data,'$.ProdDownTime') IS NOT NULL)
              AND @metricCode='S9_L1_004'
            ORDER BY sync_time DESC
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "小时",
            DateColumn = "sync_time",
            DrillQueryKey = "sourceRowId",
            MetricCodes = new[] { "S9_L1_004" }
        },
        new(
            "s9_inventory_turnover_metric",
            SmartDiagnosisEvidenceDomain.Supply,
            new[] { "S9" },
            "turnover_segment",
            "/aidop/smart-ops/grid",
            """
            SELECT COUNT(*)
            FROM ado_s9_kpi_value_l1_day
            WHERE tenant_id=@tenantId AND is_deleted=0 AND metric_value IS NOT NULL
              AND metric_code IN ('S1_L1_004','S2_L1_004','S3_L1_004')
              AND @metricCode='S9_L1_005'
            """,
            """
            SELECT metric_code AS ObjectCode,
                   metric_code AS Title,
                   metric_value AS Contribution,
                   'blue' AS Status,
                   biz_date AS OccurredAt
            FROM ado_s9_kpi_value_l1_day
            WHERE tenant_id=@tenantId AND is_deleted=0 AND metric_value IS NOT NULL
              AND metric_code IN ('S1_L1_004','S2_L1_004','S3_L1_004')
              AND @metricCode='S9_L1_005'
            ORDER BY metric_code
            LIMIT @limit
            """,
            Array.Empty<SmartDiagnosisEvidenceFilterColumn>())
        {
            ContributionUnit = "天",
            DateColumn = "biz_date",
            DrillQueryKey = "metricCode",
            MetricCodes = new[] { "S9_L1_005" }
        },
        new(
            "s8_exception",
            SmartDiagnosisEvidenceDomain.Exception,
            new[] { "S8" },
            "s8_exception",
            "/aidop/s8/exceptions",
            """
            SELECT COUNT(*) FROM ado_s8_exception
            WHERE tenant_id=@tenantId AND IFNULL(is_deleted,0)=0
              AND IFNULL(exception_code,'')<>''
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId
                   OR (@factoryId IN (0,1) AND factory_id<>@tenantId))
            """,
            """
            SELECT exception_code AS ObjectCode,
                   CONCAT(IFNULL(exception_code,''),' / ',IFNULL(title,'')) AS Title,
                   GREATEST(IFNULL(priority_score,0),0) AS Contribution,
                   CASE
                     WHEN UPPER(IFNULL(status,'')) IN ('CLOSED','CANCELLED','CANCELED') THEN 'green'
                     WHEN UPPER(IFNULL(severity,'')) IN ('SERIOUS','CRITICAL','HIGH') THEN 'red'
                     ELSE 'yellow'
                   END AS Status,
                   created_at AS OccurredAt
            FROM ado_s8_exception
            WHERE tenant_id=@tenantId AND IFNULL(is_deleted,0)=0
              AND IFNULL(exception_code,'')<>''
              AND (factory_id IS NULL OR factory_id=0 OR factory_id=1 OR factory_id=@factoryId
                   OR (@factoryId IN (0,1) AND factory_id<>@tenantId))
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "related_object_code"),
            }),
        Evidence("s3_planner_efficiency_metric", SmartDiagnosisEvidenceDomain.Supply, "S3", "purchase_request",
            "/aidop/s3/procurement/purchase-request", "mdp_std_purchase_request", "pr_no", "request_qty", "request_date",
            new[] { "S3_L1_003", "S3_L2_001", "S3_L2_002", "S3_L2_003", "S3_L2_006" }, "item_code"),
        Evidence("s3_material_turnover_metric", SmartDiagnosisEvidenceDomain.Supply, "S3", "item",
            "/aidop/s5/inventory/inout-query", "mdp_std_inv_trans", "item_num", "qty_change", "approved_time",
            new[] { "S3_L1_004" }, "item_num"),
        Evidence("s4_purchase_efficiency_metric", SmartDiagnosisEvidenceDomain.Supply, "S4", "purchase_order",
            "/aidop/s3/procurement/purchase-order", "mdp_std_purchase_order", "po_no", "order_qty", "sync_time",
            new[] { "S4_L1_003" }, "item_code"),
        Evidence("s5_warehouse_efficiency_metric", SmartDiagnosisEvidenceDomain.Supply, "S5", "item",
            "/aidop/s5/warehouse/production-issue", "mdp_std_inv_trans", "item_num", "qty_change", "approved_time",
            new[] { "S5_L1_003", "S5_L2_003", "S5_L2_006", "S5_L3_001", "S5_L3_003" }, "item_num"),
        Evidence("s5_balance_turnover_metric", SmartDiagnosisEvidenceDomain.Supply, "S5", "item",
            "/aidop/s5/warehouse-kanban/turnover-report", "mdp_std_inventory_balance_monthly", "item_code", "avg_balance_amount", "period_ym",
            new[] { "S5_L1_004" }, "item_code"),
        Evidence("s6_manufacture_cycle_metric", SmartDiagnosisEvidenceDomain.Manufacturing, "S6", "work_order",
            "/aidop/s6/production-record/order-list", "mdp_std_work_order_line", "task_no", "qty_planned", "release_time",
            new[] { "S6_L1_001", "S6_L2_004" }, "item_code"),
        Evidence("s6_report_metric", SmartDiagnosisEvidenceDomain.Manufacturing, "S6", "work_order",
            "/aidop/s6/production-record/order-list", "mdp_std_s6_report", "work_order_no", "report_qty", "report_date",
            new[] { "S6_L1_003", "S6_L2_003", "S6_L3_007" }, "work_order_no"),
        Evidence("s7_fqc_metric", SmartDiagnosisEvidenceDomain.Quality, "S7", "fqc_task",
            "/aidop/s7/fqc/task-list", "mdp_std_fqc_task", "bill_no", "qty", "inspect_start_time",
            new[] { "S7_L2_001", "S7_L2_003" }, "material_code"),
        Evidence("s7_ship_recon_metric", SmartDiagnosisEvidenceDomain.Delivery, "S7", "sales_order",
            "/aidop/s7/finished-outbound/sales-shipment-notice", "mdp_ship_recon_diff", "ord_nbr", "diff_qty", "biz_date",
            new[] { "S7_L1_003", "S7_L2_004" }, "ord_nbr"),
    };

    private static SmartDiagnosisEvidenceSource Evidence(
        string key, SmartDiagnosisEvidenceDomain domain, string module, string objectType, string drillPath,
        string table, string objectColumn, string qtyColumn, string dateColumn,
        string[] metricCodes, string filterColumn)
    {
        var list = string.Join(",", metricCodes.Select(c => $"'{c}'"));
        var where = $"""
            WHERE tenant_id=@tenantId
              AND @metricCode IN ({list})
            """;
        return new SmartDiagnosisEvidenceSource(
            key, domain, new[] { module }, objectType, drillPath,
            $"SELECT COUNT(*) FROM {table}\n{where}",
            $"""
            SELECT {objectColumn} AS ObjectCode,
                   {objectColumn} AS Title,
                   IFNULL({qtyColumn},0) AS Contribution,
                   'blue' AS Status,
                   {dateColumn} AS OccurredAt
            FROM {table}
            {where}
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[] { new SmartDiagnosisEvidenceFilterColumn("material", filterColumn) })
        {
            MetricCodes = metricCodes,
            DateColumn = dateColumn,
            DrillQueryKey = "material"
        };
    }

    public static IReadOnlyList<SmartDiagnosisEvidenceSource> All => Sources;

    public static IReadOnlyList<SmartDiagnosisEvidenceSource> Resolve(string moduleCode)
    {
        var mc = (moduleCode ?? "").Trim().ToUpperInvariant();
        if (mc == "S9")
            return Sources.Where(x => x.Key != "s8_exception" && x.MetricCodes.Count == 0).ToList();
        return Sources
            .Where(x => x.MetricCodes.Count == 0
                        && x.Modules.Contains(mc, StringComparer.OrdinalIgnoreCase))
            .ToList();
    }

    public static IReadOnlyList<SmartDiagnosisEvidenceSource> ResolveMetric(
        string moduleCode,
        string metricCode)
    {
        var mc = (moduleCode ?? "").Trim().ToUpperInvariant();
        var metric = (metricCode ?? "").Trim().ToUpperInvariant();
        if (mc.Length == 0 || metric.Length == 0)
            return Array.Empty<SmartDiagnosisEvidenceSource>();
        return Sources
            .Where(x => x.Modules.Contains(mc, StringComparer.OrdinalIgnoreCase)
                        && x.MetricCodes.Contains(metric, StringComparer.OrdinalIgnoreCase))
            .ToList();
    }

    public static SmartDiagnosisEvidenceSource? Get(string key) =>
        Sources.FirstOrDefault(x => string.Equals(x.Key, key, StringComparison.OrdinalIgnoreCase));
}

public sealed class SmartDiagnosisEvidenceItem
{
    public string ObjectType { get; set; } = "";
    public string ObjectCode { get; set; } = "";
    public string Title { get; set; } = "";
    public decimal? Contribution { get; set; }
    public string ContributionUnit { get; set; } = "";
    public string Status { get; set; } = "green";
    public string? OccurredAt { get; set; }
    public string? DrillPath { get; set; }
}

public sealed class SmartDiagnosisEvidenceResult
{
    public string Scope { get; set; } = "no_data";
    public string? MetricCode { get; set; }
    public string MatchScope { get; set; } = "module";
    public string? AsOf { get; set; }
    public string Domain { get; set; } = "";
    public int Total { get; set; }
    public int Returned { get; set; }
    public bool Truncated { get; set; }
    public List<string> Sources { get; set; } = new();
    public List<SmartDiagnosisEvidenceItem> Items { get; set; } = new();
}

internal sealed class SmartDiagnosisEvidenceSqlRow
{
    public string? ObjectCode { get; set; }
    public string? Title { get; set; }
    public decimal? Contribution { get; set; }
    public string? Status { get; set; }
    public DateTime? OccurredAt { get; set; }
}