84 lines
3.1 KiB
TypeScript
84 lines
3.1 KiB
TypeScript
/**
|
|
* One-time backfill: copies IV_Class_ID (classId) from CW MSSQL ProductCatalog
|
|
* to the API PostgreSQL CatalogItem table.
|
|
*
|
|
* Run with: bun backfill-classid.ts
|
|
*/
|
|
|
|
import { PrismaMssql } from "@prisma/adapter-mssql";
|
|
import { PrismaClient as CwPrismaClient } from "./generated/prisma/client";
|
|
import pg from "/root/projects/optima/node_modules/.bun/pg@8.20.0+52bd52a0bccfa6a2/node_modules/pg/lib/index.js";
|
|
|
|
// In dalpuri's .env, DATABASE_URL is the CW MSSQL connection string.
|
|
const CW_DATABASE_URL = process.env.DATABASE_URL!;
|
|
|
|
// The API PostgreSQL URL — hardcoded to avoid env var ambiguity
|
|
const API_DATABASE_URL = "postgresql://optima:123web123@localhost:5432/optima";
|
|
|
|
const cwPrisma = new CwPrismaClient({ adapter: new PrismaMssql(CW_DATABASE_URL) });
|
|
const pgPool = new pg.Pool({ connectionString: API_DATABASE_URL });
|
|
|
|
async function main() {
|
|
console.log("[backfill-classid] Fetching ProductCatalog classId from CW MSSQL...");
|
|
const cwItems = await cwPrisma.productCatalog.findMany({
|
|
select: { catalogRecId: true, classId: true },
|
|
});
|
|
console.log(`[backfill-classid] Fetched ${cwItems.length} CW catalog items`);
|
|
|
|
let updated = 0;
|
|
let skipped = 0;
|
|
let failed = 0;
|
|
|
|
// Batch updates in groups of 500
|
|
const BATCH_SIZE = 500;
|
|
for (let i = 0; i < cwItems.length; i += BATCH_SIZE) {
|
|
const batch = cwItems.slice(i, i + BATCH_SIZE);
|
|
await Promise.all(
|
|
batch.map(async (item) => {
|
|
try {
|
|
const result = await pgPool.query(
|
|
'UPDATE "CatalogItem" SET "classId" = $1 WHERE "id" = $2',
|
|
[item.classId, item.catalogRecId]
|
|
);
|
|
if (result.rowCount && result.rowCount > 0) {
|
|
updated++;
|
|
} else {
|
|
skipped++;
|
|
}
|
|
} catch (err) {
|
|
failed++;
|
|
console.error(`[backfill-classid] Failed for id=${item.catalogRecId}:`, err);
|
|
}
|
|
})
|
|
);
|
|
console.log(`[backfill-classid] Progress: ${Math.min(i + BATCH_SIZE, cwItems.length)} / ${cwItems.length}`);
|
|
}
|
|
|
|
console.log(`[backfill-classid] Done. Updated: ${updated}, Skipped (not in API DB): ${skipped}, Failed: ${failed}`);
|
|
|
|
// Verify
|
|
const verifyRes = await pgPool.query<{nonNull: string, total: string}>(
|
|
'SELECT COUNT(*) FILTER (WHERE "classId" IS NOT NULL) as "nonNull", COUNT(*) as total FROM "CatalogItem"'
|
|
);
|
|
const { nonNull, total } = verifyRes.rows[0];
|
|
console.log(`[backfill-classid] Verification: ${nonNull} / ${total} catalog items now have classId`);
|
|
|
|
// Breakdown
|
|
const breakdownRes = await pgPool.query<{classId: string | null, count: string}>(
|
|
'SELECT "classId", COUNT(*) as count FROM "CatalogItem" GROUP BY "classId" ORDER BY "classId"'
|
|
);
|
|
for (const row of breakdownRes.rows) {
|
|
const label = row.classId === 'S' ? 'Service/Labor' : row.classId === 'I' ? 'Inventory' : row.classId === 'N' ? 'Non-inventory' : 'null';
|
|
console.log(` classId=${row.classId ?? 'null'} (${label}): ${row.count}`);
|
|
}
|
|
}
|
|
|
|
main()
|
|
.catch((err) => {
|
|
console.error("[backfill-classid] Fatal error:", err);
|
|
process.exit(1);
|
|
})
|
|
.finally(async () => {
|
|
await Promise.all([cwPrisma.$disconnect(), pgPool.end()]);
|
|
});
|