AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/ChatBI/ChatBIService.cs

using System.Text;
using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Infrastructure;

namespace Admin.NET.Plugin.AiDOP.ChatBI;

public sealed class ChatBIService : ITransient
{
    private static readonly string[] ValueTables =
    {
        "ado_s9_kpi_value_l1_day",
        "ado_s9_kpi_value_l2_day",
        "ado_s9_kpi_value_l3_day",
        "ado_s9_kpi_value_l4_day"
    };

    private readonly ISqlSugarClient _db;
    private readonly DeepSeekChatClient _deepSeek;

    public ChatBIService(ISqlSugarClient db, DeepSeekChatClient deepSeek)
    {
        _db = db;
        _deepSeek = deepSeek;
    }

    public async Task<ChatBIAnswerOutput> AskAsync(ChatBIAskInput input, long tenantId, CancellationToken cancellationToken = default)
    {
        var question = string.IsNullOrWhiteSpace(input.Question) ? "当前最需要关注的问题是什么？" : input.Question.Trim();
        var moduleCode = NormalizeModuleCode(input.ModuleCode);
        var intent = ClassifyIntent(question, moduleCode);
        var allMetrics = await LoadMetricCardsAsync(tenantId, input.FactoryId <= 0 ? 1 : input.FactoryId, moduleCode);
        var focus = ResolveFocusMetric(allMetrics, question, intent);
        var hasExplicitMetric = HasExplicitMetricMention(allMetrics, question);
        var metrics = BuildContextMetrics(allMetrics, focus, intent, moduleCode, hasExplicitMetric);
        var contextTitle = moduleCode == null ? "九宫格全局运营看板" : $"{moduleCode} {ResolveModuleName(moduleCode)}";
        var fallback = BuildDeterministicAnswer(question, contextTitle, intent, focus, metrics);

        var llmAnswer = await TryBuildDeepSeekAnswerAsync(question, contextTitle, input.Filters, fallback, metrics, cancellationToken);
        if (!string.IsNullOrWhiteSpace(llmAnswer))
        {
            fallback.Source = "deepseek";
            fallback.IsFallback = false;
            fallback.AnswerText = llmAnswer;
            fallback.Summary = FirstSentence(llmAnswer);
        }

        fallback.ContextTitle = contextTitle;
        fallback.Actions = BuildActions(moduleCode, focus ?? metrics.FirstOrDefault());
        fallback.Suggestions = BuildSuggestions(moduleCode, intent, focus ?? metrics.FirstOrDefault());
        return fallback;
    }

    private async Task<List<ChatBIMetricCard>> LoadMetricCardsAsync(long tenantId, long factoryId, string? moduleCode)
    {
        var kpiQuery = _db.Queryable<AdoSmartOpsKpiMaster>()
            .Where(x => x.TenantId == tenantId && x.IsEnabled);
        if (!string.IsNullOrWhiteSpace(moduleCode))
            kpiQuery = kpiQuery.Where(x => x.ModuleCode == moduleCode);

        var kpis = await kpiQuery
            .OrderBy(x => x.MetricLevel)
            .OrderBy(x => x.SortNo)
            .ToListAsync();
        var values = await LoadCurrentValuesAsync(tenantId, factoryId, moduleCode);

        var cards = kpis
            .Select(kpi =>
            {
                values.TryGetValue(kpi.MetricCode, out var value);
                var current = value?.MetricValue;
                var target = value?.TargetValue;
                var status = AidopS4KpiMerge.AchievementLevel(
                    current,
                    target,
                    kpi.Direction ?? "higher_is_better",
                    kpi.YellowThreshold,
                    kpi.RedThreshold);
                var gap = AidopS4KpiMerge.GapValue(current, target);

                return new ChatBIMetricCard
                {
                    Id = kpi.Id,
                    ParentId = kpi.ParentId,
                    ModuleCode = kpi.ModuleCode,
                    MetricCode = kpi.MetricCode,
                    MetricName = kpi.MetricName,
                    MetricLevel = kpi.MetricLevel,
                    CurrentValue = current,
                    TargetValue = target,
                    Unit = kpi.Unit ?? "",
                    StatusColor = status,
                    GapLabel = FormatGapLabel(gap, kpi.Unit),
                    Department = kpi.Department ?? "",
                    TrendFlag = value?.TrendFlag ?? ""
                };
            })
            .Where(x => x.CurrentValue != null || x.TargetValue != null)
            .ToList();
        return cards;
    }

