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

using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
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 llmClass = await TryClassifyIntentWithLlmAsync(question, moduleCode, cancellationToken);
        var usedLlmClass = llmClass != null;
        var intent = usedLlmClass
            ? MapLlmTypeToIntent(llmClass.Type, question)
            : ClassifyIntent(question, moduleCode);
        if (IsOutOfScopeDetailQuestion(question))
            intent = "data_query";
        var (dateStart, dateEnd) = ParseDateFilters(input.Filters);
        var allMetrics = await LoadMetricCardsAsync(
            tenantId, input.FactoryId <= 0 ? 1 : input.FactoryId, moduleCode, dateStart, dateEnd);
        var excludedModules = ParseExcludedModules(question);
        var includedModules = ParseIncludedModules(question, excludedModules);
        var matchText = AppendMetricHint(question, llmClass?.MetricHint);
        var hasNamedMetric = HasExplicitMetricMention(allMetrics, matchText);
        if (intent == "data_query")
        {
            // 来自哪些订单 / 怎么算 / 取值：仍属数据查询，不改成诊断模板。
        }
        else if (!usedLlmClass && hasNamedMetric && !IsDiagnosticQuestion(question) && intent is not "best_metric")
            intent = IsLookupQuestion(question) ? "list_metrics" : "metric_status";
        else if (intent == "unrecognized" && includedModules.Count > 0)
            intent = "list_metrics";
        else if (usedLlmClass && intent == "unrecognized" && hasNamedMetric && !IsDiagnosticQuestion(question))
            intent = "data_query";
        var requestedLevel = ParseRequestedLevel(question)
            ?? (intent is "list_metrics" or "best_metric" && !hasNamedMetric ? 1 : null);
        var scopedMetrics = ApplyQuestionScope(allMetrics, includedModules, excludedModules, requestedLevel);
        var hasExplicitMetric = HasExplicitMetricMention(scopedMetrics, matchText);
        ChatBIMetricCard? focus;
        List<ChatBIMetricCard> metrics;
        if (intent == "unrecognized")
        {
            focus = null;
            metrics = new List<ChatBIMetricCard>();
        }
        else if (intent == "data_query")
        {
            var canLookup = hasNamedMetric || IsLookupQuestion(question) || requestedLevel != null;
            metrics = canLookup
                ? SelectLookupMetrics(scopedMetrics, matchText, hasNamedMetric)
                : new List<ChatBIMetricCard>();
            focus = metrics.FirstOrDefault();
        }
        else if (intent == "list_metrics")
        {
            focus = null;
            metrics = SelectLookupMetrics(scopedMetrics, matchText, hasNamedMetric);
            if (metrics.Count == 1) focus = metrics[0];
        }
        else if (intent == "best_metric")
        {
            metrics = SelectBestMetrics(scopedMetrics);
            focus = metrics.FirstOrDefault();
        }
        else
        {
            focus = ResolveFocusMetric(scopedMetrics, matchText, intent);
            metrics = BuildContextMetrics(scopedMetrics, focus, intent, moduleCode, hasExplicitMetric);
        }
        var contextTitle = moduleCode == null ? "九宫格全局运营看板" : $"{moduleCode} {ResolveModuleName(moduleCode)}";
        var fallback = BuildDeterministicAnswer(question, contextTitle, intent, focus, metrics, excludedModules, requestedLevel, moduleCode);

        var llmAnswer = intent == "unrecognized"
            ? null
            : 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(), intent);
        fallback.Suggestions = BuildSuggestions(moduleCode, intent, focus ?? metrics.FirstOrDefault());
        return fallback;
    }

    private async Task<List<ChatBIMetricCard>> LoadMetricCardsAsync(
        long tenantId, long factoryId, string? moduleCode, DateTime? dateStart, DateTime? dateEnd)
    {
        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, dateStart, dateEnd);

        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 ?? "",
                    Direction = kpi.Direction ?? "higher_is_better"
                };
            })
            .Where(x => x.CurrentValue != null || x.TargetValue != null)
            .ToList();
        return cards;
    }

    private async Task<Dictionary<string, MetricValueRow>> LoadCurrentValuesAsync(
        long tenantId, long factoryId, string? moduleCode, DateTime? dateStart, DateTime? dateEnd)
    {
        var result = new Dictionary<string, MetricValueRow>(StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < ValueTables.Length; i++)
        {
            var table = ValueTables[i];
            try
            {
                var sql = string.IsNullOrWhiteSpace(moduleCode)
                    ? $"""
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag
FROM {table} v
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.is_deleted=0
  AND v.biz_date=(
      SELECT MAX(v2.biz_date) FROM {table} v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.metric_code=v.metric_code AND v2.is_deleted=0
        AND (@ds IS NULL OR v2.biz_date >= @ds)
        AND (@de IS NULL OR v2.biz_date <= @de)
  )
"""
                    : $"""
SELECT v.metric_code AS MetricCode, v.metric_value AS MetricValue, v.target_value AS TargetValue,
       v.status_color AS StatusColor, v.trend_flag AS TrendFlag
FROM {table} v
WHERE v.tenant_id=@tenantId AND v.factory_id=@factoryId AND v.module_code=@moduleCode AND v.is_deleted=0
  AND v.biz_date=(
      SELECT MAX(v2.biz_date) FROM {table} v2
      WHERE v2.tenant_id=v.tenant_id AND v2.factory_id=v.factory_id
        AND v2.module_code=v.module_code AND v2.metric_code=v.metric_code AND v2.is_deleted=0
        AND (@ds IS NULL OR v2.biz_date >= @ds)
        AND (@de IS NULL OR v2.biz_date <= @de)
  )
""";

                var rows = await _db.Ado.SqlQueryAsync<MetricValueRow>(sql, new
                {
                    tenantId,
                    factoryId,
                    moduleCode,
                    ds = dateStart,
                    de = dateEnd
                });
                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<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 = SystemPromptFor(deterministic.Intent);
        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,
        HashSet<string>? excludedModules = null,
        int? requestedLevel = null,
        string? moduleCode = null)
    {
        var sections = intent switch
        {
            "unrecognized" => BuildUnrecognizedSections(question, contextTitle, moduleCode),
            "data_query" => BuildDataQuerySections(question, metrics),
            "list_metrics" => BuildListSections(question, contextTitle, metrics, excludedModules, requestedLevel),
            "best_metric" => BuildRankSections(question, contextTitle, metrics),
            "metric_status" => BuildStatusSections(focus),
            _ => 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 = intent == "unrecognized"
                ? "请改问当前看板的 KPI"
                : focus == null
                    ? "暂未读取到 KPI 聚合数据"
                    : intent == "best_metric" ? $"当前最好：{focus.MetricName}" : $"重点关注：{focus.MetricName}",
            AnswerText = answer,
            Sections = sections,
            Metrics = metrics
        };
    }

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

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

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

    internal static string ClassifyIntent(string question, string? moduleCode)
    {
        if (IsOutOfScopeDetailQuestion(question)) return "data_query";
        if (IsBestQuestion(question)) return "best_metric";
        if (IsBottleneckQuestion(question)) return "global_bottleneck";
        if (IsDiagnosticQuestion(question))
            return MapDiagnoseIntent(question);
        if (IsLookupQuestion(question)
            || Regex.IsMatch(question, @"L\s*[1-4].*(指标|值|多少)", RegexOptions.IgnoreCase)
            || Regex.IsMatch(question, @"(指标|值|多少).*L\s*[1-4]", RegexOptions.IgnoreCase))
            return "list_metrics";
        return "unrecognized";
    }

    internal static string MapLlmTypeToIntent(string type, string question) => type switch
    {
        "data_query" => "data_query",
        "compare" => IsBottleneckQuestion(question) && !IsBestQuestion(question)
            ? "global_bottleneck"
            : IsBestQuestion(question) ? "best_metric" : "list_metrics",
        "locate" => "global_bottleneck",
        "diagnose" => MapDiagnoseIntent(question),
        "unrecognized" => "unrecognized",
        _ => "unrecognized"
    };

    internal static ChatBILlmClass? ParseLlmClassification(string? raw)
    {
        if (string.IsNullOrWhiteSpace(raw)) return null;
        var text = raw.Trim();
        var start = text.IndexOf('{');
        var end = text.LastIndexOf('}');
        if (start < 0 || end <= start) return null;
        text = text[start..(end + 1)];
        try
        {
            using var doc = JsonDocument.Parse(text);
            var type = doc.RootElement.TryGetProperty("type", out var typeEl)
                ? typeEl.GetString()?.Trim().ToLowerInvariant()
                : null;
            if (type is not ("data_query" or "compare" or "locate" or "diagnose" or "unrecognized"))
                return null;
            var hint = doc.RootElement.TryGetProperty("metricHint", out var hintEl)
                ? hintEl.GetString()
                : null;
            return new ChatBILlmClass(type, string.IsNullOrWhiteSpace(hint) ? null : hint.Trim());
        }
        catch (JsonException)
        {
            return null;
        }
    }

    private async Task<ChatBILlmClass?> TryClassifyIntentWithLlmAsync(
        string question, string? moduleCode, CancellationToken cancellationToken)
    {
        try
        {
            var scope = moduleCode == null ? "九宫格全局运营看板" : $"{moduleCode} 模块看板";
            var raw = await _deepSeek.CompleteAsync(
                ClassifySystemPrompt,
                $"入口：{scope}\n用户问题：{question}",
                cancellationToken,
                maxTokens: 80);
            return ParseLlmClassification(raw);
        }
        catch
        {
            return null;
        }
    }

    private static string MapDiagnoseIntent(string question)
    {
        if (ContainsAny(question, "改善计划", "改善措施", "怎么改善", "如何改善", "下一步")) return "improvement_plan";
        if (ContainsAny(question, "趋势", "变化", "近", "最近", "连续")) return "trend_summary";
        return "root_cause";
    }

    private static string AppendMetricHint(string question, string? metricHint) =>
        string.IsNullOrWhiteSpace(metricHint) ? question : $"{question} {metricHint}";

    private const string ClassifySystemPrompt = """
你是 Ai-DOP ChatBI 的问题分类器。只判断用户问题属于哪一类，不要回答业务内容，不要给数值。
只输出一行 JSON，不要 Markdown，不要解释。格式：
{"type":"data_query|compare|locate|diagnose|unrecognized","metricHint":""}

type 含义：
- data_query：数据查询。问指标是多少、什么情况、当前值、怎么算、计算口径、来自哪些订单/单据/明细。
- compare：指标好坏对比。问谁最好、哪些绿灯、最差、好坏对比。
- locate：问题定位。问最严重、瓶颈、哪些模块红灯、当前最需要关注。
- diagnose：问题原因和如何改善。问为什么、原因、怎么改善、改善计划、趋势恶化。
- unrecognized：无法归入以上四类，或与看板 KPI 无关。

metricHint：若问题里提到了指标名，填该名称；否则空字符串。
""";

    internal static bool IsBestQuestion(string question) =>
        ContainsAny(question, "最好", "最优秀", "表现最好", "做得最好", "做的最好", "哪些绿灯", "哪个绿灯");

    internal static bool IsBottleneckQuestion(string question) =>
        ContainsAny(question, "最严重", "最差", "最需要关注", "需要关注", "瓶颈", "风险", "红灯", "哪些模块出现");

    internal static bool IsOutOfScopeDetailQuestion(string question) =>
        ContainsAny(question, "来自哪些", "哪些订单", "哪些单据", "哪几笔", "哪张订单",
            "怎么算", "如何计算", "如何算出", "计算出来", "计算口径", "明细", "原始数据", "原始明细");

    private static List<string> AllowedQuestionExamples(string? moduleCode) =>
        moduleCode == null
            ? new List<string>
            {
                "当前全局最严重的问题是什么？",
                "整体做得最好的是哪个？",
                "物料交货满足率什么情况",
            }
            : new List<string>
            {
                $"{moduleCode} 当前最严重的指标是什么？",
                $"{moduleCode} 哪些指标是绿灯？",
                $"{moduleCode} L1 指标分别是多少？",
            };

    private static List<ChatBIAnswerSection> BuildUnrecognizedSections(
        string question,
        string contextTitle,
        string? moduleCode)
    {
        var scope = moduleCode == null
            ? "当前九宫格上的 L1 运营指标（S1–S7 / S9），以及你在看板上选的日期等筛选条件"
            : $"{moduleCode} {ResolveModuleName(moduleCode)} 上的 L1–L4 指标，以及当前筛选条件";
        return new List<ChatBIAnswerSection>
        {
            new()
            {
                Title = "无法识别",
                Tone = "warning",
                Content = $"「{question}」不在 ChatBI 可回答范围内。这里只根据{scope}做指标问答，不查原始明细，也不生成 SQL。"
            },
            new()
            {
                Title = "可以这样问",
                Tone = "info",
                Content = "可问四类问题：数据查询 / 指标好坏对比 / 问题定位 / 问题原因和如何改善。请用看板上的指标名，例如物料交货满足率。"
            }
        };
    }

    private static bool IsLookupQuestion(string question) =>
        ContainsAny(question, "是多少", "当前值", "现值", "指标值", "查一下", "查询", "取值", "读数",
            "有哪些", "列出", "分别是", "以外", "之外", "除外", "除了");

    private static bool IsDiagnosticQuestion(string question) =>
        ContainsAny(question, "为什么", "原因", "异常", "改善", "措施", "下一步", "趋势", "怎么处理", "怎么办", "没达标", "未达标");

    private static List<ChatBIAnswerSection> BuildDataQuerySections(string question, List<ChatBIMetricCard> metrics)
    {
        var sections = new List<ChatBIAnswerSection>();
        if (metrics.Count > 0)
        {
            var lines = metrics.Select(x =>
                $"{x.ModuleCode} {x.MetricName}：当前 {FormatValue(x.CurrentValue, x.Unit)}，目标 {FormatValue(x.TargetValue, x.Unit)}，{StatusText(x.StatusColor)}");
            sections.Add(new ChatBIAnswerSection
            {
                Title = "查询结果",
                Tone = "info",
                Content = string.Join("；", lines)
            });
        }
        sections.Add(new ChatBIAnswerSection
        {
            Title = "数据说明",
            Tone = "warning",
            Content = IsOutOfScopeDetailQuestion(question)
                ? "这属于数据查询。当前只能给出看板已算出的 KPI 聚合值，不能列出构成该指标的订单或原始明细，也不生成 SQL。若要看单据，请到对应模块详情页。"
                : "这属于数据查询，以上为当前看板聚合值。"
        });
        return sections;
    }

    private static string SystemPromptFor(string intent) => intent switch
    {
        "data_query" => """
你是 Ai-DOP 制造运营 ChatBI 助手。用户在做数据查询（取值、来源、怎么算、哪些订单）。
先如实给出 KPI 聚合摘要中的当前值、目标、状态。
若用户问来自哪些订单、单据或明细：明确说当前只能查看板聚合结果，不能列出订单，也不生成 SQL。
禁止改写成红灯诊断或改善计划。不得编造摘要里没有的数值。
总字数控制在 200 字以内。
""",
        "list_metrics" => """
你是 Ai-DOP 制造运营 ChatBI 助手。用户要的是指标取值清单，不是诊断报告。
只根据「KPI 聚合摘要」如实列出模块、指标名、当前值、目标、状态。
不得改写成红灯瓶颈分析，不得编造摘要里没有的数值。若摘要为空，明确说所选条件下没有日值。
总字数控制在 280 字以内。
""",
        "best_metric" => """
你是 Ai-DOP 制造运营 ChatBI 助手。用户在问谁做得最好、哪项表现最好。
直接点名最好的 1 个指标及其模块、当前值、目标、状态；最多再列 1-2 个对照。
禁止套用「结论 / 证据 / 可能原因 / 下一步」。
禁止把红灯或最差指标说成最好。只能用摘要里的绿灯或相对最好的项。
总字数控制在 180 字以内。
""",
        "metric_status" => """
你是 Ai-DOP 制造运营 ChatBI 助手。用户在问某个指标现在怎么样。
只回答该指标的当前值、目标、状态。不要写可能原因和下一步，除非用户明确问原因。
不得把其它模块红灯拉进来。不得编造摘要里没有的数值。
总字数控制在 160 字以内。
""",
        _ => """
你是 Ai-DOP 制造运营 ChatBI 助手。后端已经完成意图识别、指标选择和事实校验。
先直接回答用户问题。仅当用户在问原因、异常或改善时，再补充可能原因 / 下一步。
不得新增未给出的原因、单据、负责人或数值。
如果用户问题已经指定某个指标，只能围绕焦点指标和它的真实下级指标回答，不得把其它模块、其它 L1 红灯写成原因。
总字数控制在 220 字以内。
"""
    };

    internal static List<ChatBIMetricCard> SelectBestMetrics(List<ChatBIMetricCard> scoped)
    {
        return scoped
            .OrderBy(x => StatusRank(x.StatusColor) == 0 ? 9 : StatusRank(x.StatusColor))
            .ThenByDescending(AchievementScore)
            .ThenBy(x => x.MetricLevel)
            .ThenBy(x => x.MetricCode)
            .Take(4)
            .ToList();
    }

    internal static decimal AchievementScore(ChatBIMetricCard metric)
    {
        if (metric.CurrentValue is not { } current || metric.TargetValue is not { } target || target == 0)
            return 0;
        return AidopS4KpiMerge.IsLowerBetter(metric.Direction)
            ? (current == 0 ? 99 : target / current)
            : current / target;
    }

    private static List<ChatBIAnswerSection> BuildRankSections(
        string question,
        string contextTitle,
        List<ChatBIMetricCard> metrics)
    {
        if (metrics.Count == 0)
        {
            return new List<ChatBIAnswerSection>
            {
                new()
                {
                    Title = "直接回答",
                    Tone = "info",
                    Content = $"{contextTitle} 当前没有可比较的日值，无法判断谁做得最好。"
                }
            };
        }

        var best = metrics[0];
        var hasGreen = StatusRank(best.StatusColor) == 1;
        var lead = hasGreen
            ? $"针对「{question}」，当前整体做得最好的是 {best.ModuleCode} {best.MetricName}，绿灯，当前 {FormatValue(best.CurrentValue, best.Unit)}，目标 {FormatValue(best.TargetValue, best.Unit)}。"
            : $"针对「{question}」，当前没有绿灯。相对最好的是 {best.ModuleCode} {best.MetricName}，{StatusText(best.StatusColor)}，当前 {FormatValue(best.CurrentValue, best.Unit)}，目标 {FormatValue(best.TargetValue, best.Unit)}。";
        var sections = new List<ChatBIAnswerSection>
        {
            new() { Title = "直接回答", Tone = hasGreen ? "success" : "warning", Content = lead }
        };
        var others = metrics.Skip(1).Take(2).ToList();
        if (others.Count > 0)
        {
            sections.Add(new ChatBIAnswerSection
            {
                Title = "对照",
                Tone = "info",
                Content = "其次较好的还有：" + string.Join("；", others.Select(x =>
                    $"{x.ModuleCode} {x.MetricName} {StatusText(x.StatusColor)}，当前 {FormatValue(x.CurrentValue, x.Unit)} / 目标 {FormatValue(x.TargetValue, x.Unit)}"))
            });
        }
        return sections;
    }

    private static List<ChatBIAnswerSection> BuildStatusSections(ChatBIMetricCard? focus)
    {
        if (focus == null) return new List<ChatBIAnswerSection>();
        return new List<ChatBIAnswerSection>
        {
            new()
            {
                Title = "指标状态",
                Tone = StatusRank(focus.StatusColor) >= 3 ? "danger" : StatusRank(focus.StatusColor) == 2 ? "warning" : "success",
                Content = $"{focus.ModuleCode} {focus.MetricName} 当前 {FormatValue(focus.CurrentValue, focus.Unit)}，目标 {FormatValue(focus.TargetValue, focus.Unit)}，状态 {StatusText(focus.StatusColor)}，期量差 {FallbackText(focus.GapLabel, "-")}。"
            }
        };
    }

    private static List<ChatBIMetricCard> SelectLookupMetrics(List<ChatBIMetricCard> scoped, string question, bool hasNamedMetric)
    {
        if (hasNamedMetric)
        {
            var named = scoped
                .Where(x => MetricMatchScore(question, x) > 0)
                .OrderByDescending(x => MetricMatchScore(question, x))
                .ThenBy(x => x.MetricLevel)
                .Take(8)
                .ToList();
            if (named.Count > 0) return named;
        }
        return scoped.OrderBy(x => x.ModuleCode).ThenBy(x => x.MetricLevel).ThenBy(x => x.MetricCode).Take(30).ToList();
    }

    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> BuildListSections(
        string question,
        string contextTitle,
        List<ChatBIMetricCard> metrics,
        HashSet<string>? excludedModules,
        int? requestedLevel)
    {
        var levelText = requestedLevel == null ? "L1" : $"L{requestedLevel}";
        var excludeText = excludedModules is { Count: > 0 }
            ? $"已排除 {string.Join("、", excludedModules.OrderBy(x => x))}。"
            : "";
        if (metrics.Count == 0)
        {
            return new List<ChatBIAnswerSection>
            {
                new()
                {
                    Title = "取值结果",
                    Tone = "info",
                    Content = $"{contextTitle} 在当前问题与筛选条件下没有可展示的 {levelText} 日值。{excludeText}请改到有数据的日期区间后再问。"
                }
            };
        }

        if (metrics.Count == 1)
        {
            var one = metrics[0];
            return new List<ChatBIAnswerSection>
            {
                new()
                {
                    Title = "取值结果",
                    Tone = "info",
                    Content = $"{one.ModuleCode} {one.MetricName} 当前 {FormatValue(one.CurrentValue, one.Unit)}，目标 {FormatValue(one.TargetValue, one.Unit)}，状态 {StatusText(one.StatusColor)}。"
                }
            };
        }

        var lines = metrics.Select(x =>
            $"{x.ModuleCode} {x.MetricName}：当前 {FormatValue(x.CurrentValue, x.Unit)}，目标 {FormatValue(x.TargetValue, x.Unit)}，{StatusText(x.StatusColor)}");
        return new List<ChatBIAnswerSection>
        {
            new()
            {
                Title = "取值结果",
                Tone = "info",
                Content = $"针对「{question}」列出 {metrics.Count} 个{levelText}指标。{excludeText}"
            },
            new()
            {
                Title = $"{levelText} 指标",
                Tone = "info",
                Content = string.Join("；", lines)
            }
        };
    }

    private static List<ChatBIMetricCard> ApplyQuestionScope(
        List<ChatBIMetricCard> metrics,
        HashSet<string> includedModules,
        HashSet<string> excludedModules,
        int? requestedLevel)
    {
        return metrics
            .Where(x => requestedLevel == null || x.MetricLevel == requestedLevel)
            .Where(x => excludedModules.Count == 0 || !excludedModules.Contains(x.ModuleCode))
            .Where(x => includedModules.Count == 0 || includedModules.Contains(x.ModuleCode))
            .ToList();
    }

    private static HashSet<string> ParseExcludedModules(string question)
    {
        var set = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (Match match in Regex.Matches(question, @"(?:除了|除開)?\s*S\s*([1-9])\s*(?:楼|樓|模块|模組)?\s*(?:以外|之外|除外)"))
            set.Add("S" + match.Groups[1].Value);
        foreach (Match match in Regex.Matches(question, @"除了\s*S\s*([1-9])"))
            set.Add("S" + match.Groups[1].Value);
        return set;
    }

    private static HashSet<string> ParseIncludedModules(string question, HashSet<string> excluded)
    {
        var set = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        if (excluded.Count > 0) return set;
        foreach (Match match in Regex.Matches(question, @"S\s*([1-9])"))
            set.Add("S" + match.Groups[1].Value);
        return set;
    }

    private static int? ParseRequestedLevel(string question)
    {
        var match = Regex.Match(question, @"L\s*([1-4])", RegexOptions.IgnoreCase);
        return match.Success && int.TryParse(match.Groups[1].Value, out var level) ? level : null;
    }

    private static (DateTime? DateStart, DateTime? DateEnd) ParseDateFilters(Dictionary<string, string>? filters)
    {
        DateTime? start = null;
        DateTime? end = null;
        if (filters != null)
        {
            if (filters.TryGetValue("dateStart", out var rawStart) && DateTime.TryParse(rawStart, out var parsedStart))
                start = parsedStart.Date;
            if (filters.TryGetValue("dateEnd", out var rawEnd) && DateTime.TryParse(rawEnd, out var parsedEnd))
                end = parsedEnd.Date;
        }
        return (start, end);
    }

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

        var sameModule = metrics
            .Where(x => string.Equals(x.ModuleCode, focus.ModuleCode, StringComparison.OrdinalIgnoreCase))
            .ToList();
        var relatedRisks = metrics
            .Where(x => x.MetricCode != focus.MetricCode && StatusRank(x.StatusColor) >= 2)
            .Where(x => string.Equals(x.ModuleCode, focus.ModuleCode, StringComparison.OrdinalIgnoreCase)
                        && (x.MetricLevel > focus.MetricLevel || IsDescendantOf(x, focus, sameModule)))
            .OrderByDescending(x => StatusRank(x.StatusColor))
            .ThenBy(x => x.MetricLevel)
            .Take(3)
            .ToList();
        var riskText = relatedRisks.Count == 0
            ? $"未发现 {focus.MetricName} 的红黄下级指标，需进入智慧诊断继续看明细证据，不能用其它模块红灯当原因。"
            : "其下级红黄指标包括：" + 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;
        if (question.Contains(metric.MetricName, StringComparison.OrdinalIgnoreCase)) return true;
        if (question.Contains(metric.MetricCode, StringComparison.OrdinalIgnoreCase)) return true;
        var core = NormalizeMetricLabel(metric.MetricName);
        return core.Length >= 4 && question.Contains(core, StringComparison.OrdinalIgnoreCase);
    }

    internal static string NormalizeMetricLabel(string? raw)
    {
        var s = (raw ?? "").Trim();
        s = Regex.Replace(s, @"[（(][^）)]*[）)]", string.Empty);
        return s.Trim();
    }

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