`server.js`

105 lines
'use strict';
/**
 * Vendor Price Requests — review/approve/SEND per-vendor letters asking for current
 * trade pricing on the showroom-line SKUs we have no cost for. Reads price_sourcing,
 * groups by vendor (gaps = crawl_status<>'found'), drafts a letter listing the mfr_skus,
 * lets Steve set the recipient email + edit, and SENDS via George (Gmail) — gated:
 * nothing sends without a click. Marks letter_status='sent'.
 */
const express = require('express');
const path = require('path');
const fs = require('fs');
const { Pool } = require('pg');
for (const line of fs.readFileSync(path.join(__dirname, '.env'), 'utf8').split('\n')) {
  const m = line.match(/^\s*([A-Za-z_]+)\s*=\s*(.*)\s*$/); if (m && process.env[m[1]] === undefined) process.env[m[1]] = m[2];
}
const PORT = process.env.PORT || 9803; // dedicated port; was 9620 which collided with the va-schumacher vendor-agent (EADDRINUSE)
const GEORGE = (process.env.GEORGE_URL || '').replace(/\/$/, '');
const AUTH = process.env.GEORGE_BASIC_AUTH || '';
const pool = new Pool({ host: '/tmp', port: 5432, user: 'stevestudio2', database: 'dw_unified', max: 6 });
const app = express();
app.use(express.json({ limit: '2mb' }));
app.use(express.static(path.join(__dirname, 'public')));

async function george(p, { method = 'GET', body } = {}) {
  const res = await fetch(GEORGE + p, { method, headers: { Authorization: `Basic ${AUTH}`, ...(body ? { 'Content-Type': 'application/json' } : {}) }, body: body ? JSON.stringify(body) : undefined });
  const t = await res.text(); let d; try { d = JSON.parse(t); } catch { d = { raw: t }; }
  if (!res.ok) throw new Error(d.error || t.slice(0, 200));
  return d;
}
function letterBody(vendor, rows, sheetUrl) {
  const lines = rows.map(r => `  • ${r.mfr_sku || r.dw_sku}${r.pattern ? '  —  ' + r.pattern : ''}${r.color ? ' / ' + r.color : ''}`).join('\n');
  const sheetBlock = sheetUrl
    ? `We've prepared a shared spreadsheet pre-filled with the ${rows.length} patterns below — please fill in the blank columns (width, repeat, contents, discount, retail price, net price) directly in it:\n${sheetUrl}\n\n(The same SKU list is below for reference.)\n\n`
    : '';
  return `Hello,\n\nDesigner Wallcoverings is preparing to feature the following ${vendor} patterns in our showroom and online catalog, and we'd like to confirm current trade (wholesale) pricing and any applicable dealer discount so we can list them correctly.\n\n${sheetBlock}Could you please provide current per-unit trade pricing (and unit of measure — per roll / per yard / per panel) for the following items:\n\n${lines}\n\nA current price list covering these SKUs works perfectly if easier on your end. Thank you — we appreciate it and look forward to showcasing the line.\n\nBest regards,\nDesigner Wallcoverings\ninfo@designerwallcoverings.com`;
}

