hyreal-gen-pic/scripts/test-ops-import.js

#!/usr/bin/env node
'use strict';

// 店铺运营数据：解析器单测 + 端到端接口测试
// 全部本地跑：临时 SQLite 文件 + 随机端口，不依赖网络与云端数据库。
// 用法：npm run test:ops

const { spawn } = require('child_process');
const net = require('net');
const fs = require('fs');
const os = require('os');
const path = require('path');
const crypto = require('crypto');

const ROOT = path.resolve(__dirname, '..');
const FIXTURE = path.join(__dirname, 'fixtures', 'ops-manual-sample.xlsx');
const PLATFORM_FIXTURE = path.join(__dirname, 'fixtures', 'platform-export-sample.xlsx');
const PLATFORM_VARIANT = path.join(__dirname, 'fixtures', 'platform-export-variant.xlsx');
const importer = require(path.join(ROOT, 'ops-import.js'));

const results = [];
function expect(cond, msg) {
  if (!cond) throw new Error(msg);
}
async function check(name, fn) {
  try {
    await fn();
    results.push({ name, ok: true });
  } catch (e) {
    results.push({ name, ok: false, error: e.message || String(e) });
  }
}

function pickPort() {
  return new Promise((resolve, reject) => {
    const srv = net.createServer();
    srv.unref();
    srv.on('error', reject);
    srv.listen(0, '127.0.0.1', () => {
      const port = srv.address().port;
      srv.close(() => resolve(port));
    });
  });
}

async function waitForHealth(port, timeoutMs = 20000) {
  const start = Date.now();
  for (;;) {
    try {
      const res = await fetch(`http://127.0.0.1:${port}/healthz`);
      if (res.ok) return;
    } catch { /* 还没起来 */ }
    if (Date.now() - start > timeoutMs) throw new Error('服务未在超时内启动');
    await new Promise((r) => setTimeout(r, 300));
  }
}

async function readJson(res) {
  const text = await res.text();
  try {
    return { status: res.status, body: JSON.parse(text) };
  } catch {
    return { status: res.status, body: { raw: text } };
  }
}

