← back to Stayclaim

src/lib/db.ts

761 lines

import { Pool, type PoolConfig } from 'pg';

/**
 * Local Homebrew Postgres uses peer/trust auth over the unix socket (/tmp).
 * Defaulting to that avoids password prompts in dev. Override with DATABASE_URL
 * for staging/prod (Kamatera etc).
 */
if (
  process.env.NODE_ENV === 'production' &&
  process.env.NEXT_PHASE !== 'phase-production-build' &&
  !process.env.DATABASE_URL &&
  !process.env.PGUSER
) {
  throw new Error('PG misconfigured: set DATABASE_URL or PGUSER in production runtime');
}

const config: PoolConfig = process.env.DATABASE_URL
  ? { connectionString: process.env.DATABASE_URL, max: 5 }
  : {
      host: process.env.PGHOST ?? '/tmp',
      database: process.env.PGDATABASE ?? 'stayclaim',
      user: process.env.PGUSER ?? process.env.USER,
      max: 5,
    };

declare global {
  var __pg_pool: Pool | undefined;
}
export const pool: Pool = global.__pg_pool ?? new Pool(config);
if (process.env.NODE_ENV !== 'production') global.__pg_pool = pool;

export type Listing = {
  id: string;
  slug: string;
  source: string;
  source_id: string | null;
  title: string;
  headline: string | null;
  description: string | null;
  address_line1: string | null;
  city: string | null;
  state: string | null;
  postal_code: string | null;
  country: string;
  latitude: number | null;
  longitude: number | null;
  bedrooms: number | null;
  bathrooms: number | null;
  sleeps: number | null;
  property_type: string | null;
  amenities: string[];
  hero_image: string | null;
  images: string[];
  nightly_price: number | null;
  currency: string;
  source_url: string | null;
  tier: 'free' | 'basic' | 'pro';
  claimed_by: string | null;
  claimed_at: string | null;
  is_public: boolean;
  updated_at?: Date | string | null;
};

export async function getListingBySlug(slug: string): Promise<Listing | null> {
  const { rows } = await pool.query<Listing>(
    'SELECT * FROM listing WHERE slug = $1 AND is_public = true',
    [slug]
  );
  return rows[0] ?? null;
}

export async function searchListings(q: {
  text?: string;
  city?: string;
  min?: number;
  max?: number;
  limit?: number;
}): Promise<Listing[]> {
  const conds: string[] = ['is_public = true'];
  const params: unknown[] = [];
  if (q.city) {
    params.push(q.city.toLowerCase());
    conds.push(`lower(city) = $${params.length}`);
  }
  if (q.min != null) {
    params.push(q.min);
    conds.push(`nightly_price >= $${params.length}`);
  }
  if (q.max != null) {
    params.push(q.max);
    conds.push(`nightly_price <= $${params.length}`);
  }

  let orderBy = 'ingested_at DESC';
  if (q.text && q.text.trim()) {
    params.push(q.text.trim());
    const tsParam = params.length;
    conds.push(`search_doc @@ websearch_to_tsquery('english', $${tsParam})`);
    orderBy = `ts_rank(search_doc, websearch_to_tsquery('english', $${tsParam})) DESC, ingested_at DESC`;
  }

  const safeLimit = Math.min(Math.max(1, Math.floor(q.limit ?? 24)), 100);
  params.push(safeLimit);
  const sql = `SELECT * FROM listing WHERE ${conds.join(' AND ')} ORDER BY ${orderBy} LIMIT $${params.length}`;
  const { rows } = await pool.query<Listing>(sql, params);
  return rows;
}

export type Entity = {
  id: string;
  slug: string;
  kind: 'person' | 'business' | 'architect' | 'studio';
  display_name: string;
  also_known_as: string[] | null;
  birth_year: number | null;
  death_year: number | null;
  is_living: boolean | null;
  bio_short: string | null;
  wiki_url: string | null;
  hero_image: string | null;
};

