`src/server/listings.ts`

111 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';

export function mountListings(app: Express): void {
  // GET /api/listings?firm=<id>&county=<region_id>&source=&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}`); }
      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
           FROM listing l
           LEFT JOIN firm f ON f.id = l.firm_id
           LEFT JOIN region rg ON rg.id = l.region_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) });
    }
  });

  // 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) });
    }
  });
}