`src/ingest/commercial/briefs.ts`

98 lines
/**
 * Phase 4 — generated commercial news blurbs ($0, local Ollama).
 * For a bounded set (top assets by assessed value + most recently recorded), asks a
 * local LLM for ONE factual news-wire sentence built strictly from assessor facts —
 * no invented sale prices, owners, tenants, or dates — and upserts into
 * commercial_brief so the feed/explorer serve briefs instantly.
 *
 * Run: npm run ingest:commercial-briefs           (Mac2 Ollama)
 *      OLLAMA_URL=http://192.168.1.133:11434 npm run ingest:commercial-briefs
 */
import { pool, query } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { categoryLabel, type CommercialType } from '../../lib/commercial_types.ts';

const FIPS = '06037';
const OLLAMA = (process.env.OLLAMA_URL || 'http://127.0.0.1:11434') + '/api/generate';
const MODEL = process.env.OLLAMA_MODEL || 'qwen2.5:latest';
const TOP_VALUE = Number(process.env.BRIEF_TOP_VALUE || 80);
const TOP_RECENT = Number(process.env.BRIEF_TOP_RECENT || 140);

interface Asset {
  ain: string; address: string | null; city: string | null; ctype: CommercialType;
  use_desc: string | null; assessed_total: string | null; recording_date: string | null;
  sqft: number | null; year_built: number | null;
}

const usd = (n: string | null) => (n == null ? null : '$' + Number(n).toLocaleString('en-US'));

function prompt(a: Asset): string {
  const facts = [
    `type: ${categoryLabel(a.ctype)}`,
    a.use_desc && `use: ${a.use_desc}`,
    a.address && `address: ${a.address}`,
    a.city && `city: ${a.city}`,
    a.assessed_total && `assessed value: ${usd(a.assessed_total)}`,
    a.recording_date && `last recorded transfer: ${String(a.recording_date).slice(0, 10)}`,
    a.sqft && `building size: ${a.sqft.toLocaleString()} sqft`,
    a.year_built && `year built: ${a.year_built}`,
  ].filter(Boolean).join('; ');
  return `You are a Los Angeles commercial real estate news desk. Write ONE factual sentence (max 32 words), news-wire tone, about this property using ONLY the facts below. Do NOT invent sale prices, owners, buyers, sellers, tenants, or dates. "Assessed value" is a tax assessment, not a sale price — never call it a sale. No preamble, output only the sentence.\nFACTS: ${facts}\nSENTENCE:`;
}

async function generate(a: Asset): Promise<string | null> {
  const res = await fetch(OLLAMA, {
    method: 'POST', headers: { 'content-type': 'application/json' },
    body: JSON.stringify({ model: MODEL, prompt: prompt(a), stream: false, think: false, options: { temperature: 0.4, num_predict: 90 } }),
    signal: AbortSignal.timeout(60_000),
  });
  if (!res.ok) throw new Error(`ollama ${res.status}`);
  const j = await res.json() as { response?: string };
  let s = (j.response || '').trim().replace(/^["']|["']$/g, '').replace(/\s+/g, ' ');
  if (s.length < 12) return null;
  if (s.length > 400) s = s.slice(0, 400);
  return s;
}

export async function ingestCommercialBriefs(): Promise<{ upserted: number }> {
  const runId = await openRun('commercial_briefs', `${OLLAMA} (${MODEL})`);
  try {
    // bounded target set: top by value UNION most recently recorded
    const rows = await query<Asset>(
      `SELECT ain, address, city, ctype, use_desc, assessed_total, recording_date, sqft, year_built FROM (
         (SELECT * FROM commercial_parcel WHERE county_fips=$1 AND assessed_total IS NOT NULL
            ORDER BY assessed_total DESC LIMIT $2)
         UNION
         (SELECT * FROM commercial_parcel WHERE county_fips=$1 AND recording_date IS NOT NULL
            ORDER BY recording_date DESC LIMIT $3)
       ) s`, [FIPS, TOP_VALUE, TOP_RECENT]);
    console.log(`[briefs] target set: ${rows.rows.length} assets, model ${MODEL}`);

    let upserted = 0, failed = 0;
    for (const a of rows.rows) {
      try {
        const brief = await generate(a);
        if (!brief) { failed++; continue; }
        await query(
          `INSERT INTO commercial_brief (county_fips, ain, brief, model, generated_at)
           VALUES ($1,$2,$3,$4,now())
           ON CONFLICT (county_fips, ain) DO UPDATE SET brief=EXCLUDED.brief, model=EXCLUDED.model, generated_at=now()`,
          [FIPS, a.ain, brief, MODEL]);
        upserted++;
        if (upserted % 25 === 0) console.log(`[briefs] ${upserted} generated…`);
      } catch (e: any) { failed++; if (failed <= 3) console.error(`[briefs] ${a.ain}: ${e.message}`); }
    }
    if (upserted < 20) throw new Error(`only ${upserted} briefs generated — Ollama unreachable?`);
    await closeRun(runId, 'ok', { upserted, skipped: failed, notes: `commercial briefs: ${upserted} generated, ${failed} failed (${MODEL})` });
    console.log(`[briefs] ok: ${upserted} briefs (${failed} failed)`);
    return { upserted };
  } catch (e: any) {
    await closeRun(runId, 'failed', { notes: String(e.message || e) });
    throw e;
  }
}

if (import.meta.url === `file://${process.argv[1]}`) {
  ingestCommercialBriefs().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
}