hyreal-gen-pic/nl-query.js

'use strict';

/**
 * 智能问数 · NL2Query（两段式）
 * ----------------------------------------------------------------------------
 * 参考 spring-ai-alibaba/DataAgent 的「意图理解 → 查询规划 → 报告呈现」链路，
 * 但不引入 SQL/向量库/Java 栈：
 *   · 第一段（规划）：基于 napos-service 列注册表（同义词 + 口径）把问题翻译成
 *     结构化查询 JSON —— 行数据不出网，模型只做规划不做计算
 *   · 查询执行：本地数据湖取数/聚合/排序（sanitizeQuery 钳制参数）
 *   · 第二段（结论）：把聚合后的少量结果（Top≤10 行 + 区间合计）发回模型，
 *     写一句带真实数字的回答；失败自动降级为模板句
 *   · 结果呈现：reply 文本 + ECharts 图表配置（前端渲染）
 *
 * 需要 OpenAI 兼容的对话模型（config.chatModel / chatApiKey / chatApiBase）。
 */

const https = require('https');
const http = require('http');

const COLUMN_KEYS = new Set(['date', 'shopName', 'bizHours', 'abnormalCloseHours', 'validOrderCnt',
  'incomeAmt', 'grossAmt', 'goodsSaleAmt', 'expUv', 'orderUserCnt',
  'clkExpRate', 'ordClkRate', 'perActualPay', 'expenseAmt', 'actOrdCnt', 'actSubsidyAmt',
  'shopRoi', 'rebuyRate30d']);

const z2 = (n) => String(n).padStart(2, '0');
function ymdLocal(d) { return `${d.getFullYear()}${z2(d.getMonth() + 1)}${z2(d.getDate())}`; }
function daysAgoYmd(n) { return ymdLocal(new Date(Date.now() - n * 86400000)); }
function normDay(v) { return /^\d{8}$/.test(String(v || '')) ? String(v) : ''; }

// ---------- OpenAI 兼容对话调用 ----------

function chatCompletion({ apiBase, apiKey, model, messages, timeoutMs = 45000 }) {
  return new Promise((resolve, reject) => {
    let base;
    try { base = new URL(apiBase); } catch (e) { return reject(new Error('对话模型地址不合法: ' + apiBase)); }
    const body = JSON.stringify({ model, messages, temperature: 0, stream: false });
    const options = {
      hostname: base.hostname,
      port: base.port || 443,
      path: (base.pathname.replace(/\/+$/, '') || '') + '/v1/chat/completions',
      method: 'POST',
      headers: {
        Authorization: `Bearer ${apiKey}`,
        'Content-Type': 'application/json; charset=utf-8',
        'Content-Length': Buffer.byteLength(body),
      },
      timeout: timeoutMs,
    };
    const req = (base.protocol === 'http:' ? http : https).request(options, (res) => {
      const chunks = [];
      res.on('data', (c) => chunks.push(c));
      res.on('end', () => {
        const raw = Buffer.concat(chunks).toString('utf8');
        let json = null;
        try { json = JSON.parse(raw); } catch { /* 保留 raw */ }
        if (res.statusCode !== 200) {
          const msg = (json && json.error && json.error.message) || raw.slice(0, 200) || `HTTP ${res.statusCode}`;
          return reject(new Error(`对话模型请求失败: ${msg}`));
        }
        const content = json && json.choices && json.choices[0] && json.choices[0].message && json.choices[0].message.content;
        if (!content) return reject(new Error('对话模型返回为空'));
        resolve(content);
      });
    });
    req.on('error', reject);
    req.on('timeout', () => req.destroy(new Error('对话模型请求超时')));
    req.write(body);
    req.end();
  });
}

/** 从模型输出中稳健地提取第一个 JSON 对象 */
function extractJson(text) {
  const fenced = text.match(/```(?:json)?\s*([\s\S]*?)```/);
  const candidate = fenced ? fenced[1] : text;
  const start = candidate.indexOf('{');
  const end = candidate.lastIndexOf('}');
  if (start === -1 || end <= start) throw new Error('模型未返回 JSON');
  return JSON.parse(candidate.slice(start, end + 1));
}

// ---------- Prompt 构造 ----------

function semanticModelText() {
  const { REPORT_COLUMNS } = require('./napos-service.js');
  return REPORT_COLUMNS.filter((c) => c.key !== 'date' && c.key !== 'shopName').map((c) => {
    const syn = (c.synonyms || []).join('/');
    const desc = c.description || '';
    return `- ${c.key} | ${c.label} | 同义词:${syn} | ${desc}${c.reserved ? ' | ⚠️预留列,当前无数据,不要用于查询' : ''}`;
  }).join('\n');
}

