`lib/clients.js`

150 lines
'use strict';
// Real DW client designers — sourced from George info@designerwallcoverings.com
// Gmail recon + Steve's personal Gmail recon, materialized via sig-extract into
// sod_designers + sod_firms + sod_links.
//
// This is the second tier of the directory — the 150-entry editorial JSON at
// data/designers.json is the curated marquee (Dorothy Draper, Kelly Wearstler,
// Studio Sofield, etc.); /clients surfaces the actual designers who buy from
// Designer Wallcoverings. They get the same profile-page treatment but their
// data comes from sig-extract + future claim flow.
//
// PII policy: city + state only; no street address, no zip, no phone. Phone
// becomes available only via the claim flow. See
// ~/.claude/projects/-Users-stevestudio2/memory/feedback_designer_directory_pii_policy.md.

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.
const SORT_KEYS = {
  default:  `(d.premium_tier IS NOT NULL) DESC, d.full_name`,
  name_az:  `d.full_name`,
  name_za:  `d.full_name DESC`,
  city:     `COALESCE(f.city, d.city) NULLS LAST, d.full_name`,
  firm:     `f.name NULLS LAST, d.full_name`,
  claimed:  `(d.claimed_at IS NOT NULL) DESC, d.full_name`,
};

async function listClients({ limit = 200, q = null, area = null, sort = 'default' } = {}) {
  const params = [];
  let where = `(d.role IS NULL OR d.role <> 'Manufacturer Rep')`;
  if (q) {
    params.push(`%${q.toLowerCase()}%`);
    where += ` AND (LOWER(d.full_name) LIKE $${params.length} OR LOWER(f.name) LIKE $${params.length} OR LOWER(d.city) LIKE $${params.length})`;
  }
  if (area) {
    params.push(area);
    where += ` AND ($${params.length} = ANY(d.areas_served) OR $${params.length} = ANY(f.areas_served))`;
  }
  params.push(limit);
  const sql = `
    SELECT d.id, d.slug, d.full_name, d.role,
           d.city                   AS designer_city,
           d.state_or_region        AS designer_state,
           d.country                AS designer_country,
           d.areas_served           AS designer_areas,
           d.headshot_source, d.headshot_url,
           d.premium_tier, d.claimed_at,
           f.id      AS firm_id,
           f.slug    AS firm_slug,
           f.name    AS firm_name,
           f.city            AS firm_city,
           f.state_or_region AS firm_state,
           f.country         AS firm_country,
           f.areas_served    AS firm_areas,
           f.website         AS firm_website,
           f.size_band, f.founded_year,
           (
             SELECT json_agg(json_build_object('url', l.url, 'kind', l.kind))
               FROM sod_links l
              WHERE l.designer_id = d.id OR l.firm_id = f.id
           ) AS links
      FROM sod_designers d
      LEFT JOIN sod_designer_firm df ON df.designer_id = d.id AND df.is_current = true
      LEFT JOIN sod_firms f          ON f.id = df.firm_id
     WHERE ${where}
     ORDER BY ${SORT_KEYS[sort] || SORT_KEYS.default}
     LIMIT $${params.length}`;
  const r = await pool.query(sql, params);
  return r.rows.map(row => {
    // Roll up city/state — prefer firm's, fall back to designer's.
    const city  = row.firm_city  || row.designer_city  || null;
    const state = row.firm_state || row.designer_state || null;
    const country = row.firm_country || row.designer_country || null;
    const areas = [...new Set([...(row.designer_areas || []), ...(row.firm_areas || [])])];
    // Group links by kind.
    const linksByKind = {};
    for (const l of (row.links || [])) {
      (linksByKind[l.kind] = linksByKind[l.kind] || []).push(l.url);
    }
    return {
      id: row.id,
      slug: row.slug,
      full_name: row.full_name,
      role: row.role,
      city, state, country, areas,
      firm: row.firm_name ? {
        id: row.firm_id, slug: row.firm_slug, name: row.firm_name,
        website: row.firm_website, founded_year: row.founded_year, size_band: row.size_band,
      } : null,
      premium_tier: row.premium_tier,
      claimed: !!row.claimed_at,
      links: linksByKind,
      headshot: (row.headshot_source === 'made-with-ai' || !row.headshot_url) ? '/img/made-with-ai.svg' : row.headshot_url,
    };
  });
}

async function getClient(slug) {
  const rows = await listClients({ limit: 1, q: null });
  // Refetch with slug match (cheap on this scale; can be tightened later)
  const r = await pool.query(`
    SELECT d.*, f.id AS firm_id, f.slug AS firm_slug, f.name AS firm_name,
           f.website AS firm_website, f.founded_year, f.size_band,
           f.city AS firm_city, f.state_or_region AS firm_state, f.country AS firm_country,
           f.areas_served AS firm_areas
      FROM sod_designers d
      LEFT JOIN sod_designer_firm df ON df.designer_id = d.id AND df.is_current = true
      LEFT JOIN sod_firms f          ON f.id = df.firm_id
     WHERE d.slug = $1
     LIMIT 1`, [slug]);
  if (!r.rows[0]) return null;
  const d = r.rows[0];
  const lr = await pool.query(
    `SELECT url, kind FROM sod_links WHERE designer_id = $1 OR firm_id = $2`,
    [d.id, d.firm_id]
  );
  const linksByKind = {};
  for (const l of lr.rows) (linksByKind[l.kind] = linksByKind[l.kind] || []).push(l.url);
  return {
    id: d.id, slug: d.slug, full_name: d.full_name, role: d.role,
    city: d.firm_city || d.city || null,
    state: d.firm_state || d.state_or_region || null,
    country: d.firm_country || d.country || null,
    areas: [...new Set([...(d.areas_served || []), ...(d.firm_areas || [])])],
    bio: d.bio,
    premium_tier: d.premium_tier,
    claimed: !!d.claimed_at,
    firm: d.firm_name ? {
      id: d.firm_id, slug: d.firm_slug, name: d.firm_name,
      website: d.firm_website, founded_year: d.founded_year, size_band: d.size_band,
    } : null,
    headshot: (d.headshot_source === 'made-with-ai' || !d.headshot_url) ? '/img/made-with-ai.svg' : d.headshot_url,
    links: linksByKind,
  };
}

async function totalCount() {
  const r = await pool.query(
    `SELECT count(*) AS n FROM sod_designers WHERE role IS NULL OR role <> 'Manufacturer Rep'`
  );
  return parseInt(r.rows[0].n, 10);
}

module.exports = { listClients, getClient, totalCount, SORT_KEYS };