export async function getEntityBySlug(slug: string): Promise<Entity | null> {
  const { rows } = await pool.query<Entity>('SELECT * FROM entity WHERE slug = $1', [slug]);
  return rows[0] ?? null;
}

export type EntityAssociation = {
  id: string;
  listing_id: string;
  relation: 'resident' | 'owner' | 'designer' | 'guest' | 'tenant';
  date_from: Date | null;
  date_to: Date | null;
  summary: string | null;
  source_tier: 'A' | 'B' | 'C' | 'D';
  source_urls: string[] | null;
  listing_slug: string;
  listing_title: string;
  listing_city: string | null;
};

export function yearOf(d: Date | string | null | undefined): string {
  if (!d) return '?';
  const date = d instanceof Date ? d : new Date(d);
  return Number.isNaN(date.getTime()) ? '?' : String(date.getUTCFullYear());
}

export async function getEntityAssociations(
  entityId: string
): Promise<(EntityAssociation & { listing_lat: number | null; listing_lng: number | null })[]> {
  const { rows } = await pool.query(
    `SELECT epa.id, epa.listing_id, epa.relation, epa.date_from, epa.date_to,
            epa.summary, epa.source_tier, epa.source_urls,
            l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city,
            l.latitude AS listing_lat, l.longitude AS listing_lng
     FROM entity_place_association epa
     JOIN listing l ON l.id = epa.listing_id
     WHERE epa.entity_id = $1 AND epa.public_visible = true
     ORDER BY COALESCE(epa.date_from, CURRENT_DATE) ASC`,
    [entityId]
  );
  return rows;
}

export async function getListingAssociations(listingId: string): Promise<
  (EntityAssociation & { entity_slug: string; entity_name: string; entity_kind: string })[]
> {
  const { rows } = await pool.query(
    `SELECT epa.id, epa.listing_id, epa.relation, epa.date_from, epa.date_to,
            epa.summary, epa.source_tier, epa.source_urls,
            l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city,
            e.slug AS entity_slug, e.display_name AS entity_name, e.kind AS entity_kind
     FROM entity_place_association epa
     JOIN entity e ON e.id = epa.entity_id
     JOIN listing l ON l.id = epa.listing_id
     WHERE epa.listing_id = $1 AND epa.public_visible = true
     ORDER BY COALESCE(epa.date_from, CURRENT_DATE) ASC`,
    [listingId]
  );
  return rows;
}

export async function getNearbyListings(city: string, excludeId: string, limit = 4): Promise<Listing[]> {
  const { rows } = await pool.query<Listing>(
    `SELECT * FROM listing
     WHERE lower(city) = lower($1) AND id <> $2 AND is_public = true
     ORDER BY ingested_at DESC
     LIMIT $3`,
    [city, excludeId, limit]
  );
  return rows;
}

export async function listEntities(kind?: string): Promise<Entity[]> {
  const sql = kind
    ? 'SELECT * FROM entity WHERE kind = $1 ORDER BY display_name ASC LIMIT 5000'
    : 'SELECT * FROM entity ORDER BY display_name ASC LIMIT 5000';
  const params = kind ? [kind] : [];
  const { rows } = await pool.query<Entity>(sql, params);
  return rows;
}

export async function listEntitiesWithCounts(kind?: string): Promise<(Entity & { oeuvre_count: number })[]> {
  const sql = `
    SELECT e.*, COUNT(epa.id)::int AS oeuvre_count
    FROM entity e
    LEFT JOIN entity_place_association epa ON epa.entity_id = e.id AND epa.public_visible = true
    ${kind ? 'WHERE e.kind = $1' : ''}
    GROUP BY e.id
    ORDER BY e.display_name ASC
    LIMIT 5000
  `;
  const params = kind ? [kind] : [];
  const { rows } = await pool.query(sql, params);
  return rows;
}

