`lib/firms.js`

121 lines
'use strict';
// Firm-tier listings for /firms — surfaces the seeded top-US firms (Gensler,
// Studio Sofield, Kelly Wearstler etc.) plus any firms auto-materialized from
// sig-extract. Independent from /clients which keys on designer rows.
//
// PII policy: city + state + areas only. No street, no zip, no phone. Same as
// /clients.

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
  max: 4,
});

// Per Steve's standing rule: every product/listing grid needs sort + density.
// SORT_KEYS controls the firm-tier sort dropdown — claimed-first is default
// because verified profiles deserve top-of-feed.
const SORT_KEYS = {
  claimed:    `(f.claimed_at IS NOT NULL) DESC, f.size_band DESC NULLS LAST, f.name`,
  name_az:    `f.name`,
  name_za:    `f.name DESC`,
  founded_new:`f.founded_year DESC NULLS LAST, f.name`,
  founded_old:`f.founded_year ASC NULLS LAST, f.name`,
  city:       `f.city NULLS LAST, f.name`,
  size:       `f.size_band DESC NULLS LAST, f.name`,
};

async function listFirms({ q = null, sort = 'claimed', limit = 300 } = {}) {
  const sortKey = SORT_KEYS[sort] ? sort : 'claimed';
  const orderBy = SORT_KEYS[sortKey];
  const params = [];
  let where = `1=1`;
  if (q) {
    params.push(`%${q.toLowerCase()}%`);
    where += ` AND (LOWER(f.name) LIKE $${params.length} OR LOWER(f.city) LIKE $${params.length} OR LOWER(f.state_or_region) LIKE $${params.length})`;
  }
  params.push(limit);
  const sql = `
    SELECT f.id, f.slug, f.name, f.city, f.state_or_region AS state, f.country,
           f.founded_year, f.size_band, f.website, f.bio, f.styles,
           f.areas_served, f.logo_source, f.logo_url, f.claimed_at,
           (SELECT count(*) FROM sod_designer_firm df WHERE df.firm_id = f.id AND df.is_current=true) AS designer_count,
           (
             SELECT json_agg(json_build_object('url', l.url, 'kind', l.kind))
               FROM sod_links l
              WHERE l.firm_id = f.id
           ) AS links
      FROM sod_firms f
     WHERE ${where}
     ORDER BY ${orderBy}
     LIMIT $${params.length}`;
  const r = await pool.query(sql, params);
  return r.rows.map(row => {
    const linksByKind = {};
    for (const l of (row.links || [])) {
      (linksByKind[l.kind] = linksByKind[l.kind] || []).push(l.url);
    }
    return {
      id: row.id,
      slug: row.slug,
      name: row.name,
      city: row.city,
      state: row.state,
      country: row.country,
      founded_year: row.founded_year,
      size_band: row.size_band,
      website: row.website,
      bio: row.bio,
      styles: row.styles || [],
      areas: row.areas_served || [],
      designer_count: parseInt(row.designer_count, 10) || 0,
      claimed: !!row.claimed_at,
      logo: (row.logo_source === 'made-with-ai' || !row.logo_url) ? '/img/made-with-ai.svg' : row.logo_url,
      links: linksByKind,
    };
  });
}

async function getFirm(slug) {
  const r = await pool.query(
    `SELECT f.*,
            (SELECT count(*) FROM sod_designer_firm df WHERE df.firm_id = f.id AND df.is_current=true) AS designer_count
       FROM sod_firms f
      WHERE f.slug = $1
      LIMIT 1`, [slug]);
  if (!r.rows[0]) return null;
  const f = r.rows[0];
  const dr = await pool.query(
    `SELECT d.id, d.slug, d.full_name, d.role, d.city, d.state_or_region AS state
       FROM sod_designers d
       JOIN sod_designer_firm df ON df.designer_id = d.id AND df.is_current = true
      WHERE df.firm_id = $1
      ORDER BY d.full_name
      LIMIT 50`, [f.id]);
  const lr = await pool.query(
    `SELECT url, kind FROM sod_links WHERE firm_id = $1`, [f.id]);
  const linksByKind = {};
  for (const l of lr.rows) (linksByKind[l.kind] = linksByKind[l.kind] || []).push(l.url);
  return {
    id: f.id, slug: f.slug, name: f.name,
    city: f.city, state: f.state_or_region, country: f.country,
    founded_year: f.founded_year, size_band: f.size_band,
    website: f.website, bio: f.bio,
    styles: f.styles || [],
    areas: f.areas_served || [],
    designer_count: parseInt(f.designer_count, 10) || 0,
    claimed: !!f.claimed_at,
    logo: (f.logo_source === 'made-with-ai' || !f.logo_url) ? '/img/made-with-ai.svg' : f.logo_url,
    links: linksByKind,
    designers: dr.rows,
  };
}

async function totalCount() {
  const r = await pool.query(`SELECT count(*) AS n FROM sod_firms`);
  return parseInt(r.rows[0].n, 10);
}

module.exports = { listFirms, getFirm, totalCount, SORT_KEYS };