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

using System.Text.RegularExpressions;
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);
        return await QuerySourcesAsync(tenantId, factoryId, sources, filter, cap, null, "module");
    }

    public async Task<SmartDiagnosisEvidenceResult> QueryMetricAsync(
        long tenantId,
        long factoryId,
        string moduleCode,
        string metricCode,
        SmartOpsDashboardFilter? filter = null,
        int limit = SmartDiagnosisEvidenceRegistry.DefaultLimit)
    {
        SmartOpsTrustedScopeRules.EnsureBusinessTenant(tenantId);
        var cap = Math.Clamp(limit, 1, SmartDiagnosisEvidenceRegistry.MaxLimit);
        var normalizedMetric = (metricCode ?? "").Trim().ToUpperInvariant();
        var sources = SmartDiagnosisEvidenceRegistry.ResolveMetric(moduleCode, normalizedMetric);
        return await QuerySourcesAsync(
            tenantId,
            factoryId,
            sources,
            filter,
            cap,
            normalizedMetric,
            sources.Count == 0 ? "none" : "exact_metric");
    }

    private async Task<SmartDiagnosisEvidenceResult> QuerySourcesAsync(
        long tenantId,
        long factoryId,
        IReadOnlyList<SmartDiagnosisEvidenceSource> sources,
        SmartOpsDashboardFilter? filter,
        int cap,
        string? metricCode,
        string matchScope)
    {
        var result = new SmartDiagnosisEvidenceResult
        {
            AsOf = DateTime.Today.ToString("yyyy-MM-dd"),
            Domain = string.Join(",", sources.Select(x => x.Domain.ToString()).Distinct()),
            MetricCode = metricCode,
            MatchScope = matchScope,
        };
        if (sources.Count == 0)
        {
            result.Scope = metricCode == null ? "unsupported" : "unsupported_metric";
            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,
                metricCode);
            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";
        var latest = result.Items
            .Select(x => x.OccurredAt)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .OrderByDescending(x => x)
            .FirstOrDefault();
        if (!string.IsNullOrWhiteSpace(latest))
            result.AsOf = latest;
        return result;
    }

    private async Task<(int Total, List<SmartDiagnosisEvidenceItem> Items)> QuerySourceAsync(
        SmartDiagnosisEvidenceSource source,
        long tenantId,
        long factoryId,
        SmartOpsDashboardFilter? filter,
        int limit,
        string? metricCode)
    {
        var parameters = new List<SugarParameter>
        {
            new("@tenantId", tenantId),
            new("@factoryId", factoryId),
            new("@limit", limit),
        };
        if (!string.IsNullOrWhiteSpace(metricCode))
            parameters.Add(new SugarParameter("@metricCode", metricCode));
        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.ContributionUnit,
            Status = string.IsNullOrWhiteSpace(row.Status) ? "green" : row.Status,
            OccurredAt = row.OccurredAt?.ToString("yyyy-MM-dd"),
            DrillPath = BuildDrillPath(source, row.ObjectCode),
        }).ToList();
        return (total, items);
    }

    private static string? BuildDrillPath(SmartDiagnosisEvidenceSource source, string? objectCode)
    {
        if (string.IsNullOrWhiteSpace(source.DrillPath))
            return null;
        if (string.IsNullOrWhiteSpace(source.DrillQueryKey) || string.IsNullOrWhiteSpace(objectCode))
            return source.DrillPath;
        var sep = source.DrillPath.Contains('?') ? "&" : "?";
        return source.DrillPath + sep + source.DrillQueryKey + "=" + Uri.EscapeDataString(objectCode);
    }

    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()));
        }
        if (!string.IsNullOrWhiteSpace(source.DateColumn)
            && DateColumnPattern.IsMatch(source.DateColumn))
        {
            if (filter.DateStart.HasValue)
            {
                clauses.Add($"{source.DateColumn} >= @f_dateStart");
                parameters.Add(new SugarParameter("@f_dateStart", filter.DateStart.Value.Date));
            }
            if (filter.DateEnd.HasValue)
            {
                clauses.Add($"{source.DateColumn} <= @f_dateEnd");
                parameters.Add(new SugarParameter("@f_dateEnd", filter.DateEnd.Value.Date));
            }
        }
        return clauses.Count == 0 ? "" : " AND " + string.Join(" AND ", clauses);
    }

    private static readonly Regex DateColumnPattern = new(
        "^[a-zA-Z0-9_]+(\\.[a-zA-Z0-9_]+)?$",
        RegexOptions.Compiled | RegexOptions.CultureInvariant);

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