    private async Task<Dictionary<string, MetricValueRow>> LoadCurrentValuesAsync(long tenantId, long factoryId, string? moduleCode)
    {
        var result = new Dictionary<string, MetricValueRow>(StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < ValueTables.Length; i++)
        {
            var table = ValueTables[i];
            try
            {
                var bizDate = await ResolveMaxBizDateAsync(table, tenantId, factoryId, moduleCode);
                if (bizDate == null) continue;

                var sql = string.IsNullOrWhiteSpace(moduleCode)
                    ? $"""
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag
FROM {table}
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0 AND biz_date=@bizDate
"""
                    : $"""
SELECT metric_code AS MetricCode, metric_value AS MetricValue, target_value AS TargetValue, status_color AS StatusColor, trend_flag AS TrendFlag
FROM {table}
WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode AND is_deleted=0 AND biz_date=@bizDate
""";

                var rows = await _db.Ado.SqlQueryAsync<MetricValueRow>(sql, new { tenantId, factoryId, moduleCode, bizDate });
                foreach (var row in rows.Where(x => !string.IsNullOrWhiteSpace(x.MetricCode)))
                {
                    row.Level = i + 1;
                    result[row.MetricCode!] = row;
                }
            }
            catch
            {
                // Demo 读取允许某一层指标值表暂缺，避免 ChatBI 整体不可用。
            }
        }

        return result;
    }

    private async Task<DateTime?> ResolveMaxBizDateAsync(string table, long tenantId, long factoryId, string? moduleCode)
    {
        var sql = string.IsNullOrWhiteSpace(moduleCode)
            ? $"SELECT MAX(biz_date) FROM {table} WHERE tenant_id=@tenantId AND factory_id=@factoryId AND is_deleted=0"
            : $"SELECT MAX(biz_date) FROM {table} WHERE tenant_id=@tenantId AND factory_id=@factoryId AND module_code=@moduleCode AND is_deleted=0";
        return await _db.Ado.GetDateTimeAsync(sql, new { tenantId, factoryId, moduleCode });
    }

    private async Task<string?> TryBuildDeepSeekAnswerAsync(
        string question,
        string contextTitle,
        Dictionary<string, string>? filters,
        ChatBIAnswerOutput deterministic,
        List<ChatBIMetricCard> metrics,
        CancellationToken cancellationToken)
    {
        if (metrics.Count == 0) return null;

        var systemPrompt = """
你是 Ai-DOP 制造运营 ChatBI 助手。后端已经完成意图识别、指标选择和事实校验。
你的任务只是把“确定性回答结构”润色成清晰中文，不得新增未给出的原因、单据、负责人或数值。
如果用户问题已经指定某个指标，只能围绕焦点指标和 KPI 聚合摘要中列出的真实下级指标回答，不得引用同模块其它兄弟指标。
请按「结论 / 证据 / 可能原因 / 下一步」四段输出，每段 1 到 2 句，总字数控制在 220 字以内。
""";
        var userPrompt = new StringBuilder()
            .AppendLine($"入口：{contextTitle}")
            .AppendLine($"用户问题：{question}")
            .AppendLine($"识别意图：{deterministic.Intent}")
            .AppendLine($"焦点指标：{deterministic.FocusMetricCode}")
            .AppendLine($"筛选条件：{FormatFilters(filters)}")
            .AppendLine("确定性回答结构：");
        foreach (var section in deterministic.Sections)
            userPrompt.AppendLine($"- {section.Title}：{section.Content}");
        userPrompt
            .AppendLine("KPI 聚合摘要：");
        foreach (var m in metrics.Take(10))
        {
            userPrompt.AppendLine(
                $"- {m.ModuleCode} {m.MetricName}({m.MetricCode}, L{m.MetricLevel})：当前 {FormatValue(m.CurrentValue, m.Unit)}，目标 {FormatValue(m.TargetValue, m.Unit)}，状态 {StatusText(m.StatusColor)}，期量差 {m.GapLabel}，责任 {FallbackText(m.Department, "未配置")}");
        }

        return await _deepSeek.CompleteAsync(systemPrompt, userPrompt.ToString(), cancellationToken);
    }

