AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Service/S8/S8HomeAlertQueryService.cs

using Admin.NET.Plugin.AiDOP.Infrastructure.S8;
using Admin.NET.Plugin.AiDOP.SmartOps;

namespace Admin.NET.Plugin.AiDOP.Service.S8;

/// <summary>
/// 九宫格 S8 的当前异常与模块灯态。异常权威是 ado_s8_exception，监控权威是 ado_s8_watch_rule。
/// </summary>
public sealed class S8HomeAlertQueryService : ITransient
{
    public const int RecentLimit = 6;
    public const int MinFreshWindowSeconds = 600;

    /// <summary>列表、汇总、趋势共用的业务维度过滤。只匹配异常权威表上的可追溯字段。</summary>
    public const string TextFilterSql = """
  AND (@product IS NULL OR IFNULL(title,'') LIKE CONCAT('%', @product, '%')
       OR IFNULL(description,'') LIKE CONCAT('%', @product, '%')
       OR IFNULL(related_object_code,'') LIKE CONCAT('%', @product, '%')
       OR IFNULL(source_object_id,'') LIKE CONCAT('%', @product, '%')
       OR IFNULL(source_payload,'') LIKE CONCAT('%', @product, '%')
       OR IFNULL(exception_code,'') LIKE CONCAT('%', @product, '%'))
  AND (@orderNo IS NULL OR IFNULL(title,'') LIKE CONCAT('%', @orderNo, '%')
       OR IFNULL(description,'') LIKE CONCAT('%', @orderNo, '%')
       OR IFNULL(related_object_code,'') LIKE CONCAT('%', @orderNo, '%')
       OR IFNULL(source_object_id,'') LIKE CONCAT('%', @orderNo, '%')
       OR IFNULL(source_payload,'') LIKE CONCAT('%', @orderNo, '%')
       OR IFNULL(exception_code,'') LIKE CONCAT('%', @orderNo, '%'))
  AND (@productionLine IS NULL OR IFNULL(title,'') LIKE CONCAT('%', @productionLine, '%')
       OR IFNULL(description,'') LIKE CONCAT('%', @productionLine, '%')
       OR IFNULL(related_object_code,'') LIKE CONCAT('%', @productionLine, '%')
       OR IFNULL(source_object_id,'') LIKE CONCAT('%', @productionLine, '%')
       OR IFNULL(source_payload,'') LIKE CONCAT('%', @productionLine, '%')
       OR IFNULL(exception_code,'') LIKE CONCAT('%', @productionLine, '%'))
""";

    private const string ActiveStatusSql = "status NOT IN ('CLOSED','REJECTED','RECOVERED')";

    private readonly ISqlSugarClient _db;

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

    public async Task<List<S8HomeAlertItem>> GetRecentAsync(long tenantId, SmartOpsDashboardFilter filter)
    {
        var sql = $"""
SELECT DATE_FORMAT(COALESCE(last_detected_at, created_at), '%H:%i') AS Time,
       IFNULL(NULLIF(stage_code,''), IFNULL(NULLIF(module_code,''), 'S8')) AS Module,
       IFNULL(NULLIF(title,''), IFNULL(NULLIF(description,''), '异常告警')) AS Message,
       severity AS Severity
FROM ado_s8_exception
WHERE tenant_id=@tenantId AND is_deleted=0 AND {ActiveStatusSql}
  AND (@ds IS NULL OR created_at >= @ds)
  AND (@de IS NULL OR created_at < DATE_ADD(@de, INTERVAL 1 DAY))
  {TextFilterSql}
ORDER BY COALESCE(last_detected_at, created_at) DESC, id DESC
LIMIT {RecentLimit}
""";
        var args = FilterArgs(tenantId, filter);
        args["ds"] = filter.DateStart;
        args["de"] = filter.DateEnd;
        var rows = await _db.Ado.SqlQueryAsync<S8HomeAlertRaw>(sql, args);
        return rows.Select(MapAlert).ToList();
    }

    public async Task<S8HomeAlertSummary> GetCreatedSummaryAsync(
        long tenantId, SmartOpsDashboardFilter filter, DateTime windowStart, DateTime windowEnd, bool isToday)
    {
        var sql = $"""
SELECT COUNT(*) AS Total,
       IFNULL(SUM(CASE WHEN UPPER(IFNULL(severity,'')) IN ('SERIOUS','HIGH','CRITICAL') THEN 1 ELSE 0 END), 0) AS Critical
FROM ado_s8_exception
WHERE tenant_id=@tenantId AND is_deleted=0
  AND created_at >= @ws AND created_at < @we
  {TextFilterSql}
""";
        var args = FilterArgs(tenantId, filter);
        args["ws"] = windowStart;
        args["we"] = windowEnd;
        var row = (await _db.Ado.SqlQueryAsync<S8HomeSummaryRaw>(sql, args)).FirstOrDefault();
        return new S8HomeAlertSummary(windowStart, windowEnd, isToday, row?.Total ?? 0, row?.Critical ?? 0);
    }

