bulk-import.ts

/**
 * V.AI AVATAR — Bulk product import (Thêm sản phẩm hàng loạt)
 * =============================================================
 * Admin uploads a catalogue file (Excel .xlsx / CSV / PDF / image) and the
 * module auto-detects the table columns + rows, extracts each product
 * (name, sku/model, price, brand, description, features, specs), and lets the
 * frontend present an editable preview before committing all rows at once via
 * /api/bulk-add-products (which appends to the shared catalog dataset
 * bep40/grob-products-updated/products_url_added.json, synced to ZaloBot).
 *
 * Parsing is DETERMINISTIC-first (Excel/CSV → table parser; PDF → text layer),
 * and AI/vision has a best-effort, gracefully-degrading path for scanned PDFs
 * and catalogue images (Qwen2.5-VL via HF Inference Providers). If the AI is
 * unavailable (credits depleted, offline), the image path falls back to the
 * existing per-product OCR service (one product → one record) as a manual
 * assist. The commit path NEVER depends on AI.
 *
 * Exports used by index.ts:
 *   parseBulkFile(thing, filename, opts)   -> { ok, products:[...], ready, reason, warnings }
 *   normalizeCatalogProduct(obj, opts)     -> clean product record
 *   buildCatalogRecord(prod)               -> zalobot products_url_added format
 *   CATALOG_DATASET / CATALOG_FILE
 */

export const CATALOG_DATASET = "bep40/grob-products-updated";
export const CATALOG_FILE = "products_url_added.json";

// ── Column-header alias → canonical field ────────────────────────────────────
// Vietnamese headers vary widely (case, diacritics, spacing). We normalize the
// header (strip diacritics, lowercase, collapse spaces) then match aliases.
const HEADER_ALIASES: Array<[string, string[]]> = [
  ["name", ["ten san pham", "ten sp", "ten", "name", "product name", "san pham", "ten san phẩm", "ten hang", "tensp"]],
  ["sku", ["ma san pham", "ma sp", "ma", "sku", "code", "model", "ma model", "model may", "ma san phẩm"]],
  ["listPrice", ["gia niem yet", "gia niem yét", "gia goc", "list price", "gia list", "gia thi truong"]],
  ["salePrice", ["gia khuyen mai", "gia khuyến mãi", "gia km", "gia ban", "gia bán", "gia hien tai", "gia khuyen", "sale price", "gia km sau ck"]],
  ["price", ["don gia", "gia", "giá", "price", "gia chua vat", "giaban", "gia ban le"]],
  ["brand", ["thuong hieu", "brand", "hang", "nhan hieu", "hang sx", "thuong hiệu"]],
  ["description", ["mo ta", "mo ta san pham", "description", "mo tả", "descr", "noi dung"]],
  ["features", ["tinh nang", "tinh nang noi bat", "features", "dac diem", "tinh năng"]],
  ["specs", ["thong so", "thong so ky thuat", "specs", "thong số", "thong so ki thuat", "chi tiet ky thuat"]],
  ["category", ["danh muc", "loai", "phan loai", "category", "nhom", "danh mục", "loai san pham"]],
  ["image", ["hinh anh", "anh", "image", "img", "link hinh", "anh san pham", "hinh"]],
  ["url", ["link", "url", "duong dan", "duong link"]],
  ["unit", ["don vi tinh", "dvt", "unit", "don vi"]],
];

// Strip Vietnamese diacritics + lowercase + collapse spaces.
function _normHeader(s: string): string {
  return String(s || "")
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/đ/g, "d")
    .replace(/Đ/g, "d")
    .toLowerCase()
    .replace(/\s+/g, " ")
    .trim();
}

function _classifyHeader(header: string): string | null {
  const h = _normHeader(header);
  if (!h) return null;
  for (const [field, aliases] of HEADER_ALIASES) {
    if (aliases.indexOf(h) >= 0) return field;
  }
  // substring fallback: "ten sp 1" etc.
  for (const [field, aliases] of HEADER_ALIASES) {
    for (const a of aliases) {
      if (a.length >= 3 && h.indexOf(a) === 0) return field;
      if (h.indexOf(a) >= 0 && h.length - a.length <= 2) return field;
    }
  }
  return null;
}

// Parse a VND price string ("5.500.000", "5,500,000", "5tr5", "1.290.000đ").
export function parseVnd(s: any): number {
  if (s == null) return 0;
  if (typeof s === "number") return isFinite(s) ? Math.round(s) : 0;
  const t = String(s).trim();
  if (!t) return 0;
  // Handle "5tr5", "1.2tr", "3 triệu", "12tr" shorthand
  const tr = t.replace(/\s/g, "").match(/^([\d.,]+)\s*(tr|triệu|trieu)/i);
  if (tr) {
    const n = parseFloat(tr[1].replace(/,/g, "").replace(/\./g, "."));
    if (!isNaN(n)) return Math.round(n * 1e6);
    return 0;
  }
  const digits = t.replace(/[đ₫]/g, "").replace(/,/g, ".");
  const m = String(digits).match(/([\d][\d.]*)/);
  if (!m) return 0;
  let num = m[1];
  const parts = num.split(".");
  let n: number;
  // Treat dot-separated groups as THOUSANDS separators (Vietnamese notation:
  // 1.290.000, 680.000, 5.500.000) whenever trailing groups are exactly 3 digits.
  const hasThousandsSep = parts.length >= 2 && parts.slice(1).every((g) => g.length === 3);
  if (hasThousandsSep) {
    n = parseInt(parts.join(""), 10);
  } else {
    n = parseFloat(num.replace(/\./g, "."));
  }
  return !isNaN(n) ? Math.round(n) : 0;
}

function _cellVal(v: any): string {
  if (v == null) return "";
  if (typeof v === "number" || typeof v === "boolean") return String(v);
  if (typeof v === "object" && typeof v.text === "string") return v.text; // rich text cell
  if (v instanceof Date) return v.toISOString().slice(0, 10);
  return String(v).trim();
}

// Build the zalobot products_url_added record from a normalized product object.
export function buildCatalogRecord(prod: any): any {
  // pn = sale price (giá KM); listPn = niêm yết; keep both + priceMode "both"
  // so the product shows "giá niêm yết — giá KM" everywhere (cards, detail,
  // FLASHSALE panel) exactly like a discounted product on the CK link.
  let pn = parseVnd(prod.priceNum != null ? prod.priceNum : prod.price);
  if (!pn && prod.salePn) pn = parseVnd(prod.salePn);
  let listPn = parseVnd(prod.listPrice != null ? prod.listPrice : prod.listPn);
  if (listPn && pn && listPn < pn) { const _t = listPn; listPn = pn; pn = _t; }
  let salePn = parseVnd(prod.salePn != null ? prod.salePn : (prod.salePrice != null ? prod.salePrice : pn));
  if (salePn && listPn && salePn > listPn) salePn = listPn;
  const priceMode = (prod.priceMode === "both" || prod.priceMode === "listFirst")
    ? prod.priceMode
    : (listPn && salePn && listPn > salePn ? "both" : (prod.priceMode || "sale"));
  const now = new Date().toISOString();
  const sku = String(prod.sku || prod.model || "").trim();
  return {
    n: String(prod.name || "").trim(),
    l: String(prod.url || prod.link || "").trim(),
    i: String(prod.image || (Array.isArray(prod.images) ? prod.images[0] : "") || "").trim(),
    p: pn ? pn.toLocaleString("vi-VN") + "đ" : (prod.price ? String(prod.price) : "Liên hệ"),
    pn: pn,
    listPn: listPn,
    salePn: salePn,
    priceMode: priceMode,
    c: String(prod.category || "").trim(),
    cs: "san-pham-them-moi",
    ci: "fa-box",
    imgs: (Array.isArray(prod.images) ? prod.images : [String(prod.image || "")]).filter(Boolean),
    sum: String(prod.description || prod.summary || "").trim(),
    desc: String(prod.description || prod.summary || "").trim(),
    specs: prod.specs && typeof prod.specs === "object" ? prod.specs : {},
    feats: Array.isArray(prod.features) ? prod.features.map(String) : [],
    sku: sku,
    vid: "",
    mod: String(prod.model || prod.sku || "").trim(),
    brand: String(prod.brand || "").trim(),
    slug: String(prod.slug || sku || "san-pham").toLowerCase(),
    _source: "avatar2-bulk-import",
    _added_at: now,
  };
}

