Saas-voc/tools/import-demashi.mjs

import assert from "node:assert/strict";
import crypto from "node:crypto";
import fs from "node:fs/promises";
import path from "node:path";
import process from "node:process";
import XLSX from "xlsx";

const DEFAULT_OUTPUT = "src/assets/data/demashi-summary.json";
const METRIC_SHEET = "德玛仕产品基础数据";
const RELATION_SHEET = "竟对品牌及编码";

const args = parseArgs(process.argv.slice(2));
if (!args.input) {
  console.error("Usage: npm run import:demashi -- --input <workbook.xlsx> [--output <file>] [--verify-demashi]");
  process.exit(1);
}

const inputPath = path.resolve(args.input);
const outputPath = path.resolve(args.output || DEFAULT_OUTPUT);
const fileBuffer = await fs.readFile(inputPath);
const workbook = XLSX.read(fileBuffer, { cellDates: true, raw: true });
const metricSheet = requireSheet(workbook, METRIC_SHEET);
const relationSheet = requireSheet(workbook, RELATION_SHEET);

const metricRows = XLSX.utils.sheet_to_json(metricSheet, { header: 1, defval: null, raw: true });
const relationRows = XLSX.utils.sheet_to_json(relationSheet, { header: 1, defval: null, raw: true });
const normalized = normalizeWorkbook(metricRows, relationRows, {
  sourceFile: path.basename(inputPath),
  sourceHash: crypto.createHash("sha256").update(fileBuffer).digest("hex"),
});

if (args.verifyDemashi) verifyDemashi(normalized);

await fs.mkdir(path.dirname(outputPath), { recursive: true });
await fs.writeFile(outputPath, `${JSON.stringify(normalized, null, 2)}\n`, "utf8");

console.log(JSON.stringify({
  output: outputPath,
  summary: normalized.summary,
  quality: normalized.quality,
}, null, 2));

function parseArgs(argv) {
  const parsed = { input: "", output: "", verifyDemashi: false };
  for (let index = 0; index < argv.length; index += 1) {
    const current = argv[index];
    if (current === "--input") parsed.input = argv[++index] || "";
    else if (current.startsWith("--input=")) parsed.input = current.slice(8);
    else if (current === "--output") parsed.output = argv[++index] || "";
    else if (current.startsWith("--output=")) parsed.output = current.slice(9);
    else if (current === "--verify-demashi") parsed.verifyDemashi = true;
  }
  return parsed;
}

function requireSheet(workbookValue, name) {
  const sheet = workbookValue.Sheets[name];
  if (!sheet) throw new Error(`Missing worksheet: ${name}`);
  return sheet;
}