    public async Task<List<S8HomeTrendPoint>> GetCreatedTrendAsync(
        long tenantId, SmartOpsDashboardFilter filter, DateTime start, DateTime end)
    {
        var sql = $"""
SELECT DATE(created_at) AS BizDate, COUNT(*) AS Cnt
FROM ado_s8_exception
WHERE tenant_id=@tenantId AND is_deleted=0
  AND created_at >= @ds AND created_at < DATE_ADD(@de, INTERVAL 1 DAY)
  {TextFilterSql}
GROUP BY DATE(created_at)
ORDER BY BizDate
""";
        var args = FilterArgs(tenantId, filter);
        args["ds"] = start;
        args["de"] = end;
        var rows = await _db.Ado.SqlQueryAsync<S8HomeTrendRaw>(sql, args);
        var byDate = rows.ToDictionary(x => x.BizDate.Date, x => x.Cnt);
        var points = new List<S8HomeTrendPoint>();
        for (var day = start.Date; day <= end.Date; day = day.AddDays(1))
            points.Add(new S8HomeTrendPoint(day, byDate.GetValueOrDefault(day)));
        return points;
    }

    public async Task<S8ModuleLampPayload> GetModuleLampsAsync(long tenantId, DateTime now)
    {
        const string exceptionSql = """
SELECT stage_code AS StageCode, severity AS Severity
FROM ado_s8_exception
WHERE tenant_id=@tenantId AND is_deleted=0
  AND status NOT IN ('CLOSED','REJECTED','RECOVERED')
  AND stage_code IN ('S1','S2','S3','S4','S5','S6','S7')
""";
        const string ruleSql = """
SELECT stage_code AS StageCode, enabled AS Enabled, poll_interval_seconds AS PollIntervalSeconds,
       last_status AS LastStatus, last_run_at AS LastRunAt, paused_until AS PausedUntil
FROM ado_s8_watch_rule
WHERE tenant_id=@tenantId
  AND stage_code IN ('S1','S2','S3','S4','S5','S6','S7')
""";
        var exceptions = await _db.Ado.SqlQueryAsync<S8StageSeverityRaw>(exceptionSql, new { tenantId });
        var ruleRows = await _db.Ado.SqlQueryAsync<S8WatchRuleRaw>(ruleSql, new { tenantId });
        var rules = ruleRows.Select(x => new S8WatchRuleSignal(
            x.StageCode ?? "",
            x.Enabled,
            x.PollIntervalSeconds,
            x.LastStatus,
            x.LastRunAt,
            x.PausedUntil)).ToList();
        var items = S8ModuleCode.All.Select(code =>
        {
            var stageRows = exceptions.Where(x => string.Equals(x.StageCode, code, StringComparison.Ordinal)).ToList();
            var serious = stageRows.Count(x => S8SeverityCode.IsSerious(x.Severity));
            var follow = stageRows.Count - serious;
            var stageRules = rules.Where(x => string.Equals(x.StageCode, code, StringComparison.Ordinal)).ToList();
            var decision = S8ModuleLampPolicy.Decide(serious, follow, stageRules, now);
            return new S8ModuleLampItem(
                code,
                S8ModuleCode.Label(code),
                decision.BusinessState,
                decision.MonitorState,
                serious,
                follow,
                stageRows.Count,
                stageRules.Count,
                stageRules.Count(x => x.Enabled),
                stageRules.Count(x => S8ModuleLampPolicy.IsRunnable(x, now)),
                stageRules.Where(x => x.LastRunAt.HasValue).Select(x => x.LastRunAt).Max(),
                decision.MonitorReasonCode);
        }).ToList();
        return new S8ModuleLampPayload(now, items);
    }

    private static S8HomeAlertItem MapAlert(S8HomeAlertRaw row)
    {
        var serious = S8SeverityCode.IsSerious(row.Severity);
        return new S8HomeAlertItem(
            row.Time ?? "",
            string.IsNullOrWhiteSpace(row.Module) ? "S8" : row.Module,
            string.IsNullOrWhiteSpace(row.Message) ? "异常告警" : row.Message,
            serious ? "critical" : "medium",
            serious ? "严重" : "关注");
    }

    private static Dictionary<string, object?> FilterArgs(long tenantId, SmartOpsDashboardFilter filter) => new()
    {
        ["tenantId"] = tenantId,
        ["product"] = BlankToNull(filter.Product),
        ["orderNo"] = BlankToNull(filter.OrderNo),
        ["productionLine"] = BlankToNull(filter.ProductionLine)
    };

