← back to Nationalrealestate
src/server/listings.ts
197 lines
/**
* M-B3 listing routes — the firm↔listing tie made queryable + browsable.
* Kept in its OWN module (mountListings(app)) so index.ts only gains an import + mount line;
* the existing /api/firm/:id in index.ts is NOT touched — the per-firm listings live at the
* new sub-route GET /api/firm/:id/listings.
*
* Every listing row is FACTS ONLY + the broker's own source url (we link out, never present
* a listing as ours). Each row also carries the holding brokerage FIRM name — the key relationship.
*/
import type { Express } from 'express';
import { query } from '../../db/pool.ts';
import { addressKeySql } from './broker-of-record.ts';
export function mountListings(app: Express): void {
// GET /api/listings?firm=<id>&county=<region_id>&source=&state=<lifecycle>&limit=
app.get('/api/listings', async (req, res) => {
try {
const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 2000);
const conds: string[] = [];
const params: unknown[] = [];
if (req.query.firm) { params.push(Number(req.query.firm)); conds.push(`l.firm_id = $${params.length}`); }
if (req.query.county) { params.push(Number(req.query.county)); conds.push(`l.region_id = $${params.length}`); }
if (req.query.source) { params.push(String(req.query.source)); conds.push(`l.source = $${params.length}`); }
// TK-10155: filter by current lifecycle state (withdrawn|stale|new|active|closed|reappeared).
if (req.query.state) { params.push(String(req.query.state)); conds.push(`lcs.state = $${params.length}`); }
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
params.push(limit);
const r = await query(
`SELECT l.id, l.source, l.source_id, l.address, l.price, l.beds, l.baths, l.sqft,
l.lat, l.lng, l.url, l.status, l.first_seen, l.last_seen,
l.firm_id, f.name AS firm_name,
l.region_id, rg.name AS county_name, rg.state_code,
-- TK-10139: newest broker/firm of record for this listing's address.
lbor.broker_name AS last_broker_of_record,
lbor.firm_name AS last_firm_of_record,
lbor.observed_at AS last_broker_observed_at,
-- TK-10155: current lifecycle state + when it was last observed.
lcs.state AS lifecycle_state,
lcs.observed_at AS lifecycle_since
FROM listing l
LEFT JOIN firm f ON f.id = l.firm_id
LEFT JOIN region rg ON rg.id = l.region_id
LEFT JOIN last_broker_of_record lbor
ON lbor.address_key = ${addressKeySql(`coalesce(l.address,'')`)}
LEFT JOIN listing_current_state lcs
ON lcs.source = l.source AND lcs.source_id = l.source_id
${where}
ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
LIMIT $${params.length}`,
params,
);
res.json({ count: r.rows.length, rows: r.rows });
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// TK-10155: GET /api/listings/:id/lifecycle — full lifecycle history + preserved snapshots
// for one listing (newest-first). The headline drill-down: current state, every state
// transition, and every preserved property snapshot.
app.get('/api/listings/:id/lifecycle', async (req, res) => {
try {
const id = Number(req.params.id);
if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad listing id' });
const [events, snaps] = await Promise.all([
query(
`SELECT id, listing_id, source, source_id, address_key, state, prev_state, price, reason, observed_at
FROM listing_lifecycle_event
WHERE listing_id = $1
ORDER BY observed_at DESC, id DESC`,
[id],
),
query(
`SELECT id, captured_at, captured_reason, snapshot
FROM listing_snapshot
WHERE listing_id = $1
ORDER BY captured_at DESC, id DESC`,
[id],
),
]);
const current_state = events.rows[0]?.state ?? null;
res.json({
listing_id: id,
current_state,
events: events.rows,
snapshots: snaps.rows,
});
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// TK-10155: GET /api/withdrawn?limit=N — recently withdrawn listings WITH their preserved
// snapshot. The headline feature: everything that got pulled from the feed, full info saved,
// newest-withdrawn first. Uses the withdrawn lifecycle_event as the trigger time, and joins
// the snapshot that was captured for that withdrawal (falls back to the newest snapshot).
app.get('/api/withdrawn', async (req, res) => {
try {
const limit = Math.min(Math.max(Number(req.query.limit) || 100, 1), 1000);
const r = await query(
`WITH w AS (
-- latest event per listing; keep only those whose current state is withdrawn.
SELECT DISTINCT ON (source, source_id)
source, source_id, listing_id, state, reason, observed_at
FROM listing_lifecycle_event
ORDER BY source, source_id, observed_at DESC, id DESC
)
SELECT w.source, w.source_id, w.listing_id,
w.observed_at AS withdrawn_at, w.reason,
s.captured_at, s.captured_reason, s.snapshot
FROM w
LEFT JOIN LATERAL (
SELECT captured_at, captured_reason, snapshot
FROM listing_snapshot ls
WHERE ls.source = w.source AND ls.source_id = w.source_id
ORDER BY (ls.captured_reason = 'withdrawn') DESC, ls.captured_at DESC, ls.id DESC
LIMIT 1
) s ON true
WHERE w.state = 'withdrawn'
ORDER BY w.observed_at DESC
LIMIT $1`,
[limit],
);
res.json({ count: r.rows.length, rows: r.rows });
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// GET /api/listings/stats — count by firm + by source (proves the firm↔listing tie)
app.get('/api/listings/stats', async (_req, res) => {
try {
const [bySource, byFirm, totals] = await Promise.all([
query<{ source: string; n: string; firms: string }>(
`SELECT source, COUNT(*)::text AS n, COUNT(DISTINCT firm_id)::text AS firms
FROM listing GROUP BY source ORDER BY source`),
query<{ firm_id: number; firm_name: string; source: string; n: string }>(
`SELECT l.firm_id, f.name AS firm_name, l.source, COUNT(*)::text AS n
FROM listing l JOIN firm f ON f.id = l.firm_id
GROUP BY l.firm_id, f.name, l.source
ORDER BY COUNT(*) DESC`),
query<{ total: string; with_firm: string; with_region: string }>(
`SELECT COUNT(*)::text AS total,
COUNT(firm_id)::text AS with_firm,
COUNT(region_id)::text AS with_region
FROM listing`),
]);
res.json({
by_source: bySource.rows.map(r => ({ source: r.source, listings: Number(r.n), firms: Number(r.firms) })),
by_firm: byFirm.rows.map(r => ({ firm_id: r.firm_id, firm_name: r.firm_name, source: r.source, listings: Number(r.n) })),
totals: {
total: Number(totals.rows[0].total),
with_firm: Number(totals.rows[0].with_firm),
with_region: Number(totals.rows[0].with_region),
},
});
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// GET /api/firm/:id/listings — "N listings from this brokerage" for a firm view.
// NEW sub-route; the existing /api/firm/:id in index.ts is left untouched.
app.get('/api/firm/:id/listings', async (req, res) => {
try {
const id = Number(req.params.id);
if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad firm id' });
const r = await query(
`SELECT l.id, l.source, l.source_id, l.address, l.price, l.beds, l.baths, l.sqft,
l.lat, l.lng, l.url, l.status, l.first_seen, l.last_seen,
rg.name AS county_name, rg.state_code
FROM listing l LEFT JOIN region rg ON rg.id = l.region_id
WHERE l.firm_id = $1
ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
LIMIT 200`,
[id],
);
res.json({ firm_id: id, count: r.rows.length, listings: r.rows });
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// Firms that hold at least one piloted listing — powers the listings.html firm filter.
app.get('/api/listings/firms', async (_req, res) => {
try {
const r = await query<{ firm_id: number; firm_name: string; n: string }>(
`SELECT l.firm_id, f.name AS firm_name, COUNT(*)::text AS n
FROM listing l JOIN firm f ON f.id = l.firm_id
GROUP BY l.firm_id, f.name ORDER BY f.name`);
res.json({ firms: r.rows.map(x => ({ firm_id: x.firm_id, firm_name: x.firm_name, listings: Number(x.n) })) });
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
}