← back to NationalPaperHangers
routes/public.js
1108 lines
const express = require('express');
const db = require('../lib/db');
const bookingToken = require('../lib/booking-token');
const butlr = require('../lib/butlr');
const { requireInstaller } = require('../lib/auth');
const router = express.Router();
router.get('/', async (req, res, next) => {
try {
const featured = await db.many(
`SELECT id, slug, business_name, headline, city, state, market_segments, materials, accreditations, verified, claim_status
FROM installers
WHERE status = 'active' AND verified = true
ORDER BY tier DESC, updated_at DESC
LIMIT 6`
);
const stats = await db.one(
`SELECT
COUNT(*) FILTER (WHERE status='active' OR claim_status='unclaimed') AS total_listings,
COUNT(DISTINCT state) FILTER (WHERE state IS NOT NULL AND (status='active' OR claim_status='unclaimed')) AS state_count,
COUNT(*) FILTER (WHERE 'WIA Certified Installer' = ANY(accreditations)) AS accredited_count
FROM installers`
);
res.render('public/home', {
title: 'National Paper Hangers · Verified luxury wallcovering installers',
metaDescription: `Directory of verified luxury wallcovering installers. ${stats.total_listings || ''}+ studios across ${stats.state_count || ''} states. Search and schedule consultations directly with installation studios specializing in hand-painted, silk, grasscloth, and mural wallcoverings.`,
canonicalPath: '/',
featured, stats
});
} catch (err) { next(err); }
});
router.get('/find', async (req, res, next) => {
try {
const q = (req.query.q || '').trim();
const segment = (req.query.segment || '').trim();
const material = (req.query.material || '').trim();
const zip = (req.query.zip || '').trim();
const state = (req.query.state || '').trim();
// Default: show ALL active installers (claimed + unclaimed). Steve override
// 2026-05-07 — wants the directory's full breadth visible by default; the
// earlier 'claimed-only' default was hiding 519 of 524 shops. ?show=claimed
// now opts down to the 5 verified studios; toggleHref still works in reverse.
const show = (req.query.show || 'all').trim();
const where = (show === 'claimed')
? [`status = 'active' AND (claim_status = 'self' OR claim_status = 'claimed')`]
: [`(status = 'active' OR claim_status = 'unclaimed')`];
const params = [];
let i = 1;
if (q) {
params.push(`%${q.toLowerCase()}%`);
where.push(`(LOWER(business_name) LIKE $${i} OR LOWER(headline) LIKE $${i} OR LOWER(bio) LIKE $${i} OR LOWER(city) LIKE $${i})`);
i++;
}
if (segment) {
params.push(segment);
where.push(`$${i} = ANY(market_segments)`);
i++;
}
if (material) {
params.push(material);
where.push(`$${i} = ANY(materials)`);
i++;
}
if (zip) {
params.push(zip);
where.push(`zip = $${i}`);
i++;
}
if (state) {
params.push(state.toUpperCase());
where.push(`state = $${i}`);
i++;
}
const sql = `
SELECT i.id, i.slug, i.business_name, i.headline, i.bio, i.city, i.state, i.zip,
i.market_segments, i.materials, i.brands_handled, i.accreditations,
i.verified, i.tier, i.response_time_hours, i.claim_status, i.instagram_handle, i.website,
COALESCE((
SELECT COUNT(*) FROM installer_credentials c
WHERE c.installer_id = i.id AND c.ops_verified = true
), 0)::int AS verified_brands_count,
COALESCE((
-- LIMIT must be inside a sub-SELECT: applied next to array_agg
-- it does nothing (the aggregate collapses to one row), so the
-- card would show every verified brand instead of the top 3.
SELECT array_agg(b.brand)
FROM (
SELECT c.brand
FROM installer_credentials c
WHERE c.installer_id = i.id AND c.ops_verified = true
ORDER BY c.display_order, c.year_issued DESC NULLS LAST
LIMIT 3
) b
), ARRAY[]::text[]) AS verified_brands
FROM installers i
WHERE ${where.join(' AND ')}
ORDER BY (CASE WHEN claim_status = 'claimed' OR claim_status = 'self' THEN 0 ELSE 1 END),
(CASE tier WHEN 'enterprise' THEN 0 WHEN 'signature' THEN 1 WHEN 'pro' THEN 2 ELSE 3 END),
verified DESC, updated_at DESC
LIMIT 600`;
const installers = await db.many(sql, params);
// Dynamic title reflects active filters for SEO long-tail.
const titleParts = [];
if (state) titleParts.push(state.toUpperCase());
if (material) titleParts.push(material.replace(/_/g, ' '));
if (segment) titleParts.push(segment.replace(/_/g, ' '));
const title = titleParts.length
? `${titleParts.map(s => s.charAt(0).toUpperCase() + s.slice(1)).join(' · ')} wallpaper installers · National Paper Hangers`
: 'Find a verified wallpaper installer · National Paper Hangers';
const metaDescription = `Browse ${installers.length}+ verified wallcovering installers${state ? ' in ' + state.toUpperCase() : ' across the United States'}. Filter by city, ZIP, market segment, and material. Luxury residential, hospitality, and commercial.`;
// Build a toggle URL preserving current filters.
const _filterParams = new URLSearchParams();
if (q) _filterParams.set('q', q);
if (zip) _filterParams.set('zip', zip);
if (state) _filterParams.set('state', state);
if (segment) _filterParams.set('segment', segment);
if (material) _filterParams.set('material', material);
// Toggle now flips the OTHER way: default is all → claimed-only opt-in
if (show === 'all') _filterParams.set('show', 'claimed');
const toggleHref = '/find' + (_filterParams.toString() ? '?' + _filterParams.toString() : '');
// Cross-link to /map preserving the shared filter params (q/segment/material).
// ZIP/state aren't yet in the map UI; they get dropped silently.
const _mapParams = new URLSearchParams();
if (q) _mapParams.set('q', q);
if (segment) _mapParams.set('segment', segment);
if (material) _mapParams.set('material', material);
const mapHref = '/map' + (_mapParams.toString() ? '?' + _mapParams.toString() : '');
// rel=alternate JSON mirror — apps + agents + structured-data crawlers
// can fetch the same filtered list as JSON without HTML scraping.
const _baseUrl = (process.env.PUBLIC_URL || 'https://www.nationalpaperhangers.com').replace(/\/+$/, '');
const _apiQs = new URLSearchParams();
if (q) _apiQs.set('q', q);
if (segment) _apiQs.set('segment', segment);
if (material) _apiQs.set('material', material);
if (zip) _apiQs.set('zip', zip);
if (state) _apiQs.set('state', state);
if (show !== 'all') _apiQs.set('show', show);
const alternateJsonUrl = _baseUrl + '/api/find' + (_apiQs.toString() ? '?' + _apiQs.toString() : '');
const canonicalUrl = _baseUrl + '/find' + (_apiQs.toString() ? '?' + _apiQs.toString() : '');
// HTTP Link header — RFC 8288. Crawlers that don't parse HTML still see this.
res.set('Link', `<${canonicalUrl}>; rel="canonical", <${alternateJsonUrl}>; rel="alternate"; type="application/json"`);
res.render('public/find', {
title, metaDescription,
canonicalPath: '/find',
installers,
q, segment, material, zip, state, show, toggleHref, mapHref,
alternateJsonUrl
});
} catch (err) { next(err); }
});
// JSON-API mirror of /find — same filter logic, JSON output. Surfaced via
// rel="alternate" link tag + HTTP Link header on the HTML /find route.
router.get('/api/find', async (req, res, next) => {
try {
const q = (req.query.q || '').trim();
const segment = (req.query.segment || '').trim();
const material = (req.query.material || '').trim();
const zip = (req.query.zip || '').trim();
const state = (req.query.state || '').trim();
const show = (req.query.show || 'all').trim();
const where = (show === 'claimed')
? [`status = 'active' AND (claim_status = 'self' OR claim_status = 'claimed')`]
: [`(status = 'active' OR claim_status = 'unclaimed')`];
const params = [];
let i = 1;
if (q) {
params.push(`%${q.toLowerCase()}%`);
where.push(`(LOWER(business_name) LIKE $${i} OR LOWER(headline) LIKE $${i} OR LOWER(bio) LIKE $${i} OR LOWER(city) LIKE $${i})`);
i++;
}
if (segment) { params.push(segment); where.push(`$${i} = ANY(market_segments)`); i++; }
if (material) { params.push(material); where.push(`$${i} = ANY(materials)`); i++; }
if (zip) { params.push(zip); where.push(`zip = $${i}`); i++; }
if (state) { params.push(state.toUpperCase()); where.push(`state = $${i}`); i++; }
const installers = await db.many(`
SELECT i.id, i.slug, i.business_name, i.headline, i.city, i.state, i.zip,
i.market_segments, i.materials, i.tier, i.claim_status, i.verified, i.website
FROM installers i
WHERE ${where.join(' AND ')}
ORDER BY (CASE WHEN claim_status = 'claimed' OR claim_status = 'self' THEN 0 ELSE 1 END),
(CASE tier WHEN 'enterprise' THEN 0 WHEN 'signature' THEN 1 WHEN 'pro' THEN 2 ELSE 3 END),
verified DESC, updated_at DESC
LIMIT 600
`, params);
res.set('Cache-Control', 'public, max-age=300');
res.json({
query: { q, segment: segment || null, material: material || null, zip: zip || null, state: state || null, show },
pagination: { totalCount: installers.length, limit: 600 },
results: installers
});
} catch (err) { next(err); }
});
// ─── /papers — "This Paper" peer-installer commentary (UX backlog #3) ────
// Public list of wallcovering threads with comment-count badges. Read-only
// in this tick — comment submission lands in tick 3.
router.get('/papers', async (req, res, next) => {
try {
const category = (req.query.category || '').trim();
const params = [];
const where = [];
let i = 1;
if (category) {
params.push(category);
where.push(`category = $${i}`);
i++;
}
const sql = `
SELECT id, slug, brand, paper_name, sku, paste_type, category,
description, comment_count, updated_at
FROM paper_threads
${where.length ? 'WHERE ' + where.join(' AND ') : ''}
ORDER BY comment_count DESC, brand, paper_name
LIMIT 200`;
const threads = await db.many(sql, params);
// Category facets — surface what's available so the filter pill row
// doesn't show empty buckets.
const catRows = await db.many(
`SELECT category, COUNT(*)::int AS n
FROM paper_threads
WHERE category IS NOT NULL
GROUP BY category
ORDER BY n DESC, category`
);
res.render('public/papers-list', {
title: 'Wallcovering papers · install threads · National Paper Hangers',
threads,
categories: catRows,
activeCategory: category || null
});
} catch (err) { next(err); }
});
router.get('/papers/:slug', async (req, res, next) => {
try {
const thread = await db.one(
`SELECT id, slug, brand, paper_name, sku, paste_type, category,
description, seeded_by, comment_count, created_at, updated_at
FROM paper_threads
WHERE slug = $1`,
[req.params.slug]
);
if (!thread) return res.status(404).render('public/404', { title: 'Not Found' });
// Public-readable comments — exclude flagged. Join installer for
// attribution + selection signal (designers see which installers
// contribute thoughtful answers).
const comments = await db.many(
`SELECT c.id, c.body, c.helpful_count, c.created_at, c.edited_at,
i.slug AS installer_slug, i.business_name AS installer_business_name,
i.city AS installer_city, i.state AS installer_state,
i.tier AS installer_tier, i.verified AS installer_verified
FROM paper_comments c
JOIN installers i ON i.id = c.installer_id
WHERE c.thread_id = $1 AND c.flagged = false
AND (i.status = 'active' OR i.claim_status = 'unclaimed')
ORDER BY c.helpful_count DESC, c.created_at ASC
LIMIT 200`,
[thread.id]
);
res.render('public/papers-detail', {
title: `${thread.brand} ${thread.paper_name} — install thread · National Paper Hangers`,
thread,
comments
});
} catch (err) { next(err); }
});
// POST /papers/:slug/comments — verified-installer note submission. Body is
// the only field. We don't expose authorship as a form choice — it's always
// the logged-in installer. Comments are public-readable immediately (no
// pre-moderation queue) but ops can flag them via /admin/papers-moderation.
router.post('/papers/:slug/comments', express.urlencoded({ extended: false }), requireInstaller, async (req, res, next) => {
try {
const thread = await db.one(
`SELECT id, slug FROM paper_threads WHERE slug = $1`,
[req.params.slug]
);
if (!thread) return res.status(404).render('public/404', { title: 'Not Found' });
const body = String(req.body.body || '').trim();
if (body.length < 20) {
req.session.flash = { error: 'Notes must be at least 20 characters — give designers something they can act on.' };
return res.redirect(`/papers/${thread.slug}#comment-form`);
}
if (body.length > 4000) {
req.session.flash = { error: 'Notes are capped at 4,000 characters.' };
return res.redirect(`/papers/${thread.slug}#comment-form`);
}
await db.query(
`INSERT INTO paper_comments (thread_id, installer_id, body) VALUES ($1, $2, $3)`,
[thread.id, req.installer.id, body]
);
req.session.flash = { ok: 'Note added to the thread.' };
res.redirect(`/papers/${thread.slug}#comments`);
} catch (err) { next(err); }
});
// POST /papers/:slug/comments/:id/helpful — designer-and-anyone vote.
// No auth required (designer participation is the value). One vote per
// IP-hash per comment via PK on paper_comment_votes. Idempotent.
router.post('/papers/:slug/comments/:id/helpful', express.urlencoded({ extended: false }), async (req, res, next) => {
try {
const commentId = parseInt(req.params.id, 10);
if (!Number.isFinite(commentId)) return res.status(400).send('bad request');
// Verify the comment exists + belongs to this thread (defense in depth —
// a malformed URL with a mismatched slug shouldn't allow voting on
// unrelated comments).
const c = await db.one(
`SELECT c.id FROM paper_comments c
JOIN paper_threads t ON t.id = c.thread_id
WHERE c.id = $1 AND t.slug = $2 AND c.flagged = false`,
[commentId, req.params.slug]
);
if (!c) return res.status(404).render('public/404', { title: 'Not Found' });
const ipHash = require('crypto')
.createHash('sha256')
.update((req.headers['x-forwarded-for'] || req.ip || '') + (process.env.SESSION_SECRET || ''))
.digest('hex')
.slice(0, 16);
// Try to record the vote. ON CONFLICT DO NOTHING + RETURNING distinguishes
// first-vote from repeat-vote. Only first-vote bumps the denormalized
// counter — repeat-votes silently no-op.
const inserted = await db.many(
`INSERT INTO paper_comment_votes (comment_id, ip_hash)
VALUES ($1, $2)
ON CONFLICT (comment_id, ip_hash) DO NOTHING
RETURNING comment_id`,
[commentId, ipHash]
);
if (inserted.length > 0) {
await db.query(`UPDATE paper_comments SET helpful_count = helpful_count + 1 WHERE id = $1`, [commentId]);
}
res.redirect(`/papers/${req.params.slug}#comments`);
} catch (err) { next(err); }
});
router.get('/installer/:slug', async (req, res, next) => {
try {
const installer = await db.one(
`SELECT * FROM installers WHERE slug = $1 AND (status = 'active' OR claim_status = 'unclaimed')`,
[req.params.slug]
);
if (!installer) return res.status(404).render('public/404', { title: 'Not Found' });
// Resolve featured installation videos to embeddable iframes (YouTube /
// Instagram reels / TikTok / Vimeo). Fall through gracefully when no
// featured_video_urls — the EJS section just doesn't render.
const socialEmbed = require('../lib/social-embed');
const featuredVideos = socialEmbed.embedAll(installer.featured_video_urls);
const portfolio = await db.many(
`SELECT * FROM installer_portfolio WHERE installer_id = $1 ORDER BY display_order, id`,
[installer.id]
);
const reviews = await db.many(
`SELECT * FROM installer_reviews WHERE installer_id = $1 AND published = true ORDER BY published_at DESC LIMIT 8`,
[installer.id]
);
// Brand-Trained credentials (UX idea #2) — only ops-verified entries are
// shown publicly. Studios can self-add via /admin/profile but must be
// reviewed before badges appear on the public profile (prevents spam).
const credentials = await db.many(
`SELECT brand, credential_type, year_issued, year_expires, certificate_url
FROM installer_credentials
WHERE installer_id = $1 AND ops_verified = true
ORDER BY display_order, year_issued DESC NULLS LAST`,
[installer.id]
);
// Acceptance-rate signal (UX idea #7) — rolling 365 days. Hidden when
// total < 10 to avoid noisy small-sample numbers on new studios.
let acceptance = null;
const acc = await db.one(
`SELECT
COUNT(*) FILTER (WHERE status IN ('confirmed','completed'))::int AS accepted,
COUNT(*) FILTER (WHERE status = 'declined')::int AS declined
FROM bookings
WHERE installer_id = $1
AND created_at >= now() - interval '365 days'
AND status IN ('confirmed','completed','declined')`,
[installer.id]
);
if (acc) {
const total = (acc.accepted || 0) + (acc.declined || 0);
if (total >= 10) {
acceptance = {
rate: Math.round((acc.accepted / total) * 100),
accepted: acc.accepted,
declined: acc.declined,
total
};
}
}
// Paper-thread contribution signal (UX backlog #3, tick 4). Designers see
// which studios contribute craft knowledge → selection signal that no
// generic directory has. Limit to non-flagged comments + 3 recent threads
// for the inline list.
const contribSummary = await db.one(
`SELECT COUNT(*)::int AS n,
COALESCE(SUM(helpful_count), 0)::int AS helpful_total
FROM paper_comments
WHERE installer_id = $1 AND flagged = false`,
[installer.id]
);
const contribRecent = await db.many(
`SELECT DISTINCT ON (t.id) t.slug, t.brand, t.paper_name, c.created_at
FROM paper_comments c
JOIN paper_threads t ON t.id = c.thread_id
WHERE c.installer_id = $1 AND c.flagged = false
ORDER BY t.id, c.created_at DESC
LIMIT 3`,
[installer.id]
);
const paperContributions = {
count: contribSummary.n,
helpful: contribSummary.helpful_total,
recent: contribRecent
};
const cityState = [installer.city, installer.state].filter(Boolean).join(', ');
const matSummary = (installer.materials || []).slice(0, 3).map(m => m.replace(/_/g, ' ')).join(', ');
// Pick the template the studio chose (defaults to 'editorial' via SQL default).
// Fallback to the legacy 'public/installer' view if template_slug isn't set
// or names something we don't ship — so existing rows never 500.
var TEMPLATES = ['editorial','trade-pro','concierge','studio','heritage','bilingue'];
// ?_preview=<slug> lets the /admin/template chooser iframe render any of
// the 6 layouts against this studio's data without saving the choice.
var previewSlug = String(req.query._preview || '').toLowerCase();
var chosen = TEMPLATES.indexOf(previewSlug) !== -1 ? previewSlug : installer.template_slug;
var tpl = (chosen && TEMPLATES.indexOf(chosen) !== -1)
? 'public/installer-tpl-' + chosen
: 'public/installer';
// OG preview: prefer studio's chosen hero, then their first portfolio shot,
// then a segment-matched PD image, then the brand default (head.ejs falls
// back to /img/og-default.jpg when ogImage is null).
var _settings = installer.template_settings || {};
var _segImg = require('../lib/segment-image').pickSegmentImage(installer);
var _ogImage = _settings.hero_url
|| (portfolio[0] && portfolio[0].image_url)
|| (_segImg && _segImg.file)
|| null;
res.render(tpl, {
title: cityState
? `${installer.business_name} — Wallpaper Installer in ${cityState} · National Paper Hangers`
: `${installer.business_name} · National Paper Hangers`,
metaDescription: `${installer.business_name}${cityState ? ' is a verified wallcovering installer in ' + cityState : ' — wallcovering installer'}.${matSummary ? ' Specializing in ' + matSummary + '.' : ''} Book a consultation on National Paper Hangers.`,
canonicalPath: `/installer/${installer.slug}`,
ogImage: _ogImage,
installer, portfolio, reviews, acceptance, credentials, featuredVideos,
paperContributions,
// "Call this installer" — shown only when the Butlr integration is
// configured (BUTLR_EXTERNAL_SECRET set) AND the studio has a phone.
butlrEnabled: butlr.isConfigured()
});
} catch (err) { next(err); }
});
router.get('/installer/:slug/book', async (req, res, next) => {
try {
// Show the page for both active AND unclaimed studios. Unclaimed
// studios fall through to the calendarEnabled=false branch which
// surfaces the contact-direct path (website / IG / phone).
// Bug fix 2026-05-06: previously 404'd for unclaimed, which created
// a dead-end click from /installer/:slug pages.
const installer = await db.one(
`SELECT id, slug, business_name, city, state, response_time_hours, tier,
website, instagram_handle, phone, claim_status, status
FROM installers WHERE slug = $1 AND (status = 'active' OR claim_status = 'unclaimed')`,
[req.params.slug]
);
if (!installer) return res.status(404).render('public/404', { title: 'Not Found' });
// Calendar requires THREE things, not just paid tier (Steve 2026-05-06):
// 1. Studio is claimed (claim_status in 'self','claimed') — there's a real
// installer-user behind the listing, not a directory shell.
// 2. Subscription tier is pro/signature/enterprise — calendar is paid.
// 3. At least one active availability window exists — the studio has
// actually configured their working hours. Without this the slot grid
// renders empty and looks broken.
// Any missing → fall through to the contact-direct branch (website / IG / phone).
const isClaimed = ['self','claimed'].includes(installer.claim_status);
const isPaidTier = ['pro','signature','enterprise'].includes(installer.tier);
let hasAvailability = false;
if (isClaimed && isPaidTier) {
const avail = await db.one(
'SELECT COUNT(*)::int AS c FROM installer_availability WHERE installer_id = $1 AND active = true',
[installer.id]
);
hasAvailability = avail.c > 0;
}
const calendarEnabled = isClaimed && isPaidTier && hasAvailability;
// Surface WHY the calendar is off so the EJS can show the right copy:
// 'unclaimed' → "Studio hasn't claimed their listing yet"
// 'free_tier' → "Studio is on the free tier; book by contacting directly"
// 'no_schedule' → "Studio is set up but hasn't configured availability yet"
let calendarBlockedReason = null;
if (!isClaimed) calendarBlockedReason = 'unclaimed';
else if (!isPaidTier) calendarBlockedReason = 'free_tier';
else if (!hasAvailability) calendarBlockedReason = 'no_schedule';
res.render('public/book', {
title: `Book ${installer.business_name} · National Paper Hangers`,
metaDescription: `Schedule a consultation with ${installer.business_name}${installer.city ? ' in ' + installer.city + ', ' + installer.state : ''}. Pick from open slots in their live calendar.`,
canonicalPath: `/installer/${installer.slug}/book`,
installer, calendarEnabled, calendarBlockedReason,
// Stripe publishable key for the booking-deposit UI. Null in mock mode
// — book.ejs renders a "test mode" callout instead of the card element.
stripePublishableKey: process.env.STRIPE_PUBLISHABLE_KEY || null
});
} catch (err) { next(err); }
});
// POST /installer/:slug/notify-when-live — capture interest from visitors who
// hit /book on an unclaimed studio. When the studio claims + configures
// availability, every email here gets a one-shot notification (separate
// scheduled job; not fired inline). Per-installer dedup via UNIQUE constraint.
router.post('/installer/:slug/notify-when-live', express.urlencoded({ extended: false }), async (req, res, next) => {
try {
const installer = await db.one(
`SELECT id, slug, business_name, claim_status FROM installers WHERE slug = $1`,
[req.params.slug]
);
if (!installer) return res.status(404).render('public/404', { title: 'Not Found' });
// Only meaningful for unclaimed listings — silently treat already-claimed
// as a no-op (avoid leaking that a different visitor's "interest" worked).
const email = String(req.body.email || '').trim().toLowerCase();
const zip = String(req.body.zip || '').trim().slice(0, 10) || null;
const notes = String(req.body.notes || '').trim().slice(0, 500) || null;
// Basic email shape check; defer real validation to send-time.
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
return res.status(400).render('public/book-notify-result', {
title: 'Email needed',
installer,
ok: false,
error: 'Please enter a valid email address.'
});
}
if (installer.claim_status === 'unclaimed') {
const ipHash = require('crypto')
.createHash('sha256')
.update((req.headers['x-forwarded-for'] || req.ip || '') + (process.env.SESSION_SECRET || ''))
.digest('hex')
.slice(0, 16);
await db.query(
`INSERT INTO installer_interest (installer_id, email, source, zip, notes, ip_hash)
VALUES ($1, $2, 'book_page', $3, $4, $5)
ON CONFLICT (installer_id, email) DO NOTHING`,
[installer.id, email, zip, notes, ipHash]
);
}
res.render('public/book-notify-result', {
title: 'Got it',
installer,
ok: true,
email
});
} catch (err) { next(err); }
});
// POST /installer/:slug/coi-request — designer-driven Certificate of Insurance
// request (UX backlog #5). Captures structured request, emails the installer
// + their broker (if listed), and confirms to the designer. No PDF generation
// in v0 — the broker issues the cert and emails the designer directly.
router.post('/installer/:slug/coi-request', express.urlencoded({ extended: false }), async (req, res, next) => {
try {
const installer = await db.one(
`SELECT id, slug, business_name, email, insurance, insurance_on_file, claim_status, status
FROM installers WHERE slug = $1 AND (status = 'active' OR claim_status = 'unclaimed')`,
[req.params.slug]
);
if (!installer) return res.status(404).render('public/404', { title: 'Not Found' });
const f = req.body || {};
const trim = (s, max) => String(s || '').trim().slice(0, max);
const designerName = trim(f.designer_name, 120);
const designerEmail = trim(f.designer_email, 200).toLowerCase();
const additional = trim(f.additional_insured_name, 200);
if (!designerName || !designerEmail || !additional) {
return res.status(400).render('public/coi-request-result', {
title: 'Missing fields',
installer, ok: false,
error: 'Designer name, email, and the name on the certificate are required.'
});
}
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(designerEmail)) {
return res.status(400).render('public/coi-request-result', {
title: 'Invalid email',
installer, ok: false,
error: 'Please enter a valid email address.'
});
}
const designerCompany = trim(f.designer_company, 200) || null;
const designerPhone = trim(f.designer_phone, 40) || null;
const projectName = trim(f.project_name, 200) || null;
const projectAddress = trim(f.project_address, 400) || null;
const projectStartDate = /^\d{4}-\d{2}-\d{2}$/.test(f.project_start_date) ? f.project_start_date : null;
const projectValueRaw = String(f.project_value_usd || '').replace(/[^0-9.]/g, '');
// parseFloat('.') / parseFloat('') are NaN; a bare "." survives the
// truthiness check above, so guard explicitly or NaN reaches the NUMERIC
// column and 500s the INSERT.
const projectValueParsed = parseFloat(projectValueRaw);
const projectValueUsd = Number.isFinite(projectValueParsed)
? Math.min(99999999, Math.max(0, projectValueParsed))
: null;
const additionalAddress = trim(f.additional_insured_address, 400) || null;
const notes = trim(f.notes, 800) || null;
const ua = (req.headers['user-agent'] || '').slice(0, 200);
const ip = (req.headers['x-forwarded-for'] || req.ip || '').toString().split(',')[0].trim().slice(0, 64);
const inserted = await db.one(
`INSERT INTO coi_requests (
installer_id, designer_name, designer_company, designer_email, designer_phone,
project_name, project_address, project_start_date, project_value_usd,
additional_insured_name, additional_insured_address, notes,
source_ip, source_user_agent
) VALUES (
$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14
) RETURNING *`,
[installer.id, designerName, designerCompany, designerEmail, designerPhone,
projectName, projectAddress, projectStartDate, projectValueUsd,
additional, additionalAddress, notes, ip, ua]
);
// Fan-out emails — best-effort, non-blocking on failure
const email = require('../lib/email');
const publicUrl = process.env.PUBLIC_URL || `http://localhost:${process.env.PORT || 9765}`;
const ins = installer.insurance || {};
const sendOps = [];
if (installer.email) {
const tpl = email.coiRequestInstaller({ request: inserted, installer, publicUrl });
sendOps.push(email.sendEmail({ to: installer.email, ...tpl })
.then(r => r.ok && db.query(`UPDATE coi_requests SET installer_notified_at=now() WHERE id=$1`, [inserted.id])));
}
if (ins.broker_email && /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(ins.broker_email)) {
const tpl = email.coiRequestBroker({ request: inserted, installer });
sendOps.push(email.sendEmail({ to: ins.broker_email, ...tpl })
.then(r => r.ok && db.query(`UPDATE coi_requests SET broker_notified_at=now() WHERE id=$1`, [inserted.id])));
}
{
const tpl = email.coiRequestDesigner({ request: inserted, installer });
sendOps.push(email.sendEmail({ to: designerEmail, ...tpl }));
}
Promise.allSettled(sendOps).catch(() => {}); // fire-and-forget
res.render('public/coi-request-result', {
title: 'COI request received',
installer,
ok: true,
request: inserted
});
} catch (err) { next(err); }
});
// /bookings/:uuid — booking detail page with PII (customer email, phone,
// address). Access is gated three ways:
// (a) ?t=<HMAC sig> — the link emailed in the booking confirmation
// (b) authenticated installer who owns the booking
// (c) future: a customer login flow, not built yet
//
// Without one of those, return 404 (don't leak existence).
router.get('/bookings/:uuid', async (req, res, next) => {
try {
const booking = await db.one(
`SELECT b.*, i.business_name, i.slug AS installer_slug, i.phone AS installer_phone, i.id AS installer_id_join
FROM bookings b
JOIN installers i ON i.id = b.installer_id
WHERE b.uuid = $1`,
[req.params.uuid]
);
if (!booking) return res.status(404).render('public/404', { title: 'Not Found' });
const tokenOK = bookingToken.verify(booking.uuid, req.query.t || '');
const ownerOK = !!(req.installer && req.installer.id === booking.installer_id_join);
if (!tokenOK && !ownerOK) {
return res.status(404).render('public/404', { title: 'Not Found' });
}
res.render('public/booking', { title: 'Your booking · National Paper Hangers', booking });
} catch (err) { next(err); }
});
// XML sitemap. Lists home + key static pages + every installer slug that's
// either active (claimed) or unclaimed (publicly visible per directory policy).
router.get('/sitemap.xml', async (req, res, next) => {
try {
const rows = await db.many(
`SELECT slug, GREATEST(updated_at, created_at) AS lastmod
FROM installers
WHERE status = 'active' OR claim_status = 'unclaimed'
ORDER BY lastmod DESC`
);
const paperRows = await db.many(
`SELECT slug, GREATEST(updated_at, created_at) AS lastmod
FROM paper_threads
ORDER BY lastmod DESC`
);
const base = (process.env.PUBLIC_URL || 'https://www.nationalpaperhangers.com').replace(/\/+$/, '');
const fmt = d => (d ? new Date(d).toISOString().slice(0, 10) : new Date().toISOString().slice(0, 10));
const urls = [
`<url><loc>${base}/</loc><changefreq>weekly</changefreq><priority>1.0</priority></url>`,
`<url><loc>${base}/find</loc><changefreq>daily</changefreq><priority>0.9</priority></url>`,
`<url><loc>${base}/map</loc><changefreq>weekly</changefreq><priority>0.8</priority></url>`,
`<url><loc>${base}/papers</loc><changefreq>weekly</changefreq><priority>0.8</priority></url>`,
`<url><loc>${base}/watch</loc><changefreq>weekly</changefreq><priority>0.7</priority></url>`,
`<url><loc>${base}/measure</loc><changefreq>monthly</changefreq><priority>0.7</priority></url>`,
`<url><loc>${base}/for-installers</loc><changefreq>monthly</changefreq><priority>0.6</priority></url>`,
`<url><loc>${base}/about</loc><changefreq>monthly</changefreq><priority>0.5</priority></url>`,
...rows.map(r =>
`<url><loc>${base}/installer/${encodeURIComponent(r.slug)}</loc><lastmod>${fmt(r.lastmod)}</lastmod><changefreq>monthly</changefreq><priority>0.8</priority></url>`
),
...paperRows.map(r =>
`<url><loc>${base}/papers/${encodeURIComponent(r.slug)}</loc><lastmod>${fmt(r.lastmod)}</lastmod><changefreq>weekly</changefreq><priority>0.6</priority></url>`
)
].join('\n ');
res.set('Content-Type', 'application/xml; charset=utf-8');
res.send(`<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n ${urls}\n</urlset>\n`);
} catch (err) { next(err); }
});
// Map view: pin every geocoded installer on a Leaflet+OSM map. Pins cluster
// at low zoom; click → popup with mini-card + links to profile and book/visit.
router.get('/map', async (req, res, next) => {
try {
// Address-driven map: the user enters where they are; we show only the
// installers servicing THAT location, sorted by distance. No
// country-wide pin map — that gave noise instead of answers.
const { geocode, distanceMiles } = require('../lib/geocode');
const address = String(req.query.address || '').trim();
const stats = await db.one(
`SELECT
COUNT(*) FILTER (WHERE latitude IS NOT NULL AND longitude IS NOT NULL) AS pinned,
COUNT(*) FILTER (WHERE status='active' OR claim_status='unclaimed') AS total
FROM installers`
);
if (!address) {
// First visit: just show the address-input form. No map yet.
return res.render('public/map', {
title: 'Who services my area? · National Paper Hangers',
metaDescription: `Enter your address and we'll show every wallcovering installer who services your area — sorted by distance.`,
canonicalPath: '/map',
path: '/map',
stats,
address: '',
location: null,
results: [],
geocodeFailed: false
});
}
const loc = await geocode(address);
if (!loc) {
return res.render('public/map', {
title: 'Address not found · National Paper Hangers',
metaDescription: '',
canonicalPath: '/map',
path: '/map',
stats,
address,
location: null,
results: [],
geocodeFailed: true
});
}
// Strip "County" suffix from Nominatim's "Los Angeles County" before
// matching against installer.service_counties — installers tend to enter
// bare county names in admin.
const userCounty = (loc.county || '').replace(/\s+County$/i, '').trim();
const userTown = (loc.town || '').trim();
const userZip = (loc.zip || '').trim();
// Pull every geocoded installer and decide service-area match in JS.
// 524 rows is cheap; once we cross 5k we'll move this filter to SQL using
// ST_DWithin via earthdistance/cube. Don't pre-optimise.
const rows = await db.many(
`SELECT i.id, i.slug, i.business_name, i.headline, i.city, i.state, i.zip,
i.latitude::float8 AS lat, i.longitude::float8 AS lng,
i.service_radius_miles, i.service_counties, i.service_towns, i.service_zips,
i.tier, i.verified, i.claim_status, i.response_time_hours,
i.market_segments, i.materials,
COALESCE((
SELECT COUNT(*) FROM installer_credentials c
WHERE c.installer_id = i.id AND c.ops_verified = true
), 0)::int AS verified_brands_count
FROM installers i
WHERE i.latitude IS NOT NULL AND i.longitude IS NOT NULL
AND (i.status='active' OR i.claim_status='unclaimed')`
);
const matches = [];
for (const r of rows) {
const d = distanceMiles(loc.lat, loc.lng, r.lat, r.lng);
const radiusHit = d <= (r.service_radius_miles || 50);
const countyHit = userCounty && (r.service_counties || []).some(c =>
c.toLowerCase().replace(/\s+county$/i, '').trim() === userCounty.toLowerCase());
const townHit = userTown && (r.service_towns || []).some(t =>
t.toLowerCase().trim() === userTown.toLowerCase());
const zipHit = userZip && (r.service_zips || []).some(z => z.trim() === userZip);
if (radiusHit || countyHit || townHit || zipHit) {
matches.push({
...r,
distance_miles: Math.round(d * 10) / 10,
match_reasons: [
radiusHit && `within ${r.service_radius_miles} mi`,
countyHit && `services ${userCounty}`,
townHit && `services ${userTown}`,
zipHit && `services ${userZip}`,
].filter(Boolean)
});
}
}
matches.sort((a, b) => {
// Tier-paid first within same distance bucket
const tierOrder = { enterprise: 0, signature: 1, pro: 2 };
const at = tierOrder[a.tier] ?? 3;
const bt = tierOrder[b.tier] ?? 3;
if (Math.abs(a.distance_miles - b.distance_miles) > 5) return a.distance_miles - b.distance_miles;
return at - bt || a.distance_miles - b.distance_miles;
});
res.render('public/map', {
title: `Installers near ${loc.display_name.split(',').slice(0,2).join(',')} · National Paper Hangers`,
metaDescription: `${matches.length} wallcovering installer${matches.length === 1 ? '' : 's'} service ${loc.display_name.split(',').slice(0,2).join(',')}. Sorted by distance, with response time + tier signals.`,
canonicalPath: '/map',
path: '/map',
stats,
address,
location: loc,
results: matches,
geocodeFailed: false
});
} catch (err) { next(err); }
});
// JSON API for the on-page map widget. Caller passes lat+lng (already
// geocoded by the form post), and we return the filtered + sorted servicing
// installers. Cached 60s per coord pair.
router.get('/api/near-me', async (req, res, next) => {
try {
const { distanceMiles } = require('../lib/geocode');
const lat = parseFloat(req.query.lat);
const lng = parseFloat(req.query.lng);
const county = String(req.query.county || '').replace(/\s+County$/i, '').trim().toLowerCase();
const town = String(req.query.town || '').trim().toLowerCase();
const zip = String(req.query.zip || '').trim();
const hasCoords = Number.isFinite(lat) && Number.isFinite(lng);
// Accept either coordinates (radius search) OR a county/town/zip filter —
// the match logic below supports all four. Reject only when none are given.
if (!hasCoords && !county && !town && !zip) {
return res.status(400).json({ error: 'lat/lng or county/town/zip required' });
}
const rows = await db.many(
`SELECT i.id, i.slug, i.business_name, i.city, i.state,
i.latitude::float8 AS lat, i.longitude::float8 AS lng,
i.service_radius_miles, i.service_counties, i.service_towns, i.service_zips,
i.tier, i.verified, i.claim_status, i.response_time_hours
FROM installers i
WHERE i.latitude IS NOT NULL AND i.longitude IS NOT NULL
AND (i.status='active' OR i.claim_status='unclaimed')`
);
const matches = [];
for (const r of rows) {
const d = hasCoords ? distanceMiles(lat, lng, r.lat, r.lng) : null;
const radiusHit = d !== null && d <= (r.service_radius_miles || 50);
const countyHit = county && (r.service_counties || []).some(c =>
c.toLowerCase().replace(/\s+county$/i, '').trim() === county);
const townHit = town && (r.service_towns || []).some(t => t.toLowerCase().trim() === town);
const zipHit = zip && (r.service_zips || []).some(z => z.trim() === zip);
if (radiusHit || countyHit || townHit || zipHit) {
matches.push({
id: r.id, slug: r.slug, business_name: r.business_name,
city: r.city, state: r.state,
lat: r.lat, lng: r.lng,
tier: r.tier, verified: r.verified, claim_status: r.claim_status,
response_time_hours: r.response_time_hours,
distance_miles: d === null ? null : Math.round(d * 10) / 10
});
}
}
// Sort by distance when coordinates were supplied; nulls (filter-only
// matches) sort last.
matches.sort((a, b) => {
if (a.distance_miles === null) return b.distance_miles === null ? 0 : 1;
if (b.distance_miles === null) return -1;
return a.distance_miles - b.distance_miles;
});
res.set('cache-control', 'public, max-age=60');
res.json({
count: matches.length,
user: hasCoords ? { lat, lng } : { county: county || null, town: town || null, zip: zip || null },
installers: matches
});
} catch (err) { next(err); }
});
// Lightweight JSON for the map. Only public, non-PII fields. Cached 60s
// at the edge — pins barely change between requests.
router.get('/api/installers.geo', async (req, res, next) => {
try {
const rows = await db.many(
`SELECT i.id, i.slug, i.business_name, i.city, i.state,
i.latitude::float8 AS lat, i.longitude::float8 AS lng,
i.tier, i.verified, i.claim_status,
i.market_segments, i.materials,
COALESCE((
SELECT COUNT(*) FROM installer_credentials c
WHERE c.installer_id = i.id AND c.ops_verified = true
), 0)::int AS verified_brands_count,
COALESCE((
-- LIMIT must be inside a sub-SELECT (see /find above) —
-- placed next to array_agg it's a no-op.
SELECT array_agg(b.brand)
FROM (
SELECT c.brand
FROM installer_credentials c
WHERE c.installer_id = i.id AND c.ops_verified = true
ORDER BY c.display_order, c.year_issued DESC NULLS LAST
LIMIT 2
) b
), ARRAY[]::text[]) AS verified_brands
FROM installers i
WHERE i.latitude IS NOT NULL AND i.longitude IS NOT NULL
AND (i.status='active' OR i.claim_status='unclaimed')
ORDER BY i.tier DESC, i.verified DESC, i.id`
);
const segmentImage = require('../lib/segment-image');
for (const r of rows) {
const img = segmentImage.pickSegmentImage(r);
r.thumb = img ? img.file : null;
}
res.set('cache-control', 'public, max-age=60');
res.json({ count: rows.length, installers: rows });
} catch (err) { next(err); }
});
// Healthcheck. Used by nginx upstream + uptime monitors after Kamatera deploy.
// Returns 200 + JSON status when DB ping succeeds, 503 otherwise. No locals
// needed (no template render) so it stays cheap on every poll.
router.get('/healthz', async (req, res) => {
try {
await db.query('SELECT 1');
res.set('cache-control', 'no-store').json({ ok: true, ts: Date.now() });
} catch (err) {
res.status(503).set('cache-control', 'no-store').json({ ok: false, error: 'db_unreachable' });
}
});
// Curated /watch — verified YouTube videos about professional wallcovering
// installation. Hand-picked by NPH ops; per-installer social handles are a
// SEPARATE feature gated on social_verified.
router.get('/watch', async (req, res, next) => {
try {
const cat = (req.query.category || '').trim();
const allowed = new Set(['hand_painted','silk','grasscloth','mural','metallic_leaf','vinyl','technique','historical','brand_showcase','installer_at_work']);
const where = ['is_published = true'];
const params = [];
if (cat && allowed.has(cat)) {
params.push(cat);
where.push(`category = $${params.length}`);
}
const videos = await db.many(
`SELECT id, youtube_id, platform, title, channel_name, channel_url, category,
materials, brands, description, duration_seconds, source_url, license_note
FROM curated_videos
WHERE ${where.join(' AND ')}
ORDER BY display_order, added_at DESC
LIMIT 200`,
params
);
// Resolve each row to an embeddable iframe (YouTube nocookie or LinkedIn).
const socialEmbed = require('../lib/social-embed');
for (const v of videos) {
const e = socialEmbed.embedFromRow(v);
v.embed_html = e ? e.html : null;
}
const counts = await db.many(
`SELECT category, COUNT(*)::int AS n FROM curated_videos WHERE is_published = true GROUP BY category ORDER BY n DESC`
);
const industryLinks = await db.many(
`SELECT id, platform, name, url, blurb, category
FROM industry_links
WHERE is_published = true
ORDER BY display_order, added_at DESC
LIMIT 60`
);
res.render('public/watch', {
title: 'Watch · Professional wallcovering installation videos · National Paper Hangers',
metaDescription: `${videos.length}+ hand-picked installation videos from manufacturer brand channels and craft educators. Curated by NPH ops — every entry reviewed before publish.`,
canonicalPath: '/watch',
videos, counts, currentCategory: cat, industryLinks
});
} catch (err) { next(err); }
});
router.get('/about', (req, res) => {
res.render('public/about', { title: 'About · National Paper Hangers' });
});
router.get('/for-installers', (req, res) => {
res.render('public/for-installers', { title: 'For installers · National Paper Hangers' });
});
// AR Wall Measurement Kit — phone-sensor wall measure + roll/panel calculator
// for designers & DIYers. Reuses the /book RoomMeasure engine; no server data.
//
// P3 order-handoff: when launched from a DW / wallco product page, the link
// carries product context (?sku=&name=&repeat=&format=&panelw=&shop=&from=).
// We sanitize it, pre-fill the calculator, and point "Shop the look" back at
// the exact PDP. The return URL is host-allowlisted so the page can never be
// turned into an open redirect.
const MEASURE_SHOP_HOSTS = [
'designerwallcoverings.com',
'wallco.ai',
'philipperomano.com',
'novasuede.com'
];
const MEASURE_FORMATS = ['us_standard', 'euro', 'panels'];
// Accept only http(s) URLs whose hostname IS or ends with an allowlisted
// brand domain. Anything else (javascript:, other hosts, garbage) → null.
function sanitizeShopUrl(raw) {
if (!raw || typeof raw !== 'string' || raw.length > 2048) return null;
let u;
try { u = new URL(raw); } catch (_) { return null; }
if (u.protocol !== 'http:' && u.protocol !== 'https:') return null;
const host = u.hostname.toLowerCase();
const ok = MEASURE_SHOP_HOSTS.some(d => host === d || host.endsWith('.' + d));
return ok ? u.toString() : null;
}
function clampNum(raw, min, max) {
const n = parseFloat(raw);
if (!isFinite(n)) return null;
return Math.min(max, Math.max(min, n));
}
router.get('/measure', (req, res) => {
const q = req.query || {};
const shop = sanitizeShopUrl(q.shop);
// String fields are echoed via EJS <%= %> (HTML-escaped) and serialized into
// a JSON <script> block (not executable), so no further escaping is needed
// here — just bound the length to keep the page sane.
const str = (v) => (typeof v === 'string' && v.trim()) ? v.trim().slice(0, 120) : null;
const product = {
sku: str(q.sku),
name: str(q.name) || str(q.product),
from: str(q.from),
shop,
repeat: clampNum(q.repeat, 0, 120),
panelw: clampNum(q.panelw, 6, 60),
format: MEASURE_FORMATS.includes(q.format) ? q.format : null
};
const hasProduct = !!(product.sku || product.name || product.shop ||
product.repeat != null || product.format);
const titleSuffix = product.name ? ` for ${product.name}` : '';
res.render('public/measure', {
title: `Measure your walls${titleSuffix} — AR Wall Measurement Kit · National Paper Hangers`,
metaDescription: 'Measure any wall with your phone — no tape measure — then get an instant rolls-or-panels estimate for your wallpaper project. Free tool from National Paper Hangers.',
product: hasProduct ? product : null
});
});
router.get('/privacy', (req, res) => {
res.render('public/legal', { title: 'Privacy · National Paper Hangers', kind: 'privacy' });
});
router.get('/terms', (req, res) => {
res.render('public/legal', { title: 'Terms · National Paper Hangers', kind: 'terms' });
});
module.exports = router;