function normalizeWorkbook(rawMetricRows, rawRelationRows, source) {
  const metricHeaderIndex = findHeaderRow(rawMetricRows, ["时间", "SKU", "SKU名称"]);
  const relationHeaderIndex = findHeaderRow(rawRelationRows, ["类目", "德玛仕sku"]);
  const metricHeaders = indexHeaders(rawMetricRows[metricHeaderIndex]);
  const relationHeaders = indexHeaders(rawRelationRows[relationHeaderIndex]);
  const products = new Map();
  const dailyTotals = new Map();
  let validMetricRows = 0;

  for (const row of rawMetricRows.slice(metricHeaderIndex + 1)) {
    const productId = asId(read(row, metricHeaders, "SKU"));
    const date = asDate(read(row, metricHeaders, "时间"));
    if (!productId || !date) continue;
    validMetricRows += 1;
    const productKey = makeProductKey("jd", productId);
    const metric = normalizeMetricRow(row, metricHeaders, date);
    const product = products.get(productKey) || createProduct(row, metricHeaders, productId, productKey);
    product.trend.push(metric);
    addMetric(product.summary, metric);
    products.set(productKey, product);

    const day = dailyTotals.get(date) || emptyDailyMetric(date);
    addMetric(day, metric);
    dailyTotals.set(date, day);
  }

  for (const product of products.values()) {
    product.trend.sort((left, right) => left.date.localeCompare(right.date));
    finalizeMetricSummary(product.summary);
  }

  const mappingGroups = [];
  const relations = [];
  const competitorIds = new Set();
  const orphanMappings = [];
  const mappingsWithoutCompetitor = [];
  const brandWithoutProductId = [];

  for (const row of rawRelationRows.slice(relationHeaderIndex + 1)) {
    const ownProductId = asId(read(row, relationHeaders, "德玛仕sku"));
    if (!ownProductId) continue;
    const ownProductKey = makeProductKey("jd", ownProductId);
    const category = cleanText(read(row, relationHeaders, "类目"));
    const model = cleanText(read(row, relationHeaders, "德玛仕型号"));
    const competitors = [];

    for (let slot = 1; slot <= 3; slot += 1) {
      const brand = cleanText(read(row, relationHeaders, `竟品${slot}品牌`));
      const productId = asId(read(row, relationHeaders, `竟品sku${slot}`));
      if (brand && !productId) {
        brandWithoutProductId.push({ ownProductId, slot, brand });
        continue;
      }
      if (!productId) continue;
      const competitorProductKey = makeProductKey("jd", productId);
      const relation = {
        relationKey: `${ownProductKey}:${competitorProductKey}`,
        ownProductKey,
        ownProductId,
        competitorProductKey,
        competitorProductId: productId,
        competitorBrand: brand,
        category,
      };
      competitors.push(relation);
      relations.push(relation);
      competitorIds.add(productId);
    }

    const ownProduct = products.get(ownProductKey);
    if (ownProduct) {
      ownProduct.model = model || ownProduct.model;
      ownProduct.relationCount = competitors.length;
    } else {
      orphanMappings.push({ ownProductId, ownProductKey, model, category });
    }
    if (!competitors.length) mappingsWithoutCompetitor.push({ ownProductId, ownProductKey, model, category });
    mappingGroups.push({ ownProductId, ownProductKey, model, category, competitors });
  }

  const productList = Array.from(products.values()).sort((left, right) => right.summary.gmv - left.summary.gmv);
  const finalizedDailyTotals = Array.from(dailyTotals.values())
    .sort((left, right) => left.date.localeCompare(right.date))
    .map((metric) => finalizeMetricSummary(metric));
  const category2Count = new Set(productList.map((product) => product.category2).filter(Boolean)).size;
  const category3Count = new Set(productList.map((product) => product.category3).filter(Boolean)).size;
  const dates = finalizedDailyTotals.map((metric) => metric.date);

  return {
    schemaVersion: 1,
    generatedAt: new Date().toISOString(),
    caseName: "德玛仕",
    platform: "jd",
    source: {
      ...source,
      sheets: [METRIC_SHEET, RELATION_SHEET],
      dateRange: { start: dates[0] || "", end: dates.at(-1) || "" },
    },
    summary: {
      metricRows: validMetricRows,
      metricProducts: productList.length,
      mappingRows: mappingGroups.length,
      relations: relations.length,
      uniqueCompetitorProducts: competitorIds.size,
      category2Count,
      category3Count,
      reviewCount: 0,
    },
    dailyTotals: finalizedDailyTotals,
    products: productList,
    mappingGroups,
    relations,
    quality: {
      orphanMappings,
      mappingsWithoutCompetitor,
      brandWithoutProductId,
    },
  };
}

function findHeaderRow(rows, requiredHeaders) {
  const index = rows.findIndex((row) => requiredHeaders.every((header) => row.some((cell) => cleanText(cell) === header)));
  if (index < 0) throw new Error(`Header row not found: ${requiredHeaders.join(", ")}`);
  return index;
}

function indexHeaders(headerRow) {
  return new Map(headerRow.map((value, index) => [cleanText(value), index]).filter(([name]) => Boolean(name)));
}

function read(row, headers, name) {
  const index = headers.get(name);
  return index === undefined ? null : row[index];
}

function createProduct(row, headers, productId, productKey) {
  return {
    platform: "jd",
    productId,
    productKey,
    asin: productId,
    role: "own",
    brand: "德玛仕",
    title: cleanText(read(row, headers, "SKU名称")),
    model: "",
    category1: cleanText(read(row, headers, "一级类目")),
    category2: cleanText(read(row, headers, "二级类目")),
    category3: cleanText(read(row, headers, "三级类目")),
    source: "excel",
    relationCount: 0,
    summary: emptyMetricSummary(),
    trend: [],
  };
}

function normalizeMetricRow(row, headers, date) {
  return {
    date,
    gmv: asNumber(read(row, headers, "成交金额")),
    soldUnits: asNumber(read(row, headers, "成交商品件数")),
    transactionOrders: asNumber(read(row, headers, "成交单量")),
    transactionCustomers: asNumber(read(row, headers, "成交客户数")),
    sourceConversionRate: asPercent(read(row, headers, "成交转化率")),
    impressions: asNumber(read(row, headers, "搜索曝光次数")),
    clicks: asNumber(read(row, headers, "搜索点击次数")),
    views: asNumber(read(row, headers, "商品浏览量")),
    visitors: asNumber(read(row, headers, "商品访客数")),
    cartUnits: asNumber(read(row, headers, "加购商品件数")),
    orderAmount: asNumber(read(row, headers, "下单金额")),
    orderUnits: asNumber(read(row, headers, "下单商品件数")),
    orderCount: asNumber(read(row, headers, "下单单量")),
    refundAmount: asNumber(read(row, headers, "取消及售后退款金额")),
    refundUnits: asNumber(read(row, headers, "取消及售后退款商品件数")),
    refundOrders: asNumber(read(row, headers, "取消及售后退款单量")),
  };
}

