← back to Costa Rica
server.js
643 lines
'use strict';
require('dotenv').config();
const express = require('express');
const basicAuth = require('express-basic-auth');
const { Pool } = require('pg');
const path = require('path');
const PORT = parseInt(process.env.PORT || '9791', 10);
const SITE_NAME = process.env.SITE_NAME || 'Costa Rica Directory';
const SITE_DOMAIN = process.env.SITE_DOMAIN || 'costarica.agentabrams.com';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
app.set('trust proxy', true);
app.use(express.json({ limit: '1mb' }));
app.use(express.urlencoded({ extended: true }));
// Cloudflare HTML caching guard (per MEMORY.md)
app.use((req, res, next) => {
if (req.path === '/' || req.path.endsWith('.html')) {
res.set('Cache-Control', 'no-store, must-revalidate');
}
next();
});
// Whole-site Basic Auth gate (in-development; remove when ready for public)
const BA_USER = process.env.BASIC_AUTH_USER;
const BA_PASS = process.env.BASIC_AUTH_PASS;
if (BA_USER && BA_PASS) {
app.use(basicAuth({
users: { [BA_USER]: BA_PASS },
challenge: true,
realm: 'CR-Directory',
}));
} else {
console.warn('[boot] BASIC_AUTH_USER/PASS not set — site is OPEN');
}
app.get('/health', (_req, res) => res.json({ ok: true, site: SITE_NAME, ts: new Date().toISOString() }));
const VERTICALS = {
tourism: ['tourism_hotel','tourism_tour','tourism_beach','tourism_restaurant','tourism_surf'],
rentals: ['rentals_short','rentals_long','rentals_realestate'],
service: ['service_food','service_beauty','service_retail','service_fitness','service_pet','service_auto','service_cleaning','service_creative'],
};
function sortClause(sort) {
switch ((sort || '').toLowerCase()) {
case 'name': return 'ORDER BY LOWER(p.name) ASC';
case 'name-desc': return 'ORDER BY LOWER(p.name) DESC';
case 'rating': return 'ORDER BY p.rating DESC NULLS LAST, p.id DESC';
case 'verified': return 'ORDER BY p.verified DESC, p.id DESC';
case 'newest': return 'ORDER BY p.id DESC';
case 'oldest': return 'ORDER BY p.id ASC';
default: return 'ORDER BY p.id DESC';
}
}
app.get('/api/provinces', async (_req, res) => {
try {
const { rows } = await pool.query(`
WITH province_totals AS (
SELECT r.province AS name, COUNT(p.id)::int AS total_places
FROM regions r LEFT JOIN places p ON p.region_id = r.id AND p.status='active'
GROUP BY r.province
),
top_regions AS (
SELECT r.province, r.slug, r.name AS region_name, r.image_url,
COUNT(p.id)::int AS n,
ROW_NUMBER() OVER (PARTITION BY r.province ORDER BY COUNT(p.id) DESC, r.name ASC) AS rk
FROM regions r LEFT JOIN places p ON p.region_id = r.id AND p.status='active'
GROUP BY r.id
)
SELECT pt.name AS province, pt.total_places,
json_agg(json_build_object('slug', tr.slug, 'name', tr.region_name, 'image_url', tr.image_url, 'n', tr.n)
ORDER BY tr.rk) FILTER (WHERE tr.rk <= 6) AS top_regions
FROM province_totals pt
LEFT JOIN top_regions tr ON tr.province = pt.name
WHERE pt.name IS NOT NULL
GROUP BY pt.name, pt.total_places
ORDER BY pt.total_places DESC, pt.name ASC`);
res.json({ provinces: rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Province detail: all cantones + by-vertical totals + 8 sample places
const PROVINCE_SLUGS = {
'san-jose': 'San José',
'alajuela': 'Alajuela',
'heredia': 'Heredia',
'cartago': 'Cartago',
'guanacaste': 'Guanacaste',
'puntarenas': 'Puntarenas',
'limon': 'Limón',
};
app.get('/api/provinces/:slug', async (req, res) => {
try {
const provName = PROVINCE_SLUGS[req.params.slug];
if (!provName) return res.status(404).json({ error: 'unknown province' });
const [{ rows: cantones }, { rows: byVert }, { rows: samples }, { rows: totalRow }] = await Promise.all([
pool.query(
`SELECT r.slug, r.name, r.image_url, r.image_credit, r.image_source_url,
COUNT(p.id)::int AS n
FROM regions r LEFT JOIN places p ON p.region_id = r.id AND p.status='active'
WHERE r.province = $1
GROUP BY r.id
ORDER BY n DESC, r.name ASC`, [provName]
),
pool.query(
`SELECT p.vertical, COUNT(*)::int AS n
FROM places p JOIN regions r ON r.id = p.region_id
WHERE r.province = $1 AND p.status='active'
GROUP BY p.vertical
ORDER BY n DESC LIMIT 12`, [provName]
),
pool.query(
`SELECT p.slug, p.name, p.vertical, p.image_url, p.address,
r.slug AS region_slug, r.name AS region_name,
r.image_url AS region_image_url
FROM places p JOIN regions r ON r.id = p.region_id
WHERE r.province = $1 AND p.status='active'
ORDER BY (p.image_url IS NOT NULL) DESC, p.id DESC
LIMIT 8`, [provName]
),
pool.query(
`SELECT COUNT(p.id)::int AS total
FROM places p JOIN regions r ON r.id = p.region_id
WHERE r.province = $1 AND p.status='active'`, [provName]
),
]);
res.json({
slug: req.params.slug,
name: provName,
total: totalRow[0]?.total || 0,
cantones,
by_vertical: byVert,
samples,
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Vertical = e.g. service_retail, tourism_hotel, rentals_realestate.
// Slug uses hyphens: service_retail <-> service-retail.
const vSlug = v => String(v || '').replaceAll('_', '-').toLowerCase();
const vUnSlug = s => String(s || '').replaceAll('-', '_').toLowerCase();
app.get('/api/verticals', async (_req, res) => {
try {
const { rows } = await pool.query(`
WITH counts AS (
SELECT vertical, category, COUNT(*)::int AS n
FROM places WHERE status='active' GROUP BY vertical, category
),
samples AS (
SELECT DISTINCT ON (p.vertical) p.vertical, p.image_url, r.image_url AS region_image_url
FROM places p LEFT JOIN regions r ON r.id = p.region_id
WHERE p.status='active'
ORDER BY p.vertical, (p.image_url IS NOT NULL) DESC, p.id DESC
)
SELECT c.vertical, c.category, c.n,
COALESCE(s.image_url, s.region_image_url) AS image_url
FROM counts c LEFT JOIN samples s ON s.vertical = c.vertical
ORDER BY c.n DESC, c.vertical ASC`);
res.json({ verticals: rows.map(r => ({ ...r, slug: vSlug(r.vertical) })) });
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/verticals/:slug', async (req, res) => {
try {
const vertical = vUnSlug(req.params.slug);
const [{ rows: meta }, { rows: byRegion }, { rows: byProv }, { rows: samples }] = await Promise.all([
pool.query(
`SELECT vertical, category, COUNT(*)::int AS total
FROM places WHERE vertical=$1 AND status='active'
GROUP BY vertical, category`, [vertical]
),
pool.query(
`SELECT r.slug, r.name, r.province, r.image_url, COUNT(p.id)::int AS n
FROM places p JOIN regions r ON r.id = p.region_id
WHERE p.vertical=$1 AND p.status='active'
GROUP BY r.id
ORDER BY n DESC, r.name ASC LIMIT 24`, [vertical]
),
pool.query(
`SELECT r.province AS name, COUNT(p.id)::int AS n
FROM places p JOIN regions r ON r.id = p.region_id
WHERE p.vertical=$1 AND p.status='active'
GROUP BY r.province ORDER BY n DESC`, [vertical]
),
pool.query(
`SELECT p.slug, p.name, p.image_url, p.address, p.website,
r.slug AS region_slug, r.name AS region_name, r.province,
r.image_url AS region_image_url
FROM places p JOIN regions r ON r.id = p.region_id
WHERE p.vertical=$1 AND p.status='active'
ORDER BY (p.image_url IS NOT NULL) DESC, p.id DESC LIMIT 24`, [vertical]
),
]);
if (!meta.length) return res.status(404).json({ error: 'unknown vertical' });
res.json({
slug: req.params.slug,
vertical,
category: meta[0].category,
total: meta[0].total,
by_region: byRegion,
by_province: byProv,
samples,
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Cross-entity search: places + regions + provinces, grouped & ranked
app.get('/api/search', async (req, res) => {
try {
const q = String(req.query.q || '').trim();
if (!q || q.length < 2) return res.json({ q, places: [], regions: [], provinces: [], counts: { places: 0, regions: 0, provinces: 0 } });
const placeLimit = Math.min(parseInt(req.query.place_limit || '24', 10) || 24, 60);
const regionLimit = Math.min(parseInt(req.query.region_limit || '12', 10) || 12, 30);
const pat = `%${q.toLowerCase()}%`;
const exactPat = q.toLowerCase();
const [{ rows: places }, { rows: placesCnt }, { rows: regions }, { rows: regionsCnt }] = await Promise.all([
pool.query(
`SELECT p.slug, p.name, p.vertical, p.category, p.address, p.image_url, p.cedula_juridica,
r.slug AS region_slug, r.name AS region_name, r.province, r.image_url AS region_image_url,
CASE WHEN LOWER(p.name) = $2 THEN 100
WHEN LOWER(p.name) LIKE $2 || '%' THEN 80
WHEN LOWER(p.name) LIKE '%' || $2 || '%' THEN 50
ELSE 10 END AS rank
FROM places p LEFT JOIN regions r ON r.id = p.region_id
WHERE p.status='active'
AND (LOWER(p.name) LIKE $1 OR LOWER(p.address) LIKE $1 OR LOWER(p.description) LIKE $1)
ORDER BY rank DESC, (p.image_url IS NOT NULL) DESC, p.name ASC
LIMIT $3`, [pat, exactPat, placeLimit]
),
pool.query(
`SELECT COUNT(*)::int AS total FROM places p
WHERE p.status='active'
AND (LOWER(p.name) LIKE $1 OR LOWER(p.address) LIKE $1 OR LOWER(p.description) LIKE $1)`, [pat]
),
pool.query(
`SELECT r.slug, r.name, r.province, r.image_url, r.image_credit, r.image_source_url,
COUNT(p.id)::int AS n,
CASE WHEN LOWER(r.name) = $2 THEN 100
WHEN LOWER(r.name) LIKE $2 || '%' THEN 80
WHEN LOWER(r.name) LIKE '%' || $2 || '%' THEN 50
ELSE 10 END AS rank
FROM regions r LEFT JOIN places p ON p.region_id = r.id AND p.status='active'
WHERE LOWER(r.name) LIKE $1
GROUP BY r.id
ORDER BY rank DESC, n DESC, r.name ASC
LIMIT $3`, [pat, exactPat, regionLimit]
),
pool.query(
`SELECT COUNT(*)::int AS total FROM regions r WHERE LOWER(r.name) LIKE $1`, [pat]
),
]);
// Provinces: small fixed list, ILIKE on canonical names
const allProv = ['San José','Alajuela','Heredia','Cartago','Guanacaste','Puntarenas','Limón'];
const provMatches = allProv
.filter(p => p.toLowerCase().includes(exactPat))
.map(p => ({ name: p, slug: { 'San José':'san-jose','Alajuela':'alajuela','Heredia':'heredia','Cartago':'cartago','Guanacaste':'guanacaste','Puntarenas':'puntarenas','Limón':'limon' }[p] }));
res.json({
q,
places, regions, provinces: provMatches,
counts: { places: placesCnt[0]?.total || 0, regions: regionsCnt[0]?.total || 0, provinces: provMatches.length },
});
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/regions', async (_req, res) => {
try {
const { rows } = await pool.query(`
SELECT r.id, r.slug, r.name, r.province, r.region_type, r.lat, r.lng,
COUNT(p.id)::int AS place_count
FROM regions r
LEFT JOIN places p ON p.region_id = r.id AND p.status = 'active'
GROUP BY r.id
ORDER BY r.name ASC
`);
res.json({ regions: rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.get('/api/places', async (req, res) => {
try {
const limit = Math.min(parseInt(req.query.limit || '60', 10) || 60, 250);
const offset = Math.max(parseInt(req.query.offset || '0', 10) || 0, 0);
const sort = sortClause(req.query.sort);
const where = ["p.status = 'active'"];
const args = [];
if (req.query.category) {
args.push(req.query.category);
where.push(`p.category = $${args.length}`);
}
if (req.query.vertical) {
args.push(req.query.vertical);
where.push(`p.vertical = $${args.length}`);
}
if (req.query.region) {
args.push(req.query.region);
where.push(`r.slug = $${args.length}`);
}
if (req.query.q) {
args.push(`%${req.query.q.toLowerCase()}%`);
where.push(`(LOWER(p.name) LIKE $${args.length} OR LOWER(p.description) LIKE $${args.length} OR LOWER(p.address) LIKE $${args.length})`);
}
const sql = `
SELECT p.id, p.slug, p.name, p.category, p.vertical, p.description, p.address,
p.phone, p.email, p.website, p.price_range, p.rating, p.image_url, p.tags,
p.lat, p.lng, p.verified, p.source, p.source_url, p.created_at, p.cedula_juridica,
p.image_credit, p.image_license, p.image_source_url,
r.slug AS region_slug, r.name AS region_name, r.province,
r.image_url AS region_image_url, r.image_credit AS region_image_credit,
r.image_source_url AS region_image_source_url, r.lat AS region_lat, r.lng AS region_lng,
COALESCE(p.image_url, r.image_url) AS effective_image_url,
COALESCE(p.image_credit, r.image_credit) AS effective_image_credit,
COALESCE(p.image_source_url, r.image_source_url) AS effective_image_source_url
FROM places p
LEFT JOIN regions r ON p.region_id = r.id
WHERE ${where.join(' AND ')}
${sort}
LIMIT ${limit} OFFSET ${offset}
`;
const { rows } = await pool.query(sql, args);
const countArgs = args.slice();
const countSql = `
SELECT COUNT(*)::int AS total
FROM places p
LEFT JOIN regions r ON p.region_id = r.id
WHERE ${where.join(' AND ')}
`;
const { rows: [{ total }] } = await pool.query(countSql, countArgs);
// RFC 8288 Link header — canonical + prev/next/alternate (VCL pattern)
const baseUrl = (process.env.PUBLIC_URL || `https://${SITE_DOMAIN}`).replace(/\/+$/, '');
const qs = new URLSearchParams();
for (const k of ['q','category','vertical','region','sort']) if (req.query[k]) qs.set(k, req.query[k]);
const buildUrl = (path, n) => {
const params = new URLSearchParams(qs);
if (n != null) params.set('offset', String(n));
params.set('limit', String(limit));
return `${baseUrl}${path}${params.toString() ? '?' + params.toString() : ''}`;
};
const linkParts = [`<${buildUrl('/api/places', offset)}>; rel="canonical"`];
if (offset > 0) linkParts.push(`<${buildUrl('/api/places', Math.max(0, offset - limit))}>; rel="prev"`);
if (offset + limit < total) linkParts.push(`<${buildUrl('/api/places', offset + limit)}>; rel="next"`);
res.set('Link', linkParts.join(', '));
res.json({ total, limit, offset, places: rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// JSON mirror of the homepage / region listing — surfaced via rel="alternate"
// HTML link tag + HTTP Link header so apps + agents can fetch the same paginated
// results without HTML scraping (VCL pattern, RFC 8288).
app.get('/api/find', async (req, res) => {
// /api/find is just /api/places under a more discoverable name.
req.url = '/api/places' + (req.url.includes('?') ? req.url.slice(req.url.indexOf('?')) : '');
return app._router.handle(req, res, () => {});
});
app.get('/api/places/:slug', async (req, res) => {
try {
const { rows } = await pool.query(`
SELECT p.*,
r.slug AS region_slug, r.name AS region_name, r.province,
r.image_url AS region_image_url, r.image_credit AS region_image_credit,
r.image_source_url AS region_image_source_url,
r.lat AS region_lat, r.lng AS region_lng,
COALESCE(p.image_url, r.image_url) AS effective_image_url,
COALESCE(p.image_credit, r.image_credit) AS effective_image_credit,
COALESCE(p.image_source_url, r.image_source_url) AS effective_image_source_url
FROM places p
LEFT JOIN regions r ON p.region_id = r.id
WHERE p.slug = $1
`, [req.params.slug]);
if (!rows.length) return res.status(404).json({ error: 'not_found' });
const place = rows[0];
// Sibling listings — "More in {region}" cross-linking (VCL pattern)
if (place.region_id) {
const { rows: siblings } = await pool.query(`
SELECT slug, name, vertical, image_url
FROM places
WHERE region_id = $1 AND id != $2 AND status = 'active'
ORDER BY id DESC LIMIT 8
`, [place.region_id, place.id]);
place.siblings_in_region = siblings;
} else {
place.siblings_in_region = [];
}
res.json(place);
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.get('/api/stats', async (_req, res) => {
try {
const { rows: byCat } = await pool.query(
`SELECT category, COUNT(*)::int AS n FROM places WHERE status='active' GROUP BY category ORDER BY n DESC`
);
const { rows: byVert } = await pool.query(
`SELECT vertical, COUNT(*)::int AS n FROM places WHERE status='active' GROUP BY vertical ORDER BY n DESC`
);
const { rows: byRegion } = await pool.query(`
SELECT r.slug, r.name, COUNT(p.id)::int AS n
FROM regions r
LEFT JOIN places p ON p.region_id = r.id AND p.status='active'
GROUP BY r.id ORDER BY n DESC, r.name ASC
`);
const { rows: [{ total }] } = await pool.query(
`SELECT COUNT(*)::int AS total FROM places WHERE status='active'`
);
res.json({ total, by_category: byCat, by_vertical: byVert, by_region: byRegion, verticals: VERTICALS });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.post('/api/leads', async (req, res) => {
try {
const { place_id, name, email, phone, message, meta } = req.body || {};
const { rows } = await pool.query(`
INSERT INTO leads (place_id, name, email, phone, message, meta, ip, user_agent)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING id
`, [place_id || null, name, email, phone, message, meta || {}, req.ip, req.get('user-agent') || '']);
res.json({ ok: true, id: rows[0].id });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
app.get('/api/ingest/runs', async (_req, res) => {
try {
const { rows } = await pool.query(
`SELECT id, source, started_at, finished_at, rows_in, rows_added, rows_updated, status, notes
FROM ingest_runs ORDER BY started_at DESC LIMIT 50`
);
res.json({ runs: rows });
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// SEO: robots.txt — keep /api/* + /unsubscribe + /admin out of search
app.get('/robots.txt', (_req, res) => {
const url = (process.env.PUBLIC_URL || `https://${SITE_DOMAIN}`).replace(/\/+$/, '');
res.type('text/plain').send(
`User-agent: *\nAllow: /\nDisallow: /api/\n\nSitemap: ${url}/sitemap.xml\n`
);
});
// SEO: sitemap.xml — VCL pattern.
// Static + per-region + per-vertical + per-place. Capped at 50k URLs for now;
// when we exceed that we paginate via sitemap-index.
app.get('/sitemap.xml', async (_req, res, next) => {
try {
const baseUrl = (process.env.PUBLIC_URL || `https://${SITE_DOMAIN}`).replace(/\/+$/, '');
const escape = (s) => String(s).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/"/g,'"').replace(/'/g,''');
const today = new Date().toISOString().slice(0, 10);
const { rows: regions } = await pool.query(
`SELECT slug, name FROM regions WHERE region_type IN ('city','town','canton') ORDER BY name`
);
const { rows: verticals } = await pool.query(
`SELECT DISTINCT vertical FROM places WHERE status='active' ORDER BY vertical`
);
const { rows: places } = await pool.query(
`SELECT slug, updated_at, image_url FROM places WHERE status='active' ORDER BY id DESC LIMIT 48000`
);
const staticPages = [
{ loc: '/', changefreq: 'daily', priority: '1.0' },
{ loc: '/provinces', changefreq: 'weekly', priority: '0.8' },
{ loc: '/verticals', changefreq: 'weekly', priority: '0.8' },
{ loc: '/about', changefreq: 'monthly', priority: '0.7' },
{ loc: '/search', changefreq: 'weekly', priority: '0.6' },
{ loc: '/stats', changefreq: 'weekly', priority: '0.5' },
{ loc: '/?category=tourism', changefreq: 'daily', priority: '0.8' },
{ loc: '/?category=rentals', changefreq: 'daily', priority: '0.8' },
{ loc: '/?category=service', changefreq: 'daily', priority: '0.8' },
...Object.keys(PROVINCE_SLUGS).map(s => ({ loc: `/pr/${s}`, changefreq: 'weekly', priority: '0.85' })),
...verticals.map(v => ({ loc: `/v/${vSlug(v.vertical)}`, changefreq: 'weekly', priority: '0.7' })),
];
const regionPages = regions.map(r => ({ loc: `/r/${r.slug}`, changefreq: 'weekly', priority: '0.7' }));
let xml = '<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">\n';
for (const p of [...staticPages, ...regionPages]) {
xml += ` <url><loc>${baseUrl}${escape(p.loc)}</loc><lastmod>${today}</lastmod><changefreq>${p.changefreq}</changefreq><priority>${p.priority}</priority></url>\n`;
}
for (const pl of places) {
const lastmod = (pl.updated_at instanceof Date ? pl.updated_at : new Date(pl.updated_at || Date.now())).toISOString().slice(0, 10);
const loc = `${baseUrl}/p/${escape(pl.slug)}`;
let imgBlock = '';
if (pl.image_url) imgBlock = `<image:image><image:loc>${escape(pl.image_url)}</image:loc></image:image>`;
xml += ` <url><loc>${loc}</loc><lastmod>${lastmod}</lastmod><changefreq>weekly</changefreq><priority>0.5</priority>${imgBlock}</url>\n`;
}
xml += '</urlset>\n';
res.set('Content-Type', 'application/xml; charset=utf-8');
res.set('Cache-Control', 'public, max-age=3600');
res.send(xml);
} catch (err) { next(err); }
});
// JSON-LD LocalBusiness payload for a single place — embedded by /p/<slug> client.
app.get('/api/places/:slug/jsonld', async (req, res) => {
try {
const { rows } = await pool.query(`
SELECT p.*, r.name AS region_name, r.province
FROM places p LEFT JOIN regions r ON p.region_id = r.id
WHERE p.slug = $1`, [req.params.slug]);
if (!rows.length) return res.status(404).json({});
const p = rows[0];
const baseUrl = (process.env.PUBLIC_URL || `https://${SITE_DOMAIN}`).replace(/\/+$/, '');
const ld = {
'@context': 'https://schema.org',
'@type': 'LocalBusiness',
'@id': `${baseUrl}/p/${p.slug}`,
name: p.name,
description: p.description || undefined,
url: p.website || `${baseUrl}/p/${p.slug}`,
image: p.image_url || undefined,
telephone: p.phone || undefined,
email: p.email || undefined,
identifier: p.cedula_juridica ? { '@type': 'PropertyValue', name: 'Cédula jurídica', value: p.cedula_juridica } : undefined,
address: (p.address || p.region_name) ? {
'@type': 'PostalAddress',
streetAddress: p.address || undefined,
addressLocality: p.region_name || undefined,
addressRegion: p.province || undefined,
addressCountry: 'CR',
} : undefined,
geo: (p.lat && p.lng) ? { '@type': 'GeoCoordinates', latitude: p.lat, longitude: p.lng } : undefined,
aggregateRating: p.rating ? { '@type': 'AggregateRating', ratingValue: p.rating, bestRating: 5 } : undefined,
};
Object.keys(ld).forEach(k => ld[k] === undefined && delete ld[k]);
res.json(ld);
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Region landing page — dedicated layout with hero image + region map.
app.get('/r/:slug', async (req, res) => {
try {
const { rows } = await pool.query('SELECT slug FROM regions WHERE slug = $1', [req.params.slug]);
if (!rows.length) return res.status(404).sendFile(path.join(__dirname, 'public', '404.html'));
res.sendFile(path.join(__dirname, 'public', 'region.html'));
} catch (e) { res.status(500).json({ error: e.message }); }
});
// Region details endpoint (one region + count + sibling regions in same province)
app.get('/api/regions/:slug', async (req, res) => {
try {
const { rows } = await pool.query(`
SELECT r.id, r.slug, r.name, r.province, r.region_type, r.lat, r.lng,
r.image_url, r.image_credit, r.image_license, r.image_source_url,
r.description,
COUNT(p.id)::int AS place_count
FROM regions r
LEFT JOIN places p ON p.region_id = r.id AND p.status = 'active'
WHERE r.slug = $1
GROUP BY r.id`, [req.params.slug]);
if (!rows.length) return res.status(404).json({ error: 'not_found' });
const region = rows[0];
const { rows: byVert } = await pool.query(
`SELECT vertical, COUNT(*)::int AS n FROM places WHERE region_id=$1 AND status='active' GROUP BY vertical ORDER BY n DESC`,
[region.id]
);
const { rows: provincial } = await pool.query(
`SELECT r.slug, r.name, r.image_url, COUNT(p.id)::int AS n
FROM regions r LEFT JOIN places p ON p.region_id=r.id AND p.status='active'
WHERE r.province = $1 AND r.id != $2 AND r.region_type IN ('city','town','canton')
GROUP BY r.id ORDER BY n DESC NULLS LAST, r.name LIMIT 12`,
[region.province, region.id]
);
region.by_vertical = byVert;
region.nearby_in_province = provincial;
res.json(region);
} catch (e) {
res.status(500).json({ error: e.message });
}
});
// Place detail page
app.get('/p/:slug', async (req, res) => {
try {
const { rows } = await pool.query(`
SELECT p.*, r.slug AS region_slug, r.name AS region_name, r.province
FROM places p
LEFT JOIN regions r ON p.region_id = r.id
WHERE p.slug = $1
`, [req.params.slug]);
if (!rows.length) return res.status(404).sendFile(path.join(__dirname, 'public', '404.html'));
res.sendFile(path.join(__dirname, 'public', 'place.html'));
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/stats', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'stats.html')));
app.get('/provinces', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'provinces.html')));
app.get('/search', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'search.html')));
app.get('/verticals', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'verticals.html')));
app.get('/about', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'about.html')));
app.get('/v/:slug', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'vertical.html')));
app.get('/pr/:slug', async (req, res) => {
if (!PROVINCE_SLUGS[req.params.slug]) return res.status(404).sendFile(path.join(__dirname, 'public', '404.html'));
res.sendFile(path.join(__dirname, 'public', 'province.html'));
});
// Snapshot-file 404 guard — refuse to ever serve .bak / .bak.* / .pre-* /
// .orig editor leftovers, even if one slips into public/ by accident.
app.use((req, res, next) => {
if (/\.(bak|orig)(\.|$)|\.pre-/i.test(req.path)) {
return res.status(404).type('text/plain').send('not found');
}
next();
});
app.use(express.static(path.join(__dirname, 'public')));
app.listen(PORT, '0.0.0.0', () => {
console.log(`[${SITE_NAME}] listening on :${PORT} — gated as ${BA_USER || 'OPEN'} — domain ${SITE_DOMAIN}`);
});