product-import-ai/scripts/read-sql-sheet.ts

// @ts-nocheck
import fs from "node:fs";
import { createRequire } from "node:module";

const require = createRequire(import.meta.url);
const JSZip = require("jszip");

const workbookPath = process.argv[2] || "xxx_endpoint_bo_2026-08-04_v1.xlsm";
const fromRow = Number.parseInt(process.argv[3] || "1", 10);
const toRow = Number.parseInt(process.argv[4] || String(fromRow), 10);

const zip = await JSZip.loadAsync(fs.readFileSync(workbookPath));
const sharedStringsXml = await zip.file("xl/sharedStrings.xml").async("string");
const sharedStrings = [...sharedStringsXml.matchAll(/<si[\s\S]*?<\/si>/g)].map((match) =>
  decodeXml([...match[0].matchAll(/<t[^>]*>([\s\S]*?)<\/t>/g)].map((part) => part[1]).join("")),
);

const sqlSheetXml = await zip.file("xl/worksheets/sheet5.xml").async("string");

for (const rowMatch of sqlSheetXml.matchAll(/<row[^>]* r="(\d+)"[\s\S]*?<\/row>/g)) {
  const rowNumber = Number(rowMatch[1]);
  if (rowNumber < fromRow || rowNumber > toRow) continue;

  const cells = {};
  for (const cellMatch of rowMatch[0].matchAll(
    /<c[^>]* r="([A-Z]+)\d+"([^>]*)>([\s\S]*?)<\/c>/g,
  )) {
    const [, column, attributes, body] = cellMatch;
    const inlineString = body.match(/<is>[\s\S]*?<t[^>]*>([\s\S]*?)<\/t>[\s\S]*?<\/is>/);
    const rawValue = body.match(/<v>([\s\S]*?)<\/v>/)?.[1] ?? inlineString?.[1] ?? "";
    cells[column] = attributes.includes('t="s"')
      ? sharedStrings[Number(rawValue)] || ""
      : decodeXml(rawValue);
  }

  console.log(JSON.stringify({ row: rowNumber, cells }, null, 2));
}

function decodeXml(value) {
  return String(value ?? "")
    .replaceAll("&lt;", "<")
    .replaceAll("&gt;", ">")
    .replaceAll("&amp;", "&")
    .replaceAll("&quot;", '"')
    .replaceAll("&apos;", "'");
}