← back to Interiordesignershowroom
routes/admin.js
912 lines
// Gated admin curation dashboard. Basic-auth, no npm deps beyond express.
// Lets Steve hand-pick `featured` products, publish/unpublish guides, and see
// which links are actually converting (click analytics).
const express = require('express');
const crypto = require('crypto');
const fs = require('fs');
const path = require('path');
const db = require('../lib/db');
const { esc, money, fmtStamp } = require('../lib/render');
const { detailPref } = require('../lib/adminview');
const router = express.Router();
// Affiliate networks we can send Steve to in order to JOIN a program. There is no
// true one-click affiliate join (every network makes you log in as a publisher and
// click "Join" on the advertiser). Per the DTD verdict (2026-08-03) the "Join via
// <Network>" button resolves option C-as-a-superset-of-A: use a per-network
// marketplace SEARCH deep-link (brand pre-filled, `{q}` placeholder) ONLY where that
// pattern is verified to survive the network's login redirect (searchVerified:true);
// everywhere else fall back to the plain marketplace `join` landing. Networks stay on
// the landing until each `search` URL is confirmed (CJ's is login-gated and unverified,
// so it lands — exactly the dissent's concern). To enable a search deep-link after a
// 5-second live test, just flip that network's `searchVerified` to true.
const NETWORKS = {
cj: { label: 'CJ', join: 'https://members.cj.com/member/publisher/marketplace', search: 'https://members.cj.com/member/publisher/marketplace?keyword={q}', searchVerified: false },
rakuten: { label: 'Rakuten', join: 'https://rakutenadvertising.com/en-us/affiliate-marketing-for-publishers/', search: null, searchVerified: false },
shareasale: { label: 'ShareASale', join: 'https://account.shareasale.com/a-loginpage.cfm', search: 'https://account.shareasale.com/a-searchmerchants.cfm?keyword={q}', searchVerified: false },
impact: { label: 'Impact', join: 'https://app.impact.com/', search: null, searchVerified: false },
amazon: { label: 'Amazon', join: 'https://affiliate-program.amazon.com/', search: null, searchVerified: false },
};
// Resolve the join target for a (network, brand): a brand-prefilled marketplace SEARCH
// where that network has a verified search pattern, else the plain marketplace landing
// (the safe A-fallback that never 404s). Returns { url, prefilled }.
function joinTarget(network, brand) {
const net = NETWORKS[network];
if (!net) return { url: '#', prefilled: false };
if (net.searchVerified && net.search && brand) {
return { url: net.search.replace('{q}', encodeURIComponent(brand)), prefilled: true };
}
return { url: net.join, prefilled: false };
}
// Load the curated suggestion roster (data/suggested-affiliates.json). Read live so
// the list can be extended without a restart; never throws the request on a bad file.
function loadSuggestions() {
try {
const raw = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'suggested-affiliates.json'), 'utf8'));
return Array.isArray(raw.lines) ? raw.lines : [];
} catch (_) { return []; }
}
// Confirmed CJ account identifiers (data/cj-account.json), shown on the Affiliates
// admin as a reference bar. Read live; absent/broken file just hides the bar.
function loadCjAccount() {
try { return JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'cj-account.json'), 'utf8')); }
catch (_) { return null; }
}
function cjAccountBar() {
const a = loadCjAccount();
if (!a) return '';
const cell = (k, v) => v
? `<span style="margin-right:18px"><span class="net">${esc(k)}</span> <b>${esc(v)}</b></span>`
: '';
return `<div style="background:#fff;border:1px solid var(--line);border-radius:6px;padding:10px 14px;margin:0 0 16px;font-size:.85rem">
<b style="font-size:.72rem;text-transform:uppercase;letter-spacing:.05em;color:#6b6259;margin-right:14px">CJ Account</b>
${cell('Login', a.login_email)}${cell('CID', a.cid)}${cell('PID', a.pid)}
<span class="pill on" title="Config CJ_WEBSITE_ID matches the PID in the live tracking links — attribution wired correctly">✓ verified</span>
</div>`;
}
// Sub-nav for the Affiliates section: two tabs, current one highlighted.
function affiliateTabs(active) {
const tab = (href, label, on) =>
`<a href="${href}" class="tab${on ? ' tab-on' : ''}">${label}</a>`;
return `<div class="tabs">
${tab('/admin/affiliates', 'Active affiliates', active === 'active')}
${tab('/admin/affiliates/suggested', 'Suggested lines to join', active === 'suggested')}
</div>`;
}
// --- Basic auth (timing-safe) --------------------------------------------
const USER = process.env.ADMIN_USER || 'admin';
const PASS = process.env.ADMIN_PASS || 'DW2024!';
function safeEq(a, b) {
const A = Buffer.from(a), B = Buffer.from(b);
if (A.length !== B.length) return false;
return crypto.timingSafeEqual(A, B);
}
function auth(req, res, next) {
const h = req.get('authorization') || '';
const m = h.match(/^Basic (.+)$/);
if (m) {
const decoded = Buffer.from(m[1], 'base64').toString();
const colonIdx = decoded.indexOf(':');
const u = colonIdx === -1 ? decoded : decoded.slice(0, colonIdx);
const p = colonIdx === -1 ? undefined : decoded.slice(colonIdx + 1);
if (u !== undefined && p !== undefined && safeEq(u, USER) && safeEq(p, PASS)) {
// Non-secret UI hint: tells the public storefront to show the admin "see-more"
// bar. NOT a security boundary — every mutation still requires this Basic auth.
res.cookie('ids_admin_ui', '1', { maxAge: 12 * 3600e3, sameSite: 'lax', path: '/' });
return next();
}
}
res.set('WWW-Authenticate', 'Basic realm="IDS Admin"').status(401).send('Auth required');
}
router.use('/admin', auth);
router.use('/admin', express.urlencoded({ extended: false }));
// CSRF guard: Basic-auth creds auto-attach cross-site, so a state-changing POST
// needs an origin check. Reject any admin POST whose Origin/Referer host doesn't
// match the request host (a same-site form always sends a matching one).
router.use('/admin', (req, res, next) => {
if (req.method !== 'POST') return next();
const host = req.get('host');
const src = req.get('origin') || req.get('referer') || '';
let ok = false;
try { ok = !!src && new URL(src).host === host; } catch (_) { ok = false; }
if (!ok) return res.status(403).send('Cross-origin request blocked.');
next();
});
// Steve's rule: admin cards show created date AND time, visible, ISO in title=.
// Formats through the shared render.fmtStamp() so the admin chip and the
// storefront "Added" line stay byte-identical (and it's NaN-safe — a garbage
// timestamp renders nothing instead of "Invalid Date").
function when(ts) {
const f = fmtStamp(ts);
if (!f) return '';
return `<span class="when" title="${esc(f.iso)}">🕓 ${esc(f.label)}</span>`;
}
// Per-product click analytics cell: lifetime total, a 7-day sub-count, and the
// last-clicked date (dating the analytics). No clicks → a muted "—" so the
// mostly-quiet catalog reads honestly instead of showing fake zeros everywhere.
function clickCell(p) {
const n = Number(p.clicks) || 0;
if (!n) return '<span class="subtle">—</span>';
const wk = Number(p.clicks7) || 0;
return `<b>${n}</b> click${n === 1 ? '' : 's'}`
+ (wk ? ` <span class="subtle">(${wk} · 7d)</span>` : '')
+ (p.last_click ? `<div>${when(p.last_click)}</div>` : '');
}
const shell = (body) => `<!doctype html><html lang="en"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1"><title>IDS Admin</title>
<style>
:root{--line:#e2ddd4;--ink:#221e19;--accent:#8a7250}
*{box-sizing:border-box}body{font:14px/1.5 -apple-system,Segoe UI,Helvetica,Arial,sans-serif;margin:0;background:#f6f3ee;color:var(--ink)}
header{background:#221e19;color:#f3ede3;padding:14px 22px;display:flex;justify-content:space-between;align-items:center}
header a{color:#e6c98a;text-decoration:none;font-size:.85rem}
main{max-width:1200px;margin:0 auto;padding:22px}
h1{font-size:1.3rem;margin:0}h2{font-size:1rem;border-bottom:1px solid var(--line);padding-bottom:6px;margin:26px 0 12px}
.stats{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:12px}
.stat{background:#fff;border:1px solid var(--line);border-radius:6px;padding:14px}
.stat b{font-size:1.6rem;display:block}.stat span{color:#8a8178;font-size:.78rem;text-transform:uppercase;letter-spacing:.05em}
table{width:100%;border-collapse:collapse;background:#fff;border:1px solid var(--line);border-radius:6px;overflow:hidden}
th,td{text-align:left;padding:8px 10px;border-bottom:1px solid var(--line);font-size:.86rem;vertical-align:middle}
th{background:#efe9df;font-size:.72rem;text-transform:uppercase;letter-spacing:.05em;color:#6b6259}
.when{color:#8a8178;font-size:.75rem;white-space:nowrap}
.pill{display:inline-block;padding:1px 8px;border-radius:20px;font-size:.72rem;border:1px solid var(--line)}
.on{background:#e7f3e7;border-color:#bcd9bc}.off{background:#f3e7e7;border-color:#d9bcbc}
button{cursor:pointer;border:1px solid var(--line);background:#fff;border-radius:5px;padding:4px 10px;font-size:.8rem}
button:hover{border-color:var(--accent);color:var(--accent)}
img.thumb{width:40px;height:40px;object-fit:cover;border-radius:4px;vertical-align:middle}
.net{font-size:.7rem;text-transform:uppercase;color:var(--accent);letter-spacing:.04em}
.subtle{color:#8a8178;font-size:.82em}
tr.row-off td{background:#faf5f2;color:#9a8f86}
tr.row-off td:first-child{box-shadow:inset 3px 0 0 #d9bcbc}
.pill.on{color:#2f7a2f}.pill.off{color:#b1483c}
header nav a{margin-left:2px}
.tabs{display:flex;gap:4px;border-bottom:1px solid var(--line);margin:6px 0 18px}
.tabs .tab{padding:8px 16px;text-decoration:none;color:#8a8178;font-size:.9rem;border:1px solid transparent;border-bottom:none;border-radius:6px 6px 0 0;position:relative;top:1px}
.tabs .tab:hover{color:var(--ink)}
.tabs .tab-on{background:#fff;border-color:var(--line);color:var(--ink);font-weight:600}
.chips{display:flex;flex-wrap:wrap;gap:6px;margin:0 0 14px}
.chips a{padding:3px 11px;border:1px solid var(--line);border-radius:20px;text-decoration:none;color:#6b6259;font-size:.78rem;background:#fff}
.chips a.on{background:#221e19;color:#e6c98a;border-color:#221e19}
a.ezjoin{display:inline-block;background:#8a7250;color:#fff;padding:4px 12px;border-radius:5px;text-decoration:none;font-size:.8rem;white-space:nowrap}
a.ezjoin:hover{background:#6f5c40}
.why{color:#6b6259;font-size:.82rem}
</style></head><body>
<header><h1>Interior Designer’s Showroom — Admin</h1>
<nav><a href="/admin">Dashboard</a> · <a href="/admin/brands">Brands</a> · <a href="/admin/suppress">Suppress</a> · <a href="/admin/affiliates">Affiliates</a> · <a href="/">View site ↗</a></nav></header>
<main>${body}</main></body></html>`;
router.get('/admin', async (req, res, next) => {
try {
const details = detailPref(req, res); // "see more on the backend" toggle
// Sortable products table (Steve's standing "every data table is sortable" rule).
// ?sort=clicks surfaces what's actually converting; default 'new' keeps the
// curation/recency order. Whitelisted to two fixed ORDER BY clauses, so the
// query param can never reach the SQL as free text.
const sort = req.query.sort === 'clicks' ? 'clicks' : 'new';
const orderSql = sort === 'clicks'
? `p.suppressed, COALESCE(cl.clicks,0) DESC, cl.last_click DESC NULLS LAST, p.created_at DESC`
: `p.featured DESC, p.suppressed, p.created_at DESC`;
const [counts, topClicks, products, guides] = await Promise.all([
db.query(`SELECT
(SELECT count(*) FROM products) AS products,
(SELECT count(*) FROM products WHERE featured) AS featured,
(SELECT count(*) FROM products WHERE suppressed) AS suppressed,
(SELECT count(DISTINCT brand) FROM products WHERE brand IS NOT NULL AND brand <> '') AS brands,
(SELECT count(DISTINCT brand) FROM products WHERE suppressed AND brand IS NOT NULL AND brand <> '') AS brands_hidden,
(SELECT count(*) FROM guides WHERE published) AS guides,
(SELECT count(*) FROM clicks) AS clicks,
(SELECT count(*) FROM clicks WHERE clicked_at > now() - interval '7 days') AS clicks7,
(SELECT count(DISTINCT advertiser) FROM products WHERE advertiser IS NOT NULL) AS affiliates,
(SELECT count(*) FROM affiliate_settings WHERE enabled = FALSE) AS affiliates_off`),
db.query(`SELECT p.id,p.title,p.advertiser,count(c.*) AS n
FROM clicks c JOIN products p ON p.id=c.product_id
GROUP BY p.id,p.title,p.advertiser ORDER BY n DESC LIMIT 10`),
// Per-product click analytics folded in via one grouped LEFT JOIN: lifetime
// clicks, a 7-day window, and the last-clicked timestamp (the date we stamp
// on the analytics). Products with no clicks stay NULL → rendered as "—".
db.query(`SELECT p.id,p.title,p.advertiser,p.network,p.external_id,p.brand,p.category,p.price,p.sale_price,p.image_url,p.featured,p.suppressed,p.in_stock,p.room,p.created_at,p.updated_at,
COALESCE(cl.clicks,0) AS clicks,
COALESCE(cl.clicks7,0) AS clicks7,
cl.last_click
FROM products p
LEFT JOIN (
SELECT product_id,
count(*) AS clicks,
count(*) FILTER (WHERE clicked_at > now() - interval '7 days') AS clicks7,
max(clicked_at) AS last_click
FROM clicks GROUP BY product_id
) cl ON cl.product_id = p.id
ORDER BY ${orderSql} LIMIT 200`),
db.query(`SELECT id,slug,title,published,created_at FROM guides ORDER BY created_at DESC`),
]);
const c = counts.rows[0];
const stats = `<div class="stats">
<div class="stat"><b>${c.products}</b><span>Products</span></div>
<div class="stat"><b>${c.featured}</b><span>Featured</span></div>
<div class="stat"><b>${c.suppressed}</b><span>Suppressed${Number(c.suppressed) ? ' · hidden' : ''}</span></div>
<a class="stat" href="/admin/brands" style="text-decoration:none;color:inherit"><b>${c.brands}</b><span>Brands${Number(c.brands_hidden) ? ` · <span style="color:#b1483c">${c.brands_hidden} hidden</span>` : ' →'}</span></a>
<div class="stat"><b>${c.guides}</b><span>Published guides</span></div>
<div class="stat"><b>${c.clicks}</b><span>Total clicks</span></div>
<div class="stat"><b>${c.clicks7}</b><span>Clicks · 7 days</span></div>
<div class="stat"><b>${c.affiliates}</b><span>Affiliates${Number(c.affiliates_off) ? ` · <span style="color:#b1483c">${c.affiliates_off} off</span>` : ''}</span></div>
</div>`;
const topRows = topClicks.rows.length
? topClicks.rows.map((r) => `<tr><td>${esc(r.title)}</td><td>${esc(r.advertiser || '')}</td><td><b>${r.n}</b></td></tr>`).join('')
: `<tr><td colspan="3" style="color:#8a8178">No clicks logged yet.</td></tr>`;
// Extra <th>/<td> columns only rendered when "Show details" is on ("see more").
const detailHead = details ? `<th>Brand</th><th>Category</th><th>Ext ID</th><th>Stock</th><th>Updated</th>` : '';
const prodRows = products.rows.map((p) => `<tr class="${p.suppressed ? 'row-off' : ''}">
<td>${p.image_url ? `<img class="thumb" src="${esc(p.image_url)}" alt="">` : ''}</td>
<td>${esc(p.title)}<div class="net">${esc(p.network)} · ${esc(p.advertiser || '')}</div></td>
<td>${esc(p.room || '')}</td>
<td>${money(p.sale_price || p.price)}</td>
<td>${when(p.created_at)}</td>
<td>${clickCell(p)}</td>
<td><span class="pill ${p.featured ? 'on' : 'off'}">${p.featured ? 'Featured' : '—'}</span>${p.suppressed ? ' <span class="pill off">Hidden</span>' : ''}</td>
${details ? `<td class="subtle">${esc(p.brand || '')}</td><td class="subtle">${esc(p.category || '')}</td><td class="subtle">${esc(p.external_id || '')}</td><td class="subtle">${p.in_stock === false ? 'out' : 'in'}</td><td>${when(p.updated_at)}</td>` : ''}
<td style="white-space:nowrap">
<form method="post" action="/admin/products/${p.id}/featured" style="display:inline;margin:0"><button>${p.featured ? 'Unfeature' : 'Feature'}</button></form>
<form method="post" action="/admin/products/${p.id}/suppress" style="display:inline;margin:0"><button title="${p.suppressed ? 'Show this product on the storefront again' : 'Hide this product everywhere (kept in DB)'}">${p.suppressed ? 'Unhide' : 'Hide'}</button></form>
</td>
</tr>`).join('');
const guideRows = guides.rows.map((g) => `<tr>
<td>${esc(g.title)}<div class="net">/guides/${esc(g.slug)}</div></td>
<td>${when(g.created_at)}</td>
<td><span class="pill ${g.published ? 'on' : 'off'}">${g.published ? 'Published' : 'Draft'}</span></td>
<td><form method="post" action="/admin/guides/${g.id}/published"><button>${g.published ? 'Unpublish' : 'Publish'}</button></form></td>
</tr>`).join('');
// Sort links for the products table — the active one is inked/bold, and each
// preserves the current details toggle so the two controls never fight.
const sortLink = (key, label) => `<a href="/admin?sort=${key}&details=${details ? 1 : 0}" style="text-decoration:none;${sort === key ? 'font-weight:600;color:var(--ink)' : 'color:#8a7250'}">${label}</a>`;
res.send(shell(`
${stats}
<p style="margin:16px 0 0"><a href="/admin/affiliates" style="display:inline-block;background:#221e19;color:#e6c98a;padding:8px 14px;border-radius:6px;text-decoration:none;font-size:.85rem">⚙︎ Manage affiliates — turn sources on / off →</a></p>
<h2>Top-clicked products</h2>
<table><tr><th>Product</th><th>Advertiser</th><th>Clicks</th></tr>${topRows}</table>
<h2>Products — curate featured (${products.rows.length})
<span style="float:right;font-size:.75rem;font-weight:400">
<span class="subtle">Sort:</span>
${sortLink('new', 'Newest')} <span class="subtle">·</span> ${sortLink('clicks', 'Most clicks')}
<span class="subtle" style="margin:0 8px">|</span>
<a href="/admin?sort=${sort}&details=${details ? 0 : 1}" style="text-decoration:none;color:${details ? '#b1483c' : '#8a7250'}">${details ? '− Hide details' : '+ Show details'}</a>
</span>
</h2>
<table><tr><th></th><th>Product</th><th>Room</th><th>Price</th><th>Added</th><th>Clicks</th><th>Status</th>${detailHead}<th></th></tr>${prodRows}</table>
<h2>Guides</h2>
<table><tr><th>Guide</th><th>Created</th><th>Status</th><th></th></tr>${guideRows}</table>
`));
} catch (e) { next(e); }
});
// Reject a bad :id before it hits the bigint column (would 500 otherwise). Shared
// robust guard (also closes overflow, scientific/hex notation).
const { intOrNull: intId } = require('../lib/ids');
router.post('/admin/products/:id/featured', async (req, res, next) => {
try {
const id = intId(req.params.id);
if (!id) return res.status(404).send('Unknown product.');
await db.query(`UPDATE products SET featured = NOT featured, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
} catch (e) { next(e); }
});
// Hide/show one product everywhere (kept in the DB, just flagged off — the reversible
// "check it off as no"). The storefront gate lives in lib/catalog.js + lib/rooms.js.
router.post('/admin/products/:id/suppress', async (req, res, next) => {
try {
const id = intId(req.params.id);
if (!id) return res.status(404).send('Unknown product.');
await db.query(`UPDATE products SET suppressed = NOT suppressed, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
} catch (e) { next(e); }
});
router.post('/admin/guides/:id/published', async (req, res, next) => {
try {
const id = intId(req.params.id);
if (!id) return res.status(404).send('Unknown guide.');
await db.query(`UPDATE guides SET published = NOT published, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
} catch (e) { next(e); }
});
// --- Brands: select a whole BRAND LINE and hide/show it across every advertiser --
// The fourth join key. network/advertiser toggles (affiliate_settings) hide by SOURCE;
// the per-product Hide flips ONE row. A brand, though, can be carried by several
// advertisers at once — e.g. "Honiture" sold by the Honiture merchant AND by resellers
// (AUKRO, BSC). Killing the merchant leaves the reseller-carried items live. This page
// flips `products.suppressed` for EVERY row of a brand in one click, so a cross-advertiser
// line is switched off (or back on) as a unit. Reversible; nothing is deleted.
function brandBtn(brand, action, label, disabled) {
if (disabled) return `<button type="button" disabled style="opacity:.4;cursor:not-allowed">${label}</button>`;
return `<form method="post" action="/admin/brands/suppress" style="display:inline;margin:0 3px 0 0">
<input type="hidden" name="brand" value="${esc(brand)}">
<input type="hidden" name="action" value="${action}">
<button>${label}</button></form>`;
}
router.get('/admin/brands', async (req, res, next) => {
try {
const q = req.query.q ? String(req.query.q).slice(0, 60) : '';
const params = [];
let filter = `brand IS NOT NULL AND brand <> ''`;
if (q) { params.push('%' + q + '%'); filter += ` AND brand ILIKE $${params.length}`; }
const { rows } = await db.query(`SELECT brand,
count(*) AS products,
count(*) FILTER (WHERE in_stock) AS in_stock,
count(*) FILTER (WHERE suppressed) AS suppressed,
count(DISTINCT advertiser) AS advertisers,
string_agg(DISTINCT advertiser, ', ' ORDER BY advertiser) AS advertiser_list,
max(created_at) AS latest
FROM products WHERE ${filter}
GROUP BY brand
ORDER BY (count(*) FILTER (WHERE suppressed) > 0) DESC, count(*) DESC
LIMIT 300`, params);
const onOff = (on) => `<span class="pill ${on ? 'on' : 'off'}">${on ? '● ON' : '○ OFF'}</span>`;
const hiddenBrands = rows.filter((r) => Number(r.suppressed) > 0).length;
const brandRows = rows.map((r) => {
const total = Number(r.products), hid = Number(r.suppressed), advs = Number(r.advertisers);
const allHidden = hid === total, allVisible = hid === 0;
// status pill: fully visible / partially hidden / fully hidden
const status = allVisible ? onOff(true)
: allHidden ? onOff(false)
: `<span class="pill" style="background:#faf1e0;border-color:#e5cf9e;color:#9a6a1c">◐ ${hid}/${total} hidden</span>`;
// cross-advertiser badge — the Honiture case: one brand, many merchants
const spread = advs > 1
? `<span class="pill" title="${esc(r.advertiser_list || '')}" style="background:#eef2f7;border-color:#c6d3e2;color:#3a5578;margin-left:6px">spans ${advs} advertisers</span>`
: '';
// action buttons: Hide all (disabled if already fully hidden), Unhide all (disabled if none hidden)
const actions = brandBtn(r.brand, 'hide', 'Hide brand', allHidden)
+ brandBtn(r.brand, 'unhide', 'Unhide brand', allVisible);
return `<tr class="${allHidden ? 'row-off' : ''}">
<td><a href="/shop?q=${encodeURIComponent(r.brand)}" target="_blank" rel="noopener" style="color:var(--ink);font-weight:600;text-decoration:none">${esc(r.brand)}</a> ${spread}</td>
<td>${advs}${r.advertiser_list ? ` <span class="subtle" title="${esc(r.advertiser_list)}">›</span>` : ''}</td>
<td>${r.in_stock} <span class="subtle">/ ${total}</span></td>
<td>${hid ? `<b style="color:#b1483c">${hid}</b>` : '0'}</td>
<td>${when(r.latest)}</td>
<td>${status}</td>
<td style="white-space:nowrap">${actions}</td>
</tr>`;
}).join('');
const intro = `<p class="subtle" style="margin:0 0 10px">
Select a whole <b>brand line</b> and switch it off across <i>every</i> advertiser at once —
the reversible "check it off as no" at the brand level. Hiding a brand flips it out of the
storefront, room builder, and brand filters everywhere it appears; it stays in the database and
returns the moment you un-hide it. Use this for a brand carried by several merchants (a
<b>spans N advertisers</b> badge marks those) where a single advertiser switch wouldn't catch it all.
${hiddenBrands ? `<b style="color:#b1483c"> ${hiddenBrands} brand${hiddenBrands === 1 ? '' : 's'} currently hidden (fully or partly).</b>` : ''}</p>`;
const searchForm = `<form method="get" action="/admin/brands" style="margin:0 0 14px">
<input name="q" value="${esc(q)}" placeholder="Search brands…" style="padding:6px 10px;border:1px solid var(--line);border-radius:5px;font-size:.85rem;min-width:240px">
<button style="padding:6px 12px">Search</button>
${q ? ` <a href="/admin/brands" class="subtle" style="margin-left:6px">clear</a>` : ''}</form>`;
// Green confirmation banner after a keyword hide/unhide (carried via ?done=).
const banner = req.query.done
? `<p style="background:#e7f3e7;border:1px solid #bcd9bc;border-radius:6px;padding:8px 12px;margin:0 0 14px">✓ ${esc(String(req.query.done).slice(0, 200))}</p>`
: '';
// Keyword line-hider: matches title OR brand OR advertiser, so it catches a line
// even when a reseller stamps its own name in the brand column (the Honiture gap).
// Always routes through a preview first — no free-text write without a count.
const keywordBox = `<div style="border:1px solid var(--line);background:#fff;border-radius:8px;padding:14px 16px;margin:0 0 18px">
<div style="font-weight:600;margin-bottom:4px">Hide an entire line by keyword</div>
<p class="subtle" style="margin:0 0 10px">Matches <b>title</b>, <b>brand</b> and <b>advertiser</b> — so it catches a line even when a reseller mislabels the brand column (the Honiture case). You'll see an exact preview and count before anything changes.</p>
<form method="get" action="/admin/brands/keyword" style="margin:0">
<input name="kw" placeholder="e.g. honiture" required minlength="2" style="padding:6px 10px;border:1px solid var(--line);border-radius:5px;font-size:.85rem;min-width:240px">
<button style="padding:6px 12px">Preview matches →</button>
</form>
</div>`;
res.send(shell(`
<h1 style="font-size:1.15rem;margin:0 0 4px">Brands — hide or show a whole line</h1>
${intro}
${banner}
${keywordBox}
${searchForm}
<table>
<tr><th>Brand</th><th>Advertisers</th><th>In-stock / total</th><th>Hidden</th><th>Newest item</th><th>Status</th><th>Action</th></tr>
${brandRows || `<tr><td colspan="7" class="subtle">No brands${q ? ` matching “${esc(q)}”` : ''}.</td></tr>`}
</table>
${rows.length >= 300 ? `<p class="subtle">Showing the first 300 brands — narrow with search.</p>` : ''}
`));
} catch (e) { next(e); }
});
// Flip `suppressed` for EVERY product of one brand. action=hide → TRUE, unhide → FALSE.
// Guarded: the brand must actually exist in the catalog. Reversible, kept in DB.
router.post('/admin/brands/suppress', async (req, res, next) => {
try {
const brand = String(req.body.brand || '').trim().slice(0, 200);
const hide = req.body.action !== 'unhide'; // default = hide
if (!brand) return res.status(400).send('Missing brand.');
const exists = await db.query('SELECT 1 FROM products WHERE brand=$1 LIMIT 1', [brand]);
if (!exists.rowCount) return res.status(404).send('Unknown brand.');
await db.query('UPDATE products SET suppressed=$2, updated_at=now() WHERE brand=$1', [brand, hide]);
res.redirect('/admin/brands');
} catch (e) { next(e); }
});
// Keyword line-hider — PREVIEW. Shows exactly what a keyword would hide (matching
// title OR brand OR advertiser) BEFORE any write: total, already-hidden, a
// brand×advertiser breakdown, and a thumbnail sample. No mutation happens here.
router.get('/admin/brands/keyword', async (req, res, next) => {
try {
const kw = String(req.query.kw || '').trim().slice(0, 60);
if (kw.length < 2) return res.redirect('/admin/brands');
const like = '%' + kw + '%';
const MATCH = `(title ILIKE $1 OR brand ILIKE $1 OR advertiser ILIKE $1)`;
const [tot, brk, sample] = await Promise.all([
db.query(`SELECT count(*) AS total, count(*) FILTER (WHERE suppressed) AS hidden FROM products WHERE ${MATCH}`, [like]),
db.query(`SELECT COALESCE(NULLIF(brand,''),'(no brand)') AS brand, COALESCE(NULLIF(advertiser,''),'(none)') AS advertiser, network,
count(*) AS n, count(*) FILTER (WHERE suppressed) AS hid
FROM products WHERE ${MATCH} GROUP BY 1,2,3 ORDER BY n DESC LIMIT 100`, [like]),
db.query(`SELECT id,title,brand,advertiser,image_url,suppressed FROM products WHERE ${MATCH} ORDER BY suppressed, title LIMIT 24`, [like]),
]);
const total = Number(tot.rows[0].total), hidden = Number(tot.rows[0].hidden), visible = total - hidden;
if (!total) {
return res.send(shell(`<h1 style="font-size:1.15rem">No matches for “${esc(kw)}”</h1>
<p class="subtle">Nothing in the catalog matches that keyword in title, brand, or advertiser.</p>
<p><a href="/admin/brands">← Back to Brands</a></p>`));
}
const brkRows = brk.rows.map((r) => `<tr class="${Number(r.hid) === Number(r.n) ? 'row-off' : ''}">
<td>${esc(r.brand)}</td><td>${esc(r.advertiser)}<div class="net">${esc(r.network)}</div></td>
<td>${r.n}</td><td>${Number(r.hid) ? `<b style="color:#b1483c">${r.hid}</b>` : '0'}</td></tr>`).join('');
const sampleCards = sample.rows.map((p) => `<div style="border:1px solid var(--line);border-radius:6px;padding:6px;background:#fff;${p.suppressed ? 'opacity:.55' : ''}">
${p.image_url ? `<img src="${esc(p.image_url)}" alt="" style="width:100%;height:80px;object-fit:cover;border-radius:4px">` : ''}
<div style="font-size:.72rem;margin-top:4px;line-height:1.25">${esc(String(p.title).slice(0, 60))}</div>
<div class="net" style="font-size:.62rem">${esc(p.advertiser || '')}${p.suppressed ? ' · hidden' : ''}</div></div>`).join('');
const big = total > 200
? `<p style="background:#faf1e0;border:1px solid #e5cf9e;border-radius:6px;padding:8px 12px;color:#9a6a1c;margin:0 0 12px">⚠︎ This matches <b>${total}</b> products — a broad keyword. Check the breakdown below before applying.</p>`
: '';
const confirm = (action, label, disabled) => disabled
? `<button type="button" disabled style="opacity:.4;cursor:not-allowed;padding:8px 16px">${label}</button>`
: `<form method="post" action="/admin/brands/keyword/apply" style="display:inline;margin:0 6px 0 0">
<input type="hidden" name="kw" value="${esc(kw)}"><input type="hidden" name="action" value="${action}">
<button style="padding:8px 16px;${action === 'hide' ? 'background:#221e19;color:#e6c98a;border-color:#221e19' : ''}">${label}</button></form>`;
res.send(shell(`
<p style="margin:0 0 4px"><a href="/admin/brands" class="subtle">← Brands</a></p>
<h1 style="font-size:1.15rem;margin:0 0 6px">Preview — line “${esc(kw)}”</h1>
${big}
<p style="margin:0 0 14px"><b style="font-size:1.4rem">${total}</b> product${total === 1 ? '' : 's'} match
<span class="subtle">· ${visible} visible · ${hidden} already hidden</span></p>
<div style="margin:0 0 20px">
${confirm('hide', `Hide all ${total} →`, visible === 0)}
${confirm('unhide', `Un-hide all ${total}`, hidden === 0)}
<a href="/admin/brands" style="margin-left:10px;color:#8a8178">Cancel</a>
</div>
<h2>Breakdown — where these come from</h2>
<table><tr><th>Brand</th><th>Advertiser</th><th>Matches</th><th>Hidden</th></tr>${brkRows}</table>
<h2>Sample (${sample.rows.length} of ${total})</h2>
<div style="display:grid;grid-template-columns:repeat(auto-fill,minmax(120px,1fr));gap:8px">${sampleCards}</div>
`));
} catch (e) { next(e); }
});
// Keyword line-hider — APPLY. Flips `suppressed` for every product matching the
// keyword in title/brand/advertiser. Reversible; kept in DB. Reached only from the
// preview's confirm button (same-origin POST, CSRF-guarded above).
router.post('/admin/brands/keyword/apply', async (req, res, next) => {
try {
const kw = String(req.body.kw || '').trim().slice(0, 60);
const hide = req.body.action !== 'unhide';
if (kw.length < 2) return res.status(400).send('Keyword too short.');
const like = '%' + kw + '%';
const r = await db.query(
`UPDATE products SET suppressed=$2, updated_at=now()
WHERE (title ILIKE $1 OR brand ILIKE $1 OR advertiser ILIKE $1)`, [like, hide]);
const msg = `${hide ? 'Hid' : 'Un-hid'} ${r.rowCount} product${r.rowCount === 1 ? '' : 's'} matching “${kw}”.`;
res.redirect('/admin/brands?done=' + encodeURIComponent(msg));
} catch (e) { next(e); }
});
// --- Suppress by keyword/id: durable rules that hide products --------------
// "Enter keywords and those words suppress products" (Steve, 2026-08-03). A rule is
// a persistent record in suppress_rules; applying it flips `suppressed=TRUE` on every
// current match (title/brand/advertiser for keywords, id for ids). Because the whole
// storefront already filters `NOT suppressed`, a suppressed product vanishes from
// /shop, /rooms, guides, and the sitemap at once. VACUUM re-applies every rule so
// products ingested LATER that match an old keyword also get hidden. All reversible.
// SQL that matches a keyword rule in title/brand/advertiser. One placeholder ($1)
// reused thrice. Caller passes the '%'-wrapped value.
const KW_MATCH = `(title ILIKE $1 OR brand ILIKE $1 OR advertiser ILIKE $1)`;
// Apply ONE rule: suppress its still-visible matches. Returns count newly hidden.
async function applyRule(kind, value) {
if (kind === 'id') {
const id = Number(value);
if (!Number.isInteger(id) || id <= 0) return 0;
const r = await db.query(`UPDATE products SET suppressed=TRUE, updated_at=now() WHERE id=$1 AND NOT suppressed`, [id]);
return r.rowCount;
}
const r = await db.query(`UPDATE products SET suppressed=TRUE, updated_at=now() WHERE NOT suppressed AND ${KW_MATCH}`, ['%' + value + '%']);
return r.rowCount;
}
// Per-rule live stats: total products it matches + how many are currently hidden.
async function ruleStats(kind, value) {
if (kind === 'id') {
const r = await db.query(`SELECT count(*) AS total, count(*) FILTER (WHERE suppressed) AS hidden FROM products WHERE id=$1`, [Number(value) || -1]);
return { total: Number(r.rows[0].total), hidden: Number(r.rows[0].hidden) };
}
const r = await db.query(`SELECT count(*) AS total, count(*) FILTER (WHERE suppressed) AS hidden FROM products WHERE ${KW_MATCH}`, ['%' + value + '%']);
return { total: Number(r.rows[0].total), hidden: Number(r.rows[0].hidden) };
}
// The target Steve builds against — surfaced as a one-click "View shop" link.
const SHOP_PREVIEW = '/shop?room=decor&style=modern';
router.get('/admin/suppress', async (req, res, next) => {
try {
const done = String(req.query.done || '').slice(0, 300);
const { rows: rules } = await db.query(`SELECT id, kind, value, created_at FROM suppress_rules ORDER BY created_at DESC`);
// Attach live match/hidden counts to each rule (small table, sequential is fine).
for (const r of rules) Object.assign(r, await ruleStats(r.kind, r.value));
const totalHidden = (await db.query(`SELECT count(*) AS n FROM products WHERE suppressed`)).rows[0].n;
const banner = done
? `<p style="background:#e7f3e7;border:1px solid #bcd9bc;border-radius:6px;padding:8px 12px;color:#2f7a2f;margin:0 0 16px">✓ ${esc(done)}</p>` : '';
const ruleRows = rules.length ? rules.map((r) => {
const visible = r.total - r.hidden;
return `<tr>
<td><span class="pill">${r.kind}</span></td>
<td><b>${esc(r.value)}</b></td>
<td>${r.total}<div class="net">${r.hidden} hidden${visible ? ` · <span style="color:#b1483c">${visible} still visible</span>` : ''}</div></td>
<td>${when(r.created_at)}</td>
<td style="white-space:nowrap">
${visible ? `<form method="post" action="/admin/suppress/${r.id}/apply" style="display:inline;margin:0 4px 0 0"><button title="Suppress the ${visible} matches still showing">Re-apply</button></form>` : ''}
<form method="post" action="/admin/suppress/${r.id}/remove" style="display:inline;margin:0 4px 0 0"><button title="Remove this rule (products it hid stay hidden)">Remove</button></form>
${r.hidden ? `<form method="post" action="/admin/suppress/${r.id}/remove" style="display:inline;margin:0" onsubmit="return confirm('Remove this rule AND un-hide its ${r.hidden} matching products?')"><input type="hidden" name="unhide" value="1"><button title="Remove the rule and show its products again">Remove + unhide</button></form>` : ''}
</td></tr>`;
}).join('') : `<tr><td colspan="5" style="color:#8a8178">No suppression keywords yet. Add one above.</td></tr>`;
res.send(shell(`
<h1 style="font-size:1.15rem;margin:0 0 4px">Suppress products by keyword or id</h1>
<p class="subtle" style="margin:0 0 16px">Any product whose <b>title, brand, or advertiser</b> contains a keyword here is hidden from the storefront (and the room builder, guides, and sitemap). Reversible.</p>
${banner}
<form method="post" action="/admin/suppress/add" style="background:#fff;border:1px solid var(--line);border-radius:8px;padding:16px;margin:0 0 22px;max-width:680px">
<label style="display:block;font-weight:600;margin:0 0 4px">Keywords to suppress</label>
<textarea name="keywords" rows="3" placeholder="One per line or comma-separated — e.g. clearance refurbished, open box" style="width:100%;padding:8px;border:1px solid var(--line);border-radius:5px;font:inherit"></textarea>
<label style="display:block;font-weight:600;margin:12px 0 4px">Product ids to suppress <span class="subtle" style="font-weight:400">(optional, comma/space separated)</span></label>
<input name="ids" placeholder="e.g. 1423 1899" style="width:100%;padding:8px;border:1px solid var(--line);border-radius:5px;font:inherit">
<div style="margin-top:14px"><button style="background:#221e19;color:#e6c98a;border-color:#221e19;padding:8px 18px">Suppress →</button></div>
</form>
<div style="display:flex;gap:10px;align-items:center;margin:0 0 16px;flex-wrap:wrap">
<form method="post" action="/admin/suppress/vacuum" style="margin:0"><button title="Re-apply every rule — suppress any products (e.g. newly-ingested) that now match an existing keyword" style="padding:8px 16px">🧹 Vacuum (re-apply all rules)</button></form>
<a href="/admin/suppress" title="Reload — recompute match counts" style="padding:8px 16px;border:1px solid var(--line);border-radius:5px;text-decoration:none;color:var(--ink);background:#fff">↻ Refresh</a>
<a href="${SHOP_PREVIEW}" target="_blank" style="padding:8px 16px;border:1px solid var(--line);border-radius:5px;text-decoration:none;color:var(--ink);background:#fff">View shop ↗</a>
<span class="subtle" style="margin-left:auto">${totalHidden} product${Number(totalHidden) === 1 ? '' : 's'} hidden total</span>
</div>
<h2>Active suppression rules</h2>
<table><tr><th>Kind</th><th>Value</th><th>Matches</th><th>Added</th><th>Actions</th></tr>${ruleRows}</table>
`));
} catch (e) { next(e); }
});
// Add keyword/id rules and immediately suppress current matches.
router.post('/admin/suppress/add', async (req, res, next) => {
try {
const kws = String(req.body.keywords || '')
.split(/[\n,]/).map((s) => s.trim()).filter((s) => s.length >= 2).slice(0, 100);
const ids = String(req.body.ids || '')
.split(/[\s,]+/).map((s) => s.trim()).filter((s) => /^[0-9]{1,18}$/.test(s)).slice(0, 200);
if (!kws.length && !ids.length) return res.redirect('/admin/suppress?done=' + encodeURIComponent('Nothing to add — enter a keyword (2+ chars) or a numeric id.'));
let hidden = 0, added = 0;
for (const kw of kws) {
const ins = await db.query(`INSERT INTO suppress_rules (kind, value) VALUES ('keyword', $1) ON CONFLICT (kind, value) DO NOTHING`, [kw]);
added += ins.rowCount;
hidden += await applyRule('keyword', kw);
}
for (const id of ids) {
const ins = await db.query(`INSERT INTO suppress_rules (kind, value) VALUES ('id', $1) ON CONFLICT (kind, value) DO NOTHING`, [id]);
added += ins.rowCount;
hidden += await applyRule('id', id);
}
const msg = `Added ${added} rule${added === 1 ? '' : 's'} · suppressed ${hidden} product${hidden === 1 ? '' : 's'}.`;
res.redirect('/admin/suppress?done=' + encodeURIComponent(msg));
} catch (e) { next(e); }
});
// Re-apply ONE rule (suppress its still-visible matches).
router.post('/admin/suppress/:id/apply', async (req, res, next) => {
try {
const id = intId(req.params.id);
if (!id) return res.redirect('/admin/suppress');
const { rows } = await db.query(`SELECT kind, value FROM suppress_rules WHERE id=$1`, [id]);
if (!rows.length) return res.redirect('/admin/suppress');
const n = await applyRule(rows[0].kind, rows[0].value);
res.redirect('/admin/suppress?done=' + encodeURIComponent(`Suppressed ${n} more match${n === 1 ? '' : 'es'} for “${rows[0].value}”.`));
} catch (e) { next(e); }
});
// Vacuum: re-apply EVERY rule. Suppresses any product that matches a rule but slipped
// in later (e.g. a fresh affiliate ingest). Reversible; only flips visible→hidden.
router.post('/admin/suppress/vacuum', async (_req, res, next) => {
try {
const { rows } = await db.query(`SELECT kind, value FROM suppress_rules`);
let hidden = 0;
for (const r of rows) hidden += await applyRule(r.kind, r.value);
res.redirect('/admin/suppress?done=' + encodeURIComponent(`Vacuum complete — re-applied ${rows.length} rule${rows.length === 1 ? '' : 's'}, suppressed ${hidden} newly-matching product${hidden === 1 ? '' : 's'}.`));
} catch (e) { next(e); }
});
// Remove a rule. With ?unhide=1, also un-suppress the products it currently matches
// (a rule is the audit record; removing it alone does NOT auto-show its products).
router.post('/admin/suppress/:id/remove', async (req, res, next) => {
try {
const id = intId(req.params.id);
if (!id) return res.redirect('/admin/suppress');
const { rows } = await db.query(`SELECT kind, value FROM suppress_rules WHERE id=$1`, [id]);
if (!rows.length) return res.redirect('/admin/suppress');
const { kind, value } = rows[0];
let unhid = 0;
if (req.body.unhide === '1') {
if (kind === 'id') {
const r = await db.query(`UPDATE products SET suppressed=FALSE, updated_at=now() WHERE id=$1 AND suppressed`, [Number(value) || -1]);
unhid = r.rowCount;
} else {
const r = await db.query(`UPDATE products SET suppressed=FALSE, updated_at=now() WHERE suppressed AND ${KW_MATCH}`, ['%' + value + '%']);
unhid = r.rowCount;
}
}
await db.query(`DELETE FROM suppress_rules WHERE id=$1`, [id]);
const msg = req.body.unhide === '1' ? `Removed rule “${value}” and un-hid ${unhid} product${unhid === 1 ? '' : 's'}.` : `Removed rule “${value}”.`;
res.redirect('/admin/suppress?done=' + encodeURIComponent(msg));
} catch (e) { next(e); }
});
// --- Affiliates: see every link source & switch it on/off -----------------
// An affiliate is either a whole NETWORK (cj/amazon/...) or a specific ADVERTISER
// (merchant) on that network. OFF here => that source's products vanish from the
// storefront, room builder, and brand facet (see the AFFILIATE_ENABLED gate in
// lib/catalog.js + lib/rooms.js). A product is live only if BOTH its network and
// its advertiser are on.
// Null byte as composite-key separator: guaranteed absent from any real network or
// advertiser string, so the key never collides across its two parts.
const KEY_SEP = '\x00';
const key = (net, adv) => `${net}${KEY_SEP}${adv || ''}`;
// A live on/off form button. `disabledOn` (network is off) locks an advertiser's
// row so you can't pretend a merchant is live while its whole network is dark.
function toggleForm(network, advertiser, enabled, locked) {
const label = enabled ? 'Turn OFF' : 'Turn ON';
const btn = locked
? `<button type="button" disabled title="Network is off — turn the network on first" style="opacity:.4;cursor:not-allowed">Turn ON</button>`
: `<button title="${enabled ? 'Hide this source from the storefront' : 'Show this source on the storefront'}">${label}</button>`;
return `<form method="post" action="/admin/affiliates/toggle" style="margin:0">
<input type="hidden" name="network" value="${esc(network)}">
<input type="hidden" name="advertiser" value="${esc(advertiser || '')}">${btn}</form>`;
}
router.get('/admin/affiliates', async (_req, res, next) => {
try {
const [nets, advs, clk] = await Promise.all([
// network-level rollup + its own on/off row (advertiser='')
db.query(`SELECT p.network,
count(*) AS products,
count(*) FILTER (WHERE p.in_stock) AS in_stock,
count(DISTINCT p.advertiser) AS advertisers,
max(p.created_at) AS latest,
COALESCE(s.enabled, TRUE) AS enabled
FROM products p
LEFT JOIN affiliate_settings s ON s.network = p.network AND s.advertiser = ''
GROUP BY p.network, s.enabled
ORDER BY products DESC`),
// advertiser-level rollup + own state (adv_enabled) and its network's state (net_enabled)
db.query(`SELECT p.network,
COALESCE(p.advertiser, '') AS advertiser,
count(*) AS products,
count(*) FILTER (WHERE p.in_stock) AS in_stock,
max(p.created_at) AS latest,
COALESCE(s.enabled, TRUE) AS adv_enabled,
COALESCE(ns.enabled, TRUE) AS net_enabled
FROM products p
LEFT JOIN affiliate_settings s ON s.network = p.network AND s.advertiser = COALESCE(p.advertiser, '')
LEFT JOIN affiliate_settings ns ON ns.network = p.network AND ns.advertiser = ''
GROUP BY p.network, COALESCE(p.advertiser, ''), s.enabled, ns.enabled
ORDER BY p.network, products DESC`),
// clicks per (network, advertiser), total + last 7 days
db.query(`SELECT network, COALESCE(advertiser, '') AS advertiser,
count(*) AS total,
count(*) FILTER (WHERE clicked_at > now() - interval '7 days') AS n7
FROM clicks GROUP BY network, COALESCE(advertiser, '')`),
]);
// index clicks by advertiser key + roll up to network totals
const clkByAdv = new Map(), clkByNet = new Map();
for (const r of clk.rows) {
clkByAdv.set(key(r.network, r.advertiser), r);
const nn = clkByNet.get(r.network) || { total: 0, n7: 0 };
nn.total += Number(r.total); nn.n7 += Number(r.n7);
clkByNet.set(r.network, nn);
}
const onOff = (on) => `<span class="pill ${on ? 'on' : 'off'}">${on ? '● ON' : '○ OFF'}</span>`;
// ---- Networks table (coarse master switch per affiliate program) ----
const netRows = nets.rows.map((n) => {
const ck = clkByNet.get(n.network) || { total: 0, n7: 0 };
return `<tr>
<td><b class="net">${esc(n.network)}</b></td>
<td>${n.advertisers}</td>
<td>${n.in_stock} <span class="subtle">/ ${n.products}</span></td>
<td>${ck.n7} <span class="subtle">/ ${ck.total}</span></td>
<td>${when(n.latest)}</td>
<td>${onOff(n.enabled)}</td>
<td>${toggleForm(n.network, '', n.enabled, false)}</td>
</tr>`;
}).join('');
// ---- Advertisers table (the real "affiliates" list, grouped by network) ----
const advRows = advs.rows.map((a) => {
const ck = clkByAdv.get(key(a.network, a.advertiser)) || { total: 0, n7: 0 };
const live = a.adv_enabled && a.net_enabled; // effective storefront visibility
const statusCell = a.net_enabled
? onOff(a.adv_enabled)
: `${onOff(false)} <span class="subtle" title="Suppressed because the ${esc(a.network)} network is off">(network off)</span>`;
return `<tr class="${live ? '' : 'row-off'}">
<td>${esc(a.advertiser || '(unattributed)')}<div class="net">${esc(a.network)}</div></td>
<td>${a.in_stock} <span class="subtle">/ ${a.products}</span></td>
<td>${ck.n7} <span class="subtle">/ ${ck.total}</span></td>
<td>${when(a.latest)}</td>
<td>${statusCell}</td>
<td>${toggleForm(a.network, a.advertiser, a.adv_enabled, !a.net_enabled)}</td>
</tr>`;
}).join('');
const offCount = advs.rows.filter((a) => !(a.adv_enabled && a.net_enabled)).length;
const intro = `<p class="subtle" style="margin:0 0 6px">
An <b>affiliate</b> is a source of tracked links — a whole network or a single merchant on it.
Switch one <b>OFF</b> and its products immediately disappear from the storefront, room builder, and brand
filters (no re-import needed); switch it back <b>ON</b> to restore them. Nothing is deleted.
${offCount ? `<b style="color:#b1483c"> ${offCount} affiliate${offCount === 1 ? '' : 's'} currently off.</b>` : ''}</p>`;
res.send(shell(`
<h1 style="font-size:1.15rem;margin:0 0 4px">Affiliates</h1>
${affiliateTabs('active')}
${cjAccountBar()}
${intro}
<h2>Networks — master switch per program</h2>
<table>
<tr><th>Network</th><th>Merchants</th><th>In-stock / total</th><th>Clicks 7d / all</th><th>Newest item</th><th>Status</th><th>Switch</th></tr>
${netRows || `<tr><td colspan="7" class="subtle">No products ingested yet.</td></tr>`}
</table>
<h2>Advertisers — every merchant (${advs.rows.length})</h2>
<table>
<tr><th>Affiliate (merchant)</th><th>In-stock / total</th><th>Clicks 7d / all</th><th>Newest item</th><th>Status</th><th>Switch</th></tr>
${advRows || `<tr><td colspan="6" class="subtle">No advertisers found.</td></tr>`}
</table>
`));
} catch (e) { next(e); }
});
// Suggested lines to join — a curated gap-finder. Shows on-brand affiliate programs
// across CJ + other networks, flags the ones already in the catalog (✓ Joined), and
// gives a "Join via <Network>" deep-link into the right network's marketplace for the rest.
router.get('/admin/affiliates/suggested', async (req, res, next) => {
try {
const lines = loadSuggestions();
const { rows: advRows } = await db.query(
`SELECT DISTINCT advertiser, network FROM products WHERE advertiser IS NOT NULL AND advertiser <> ''`);
const norm = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim();
const advs = advRows.map((a) => ({ ...a, n: norm(a.advertiser) }));
// A suggestion is "joined" if a live advertiser name overlaps it either direction
// ("Wayfair" ⊂ "Wayfair North America"). Returns the networks it's live on.
const joinedOn = (name) => {
const n = norm(name);
if (!n) return [];
const hits = advs.filter((a) => a.n && (a.n.includes(n) || n.includes(a.n)));
return [...new Set(hits.map((h) => h.network))];
};
const netFilter = NETWORKS[req.query.network] ? req.query.network : '';
const enriched = lines.map((l) => {
const on = joinedOn(l.name);
const nets = [l.network, ...(l.also || [])].filter((x) => NETWORKS[x]);
return { ...l, nets, joined: on.length > 0, joinedNets: on };
});
const shown = netFilter ? enriched.filter((l) => l.nets.includes(netFilter)) : enriched;
const joinedCount = enriched.filter((l) => l.joined).length;
const openCount = enriched.length - joinedCount;
// network filter chips
const chip = (netKey, label, on) => `<a href="/admin/affiliates/suggested${netKey ? `?network=${netKey}` : ''}" class="${on ? 'on' : ''}">${esc(label)}</a>`;
const chips = `<div class="chips">
${chip('', 'All networks', !netFilter)}
${Object.keys(NETWORKS).map((k) => chip(k, NETWORKS[k].label, netFilter === k)).join('')}
</div>`;
const netBadges = (nets) => nets.map((k, i) =>
`<span class="net"${i ? ' style="margin-left:6px"' : ''}>${esc(NETWORKS[k].label)}</span>`).join('');
const rows = shown.map((l) => {
const primary = l.network;
const joinedLabel = l.joinedNets.map((k) => (NETWORKS[k] ? NETWORKS[k].label : k)).join(', ');
const t = joinTarget(primary, l.name);
const tip = t.prefilled
? `Opens ${NETWORKS[primary].label} marketplace search pre-filled for "${l.name}" — click Join Program`
: `Opens ${NETWORKS[primary].label} — search "${l.name}" and click Join Program`;
const joinBtn = l.joined
? `<span class="subtle" title="Already in your catalog on ${esc(joinedLabel)}">on ${esc(joinedLabel)}</span>`
: `<a class="ezjoin" href="${esc(t.url)}" target="_blank" rel="noopener nofollow" title="${esc(tip)}">Join via ${esc(NETWORKS[primary].label)} →</a>`;
return `<tr class="${l.joined ? 'row-off' : ''}">
<td><b>${esc(l.name)}</b></td>
<td>${netBadges(l.nets)}</td>
<td>${esc(l.category || '')}</td>
<td class="why">${esc(l.why || '')}</td>
<td>${l.joined ? '<span class="pill on">✓ Joined</span>' : '<span class="pill off">Not joined</span>'}</td>
<td>${joinBtn}</td>
</tr>`;
}).join('');
const intro = `<p class="subtle" style="margin:0 0 10px">
On-brand affiliate programs worth adding. <b>${openCount}</b> not yet joined ·
<b>${joinedCount}</b> already in your catalog. There's no true one-click join —
<b>Join via <Network></b> drops you into that network's publisher marketplace
(pre-filled search where the network supports it) so you can hit <i>Join Program</i>.
Extend the list in <code>data/suggested-affiliates.json</code>.</p>`;
res.send(shell(`
<h1 style="font-size:1.15rem;margin:0 0 4px">Affiliates</h1>
${affiliateTabs('suggested')}
${cjAccountBar()}
${intro}
${chips}
<table>
<tr><th>Line</th><th>Network(s)</th><th>Category</th><th>Why it fits</th><th>Status</th><th>Join</th></tr>
${rows || `<tr><td colspan="6" class="subtle">No suggestions${netFilter ? ` on ${esc(NETWORKS[netFilter].label)}` : ''}. Add some to data/suggested-affiliates.json.</td></tr>`}
</table>
`));
} catch (e) { next(e); }
});
// Toggle one affiliate (network-wide when advertiser is blank, else that merchant).
// Default state is ENABLED, so we only ever persist a row once it's been touched;
// the flip is idempotent and reversible. We guard against junk keys by requiring
// the (network[, advertiser]) to actually exist in the catalog.
router.post('/admin/affiliates/toggle', async (req, res, next) => {
try {
const network = String(req.body.network || '').trim().slice(0, 40);
const advertiser = String(req.body.advertiser || '').trim().slice(0, 200);
if (!network) return res.status(400).send('Missing network.');
const exists = advertiser
? await db.query('SELECT 1 FROM products WHERE network=$1 AND advertiser=$2 LIMIT 1', [network, advertiser])
: await db.query('SELECT 1 FROM products WHERE network=$1 LIMIT 1', [network]);
if (!exists.rowCount) return res.status(404).send('Unknown affiliate.');
await db.query(
`INSERT INTO affiliate_settings (network, advertiser, enabled, updated_at)
VALUES ($1, $2, FALSE, now())
ON CONFLICT (network, advertiser)
DO UPDATE SET enabled = NOT affiliate_settings.enabled, updated_at = now()`,
[network, advertiser]);
res.redirect('/admin/affiliates');
} catch (e) { next(e); }
});
module.exports = router;