`make-sheet.js`

47 lines
'use strict';
/**
 * Create a per-vendor fillable Google Sheet in the shared "DW Vendors + Price Lists"
 * Drive folder: pre-filled with our SKUs (MFR SKU / Name / Color), blank columns for
 * the vendor to fill (Width, Repeat, Contents, Discount %, Retail Price, Net Price).
 * Uploads CSV→Sheet via rclone, sets anyone-with-link=writer (editable), saves the URL
 * to vendor_contacts.sheet_url. Usage: node make-sheet.js "<Vendor>"
 */
const fs = require('fs');
const { execSync } = require('child_process');
const PGENV = { PGHOST: '/tmp', PGPORT: '5432', PGUSER: 'stevestudio2', PGDATABASE: 'dw_unified', PATH: '/opt/homebrew/opt/postgresql@14/bin:/usr/local/bin:/opt/homebrew/bin:/usr/bin:/bin' };
const FOLDER = 'gdrive:DW Vendors + Price Lists';
const vendor = process.argv[2];
if (!vendor) { console.error('usage: node make-sheet.js "<Vendor>"'); process.exit(1); }
function psql(s) { return execSync('psql -tA', { input: s, env: PGENV, encoding: 'utf8' }); }
function csvCell(s) { s = String(s == null ? '' : s); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; }

// 1. rows for this vendor (unpriced, not own-brand)
const raw = psql(`SELECT mfr_sku||''||COALESCE(pattern,'')||''||COALESCE(color,'') FROM price_sourcing WHERE vendor=$v$${vendor}$v$ AND COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY pattern, mfr_sku`);
const rows = raw.split('\n').filter(Boolean).map(l => l.split(''));
if (!rows.length) { console.error('no rows for vendor'); process.exit(2); }

// 2. CSV: pre-filled MFR SKU/Name/Color; blanks for the vendor
const header = ['MFR SKU', 'Name', 'Color', 'Width', 'Repeat', 'Contents', 'Discount %', 'Retail Price', 'Net Price'];
const lines = [header.join(',')].concat(rows.map(r => [r[0], r[1], r[2], '', '', '', '', '', ''].map(csvCell).join(',')));
const safeName = vendor.replace(/[\/\\]/g, '-');
const tmp = `/tmp/vpr-sheet-${Date.now()}.csv`;
fs.writeFileSync(tmp, lines.join('\n'));

// 3. upload CSV -> Google Sheet (this also refreshes rclone's token)
const sheetName = `${safeName} - Price Request`;
execSync(`rclone copyto ${JSON.stringify(tmp)} ${JSON.stringify(FOLDER + '/' + sheetName)} --drive-import-formats csv`, { stdio: 'pipe' });
fs.unlinkSync(tmp);

// 4. resolve the new file id, set anyone-with-link=writer (editable), build link
const id = execSync(`rclone lsjson ${JSON.stringify(FOLDER)} 2>/dev/null`, { encoding: 'utf8' });
const item = JSON.parse(id).find(x => x.Name === sheetName);
if (!item) { console.error('uploaded sheet not found'); process.exit(3); }
const fileId = item.ID;
const tok = (() => { const d = JSON.parse(execSync('rclone config dump', { encoding: 'utf8' })); return JSON.parse(d.gdrive.token).access_token; })();
execSync(`curl -s -X POST "https://www.googleapis.com/drive/v3/files/${fileId}/permissions" -H "Authorization: Bearer ${tok}" -H "Content-Type: application/json" -d '{"role":"writer","type":"anyone"}'`, { stdio: 'pipe' });
const url = `https://docs.google.com/spreadsheets/d/${fileId}/edit`;

// 5. persist
psql(`INSERT INTO vendor_contacts(vendor,sheet_url,sheet_created_at) VALUES($v$${vendor}$v$,$v$${url}$v$,now()) ON CONFLICT(vendor) DO UPDATE SET sheet_url=$v$${url}$v$, sheet_created_at=now()`);
console.log(JSON.stringify({ ok: true, vendor, rows: rows.length, url }));