hyreal-gen-pic/ops-import.js

'use strict';

/**
 * 店铺运营数据 · 解析层
 * ----------------------------------------------------------------------------
 * 把上传的 xlsx 识别成「人工统计横表」并转成规范行：
 *   一行一店 × 29 天 × 4 指标 → 长表（每店每天一行）
 *
 * 解析器以注册表组织：以后加「平台导出 124 列」只需追加一个 { kind, detect, parse }，
 * 存储与界面不用改。
 */

const { readXlsx } = require('./server/services/xlsx.js');

const METRICS = {
  order_cnt: { label: '订单量', kind: 'int' },
  revenue: { label: '营收', kind: 'money' },
  exposure: { label: '曝光量', kind: 'int' },
  click_rate: { label: '进店转换率', kind: 'rate' },
};
const METRIC_KEYS = Object.keys(METRICS);

// 表头里可能出现的指标写法（人工表用「转换率」，平台用「转化率」）
const METRIC_BY_LABEL = {
  '订单量': 'order_cnt',
  '营收': 'revenue',
  '营业额': 'revenue',
  '曝光量': 'exposure',
  '进店转换率': 'click_rate',
  '进店转化率': 'click_rate',
  '转换率': 'click_rate',
  '转化率': 'click_rate',
};

const BASE_COLUMNS = {
  school: ['学校名称', '学校', '校区'],
  shopName: ['店铺名称', '门店名称', '店铺', '门店'],
  shopId: ['ID', '门店ID', '店铺ID'],
  status: ['上线状态', '状态'],
};

const DATE_COLUMN_RE = /^(\d{1,2})\.(\d{1,2})(订单量|营收|营业额|曝光量|进店转换率|进店转化率|转换率|转化率)$/;

function parseError(message, code = 'OPS_PARSE_FAILED') {
  const err = new Error(message);
  err.code = code;
  return err;
}

function normalizeHeader(value) {
  return String(value == null ? '' : value).replace(/[\s\u3000]+/g, '').trim();
}

function findColumn(header, names) {
  for (let i = 0; i < header.length; i++) {
    const text = normalizeHeader(header[i]);
    if (names.includes(text)) return i;
  }
  // 兜底：包含匹配（例如「门店名称（必填）」）
  for (let i = 0; i < header.length; i++) {
    const text = normalizeHeader(header[i]);
    if (names.some((n) => text.includes(n))) return i;
  }
  return -1;
}

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

function toShopId(raw) {
  if (raw == null || raw === '') return '';
  if (typeof raw === 'number') return Number.isInteger(raw) ? String(raw) : String(raw).replace(/\.0+$/, '');
  return String(raw).trim();
}

// ---------- 值清洗 ----------

/**
 * 单元格 → 数值 + 质量标记
 * @returns {{ value: number|null, reasons: string[], rawText: string }}
 */
function cleanValue(raw, metricKey) {
  const kind = METRICS[metricKey].kind;
  if (raw === null || raw === undefined || raw === '') {
    return { value: null, reasons: ['missing'], rawText: '' };
  }
  const reasons = [];
  let text = raw;
  if (typeof text === 'string') {
    text = text.trim();
    if (text === '') return { value: null, reasons: ['missing'], rawText: String(raw) };
    if (/[％%]/.test(text)) {
      text = text.replace(/[％%]/g, '');
      reasons.push('pct-symbol');
    }
    if (/\d\.\.\d/.test(text)) {
      text = text.replace(/\.{2,}/g, '.');
      reasons.push('malformed-percent');
    }
  }
  const num = typeof text === 'number' ? text : Number(text);
  if (!Number.isFinite(num)) {
    return { value: null, reasons: [...reasons, 'non-numeric'], rawText: String(raw) };
  }

  if (kind === 'rate') {
    const percentLike = reasons.includes('pct-symbol') || reasons.includes('malformed-percent');
    let value = num;
    if (percentLike) {
      value = num / 100;
    } else if (value > 1) {
      value = value / 100;
      reasons.push('percent-scaled');
    }
    if (value > 1 || value < 0) reasons.push('out-of-range');
    return { value: Math.round(value * 1e6) / 1e6, reasons, rawText: String(raw) };
  }

  if (kind === 'int') {
    let value = Math.round(num);
    if (value < 0) {
      value = 0;
      reasons.push('negative');
    }
    return { value, reasons, rawText: String(raw) };
  }

  // money
  if (num < 0) reasons.push('negative');
  return { value: Math.round(num * 100) / 100, reasons, rawText: String(raw) };
}

