230 lines
7.3 KiB
JavaScript
230 lines
7.3 KiB
JavaScript
import "dotenv/config";
|
||
import fs from "fs";
|
||
import path from "path";
|
||
import { parse } from "csv-parse/sync";
|
||
import { pool } from "../src/modules/shared/db/pool.js";
|
||
|
||
function parseArgs() {
|
||
const args = process.argv.slice(2);
|
||
const out = { file: null, tenantKey: null, replace: true };
|
||
for (let i = 0; i < args.length; i++) {
|
||
const a = args[i];
|
||
if (a === "--file") out.file = args[++i];
|
||
else if (a === "--tenant-key") out.tenantKey = args[++i];
|
||
else if (a === "--no-replace") out.replace = false;
|
||
}
|
||
if (!out.file) {
|
||
throw new Error("Usage: node scripts/import-woo-snapshot.mjs --file <path> [--tenant-key <key>] [--no-replace]");
|
||
}
|
||
return out;
|
||
}
|
||
|
||
function parseNumber(val) {
|
||
if (val == null) return null;
|
||
const s = String(val).replace(/\./g, "").replace(",", ".").trim();
|
||
if (!s) return null;
|
||
const n = Number(s);
|
||
return Number.isFinite(n) ? n : null;
|
||
}
|
||
|
||
function parseBool(val) {
|
||
if (val == null) return null;
|
||
const s = String(val).trim().toLowerCase();
|
||
if (s === "1" || s === "si" || s === "sí" || s === "yes" || s === "true") return true;
|
||
if (s === "0" || s === "no" || s === "false") return false;
|
||
return null;
|
||
}
|
||
|
||
function splitList(val) {
|
||
if (!val) return [];
|
||
return String(val)
|
||
.split(",")
|
||
.map((v) => v.trim())
|
||
.filter(Boolean);
|
||
}
|
||
|
||
function extractAttributes(row) {
|
||
const attrs = {};
|
||
for (const key of Object.keys(row)) {
|
||
const m = /^Nombre del atributo (\d+)$/i.exec(key);
|
||
if (!m) continue;
|
||
const idx = m[1];
|
||
const name = String(row[key] || "").trim();
|
||
if (!name) continue;
|
||
const valuesKey = `Valor(es) del atributo ${idx}`;
|
||
const rawValues = row[valuesKey];
|
||
const values = String(rawValues || "")
|
||
.split("|")
|
||
.map((v) => v.trim())
|
||
.filter(Boolean);
|
||
attrs[name.toLowerCase()] = values.length ? values : [String(rawValues || "").trim()].filter(Boolean);
|
||
}
|
||
return attrs;
|
||
}
|
||
|
||
function normalizeRow(row) {
|
||
const wooId = Number(row["ID"] || row["ID"] || row["\uFEFFID"] || row["Id"] || row["id"] || null);
|
||
const type = String(row["Tipo"] || "").trim().toLowerCase();
|
||
const parentId = Number(row["Superior"] || null) || null;
|
||
const name = String(row["Nombre"] || "").trim();
|
||
const slug = String(row["Slug"] || row["slug"] || "").trim() || null;
|
||
const published = parseBool(row["Publicado"]);
|
||
const status = published === true ? "publish" : published === false ? "draft" : null;
|
||
const visibility = String(row["Visibilidad en el catálogo"] || "").trim() || null;
|
||
const priceRegular = parseNumber(row["Precio normal"]);
|
||
const priceSale = parseNumber(row["Precio rebajado"]);
|
||
const priceCurrent = priceSale != null ? priceSale : priceRegular;
|
||
const hasStock = parseBool(row["¿Existencias?"]);
|
||
const stockQty = parseNumber(row["Inventario"]);
|
||
const backorders = String(row["¿Permitir reservas de productos agotados?"] || "").trim().toLowerCase() || null;
|
||
const weightKg = parseNumber(row["Peso (kg)"]);
|
||
const categories = splitList(row["Categorías"]);
|
||
const tags = splitList(row["Etiquetas"]);
|
||
const attributes = extractAttributes(row);
|
||
if (weightKg != null) attributes["peso_kg"] = [String(weightKg)];
|
||
|
||
let stockStatus = null;
|
||
if (hasStock === true) {
|
||
if (stockQty == null || stockQty > 0) stockStatus = "instock";
|
||
else stockStatus = "outofstock";
|
||
} else if (hasStock === false) {
|
||
stockStatus = "outofstock";
|
||
}
|
||
|
||
return {
|
||
woo_id: wooId,
|
||
type,
|
||
parent_id: parentId,
|
||
name,
|
||
slug,
|
||
status,
|
||
catalog_visibility: visibility,
|
||
price_regular: priceRegular,
|
||
price_sale: priceSale,
|
||
price_current: priceCurrent,
|
||
stock_status: stockStatus,
|
||
stock_qty: stockQty == null ? null : Math.round(stockQty),
|
||
backorders,
|
||
categories,
|
||
tags,
|
||
attributes_normalized: attributes,
|
||
};
|
||
}
|
||
|
||
async function getTenants(tenantKey) {
|
||
if (tenantKey) {
|
||
const { rows } = await pool.query(`select id, key from tenants where key=$1`, [tenantKey]);
|
||
return rows;
|
||
}
|
||
const { rows } = await pool.query(`select id, key from tenants`);
|
||
return rows;
|
||
}
|
||
|
||
async function insertRun({ tenantId, total, source }) {
|
||
const { rows } = await pool.query(
|
||
`insert into woo_snapshot_runs (tenant_id, source, total_items) values ($1, $2, $3) returning id`,
|
||
[tenantId, source, total]
|
||
);
|
||
return rows[0]?.id || null;
|
||
}
|
||
|
||
async function upsertSnapshotItem({ tenantId, runId, item, raw }) {
|
||
const q = `
|
||
insert into woo_products_snapshot
|
||
(tenant_id, woo_id, type, parent_id, name, slug, status, catalog_visibility,
|
||
price_regular, price_sale, price_current, stock_status, stock_qty, backorders,
|
||
categories, tags, attributes_normalized, date_modified, run_id, raw, updated_at)
|
||
values
|
||
($1,$2,$3,$4,$5,$6,$7,$8,
|
||
$9,$10,$11,$12,$13,$14,
|
||
$15::jsonb,$16::jsonb,$17::jsonb,$18,$19,$20::jsonb,now())
|
||
on conflict (tenant_id, woo_id)
|
||
do update set
|
||
type = excluded.type,
|
||
parent_id = excluded.parent_id,
|
||
name = excluded.name,
|
||
slug = excluded.slug,
|
||
status = excluded.status,
|
||
catalog_visibility = excluded.catalog_visibility,
|
||
price_regular = excluded.price_regular,
|
||
price_sale = excluded.price_sale,
|
||
price_current = excluded.price_current,
|
||
stock_status = excluded.stock_status,
|
||
stock_qty = excluded.stock_qty,
|
||
backorders = excluded.backorders,
|
||
categories = excluded.categories,
|
||
tags = excluded.tags,
|
||
attributes_normalized = excluded.attributes_normalized,
|
||
date_modified = excluded.date_modified,
|
||
run_id = excluded.run_id,
|
||
raw = excluded.raw,
|
||
updated_at = now()
|
||
`;
|
||
await pool.query(q, [
|
||
tenantId,
|
||
item.woo_id,
|
||
item.type,
|
||
item.parent_id,
|
||
item.name,
|
||
item.slug,
|
||
item.status,
|
||
item.catalog_visibility,
|
||
item.price_regular,
|
||
item.price_sale,
|
||
item.price_current,
|
||
item.stock_status,
|
||
item.stock_qty,
|
||
item.backorders,
|
||
JSON.stringify(item.categories ?? []),
|
||
JSON.stringify(item.tags ?? []),
|
||
JSON.stringify(item.attributes_normalized ?? {}),
|
||
null,
|
||
runId,
|
||
JSON.stringify(raw ?? {}),
|
||
]);
|
||
}
|
||
|
||
async function deleteMissing({ tenantId, runId }) {
|
||
await pool.query(
|
||
`delete from woo_products_snapshot where tenant_id=$1 and coalesce(run_id,0) <> $2`,
|
||
[tenantId, runId]
|
||
);
|
||
}
|
||
|
||
async function main() {
|
||
const { file, tenantKey, replace } = parseArgs();
|
||
const abs = path.resolve(file);
|
||
const content = fs.readFileSync(abs);
|
||
const records = parse(content, {
|
||
columns: true,
|
||
skip_empty_lines: true,
|
||
relax_column_count: true,
|
||
relax_column_count_less: true,
|
||
relax_column_count_more: true,
|
||
});
|
||
const normalized = records.map((r) => ({ item: normalizeRow(r), raw: r })).filter((r) => r.item.woo_id && r.item.name);
|
||
|
||
const tenants = await getTenants(tenantKey);
|
||
if (!tenants.length) {
|
||
throw new Error("No tenants found for import");
|
||
}
|
||
|
||
for (const t of tenants) {
|
||
const runId = await insertRun({ tenantId: t.id, total: normalized.length, source: "csv" });
|
||
for (const row of normalized) {
|
||
await upsertSnapshotItem({ tenantId: t.id, runId, item: row.item, raw: row.raw });
|
||
}
|
||
if (replace && runId) {
|
||
await deleteMissing({ tenantId: t.id, runId });
|
||
}
|
||
console.log(`[import] tenant=${t.key} items=${normalized.length} run_id=${runId}`);
|
||
}
|
||
|
||
await pool.end();
|
||
}
|
||
|
||
main().catch((err) => {
|
||
console.error(err);
|
||
process.exit(1);
|
||
});
|