`viewer/server.js`

142 lines
import express from 'express';
import { fileURLToPath } from 'url';
import path from 'path';
import pg from 'pg';

const { Pool } = pg;
const pool = process.env.DATABASE_URL
  ? new Pool({ connectionString: process.env.DATABASE_URL })
  : new Pool({ database: process.env.REALESTATE_DB || 'realestate' });

const app = express();
const PORT = process.env.PERMIT_VIEWER_PORT || 9814;
const __dirname = path.dirname(fileURLToPath(import.meta.url));

// --- Basic auth (admin / DW2024!) ---
const USER = process.env.VIEWER_USER || 'admin';
const PASS = process.env.VIEWER_PASS || 'DW2024!';
app.use((req, res, next) => {
  if (process.env.VIEWER_NO_AUTH === '1') return next(); // local screenshot/dev only
  const hdr = req.headers.authorization || '';
  const [, b64] = hdr.split(' ');
  const [u, p] = Buffer.from(b64 || '', 'base64').toString().split(':');
  if (u === USER && p === PASS) return next();
  res.set('WWW-Authenticate', 'Basic realm="LA Building Permits"').status(401).send('Auth required');
});

app.use(express.static(path.join(__dirname, 'public')));

// Building permits only (the 3 Bldg-* datasets; trade permits excluded).
const BASE = `p.dataset_id IN ('pi9x-tg5x','dyxf-7hc4','e67z-kt2n')`;
const ERA = `CASE p.dataset_id WHEN 'pi9x-tg5x' THEN '2020-present' WHEN 'dyxf-7hc4' THEN '2010-2019' WHEN 'e67z-kt2n' THEN 'pre-2010' END`;

// Build a parameterized WHERE from query filters. Returns { sql, params }.
function buildWhere(qp, params = []) {
  const w = [BASE];
  const add = (v) => { params.push(v); return `$${params.length}`; };
  if (qp.q) w.push(`p.primary_address ILIKE ${add('%' + qp.q + '%')}`);
  if (qp.era) {
    const map = { '2020-present': 'pi9x-tg5x', '2010-2019': 'dyxf-7hc4', 'pre-2010': 'e67z-kt2n' };
    if (map[qp.era]) w.push(`p.dataset_id = ${add(map[qp.era])}`);
  }
  if (qp.work_type) w.push(`p.permit_type = ${add(qp.work_type)}`);
  if (qp.building_type) w.push(`p.permit_sub_type = ${add(qp.building_type)}`);
  if (qp.status) w.push(`p.status_desc = ${add(qp.status)}`);
  if (qp.cd) w.push(`p.council_district = ${add(qp.cd)}`);
  if (qp.zip) w.push(`p.zip_code = ${add(qp.zip)}`);
  if (qp.min_value) w.push(`p.valuation >= ${add(Number(qp.min_value))}`);
  if (qp.since) w.push(`p.issue_date >= ${add(qp.since)}`);
  return { sql: w.join(' AND '), params };
}

// Named sort presets (back-compat).
const SORTS = {
  newest: 'p.issue_date DESC NULLS LAST',
  oldest: 'p.issue_date ASC NULLS LAST',
  value_desc: 'p.valuation DESC NULLS LAST',
  value_asc: 'p.valuation ASC NULLS LAST',
  address: 'p.primary_address ASC',
};
// Per-column sort whitelist (col name -> SQL expr). Enables sort=<col>&dir=asc|desc.
const COLS = {
  lead_score: 'permit_lead_score_v2(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc,a.total_value,a.year_built)',
  issued: 'p.issue_date', address: 'p.primary_address', zip: 'p.zip_code',
  cd: 'p.council_district', work_type: 'p.permit_type', building_type: 'p.permit_sub_type',
  use_desc: 'p.use_desc', status: 'p.status_desc', project_value: 'p.valuation',
  apn: 'p.apn', assessed_value: 'a.total_value', year_built: 'a.year_built',
  sqft: 'a.sqft_main', era: 'p.dataset_id', permit_nbr: 'p.permit_nbr',
};
function orderBy(sort, dir) {
  if (COLS[sort]) return `${COLS[sort]} ${dir === 'asc' ? 'ASC' : 'DESC'} NULLS LAST`;
  return SORTS[sort] || 'permit_lead_score_v2(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc,a.total_value,a.year_built) DESC NULLS LAST';
}

