← back to AsSeenInMovies
server.js
2274 lines
'use strict';
// AsSeenInMovies — IMDb-class movie + people DB tied to thesetdecorator's
// 61k-row set-decorator roster and dw_unified's product catalog. Adds a
// SPOTTED feature for products visible in scenes, premium upgrade tier
// for claimed actor/actress/crew profiles, and an AI-generated placeholder
// image for every poster/headshot we don't have rights-cleared.
//
// Steve's standing rules baked in:
// - NEVER serve visual assets sourced from IMDb. Posters/headshots come
// from TMDB (with attribution), Wikimedia Commons (CC), public-domain
// production stills, or our /img/made-with-ai.svg fallback.
// - Public data only. Facts are public; images aren't.
// - Local LLMs only for any text generation work (no API costs).
require('dotenv').config();
const express = require('express');
const compression = require('compression');
const path = require('path');
const fs = require('fs');
const { Pool } = require('pg');
// Cached version metadata: package.json version + git short SHA (best-effort).
// Resolved once at boot to keep /api/version sub-1ms. SHA falls back to 'unknown'
// when git isn't on PATH or the project isn't a repo.
const PKG_VERSION = (() => {
try { return require('./package.json').version || '0.0.0'; }
catch (_) { return '0.0.0'; }
})();
const GIT_SHA = (() => {
try {
return require('child_process')
.execSync('git rev-parse --short HEAD', { cwd: __dirname, stdio: ['ignore', 'pipe', 'ignore'] })
.toString().trim() || 'unknown';
} catch (_) { return 'unknown'; }
})();
const STARTED_AT_ISO = new Date().toISOString();
const PORT = parseInt(process.env.PORT || '9742', 10);
const DB_URL = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2';
// Admin gate for /spotted/queue moderation routes. If ASIM_ADMIN_TOKEN is not
// set in .env, generate an ephemeral one at boot and log it to stdout — that
// way moderation is never silently unauth'd, but Steve can grab the token
// from `pm2 logs asim` without provisioning anything.
const ADMIN_TOKEN = process.env.ASIM_ADMIN_TOKEN
|| require('crypto').randomBytes(16).toString('hex');
if (!process.env.ASIM_ADMIN_TOKEN) {
console.log(`[asim] ephemeral admin token: ${ADMIN_TOKEN} — set ASIM_ADMIN_TOKEN in .env to persist`);
}
function isAdmin(req) {
const tok = req.query.token || req.headers['x-admin-token'] || (req.body && req.body.token);
return typeof tok === 'string' && tok === ADMIN_TOKEN;
}
const app = express();
// Trust the first proxy hop (Cloudflare → Kamatera nginx → here). Lets
// req.ip + req.protocol return the real client values from X-Forwarded-*
// headers, which downstream rate-limit/logging needs.
app.set('trust proxy', 1);
// gzip compression for all responses ≥ 1KB. Big leverage on JSON-LD-heavy
// HTML pages — /movies and /spotted carry full ItemList payloads now.
app.use(compression({ threshold: 1024 }));
// Minimal security headers — sod has the full helmet suite but asim has
// the Big Red iframe widget which complicates CSP, so we set the
// uncontroversial ones manually: clickjacking, MIME-sniffing, referrer
// leak, and permissions-policy default deny.
app.use((req, res, next) => {
res.setHeader('X-Content-Type-Options', 'nosniff');
res.setHeader('X-Frame-Options', 'SAMEORIGIN');
res.setHeader('Referrer-Policy', 'strict-origin-when-cross-origin');
res.setHeader('Permissions-Policy', 'geolocation=(), microphone=(), camera=(), payment=()');
next();
});
app.use(express.json({ limit: '2mb' }));
app.disable('x-powered-by');
const pool = new Pool({ connectionString: DB_URL, max: 6, idleTimeoutMillis: 20_000 });
pool.on('error', (e) => console.error('[pg]', e.message));
// Apply schema on startup if asim_movies doesn't exist yet — idempotent.
async function ensureSchema() {
try {
const r = await pool.query(`SELECT to_regclass('public.asim_movies') AS t`);
if (!r.rows[0]?.t) {
const sql = fs.readFileSync(path.join(__dirname, 'data', 'schema.sql'), 'utf8');
await pool.query(sql);
console.log('[schema] applied asim_* tables');
}
} catch (e) {
console.error('[schema]', e.message);
}
}
// 404-guard snapshot/backup paths BEFORE static so an accidentally-committed
// `*.bak` / `*.bak.<n>` / `*.pre-<label>` file under public/ can never leak.
// Also covers query-string variants (`?backup`) and tilde-suffix backups.
app.use((req, res, next) => {
const p = req.path;
if (/\.(bak|bak\.[^/]+|pre-[^/]+|orig|swp)$/i.test(p) ||
/(^|\/)\.pre-[^/]+/.test(p) ||
/~$/.test(p)) {
return res.status(404).type('text/plain').send('not-found');
}
next();
});
app.use(express.static(path.join(__dirname, 'public'), { maxAge: '1h' }));
// /api — JSON index of all public API endpoints. Useful for integrators
// who want to know what's available without reading the source.
app.get('/api', (_req, res) => {
res.json({
name: 'AsSeenInMovies API',
base: process.env.ASIM_PUBLIC_URL || 'https://asseeninmovies.com',
endpoints: {
'GET /api/health': 'Liveness probe + cached counts (movies/people/spotted)',
'GET /api/version': 'Deploy fingerprint — JSON {name, version, git, node, started_at}',
'GET /api/search?q=…': 'ILIKE search across movies + people (poster-having subset)',
'GET /api/movie/:id': 'Single movie row by id (TMDB id, not slug)',
'GET /api/person/:id': 'Single person row by id',
'GET /api/spotted': 'Verified SPOTTED placements (limit ≤ 200, optional category=…)',
'GET /api/spotted/stats': 'Aggregate counts (pending/live/rejected/submitted_7d/films/brands/categories)',
'GET /api/spotted/random': 'A single random verified SPOTTED placement (with movie context)',
'GET /api/spotted/featured':'N random verified SPOTTED placements (?n=6, max 24) — multi-row companion',
'GET /api/movies/random': 'A single random poster-having movie',
'GET /api/people/random': 'A single random headshot-having person',
'GET /api/credits/stats': 'Aggregate counts on asim_credits (total, acting, directing, writing, distinct_people, distinct_movies)',
'GET /api/movies/featured':'N random poster-having movies (?n=6, max 24) — multi-row companion to /api/movies/random',
'GET /api/people/featured':'N random headshot-having people (?n=6, max 24)',
'GET /api/ingest/status': 'Background-ingest queue depth (admin-ish; safe to expose)',
},
public_data_only: true,
license: 'Movie metadata via TMDB (not endorsed/certified). People + credits via TMDB + Wikidata CC0.',
});
});
// k8s-style alias — many infra tools probe /healthz by convention.
// no-store so monitors always see a fresh liveness signal.
app.get('/healthz', (_req, res) => {
res.setHeader('Cache-Control', 'no-store');
res.json({ ok: true, ts: Date.now() });
});
// /api/version — deploy fingerprint. Lets Steve verify which build is live
// without inspecting pm2 logs. Static at boot, so no DB hit.
app.get('/api/version', (_req, res) => {
res.setHeader('Cache-Control', 'no-store');
res.json({
name: 'asseeninmovies',
version: PKG_VERSION,
git: GIT_SHA,
node: process.version,
started_at: STARTED_AT_ISO,
});
});
app.get('/api/health', async (_req, res) => {
try {
// Cheap liveness: SELECT 1 only. The count(*) counts come from the
// home cache (60s SWR), so /api/health stays sub-10ms regardless of
// table size. Was 500ms+ doing full count(*) on 290k rows.
res.setHeader('Cache-Control', 'no-store');
const r = await pool.query('SELECT 1 AS ok');
const stats = (_homeCache && _homeCache.data && _homeCache.data.stats) || null;
res.json({
ok: r.rows[0].ok === 1,
counts: stats ? {
movies: Number(stats.movies_total) || 0,
people: Number(stats.people_total) || 0,
credits: Number(stats.credits_total) || 0,
spotted: Number(stats.spotted_total) || 0,
} : null,
uptime_s: Math.floor(process.uptime()),
as_of: stats && _homeCache.at ? new Date(_homeCache.at).toISOString() : null,
});
} catch (e) {
res.status(500).json({ ok: false, error: e.message });
}
});
// Search across movies + people (basic ILIKE for tick 0 — Postgres FTS in a later tick)
// /search — HTML search page; renders combined movies + people grid from
// /api/search. Replaces the old nav link that pointed at the JSON endpoint.
app.get('/search', async (req, res) => {
const q = typeof req.query.q === 'string' && req.query.q.length < 80 ? req.query.q.trim() : '';
let movies = [], people = [];
if (q.length >= 2) {
const like = '%' + q + '%';
try {
// Limit search to the visually-rich subset (with posters / headshots).
// The full asim_movies table is 290k rows of mostly stub data; ILIKE
// against the curated ~1.3k poster-having movies is fast against the
// partial idx_asim_movies_has_poster index. Users searching for a
// movie expect to see it WITH a poster; stub-only results aren't
// shippable until enrichment fills in their images anyway.
const [m, p] = await Promise.all([
pool.query(`SELECT id, title, release_year, poster_url FROM asim_movies WHERE poster_url IS NOT NULL AND title ILIKE $1 ORDER BY release_year DESC NULLS LAST, id DESC LIMIT 24`, [like]),
pool.query(`SELECT id, full_name, headshot_url, primary_profession FROM asim_people WHERE headshot_url IS NOT NULL AND full_name ILIKE $1 ORDER BY full_name LIMIT 24`, [like]),
]);
movies = m.rows; people = p.rows;
} catch (e) { /* fall through to empty */ }
}
const movieCards = movies.map(m => `
<a class="grid-card" href="/movie/${m.id}">
<div class="grid-poster" style="background-image:url('${esc(m.poster_url || '/img/made-with-ai.svg')}');--card-bg:url('${esc(m.poster_url || '/img/made-with-ai.svg')}')"></div>
<div class="grid-body">
<div class="grid-title">${esc(m.title)}</div>
<div class="grid-meta">${m.release_year || '—'}</div>
</div>
</a>`).join('');
const peopleCards = people.map(p => `
<a class="grid-card" href="/person/${p.id}">
<div class="grid-headshot" style="background-image:url('${esc(p.headshot_url || '/img/made-with-ai.svg')}');--card-bg:url('${esc(p.headshot_url || '/img/made-with-ai.svg')}')"></div>
<div class="grid-body">
<div class="grid-title">${esc(p.full_name)}</div>
<div class="grid-meta">${(p.primary_profession || []).slice(0,2).join(' · ')}</div>
</div>
</a>`).join('');
const body = `
<style>
.search-bar{display:flex;gap:10px;align-items:center;margin-bottom:32px}
.search-bar input{flex:1;background:transparent;border:1px solid var(--line);color:inherit;padding:10px 14px;border-radius:6px;font:inherit;font-size:16px}
.search-bar button{padding:10px 18px;background:var(--gold);color:#1a1410;border:0;border-radius:6px;font:inherit;letter-spacing:0.06em;cursor:pointer}
.results-head{font-size:13px;letter-spacing:0.16em;text-transform:uppercase;color:var(--ink-faint);margin:32px 0 14px;padding-bottom:6px;border-bottom:1px solid var(--line)}
.grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(160px,1fr));gap:18px}
.grid-card{display:block;border:1px solid var(--line);border-radius:8px;overflow:hidden;background:var(--card);transition:transform .15s;text-decoration:none;color:inherit}
.grid-card:hover{transform:translateY(-2px);border-color:var(--gold)}
.grid-poster{aspect-ratio:2/3;background:#15130f no-repeat center/cover;position:relative;overflow:hidden}
.grid-headshot{aspect-ratio:3/4;background:#15130f no-repeat center/cover;position:relative;overflow:hidden}
.grid-poster::after,.grid-headshot::after{content:"";position:absolute;inset:0;background-image:var(--card-bg,none);background-repeat:repeat;background-size:33.33% 33.33%;background-position:center;opacity:0;transition:opacity 360ms ease;pointer-events:none;will-change:opacity}
.grid-card:hover .grid-poster::after,.grid-card:hover .grid-headshot::after,.grid-card:focus-visible .grid-poster::after,.grid-card:focus-visible .grid-headshot::after{opacity:1}
@media (prefers-reduced-motion:reduce){.grid-poster::after,.grid-headshot::after{transition:none}}
.grid-body{padding:10px 12px}
.grid-title{font-size:13px;line-height:1.3;margin-bottom:4px;color:var(--ink);overflow:hidden;display:-webkit-box;-webkit-line-clamp:2;-webkit-box-orient:vertical}
.grid-meta{color:var(--ink-faint);font-size:11px;letter-spacing:0.04em}
</style>
<h1 class="title">Search</h1>
<form class="search-bar" method="get" action="/search">
<input name="q" type="search" placeholder="Title, actor, director…" value="${esc(q)}" autofocus>
<button type="submit">Search</button>
</form>
${q.length >= 2 ? `
<div class="results-head">Movies (${movies.length})</div>
<div class="grid">${movieCards || '<p class="empty">No movies match.</p>'}</div>
<div class="results-head">People (${people.length})</div>
<div class="grid">${peopleCards || '<p class="empty">No people match.</p>'}</div>
` : '<p class="empty">Type 2 or more characters to search.</p>'}
`;
res.type('html').send(htmlShell(q ? `Search: ${q}` : 'Search', body, '', 'https://asseeninmovies.com/search'));
});
app.get('/api/search', async (req, res) => {
// 60s Cache-Control — search results are stable across short windows.
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
const q = String(req.query.q || '').trim();
if (!q || q.length < 2) return res.json({ movies: [], people: [] });
const like = '%' + q + '%';
try {
const [m, p] = await Promise.all([
pool.query(`SELECT id, tmdb_id, title, release_year, poster_url, poster_source
FROM asim_movies WHERE title ILIKE $1 ORDER BY popularity DESC NULLS LAST LIMIT 10`, [like]),
pool.query(`SELECT id, tmdb_id, full_name, primary_profession, headshot_url, headshot_source
FROM asim_people WHERE full_name ILIKE $1 ORDER BY full_name LIMIT 10`, [like]),
]);
res.json({ movies: m.rows, people: p.rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// Movie detail
app.get('/api/movie/:id', async (req, res) => {
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id) || id < 1) return res.status(400).json({ error: 'bad id' });
try {
// 5-min Cache-Control — movie metadata is stable; only SPOTTED changes,
// and it's surfaced separately in /api/spotted with its own short cache.
const [m, credits, spotted] = await Promise.all([
pool.query(`SELECT * FROM asim_movies WHERE id=$1 LIMIT 1`, [id]),
pool.query(`
SELECT c.role, c.character_name, c.order_idx, c.job_title, c.department,
p.id AS person_id, p.full_name, p.headshot_url, p.headshot_source, p.premium_tier
FROM asim_credits c JOIN asim_people p ON p.id = c.person_id
WHERE c.movie_id=$1 ORDER BY c.order_idx NULLS LAST, p.full_name`, [id]),
pool.query(`SELECT * FROM asim_spotted WHERE movie_id=$1 ORDER BY verified_at DESC NULLS LAST, created_at DESC`, [id])
]);
if (!m.rows[0]) return res.status(404).json({ error: 'not found' });
res.setHeader('Cache-Control', 'public, max-age=300, stale-while-revalidate=600');
res.json({ movie: m.rows[0], credits: credits.rows, spotted: spotted.rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// Person detail (basic public profile + premium extras if claimed)
app.get('/api/person/:id', async (req, res) => {
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id) || id < 1) return res.status(400).json({ error: 'bad id' });
try {
const [p, credits] = await Promise.all([
pool.query(`SELECT * FROM asim_people WHERE id=$1 LIMIT 1`, [id]),
pool.query(`
SELECT c.role, c.character_name, c.job_title, c.department,
m.id AS movie_id, m.title, m.release_year, m.poster_url, m.poster_source
FROM asim_credits c JOIN asim_movies m ON m.id = c.movie_id
WHERE c.person_id=$1 ORDER BY m.release_year DESC NULLS LAST`, [id])
]);
if (!p.rows[0]) return res.status(404).json({ error: 'not found' });
// Strip premium_extras from public response unless claimed + active
const person = p.rows[0];
if (!person.claimed_at || (person.premium_expires_at && new Date(person.premium_expires_at) < new Date())) {
person.premium_extras = null;
}
res.setHeader('Cache-Control', 'public, max-age=300, stale-while-revalidate=600');
res.json({ person, credits: credits.rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// Live ingest progress — used by the YOLO loop + a tiny status badge on /
app.get('/api/ingest/status', async (_req, res) => {
try {
const stubs = await pool.query(`
SELECT source, status, COUNT(*)::bigint AS n
FROM asim_ingest_stubs GROUP BY source, status ORDER BY source, status
`);
const runs = await pool.query(`
SELECT id, run_kind, source, started_at, finished_at, status, records_in, records_out, notes
FROM asim_ingest_runs ORDER BY started_at DESC LIMIT 10
`);
// Aggregate per-source done/queued/total for the bar chart.
const bySource = {};
for (const r of stubs.rows) {
const s = bySource[r.source] = bySource[r.source] || { source: r.source, queued: 0, done: 0, in_flight: 0, failed: 0, gone: 0, skipped: 0, total: 0 };
s[r.status] = Number(r.n);
s.total += Number(r.n);
}
res.json({
sources: Object.values(bySource).sort((a, b) => b.total - a.total),
recent_runs: runs.rows,
tmdb_key_set: Boolean(process.env.TMDB_API_KEY),
});
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// SPOTTED — public submission form. Users (fans / set decorators / brand
// reps) can flag a product they saw on screen. Inserts with verified_at=NULL
// so admin review is required before it surfaces in /api/spotted. Rate-limit:
// 1 submission per IP per 30 seconds (anti-spam, generous for honest users).
const spottedRateMap = new Map(); // ip → last-submit-timestamp
function spottedRateLimit(ip) {
const last = spottedRateMap.get(ip) || 0;
const now = Date.now();
if (now - last < 30000) return false;
spottedRateMap.set(ip, now);
// Garbage-collect map every ~5min.
if (spottedRateMap.size > 2000) {
const cutoff = now - 60000;
for (const [k, v] of spottedRateMap) if (v < cutoff) spottedRateMap.delete(k);
}
return true;
}
// /spotted — public HTML gallery of verified placements (companion to the
// /spotted/submit form and /api/spotted JSON feed). Filterable by category +
// pageable.
app.get('/spotted', async (req, res) => {
try {
const page = Math.max(1, parseInt(req.query.page || '1', 10));
const limit = 30;
const offset = (page - 1) * limit;
const cat = typeof req.query.category === 'string' && req.query.category.length < 60 ? req.query.category : null;
const params = [];
let where = `s.verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')`;
if (cat) { params.push(cat); where += ` AND s.product_category = $${params.length}`; }
params.push(limit, offset);
const countParams = params.slice(0, params.length - 2);
// All 5 queries are independent — fire them in parallel so the wall
// time = max of any one, not the sum (was 4 awaits sequential ≈ 1.3s
// cold; parallel keeps it bounded by the slowest query).
const [r, totalR, catsR, statsR, topMoviesR] = await Promise.all([
pool.query(`
SELECT s.id, s.product_name, s.product_category, s.brand, s.scene_description,
s.scene_timecode, s.shop_url, s.image_url, s.image_source,
m.id AS movie_id, m.title AS movie_title, m.release_year, m.poster_url, m.poster_source
FROM asim_spotted s JOIN asim_movies m ON m.id=s.movie_id
WHERE ${where}
ORDER BY s.verified_at DESC, s.id DESC
LIMIT $${params.length-1} OFFSET $${params.length}`, params),
pool.query(`SELECT count(*) AS n FROM asim_spotted s WHERE ${where}`, countParams),
pool.query(`SELECT product_category, count(*) AS n FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL GROUP BY 1 ORDER BY 2 DESC`),
pool.query(`
SELECT
(SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS placements,
(SELECT count(DISTINCT movie_id) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS movies,
(SELECT count(DISTINCT NULLIF(TRIM(brand),'')) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS brands,
(SELECT count(DISTINCT product_category) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL) AS categories`),
pool.query(`
SELECT s.movie_id, m.title, m.release_year, m.poster_url, m.poster_source, count(*) AS n
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NOT NULL AND (s.verified_by IS NULL OR s.verified_by NOT LIKE 'rejected%')
GROUP BY s.movie_id, m.title, m.release_year, m.poster_url, m.poster_source
ORDER BY count(*) DESC, m.title
LIMIT 6`)
]);
const total = parseInt(totalR.rows[0].n, 10);
const totalPages = Math.max(1, Math.ceil(total / limit));
const gs = statsR.rows[0] || {};
const topMovies = topMoviesR.rows;
const qs = (overrides = {}) => {
const u = new URLSearchParams();
if (cat) u.set('category', cat);
Object.entries(overrides).forEach(([k,v]) => v == null ? u.delete(k) : u.set(k, v));
return u.toString();
};
const filterBar = `
<nav class="cat-bar">
<a href="/spotted" class="${!cat ? 'active' : ''}">All <span class="muted">${total}</span></a>
${catsR.rows.map(c => `<a href="/spotted?${qs({category: c.product_category, page: null})}" class="${cat===c.product_category?'active':''}">${esc(c.product_category)} <span class="muted">${c.n}</span></a>`).join('')}
</nav>`;
const rows = r.rows.map(s => `
<article class="spot-row">
<a class="spot-poster" href="/movie/${s.movie_id}" aria-label="${esc(s.movie_title)}">
<img src="${esc(s.poster_url && (s.poster_source||'').toLowerCase()!=='made-with-ai' ? s.poster_url : '/img/made-with-ai.svg')}" alt="" loading="lazy">
</a>
<div class="spot-body">
<div class="spot-meta">
<a href="/movie/${s.movie_id}">${esc(s.movie_title)}${s.release_year ? ' <span class="muted">('+s.release_year+')</span>' : ''}</a>
${s.scene_timecode ? '<span class="muted"> · '+esc(s.scene_timecode)+'</span>' : ''}
</div>
<h3 class="spot-title">${esc(s.product_name)}${s.brand ? ' <span class="muted">— '+esc(s.brand)+'</span>' : ''}</h3>
${s.product_category ? '<span class="chip">'+esc(s.product_category)+'</span>' : ''}
${s.scene_description ? '<p class="spot-desc">'+esc(s.scene_description)+'</p>' : ''}
${s.shop_url ? '<a class="shop-btn" href="'+esc(s.shop_url)+'" target="_blank" rel="nofollow noopener noreferrer">Where to find it ↗</a>' : ''}
</div>
</article>`).join('');
const pager = totalPages > 1 ? `<div id="scroll-sentinel" style="padding:20px;text-align:center;color:var(--ink-faint);font-size:13px;"><span id="loading-status">Loading more…</span></div>` : '';
const body = `
<style>
.cat-bar{display:flex;flex-wrap:wrap;gap:6px;margin-bottom:24px;padding-bottom:14px;border-bottom:1px solid var(--line)}
.cat-bar a{padding:6px 14px;border:1px solid var(--line);border-radius:999px;font-size:11.5px;letter-spacing:0.08em;text-transform:uppercase;color:var(--ink-soft);text-decoration:none}
.cat-bar a.active{background:var(--gold);color:#1a1410;border-color:var(--gold);font-weight:600}
.cat-bar a:hover{border-color:var(--gold);color:var(--gold)}
.cat-bar a.active:hover{color:#1a1410}
.cat-bar .muted{color:inherit;opacity:0.55;margin-left:4px}
.spot-row{display:grid;grid-template-columns:120px 1fr;gap:18px;padding:18px 0;border-bottom:1px dotted var(--line)}
@media (max-width:560px){.spot-row{grid-template-columns:90px 1fr;gap:14px}}
.spot-poster{display:block;aspect-ratio:2/3;background:#15130f;border-radius:6px;overflow:hidden;border:1px solid var(--line)}
.spot-poster img{width:100%;height:100%;object-fit:cover;display:block}
.spot-meta{font-size:13px;color:var(--ink-faint);margin-bottom:4px}
.spot-meta a{color:var(--ink);text-decoration:none}
.spot-meta a:hover{color:var(--gold)}
.spot-title{margin:2px 0 8px;font-family:var(--serif);font-weight:400;font-size:20px}
.spot-desc{margin:8px 0 10px;color:var(--ink-soft);font-size:13.5px;line-height:1.55}
.shop-btn{display:inline-block;padding:5px 12px;border:1px solid var(--gold);color:var(--gold);font-size:11.5px;letter-spacing:0.1em;text-transform:uppercase;border-radius:4px;text-decoration:none}
.shop-btn:hover{background:var(--gold);color:#1a1410}
.submit-banner{margin:24px 0;padding:14px 18px;border:1px solid var(--gold);border-radius:8px;background:rgba(201,161,75,0.05);display:flex;justify-content:space-between;align-items:center;gap:14px;flex-wrap:wrap}
.submit-banner a{padding:7px 18px;background:var(--gold);color:#1a1410;border-radius:5px;font-size:11.5px;letter-spacing:0.12em;text-transform:uppercase;text-decoration:none;font-weight:600}
.submit-banner a:hover{filter:brightness(1.1)}
.pager{display:flex;gap:10px;align-items:center;justify-content:center;margin:32px 0}
.sp-stats{display:grid;grid-template-columns:repeat(4,1fr);gap:10px;margin:18px 0 22px}
.sp-stats .stat{padding:13px 14px;border:1px solid var(--line);border-radius:6px;background:rgba(255,255,255,0.02);display:flex;flex-direction:column;gap:2px}
.sp-stats .num{font-family:var(--serif);font-size:30px;font-weight:500;color:var(--gold);line-height:1.0}
.sp-stats .lbl{font-size:10.5px;letter-spacing:0.1em;text-transform:uppercase;color:var(--ink-faint)}
@media (max-width:560px){.sp-stats{grid-template-columns:repeat(2,1fr)}}
.top-films{margin:0 0 28px}
.top-films h2{margin:0 0 12px;font-family:var(--serif);font-size:15px;letter-spacing:0.16em;text-transform:uppercase;color:var(--ink-faint);font-weight:400}
.top-films .rail{display:grid;grid-template-columns:repeat(6,1fr);gap:10px}
@media (max-width:900px){.top-films .rail{grid-template-columns:repeat(3,1fr)}}
@media (max-width:540px){.top-films .rail{grid-template-columns:repeat(2,1fr)}}
.top-films .tf-card{display:block;text-decoration:none;color:var(--ink);position:relative;border:1px solid var(--line);border-radius:6px;overflow:hidden;transition:transform .15s,border-color .15s}
.top-films .tf-card:hover{transform:translateY(-2px);border-color:var(--gold)}
.top-films .tf-poster{aspect-ratio:2/3;background:#15130f no-repeat center/cover;position:relative;overflow:hidden}
.top-films .tf-poster::after{content:"";position:absolute;inset:0;background-image:var(--card-bg,none);background-repeat:repeat;background-size:33.33% 33.33%;background-position:center;opacity:0;transition:opacity 360ms ease;pointer-events:none;will-change:opacity}
.top-films .tf-card:hover .tf-poster::after,.top-films .tf-card:focus-visible .tf-poster::after{opacity:1}
@media (prefers-reduced-motion:reduce){.top-films .tf-poster::after{transition:none}}
.top-films .tf-meta{padding:6px 8px;font-size:11.5px;line-height:1.3;background:rgba(0,0,0,0.4)}
.top-films .tf-title{display:-webkit-box;-webkit-line-clamp:1;-webkit-box-orient:vertical;overflow:hidden}
.top-films .tf-count{display:inline-block;position:absolute;top:6px;right:6px;background:var(--gold);color:#1a1410;font-size:10.5px;letter-spacing:0.08em;font-weight:600;padding:2px 7px;border-radius:999px}
</style>
<h1 class="title">SPOTTED</h1>
<p class="meta-line">${total.toLocaleString()} verified product placements — set decor, wallcoverings, lighting, props.</p>
<div class="sp-stats">
<div class="stat"><span class="num">${Number(gs.placements||0).toLocaleString()}</span><span class="lbl">placements</span></div>
<div class="stat"><span class="num">${Number(gs.movies||0).toLocaleString()}</span><span class="lbl">films</span></div>
<div class="stat"><span class="num">${Number(gs.brands||0).toLocaleString()}</span><span class="lbl">brands</span></div>
<div class="stat"><span class="num">${Number(gs.categories||0).toLocaleString()}</span><span class="lbl">categories</span></div>
</div>
<div class="submit-banner">
<span>Saw something on screen that we don't have yet?</span>
<a href="/spotted/submit">Submit a placement →</a>
</div>
${topMovies.length > 1 ? `
<section class="top-films">
<h2>Most-Spotted Films</h2>
<div class="rail">
${topMovies.map(m => `
<a class="tf-card" href="/movie/${m.movie_id}" aria-label="${esc(m.title)}">
<div class="tf-poster" style="background-image:url('${esc((m.poster_source||'').toLowerCase()!=='made-with-ai' && m.poster_url ? m.poster_url : '/img/made-with-ai.svg')}');--card-bg:url('${esc((m.poster_source||'').toLowerCase()!=='made-with-ai' && m.poster_url ? m.poster_url : '/img/made-with-ai.svg')}')"></div>
<span class="tf-count">${m.n}</span>
<div class="tf-meta"><div class="tf-title">${esc(m.title)}</div><div class="muted" style="font-size:10.5px">${m.release_year || ''}</div></div>
</a>`).join('')}
</div>
</section>` : ''}
${filterBar}
${rows || '<p class="empty">No verified placements in this category yet.</p>'}
${pager}
<script>
(function(){
const grid = document.querySelector('main');
const sentinel = document.getElementById('scroll-sentinel');
if (!grid || !sentinel || !('IntersectionObserver' in window)) return;
let currentPage = ${page};
let totalPages = ${totalPages};
let isLoading = false;
const scrollObserver = new IntersectionObserver(entries => {
for (const e of entries) {
if (e.isIntersecting && currentPage < totalPages && !isLoading) {
loadNextPage();
}
}
}, { rootMargin: '400px 0px' });
scrollObserver.observe(sentinel);
function loadNextPage() {
if (isLoading || currentPage >= totalPages) return;
isLoading = true;
const nextPage = currentPage + 1;
const url = new URL(location.href);
url.searchParams.set('page', nextPage);
fetch(url.toString())
.then(r => r.text())
.then(html => {
const parser = new DOMParser();
const doc = parser.parseFromString(html, 'text/html');
const nextRows = doc.querySelectorAll('main > .spot-row');
if (nextRows.length === 0) {
currentPage = totalPages;
document.getElementById('loading-status').textContent = 'All placements loaded';
sentinel.style.color = '#999';
return;
}
const main = document.querySelector('main');
const lastRow = main.querySelector('.spot-row:last-of-type');
nextRows.forEach(row => {
lastRow.parentNode.insertBefore(row.cloneNode(true), sentinel);
});
currentPage = nextPage;
if (currentPage >= totalPages) {
document.getElementById('loading-status').textContent = 'All placements loaded';
sentinel.style.color = '#999';
} else {
document.getElementById('loading-status').textContent = 'Loading more…';
}
isLoading = false;
})
.catch(err => {
console.error('Failed to load more:', err);
document.getElementById('loading-status').textContent = 'Error loading more';
sentinel.style.color = '#d84315';
isLoading = false;
});
}
})();
</script>`;
// ItemList schema — rich snippet candidates for the gallery page.
// Each Product item links back to its parent movie page; SearchAction
// would be redundant since the site WebSite schema covers it.
const itemList = {
'@context': 'https://schema.org',
'@type': 'ItemList',
itemListElement: r.rows.map((s, i) => ({
'@type': 'ListItem',
position: offset + i + 1,
item: {
'@type': 'Product',
name: s.product_name,
...(s.brand ? { brand: { '@type': 'Brand', name: s.brand } } : {}),
...(s.product_category ? { category: s.product_category } : {}),
url: `https://asseeninmovies.com/movie/${s.movie_id}#spotted-${s.id}`,
},
})),
numberOfItems: total,
};
const headExtras = `<script type="application/ld+json">${JSON.stringify(itemList)}</script>`;
res.type('html').send(htmlShell(cat ? 'Spotted · ' + cat : 'Spotted', body, headExtras, 'https://asseeninmovies.com/spotted'));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
app.get('/spotted/submit', async (req, res) => {
const movieId = parseInt(req.query.movie_id || '0', 10);
let movieTitle = '', movieYear = '';
if (Number.isFinite(movieId) && movieId > 0) {
try {
const r = await pool.query(`SELECT title, release_year FROM asim_movies WHERE id=$1 LIMIT 1`, [movieId]);
if (r.rows[0]) { movieTitle = r.rows[0].title; movieYear = r.rows[0].release_year || ''; }
} catch (_e) { /* fall through */ }
}
const body = `
<style>
.form-stack{max-width:640px;margin:0 auto;display:flex;flex-direction:column;gap:14px}
.form-stack label{display:flex;flex-direction:column;gap:5px;font-size:12px;letter-spacing:0.12em;text-transform:uppercase;color:var(--ink-faint)}
.form-stack input,.form-stack textarea,.form-stack select{background:transparent;border:1px solid var(--line);color:var(--ink);padding:10px 12px;border-radius:6px;font:inherit;font-size:14.5px;text-transform:none;letter-spacing:normal}
.form-stack textarea{min-height:84px;resize:vertical}
.form-stack .row{display:grid;grid-template-columns:2fr 1fr;gap:14px}
@media (max-width:560px){.form-stack .row{grid-template-columns:1fr}}
.submit-btn{background:var(--gold);color:#1a1410;border:0;padding:14px 28px;border-radius:6px;font:inherit;font-size:14px;letter-spacing:0.12em;text-transform:uppercase;cursor:pointer;font-weight:600;margin-top:8px}
.submit-btn:hover{filter:brightness(1.1)}
.submit-btn:disabled{opacity:0.4;cursor:wait}
.form-help{color:var(--ink-faint);font-size:12px;margin-top:-2px}
.form-result{margin-top:18px;padding:14px 18px;border-radius:6px;border:1px solid var(--gold);background:rgba(201,161,75,0.07)}
.form-result.bad{border-color:#a33;background:rgba(170,50,50,0.07);color:#fbb}
.ms-search{position:relative}
.ms-results{position:absolute;top:calc(100% + 4px);left:0;right:0;background:var(--card);border:1px solid var(--line);border-radius:6px;max-height:240px;overflow:auto;z-index:5;display:none}
.ms-results.open{display:block}
.ms-results a{display:block;padding:8px 12px;color:var(--ink);font-size:13px;border-bottom:1px solid var(--line);text-decoration:none}
.ms-results a:hover{background:rgba(201,161,75,0.08);color:var(--gold)}
</style>
<h1 class="title">Submit a SPOTTED placement</h1>
<p class="meta-line">Saw a product on screen you can identify? Submit it for the community.</p>
<form id="spotted-form" class="form-stack" autocomplete="off" novalidate>
<label>
Movie or TV episode
<div class="ms-search">
<input id="movie-search" name="movie_search" type="text" placeholder="Type to search…" value="${esc(movieTitle ? movieTitle + (movieYear ? ' (' + movieYear + ')' : '') : '')}" required>
<input id="movie-id" name="movie_id" type="hidden" value="${esc(movieId || '')}">
<div id="movie-results" class="ms-results"></div>
</div>
<span class="form-help">Required. Start typing and pick from the dropdown.</span>
</label>
<div class="row">
<label>
Product name
<input name="product_name" type="text" placeholder="e.g. Brass arc floor lamp" required maxlength="160">
</label>
<label>
Category
<select name="product_category">
<option value="">— pick one —</option>
<option>Wallcovering</option>
<option>Lamp / Lighting</option>
<option>Furniture</option>
<option>Rug / Floor</option>
<option>Window treatment</option>
<option>Art / Wall décor</option>
<option>Accessories</option>
<option>Wardrobe</option>
<option>Tabletop / Kitchen</option>
<option>Other</option>
</select>
</label>
</div>
<div class="row">
<label>
Brand
<input name="brand" type="text" placeholder="e.g. Schoolhouse" maxlength="120">
</label>
<label>
Scene timecode
<input name="scene_timecode" type="text" placeholder="e.g. 0:42:13" maxlength="14">
</label>
</div>
<label>
Scene description
<textarea name="scene_description" placeholder="Where in the film does this appear? e.g. \\"Living room scene when Eve confronts Marlene, ~42 min in.\\"" maxlength="500"></textarea>
</label>
<label>
Where to buy (URL)
<input name="shop_url" type="url" placeholder="https://…" maxlength="500">
</label>
<div class="row">
<label>
Your email (optional)
<input name="submitted_by_email" type="email" placeholder="you@example.com" maxlength="160">
<span class="form-help">Only used to credit you if verified. Never shown publicly.</span>
</label>
<label>
Your role
<select name="submitted_by_role">
<option value="fan">Fan</option>
<option value="set-decorator">Set decorator</option>
<option value="brand-rep">Brand representative</option>
<option value="other">Other</option>
</select>
</label>
</div>
<button type="submit" class="submit-btn">Submit for review →</button>
<div id="form-result" class="form-result" style="display:none"></div>
</form>
<script>
(function(){
var ms = document.getElementById('movie-search');
var mid = document.getElementById('movie-id');
var box = document.getElementById('movie-results');
var debounce = null;
function clearResults(){ box.innerHTML=''; box.classList.remove('open'); }
function pickMovie(id, title, year){ mid.value = id; ms.value = title + (year ? ' ('+year+')' : ''); clearResults(); }
function searchMovies(q){
fetch('/api/search?q=' + encodeURIComponent(q)).then(function(r){return r.json()}).then(function(j){
var movies = (j && j.movies) ? j.movies.slice(0,8) : [];
if (!movies.length){ clearResults(); return; }
box.innerHTML = movies.map(function(m){
var safeTitle = (m.title||'').replace(/[<>&"]/g, function(c){return {'<':'<','>':'>','&':'&','"':'"'}[c]});
return '<a href="#" data-id="'+m.id+'" data-title="'+safeTitle+'" data-year="'+(m.release_year||'')+'">' + safeTitle + ' <span style="color:var(--ink-faint);font-size:11px">'+(m.release_year||'')+'</span></a>';
}).join('');
box.classList.add('open');
box.querySelectorAll('a').forEach(function(a){
a.addEventListener('click', function(e){
e.preventDefault();
pickMovie(a.getAttribute('data-id'), a.getAttribute('data-title'), a.getAttribute('data-year'));
});
});
}).catch(clearResults);
}
ms.addEventListener('input', function(){
mid.value = ''; // clear bound id when user types again
clearTimeout(debounce);
var q = ms.value.trim();
if (q.length < 2) { clearResults(); return; }
debounce = setTimeout(function(){ searchMovies(q); }, 200);
});
document.addEventListener('click', function(e){
if (!box.contains(e.target) && e.target !== ms) clearResults();
});
var form = document.getElementById('spotted-form');
var result = document.getElementById('form-result');
form.addEventListener('submit', function(e){
e.preventDefault();
result.style.display='none';
var fd = new FormData(form);
var data = {};
fd.forEach(function(v,k){ data[k] = v; });
if (!data.movie_id) { result.className='form-result bad'; result.style.display='block'; result.textContent='Pick a movie from the dropdown.'; return; }
if (!data.product_name) { result.className='form-result bad'; result.style.display='block'; result.textContent='Product name required.'; return; }
var btn = form.querySelector('button[type=submit]');
btn.disabled = true; btn.textContent = 'Submitting…';
fetch('/api/spotted/submit', {method:'POST', headers:{'Content-Type':'application/json'}, body: JSON.stringify(data)})
.then(function(r){ return r.json().then(function(j){ return {ok:r.ok, body:j} }) })
.then(function(x){
btn.disabled=false; btn.textContent='Submit for review →';
if (x.ok) {
result.className='form-result'; result.style.display='block';
result.innerHTML='<strong>Thanks!</strong> Submission #'+x.body.id+' is queued for review. We\\'ll cross-check it against the set-decorator credits and verify within 48h.';
form.reset();
} else {
result.className='form-result bad'; result.style.display='block';
result.textContent = x.body.error || 'Submission failed. Please try again.';
}
})
.catch(function(err){
btn.disabled=false; btn.textContent='Submit for review →';
result.className='form-result bad'; result.style.display='block';
result.textContent='Network error. Please try again.';
});
});
})();
</script>
`;
res.type('html').send(htmlShell('Submit a placement', body, '', 'https://asseeninmovies.com/spotted/submit'));
});
app.post('/api/spotted/submit', async (req, res) => {
const ip = req.headers['x-forwarded-for']?.split(',')[0]?.trim() || req.ip || 'unknown';
if (!spottedRateLimit(ip)) return res.status(429).json({ error: 'Too many submissions — wait 30 seconds before retrying.' });
const b = req.body || {};
const movieId = parseInt(b.movie_id, 10);
if (!Number.isFinite(movieId) || movieId < 1) return res.status(400).json({ error: 'movie_id required' });
const productName = String(b.product_name || '').trim();
if (!productName || productName.length < 2 || productName.length > 160) return res.status(400).json({ error: 'product_name must be 2–160 chars' });
// Bound all free-text fields.
const cat = String(b.product_category || '').slice(0, 60) || null;
const brand = String(b.brand || '').slice(0, 120) || null;
const desc = String(b.scene_description || '').slice(0, 500) || null;
const tc = String(b.scene_timecode || '').slice(0, 14) || null;
const url = String(b.shop_url || '').slice(0, 500) || null;
const em = String(b.submitted_by_email || '').slice(0, 160) || null;
const role = ['fan','set-decorator','brand-rep','other'].includes(b.submitted_by_role) ? b.submitted_by_role : 'fan';
if (url && !/^https?:\/\//i.test(url)) return res.status(400).json({ error: 'shop_url must start with http(s)://' });
if (em && !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(em)) return res.status(400).json({ error: 'submitted_by_email looks invalid' });
try {
// Confirm the movie_id exists (else FK would error anyway, but a friendlier
// 404 helps).
const m = await pool.query(`SELECT id FROM asim_movies WHERE id=$1 LIMIT 1`, [movieId]);
if (!m.rows[0]) return res.status(404).json({ error: 'movie not found' });
const r = await pool.query(`
INSERT INTO asim_spotted
(movie_id, product_name, product_category, brand, scene_description,
scene_timecode, shop_url, submitted_by_email, submitted_by_role,
image_source)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'submitted')
RETURNING id`,
[movieId, productName, cat, brand, desc, tc, url, em, role]);
res.json({ ok: true, id: r.rows[0].id });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// SPOTTED feed — most recent verified placements across all films
app.get('/api/spotted', async (req, res) => {
const limit = Math.min(parseInt(req.query.limit, 10) || 60, 200);
const cat = req.query.category;
try {
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
const params = [];
let where = `WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')`;
if (cat) { params.push(cat); where += ` AND product_category = $${params.length}`; }
params.push(limit);
const r = await pool.query(`
SELECT s.*, m.title AS movie_title, m.release_year, m.poster_url
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
${where}
ORDER BY verified_at DESC LIMIT $${params.length}`, params);
res.json({ items: r.rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// JSON stats — same numbers the /spotted gallery + /spotted/queue strips
// render, exposed as a single small API. Useful for the status-loop tail
// monitor and any external dashboard. No auth needed — public counts only.
// /api/movies/featured + /api/people/featured — return N (default 6, max 24)
// random poster/headshot-having rows. Used by widgets that want a small grid.
app.get('/api/movies/featured', async (req, res) => {
try {
const n = Math.min(Math.max(parseInt(req.query.n, 10) || 6, 1), 24);
const r = await pool.query(`SELECT id, title, release_year, poster_url FROM asim_movies WHERE poster_url IS NOT NULL ORDER BY random() LIMIT ${n}`);
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ movies: r.rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/people/featured', async (req, res) => {
try {
const n = Math.min(Math.max(parseInt(req.query.n, 10) || 6, 1), 24);
const r = await pool.query(`SELECT id, full_name, headshot_url, primary_profession FROM asim_people WHERE headshot_url IS NOT NULL ORDER BY random() LIMIT ${n}`);
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ people: r.rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// /api/movies/random + /api/people/random — JSON companions to the /random/*
// redirects. Lightweight, 60s cache. Used by widgets that want a row inline.
app.get('/api/movies/random', async (_req, res) => {
try {
const r = await pool.query(`SELECT id, title, release_year, poster_url FROM asim_movies WHERE poster_url IS NOT NULL ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.status(404).json({ error: 'no-poster-yet' });
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ movie: r.rows[0] });
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/people/random', async (_req, res) => {
try {
const r = await pool.query(`SELECT id, full_name, headshot_url, primary_profession FROM asim_people WHERE headshot_url IS NOT NULL ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.status(404).json({ error: 'no-headshot-yet' });
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ person: r.rows[0] });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// /api/spotted/random — JSON companion to /random/movie /random/person.
// Pulls a single verified SPOTTED row at random. Useful for widgets that
// want to feature "a placement from the catalog" inline. 60s cache.
app.get('/api/spotted/random', async (_req, res) => {
try {
const r = await pool.query(`
SELECT s.id, s.product_name, s.product_category, s.brand,
s.scene_description, s.scene_timecode, s.shop_url,
m.id AS movie_id, m.title AS movie_title, m.release_year, m.poster_url
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NOT NULL AND (s.verified_by IS NULL OR s.verified_by NOT LIKE 'rejected%')
ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.status(404).json({ error: 'no-spotted-yet' });
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ spotted: r.rows[0] });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// /api/spotted/featured — N (default 6, max 24) verified spotted rows with
// movie context, randomly sampled. Multi-row companion to /api/spotted/random.
// Used by widgets that want a small grid inline. 60s cache + 120s SWR.
app.get('/api/spotted/featured', async (req, res) => {
try {
const n = Math.min(Math.max(parseInt(req.query.n, 10) || 6, 1), 24);
const r = await pool.query(`
SELECT s.id, s.product_name, s.product_category, s.brand,
s.scene_description, s.scene_timecode, s.shop_url,
m.id AS movie_id, m.title AS movie_title, m.release_year, m.poster_url
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NOT NULL AND (s.verified_by IS NULL OR s.verified_by NOT LIKE 'rejected%')
ORDER BY random() LIMIT $1`, [n]);
res.setHeader('Cache-Control', 'public, max-age=60, stale-while-revalidate=120');
res.json({ spotted: r.rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// /api/credits/stats — aggregate counts on asim_credits. Departments, actors
// vs crew, top contributors. Cached 5 min (363k rows is heavy to recount).
//
// Note: department field is largely empty in current data (TMDB ingest
// populates job_title and role, but department only on directing
// credits). Expect acting=0 and writing=0 until a backfill ticks the
// department column. Tracked as future enrichment work.
app.get('/api/credits/stats', async (_req, res) => {
try {
res.setHeader('Cache-Control', 'public, max-age=300, stale-while-revalidate=600');
const r = await pool.query(`
SELECT
(SELECT count(*) FROM asim_credits) AS total,
(SELECT count(*) FROM asim_credits WHERE department='Acting') AS acting,
(SELECT count(*) FROM asim_credits WHERE department='Directing') AS directing,
(SELECT count(*) FROM asim_credits WHERE department='Writing') AS writing,
(SELECT count(DISTINCT person_id) FROM asim_credits) AS distinct_people,
(SELECT count(DISTINCT movie_id) FROM asim_credits) AS distinct_movies`);
const row = r.rows[0] || {};
res.json({
total: Number(row.total || 0),
acting: Number(row.acting || 0),
directing: Number(row.directing || 0),
writing: Number(row.writing || 0),
distinct_people: Number(row.distinct_people || 0),
distinct_movies: Number(row.distinct_movies || 0),
as_of: new Date().toISOString(),
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/spotted/stats', async (_req, res) => {
// Short Cache-Control — stats don't change second-to-second. 30s lets
// crawlers/dashboards/status-loop poll without hammering pg.
res.setHeader('Cache-Control', 'public, max-age=30, stale-while-revalidate=60');
try {
const r = await pool.query(`
SELECT
(SELECT count(*) FROM asim_spotted WHERE verified_at IS NULL) AS pending_total,
(SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS live_total,
(SELECT count(*) FROM asim_spotted WHERE verified_by LIKE 'rejected%') AS rejected_total,
(SELECT count(*) FROM asim_spotted WHERE created_at >= NOW() - INTERVAL '7 days') AS submitted_7d,
(SELECT count(DISTINCT movie_id) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS films,
(SELECT count(DISTINCT NULLIF(TRIM(brand),'')) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS brands,
(SELECT count(DISTINCT product_category) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL) AS categories`);
const row = r.rows[0] || {};
res.json({
pending_total: Number(row.pending_total || 0),
live_total: Number(row.live_total || 0),
rejected_total:Number(row.rejected_total || 0),
submitted_7d: Number(row.submitted_7d || 0),
films: Number(row.films || 0),
brands: Number(row.brands || 0),
categories: Number(row.categories || 0),
as_of: new Date().toISOString(),
});
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// ────────────────────────────────────────────────────────────────────
// /spotted/queue — admin moderation for pending user submissions.
// Token-gated (ASIM_ADMIN_TOKEN env or ephemeral boot-token printed once).
// Reversible: approve sets verified_at=NOW, reject sets verified_at and
// stamps verified_by='rejected:<reason>' so the row is filtered out of
// the public gallery WITHOUT being deleted — undoable from psql.
// ────────────────────────────────────────────────────────────────────
// /spotted/queue.csv — admin CSV export of pending submissions. Same gate
// as /spotted/queue. Useful for moderators who want to review offline.
app.get('/spotted/queue.csv', async (req, res) => {
res.setHeader('Cache-Control', 'no-store, must-revalidate');
if (!isAdmin(req)) return res.status(401).type('text/plain').send('admin token required');
try {
const r = await pool.query(`
SELECT s.id, s.product_name, s.product_category, s.brand, s.scene_timecode,
s.scene_description, s.shop_url, s.submitted_by_email, s.submitted_by_role,
s.created_at, m.title AS movie_title, m.release_year
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NULL ORDER BY s.created_at ASC`);
const escapeCsv = v => v == null ? '' : '"' + String(v).replace(/"/g, '""') + '"';
const cols = ['id','movie_title','release_year','product_name','product_category','brand','scene_timecode','scene_description','shop_url','submitted_by_email','submitted_by_role','created_at'];
const out = [cols.join(',')];
for (const row of r.rows) {
out.push(cols.map(c => escapeCsv(row[c])).join(','));
}
res.setHeader('Content-Disposition', `attachment; filename="spotted-queue-${new Date().toISOString().slice(0,10)}.csv"`);
res.type('text/csv').send(out.join('\n'));
} catch (e) {
res.status(500).type('text/plain').send(`error: ${e.message}`);
}
});
app.get('/spotted/queue', async (req, res) => {
// Admin pages must never sit in any cache (shared or browser) — auth state
// is token-gated and stale snapshots could leak privileged data.
res.setHeader('Cache-Control', 'no-store, must-revalidate');
if (!isAdmin(req)) return res.status(401).type('html').send(htmlShell('Admin', `<div class="empty"><p>Admin token required. Append <code>?token=<ASIM_ADMIN_TOKEN></code> to the URL.</p></div>`));
try {
const [r, sres] = await Promise.all([
pool.query(`
SELECT s.*, m.title AS movie_title, m.release_year
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NULL
ORDER BY s.created_at DESC LIMIT 200`),
pool.query(`
SELECT
(SELECT COUNT(*) FROM asim_spotted WHERE verified_at IS NULL) AS pending_total,
(SELECT COUNT(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS approved_total,
(SELECT COUNT(*) FROM asim_spotted WHERE verified_by LIKE 'rejected%') AS rejected_total,
(SELECT COUNT(*) FROM asim_spotted WHERE created_at >= NOW() - INTERVAL '7 days') AS submitted_7d`)
]);
const stats = sres.rows[0] || {};
const t = req.query.token;
const rows = r.rows.map(s => `
<article class="queue-row" id="row-${s.id}">
<header>
<span class="qid">#${s.id}</span>
<a class="movie-link" href="/movie/${s.movie_id}" target="_blank" rel="noopener noreferrer">${esc(s.movie_title || '')} (${esc(String(s.release_year || '?'))})</a>
<time>${esc(new Date(s.created_at).toISOString().slice(0,16).replace('T',' '))} UTC</time>
</header>
<div class="qbody">
<div class="qfields">
<div><strong>Product:</strong> ${esc(s.product_name || '')}</div>
${s.brand ? `<div><strong>Brand:</strong> ${esc(s.brand)}</div>` : ''}
${s.product_category ? `<div><strong>Category:</strong> ${esc(s.product_category)}</div>` : ''}
${s.scene_timecode ? `<div><strong>Timecode:</strong> ${esc(s.scene_timecode)}</div>` : ''}
${s.scene_description ? `<div><strong>Scene:</strong> ${esc(s.scene_description)}</div>` : ''}
${s.shop_url ? `<div><strong>Shop:</strong> <a href="${esc(s.shop_url)}" rel="nofollow noopener noreferrer" target="_blank">${esc(s.shop_url.slice(0,80))}</a></div>` : ''}
<div class="submitter">
<strong>By:</strong> ${esc(s.submitted_by_email || '(anon)')} · ${esc(s.submitted_by_role || 'fan')}
</div>
</div>
<div class="qactions">
<button class="btn-approve" data-id="${s.id}">Approve</button>
<button class="btn-reject" data-id="${s.id}">Reject</button>
</div>
</div>
</article>`).join('');
const body = `
<main class="container queue-page">
<h1>Spotted · Moderation Queue</h1>
<div class="stats-strip">
<div class="stat"><span class="num">${stats.pending_total ?? 0}</span><span class="lbl">pending</span></div>
<div class="stat"><span class="num">${stats.approved_total ?? 0}</span><span class="lbl">live</span></div>
<div class="stat"><span class="num">${stats.rejected_total ?? 0}</span><span class="lbl">rejected</span></div>
<div class="stat"><span class="num">${stats.submitted_7d ?? 0}</span><span class="lbl">submitted · 7d</span></div>
</div>
<p class="hint">Showing up to 200 oldest-first. Approve publishes to /spotted; reject is reversible (clear <code>verified_by</code> in psql to undo).</p>
<section class="queue-list">${rows || '<div class="empty">Queue is empty — no pending submissions.</div>'}</section>
</main>
<style>
.queue-page { max-width: 980px; padding-top: 28px; }
.queue-page h1 { font-family: 'Cormorant Garamond', serif; font-size: 38px; margin: 0 0 14px; }
.queue-page .stats-strip { display: grid; grid-template-columns: repeat(4, 1fr); gap: 10px; margin: 0 0 18px; }
.queue-page .stat { padding: 12px 14px; border: 1px solid rgba(0,0,0,0.10); border-radius: 6px; background: rgba(255,255,255,0.5); display: flex; flex-direction: column; gap: 2px; }
.queue-page .stat .num { font-family: 'Cormorant Garamond', serif; font-size: 30px; font-weight: 600; color: #B58A50; line-height: 1.0; }
.queue-page .stat .lbl { font-size: 11px; letter-spacing: 0.08em; text-transform: uppercase; color: var(--ink-mute, #777); }
@media (max-width: 640px) { .queue-page .stats-strip { grid-template-columns: repeat(2, 1fr); } }
.queue-page .hint { color: var(--ink-mute, #777); margin: 0 0 24px; font-size: 14px; }
.queue-page .hint code { background: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-size: 12px; }
.queue-row { border: 1px solid rgba(0,0,0,0.12); border-radius: 6px; padding: 16px 18px; margin: 0 0 14px; background: rgba(255,255,255,0.6); }
.queue-row header { display: flex; gap: 14px; align-items: baseline; margin-bottom: 10px; font-size: 13px; }
.queue-row .qid { font-weight: 600; color: #B58A50; }
.queue-row .movie-link { color: inherit; text-decoration: none; border-bottom: 1px solid rgba(0,0,0,0.2); }
.queue-row time { margin-left: auto; color: var(--ink-mute, #777); }
.queue-row .qbody { display: grid; grid-template-columns: 1fr 160px; gap: 18px; }
.queue-row .qfields div { margin: 3px 0; font-size: 14px; line-height: 1.45; }
.queue-row .submitter { margin-top: 8px; padding-top: 8px; border-top: 1px dashed rgba(0,0,0,0.08); color: var(--ink-mute, #777); font-size: 12px; }
.queue-row .qactions { display: flex; flex-direction: column; gap: 8px; }
.queue-row button { padding: 9px 14px; border: 0; border-radius: 4px; font-weight: 600; font-size: 13px; cursor: pointer; font-family: inherit; }
.queue-row .btn-approve { background: #2D7A3F; color: #fff; }
.queue-row .btn-reject { background: #B23A3A; color: #fff; }
.queue-row.gone { opacity: 0.35; pointer-events: none; }
</style>
<script>
(function(){
const TOKEN = ${JSON.stringify(typeof t === 'string' ? t : '')};
async function act(action, id){
const row = document.getElementById('row-'+id);
const r = await fetch('/spotted/queue/'+id+'/'+action, {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'x-admin-token': TOKEN },
body: JSON.stringify({})
});
if (r.ok) { row.classList.add('gone'); row.querySelector('.qactions').innerHTML = '<span style="font-size:13px;color:#666">'+action+'d</span>'; }
else { const j = await r.json().catch(()=>({error:'err'})); alert('Failed: '+(j.error||r.status)); }
}
document.querySelectorAll('.btn-approve').forEach(b => b.onclick = () => act('approve', b.dataset.id));
document.querySelectorAll('.btn-reject').forEach(b => b.onclick = () => act('reject', b.dataset.id));
})();
</script>`;
res.type('html').send(htmlShell('Moderation Queue', body));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
app.post('/spotted/queue/:id/approve', async (req, res) => {
if (!isAdmin(req)) return res.status(401).json({ error: 'admin token required' });
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id)) return res.status(400).json({ error: 'bad id' });
try {
const r = await pool.query(`
UPDATE asim_spotted
SET verified_at = NOW(), verified_by = COALESCE(verified_by, 'admin-queue'), updated_at = NOW()
WHERE id = $1 AND verified_at IS NULL
RETURNING id`, [id]);
if (!r.rows[0]) return res.status(404).json({ error: 'not found or already verified' });
res.json({ ok: true, id });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.post('/spotted/queue/:id/reject', async (req, res) => {
if (!isAdmin(req)) return res.status(401).json({ error: 'admin token required' });
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id)) return res.status(400).json({ error: 'bad id' });
try {
// Reject = mark verified_at + verified_by='rejected'. Row stays in DB
// (undoable by clearing verified_by). Public gallery filters out
// verified_by LIKE 'rejected%' so it never shows.
const r = await pool.query(`
UPDATE asim_spotted
SET verified_at = NOW(), verified_by = 'rejected', updated_at = NOW()
WHERE id = $1 AND verified_at IS NULL
RETURNING id`, [id]);
if (!r.rows[0]) return res.status(404).json({ error: 'not found or already processed' });
res.json({ ok: true, id });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// ────────────────────────────────────────────────────────────────────
// HTML PAGES — /movie/:id + /person/:id + helpers. Built so Steve can
// actually browse the data, not just hit /api/* JSON. Dark luxe-magazine
// aesthetic (cream/gold/dark-ink, Cormorant Garamond + Inter), with
// poster/headshot falling back to /img/made-with-ai.svg whenever the
// source isn't rights-cleared. TMDB attribution mandatory on every page.
// ────────────────────────────────────────────────────────────────────
const esc = (s) => String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]));
const posterFor = (m) => (m && m.poster_url && (m.poster_source || '').toLowerCase() !== 'made-with-ai') ? m.poster_url : '/img/made-with-ai.svg';
const headshotFor = (p) => (p && p.headshot_url && (p.headshot_source || '').toLowerCase() !== 'made-with-ai') ? p.headshot_url : '/img/made-with-ai.svg';
// Per Steve's fan-art credit policy (memory: feedback_fan_art_credit_policy):
// every non-PD image must render an artist + license + source-URL line.
// CC0 / PD images get a "Public domain" pill with source link.
// AI-placeholder gets the existing "made with AI" pill.
function imageCreditHtml(credit, license, sourceUrl, sourceKind) {
if (!sourceUrl && sourceKind === 'made-with-ai') return '<span class="ai-pill">made with AI</span>';
if (!sourceUrl) return '';
const lic = (license || '').toLowerCase();
const isPd = lic === 'cc0' || lic === 'cc0-1.0' || lic.includes('public domain') || lic === 'pd';
if (isPd) {
return `<span class="img-credit pd">Public domain · <a href="${esc(sourceUrl)}" rel="nofollow noopener noreferrer" target="_blank">source</a></span>`;
}
const artistPart = credit ? `${esc(credit)} / ` : '';
const licPart = license ? `${esc(license)} / ` : '';
return `<span class="img-credit">Photo: ${artistPart}${licPart}<a href="${esc(sourceUrl)}" rel="nofollow noopener noreferrer" target="_blank">source</a></span>`;
}
function htmlShell(title, body, headExtras = '', canonical = '', metaDesc = '') {
const fullTitle = `${title} · AsSeenInMovies`;
// Default fallback meta description if the caller didn't pass one and
// didn't inject one via headExtras. Route-level callers should override
// for stronger SEO; this guarantees no page ships with zero description.
const desc = metaDesc || `${title} — film + TV reference on AsSeenInMovies. 290k+ movies, 61k+ people, verified SPOTTED set decor.`;
const hasOgInExtras = /property="og:title"/.test(headExtras);
return `<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><title>${esc(fullTitle)}</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="icon" type="image/svg+xml" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 32 32'%3E%3Crect width='32' height='32' rx='6' fill='%230d0c0a'/%3E%3Ctext x='16' y='22' font-family='Georgia,serif' font-style='italic' font-size='20' fill='%23c9a14b' text-anchor='middle'%3EA%3C/text%3E%3C/svg%3E">
<meta name="color-scheme" content="dark">
<meta name="theme-color" content="#0d0c0a">
${canonical ? `<link rel="canonical" href="${esc(canonical)}">` : ''}
${hasOgInExtras ? '' : `<meta name="description" content="${esc(desc)}">
<meta property="og:type" content="website">
<meta property="og:title" content="${esc(fullTitle)}">
<meta property="og:description" content="${esc(desc)}">
${canonical ? `<meta property="og:url" content="${esc(canonical)}">` : ''}
<meta property="og:image" content="https://asseeninmovies.com/img/made-with-ai.svg">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://asseeninmovies.com/img/made-with-ai.svg">`}
${headExtras}
${process.env.GA_ID ? `<script async src="https://www.googletagmanager.com/gtag/js?id=${process.env.GA_ID}"></script>
<script>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments);}gtag('js',new Date());gtag('config','${process.env.GA_ID}');</script>` : ''}
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Cormorant+Garamond:ital,wght@0,300;0,400;0,600;1,400&family=Inter:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root { --bg:#0d0c0a; --ink:#f0ebe3; --ink-soft:#c8beb2; --ink-faint:#7a706a; --gold:#c9a14b; --line:#2a2620; --card:#1a1714; --serif:'Cormorant Garamond',Georgia,serif; --sans:'Inter',-apple-system,system-ui,sans-serif; }
*,*::before,*::after { box-sizing:border-box; }
body { margin:0; background:var(--bg); color:var(--ink); font:15px/1.55 var(--sans); }
a { color:var(--ink); text-decoration:none; }
a:hover { color:var(--gold); }
header.site { display:flex; align-items:center; justify-content:space-between; padding:18px 32px; border-bottom:1px solid var(--line); background:var(--bg); position:sticky; top:0; z-index:10; }
header.site .brand { font-family:var(--serif); font-style:italic; font-size:20px; letter-spacing:0.04em; color:var(--gold); }
header.site nav { display:flex; gap:24px; font-size:12px; letter-spacing:0.12em; text-transform:uppercase; color:var(--ink-faint); }
header.site nav a:hover { color:var(--ink); }
main { max-width:1100px; margin:0 auto; padding:48px 32px 80px; }
.hero { display:grid; grid-template-columns:280px 1fr; gap:36px; margin-bottom:48px; }
@media (max-width:720px) { .hero { grid-template-columns:1fr; } }
.poster { aspect-ratio:2/3; background:#15130f no-repeat center/cover; border-radius:8px; border:1px solid var(--line); box-shadow:0 18px 60px rgba(0,0,0,0.55); }
.title { font-family:var(--serif); font-weight:400; font-size:clamp(32px,4.5vw,52px); line-height:1.08; margin:0 0 8px; letter-spacing:-0.01em; }
.meta-line { color:var(--ink-faint); font-size:13px; letter-spacing:0.06em; text-transform:uppercase; margin-bottom:18px; }
.tagline { font-family:var(--serif); font-style:italic; color:var(--gold); font-size:18px; margin:0 0 16px; }
.overview { color:var(--ink-soft); font-size:15.5px; line-height:1.65; max-width:60ch; }
.chips { display:flex; flex-wrap:wrap; gap:8px; margin:18px 0; }
.chip { display:inline-block; padding:5px 11px; border:1px solid var(--line); border-radius:999px; font-size:11px; letter-spacing:0.08em; color:var(--ink-soft); background:rgba(201,161,75,0.04); }
.chip.gold { background:var(--gold); color:#1a1410; border-color:var(--gold); font-weight:600; }
section { margin-top:48px; }
section h2 { font-family:var(--serif); font-weight:400; font-size:22px; letter-spacing:0.04em; margin:0 0 16px; padding-bottom:8px; border-bottom:1px solid var(--line); }
.dept { color:var(--gold); font-size:11px; letter-spacing:0.18em; text-transform:uppercase; margin:24px 0 10px; }
.credit-row { display:grid; grid-template-columns:1fr 1fr; gap:6px 24px; padding:6px 0; border-bottom:1px dotted var(--line); font-size:14px; }
.credit-row .name a { color:var(--ink); }
.credit-row .name a:hover { color:var(--gold); }
.credit-row .role { color:var(--ink-faint); font-size:12px; }
.credit-row .character { color:var(--ink-soft); font-style:italic; }
.premium-badge { display:inline-block; padding:2px 7px; background:var(--gold); color:#1a1410; font-size:9px; font-weight:700; letter-spacing:0.18em; border-radius:3px; margin-left:8px; vertical-align:middle; }
.spotted-row { display:grid; grid-template-columns:120px 1fr; gap:18px; padding:16px 0; border-bottom:1px dotted var(--line); }
.spotted-row .badge-col { font-size:10px; letter-spacing:0.14em; text-transform:uppercase; color:var(--gold); }
.spotted-row .desc { color:var(--ink-soft); font-size:13.5px; line-height:1.55; }
.spotted-row .shop { display:inline-block; margin-top:6px; padding:4px 10px; border:1px solid var(--gold); color:var(--gold); font-size:11px; letter-spacing:0.1em; border-radius:3px; }
.spotted-row .shop:hover { background:var(--gold); color:#1a1410; }
.filmography { display:grid; grid-template-columns:60px 1fr 100px; gap:8px 16px; align-items:center; padding:8px 0; border-bottom:1px dotted var(--line); font-size:14px; }
.filmography .yr { color:var(--ink-faint); font-variant-numeric:tabular-nums; font-size:13px; }
.filmography .role-tag { color:var(--ink-faint); font-size:11px; letter-spacing:0.08em; text-transform:uppercase; }
footer.site { padding:24px 32px 36px; border-top:1px solid var(--line); color:var(--ink-faint); font-size:11px; letter-spacing:0.06em; text-align:center; line-height:1.7; }
footer.site a { color:var(--gold); }
.empty { padding:60px 0; text-align:center; color:var(--ink-faint); font-style:italic; }
.ai-pill { display:inline-block; padding:3px 9px; border:1px dotted var(--ink-faint); color:var(--ink-faint); font-size:10px; letter-spacing:0.16em; border-radius:3px; text-transform:uppercase; }
.img-credit { display:inline-block; padding:3px 9px; border:1px solid var(--line); color:var(--ink-soft); font-size:10px; letter-spacing:0.06em; border-radius:3px; }
.img-credit a { color:var(--gold); text-decoration:underline; }
.img-credit.pd { border-color:var(--gold); }
</style>
</head><body>
<header class="site">
<a class="brand" href="/">AsSeenInMovies</a>
<nav>
<a href="/movies">Movies</a>
<a href="/people">People</a>
<a href="/spotted">Spotted</a>
<a href="/spotted/submit">Submit</a>
<a href="/search">Search</a>
<a href="/random/movie" title="Random movie" aria-label="Show a random movie">🎲</a>
</nav>
</header>
<main>${body}</main>
<footer class="site">
AsSeenInMovies · public-data only · © Designer Wallcoverings · <a href="/about">About</a> · <a href="/privacy">Privacy</a> · <a href="/terms">Terms</a> · <a href="/api">API</a><br>
Movie metadata via <a href="https://www.themoviedb.org/">TMDB</a> — this product uses the TMDB API but is not endorsed or certified by TMDB.<br>
Posters and headshots that aren't rights-cleared use a <span class="ai-pill">made with AI</span> placeholder. Never sourced from IMDb.
</footer>
<!-- Big Red — lower-right avatar launcher (red → green when live).
Served by ~/Projects/big-red on :9935. -->
<script src="http://localhost:9935/widget.js"
data-host="http://localhost:9935"
data-mode="retail"
defer onerror="this.remove()"></script>
</body></html>`;
}
// /movie/:id — hero (poster + title + meta + overview) + SPOTTED row + credits grouped by department
app.get('/movie/:id', async (req, res) => {
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id) || id < 1) return res.status(404).type('html').send(htmlShell('Not found', '<div class="empty">Movie not found.</div>'));
try {
const m = await pool.query(`SELECT * FROM asim_movies WHERE id=$1 LIMIT 1`, [id]);
if (!m.rows[0]) return res.status(404).type('html').send(htmlShell('Not found', '<div class="empty">Movie not found.</div>'));
const mv = m.rows[0];
// Credits + spotted are independent — parallel cuts wall time roughly in
// half on cold cache. Public /movie/:id should only surface verified
// placements; pending submissions wait for admin review.
const [credits, spotted] = await Promise.all([
pool.query(`
SELECT c.role, c.character_name, c.order_idx, c.job_title, c.department,
p.id AS person_id, p.full_name, p.headshot_url, p.headshot_source, p.premium_tier
FROM asim_credits c JOIN asim_people p ON p.id = c.person_id
WHERE c.movie_id=$1 ORDER BY
CASE c.department
WHEN 'Directing' THEN 1 WHEN 'Acting' THEN 2 WHEN 'Writing' THEN 3
WHEN 'Art' THEN 4 WHEN 'Camera' THEN 5 WHEN 'Editing' THEN 6
WHEN 'Sound' THEN 7 WHEN 'Production' THEN 8 ELSE 9 END,
c.order_idx NULLS LAST, p.full_name`, [id]),
pool.query(`
SELECT * FROM asim_spotted WHERE movie_id=$1 AND verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')
ORDER BY verified_at DESC, created_at DESC`, [id])
]);
// Group credits by department
const groups = {};
for (const c of credits.rows) {
const d = c.department || 'Other';
(groups[d] = groups[d] || []).push(c);
}
const deptOrder = ['Directing','Acting','Writing','Art','Camera','Editing','Sound','Production','Other'];
const heroHtml = `
<div class="hero">
<div class="poster" style="background-image:url('${posterFor(mv)}')"></div>
<div>
<h1 class="title">${esc(mv.title || 'Untitled')}</h1>
<div class="meta-line">
${mv.release_year ? esc(mv.release_year) + ' · ' : ''}${mv.runtime_min ? esc(mv.runtime_min) + ' min · ' : ''}${(mv.genres || []).map(esc).join(' · ')}
</div>
${mv.tagline ? `<p class="tagline">${esc(mv.tagline)}</p>` : ''}
${mv.overview ? `<p class="overview">${esc(mv.overview)}</p>` : ''}
<div class="chips">
${mv.imdb_id ? `<span class="chip">imdb · ${esc(mv.imdb_id)}</span>` : ''}
${mv.tmdb_id ? `<span class="chip">tmdb · ${esc(mv.tmdb_id)}</span>` : ''}
${mv.status ? `<span class="chip">${esc(mv.status)}</span>` : ''}
${imageCreditHtml(mv.poster_credit, mv.poster_license, mv.poster_source_url, mv.poster_source)}
</div>
</div>
</div>`;
// Show "Spotted on set" section either when we have verified rows OR
// unconditionally as an invitation to submit. The CTA links to the
// submission form with movie_id pre-filled.
const spottedHtml = `
<section>
<h2 style="display:flex;justify-content:space-between;align-items:baseline;gap:10px;flex-wrap:wrap">
<span>Spotted on set${spotted.rows.length ? ` <span style="color:var(--ink-faint);font-size:14px;letter-spacing:0">· ${spotted.rows.length}</span>` : ''}</span>
<a href="/spotted/submit?movie_id=${id}" style="font-size:12px;letter-spacing:0.12em;text-transform:uppercase;color:var(--gold);text-decoration:none;border:1px solid var(--gold);padding:5px 12px;border-radius:4px">+ Submit a placement</a>
</h2>
${spotted.rows.length ? spotted.rows.map(s => `
<div class="spotted-row">
<div class="badge-col">${esc(s.product_category || 'dressing')}${s.is_premium_pick ? '<br><span class="premium-badge">DOCUMENTED</span>' : ''}</div>
<div>
<div style="font-weight:500">${esc(s.product_name)}${s.brand ? ` <span style="color:var(--ink-faint);font-weight:400">— ${esc(s.brand)}</span>` : ''}</div>
${s.scene_timecode ? `<div style="color:var(--ink-faint);font-size:12px;margin-top:2px">${esc(s.scene_timecode)}</div>` : ''}
<div class="desc">${esc(s.scene_description || '')}</div>
${s.shop_url ? `<a class="shop" href="${esc(s.shop_url)}" target="_blank" rel="nofollow noopener noreferrer">Where to find it ↗</a>` : ''}
</div>
</div>`).join('') : '<p style="color:var(--ink-faint);font-style:italic;padding:14px 0 4px;font-size:13.5px">No verified placements yet for this film. <a href="/spotted/submit?movie_id=' + id + '" style="color:var(--gold)">Submit one →</a></p>'}
</section>`;
const creditsHtml = deptOrder.filter(d => groups[d] && groups[d].length).map(d => `
<div class="dept">${esc(d)}</div>
${groups[d].slice(0, d === 'Acting' ? 20 : 30).map(c => `
<div class="credit-row">
<div class="name"><a href="/person/${esc(c.person_id)}">${esc(c.full_name)}</a>${c.premium_tier ? '<span class="premium-badge">' + esc(c.premium_tier) + '</span>' : ''}</div>
<div class="role">${esc(c.character_name || c.job_title || c.role || '')}</div>
</div>`).join('')}
`).join('');
// JSON-LD Movie schema + Open Graph + Twitter Card for share previews
// and rich snippets. The poster_url goes into og:image (preferring real
// posters over the made-with-ai placeholder).
const ogImage = mv.poster_url && (mv.poster_source || '').toLowerCase() !== 'made-with-ai'
? mv.poster_url
: 'https://asseeninmovies.com/img/made-with-ai.svg';
const ogDesc = mv.overview ? mv.overview.slice(0, 280) : `${mv.title}${mv.release_year ? ' (' + mv.release_year + ')' : ''} — credits, scene details, and SPOTTED placements on AsSeenInMovies.`;
const jsonLd = JSON.stringify({
'@context': 'https://schema.org',
'@graph': [
{
'@type': 'Movie',
name: mv.title,
...(mv.release_year ? { datePublished: String(mv.release_year) } : {}),
...(mv.overview ? { description: mv.overview } : {}),
...(mv.poster_url ? { image: ogImage } : {}),
...(mv.imdb_id ? { sameAs: `https://www.imdb.com/title/${mv.imdb_id}/` } : {}),
...(mv.genres && mv.genres.length ? { genre: mv.genres } : {}),
...(mv.runtime_min ? { duration: `PT${mv.runtime_min}M` } : {}),
url: `https://asseeninmovies.com/movie/${id}`,
},
{
'@type': 'BreadcrumbList',
itemListElement: [
{ '@type': 'ListItem', position: 1, name: 'Home', item: 'https://asseeninmovies.com/' },
{ '@type': 'ListItem', position: 2, name: 'Movies', item: 'https://asseeninmovies.com/movies' },
{ '@type': 'ListItem', position: 3, name: mv.title, item: `https://asseeninmovies.com/movie/${id}` },
],
},
],
});
const headExtras = `
<meta name="description" content="${esc(ogDesc)}">
<meta property="og:type" content="video.movie">
<meta property="og:title" content="${esc(mv.title || 'Untitled')}">
<meta property="og:description" content="${esc(ogDesc)}">
<meta property="og:image" content="${esc(ogImage)}">
<meta property="og:url" content="https://asseeninmovies.com/movie/${id}">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:title" content="${esc(mv.title || 'Untitled')}">
<meta name="twitter:description" content="${esc(ogDesc)}">
<meta name="twitter:image" content="${esc(ogImage)}">
<script type="application/ld+json">${jsonLd}</script>`;
res.type('html').send(htmlShell(mv.title || ('Movie #' + id), heroHtml + spottedHtml + (creditsHtml ? `<section><h2>Cast & crew</h2>${creditsHtml}</section>` : ''), headExtras, `https://asseeninmovies.com/movie/${id}`));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
// /person/:id — headshot + name + profession + filmography table
app.get('/person/:id', async (req, res) => {
const id = parseInt(req.params.id, 10);
if (!Number.isFinite(id) || id < 1) return res.status(404).type('html').send(htmlShell('Not found', '<div class="empty">Person not found.</div>'));
try {
const p = await pool.query(`SELECT * FROM asim_people WHERE id=$1 LIMIT 1`, [id]);
if (!p.rows[0]) return res.status(404).type('html').send(htmlShell('Not found', '<div class="empty">Person not found.</div>'));
const person = p.rows[0];
// Credits + spotted are independent — parallel. Spotted surfaces the
// person ⇄ movie ⇄ spotted graph: any verified placement on a film
// this person worked on. Mostly empty today (14 rows) but lights up as
// submissions accumulate.
const [credits, personSpotted] = await Promise.all([
pool.query(`
SELECT c.role, c.character_name, c.job_title, c.department,
m.id AS movie_id, m.title, m.release_year, m.poster_url, m.poster_source
FROM asim_credits c JOIN asim_movies m ON m.id = c.movie_id
WHERE c.person_id=$1 ORDER BY m.release_year DESC NULLS LAST`, [id]),
pool.query(`
SELECT DISTINCT s.id AS spot_id, s.product_name, s.product_category, s.brand,
s.scene_timecode, s.shop_url,
m.id AS movie_id, m.title, m.release_year
FROM asim_spotted s
JOIN asim_movies m ON m.id = s.movie_id
JOIN asim_credits c ON c.movie_id = m.id
WHERE c.person_id = $1 AND s.verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')
ORDER BY m.release_year DESC NULLS LAST, s.product_name
LIMIT 24`, [id])
]);
const isPremium = person.claimed_at && (!person.premium_expires_at || new Date(person.premium_expires_at) > new Date());
const heroHtml = `
<div class="hero">
<div class="poster" style="aspect-ratio:1; background-image:url('${headshotFor(person)}')"></div>
<div>
<h1 class="title">${esc(person.full_name)}${isPremium ? '<span class="premium-badge">VERIFIED</span>' : ''}</h1>
<div class="meta-line">
${person.birth_year ? esc(person.birth_year) + (person.death_year ? '–' + esc(person.death_year) : '') + ' · ' : ''}${person.birth_place ? esc(person.birth_place) : ''}
</div>
<div class="chips">
${(person.primary_profession || []).map(p => `<span class="chip gold">${esc(p)}</span>`).join('')}
${(person.union_memberships || []).map(u => `<span class="chip">${esc(u)}</span>`).join('')}
${imageCreditHtml(person.headshot_credit, person.headshot_license, person.headshot_source_url, person.headshot_source)}
</div>
${person.bio ? `<p class="overview">${esc(person.bio).slice(0, 800)}${person.bio.length > 800 ? '…' : ''}</p>` : ''}
${!isPremium ? `<p style="margin-top:18px"><a href="/claim/person/${esc(id)}" style="display:inline-block;padding:8px 16px;border:1px solid var(--gold);color:var(--gold);font-size:12px;letter-spacing:0.1em">Claim this profile →</a></p>` : ''}
</div>
</div>`;
const filmHtml = credits.rows.length ? `
<section>
<h2>Filmography · ${credits.rows.length}</h2>
${credits.rows.slice(0, 200).map(c => `
<div class="filmography">
<div class="yr">${esc(c.release_year || '—')}</div>
<div><a href="/movie/${esc(c.movie_id)}">${esc(c.title)}</a>${c.character_name ? '<span class="role-tag"> · as ' + esc(c.character_name) + '</span>' : ''}</div>
<div class="role-tag">${esc(c.role || c.job_title || '')}</div>
</div>`).join('')}
</section>` : '<div class="empty">No credits loaded yet.</div>';
const personSpottedHtml = personSpotted.rows.length ? `
<section>
<h2>SPOTTED in their films · ${personSpotted.rows.length}</h2>
${personSpotted.rows.map(s => `
<div class="spotted-row">
<div class="badge-col">${esc(s.product_category || 'dressing')}</div>
<div>
<div style="font-weight:500">${esc(s.product_name)}${s.brand ? ' <span style="color:var(--ink-faint);font-weight:400">— '+esc(s.brand)+'</span>' : ''}</div>
<div class="desc"><a href="/movie/${esc(s.movie_id)}#spotted">${esc(s.title)}${s.release_year ? ' ('+esc(s.release_year)+')' : ''}</a>${s.scene_timecode ? ' · ' + esc(s.scene_timecode) : ''}</div>
${s.shop_url ? `<a class="shop" href="${esc(s.shop_url)}" target="_blank" rel="nofollow noopener noreferrer">Where to find it ↗</a>` : ''}
</div>
</div>`).join('')}
</section>` : '';
const ogImage = person.headshot_url && (person.headshot_source || '').toLowerCase() !== 'made-with-ai'
? person.headshot_url
: 'https://asseeninmovies.com/img/made-with-ai.svg';
const profProf = (person.primary_profession || []).slice(0, 2).join(', ') || 'film professional';
const ogDesc = `${person.full_name} — ${profProf}${person.birth_year ? ', born ' + person.birth_year : ''}. Filmography and verified SPOTTED placements on AsSeenInMovies.`;
const jsonLd = JSON.stringify({
'@context': 'https://schema.org',
'@graph': [
{
'@type': 'Person',
name: person.full_name,
...(person.bio ? { description: person.bio.slice(0, 800) } : {}),
...(person.headshot_url ? { image: ogImage } : {}),
...(person.imdb_id ? { sameAs: `https://www.imdb.com/name/${person.imdb_id}/` } : {}),
...(person.birth_year ? { birthDate: String(person.birth_year) } : {}),
...(person.death_year ? { deathDate: String(person.death_year) } : {}),
...(person.primary_profession && person.primary_profession.length ? { jobTitle: person.primary_profession[0] } : {}),
url: `https://asseeninmovies.com/person/${id}`,
},
{
'@type': 'BreadcrumbList',
itemListElement: [
{ '@type': 'ListItem', position: 1, name: 'Home', item: 'https://asseeninmovies.com/' },
{ '@type': 'ListItem', position: 2, name: 'People', item: 'https://asseeninmovies.com/people' },
{ '@type': 'ListItem', position: 3, name: person.full_name, item: `https://asseeninmovies.com/person/${id}` },
],
},
],
});
const headExtras = `
<meta name="description" content="${esc(ogDesc)}">
<meta property="og:type" content="profile">
<meta property="og:title" content="${esc(person.full_name)}">
<meta property="og:description" content="${esc(ogDesc)}">
<meta property="og:image" content="${esc(ogImage)}">
<meta property="og:url" content="https://asseeninmovies.com/person/${id}">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:title" content="${esc(person.full_name)}">
<meta name="twitter:description" content="${esc(ogDesc)}">
<meta name="twitter:image" content="${esc(ogImage)}">
<script type="application/ld+json">${jsonLd}</script>`;
res.type('html').send(htmlShell(person.full_name, heroHtml + filmHtml + personSpottedHtml, headExtras, `https://asseeninmovies.com/person/${id}`));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
// /movies — paginated grid of movies. Defaults to credited-poster-first so the
// gallery looks alive even with 6.46M unenriched stubs in the tail. Filters by
// release year and genre. Query: ?page= ?year= ?genre= ?q=
app.get('/movies', async (req, res) => {
try {
const MOVIE_SORTS = {
// `popularity DESC NULLS LAST, id DESC` matches
// idx_asim_movies_popularity_w_poster — the prior
// `(popularity IS NOT NULL) DESC` prefix was an expression that broke
// index-order use and forced a 290k-row sort.
popular: `popularity DESC NULLS LAST, id DESC`,
newest: `release_year DESC NULLS LAST, id DESC`,
oldest: `release_year ASC NULLS LAST, id`,
title_az: `title ASC, id`,
title_za: `title DESC, id`,
rating: `vote_average DESC NULLS LAST, id`,
};
const page = Math.max(1, parseInt(req.query.page || '1', 10));
const limit = 48;
const offset = (page - 1) * limit;
const year = /^\d{4}$/.test(req.query.year || '') ? parseInt(req.query.year, 10) : null;
const genre = typeof req.query.genre === 'string' && req.query.genre.length < 32 ? req.query.genre : null;
const q = typeof req.query.q === 'string' && req.query.q.length < 80 ? req.query.q.trim() : null;
const sort = MOVIE_SORTS[req.query.sort] ? req.query.sort : 'popular';
const params = [];
let where = `poster_url IS NOT NULL`;
if (year) { params.push(year); where += ` AND release_year = $${params.length}`; }
if (genre) { params.push(genre); where += ` AND $${params.length} = ANY(genres)`; }
if (q) { params.push(`%${q.toLowerCase()}%`); where += ` AND LOWER(title) LIKE $${params.length}`; }
params.push(limit, offset);
const countParams = params.slice(0, params.length - 2);
const [r, totalR] = await Promise.all([
pool.query(`
SELECT id, title, release_year, genres, poster_url, poster_source, vote_average
FROM asim_movies
WHERE ${where}
ORDER BY ${MOVIE_SORTS[sort]}
LIMIT $${params.length-1} OFFSET $${params.length}`, params),
pool.query(`SELECT count(*) AS n FROM asim_movies WHERE ${where}`, countParams)
]);
const total = parseInt(totalR.rows[0].n, 10);
const totalPages = Math.max(1, Math.ceil(total / limit));
const cards = r.rows.map(m => `
<a class="grid-card" href="/movie/${m.id}">
<div class="grid-poster" style="background-image:url('${esc(m.poster_url || '')}');--card-bg:url('${esc(m.poster_url || '')}')"></div>
<div class="grid-body">
<div class="grid-title">${esc(m.title)}</div>
<div class="grid-meta">${m.release_year || '—'}${m.vote_average ? ' · ★ ' + Number(m.vote_average).toFixed(1) : ''}</div>
</div>
</a>`).join('');
const qs = (overrides = {}) => {
const u = new URLSearchParams();
if (year) u.set('year', year);
if (genre) u.set('genre', genre);
if (q) u.set('q', q);
if (sort && sort !== 'popular') u.set('sort', sort);
Object.entries(overrides).forEach(([k,v]) => v == null ? u.delete(k) : u.set(k, v));
return u.toString();
};
const pager = `
<nav class="pager" aria-label="Pagination">
${page > 1 ? `<a class="chip" href="/movies?${qs({page: page-1})}">← Prev</a>` : ''}
<span class="chip" style="background:var(--gold);color:#1a1410">Page ${page} of ${totalPages.toLocaleString()}</span>
${page < totalPages ? `<a class="chip" href="/movies?${qs({page: page+1})}">Next →</a>` : ''}
</nav>`;
const sortOpt = (val, label) => `<option value="${val}"${sort===val?' selected':''}>${label}</option>`;
const filterBar = `
<form class="filter-bar" method="get" action="/movies">
<input name="q" type="search" placeholder="Search title" value="${esc(q || '')}">
<input name="year" type="text" placeholder="Year (e.g. 1994)" value="${esc(year || '')}" maxlength="4" size="6">
<input name="genre" type="text" placeholder="Genre (e.g. Drama)" value="${esc(genre || '')}" size="14">
<label class="control"><span style="font-size:11px;letter-spacing:0.08em;color:var(--ink-faint);margin-right:4px">Sort</span>
<select name="sort" onchange="this.form.submit()">
${sortOpt('popular', 'Most popular')}
${sortOpt('newest', 'Newest')}
${sortOpt('oldest', 'Oldest')}
${sortOpt('title_az', 'Title A→Z')}
${sortOpt('title_za', 'Title Z→A')}
${sortOpt('rating', 'Highest rated')}
</select>
</label>
<label class="control"><span style="font-size:11px;letter-spacing:0.08em;color:var(--ink-faint);margin-right:4px">Density</span>
<input type="range" min="140" max="280" step="20" value="180" data-density="movies">
</label>
<button type="submit" class="chip" style="background:transparent">Filter</button>
</form>`;
const body = `
<style>
:root{--movies-card:180px}
.filter-bar{display:flex;gap:10px;flex-wrap:wrap;align-items:center;margin-bottom:24px}
.filter-bar input[type=search],.filter-bar input[type=text]{background:transparent;border:1px solid var(--line);color:inherit;padding:7px 12px;border-radius:6px;font:inherit;min-width:140px}
.filter-bar .control{display:inline-flex;align-items:center;gap:6px}
.filter-bar select{background:transparent;border:1px solid var(--line);color:inherit;padding:7px 12px;border-radius:6px;font:inherit}
.filter-bar input[type=range]{accent-color:var(--gold)}
.grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(var(--movies-card),1fr));gap:20px}
.grid-card{display:block;border:1px solid var(--line);border-radius:8px;overflow:hidden;background:var(--card);transition:transform .15s}
.grid-card:hover{transform:translateY(-2px)}
.grid-poster{aspect-ratio:2/3;background:#15130f no-repeat center/cover;position:relative;overflow:hidden}
.grid-poster::after{content:"";position:absolute;inset:0;background-image:var(--card-bg,none);background-repeat:repeat;background-size:33.33% 33.33%;background-position:center;opacity:0;transition:opacity 360ms ease;pointer-events:none;will-change:opacity}
.grid-card:hover .grid-poster::after,.grid-card:focus-visible .grid-poster::after{opacity:1}
@media (prefers-reduced-motion:reduce){.grid-poster::after{transition:none}}
.grid-body{padding:10px 12px}
.grid-title{font-size:13.5px;line-height:1.35;margin-bottom:4px;color:var(--ink);overflow:hidden;display:-webkit-box;-webkit-line-clamp:2;-webkit-box-orient:vertical}
.grid-meta{color:var(--ink-faint);font-size:11px;letter-spacing:0.04em}
.pager{display:flex;gap:10px;align-items:center;justify-content:center;margin:32px 0}
</style>
<h1 class="title">Movies</h1>
<p class="meta-line">${total.toLocaleString()} movies with rights-cleared posters</p>
${filterBar}
<div class="grid">${cards || '<div class="empty">No movies match those filters yet.</div>'}</div>
${pager}
<script>(function(){var KEY='asim_movies_density';var saved=parseInt(localStorage.getItem(KEY)||'180',10);var r=document.querySelector('[data-density="movies"]');if(!r)return;if(saved&&saved>=140&&saved<=280){r.value=saved;document.documentElement.style.setProperty('--movies-card',saved+'px')}r.addEventListener('input',function(){document.documentElement.style.setProperty('--movies-card',r.value+'px');localStorage.setItem(KEY,r.value)})})();</script>`;
const itemListMovies = {
'@context': 'https://schema.org',
'@type': 'ItemList',
itemListElement: r.rows.map((m, i) => ({
'@type': 'ListItem',
position: offset + i + 1,
item: {
'@type': 'Movie',
name: m.title,
...(m.release_year ? { datePublished: String(m.release_year) } : {}),
url: `https://asseeninmovies.com/movie/${m.id}`,
},
})),
numberOfItems: total,
};
res.type('html').send(htmlShell('Movies', body, `<script type="application/ld+json">${JSON.stringify(itemListMovies)}</script>`, 'https://asseeninmovies.com/movies'));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
// /people — paginated grid of people with headshots, popularity-sorted.
app.get('/people', async (req, res) => {
try {
const PEOPLE_SORTS = {
default: `(claimed_at IS NOT NULL) DESC, full_name`,
name_az: `full_name ASC`,
name_za: `full_name DESC`,
youngest: `birth_year DESC NULLS LAST, full_name`,
oldest: `birth_year ASC NULLS LAST, full_name`,
living: `(death_year IS NULL) DESC, full_name`,
};
const page = Math.max(1, parseInt(req.query.page || '1', 10));
const limit = 60;
const offset = (page - 1) * limit;
const q = typeof req.query.q === 'string' && req.query.q.length < 80 ? req.query.q.trim() : null;
const sort = PEOPLE_SORTS[req.query.sort] ? req.query.sort : 'default';
const params = [];
let where = `headshot_url IS NOT NULL`;
if (q) { params.push(`%${q.toLowerCase()}%`); where += ` AND LOWER(full_name) LIKE $${params.length}`; }
params.push(limit, offset);
const countParams = params.slice(0, params.length - 2);
const [r, totalR] = await Promise.all([
pool.query(`
SELECT id, full_name, headshot_url, headshot_source, primary_profession, birth_year, death_year
FROM asim_people
WHERE ${where}
ORDER BY ${PEOPLE_SORTS[sort]}
LIMIT $${params.length-1} OFFSET $${params.length}`, params),
pool.query(`SELECT count(*) AS n FROM asim_people WHERE ${where}`, countParams)
]);
const total = parseInt(totalR.rows[0].n, 10);
const totalPages = Math.max(1, Math.ceil(total / limit));
const cards = r.rows.map(p => `
<a class="grid-card person" href="/person/${p.id}">
<div class="grid-headshot" style="background-image:url('${esc(p.headshot_url || '')}');--card-bg:url('${esc(p.headshot_url || '')}')"></div>
<div class="grid-body">
<div class="grid-title">${esc(p.full_name)}</div>
<div class="grid-meta">${(p.primary_profession || []).slice(0,2).join(' · ')}${p.birth_year ? ' · ' + p.birth_year + (p.death_year ? '–' + p.death_year : '') : ''}</div>
</div>
</a>`).join('');
const qs = (overrides = {}) => {
const u = new URLSearchParams();
if (q) u.set('q', q);
if (sort && sort !== 'default') u.set('sort', sort);
Object.entries(overrides).forEach(([k,v]) => v == null ? u.delete(k) : u.set(k, v));
return u.toString();
};
const pager = `
<nav class="pager" aria-label="Pagination">
${page > 1 ? `<a class="chip" href="/people?${qs({page: page-1})}">← Prev</a>` : ''}
<span class="chip" style="background:var(--gold);color:#1a1410">Page ${page} of ${totalPages.toLocaleString()}</span>
${page < totalPages ? `<a class="chip" href="/people?${qs({page: page+1})}">Next →</a>` : ''}
</nav>`;
const sortOpt = (val, label) => `<option value="${val}"${sort===val?' selected':''}>${label}</option>`;
const body = `
<style>
:root{--people-card:160px}
.filter-bar{display:flex;gap:10px;flex-wrap:wrap;align-items:center;margin-bottom:24px}
.filter-bar input[type=search]{background:transparent;border:1px solid var(--line);color:inherit;padding:7px 12px;border-radius:6px;font:inherit;min-width:200px}
.filter-bar .control{display:inline-flex;align-items:center;gap:6px}
.filter-bar select{background:transparent;border:1px solid var(--line);color:inherit;padding:7px 12px;border-radius:6px;font:inherit}
.filter-bar input[type=range]{accent-color:var(--gold)}
.grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(var(--people-card),1fr));gap:18px}
.grid-card{display:block;border:1px solid var(--line);border-radius:8px;overflow:hidden;background:var(--card);transition:transform .15s}
.grid-card:hover{transform:translateY(-2px)}
.grid-headshot{aspect-ratio:3/4;background:#15130f no-repeat center/cover;position:relative;overflow:hidden}
.grid-headshot::after{content:"";position:absolute;inset:0;background-image:var(--card-bg,none);background-repeat:repeat;background-size:33.33% 33.33%;background-position:center;opacity:0;transition:opacity 360ms ease;pointer-events:none;will-change:opacity}
.grid-card:hover .grid-headshot::after,.grid-card:focus-visible .grid-headshot::after{opacity:1}
@media (prefers-reduced-motion:reduce){.grid-headshot::after{transition:none}}
.grid-body{padding:10px 12px}
.grid-title{font-size:13.5px;line-height:1.35;margin-bottom:4px;color:var(--ink)}
.grid-meta{color:var(--ink-faint);font-size:11px;letter-spacing:0.04em;overflow:hidden;text-overflow:ellipsis;white-space:nowrap}
.pager{display:flex;gap:10px;align-items:center;justify-content:center;margin:32px 0}
</style>
<h1 class="title">People</h1>
<p class="meta-line">${total.toLocaleString()} people with public headshots</p>
<form class="filter-bar" method="get" action="/people">
<input name="q" type="search" placeholder="Search name" value="${esc(q || '')}">
<label class="control"><span style="font-size:11px;letter-spacing:0.08em;color:var(--ink-faint);margin-right:4px">Sort</span>
<select name="sort" onchange="this.form.submit()">
${sortOpt('default', 'Claimed first')}
${sortOpt('name_az', 'Name A→Z')}
${sortOpt('name_za', 'Name Z→A')}
${sortOpt('youngest', 'Youngest')}
${sortOpt('oldest', 'Oldest')}
${sortOpt('living', 'Living first')}
</select>
</label>
<label class="control"><span style="font-size:11px;letter-spacing:0.08em;color:var(--ink-faint);margin-right:4px">Density</span>
<input type="range" min="120" max="280" step="20" value="160" data-density="people">
</label>
<button type="submit" class="chip" style="background:transparent">Filter</button>
</form>
<div class="grid">${cards || '<div class="empty">No people match that filter yet.</div>'}</div>
${pager}
<script>(function(){var KEY='asim_people_density';var saved=parseInt(localStorage.getItem(KEY)||'160',10);var r=document.querySelector('[data-density="people"]');if(!r)return;if(saved&&saved>=120&&saved<=280){r.value=saved;document.documentElement.style.setProperty('--people-card',saved+'px')}r.addEventListener('input',function(){document.documentElement.style.setProperty('--people-card',r.value+'px');localStorage.setItem(KEY,r.value)})})();</script>`;
const itemListPeople = {
'@context': 'https://schema.org',
'@type': 'ItemList',
itemListElement: r.rows.map((p, i) => ({
'@type': 'ListItem',
position: offset + i + 1,
item: {
'@type': 'Person',
name: p.full_name,
...(p.primary_profession && p.primary_profession.length ? { jobTitle: p.primary_profession[0] } : {}),
url: `https://asseeninmovies.com/person/${p.id}`,
},
})),
numberOfItems: total,
};
res.type('html').send(htmlShell('People', body, `<script type="application/ld+json">${JSON.stringify(itemListPeople)}</script>`, 'https://asseeninmovies.com/people'));
} catch (e) {
res.status(500).type('html').send(htmlShell('Error', `<div class="empty">${esc(e.message)}</div>`));
}
});
// Home — placeholder until tick 1 ships TMDB ingest + real UI.
// In-memory home-page cache with stale-while-revalidate semantics. count(*)
// on 290k asim_movies + 61k asim_people is 2-3s cold each, so we never want
// a user request to wait on that. Behavior:
// - cache fresh (< TTL): return immediately
// - cache stale + has data: return stale data, kick off background refresh
// - cache empty (first hit): await the inflight refresh (warmup covers this)
let _homeCache = { at: 0, data: null, inflight: null };
const HOME_CACHE_TTL_MS = 60_000;
function startHomeRefresh() {
if (_homeCache.inflight) return _homeCache.inflight;
const p = (async () => {
const [s, fm, fp, fs] = await Promise.all([
pool.query(`
SELECT
(SELECT count(*) FROM asim_movies) AS movies_total,
(SELECT count(*) FROM asim_movies WHERE poster_url IS NOT NULL) AS movies_poster,
(SELECT count(*) FROM asim_people) AS people_total,
(SELECT count(*) FROM asim_people WHERE headshot_url IS NOT NULL) AS people_headshot,
(SELECT count(*) FROM asim_credits) AS credits_total,
(SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS spotted_total`),
pool.query(`SELECT id, title, release_year, poster_url FROM asim_movies WHERE poster_url IS NOT NULL ORDER BY random() LIMIT 6`),
pool.query(`SELECT id, full_name, headshot_url, primary_profession FROM asim_people WHERE headshot_url IS NOT NULL ORDER BY random() LIMIT 6`),
pool.query(`
SELECT s.id AS spot_id, s.product_name, s.product_category, s.brand, s.scene_timecode,
m.id AS movie_id, m.title, m.release_year, m.poster_url, m.poster_source
FROM asim_spotted s JOIN asim_movies m ON m.id = s.movie_id
WHERE s.verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')
ORDER BY s.verified_at DESC LIMIT 6`)
]);
return { stats: s.rows[0], featuredMovies: fm.rows, featuredPeople: fp.rows, featuredSpotted: fs.rows };
})().then(data => {
_homeCache = { at: Date.now(), data, inflight: null };
return data;
}).catch(e => {
_homeCache = { ..._homeCache, inflight: null };
throw e;
});
_homeCache.inflight = p;
return p;
}
async function getHomeCache() {
const now = Date.now();
const fresh = (now - _homeCache.at) < HOME_CACHE_TTL_MS;
if (fresh && _homeCache.data) return _homeCache.data;
if (_homeCache.data) {
// stale-while-revalidate: serve immediately, refresh in background
if (!_homeCache.inflight) startHomeRefresh().catch(() => {});
return _homeCache.data;
}
// empty cache — first hit ever. Must await.
return await startHomeRefresh();
}
app.get('/', async (_req, res) => {
// Live-stats home with featured rails of recent movies + people. Read-only.
let stats = { movies_total: 0, people_total: 0, movies_poster: 0, people_headshot: 0, spotted_total: 0 };
let featuredMovies = [], featuredPeople = [], featuredSpotted = [];
try {
const cached = await getHomeCache();
stats = cached.stats;
featuredMovies = cached.featuredMovies;
featuredPeople = cached.featuredPeople;
featuredSpotted = cached.featuredSpotted;
} catch (e) {
// home page should never 500; fall back to static.
}
const movieCards = featuredMovies.map(m => `
<a class="rail-card" href="/movie/${m.id}">
<div class="rail-poster" style="background-image:url('${esc(m.poster_url || '')}');--card-bg:url('${esc(m.poster_url || '')}')"></div>
<div class="rail-title">${esc(m.title)}</div>
<div class="rail-meta">${m.release_year || '—'}</div>
</a>`).join('');
const peopleCards = featuredPeople.map(p => `
<a class="rail-card" href="/person/${p.id}">
<div class="rail-headshot" style="background-image:url('${esc(p.headshot_url || '')}');--card-bg:url('${esc(p.headshot_url || '')}')"></div>
<div class="rail-title">${esc(p.full_name)}</div>
<div class="rail-meta">${(p.primary_profession || []).slice(0,2).join(' · ')}</div>
</a>`).join('');
const spottedCards = featuredSpotted.map(s => `
<a class="rail-card" href="/movie/${s.movie_id}#spotted">
<div class="rail-poster" style="background-image:url('${esc((s.poster_source||'').toLowerCase()!=='made-with-ai' && s.poster_url ? s.poster_url : '/img/made-with-ai.svg')}');--card-bg:url('${esc((s.poster_source||'').toLowerCase()!=='made-with-ai' && s.poster_url ? s.poster_url : '/img/made-with-ai.svg')}')"></div>
<div class="rail-title">${esc(s.product_name)}${s.brand ? ' · <span style="color:var(--muted)">'+esc(s.brand)+'</span>' : ''}</div>
<div class="rail-meta">${esc(s.title)}${s.release_year ? ' ('+s.release_year+')' : ''}</div>
</a>`).join('');
res.type('html').send(`<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><title>AsSeenInMovies</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="icon" type="image/svg+xml" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 32 32'%3E%3Crect width='32' height='32' rx='6' fill='%230d0c0a'/%3E%3Ctext x='16' y='22' font-family='Georgia,serif' font-style='italic' font-size='20' fill='%23c9a14b' text-anchor='middle'%3EA%3C/text%3E%3C/svg%3E">
<meta name="color-scheme" content="dark">
<meta name="theme-color" content="#0d0c0a">
${process.env.GA_ID ? `<script async src="https://www.googletagmanager.com/gtag/js?id=${process.env.GA_ID}"></script>
<script>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments);}gtag('js',new Date());gtag('config','${process.env.GA_ID}');</script>` : ''}
<link rel="canonical" href="https://asseeninmovies.com/">
<meta name="description" content="AsSeenInMovies — a film + TV reference built on public data. Browse 290k+ movies, 61k+ people, and verified SPOTTED set decor, wallcoverings, lighting, and props from the films you love.">
<meta property="og:type" content="website">
<meta property="og:title" content="AsSeenInMovies — A Film + TV Reference">
<meta property="og:description" content="290k+ movies, 61k+ people, and verified SPOTTED set decor, wallcoverings, lighting, and props from the films you love.">
<meta property="og:url" content="https://asseeninmovies.com/">
<meta property="og:image" content="https://asseeninmovies.com/img/made-with-ai.svg">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://asseeninmovies.com/img/made-with-ai.svg">
<script type="application/ld+json">{"@context":"https://schema.org","@graph":[{"@type":"WebSite","@id":"https://asseeninmovies.com/#website","url":"https://asseeninmovies.com/","name":"AsSeenInMovies","description":"A film + TV reference built on public data — movies, people, and verified SPOTTED set decor placements.","potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://asseeninmovies.com/search?q={search_term_string}"},"query-input":"required name=search_term_string"}},{"@type":"Organization","@id":"https://asseeninmovies.com/#org","name":"Designer Wallcoverings","url":"https://designerwallcoverings.com/","sameAs":["https://www.themoviedb.org/"]}]}</script>
<style>
:root { --bg:#0d0c0a; --ink:#f0ebe3; --gold:#c9a14b; --muted:#7a706a; --line:#2a2620; }
*,*::before,*::after { box-sizing: border-box; }
body { margin:0; background:var(--bg); color:var(--ink); font:15px/1.6 'Inter','SF Pro Text',-apple-system,sans-serif; min-height:100vh; }
header { padding:80px 32px 32px; text-align:center; border-bottom:1px solid var(--line); }
h1 { margin:0 0 8px; font-family:'Cormorant Garamond',Georgia,serif; font-weight:300; font-size:clamp(40px,5.5vw,76px); letter-spacing:0.04em; }
h1 .as { color:var(--gold); font-style:italic; font-weight:400; }
.tag { color:var(--muted); font-size:13px; letter-spacing:0.22em; text-transform:uppercase; margin-bottom:24px }
.cta-row { display:flex; gap:14px; justify-content:center; margin-top:18px; flex-wrap:wrap }
.cta { display:inline-block; padding:10px 22px; border:1px solid var(--gold); color:var(--gold); border-radius:6px; text-decoration:none; font-size:13px; letter-spacing:0.12em; text-transform:uppercase }
.cta:hover { background:var(--gold); color:#1a1410 }
.stat-row { display:flex; gap:32px; justify-content:center; margin-top:28px; flex-wrap:wrap; color:var(--muted); font-size:12px; letter-spacing:0.18em; text-transform:uppercase }
.stat-row b { color:var(--gold); font-weight:400; font-size:14px; font-variant-numeric:tabular-nums }
.stat-row a { color:inherit; text-decoration:none; border-bottom:1px dotted transparent; transition:border-color .15s }
.stat-row a:hover { border-bottom-color:var(--gold); text-decoration:none }
main { max-width:1180px; margin:0 auto; padding:60px 24px 80px }
.rail-head { display:flex; align-items:baseline; justify-content:space-between; margin-bottom:18px; padding-bottom:8px; border-bottom:1px solid var(--line) }
.rail-head h2 { margin:0; font-size:14px; letter-spacing:0.18em; text-transform:uppercase; color:var(--gold) }
.rail-head a { color:var(--muted); font-size:12px; letter-spacing:0.12em; text-decoration:none; text-transform:uppercase }
.rail-head a:hover { color:var(--gold) }
.rail { display:grid; grid-template-columns:repeat(6, 1fr); gap:16px; margin-bottom:48px }
@media (max-width:900px) { .rail { grid-template-columns:repeat(3,1fr) } }
@media (max-width:540px) { .rail { grid-template-columns:repeat(2,1fr) } }
.rail-card { display:block; text-decoration:none; color:var(--ink); border:1px solid var(--line); border-radius:8px; overflow:hidden; background:rgba(201,161,75,0.03); transition:transform .15s }
.rail-card:hover { transform:translateY(-3px); border-color:var(--gold) }
.rail-poster { aspect-ratio:2/3; background:#15130f no-repeat center/cover; position:relative; overflow:hidden }
.rail-headshot { aspect-ratio:3/4; background:#15130f no-repeat center/cover; position:relative; overflow:hidden }
.rail-poster::after,.rail-headshot::after{content:"";position:absolute;inset:0;background-image:var(--card-bg,none);background-repeat:repeat;background-size:33.33% 33.33%;background-position:center;opacity:0;transition:opacity 360ms ease;pointer-events:none;will-change:opacity}
.rail-card:hover .rail-poster::after,.rail-card:hover .rail-headshot::after,.rail-card:focus-visible .rail-poster::after,.rail-card:focus-visible .rail-headshot::after{opacity:1}
@media (prefers-reduced-motion:reduce){.rail-poster::after,.rail-headshot::after{transition:none}}
.rail-title { padding:8px 10px 2px; font-size:12.5px; line-height:1.3; overflow:hidden; display:-webkit-box; -webkit-line-clamp:2; -webkit-box-orient:vertical }
.rail-meta { padding:0 10px 8px; color:var(--muted); font-size:10.5px; letter-spacing:0.06em }
.blurb-grid { display:grid; grid-template-columns:1fr 1fr; gap:24px; margin-top:24px }
@media (max-width:720px) { .blurb-grid { grid-template-columns:1fr } }
.blurb { padding:18px 20px; border:1px solid var(--line); border-radius:8px; background:rgba(201,161,75,0.02) }
.blurb h3 { margin:0 0 8px; font-size:13px; letter-spacing:0.14em; text-transform:uppercase; color:var(--gold) }
.blurb p { margin:0; color:var(--ink); font-size:13.5px }
.blurb small { display:block; margin-top:8px; color:var(--muted); font-size:11.5px }
code { font-family:ui-monospace,Menlo,monospace; font-size:12px; color:var(--gold); }
a { color:var(--gold); text-decoration:none; }
a:hover { text-decoration:underline; }
footer { border-top:1px solid var(--line); text-align:center; padding:22px; color:var(--muted); font-size:12px; letter-spacing:0.06em; }
</style>
</head>
<body>
<header>
<h1><span class="as">As</span>Seen<span class="as">In</span>Movies</h1>
<p class="tag">a film + tv reference, built on public data</p>
<div class="cta-row">
<a class="cta" href="/movies">Browse movies →</a>
<a class="cta" href="/people">Browse people →</a>
<a class="cta" href="/spotted">SPOTTED →</a>
</div>
<div class="stat-row">
<a href="/movies"><b>${Number(stats.movies_total).toLocaleString()}</b> movies</a>
<span><b>${Number(stats.movies_poster).toLocaleString()}</b> with posters</span>
<a href="/people"><b>${Number(stats.people_total).toLocaleString()}</b> people</a>
<span><b>${Number(stats.people_headshot).toLocaleString()}</b> with headshots</span>
<span><b>${Number(stats.credits_total || 0).toLocaleString()}</b> credits</span>
<a href="/spotted"><b>${Number(stats.spotted_total).toLocaleString()}</b> spotted</a>
</div>
</header>
<main>
${featuredMovies.length ? `
<div class="rail-head">
<h2>Featured movies</h2>
<a href="/movies">Browse all →</a>
</div>
<div class="rail">${movieCards}</div>` : ''}
${featuredPeople.length ? `
<div class="rail-head">
<h2>Featured people</h2>
<a href="/people">Browse all →</a>
</div>
<div class="rail">${peopleCards}</div>` : ''}
${featuredSpotted.length ? `
<div class="rail-head">
<h2>Recent SPOTTED</h2>
<a href="/spotted">Browse all →</a>
</div>
<div class="rail">${spottedCards}</div>` : ''}
<div class="blurb-grid">
<div class="blurb">
<h3>What this is</h3>
<p>An open IMDb-class reference built from <em>only</em> public data — TMDB, Wikidata, OMDb, MovieLens, and public union directories. Tied to <a href="https://thesetdecorator.com">thesetdecorator.com</a> for set-decorator credits and to the DW catalog for SPOTTED product placements.</p>
<small>No visual assets are sourced from IMDb. Posters and headshots that aren't rights-cleared use a "made with AI" placeholder.</small>
</div>
<div class="blurb">
<h3>SPOTTED</h3>
<p>Find the actual products you see on screen — wallpaper, lamps, props, wardrobe — tagged by set decorators and verified by claim-holders.</p>
<small>Live feed: <code>/api/spotted</code></small>
</div>
<div class="blurb">
<h3>Premium profiles</h3>
<p>Every actor, actress, and crew member has a basic public page built from public credits. Verified union members and their representation can upgrade to a flagship profile — reels, current projects, contact form, custom bio.</p>
<small>Stripe upgrade flow ships in a later tick.</small>
</div>
<div class="blurb">
<h3>API</h3>
<p>Read-only endpoints for movie metadata, people, search, and SPOTTED. Public-data-only — no IMDb scrape, no paywalled feeds.</p>
<small><code>/api/health</code> · <code>/api/search?q=</code> · <code>/api/movie/:id</code> · <code>/api/person/:id</code> · <code>/api/spotted</code></small>
</div>
</div>
</main>
<footer>
AsSeenInMovies · public-data only · © Designer Wallcoverings ·
<a href="/about" style="color:var(--gold)">About</a> ·
<a href="/privacy" style="color:var(--gold)">Privacy</a> ·
<a href="/terms" style="color:var(--gold)">Terms</a> ·
<a href="/api" style="color:var(--gold)">API</a><br>
<span style="font-size:10px;letter-spacing:0.1em;text-transform:uppercase;color:var(--muted)">
Movie metadata via <a href="https://www.themoviedb.org/" target="_blank" rel="noopener noreferrer" style="color:var(--gold)">TMDB</a> — this product uses the TMDB API but is not endorsed or certified by TMDB.
</span>
</footer>
</body></html>`);
});
// ────────────────────────────────────────────────────────────────────
// /sitemap.xml + /robots.txt — SEO surface. asseeninmovies has 290k movies
// and 61k people; we surface the static pages, then a popularity-capped
// slice of detail pages (top ~10k each by popularity / credit count) plus
// all verified SPOTTED pages. Sitemap protocol max is 50,000 URLs per
// file — we sit well under. Cached in-memory for 10 min so 290k count(*)
// doesn't hit on every crawler probe.
// ────────────────────────────────────────────────────────────────────
let _sitemapCache = { at: 0, xml: null };
let _sitemapInflight = null;
const SITEMAP_TTL_MS = 10 * 60 * 1000;
// Build the sitemap XML, populate the cache, and return it. Coalesces concurrent
// requests through _sitemapInflight so a cold cache + 2 simultaneous crawler
// hits trigger one DB pass, not two. Used by /sitemap.xml AND /sitemap.txt so
// they stay consistent and /sitemap.txt never returns a "warming" stub.
async function buildSitemapXml() {
if (_sitemapInflight) return _sitemapInflight;
_sitemapInflight = (async () => {
const base = process.env.ASIM_PUBLIC_URL || 'https://asseeninmovies.com';
const today = new Date().toISOString().slice(0, 10);
const STATIC = [
['/', '1.0', 'weekly'],
['/movies', '0.9', 'weekly'],
['/people', '0.9', 'weekly'],
['/spotted', '0.9', 'daily'],
['/spotted/submit', '0.4', 'monthly'],
['/search', '0.5', 'weekly'],
['/about', '0.5', 'monthly'],
['/privacy', '0.3', 'yearly'],
['/terms', '0.3', 'yearly'],
];
const [moviesR, peopleR, spottedR] = await Promise.all([
pool.query(`SELECT id FROM asim_movies WHERE poster_url IS NOT NULL ORDER BY popularity DESC NULLS LAST, id DESC LIMIT 10000`),
pool.query(`SELECT id FROM asim_people WHERE headshot_url IS NOT NULL ORDER BY id LIMIT 10000`),
pool.query(`SELECT id, movie_id FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')`)
]);
const urls = [];
for (const [path, pri, cf] of STATIC) {
urls.push(`<url><loc>${base}${path}</loc><lastmod>${today}</lastmod><changefreq>${cf}</changefreq><priority>${pri}</priority></url>`);
}
for (const m of moviesR.rows) {
urls.push(`<url><loc>${base}/movie/${m.id}</loc><lastmod>${today}</lastmod><changefreq>monthly</changefreq><priority>0.6</priority></url>`);
}
for (const p of peopleR.rows) {
urls.push(`<url><loc>${base}/person/${p.id}</loc><lastmod>${today}</lastmod><changefreq>monthly</changefreq><priority>0.5</priority></url>`);
}
for (const s of spottedR.rows) {
urls.push(`<url><loc>${base}/movie/${s.movie_id}#spotted-${s.id}</loc><lastmod>${today}</lastmod><changefreq>weekly</changefreq><priority>0.7</priority></url>`);
}
const xml = `<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n ${urls.join('\n ')}\n</urlset>`;
_sitemapCache = { at: Date.now(), xml };
return _sitemapCache;
})();
try { return await _sitemapInflight; }
finally { _sitemapInflight = null; }
}
app.get('/sitemap.xml', async (_req, res) => {
const now = Date.now();
try {
const cache = (_sitemapCache.xml && (now - _sitemapCache.at) < SITEMAP_TTL_MS)
? _sitemapCache
: await buildSitemapXml();
res.setHeader('Cache-Control', 'public, max-age=21600, stale-while-revalidate=86400');
res.setHeader('Last-Modified', new Date(cache.at).toUTCString());
res.type('application/xml').send(cache.xml);
} catch (e) {
res.status(500).type('text/plain').send(`error: ${e.message}`);
}
});
// /about — editorial-style explainer of what the site is, who runs it,
// where the data comes from, and the rules around imagery. Static (no DB).
app.get('/about', (_req, res) => {
const body = `
<div style="max-width:720px;margin:40px auto">
<h1 class="title" style="font-size:48px;margin-bottom:8px">About AsSeenInMovies</h1>
<p class="meta-line">A film + TV reference, built on public data.</p>
<section style="margin-top:36px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:22px;margin-bottom:12px;color:var(--gold)">What this is</h2>
<p style="color:var(--ink-soft);font-size:15.5px;line-height:1.7">AsSeenInMovies is an open IMDb-class reference for film and TV, sourced from public-domain and rights-cleared datasets only — TMDB, Wikidata, OMDb, MovieLens, and the public union directories. Two things make it distinctive:</p>
<ul style="color:var(--ink-soft);font-size:15px;line-height:1.8;padding-left:22px">
<li><strong>SPOTTED</strong> — set decor, wallcoverings, lighting and props identified frame-by-frame and tied back to the actual film. Tagged by set decorators and verified by claim-holders.</li>
<li><strong>Made-with-AI placeholders</strong> — anywhere we don't have a rights-cleared image, we use an AI-generated mark in its place. No visual asset is ever sourced from IMDb.</li>
</ul>
</section>
<section style="margin-top:36px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:22px;margin-bottom:12px;color:var(--gold)">Who runs it</h2>
<p style="color:var(--ink-soft);font-size:15.5px;line-height:1.7">Built and maintained by <a href="https://designerwallcoverings.com" style="color:var(--gold)">Designer Wallcoverings</a> in partnership with <a href="https://thesetdecorator.com" style="color:var(--gold)">thesetdecorator.com</a>. The SPOTTED placement tier surfaces real, shoppable products from the wallcoverings catalog when there's a verified match.</p>
</section>
<section style="margin-top:36px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:22px;margin-bottom:12px;color:var(--gold)">Submit a placement</h2>
<p style="color:var(--ink-soft);font-size:15.5px;line-height:1.7">Anyone can <a href="/spotted/submit" style="color:var(--gold)">submit a placement</a> — film/TV product spotters, set decorators, brand reps, fans. Submissions enter a moderation queue and ship live once verified. Reversible at any time.</p>
</section>
<section style="margin-top:36px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:22px;margin-bottom:12px;color:var(--gold)">Public data only</h2>
<p style="color:var(--ink-soft);font-size:15.5px;line-height:1.7">Movie metadata via <a href="https://www.themoviedb.org/" style="color:var(--gold)">TMDB</a> — this product uses the TMDB API but is not endorsed or certified by TMDB. People and credit data via TMDB + Wikidata SPARQL with CC0 license verification on every image. Set-decorator credits are imported from the public <a href="https://thesetdecorator.com" style="color:var(--gold)">Set Decorators Society</a> directory.</p>
</section>
</div>`;
res.type('html').send(htmlShell('About', body, '', 'https://asseeninmovies.com/about', 'AsSeenInMovies is an open IMDb-class film + TV reference built on public data, run by Designer Wallcoverings, featuring verified SPOTTED set-decor placements.'));
});
// /privacy — minimal privacy notice. Site does not collect PII; the only
// optional input is submitter email on /spotted/submit (used only to credit
// the submitter when a placement is verified, never displayed publicly).
app.get('/privacy', (_req, res) => {
const body = `
<div style="max-width:720px;margin:40px auto">
<h1 class="title" style="font-size:40px">Privacy</h1>
<p class="meta-line">Last updated 2026-05-12.</p>
<section style="margin-top:32px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">What we collect</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">AsSeenInMovies does not require an account, does not store cookies for identification, and does not collect personal data beyond what server logs naturally record (IP address, user-agent, request path) for security and abuse mitigation. The only optional user input is on the <a href="/spotted/submit" style="color:var(--gold)">submit a placement</a> form, where you may provide your name + email to be credited when your submission is verified.</p>
</section>
<section style="margin-top:28px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Third-party services</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">Movie metadata via the <a href="https://www.themoviedb.org/" style="color:var(--gold)">TMDB</a> API. Anonymous usage analytics may be collected (page paths and aggregate counts only, no individual user tracking).</p>
</section>
<section style="margin-top:28px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Contact</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">Questions about data, takedown requests, or to remove your submitted email: <a href="mailto:info@asseeninmovies.com" style="color:var(--gold)">info@asseeninmovies.com</a>.</p>
</section>
</div>`;
res.type('html').send(htmlShell('Privacy', body, '', 'https://asseeninmovies.com/privacy', 'AsSeenInMovies privacy notice — what we collect (almost nothing), third-party services, contact.'));
});
// /terms — minimal terms / use policy. Public-data, public-submission, no
// guarantee of accuracy, claim-yours-back available.
app.get('/terms', (_req, res) => {
const body = `
<div style="max-width:720px;margin:40px auto">
<h1 class="title" style="font-size:40px">Terms of use</h1>
<p class="meta-line">Last updated 2026-05-12.</p>
<section style="margin-top:32px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Public data</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">Movie + person metadata is sourced from public-domain or rights-cleared datasets (TMDB, Wikidata, OMDb, MovieLens, union directories). We make no warranty as to accuracy or completeness. If you find an error, <a href="mailto:info@asseeninmovies.com" style="color:var(--gold)">tell us</a>.</p>
</section>
<section style="margin-top:28px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Submitting content</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">By submitting a SPOTTED placement, you confirm the information is accurate to the best of your knowledge and that you have the right to share it. We reserve the right to edit, decline, or remove any submission. All submissions go through moderation before going live, and you may request removal of your contribution at any time.</p>
</section>
<section style="margin-top:28px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Imagery</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">Posters and headshots are either rights-cleared (PD / CC0 / CC-BY) or replaced with a <span class="ai-pill">made with AI</span> placeholder. We never source visual assets from IMDb. If you hold rights to an image displayed here in error, contact us for immediate removal.</p>
</section>
<section style="margin-top:28px">
<h2 style="font-family:var(--serif);font-weight:400;font-size:20px;margin-bottom:10px;color:var(--gold)">Liability</h2>
<p style="color:var(--ink-soft);font-size:15px;line-height:1.7">The site is provided as-is. No commercial transaction takes place here. Shop links are affiliate or vendor references; you transact with the vendor directly under their terms.</p>
</section>
</div>`;
res.type('html').send(htmlShell('Terms', body, '', 'https://asseeninmovies.com/terms', 'AsSeenInMovies terms of use — public data, user submissions, imagery policy, liability.'));
});
// /random/movie + /random/person — pick a random visually-rich row and 302
// to its detail page. Useful for "I'm feeling lucky" exploration. Limits
// scope to the partial idx_*_has_poster / has_headshot indexes so the
// random pick stays fast (TABLESAMPLE on poster-having only).
app.get('/random/movie', async (_req, res) => {
try {
const r = await pool.query(`SELECT id FROM asim_movies WHERE poster_url IS NOT NULL ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.redirect('/movies');
res.redirect(`/movie/${r.rows[0].id}`);
} catch (_e) { res.redirect('/movies'); }
});
app.get('/random/person', async (_req, res) => {
try {
const r = await pool.query(`SELECT id FROM asim_people WHERE headshot_url IS NOT NULL ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.redirect('/people');
res.redirect(`/person/${r.rows[0].id}`);
} catch (_e) { res.redirect('/people'); }
});
app.get('/random/spotted', async (_req, res) => {
try {
const r = await pool.query(`SELECT movie_id FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') ORDER BY random() LIMIT 1`);
if (!r.rows[0]) return res.redirect('/spotted');
res.redirect(`/movie/${r.rows[0].movie_id}#spotted`);
} catch (_e) { res.redirect('/spotted'); }
});
// Bots and link previewers that don't honor <link rel="icon"> in the head
// still GET /favicon.ico — serve the same inline SVG so server logs don't
// fill with 404s and previews show the brand mark.
const FAVICON_SVG = `<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 32 32"><rect width="32" height="32" rx="6" fill="#0d0c0a"/><text x="16" y="22" font-family="Georgia,serif" font-style="italic" font-size="20" fill="#c9a14b" text-anchor="middle">A</text></svg>`;
app.get('/favicon.ico', (_req, res) => {
res.type('image/svg+xml').setHeader('Cache-Control', 'public, max-age=86400').send(FAVICON_SVG);
});
// /sitemap.txt — plain-text Google-supported sitemap format. Same URL set
// as /sitemap.xml but one URL per line, no XML wrapping. Some crawlers
// prefer this; some tools (Bing Webmaster) accept it as fallback.
app.get('/sitemap.txt', async (_req, res) => {
try {
const cache = (_sitemapCache.xml && (Date.now() - _sitemapCache.at) < SITEMAP_TTL_MS)
? _sitemapCache
: await buildSitemapXml();
const urls = (cache.xml.match(/<loc>([^<]+)<\/loc>/g) || []).map(m => m.slice(5, -6));
res.setHeader('Cache-Control', 'public, max-age=21600, stale-while-revalidate=86400');
res.setHeader('Last-Modified', new Date(cache.at).toUTCString());
res.type('text/plain').send(urls.join('\n') + '\n');
} catch (e) {
res.status(500).type('text/plain').send(`error: ${e.message}`);
}
});
app.get('/robots.txt', (_req, res) => {
const base = process.env.ASIM_PUBLIC_URL || 'https://asseeninmovies.com';
res.setHeader('Cache-Control', 'public, max-age=86400');
res.type('text/plain').send(`User-agent: *\nAllow: /\nDisallow: /spotted/queue\nDisallow: /api/\nSitemap: ${base}/sitemap.xml\nSitemap: ${base}/sitemap.txt\n`);
});
// Branded 404 — was leaking the stock Express "Cannot GET /…" plain-text
// page. Now uses htmlShell so theme + nav + footer are consistent, plus
// suggested paths to recover (movies / people / spotted / search / random).
app.use((req, res) => {
if (req.path.startsWith('/api/')) {
return res.status(404).json({ error: 'not-found', path: req.path });
}
const body = `
<div style="max-width:600px;margin:80px auto;text-align:center">
<h1 class="title" style="font-size:96px;color:var(--gold);margin:0">404</h1>
<p class="meta-line" style="margin:8px 0 28px">${esc(req.path)} — page not found</p>
<p style="color:var(--ink-soft);margin-bottom:32px">The film, person, or page you were looking for isn't here. A few places to recover:</p>
<div style="display:flex;flex-wrap:wrap;gap:10px;justify-content:center">
<a class="chip" href="/" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">Home</a>
<a class="chip" href="/movies" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">Movies</a>
<a class="chip" href="/people" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">People</a>
<a class="chip" href="/spotted" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">Spotted</a>
<a class="chip" href="/search" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">Search</a>
<a class="chip" href="/random/movie" style="border-color:var(--gold);color:var(--gold);padding:8px 18px;text-decoration:none">🎲 Random movie</a>
</div>
</div>`;
res.status(404).type('html').send(htmlShell('Not Found', body));
});
app.listen(PORT, '127.0.0.1', async () => {
await ensureSchema();
console.log(`asseeninmovies → http://127.0.0.1:${PORT}`);
// Prime the home-page cache so the first real request doesn't wait on
// cold count(*) queries (2-3s each on 290k+61k rows).
getHomeCache()
.then(d => console.log(`[home-cache] warm — movies=${d.stats.movies_total} people=${d.stats.people_total} spotted=${d.stats.spotted_total}`))
.catch(e => console.warn(`[home-cache] warmup failed: ${e.message}`));
// Keep the home cache fresh during quiet periods so the next-traffic
// burst doesn't hit cold count(*). 45s is below the 60s TTL so the
// cache never goes stale on a populated process. unref() lets the
// timer never block shutdown.
setInterval(() => {
startHomeRefresh().catch(() => {});
}, 45_000).unref();
});