// Normalize a raw parsed row (from Excel/CSV/PDF/AI) into a clean product object.
// Accepts both "keyed" objects and positional arrays (older files without headers).
export function normalizeCatalogProduct(input: any, opts: any = {}): any {
  const out: any = {};
  if (!input || typeof input !== "object") return out;

  // Case A: positional array + optional header map provided by caller.
  if (Array.isArray(input) && Array.isArray(opts.headers)) {
    opts.headers.forEach((h: string, i: number) => {
      const f = _classifyHeader(h) || _classifyHeader(String(h));
      if (f && !out[f]) out[f] = _cellVal(input[i]);
    });
    // When there is no real "name" column, the description column (e.g.
    // "Mô tả sản phẩm") holds the product name — promote it so the row is kept.
    if (!out.name && out.description) { out.name = out.description; out.description = ""; }
    // Single-cell rows ("Máy giặt Hitachi ... - 6.490.000đ") from headerless
    // order-form exports: split the trailing VND price out so the product has a
    // real price signal (pn>0) instead of being dropped as "no price, no SKU".
    if (out.name && !out.price) {
      const pm = String(out.name).match(/^(.*?)[\s\-–—|:]+(\d{1,3}(?:[.,]\d{3})+|\d{4,})\s*(?:đ|₫|dong|vnd|d)?\s*$/i);
      if (pm && pm[1] && _normHeader(pm[1]).length >= 4 && !_isLabelLine(_normHeader(pm[1]))) {
        out.name = pm[1].trim();
        out.price = pm[2];
      }
    }
    return out;
  }

  // Case B: keyed object — try direct/common keys first, then header-alias match.
  const src: any = input instanceof Map ? Object.fromEntries(input) : (input || {});
  const keys = Object.keys(src);
  const direct: Record<string, string> = {};
  keys.forEach((k) => {
    const f = _classifyHeader(k);
    if (f && !direct[f]) direct[f] = _cellVal(src[k]);
  });
  // common internal keys
  const aliases: any = {
    name: src.name || src.n || src.title || src.ten || "",
    sku: src.sku || src.ma || src.code || src.model || src.mod || "",
    price: src.price != null ? src.price : (src.gia != null ? src.gia : src.pn),
    priceNum: src.priceNum != null ? src.priceNum : src.pn,
    salePrice: src.salePrice != null ? src.salePrice : (src.giaKhuyenMai != null ? src.giaKhuyenMai : (src.giaKM != null ? src.giaKM : "")),
    listPrice: src.listPrice != null ? src.listPrice : src.giaNiemYet,
    brand: src.brand || src.thuongHieu || src.hang || "",
    description: src.description || src.desc || src.summary || src.moTa || "",
    features: Array.isArray(src.features) ? src.features : (Array.isArray(src.feats) ? src.feats : (typeof src.tinhNang === "string" ? src.tinhNang.split("\n") : [])),
    specs: src.specs && typeof src.specs === "object" ? src.specs : {},
    category: src.category || src.danhMuc || src.loai || "",
    image: src.image || src.img || src.hinhAnh || (Array.isArray(src.images) ? src.images[0] : "") || "",
    images: Array.isArray(src.images) ? src.images : (Array.isArray(src.imgs) ? src.imgs : []),
    url: src.url || src.link || "",
    slug: src.slug || "",
    model: src.model || src.mod || src.sku || "",
  };
  // header-alias wins over ambiguous internal keys
  Object.keys(direct).forEach((f) => { if (direct[f]) out[f] = direct[f]; });
  Object.keys(aliases).forEach((f) => { if (aliases[f] !== undefined && aliases[f] !== "" && !out[f]) out[f] = aliases[f]; });

  // Parse features/specs from a single text block if they were one cell.
  if (!Array.isArray(out.features) && typeof out.features === "string") {
    out.features = String(out.features).split(/[\n;]/).map(s => s.trim()).filter(Boolean);
  }
  if (out.features && !Array.isArray(out.features)) out.features = [String(out.features)];

  return out;
}

// Parse a features text (newline / bullet separated) into an array.
export function splitFeatures(s: any): string[] {
  if (Array.isArray(s)) return s.map(String).map(t => t.trim()).filter(Boolean);
  return String(s || "").split(/\n|;\s*/).map(t => t.trim().replace(/^[•▪\-*✓]\s*/, "")).filter(Boolean);
}

/**
 * Main entrypoint. `thing` is either a Buffer/Uint8Array of the uploaded file
 * OR a base64 data-URL string. `filename` gives the extension to pick a parser.
 */
export async function parseBulkFile(thing: any, filename: string, opts: any = {}): Promise<any> {
  const name = String(filename || "").toLowerCase();
  const warnings: string[] = [];
  let bytes: Uint8Array;
  if (typeof thing === "string" && /^data:/.test(thing)) {
    const b64 = thing.split(",")[1] || "";
    bytes = new Uint8Array(Buffer.from(b64, "base64"));
  } else if (typeof thing === "string" && /[^a-zA-Z0-9+/=\s]/.test(thing)) {
    // plain text (CSV content)
    return parseCsvText(thing, opts, warnings);
  } else if (Buffer.isBuffer(thing) || thing instanceof Uint8Array || thing instanceof ArrayBuffer) {
    bytes = thing instanceof ArrayBuffer ? new Uint8Array(thing) : new Uint8Array(thing.buffer ? thing.buffer : thing);
  } else {
    return { ok: false, products: [], ready: false, reason: "Unsupported payload" };
  }

  try {
    if (name.endsWith(".xlsx") || name.endsWith(".xls")) return await parseXlsx(bytes, opts, warnings);
    if (name.endsWith(".csv")) {
      const txt = new TextDecoder().decode(bytes);
      return parseCsvText(txt, opts, warnings);
    }
    if (name.endsWith(".pdf")) return await parsePdf(bytes, opts, warnings);
    // image (jpg/png/webp/gif)
    if (/\.(jpe?g|png|webp|gif|bmp)$/.test(name)) return await parseImageCatalogue(bytes, opts, warnings);
    // unknown extension → try CSV text, else XLSX
    return await parseCsvText(new TextDecoder().decode(bytes), opts, warnings);
  } catch (e: any) {
    return { ok: false, products: [], ready: false, reason: String(e?.message || e), warnings };
  }
}

