← back to Stayclaim
src/components/home/CommunityHome.tsx
213 lines
/**
* Bubbe's Block — community home. Neighborhood-focused.
* Hero: warm gold, neighborhood-first navigation.
* Show neighborhoods as the unit of community, not individual addresses.
*/
import Link from 'next/link';
import { unstable_cache } from 'next/cache';
import { pool } from '@/lib/db';
import { sisterSurfaces } from '@/lib/site';
export const dynamic = 'force-dynamic';
type Hood = { city: string; listing_count: string; recent_permits: string };
type Activity = { kind: string; label: string | null; address: string | null; when_: string | null; slug: string; city: string | null };
async function getNeighborhoods(): Promise<Hood[]> {
// CTE form: pre-aggregate permit counts once, then LEFT JOIN to the city-listing
// group. Avoids the correlated-subquery fan-out that was running ~200x cold
// (one per candidate city). 2000ms→50ms on Bubbe's Block home.
const { rows } = await pool.query<Hood>(`
WITH city_listings AS (
SELECT city, count(*) AS listing_count
FROM listing
WHERE is_public = true AND city IS NOT NULL
GROUP BY city
HAVING count(*) > 50
),
city_permits AS (
SELECT l.city, count(*) AS recent_permits
FROM permit p
JOIN listing l ON l.id = p.listing_id
WHERE p.issue_date > (CURRENT_DATE - INTERVAL '1 year')
AND l.is_public AND l.city IS NOT NULL
GROUP BY l.city
)
SELECT cl.city,
cl.listing_count::text AS listing_count,
COALESCE(cp.recent_permits, 0)::text AS recent_permits
FROM city_listings cl
LEFT JOIN city_permits cp ON cp.city = cl.city
ORDER BY cl.listing_count DESC
LIMIT 16
`);
return rows;
}
async function getRecentActivity(): Promise<Activity[]> {
// Mix of recent permits + LAHD events as a "what's happening on the block" feed
const { rows } = await pool.query<Activity>(`
SELECT 'permit' as kind, p.permit_type as label, p.primary_address as address, p.issue_date::text as when_, l.slug, l.city
FROM permit p JOIN listing l ON l.id=p.listing_id
WHERE p.issue_date > (CURRENT_DATE - INTERVAL '1 month') AND l.is_public AND p.permit_type IS NOT NULL
ORDER BY p.issue_date DESC LIMIT 10
`);
return rows;
}
const getCounts = unstable_cache(
async () => {
const { rows } = await pool.query<any>(`
SELECT
(SELECT count(*) FROM listing WHERE is_public) as listings,
(SELECT count(DISTINCT city) FROM listing WHERE is_public AND city IS NOT NULL) as cities,
(SELECT count(*) FROM place_event WHERE valid_from > (CURRENT_DATE - INTERVAL '1 year')) as recent_events
`);
return rows[0];
},
['community-home-counts'],
{ revalidate: 600 },
);
export default async function CommunityHome() {
const [hoods, activity, counts] = await Promise.all([
getNeighborhoods(),
getRecentActivity(),
getCounts(),
]);
return (
<>
{/* HERO — warm gold, hand-letter mood */}
<section className="bg-amber-50">
<div className="max-w-7xl mx-auto px-6 py-20 md:py-28">
<p className="text-[10px] uppercase tracking-[0.3em] text-amber-800 font-mono mb-3">
◆ Bubbe's Block
</p>
<h1 className="font-display text-[14vw] md:text-[10rem] leading-[0.82] tracking-[-0.04em] text-ink">
The block,<br/>
<span className="italic text-amber-700">then and now.</span>
</h1>
<p className="mt-10 font-display italic text-2xl md:text-3xl text-ink/70 max-w-3xl leading-tight">
Who lives here today and who lived here a hundred years ago. Neighborhood by neighborhood,
grounded in <span className="not-italic font-medium">{Number(counts?.listings ?? 0).toLocaleString()}</span> houses across <span className="not-italic font-medium">{Number(counts?.cities ?? 0).toLocaleString()}</span> LA-area cities.
</p>
<form action="/search" className="mt-12 flex flex-col sm:flex-row gap-3 max-w-2xl">
<input
type="search"
name="q"
placeholder="Your neighborhood, street, or address…"
className="flex-1 border-2 border-amber-800 bg-white px-5 py-4 text-base font-sans focus:outline-none focus:border-amber-900 tracking-tight"
/>
<button type="submit" className="bg-amber-800 text-amber-50 px-8 py-4 text-xs uppercase tracking-[0.2em] hover:bg-ink transition font-mono">
Find your block
</button>
</form>
</div>
</section>
{/* NEIGHBORHOOD GRID */}
{hoods.length > 0 && (
<section className="bg-sand py-20 border-y border-amber-900/15">
<div className="max-w-7xl mx-auto px-6">
<p className="text-[10px] uppercase tracking-[0.25em] text-amber-800 font-mono mb-3">Neighborhoods</p>
<h2 className="font-display text-5xl md:text-6xl tracking-[-0.02em] mb-10 leading-[0.95]">
Pick a block.
</h2>
<div className="grid grid-cols-2 md:grid-cols-3 lg:grid-cols-4 gap-4">
{hoods.map(h => (
<Link
key={h.city}
href={`/browse?city=${encodeURIComponent(h.city)}`}
className="block border-2 border-amber-900/15 bg-amber-50 hover:border-amber-800 hover:bg-white transition p-6 group"
>
<p className="font-mono text-[10px] uppercase tracking-[0.15em] text-amber-700/70">Los Angeles area</p>
<h3 className="font-display text-2xl text-ink mt-2 leading-tight tracking-tight group-hover:text-amber-800 transition">
{h.city}
</h3>
<p className="mt-4 font-display text-4xl text-ink tabular-nums leading-tight">
{Number(h.listing_count ?? 0).toLocaleString()}
</p>
<p className="font-mono text-[10px] uppercase tracking-wider text-ink/45 mt-1">addresses</p>
{Number(h.recent_permits ?? 0) > 0 && (
<p className="mt-3 font-mono text-[10px] uppercase tracking-wider text-amber-800">
↗ {h.recent_permits} permit{h.recent_permits === '1' ? '' : 's'} this year
</p>
)}
</Link>
))}
</div>
</div>
</section>
)}
{/* WHAT'S HAPPENING — recent activity feed */}
{activity.length > 0 && (
<section className="bg-amber-100/50 py-20">
<div className="max-w-5xl mx-auto px-6">
<p className="text-[10px] uppercase tracking-[0.25em] text-amber-800 font-mono mb-3">What's happening</p>
<h2 className="font-display text-5xl md:text-6xl tracking-[-0.02em] mb-10 leading-[0.95]">
On the block, <span className="italic text-amber-700">this month.</span>
</h2>
<ul className="divide-y divide-amber-900/15">
{activity.map((a, i) => (
<li key={i} className="py-5 flex flex-col md:flex-row md:items-baseline gap-2 md:gap-6">
<span className="font-mono text-xs text-amber-800 tabular-nums shrink-0 w-24">
{a.when_}
</span>
<Link href={`/address/${encodeURIComponent(a.slug)}`} className="font-display text-xl text-ink hover:text-amber-800 transition flex-1">
{a.label} <span className="text-ink/50">·</span> {a.address}
</Link>
<span className="font-mono text-[10px] uppercase tracking-[0.15em] text-ink/45 shrink-0">
{a.city}
</span>
</li>
))}
</ul>
</div>
</section>
)}
{/* THE NAME */}
<section className="bg-ink text-amber-50 py-24">
<div className="max-w-3xl mx-auto px-6 text-center">
<p className="text-[10px] uppercase tracking-[0.3em] text-amber-300 font-mono mb-6">Why “Bubbe’s Block”</p>
<p className="font-display italic text-3xl md:text-4xl leading-tight text-amber-50/90">
“Bubbe knew everything about this block. Who moved in, who fixed the porch,
whose son made it big, and whose great-grandfather ran the corner store. We’re trying to write that down.”
</p>
<p className="mt-8 font-mono text-xs uppercase tracking-[0.2em] text-amber-200/60">
— the founders, basically
</p>
</div>
</section>
{/* SISTER SURFACES */}
<section className="bg-amber-50 py-16 border-t-2 border-amber-900/15">
<div className="max-w-7xl mx-auto px-6">
<p className="text-[10px] uppercase tracking-[0.25em] text-amber-800 font-mono mb-6">Sister sites</p>
<div className="grid md:grid-cols-2 gap-6">
{sisterSurfaces('community').map(s => (
<a
key={s.domain}
href={`https://${s.domain}${s.primaryCta.href}`}
className="block border-2 border-amber-900/15 hover:border-amber-800 bg-white p-8 group transition"
>
<p className="font-mono text-[10px] uppercase tracking-[0.25em] text-amber-800/60">{s.domain}</p>
<h3 className="font-display text-3xl text-ink mt-2 tracking-[-0.01em] group-hover:text-amber-800 transition">
{s.brandName}
</h3>
<p className="font-display italic text-lg text-ink/65 mt-1">{s.tagline}</p>
<p className="mt-4 font-mono text-[11px] uppercase tracking-[0.15em] text-amber-800">
{s.primaryCta.label} →
</p>
</a>
))}
</div>
</div>
</section>
</>
);
}