hyreal-gen-pic/server/agent/tools/lake.js

'use strict';

/**
 * 智能问数智能体 · 湖内取数与体检工具
 * ----------------------------------------------------------------------------
 * lake_overview  摸底：主体/门店/区间/行数/最近同步（循环第一步的事实基础）
 * query_report   查询：明细/按店/按日，内核复用 nl-query 的 sanitize+execute（口径同报表页）
 * data_quality   体检：全零行 / 层间一致性 / 恒等式 / 日期连续性 / 预留列 / 最近同步
 *
 * handler(ctx, args) → { summary（给模型的观察文本，纯文本无嵌套）, data（给前端的产物，行数已裁剪） }
 * ctx = { svc: napos-service, entityKey, meta, shops, dateRange }
 */

const nlQuery = require('../../../nl-query.js');

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 spanDays(from, to) {
  return Math.round((new Date(Number(to.slice(0, 4)), Number(to.slice(4, 6)) - 1, Number(to.slice(6, 8)))
    - new Date(Number(from.slice(0, 4)), Number(from.slice(4, 6)) - 1, Number(from.slice(6, 8)))) / 86400000) + 1;
}
function eachDay(from, to) {
  const out = [];
  const d = new Date(Number(from.slice(0, 4)), Number(from.slice(4, 6)) - 1, Number(from.slice(6, 8)));
  const end = to;
  for (;;) {
    const ymd = `${d.getFullYear()}${z2(d.getMonth() + 1)}${z2(d.getDate())}`;
    if (ymd > end) break;
    out.push(ymd);
    d.setDate(d.getDate() + 1);
  }
  return out;
}
function ymdDash(v) { return String(v || '').replace(/^(\d{4})(\d{2})(\d{2})$/, '$1-$2-$3'); }

/** 列注册表快捷访问：label / type */
function colMap(svc) {
  return new Map((svc.REPORT_COLUMNS || []).map((c) => [c.key, c]));
}

/** 行瘦身：raw 原始字段不进产物（60+ 字段 × 200 行太大，模型与前端都不需要） */
function pickRow(r) {
  const out = {};
  for (const k of Object.keys(r)) if (k !== 'raw') out[k] = r[k];
  return out;
}

// ---------- lake_overview ----------

function overviewSanitize(args) { return {}; }

function overviewHandler(ctx) {
  const { svc, entityKey, meta, dateRange } = ctx;
  const store = svc.store;
  const rows = store.loadRows(entityKey);
  const scopes = {};
  for (const r of rows) scopes[r.scope] = (scopes[r.scope] || 0) + 1;
  const dates = store.datesInLake(entityKey);
  const syncs = store.loadSyncLogs(entityKey);
  const last = syncs[0] || null;
  const shops = meta.shops || [];
  const lines = [];
  lines.push(`主体「${meta.name || entityKey}」：${shops.length} 家门店、${(meta.chains || []).length} 个连锁。`);
  if (dates.length) {
    lines.push(`湖内区间 ${ymdDash(dateRange.from)} ~ ${ymdDash(dateRange.to)}（${dates.length} 天有数据）。`);
  } else {
    lines.push('湖内暂无数据行：请先到「数据采集」完成一次同步，或确认已从云端拉取。');
  }
  lines.push(`行数：门店明细 ${scopes.shop || 0} / 连锁 ${scopes.chain || 0} / 公司 ${scopes.company || 0} / 区间汇总 ${scopes.summary || 0}。`);
  if (last) {
    lines.push(`最近同步 ${last.at}：${ymdDash(last.from || '')} ~ ${ymdDash(last.to || '')}，新增 ${last.added ?? 0} 更新 ${last.updated ?? 0} 失败 ${last.failed ?? 0}${last.cancelled ? '（被取消）' : ''}。`);
  } else {
    lines.push('暂无同步记录。');
  }
  return {
    summary: lines.join('\n'),
    data: {
      entity: { key: entityKey, name: meta.name || entityKey },
      chains: meta.chains || [],
      shops: shops.slice(0, 300),
      dateRange,
      datesCount: dates.length,
      scopes,
      lastSync: last ? { at: last.at, from: last.from, to: last.to, added: last.added, updated: last.updated, failed: last.failed, cancelled: !!last.cancelled } : null,
    },
  };
}