function buildMessages({ q, entity, shops, dateRange, history }) {
  const shopLines = (shops || []).slice(0, 300)
    .map((s) => `${s.shopId}|${s.shopName}`).join('\n');
  const system = `你是「淘宝闪购门店经营数据」的问数助手。把用户的自然语言问题翻译成一个结构化查询 JSON。

## 今天日期
${ymdLocal(new Date())}（YYYYMMDD）。数据可用区间：${dateRange.from} ~ ${dateRange.to}（区间外无数据；当天未结算，查询截止日默认取昨天）。

## 指标语义（key | 名称 | 同义词 | 口径）
${semanticModelText()}

## 门店清单（shopId|shopName）
${shopLines || '（暂无门店清单，shopNames 留空）'}

## 输出格式（只输出一个 JSON 对象，不要输出其他文字）
{
  "mode": "detail|shop|date",        // detail=按日期×门店明细; shop=按门店汇总; date=按日期汇总。问"每家/各门店/排行/Top"用 shop；问"每天/趋势/最近几天"用 date；问具体某天各门店用 detail
  "from": "YYYYMMDD",                // 缺省 = 昨天-29
  "to": "YYYYMMDD",                  // 缺省 = 昨天
  "shopNames": ["门店名"],           // 可选。用户指定门店时给名称（支持部分匹配）；没指定给 []
  "metrics": ["grossAmt"],           // 展示的指标 key 数组，1~4 个，必须来自上面的指标语义；用户说"营业额和单量"就是 ["grossAmt","validOrderCnt"]
  "sort": "grossAmt",                // 排序指标 key，可选；shop 模式默认营业额降序
  "sortDir": "desc",
  "limit": 10,                       // 可选，shop 模式 Top N；用户问"全部/所有"给 0 表示不限
  "chart": { "type": "bar|line|none", "x": "shopName|date", "y": ["grossAmt"] },  // 推荐图表：mode=shop→bar(x=shopName)，mode=date→line(x=date)，用户只是问一个数字→none
  "chartTitle": "图表标题"
}

## 规则
- 对话可能带上下文（之前的问答）：用户说"那它呢/换成二店/再看单量"时，结合上一轮的门店与指标补全本轮查询
- 指标同义词要对齐：如"流水/GMV/销售额"→grossAmt，"单量"→validOrderCnt，"进店率"→clkExpRate，"访购率"→ordClkRate
- "上月/最近30天/本周"等要换算成具体 from/to，且不晚于 ${dateRange.to}
- 预留列（营业时长/异常关店时长/商品销售额）没有数据：用户问到时 metrics 仍可带上（展示为空），无需额外说明
- 除非用户明确要全部门店，shop 模式默认 limit 10`;
  const msgs = [{ role: 'system', content: system }];
  for (const h of (Array.isArray(history) ? history : []).slice(-8)) {
    if (!h || !h.content) continue;
    if (h.role !== 'user' && h.role !== 'assistant') continue;
    msgs.push({ role: h.role, content: String(h.content).slice(0, 600) });
  }
  msgs.push({ role: 'user', content: String(q || '').slice(0, 500) });
  return msgs;
}

// ---------- 查询执行 ----------

function matchShopsByName(shops, names) {
  if (!Array.isArray(names) || !names.length) return null;
  const picked = new Set();
  for (const raw of names) {
    const want = String(raw || '').trim().toLowerCase();
    if (!want) continue;
    let hit = (shops || []).find((s) => String(s.shopName).toLowerCase() === want);
    if (!hit) hit = (shops || []).find((s) => String(s.shopName).toLowerCase().includes(want));
    if (hit) picked.add(String(hit.shopId));
  }
  return picked.size ? [...picked] : null;
}

function sanitizeQuery(raw, shops, dateRange) {
  const q = raw || {};
  const mode = ['detail', 'shop', 'date'].includes(q.mode) ? q.mode : 'shop';
  const from = normDay(q.from) || daysAgoYmd(30);
  const to = normDay(q.to) || daysAgoYmd(1);
  const clamped = {
    mode,
    from: from < dateRange.from ? dateRange.from : from,
    to: to > dateRange.to ? dateRange.to : (to > from ? to : dateRange.to),
    shopIds: matchShopsByName(shops, q.shopNames),
    metrics: (Array.isArray(q.metrics) ? q.metrics : ['grossAmt'])
      .map(String).filter((k) => COLUMN_KEYS.has(k)).slice(0, 4),
    sort: COLUMN_KEYS.has(q.sort) ? q.sort : 'grossAmt',
    sortDir: q.sortDir === 'asc' ? 'asc' : 'desc',
    limit: Number(q.limit) > 0 ? Math.min(Number(q.limit), 200) : 0,
    chart: q.chart && ['bar', 'line', 'none'].includes(q.chart.type)
      ? { type: q.chart.type, x: q.chart.x === 'date' ? 'date' : 'shopName', y: (Array.isArray(q.chart.y) ? q.chart.y : ['grossAmt']).filter((k) => COLUMN_KEYS.has(k)).slice(0, 2) }
      : { type: mode === 'date' ? 'line' : 'bar', x: mode === 'date' ? 'date' : 'shopName', y: ['grossAmt'] },
    chartTitle: String(q.chartTitle || '').slice(0, 60),
  };
  if (!clamped.metrics.length) clamped.metrics = ['grossAmt'];
  if (!clamped.chart.y.length) clamped.chart.y = [clamped.metrics[0]];
  return clamped;
}