// --- Paginated, filtered, sorted permits ---
app.get('/api/permits', async (req, res) => {
  try {
    const limit = Math.min(Number(req.query.limit) || 60, 200);
    const page = Math.max(Number(req.query.page) || 1, 1);
    const offset = (page - 1) * limit;
    const order = orderBy(req.query.sort, req.query.dir);

    const { sql: where, params } = buildWhere(req.query);
    const rowsSql = `
      SELECT permit_lead_score_v2(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc,a.total_value,a.year_built) AS lead_score,
             ${ERA} AS era, p.permit_nbr, p.issue_date, p.primary_address AS address, p.zip_code AS zip,
             p.council_district AS cd, p.permit_type AS work_type, p.permit_sub_type AS building_type,
             p.use_desc, p.status_desc AS status, p.valuation AS project_value, p.apn,
             a.total_value AS assessed_value, a.year_built, a.sqft_main AS sqft, p.lat, p.lon
      FROM la_building_permits_raw p
      LEFT JOIN la_assessor_parcels_raw a ON a.ain = p.apn AND a.roll_year = '2025'
      WHERE ${where}
      ORDER BY ${order}
      LIMIT ${limit} OFFSET ${offset}`;
    const rows = (await pool.query(rowsSql, params)).rows;

    // total (exact when filtered; cached constant when unfiltered for speed)
    const isFiltered = Object.keys(req.query).some((k) => ['q','era','work_type','building_type','status','cd','zip','min_value','since'].includes(k) && req.query[k]);
    let total;
    if (isFiltered) {
      total = Number((await pool.query(`SELECT count(*) c FROM la_building_permits_raw p WHERE ${where}`, params)).rows[0].c);
    } else {
      total = 1578997;
    }
    res.json({ total, page, limit, rows });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// --- Facet counts (respect current filters) ---
app.get('/api/facets', async (req, res) => {
  try {
    const facet = async (expr) => {
      const { sql: where, params } = buildWhere(req.query);
      const q = `SELECT ${expr} AS v, count(*) c FROM la_building_permits_raw p WHERE ${where} AND ${expr} IS NOT NULL GROUP BY 1 ORDER BY c DESC`;
      return (await pool.query(q, params)).rows;
    };
    const [era, work_type, building_type, status, cd, zip] = await Promise.all([
      facet(ERA), facet('p.permit_type'), facet('p.permit_sub_type'),
      facet('p.status_desc'), facet('p.council_district'),
      (async () => { const { sql: where, params } = buildWhere(req.query);
        return (await pool.query(`SELECT p.zip_code v, count(*) c FROM la_building_permits_raw p WHERE ${where} AND p.zip_code IS NOT NULL GROUP BY 1 ORDER BY c DESC LIMIT 40`, params)).rows; })(),
    ]);
    res.json({ era, work_type, building_type, status, cd, zip });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// --- Single permit detail ---
app.get('/api/permit/:nbr', async (req, res) => {
  try {
    const q = `
      SELECT ${ERA} AS era, p.*, a.total_value AS parcel_assessed, a.year_built AS parcel_year_built,
             a.sqft_main AS parcel_sqft, a.use_type AS parcel_use, a.property_location AS parcel_address
      FROM la_building_permits_raw p
      LEFT JOIN la_assessor_parcels_raw a ON a.ain = p.apn AND a.roll_year = '2025'
      WHERE ${BASE} AND p.permit_nbr = $1 LIMIT 1`;
    const row = (await pool.query(q, [req.params.nbr])).rows[0];
    if (!row) return res.status(404).json({ error: 'not found' });
    res.json(row);
  } catch (e) { res.status(500).json({ error: e.message }); }
});

app.listen(PORT, () => console.log(`LA Building Permits viewer → http://127.0.0.1:${PORT} (admin/${PASS})`));