async function parseXlsx(bytes: Uint8Array, opts: any, warnings: string[]): Promise<any> {
  const ExcelJS = (await import("exceljs")).default;
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.load(bytes.buffer ? bytes.buffer.slice(bytes.byteOffset, bytes.byteOffset + bytes.byteLength) : bytes);
  const worksheets = wb.worksheets || [];
  if (!worksheets.length) return { ok: false, products: [], ready: false, reason: "No worksheet found" };

  // Real catalogues often put the DATA on a non-first sheet ("Sheet2", "Bảng giá"),
  // with the first sheet holding only a title/banner. Parse EVERY worksheet and
  // AGGREGATE the products from all sheets that carry a genuine product table
  // (≥1 real signal: a row with a price>0 or a SKU). This guarantees "các dòng có
  // SP" spread across MULTIPLE sheets are all captured (not just the single best
  // sheet), while banner/title-only sheets (no price, no SKU) are excluded.
  let allProds: any[] = [];
  let sheetsWithReal = 0;
  for (const ws of worksheets) {
    const rows: any[][] = [];
    ws.eachRow({ includeEmpty: false }, (row: any) => {
      const vals: any[] = [];
      row.eachCell({ includeEmpty: true }, (cell: any, n: number) => { vals[Number(n) - 1] = cell.value; });
      if (vals.every((v) => v == null || String(v).trim() === "")) return;
      rows.push(vals);
    });
    if (!rows.length) continue;
    const r = rowsToProducts(rows, opts, warnings);
    const prods = (r && Array.isArray(r.products) ? r.products : []);
    // Real signal: at least one row carries a real price (>0) or a SKU — a
    // genuine product table. A banner/title-only sheet has neither.
    const hasRealSignal = prods.some((pp) => pp && (Number(pp.pn) > 0 || (pp.sku && String(pp.sku).trim())));
    if (prods.length && hasRealSignal) { allProds = allProds.concat(prods); sheetsWithReal++; }
    console.log("[bulk-import] sheet '" + (ws.name || "?") + "': " + prods.length + " products, real=" + hasRealSignal + " → keep=" + (prods.length && hasRealSignal));
  }
  if (allProds.length) {
    // Cross-sheet dedup (same SKU/name appearing on multiple sheets) happens
    // here via the shared finalizeProducts.
    const fin = finalizeProducts(allProds, opts, warnings);
    if (fin && Array.isArray(fin.products) && fin.products.length) {
      fin.sheets = sheetsWithReal;
      return fin;
    }
  }
  return { ok: false, products: [], ready: false, reason: "No product rows detected in any sheet.", warnings };
}

async function parseCsvText(txt: string, opts: any, warnings: string[]): Promise<any> {
  const { parse } = await import("csv-parse/sync");
  let recs: any;
  try {
    recs = parse(txt, { columns: true, bom: true, relax_column_count: true, skip_empty_lines: true, trim: true });
  } catch (e: any) {
    // Fallback: treat as plain line-per-product text.
    return parsePdfTextLines(txt.split(/\r?\n/), opts, warnings);
  }
  const headers = Object.keys(Array.isArray(recs) && recs[0] ? recs[0] : {});
  const products = (Array.isArray(recs) ? recs : []).map((r: any) => normalizeCatalogProduct(r, opts)).filter((p: any) => p && (p.name || p.sku));
  return finalizeProducts(products, opts, warnings);
}

// Public entrypoint for the OCR pipeline: the frontend OCRs an image/PDF page
// into plain text, then sends that text here to be decomposed into products
// (auto column+row detection) — identical logic to CSV/PDF-text parsing but
// without needing AI credits. 
export async function parseTextContent(text: string, opts: any = {}): Promise<any> {
  const warnings: string[] = [];
  if (!text || !String(text).trim()) {
    return { ok: true, products: [], ready: false, reason: "Không nhận được chữ (OCR trống).", warnings };
  }
  const txt = String(text);
  // If it looks like CSV (delimiters: comma/semicolon/tab with headers), route
  // through the strict parser; otherwise treat as loosely-aligned line rows.
  // Detect real CSV (comma/semicolon/tab delimiters WITH a header). Important:
  // Vietnamese OCR text uses commas as THOUSANDS separators ("6,290,000") inside
  // prices, so a naive comma check wrongly treats product lines as CSV. Only
  // treat as CSV when a comma/semicolon/tab is followed by a NON-digit (a true
  // field boundary like a name cell), OR the first line clearly lists column
  // headers (contains a second known header word after a delimiter).
  const firstLine = (txt.split(/\r?\n/)[0] || "");
  const looksCsv = /(?:,|;|\t)[^\d,\s]/.test(firstLine) || /	/.test(firstLine) || /,(?:ten|Tên|ma|Mã|san pham|Sản phẩm|gia|Giá)/.test(firstLine);
  if (looksCsv) {
    const r = await parseCsvText(txt, opts, warnings);
    // only trust CSV result if it produced something; else fall through.
    if (r.ready && r.products && r.products.length) return r;
  }
  return lineRowsToProducts(txt.split(/\r?\n/), opts, warnings);
}

