← back to Fromental Internal
lib/vendor-requests.js
223 lines
/* Internal purchasing requests — shared module for every *.designerwallcoverings.com
internal vendor PDP (astek, reidwitlin, quadrille, sangetsu, …).
POST /api/request → create REQ-YYMMDD-<id> in dw_unified.vendor_requests;
stock/price requests AUTO-EMAIL the vendor via
george-gmail (:9850) when a vendor email is on file.
POST /api/request/preview → compose the exact email (no insert, no send).
GET /api/requests → recent requests for this vendor (internal queue view).
Hard rules baked in:
- NEVER include a client name — emails reference our account number + REQ only.
- Vendor emails go out ONLY on an explicit user click behind Basic Auth; the
george external-send guard token is passed because that click IS the approval.
- No vendor email on file → request is still logged (status no_email) and the
caller is told to phone the vendor instead. */
const fs = require('fs');
const os = require('os');
const path = require('path');
const http = require('http');
const { Pool } = require('pg');
const GEORGE = process.env.GEORGE_URL || 'http://127.0.0.1:9850';
const FROM = 'steve@designerwallcoverings.com';
function envFrom(file, key) {
try { const m = fs.readFileSync(file, 'utf8').match(new RegExp('^' + key + '=(.+)$', 'm')); return m ? m[1].replace(/^["']|["']$/g, '').trim() : ''; }
catch { return ''; }
}
// Resolve a value from the first source that has it: an explicit process env var,
// then each candidate .env file. Makes the lib portable across Mac (~/Projects/...)
// and Kamatera, where george lives at ~/DW-Agents/gmail-agent — the old single
// hard-coded path returned '' there, so the send token was empty and george
// rejected every request (status email_failed).
function firstEnv(key, files) {
if (process.env[key]) return process.env[key];
for (const f of files) { const v = envFrom(f, key); if (v) return v; }
return '';
}
const HOME = os.homedir();
const GEORGE_ENVS = [
path.join(HOME, 'Projects/george-gmail/.env'), // Mac dev
path.join(HOME, 'DW-Agents/gmail-agent/.env'), // Kamatera prod
];
const SEND_TOKEN = firstEnv('GEORGE_EXTERNAL_SEND_TOKEN', GEORGE_ENVS);
const PGPASS = firstEnv('DW_ADMIN_DB_PASSWORD', [path.join(HOME, 'Projects/secrets-manager/.env')]);
// george basic auth — mirror george's own resolution: GEORGE_BASIC_AUTH from its
// .env when set, else the historical fallback (admin + empty password).
const GEORGE_AUTH = firstEnv('GEORGE_BASIC_AUTH', GEORGE_ENVS) || 'admin:';
let pool = null;
function db() {
if (!pool) pool = new Pool({ host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PGPASS, max: 3 });
return pool;
}
// Self-bootstrap the table so a fresh DB (Kamatera, mac2, a new sister site)
// never throws `relation "vendor_requests" does not exist` on the first click.
// Idempotent (IF NOT EXISTS) and memoized — the DDL runs at most once per process.
let schemaReady = null;
function ensureSchema() {
if (!schemaReady) {
schemaReady = db().query(`
CREATE TABLE IF NOT EXISTS vendor_requests (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
req_no text,
req_type text,
vendor_code text,
vendor_name text,
vendor_email text,
account_number text,
dw_sku text,
mfr_sku text,
product_title text,
qty text,
note text,
requested_by text,
status text,
email_subject text,
email_body text,
email_message_id text,
created_at timestamptz NOT NULL DEFAULT now(),
sent_at timestamptz
);
CREATE INDEX IF NOT EXISTS vendor_requests_vendor_code_id_idx
ON vendor_requests (vendor_code, id DESC);
`).catch(e => { schemaReady = null; console.error('[vendor-requests] ensureSchema failed:', e.message); throw e; });
}
return schemaReady;
}
const esc = s => String(s == null ? '' : s).replace(/[&<>]/g, c => ({ '&': '&', '<': '<', '>': '>' }[c]));
// ---- email composition — polite, account-number-only, client-anonymous ----
// vendor.buy_from = distributor that carries the line (e.g. Artmura via NewWall):
// the email greets the distributor; the line name stays in the subject/item.
function composeEmail(type, vendor, item, reqNo) {
const who = vendor.buy_from || vendor.name;
const acct = vendor.account_number || null;
const ask = type === 'stock'
? 'Could you please check current stock availability on the following item?'
: 'Could you please confirm our current net / wholesale cost (and any applicable discount) on the following item?';
// HARD RULE (Steve 2026-07-02): outbound emails NEVER mention our internal
// (DW) SKU numbers — manufacturer SKU + REQ number only.
const subjectHead = type === 'stock' ? 'Stock check' : 'Pricing request';
const subjectSku = item.mfr_sku ? ` (${item.mfr_sku})` : '';
const subject = `${subjectHead} — ${item.title || item.mfr_sku || 'item inquiry'}${subjectSku}${acct ? ` — Acct #${acct}` : ''} — ${reqNo}`;
const rows = [
['Pattern', item.title], ['Item / SKU', item.mfr_sku],
['Quantity', item.qty || (type === 'stock' ? 'to be confirmed' : null)],
['Note', item.note],
].filter(r => r[1]);
const body = [
`<p>Hello ${esc(who)} team,</p>`,
`<p>${ask}</p>`,
`<p>${rows.map(r => ` <b>${esc(r[0])}:</b> ${esc(r[1])}`).join('<br>')}</p>`,
`<p>This is for Designer Wallcoverings${acct ? `, account #${esc(acct)}` : ''}. Please reference <b>${reqNo}</b> in your reply.</p>`,
`<p>Thank you very much — we appreciate your help!</p>`,
`<p>Warm regards,<br><br>Designer Wallcoverings — Purchasing<br>${FROM}${acct ? `<br>Account #${esc(acct)}` : ''} · Ref ${reqNo}</p>`,
].join('\n');
return { subject, body };
}
function georgeSend({ to, subject, body }) {
return new Promise((resolve) => {
const payload = JSON.stringify({ account: 'steve-office', from: FROM, to, subject, body, source: 'vendor-pdp' });
const u = new URL(GEORGE + '/api/send');
const req = http.request({ hostname: u.hostname, port: u.port, path: u.pathname, method: 'POST',
headers: { 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(payload), 'X-Send-Approval': SEND_TOKEN,
'Authorization': 'Basic ' + Buffer.from(GEORGE_AUTH).toString('base64') } }, res => {
let d = ''; res.on('data', c => d += c);
res.on('end', () => { try { resolve({ status: res.statusCode, ...JSON.parse(d) }); } catch { resolve({ status: res.statusCode, error: d.slice(0, 300) }); } });
});
req.on('error', e => resolve({ status: 0, error: e.message }));
req.setTimeout(15000, () => { req.destroy(); resolve({ status: 0, error: 'george timeout' }); });
req.end(payload);
});
}
function reqNoFor(id, createdAt) {
const d = new Date(createdAt);
const ymd = d.getFullYear().toString().slice(2) + String(d.getMonth() + 1).padStart(2, '0') + String(d.getDate()).padStart(2, '0');
return `REQ-${ymd}-${id}`;
}
function mountVendorRequests(app, { vendorCode, getVendor, dataDir }) {
// warm the schema on mount so the table exists before the first request lands
ensureSchema().catch(() => {});
// exact email preview — same composer the send path uses; nothing persisted
app.post('/api/request/preview', (req, res) => {
const { type, dw_sku, mfr_sku, title, qty, note } = req.body || {};
if (!['stock', 'price'].includes(type)) return res.status(400).json({ ok: false, error: 'preview is for stock/price only' });
const vendor = getVendor() || {};
const { subject, body } = composeEmail(type, vendor, { dw_sku, mfr_sku, title, qty, note }, 'REQ-(assigned on send)');
res.json({ ok: true, to: vendor.email || null, from: FROM, subject, body, no_email: !vendor.email, phone: vendor.phone || null });
});
app.post('/api/request', async (req, res) => {
const { dw_sku, mfr_sku, title, qty, note, name } = req.body || {};
const type = ['memo', 'stock', 'price'].includes(req.body && req.body.type) ? req.body.type : 'memo';
if (!dw_sku && !mfr_sku) return res.status(400).json({ ok: false, error: 'sku required' });
const vendor = getVendor() || {};
try {
await ensureSchema(); // guarantees the table on a cold process (no more "relation does not exist")
const ins = await db().query(
`INSERT INTO vendor_requests (req_type, vendor_code, vendor_name, vendor_email, account_number,
dw_sku, mfr_sku, product_title, qty, note, requested_by, status)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,'open') RETURNING id, created_at`,
[type, vendorCode, vendor.name || null, vendor.email || null, vendor.account_number || null,
dw_sku || null, mfr_sku || null, title || null, qty || null, note || null, name || FROM]);
const { id, created_at } = ins.rows[0];
const reqNo = reqNoFor(id, created_at);
let status = 'open', emailed = false, message, mail = null;
if (type === 'memo') {
message = `${reqNo} logged — purchasing will order the memo sample.`;
} else if (!vendor.email) {
status = 'no_email';
message = `${reqNo} logged. No vendor email on file — call ${vendor.buy_from || vendor.name || 'the vendor'}${vendor.phone ? ' at ' + vendor.phone : ''}.`;
} else {
mail = composeEmail(type, vendor, { dw_sku, mfr_sku, title, qty, note }, reqNo);
const to = process.env.REQUEST_EMAIL_OVERRIDE || vendor.email; // override = smoke-test only
const sent = await georgeSend({ to, ...mail });
if (sent.success) {
status = 'emailed'; emailed = true;
message = `${reqNo} sent to ${vendor.buy_from || vendor.name} (${to}).`;
} else {
status = 'email_failed';
message = `${reqNo} logged, but the email failed (${sent.error || 'status ' + sent.status}) — call ${vendor.phone || 'the vendor'}.`;
}
await db().query(
`UPDATE vendor_requests SET req_no=$1, status=$2, email_subject=$3, email_body=$4,
email_message_id=$5, sent_at=CASE WHEN $2='emailed' THEN now() ELSE NULL END WHERE id=$6`,
[reqNo, status, mail.subject, mail.body, sent.messageId || null, id]);
}
if (type === 'memo' || status === 'no_email') {
await db().query(`UPDATE vendor_requests SET req_no=$1, status=$2 WHERE id=$3`, [reqNo, status, id]);
}
// local trail, same file the old inquiry endpoint used
try {
fs.appendFileSync(path.join(dataDir, 'inquiries.jsonl'),
JSON.stringify({ at: new Date().toISOString(), req_no: reqNo, type, status, sku: dw_sku || mfr_sku, qty: qty || '', note: note || '' }) + '\n');
} catch {}
res.json({ ok: true, req_no: reqNo, status, emailed, message });
} catch (e) {
console.error('[vendor-requests]', e.message);
res.status(500).json({ ok: false, error: 'request failed: ' + e.message });
}
});
app.get('/api/requests', async (_req, res) => {
try {
await ensureSchema();
const { rows } = await db().query(
`SELECT req_no, req_type, status, dw_sku, mfr_sku, product_title, qty, created_at, sent_at
FROM vendor_requests WHERE vendor_code=$1 ORDER BY id DESC LIMIT 50`, [vendorCode]);
res.json({ requests: rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
}
module.exports = { mountVendorRequests, composeEmail };