/** 执行 sanitize 后的查询，返回面向展示的 rows/columns/summary */
function executeQuery(entityKey, query, svc) {
  const common = { entity: entityKey, from: query.from, to: query.to };
  let result;
  if (query.mode === 'detail') {
    result = svc.queryReport({ ...common, scope: 'shop', shops: query.shopIds || undefined });
  } else {
    result = svc.aggregateReport({ ...common, group: query.mode === 'date' ? 'date' : 'shop' });
    if (query.shopIds && query.mode === 'shop') {
      const wanted = new Set(query.shopIds.map(String));
      result.rows = result.rows.filter((r) => wanted.has(String(r.shopId)));
    }
  }
  let rows = result.rows || [];
  const sortKey = query.sort;
  rows = rows.slice().sort((a, b) => {
    const av = a[sortKey], bv = b[sortKey];
    const an = Number(av), bn = Number(bv);
    if (Number.isFinite(an) && Number.isFinite(bn)) return query.sortDir === 'asc' ? an - bn : bn - an;
    return String(av || '').localeCompare(String(bv || ''), 'zh-CN') * (query.sortDir === 'asc' ? 1 : -1);
  });
  if (query.limit > 0) rows = rows.slice(0, query.limit);
  return {
    columns: (svc.REPORT_COLUMNS || []).filter((c) => ['date', 'shopName'].includes(c.key) || query.metrics.includes(c.key)),
    rows,
    summary: result.summary,
    applied: {
      mode: query.mode, from: query.from, to: query.to,
      shopIds: query.shopIds || [], metrics: query.metrics,
      sort: query.sort, sortDir: query.sortDir, limit: query.limit,
    },
  };
}

// ---------- 第二段：结果结论（少量聚合数据回传模型写结论句） ----------

const MODE_LABEL = { shop: '按门店汇总', date: '按日期汇总', detail: '日期×门店明细' };
const ymdDash = (v) => String(v || '').replace(/^(\d{4})(\d{2})(\d{2})$/, '$1-$2-$3');

function colRegistry() { return require('./napos-service.js').REPORT_COLUMNS; }

/** 与前端报表一致的数值格式化（pct 存小数显示乘 100） */
function fmtVal(type, v) {
  const n = Number(v);
  if (v == null || v === '' || !Number.isFinite(n)) return '无数据';
  if (type === 'pct') return `${(n * 100).toFixed(2)}%`;
  if (type === 'money') return `${n.toLocaleString('zh-CN', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}元`;
  if (type === 'int') return n.toLocaleString('zh-CN', { maximumFractionDigits: 0 });
  return n.toFixed(2);
}

/** 把执行结果压缩成给模型看的摘要（纯函数，smoke 直接回归）：
 *  口径行 + Top≤10 行（店名/日期 × 所选指标）+ 区间合计/均值 + 空结果说明。 */
function buildResultDigest(query, result) {
  const reg = new Map(colRegistry().map((c) => [c.key, c]));
  const label = (k) => (reg.get(k) || {}).label || k;
  const type = (k) => (reg.get(k) || {}).type || 'num';
  const lines = [];
  lines.push(`口径：${MODE_LABEL[query.mode] || query.mode}，区间 ${ymdDash(query.from)} ~ ${ymdDash(query.to)}`
    + (query.shopIds && query.shopIds.length ? `，已限定 ${query.shopIds.length} 家门店` : '')
    + `，指标：${query.metrics.map(label).join('、')}`);
  const rows = (result.rows || []).slice(0, 10);
  for (const r of rows) {
    const name = query.mode === 'date' ? ymdDash(r.date)
      : query.mode === 'shop' ? String(r.shopName || r.shopId || '')
      : `${ymdDash(r.date)} ${r.shopName || ''}`;
    lines.push(`${name}：${query.metrics.map((k) => `${label(k)} ${fmtVal(type(k), r[k])}`).join('，')}`);
  }
  if ((result.rows || []).length > 10) lines.push(`（其余 ${result.rows.length - 10} 行略）`);
  const s = result.summary || {};
  const totals = query.metrics
    .map((k) => `${label(k)}${type(k) === 'pct' ? '均值' : '合计'} ${fmtVal(type(k), s[k])}`)
    .filter((t) => !t.endsWith('无数据'));
  if (totals.length) lines.push(`区间合计：${totals.join('，')}`);
  if (!rows.length) lines.push('（查询结果为空：区间内可能没有数据）');
  return lines.join('\n');
}