// Heuristic: is this raw text a "non-product" row (totals, page footers,
// section titles, unit/currency rom, "tổng cộng", "tổng tiền" sums, page
// numbers, column-of-just-name headers like "STT", etc.)? Returns true if it
// should be excluded from product extraction.
// Noise keywords are matched as WHOLE WORDS (word boundaries) so that a keyword
// like "cong" / "tong" never matches inside a real product name such as "công
// nghệ Inverter" or "công suất 450 lít" (Vietnamese diacritics are stripped by
// _normHeader, so "công" -> "cong"). A noise LINE is one that IS (or starts
// with) a total/label — not one that merely contains the substring.
const NOISE_NAME_RE = [
  /^(?:tong cong|tong tien|tong so|tong|cong|sub total|subtotal|total|thanh tien|thanh toan|gia tri don hang|so tien|stt)(?:\s|$)/,
];
function _isNoiseName(s: string, hasPrice?: boolean): boolean {
  const n = _normHeader(s);
  if (!n) return true;
  if (n.length <= 2) return true; // isolated "STT", "OD", short labels
  for (const re of NOISE_NAME_RE) { if (re.test(n)) return true; }
  // Label-start lines ("Lưu ý: …", "Thuế VAT 10%", "Đơn vị tính", "Nhóm: …",
  // "Công ty …", "Hotline …", "Bảo hành …") are never products.
  if (_isLabelLine(n)) return true;
  // All-caps section titles ("MÁY GIẶT", "PHỤ KIỆN BẾP") are never products,
  // UNLESS the row carries a real price (an all-caps product WITH price such as
  // "MÁY GIẶT SAMSUNG 9KG 6.500.000" must be kept).
  if (!hasPrice && _looksLikeTitleOnly(s)) return true;
  return false;
}
// Raw cell text that indicates a non-data row (an Excel TOTAL row, a page
// header/footer, "đơn vị tính", etc.).
function _cellIsNoise(v: any): boolean {
  const s = _cellVal(v);
  if (!s) return false;
  const n = _normHeader(s);
  if (!n) return false;
  // A cell is "noise" only when it IS (or starts with) a total/unit/label
  // keyword — never a substring match ("công nghệ" must NOT be noise).
  if (/^(?:tong|cong|tong cong|tong tien|total|sub ?total|stt)(?:\s|$)/.test(n)) return true;
  if (/^(?:don vi|dvt|ghi chu|ky hieu|loai|nhom|muc luc|bang gia)(?:\s|$)/.test(n)) return true;
  return false;
}
// ── Row-level product detection (definitive) ──────────────────────────────
// A row is a PRODUCT only when ALL of these hold:
//   1. The NAME column cell is a real product name: ≥3 chars, contains letters,
//      and does NOT start with a label/section keyword ("TỔNG CỘNG", "Ghi chú",
//      "Lưu ý", "Thuế VAT", "Nhóm:", "Đơn vị tính", "Trang 2", "MÁY GIẶT"-style
//      all-caps group titles without a price, "Công ty", "Hotline", ...).
//   2. The row carries at least one of: a PRICE in a price column, an SKU/model
//      in the SKU column, or ≥2 other populated columns (unit, brand, category…).
//   3. The row is not a total/sum row anywhere.
// This completely removes non-product rows (section titles, notes, taxes, page
// footers, totals) while keeping every real product row.
const LABEL_START_RE = /^(?:tong cong|tong tien|tong so|tong|total|sub ?total|stt|so thu tu|cong tien|cong thanh|cong don|thanh tien|thanh toan|don vi|dvt|doi vi|don gia|ghi chu|luu y|ky hieu|loai|nhom|phan nhom|danh muc|muc luc|bang gia|toan bo|san pham|huong dan|ngay|thang|trang|page|hotline|lien he|dia chi|cong ty|thue|vat|phi|chiet khau|giam gia|khuyen mai|bao hanh|van chuyen|xem them|tai ve|mua ngay|ma don|so don|don hang|khach hang|khach|dien thoai|sdt|sdt khach|email|nguoi nhan|nguoi mua|nguoi lap|nguoi duyet|nguoi kiem|nguoi giao|nguoi xem|lap bao gia|bao gia|dia chi giao|phuong thuc|hinh thuc|ngay dat|ngay giao|ky gui|len don|don vi ban)\b/;
// Does the normalized name start with a known label word? (diacritics already
// stripped by _normHeader → "Lưu ý" = "luu y", "Thuế VAT" = "thue vat".)
function _isLabelLine(n: string): boolean {
  if (!n) return false;
  return LABEL_START_RE.test(n);
}
// All-caps title (≥2 words, NO digits, NO price) — classic section header:
// "MÁY GIẶT", "PHỤ KIỆN BẾP", "BẢNG GIÁ" … Real all-caps PRODUCT names almost
// always contain digits (model/size/capacity: "MÁY GIẶT SAMSUNG 9KG"), so
// digit-free all-caps text is a strong section-title signal.
function _looksLikeTitleOnly(s: string): boolean {
  const t = String(s || "").trim();
  if (t.length < 3) return false;
  if (!/[a-zA-ZÀ-ỹ]/.test(t)) return false;
  if (/\d/.test(t)) return false; // "9KG", "80CM", "GL-400" → has digits → not a title
  const words = t.split(/\s+/).filter(Boolean);
  if (words.length < 2) return false;          // single word "Máy" is not a title either
  return words.every((w) => /^[A-ZÀ-ỸĐ][A-ZÀ-ỸĐ0-9().,/&%\-_']*$/.test(w));
}
// A single-cell row like "Máy giặt Hitachi ... - 6.490.000đ" (whole product in
// ONE cell, common in order forms / exported text) is a product. Labels
// ("Khách hàng: ...", "Điện thoại: ...", totals, notes) must NOT match — the
// text BEFORE the price must look like a real product name.
function _rowLooksLikeSingleCellProduct(row: any[]): boolean {
  const cells = (row || []).map(_cellVal).map((s) => s.trim()).filter(Boolean);
  if (cells.length !== 1) return false;
  const t = cells[0];
  // Require a parseable VND price at the END of the string (thousands-grouped
  // OR a bare 4+ digit integer), with the price as the LAST token so a model
  // number ("BD-1054HVOW") never matches mid-name.
  const mt = t.match(/(?:^|\s)(\d{1,3}(?:[.,]\d{3})+|\d{4,})\s*(?:đ|₫|dong|vnd|d)?\s*$/i);
  if (!mt) return false;
  const before = t.slice(0, mt.index as number).trim();
  const n = _normHeader(before);
  if (!n || n.length < 4) return false;
  if (!/[a-zA-ZÀ-ỹ]/.test(before)) return false;
  if (_isLabelLine(n)) return false;            // "Mã đơn:", "Khách hàng:", "TỔNG CỘNG"...
  if (_looksLikeTitleOnly(before)) return false; // all-caps no-digit section titles
  // price must be plausible (>10k VND)
  const pv = parseVnd(mt[1]);
  if (pv < 10000) return false;
  // Strip a leading ordinal ("1 Máy giặt..." / "1. Máy giặt...")
  const okName = before.replace(/^\d{1,3}[.)\s]+\s*/, "").trim();
  return okName.length >= 4;
}

function _rowLooksLikeProduct(row: any[], headerRow: any[], opts: any): boolean {
  const head = (headerRow || []).map((h) => _classifyHeader(h));
  let nameIdx = head.indexOf("name");
  const skuIdx = head.indexOf("sku");
  const priceIdx = Math.max(head.indexOf("price"), head.indexOf("salePrice"), head.indexOf("listPrice"));
  const unitIdx = head.indexOf("unit");
  const brandIdx = head.indexOf("brand");
  const catIdx = head.indexOf("category");
  const sttIdx = head.indexOf("stt") >= 0 ? head.indexOf("stt") : -1;
  // "Mô tả sản phẩm" columns ARE the product name in many catalogues (the name
  // column is missing) — treat the description column as the name column for
  // row detection so those rows are no longer dropped as "no name".
  if (nameIdx < 0) {
    const descIdx = head.indexOf("description");
    if (descIdx >= 0) nameIdx = descIdx;
  }

  // Total/sum anywhere in the row → never a product.
  const joined = _normHeader(row.map(_cellVal).join(" "));
  if (/^(?:.*\s)?(tong cong|tong tien|tong so|cong tien|thanh tien|thanh toan|sub ?total)\s?/.test(joined)) return false;

  if (nameIdx >= 0) {
    const nameCell = _cellVal(row[nameIdx] != null ? row[nameIdx] : "").trim();
    const nameNorm = _normHeader(nameCell);
    // Name must be a real product name.
    // Name must be a real product name: ≥3 chars, has letters, not a label
    // line, and not an all-caps digit-free section title. (The price/sku check
    // below still decides; a title is only rejected when it carries NO price
    // and NO sku, e.g. a bare "MÁY GIẶT" row.)
    const realName = nameCell.length >= 3
      && /[a-zA-ZÀ-ỹ]/.test(nameCell)
      && !_isLabelLine(nameNorm)
      && !_looksLikeTitleOnly(nameCell);
    if (!realName) {
      // SKU-only fallback (name column empty or noise, but a real SKU present)
      if (skuIdx >= 0) {
        const skuCell = _cellVal(row[skuIdx] != null ? row[skuIdx] : "").trim();
        if (skuCell && !_isNoiseName(skuCell) && skuCell.length >= 2 && !/^[A-Z0-9]{1,2}$/.test(skuCell)) return true;
      }
      return false;
    }
    // Require supporting data: price in a price col, or sku, or ≥2 populated
    // non-name/non-STT columns (unit/brand/category/desc/…).
    let priceVal = 0;
    if (priceIdx >= 0) {
      const rawP = _cellVal(row[priceIdx] != null ? row[priceIdx] : "");
      priceVal = parseVnd(rawP);
      if (!priceVal && /[\d.,]/.test(rawP)) { const m = rawP.match(/(\d[\d.,]*)/); if (m) priceVal = parseVnd(m[1]); }
    }
    const skuCell = skuIdx >= 0 ? _cellVal(row[skuIdx] != null ? row[skuIdx] : "").trim() : "";
    let otherCols = 0;
    [unitIdx, brandIdx, catIdx].forEach((ix) => {
      if (ix >= 0 && ix !== nameIdx && ix !== sttIdx) {
        const v = _cellVal(row[ix] != null ? row[ix] : "");
        if (v.trim() && !_isNoiseName(v)) otherCols++;
      }
    });
    if (priceVal > 0) return true;
    if (skuCell && skuCell.length >= 2 && !_isNoiseName(skuCell)) return true;
    if (otherCols >= 1) return true;
    return false;
  }
  // No name column: require ≥2 meaningful alphabetic cells that are not noise,
  // AND the row is not a total row.
  let alphaCells: string[] = [];
  row.forEach((v) => {
    const s = _cellVal(v).trim();
    if (!s) return;
    if (_isNoiseName(s)) return;
    if (/[a-zA-ZÀ-ỹ]/.test(s) && s.length >= 3) alphaCells.push(s);
  });
  if (alphaCells.length < 2) return false;
  if (_isLabelLine(_normHeader(alphaCells[0]))) return false;
  return true;
}

function _findHeaderRow(rows: any[][]): { row: any[]; idx: number } | null {
  // Real catalogues often have a TITLE row ("BẢNG GIÁ SẢN PHẨM 8/2026") above
  // the header row, so scan the first rows for one where >= 2 cells classify
  // as known columns (name/sku/price/category/...), or (name + price) alone.
  // ⚠ Order-form files ("Mã đơn: DH-001", "Khách hàng: ...", "Điện thoại: ...",
  // empty rows, THEN the product table) push the header row DEEP below the top.
  // We scan further (up to 25 rows) but still require a genuine header (>=2
  // known columns, or name+price, or description+sku/price) so banner/title-only
  // sheets are never mistaken for a table.
  for (let ri = 0; ri < Math.min(rows.length, 25); ri++) {
    const row = rows[ri] || [];
    const cls = row.map((h) => _classifyHeader(h)).filter(Boolean);
    if (cls.length >= 2) return { row, idx: ri };
    const fields = new Set(cls);
    if (fields.has("name") && fields.has("price")) return { row, idx: ri };
    // "Mô tả sản phẩm" (description) + sku/price is still a real header row —
    // the description column holds the actual product name in many catalogues.
    if (fields.has("description") && (fields.has("sku") || fields.has("price"))) return { row, idx: ri };
  }
  return null;
}

function rowsToProducts(rows: any[][], opts: any, warnings: string[]): any {
  if (!rows || !rows.length) return { ok: false, products: [], ready: false, reason: "Empty file" };
  // Locate the real header row (skipping title/banner rows above it).
  const found = _findHeaderRow(rows);
  const headerRow = found ? found.row : (rows[0] || []);
  const headerIdx = found ? found.idx : 0;
  if (!found) {
    // headerless: caller provides field order via opts.headers or default.
    const defH = (opts.headers || ["name", "sku", "price", "brand", "description"]).slice(0, headerRow.length);
    const products = rows
      .filter((r, i) => {
        // A "Tên - Giá" single-cell row ("Máy giặt Hitachi ... - 6.490.000đ")
        // is a product even though it has no separate cells. The old code only
        // kept row 0 blindly + rows passing the column-based check; that made
        // order-form label rows ("Khách hàng: ...") become fake products and
        // real single-cell product rows get dropped.
        if (_rowLooksLikeSingleCellProduct(r)) return true;
        return _rowLooksLikeProduct(r, defH.map((x: string) => x), opts);
      })
      .map((r) => normalizeCatalogProduct(r, { headers: defH }))
      .filter((p) => p && (p.name || p.sku));
    return finalizeProducts(products, opts, warnings);
  }
  // Data rows = everything AFTER the located header row.
  const products = rows.slice(headerIdx + 1)
    .filter((r) => _rowLooksLikeProduct(r, headerRow, opts))
    .map((r) => normalizeCatalogProduct(r, { headers: headerRow.map(_cellVal) }))
    .filter((p) => p && (p.name || p.sku));
  const fin = finalizeProducts(products, opts, warnings);
  if (fin && typeof fin === "object" && Array.isArray(fin.products)) fin._headerFound = true;
  return fin;
}

async function parsePdf(bytes: Uint8Array, opts: any, warnings: string[]): Promise<any> {
  // Polyfill DOMMatrix (pdfjs needs it in Bun).
  if (!(globalThis as any).DOMMatrix) {
    class M { constructor(){ this.a=1;this.b=0;this.c=0;this.d=1;this.e=0;this.f=0;this.m11=1;this.m12=0;this.m13=0;this.m14=0;this.m21=0;this.m22=1;this.m23=0;this.m24=0;this.m31=0;this.m32=0;this.m33=1;this.m34=0;this.m41=0;this.m42=0;this.m43=0;this.m44=1;} }
    (globalThis as any).DOMMatrix = M;
  }
  const { PDFParse } = await import("pdf-parse");
  const p = new PDFParse({ data: bytes.buffer ? bytes.buffer.slice(bytes.byteOffset, bytes.byteOffset + bytes.byteLength) : bytes });
  try {
    const r = await p.getText();
    await p.destroy();
    const text = String(r?.text || "");
    if (!text.trim()) {
      warnings.push("PDF has no extractable text layer (may be scanned images) — try updating to use AI/vision OCR.");
      return { ok: false, products: [], ready: false, reason: "Scanned/image PDF has no text layer.", warnings };
    }
    return parsePdfTextLines(text.split("\n"), opts, warnings);
  } catch (e: any) {
    try { await p.destroy(); } catch (_) {}
    return { ok: false, products: [], ready: false, reason: String(e?.message || e), warnings };
  }
}

// Parse PDF plain-text lines into products. Strategy: if there's a header-like
// first line pipeline them through table logic; otherwise fall back to treating
// each meaningful line as one product (name + price regex).
function parsePdfTextLines(lines: string[], opts: any, warnings: string[]): any {
  // Try to build rows by splitting each line on 2+ whitespace/pipe delimiters.
  const made = lineRowsToProducts(lines, opts, warnings);
  return made;
}

// Robust OCR/catalogue line parser: finds the price anywhere in the line (dot-
// grouped thousands OR a standalone 4+ digit integer), splits name/sku before it
// and brand/qty after it. Handles ragged OCR text whose column spacing does NOT
// align with the header (the position-slicing path fails on such input). Returns
// null when the line has no parseable price (then callers keep other strategies).
function parseRaggedRow(line: string, opts: any): any | null {
  const L = String(line || "");
  // (a) grouped thousands — accept BOTH dot (1.290.000) and comma (6,290,000)
  //     VND separators, plus optional currency suffix.
  // (a) grouped thousands — accept dot (1.290.000) and comma (6,290,000) VND
  //     separators, plus optional currency suffix. OCR often renders "đ" (đồng)
  //     as "d", so include a lone "d" as a valid currency token too.
  let mt = L.match(/(?<![\d.,])(\d{1,3}(?:[.,]\d{3})+)(?!\d)(\s*(?:đ|₫|đồng|dong|vnd|d))?/i);
  if (!mt) {
    // (b) standalone integer token of 4+ digits separated from letters
    mt = L.match(/(?<![\wđ₫])(\d{4,})(?![\wđ₫])(\s*(?:đ|₫|đồng|dong|vnd|d))?/i);
  }
  if (!mt) return null;
  // Normalize the captured thousands to dots (so parseVnd parses them correctly);
  // a pure 4-digit "6,290" without three trailing digits is NOT thousands.
  const priceText = mt[1] + (mt[2] ? mt[2].trim() : "");
  const priceStart = mt.index as number;
  const full = mt[0];
  const before = L.slice(0, priceStart).trim();
  let after = L.slice(priceStart + full.length).trim();
  const out: any = { price: priceText };
  // numeric qty token immediately after the price → drop (not product text)
  let aftk = after.split(/\s+/);
  if (aftk.length && /^\d{1,3}$/.test(aftk[0])) after = aftk.slice(1).join(" ").trim();
  // A lone currency token (đ, d, ₫, vnd, đồng) directly after the price is NOT a
  // brand — strip it even when extra spaces separate it ("6.290.000d  Hitachi").
  after = after.replace(/^[đd₫]\s+/, "");
  // collapse multiple spaces and drop leading currency-only fragment
  after = after.replace(/\s{2,}/g, " ").trim();
  if (/^[đd₫vnd]{1,4}$/i.test(after)) after = "";
  if (after && /[a-zA-ZÀ-ỹ]/.test(after)) out.brand = after;
  // trailing code-like SKU at the end of the "before" segment: "EH-90", "LO-45",
  // "MQ-8", "MM-6030" (letters(+dash/space)+digits, short, contains a digit).
  const sm = before.match(/(?:^|\s)([A-Za-z][A-Za-z0-9]* ?[-/]? ?\d[\w.\-\/]*)$/);
  if (sm && /\d/.test(sm[1]) && sm[1].length <= 20 && /[A-Z]/.test(sm[1])) {
    out.sku = sm[1].replace(/\s+/g, "");
    out.name = before.slice(0, before.length - sm[1].length).replace(/^[\s\-–—]+|[\s\-–—]+$/g, "") || before;
  } else {
    out.name = before;
  }
  // Strip a leading OCR row ordinal ("1 May giat..." -> "May giat...") when the
  // rest looks like a product name (starts with a letter). Never touch names
  // that genuinely begin with digits.
  if (out.name && /^\d{1,3}[.\s]\s*[A-Za-zÀ-ỹ]/.test(out.name)) {
    out.name = out.name.replace(/^\d{1,3}[.\s]+\s*/, "").trim();
  }
  // Trailing pure-digit SKU/model ("...BD-1054HVOW 357869" -> name + sku=357869).
  // Catalogue rows often carry a numeric mã SP after the name; without this the
  // digits stay glued to the product name.
  if (out.name && !out.sku) {
    const tm = out.name.match(/^(.*?)(?:\s+)(\d{5,8})$/);
    if (tm && /[a-zA-ZÀ-ỹ]/.test(tm[1])) { out.name = tm[1].trim(); out.sku = tm[2]; }
  }
  if (!out.name && out.sku) out.name = "";
  return out;
}

function lineRowsToProducts(lines: string[], opts: any, warnings: string[]): any {
  // Heuristic table splitter: detect a header line (contains "tên" & "giá"),
  // then treat subsequent lines as rows sliced BY HEADER COLUMN POSITIONS.
  // This is far more accurate than naive whitespace splitting because product
  // names often contain multiple spaces.
  // A header line must contain a NAME column word AND a PRICE column word.
  // Use word-boundary checks so "giat" (washer) or "giay" (shoe) never trigger
  // the header detector — only a genuine header like "Tên SP / Giá bán".
  const _hasNameCol = (n: string) => /(?:^|\s)(?:ten|ten sp|ten san pham|san pham|ten hang|ma san pham|ma sp|hang|name|product)(?:\s|$)/.test(n);
  const _hasPriceCol = (n: string) => /(?:^|\s)(?:gia|gia ban|gia niem yet|gia km|don gia|price|gia khuyen mai|gia chua vat)(?:\s|$)/.test(n);
  const headerIdx = lines.findIndex((l) => {
    const n = _normHeader(l);
    return _hasNameCol(n) && _hasPriceCol(n) && n.length < 160 && /\s{2,}|\t/.test(l);
  });
  let products: any[] = [];
  const headerLine = headerIdx >= 0 ? lines[headerIdx] : "";
  if (headerIdx >= 0) {
    // Build column x-positions from the header (align on 2+ spaces).
    const cols: Array<{ start: number; end: number; field: string | null }> = [];
    const re = /(\S.*?)(?=\s{2,}|\s*$)/g;
    let mt: RegExpExecArray | null, lastEnd = 0;
    while ((mt = re.exec(headerLine)) !== null) {
      const start = mt.index;
      const end = start + mt[0].length;
      const text = headerLine.slice(start, end).trim();
      const field = _classifyHeader(text);
      cols.push({ start, end, field });
      lastEnd = end;
    }
    // token-based fallback columns (simple 2+ space split)
    const tokFields = headerLine.split(/\s{2,}|\t+/).map((c) => c.trim()).filter(Boolean).map(_classifyHeader);
    for (let i = headerIdx + 1; i < lines.length; i++) {
      const line = lines[i];
      if (!line || !line.trim()) continue;
      if (_isNoiseName(line.trim(), /\d/.test(line))) continue; // totals/footers/labels/titles (keep if line has a number = likely a price)
      if (/^(trang|page)\s*\d+$/i.test(line.trim())) continue;
      const obj: any = {};
      const used: Array<[string, string]> = [];
      cols.forEach((c, ci) => {
        const f = c.field;
        if (!f) return;
        const s = c.start;
        const e = (cols[ci + 1] ? cols[ci + 1].start : (line.length || c.end));
        let seg = line.slice(s, Math.max(s, Math.min(e, line.length || c.end))).trim();
        used.push([f, seg]);
      });
      used.forEach(([f, v]) => { if (v && !obj[f]) obj[f] = v; });
      // tokenized interpretation (fallback when position slicing misaligns)
      const tokObj: any = {};
      const tokens = line.split(/\s{2,}|\t+/).map((t) => t.trim()).filter(Boolean);
      tokFields.forEach((f, ti) => { if (f && tokens[ti]) tokObj[f] = tokens[ti]; });
      // Quality check: choose the interpretation that yields a parseable price
      // and/or a code-like SKU.
      function quality(o: any): number {
        let q = 0;
        if (o.name && String(o.name).trim().length >= 4) q += 2;
        if (parseVnd(o.price) > 0) q += 3;
        if (o.sku && /^[A-Za-z0-9][A-Za-z0-9.\-]{1,20}$/.test(String(o.sku).trim())) q += 3;
        if (o.brand) q += 1;
        return q;
      }
      var ragged = parseRaggedRow(line, opts);
      const q0 = quality(ragged || {}), q1 = quality(obj), q2 = quality(tokObj);
      const bestBase = Math.max(q1, q2);
      // Prefer the robust regex parser on any tie (it cleanly separates
      // name/sku/price/brand even when OCR columns are ragged).
      let winner: any;
      if (q0 >= bestBase && ragged) winner = ragged;
      else if (q2 > q1) winner = tokObj;
      else winner = obj;
      // OCR noise cleanup: strip spaces inside codes ("G | L-400" → "GL-400").
      if (winner.sku) winner.sku = String(winner.sku).replace(/\s+/g, "").trim();
      // Only merge a trailing name word into the SKU when the SKU has NO digit
      // yet (mangled OCR code like "G L" / letters only). A clean SKU that
      // already contains digits (e.g. "GL-400", "EH-90") must NOT absorb the
      // last word of the product name (e.g. "nau" → would corrupt to "nauGL-400").
      const _skuHasDigit = /\d/.test(String(winner.sku || ""));
      if (!_skuHasDigit && winner.name && winner.sku && /(?:^|\s)[A-Za-z0-9]{1,3}$/.test(String(winner.name).trim())) {
        const nm = String(winner.name).trim();
        const m2 = nm.match(/^(.*?)(?:\s+([A-Za-z0-9]{1,3}))$/);
        if (m2 && m2[2] && winner.sku && !/^[A-Za-z0-9]{1,3}$/.test(String(winner.sku).trim())) {
          const merged = m2[2] + String(winner.sku).trim();
          if (merged.length >= 3 && merged.length <= 24) { winner.name = m2[1]; winner.sku = merged; }
        }
      }
      if (winner.name && /^(trang|page)\s*\d+$/i.test(String(winner.name).trim())) continue;
      if (winner.name && _isNoiseName(String(winner.name), parseVnd(winner.price) > 0)) continue;
      if (winner.name || winner.sku || winner.price) {
        products.push(normalizeCatalogProduct(winner, opts));
      }
    }
  } else {
    // No header: one product per meaningful line. Use the robust ragged-row
    // parser (which finds the REAL VND price anywhere in the line and splits
    // name/sku before it, brand after it) instead of naively grabbing the first
    // number — the naive regex wrongly treated a leading row ordinal ("3 Bep
    // tu...") as the price. Fall back to whole-line-as-name only if no price.
    lines.forEach((l) => {
      const t = (l || "").trim();
      if (!t || t.length < 3) return;
      if (_isNoiseName(t, /\d/.test(t))) return; // skip totals/footers/labels/titles (keep if a number hints a price)
      if (/^(trang|page)\s*\d+$/i.test(t)) return; // "Trang 2", "page 3"
      const ragged = parseRaggedRow(t, opts);
      if (ragged && (ragged.price || ragged.sku)) {
        const p = normalizeCatalogProduct(ragged, opts);
        if (p && (p.name || p.sku || p.price)) products.push(p);
        return;
      }
      const p: any = { name: t };
      const pm = t.match(/([\d][\d.,]*(?:\s*(?:tr|đ|₫|dồng))?)/);
      if (pm) { p.price = pm[1]; p.name = t.replace(pm[1], "").trim(); }
      if (p.name && _isNoiseName(p.name, /\d/.test(p.price || ""))) return;
      if (p.name || p.price) products.push(p);
    });
  }
  return finalizeProducts(products, opts, warnings);
}

// Image catalogue + scanned PDF: best-effort via AI vision (Qwen2.5-VL). If AI
// unavailable, degrade gracefully with a clear, actionable message — NEVER fail
// the request. Excel/CSV untouched.
async function parseImageCatalogue(bytes: Uint8Array, opts: any, warnings: string[]): Promise<any> {
  const b64 = Buffer.from(bytes).toString("base64");
  const mime = (opts.mime || guessMime(bytes));
  try {
    const ai = await aiExtractTable(b64, mime, opts);
    if (ai.ok && Array.isArray(ai.products) && ai.products.length) {
      const cleaned = ai.products.map((p: any) => normalizeCatalogProduct(p, opts)).filter((p: any) => p && (p.name || p.sku));
      if (cleaned.length) return finalizeProducts(cleaned, opts, warnings);
    }
    const why = (ai && ai.reason) || "unknown";
    warnings.push("AI/vision không đọc được ảnh catalogue (" + why + "). Vui lòng dùng file Excel/CSV cho kết quả chính xác, hoặc dùng tính năng 'Thêm SP' để thêm từng ảnh (OCR).");
  } catch (e: any) {
    warnings.push("AI vision error: " + String(e?.message || e) + ". Vui lòng dùng file Excel/CSV.");
  }
  return { ok: true, products: [], ready: false, reason: "Cần file Excel/CSV hoặc cấu hình AI vision để đọc ảnh catalogue.", warnings };
}

// Call Qwen2.5-VL via HF Inference Providers router. Robust JSON parse; never
// throws on model/credits errors — returns { ok:false, reason }.
async function aiExtractTable(b64: string, mime: string, opts: any): Promise<any> {
  const token = (process.env.HF_INFERENCE_TOKEN || process.env.HF_TOKEN || "").trim();
  if (!token) return { ok: false, reason: "no HF token" };
  const model = opts.aiModel || "Qwen/Qwen2.5-VL-7B-Instruct";
  const prompt = `You are extracting products from a Vietnamese home-appliance catalogue table shown in this image.
Look at the rows and columns. Each row is ONE product. Detect columns for: name (Tên), sku/mã, brand (thương hiệu), price (giá), description, features (tính năng), specs (thông số kỹ thuật), category (danh mục).
Return a JSON array of objects. For each product use ONLY these keys: name, sku, brand, price, description, features, specs, category.
- price as a plain number string using dot thousands separator, e.g. "5.500.000". If price is in millions like "5tr5", convert to "5.500.000".
- specs and features each as a JSON array of strings.
- preserve Vietnamese diacritics exactly.
- If a column is missing for a row, use empty string (or empty array).
Return ONLY valid JSON (a single array). No markdown, no extra text.`;

  const body = {
    model,
    messages: [
      { role: "user", content: [
        { type: "text", text: prompt },
        { type: "image_url", image_url: { url: `data:${mime};base64,${b64}` } },
      ]},
    ],
    max_tokens: 4096,
    temperature: 0,
  };
  let resp: Response;
  try {
    resp = await fetch("https://router.huggingface.co/v1/chat/completions", {
      method: "POST",
      headers: { "Content-Type": "application/json", "Authorization": "Bearer " + token, "User-Agent": "vai-avatar2-bulk" },
      body: JSON.stringify(body),
      signal: AbortSignal.timeout(60000),
    });
  } catch (e: any) {
    return { ok: false, reason: String(e?.message || e) };
  }
  if (!resp.ok) {
    const t = await resp.text().catch(() => "");
    return { ok: false, reason: "HTTP " + resp.status + " " + t.slice(0, 120), status: resp.status };
  }
  let content = "";
  try {
    const data: any = await resp.json();
    content = String(data?.choices?.[0]?.message?.content || "").trim();
  } catch (e: any) {
    return { ok: false, reason: "Bad AI response JSON" };
  }
  // Robust JSON parse
  let arr: any = null;
  try { arr = JSON.parse(content); }
  catch (e1) {
    const m = content.match(/\[[\s\S]*\]/);
    if (m) { try { arr = JSON.parse(m[0]); } catch (e2) {} }
  }
  if (!Array.isArray(arr)) return { ok: false, reason: "AI returned non-array JSON" };
  products: for (let i = 0; i < arr.length; i++) {
    // coerce features/specs from strings if model disagreed
    const p = arr[i];
    if (p && typeof p === "object") {
      p.features = splitFeatures(p.features);
      if (p.specs && typeof p.specs === "string") {
        p.specs = Object.fromEntries(String(p.specs).split(/\n|;/).map((s) => { const mm = s.match(/^([^:：]+)[:：]\s*(.+)$/); return mm ? [mm[1].trim(), mm[2].trim()] : [null, null]; }).filter((x: any) => x[0]));
      }
    }
  }
  return { ok: true, products: arr };
}

function guessMime(bytes: Uint8Array): string {
  // png / jpeg / webp / gif magic numbers
  if (bytes[0] === 0x89 && bytes[1] === 0x50) return "image/png";
  if (bytes[0] === 0xff && bytes[1] === 0xd8) return "image/jpeg";
  if (bytes[0] === 0x52 && bytes[1] === 0x49 && bytes[2] === 0x46 && bytes[3] === 0x46) return "image/webp";
  if (bytes[0] === 0x47 && bytes[1] === 0x49 && bytes[2] === 0x46) return "image/gif";
  return "image/jpeg";
}

function finalizeProducts(products: any[], opts: any, warnings: string[]): any {
  const seen = new Set<string>();
  const deduped: any[] = [];
  (Array.isArray(products) ? products : []).forEach((p: any) => {
    if (!p || typeof p !== "object") return;
    // Hard-exclude noise/total rows before anything else.
    const nm = String(p.name || "").trim();
    const skuV = String(p.sku || p.model || "").trim();
    if (!nm && !skuV) return;
    if (nm && _isNoiseName(nm, parseVnd(p.priceNum != null ? p.priceNum : p.price || p.salePrice) > 0)) return; // "TỔNG CỘNG", "Cộng tiền", "STT", "Lưu ý", "MÁY GIẶT" (no price)
    if (/^(trang|page)\s*\d+$/i.test(nm)) return;          // "Trang 2", "page 5" page footers
    // Non-data rows: unit-of-measure, note/label lines, section markers.
    // Match against the diacritic-stripped normalized name so "Ghi chú: ...",
    // "Đơn vị tính: ...", "Ghi chú", "Đơn vị tính" (with real Vietnamese
    // diacritics) are all caught — previously only the ASCII forms matched.
    const nmNorm = _normHeader(nm);
    if (nmNorm && /^(don vi(tinh)?|dvt|doi vi|ghi chu|ky hieu|loai|nhom|hang|huong dan|ngay\b|thang\b|trang\b|page\b)\s*[:：]?\s*.{0,60}$/.test(nmNorm)) return;
    if (nm && /^(nhom|phan nhom|danh muc|muc luc|bang gia|toan bo|san pham)$/i.test(nm)) return;
    if (nm && /^[\d.,\sđ₫%]+$/.test(nm) && !skuV) return;  // numeric-only name (a total amount)
    if (nm && /[a-zA-ZÀ-ỹ]/.test(nm) === false && !skuV) return;
    if (!p.name && !p.sku) return;
    // price parsing — prefer the sale price (giá KM) as the display price;
    // the list price (niêm yết) becomes listPn. Both prices together make the
    // product eligible for FLASHSALE (<40% discount) / BIGSALE (>=40%).
    // Single-cell rows ("Máy giặt ... - 6.490.000đ"): strip the trailing price
    // out of the NAME so the name stays clean and the price is real.
    if (!p.price && p.name && /[\d.,]/.test(p.name)) {
      const pm = String(p.name).match(/^(.*?)[\s\-–—|:]*(\d{1,3}(?:[.,]\d{3})+|\d{4,})\s*(?:đ|₫|dong|vnd|d)?\s*$/i);
      if (pm && pm[1] && _normHeader(pm[1]).length >= 4 && !_isLabelLine(_normHeader(pm[1]))) {
        p.name = pm[1].trim();
        p.price = pm[2];
      }
    }
    let _saleRaw = (p.salePrice != null && p.salePrice !== "") ? p.salePrice : (p.price != null ? p.price : p.priceNum);
    p.pn = parseVnd(p.priceNum != null ? p.priceNum : _saleRaw);
    if (!p.pn && p.price != null) p.pn = parseVnd(p.price);
    // List-price-only rows ("Giá niêm yết" but no "Giá bán" column): the list
    // price becomes the display price, otherwise a row whose ONLY price is the
    // niêm yết would end up pn=0 and be dropped as a non-product.
    if (!p.pn && p.listPrice != null) p.pn = parseVnd(p.listPrice);
    if (!p.price && p.pn) p.price = p.pn.toLocaleString("vi-VN") + "đ";
    if (p.listPrice != null) p.listPn = parseVnd(p.listPrice);
    if (p.salePrice != null && p.salePrice !== "") p.salePn = parseVnd(p.salePrice);
    else if (p.pn) p.salePn = p.pn;
    // normalize: list > sale (niêm yết is the higher price)
    if (p.listPn && p.salePn && p.listPn < p.salePn) { const _t = p.listPn; p.listPn = p.salePn; p.salePn = _t; }
    if (p.listPn && !p.salePn) p.salePn = p.listPn;
    if (p.salePn && !p.listPn) p.listPn = p.salePn;
    // features
    p.features = splitFeatures(p.features);
    if (!Array.isArray(p.features)) p.features = [];
    if (!p.category && opts.defaultCategory) p.category = opts.defaultCategory;
    // Dedup key: prefer SKU. When SKU is missing, a product is identified by
    // name + sale price + a per-row index — otherwise two distinct rows that
    // share an empty SKU and the SAME name (e.g. same model with different
    // prices/variants) would wrongly collapse into one product ("dòng có sp bị
    // mất"). We still dedupe exact (sku present) repeats.
    let key: string;
    if (skuV) key = String((skuV + "|" + p.name || "").toLowerCase());
    else key = String(("|" + (p.name || "").toLowerCase()) + "|" + String(p.pn || 0) + "|" + String(p.listPn || 0) + "|" + String(p.salePn || 0));
    if (seen.has(key)) { warnings.push("Duplicate row skipped: " + (p.name || p.sku)); return; }
    seen.add(key);
    deduped.push(p);
  });
  if (!deduped.length) return { ok: true, products: [], ready: false, reason: "No product rows detected.", warnings };
  return { ok: true, products: deduped, ready: true, count: deduped.length, warnings };
}