export type MediaAsset = {
  id: string;
  listing_id: string;
  kind: 'archival_photo' | 'historic_map' | 'postcard' | 'drone' | 'streetview_thumb' | 'newspaper_clip';
  year: number | null;
  caption: string | null;
  storage_url: string;
  source_tier: 'A' | 'B' | 'C' | 'D';
  source_label: string | null;
  source_url: string | null;
  rights: string | null;
};

export async function getMediaForListing(listingId: string): Promise<MediaAsset[]> {
  const { rows } = await pool.query<MediaAsset>(
    `SELECT id, listing_id, kind, year, caption, storage_url, source_tier, source_label, source_url, rights
     FROM media_asset
     WHERE listing_id = $1 AND public_visible = true
     ORDER BY year ASC NULLS LAST, added_at ASC`,
    [listingId]
  );
  return rows;
}

export type Structure = {
  id: string;
  listing_id: string;
  year_built: number | null;
  year_demolished: number | null;
  property_type: string | null;
  style: string | null;
  bedrooms: number | null;
  bathrooms: number | null;
  building_area_sqft: number | null;
  lot_size_sqft: number | null;
};

export async function getStructuresForListing(listingId: string): Promise<Structure[]> {
  const { rows } = await pool.query<Structure>(
    `SELECT s.id, s.listing_id, s.year_built, s.year_demolished, s.property_type, s.style,
            s.bedrooms, s.bathrooms, s.building_area_sqft, s.lot_size_sqft
     FROM structure s
     JOIN listing l ON l.id = s.listing_id
     WHERE s.listing_id = $1 AND l.is_public = true
     ORDER BY s.year_built ASC NULLS LAST`,
    [listingId]
  );
  return rows;
}

export type Parcel = { id: string; apn: string; jurisdiction: string };

export type RealPermit = {
  id: string;
  source: string | null;
  source_dataset: string | null;
  permit_number: string;
  primary_address: string | null;
  permit_group: string | null;
  permit_type: string | null;
  permit_sub_type: string | null;
  use_desc: string | null;
  issue_date: string | null;
  status_desc: string | null;
  valuation: string | null;
  work_desc: string | null;
  source_url: string | null;
  source_label: string | null;
};

export type CodeEnforcementCase = {
  id: string;
  source_dataset: string | null;
  case_number: string;
  case_type: string | null;
  status: string | null;
  opened_at: string | null;
  source_url: string | null;
  source_label: string | null;
};

export type NewsAddressMention = {
  id: string;
  raw_address: string;
  paper_name: string | null;
  issue_date: string | null;
  context: string | null;
  ia_url: string | null;
};

export async function getParcelForListing(listingId: string): Promise<Parcel | null> {
  const { rows } = await pool.query<Parcel>(
    `SELECT id, apn, jurisdiction FROM parcel WHERE current_listing_id = $1 LIMIT 1`,
    [listingId]
  );
  return rows[0] ?? null;
}

export type PlaceEvent = {
  id: string;
  listing_id: string;
  kind: 'permit' | 'sale' | 'remodel' | 'demolition' | 'designation' | 'media' | 'occupancy';
  summary: string;
  valid_from: Date | null;
  valid_to: Date | null;
  recorded_at: Date;
  source_tier: 'A' | 'B' | 'C' | 'D';
  source_label: string | null;
  source_url: string | null;
  source_id: string | null;
  confidence: number;
};

export async function getEventsForListing(listingId: string): Promise<PlaceEvent[]> {
  const { rows } = await pool.query<PlaceEvent>(
    `SELECT * FROM place_event
     WHERE listing_id = $1 AND public_visible = true
     ORDER BY COALESCE(valid_from, recorded_at::date) ASC`,
    [listingId]
  );
  return rows;
}