// ---------- query_report ----------

function querySanitize(args, ctx) {
  const { shops, dateRange } = ctx;
  return nlQuery.sanitizeQuery(args || {}, shops, dateRange);
}

function queryHandler(ctx, query) {
  const { svc, entityKey } = ctx;
  const result = nlQuery.executeQuery(entityKey, query, svc);
  const rows = (result.rows || []).map(pickRow);
  return {
    summary: nlQuery.buildResultDigest(query, { columns: result.columns, rows: result.rows, summary: result.summary }),
    data: {
      applied: result.applied,
      columns: (result.columns || []).map((c) => ({ key: c.key, label: c.label, type: c.type })),
      summary: result.summary,
      totalRows: rows.length,
      rows: rows.slice(0, 200),
    },
  };
}

// ---------- data_quality ----------

const IDENTITY_TOL = 0.005;   // 恒等式容差：收入+支出 与 营业额 相对差 0.5%
const CROSS_TOL = 0.005;      // 层间一致性容差：门店合计 vs 连锁/公司 相对差 0.5%
const CROSS_FAIL = 0.2;       // 层间相对差超过 20% 判 fail（典型：全零行）

function qualitySanitize(args, ctx) {
  const a = args || {};
  const { dateRange } = ctx;
  const from = /^\d{8}$/.test(String(a.from || '')) ? String(a.from) : dateRange.from;
  const to = /^\d{8}$/.test(String(a.to || '')) ? String(a.to) : dateRange.to;
  return { from: from < dateRange.from ? dateRange.from : from, to: to > dateRange.to ? dateRange.to : to };
}

