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 } = 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[].reasonText 的具体原因（未开业/闭店/无曝光/无进店/无转化），不要笼统说「零单门店」；',
  '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);
}

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.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) {
        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.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) {
      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) });
    }
  });

  const insightCache = new Map();
  const INSIGHT_TTL_MS = 10 * 60 * 1000;

  app.post('/api/ops/insights', async ({ req, res }) => {
    if (!(await requireHealthy(store, res))) return;
    const chatCfg = makeChatCfg(ctx);
    if (!chatCfg.chatApiKey || chatCfg.chatApiKey === 'your-api-key-here') {
      return sendJson(res, 200, { enabled: false, reason: 'chat-not-configured' });
    }
    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),
    };
    const cacheKey = JSON.stringify(params);
    const cached = insightCache.get(cacheKey);
    if (!body.refresh && cached && Date.now() - cached.at < INSIGHT_TTL_MS) {
      return sendJson(res, 200, { enabled: true, ...cached.value, cached: true });
    }

    let data;
    try {
      data = await buildDashboard(store, params);
    } catch (e) {
      return sendJson(res, 503, { error: '聚合失败', detail: e.message || String(e) });
    }
    if (!data.kpis) {
      return sendJson(res, 200, { enabled: true, points: [], note: '所选范围没有数据' });
    }

    const context = insightContext(data);
    let text;
    try {
      text = await chatCompletion({
        apiBase: chatCfg.chatApiBase,
        apiKey: chatCfg.chatApiKey,
        model: chatCfg.chatModel,
        temperature: 0.3,
        timeoutMs: 45000,
        messages: [
          { role: 'system', content: INSIGHT_SYSTEM_PROMPT },
          { role: 'user', content: JSON.stringify(context) },
        ],
      });
    } catch (e) {
      return sendJson(res, 502, { enabled: true, error: '模型调用失败', detail: e.message || String(e) });
    }

    const value = {
      points: parseInsightPoints(text),
      model: chatCfg.chatModel,
      generatedAt: new Date().toISOString(),
      digest: context,
    };
    insightCache.set(cacheKey, { at: Date.now(), value });
    sendJson(res, 200, { enabled: true, ...value });
  });

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