// ---------- 表头 ----------

function detect(cells) {
  const header = (cells && cells[0]) || [];
  const texts = header.map(normalizeHeader);
  const metricHits = ['订单量', '营收', '营业额', '曝光量'].filter((label) => texts.some((t) => t.includes(label))).length;
  const hasRate = texts.some((t) => t.includes('转换率') || t.includes('转化率'));
  const hasDateCol = texts.some((t) => DATE_COLUMN_RE.test(t));
  return metricHits >= 2 && hasRate && hasDateCol ? 'manual-ops' : null;
}

/** 平台导出（全字段明细）：一行一店一天，含「日期 / 门店编号 / 门店名称」+ 100 多列指标 */
function detectPlatformExport(cells) {
  const header = ((cells && cells[0]) || []).map(normalizeHeader);
  if (header.length < 50) return false;
  const has = (name) => header.some((h) => h === name);
  return has('日期') && has('门店编号') && has('门店名称');
}

/** 日期归一化：2026-10-18 / 2026/10/18 / 20261018 → 20261018 */
function normalizeDate(value) {
  const text = String(value == null ? '' : value).trim();
  const m = /^(\d{4})[-/.](\d{1,2})[-/.](\d{1,2})/.exec(text);
  if (m) return m[1] + pad2(Number(m[2])) + pad2(Number(m[3]));
  const compact = text.replace(/\D/g, '');
  return /^\d{8}$/.test(compact) ? compact : '';
}

function indexOfHeader(header, name) {
  for (let i = 0; i < header.length; i++) if (normalizeHeader(header[i]) === name) return i;
  return -1;
}

/**
 * 平台导出 → 全字段行
 * @param {Array<Array<any>>} cells
 * @param {{ school?: string }} options
 */
function parsePlatformExport(cells, options = {}) {
  const header = ((cells && cells[0]) || []).map((h) => String(h == null ? '' : h).trim());
  const iDate = indexOfHeader(header, '日期');
  const iShopId = indexOfHeader(header, '门店编号');
  const iShopName = indexOfHeader(header, '门店名称');
  if (iDate < 0 || iShopId < 0 || iShopName < 0) {
    throw parseError('缺少「日期 / 门店编号 / 门店名称」列', 'OPS_FORMAT_UNSUPPORTED');
  }
  const school = String(options.school || '').trim();
  const columns = header.map((h, i) => ({ name: h, col: i })).filter((c) => c.name);

  const rows = [];
  const shops = new Map();
  const dates = new Set();
  const warnings = [];

  for (let r = 1; r < cells.length; r++) {
    const line = cells[r] || [];
    const date = normalizeDate(line[iDate]);
    const shopId = toShopId(line[iShopId]);
    const shopName = normalizeHeader(line[iShopName]);
    if (!date || !shopId) {
      const anyValue = line.some((v, i) => i !== iDate && v != null && v !== '');
      if (anyValue) warnings.push({ row: r + 1, column: date ? '门店编号' : '日期', raw: date ? shopId : String(line[iDate] || ''), reason: 'missing-key' });
      continue;
    }
    if (!shopName) {
      warnings.push({ row: r + 1, column: '门店名称', raw: '', reason: 'missing-shop-name' });
      continue;
    }

    const data = {};
    for (const c of columns) {
      const value = line[c.col];
      data[c.name] = value === undefined ? null : value;
    }

    rows.push({ date, shopId, shopName, school, data });
    dates.add(date);
    if (!shops.has(shopId)) shops.set(shopId, { key: shopId, shopId, shopName, school, status: '', kind: 'platform' });
  }

  const columnNames = columns.map((c) => c.name).filter((n) => !['日期', '门店编号', '门店名称'].includes(n));
  return { rows, shops: [...shops.values()], warnings, dates: [...dates].sort(), kind: 'platform-export', columns: columnNames };
}