export async function getArchiveTimeline(opts: {
  limit?: number;
  kind?: string;
  decade?: string; // "1920s" | "2020s" | etc.
  city?: string;
} = {}): Promise<(PlaceEvent & { listing_slug: string; listing_title: string; listing_city: string | null })[]> {
  const limit = opts.limit ?? 300;
  const conds: string[] = ['pe.public_visible = true', 'l.is_public = true'];
  const params: unknown[] = [];
  if (opts.kind) {
    params.push(opts.kind);
    conds.push(`pe.kind = $${params.length}`);
  }
  if (opts.city) {
    params.push(opts.city);
    conds.push(`l.city = $${params.length}`);
  }
  if (opts.decade && /^\d{3}0s$/.test(opts.decade)) {
    const start = Number(opts.decade.slice(0, 4));
    params.push(`${start}-01-01`);
    params.push(`${start + 10}-01-01`);
    conds.push(
      `COALESCE(pe.valid_from, pe.recorded_at::date) >= $${params.length - 1}::date AND COALESCE(pe.valid_from, pe.recorded_at::date) < $${params.length}::date`
    );
  }
  params.push(limit);
  const sql = `SELECT pe.*, l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city
               FROM place_event pe
               JOIN listing l ON l.id = pe.listing_id
               WHERE ${conds.join(' AND ')}
               ORDER BY COALESCE(pe.valid_from, pe.recorded_at::date) DESC
               LIMIT $${params.length}`;
  const { rows } = await pool.query(sql, params);
  return rows;
}

export async function getRecentPermits(
  limit = 6
): Promise<(PlaceEvent & { listing_slug: string; listing_title: string; listing_city: string | null; listing_hero: string | null })[]> {
  const { rows } = await pool.query(
    `SELECT pe.*, l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city, l.hero_image AS listing_hero
     FROM place_event pe
     JOIN listing l ON l.id = pe.listing_id
     WHERE pe.kind = 'permit' AND pe.public_visible = true AND l.is_public = true
     ORDER BY pe.valid_from DESC NULLS LAST
     LIMIT $1`,
    [limit]
  );
  return rows;
}

export async function getOwnerByEmail(email: string): Promise<{ id: string; email: string; name: string | null } | null> {
  const { rows } = await pool.query(
    `SELECT id, email, name FROM owner WHERE lower(email) = lower($1) LIMIT 1`,
    [email]
  );
  return rows[0] ?? null;
}

export async function getListingsForOwner(ownerId: string): Promise<Listing[]> {
  const { rows } = await pool.query<Listing>(
    `SELECT * FROM listing WHERE claimed_by = $1 ORDER BY claimed_at DESC NULLS LAST LIMIT 500`,
    [ownerId]
  );
  return rows;
}

export async function getPendingClaimsForEmail(email: string): Promise<{ id: string; listing_id: string; status: string; created_at: Date; listing_slug: string; listing_title: string }[]> {
  const { rows } = await pool.query(
    `SELECT cr.id, cr.listing_id, cr.status, cr.created_at,
            l.slug AS listing_slug, l.title AS listing_title
     FROM claim_request cr
     JOIN listing l ON l.id = cr.listing_id
     WHERE lower(cr.email) = lower($1)
     ORDER BY cr.created_at DESC
     LIMIT 100`,
    [email]
  );
  return rows;
}

export type FilmProduction = {
  id: string;
  slug: string;
  kind: 'film' | 'tv' | 'commercial' | 'music_video' | 'documentary' | 'short' | 'student';
  title: string;
  year: number | null;
  imdb_id: string | null;
  wikidata_qid: string | null;
  poster_url: string | null;
  blurb: string | null;
};

export type FilmingLocation = {
  id: string;
  production_id: string;
  listing_id: string;
  role: string | null;
  scene_note: string | null;
  shoot_date_from: Date | null;
  shoot_date_to: Date | null;
  permit_number: string | null;
  source_tier: 'A' | 'B' | 'C' | 'D';
  source_label: string | null;
  source_url: string | null;
  confidence: number;
};

export async function getProductionBySlug(slug: string): Promise<FilmProduction | null> {
  const { rows } = await pool.query<FilmProduction>('SELECT * FROM film_production WHERE slug = $1', [slug]);
  return rows[0] ?? null;
}