    private static string? BlankToNull(string? value) => string.IsNullOrWhiteSpace(value) ? null : value;
}

public static class S8ModuleLampPolicy
{
    public static int FreshWindowSeconds(int pollIntervalSeconds) =>
        Math.Max(pollIntervalSeconds * 2, S8HomeAlertQueryService.MinFreshWindowSeconds);

    public static bool IsRunnable(S8WatchRuleSignal rule, DateTime now) =>
        rule.Enabled && (!rule.PausedUntil.HasValue || rule.PausedUntil.Value <= now);

    public static S8ModuleLampDecision Decide(
        int seriousCount, int followCount, IReadOnlyList<S8WatchRuleSignal> rules, DateTime now)
    {
        var (monitorState, reason) = ResolveMonitor(rules, now);
        var business = seriousCount > 0 ? "CRITICAL"
            : followCount > 0 ? "WARNING"
            : monitorState == "HEALTHY" ? "NORMAL"
            : "UNKNOWN";
        return new S8ModuleLampDecision(business, monitorState, reason);
    }

    private static (string State, string Reason) ResolveMonitor(IReadOnlyList<S8WatchRuleSignal> rules, DateTime now)
    {
        if (rules.Count == 0)
            return ("UNMONITORED", "NO_RULE");
        var runnable = rules.Where(x => IsRunnable(x, now)).ToList();
        if (runnable.Count == 0)
            return ("PAUSED", rules.Any(x => x.Enabled) ? "RULE_PAUSED" : "RULE_DISABLED");

        var reasons = runnable.Select(x => RuleReason(x, now)).Where(x => x != "OK").ToList();
        if (reasons.Count == 0)
            return ("HEALTHY", "OK");
        if (reasons.Contains("RUN_FAILED"))
            return ("DEGRADED", "RUN_FAILED");
        if (reasons.Contains("NEVER_RUN"))
            return ("DEGRADED", "NEVER_RUN");
        return ("DEGRADED", "RUN_STALE");
    }

    private static string RuleReason(S8WatchRuleSignal rule, DateTime now)
    {
        if (!string.Equals(rule.LastStatus, "SUCCESS", StringComparison.OrdinalIgnoreCase))
            return rule.LastRunAt.HasValue ? "RUN_FAILED" : "NEVER_RUN";
        if (!rule.LastRunAt.HasValue)
            return "NEVER_RUN";
        var window = TimeSpan.FromSeconds(FreshWindowSeconds(rule.PollIntervalSeconds));
        return now - rule.LastRunAt.Value <= window ? "OK" : "RUN_STALE";
    }
}

public sealed record S8WatchRuleSignal(
    string StageCode,
    bool Enabled,
    int PollIntervalSeconds,
    string? LastStatus,
    DateTime? LastRunAt,
    DateTime? PausedUntil);

public sealed record S8ModuleLampDecision(string BusinessState, string MonitorState, string MonitorReasonCode);

public sealed record S8HomeAlertItem(string Time, string Module, string Message, string Level, string LevelLabel);

public sealed record S8HomeAlertSummary(DateTime WindowStart, DateTime WindowEnd, bool IsToday, int Total, int Critical);

public sealed record S8HomeTrendPoint(DateTime Date, int Count);

public sealed record S8ModuleLampItem(
    string Code,
    string Name,
    string BusinessState,
    string MonitorState,
    int SeriousCount,
    int FollowCount,
    int OpenCount,
    int ConfiguredRuleCount,
    int EnabledRuleCount,
    int RunnableRuleCount,
    DateTime? LatestRunAt,
    string MonitorReasonCode);

public sealed record S8ModuleLampPayload(DateTime AsOf, IReadOnlyList<S8ModuleLampItem> Items);

internal sealed class S8HomeAlertRaw
{
    public string? Time { get; set; }
    public string? Module { get; set; }
    public string? Message { get; set; }
    public string? Severity { get; set; }
}

internal sealed class S8HomeSummaryRaw
{
    public int Total { get; set; }
    public int Critical { get; set; }
}

internal sealed class S8HomeTrendRaw
{
    public DateTime BizDate { get; set; }
    public int Cnt { get; set; }
}

internal sealed class S8StageSeverityRaw
{
    public string? StageCode { get; set; }
    public string? Severity { get; set; }
}

internal sealed class S8WatchRuleRaw
{
    public string? StageCode { get; set; }
    public bool Enabled { get; set; }
    public int PollIntervalSeconds { get; set; }
    public string? LastStatus { get; set; }
    public DateTime? LastRunAt { get; set; }
    public DateTime? PausedUntil { get; set; }
}