hyreal-gen-pic/server/routes/ops.js

'use strict';

// 店铺运营数据：上传解析、矩阵查询、导入记录、原件下载、重新解析、CSV 导出。
// 存储由 ops-store 决定（默认本地 SQLite），此处只做协议与编排。

const crypto = require('crypto');
const { sendJson, readBody } = require('../util.js');
const opsImport = require('../../ops-import.js');
const { createOpsStore } = require('../../ops-store.js');
const { loadSchools } = require('../../schools-store.js');
const { makeChatCfg } = require('../context.js');
const { chatCompletion, extractJson } = require('../agent/llm.js');

const MAX_UPLOAD_BYTES = 10 * 1024 * 1024;
const UPLOAD_ID_RE = /^\d{8}-\d{6}-[a-z0-9]{6}$/;
const XLSX_MIME = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
const PARSER_VERSION = 1;
const BASE_FIELDS = [
  { key: 'order_cnt', label: '订单量', type: 'int', source: 'manual' },
  { key: 'revenue', label: '营收', type: 'money', source: 'manual' },
  { key: 'exposure', label: '曝光量', type: 'int', source: 'manual' },
  { key: 'click_rate', label: '进店转化率', type: 'rate', source: 'manual' },
];
const BASE_FIELD_KEYS = BASE_FIELDS.map((f) => f.key);

function pad2(n) {
  return String(n).padStart(2, '0');
}

function newUploadId() {
  const d = new Date();
  const stamp = `${d.getFullYear()}${pad2(d.getMonth() + 1)}${pad2(d.getDate())}-${pad2(d.getHours())}${pad2(d.getMinutes())}${pad2(d.getSeconds())}`;
  return `${stamp}-${crypto.randomBytes(3).toString('hex')}`;
}