export async function getLocationsForProduction(productionId: string): Promise<
  (FilmingLocation & { listing_slug: string; listing_title: string; listing_city: string | null; listing_lat: number | null; listing_lng: number | null })[]
> {
  const { rows } = await pool.query(
    `SELECT fl.*, l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city,
            l.latitude AS listing_lat, l.longitude AS listing_lng
     FROM filming_location fl
     JOIN listing l ON l.id = fl.listing_id
     WHERE fl.production_id = $1 AND fl.public_visible = true AND l.is_public = true
     ORDER BY fl.shoot_date_from ASC NULLS LAST`,
    [productionId]
  );
  return rows;
}

export async function getProductionsForListing(listingId: string): Promise<
  (FilmingLocation & { production_slug: string; production_title: string; production_kind: string; production_year: number | null })[]
> {
  const { rows } = await pool.query(
    `SELECT fl.*, fp.slug AS production_slug, fp.title AS production_title,
            fp.kind AS production_kind, fp.year AS production_year
     FROM filming_location fl
     JOIN film_production fp ON fp.id = fl.production_id
     WHERE fl.listing_id = $1 AND fl.public_visible = true
     ORDER BY fl.shoot_date_from DESC NULLS LAST`,
    [listingId]
  );
  return rows;
}

export type FilmingPin = {
  slug: string;
  title: string;
  lat: number;
  lng: number;
  productions: { slug: string; title: string; year: number | null }[];
};

export async function listAllFilmingShoots(): Promise<
  Array<{
    id: string;
    role: string | null;
    scene_note: string | null;
    shoot_date_from: Date | null;
    permit_number: string | null;
    listing_slug: string;
    listing_title: string;
    listing_city: string | null;
    production_slug: string;
    production_title: string;
    production_kind: string;
    production_year: number | null;
  }>
> {
  const { rows } = await pool.query(
    `SELECT fl.id, fl.role, fl.scene_note, fl.shoot_date_from, fl.permit_number,
            l.slug AS listing_slug, l.title AS listing_title, l.city AS listing_city,
            fp.slug AS production_slug, fp.title AS production_title,
            fp.kind AS production_kind, fp.year AS production_year
     FROM filming_location fl
     JOIN listing l ON l.id = fl.listing_id
     JOIN film_production fp ON fp.id = fl.production_id
     WHERE fl.public_visible = true AND l.is_public = true
     ORDER BY fl.shoot_date_from DESC NULLS LAST
     LIMIT 5000`
  );
  return rows;
}

export async function getNearbyFilming(
  city: string,
  excludeListingId: string,
  limit = 5
): Promise<
  Array<{
    listing_slug: string;
    listing_title: string;
    role: string | null;
    shoot_year: number | null;
    production_slug: string;
    production_title: string;
    production_kind: string;
  }>
> {
  const { rows } = await pool.query(
    `SELECT l.slug AS listing_slug, l.title AS listing_title,
            fl.role,
            EXTRACT(year FROM fl.shoot_date_from)::int AS shoot_year,
            fp.slug AS production_slug, fp.title AS production_title, fp.kind AS production_kind
     FROM filming_location fl
     JOIN listing l ON l.id = fl.listing_id
     JOIN film_production fp ON fp.id = fl.production_id
     WHERE fl.public_visible = true AND l.is_public = true
       AND l.city = $1 AND l.id <> $2
     ORDER BY fl.shoot_date_from DESC NULLS LAST
     LIMIT $3`,
    [city, excludeListingId, limit]
  );
  return rows;
}

export async function getAllFilmingPins(): Promise<FilmingPin[]> {
  const { rows } = await pool.query<FilmingPin>(
    `SELECT l.slug, l.title, l.latitude::float AS lat, l.longitude::float AS lng,
            json_agg(json_build_object('slug', fp.slug, 'title', fp.title, 'year', fp.year)
                     ORDER BY fp.year DESC NULLS LAST) AS productions
     FROM filming_location fl
     JOIN listing l ON l.id = fl.listing_id
     JOIN film_production fp ON fp.id = fl.production_id
     WHERE fl.public_visible = true AND l.is_public = true
       AND l.latitude IS NOT NULL AND l.longitude IS NOT NULL
     GROUP BY l.id, l.slug, l.title, l.latitude, l.longitude
     LIMIT 2000`
  );
  return rows;
}