// per-vendor summary of gaps (+ draft flag)
app.get('/api/vendors', async (req, res) => {
  try {
    const q = await pool.query(`
      SELECT ps.vendor, count(*)::int AS gap_count,
             max(vc.email) AS email,
             max(ps.letter_status) AS letter_status,
             bool_or(vc.draft_body IS NOT NULL AND vc.draft_body<>'') AS has_draft
      FROM price_sourcing ps LEFT JOIN vendor_contacts vc ON vc.vendor=ps.vendor
      WHERE ps.crawl_status <> 'found' AND COALESCE(ps.has_price,false)=false AND COALESCE(ps.letter_status,'none') <> 'skip-own-brand'
      GROUP BY ps.vendor ORDER BY (max(ps.letter_status)='sent') ASC, count(*) DESC`);
    res.json({ vendors: q.rows });
  } catch (e) { res.status(500).json({ error: e.message }); }
});
// draft letter + SKU list for a vendor — returns SAVED draft if one exists, else generates
app.get('/api/letter/:vendor', async (req, res) => {
  try {
    const v = req.params.vendor;
    const rows = (await pool.query(`SELECT dw_sku, mfr_sku, pattern, color FROM price_sourcing WHERE vendor=$1 AND crawl_status<>'found' AND COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY pattern, mfr_sku`, [v])).rows;
    const vc = (await pool.query(`SELECT email, draft_subject, draft_body, draft_saved_at, sheet_url FROM vendor_contacts WHERE vendor=$1`, [v])).rows[0] || {};
    const hasDraft = !!(vc.draft_body && vc.draft_body.trim());
    res.json({ vendor: v, count: rows.length, email: vc.email || '', sheetUrl: vc.sheet_url || '',
      subject: hasDraft ? vc.draft_subject : `Trade pricing request — ${v} (${rows.length} patterns)`,
      body: hasDraft ? vc.draft_body : letterBody(v, rows, vc.sheet_url),
      hasDraft, draftSavedAt: vc.draft_saved_at || null, skus: rows.slice(0, 500) });
  } catch (e) { res.status(500).json({ error: e.message }); }
});
// save a draft (persist edited letter without sending) → marks vendor 'drafted'
app.post('/api/draft/:vendor', async (req, res) => {
  try {
    const { subject, body, email } = req.body || {};
    await pool.query(`INSERT INTO vendor_contacts(vendor,email,draft_subject,draft_body,draft_saved_at) VALUES($1,$2,$3,$4,now())
      ON CONFLICT(vendor) DO UPDATE SET draft_subject=$3, draft_body=$4, draft_saved_at=now(), email=COALESCE(NULLIF($2,''),vendor_contacts.email)`, [req.params.vendor, (email || '').trim(), subject || '', body || '']);
    await pool.query(`UPDATE price_sourcing SET letter_status='drafted', updated_at=now() WHERE vendor=$1 AND COALESCE(letter_status,'none') NOT IN ('sent','skip-own-brand')`, [req.params.vendor]);
    res.json({ ok: true });
  } catch (e) { res.status(500).json({ error: e.message }); }
});
// save/update recipient email
app.post('/api/contact/:vendor', async (req, res) => {
  try {
    await pool.query(`INSERT INTO vendor_contacts(vendor,email) VALUES($1,$2) ON CONFLICT(vendor) DO UPDATE SET email=$2, updated_at=now()`, [req.params.vendor, (req.body.email || '').trim()]);
    res.json({ ok: true });
  } catch (e) { res.status(500).json({ error: e.message }); }
});
// SEND the letter via George (GATED — only on explicit click)
app.post('/api/send/:vendor', async (req, res) => {
  try {
    const { to, subject, body } = req.body || {};
    if (!to || !/.+@.+\..+/.test(to)) return res.status(400).json({ error: 'valid recipient email required' });
    if (!body) return res.status(400).json({ error: 'body required' });
    const sent = await george('/api/send', { method: 'POST', body: { account: 'info', to, subject, body: body.replace(/\n/g, '<br>'), no_source_tag: true } });
    await pool.query(`UPDATE price_sourcing SET letter_status='sent', updated_at=now() WHERE vendor=$1 AND crawl_status<>'found' AND COALESCE(has_price,false)=false`, [req.params.vendor]);
    await pool.query(`INSERT INTO vendor_contacts(vendor,email) VALUES($1,$2) ON CONFLICT(vendor) DO UPDATE SET email=$2, updated_at=now()`, [req.params.vendor, to]);
    res.json({ ok: true, sent });
  } catch (e) { res.status(500).json({ error: e.message }); }
});
// create the per-vendor fillable Google Sheet in the shared Drive folder
app.post('/api/sheet/:vendor', (req, res) => {
  const { execFile } = require('child_process');
  execFile('node', [path.join(__dirname, 'make-sheet.js'), req.params.vendor], { timeout: 120000 }, (err, stdout, stderr) => {
    if (err) return res.status(500).json({ error: (stderr || err.message || '').slice(0, 300) });
    try { res.json(JSON.parse(stdout.trim().split('\n').pop())); }
    catch (e) { res.status(500).json({ error: 'sheet created but parse failed: ' + stdout.slice(-200) }); }
  });
});
app.get('/api/health', (req, res) => res.json({ ok: true, george: !!GEORGE }));
app.listen(PORT, () => console.log(`Vendor Price Requests → http://127.0.0.1:${PORT}`));