← back to Greenland Onboard
scripts/full-04-stage.mjs
183 lines
#!/usr/bin/env node
// Step 7: STAGE (reversible only)
// - CREATE TABLE IF NOT EXISTS greenland_full_catalog (status DRAFT, quote-only, price NULL); upsert all 908
// - Write Shopify DRAFT payloads -> data/shopify-payloads.ndjson (NOT pushed)
// - Write data/greenland-full-specs.csv (all 908 rows, every column)
// Non-destructive. Does NOT touch greenland_catalog. Sets NO price.
import fs from 'node:fs';
import path from 'node:path';
import { execFileSync } from 'node:child_process';
const ROOT = path.resolve(import.meta.dirname, '..');
const DB = 'postgresql:///dw_unified?host=/tmp&user=stevestudio2';
const REC = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'enriched.json'), 'utf8'));
function psql(sql) {
return execFileSync('psql', [DB, '-v', 'ON_ERROR_STOP=1', '-q', '-c', sql], { encoding: 'utf8' });
}
function psqlStdin(sql) {
return execFileSync('psql', [DB, '-v', 'ON_ERROR_STOP=1', '-q'], { input: sql, encoding: 'utf8' });
}
const q = (s) => (s == null ? '' : String(s)).replace(/'/g, "''");
// ---- 1. Table (non-destructive) ----
psql(`CREATE TABLE IF NOT EXISTS greenland_full_catalog (
dw_sku text PRIMARY KEY,
join_key text,
prefix text,
material text,
city text,
raw_color text,
clean_color text,
color_source text,
hex text,
top_colors jsonb,
color_family text,
title text,
alt text,
tags text[],
min_qty int,
step_qty int,
minimum_order text,
mfr_sku text,
mfr_model text,
collection_code text,
collection_name text,
width text,
use_type text,
repeat_info text,
fire_rating text,
backing text,
weight text,
install_url text,
catalog_url text,
product_url text,
image_local text,
body_html text,
price numeric, -- always NULL (quote-only)
status text DEFAULT 'DRAFT',
updated_at timestamptz DEFAULT now()
);`);
// ---- upsert in batches ----
const cols = ['dw_sku','join_key','prefix','material','city','raw_color','clean_color','color_source','hex','top_colors','color_family','title','alt','tags','min_qty','step_qty','minimum_order','mfr_sku','mfr_model','collection_code','collection_name','width','use_type','repeat_info','fire_rating','backing','weight','install_url','catalog_url','product_url','image_local','body_html','price','status'];
const updateSet = cols.filter(c => c !== 'dw_sku').map(c => `${c}=EXCLUDED.${c}`).join(', ') + ', updated_at=now()';
function pgArray(arr) {
// text[] literal
return `ARRAY[${arr.map(x => `'${q(x)}'`).join(',')}]::text[]`;
}
let upserted = 0;
const BATCH = 100;
for (let i = 0; i < REC.length; i += BATCH) {
const slice = REC.slice(i, i + BATCH);
const values = slice.map(r => {
const vals = [
`'${q(r.dwSku)}'`,
`'${q(r.joinKey)}'`,
`'${q(r.prefix)}'`,
`'${q(r.material)}'`,
`'${q(r.city)}'`,
`'${q(r.rawColor)}'`,
`'${q(r.cleanColor)}'`,
`'${q(r.colorSource)}'`,
`'${q(r.hex)}'`,
`'${q(JSON.stringify(r.topColors))}'::jsonb`,
`'${q(r.colorFamily)}'`,
`'${q(r.title)}'`,
`'${q(r.alt)}'`,
pgArray(r.tags),
`${r.min}`,
`${r.step}`,
`'${q(r.minimumOrder)}'`,
`'${q(r.mfrSku)}'`,
`'${q(r.mfrModel)}'`,
`'${q(r.collectionCode)}'`,
`'${q(r.collectionName)}'`,
`'${q(r.width)}'`,
`'${q(r.use)}'`,
`'${q(r.repeat)}'`,
`'${q(r.fireRating)}'`,
`'${q(r.backing)}'`,
`'${q(r.weight)}'`,
`'${q(r.installUrl)}'`,
`'${q(r.catalogUrl)}'`,
`'${q(r.productUrl)}'`,
`'${q(r.imageLocal)}'`,
`'${q(r.body_html || '')}'`,
`NULL`, // price - quote-only
`'DRAFT'`,
];
return `(${vals.join(',')})`;
}).join(',\n');
const sql = `INSERT INTO greenland_full_catalog (${cols.join(',')}) VALUES\n${values}\nON CONFLICT (dw_sku) DO UPDATE SET ${updateSet};`;
psqlStdin(sql);
upserted += slice.length;
}
// ---- 2. Shopify DRAFT payloads (NOT pushed) ----
const payloadPath = path.join(ROOT, 'data', 'shopify-payloads.ndjson');
const out = fs.createWriteStream(payloadPath);
for (const r of REC) {
const payload = {
status: 'draft',
title: r.title,
body_html: r.body_html || '',
vendor: 'Phillipe Romano',
product_type: r.material === 'Specialty' ? 'Wallcovering' : 'Wallcovering',
tags: r.tags.join(', '),
handle: r.title.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, ''),
metafields: {
'global.Brand': 'Phillipe Romano',
'custom.material': r.material,
'custom.city_style': r.city,
'custom.color': r.cleanColor,
'custom.hex': r.hex,
'custom.width': r.width,
'custom.fire_rating': r.fireRating,
'custom.backing': r.backing,
'custom.collection': r.collectionName,
},
variants: [
{ option1: 'Yard', sku: r.dwSku, price: null, inventory_management: null,
requires_shipping: true, min_qty: r.min, step_qty: r.step, quote_only: true },
{ option1: 'Sample', sku: `${r.dwSku}-SAMPLE`, price: '4.25',
requires_shipping: true, min_qty: 1, step_qty: 1 },
],
options: [{ name: 'Size', values: ['Yard', 'Sample'] }],
images: [{ src: `img/${r.dwSku}.jpg`, alt: r.alt }],
_dw_sku: r.dwSku,
_quote_only: true,
_price: null,
};
out.write(JSON.stringify(payload) + '\n');
}
out.end();
// ---- 3. CSV ----
const csvCols = ['dwSku','prefix','material','city','rawColor','cleanColor','colorSource','hex','colorFamily','topColors','title','alt','tags','min','step','minimumOrder','mfrSku','mfrModel','collectionCode','collectionName','width','use','repeat','fireRating','backing','weight','installUrl','catalogUrl','productUrl','imageLocal','body_html','price','status'];
const esc = (v) => {
const s = v == null ? '' : (typeof v === 'object' ? JSON.stringify(v) : String(v));
return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
};
const lines = [csvCols.join(',')];
for (const r of REC) {
const row = csvCols.map(c => {
if (c === 'tags') return esc(r.tags.join('; '));
if (c === 'price') return ''; // quote-only
if (c === 'status') return 'DRAFT';
return esc(r[c]);
});
lines.push(row.join(','));
}
fs.writeFileSync(path.join(ROOT, 'data', 'greenland-full-specs.csv'), lines.join('\n'));
const count = psql(`SELECT count(*) FROM greenland_full_catalog;`).trim();
console.log(JSON.stringify({
upserted,
db_rows: count.split('\n').filter(l => /^\s*\d+/.test(l))[0]?.trim(),
payloads: payloadPath,
csv_rows: REC.length,
}, null, 2));