function sanitizeName(name) {
  const text = String(name || 'upload.xlsx')
    .replace(/[\\/:*?"<>|\u0000-\u001f]/g, '_')
    .trim()
    .slice(0, 120);
  return text || 'upload.xlsx';
}

function splitList(value) {
  return String(value || '')
    .split(',')
    .map((s) => s.trim())
    .filter(Boolean);
}

function parseFieldKeys(value) {
  return splitList(value);
}

function parseYmd(value) {
  const text = String(value || '').replace(/-/g, '');
  return /^\d{8}$/.test(text) ? text : '';
}

/** 存储不可用时的统一响应（summary 例外，它要返回 200 + enabled:false） */
async function requireHealthy(store, res) {
  const health = await store.health();
  if (health.ok) return true;
  sendJson(res, 503, {
    error: '存储不可用',
    reason: health.reason || 'storage-unreachable',
    detail: health.detail || '',
  });
  return false;
}

function normalizeFieldValue(value, type) {
  if (value === null || value === undefined || value === '') return null;
  if (type === 'text') return String(value);
  const n = Number(value);
  if (Number.isFinite(n)) return n;
  return type === 'int' || type === 'money' || type === 'rate' ? null : String(value);
}

function fieldValue(row, field) {
  if (field.source === 'platform') {
    const data = row.platform || {};
    const raw = Object.prototype.hasOwnProperty.call(data, field.label) ? data[field.label] : null;
    return normalizeFieldValue(raw, field.type);
  }
  return normalizeFieldValue(row[field.key], field.type);
}

/** 按字段名与样本值推断展示类型 */
function inferFieldType(label, samples) {
  const name = String(label || '');
  if (/率|占比/.test(name)) return 'rate';
  if (/额|费|价|收入|支出|补贴|成本|销售额/.test(name)) return 'money';
  if (/时长|时间段|时间$|列表|名称|地址|ID$|id$/.test(name)) return 'text';
  const present = samples.filter((v) => v !== null && v !== undefined && v !== '');
  if (!present.length) return 'text';
  const numeric = present.filter((v) => typeof v === 'number' || (typeof v === 'string' && v.trim() !== '' && Number.isFinite(Number(v))));
  return numeric.length === present.length ? 'int' : 'text';
}

/** 可选字段目录：4 个基础字段 + 平台导出里的全部列 */
async function buildFieldCatalog(store) {
  const fields = BASE_FIELDS.map((f) => ({ ...f }));
  let columns = [];
  let sample = [];
  try {
    const uploads = await store.listUploads();
    const seen = new Set();
    for (const upload of uploads) {
      for (const col of ((upload.report && upload.report.columns) || [])) seen.add(col);
    }
    sample = (await store.queryPlatformRows({})).slice(0, 300);
    if (!seen.size) {
      for (const row of sample) for (const col of Object.keys(row.data || {})) seen.add(col);
    }
    columns = [...seen];
  } catch { /* 存储不可用时只有基础字段 */ }

  for (const column of columns) {
    const key = `p:${column}`;
    if (fields.some((f) => f.key === key)) continue;
    if (['日期', '门店编号', '门店名称'].includes(column)) continue;
    fields.push({
      key,
      label: column,
      type: inferFieldType(column, sample.map((row) => (row.data || {})[column])),
      source: 'platform',
    });
  }
  return fields;
}

function buildMatrix(rows, dates, fields) {
  const schools = new Map();
  for (const row of rows) {
    const schoolName = row.school || '未分组';
    if (!schools.has(schoolName)) schools.set(schoolName, new Map());
    const shopMap = schools.get(schoolName);
    if (!shopMap.has(row.shopKey)) {
      shopMap.set(row.shopKey, {
        key: row.shopKey,
        shopId: row.shopId,
        shopName: row.shopName,
        status: row.status,
        series: {},
        quality: {},
        totals: {},
      });
    }
    const shop = shopMap.get(row.shopKey);
    for (const field of fields) {
      if (!shop.series[field.key]) shop.series[field.key] = {};
      shop.series[field.key][row.date] = fieldValue(row, field);
    }
    if (Array.isArray(row.quality) && row.quality.length) shop.quality[row.date] = row.quality;
  }

  const groups = [];
  for (const [school, shopMap] of [...schools.entries()].sort((a, b) => a[0].localeCompare(b[0], 'zh-Hans-CN'))) {
    const shops = [...shopMap.values()].sort((a, b) => String(a.shopName).localeCompare(String(b.shopName), 'zh-Hans-CN'));
    for (const shop of shops) {
      for (const field of fields) {
        const series = shop.series[field.key] || {};
        if (field.type === 'int' || field.type === 'money') {
          let sum = 0;
          let seen = false;
          for (const date of dates) {
            const value = series[date];
            if (typeof value === 'number') { sum += value; seen = true; }
          }
          shop.totals[field.key] = seen ? Math.round(sum * 100) / 100 : null;
        } else if (field.type === 'rate') {
          let weight = 0;
          let acc = 0;
          const base = shop.series.exposure || {};
          for (const date of dates) {
            const rate = series[date];
            const exposure = base[date];
            if (typeof exposure === 'number' && exposure > 0 && typeof rate === 'number') {
              weight += exposure;
              acc += rate * exposure;
            }
          }
          shop.totals[field.key] = weight > 0 ? acc / weight : null;
        } else {
          shop.totals[field.key] = null;
        }
      }
    }
    groups.push({ school, shops });
  }
  return groups;
}

function numOrNull(value) {
  if (value === null || value === undefined || value === '') return null;
  const n = Number(value);
  return Number.isFinite(n) ? n : null;
}

/** 平台全字段行 → 矩阵行：4 个基础指标另存，原始 124 字段留在 platform */
function platformRowToMatrix(row) {
  const d = row.data || {};
  return {
    date: row.date,
    shopKey: String(row.shopId || ''),
    shopId: String(row.shopId || ''),
    shopName: row.shopName || '',
    school: row.school || '',
    status: '',
    order_cnt: numOrNull(d['有效订单']),
    revenue: numOrNull(d['营业额']),
    exposure: numOrNull(d['曝光人数']),
    click_rate: numOrNull(d['进店转化率']),
    platform: d,
    quality: [],
  };
}

/** 人工表 + 平台明细合并：同一天同一店，人工的 4 个指标覆盖平台，其余平台字段保留 */
async function loadMergedRows(store, { from, to, schools, shops } = {}) {
  let platform = [];
  try {
    platform = await store.queryPlatformRows({ from, to });
  } catch { /* 平台表不可用不影响人工数据 */ }
  const manual = await store.queryRows({ from, to, schools, shops });

  const map = new Map();
  for (const row of platform) map.set(`${row.date}|${row.shopId}`, platformRowToMatrix(row));
  for (const row of manual) {
    const key = `${row.date}|${row.shopKey}`;
    const base = map.get(key);
    map.set(key, { ...(base || {}), ...row, platform: base ? base.platform : null });
  }

  let rows = [...map.values()];
  if (schools && schools.length) rows = rows.filter((r) => schools.includes(String(r.school || '')));
  if (shops && shops.length) rows = rows.filter((r) => shops.includes(String(r.shopId || r.shopKey || '')));
  return rows;
}

function addDays(ymd, n) {
  const d = new Date(Date.UTC(Number(ymd.slice(0, 4)), Number(ymd.slice(4, 6)) - 1, Number(ymd.slice(6, 8))));
  d.setUTCDate(d.getUTCDate() + n);
  return `${d.getUTCFullYear()}${pad2(d.getUTCMonth() + 1)}${pad2(d.getUTCDate())}`;
}

function dayCount(from, to) {
  const a = Date.UTC(Number(from.slice(0, 4)), Number(from.slice(4, 6)) - 1, Number(from.slice(6, 8)));
  const b = Date.UTC(Number(to.slice(0, 4)), Number(to.slice(4, 6)) - 1, Number(to.slice(6, 8)));
  return Math.max(1, Math.round((b - a) / 86400000) + 1);
}

function pctDelta(cur, prev) {
  if (prev == null || prev === 0 || cur == null) return null;
  return Math.round(((cur - prev) / prev) * 1000) / 10;
}

function sumSeries(rows, key) {
  let sum = 0;
  for (const row of rows) if (typeof row[key] === 'number') sum += row[key];
  return sum;
}

function platformNum(row, column) {
  if (!row.platform) return null;
  return numOrNull(row.platform[column]);
}

/** 零单门店诊断：用平台字段区分「未开业 / 闭店 / 无曝光 / 无进店 / 无转化」 */
function diagnoseZeroOrder(row) {
  const p = row.platform;
  const orders = Number(row.order_cnt);
  if (!(orders === 0)) return null;
  if (!p) {
    return { reason: 'unknown', reasonText: '缺少平台明细，无法判断' };
  }
  const bizHours = String(p['营业时长'] == null ? '' : p['营业时长']).trim();
  const zeroBiz = /^0\s*小时\s*0\s*分$/.test(bizHours) || bizHours === '0' || bizHours === '';
  const abnormal = numOrNull(p['异常关店时长']) || 0;
  const exposure = numOrNull(p['曝光人数']);
  const visit = numOrNull(p['进店人数']);
  const validShop = String(p['是否有效门店'] || '').trim();

  if (zeroBiz) {
    if (validShop === '否') {
      return { reason: 'not-open', reasonText: '未开业（非有效门店，当日无营业时长）', bizHours, abnormal, exposure, visit, validShop };
    }
    if (abnormal > 0) {
      return { reason: 'closed', reasonText: `当日闭店（异常关店 ${abnormal} 分钟，无营业时长）`, bizHours, abnormal, exposure, visit, validShop };
    }
    return { reason: 'not-open', reasonText: '未营业（当日无营业时长，也未记录异常关店）', bizHours, abnormal, exposure, visit, validShop };
  }
  if (exposure === 0) {
    return { reason: 'no-traffic', reasonText: '营业中但无曝光（未进流量池）', bizHours, abnormal, exposure, visit, validShop };
  }
  if (visit === 0) {
    return { reason: 'no-visit', reasonText: `有曝光 ${exposure} 但无进店`, bizHours, abnormal, exposure, visit, validShop };
  }
  return { reason: 'no-conversion', reasonText: `有进店 ${visit} 但零下单（转化问题）`, bizHours, abnormal, exposure, visit, validShop };
}

/** 一次聚合：KPI 口径集中在这里，前端不再二次计算 */
function aggregateRows(rows) {
  let revenue = 0;
  let orders = 0;
  let exposure = 0;
  let visit = 0;
  let buyers = 0;
  let expense = 0;
  let subsidy = 0;
  let ratingSum = 0;
  let ratingCount = 0;
  let rateWeight = 0;
  let rateAcc = 0;

  for (const row of rows) {
    if (typeof row.revenue === 'number') revenue += row.revenue;
    if (typeof row.order_cnt === 'number') orders += row.order_cnt;
    if (typeof row.exposure === 'number') exposure += row.exposure;
    if (typeof row.click_rate === 'number' && row.exposure > 0) {
      rateWeight += row.exposure;
      rateAcc += row.click_rate * row.exposure;
    }
    const v = platformNum(row, '进店人数');
    if (v != null) visit += v;
    const b = platformNum(row, '下单人数');
    if (b != null) buyers += b;
    const e = platformNum(row, '支出');
    if (e != null) expense += e;
    const sub = platformNum(row, '活动总补贴');
    if (sub != null) subsidy += sub;
    const rating = platformNum(row, '店铺评分');
    if (rating != null && rating > 0) {
      ratingSum += rating;
      ratingCount += 1;
    }
  }

  const conversion = visit > 0 && exposure > 0
    ? visit / exposure
    : (rateWeight > 0 ? rateAcc / rateWeight : null);

  return {
    revenue: Math.round(revenue * 100) / 100,
    orders,
    exposure,
    visit,
    buyers,
    expense: Math.round(expense * 100) / 100,
    subsidy: Math.round(subsidy * 100) / 100,
    rating: ratingCount ? Math.round((ratingSum / ratingCount) * 100) / 100 : null,
    avgTicket: orders > 0 ? Math.round((revenue / orders) * 100) / 100 : null,
    conversion: conversion == null ? null : Math.round(conversion * 10000) / 10000,
  };
}

async function buildDashboard(store, { from, to, schools, shops }) {
  const filters = { schools, shops };
  let rows = await loadMergedRows(store, { from, to, ...filters });
  const dates = [...new Set(rows.map((r) => r.date))].sort();
  const start = from || dates[0];
  const end = to || dates[dates.length - 1];

  if (!start || !end) {
    return { from: null, to: null, prev: null, kpis: null, trend: [], ranking: [], funnel: null, structure: [] };
  }

  // 未指定区间时按实际数据范围收敛
  if (!from || !to) rows = await loadMergedRows(store, { from: start, to: end, ...filters });

  const span = dayCount(start, end);
  const prevTo = addDays(start, -1);
  const prevFrom = addDays(prevTo, -(span - 1));
  let prevRows = [];
  try {
    prevRows = await loadMergedRows(store, { from: prevFrom, to: prevTo, ...filters });
  } catch { /* 上期缺失不影响本期 */ }

  const cur = aggregateRows(rows);
  const prev = prevRows.length ? aggregateRows(prevRows) : null;

  const kpi = (value, prevValue, unit, isRate) => ({
    value,
    prev: prevValue,
    delta: prevValue == null || value == null
      ? null
      : (isRate ? Math.round((value - prevValue) * 1000) / 10 : pctDelta(value, prevValue)),
    unit,
  });

  const kpis = {
    revenue: kpi(cur.revenue, prev && prev.revenue, 'money'),
    orders: kpi(cur.orders, prev && prev.orders, 'int'),
    avgTicket: kpi(cur.avgTicket, prev && prev.avgTicket, 'money'),
    exposure: kpi(cur.exposure, prev && prev.exposure, 'int'),
    conversion: kpi(cur.conversion, prev && prev.conversion, 'pp', true),
    rating: { value: cur.rating, prev: null, delta: null, unit: 'num' },
  };

  const byDate = new Map();
  const byShop = new Map();
  for (const row of rows) {
    if (!byDate.has(row.date)) byDate.set(row.date, []);
    byDate.get(row.date).push(row);
    const key = String(row.shopId || row.shopKey || '');
    if (!key) continue;
    if (!byShop.has(key)) byShop.set(key, { shopId: key, shopName: row.shopName || key, school: row.school || '', rows: [] });
    byShop.get(key).rows.push(row);
  }

  const trend = [...byDate.entries()].sort((a, b) => (a[0] < b[0] ? -1 : 1)).map(([date, list]) => {
    const agg = aggregateRows(list);
    return { date, revenue: agg.revenue, orders: agg.orders };
  });

  const allShops = [...byShop.values()]
    .map((shop) => {
      const agg = aggregateRows(shop.rows);
      return { shopId: shop.shopId, shopName: shop.shopName, school: shop.school, revenue: agg.revenue, orders: agg.orders };
    })
    .sort((a, b) => b.revenue - a.revenue);
  const ranking = allShops.slice(0, 10);
  const bottom = allShops.slice(-5).reverse();

  const funnel = {
    exposure: cur.exposure,
    visit: cur.visit,
    order: cur.buyers || cur.orders,
  };

  const zeroOrder = [];
  for (const row of rows) {
    const diagnosis = diagnoseZeroOrder(row);
    if (!diagnosis) continue;
    zeroOrder.push({
      date: row.date,
      shopId: row.shopId,
      shopName: row.shopName,
      school: row.school || '',
      orders: 0,
      ...diagnosis,
    });
  }
  const zeroOrderSummary = zeroOrder.reduce((acc, item) => {
    acc[item.reason] = (acc[item.reason] || 0) + 1;
    return acc;
  }, {});

  const structure = [...byDate.entries()].sort((a, b) => (a[0] < b[0] ? -1 : 1)).map(([date, list]) => {
    const agg = aggregateRows(list);
    return { date, income: agg.revenue, expense: agg.expense, subsidy: agg.subsidy };
  });

  return {
    from: start,
    to: end,
    prev: prevRows.length ? { from: prevFrom, to: prevTo } : null,
    kpis,
    trend,
    ranking,
    bottom,
    zeroOrder,
    zeroOrderSummary,
    shopCount: allShops.length,
    funnel,
    structure,
  };
}

const INSIGHT_SYSTEM_PROMPT = [
  '你是连锁餐饮公司的经营分析师。用户会给一段 JSON，包含某段时间的经营汇总（kpis）、按日趋势（trend）、门店排行（ranking）与垫底（bottom）、转化漏斗（funnel）、收入支出结构（structure）。',
  '请用中文输出 3~5 条要点，每行一条，以「- 」开头。要求：',
  '1. 先给结论，再给依据；',
  '2. 必须引用 JSON 里的具体数字（金额保留两位小数、人数/单量用整数、比率用百分比）；',
  '3. 指出风险（下滑、零单门店、成本占比高、漏斗转化偏低等），并给一句可执行建议；',
  '4. 数据不足（如 prev 为 null、只有 1 天）就直接说明，不要编造；',
  '5. 若 zeroOrder 非空，必须用一条要点按原因归类汇总（未开业/闭店/无曝光/无进店/无转化各多少店次），并点明哪一类需要运营介入；不要笼统说「零单门店」；',
  '6. 每条不超过 60 字，不要输出 JSON、标题或前后缀。',
].join('\n');

function insightContext(data) {
  const k = (key) => (data.kpis[key] ? { value: data.kpis[key].value, delta: data.kpis[key].delta } : null);
  const rate = (a, b) => (b > 0 ? Math.round((a / b) * 1000) / 10 : null);
  const funnel = data.funnel || {};
  return {
    range: { from: data.from, to: data.to, days: data.trend.length, prev: data.prev },
    kpis: {
      revenue: k('revenue'),
      orders: k('orders'),
      avgTicket: k('avgTicket'),
      exposure: k('exposure'),
      conversion: k('conversion'),
      rating: k('rating'),
    },
    funnel: {
      exposure: funnel.exposure,
      visit: funnel.visit,
      order: funnel.order,
      visitRate: rate(funnel.visit, funnel.exposure),
      orderRate: rate(funnel.order, funnel.visit),
    },
    trend: data.trend.slice(-14),
    shopCount: data.shopCount,
    topShops: (data.ranking || []).slice(0, 5).map((s) => ({ name: s.shopName, revenue: s.revenue, orders: s.orders })),
    bottomShops: (data.bottom || []).map((s) => ({ name: s.shopName, revenue: s.revenue, orders: s.orders })),
    zeroOrderSummary: data.zeroOrderSummary || {},
    zeroOrder: (data.zeroOrder || []).slice(0, 20).map((z) => ({
      date: z.date,
      shop: z.shopName,
      reason: z.reason,
      reasonText: z.reasonText,
    })),
    structure: (data.structure || []).map((s) => ({
      date: s.date,
      income: s.income,
      expense: s.expense,
      subsidy: s.subsidy,
      expenseRatio: s.income > 0 ? Math.round((s.expense / s.income) * 1000) / 10 : null,
    })),
  };
}

function parseInsightPoints(text) {
  return String(text || '')
    .split(/\r?\n/)
    .map((line) => line.replace(/^\s*(?:[-*•]|\d+[.、)])\s*/, '').trim())
    .filter(Boolean)
    .slice(0, 6);
}

/** 由聚合结果生成 Report JSON（图文 / 交互 / PDF 三版共用；数值全部来自后端计算） */
function buildReport(data, meta = {}) {
  const { kpis, trend, ranking, bottom, zeroOrder, funnel, structure, from, to, prev, shopCount } = data;
  const money = (v) => (v == null ? null : Math.round(v * 100) / 100);
  const pct = (v) => (v == null ? null : Math.round(v * 1000) / 10);
  const findings = [];

  const rev = kpis.revenue || {};
  if (rev.delta != null) {
    findings.push({
      severity: rev.delta < -5 ? 'warning' : rev.delta > 5 ? 'positive' : 'neutral',
      finding: `营业额 ${money(rev.value)} 元，较上期 ${rev.delta > 0 ? '增长' : '下降'} ${Math.abs(rev.delta)}%`,
      evidence: [`revenue:${money(rev.value)}`, `previous:${money(rev.prev)}`],
      interpretation: rev.delta < 0 ? '规模收缩，需要结合订单量与流量拆解原因' : '规模增长，关注是否由活动或流量投放带来',
      confidence: 'high',
    });
  }

  const conv = kpis.conversion || {};
  if (conv.value != null) {
    findings.push({
      severity: conv.delta != null && conv.delta < -0.5 ? 'warning' : 'neutral',
      finding: `进店转化率 ${pct(conv.value)}%${conv.delta != null ? `，较上期 ${conv.delta > 0 ? '+' : ''}${conv.delta}pp` : ''}`,
      evidence: [`conversion:${conv.value}`],
      interpretation: '转化率反映进店后的下单意愿，需结合定价、售罄与出餐时长判断',
      confidence: 'medium',
    });
  }

  const zeroByReason = (zeroOrder || []).reduce((acc, item) => {
    acc[item.reason] = acc[item.reason] || [];
    acc[item.reason].push(item);
    return acc;
  }, {});
  const zeroTotal = (zeroOrder || []).length;
  if (zeroTotal) {
    const parts = Object.entries(zeroByReason).map(([reason, list]) => `${REASON_LABEL[reason] || reason} ${list.length} 店次`);
    findings.push({
      severity: (zeroByReason['no-conversion'] || []).length ? 'warning' : 'neutral',
      finding: `零单合计 ${zeroTotal} 店次：${parts.join('、')}`,
      evidence: ['zeroOrder'],
      interpretation: '未开业/闭店属于营业状态问题；有进店无下单才是转化问题，需要优先处理',
      confidence: 'high',
    });
  }

  if (ranking && ranking.length && rev.value > 0) {
    const share = Math.round((ranking[0].revenue / rev.value) * 1000) / 10;
    findings.push({
      severity: share > 40 ? 'warning' : 'neutral',
      finding: `头部门店「${ranking[0].shopName}」贡献营业额 ${money(ranking[0].revenue)} 元，占比 ${share}%`,
      evidence: [`top_shop:${ranking[0].shopId}`],
      interpretation: share > 40 ? '集中度偏高，单店波动会显著影响大盘' : '门店贡献相对分散',
      confidence: 'high',
    });
  }

  if (!prev) {
    findings.push({
      severity: 'neutral',
      finding: '所选区间之前没有可比数据，本期不计算环比趋势',
      evidence: ['prev:null'],
      interpretation: '只能做横向门店对比与当日诊断，不要据此判断长期趋势',
      confidence: 'high',
    });
  }

  const alerts = [];
  for (const [reason, list] of Object.entries(zeroByReason)) {
    alerts.push({
      severity: reason === 'no-conversion' ? 'P1' : 'P2',
      store_id: list[0].shopId || null,
      title: `${REASON_LABEL[reason] || reason}：${list.length} 店次`,
      evidence: list.slice(0, 5).map((z) => `${z.date} ${z.shopName}：${z.reasonText}`),
      hypotheses: reason === 'no-conversion'
        ? ['菜品/定价与周边相比缺乏竞争力', '出餐或配送时长导致流失', '活动力度不足或库存售罄']
        : ['门店未上线或当日排班缺失', '平台侧闭店/审核状态异常'],
      next_checks: reason === 'no-conversion'
        ? ['核对当日菜品售罄记录', '查看差评与退款原因', '对比同商圈同类门店客单价']
        : ['确认门店上线状态与营业时间设置', '核对平台闭店记录'],
    });
  }
  if (conv.delta != null && conv.delta < -0.5) {
    alerts.push({
      severity: 'P1', store_id: null, title: '进店转化率下滑',
      evidence: [`conversion:${conv.value}`, `previous:${conv.prev}`],
      hypotheses: ['菜单改价或活动结束', '高峰期出餐变慢', '竞品补贴'],
      next_checks: ['对比改价/活动时间', '查看出餐时长与超时单'],
    });
  }

  const actionPlan = [];
  if ((zeroByReason['no-conversion'] || []).length) {
    actionPlan.push({
      priority: 'P0',
      action: '对「有进店无下单」门店做单店诊断：菜品可售、价格带、出餐与配送时长',
      owner_role: '运营负责人',
      time_horizon: '24 小时内',
      success_metric: '次日进店→下单转化率回升',
      related_alerts: ['有进店无下单'],
    });
  }
  if ((zeroByReason['not-open'] || []).length || (zeroByReason['closed'] || []).length) {
    actionPlan.push({
      priority: 'P1',
      action: '核对未开业/闭店门店的上线状态、营业时间与异常关店记录',
      owner_role: '门店督导',
      time_horizon: '48 小时内',
      success_metric: '营业时长恢复、零单店次下降',
      related_alerts: ['未开业', '当日闭店'],
    });
  }
  if (conv.delta != null && conv.delta < -0.5) {
    actionPlan.push({
      priority: 'P1',
      action: '排查转化率下滑：活动是否结束、是否改价、是否有售罄',
      owner_role: '运营负责人',
      time_horizon: '72 小时内',
      success_metric: '转化率回到上期水平',
      related_alerts: ['进店转化率下滑'],
    });
  }
  if (prev) {
    actionPlan.push({
      priority: 'P2',
      action: '补全成本与结算口径，再评估毛利与真实利润',
      owner_role: '财务',
      time_horizon: '2 周内',
      success_metric: '可计算毛利与费用率',
      related_alerts: [],
    });
  }

  const kpiList = [
    ['revenue', '营业额', 'CNY', '平台「营业额」列求和'],
    ['orders', '有效订单', '单', '平台「有效订单」列求和'],
    ['avgTicket', '客单价', 'CNY', '营业额 ÷ 有效订单'],
    ['exposure', '曝光人数', '人', '平台「曝光人数」列求和'],
    ['conversion', '进店转化率', '%', 'Σ进店人数 ÷ Σ曝光人数'],
    ['rating', '店铺评分', '分', '有效评分门店均值（排除 0 分）'],
  ].map(([key, label, unit, definition]) => {
    const item = kpis[key] || {};
    const fmt = (value) => {
      if (value == null) return null;
      if (unit === '%') return pct(value);
      if (unit === '单' || unit === '人') return Math.round(value);
      return money(value);
    };
    return {
      key, label, unit, definition,
      current: fmt(item.value),
      previous: fmt(item.prev),
      difference: item.value != null && item.prev != null && unit !== '%'
        ? Math.round((item.value - item.prev) * 100) / 100
        : (item.value != null && item.prev != null ? Math.round((item.value - item.prev) * 1000) / 10 : null),
      change_pct: item.delta,
      status: item.delta == null ? 'unknown' : item.delta > 0 ? 'up' : item.delta < 0 ? 'down' : 'flat',
    };
  });

  return {
    meta: {
      title: meta.title || '经营分析报告',
      organization: meta.organization || '',
      period_start: formatYmd(from),
      period_end: formatYmd(to),
      generated_at: new Date().toISOString(),
      currency: 'CNY',
      data_scope: `${shopCount} 家门店 · ${trend.length} 天 · ${zeroTotal} 个零单店次`,
      source_files: meta.sourceFiles || [],
    },
    executive_summary: {
      overall_assessment: rev.delta == null
        ? '本期有经营数据，但缺少可比的上期，暂不做趋势判断；以下结论基于本期横向对比与门店诊断。'
        : `本期营业额 ${money(rev.value)} 元，较上期${rev.delta >= 0 ? '增长' : '下降'} ${Math.abs(rev.delta)}%，订单 ${kpis.orders.value} 单。`,
      key_findings: findings,
    },
    kpis: kpiList,
    sections: [
      { id: 'overview', title: '经营概览', summary: rev.delta == null ? '本期无上期对比' : `营业额环比 ${rev.delta}%`, paragraphs: [], chart_ids: ['chart_trend'], table_ids: [], evidence_keys: ['revenue', 'orders'] },
      { id: 'stores', title: '门店表现', summary: `共 ${shopCount} 家门店，Top1 为「${(ranking[0] || {}).shopName || '—'}」`, paragraphs: [], chart_ids: ['chart_rank'], table_ids: ['table_stores'], evidence_keys: ['revenue'] },
      { id: 'funnel', title: '流量与转化', summary: `曝光 ${funnel.exposure} → 进店 ${funnel.visit} → 下单 ${funnel.order}`, paragraphs: [], chart_ids: ['chart_funnel'], table_ids: [], evidence_keys: ['conversion'] },
      { id: 'finance', title: '收入与费用', summary: '仅展示平台收入、支出与补贴，不等同于利润', paragraphs: [], chart_ids: ['chart_structure'], table_ids: [], evidence_keys: ['revenue'] },
      { id: 'quality', title: '数据质量', summary: '', paragraphs: [], chart_ids: [], table_ids: [], evidence_keys: [] },
    ],
    stores: (ranking || []).slice(0, 10).map((shop, index) => ({
      store_id: shop.shopId,
      name: shop.shopName,
      rank: index + 1,
      facts: [`营业额 ${money(shop.revenue)} 元，订单 ${shop.orders} 单`],
      diagnosis: index === 0 ? '本期贡献最高，需保持供给与活动稳定' : '贡献靠后，结合流量与转化排查',
      risks: [],
      recommendations: [],
    })),
    charts: [
      { id: 'chart_trend', type: 'line_bar', title: '营业额与订单趋势', description: '柱为营业额，线为订单量；天数较少时不做平滑趋势判断。', source: '平台明细按日聚合', unit: 'CNY', data: trend },
      { id: 'chart_rank', type: 'horizontal_bar', title: '门店营业额 Top 10', description: '按所选区间营业额排序。', source: '平台明细按门店聚合', unit: 'CNY', data: ranking },
      { id: 'chart_funnel', type: 'funnel', title: '曝光 → 进店 → 下单', description: '三层人数不可跨口径混算。', source: '平台「曝光人数/进店人数/下单人数」', unit: '人', data: funnel },
      { id: 'chart_structure', type: 'bar', title: '收入与支出结构', description: '收入为营业额；支出与补贴按平台口径。', source: '平台「营业额/支出/活动总补贴」', unit: 'CNY', data: structure },
    ],
    tables: [{
      id: 'table_stores',
      title: '门店明细（Top 10）',
      columns: [
        { key: 'name', label: '门店' }, { key: 'revenue', label: '营业额' },
        { key: 'orders', label: '订单' }, { key: 'share', label: '占比' },
      ],
      rows: (ranking || []).map((shop) => ({
        name: shop.shopName, revenue: money(shop.revenue), orders: shop.orders,
        share: rev.value > 0 ? Math.round((shop.revenue / rev.value) * 1000) / 10 : null,
      })),
      footnote: '占比 = 门店营业额 ÷ 本期总营业额',
    }],
    alerts,
    action_plan: actionPlan,
    data_quality: {
      coverage: `${formatYmd(from)} ~ ${formatYmd(to)}，共 ${trend.length} 天`,
      warnings: prev ? [] : ['所选区间前无数据，环比不可用'],
      comparability: '同店对比要求门店集合一致；本期未做同店筛选',
      missing_metrics: ['成本、平台佣金明细缺失，无法计算毛利与净利'],
    },
    methodology: [
      '金额与单量均由后端按「日期+门店」明细求和，不由模型生成',
      '进店转化率 = Σ进店人数 ÷ Σ曝光人数（同层级口径）',
      '客单价 = 营业额 ÷ 有效订单，订单为 0 时为 null',
      '上期为紧邻的等长区间，比率变化用百分点（pp）',
    ],
  };
}

const REPORT_AI_PROMPT = [
  '你是连锁餐饮公司的经营分析师。用户会给一段已经计算好的经营数据 JSON（含 kpis/trend/ranking/bottom/funnel/structure/zeroOrder）。',
  '数值全部由后端计算，你不得修改、杜撰或重算；只做解释、诊断与建议。',
  '只输出一个 JSON 对象，不要代码块、不要前后缀，结构如下：',
  '{"overall_assessment":"一段客观判断",',
  ' "key_findings":[{"severity":"positive|neutral|warning|critical","finding":"结论","evidence":["指标:数值"],"interpretation":"解释","confidence":"high|medium|low"}],',
  ' "sections":[{"id":"overview|stores|funnel|finance","summary":"本节结论","paragraphs":["事实与解释"]}],',
  ' "alerts":[{"severity":"P0|P1|P2","title":"告警标题","evidence":["数据证据"],"hypotheses":["待验证原因"],"next_checks":["核查步骤"]}],',
  ' "action_plan":[{"priority":"P0|P1|P2","action":"具体行动","owner_role":"负责人角色","time_horizon":"时间窗","success_metric":"可衡量指标"}]}',
  '要求：3~5 条 findings、2~4 条 alerts、1~4 条 action；金额保留两位小数，比率用百分比；',
  'zeroOrder 非空时必须按原因归类（未开业/闭店/无曝光/无进店/无转化各多少店次）并点明哪类需要运营介入；',
  '没有上期数据时明确说明不可做趋势判断；不输出「赋能」「抓手」等套话。',
  '术语必须与指标名一致：revenue 是「营业额」（不是收入/结算收入/利润），avgTicket 是「客单价」，conversion 是「进店转化率」；日期一律写成 YYYY-MM-DD。',
].join('\n');

/** 把模型返回的叙述字段覆盖到规则版报告上；结构不合法就返回 null（保留规则版） */
function overlayAiReport(base, ai, meta = {}) {
  if (!ai || typeof ai !== 'object') return null;
  const report = JSON.parse(JSON.stringify(base));
  let touched = false;

  if (typeof ai.overall_assessment === 'string' && ai.overall_assessment.trim()) {
    report.executive_summary.overall_assessment = ai.overall_assessment.trim();
    touched = true;
  }
  if (Array.isArray(ai.key_findings) && ai.key_findings.length) {
    const findings = ai.key_findings
      .filter((f) => f && f.finding)
      .slice(0, 6)
      .map((f) => ({
        severity: ['positive', 'neutral', 'warning', 'critical'].includes(f.severity) ? f.severity : 'neutral',
        finding: String(f.finding).slice(0, 120),
        evidence: Array.isArray(f.evidence) ? f.evidence.slice(0, 5).map(String) : [],
        interpretation: f.interpretation ? String(f.interpretation).slice(0, 200) : '',
        confidence: ['high', 'medium', 'low'].includes(f.confidence) ? f.confidence : 'medium',
      }));
    if (findings.length) {
      report.executive_summary.key_findings = findings;
      touched = true;
    }
  }
  if (Array.isArray(ai.sections)) {
    const byId = new Map(report.sections.map((sec) => [sec.id, sec]));
    for (const sec of ai.sections) {
      const target = sec && byId.get(sec.id);
      if (!target) continue;
      if (sec.summary) target.summary = String(sec.summary).slice(0, 200);
      if (Array.isArray(sec.paragraphs) && sec.paragraphs.length) {
        target.paragraphs = sec.paragraphs.slice(0, 4).map((t) => String(t).slice(0, 400));
      }
      touched = true;
    }
  }
  if (Array.isArray(ai.alerts) && ai.alerts.length) {
    report.alerts = ai.alerts.slice(0, 5).map((a) => ({
      severity: ['P0', 'P1', 'P2'].includes(a.severity) ? a.severity : 'P2',
      store_id: a.store_id || null,
      title: String(a.title || '').slice(0, 80),
      evidence: Array.isArray(a.evidence) ? a.evidence.slice(0, 6).map(String) : [],
      hypotheses: Array.isArray(a.hypotheses) ? a.hypotheses.slice(0, 4).map(String) : [],
      next_checks: Array.isArray(a.next_checks) ? a.next_checks.slice(0, 4).map(String) : [],
    }));
    touched = true;
  }
  if (Array.isArray(ai.action_plan) && ai.action_plan.length) {
    report.action_plan = ai.action_plan.slice(0, 4).map((p) => ({
      priority: ['P0', 'P1', 'P2'].includes(p.priority) ? p.priority : 'P2',
      action: String(p.action || '').slice(0, 160),
      owner_role: String(p.owner_role || '').slice(0, 40),
      time_horizon: String(p.time_horizon || '').slice(0, 40),
      success_metric: String(p.success_metric || '').slice(0, 80),
      related_alerts: Array.isArray(p.related_alerts) ? p.related_alerts : [],
    }));
    touched = true;
  }
  if (!touched) return null;
  report.ai = { source: 'llm', model: meta.model || '', generatedAt: new Date().toISOString() };
  return report;
}

const REASON_LABEL = { 'not-open': '未开业', closed: '当日闭店', 'no-traffic': '无曝光', 'no-visit': '无进店', 'no-conversion': '有进店无下单', unknown: '数据不足' };

function formatYmd(ymd) {
  const s = String(ymd || '');
  return /^\d{8}$/.test(s) ? `${s.slice(0, 4)}-${s.slice(4, 6)}-${s.slice(6, 8)}` : '';
}

function csvCell(value) {
  const text = value == null ? '' : String(value);
  return /[",\n\r]/.test(text) ? '"' + text.replace(/"/g, '""') + '"' : text;
}

/** 从文件名识别已登记学校（取最长匹配，如「浙江工商桐庐校区20261018…」→ 浙江工商大学桐庐校区） */
function resolveSchoolFromFileName(fileName) {
  let best = '';
  let bestName = '';
  for (const school of loadSchools()) {
    for (const candidate of [school.name, ...(school.aliases || [])]) {
      const name = String(candidate || '').trim();
      if (name && fileName.includes(name) && name.length > bestName.length) {
        best = school.name;
        bestName = name;
      }
    }
  }
  return best;
}

function register(app, ctx) {
  const store = createOpsStore(ctx.config);

  // ---------- 上传 ----------

  app.post('/api/ops/upload', async ({ req, res }) => {
    const body = await readBody(req);
    const fileName = sanitizeName(body.fileName);
    if (!/\.xlsx$/i.test(fileName)) {
      return sendJson(res, 400, { error: '只支持 .xlsx 文件' });
    }
    const base64 = String(body.contentBase64 || '').replace(/^data:[^;]+;base64,/, '');
    if (!base64) return sendJson(res, 400, { error: '缺少文件内容' });
    let content;
    try {
      content = Buffer.from(base64, 'base64');
    } catch {
      return sendJson(res, 400, { error: 'base64 解码失败' });
    }
    if (!content.length) return sendJson(res, 400, { error: '文件内容为空' });
    if (content.length > MAX_UPLOAD_BYTES) {
      return sendJson(res, 413, { error: '文件超过 10MB 上限' });
    }
    if (!(await requireHealthy(store, res))) return;

    const sha256 = crypto.createHash('sha256').update(content).digest('hex');
    const existing = await store.findUploadBySha(sha256);
    if (!body.force) {
      if (existing) {
        return sendJson(res, 409, { error: '该文件已导入过', uploadId: existing, duplicate: true });
      }
    }

    const year = Number(body.year) || new Date().getFullYear();
    let school = String(body.school || '').trim().slice(0, 60);
    let parsed;
    try {
      parsed = opsImport.parseAny(content, { year, school });
    } catch (e) {
      return sendJson(res, 400, { error: e.message || '解析失败', code: e.code || 'OPS_PARSE_FAILED' });
    }
    if (!parsed.rows.length) {
      return sendJson(res, 400, { error: '未解析到有效数据行' });
    }

    const uploadId = newUploadId();
    const uploadedAt = new Date().toISOString();
    const from = parsed.dates[0] || '';
    const to = parsed.dates[parsed.dates.length - 1] || '';

    // ---------- 平台导出（全字段明细） ----------
    if (parsed.kind === 'platform-export') {
      // 表里没有学校列：优先用上传时选定的学校，其次从文件名识别（如「浙江工商桐庐校区20261018…」）
      if (!school) school = resolveSchoolFromFileName(fileName);
      if (!school) {
        return sendJson(res, 400, { error: '请选择学校（平台导出表里没有学校列）', code: 'OPS_SCHOOL_REQUIRED' });
      }

      const existingRows = await store.queryPlatformRows({ from, to });
      const known = new Map(existingRows.map((r) => [`${r.date}|${r.shopId}`, r]));
      let newRows = 0;
      let identicalRows = 0;
      let conflictRows = 0;
      const incoming = [];
      for (const row of parsed.rows) {
        const key = `${row.date}|${row.shopId}`;
        const old = known.get(key);
        if (!old) { newRows += 1; incoming.push(row); continue; }
        const sameData = JSON.stringify(old.data) === JSON.stringify(row.data);
        if (sameData && old.school === school) { identicalRows += 1; continue; }
        conflictRows += 1;
        if (body.force) incoming.push(row);
      }
      const toWrite = body.force ? parsed.rows : incoming;
      const report = {
        kind: 'platform-export',
        rows: parsed.rows.length,
        shops: parsed.shops.length,
        newRows,
        identicalRows,
        conflictRows,
        writtenRows: toWrite.length,
        warningCount: parsed.warnings.length,
        dates: { from, to },
        year,
        schoolOverride: school,
        parser: { kind: 'platform-export', version: PARSER_VERSION },
        columns: parsed.columns || [],
        warnings: parsed.warnings.slice(0, 500),
      };

      try {
        await store.withTransaction(async (tx) => {
          // 事务内二次校验：并发同名文件只允许一个写入
          if (!body.force && await tx.findUploadBySha(sha256)) {
            const err = new Error('duplicate');
            err.code = 'OPS_DUPLICATE';
            throw err;
          }
          if (body.force) await tx.deletePlatformRowsBySha(sha256);
          await tx.insertUpload({
            id: uploadId,
            originalName: fileName,
            sha256,
            sizeBytes: content.length,
            uploadedAt,
            year,
            parserKind: 'platform-export',
            parserVersion: PARSER_VERSION,
            report,
            content,
          });
          if (toWrite.length) {
            await tx.upsertPlatformRows(toWrite.map((row) => ({ ...row, school, uploadId })));
          }
        });
      } catch (e) {
        if (e.code === 'OPS_DUPLICATE') {
          return sendJson(res, 409, { error: '该文件已导入过', uploadId: await store.findUploadBySha(sha256), duplicate: true });
        }
        return sendJson(res, 500, { error: '写入失败', detail: e.message || String(e) });
      }

      return sendJson(res, 200, {
        ok: true,
        uploadId,
        report,
        warnings: parsed.warnings.slice(0, 200),
        duplicateData: report.newRows === 0 && report.conflictRows === 0,
      });
    }

    // ---------- 人工统计表 ----------
    // 表格里没有「学校名称」列、上传时也没指定 → 让用户补选，避免落成空白分组
    if (!parsed.shops.some((shop) => shop.school && shop.school.trim())) {
      return sendJson(res, 400, {
        error: '表格里没有「学校名称」列，请在上传时选择或填写学校',
        code: 'OPS_SCHOOL_REQUIRED',
      });
    }

    // 覆盖计数：该区间内已存在的「日期 + 店铺」组合
    let overwritten = 0;
    try {
      const existingDaily = await store.queryRows({ from, to });
      const knownDaily = new Set(existingDaily.map((r) => `${r.date}|${r.shopKey}`));
      for (const row of parsed.rows) if (knownDaily.has(`${row.date}|${row.shopKey}`)) overwritten += 1;
    } catch { /* 计数失败不影响导入 */ }

    const report = {
      kind: 'manual-ops',
      rows: parsed.rows.length,
      shops: parsed.shops.length,
      overwritten,
      warningCount: parsed.warnings.length,
      dates: { from, to },
      year,
      schoolOverride: school,
      parser: { kind: 'manual-ops', version: PARSER_VERSION },
      warnings: parsed.warnings.slice(0, 500),
    };

    try {
      await store.withTransaction(async (tx) => {
        if (!body.force && await tx.findUploadBySha(sha256)) {
          const err = new Error('duplicate');
          err.code = 'OPS_DUPLICATE';
          throw err;
        }
        if (body.force) await tx.deleteRowsBySha(sha256);
        await tx.insertUpload({
          id: uploadId,
          originalName: fileName,
          sha256,
          sizeBytes: content.length,
          uploadedAt,
          year,
          parserKind: 'manual-ops',
          parserVersion: PARSER_VERSION,
          report,
          content,
        });
        await tx.upsertRows(parsed.rows.map((row) => ({ ...row, uploadId })));
        await tx.upsertShops(parsed.shops.map((shop) => ({ ...shop, lastUploadId: uploadId })));
        await tx.pruneOrphanShops();
      });
    } catch (e) {
      if (e.code === 'OPS_DUPLICATE') {
        return sendJson(res, 409, { error: '该文件已导入过', uploadId: await store.findUploadBySha(sha256), duplicate: true });
      }
      return sendJson(res, 500, { error: '写入失败', detail: e.message || String(e) });
    }

    sendJson(res, 200, {
      ok: true,
      uploadId,
      report,
      warnings: parsed.warnings.slice(0, 200),
    });
  });

  // ---------- 概览 ----------

  app.get('/api/ops/summary', async ({ res }) => {
    const health = await store.health();
    if (!health.ok) {
      return sendJson(res, 200, {
        enabled: false,
        driver: store.kind,
        reason: health.reason || 'storage-unreachable',
        detail: health.detail || '',
      });
    }
    try {
      const data = await store.summary();
      sendJson(res, 200, { enabled: true, driver: store.kind, ...data });
    } catch (e) {
      sendJson(res, 200, {
        enabled: false,
        driver: store.kind,
        reason: 'storage-unreachable',
        detail: e.message || String(e),
      });
    }
  });

  // ---------- 矩阵查询 ----------

  app.get('/api/ops/fields', async ({ res }) => {
    if (!(await requireHealthy(store, res))) return;
    try {
      const fields = await buildFieldCatalog(store);
      sendJson(res, 200, {
        fields: fields.map(({ key, label, type, source }) => ({ key, label, type, source })),
        defaults: BASE_FIELD_KEYS,
      });
    } catch (e) {
      sendJson(res, 503, { error: '读取失败', detail: e.message || String(e) });
    }
  });

  // ---------- 统一报告生成（经营解读与经营报告共用一份 JSON） ----------
  const REPORT_CACHE_TTL_MS = 10 * 60 * 1000;
  const reportCache = new Map();

  async function getReportPayload(params, { ai = false, force = false } = {}) {
    const key = JSON.stringify(params);
    const hit = reportCache.get(key);
    const fresh = hit && Date.now() - hit.at < REPORT_CACHE_TTL_MS;
    if (!force && fresh && (!ai || hit.source === 'llm')) {
      return { ...hit.payload, cached: true };
    }

    const data = await buildDashboard(store, params);
    if (!data.kpis) return { empty: true };

    const uploads = await store.listUploads().catch(() => []);
    const base = buildReport(data, {
      organization: (params.schools && params.schools[0]) || '',
      sourceFiles: uploads.slice(0, 3).map((u) => u.originalName),
    });
    base.ai = { source: 'rules', model: '', generatedAt: new Date().toISOString() };

    let payload = base;
    let source = 'rules';
    let aiError = '';
    const chatCfg = makeChatCfg(ctx);
    const configured = chatCfg.chatApiKey && chatCfg.chatApiKey !== 'your-api-key-here';

    if (ai && configured) {
      try {
        const text = await chatCompletion({
          apiBase: chatCfg.chatApiBase,
          apiKey: chatCfg.chatApiKey,
          model: chatCfg.chatModel,
          temperature: 0.3,
          timeoutMs: 60000,
          messages: [
            { role: 'system', content: REPORT_AI_PROMPT },
            { role: 'user', content: JSON.stringify(insightContext(data)) },
          ],
        });
        const merged = overlayAiReport(base, extractJson(text), { model: chatCfg.chatModel });
        if (merged) {
          payload = merged;
          source = 'llm';
        } else {
          aiError = '模型返回结构不合法，已退回规则版';
        }
      } catch (e) {
        aiError = e.message || String(e);
      }
    } else if (ai && !configured) {
      aiError = '未配置对话模型，当前为规则版';
    }
    if (aiError) payload.aiError = aiError;

    reportCache.set(key, { at: Date.now(), source, payload });
    return { ...payload, cached: false };
  }

  // 经营解读 = 报告 JSON 的摘要视图
  app.post('/api/ops/insights', async ({ req, res }) => {
    if (!(await requireHealthy(store, res))) return;
    const body = await readBody(req);
    const params = {
      from: parseYmd(body.from) || undefined,
      to: parseYmd(body.to) || undefined,
      schools: splitList(body.schools),
      shops: splitList(body.shops),
    };
    try {
      const report = await getReportPayload(params, { ai: true, force: !!body.refresh });
      if (report.empty || !report.executive_summary) {
        return sendJson(res, 200, { enabled: true, points: [], note: '所选范围没有数据' });
      }
      const findings = report.executive_summary.key_findings || [];
      const points = findings.slice(0, 5).map((f) => f.finding);
      const alerts = (report.alerts || []).slice(0, 3).map((a) => ({ severity: a.severity, title: a.title }));
      sendJson(res, 200, {
        enabled: true,
        points,
        alerts,
        source: (report.ai && report.ai.source) || 'rules',
        model: (report.ai && report.ai.model) || '',
        generatedAt: (report.ai && report.ai.generatedAt) || new Date().toISOString(),
        aiError: report.aiError || '',
        range: { from: report.meta.period_start, to: report.meta.period_end },
        cached: !!report.cached,
      });
    } catch (e) {
      sendJson(res, 502, { enabled: true, error: '生成失败', detail: e.message || String(e) });
    }
  });

  app.get('/api/ops/report', async ({ res, query }) => {
    if (!(await requireHealthy(store, res))) return;
    const params = {
      from: parseYmd(query.from) || undefined,
      to: parseYmd(query.to) || undefined,
      schools: splitList(query.schools),
      shops: splitList(query.shops),
    };
    try {
      const report = await getReportPayload(params, { ai: query.ai === '1', force: query.refresh === '1' });
      if (report.empty) return sendJson(res, 200, { empty: true });
      sendJson(res, 200, { report });
    } catch (e) {
      sendJson(res, 503, { error: '报告生成失败', detail: e.message || String(e) });
    }
  });

  // ---------- 报告中心（保存 / 列表 / 查看 / 删除） ----------

  app.post('/api/ops/reports', async ({ req, res }) => {
    if (!(await requireHealthy(store, res))) return;
    const body = await readBody(req);
    const params = {
      from: parseYmd(body.from) || undefined,
      to: parseYmd(body.to) || undefined,
      schools: splitList(body.schools),
      shops: splitList(body.shops),
    };
    try {
      // 前端会把它当前看到的 Report JSON 一起提交，保证「保存的就是所见」
      let report = body.report;
      if (!report || !report.meta || !Array.isArray(report.kpis)) {
        report = await getReportPayload(params, { ai: false });
      }
      if (!report || report.empty) return sendJson(res, 400, { error: '所选范围没有数据，无法保存' });
      const id = `rpt_${newUploadId()}`;
      const record = {
        id,
        createdAt: new Date().toISOString(),
        periodFrom: String(report.meta.period_start || '').replace(/-/g, ''),
        periodTo: String(report.meta.period_end || '').replace(/-/g, ''),
        schools: params.schools,
        shops: params.shops,
        source: (report.ai && report.ai.source) || 'rules',
        model: (report.ai && report.ai.model) || '',
        title: report.meta.title || '经营分析报告',
        report,
      };
      await store.insertReport(record);
      sendJson(res, 200, { ok: true, id, createdAt: record.createdAt, source: record.source });
    } catch (e) {
      sendJson(res, 500, { error: '保存失败', detail: e.message || String(e) });
    }
  });

  app.get('/api/ops/reports', async ({ res }) => {
    if (!(await requireHealthy(store, res))) return;
    try {
      const reports = await store.listReports();
      // 把保存时记录的店铺 key 映射成名称，历史记录才看得懂范围
      let shopNameByKey = new Map();
      try {
        const summary = await store.summary();
        shopNameByKey = new Map((summary.shops || []).map((s) => [String(s.key || s.shopId || ''), s.shopName || '']));
      } catch { /* 映射失败时保留 key */ }
      sendJson(res, 200, {
        reports: reports.map((item) => ({
          ...item,
          schoolNames: item.schools || [],
          shopNames: (item.shops || []).map((key) => shopNameByKey.get(String(key)) || String(key)),
        })),
      });
    } catch (e) {
      sendJson(res, 503, { error: '读取失败', detail: e.message || String(e) });
    }
  });

  app.get('/api/ops/reports/:id', async ({ res, params }) => {
    if (!/^rpt_\d{8}-\d{6}-[a-z0-9]{6}$/.test(params.id)) return sendJson(res, 400, { error: '非法的报告 ID' });
    if (!(await requireHealthy(store, res))) return;
    try {
      const report = await store.getReport(params.id);
      if (!report) return sendJson(res, 404, { error: '报告不存在' });
      sendJson(res, 200, { report });
    } catch (e) {
      sendJson(res, 503, { error: '读取失败', detail: e.message || String(e) });
    }
  });

  app.del('/api/ops/reports/:id', async ({ res, params }) => {
    if (!/^rpt_\d{8}-\d{6}-[a-z0-9]{6}$/.test(params.id)) return sendJson(res, 400, { error: '非法的报告 ID' });
    if (!(await requireHealthy(store, res))) return;
    try {
      const removed = await store.deleteReport(params.id);
      sendJson(res, 200, { ok: true, removed });
    } catch (e) {
      sendJson(res, 500, { error: '删除失败', detail: e.message || String(e) });
    }
  });

  app.get('/api/ops/dashboard', async ({ res, query }) => {
    if (!(await requireHealthy(store, res))) return;
    const from = parseYmd(query.from);
    const to = parseYmd(query.to);
    const schools = splitList(query.schools);
    const shops = splitList(query.shops);
    try {
      const data = await buildDashboard(store, { from: from || undefined, to: to || undefined, schools, shops });
      sendJson(res, 200, data);
    } catch (e) {
      sendJson(res, 503, { error: '聚合失败', detail: e.message || String(e) });
    }
  });

  app.get('/api/ops/rows', async ({ res, query }) => {
    if (!(await requireHealthy(store, res))) return;
    const from = parseYmd(query.from);
    const to = parseYmd(query.to);
    const schools = splitList(query.schools);
    const shops = splitList(query.shops);
    let rows;
    try {
      rows = await loadMergedRows(store, { from: from || undefined, to: to || undefined, schools, shops });
    } catch (e) {
      return sendJson(res, 503, { error: '查询失败', detail: e.message || String(e) });
    }
    const catalog = await buildFieldCatalog(store);
    const wantedFields = parseFieldKeys(query.fields);
    let fields = wantedFields.length
      ? wantedFields.map((key) => catalog.find((f) => f.key === key)).filter(Boolean)
      : catalog.filter((f) => BASE_FIELD_KEYS.includes(f.key));
    if (!fields.length) fields = catalog.filter((f) => BASE_FIELD_KEYS.includes(f.key));

    const dates = [...new Set(rows.map((r) => r.date))].sort();
    sendJson(res, 200, {
      from: from || (dates[0] || null),
      to: to || (dates[dates.length - 1] || null),
      dates,
      fields: fields.map(({ key, label, type }) => ({ key, label, type })),
      groups: buildMatrix(rows, dates, fields),
    });
  });

  // ---------- 导入记录 ----------

  app.get('/api/ops/uploads', async ({ res }) => {
    if (!(await requireHealthy(store, res))) return;
    try {
      const uploads = await store.listUploads();
      sendJson(res, 200, { uploads });
    } catch (e) {
      sendJson(res, 503, { error: '读取失败', detail: e.message || String(e) });
    }
  });

  // ---------- 原件下载 ----------

  app.get('/api/ops/uploads/:id/file', async ({ res, params }) => {
    if (!UPLOAD_ID_RE.test(params.id)) return sendJson(res, 400, { error: '非法的上传 ID' });
    if (!(await requireHealthy(store, res))) return;
    const record = await store.getContent(params.id);
    if (!record) return sendJson(res, 404, { error: '找不到该上传记录' });
    const name = sanitizeName(record.originalName);
    res.writeHead(200, {
      'Content-Type': XLSX_MIME,
      'Content-Length': record.content.length,
      'Content-Disposition': `attachment; filename*=UTF-8''${encodeURIComponent(name)}`,
    });
    res.end(record.content);
  });

  // ---------- 重新解析 ----------

  app.post('/api/ops/uploads/:id/reparse', async ({ res, params }) => {
    if (!UPLOAD_ID_RE.test(params.id)) return sendJson(res, 400, { error: '非法的上传 ID' });
    if (!(await requireHealthy(store, res))) return;
    const record = await store.getContent(params.id);
    if (!record) return sendJson(res, 404, { error: '找不到该上传记录' });
    const upload = await store.getUpload(params.id);
    const year = (upload && upload.year) || new Date().getFullYear();
    const school = (upload && upload.report && upload.report.schoolOverride) || '';
    let parsed;
    try {
      parsed = opsImport.parseAny(record.content, { year, school });
    } catch (e) {
      return sendJson(res, 400, { error: e.message || '解析失败', code: e.code || 'OPS_PARSE_FAILED' });
    }
    const from = parsed.dates[0] || '';
    const to = parsed.dates[parsed.dates.length - 1] || '';

    if (parsed.kind === 'platform-export') {
      const report = {
        kind: 'platform-export',
        rows: parsed.rows.length,
        shops: parsed.shops.length,
        newRows: parsed.rows.length,
        identicalRows: 0,
        conflictRows: 0,
        writtenRows: parsed.rows.length,
        warningCount: parsed.warnings.length,
        dates: { from, to },
        year,
        schoolOverride: school,
        parser: { kind: 'platform-export', version: PARSER_VERSION },
        warnings: parsed.warnings.slice(0, 500),
        reparsedAt: new Date().toISOString(),
      };
      try {
        await store.withTransaction(async (tx) => {
          await tx.deletePlatformRowsByUpload(params.id);
          await tx.upsertPlatformRows(parsed.rows.map((row) => ({ ...row, school, uploadId: params.id })));
          await tx.updateReport(params.id, report);
        });
      } catch (e) {
        return sendJson(res, 500, { error: '重新解析写入失败', detail: e.message || String(e) });
      }
      return sendJson(res, 200, { ok: true, uploadId: params.id, report });
    }

    const report = {
      kind: 'manual-ops',
      rows: parsed.rows.length,
      shops: parsed.shops.length,
      overwritten: 0,
      warningCount: parsed.warnings.length,
      dates: { from, to },
      year,
      schoolOverride: school,
      parser: { kind: 'manual-ops', version: PARSER_VERSION },
      warnings: parsed.warnings.slice(0, 500),
      reparsedAt: new Date().toISOString(),
    };
    try {
      await store.withTransaction(async (tx) => {
        await tx.deleteRowsByUpload(params.id);
        await tx.upsertRows(parsed.rows.map((row) => ({ ...row, uploadId: params.id })));
        await tx.upsertShops(parsed.shops.map((shop) => ({ ...shop, lastUploadId: params.id })));
        await tx.pruneOrphanShops();
        await tx.updateReport(params.id, report);
      });
    } catch (e) {
      return sendJson(res, 500, { error: '重新解析写入失败', detail: e.message || String(e) });
    }
    sendJson(res, 200, { ok: true, uploadId: params.id, report });
  });

  // ---------- 导出 ----------

  app.get('/api/ops/export', async ({ res, query }) => {
    if (!(await requireHealthy(store, res))) return;
    const from = parseYmd(query.from);
    const to = parseYmd(query.to);
    const schools = splitList(query.schools);
    const shops = splitList(query.shops);
    let rows;
    try {
      rows = await loadMergedRows(store, { from: from || undefined, to: to || undefined, schools, shops });
    } catch (e) {
      return sendJson(res, 503, { error: '查询失败', detail: e.message || String(e) });
    }

    const catalog = await buildFieldCatalog(store);
    const wantedFields = parseFieldKeys(query.fields);
    let fields = wantedFields.length
      ? wantedFields.map((key) => catalog.find((f) => f.key === key)).filter(Boolean)
      : catalog.filter((f) => BASE_FIELD_KEYS.includes(f.key));
    if (!fields.length) fields = catalog.filter((f) => BASE_FIELD_KEYS.includes(f.key));

    const sorted = rows.slice().sort((a, b) => {
      const school = String(a.school || '').localeCompare(String(b.school || ''), 'zh-Hans-CN');
      if (school !== 0) return school;
      const shop = String(a.shopName || '').localeCompare(String(b.shopName || ''), 'zh-Hans-CN');
      if (shop !== 0) return shop;
      return a.date < b.date ? -1 : a.date > b.date ? 1 : 0;
    });

    const lines = [];
    lines.push(['学校', '店铺ID', '店铺名称', '日期', ...fields.map((f) => f.label)].map(csvCell).join(','));
    for (const row of sorted) {
      lines.push([
        row.school || '',
        row.shopId || '',
        row.shopName || '',
        row.date,
        ...fields.map((field) => {
          const value = fieldValue(row, field);
          return value == null ? '' : value;
        }),
      ].map(csvCell).join(','));
    }

    const csv = '\ufeff' + lines.join('\r\n');
    res.writeHead(200, {
      'Content-Type': 'text/csv; charset=utf-8',
      'Content-Disposition': `attachment; filename="ops-${from || 'all'}-${to || 'all'}.csv"`,
    });
    res.end(csv);
  });

}

module.exports = { register };