`src/server/sublease.ts`

142 lines
/**
 * Sublease inventory routes — manual-entry CRE sublease listings.
 *
 * Why manual-entry: every real commercial-sublease source (LoopNet/CoStar, CommercialCafe,
 * Colliers, CREXi) is bot-walled (Cloudflare managed challenges or anti-scrape ToS), so a
 * clean robots-honored/facts-only crawl is impossible — the only path through is a
 * challenge-solver = detection evasion (a red line). So the honest pipeline is: RENTV's team
 * enters real sublease deals they source through their broker relationships via the gated
 * admin form, and this table is the single source of truth. Ships EMPTY-HONEST until a real
 * row exists — never fabricated inventory.
 *
 * Mounted from index.ts via mountSublease(app); the whole API is already behind the global
 * basic-auth middleware there, so GET (read, proxied to /desk) and POST (admin add) are both
 * auth-gated without extra wiring.
 */
import type { Express } from 'express';
import { query } from '../../db/pool.ts';

// Numeric coercers: a non-numeric value (e.g. "abc") must NOT reach Postgres.
// Number("abc") === NaN → an integer column (term_months) 500s ("invalid input
// syntax for type integer: NaN"), and a numeric/double column (sqft/asking_rate/
// lat/lng) silently STORES literal NaN, poisoning sorts and the map. Coerce
// non-finite input to null so bad input degrades to "unset", never a 500 or NaN row.
const num = (v: unknown): number | null => {
  if (v === '' || v == null) return null;
  const n = Number(v);
  return Number.isFinite(n) ? n : null;
};
const int = (v: unknown): number | null => {
  const n = num(v);
  return n == null ? null : Math.round(n);
};
// A `date` column rejects a malformed string with a 500; require an ISO-ish date, else null.
const isoDate = (v: unknown): string | null => {
  if (v === '' || v == null) return null;
  const s = String(v).trim();
  return /^\d{4}-\d{2}-\d{2}/.test(s) && !Number.isNaN(Date.parse(s)) ? s : null;
};

// Columns the admin form may set, mapped to their coercers. Anything else is ignored.
const FIELDS: Record<string, (v: unknown) => unknown> = {
  address: v => String(v).trim(),
  city: v => (v == null ? null : String(v).trim()),
  submarket: v => (v == null ? null : String(v).trim()),
  floor_suite: v => (v == null ? null : String(v).trim()),
  sqft: num,
  asking_rate: num,
  rate_period: v => (['yr', 'mo'].includes(String(v)) ? String(v) : 'yr'),
  lease_type: v => (['sublease', 'direct'].includes(String(v)) ? String(v) : 'sublease'),
  term_months: int,
  available_date: isoDate,
  sublandlord: v => (v == null ? null : String(v).trim()),
  firm_name: v => (v == null ? null : String(v).trim()),
  broker_name: v => (v == null ? null : String(v).trim()),
  broker_phone: v => (v == null ? null : String(v).trim()),
  broker_email: v => (v == null ? null : String(v).trim()),
  listing_url: v => (v == null ? null : String(v).trim()),
  lat: num,
  lng: num,
  status: v => (['active', 'leased', 'withdrawn'].includes(String(v)) ? String(v) : 'active'),
  notes: v => (v == null ? null : String(v).trim()),
};

export function mountSublease(app: Express): void {
  // GET /api/sublease?q=&city=&status=active&limit=  — CRCP-friendly rows, newest first.
  app.get('/api/sublease', async (req, res) => {
    try {
      const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 2000);
      const conds: string[] = [];
      const params: unknown[] = [];
      // default to active inventory; ?status=all shows everything
      if (String(req.query.status || 'active') !== 'all') {
        params.push(String(req.query.status || 'active'));
        conds.push(`status = $${params.length}`);
      }
      if (req.query.city) { params.push(String(req.query.city)); conds.push(`lower(city) = lower($${params.length})`); }
      if (req.query.q) {
        params.push('%' + String(req.query.q).toLowerCase() + '%');
        const i = params.length;
        conds.push(`(lower(address) LIKE $${i} OR lower(coalesce(city,'')) LIKE $${i}
                    OR lower(coalesce(submarket,'')) LIKE $${i} OR lower(coalesce(sublandlord,'')) LIKE $${i}
                    OR lower(coalesce(firm_name,'')) LIKE $${i} OR lower(coalesce(broker_name,'')) LIKE $${i})`);
      }
      const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
      params.push(limit);
      const r = await query(
        `SELECT id, address, city, submarket, floor_suite, sqft, asking_rate, rate_period,
                lease_type, term_months, available_date, sublandlord, firm_name, firm_id,
                broker_name, broker_phone, broker_email, broker_id, listing_url, source,
                lat, lng, status, notes, created_at, updated_at
           FROM sublease ${where}
          ORDER BY created_at DESC, id DESC
          LIMIT $${params.length}`,
        params,
      );
      const st = String(req.query.status || 'active');
      const total = st === 'all'
        ? await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM sublease`)
        : await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM sublease WHERE status=$1`, [st]);
      res.json({ count: r.rows.length, total: Number(total.rows[0].n), rows: r.rows });
    } catch (e: any) {
      res.status(500).json({ error: String(e.message || e) });
    }
  });

  // POST /api/admin/sublease — insert one real listing (admin, already basic-auth-gated).
  app.post('/api/admin/sublease', async (req, res) => {
    try {
      const body = req.body || {};
      if (!body.address || !String(body.address).trim()) {
        return res.status(400).json({ error: 'address is required' });
      }
      const cols: string[] = [];
      const vals: unknown[] = [];
      const ph: string[] = [];
      for (const [col, coerce] of Object.entries(FIELDS)) {
        if (col in body) { cols.push(col); vals.push(coerce(body[col])); ph.push(`$${vals.length}`); }
      }
      cols.push('source'); vals.push(String(body.source || 'manual')); ph.push(`$${vals.length}`);
      const r = await query(
        `INSERT INTO sublease (${cols.join(', ')}) VALUES (${ph.join(', ')}) RETURNING *`,
        vals,
      );
      res.json({ ok: true, row: r.rows[0] });
    } catch (e: any) {
      res.status(500).json({ ok: false, error: String(e.message || e) });
    }
  });

  // DELETE /api/admin/sublease/:id — withdraw a listing (soft: mark withdrawn).
  app.delete('/api/admin/sublease/:id', async (req, res) => {
    try {
      const id = Number(req.params.id);
      if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad id' });
      await query(`UPDATE sublease SET status='withdrawn', updated_at=now() WHERE id=$1`, [id]);
      res.json({ ok: true });
    } catch (e: any) {
      res.status(500).json({ ok: false, error: String(e.message || e) });
    }
  });
}