function qualityHandler(ctx, args) {
  const { svc, entityKey, dateRange } = ctx;
  const store = svc.store;
  const { from, to } = args;
  const inRange = (r) => r.date >= from && r.date <= to;
  const shopRows = store.queryRows(entityKey, { from, to, scope: 'shop' });
  const chainRows = store.queryRows(entityKey, { from, to, scope: 'chain' });
  const companyRows = store.queryRows(entityKey, { from, to, scope: 'company' });
  const findings = [];
  const note = (level, check, detail) => findings.push({ level, check, detail });

  // 1) 门店明细全零行（已知 bug 形态：shop 全零而 chain 有数）
  const zeroRows = shopRows.filter((r) => !(Number(r.validOrderCnt) > 0) && !(Number(r.grossAmt) > 0));
  if (shopRows.length) {
    const pct = zeroRows.length / shopRows.length;
    const byShop = new Map();
    for (const r of zeroRows) byShop.set(r.shopName || r.shopId, (byShop.get(r.shopName || r.shopId) || 0) + 1);
    const top = [...byShop.entries()].sort((a, b) => b[1] - a[1]).slice(0, 5)
      .map(([name, n]) => `${name}×${n}`).join('、');
    const level = pct >= 0.3 ? 'fail' : (pct >= 0.05 ? 'warn' : 'ok');
    note(level, '门店明细全零行', zeroRows.length
      ? `${zeroRows.length}/${shopRows.length} 行营业额与单量全零（${(pct * 100).toFixed(1)}%），主要：${top}。疑似取数参数或数据权限异常`
      : '未发现全零行');
  }

  // 2) 层间一致性：sum(门店) vs sum(连锁) vs 公司行（按日比对营业额）
  const sumBy = (rows) => {
    const m = new Map();
    for (const r of rows) m.set(r.date, (m.get(r.date) || 0) + (Number(r.grossAmt) || 0));
    return m;
  };
  const shopSum = sumBy(shopRows);
  const chainSum = sumBy(chainRows);
  const companySum = sumBy(companyRows);
  const mismatches = [];
  const relDiff = (a, b) => Math.abs(a - b) / Math.max(Math.abs(b), 1);
  for (const [d, sv] of shopSum) {
    const cv = chainSum.get(d);
    if (cv !== undefined && relDiff(sv, cv) > CROSS_TOL) mismatches.push({ date: d, kind: '门店合计≠连锁', sv, cv });
  }
  for (const [d, cv] of chainSum) {
    const pv = companySum.get(d);
    if (pv !== undefined && relDiff(cv, pv) > CROSS_TOL) mismatches.push({ date: d, kind: '连锁合计≠公司', sv: cv, cv: pv });
  }
  if (shopSum.size || chainSum.size) {
    if (mismatches.length) {
      const worst = mismatches.slice().sort((a, b) => relDiff(b.sv, b.cv) - relDiff(a.sv, a.cv))[0];
      const level = mismatches.some((m) => relDiff(m.sv, m.cv) > CROSS_FAIL) ? 'fail' : 'warn';
      note(level, '层间一致性（营业额）', `${mismatches.length} 天层间对不齐，最差 ${ymdDash(worst.date)} ${worst.kind}：${Math.round(worst.sv)} vs ${Math.round(worst.cv)}。`);
    } else {
      note('ok', '层间一致性（营业额）', `抽查 ${Math.min(shopSum.size, chainSum.size)} 天门店/连锁/公司层间一致（容差 ${(CROSS_TOL * 100).toFixed(1)}%）。`);
    }
  }

  // 3) 恒等式：收入 + 支出 = 营业额
  const identityBad = shopRows.filter((r) => {
    const g = Number(r.grossAmt) || 0;
    const lhs = (Number(r.incomeAmt) || 0) + (Number(r.expenseAmt) || 0);
    return Math.abs(lhs - g) > Math.max(g * IDENTITY_TOL, 1);
  });
  note(identityBad.length ? 'warn' : 'ok', '恒等式 收入+支出=营业额',
    identityBad.length ? `${identityBad.length} 行不满足（示例：${ymdDash(identityBad[0].date)} ${identityBad[0].shopName || ''}）` : '全部满足');

  // 4) 日期连续性
  const dates = [...new Set(shopRows.map((r) => r.date))].sort();
  if (dates.length) {
    const expected = eachDay(dates[0], dates[dates.length - 1]);
    const missing = expected.filter((d) => !dates.includes(d));
    const pct = missing.length / expected.length;
    note(pct >= 0.3 ? 'warn' : 'info', '日期连续性',
      missing.length ? `区间内缺 ${missing.length}/${expected.length} 天（${pct >= 0.3 ? '偏多，' : ''}示例：${missing.slice(0, 5).map(ymdDash).join('、')}）。食堂休假/寒暑假属正常缺日`
        : `区间 ${ymdDash(dates[0])} ~ ${ymdDash(dates[dates.length - 1])} 逐日连续`);
  }

  // 5) 预留列（恒定无数据，属预期而非故障）
  note('info', '预留列', '营业时长/异常关店时长/商品销售额当前无数据源，恒为空（口径见语义层）');

  // 6) 最近同步
  const last = (store.loadSyncLogs(entityKey)[0]) || null;
  if (!last) note('info', '最近同步', '无同步记录');
  else {
    const failed = last.failed || 0;
    const warned = (last.steps || []).filter((s) => s && s.warn).length;
    note(failed > 0 ? 'warn' : (warned > 0 ? 'warn' : 'ok'), '最近同步',
      `${last.at} ${failed ? `失败 ${failed} 步` : ''}${warned ? `告警 ${warned} 步（含全零）` : ''}${!failed && !warned ? '正常完成' : ''}`);
  }

  const lines = findings.map((f) => `[${f.level}] ${f.check}：${f.detail}`);
  const rangeLine = `体检范围 ${ymdDash(from)} ~ ${ymdDash(to)}（湖内 ${ymdDash(dateRange.from)} ~ ${ymdDash(dateRange.to)}）：`;
  return {
    summary: rangeLine + '\n' + lines.join('\n'),
    data: {
      from, to, findings,
      verdicts: findings.map((f) => ({
        kind: 'quality', check: f.check,
        status: f.level === 'fail' ? 'fail' : (f.level === 'warn' ? 'warn' : 'ok'),
        detail: f.detail,
      })),
    },
  };
}

module.exports = {
  overviewSanitize, overviewHandler,
  querySanitize, queryHandler,
  qualitySanitize, qualityHandler,
  pickRow, colMap, spanDays, eachDay, ymdDash, daysAgoYmd,
};