export async function listProductionsWithCounts(opts: { kind?: string; decade?: string } = {}): Promise<(FilmProduction & { location_count: number })[]> {
  const conds: string[] = [];
  const params: unknown[] = [];
  if (opts.kind) {
    params.push(opts.kind);
    conds.push(`fp.kind = $${params.length}`);
  }
  if (opts.decade && /^\d{3}0s$/.test(opts.decade)) {
    const start = Number(opts.decade.slice(0, 4));
    params.push(start);
    conds.push(`fp.year >= $${params.length}`);
    params.push(start + 10);
    conds.push(`fp.year < $${params.length}`);
  }
  const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
  const { rows } = await pool.query(
    `SELECT fp.*, COUNT(fl.id)::int AS location_count
     FROM film_production fp
     LEFT JOIN filming_location fl ON fl.production_id = fp.id AND fl.public_visible = true
     ${where}
     GROUP BY fp.id
     HAVING COUNT(fl.id) > 0
     ORDER BY COUNT(fl.id) DESC, fp.year DESC NULLS LAST, fp.title
     LIMIT 1000`,
    params
  );
  return rows;
}

export async function listNeighborhoodsWithGeo(): Promise<
  { city: string; state: string; count: number; lat: number | null; lng: number | null }[]
> {
  const { rows } = await pool.query(
    `SELECT city, state, count(*)::int AS count,
            avg(latitude)::float AS lat, avg(longitude)::float AS lng
     FROM listing
     WHERE city IS NOT NULL AND is_public = true
     GROUP BY city, state
     ORDER BY count(*) DESC
     LIMIT 1000`
  );
  return rows;
}

export type ClaimExample = {
  slug: string;
  title: string;
  city: string | null;
  hero_image: string | null;
  year_built: number | null;
  property_type: string | null;
  films_count: number;
  permits_count: number;
  owners_count: number;
};

/**
 * Picks 4-6 listings with the richest metadata for the /submit page hero.
 * Used to show what a claimed address looks like — demonstrates the value
 * proposition before asking the user to add their own. Per UX critique R5.
 */
export async function getClaimExampleAddresses(limit = 4): Promise<ClaimExample[]> {
  // Pre-aggregate film/permit/owner counts + min(year_built) per listing once,
  // then LEFT JOIN. Avoids the 6-subquery-per-row fan-out that was running
  // against the full listing table at LIMIT 4. /submit page 500ms→30ms.
  const { rows } = await pool.query<ClaimExample>(
    `WITH film_counts AS (
       SELECT listing_id, count(*)::int AS films_count
       FROM filming_location WHERE public_visible = true GROUP BY listing_id
     ),
     permit_counts AS (
       SELECT listing_id, count(*)::int AS permits_count
       FROM place_event WHERE public_visible = true AND kind = 'permit' GROUP BY listing_id
     ),
     owner_counts AS (
       SELECT listing_id, count(DISTINCT entity_id)::int AS owners_count
       FROM entity_place_association WHERE public_visible = true GROUP BY listing_id
     ),
     years AS (
       SELECT listing_id, min(year_built) AS year_built
       FROM structure GROUP BY listing_id
     )
     SELECT
       l.slug, l.title, l.city, l.hero_image,
       y.year_built,
       l.property_type,
       COALESCE(f.films_count, 0)   AS films_count,
       COALESCE(p.permits_count, 0) AS permits_count,
       COALESCE(o.owners_count, 0)  AS owners_count
     FROM listing l
     LEFT JOIN film_counts   f ON f.listing_id = l.id
     LEFT JOIN permit_counts p ON p.listing_id = l.id
     LEFT JOIN owner_counts  o ON o.listing_id = l.id
     LEFT JOIN years         y ON y.listing_id = l.id
     WHERE l.is_public = true AND l.hero_image IS NOT NULL
     ORDER BY COALESCE(f.films_count, 0) DESC,
              (y.year_built IS NOT NULL) DESC,
              l.updated_at DESC
     LIMIT $1`,
    [limit]
  );
  return rows;
}