/**
 * 日期列 → [{ col, date, metricKey }]
 * 年份由调用方给定；列按出现顺序递增，遇到「月份比前一列小 6 以上」视为跨年。
 */
function parseDateColumns(header, year) {
  const columns = [];
  let currentYear = Number(year);
  let prevMonth = null;
  for (let i = 0; i < header.length; i++) {
    const text = normalizeHeader(header[i]);
    const m = DATE_COLUMN_RE.exec(text);
    if (!m) continue;
    const month = Number(m[1]);
    const day = Number(m[2]);
    if (month < 1 || month > 12 || day < 1 || day > 31) continue;
    if (prevMonth !== null && month < prevMonth - 6) currentYear += 1;
    prevMonth = month;
    const metricKey = METRIC_BY_LABEL[m[3]];
    if (!metricKey) continue;
    columns.push({
      col: i,
      date: `${currentYear}${pad2(month)}${pad2(day)}`,
      metricKey,
      label: normalizeHeader(header[i]),
    });
  }
  return columns;
}

// ---------- 解析 ----------

/**
 * 人工统计横表 → 长表行
 * @param {Array<Array<any>>} cells
 * @param {{ year?: number, school?: string }} options  school 非空时覆盖表格里的「学校名称」列
 * @returns {{ rows: Array<object>, shops: Array<object>, warnings: Array<object>, dates: string[] }}
 */
