`viewer/server.js`

87 lines
#!/usr/bin/env node
// Local approval viewer for new WALLPAPER fmpro SKUs (fmpro_wallpaper_review).
// Basic-auth admin/DW2024!. Zero deps — queries via psql. Read/write dw_unified.
const http = require('http');
const { execFile } = require('child_process');
const fs = require('fs');
const path = require('path');

const PORT = 9796, USER = 'admin', PASS = 'DW2024!';
const PG = ['-h', '127.0.0.1', '-U', 'dw_admin', '-d', 'dw_unified', '-tAqX'];
const HTML = fs.readFileSync(path.join(__dirname, 'index.html'), 'utf8');

const q1 = s => "'" + String(s).replace(/'/g, "''") + "'";           // sql string literal
const clean = s => String(s || '').replace(/[^\x20-\x7E]/g, '').slice(0, 120);
const skuOk = s => /^[A-Za-z0-9_.\- ]{1,60}$/.test(s);

function sql(query) {
  return new Promise((res, rej) => {
    execFile('psql', [...PG, '-c', query], { maxBuffer: 64 * 1024 * 1024 }, (e, out) => e ? rej(e) : res(out));
  });
}
const jsonQuery = async (inner) =>
  JSON.parse((await sql(`SELECT coalesce(json_agg(t),'[]') FROM (${inner}) t`)).trim() || '[]');

function whereClause({ status, vendor, q }) {
  const w = [];
  w.push(`review_status = ${q1((status && ['pending','approved','rejected'].includes(status)) ? status : 'pending')}`);
  if (vendor) w.push(vendor === '(none)' ? `nullif(vendor_name,'') IS NULL` : `vendor_name = ${q1(clean(vendor))}`);
  if (q) { const t = q1('%' + clean(q).toLowerCase() + '%'); w.push(`(lower(sku) LIKE ${t} OR lower(coalesce(mfr_sku,'')) LIKE ${t} OR lower(coalesce(title,'')) LIKE ${t})`); }
  return w.join(' AND ');
}

const send = (r, code, body, type = 'application/json') => { r.writeHead(code, { 'Content-Type': type }); r.end(body); };

const server = http.createServer(async (req, res) => {
  const auth = (req.headers.authorization || '').split(' ')[1] || '';
  const [u, p] = Buffer.from(auth, 'base64').toString().split(':');
  if (u !== USER || p !== PASS) { res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="fmpro-review"' }); return res.end('auth'); }
  const url = new URL(req.url, 'http://x');
  try {
    if (url.pathname === '/') return send(res, 200, HTML, 'text/html');

    if (url.pathname === '/api/summary') {
      const counts = await jsonQuery(`SELECT review_status status, count(*)::int n FROM fmpro_wallpaper_review GROUP BY 1`);
      const vendors = await jsonQuery(`SELECT coalesce(nullif(vendor_name,''),'(none)') vendor, count(*)::int n
        FROM fmpro_wallpaper_review WHERE review_status='pending' GROUP BY 1 ORDER BY 2 DESC`);
      const batches = await jsonQuery(`SELECT batch_date::text batch, count(*)::int n, count(*) FILTER (WHERE review_status='pending')::int pending
        FROM fmpro_wallpaper_review GROUP BY 1 ORDER BY 1 DESC`);
      return send(res, 200, JSON.stringify({ counts, vendors, batches }));
    }

    if (url.pathname === '/api/list') {
      const g = Object.fromEntries(url.searchParams);
      const limit = Math.min(500, Math.max(1, parseInt(g.limit) || 60));
      const offset = Math.max(0, parseInt(g.offset) || 0);
      const sort = ({ sku: 'sku', vendor: 'vendor_name NULLS LAST, sku', newest: 'first_seen DESC', img: '(image_url IS NOT NULL) DESC, sku' })[g.sort] || 'sku';
      const where = whereClause(g);
      const total = (await jsonQuery(`SELECT count(*)::int n FROM fmpro_wallpaper_review WHERE ${where}`))[0]?.n || 0;
      const rows = await jsonQuery(`SELECT sku, review_status, vendor_name, mfr_sku, sku_prefix, image_filename,
        image_url, title, to_char(first_seen,'Mon DD, YYYY, HH12:MI AM') seen, batch_date::text batch
        FROM fmpro_wallpaper_review WHERE ${where} ORDER BY ${sort} LIMIT ${limit} OFFSET ${offset}`);
      return send(res, 200, JSON.stringify({ total, rows, limit, offset }));
    }

    if (url.pathname === '/api/decide' && req.method === 'POST') {
      let body = ''; for await (const c of req) body += c;
      const b = JSON.parse(body || '{}');
      const decision = b.decision === 'approved' ? 'approved' : b.decision === 'rejected' ? 'rejected' : b.decision === 'pending' ? 'pending' : null;
      if (!decision) return send(res, 400, JSON.stringify({ error: 'bad decision' }));
      let where;
      if (b.scope === 'selected' && Array.isArray(b.skus) && b.skus.length) {
        const list = b.skus.filter(skuOk).map(q1).join(',');
        if (!list) return send(res, 400, JSON.stringify({ error: 'no valid skus' }));
        where = `sku IN (${list})`;
      } else if (b.scope === 'filter') {
        where = whereClause(b); // acts on everything matching the current filter (status defaults to pending)
      } else return send(res, 400, JSON.stringify({ error: 'bad scope' }));
      const out = await sql(`WITH u AS (UPDATE fmpro_wallpaper_review
        SET review_status=${q1(decision)}, decided_at=now(), decided_by='local-viewer'
        WHERE ${where} RETURNING 1) SELECT count(*)::int FROM u`);
      return send(res, 200, JSON.stringify({ updated: parseInt(out.trim()) || 0, decision }));
    }
    return send(res, 404, JSON.stringify({ error: 'not found' }));
  } catch (e) { return send(res, 500, JSON.stringify({ error: String(e.message || e) })); }
});
server.listen(PORT, '127.0.0.1', () => console.log(`fmpro-review viewer on http://127.0.0.1:${PORT}  (admin/DW2024!)`));