/** 第二段失败时的兜底话术（不引用数字，保证接口形状不变） */
function fallbackReply(query) {
  const reg = new Map(colRegistry().map((c) => [c.key, c]));
  const labels = query.metrics.map((k) => (reg.get(k) || {}).label || k).join('、');
  return `已查询 ${ymdDash(query.from)} ~ ${ymdDash(query.to)} 的${labels}数据（${MODE_LABEL[query.mode] || query.mode}），详细结果见下方数据与图表。`;
}

/** 第二段：digest 回传模型写一句带真实数字的结论；任何失败降级为模板句 */
async function conclude({ q, cfg, query, result }) {
  try {
    const out = await chatCompletion({
      apiBase: cfg.chatApiBase, apiKey: cfg.chatApiKey, model: cfg.chatModel,
      messages: [
        { role: 'system', content: '你是数据结论助手。只依据用户提供的查询结果数据，用中文一两句话直接回答用户的问题：优先给出关键数字（大金额可用"万元"表述），点出最大值/最小值或明显变化；数据为空或全为 0 时如实说明；禁止编造、推算或引用数据之外的信息。' },
        { role: 'user', content: `问题：${q}\n\n查询结果数据：\n${buildResultDigest(query, result)}` },
      ],
    });
    const text = String(out).trim();
    if (text) return text.slice(0, 500);
  } catch { /* 第二段失败不影响查询结果返回 */ }
  return fallbackReply(query);
}

// ---------- 对外入口 ----------

/** 湖内可用日期范围（用于 prompt 约束 + 查询钳制） */
function lakeDateRange(entityKey, svc) {
  const dates = svc.store.datesInLake(entityKey);
  if (!dates.length) {
    const to = daysAgoYmd(1);
    return { from: daysAgoYmd(30), to };
  }
  return { from: dates[0], to: dates[dates.length - 1] };
}

/**
 * 智能问数主入口。
 * @param {object} o { q, entity, cfg: {chatApiBase, chatApiKey, chatModel}, svc: napos-service }
 */
async function ask(o) {
  const { q, entity, cfg, svc } = o;
  if (!q || !String(q).trim()) { const e = new Error('请输入问题'); e.code = 'ASK_EMPTY'; throw e; }
  for (const [k, label] of [['chatApiBase', '对话模型地址'], ['chatApiKey', '对话模型 Key'], ['chatModel', '对话模型']]) {
    if (!cfg[k]) {
      const e = new Error(`对话模型未配置（缺少${label}）：请到「系统设置 → API 设置 → 对话模型」选择模型后点「保存设置」`);
      e.code = 'ASK_NO_MODEL';
      throw e;
    }
  }
  const meta = svc.store.getEntity(entity);
  const shops = (meta && meta.shops) || [];
  const dateRange = lakeDateRange(entity, svc);
  const messages = buildMessages({ q, entity, shops, dateRange, history: o.history });

  let raw;
  try {
    raw = await chatCompletion({ apiBase: cfg.chatApiBase, apiKey: cfg.chatApiKey, model: cfg.chatModel, messages });
  } catch (e) {
    const err = new Error(e.message);
    err.code = 'ASK_LLM_FAIL';
    throw err;
  }
  const parsed = extractJson(raw);
  const query = sanitizeQuery(parsed, shops, dateRange);
  const result = executeQuery(entity, query, svc);
  const reply = await conclude({ q, cfg, query, result });
  return { reply, ...result, chart: query.chart, chartTitle: query.chartTitle };
}

/** 设置页「测试对话模型」：1 token 的最小请求 */
async function ping(cfg) {
  const reply = await chatCompletion({
    apiBase: cfg.chatApiBase, apiKey: cfg.chatApiKey, model: cfg.chatModel, timeoutMs: 20000,
    messages: [{ role: 'user', content: '回复"OK"两个字母即可' }],
  });
  return String(reply).trim().slice(0, 40);
}

module.exports = { ask, ping, extractJson, sanitizeQuery, matchShopsByName, lakeDateRange, buildResultDigest, fallbackReply };