function emptyMetricSummary() {
  return {
    gmv: 0,
    soldUnits: 0,
    transactionOrders: 0,
    transactionCustomers: 0,
    impressions: 0,
    clicks: 0,
    views: 0,
    visitors: 0,
    cartUnits: 0,
    orderAmount: 0,
    orderUnits: 0,
    orderCount: 0,
    refundAmount: 0,
    refundUnits: 0,
    refundOrders: 0,
    conversionRate: 0,
    clickThroughRate: 0,
    averageUnitPrice: 0,
    refundToGmvRate: 0,
  };
}

function emptyDailyMetric(date) {
  return { date, ...emptyMetricSummary() };
}

function addMetric(target, metric) {
  for (const key of [
    "gmv", "soldUnits", "transactionOrders", "transactionCustomers", "impressions", "clicks",
    "views", "visitors", "cartUnits", "orderAmount", "orderUnits", "orderCount", "refundAmount",
    "refundUnits", "refundOrders",
  ]) target[key] += metric[key] || 0;
}

function finalizeMetricSummary(summary) {
  summary.conversionRate = safeDivide(summary.transactionCustomers, summary.visitors);
  summary.clickThroughRate = safeDivide(summary.clicks, summary.impressions);
  summary.averageUnitPrice = safeDivide(summary.gmv, summary.soldUnits);
  summary.refundToGmvRate = safeDivide(summary.refundAmount, summary.gmv);
  return summary;
}

function safeDivide(numerator, denominator) {
  return denominator ? numerator / denominator : 0;
}

function makeProductKey(platform, productId) {
  return `${platform}:${productId}`;
}

function cleanText(value) {
  return value === null || value === undefined ? "" : String(value).trim();
}

function asId(value) {
  if (value === null || value === undefined || value === "") return "";
  if (typeof value === "number" && Number.isFinite(value)) return Math.trunc(value).toString();
  return cleanText(value).replace(/\.0$/, "");
}

function asNumber(value) {
  if (typeof value === "number") return Number.isFinite(value) ? value : 0;
  const parsed = Number(String(value ?? "").replace(/,/g, "").trim());
  return Number.isFinite(parsed) ? parsed : 0;
}

function asPercent(value) {
  if (typeof value === "number") return value > 1 ? value / 100 : value;
  const text = cleanText(value);
  if (!text) return 0;
  const parsed = Number(text.replace("%", ""));
  if (!Number.isFinite(parsed)) return 0;
  return text.includes("%") || parsed > 1 ? parsed / 100 : parsed;
}

function asDate(value) {
  if (value instanceof Date && !Number.isNaN(value.getTime())) return value.toISOString().slice(0, 10);
  if (typeof value === "number") {
    const decoded = XLSX.SSF.parse_date_code(value);
    if (decoded) return `${decoded.y}-${String(decoded.m).padStart(2, "0")}-${String(decoded.d).padStart(2, "0")}`;
  }
  const text = cleanText(value);
  if (!text) return "";
  const timestamp = Date.parse(text);
  return Number.isNaN(timestamp) ? text.slice(0, 10) : new Date(timestamp).toISOString().slice(0, 10);
}

function verifyDemashi(dataset) {
  assert.equal(dataset.summary.metricRows, 9717, "Unexpected metric row count");
  assert.equal(dataset.summary.metricProducts, 2817, "Unexpected product count");
  assert.equal(dataset.summary.mappingRows, 28, "Unexpected mapping row count");
  assert.equal(dataset.summary.uniqueCompetitorProducts, 37, "Unexpected competitor count");
  assert.equal(dataset.summary.category2Count, 10, "Unexpected level-2 category count");
  assert.equal(dataset.summary.category3Count, 49, "Unexpected level-3 category count");
  assert.deepEqual(dataset.quality.orphanMappings.map((item) => item.ownProductId), ["100204398739"]);
  assert.equal(dataset.quality.mappingsWithoutCompetitor.length, 2);
}