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

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