← back to Ventura Claw Leads
routes/admin.js
436 lines
const express = require('express');
const path = require('node:path');
const fs = require('node:fs');
const slugify = require('slugify');
const db = require('../lib/db');
const auth = require('../lib/auth');
const stripe = require('../lib/stripe');
const router = express.Router();
// Photo upload directory — actual multer parsing happens in server.js
// BEFORE csrfMiddleware (see lib/photo-upload.js). This route handler
// just reads req.file (the parsed result) and updates the DB.
const PHOTO_DIR = path.resolve(__dirname, '..', 'public', 'uploads', 'business-photos');
const PUBLIC_URL = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
router.use(auth.requireBusiness);
router.get('/', async (req, res, next) => {
try {
const biz = res.locals.currentBusiness;
const stats = await db.one(`
SELECT
COUNT(*)::int AS total_leads,
COUNT(*) FILTER (WHERE delivered_at IS NOT NULL)::int AS delivered,
COUNT(*) FILTER (WHERE delivered_at IS NULL)::int AS pending,
COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread,
COUNT(*) FILTER (WHERE created_at > now() - interval '30 days')::int AS last_30d,
(SELECT COALESCE(SUM(n), 0)::int FROM business_views
WHERE business_id = $1 AND day > CURRENT_DATE - interval '30 days') AS views_30d,
(SELECT COALESCE(SUM(n), 0)::int FROM business_views
WHERE business_id = $1 AND day = CURRENT_DATE) AS views_today,
(SELECT COALESCE(share_count, 0)::int FROM businesses WHERE id = $1) AS shares_total
FROM business_interest WHERE business_id = $1
`, [biz.id]);
const recent = await db.many(`
SELECT id, consumer_name, consumer_email, consumer_phone, message, zip,
created_at, delivered_at, delivery_email
FROM business_interest WHERE business_id = $1
ORDER BY created_at DESC LIMIT 5
`, [biz.id]);
// 30-day daily lead trend for the sparkline. generate_series produces a
// contiguous date axis so days with zero leads still appear (so the
// sparkline doesn't compress around active days only).
const trendRows = await db.many(`
WITH days AS (
SELECT generate_series(
(now() AT TIME ZONE 'America/Los_Angeles')::date - interval '29 days',
(now() AT TIME ZONE 'America/Los_Angeles')::date,
'1 day'::interval
)::date AS d
)
SELECT days.d AS day,
COALESCE(COUNT(bi.*), 0)::int AS n
FROM days
LEFT JOIN business_interest bi
ON bi.business_id = $1
AND (bi.created_at AT TIME ZONE 'America/Los_Angeles')::date = days.d
GROUP BY days.d
ORDER BY days.d ASC
`, [biz.id]);
const trend = trendRows.map(r => ({ day: r.day, n: r.n }));
const trendPeak = trend.reduce((m, p) => Math.max(m, p.n), 0);
res.render('admin/dashboard', {
title: `${biz.business_name} · Dashboard`,
stats, recent, trend, trendPeak,
welcome: req.query.welcome === '1'
});
} catch (err) { next(err); }
});
router.get('/profile', async (req, res, next) => {
try {
const biz = await db.one(`SELECT * FROM businesses WHERE id = $1`, [res.locals.currentBusiness.id]);
res.render('admin/profile', {
title: 'Edit profile · Ventura Claw', biz, error: null,
ok: req.query.saved === '1',
photoOk: req.query.photo === '1',
photoErr: req.query.photo_err || null
});
} catch (err) { next(err); }
});
router.post('/profile/photo', async (req, res, next) => {
try {
if (req._photoUploadError) {
return res.redirect('/admin/profile?photo_err=' + encodeURIComponent(req._photoUploadError));
}
if (!req.file) return res.redirect('/admin/profile?photo_err=no_file');
const bizId = res.locals.currentBusiness.id;
// Best-effort cleanup of the previous photo so we don't leak disk.
const prev = await db.one(`SELECT photo_path FROM businesses WHERE id = $1`, [bizId]);
if (prev && prev.photo_path) {
const prevAbs = path.resolve(__dirname, '..', 'public', prev.photo_path.replace(/^\/+/, ''));
if (prevAbs.startsWith(PHOTO_DIR)) {
try { fs.unlinkSync(prevAbs); } catch {}
}
}
const relPath = '/uploads/business-photos/' + req.file.filename;
await db.query(`UPDATE businesses SET photo_path = $1 WHERE id = $2`, [relPath, bizId]);
res.redirect('/admin/profile?photo=1');
} catch (err) { next(err); }
});
router.post('/profile/photo/delete', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const prev = await db.one(`SELECT photo_path FROM businesses WHERE id = $1`, [bizId]);
if (prev && prev.photo_path) {
const prevAbs = path.resolve(__dirname, '..', 'public', prev.photo_path.replace(/^\/+/, ''));
if (prevAbs.startsWith(PHOTO_DIR)) { try { fs.unlinkSync(prevAbs); } catch {} }
}
await db.query(`UPDATE businesses SET photo_path = NULL WHERE id = $1`, [bizId]);
res.redirect('/admin/profile?photo=1');
} catch (err) { next(err); }
});
router.post('/profile', async (req, res, next) => {
try {
const biz = res.locals.currentBusiness;
const fields = {
business_name: String(req.body.business_name || '').trim().slice(0, 200),
headline: String(req.body.headline || '').trim().slice(0, 250) || null,
description: String(req.body.description || '').trim().slice(0, 4000) || null,
street: String(req.body.street || '').trim().slice(0, 200) || null,
neighborhood: String(req.body.neighborhood || '').trim().slice(0, 80) || null,
city: String(req.body.city || '').trim().slice(0, 80) || null,
zip: String(req.body.zip || '').trim().slice(0, 10) || null,
phone: String(req.body.phone || '').trim().slice(0, 32) || null,
email: String(req.body.email || '').trim().toLowerCase().slice(0, 200) || null,
website: String(req.body.website || '').trim().slice(0, 300) || null,
instagram_handle: String(req.body.instagram_handle || '').trim().replace(/^@/, '').slice(0, 60) || null
};
if (!fields.business_name) {
const fresh = await db.one(`SELECT * FROM businesses WHERE id = $1`, [biz.id]);
return res.status(400).render('admin/profile', {
title: 'Edit profile · Ventura Claw', biz: fresh, ok: false,
error: 'Business name is required.'
});
}
await db.query(`
UPDATE businesses SET
business_name = $2, headline = $3, description = $4,
street = $5, neighborhood = $6, city = $7, zip = $8,
phone = $9, email = $10, website = $11, instagram_handle = $12
WHERE id = $1
`, [biz.id, fields.business_name, fields.headline, fields.description,
fields.street, fields.neighborhood, fields.city, fields.zip,
fields.phone, fields.email, fields.website, fields.instagram_handle]);
res.redirect('/admin/profile?saved=1');
} catch (err) { next(err); }
});
router.get('/leads', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const filter = (req.query.filter === 'unread') ? 'unread' : 'all';
const rangeWhitelist = { '7': 7, '30': 30, '90': 90, 'all': null };
const rangeKey = String(req.query.range || 'all');
const rangeDays = rangeWhitelist[rangeKey] !== undefined ? rangeWhitelist[rangeKey] : null;
const q = String(req.query.q || '').trim().slice(0, 120);
const PER_PAGE = 50;
const page = Math.max(1, Math.min(parseInt(req.query.page, 10) || 1, 200)); // cap at 200 pages = 10k rows; sane upper bound
const where = ['business_id = $1'];
const params = [bizId];
if (filter === 'unread') where.push('read_at IS NULL');
if (rangeDays) where.push(`created_at > now() - interval '${rangeDays} days'`);
if (q) {
params.push(`%${q.toLowerCase()}%`);
where.push(`(LOWER(consumer_name) LIKE $${params.length} OR LOWER(consumer_email) LIKE $${params.length} OR LOWER(message) LIKE $${params.length} OR LOWER(zip) LIKE $${params.length})`);
}
// Fetch PER_PAGE+1 to detect whether a next page exists without a separate COUNT.
params.push(PER_PAGE + 1, (page - 1) * PER_PAGE);
const limitIdx = params.length - 1;
const offsetIdx = params.length;
const fetched = await db.many(`
SELECT id, consumer_name, consumer_email, consumer_phone, message, zip, source,
created_at, delivered_at, delivery_email, delivery_msg_id, bill_amount_cents,
read_at
FROM business_interest
WHERE ${where.join(' AND ')}
ORDER BY (read_at IS NOT NULL), created_at DESC
LIMIT $${limitIdx} OFFSET $${offsetIdx}
`, params);
const hasNext = fetched.length > PER_PAGE;
const leads = fetched.slice(0, PER_PAGE);
const counts = await db.one(`
SELECT COUNT(*)::int AS total,
COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread,
COUNT(*) FILTER (WHERE created_at > now() - interval '7 days')::int AS last_7,
COUNT(*) FILTER (WHERE created_at > now() - interval '30 days')::int AS last_30,
COUNT(*) FILTER (WHERE created_at > now() - interval '90 days')::int AS last_90
FROM business_interest WHERE business_id = $1
`, [bizId]);
res.render('admin/leads', {
title: 'Leads · Ventura Claw',
leads, filter, counts, range: rangeKey, q,
page, hasNext, hasPrev: page > 1, perPage: PER_PAGE
});
} catch (err) { next(err); }
});
router.post('/leads/:id(\\d+)/read', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const leadId = parseInt(req.params.id, 10);
await db.query(
`UPDATE business_interest SET read_at = COALESCE(read_at, now()) WHERE id = $1 AND business_id = $2`,
[leadId, bizId]
);
res.redirect(req.body.next || '/admin/leads');
} catch (err) { next(err); }
});
router.post('/leads/:id(\\d+)/unread', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const leadId = parseInt(req.params.id, 10);
await db.query(
`UPDATE business_interest SET read_at = NULL WHERE id = $1 AND business_id = $2`,
[leadId, bizId]
);
res.redirect(req.body.next || '/admin/leads');
} catch (err) { next(err); }
});
router.post('/leads/mark-all-read', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
await db.query(
`UPDATE business_interest SET read_at = now() WHERE business_id = $1 AND read_at IS NULL`,
[bizId]
);
res.redirect('/admin/leads');
} catch (err) { next(err); }
});
// Lead inbox CSV export. Useful when a business runs reporting in Excel /
// Sheets, or when paid-tier subscribers reconcile leads against their CRM.
// All fields included — name, email, phone, ZIP, message, timestamps, delivery
// state, billing amount. RFC 4180 quoting (CRLF, "" escapes inner quotes).
router.get('/leads.csv', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const leads = await db.many(`
SELECT id, consumer_name, consumer_email, consumer_phone, message, zip, source,
created_at, delivered_at, delivery_email, delivery_msg_id, bill_amount_cents
FROM business_interest
WHERE business_id = $1
ORDER BY created_at DESC
LIMIT 5000
`, [bizId]);
function q(v) {
if (v == null) return '';
const s = (v instanceof Date) ? v.toISOString() : String(v);
if (/[",\r\n]/.test(s)) return '"' + s.replace(/"/g, '""') + '"';
return s;
}
const headers = ['id','created_at','consumer_name','consumer_email','consumer_phone','zip','source','message','delivered_at','delivery_email','delivery_msg_id','bill_amount_cents'];
let csv = headers.join(',') + '\r\n';
for (const l of leads) {
csv += headers.map(h => q(l[h])).join(',') + '\r\n';
}
const dateStamp = new Date().toISOString().slice(0, 10);
res.set('Content-Type', 'text/csv; charset=utf-8');
res.set('Content-Disposition', `attachment; filename="vcl-leads-${dateStamp}.csv"`);
res.set('Cache-Control', 'no-store');
res.send(csv);
} catch (err) { next(err); }
});
// ─── Team / multi-user per business ────────────────────────────────────
const compliance = require('../lib/compliance');
const { sendEmail } = require('../lib/email');
const crypto = require('node:crypto');
const PUBLIC_URL_RAW = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
router.get('/team', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const team = await db.many(`
SELECT id, email, display_name, last_login_at, created_at,
(id = $2) AS is_self
FROM business_users WHERE business_id = $1
ORDER BY (id = $2) DESC, -- self always first
last_login_at DESC NULLS LAST, -- recently-active up top
created_at ASC -- tiebreak: oldest member first
`, [bizId, res.locals.currentBusinessUser.id]);
const pendingInvites = await db.many(`
SELECT id, email, issued_at, expires_at
FROM business_invitations
WHERE business_id = $1 AND consumed_at IS NULL AND expires_at > now()
ORDER BY issued_at DESC
`, [bizId]);
res.render('admin/team', {
title: 'Team · Ventura Claw',
team, pendingInvites,
flash: req.query
});
} catch (err) { next(err); }
});
router.post('/team/invite', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const inviterId = res.locals.currentBusinessUser.id;
const email = auth.normalizeEmail(req.body.email);
if (!auth.isEmailShape(email)) return res.redirect('/admin/team?error=bad_email');
// Already a team member? Idempotent — silent ok.
const existing = await db.one(
`SELECT id FROM business_users WHERE business_id = $1 AND email = $2`,
[bizId, email]
);
if (existing) return res.redirect('/admin/team?error=already_member');
const ipHash = crypto.createHash('sha256')
.update((req.headers['x-forwarded-for'] || req.ip || '') + (process.env.SESSION_SECRET || ''))
.digest('hex').slice(0, 16);
const { token } = await auth.issueInviteToken({
businessId: bizId, email, invitedByUserId: inviterId, ipHash
});
const link = `${PUBLIC_URL_RAW}/accept-invite/${encodeURIComponent(token)}`;
const biz = res.locals.currentBusiness;
const inviter = res.locals.currentBusinessUser;
const subject = `${inviter.display_name || inviter.email} invited you to manage ${biz.business_name} on Ventura Claw`;
const html = `<div style="font-family:Georgia,serif;max-width:560px;margin:0 auto;padding:32px 24px;color:#0e0e0e">
<p style="font-size:11px;letter-spacing:0.16em;text-transform:uppercase;color:#b8860b;margin:0 0 8px">Team invitation</p>
<h1 style="font-size:24px;font-weight:400;margin:0 0 12px">You've been invited to manage ${escapeHtml(biz.business_name)}</h1>
<p style="font-size:15px;line-height:1.55"><strong>${escapeHtml(inviter.display_name || inviter.email)}</strong> added you as a team member on Ventura Claw. Once you accept, you'll see the same dashboard, lead inbox, and profile editor for <strong>${escapeHtml(biz.business_name)}</strong>.</p>
<p style="margin:24px 0"><a href="${link}" style="display:inline-block;background:#0e0e0e;color:#fff;padding:14px 22px;text-decoration:none;font-size:14px;letter-spacing:0.04em;border-radius:2px">Accept invitation →</a></p>
<p style="font-size:13px;color:#666;line-height:1.55">Link valid for 7 days; works once.</p>
${process.env.NODE_ENV === 'production' ? compliance.complianceFooter({ campaign: 'team_invite', unsubscribeUrl: null }) : ''}
</div>`;
await sendEmail({ to: email, subject, html, text: `${subject}\n\n${link}\n\n(link expires in 7 days)` });
if (process.env.NODE_ENV === 'production') {
await compliance.recordAudit({
channel: 'email', campaign: 'team_invite', recipient: email, businessId: bizId,
decision: 'sent', subject, messageId: null, payload: { token_prefix: token.slice(0, 8) }
});
}
res.redirect('/admin/team?invited=' + encodeURIComponent(email));
} catch (err) { next(err); }
});
router.post('/team/invite/:id(\\d+)/cancel', async (req, res, next) => {
try {
await db.query(
`DELETE FROM business_invitations
WHERE id = $1 AND business_id = $2 AND consumed_at IS NULL`,
[parseInt(req.params.id, 10), res.locals.currentBusiness.id]
);
res.redirect('/admin/team');
} catch (err) { next(err); }
});
router.post('/team/:id(\\d+)/remove', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const targetId = parseInt(req.params.id, 10);
const selfId = res.locals.currentBusinessUser.id;
if (targetId === selfId) return res.redirect('/admin/team?error=self_remove');
// Ensure at least one user remains (count after removal).
const others = await db.one(
`SELECT COUNT(*)::int AS n FROM business_users WHERE business_id = $1 AND id != $2`,
[bizId, targetId]
);
if (others.n === 0) return res.redirect('/admin/team?error=last_user');
await db.query(`DELETE FROM business_users WHERE id = $1 AND business_id = $2`, [targetId, bizId]);
res.redirect('/admin/team?removed=1');
} catch (err) { next(err); }
});
function escapeHtml(s) { return String(s || '').replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/"/g,'"'); }
// ─── Billing ───────────────────────────────────────────────────────────
router.get('/billing', async (req, res, next) => {
try {
// Recent Stripe events for THIS business — pulled from the audited
// subscription_events table, filtered by metadata.business_id matching
// the current business so a multi-tenant Stripe account stays clean.
const events = await db.many(`
SELECT id, stripe_event_id, event_type, payload, created_at
FROM subscription_events
WHERE (payload -> 'data' -> 'object' -> 'metadata' ->> 'business_id') = $1
ORDER BY created_at DESC
LIMIT 20
`, [String(res.locals.currentBusiness.id)]);
res.render('admin/billing', {
title: 'Billing · Ventura Claw',
stripeLive: stripe.isLive(),
tiers: stripe.VCL_TIERS,
events,
flash: req.query
});
} catch (err) { next(err); }
});
router.post('/billing/checkout', async (req, res, next) => {
try {
const tier = String(req.body.tier || '').trim();
if (!stripe.VCL_TIERS[tier]) {
return res.redirect('/admin/billing?error=unknown_tier');
}
const biz = await db.one(`SELECT id, slug, business_name, email, stripe_customer_id FROM businesses WHERE id = $1`, [res.locals.currentBusiness.id]);
const session = await stripe.createSubscriptionCheckout({
business: biz, tier,
successUrl: `${PUBLIC_URL}/admin/billing?upgraded=${tier}`,
cancelUrl: `${PUBLIC_URL}/admin/billing?canceled=1`
});
if (session.mocked) {
return res.redirect(session.url || '/admin/billing?mock=1');
}
res.redirect(303, session.url);
} catch (err) { next(err); }
});
router.post('/billing/portal', async (req, res, next) => {
try {
const biz = await db.one(`SELECT id, slug, business_name, email, stripe_customer_id FROM businesses WHERE id = $1`, [res.locals.currentBusiness.id]);
if (!biz.stripe_customer_id) {
return res.redirect('/admin/billing?error=no_customer');
}
const portal = await stripe.createPortalSession({ business: biz, returnUrl: `${PUBLIC_URL}/admin/billing` });
if (portal.mocked) return res.redirect('/admin/billing?mock=portal');
res.redirect(303, portal.url);
} catch (err) { next(err); }
});
module.exports = router;