TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { cache } from 'react';3import type { SQL } from 'drizzle-orm';4import { descendants } from '@rareindex/taxonomy';5import { rows, one, sql, num, int, str, date, joinAnd, textArray } from './_util';6import { classifySale, type SaleVerification } from '@rareindex/valuation';78export interface AssetCard {9 id: string;10 slug: string;11 title: string;12 name: string;13 categorySlug: string;14 familySlug: string;15 setName: string | null;16 setSlug: string | null;17 number: string | null;18 year: number | null;19 variant: string | null;20 edition: string | null;21 brand: string | null;22 heroImageUrl: string | null;23 rivUsd: number | null;24 rivLowUsd: number | null;25 rivHighUsd: number | null;26 rivConfidence: number | null;27 rivSampleSize: number;28 latestSaleUsd: number | null;29 latestSaleAt: Date | null;30 change1d: number | null;31 change7d: number | null;32 change30d: number | null;33 change1y: number | null;34 salesCount: number;35 sales30d: number;36 activeListings: number;37 minAskUsd: number | null;38 liquidityScore: number | null;39 rarityScore: number | null;40 momentum30d: number | null;41 trendingScore: number | null;42 valueOpportunity: number | null;43 dataQuality: number | null;44 watchers: number;45 observationsCount: number;46 updatedAt: Date | null;47 /** latest price-guide observation (never a transaction); attached on demand by attachGuidePrices() */48 guideUsd?: number | null;49 guideKind?: string | null;50 guideDate?: string | null;51 guideSource?: string | null;52}5354export const ASSET_CARD_SELECT = sql`a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.set_name, a.set_slug, a.number, a.year, a.variant, a.edition, a.brand, a.hero_image_url,55 s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, coalesce(s.riv_sample_size, 0) AS riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_1d, s.change_7d, s.change_30d, s.change_1y,56 coalesce(s.sales_count, 0) AS sales_count, coalesce(s.sales_30d, 0) AS sales_30d, coalesce(s.active_listings, 0) AS active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.momentum_30d, s.trending_score, s.value_opportunity, s.data_quality, coalesce(s.watchers, 0) AS watchers, coalesce(s.observations_count, 0) AS observations_count, s.updated_at AS stats_updated_at`;5758export function toAssetCard(x: Record<string, unknown>): AssetCard {59 return {60 id: String(x.id),61 slug: String(x.slug),62 title: String(x.title),63 name: String(x.name),64 categorySlug: String(x.category_slug),65 familySlug: String(x.family_slug),66 setName: str(x.set_name),67 setSlug: str(x.set_slug),68 number: str(x.number),69 year: num(x.year),70 variant: str(x.variant),71 edition: str(x.edition),72 brand: str(x.brand),73 heroImageUrl: str(x.hero_image_url),74 rivUsd: num(x.riv_usd),75 rivLowUsd: num(x.riv_low_usd),76 rivHighUsd: num(x.riv_high_usd),77 rivConfidence: num(x.riv_confidence),78 rivSampleSize: int(x.riv_sample_size),79 latestSaleUsd: num(x.latest_sale_usd),80 latestSaleAt: date(x.latest_sale_at),81 change1d: num(x.change_1d),82 change7d: num(x.change_7d),83 change30d: num(x.change_30d),84 change1y: num(x.change_1y),85 salesCount: int(x.sales_count),86 sales30d: int(x.sales_30d),87 activeListings: int(x.active_listings),88 minAskUsd: num(x.min_ask_usd),89 liquidityScore: num(x.liquidity_score),90 rarityScore: num(x.rarity_score),91 momentum30d: num(x.momentum_30d),92 trendingScore: num(x.trending_score),93 valueOpportunity: num(x.value_opportunity),94 dataQuality: num(x.data_quality),95 watchers: int(x.watchers),96 observationsCount: int(x.observations_count),97 updatedAt: date(x.stats_updated_at),98 };99}100101export type ExploreSort = 'relevance' | 'riv' | 'change30d' | 'change7d' | 'sales' | 'liquidity' | 'rarity' | 'trending' | 'newest' | 'latest_sale' | 'opportunity' | 'name' | 'number';102export type HasFilter = 'sales' | 'valuation' | 'listings' | 'observations' | 'images';103104export interface ExploreFilters {105 category?: string | null;106 grader?: string | null;107 grade?: string | null;108 priceMin?: number | null;109 priceMax?: number | null;110 liquidityMin?: number | null;111 rarityMin?: number | null;112 momentumMin?: number | null;113 yearFrom?: number | null;114 yearTo?: number | null;115 brand?: string | null;116 set?: string | null;117 hasValuation?: boolean;118 has?: HasFilter | null;119 /** explicit list of category slugs (overrides `category` scope) */120 scope?: string[] | null;121 q?: string | null;122 sort?: ExploreSort;123 page?: number;124 pageSize?: number;125}126127const SORTS: Record<ExploreSort, SQL> = {128 // Data-rich records first, catalog-only records last — never hidden.129 relevance: sql`(s.riv_usd IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, coalesce(s.observations_count, 0) DESC, (a.hero_image_url IS NOT NULL) DESC, a.created_at DESC`,130 riv: sql`s.riv_usd DESC NULLS LAST`,131 change30d: sql`s.change_30d DESC NULLS LAST`,132 change7d: sql`s.change_7d DESC NULLS LAST`,133 sales: sql`s.sales_count DESC NULLS LAST`,134 liquidity: sql`s.liquidity_score DESC NULLS LAST`,135 rarity: sql`s.rarity_score DESC NULLS LAST`,136 trending: sql`s.trending_score DESC NULLS LAST`,137 newest: sql`a.created_at DESC`,138 latest_sale: sql`s.latest_sale_at DESC NULLS LAST`,139 opportunity: sql`s.value_opportunity ASC NULLS LAST`, // most below RIV first140 name: sql`a.name ASC`,141 number: sql`a.set_name ASC NULLS LAST, (substring(a.number from '^[0-9]+'))::int ASC NULLS LAST, a.number ASC NULLS LAST`,142};143144const HAS: Record<HasFilter, SQL> = {145 sales: sql`coalesce(s.sales_count, 0) > 0`,146 valuation: sql`s.riv_usd IS NOT NULL`,147 listings: sql`coalesce(s.active_listings, 0) > 0`,148 observations: sql`coalesce(s.observations_count, 0) > 0 OR EXISTS (SELECT 1 FROM price_observations o WHERE o.asset_id = a.id)`,149 images: sql`a.hero_image_url IS NOT NULL`,150};151152export function categoryScope(slug: string): string[] {153 return [slug, ...descendants(slug)];154}155156export async function exploreAssets(f: ExploreFilters): Promise<{ items: AssetCard[]; total: number; page: number; pageSize: number }> {157 const pageSize = Math.min(Math.max(f.pageSize ?? 48, 1), 200);158 const page = Math.max(1, f.page ?? 1);159 const where: SQL[] = [];160 if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);161 else if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);162 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);163 if (f.set) where.push(sql`a.set_slug = ${f.set}`);164 if (f.priceMin != null) where.push(sql`s.riv_usd >= ${f.priceMin}`);165 if (f.priceMax != null) where.push(sql`s.riv_usd <= ${f.priceMax}`);166 if (f.liquidityMin != null) where.push(sql`s.liquidity_score >= ${f.liquidityMin}`);167 if (f.rarityMin != null) where.push(sql`s.rarity_score >= ${f.rarityMin}`);168 if (f.momentumMin != null) where.push(sql`s.momentum_30d >= ${f.momentumMin}`);169 if (f.yearFrom != null) where.push(sql`a.year >= ${f.yearFrom}`);170 if (f.yearTo != null) where.push(sql`a.year <= ${f.yearTo}`);171 if (f.hasValuation) where.push(sql`s.riv_usd IS NOT NULL`);172 if (f.has) where.push(sql`(${HAS[f.has]})`);173 if (f.grader) where.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = a.id AND v.grader = ${f.grader} ${f.grade ? sql`AND v.grade = ${f.grade}` : sql``})`);174 if (f.q && f.q.trim().length >= 2) {175 const q = f.q.trim();176 where.push(sql`(a.title ILIKE ${'%' + q + '%'} OR a.title % ${q} OR a.set_name ILIKE ${'%' + q + '%'} OR a.set_code ILIKE ${q} OR a.number ILIKE ${q} OR a.reference ILIKE ${q})`);177 }178 const order = SORTS[f.sort ?? 'relevance'];179 const r = await rows<Record<string, unknown>>(sql`180 SELECT ${ASSET_CARD_SELECT}, count(*) OVER() AS total181 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id182 WHERE ${joinAnd(where)}183 ORDER BY ${order}, a.title ASC184 LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}185 `);186 return { items: r.map(toAssetCard), total: r.length ? int(r[0]!.total) : 0, page, pageSize };187}188189/** Ranked lists for home/markets widgets. `scope` restricts to category slugs. */190export type RankedKind = 'trending' | 'gainers' | 'losers' | 'volume' | 'watched' | 'newest' | 'expensive' | 'opportunity' | 'liquid' | 'newest_priced' | 'documented' | 'with_sales';191export async function rankedAssets(kind: RankedKind, opts: { scope?: string[]; limit?: number; window?: '1d' | '7d' | '30d' } = {}): Promise<AssetCard[]> {192 const limit = opts.limit ?? 10;193 const scope = opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``;194 const chg = opts.window === '1d' ? sql`s.change_1d` : opts.window === '30d' ? sql`s.change_30d` : sql`s.change_7d`;195 const spec: Record<typeof kind, { where: SQL; order: SQL }> = {196 // Trending quality gates (§140): canonical identity with a transaction-based valuation, a real197 // 30-day market and a plausible price history — noisy titles with one odd sale must not lead.198 trending: { where: sql`s.trending_score IS NOT NULL AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_30d, 0) >= 2 AND (s.change_30d IS NULL OR abs(s.change_30d) <= 5)`, order: sql`s.trending_score DESC` },199 // Movers need a valuation that can move: ≥ 5 transactions, medium confidence, a plausible move (≤ ±500 %) and200 // at least one sale in the last year (a stale series cannot "move").201 gainers: { where: sql`${chg} IS NOT NULL AND ${chg} > 0 AND ${chg} <= 5 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_1y, 0) >= 3`, order: sql`${chg} DESC` },202 losers: { where: sql`${chg} IS NOT NULL AND ${chg} < 0 AND ${chg} >= -0.99 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_1y, 0) >= 3`, order: sql`${chg} ASC` },203 volume: { where: sql`s.volume_30d_usd IS NOT NULL AND s.volume_30d_usd > 0`, order: sql`s.volume_30d_usd DESC` },204 watched: { where: sql`s.watchers > 0`, order: sql`s.watchers DESC, s.views_30d DESC` },205 newest: { where: sql`true`, order: sql`a.created_at DESC` },206 expensive: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.riv_usd DESC` },207 // value_opportunity = (best gated ask − RIV) / RIV: negative = below the valuation. A deal is between −10 % and208 // −50 %; deeper discounts are review cases (§174), implausible ratios never reach the table (§84, §196).209 opportunity: { where: sql`s.value_opportunity IS NOT NULL AND s.value_opportunity <= -0.1 AND s.value_opportunity >= -0.5 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5`, order: sql`s.value_opportunity ASC` },210 liquid: { where: sql`s.liquidity_score IS NOT NULL`, order: sql`s.liquidity_score DESC` },211 // Fallback rails used while valuations are still being computed (§192: honest, data-backed)212 newest_priced: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.updated_at DESC` },213 documented: { where: sql`a.hero_image_url IS NOT NULL AND (coalesce(s.observations_count, 0) > 0 OR coalesce(s.sales_count, 0) > 0)`, order: sql`coalesce(s.sales_count, 0) DESC, coalesce(s.observations_count, 0) DESC, s.updated_at DESC` },214 with_sales: { where: sql`coalesce(s.sales_count, 0) > 0`, order: sql`s.latest_sale_at DESC` },215 };216 const s = spec[kind];217 const r = await rows<Record<string, unknown>>(sql`218 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id219 WHERE ${s.where} ${scope} ORDER BY ${s.order} NULLS LAST LIMIT ${limit}220 `);221 return r.map(toAssetCard);222}223224export interface AssetDetail extends AssetCard {225 canonicalKey: string;226 subcategorySlug: string | null;227 franchise: string | null;228 series: string | null;229 setCode: string | null;230 model: string | null;231 reference: string | null;232 language: string | null;233 region: string | null;234 country: string | null;235 material: string | null;236 size: string | null;237 color: string | null;238 rarity: string | null;239 productionQuantity: number | null;240 originalMsrp: number | null;241 originalMsrpCurrency: string | null;242 releaseDate: string | null;243 description: string | null;244 identifiers: Record<string, string>;245 metadata: Record<string, unknown>;246 verified: boolean;247 createdAt: Date;248 athUsd: number | null;249 athAt: Date | null;250 atlUsd: number | null;251 atlAt: Date | null;252 sales1y: number;253 volume30dUsd: number | null;254 sourcesCount: number;255 momentum7d: number | null;256 momentum90d: number | null;257 momentum1y: number | null;258 change90d: number | null;259 rivVariantId: string | null;260}261262export const getAssetBySlug = cache(async (slug: string): Promise<AssetDetail | null> => {263 const x = await one<Record<string, unknown>>(sql`264 SELECT ${ASSET_CARD_SELECT}, a.canonical_key, a.subcategory_slug, a.franchise, a.series, a.set_code, a.model, a.reference, a.language, a.region, a.country, a.material, a.size, a.color, a.rarity, a.production_quantity, a.original_msrp, a.original_msrp_currency, a.release_date, a.description, a.identifiers, a.metadata, a.verified, a.created_at,265 s.ath_usd, s.ath_at, s.atl_usd, s.atl_at, coalesce(s.sales_1y, 0) AS sales_1y, s.volume_30d_usd, coalesce(s.sources_count, 0) AS sources_count, s.momentum_7d, s.momentum_90d, s.momentum_1y, s.change_90d, s.riv_variant_id266 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug = ${slug} LIMIT 1267 `);268 if (!x) return null;269 return {270 ...toAssetCard(x),271 canonicalKey: String(x.canonical_key),272 subcategorySlug: str(x.subcategory_slug),273 franchise: str(x.franchise),274 series: str(x.series),275 setCode: str(x.set_code),276 model: str(x.model),277 reference: str(x.reference),278 language: str(x.language),279 region: str(x.region),280 country: str(x.country),281 material: str(x.material),282 size: str(x.size),283 color: str(x.color),284 rarity: str(x.rarity),285 productionQuantity: num(x.production_quantity),286 originalMsrp: num(x.original_msrp),287 originalMsrpCurrency: str(x.original_msrp_currency),288 releaseDate: str(x.release_date),289 description: str(x.description),290 identifiers: (x.identifiers as Record<string, string>) ?? {},291 metadata: (x.metadata as Record<string, unknown>) ?? {},292 verified: Boolean(x.verified),293 createdAt: date(x.created_at)!,294 athUsd: num(x.ath_usd),295 athAt: date(x.ath_at),296 atlUsd: num(x.atl_usd),297 atlAt: date(x.atl_at),298 sales1y: int(x.sales_1y),299 volume30dUsd: num(x.volume_30d_usd),300 sourcesCount: int(x.sources_count),301 momentum7d: num(x.momentum_7d),302 momentum90d: num(x.momentum_90d),303 momentum1y: num(x.momentum_1y),304 change90d: num(x.change_90d),305 rivVariantId: str(x.riv_variant_id),306 };307});308309export interface VariantRow {310 id: string;311 variantKey: string;312 label: string;313 grader: string | null;314 grade: string | null;315 qualifier: string | null;316 condition: string | null;317 completeness: string | null;318 isDefault: boolean;319 rivUsd: number | null;320 rivLowUsd: number | null;321 rivHighUsd: number | null;322 rivConfidence: number | null;323 rivSampleSize: number;324 latestSaleUsd: number | null;325 latestSaleAt: Date | null;326 change30d: number | null;327 change1y: number | null;328 salesCount: number;329 sales30d: number;330 activeListings: number;331 minAskUsd: number | null;332 liquidityScore: number | null;333}334335export const getAssetVariants = cache(async (assetId: string): Promise<VariantRow[]> => {336 const r = await rows<Record<string, unknown>>(sql`337 SELECT v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,338 vs.riv_usd, vs.riv_low_usd, vs.riv_high_usd, vs.riv_confidence, coalesce(vs.riv_sample_size, 0) AS riv_sample_size, vs.latest_sale_usd, vs.latest_sale_at, vs.change_30d, vs.change_1y,339 coalesce(vs.sales_count, 0) AS sales_count, coalesce(vs.sales_30d, 0) AS sales_30d, coalesce(vs.active_listings, 0) AS active_listings, vs.min_ask_usd, vs.liquidity_score340 FROM asset_variants v LEFT JOIN variant_stats vs ON vs.variant_id = v.id WHERE v.asset_id = ${assetId}341 ORDER BY vs.sales_count DESC NULLS LAST, v.is_default DESC, v.label342 `);343 return r.map((x) => ({ id: String(x.id), variantKey: String(x.variant_key), label: String(x.label), grader: str(x.grader), grade: str(x.grade), qualifier: str(x.qualifier), condition: str(x.condition), completeness: str(x.completeness), isDefault: Boolean(x.is_default), rivUsd: num(x.riv_usd), rivLowUsd: num(x.riv_low_usd), rivHighUsd: num(x.riv_high_usd), rivConfidence: num(x.riv_confidence), rivSampleSize: int(x.riv_sample_size), latestSaleUsd: num(x.latest_sale_usd), latestSaleAt: date(x.latest_sale_at), change30d: num(x.change_30d), change1y: num(x.change_1y), salesCount: int(x.sales_count), sales30d: int(x.sales_30d), activeListings: int(x.active_listings), minAskUsd: num(x.min_ask_usd), liquidityScore: num(x.liquidity_score) }));344});345346export interface SaleRow {347 id: string;348 saleDate: Date;349 price: number;350 currency: string;351 priceUsd: number;352 saleType: string;353 grader: string | null;354 grade: string | null;355 condition: string | null;356 certificationNumber: string | null;357 sourceId: string;358 sourceName: string;359 sourceUrl: string;360 auctionHouse: string | null;361 location: string | null;362 rawTitle: string;363 imageUrls: string[];364 confidence: number;365 status: string;366 flags: string[];367 variantId: string | null;368 buyerPremiumIncluded: boolean | null;369 /** buyer-pays USD (price + estimated buyer premium when hammer-only), fee basis and rate (§35); null until the fees worker has run */370 allInUsd: number | null;371 feeBasis: string | null;372 buyerPremiumRate: number | null;373 /** source metadata used by the heuristic verification label (§39) */374 sourceType: string | null;375 sourceTrust: number | null;376 verification: SaleVerification;377 verificationReason: string;378 assetSlug?: string;379 assetTitle?: string;380 categorySlug?: string;381 heroImageUrl?: string | null;382}383384export function toSaleRow(x: Record<string, unknown>): SaleRow {385 return {386 id: String(x.id),387 saleDate: date(x.sale_date)!,388 price: Number(x.price),389 currency: String(x.currency),390 priceUsd: Number(x.price_usd),391 saleType: String(x.sale_type),392 grader: str(x.grader),393 grade: str(x.grade),394 condition: str(x.condition),395 certificationNumber: str(x.certification_number),396 sourceId: String(x.source_id),397 sourceName: String(x.source_name ?? x.source_id),398 sourceUrl: String(x.source_url),399 auctionHouse: str(x.auction_house),400 location: str(x.location),401 rawTitle: String(x.raw_title),402 imageUrls: (x.image_urls as string[]) ?? [],403 confidence: Number(x.confidence ?? 0),404 status: String(x.status),405 flags: (x.flags as string[]) ?? [],406 variantId: str(x.variant_id),407 buyerPremiumIncluded: x.buyer_premium_included === null || x.buyer_premium_included === undefined ? null : Boolean(x.buyer_premium_included),408 allInUsd: num(x.all_in_usd),409 feeBasis: str(x.fee_basis),410 buyerPremiumRate: num(x.buyer_premium_rate),411 sourceType: str(x.source_type),412 sourceTrust: num(x.trust_score),413 ...(() => {414 const v = classifySale({ status: str(x.status), confidence: num(x.confidence), flags: (x.flags as string[]) ?? [], sourceType: str(x.source_type), saleType: str(x.sale_type), trust: num(x.trust_score) });415 return { verification: v.label, verificationReason: v.reason };416 })(),417 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,418 assetTitle: x.asset_title ? String(x.asset_title) : undefined,419 categorySlug: x.category_slug ? String(x.category_slug) : undefined,420 heroImageUrl: x.hero_image_url === undefined ? undefined : str(x.hero_image_url),421 };422}423424export const SALE_SELECT = sql`s.id, s.sale_date, s.price, s.currency, s.price_usd, s.sale_type, s.grader, s.grade, s.condition, s.certification_number, s.source_id, src.name AS source_name, s.source_url, s.auction_house, s.location, s.raw_title, s.image_urls, s.confidence, s.status, s.flags, s.variant_id, s.buyer_premium_included, s.all_in_usd, s.fee_basis, s.buyer_premium_rate, src.source_type, src.trust_score`;425426export async function getAssetSales(assetId: string, opts: { variantId?: string | null; limit?: number; offset?: number; includeFlagged?: boolean } = {}): Promise<{ items: SaleRow[]; total: number }> {427 const limit = opts.limit ?? 50;428 const r = await rows<Record<string, unknown>>(sql`429 SELECT ${SALE_SELECT}, count(*) OVER() AS total FROM sales s JOIN sources src ON src.id = s.source_id430 WHERE s.asset_id = ${assetId} ${opts.variantId ? sql`AND s.variant_id = ${opts.variantId}` : sql``} ${opts.includeFlagged ? sql`AND s.status <> 'excluded'` : sql`AND s.status = 'valid'`}431 ORDER BY s.sale_date DESC LIMIT ${limit} OFFSET ${opts.offset ?? 0}432 `);433 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0 };434}435436/** All valid sales (date, usd, variant) for the scatter chart, capped. */437export const getAssetSalePoints = cache(async (assetId: string, limit = 2000): Promise<Array<{ date: string; usd: number; variantId: string | null; grader: string | null; grade: string | null; sourceName: string }>> => {438 const r = await rows<Record<string, unknown>>(sql`439 SELECT s.sale_date, s.price_usd, s.variant_id, s.grader, s.grade, src.name AS source_name FROM sales s JOIN sources src ON src.id = s.source_id440 WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT ${limit}441 `);442 return r.map((x) => ({ date: date(x.sale_date)!.toISOString().slice(0, 10), usd: Number(x.price_usd), variantId: str(x.variant_id), grader: str(x.grader), grade: str(x.grade), sourceName: String(x.source_name) })).reverse();443});444445export interface ListingRow {446 id: string;447 price: number | null;448 currency: string | null;449 priceUsd: number | null;450 listingType: string;451 seller: string | null;452 location: string | null;453 shippingCost: number | null;454 condition: string | null;455 grader: string | null;456 grade: string | null;457 sourceId: string;458 sourceName: string;459 sourceUrl: string;460 rawTitle: string;461 imageUrls: string[];462 listedAt: Date | null;463 endsAt: Date | null;464 availability: string;465 bidCount: number | null;466 firstSeenAt: Date;467 lastSeenAt: Date;468 discountToRiv: number | null;469 confidence: number;470 flags: string[];471 assetSlug?: string;472 assetTitle?: string;473 categorySlug?: string;474 assetRivUsd?: number | null;475 assetRivConfidence?: number | null;476}477478export const LISTING_SELECT = sql`l.id, l.price, l.currency, l.price_usd, l.listing_type, l.seller, l.location, l.shipping_cost, l.condition, l.grader, l.grade, l.source_id, src.name AS source_name, l.source_url, l.raw_title, l.image_urls, l.listed_at, l.ends_at, l.availability, l.bid_count, l.first_seen_at, l.last_seen_at, l.discount_to_riv, l.confidence, l.flags`;479480export function toListingRow(x: Record<string, unknown>): ListingRow {481 return {482 id: String(x.id),483 price: num(x.price),484 currency: str(x.currency),485 priceUsd: num(x.price_usd),486 listingType: String(x.listing_type),487 seller: str(x.seller),488 location: str(x.location),489 shippingCost: num(x.shipping_cost),490 condition: str(x.condition),491 grader: str(x.grader),492 grade: str(x.grade),493 sourceId: String(x.source_id),494 sourceName: String(x.source_name ?? x.source_id),495 sourceUrl: String(x.source_url),496 rawTitle: String(x.raw_title),497 imageUrls: (x.image_urls as string[]) ?? [],498 listedAt: date(x.listed_at),499 endsAt: date(x.ends_at),500 availability: String(x.availability),501 bidCount: num(x.bid_count),502 firstSeenAt: date(x.first_seen_at)!,503 lastSeenAt: date(x.last_seen_at)!,504 discountToRiv: num(x.discount_to_riv),505 confidence: Number(x.confidence ?? 0),506 flags: (x.flags as string[]) ?? [],507 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,508 assetTitle: x.asset_title ? String(x.asset_title) : undefined,509 categorySlug: x.category_slug ? String(x.category_slug) : undefined,510 assetRivUsd: x.asset_riv_usd === undefined ? undefined : num(x.asset_riv_usd),511 assetRivConfidence: x.asset_riv_confidence === undefined ? undefined : num(x.asset_riv_confidence),512 };513}514515export async function getAssetListings(assetId: string, opts: { variantId?: string | null; availability?: 'available' | 'all'; limit?: number } = {}): Promise<ListingRow[]> {516 const r = await rows<Record<string, unknown>>(sql`517 SELECT ${LISTING_SELECT} FROM listings l JOIN sources src ON src.id = l.source_id518 WHERE l.asset_id = ${assetId} ${opts.variantId ? sql`AND l.variant_id = ${opts.variantId}` : sql``} ${opts.availability === 'all' ? sql`` : sql`AND l.availability = 'available'`}519 ORDER BY l.price_usd ASC NULLS LAST LIMIT ${opts.limit ?? 50}520 `);521 return r.map(toListingRow);522}523524export interface ObservationRow {525 id: string;526 priceKind: string;527 price: number;528 currency: string;529 priceUsd: number;530 observationDate: string;531 sampleSize: number | null;532 sourceId: string;533 sourceName: string;534 sourceUrl: string;535 variantId: string | null;536}537538export const getAssetObservations = cache(async (assetId: string, limit = 200): Promise<ObservationRow[]> => {539 const r = await rows<Record<string, unknown>>(sql`540 SELECT o.id, o.price_kind, o.price, o.currency, o.price_usd, o.observation_date, o.sample_size, o.source_id, src.name AS source_name, o.source_url, o.variant_id541 FROM price_observations o JOIN sources src ON src.id = o.source_id WHERE o.asset_id = ${assetId}542 ORDER BY o.observation_date DESC, o.source_id, o.price_kind LIMIT ${limit}543 `);544 return r.map((x) => ({ id: String(x.id), priceKind: String(x.price_kind), price: Number(x.price), currency: String(x.currency), priceUsd: Number(x.price_usd), observationDate: String(x.observation_date), sampleSize: num(x.sample_size), sourceId: String(x.source_id), sourceName: String(x.source_name), sourceUrl: String(x.source_url), variantId: str(x.variant_id) }));545});546547export interface SnapshotPoint {548 date: string;549 rivUsd: number | null;550 latestSaleUsd: number | null;551 medianUsd: number | null;552 salesCount: number;553 volumeUsd: number | null;554 listingsCount: number;555 minAskUsd: number | null;556 observationUsd: number | null;557}558559export const getAssetSnapshots = cache(async (assetId: string, variantId = '', days: number | null = null): Promise<SnapshotPoint[]> => {560 const r = await rows<Record<string, unknown>>(sql`561 SELECT * FROM price_snapshots WHERE asset_id = ${assetId} AND variant_id = ${variantId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date562 `);563 return r.map((x) => ({ date: String(x.date), rivUsd: num(x.riv_usd), latestSaleUsd: num(x.latest_sale_usd), medianUsd: num(x.median_usd), salesCount: int(x.sales_count), volumeUsd: num(x.volume_usd), listingsCount: int(x.listings_count), minAskUsd: num(x.min_ask_usd), observationUsd: num(x.observation_usd) }));564});565566export interface ValuationRow {567 id: string;568 variantId: string | null;569 computedAt: Date;570 rivUsd: number | null;571 lowUsd: number | null;572 highUsd: number | null;573 confidence: number;574 confidenceLabel: string;575 sampleSize: number;576 windowDays: number;577 methods: Record<string, number | null>;578 observationsUsed: number;579 method: string;580 notes: string[];581}582583export const getLatestValuation = cache(async (assetId: string, variantId: string | null = null): Promise<ValuationRow | null> => {584 const x = await one<Record<string, unknown>>(sql`585 SELECT * FROM valuations WHERE asset_id = ${assetId} ${variantId ? sql`AND variant_id = ${variantId}` : sql`AND variant_id IS NULL`} ORDER BY computed_at DESC LIMIT 1586 `);587 if (!x) return null;588 return { id: String(x.id), variantId: str(x.variant_id), computedAt: date(x.computed_at)!, rivUsd: num(x.riv_usd), lowUsd: num(x.low_usd), highUsd: num(x.high_usd), confidence: Number(x.confidence ?? 0), confidenceLabel: String(x.confidence_label), sampleSize: int(x.sample_size), windowDays: int(x.window_days), methods: (x.methods as Record<string, number | null>) ?? {}, observationsUsed: int(x.observations_used), method: String(x.method), notes: (x.notes as string[]) ?? [] };589});590591export interface SourceBreakdown {592 sourceId: string;593 name: string;594 sourceType: string;595 url: string | null;596 trustScore: number;597 sales: number;598 listings: number;599 observations: number;600 lastSeen: Date | null;601}602603export const getAssetSources = cache(async (assetId: string): Promise<SourceBreakdown[]> => {604 const r = await rows<Record<string, unknown>>(sql`605 WITH u AS (606 SELECT source_id, 'sale' AS k, created_at AS seen FROM sales WHERE asset_id = ${assetId}607 UNION ALL SELECT source_id, 'listing', last_seen_at FROM listings WHERE asset_id = ${assetId}608 UNION ALL SELECT source_id, 'observation', created_at FROM price_observations WHERE asset_id = ${assetId}609 )610 SELECT src.id AS source_id, src.name, src.source_type, src.url, src.trust_score,611 count(*) FILTER (WHERE k = 'sale') AS sales, count(*) FILTER (WHERE k = 'listing') AS listings, count(*) FILTER (WHERE k = 'observation') AS observations, max(seen) AS last_seen612 FROM u JOIN sources src ON src.id = u.source_id GROUP BY src.id, src.name, src.source_type, src.url, src.trust_score ORDER BY sales DESC, observations DESC613 `);614 return r.map((x) => ({ sourceId: String(x.source_id), name: String(x.name), sourceType: String(x.source_type), url: str(x.url), trustScore: Number(x.trust_score ?? 0), sales: int(x.sales), listings: int(x.listings), observations: int(x.observations), lastSeen: date(x.last_seen) }));615});616617export const getGradeDistribution = cache(async (assetId: string): Promise<Array<{ grader: string; grade: string; sales: number; medianUsd: number | null; lastSaleUsd: number | null; lastSaleAt: Date | null; minUsd: number | null; maxUsd: number | null }>> => {618 const r = await rows<Record<string, unknown>>(sql`619 SELECT coalesce(grader, 'raw') AS grader, coalesce(grade, '') AS grade, count(*) AS sales,620 percentile_cont(0.5) WITHIN GROUP (ORDER BY price_usd) AS median_usd, min(price_usd) AS min_usd, max(price_usd) AS max_usd,621 (array_agg(price_usd ORDER BY sale_date DESC))[1] AS last_usd, max(sale_date) AS last_at622 FROM sales WHERE asset_id = ${assetId} AND status = 'valid' GROUP BY 1, 2 ORDER BY grader, grade DESC623 `);624 return r.map((x) => ({ grader: String(x.grader), grade: String(x.grade), sales: int(x.sales), medianUsd: num(x.median_usd), lastSaleUsd: num(x.last_usd), lastSaleAt: date(x.last_at), minUsd: num(x.min_usd), maxUsd: num(x.max_usd) }));625});626627export const getMarketplaceDistribution = cache(async (assetId: string): Promise<Array<{ sourceName: string; sales: number; volumeUsd: number }>> => {628 const r = await rows<Record<string, unknown>>(sql`629 SELECT src.name AS source_name, count(*) AS sales, sum(s.price_usd) AS volume FROM sales s JOIN sources src ON src.id = s.source_id WHERE s.asset_id = ${assetId} AND s.status = 'valid' GROUP BY src.name ORDER BY sales DESC630 `);631 return r.map((x) => ({ sourceName: String(x.source_name), sales: int(x.sales), volumeUsd: Number(x.volume ?? 0) }));632});633634export const getPopulation = cache(async (assetId: string): Promise<Array<{ grader: string; reportDate: string; total: number; byGrade: Record<string, number>; sourceUrl: string | null }>> => {635 const r = await rows<Record<string, unknown>>(sql`SELECT grader, report_date, total, by_grade, source_url FROM population_reports WHERE asset_id = ${assetId} ORDER BY grader, report_date`);636 return r.map((x) => ({ grader: String(x.grader), reportDate: String(x.report_date), total: int(x.total), byGrade: (x.by_grade as Record<string, number>) ?? {}, sourceUrl: str(x.source_url) }));637});638639export const getAssetImages = cache(async (assetId: string, limit = 24): Promise<Array<{ id: string; url: string; role: string; attribution: string | null; sourceId: string | null }>> => {640 const r = await rows<Record<string, unknown>>(sql`SELECT id, url, role, attribution, source_id FROM images WHERE asset_id = ${assetId} ORDER BY (role = 'hero') DESC, created_at LIMIT ${limit}`);641 return r.map((x) => ({ id: String(x.id), url: String(x.url), role: String(x.role), attribution: str(x.attribution), sourceId: str(x.source_id) }));642});643644/** Comparable assets: same set (or category) with closest RIV; similarity = crude score on shared attributes (§134). */645export const getComparables = cache(async (asset: { id: string; categorySlug: string; setSlug: string | null; rivUsd: number | null; brand: string | null; year: number | null }, limit = 8): Promise<Array<AssetCard & { similarity: number }>> => {646 const r = await rows<Record<string, unknown>>(sql`647 SELECT ${ASSET_CARD_SELECT},648 ((CASE WHEN a.set_slug IS NOT NULL AND a.set_slug = ${asset.setSlug ?? ''} THEN 0.4 ELSE 0 END)649 + (CASE WHEN a.category_slug = ${asset.categorySlug} THEN 0.25 ELSE 0.1 END)650 + (CASE WHEN a.brand IS NOT NULL AND lower(a.brand) = lower(${asset.brand ?? ''}) THEN 0.1 ELSE 0 END)651 + (CASE WHEN a.year = ${asset.year ?? -1} THEN 0.1 ELSE 0 END)652 + (CASE WHEN s.riv_usd IS NOT NULL AND ${asset.rivUsd ?? 0} > 0 THEN greatest(0, 0.15 - abs(ln(s.riv_usd / ${asset.rivUsd ?? 1})) * 0.1) ELSE 0 END)) AS similarity653 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id654 WHERE a.id <> ${asset.id} AND (a.category_slug = ${asset.categorySlug} OR a.set_slug = ${asset.setSlug ?? ''})655 ORDER BY similarity DESC, s.sales_count DESC NULLS LAST LIMIT ${limit}656 `);657 return r.map((x) => ({ ...toAssetCard(x), similarity: Number(x.similarity ?? 0) }));658});659660export const getAssetSlugsForSitemap = cache(async (offset: number, limit: number): Promise<Array<{ slug: string; updatedAt: Date }>> => {661 const r = await rows<Record<string, unknown>>(sql`SELECT slug, updated_at FROM assets ORDER BY id LIMIT ${limit} OFFSET ${offset}`);662 return r.map((x) => ({ slug: String(x.slug), updatedAt: date(x.updated_at) ?? new Date() }));663});664665/** Live counts straight from canonical tables — correct even before the valuation worker fills asset_stats. */666export const getAssetLiveCounts = cache(async (assetId: string): Promise<{ sales: number; sales30d: number; listings: number; sources: number; latestSaleUsd: number | null; latestSaleAt: Date | null; minAskUsd: number | null }> => {667 const x = await one<Record<string, unknown>>(sql`668 SELECT669 (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid') AS sales,670 (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' AND s.sale_date >= now() - interval '30 days') AS sales_30d,671 (SELECT count(*) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS listings,672 (SELECT count(DISTINCT source_id) FROM (SELECT source_id FROM sales WHERE asset_id = ${assetId} UNION SELECT source_id FROM listings WHERE asset_id = ${assetId} UNION SELECT source_id FROM price_observations WHERE asset_id = ${assetId}) u) AS sources,673 (SELECT price_usd FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT 1) AS latest_sale_usd,674 (SELECT sale_date FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT 1) AS latest_sale_at,675 (SELECT min(price_usd) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS min_ask_usd676 `);677 return { sales: int(x?.sales), sales30d: int(x?.sales_30d), listings: int(x?.listings), sources: int(x?.sources), latestSaleUsd: num(x?.latest_sale_usd), latestSaleAt: date(x?.latest_sale_at), minAskUsd: num(x?.min_ask_usd) };678});679680export const countAssets = cache(async (): Promise<number> => int((await one<Record<string, unknown>>(sql`SELECT count(*) AS n FROM assets`))?.n));681682export const getAssetsBySlugs = cache(async (slugs: string[]): Promise<AssetCard[]> => {683 if (!slugs.length) return [];684 const r = await rows<Record<string, unknown>>(sql`SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug IN ${slugs}`);685 const map = new Map(r.map((x) => [String(x.slug), toAssetCard(x)]));686 return slugs.map((s) => map.get(s)).filter((x): x is AssetCard => Boolean(x));687});688689export const getPriceDistribution = cache(async (assetId: string, variantId: string | null = null, days = 365): Promise<{ n: number; min: number; p25: number; median: number; p75: number; max: number; trimmedMean: number } | null> => {690 const x = await one<Record<string, unknown>>(sql`691 WITH v AS (SELECT price_usd FROM sales WHERE asset_id = ${assetId} AND status = 'valid' AND sale_date >= now() - (${days}::int || ' days')::interval ${variantId ? sql`AND variant_id = ${variantId}` : sql``}),692 b AS (SELECT percentile_cont(0.1) WITHIN GROUP (ORDER BY price_usd) AS lo, percentile_cont(0.9) WITHIN GROUP (ORDER BY price_usd) AS hi FROM v)693 SELECT count(*) AS n, min(price_usd) AS min, percentile_cont(0.25) WITHIN GROUP (ORDER BY price_usd) AS p25, percentile_cont(0.5) WITHIN GROUP (ORDER BY price_usd) AS median, percentile_cont(0.75) WITHIN GROUP (ORDER BY price_usd) AS p75, max(price_usd) AS max,694 (SELECT avg(price_usd) FROM v, b WHERE price_usd BETWEEN b.lo AND b.hi) AS trimmed695 FROM v696 `);697 if (!x || int(x.n) === 0) return null;698 return { n: int(x.n), min: Number(x.min), p25: Number(x.p25), median: Number(x.median), p75: Number(x.p75), max: Number(x.max), trimmedMean: Number(x.trimmed ?? x.median) };699});700701/** Latest price-guide observation per asset (batch). Attached to cards so catalog-only assets still show a labelled guide price. */702export async function attachGuidePrices<T extends AssetCard>(cards: T[]): Promise<T[]> {703 const ids = cards.filter((c) => c.rivUsd === null).map((c) => c.id);704 if (!ids.length) return cards;705 const r = await rows<Record<string, unknown>>(sql`706 SELECT DISTINCT ON (o.asset_id) o.asset_id, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name707 FROM price_observations o JOIN sources src ON src.id = o.source_id708 WHERE o.asset_id IN ${ids}709 ORDER BY o.asset_id, o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC710 `);711 const map = new Map(r.map((x) => [String(x.asset_id), x]));712 for (const c of cards) {713 const g = map.get(c.id);714 if (g) {715 c.guideUsd = num(g.price_usd);716 c.guideKind = str(g.price_kind);717 c.guideDate = str(g.observation_date);718 c.guideSource = str(g.source_name);719 }720 }721 return cards;722}723724export interface GuidePrice {725 priceUsd: number;726 price: number;727 currency: string;728 priceKind: string;729 observationDate: string;730 sourceName: string;731 sourceUrl: string;732 variantId: string | null;733 variantLabel: string | null;734}735736/** Best current guide price for the header: newest observation, preferring 'market' in USD. */737export const getLatestGuidePrice = cache(async (assetId: string, variantId: string | null = null): Promise<GuidePrice | null> => {738 const x = await one<Record<string, unknown>>(sql`739 SELECT o.price_usd, o.price, o.currency, o.price_kind, o.observation_date, src.name AS source_name, o.source_url, o.variant_id, v.label AS variant_label740 FROM price_observations o JOIN sources src ON src.id = o.source_id LEFT JOIN asset_variants v ON v.id = o.variant_id741 WHERE o.asset_id = ${assetId} ${variantId ? sql`AND o.variant_id = ${variantId}` : sql``}742 ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC LIMIT 1743 `);744 if (!x) return null;745 return { priceUsd: Number(x.price_usd), price: Number(x.price), currency: String(x.currency), priceKind: String(x.price_kind), observationDate: String(x.observation_date), sourceName: String(x.source_name), sourceUrl: String(x.source_url), variantId: str(x.variant_id), variantLabel: str(x.variant_label) };746});747748/** Other assets in the same set, ordered by collector number (never a dead end on catalog-only pages). */749export const getSetSiblings = cache(async (asset: { id: string; setSlug: string | null }, limit = 12): Promise<AssetCard[]> => {750 if (!asset.setSlug) return [];751 const r = await rows<Record<string, unknown>>(sql`752 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id753 WHERE a.set_slug = ${asset.setSlug} AND a.id <> ${asset.id}754 ORDER BY (s.riv_usd IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, (substring(a.number from '^[0-9]+'))::int ASC NULLS LAST, a.number ASC NULLS LAST LIMIT ${limit}755 `);756 return r.map(toAssetCard);757});758759/** Similar assets by title trigram within the category (works for any catalog record). */760export const getSimilarAssets = cache(async (asset: { id: string; name: string; categorySlug: string }, limit = 8): Promise<AssetCard[]> => {761 const r = await rows<Record<string, unknown>>(sql`762 SELECT ${ASSET_CARD_SELECT}, similarity(a.title, ${asset.name}) AS sim FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id763 WHERE a.id <> ${asset.id} AND a.category_slug = ${asset.categorySlug} AND a.title % ${asset.name}764 ORDER BY sim DESC, coalesce(s.sales_count, 0) DESC LIMIT ${limit}765 `);766 return r.map(toAssetCard);767});768769/** Sets/releases derived from the assets table itself (correct even when the `sets` table is sparse). */770export interface SetGroup {771 slug: string;772 name: string;773 code: string | null;774 year: number | null;775 assets: number;776 priced: number;777 sales: number;778 withImage: number;779 thumb: string | null;780}781export const getSetGroups = cache(async (scope: string[], opts: { limit?: number; offset?: number; sort?: 'assets' | 'sales' | 'year' | 'name' } = {}): Promise<{ items: SetGroup[]; total: number }> => {782 const order = opts.sort === 'sales' ? sql`sales DESC, assets DESC` : opts.sort === 'year' ? sql`year DESC NULLS LAST, assets DESC` : opts.sort === 'name' ? sql`name ASC` : sql`assets DESC, sales DESC`;783 const r = await rows<Record<string, unknown>>(sql`784 SELECT a.set_slug AS slug, coalesce(st.name, min(a.set_name)) AS name, coalesce(st.code, min(a.set_code)) AS code, coalesce(st.release_year, min(a.year)) AS year,785 count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, count(a.hero_image_url) AS with_image,786 (array_agg(a.hero_image_url ORDER BY (substring(a.number from '^[0-9]+'))::int NULLS LAST) FILTER (WHERE a.hero_image_url IS NOT NULL))[1] AS thumb,787 count(*) OVER() AS total788 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id LEFT JOIN sets st ON st.slug = a.set_slug789 WHERE a.category_slug IN ${scope} AND a.set_slug IS NOT NULL790 GROUP BY a.set_slug, st.name, st.code, st.release_year791 ORDER BY ${order} LIMIT ${opts.limit ?? 60} OFFSET ${opts.offset ?? 0}792 `);793 return { items: r.map((x) => ({ slug: String(x.slug), name: String(x.name ?? x.slug), code: str(x.code), year: num(x.year), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), withImage: int(x.with_image), thumb: str(x.thumb) })), total: r.length ? int(r[0]!.total) : 0 };794});795796/** Set facts derived from its assets when the `sets` table has no row (fallback for /set/[slug]). */797export const getSetFromAssets = cache(async (slug: string) => {798 const x = await one<Record<string, unknown>>(sql`799 SELECT min(a.set_name) AS name, min(a.set_code) AS code, min(a.category_slug) AS category_slug, min(a.year) AS year, count(*) AS assets, min(a.language) AS language800 FROM assets a WHERE a.set_slug = ${slug}801 `);802 if (!x || int(x.assets) === 0) return null;803 return { slug, name: String(x.name ?? slug), code: str(x.code), categorySlug: String(x.category_slug), franchiseSlug: null, brandSlug: null, releaseYear: num(x.year), releaseDate: null, language: str(x.language), totalItems: null, identifiers: {} as Record<string, string>, metadata: {} as Record<string, unknown> };804});805806/** Representative thumbnail per category (one cheap index lookup per slug). */807export const getCategoryThumbnails = cache(async (slugs: string[]): Promise<Map<string, string>> => {808 if (!slugs.length) return new Map();809 const r = await rows<Record<string, unknown>>(sql`810 SELECT c.slug, (811 SELECT a.hero_image_url FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id812 WHERE (a.category_slug = c.slug OR a.family_slug = c.slug) AND a.hero_image_url IS NOT NULL813 ORDER BY s.sales_count DESC NULLS LAST, s.riv_usd DESC NULLS LAST, a.created_at DESC LIMIT 1814 ) AS url815 FROM unnest(${textArray(slugs)}) AS c(slug)816 `);817 return new Map(r.filter((x) => x.url).map((x) => [String(x.slug), String(x.url)]));818});819820/** Latest guide observations across the site (fallback for the home "latest transactions" block until sales exist). */821export const getLatestObservationsFeed = cache(async (limit = 12): Promise<Array<{ id: string; assetSlug: string; assetTitle: string; categorySlug: string; heroImageUrl: string | null; priceUsd: number; priceKind: string; observationDate: string; sourceName: string }>> => {822 const r = await rows<Record<string, unknown>>(sql`823 WITH recent AS (824 SELECT a.id, a.slug, a.title, a.category_slug, a.hero_image_url FROM assets a JOIN asset_stats s ON s.asset_id = a.id825 WHERE coalesce(s.observations_count, 0) > 0 ORDER BY s.updated_at DESC LIMIT ${limit * 3}826 )827 SELECT o.id, r.slug AS asset_slug, r.title AS asset_title, r.category_slug, r.hero_image_url, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name828 FROM recent r JOIN LATERAL (SELECT * FROM price_observations o WHERE o.asset_id = r.id ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC LIMIT 1) o ON true829 JOIN sources src ON src.id = o.source_id830 ORDER BY o.observation_date DESC LIMIT ${limit}831 `);832 return r.map((x) => ({ id: String(x.id), assetSlug: String(x.asset_slug), assetTitle: String(x.asset_title), categorySlug: String(x.category_slug), heroImageUrl: str(x.hero_image_url), priceUsd: Number(x.price_usd), priceKind: String(x.price_kind), observationDate: String(x.observation_date), sourceName: String(x.source_name) }));833});834835/** Counts for a browse scope, straight from canonical tables (for "13,384 assets · 0 priced yet" lines). */836export const getScopeCounts = cache(async (f: { scope?: string[] | null; set?: string | null; brand?: string | null }): Promise<{ assets: number; priced: number; withSales: number; withObservations: number; withImages: number; listings: number }> => {837 const where: SQL[] = [];838 if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);839 if (f.set) where.push(sql`a.set_slug = ${f.set}`);840 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);841 const x = await one<Record<string, unknown>>(sql`842 SELECT count(*) AS assets, count(s.riv_usd) AS priced, count(*) FILTER (WHERE coalesce(s.sales_count, 0) > 0) AS with_sales,843 count(*) FILTER (WHERE coalesce(s.observations_count, 0) > 0) AS with_obs, count(a.hero_image_url) AS with_images, coalesce(sum(s.active_listings), 0) AS listings844 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE ${joinAnd(where)}845 `);846 return { assets: int(x?.assets), priced: int(x?.priced), withSales: int(x?.with_sales), withObservations: int(x?.with_obs), withImages: int(x?.with_images), listings: int(x?.listings) };847});848849/** Valuation history (RIV with low/high band) from the valuations table, one point per day. */850export const getValuationHistory = cache(async (assetId: string, variantId: string | null = null, days = 1830): Promise<Array<{ date: string; rivUsd: number; lowUsd: number | null; highUsd: number | null; confidence: number; sampleSize: number }>> => {851 const r = await rows<Record<string, unknown>>(sql`852 SELECT DISTINCT ON (computed_at::date) computed_at::date AS d, riv_usd, low_usd, high_usd, confidence, sample_size853 FROM valuations854 WHERE asset_id = ${assetId} AND ${variantId ? sql`variant_id = ${variantId}` : sql`variant_id IS NULL`} AND riv_usd IS NOT NULL AND computed_at >= now() - (${days}::int || ' days')::interval855 ORDER BY computed_at::date, computed_at DESC856 `);857 return r.map((x) => ({ date: String(x.d), rivUsd: Number(x.riv_usd), lowUsd: num(x.low_usd), highUsd: num(x.high_usd), confidence: Number(x.confidence ?? 0), sampleSize: int(x.sample_size) }));858});859860/** Whether the signed-in user watches this asset (null when anonymous). */861export async function isWatchedBy(userId: string | null, assetId: string): Promise<boolean> {862 if (!userId) return false;863 const x = await one<Record<string, unknown>>(sql`864 SELECT 1 AS ok FROM watchlist_items wi JOIN watchlists w ON w.id = wi.watchlist_id865 WHERE w.user_id = ${userId} AND wi.target_type = 'asset' AND wi.target_id = ${assetId} LIMIT 1866 `);867 return Boolean(x);868}869