    private static ChatBIAnswerOutput BuildDeterministicAnswer(
        string question,
        string contextTitle,
        string intent,
        ChatBIMetricCard? focus,
        List<ChatBIMetricCard> metrics)
    {
        var sections = BuildSections(question, contextTitle, intent, focus, metrics);
        var answer = sections.Count == 0
            ? $"已收到问题“{question}”。当前 {contextTitle} 暂未读取到可用于分析的 KPI 聚合值，请先确认指标日值表是否有数据。"
            : string.Join("\n", sections.Select(x => $"{x.Title}：{x.Content}"));

        return new ChatBIAnswerOutput
        {
            Source = "local",
            IsFallback = true,
            Intent = intent,
            FocusMetricCode = focus?.MetricCode ?? "",
            Summary = focus == null ? "暂未读取到 KPI 聚合数据" : $"重点关注：{focus.MetricName}",
            AnswerText = answer,
            Sections = sections,
            Metrics = metrics
        };
    }

    private static List<ChatBIAction> BuildActions(string? moduleCode, ChatBIMetricCard? focus)
    {
        var primaryModule = moduleCode ?? focus?.ModuleCode ?? "S1";
        var metricCode = focus?.MetricLevel == 1 ? focus.MetricCode : null;
        return new List<ChatBIAction>
        {
            new() { Label = $"查看 {primaryModule} 看板", Url = $"/aidop/smart-ops/{primaryModule.ToLowerInvariant()}" },
            new()
            {
                Label = "打开智慧诊断",
                Url = string.IsNullOrWhiteSpace(metricCode)
                    ? $"/aidop/smart-diagnosis?module={primaryModule}"
                    : $"/aidop/smart-diagnosis?module={primaryModule}&metricCode={metricCode}"
            }
        };
    }

    private static List<string> BuildSuggestions(string? moduleCode, string intent, ChatBIMetricCard? focus)
    {
        if (moduleCode == null)
        {
            return new List<string>
            {
                "当前全局最严重的问题是什么？",
                "哪些模块出现红灯指标？",
                focus == null ? "S1 产销协同有什么风险？" : $"{focus.ModuleCode} 的 {focus.MetricName} 为什么异常？"
            };
        }

        return new List<string>
        {
            $"{moduleCode} 当前最严重的指标是什么？",
            focus == null ? "订单评审周期为什么红了？" : $"{focus.MetricName} 为什么异常？",
            intent == "improvement_plan" ? "如何验证改善是否有效？" : "下一步应该创建什么改善计划？"
        };
    }

    private static string ClassifyIntent(string question, string? moduleCode)
    {
        if (ContainsAny(question, "改善", "计划", "措施", "下一步", "怎么处理", "怎么办")) return "improvement_plan";
        if (ContainsAny(question, "为什么", "原因", "异常", "红", "黄", "没达标", "未达标")) return "root_cause";
        if (ContainsAny(question, "趋势", "变化", "近", "最近", "连续")) return "trend_summary";
        return moduleCode == null ? "global_bottleneck" : "metric_status";
    }

    private static ChatBIMetricCard? ResolveFocusMetric(List<ChatBIMetricCard> metrics, string question, string intent)
    {
        var exact = metrics
            .Select(x => new { Metric = x, Score = MetricMatchScore(question, x) })
            .Where(x => x.Score > 0)
            .OrderByDescending(x => x.Score)
            .ThenByDescending(x => StatusRank(x.Metric.StatusColor))
            .ThenBy(x => x.Metric.MetricLevel)
            .FirstOrDefault();
        if (exact != null) return exact.Metric;

        var preferredLevels = intent == "global_bottleneck" ? new[] { 1, 2, 3, 4 } : new[] { 1, 2, 3, 4 };
        return metrics
            .OrderByDescending(x => StatusRank(x.StatusColor))
            .ThenBy(x => Array.IndexOf(preferredLevels, x.MetricLevel))
            .ThenBy(x => x.MetricCode)
            .FirstOrDefault();
    }

