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 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 METRIC_KEYS = opsImport.METRIC_KEYS;
const SUM_METRICS = new Set(['order_cnt', 'revenue', 'exposure']);

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 parseMetricsParam(value) {
  const wanted = splitList(value);
  if (!wanted.length) return METRIC_KEYS.slice();
  return METRIC_KEYS.filter((k) => wanted.includes(k));
}

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 buildMatrix(rows, dates) {
  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 metric of METRIC_KEYS) {
      if (!shop.series[metric]) shop.series[metric] = {};
      shop.series[metric][row.date] = row[metric];
    }
    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 metric of METRIC_KEYS) {
        if (!SUM_METRICS.has(metric)) continue;
        let sum = 0;
        let seen = false;
        for (const date of dates) {
          const value = shop.series[metric][date];
          if (typeof value === 'number') { sum += value; seen = true; }
        }
        shop.totals[metric] = seen ? Math.round(sum * 100) / 100 : 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 个可展示指标，其余字段留在 raw） */
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['进店转化率']),
    raw: d,
    quality: [],
  };
}

/** 人工表 + 平台明细合并：同一天同一店，人工优先，平台补位 */
async function loadMergedRows(store, { from, to, schools, shops } = {}) {
  const manual = await store.queryRows({ from, to, schools, shops });
  let platform = [];
  try {
    platform = await store.queryPlatformRows({ from, to });
  } catch { /* 平台表不可用不影响人工数据 */ }
  const map = new Map();
  for (const row of manual) map.set(`${row.date}|${row.shopKey}`, row);
  for (const row of platform) {
    const key = `${row.date}|${row.shopId}`;
    if (map.has(key)) continue;
    map.set(key, platformRowToMatrix(row));
  }
  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 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 },
        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/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);
    const metrics = parseMetricsParam(query.metrics);
    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 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,
      metrics,
      groups: buildMatrix(rows, dates),
    });
  });

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

  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);
    const metrics = parseMetricsParam(query.metrics);
    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 lines = [];
    if (metrics.length === 1) {
      const metric = metrics[0];
      const dates = [...new Set(rows.map((r) => r.date))].sort();
      lines.push(['学校', '店铺ID', '店铺名称', '状态', ...dates].map(csvCell).join(','));
      const byShop = new Map();
      for (const row of rows) {
        if (!byShop.has(row.shopKey)) byShop.set(row.shopKey, { row, values: {} });
        byShop.get(row.shopKey).values[row.date] = row[metric];
      }
      for (const { row, values } of byShop.values()) {
        lines.push([row.school, row.shopId || '', row.shopName, row.status || '',
          ...dates.map((d) => (values[d] == null ? '' : values[d]))].map(csvCell).join(','));
      }
    } else {
      lines.push(['学校', '店铺ID', '店铺名称', '日期', '指标', '数值'].map(csvCell).join(','));
      for (const row of rows) {
        for (const metric of metrics) {
          const value = row[metric];
          lines.push([row.school, row.shopId || '', row.shopName, row.date, metric, 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 };