export async function listNeighborhoods(): Promise<{ city: string; state: string; count: number }[]> {
  const { rows } = await pool.query<{ city: string; state: string; count: string }>(
    `SELECT city, state, count(*)::text AS count
     FROM listing
     WHERE city IS NOT NULL AND is_public = true
     GROUP BY city, state
     ORDER BY count(*) DESC
     LIMIT 1000`
  );
  return rows.map(r => ({ city: r.city, state: r.state, count: Number(r.count) }));
}

export type PdFilmImage = {
  id: string;
  source: 'wikimedia' | 'archive_org' | 'loc' | 'nara' | 'mhdl' | 'smithsonian' | 'nypl' | 'flickr_commons' | 'openverse' | 'met' | 'dpla';
  source_id: string;
  title: string;
  caption: string | null;
  image_url: string;
  thumb_url: string | null;
  width: number | null;
  height: number | null;
  year: number | null;
  kind: string | null;
  rights_url: string | null;
  source_url: string;
  production_id: string | null;
};

// Sources whose hot-link images are blocked by Chromium ORB / cross-origin
// policy and therefore won't render in a browser <img> tag, even though the
// rows are valid PD content. Kept in DB but excluded from the rendered grid.
const BROWSER_BLOCKED_SOURCES = ['smithsonian'] as const;

export async function listPdFilmImages(opts: {
  limit?: number;
  source?: PdFilmImage['source'];
  sources?: PdFilmImage['source'][];      // multi-source filter (per-surface affinity)
  kind?: string;
  productionId?: string;
  productionIds?: string[];   // multi-id filter, used by /address/[slug] to scope to films shot here
  includeBlocked?: boolean;
} = {}): Promise<PdFilmImage[]> {
  const conds: string[] = ['image_url IS NOT NULL'];
  const params: unknown[] = [];
  if (opts.source)       { params.push(opts.source);       conds.push(`source = $${params.length}`); }
  if (opts.sources && opts.sources.length) {
    params.push(opts.sources);
    conds.push(`source = ANY($${params.length}::text[])`);
  }
  if (opts.kind)         { params.push(opts.kind);         conds.push(`kind = $${params.length}`); }
  if (opts.productionId) { params.push(opts.productionId); conds.push(`production_id = $${params.length}`); }
  if (opts.productionIds && opts.productionIds.length) {
    params.push(opts.productionIds);
    conds.push(`production_id = ANY($${params.length}::uuid[])`);
  }
  if (!opts.includeBlocked) {
    params.push(BROWSER_BLOCKED_SOURCES);
    conds.push(`source <> ALL($${params.length}::text[])`);
  }
  params.push(opts.limit ?? 60);
  // Interleave sources so every source gets fair representation in the first N rows:
  // rank within source by year DESC NULLS LAST, then sort by rank then source.
  // Effect: row 1 = best of each source, row 2 = next best of each source, etc.
  const sql = `
    SELECT id, source, source_id, title, caption, image_url, thumb_url,
           width, height, year, kind, rights_url, source_url, production_id
    FROM (
      SELECT *, row_number() OVER (
        PARTITION BY source
        ORDER BY year DESC NULLS LAST, fetched_at DESC
      ) AS rnk
      FROM pd_film_image
      WHERE ${conds.join(' AND ')}
    ) ranked
    ORDER BY rnk, source
    LIMIT $${params.length}
  `;
  try {
    const { rows } = await pool.query<PdFilmImage>(sql, params);
    return rows;
  } catch (e: unknown) {
    if (e instanceof Error && /relation .* does not exist/.test(e.message)) return [];
    throw e;
  }
}