    private static List<ChatBIMetricCard> BuildContextMetrics(
        List<ChatBIMetricCard> allMetrics,
        ChatBIMetricCard? focus,
        string intent,
        string? moduleCode,
        bool hasExplicitMetric)
    {
        if (focus == null)
            return allMetrics.OrderByDescending(x => StatusRank(x.StatusColor)).ThenBy(x => x.MetricLevel).Take(moduleCode == null ? 9 : 10).ToList();

        var sameModule = allMetrics.Where(x => string.Equals(x.ModuleCode, focus.ModuleCode, StringComparison.OrdinalIgnoreCase)).ToList();
        var context = new List<ChatBIMetricCard> { focus };

        if (hasExplicitMetric)
        {
            context.AddRange(sameModule
                .Where(x => x.MetricCode != focus.MetricCode && IsDescendantOf(x, focus, sameModule))
                .OrderByDescending(x => StatusRank(x.StatusColor))
                .ThenBy(x => x.MetricLevel)
                .ThenBy(x => x.MetricCode)
                .Take(9));

            return context
                .GroupBy(x => x.MetricCode, StringComparer.OrdinalIgnoreCase)
                .Select(x => x.First())
                .Take(10)
                .ToList();
        }

        if (intent is "root_cause" or "improvement_plan")
        {
            context.AddRange(sameModule
                .Where(x => x.MetricCode != focus.MetricCode && x.MetricLevel >= focus.MetricLevel)
                .OrderByDescending(x => StatusRank(x.StatusColor))
                .ThenBy(x => x.MetricLevel)
                .Take(7));
        }
        else if (moduleCode == null)
        {
            context.AddRange(allMetrics
                .Where(x => x.MetricCode != focus.MetricCode && x.MetricLevel == 1)
                .OrderByDescending(x => StatusRank(x.StatusColor))
                .ThenBy(x => x.ModuleCode)
                .Take(8));
        }
        else
        {
            context.AddRange(sameModule
                .Where(x => x.MetricCode != focus.MetricCode)
                .OrderByDescending(x => StatusRank(x.StatusColor))
                .ThenBy(x => x.MetricLevel)
                .Take(7));
        }

        return context
            .GroupBy(x => x.MetricCode, StringComparer.OrdinalIgnoreCase)
            .Select(x => x.First())
            .Take(10)
            .ToList();
    }

    private static List<ChatBIAnswerSection> BuildSections(
        string question,
        string contextTitle,
        string intent,
        ChatBIMetricCard? focus,
        List<ChatBIMetricCard> metrics)
    {
        if (focus == null) return new List<ChatBIAnswerSection>();

        var relatedRisks = metrics
            .Where(x => x.MetricCode != focus.MetricCode && StatusRank(x.StatusColor) >= 2)
            .OrderByDescending(x => StatusRank(x.StatusColor))
            .ThenBy(x => x.MetricLevel)
            .Take(3)
            .ToList();
        var riskText = relatedRisks.Count == 0
            ? "当前上下文未发现更多红黄下层指标，需进入智慧诊断继续看明细证据。"
            : "相关红黄指标包括：" + string.Join("、", relatedRisks.Select(x => $"{x.MetricName}({StatusText(x.StatusColor)})")) + "。";
        var nextAction = intent switch
        {
            "improvement_plan" => $"建议围绕 {focus.MetricName} 建立改善计划，目标值先按 {FormatValue(focus.TargetValue, focus.Unit)} 对齐，并把红黄下层指标作为行动项来源。",
            "root_cause" => $"建议打开智慧诊断，从 {focus.MetricName} 下钻到 L2/L3/L4，确认责任部门、卡点和单据后再创建改善计划。",
            "trend_summary" => $"建议结合近 7 到 14 天趋势复核 {focus.MetricName} 是否连续恶化，再决定是否升级为改善任务。",
            _ => $"建议优先跟进 {focus.MetricName}，若持续红黄则进入智慧诊断并创建改善任务。"
        };

        return new List<ChatBIAnswerSection>
        {
            new()
            {
                Title = "结论",
                Tone = StatusRank(focus.StatusColor) >= 3 ? "danger" : StatusRank(focus.StatusColor) == 2 ? "warning" : "info",
                Content = $"{contextTitle} 当前焦点是 {focus.MetricName}，状态为{StatusText(focus.StatusColor)}。"
            },
            new()
            {
                Title = "证据",
                Tone = "info",
                Content = $"{focus.MetricName} 当前 {FormatValue(focus.CurrentValue, focus.Unit)}，目标 {FormatValue(focus.TargetValue, focus.Unit)}，期量差 {FallbackText(focus.GapLabel, "-")}。"
            },
            new()
            {
                Title = "可能原因",
                Tone = relatedRisks.Count > 0 ? "warning" : "info",
                Content = riskText
            },
            new()
            {
                Title = "下一步",
                Tone = "success",
                Content = nextAction
            }
        };
    }

