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

using Admin.NET.Plugin.AiDOP.Infrastructure;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

public sealed class SmartDiagnosisEvidenceService : ITransient
{
    private readonly ISqlSugarClient _db;

    public SmartDiagnosisEvidenceService(ISqlSugarClient db) => _db = db;

    public async Task<SmartDiagnosisEvidenceResult> QueryAsync(
        long tenantId,
        long factoryId,
        string moduleCode,
        SmartOpsDashboardFilter? filter = null,
        int limit = SmartDiagnosisEvidenceRegistry.DefaultLimit)
    {
        SmartOpsTrustedScopeRules.EnsureBusinessTenant(tenantId);
        var cap = Math.Clamp(limit, 1, SmartDiagnosisEvidenceRegistry.MaxLimit);
        var sources = SmartDiagnosisEvidenceRegistry.Resolve(moduleCode);
        var result = new SmartDiagnosisEvidenceResult
        {
            AsOf = DateTime.Today.ToString("yyyy-MM-dd"),
            Domain = string.Join(",", sources.Select(x => x.Domain.ToString()).Distinct()),
        };
        if (sources.Count == 0)
        {
            result.Scope = "unsupported";
            return result;
        }

        var items = new List<SmartDiagnosisEvidenceItem>();
        var total = 0;
        foreach (var source in sources)
        {
            var (sourceTotal, sourceItems) = await QuerySourceAsync(source, tenantId, factoryId, filter, cap);
            total += sourceTotal;
            result.Sources.Add(source.Key);
            items.AddRange(sourceItems);
        }

        result.Total = total;
        result.Items = items
            .OrderByDescending(x => Severity(x.Status))
            .ThenByDescending(x => x.Contribution ?? 0)
            .Take(cap)
            .ToList();
        result.Returned = result.Items.Count;
        result.Truncated = total > result.Returned;
        result.Scope = result.Returned == 0 ? "no_data" : "filtered_atomic";
        return result;
    }

    private async Task<(int Total, List<SmartDiagnosisEvidenceItem> Items)> QuerySourceAsync(
        SmartDiagnosisEvidenceSource source,
        long tenantId,
        long factoryId,
        SmartOpsDashboardFilter? filter,
        int limit)
    {
        var parameters = new List<SugarParameter>
        {
            new("@tenantId", tenantId),
            new("@factoryId", factoryId),
            new("@limit", limit),
        };
        var extraWhere = BuildFilterSql(source, filter, parameters);
        var countSql = InjectWhere(source.CountSql, extraWhere);
        var itemSql = InjectWhere(source.ItemSql, extraWhere);
        var total = await _db.Ado.GetIntAsync(countSql, parameters.ToArray());
        if (total <= 0)
            return (0, new List<SmartDiagnosisEvidenceItem>());

        var rows = await _db.Ado.SqlQueryAsync<SmartDiagnosisEvidenceSqlRow>(itemSql, parameters.ToArray());
        var items = rows.Select(row => new SmartDiagnosisEvidenceItem
        {
            ObjectType = source.ObjectType,
            ObjectCode = row.ObjectCode ?? "",
            Title = string.IsNullOrWhiteSpace(row.Title) ? row.ObjectCode ?? source.Key : row.Title,
            Contribution = row.Contribution,
            ContributionUnit = source.Domain == SmartDiagnosisEvidenceDomain.Quality ? "qty" : "qty",
            Status = string.IsNullOrWhiteSpace(row.Status) ? "green" : row.Status,
            OccurredAt = row.OccurredAt?.ToString("yyyy-MM-dd"),
            DrillPath = source.DrillPath,
        }).ToList();
        return (total, items);
    }

    private static string BuildFilterSql(
        SmartDiagnosisEvidenceSource source,
        SmartOpsDashboardFilter? filter,
        List<SugarParameter> parameters)
    {
        if (filter == null) return "";
        var values = filter.ToContextDictionary();
        var clauses = new List<string>();
        foreach (var map in source.Filters)
        {
            if (!values.TryGetValue(map.QueryKey, out var raw) || string.IsNullOrWhiteSpace(raw))
                continue;
            var param = "@f_" + map.QueryKey;
            clauses.Add($"{map.SqlColumn} = {param}");
            parameters.Add(new SugarParameter(param, raw.Trim()));
        }
        return clauses.Count == 0 ? "" : " AND " + string.Join(" AND ", clauses);
    }

    private static string InjectWhere(string sql, string extraWhere)
    {
        if (string.IsNullOrWhiteSpace(extraWhere)) return sql;
        var orderAt = sql.LastIndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);
        if (orderAt < 0)
            return sql + extraWhere;
        return sql[..orderAt] + extraWhere + " " + sql[orderAt..];
    }

    private static int Severity(string? status) => (status ?? "").ToLowerInvariant() switch
    {
        "red" => 3,
        "yellow" => 2,
        _ => 1
    };
}