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 static class SmartDiagnosisEvidenceRegistry
{
    public const int DefaultLimit = 20;
    public const int MaxLimit = 50;

    private static readonly SmartDiagnosisEvidenceSource[] Sources =
    {
        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(work_order_no,'')<>''
            """,
            """
            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(work_order_no,'')<>''
            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(
            "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)
              AND (factory_id IS NULL OR 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)
              AND (factory_id IS NULL OR factory_id<>@tenantId)
            ORDER BY Contribution DESC, OccurredAt
            LIMIT @limit
            """,
            new[]
            {
                new SmartDiagnosisEvidenceFilterColumn("orderNo", "related_object_code"),
            }),
    };

    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").ToList();
        return Sources.Where(x => x.Modules.Contains(mc, 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? 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; }
}