    private static string? NormalizeModuleCode(string? moduleCode)
    {
        var mc = (moduleCode ?? "").Trim().ToUpperInvariant();
        return mc is "S1" or "S2" or "S3" or "S4" or "S5" or "S6" or "S7" or "S9" ? mc : null;
    }

    private static bool QuestionMatchesMetric(string question, ChatBIMetricCard metric)
    {
        if (string.IsNullOrWhiteSpace(question)) return false;
        return question.Contains(metric.MetricName, StringComparison.OrdinalIgnoreCase)
               || question.Contains(metric.MetricCode, StringComparison.OrdinalIgnoreCase);
    }

    private static bool HasExplicitMetricMention(List<ChatBIMetricCard> metrics, string question)
    {
        return metrics.Any(metric => QuestionMatchesMetric(question, metric));
    }

    private static bool IsDescendantOf(ChatBIMetricCard candidate, ChatBIMetricCard ancestor, List<ChatBIMetricCard> sameModule)
    {
        var byId = sameModule.Where(x => x.Id > 0).ToDictionary(x => x.Id);
        var parentId = candidate.ParentId;
        while (parentId != null)
        {
            if (parentId.Value == ancestor.Id) return true;
            if (!byId.TryGetValue(parentId.Value, out var parent)) return false;
            parentId = parent.ParentId;
        }

        return false;
    }

    private static int MetricMatchScore(string question, ChatBIMetricCard metric)
    {
        var score = 0;
        if (QuestionMatchesMetric(question, metric)) score += 100;
        foreach (var token in SplitMetricName(metric.MetricName))
        {
            if (token.Length >= 2 && question.Contains(token, StringComparison.OrdinalIgnoreCase)) score += 10;
        }
        return score;
    }

    private static IEnumerable<string> SplitMetricName(string metricName)
    {
        var separators = new[] { ' ', '/', '-', '_', '（', '）', '(', ')', '：', ':', '、' };
        return (metricName ?? "").Split(separators, StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
    }

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

    private static string StatusText(string? status) => (status ?? "").ToLowerInvariant() switch
    {
        "red" => "红灯",
        "yellow" => "黄灯",
        "green" => "绿灯",
        _ => "未知"
    };

    private static string ResolveModuleName(string moduleCode) => moduleCode.ToUpperInvariant() switch
    {
        "S1" => "产销协同动态详情看板",
        "S2" => "制造协同动态详情看板",
        "S3" => "供应协同动态详情看板",
        "S4" => "采购执行动态详情看板",
        "S5" => "物料仓储动态详情看板",
        "S6" => "生产执行动态详情看板",
        "S7" => "成品仓储动态详情看板",
        "S9" => "运营指标动态详情看板",
        _ => "动态详情看板"
    };

    private static bool ContainsAny(string text, params string[] needles)
    {
        return needles.Any(x => text.Contains(x, StringComparison.OrdinalIgnoreCase));
    }

    private static string FormatGapLabel(decimal? gap, string? unit)
    {
        if (gap == null) return "";
        var rounded = decimal.Round(gap.Value, 2);
        return $"{rounded:0.##}{unit ?? ""}";
    }

    private static string FormatValue(decimal? value, string? unit)
    {
        if (value == null) return "-";
        return $"{decimal.Round(value.Value, 2):0.##}{unit ?? ""}";
    }

    private static string FormatFilters(Dictionary<string, string>? filters)
    {
        if (filters == null || filters.Count == 0) return "未填写条件（展示全部）";
        return string.Join("；", filters.Where(x => !string.IsNullOrWhiteSpace(x.Value)).Select(x => $"{x.Key}={x.Value}"));
    }

    private static string FallbackText(string? value, string fallback) => string.IsNullOrWhiteSpace(value) ? fallback : value;

    private static string FirstSentence(string text)
    {
        var trimmed = text.Trim();
        var idx = trimmed.IndexOfAny(new[] { '。', '！', '？', '\n' });
        return idx > 0 ? trimmed[..Math.Min(idx + 1, trimmed.Length)] : trimmed;
    }

    private sealed class MetricValueRow
    {
        public int Level { get; set; }
        public string? MetricCode { get; set; }
        public decimal? MetricValue { get; set; }
        public decimal? TargetValue { get; set; }
        public string? StatusColor { get; set; }
        public string? TrendFlag { get; set; }
    }
}