function parseManualOps(cells, options = {}) {
  const year = Number(options.year) || new Date().getFullYear();
  const schoolOverride = String(options.school || '').trim();
  const header = (cells && cells[0]) || [];
  if (!header.length) throw parseError('表格为空');
  if (detect(cells) !== 'manual-ops') throw parseError('表头不符合人工统计表格式', 'OPS_FORMAT_UNSUPPORTED');

  const cols = {
    school: findColumn(header, BASE_COLUMNS.school),
    shopName: findColumn(header, BASE_COLUMNS.shopName),
    shopId: findColumn(header, BASE_COLUMNS.shopId),
    status: findColumn(header, BASE_COLUMNS.status),
  };
  if (cols.shopName < 0) throw parseError('缺少「店铺名称」列', 'OPS_FORMAT_UNSUPPORTED');

  const dateCols = parseDateColumns(header, year);
  if (!dateCols.length) throw parseError('未找到日期列（形如 9.9订单量）', 'OPS_FORMAT_UNSUPPORTED');

  const rows = [];
  const warnings = [];
  const shops = new Map();
  const dates = new Set();

  for (let r = 1; r < cells.length; r++) {
    const line = cells[r] || [];
    const shopName = normalizeHeader(line[cols.shopName]);
    if (!shopName) continue; // 占位行 / 空行

    const school = schoolOverride || (cols.school >= 0 ? normalizeHeader(line[cols.school]) : '');
    const shopId = cols.shopId >= 0 ? toShopId(line[cols.shopId]) : '';
    const status = cols.status >= 0 ? normalizeHeader(line[cols.status]) : '';
    const shopKey = shopId || `${school}|${shopName}`;

    const byDate = new Map();
    for (const dc of dateCols) {
      const cleaned = cleanValue(line[dc.col], dc.metricKey);
      for (const reason of cleaned.reasons) {
        warnings.push({
          row: r + 1, // Excel 行号（1 基）
          column: header[dc.col] == null ? '' : String(header[dc.col]),
          raw: cleaned.rawText,
          reason,
          shopName,
          date: dc.date,
        });
      }
      if (!byDate.has(dc.date)) byDate.set(dc.date, {});
      byDate.get(dc.date)[dc.metricKey] = cleaned;
    }

    if (!shops.has(shopKey)) {
      shops.set(shopKey, {
        key: shopKey,
        shopId,
        shopName,
        school,
        status,
      });
    }

    for (const [date, metrics] of byDate) {
      const values = METRIC_KEYS.map((k) => (metrics[k] ? metrics[k].value : null));
      const quality = [];
      const raw = {};
      for (const k of METRIC_KEYS) {
        const m = metrics[k];
        if (!m) continue;
        raw[k] = m.rawText;
        for (const reason of m.reasons) {
          if (reason === 'missing') continue; // 缺失不当作单元格异常，避免整表标黄
          if (!quality.includes(reason)) quality.push(reason);
        }
      }
      if (!shopId && !quality.includes('no-shop-id')) quality.push('no-shop-id');
      const row = {
        date,
        shopKey,
        shopId,
        shopName,
        school,
        status,
        order_cnt: metrics.order_cnt ? metrics.order_cnt.value : null,
        revenue: metrics.revenue ? metrics.revenue.value : null,
        exposure: metrics.exposure ? metrics.exposure.value : null,
        click_rate: metrics.click_rate ? metrics.click_rate.value : null,
        raw,
        quality,
      };
      rows.push({ row, hasValue: values.some((v) => v !== null) });
      dates.add(date);
    }
  }

  // 整行（该店全部日期）都没有任何数值 → 视为空行跳过；有数据的店保留全部日期，
  // 当天四项皆空也留一行（值全为 null），矩阵显示「–」，保证 店 × 天 结构完整。
  const kept = [];
  const shopsWithData = new Set();
  for (const item of rows) {
    if (item.hasValue) shopsWithData.add(item.row.shopKey);
  }
  for (const item of rows) {
    if (!shopsWithData.has(item.row.shopKey)) continue;
    kept.push(item.row);
  }

  return {
    rows: kept,
    shops: [...shops.values()].filter((s) => shopsWithData.has(s.key)),
    warnings,
    dates: [...dates].sort(),
  };
}

/** 一步到位：Buffer → 解析结果 */
function parseWorkbook(buffer, options = {}) {
  const { sheets } = readXlsx(buffer);
  if (!sheets.length) throw parseError('未找到工作表', 'OPS_FORMAT_UNSUPPORTED');
  return parseManualOps(sheets[0].rows, options);
}

/** 解析器注册表（后续加平台导出格式时在此追加） */
const PARSERS = [
  { kind: 'manual-ops', detect, parse: parseManualOps },
];

function detectKind(cells) {
  if (detect(cells) === 'manual-ops') return 'manual-ops';
  if (detectPlatformExport(cells)) return 'platform-export';
  return null;
}

function detectBuffer(buffer) {
  const { sheets } = readXlsx(buffer);
  const cells = sheets.length ? sheets[0].rows : [];
  return detectKind(cells);
}

/** 一步到位（按格式分流）：Buffer → { kind, rows, shops, warnings, dates } */
function parseAny(buffer, options = {}) {
  const { sheets } = readXlsx(buffer);
  const cells = sheets.length ? sheets[0].rows : [];
  const kind = detectKind(cells);
  if (kind === 'manual-ops') return { kind, ...parseManualOps(cells, options) };
  if (kind === 'platform-export') return parsePlatformExport(cells, options);
  throw parseError('暂不支持该表格格式', 'OPS_FORMAT_UNSUPPORTED');
}

module.exports = {
  METRICS,
  METRIC_KEYS,
  PARSERS,
  detect,
  detectPlatformExport,
  detectKind,
  parseAny,
  parsePlatformExport,
  detectBuffer,
  parseManualOps,
  parseWorkbook,
  cleanValue,
  parseDateColumns,
};