281 lines
8.2 KiB
JavaScript
281 lines
8.2 KiB
JavaScript
#!/usr/bin/env node
|
|
/**
|
|
* Load downtime + scrap catalogs from Excel under ./reasons/ into Postgres.
|
|
*
|
|
* npx dotenv -e .env -- node scripts/seed-reason-catalog-from-xlsx.mjs --org-id <uuid>
|
|
* npx dotenv -e .env -- node scripts/seed-reason-catalog-from-xlsx.mjs --org-slug my-org --replace
|
|
*
|
|
* --dry-run parse and print counts only
|
|
* --replace delete existing reason_catalog_* rows for the org before insert
|
|
*/
|
|
|
|
import { readFileSync, existsSync } from "fs";
|
|
import path from "path";
|
|
import { fileURLToPath } from "url";
|
|
import * as XLSX from "xlsx";
|
|
import { PrismaClient } from "@prisma/client";
|
|
|
|
const __dirname = path.dirname(fileURLToPath(import.meta.url));
|
|
const ROOT = path.join(__dirname, "..");
|
|
|
|
const prisma = new PrismaClient();
|
|
|
|
function composeReasonCode(prefix, suffix) {
|
|
const p = String(prefix ?? "").trim().toUpperCase();
|
|
const s = String(suffix ?? "").trim();
|
|
if (/^\d+$/.test(s) && p.length >= 3) {
|
|
return `${p}-${s}`.toUpperCase();
|
|
}
|
|
return `${p}${s}`.toUpperCase();
|
|
}
|
|
|
|
function parseArgs(argv) {
|
|
const out = {
|
|
dryRun: false,
|
|
replace: false,
|
|
orgId: null,
|
|
orgSlug: null,
|
|
downtimePath: path.join(ROOT, "reasons", "Claves Tiempo Muerto.xlsx"),
|
|
scrapPath: path.join(ROOT, "reasons", "Claves de Scrap.xlsx"),
|
|
};
|
|
|
|
for (let i = 0; i < argv.length; i += 1) {
|
|
const t = argv[i];
|
|
if (t === "--dry-run") out.dryRun = true;
|
|
else if (t === "--replace") out.replace = true;
|
|
else if (t === "--org-id") {
|
|
out.orgId = argv[i + 1] || null;
|
|
i += 1;
|
|
} else if (t === "--org-slug") {
|
|
out.orgSlug = argv[i + 1] || null;
|
|
i += 1;
|
|
} else if (t === "--downtime") {
|
|
out.downtimePath = argv[i + 1] || out.downtimePath;
|
|
i += 1;
|
|
} else if (t === "--scrap") {
|
|
out.scrapPath = argv[i + 1] || out.scrapPath;
|
|
i += 1;
|
|
} else {
|
|
throw new Error(`Unknown arg: ${t}`);
|
|
}
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function readWorkbook(filePath) {
|
|
if (!existsSync(filePath)) {
|
|
throw new Error(`File not found: ${filePath}`);
|
|
}
|
|
const buf = readFileSync(filePath);
|
|
return XLSX.read(buf, { type: "buffer" });
|
|
}
|
|
|
|
/** @returns {{ kind:'downtime', name:string, codePrefix:string, items: { suffix:string, name:string }[] }[]} */
|
|
function parseDowntimeXlsx(filePath) {
|
|
const wb = readWorkbook(filePath);
|
|
const data = XLSX.utils.sheet_to_json(wb.Sheets["Sheet1"], { header: 1, defval: "" });
|
|
const headerRowIdx = 3;
|
|
const header = data[headerRowIdx] || [];
|
|
const cols = [];
|
|
for (let c = 0; c < header.length; c += 1) {
|
|
if (String(header[c] || "").trim()) cols.push(c);
|
|
}
|
|
|
|
const categoryByCol = {};
|
|
cols.forEach((c) => {
|
|
categoryByCol[c] = String(header[c]).trim();
|
|
});
|
|
|
|
const CODE = /^([A-Z0-9][A-Za-z0-9-]*)-(\d+)\s+(.*)$/;
|
|
const rawItems = [];
|
|
|
|
for (let r = headerRowIdx + 1; r < data.length; r += 1) {
|
|
const row = data[r] || [];
|
|
for (const c of cols) {
|
|
const cell = String(row[c] ?? "").trim();
|
|
if (!cell) continue;
|
|
|
|
const m = cell.match(CODE);
|
|
if (m) {
|
|
rawItems.push({
|
|
col: c,
|
|
categoryLabel: categoryByCol[c],
|
|
prefix: m[1].toUpperCase(),
|
|
suffix: m[2],
|
|
name: m[3].trim(),
|
|
row: r,
|
|
});
|
|
} else if (cell.length > 2 && cell === cell.toUpperCase() && !/\d/.test(cell)) {
|
|
categoryByCol[c] = cell;
|
|
}
|
|
}
|
|
}
|
|
|
|
/** @type {Map<string, { kind:'downtime', name:string, codePrefix:string, items: { suffix:string, name:string }[]}>} */
|
|
const catMap = new Map();
|
|
|
|
function catKey(categoryName, prefix) {
|
|
return `${categoryName}\0${prefix}`;
|
|
}
|
|
|
|
for (const it of rawItems) {
|
|
const key = catKey(it.categoryLabel, it.prefix);
|
|
let bucket = catMap.get(key);
|
|
if (!bucket) {
|
|
bucket = { kind: "downtime", name: it.categoryLabel, codePrefix: it.prefix, items: [] };
|
|
catMap.set(key, bucket);
|
|
}
|
|
bucket.items.push({ suffix: it.suffix, name: it.name });
|
|
}
|
|
|
|
/** Dedupe suffix per category (keep first description). */
|
|
for (const b of catMap.values()) {
|
|
const seen = new Map();
|
|
const next = [];
|
|
for (const row of b.items) {
|
|
if (seen.has(row.suffix)) continue;
|
|
seen.set(row.suffix, true);
|
|
next.push(row);
|
|
}
|
|
b.items = next.sort((a, b) => Number(a.suffix) - Number(b.suffix));
|
|
}
|
|
|
|
return [...catMap.values()];
|
|
}
|
|
|
|
function parseScrapXlsx(filePath) {
|
|
const wb = readWorkbook(filePath);
|
|
const data = XLSX.utils.sheet_to_json(wb.Sheets["Sheet1"], { header: 1, defval: "" });
|
|
/** @type { { suffix:string, name:string, full:string }[] } */
|
|
const rows = [];
|
|
for (let r = 0; r < data.length; r += 1) {
|
|
const clave = String(data[r][0] ?? "").trim();
|
|
const desc = String(data[r][1] ?? "").trim().replace(/\s+/g, " ");
|
|
if (!clave || /^clave/i.test(clave)) continue;
|
|
if (!desc || /Rev\.?\s*[A-Z]/i.test(desc)) continue;
|
|
const m = clave.toUpperCase().match(/^([A-Z]+)(\d+)$/);
|
|
if (!m) {
|
|
console.warn(`[scrap] skip row ${r}:`, clave);
|
|
continue;
|
|
}
|
|
rows.push({
|
|
full: `${m[1]}${m[2]}`,
|
|
suffix: m[2],
|
|
name: desc,
|
|
});
|
|
}
|
|
|
|
/** Single category when all MX… */
|
|
const prefixes = new Set(rows.map((x) => x.full.replace(/\d+$/, "")));
|
|
if (prefixes.size !== 1) {
|
|
console.warn("[scrap] multiple prefixes:", [...prefixes]);
|
|
}
|
|
const codePrefix = [...prefixes][0] || "MX";
|
|
const items = rows.map(({ suffix, name }) => ({ suffix, name }));
|
|
return [{ kind: "scrap", name: "Scrap", codePrefix, items }];
|
|
}
|
|
|
|
async function main() {
|
|
const args = parseArgs(process.argv.slice(2));
|
|
|
|
let orgId = args.orgId;
|
|
if (!orgId && args.orgSlug) {
|
|
const org = await prisma.org.findUnique({ where: { slug: args.orgSlug }, select: { id: true } });
|
|
if (!org) throw new Error(`Org slug not found: ${args.orgSlug}`);
|
|
orgId = org.id;
|
|
}
|
|
if (!orgId) {
|
|
console.error("Provide --org-id <uuid> or --org-slug <slug>");
|
|
process.exit(1);
|
|
}
|
|
|
|
const downtimeCats = parseDowntimeXlsx(args.downtimePath);
|
|
const scrapCats = parseScrapXlsx(args.scrapPath);
|
|
|
|
const totalItems =
|
|
downtimeCats.reduce((n, c) => n + c.items.length, 0) + scrapCats.reduce((n, c) => n + c.items.length, 0);
|
|
|
|
console.log("[seed] downtime categories:", downtimeCats.length, "scrap categories:", scrapCats.length);
|
|
console.log("[seed] total items:", totalItems);
|
|
|
|
if (args.dryRun) {
|
|
console.log(JSON.stringify({ downtimeCats: downtimeCats.slice(0, 2), scrapCats }, null, 2));
|
|
return;
|
|
}
|
|
|
|
const existing = await prisma.reasonCatalogCategory.count({ where: { orgId } });
|
|
if (existing && !args.replace) {
|
|
console.error(
|
|
`Org already has ${existing} catalog categor(ies). Re-run with --replace to wipe and reload, or use Control Tower UI.`
|
|
);
|
|
process.exit(1);
|
|
}
|
|
|
|
const bundled = [...downtimeCats, ...scrapCats];
|
|
/** @type {string[]} */
|
|
const dupCheck = [];
|
|
|
|
await prisma.$transaction(async (tx) => {
|
|
if (args.replace) {
|
|
await tx.reasonCatalogItem.deleteMany({ where: { orgId } });
|
|
await tx.reasonCatalogCategory.deleteMany({ where: { orgId } });
|
|
}
|
|
|
|
let catOrder = 0;
|
|
for (const block of bundled) {
|
|
const category = await tx.reasonCatalogCategory.create({
|
|
data: {
|
|
orgId,
|
|
kind: block.kind,
|
|
name: block.name,
|
|
codePrefix: block.codePrefix,
|
|
sortOrder: catOrder++,
|
|
active: true,
|
|
},
|
|
});
|
|
|
|
let itOrder = 0;
|
|
for (const row of block.items) {
|
|
const reasonCode = composeReasonCode(block.codePrefix, row.suffix);
|
|
dupCheck.push(reasonCode);
|
|
|
|
await tx.reasonCatalogItem.create({
|
|
data: {
|
|
orgId,
|
|
categoryId: category.id,
|
|
name: row.name,
|
|
codeSuffix: row.suffix,
|
|
reasonCode,
|
|
sortOrder: itOrder++,
|
|
active: true,
|
|
},
|
|
});
|
|
}
|
|
}
|
|
|
|
await tx.orgSettings.update({
|
|
where: { orgId },
|
|
data: { version: { increment: 1 } },
|
|
});
|
|
});
|
|
|
|
const seen = new Set();
|
|
let dup = 0;
|
|
for (const rc of dupCheck) {
|
|
if (seen.has(rc)) dup++;
|
|
seen.add(rc);
|
|
}
|
|
if (dup) console.warn("[seed] duplicate reason_code skipped by DB unique?", dup);
|
|
|
|
console.log("[seed] done. Bump org_settings.version (+1).");
|
|
}
|
|
|
|
main()
|
|
.catch((e) => {
|
|
console.error(e);
|
|
process.exit(1);
|
|
})
|
|
.finally(async () => {
|
|
await prisma.$disconnect();
|
|
});
|