async function main() {
  const fixture = fs.readFileSync(FIXTURE);
  const platformFixture = fs.readFileSync(PLATFORM_FIXTURE);
  const platformVariant = fs.readFileSync(PLATFORM_VARIANT);
  const fixtureSha = crypto.createHash('sha256').update(fixture).digest('hex');

  // ---------- 解析器单测 ----------

  await check('cleanValue: 空值 → null + missing', () => {
    const r = importer.cleanValue(null, 'order_cnt');
    expect(r.value === null, 'value 应为 null');
    expect(r.reasons.includes('missing'), '应标记 missing');
  });

  await check('cleanValue: 0 与 null 可区分', () => {
    const zero = importer.cleanValue(0, 'order_cnt');
    expect(zero.value === 0, '0 应保留为 0');
    expect(zero.reasons.length === 0, '0 不应有质量标记');
    const empty = importer.cleanValue('', 'order_cnt');
    expect(empty.value === null, '空串应为 null');
  });

  await check('cleanValue: 10..6％ → 0.106 + malformed-percent', () => {
    const r = importer.cleanValue('10..6％', 'click_rate');
    expect(Math.abs(r.value - 0.106) < 1e-9, `实际 ${r.value}`);
    expect(r.reasons.includes('malformed-percent'), '应标记 malformed-percent');
  });

  await check('cleanValue: 12.3 → 0.123 + percent-scaled', () => {
    const r = importer.cleanValue(12.3, 'click_rate');
    expect(Math.abs(r.value - 0.123) < 1e-9, `实际 ${r.value}`);
    expect(r.reasons.includes('percent-scaled'), '应标记 percent-scaled');
  });

  await check('cleanValue: 未开放 → null + non-numeric', () => {
    const r = importer.cleanValue('未开放', 'order_cnt');
    expect(r.value === null, 'value 应为 null');
    expect(r.reasons.includes('non-numeric'), '应标记 non-numeric');
    expect(r.rawText === '未开放', '应保留原文');
  });

  await check('parseDateColumns: 9.9 + year 2026 → 20260909', () => {
    const cols = importer.parseDateColumns(['9.9订单量', '10.7营收'], 2026);
    expect(cols.length === 2, `列数 ${cols.length}`);
    expect(cols[0].date === '20260909', cols[0].date);
    expect(cols[1].date === '20261007', cols[1].date);
  });

  await check('parseDateColumns: 跨年 12.31 → 1.1 年 +1', () => {
    const cols = importer.parseDateColumns(['12.31订单量', '1.1订单量'], 2025);
    expect(cols[0].date === '20251231', cols[0].date);
    expect(cols[1].date === '20260101', cols[1].date);
  });

  await check('detect: fixture 识别为 manual-ops', () => {
    expect(importer.detectBuffer(fixture) === 'manual-ops', '未识别');
  });

  const parsed = importer.parseWorkbook(fixture, { year: 2026 });
  await check('fixture 解析行数 = 36 店 × 29 天', () => {
    expect(parsed.rows.length === 1044, `实际 ${parsed.rows.length}`);
    expect(parsed.shops.length === 36, `shops ${parsed.shops.length}`);
    expect(parsed.dates.length === 29, `dates ${parsed.dates.length}`);
    expect(parsed.dates[0] === '20260909' && parsed.dates[28] === '20261007', '日期范围');
  });

  await check('fixture 脏值已清洗', () => {
    const dirty = parsed.rows.find((r) => (r.raw.click_rate || '').includes('10..6'));
    expect(dirty, '找不到 10..6％ 的行');
    expect(Math.abs(dirty.click_rate - 0.106) < 1e-9, `实际 ${dirty.click_rate}`);
    expect(dirty.quality.includes('malformed-percent'), '缺少 malformed-percent');
    const scaled = parsed.rows.find((r) => Number(r.raw.click_rate) === 12.3);
    expect(scaled && scaled.quality.includes('percent-scaled'), '12.3 未按百分数处理');
    const nonNumeric = parsed.rows.find((r) => (r.raw.order_cnt || '') === '未开放');
    expect(nonNumeric && nonNumeric.order_cnt === null, '未开放 未转为 null');
  });

  await check('fixture 无 ID 店铺用 school|shopName 兜底', () => {
    const noId = parsed.rows.filter((r) => r.quality.includes('no-shop-id'));
    expect(noId.length > 0, '应存在无 ID 店铺');
    expect(noId.every((r) => r.shopKey.includes('|')), 'shopKey 兜底格式不对');
  });

  await check('fixture 指定学校时覆盖表格里的学校列', () => {
    const forced = importer.parseWorkbook(fixture, { year: 2026, school: '统一校区' });
    expect(forced.rows.length === 1044, `rows ${forced.rows.length}`);
    expect(forced.rows.every((r) => r.school === '统一校区'), '仍有行没被覆盖');
    expect([...new Set(forced.shops.map((x) => x.school))].length === 1, '学校数应为 1');
    expect(forced.shops.every((x) => x.school === '统一校区'), '店铺注册表学校未覆盖');
  });

  await check('无「学校名称」列时 school 为空（由上传接口要求补选）', () => {
    const cells = [
      ['店铺名称', 'ID', '9.9订单量', '9.9营收', '9.9曝光量', '9.9进店转换率'],
      ['测试店', '123', 1, 2, 3, 0.1],
    ];
    const out = importer.parseManualOps(cells, { year: 2026 });
    expect(out.rows.length === 1, `rows ${out.rows.length}`);
    expect(out.rows[0].school === '', `school='${out.rows[0].school}'`);
  });

  await check('fixture 占位行未产生数据', () => {
    expect(parsed.shops.every((s) => s.shopName && s.shopName.trim()), '存在空店铺名');
  });

  // ---------- 平台导出（全字段明细） ----------

  await check('platform: 识别为 platform-export', () => {
    expect(importer.detectBuffer(platformFixture) === 'platform-export', '识别失败');
  });

  const platformParsed = importer.parseAny(platformFixture, { school: '测试校区' });
  await check('platform: 10 行 / 10 店 / 124 字段', () => {
    expect(platformParsed.kind === 'platform-export', platformParsed.kind);
    expect(platformParsed.rows.length === 10, `rows ${platformParsed.rows.length}`);
    expect(platformParsed.shops.length === 10, `shops ${platformParsed.shops.length}`);
    expect(Object.keys(platformParsed.rows[0].data).length === 124, `fields ${Object.keys(platformParsed.rows[0].data).length}`);
    expect(platformParsed.rows[0].date === '20261018', platformParsed.rows[0].date);
    expect(platformParsed.rows[0].school === '测试校区', platformParsed.rows[0].school);
  });

  await check('platform: 日期归一化', () => {
    const cells = [
      ['日期', '门店编号', '门店名称', '有效订单'],
      ['2026/1/9', '1', 'A店', 1],
      ['2026.10.18', '2', 'B店', 2],
    ];
    const r = importer.parsePlatformExport(cells, {});
    expect(r.rows[0].date === '20260109', r.rows[0].date);
    expect(r.rows[1].date === '20261018', r.rows[1].date);
  });

  // ---------- 端到端（临时 SQLite） ----------

  const tmp = fs.mkdtempSync(path.join(os.tmpdir(), 'ops-test-'));
  const dbFile = path.join(tmp, 'ops.db');
  const configPath = path.join(tmp, 'config.json');
  fs.writeFileSync(configPath, JSON.stringify({
    aitable: { enabled: false },
    storage: { driver: 'sqlite', sqlite: { file: dbFile } },
  }));
  const port = await pickPort();
  const child = spawn(process.execPath, [path.join(ROOT, 'server.js')], {
    env: { ...process.env, PORT: String(port), HOST: '127.0.0.1', GENPIC_CONFIG: configPath, GENPIC_LAKE_DIR: path.join(tmp, 'lake'), NAPOS_CDP_PORT: '1' },
    stdio: ['ignore', 'pipe', 'pipe'],
  });
  let bootLog = '';
  child.stdout.on('data', (c) => { bootLog += c; });
  child.stderr.on('data', (c) => { bootLog += c; });

  const base = `http://127.0.0.1:${port}`;
  const upload = (force) => fetch(base + '/api/ops/upload', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ fileName: '样例.xlsx', contentBase64: fixture.toString('base64'), year: 2026, force: !!force }),
  }).then(readJson);

  try {
    await waitForHealth(port);

    await check('E2E 空态 summary', async () => {
      const r = await fetch(base + '/api/ops/summary').then(readJson);
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.enabled === true, 'enabled 应为 true');
      expect(r.body.rowCount === 0, 'rowCount 应为 0');
      expect(r.body.driver === 'sqlite', `driver ${r.body.driver}`);
    });

    let uploadId = '';
    await check('E2E 上传成功', async () => {
      const r = await upload(false);
      expect(r.status === 200, `status ${r.status} ${JSON.stringify(r.body).slice(0, 200)}`);
      uploadId = r.body.uploadId;
      expect(/^\d{8}-\d{6}-[a-z0-9]{6}$/.test(uploadId), `uploadId ${uploadId}`);
      expect(r.body.report.rows === 1044, `rows ${r.body.report.rows}`);
      expect(r.body.report.shops === 36, `shops ${r.body.report.shops}`);
    });

    await check('E2E 重复上传 409', async () => {
      const r = await upload(false);
      expect(r.status === 409, `status ${r.status}`);
      expect(r.body.uploadId === uploadId, '应返回已存在的 uploadId');
    });

    await check('E2E force 覆盖导入', async () => {
      const r = await upload(true);
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.report.overwritten === 1044, `overwritten ${r.body.report.overwritten}`);
    });

    await check('E2E summary 有数据', async () => {
      const r = await fetch(base + '/api/ops/summary').then(readJson);
      expect(r.body.rowCount === 1044, `rowCount ${r.body.rowCount}`);
      expect(r.body.schools.length === 3, `schools ${r.body.schools.length}`);
      expect(r.body.coverage.from === '20260909' && r.body.coverage.to === '20261007', 'coverage');
    });

    await check('E2E 矩阵查询 + 指标过滤', async () => {
      const r = await fetch(base + '/api/ops/rows?metrics=order_cnt').then(readJson);
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.dates.length === 29, `dates ${r.body.dates.length}`);
      expect(r.body.metrics.length === 1 && r.body.metrics[0] === 'order_cnt', 'metrics 过滤失效');
      expect(r.body.groups.length === 3, `groups ${r.body.groups.length}`);
      const shop = r.body.groups[0].shops[0];
      expect(typeof shop.totals.order_cnt === 'number', 'totals 缺失');
    });

    await check('E2E 学校过滤', async () => {
      const r = await fetch(base + '/api/ops/rows?schools=' + encodeURIComponent('样例学校A')).then(readJson);
      expect(r.body.groups.length === 1, `groups ${r.body.groups.length}`);
      expect(r.body.groups[0].school === '样例学校A', r.body.groups[0].school);
    });

    await check('E2E 下载原件字节一致', async () => {
      const res = await fetch(`${base}/api/ops/uploads/${uploadId}/file`);
      expect(res.status === 200, `status ${res.status}`);
      const buf = Buffer.from(await res.arrayBuffer());
      const sha = crypto.createHash('sha256').update(buf).digest('hex');
      expect(sha === fixtureSha, 'sha256 不一致');
    });

    await check('E2E 非法 uploadId 400', async () => {
      const res = await fetch(`${base}/api/ops/uploads/${encodeURIComponent('../etc')}/file`);
      expect(res.status === 400, `status ${res.status}`);
    });

    await check('E2E 不存在的 uploadId 404', async () => {
      const res = await fetch(`${base}/api/ops/uploads/20000101-000000-abcdef/file`);
      expect(res.status === 404, `status ${res.status}`);
    });

    await check('E2E 重新解析', async () => {
      const r = await fetch(`${base}/api/ops/uploads/${uploadId}/reparse`, { method: 'POST' }).then(readJson);
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.report.rows === 1044, `rows ${r.body.report.rows}`);
      const sum = await fetch(base + '/api/ops/summary').then(readJson);
      expect(sum.body.rowCount === 1044, `rowCount ${sum.body.rowCount}`);
    });

    await check('E2E 导出 CSV', async () => {
      const res = await fetch(base + '/api/ops/export?metrics=order_cnt');
      expect(res.status === 200, `status ${res.status}`);
      const text = await res.text();
      expect(/text\/csv/.test(res.headers.get('content-type') || ''), 'content-type');
      expect(text.includes('样例学校A'), 'CSV 缺少学校列');
      expect(text.trim().split('\n').length === 37, `行数 ${text.trim().split('\n').length}`);
    });

    await check('E2E 非 xlsx 拒绝', async () => {
      const res = await fetch(base + '/api/ops/upload', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ fileName: 'a.txt', contentBase64: Buffer.from('x').toString('base64'), year: 2026 }),
      });
      expect(res.status === 400, `status ${res.status}`);
    });

    await check('E2E 坏文件 400 且不落库', async () => {
      const before = (await fetch(base + '/api/ops/uploads').then(readJson)).body.uploads.length;
      const res = await fetch(base + '/api/ops/upload', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ fileName: 'bad.xlsx', contentBase64: Buffer.from('not a zip file at all').toString('base64'), year: 2026 }),
      });
      expect(res.status === 400, `status ${res.status}`);
      const after = (await fetch(base + '/api/ops/uploads').then(readJson)).body.uploads.length;
      expect(after === before, '坏文件不应产生上传记录');
    });

    await check('E2E 上传时指定学校 → 覆盖并写入', async () => {
      const res = await fetch(base + '/api/ops/upload', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ fileName: '样例.xlsx', contentBase64: fixture.toString('base64'), year: 2026, force: true, school: '测试校区' }),
      }).then(readJson);
      expect(res.status === 200, `status ${res.status}`);
      const sum = await fetch(base + '/api/ops/summary').then(readJson);
      expect(sum.body.schools.length === 1 && sum.body.schools[0].name === '测试校区', JSON.stringify(sum.body.schools));
    });

    await check('E2E 重新解析保留指定学校', async () => {
      const list = await fetch(base + '/api/ops/uploads').then(readJson);
      const id = list.body.uploads[0].id;
      const r = await fetch(`${base}/api/ops/uploads/${id}/reparse`, { method: 'POST' }).then(readJson);
      expect(r.status === 200, `status ${r.status}`);
      const sum = await fetch(base + '/api/ops/summary').then(readJson);
      expect(sum.body.schools.length === 1 && sum.body.schools[0].name === '测试校区', JSON.stringify(sum.body.schools));
    });

    const uploadPlatform = (buf, force, school) => fetch(base + '/api/ops/upload', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        fileName: '平台导出.xlsx',
        contentBase64: buf.toString('base64'),
        year: 2026,
        force: !!force,
        ...(school === undefined ? {} : { school }),
      }),
    }).then(readJson);

    await check('E2E 平台导出：未选学校 → 400 提示补选', async () => {
      const r = await uploadPlatform(platformFixture);
      expect(r.status === 400 && r.body.code === 'OPS_SCHOOL_REQUIRED', `status ${r.status} ${JSON.stringify(r.body)}`);
    });

    await check('E2E 平台导出：首次导入 10 行', async () => {
      const r = await uploadPlatform(platformFixture, false, '测试校区');
      expect(r.status === 200, `status ${r.status} ${JSON.stringify(r.body).slice(0, 200)}`);
      expect(r.body.report.kind === 'platform-export', r.body.report.kind);
      expect(r.body.report.newRows === 10, `newRows ${r.body.report.newRows}`);
      expect(r.body.report.writtenRows === 10, `written ${r.body.report.writtenRows}`);
    });

    await check('E2E 平台导出：同文件强制重传 → 全部已存在', async () => {
      const r = await uploadPlatform(platformFixture, true, '测试校区');
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.report.newRows === 0, `newRows ${r.body.report.newRows}`);
      expect(r.body.report.identicalRows === 10, `identical ${r.body.report.identicalRows}`);
      expect(r.body.duplicateData === true, '应标记重复');
    });

    await check('E2E 平台导出：内容变化 → 冲突 1 行', async () => {
      const r = await uploadPlatform(platformVariant, false, '测试校区');
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.report.conflictRows === 1, `conflict ${r.body.report.conflictRows}`);
      expect(r.body.report.identicalRows === 9, `identical ${r.body.report.identicalRows}`);
      expect(r.body.report.writtenRows === 0, `written ${r.body.report.writtenRows}`);
    });

    await check('E2E 平台导出：强制覆盖冲突', async () => {
      const r = await uploadPlatform(platformVariant, true, '测试校区');
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.report.writtenRows === 10, `written ${r.body.report.writtenRows}`);
      const sum = await fetch(base + '/api/ops/summary').then(readJson);
      expect(sum.body.platformRowCount === 10, `platformRows ${sum.body.platformRowCount}`);
    });

    await check('E2E 导入记录只含元数据', async () => {
      const r = await fetch(base + '/api/ops/uploads').then(readJson);
      expect(r.body.uploads.length >= 1, '记录为空');
      expect(!Object.prototype.hasOwnProperty.call(r.body.uploads[0], 'content'), '不应返回 content');
    });
  } finally {
    try { child.kill('SIGKILL'); } catch { /* ignore */ }
  }

  // ---------- 存储不可用时的降级 ----------
  const badTmp = fs.mkdtempSync(path.join(os.tmpdir(), 'ops-test-bad-'));
  const badConfig = path.join(badTmp, 'config.json');
  fs.writeFileSync(badConfig, JSON.stringify({
    aitable: { enabled: false },
    storage: { driver: 'sqlite', sqlite: { file: '/dev/null/ops.db' } },
  }));
  const badPort = await pickPort();
  const badChild = spawn(process.execPath, [path.join(ROOT, 'server.js')], {
    env: { ...process.env, PORT: String(badPort), HOST: '127.0.0.1', GENPIC_CONFIG: badConfig, GENPIC_LAKE_DIR: path.join(badTmp, 'lake'), NAPOS_CDP_PORT: '1' },
    stdio: ['ignore', 'pipe', 'pipe'],
  });
  const badBase = `http://127.0.0.1:${badPort}`;
  try {
    await waitForHealth(badPort);
    await check('E2E 存储不可用时 summary 降级为 enabled:false', async () => {
      const r = await fetch(badBase + '/api/ops/summary').then(readJson);
      expect(r.status === 200, `status ${r.status}`);
      expect(r.body.enabled === false, 'enabled 应为 false');
      expect(!!r.body.reason, '缺少 reason');
    });
    await check('E2E 存储不可用时其它接口 503', async () => {
      const r = await fetch(badBase + '/api/ops/rows').then(readJson);
      expect(r.status === 503, `status ${r.status}`);
    });
    await check('E2E 存储不可用时主服务仍正常', async () => {
      const r = await fetch(badBase + '/healthz');
      expect(r.status === 200, `healthz ${r.status}`);
    });
  } finally {
    try { badChild.kill('SIGKILL'); } catch { /* ignore */ }
    try { fs.rmSync(tmp, { recursive: true, force: true }); } catch { /* ignore */ }
    try { fs.rmSync(badTmp, { recursive: true, force: true }); } catch { /* ignore */ }
  }

  // ---------- 汇总 ----------

  const failed = results.filter((r) => !r.ok);
  console.log('\n========== 店铺运营测试结果 ==========');
  for (const r of results) console.log(`${r.ok ? '✓' : '✗'} ${r.name}${r.ok ? '' : '  ← ' + r.error}`);
  console.log(`\n通过 ${results.length - failed.length}/${results.length}`);
  if (failed.length) {
    if (bootLog) console.log('\n服务启动日志（末尾 800 字）：\n' + bootLog.slice(-800));
    process.exit(1);
  }
}

main().catch((e) => {
  console.error('测试脚本自身异常:', e);
  process.exit(1);
});