spb/rareindex

1import 'server-only';2import { cache } from 'react';3import { one, rows, sql, int, date } from './_util';45export interface SiteStats {6  assets: number;7  sales: number;8  observations: number;9  activeListings: number;10  sources: number;11  connectors: number;12  categoriesWithData: number;13  lastSaleAt: Date | null;14  lastUpdateAt: Date | null;15}1617/** Real counts for the homepage stat strip and the Data page. Never estimated. */18export const getSiteStats = cache(async (): Promise<SiteStats> => {19  const r = await one<Record<string, unknown>>(sql`20    SELECT21      (SELECT count(*) FROM assets) AS assets,22      (SELECT count(*) FROM sales WHERE status <> 'excluded') AS sales,23      (SELECT count(*) FROM price_observations) AS observations,24      (SELECT count(*) FROM listings WHERE availability = 'available') AS active_listings,25      (SELECT count(*) FROM sources WHERE active) AS sources,26      (SELECT count(*) FROM connectors WHERE status = 'active') AS connectors,27      (SELECT count(DISTINCT category_slug) FROM assets) AS categories_with_data,28      (SELECT max(sale_date) FROM sales) AS last_sale_at,29      GREATEST((SELECT max(created_at) FROM sales), (SELECT max(created_at) FROM price_observations), (SELECT max(updated_at) FROM asset_stats)) AS last_update_at30  `);31  return {32    assets: int(r?.assets),33    sales: int(r?.sales),34    observations: int(r?.observations),35    activeListings: int(r?.active_listings),36    sources: int(r?.sources),37    connectors: int(r?.connectors),38    categoriesWithData: int(r?.categories_with_data),39    lastSaleAt: date(r?.last_sale_at),40    lastUpdateAt: date(r?.last_update_at),41  };42});4344export interface LatestSale {45  id: string;46  assetSlug: string;47  assetTitle: string;48  categorySlug: string;49  heroImageUrl: string | null;50  saleDate: Date;51  price: number;52  currency: string;53  priceUsd: number;54  grader: string | null;55  grade: string | null;56  sourceId: string;57  sourceName: string;58  sourceUrl: string;59  saleType: string;60}6162export const getLatestSales = cache(async (limit = 12): Promise<LatestSale[]> => {63  const r = await rows<Record<string, unknown>>(sql`64    SELECT s.id, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, s.sale_date, s.price, s.currency, s.price_usd, s.grader, s.grade, s.source_id, src.name AS source_name, s.source_url, s.sale_type65    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id66    WHERE s.status = 'valid'67    ORDER BY s.sale_date DESC, s.created_at DESC LIMIT ${limit}68  `);69  return r.map((x) => ({70    id: String(x.id),71    assetSlug: String(x.asset_slug),72    assetTitle: String(x.asset_title),73    categorySlug: String(x.category_slug),74    heroImageUrl: (x.hero_image_url as string | null) ?? null,75    saleDate: date(x.sale_date)!,76    price: Number(x.price),77    currency: String(x.currency),78    priceUsd: Number(x.price_usd),79    grader: (x.grader as string | null) ?? null,80    grade: (x.grade as string | null) ?? null,81    sourceId: String(x.source_id),82    sourceName: String(x.source_name),83    sourceUrl: String(x.source_url),84    saleType: String(x.sale_type),85  }));86});87