← back to NationalPaperHangers
routes/admin.js
957 lines
const express = require('express');
const path = require('path');
const fs = require('fs');
const crypto = require('crypto');
const multer = require('multer');
const rateLimit = require('express-rate-limit');
const db = require('../lib/db');
const { requireInstaller, requirePaidTier } = require('../lib/auth');
const stripe = require('../lib/stripe');
const marketplace = require('../lib/services/marketplace');
const mediaSniff = require('../lib/media-sniff');
const router = express.Router();
router.use(requireInstaller);
// ====== Image uploads (template hero + portfolio drag-drop) =====
// Files land at public/uploads/{installer_id}/{hash}.{ext} so they're
// served straight from express.static. Filenames are content-hashed to
// dedupe and to avoid leaking original filenames.
const UPLOAD_ROOT = path.join(__dirname, '..', 'public', 'uploads');
const ALLOWED_MIME = new Set(['image/jpeg','image/png','image/webp','image/avif']);
const EXT_BY_MIME = { 'image/jpeg':'.jpg','image/png':'.png','image/webp':'.webp','image/avif':'.avif' };
const upload = multer({
storage: multer.memoryStorage(),
limits: { fileSize: 8 * 1024 * 1024, files: 1 },
fileFilter: (req, file, cb) => {
if (!ALLOWED_MIME.has(file.mimetype)) return cb(new Error('UNSUPPORTED_TYPE'));
cb(null, true);
}
});
// Coarse ops gate — until Phase 2 of the architecture refactor lands real
// role-based middleware, ops privileges are granted via the env-var
// allowlist NPH_PLATFORM_ADMIN_INSTALLER_IDS (comma-separated installer IDs).
// Steve's own installer ID goes in there. Anyone else hitting /admin/ops/* gets a 404
// (not 403, to avoid leaking the existence of the surface).
function requireOpsInstaller(req, res, next) {
const allowList = String(process.env.NPH_PLATFORM_ADMIN_INSTALLER_IDS || '')
.split(',').map(s => parseInt(s.trim(), 10)).filter(Number.isFinite);
if (!req.installer || !allowList.includes(req.installer.id)) {
return res.status(404).render('public/404', { title: 'Not Found' });
}
next();
}
router.get('/', async (req, res, next) => {
try {
const upcoming = await db.many(
`SELECT * FROM bookings
WHERE installer_id = $1
AND status IN ('pending','confirmed')
AND scheduled_end >= now()
ORDER BY scheduled_start ASC
LIMIT 6`,
[req.installer.id]
);
const stats = await db.one(
`SELECT
COUNT(*) FILTER (WHERE status='pending') AS pending_count,
COUNT(*) FILTER (WHERE status='confirmed' AND scheduled_end >= now()) AS upcoming_count,
COUNT(*) FILTER (WHERE status='completed') AS completed_count
FROM bookings WHERE installer_id = $1`,
[req.installer.id]
);
const portfolioCount = await db.one('SELECT COUNT(*)::int AS c FROM installer_portfolio WHERE installer_id = $1', [req.installer.id]);
const availabilityCount = await db.one('SELECT COUNT(*)::int AS c FROM installer_availability WHERE installer_id = $1 AND active = true', [req.installer.id]);
const market = await marketplace.getInstallerMarketplaceTotals(req.installer.id);
res.render('admin/dashboard', {
title: 'Dashboard · National Paper Hangers',
upcoming, stats, portfolioCount: portfolioCount.c,
hasAvailability: availabilityCount.c > 0,
welcome: req.query.welcome === '1',
market,
connectFlash: req.query.connect || (req.query.mock === 'connect' ? 'mock' : null)
});
} catch (err) { next(err); }
});
router.get('/calendar', requirePaidTier, async (req, res, next) => {
try {
const availability = await db.many(
`SELECT * FROM installer_availability WHERE installer_id = $1 ORDER BY day_of_week, start_time`,
[req.installer.id]
);
res.render('admin/calendar', {
title: 'Calendar · National Paper Hangers',
availability
});
} catch (err) { next(err); }
});
router.get('/bookings/:id(\\d+)', async (req, res, next) => {
try {
const booking = await db.one(
`SELECT * FROM bookings WHERE id = $1 AND installer_id = $2`,
[req.params.id, req.installer.id]
);
if (!booking) return res.status(404).render('public/404', { title: 'Not Found' });
res.render('admin/booking-detail', { title: `Booking · ${booking.customer_name}`, booking });
} catch (err) { next(err); }
});
router.get('/bookings', async (req, res, next) => {
try {
const filter = req.query.status || 'all';
let where = 'installer_id = $1';
const params = [req.installer.id];
if (filter === 'upcoming') where += ' AND status IN (\'pending\',\'confirmed\') AND scheduled_end >= now()';
if (filter === 'past') where += ' AND (status IN (\'completed\',\'no_show\') OR scheduled_end < now())';
if (filter === 'pending') where += ' AND status = \'pending\'';
if (filter === 'canceled') where += ' AND status IN (\'canceled\',\'declined\')';
const bookings = await db.many(
`SELECT * FROM bookings WHERE ${where} ORDER BY scheduled_start DESC LIMIT 200`,
params
);
const market = await marketplace.getInstallerMarketplaceTotals(req.installer.id);
res.render('admin/bookings', {
title: 'Bookings · National Paper Hangers',
bookings, filter, market
});
} catch (err) { next(err); }
});
// Booking lifecycle transitions. Each UPDATE guards on the valid prior
// states in its WHERE clause, so a terminal booking (completed / canceled /
// declined / no_show) can't be revived by a stale tab, a back-button
// re-POST, or a double-submit. A 0-row result means the transition no
// longer applies — flashed back to the installer instead of a silent no-op.
router.post('/bookings/:id/confirm', async (req, res, next) => {
try {
const r = await db.query(
`UPDATE bookings SET status='confirmed', confirmed_at=now()
WHERE id=$1 AND installer_id=$2 AND status='pending'`,
[req.params.id, req.installer.id]
);
req.session.flash = r.rowCount
? { ok: 'Booking confirmed.' }
: { error: 'That booking can no longer be confirmed.' };
res.redirect('/admin/bookings');
} catch (err) { next(err); }
});
router.post('/bookings/:id/decline', async (req, res, next) => {
try {
const r = await db.query(
`UPDATE bookings SET status='declined', canceled_at=now(), cancel_reason=$3
WHERE id=$1 AND installer_id=$2 AND status IN ('pending','confirmed')`,
[req.params.id, req.installer.id, req.body.reason || null]
);
req.session.flash = r.rowCount
? { ok: 'Booking declined.' }
: { error: 'That booking can no longer be declined.' };
res.redirect('/admin/bookings');
} catch (err) { next(err); }
});
router.post('/bookings/:id/complete', async (req, res, next) => {
try {
const r = await db.query(
`UPDATE bookings SET status='completed', completed_at=now()
WHERE id=$1 AND installer_id=$2 AND status IN ('pending','confirmed')`,
[req.params.id, req.installer.id]
);
req.session.flash = r.rowCount
? { ok: 'Booking marked complete.' }
: { error: 'That booking can no longer be marked complete.' };
res.redirect('/admin/bookings');
} catch (err) { next(err); }
});
router.get('/profile', async (req, res, next) => {
try {
const credentials = await db.many(
`SELECT id, brand, credential_type, year_issued, year_expires, certificate_url,
notes, ops_verified, ops_verified_at, display_order
FROM installer_credentials
WHERE installer_id = $1
ORDER BY display_order, year_issued DESC NULLS LAST, id`,
[req.installer.id]
);
res.render('admin/profile', {
title: 'Profile · National Paper Hangers',
credentials
});
} catch (err) { next(err); }
});
const ALLOWED_CREDENTIAL_TYPES = new Set([
'brand_trained','brand_certified','brand_approved','manufacturer_partner','trade_member'
]);
router.post('/credentials', async (req, res, next) => {
try {
const f = req.body || {};
const brand = clampLen((f.brand || '').trim(), 100);
if (!brand) {
req.session.flash = { error: 'Brand name is required for a credential.' };
return res.redirect('/admin/profile');
}
const credentialType = ALLOWED_CREDENTIAL_TYPES.has(f.credential_type) ? f.credential_type : 'brand_trained';
const yearIssued = f.year_issued && /^\d{4}$/.test(f.year_issued)
? Math.max(1900, Math.min(2100, parseInt(f.year_issued, 10))) : null;
const yearExpires = f.year_expires && /^\d{4}$/.test(f.year_expires)
? Math.max(1900, Math.min(2200, parseInt(f.year_expires, 10))) : null;
const certUrl = sanitizeWebsite(f.certificate_url);
if (f.certificate_url && !certUrl) {
req.session.flash = { error: 'Certificate URL must be a valid http(s) link.' };
return res.redirect('/admin/profile');
}
const notes = f.notes ? clampLen(f.notes, 400) : null;
await db.query(
`INSERT INTO installer_credentials
(installer_id, brand, credential_type, year_issued, year_expires, certificate_url, notes)
VALUES ($1, $2, $3, $4, $5, $6, $7)`,
[req.installer.id, brand, credentialType, yearIssued, yearExpires, certUrl, notes]
);
req.session.flash = { ok: 'Credential added — pending ops review.' };
res.redirect('/admin/profile');
} catch (err) { next(err); }
});
router.post('/credentials/:id(\\d+)/delete', async (req, res, next) => {
try {
await db.query(
`DELETE FROM installer_credentials WHERE id = $1 AND installer_id = $2`,
[req.params.id, req.installer.id]
);
req.session.flash = { ok: 'Credential removed.' };
res.redirect('/admin/profile');
} catch (err) { next(err); }
});
// ─── Insurance metadata + COI request inbox (UX backlog #5) ──────────────
router.get('/insurance', async (req, res, next) => {
try {
res.render('admin/insurance', {
title: 'Insurance · National Paper Hangers',
ins: req.installer.insurance || {}
});
} catch (err) { next(err); }
});
router.post('/insurance', async (req, res, next) => {
try {
const f = req.body || {};
const clamp = (s, max) => String(s || '').trim().slice(0, max);
const intish = (v, max) => {
const n = parseInt(String(v || '').replace(/[^0-9]/g, ''), 10);
return Number.isFinite(n) ? Math.min(max, Math.max(0, n)) : null;
};
const ins = {
carrier: clamp(f.carrier, 200) || null,
policy_number: clamp(f.policy_number, 200) || null,
limits: {
general_aggregate_usd: intish(f.general_aggregate_usd, 99999999),
per_occurrence_usd: intish(f.per_occurrence_usd, 99999999)
},
expiry: /^\d{4}-\d{2}-\d{2}$/.test(f.expiry) ? f.expiry : null,
broker_name: clamp(f.broker_name, 200) || null,
broker_email: clamp(f.broker_email, 200).toLowerCase() || null,
broker_phone: clamp(f.broker_phone, 40) || null,
auto_attach_to_email: f.auto_attach_to_email === 'on' || f.auto_attach_to_email === 'true'
};
if (ins.broker_email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(ins.broker_email)) {
req.session.flash = { error: 'Broker email is not a valid address.' };
return res.redirect('/admin/insurance');
}
// Hard-strip nulled limits subobject if both are null — keeps JSONB clean
if (ins.limits.general_aggregate_usd === null && ins.limits.per_occurrence_usd === null) {
delete ins.limits;
}
const expiryDate = ins.expiry || null;
const onFile = !!(ins.carrier && ins.policy_number);
await db.query(
`UPDATE installers
SET insurance = $2,
insurance_on_file = $3,
insurance_expires = $4
WHERE id = $1`,
[req.installer.id, ins, onFile, expiryDate]
);
req.session.flash = { ok: 'Insurance details saved.' };
res.redirect('/admin/insurance');
} catch (err) { next(err); }
});
router.get('/coi-requests', async (req, res, next) => {
try {
const rows = await db.many(
`SELECT id, designer_name, designer_company, designer_email,
project_name, project_address, project_start_date, project_value_usd,
additional_insured_name, additional_insured_address, notes,
status, installer_notified_at, broker_notified_at, fulfilled_at,
fulfilled_pdf_url, decline_reason, created_at
FROM coi_requests
WHERE installer_id = $1
ORDER BY created_at DESC
LIMIT 200`,
[req.installer.id]
);
res.render('admin/coi-requests', {
title: 'COI requests · National Paper Hangers',
requests: rows
});
} catch (err) { next(err); }
});
router.post('/coi-requests/:id(\\d+)/status', async (req, res, next) => {
try {
const allowed = new Set(['pending','acknowledged','fulfilled','declined']);
const newStatus = allowed.has(req.body.status) ? req.body.status : null;
if (!newStatus) {
req.session.flash = { error: 'Invalid status.' };
return res.redirect('/admin/coi-requests');
}
const declineReason = newStatus === 'declined'
? String(req.body.decline_reason || '').trim().slice(0, 500) || null
: null;
const fulfilledPdfUrl = newStatus === 'fulfilled' && /^https?:\/\//.test(req.body.fulfilled_pdf_url || '')
? String(req.body.fulfilled_pdf_url).trim().slice(0, 500)
: null;
await db.query(
`UPDATE coi_requests
SET status = $2,
decline_reason = $3,
fulfilled_pdf_url = COALESCE($4, fulfilled_pdf_url),
fulfilled_at = CASE WHEN $2 = 'fulfilled' THEN now() ELSE fulfilled_at END,
updated_at = now()
WHERE id = $1 AND installer_id = $5`,
[req.params.id, newStatus, declineReason, fulfilledPdfUrl, req.installer.id]
);
req.session.flash = { ok: `Request marked ${newStatus}.` };
res.redirect('/admin/coi-requests');
} catch (err) { next(err); }
});
function sanitizeWebsite(input) {
// Returns a safe http(s) URL string, or null. Strips javascript:/data:/etc.
// Length-capped to 500 to bound DB write size.
const v = String(input || '').trim();
if (!v) return null;
if (v.length > 500) return null;
let u;
try { u = new URL(v); } catch { return null; }
if (u.protocol !== 'http:' && u.protocol !== 'https:') return null;
return u.toString();
}
function clampLen(v, max) {
const s = String(v == null ? '' : v);
return s.length > max ? s.slice(0, max) : s;
}
router.post('/profile', async (req, res, next) => {
try {
const f = req.body;
const arr = (s) => (s || '').split(',').map(x => x.trim()).filter(Boolean).slice(0, 50);
const website = sanitizeWebsite(f.website);
if (f.website && !website) {
req.session.flash = { error: 'Website must be a valid http(s) URL' };
return res.redirect('/admin/profile');
}
// Equipment Fleet — structured trade-buyer disclosure (UX idea #4).
// Build a JSONB blob from form fields; null any blanks so we don't
// store empty-string noise.
const ALLOWED_LIFT = new Set(['extension_ladder', 'scaffold', 'scissor_lift', 'boom_lift']);
const ALLOWED_TABLE = new Set(['none', 'folding', 'dedicated_60', 'dedicated_72_plus']);
const ALLOWED_VEHICLE = new Set(['van', 'box_truck', 'trailer']);
const equipment = {};
if (f.eq_max_reach_ft) {
const n = parseInt(f.eq_max_reach_ft, 10);
if (Number.isFinite(n) && n > 0 && n < 200) equipment.max_reach_ft = n;
}
if (f.eq_lift_type && ALLOWED_LIFT.has(f.eq_lift_type)) equipment.lift_type = f.eq_lift_type;
if (f.eq_paper_table && ALLOWED_TABLE.has(f.eq_paper_table)) equipment.paper_table = f.eq_paper_table;
if (f.eq_vehicle && ALLOWED_VEHICLE.has(f.eq_vehicle)) equipment.vehicle = f.eq_vehicle;
if (f.eq_dust_extraction === 'on') equipment.dust_extraction = true;
if (f.eq_notes) equipment.notes = clampLen(f.eq_notes, 400);
const equipmentJson = Object.keys(equipment).length ? JSON.stringify(equipment) : null;
// Service-area lists: counties/towns/zips comma-separated; cap each entry
// length so a runaway paste doesn't bloat the row. Cap total count too.
const serviceArr = (s, max, capLen) =>
String(s || '').split(',').map(x => clampLen(x.trim(), capLen)).filter(Boolean).slice(0, max);
await db.query(
`UPDATE installers SET
business_name = $2, contact_name = $3, phone = $4, headline = $5, bio = $6,
city = $7, state = $8, zip = $9, service_radius_miles = $10, travel_available = $11,
team_size = $12, founded_year = $13, website = $14,
market_segments = $15, materials = $16, brands_handled = $17, accreditations = $18,
response_time_hours = $19, equipment = $20,
service_counties = $21, service_towns = $22, service_zips = $23,
profile_complete = (LENGTH(COALESCE($6,'')) > 40 AND LENGTH(COALESCE($5,'')) > 0)
WHERE id = $1`,
[
req.installer.id,
clampLen(f.business_name, 200),
clampLen(f.contact_name, 120),
clampLen(f.phone, 40),
clampLen(f.headline, 200),
clampLen(f.bio, 4000),
clampLen(f.city, 80),
clampLen((f.state || '').toUpperCase(), 2),
clampLen(f.zip, 20),
parseInt(f.service_radius_miles || '50', 10),
f.travel_available === 'on',
f.team_size ? parseInt(f.team_size, 10) : null,
f.founded_year ? parseInt(f.founded_year, 10) : null,
website,
arr(f.market_segments), arr(f.materials), arr(f.brands_handled), arr(f.accreditations),
parseInt(f.response_time_hours || '24', 10),
equipmentJson,
serviceArr(f.service_counties, 60, 80),
serviceArr(f.service_towns, 80, 80),
serviceArr(f.service_zips, 100, 10)
]
);
req.session.flash = { ok: 'Profile saved' };
res.redirect('/admin/profile');
} catch (err) { next(err); }
});
// Comma-separated installer IDs that get the platform-wide marketplace
// roll-up on /admin/billing. Until a real role system lands, this is the
// kill-switch for "NPH staff sees totals across every studio". Empty = nobody.
function isPlatformAdmin(installerId) {
const list = (process.env.NPH_PLATFORM_ADMIN_INSTALLER_IDS || '')
.split(',').map(s => s.trim()).filter(Boolean);
return list.includes(String(installerId));
}
router.get('/billing', async (req, res, next) => {
try {
const installerMarket = await marketplace.getInstallerMarketplaceTotals(req.installer.id);
const platformAdmin = isPlatformAdmin(req.installer.id);
const platformMarket = platformAdmin
? await marketplace.getPlatformMarketplaceTotals()
: null;
res.render('admin/billing', {
title: 'Billing · National Paper Hangers',
stripeLive: stripe.isLive(),
priceTable: stripe.PRICE_TABLE,
upgradePrompt: req.query.upgrade === '1',
installerMarket,
platformMarket,
defaultDepositCents: stripe.DEFAULT_DEPOSIT_CENTS,
defaultPlatformFeeBps: stripe.DEFAULT_PLATFORM_FEE_BPS
});
} catch (err) { next(err); }
});
// Only these tiers are purchasable. 'basic' is the free default and is never
// a checkout target. Without this guard the mocked checkout path writes the
// raw body `tier` straight to installers.tier — a logged-in installer could
// POST tier=enterprise and self-upgrade for free in mock mode.
const PURCHASABLE_TIERS = new Set(['pro', 'signature', 'enterprise']);
const VALID_CADENCES = new Set(['month', 'year']);
router.post('/billing/checkout', async (req, res, next) => {
try {
const tier = String(req.body.tier || '').toLowerCase();
const cadence = String(req.body.cadence || 'month').toLowerCase();
if (!PURCHASABLE_TIERS.has(tier) || !VALID_CADENCES.has(cadence)) {
req.session.flash = { error: 'Pick a valid plan to continue.' };
return res.redirect('/admin/billing');
}
const result = await stripe.createCheckoutSession({
installer: req.installer,
tier, cadence,
successUrl: `${process.env.PUBLIC_URL || ''}/admin/billing?ok=1`,
cancelUrl: `${process.env.PUBLIC_URL || ''}/admin/billing?canceled=1`
});
if (result.mocked) {
// No real Stripe — toggle subscription locally so UI works end-to-end
await db.query(
`UPDATE installers SET tier=$2, subscription_status='active' WHERE id=$1`,
[req.installer.id, tier]
);
}
res.redirect(result.url);
} catch (err) { next(err); }
});
router.post('/billing/portal', async (req, res, next) => {
try {
const result = await stripe.createPortalSession({
installer: req.installer,
returnUrl: `${process.env.PUBLIC_URL || ''}/admin/billing`
});
res.redirect(result.url);
} catch (err) { next(err); }
});
// ---------- Stripe Connect Express onboarding ----------
// Idempotent: creates a Connect account if missing, then mints a fresh
// account_link and redirects to Stripe-hosted onboarding. Mocked path
// returns to /admin?mock=connect when STRIPE_SECRET_KEY isn't set.
async function mintConnectOnboardLink(installer) {
const baseUrl = process.env.PUBLIC_URL || `http://localhost:${process.env.PORT || 9765}`;
const returnUrl = `${baseUrl}/admin/connect/return`;
const refreshUrl = `${baseUrl}/admin/connect/refresh`;
let accountId = installer.stripe_account_id;
if (!accountId) {
const created = await stripe.createConnectAccount({ installer });
accountId = created.account_id;
await db.query(
`UPDATE installers
SET stripe_account_id = $1,
stripe_account_charges_enabled = $2,
stripe_account_payouts_enabled = $3
WHERE id = $4`,
[accountId, !!created.charges_enabled, !!created.payouts_enabled, installer.id]
);
}
return stripe.createConnectAccountLink({ accountId, returnUrl, refreshUrl });
}
router.post('/connect/onboard', async (req, res, next) => {
try {
const link = await mintConnectOnboardLink(req.installer);
res.redirect(link.url);
} catch (err) { next(err); }
});
router.get('/connect/return', async (req, res, next) => {
try {
const installer = req.installer;
if (installer.stripe_account_id) {
const status = await stripe.getConnectAccount({ accountId: installer.stripe_account_id });
await db.query(
`UPDATE installers
SET stripe_account_charges_enabled = $1,
stripe_account_payouts_enabled = $2,
stripe_account_onboarded_at = CASE
WHEN $1 = true AND stripe_account_onboarded_at IS NULL THEN now()
ELSE stripe_account_onboarded_at
END
WHERE id = $3`,
[!!status.charges_enabled, !!status.payouts_enabled, installer.id]
);
}
res.redirect('/admin?connect=ok');
} catch (err) { next(err); }
});
router.get('/connect/refresh', async (req, res, next) => {
// Stripe redirects here via GET when an onboarding link expires. Mint a
// fresh link and bounce. Idempotent + no destructive state.
try {
const link = await mintConnectOnboardLink(req.installer);
res.redirect(link.url);
} catch (err) { next(err); }
});
// =======================================================================
// OPS — credential review queue (gated by NPH_PLATFORM_ADMIN_INSTALLER_IDS)
// =======================================================================
//
// Studios self-add Brand-Trained credentials in /admin/profile. Until ops
// flips ops_verified=true, the credential stays out of the public sidebar
// (prevents spam — anyone could claim "de Gournay-trained"). This queue
// is where Steve reviews submissions and approves/rejects.
router.get('/ops/credentials', requireOpsInstaller, async (req, res, next) => {
try {
const pending = await db.many(
`SELECT c.id, c.installer_id, c.brand, c.credential_type,
c.year_issued, c.year_expires, c.certificate_url, c.notes,
c.created_at,
i.business_name, i.slug, i.city, i.state, i.website
FROM installer_credentials c
JOIN installers i ON i.id = c.installer_id
WHERE c.ops_verified = false
ORDER BY c.created_at DESC
LIMIT 200`
);
const verifiedCount = await db.one(
`SELECT COUNT(*)::int AS c FROM installer_credentials WHERE ops_verified = true`
);
res.render('admin/ops-credentials', {
title: 'Ops · Credential review · National Paper Hangers',
pending,
verifiedCount: verifiedCount.c
});
} catch (err) { next(err); }
});
router.post('/ops/credentials/:id(\\d+)/verify', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(
`UPDATE installer_credentials
SET ops_verified = true,
ops_verified_at = now(),
ops_verified_by = $2
WHERE id = $1`,
[req.params.id, req.installer.email || ('installer:' + req.installer.id)]
);
req.session.flash = { ok: 'Credential verified — now visible on the studio profile.' };
res.redirect('/admin/ops/credentials');
} catch (err) { next(err); }
});
router.post('/ops/credentials/:id(\\d+)/reject', requireOpsInstaller, async (req, res, next) => {
try {
// Reject = delete. Studio can re-submit with corrected info. We don't
// persist a "rejected" state to keep the schema lean.
await db.query(`DELETE FROM installer_credentials WHERE id = $1`, [req.params.id]);
req.session.flash = { ok: 'Credential rejected and removed.' };
res.redirect('/admin/ops/credentials');
} catch (err) { next(err); }
});
// ===================================================================
// OPS — curated /watch video queue
// ===================================================================
//
// /watch is editorial — every video listed must be reviewed by ops before
// publish. Ops pastes a YouTube URL, the parser extracts the ID, and the
// row is added is_published=false until ops flips it. No automatic ingest.
const ALLOWED_WATCH_CATEGORIES = new Set([
'hand_painted','silk','grasscloth','mural','metallic_leaf','vinyl',
'technique','historical','brand_showcase','installer_at_work'
]);
// Returns {platform, id} or null. Accepts YouTube + LinkedIn URLs/IDs.
function parseVideoIdForOps(input) {
const v = String(input || '').trim();
if (!v) return null;
// Bare YouTube-shaped ID (also matches LI numeric URNs but those are 15+ digits)
if (/^[A-Za-z0-9_-]{6,14}$/.test(v)) return { platform: 'youtube', id: v };
if (/^\d{15,25}$/.test(v)) return { platform: 'linkedin', id: v };
try {
const u = new URL(v);
// YouTube
if (u.hostname === 'youtu.be') {
const id = u.pathname.replace(/^\//, '').split('/')[0];
return id ? { platform: 'youtube', id } : null;
}
if (/^(www\.|m\.)?youtube(-nocookie)?\.com$/.test(u.hostname)) {
let id = null;
if (u.pathname === '/watch') id = u.searchParams.get('v');
else if (u.pathname.startsWith('/embed/')) id = u.pathname.split('/')[2];
else if (u.pathname.startsWith('/shorts/')) id = u.pathname.split('/')[2];
return id ? { platform: 'youtube', id } : null;
}
// LinkedIn
if (u.hostname.endsWith('linkedin.com')) {
let m = u.pathname.match(/-activity-(\d{15,25})-/);
if (m) return { platform: 'linkedin', id: m[1] };
m = u.pathname.match(/urn:li:(?:activity|share|ugcPost):(\d{10,25})/);
if (m) return { platform: 'linkedin', id: m[1] };
m = u.pathname.match(/\/(\d{15,25})(?:\/|$)/);
if (m) return { platform: 'linkedin', id: m[1] };
}
} catch {}
return null;
}
// Back-compat shim — older /admin/ops/watch posts pre-LinkedIn called the
// old name; keep it working by delegating to the new parser.
function parseYouTubeIdForOps(input) {
const r = parseVideoIdForOps(input);
return r && r.platform === 'youtube' ? r.id : null;
}
router.get('/ops/watch', requireOpsInstaller, async (req, res, next) => {
try {
const all = await db.many(
`SELECT id, youtube_id, title, channel_name, channel_url, category,
materials, brands, description, license_note, display_order,
added_by, added_at, verified_at, is_published
FROM curated_videos
ORDER BY is_published DESC, display_order, added_at DESC`
);
res.render('admin/ops-watch', {
title: 'Ops · Curated /watch · National Paper Hangers',
videos: all,
categories: Array.from(ALLOWED_WATCH_CATEGORIES)
});
} catch (err) { next(err); }
});
router.post('/ops/watch', requireOpsInstaller, async (req, res, next) => {
try {
const f = req.body || {};
const parsed = parseVideoIdForOps(f.youtube_url || f.youtube_id || f.video_url);
if (!parsed) {
req.session.flash = { error: 'Could not parse a YouTube or LinkedIn video from that input.' };
return res.redirect('/admin/ops/watch');
}
const { platform, id: extId } = parsed;
const title = clampLen((f.title || '').trim(), 240);
if (!title) {
req.session.flash = { error: 'Title is required.' };
return res.redirect('/admin/ops/watch');
}
const category = ALLOWED_WATCH_CATEGORIES.has(f.category) ? f.category : 'technique';
const channelName = f.channel_name ? clampLen(f.channel_name.trim(), 120) : null;
const channelUrl = sanitizeWebsite(f.channel_url);
const description = f.description ? clampLen(f.description.trim(), 600) : null;
const sourceUrl = platform === 'linkedin'
? `https://www.linkedin.com/feed/update/urn:li:share:${extId}/`
: `https://www.youtube.com/watch?v=${extId}`;
const defaultLicense = platform === 'linkedin' ? 'Standard LinkedIn embed' : 'Standard YouTube embed';
const licenseNote = f.license_note ? clampLen(f.license_note.trim(), 200) : defaultLicense;
const brands = (f.brands || '').split(',').map(s => s.trim()).filter(Boolean).slice(0, 12);
const materials = (f.materials || '').split(',').map(s => s.trim()).filter(Boolean).slice(0, 8);
await db.query(
`INSERT INTO curated_videos
(youtube_id, platform, title, channel_name, channel_url, category,
materials, brands, description, source_url, license_note,
added_by, is_published)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,false)
ON CONFLICT (youtube_id) DO UPDATE
SET platform = EXCLUDED.platform,
title = EXCLUDED.title,
channel_name = EXCLUDED.channel_name,
channel_url = EXCLUDED.channel_url,
category = EXCLUDED.category,
materials = EXCLUDED.materials,
brands = EXCLUDED.brands,
description = EXCLUDED.description,
license_note = EXCLUDED.license_note`,
[
extId, platform, title, channelName, channelUrl, category,
materials, brands, description, sourceUrl, licenseNote,
req.installer.email || ('installer:' + req.installer.id)
]
);
req.session.flash = { ok: 'Video staged — flip Publish when ready.' };
res.redirect('/admin/ops/watch');
} catch (err) { next(err); }
});
router.post('/ops/watch/:id(\\d+)/publish', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(
`UPDATE curated_videos
SET is_published = true,
verified_at = COALESCE(verified_at, now())
WHERE id = $1`,
[req.params.id]
);
req.session.flash = { ok: 'Published — now visible on /watch.' };
res.redirect('/admin/ops/watch');
} catch (err) { next(err); }
});
router.post('/ops/watch/:id(\\d+)/unpublish', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(`UPDATE curated_videos SET is_published = false WHERE id = $1`, [req.params.id]);
req.session.flash = { ok: 'Unpublished — hidden from /watch.' };
res.redirect('/admin/ops/watch');
} catch (err) { next(err); }
});
router.post('/ops/watch/:id(\\d+)/delete', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(`DELETE FROM curated_videos WHERE id = $1`, [req.params.id]);
req.session.flash = { ok: 'Video removed.' };
res.redirect('/admin/ops/watch');
} catch (err) { next(err); }
});
// ===================================================================
// OPS — paper-comment moderation (UX backlog #3)
// ===================================================================
//
// Comments post immediately and are public-readable. Ops can flip the
// `flagged` boolean to hide a comment from the public detail view. Schema
// is reversible — flagging is not deletion.
router.get('/papers-moderation', requireOpsInstaller, async (req, res, next) => {
try {
const show = (req.query.show || 'all').trim();
const where = show === 'flagged' ? `c.flagged = true`
: show === 'visible' ? `c.flagged = false`
: `TRUE`;
const rows = await db.many(
`SELECT c.id, c.thread_id, c.body, c.helpful_count, c.flagged,
c.created_at, c.edited_at,
t.slug AS thread_slug, t.brand AS thread_brand, t.paper_name AS thread_paper,
i.slug AS installer_slug, i.business_name AS installer_business_name
FROM paper_comments c
JOIN paper_threads t ON t.id = c.thread_id
JOIN installers i ON i.id = c.installer_id
WHERE ${where}
ORDER BY c.created_at DESC
LIMIT 200`
);
res.render('admin/papers-moderation', {
title: 'Ops · Paper-comment moderation · National Paper Hangers',
comments: rows,
show
});
} catch (err) { next(err); }
});
router.post('/papers-moderation/:id(\\d+)/flag', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(`UPDATE paper_comments SET flagged = true WHERE id = $1`, [req.params.id]);
req.session.flash = { ok: 'Comment flagged — hidden from public view.' };
res.redirect('/admin/papers-moderation' + (req.body.return_to_show ? `?show=${encodeURIComponent(req.body.return_to_show)}` : ''));
} catch (err) { next(err); }
});
router.post('/papers-moderation/:id(\\d+)/unflag', requireOpsInstaller, async (req, res, next) => {
try {
await db.query(`UPDATE paper_comments SET flagged = false WHERE id = $1`, [req.params.id]);
req.session.flash = { ok: 'Comment unflagged — visible again.' };
res.redirect('/admin/papers-moderation' + (req.body.return_to_show ? `?show=${encodeURIComponent(req.body.return_to_show)}` : ''));
} catch (err) { next(err); }
});
// ===================================================================
// Template chooser (the 6 page designs the studio can pick from)
// ===================================================================
const TEMPLATE_SLUGS = ['editorial','trade-pro','concierge','studio','heritage','bilingue'];
router.get('/template', async (req, res, next) => {
try {
// Flash is captured + cleared by the global middleware in server.js → res.locals.flash.
// Don't read req.session.flash here (already null) and don't override locals.
res.render('admin/template', {
title: 'Page design · National Paper Hangers',
installer: req.installer
});
} catch (err) { next(err); }
});
router.post('/template', async (req, res, next) => {
try {
const slug = String(req.body.template_slug || '').toLowerCase();
if (!TEMPLATE_SLUGS.includes(slug)) {
req.session.flash = { error: 'Invalid template choice.' };
return res.redirect('/admin/template');
}
// template_settings: keep what's there, overlay our editable fields.
const existing = req.installer.template_settings || {};
// hero_url must be one of THIS installer's own content-addressed uploads
// (/uploads/<their-id>/hero-<hash>.<ext>) — the hidden field is normally
// filled by the upload JS, but a hand-crafted POST could otherwise plant
// an arbitrary off-site URL that renders into the public profile's
// <img src>. Anything else is dropped (hero falls back to portfolio[0]).
const heroRaw = String(req.body.hero_url || '').slice(0, 500);
const heroOwnPathRe = new RegExp(
'^/uploads/' + req.installer.id + '/hero-[0-9a-f]{16}\\.(jpg|png|webp|avif)$'
);
const heroUrl = heroOwnPathRe.test(heroRaw) ? heroRaw : undefined;
const settings = {
...existing,
hero_url: heroUrl,
accent_color: /^#[0-9a-f]{6}$/i.test(String(req.body.accent_color || '')) ? req.body.accent_color : undefined
};
// Strip undefineds.
Object.keys(settings).forEach(k => settings[k] === undefined && delete settings[k]);
await db.query(
`UPDATE installers SET template_slug = $2, template_settings = $3 WHERE id = $1`,
[req.installer.id, slug, JSON.stringify(settings)]
);
req.session.flash = { ok: 'Page design saved. View your live profile to see it.' };
res.redirect('/admin/template');
} catch (err) { next(err); }
});
// ===================================================================
// Drag-drop image upload (used by /admin/template hero + portfolio)
// ===================================================================
// Per-installer rate limit on uploads — caps a runaway/hostile authenticated
// installer at 30 uploads / hour. Keyed on installer.id (not IP) so studios
// behind shared NATs don't share a bucket. requireInstaller above guarantees
// req.installer is present at this point.
const uploadLimiter = rateLimit({
windowMs: 60 * 60 * 1000, max: 30,
standardHeaders: true, legacyHeaders: false,
keyGenerator: (req) => 'inst:' + (req.installer && req.installer.id || 'anon'),
message: { ok: false, error: 'Upload rate limit reached — try again in an hour.' }
});
router.post('/uploads', uploadLimiter, (req, res, next) => {
upload.single('file')(req, res, async (err) => {
if (err) {
const msg = err.message === 'UNSUPPORTED_TYPE'
? 'Image must be JPG, PNG, WebP, or AVIF.'
: (err.code === 'LIMIT_FILE_SIZE' ? 'Image too large (max 8 MB).' : 'Upload failed.');
return res.status(400).json({ ok: false, error: msg });
}
if (!req.file) return res.status(400).json({ ok: false, error: 'No file received.' });
// Authoritative gate: derive the extension from the file's actual magic
// bytes, never the spoofable browser Content-Type. multer's fileFilter
// only saw the claimed mimetype — a non-image blob mislabelled image/png
// clears that filter but fails here. Portfolio uploads are images only,
// so a video brand (.mp4/.mov/.webm) is also rejected.
const sniffed = mediaSniff.sniffExt(req.file.buffer);
if (!sniffed || mediaSniff.isVideoExt(sniffed)) {
return res.status(400).json({
ok: false,
error: 'File contents are not a supported image (JPG, PNG, WebP, or AVIF).'
});
}
try {
const role = String(req.body.role || 'portfolio').toLowerCase();
const installerId = req.installer.id;
const dir = path.join(UPLOAD_ROOT, String(installerId));
fs.mkdirSync(dir, { recursive: true });
const hash = crypto.createHash('sha256').update(req.file.buffer).digest('hex').slice(0, 16);
const ext = sniffed;
const fname = `${role}-${hash}${ext}`;
const fpath = path.join(dir, fname);
fs.writeFileSync(fpath, req.file.buffer);
const url = `/uploads/${installerId}/${fname}`;
// Portfolio uploads also persist a row so they show up on the public page
// immediately. Hero uploads are stored only on installers.template_settings.hero_url.
let portfolioId = null;
if (role === 'portfolio') {
const order = await db.one(
'SELECT COALESCE(MAX(display_order), 0) + 1 AS next_order FROM installer_portfolio WHERE installer_id = $1',
[installerId]
);
const row = await db.one(
`INSERT INTO installer_portfolio (installer_id, title, image_url, city, state, year, display_order)
VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id`,
[
installerId,
'Untitled project',
url,
req.installer.city || null,
req.installer.state || null,
new Date().getFullYear(),
order.next_order
]
);
portfolioId = row.id;
}
res.json({ ok: true, url, role, id: portfolioId, size: req.file.size, mime: req.file.mimetype });
} catch (e) { next(e); }
});
});
module.exports = router;