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';67export interface AssetCard {8 id: string;9 slug: string;10 title: string;11 name: string;12 categorySlug: string;13 familySlug: string;14 setName: string | null;15 setSlug: string | null;16 number: string | null;17 year: number | null;18 variant: string | null;19 edition: string | null;20 brand: string | null;21 heroImageUrl: string | null;22 rivUsd: number | null;23 rivLowUsd: number | null;24 rivHighUsd: number | null;25 rivConfidence: number | null;26 rivSampleSize: number;27 latestSaleUsd: number | null;28 latestSaleAt: Date | null;29 change1d: number | null;30 change7d: number | null;31 change30d: number | null;32 change1y: number | null;33 salesCount: number;34 sales30d: number;35 activeListings: number;36 minAskUsd: number | null;37 liquidityScore: number | null;38 rarityScore: number | null;39 momentum30d: number | null;40 trendingScore: number | null;41 valueOpportunity: number | null;42 dataQuality: number | null;43 watchers: number;44 observationsCount: number;45 updatedAt: Date | null;46 /** latest price-guide observation (never a transaction); attached on demand by attachGuidePrices() */47 guideUsd?: number | null;48 guideKind?: string | null;49 guideDate?: string | null;50 guideSource?: string | null;51}5253export 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,54 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,55 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`;5657export function toAssetCard(x: Record<string, unknown>): AssetCard {58 return {59 id: String(x.id),60 slug: String(x.slug),61 title: String(x.title),62 name: String(x.name),63 categorySlug: String(x.category_slug),64 familySlug: String(x.family_slug),65 setName: str(x.set_name),66 setSlug: str(x.set_slug),67 number: str(x.number),68 year: num(x.year),69 variant: str(x.variant),70 edition: str(x.edition),71 brand: str(x.brand),72 heroImageUrl: str(x.hero_image_url),73 rivUsd: num(x.riv_usd),74 rivLowUsd: num(x.riv_low_usd),75 rivHighUsd: num(x.riv_high_usd),76 rivConfidence: num(x.riv_confidence),77 rivSampleSize: int(x.riv_sample_size),78 latestSaleUsd: num(x.latest_sale_usd),79 latestSaleAt: date(x.latest_sale_at),80 change1d: num(x.change_1d),81 change7d: num(x.change_7d),82 change30d: num(x.change_30d),83 change1y: num(x.change_1y),84 salesCount: int(x.sales_count),85 sales30d: int(x.sales_30d),86 activeListings: int(x.active_listings),87 minAskUsd: num(x.min_ask_usd),88 liquidityScore: num(x.liquidity_score),89 rarityScore: num(x.rarity_score),90 momentum30d: num(x.momentum_30d),91 trendingScore: num(x.trending_score),92 valueOpportunity: num(x.value_opportunity),93 dataQuality: num(x.data_quality),94 watchers: int(x.watchers),95 observationsCount: int(x.observations_count),96 updatedAt: date(x.stats_updated_at),97 };98}99100export type ExploreSort = 'relevance' | 'riv' | 'change30d' | 'change7d' | 'sales' | 'liquidity' | 'rarity' | 'trending' | 'newest' | 'latest_sale' | 'opportunity' | 'name' | 'number';101export type HasFilter = 'sales' | 'valuation' | 'listings' | 'observations' | 'images';102103export interface ExploreFilters {104 category?: string | null;105 grader?: string | null;106 grade?: string | null;107 priceMin?: number | null;108 priceMax?: number | null;109 liquidityMin?: number | null;110 rarityMin?: number | null;111 momentumMin?: number | null;112 yearFrom?: number | null;113 yearTo?: number | null;114 brand?: string | null;115 set?: string | null;116 hasValuation?: boolean;117 has?: HasFilter | null;118 /** explicit list of category slugs (overrides `category` scope) */119 scope?: string[] | null;120 q?: string | null;121 sort?: ExploreSort;122 page?: number;123 pageSize?: number;124}125126const SORTS: Record<ExploreSort, SQL> = {127 // Data-rich records first, catalog-only records last — never hidden.128 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`,129 riv: sql`s.riv_usd DESC NULLS LAST`,130 change30d: sql`s.change_30d DESC NULLS LAST`,131 change7d: sql`s.change_7d DESC NULLS LAST`,132 sales: sql`s.sales_count DESC NULLS LAST`,133 liquidity: sql`s.liquidity_score DESC NULLS LAST`,134 rarity: sql`s.rarity_score DESC NULLS LAST`,135 trending: sql`s.trending_score DESC NULLS LAST`,136 newest: sql`a.created_at DESC`,137 latest_sale: sql`s.latest_sale_at DESC NULLS LAST`,138 opportunity: sql`s.value_opportunity DESC NULLS LAST`,139 name: sql`a.name ASC`,140 number: sql`a.set_name ASC NULLS LAST, (substring(a.number from '^[0-9]+'))::int ASC NULLS LAST, a.number ASC NULLS LAST`,141};142143const HAS: Record<HasFilter, SQL> = {144 sales: sql`coalesce(s.sales_count, 0) > 0`,145 valuation: sql`s.riv_usd IS NOT NULL`,146 listings: sql`coalesce(s.active_listings, 0) > 0`,147 observations: sql`coalesce(s.observations_count, 0) > 0 OR EXISTS (SELECT 1 FROM price_observations o WHERE o.asset_id = a.id)`,148 images: sql`a.hero_image_url IS NOT NULL`,149};150151export function categoryScope(slug: string): string[] {152 return [slug, ...descendants(slug)];153}154155export async function exploreAssets(f: ExploreFilters): Promise<{ items: AssetCard[]; total: number; page: number; pageSize: number }> {156 const pageSize = Math.min(Math.max(f.pageSize ?? 48, 1), 200);157 const page = Math.max(1, f.page ?? 1);158 const where: SQL[] = [];159 if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);160 else if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);161 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);162 if (f.set) where.push(sql`a.set_slug = ${f.set}`);163 if (f.priceMin != null) where.push(sql`s.riv_usd >= ${f.priceMin}`);164 if (f.priceMax != null) where.push(sql`s.riv_usd <= ${f.priceMax}`);165 if (f.liquidityMin != null) where.push(sql`s.liquidity_score >= ${f.liquidityMin}`);166 if (f.rarityMin != null) where.push(sql`s.rarity_score >= ${f.rarityMin}`);167 if (f.momentumMin != null) where.push(sql`s.momentum_30d >= ${f.momentumMin}`);168 if (f.yearFrom != null) where.push(sql`a.year >= ${f.yearFrom}`);169 if (f.yearTo != null) where.push(sql`a.year <= ${f.yearTo}`);170 if (f.hasValuation) where.push(sql`s.riv_usd IS NOT NULL`);171 if (f.has) where.push(sql`(${HAS[f.has]})`);172 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``})`);173 if (f.q && f.q.trim().length >= 2) {174 const q = f.q.trim();175 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})`);176 }177 const order = SORTS[f.sort ?? 'relevance'];178 const r = await rows<Record<string, unknown>>(sql`179 SELECT ${ASSET_CARD_SELECT}, count(*) OVER() AS total180 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id181 WHERE ${joinAnd(where)}182 ORDER BY ${order}, a.title ASC183 LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}184 `);185 return { items: r.map(toAssetCard), total: r.length ? int(r[0]!.total) : 0, page, pageSize };186}187188/** Ranked lists for home/markets widgets. `scope` restricts to category slugs. */189export type RankedKind = 'trending' | 'gainers' | 'losers' | 'volume' | 'watched' | 'newest' | 'expensive' | 'opportunity' | 'liquid' | 'newest_priced' | 'documented' | 'with_sales';190export async function rankedAssets(kind: RankedKind, opts: { scope?: string[]; limit?: number; window?: '1d' | '7d' | '30d' } = {}): Promise<AssetCard[]> {191 const limit = opts.limit ?? 10;192 const scope = opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``;193 const chg = opts.window === '1d' ? sql`s.change_1d` : opts.window === '30d' ? sql`s.change_30d` : sql`s.change_7d`;194 const spec: Record<typeof kind, { where: SQL; order: SQL }> = {195 trending: { where: sql`s.trending_score IS NOT NULL`, order: sql`s.trending_score DESC` },196 gainers: { where: sql`${chg} IS NOT NULL AND ${chg} > 0 AND s.riv_sample_size >= 3`, order: sql`${chg} DESC` },197 losers: { where: sql`${chg} IS NOT NULL AND ${chg} < 0 AND s.riv_sample_size >= 3`, order: sql`${chg} ASC` },198 volume: { where: sql`s.volume_30d_usd IS NOT NULL AND s.volume_30d_usd > 0`, order: sql`s.volume_30d_usd DESC` },199 watched: { where: sql`s.watchers > 0`, order: sql`s.watchers DESC, s.views_30d DESC` },200 newest: { where: sql`true`, order: sql`a.created_at DESC` },201 expensive: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.riv_usd DESC` },202 opportunity: { where: sql`s.value_opportunity IS NOT NULL AND s.value_opportunity > 0`, order: sql`s.value_opportunity DESC` },203 liquid: { where: sql`s.liquidity_score IS NOT NULL`, order: sql`s.liquidity_score DESC` },204 // Fallback rails used while valuations are still being computed (§192: honest, data-backed)205 newest_priced: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.updated_at DESC` },206 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` },207 with_sales: { where: sql`coalesce(s.sales_count, 0) > 0`, order: sql`s.latest_sale_at DESC` },208 };209 const s = spec[kind];210 const r = await rows<Record<string, unknown>>(sql`211 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id212 WHERE ${s.where} ${scope} ORDER BY ${s.order} NULLS LAST LIMIT ${limit}213 `);214 return r.map(toAssetCard);215}216217export interface AssetDetail extends AssetCard {218 canonicalKey: string;219 subcategorySlug: string | null;220 franchise: string | null;221 series: string | null;222 setCode: string | null;223 model: string | null;224 reference: string | null;225 language: string | null;226 region: string | null;227 country: string | null;228 material: string | null;229 size: string | null;230 color: string | null;231 rarity: string | null;232 productionQuantity: number | null;233 originalMsrp: number | null;234 originalMsrpCurrency: string | null;235 releaseDate: string | null;236 description: string | null;237 identifiers: Record<string, string>;238 metadata: Record<string, unknown>;239 verified: boolean;240 createdAt: Date;241 athUsd: number | null;242 athAt: Date | null;243 atlUsd: number | null;244 atlAt: Date | null;245 sales1y: number;246 volume30dUsd: number | null;247 sourcesCount: number;248 momentum7d: number | null;249 momentum90d: number | null;250 momentum1y: number | null;251 change90d: number | null;252 rivVariantId: string | null;253}254255export const getAssetBySlug = cache(async (slug: string): Promise<AssetDetail | null> => {256 const x = await one<Record<string, unknown>>(sql`257 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,258 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_id259 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug = ${slug} LIMIT 1260 `);261 if (!x) return null;262 return {263 ...toAssetCard(x),264 canonicalKey: String(x.canonical_key),265 subcategorySlug: str(x.subcategory_slug),266 franchise: str(x.franchise),267 series: str(x.series),268 setCode: str(x.set_code),269 model: str(x.model),270 reference: str(x.reference),271 language: str(x.language),272 region: str(x.region),273 country: str(x.country),274 material: str(x.material),275 size: str(x.size),276 color: str(x.color),277 rarity: str(x.rarity),278 productionQuantity: num(x.production_quantity),279 originalMsrp: num(x.original_msrp),280 originalMsrpCurrency: str(x.original_msrp_currency),281 releaseDate: str(x.release_date),282 description: str(x.description),283 identifiers: (x.identifiers as Record<string, string>) ?? {},284 metadata: (x.metadata as Record<string, unknown>) ?? {},285 verified: Boolean(x.verified),286 createdAt: date(x.created_at)!,287 athUsd: num(x.ath_usd),288 athAt: date(x.ath_at),289 atlUsd: num(x.atl_usd),290 atlAt: date(x.atl_at),291 sales1y: int(x.sales_1y),292 volume30dUsd: num(x.volume_30d_usd),293 sourcesCount: int(x.sources_count),294 momentum7d: num(x.momentum_7d),295 momentum90d: num(x.momentum_90d),296 momentum1y: num(x.momentum_1y),297 change90d: num(x.change_90d),298 rivVariantId: str(x.riv_variant_id),299 };300});301302export interface VariantRow {303 id: string;304 variantKey: string;305 label: string;306 grader: string | null;307 grade: string | null;308 qualifier: string | null;309 condition: string | null;310 completeness: string | null;311 isDefault: boolean;312 rivUsd: number | null;313 rivLowUsd: number | null;314 rivHighUsd: number | null;315 rivConfidence: number | null;316 rivSampleSize: number;317 latestSaleUsd: number | null;318 latestSaleAt: Date | null;319 change30d: number | null;320 change1y: number | null;321 salesCount: number;322 sales30d: number;323 activeListings: number;324 minAskUsd: number | null;325 liquidityScore: number | null;326}327328export const getAssetVariants = cache(async (assetId: string): Promise<VariantRow[]> => {329 const r = await rows<Record<string, unknown>>(sql`330 SELECT v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,331 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,332 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_score333 FROM asset_variants v LEFT JOIN variant_stats vs ON vs.variant_id = v.id WHERE v.asset_id = ${assetId}334 ORDER BY vs.sales_count DESC NULLS LAST, v.is_default DESC, v.label335 `);336 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) }));337});338339export interface SaleRow {340 id: string;341 saleDate: Date;342 price: number;343 currency: string;344 priceUsd: number;345 saleType: string;346 grader: string | null;347 grade: string | null;348 condition: string | null;349 certificationNumber: string | null;350 sourceId: string;351 sourceName: string;352 sourceUrl: string;353 auctionHouse: string | null;354 location: string | null;355 rawTitle: string;356 imageUrls: string[];357 confidence: number;358 status: string;359 flags: string[];360 variantId: string | null;361 buyerPremiumIncluded: boolean | null;362 assetSlug?: string;363 assetTitle?: string;364 categorySlug?: string;365 heroImageUrl?: string | null;366}367368export function toSaleRow(x: Record<string, unknown>): SaleRow {369 return {370 id: String(x.id),371 saleDate: date(x.sale_date)!,372 price: Number(x.price),373 currency: String(x.currency),374 priceUsd: Number(x.price_usd),375 saleType: String(x.sale_type),376 grader: str(x.grader),377 grade: str(x.grade),378 condition: str(x.condition),379 certificationNumber: str(x.certification_number),380 sourceId: String(x.source_id),381 sourceName: String(x.source_name ?? x.source_id),382 sourceUrl: String(x.source_url),383 auctionHouse: str(x.auction_house),384 location: str(x.location),385 rawTitle: String(x.raw_title),386 imageUrls: (x.image_urls as string[]) ?? [],387 confidence: Number(x.confidence ?? 0),388 status: String(x.status),389 flags: (x.flags as string[]) ?? [],390 variantId: str(x.variant_id),391 buyerPremiumIncluded: x.buyer_premium_included === null || x.buyer_premium_included === undefined ? null : Boolean(x.buyer_premium_included),392 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,393 assetTitle: x.asset_title ? String(x.asset_title) : undefined,394 categorySlug: x.category_slug ? String(x.category_slug) : undefined,395 heroImageUrl: x.hero_image_url === undefined ? undefined : str(x.hero_image_url),396 };397}398399export 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`;400401export async function getAssetSales(assetId: string, opts: { variantId?: string | null; limit?: number; offset?: number; includeFlagged?: boolean } = {}): Promise<{ items: SaleRow[]; total: number }> {402 const limit = opts.limit ?? 50;403 const r = await rows<Record<string, unknown>>(sql`404 SELECT ${SALE_SELECT}, count(*) OVER() AS total FROM sales s JOIN sources src ON src.id = s.source_id405 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'`}406 ORDER BY s.sale_date DESC LIMIT ${limit} OFFSET ${opts.offset ?? 0}407 `);408 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0 };409}410411/** All valid sales (date, usd, variant) for the scatter chart, capped. */412export 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 }>> => {413 const r = await rows<Record<string, unknown>>(sql`414 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_id415 WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT ${limit}416 `);417 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();418});419420export interface ListingRow {421 id: string;422 price: number | null;423 currency: string | null;424 priceUsd: number | null;425 listingType: string;426 seller: string | null;427 location: string | null;428 shippingCost: number | null;429 condition: string | null;430 grader: string | null;431 grade: string | null;432 sourceId: string;433 sourceName: string;434 sourceUrl: string;435 rawTitle: string;436 imageUrls: string[];437 listedAt: Date | null;438 endsAt: Date | null;439 availability: string;440 bidCount: number | null;441 firstSeenAt: Date;442 lastSeenAt: Date;443 discountToRiv: number | null;444 confidence: number;445 flags: string[];446 assetSlug?: string;447 assetTitle?: string;448 categorySlug?: string;449 assetRivUsd?: number | null;450 assetRivConfidence?: number | null;451}452453export 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`;454455export function toListingRow(x: Record<string, unknown>): ListingRow {456 return {457 id: String(x.id),458 price: num(x.price),459 currency: str(x.currency),460 priceUsd: num(x.price_usd),461 listingType: String(x.listing_type),462 seller: str(x.seller),463 location: str(x.location),464 shippingCost: num(x.shipping_cost),465 condition: str(x.condition),466 grader: str(x.grader),467 grade: str(x.grade),468 sourceId: String(x.source_id),469 sourceName: String(x.source_name ?? x.source_id),470 sourceUrl: String(x.source_url),471 rawTitle: String(x.raw_title),472 imageUrls: (x.image_urls as string[]) ?? [],473 listedAt: date(x.listed_at),474 endsAt: date(x.ends_at),475 availability: String(x.availability),476 bidCount: num(x.bid_count),477 firstSeenAt: date(x.first_seen_at)!,478 lastSeenAt: date(x.last_seen_at)!,479 discountToRiv: num(x.discount_to_riv),480 confidence: Number(x.confidence ?? 0),481 flags: (x.flags as string[]) ?? [],482 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,483 assetTitle: x.asset_title ? String(x.asset_title) : undefined,484 categorySlug: x.category_slug ? String(x.category_slug) : undefined,485 assetRivUsd: x.asset_riv_usd === undefined ? undefined : num(x.asset_riv_usd),486 assetRivConfidence: x.asset_riv_confidence === undefined ? undefined : num(x.asset_riv_confidence),487 };488}489490export async function getAssetListings(assetId: string, opts: { variantId?: string | null; availability?: 'available' | 'all'; limit?: number } = {}): Promise<ListingRow[]> {491 const r = await rows<Record<string, unknown>>(sql`492 SELECT ${LISTING_SELECT} FROM listings l JOIN sources src ON src.id = l.source_id493 WHERE l.asset_id = ${assetId} ${opts.variantId ? sql`AND l.variant_id = ${opts.variantId}` : sql``} ${opts.availability === 'all' ? sql`` : sql`AND l.availability = 'available'`}494 ORDER BY l.price_usd ASC NULLS LAST LIMIT ${opts.limit ?? 50}495 `);496 return r.map(toListingRow);497}498499export interface ObservationRow {500 id: string;501 priceKind: string;502 price: number;503 currency: string;504 priceUsd: number;505 observationDate: string;506 sampleSize: number | null;507 sourceId: string;508 sourceName: string;509 sourceUrl: string;510 variantId: string | null;511}512513export const getAssetObservations = cache(async (assetId: string, limit = 200): Promise<ObservationRow[]> => {514 const r = await rows<Record<string, unknown>>(sql`515 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_id516 FROM price_observations o JOIN sources src ON src.id = o.source_id WHERE o.asset_id = ${assetId}517 ORDER BY o.observation_date DESC, o.source_id, o.price_kind LIMIT ${limit}518 `);519 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) }));520});521522export interface SnapshotPoint {523 date: string;524 rivUsd: number | null;525 latestSaleUsd: number | null;526 medianUsd: number | null;527 salesCount: number;528 volumeUsd: number | null;529 listingsCount: number;530 minAskUsd: number | null;531 observationUsd: number | null;532}533534export const getAssetSnapshots = cache(async (assetId: string, variantId = '', days: number | null = null): Promise<SnapshotPoint[]> => {535 const r = await rows<Record<string, unknown>>(sql`536 SELECT * FROM price_snapshots WHERE asset_id = ${assetId} AND variant_id = ${variantId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date537 `);538 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) }));539});540541export interface ValuationRow {542 id: string;543 variantId: string | null;544 computedAt: Date;545 rivUsd: number | null;546 lowUsd: number | null;547 highUsd: number | null;548 confidence: number;549 confidenceLabel: string;550 sampleSize: number;551 windowDays: number;552 methods: Record<string, number | null>;553 observationsUsed: number;554 method: string;555 notes: string[];556}557558export const getLatestValuation = cache(async (assetId: string, variantId: string | null = null): Promise<ValuationRow | null> => {559 const x = await one<Record<string, unknown>>(sql`560 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 1561 `);562 if (!x) return null;563 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[]) ?? [] };564});565566export interface SourceBreakdown {567 sourceId: string;568 name: string;569 sourceType: string;570 url: string | null;571 trustScore: number;572 sales: number;573 listings: number;574 observations: number;575 lastSeen: Date | null;576}577578export const getAssetSources = cache(async (assetId: string): Promise<SourceBreakdown[]> => {579 const r = await rows<Record<string, unknown>>(sql`580 WITH u AS (581 SELECT source_id, 'sale' AS k, created_at AS seen FROM sales WHERE asset_id = ${assetId}582 UNION ALL SELECT source_id, 'listing', last_seen_at FROM listings WHERE asset_id = ${assetId}583 UNION ALL SELECT source_id, 'observation', created_at FROM price_observations WHERE asset_id = ${assetId}584 )585 SELECT src.id AS source_id, src.name, src.source_type, src.url, src.trust_score,586 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_seen587 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 DESC588 `);589 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) }));590});591592export 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 }>> => {593 const r = await rows<Record<string, unknown>>(sql`594 SELECT coalesce(grader, 'raw') AS grader, coalesce(grade, '') AS grade, count(*) AS sales,595 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,596 (array_agg(price_usd ORDER BY sale_date DESC))[1] AS last_usd, max(sale_date) AS last_at597 FROM sales WHERE asset_id = ${assetId} AND status = 'valid' GROUP BY 1, 2 ORDER BY grader, grade DESC598 `);599 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) }));600});601602export const getMarketplaceDistribution = cache(async (assetId: string): Promise<Array<{ sourceName: string; sales: number; volumeUsd: number }>> => {603 const r = await rows<Record<string, unknown>>(sql`604 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 DESC605 `);606 return r.map((x) => ({ sourceName: String(x.source_name), sales: int(x.sales), volumeUsd: Number(x.volume ?? 0) }));607});608609export const getPopulation = cache(async (assetId: string): Promise<Array<{ grader: string; reportDate: string; total: number; byGrade: Record<string, number>; sourceUrl: string | null }>> => {610 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`);611 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) }));612});613614export const getAssetImages = cache(async (assetId: string, limit = 24): Promise<Array<{ id: string; url: string; role: string; attribution: string | null; sourceId: string | null }>> => {615 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}`);616 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) }));617});618619/** Comparable assets: same set (or category) with closest RIV; similarity = crude score on shared attributes (§134). */620export 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 }>> => {621 const r = await rows<Record<string, unknown>>(sql`622 SELECT ${ASSET_CARD_SELECT},623 ((CASE WHEN a.set_slug IS NOT NULL AND a.set_slug = ${asset.setSlug ?? ''} THEN 0.4 ELSE 0 END)624 + (CASE WHEN a.category_slug = ${asset.categorySlug} THEN 0.25 ELSE 0.1 END)625 + (CASE WHEN a.brand IS NOT NULL AND lower(a.brand) = lower(${asset.brand ?? ''}) THEN 0.1 ELSE 0 END)626 + (CASE WHEN a.year = ${asset.year ?? -1} THEN 0.1 ELSE 0 END)627 + (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 similarity628 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id629 WHERE a.id <> ${asset.id} AND (a.category_slug = ${asset.categorySlug} OR a.set_slug = ${asset.setSlug ?? ''})630 ORDER BY similarity DESC, s.sales_count DESC NULLS LAST LIMIT ${limit}631 `);632 return r.map((x) => ({ ...toAssetCard(x), similarity: Number(x.similarity ?? 0) }));633});634635export const getAssetSlugsForSitemap = cache(async (offset: number, limit: number): Promise<Array<{ slug: string; updatedAt: Date }>> => {636 const r = await rows<Record<string, unknown>>(sql`SELECT slug, updated_at FROM assets ORDER BY id LIMIT ${limit} OFFSET ${offset}`);637 return r.map((x) => ({ slug: String(x.slug), updatedAt: date(x.updated_at) ?? new Date() }));638});639640/** Live counts straight from canonical tables — correct even before the valuation worker fills asset_stats. */641export const getAssetLiveCounts = cache(async (assetId: string): Promise<{ sales: number; sales30d: number; listings: number; sources: number; latestSaleUsd: number | null; latestSaleAt: Date | null; minAskUsd: number | null }> => {642 const x = await one<Record<string, unknown>>(sql`643 SELECT644 (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid') AS sales,645 (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,646 (SELECT count(*) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS listings,647 (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,648 (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,649 (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,650 (SELECT min(price_usd) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS min_ask_usd651 `);652 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) };653});654655export const countAssets = cache(async (): Promise<number> => int((await one<Record<string, unknown>>(sql`SELECT count(*) AS n FROM assets`))?.n));656657export const getAssetsBySlugs = cache(async (slugs: string[]): Promise<AssetCard[]> => {658 if (!slugs.length) return [];659 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}`);660 const map = new Map(r.map((x) => [String(x.slug), toAssetCard(x)]));661 return slugs.map((s) => map.get(s)).filter((x): x is AssetCard => Boolean(x));662});663664export 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> => {665 const x = await one<Record<string, unknown>>(sql`666 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``}),667 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)668 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,669 (SELECT avg(price_usd) FROM v, b WHERE price_usd BETWEEN b.lo AND b.hi) AS trimmed670 FROM v671 `);672 if (!x || int(x.n) === 0) return null;673 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) };674});675676/** Latest price-guide observation per asset (batch). Attached to cards so catalog-only assets still show a labelled guide price. */677export async function attachGuidePrices<T extends AssetCard>(cards: T[]): Promise<T[]> {678 const ids = cards.filter((c) => c.rivUsd === null).map((c) => c.id);679 if (!ids.length) return cards;680 const r = await rows<Record<string, unknown>>(sql`681 SELECT DISTINCT ON (o.asset_id) o.asset_id, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name682 FROM price_observations o JOIN sources src ON src.id = o.source_id683 WHERE o.asset_id IN ${ids}684 ORDER BY o.asset_id, o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC685 `);686 const map = new Map(r.map((x) => [String(x.asset_id), x]));687 for (const c of cards) {688 const g = map.get(c.id);689 if (g) {690 c.guideUsd = num(g.price_usd);691 c.guideKind = str(g.price_kind);692 c.guideDate = str(g.observation_date);693 c.guideSource = str(g.source_name);694 }695 }696 return cards;697}698699export interface GuidePrice {700 priceUsd: number;701 price: number;702 currency: string;703 priceKind: string;704 observationDate: string;705 sourceName: string;706 sourceUrl: string;707 variantId: string | null;708 variantLabel: string | null;709}710711/** Best current guide price for the header: newest observation, preferring 'market' in USD. */712export const getLatestGuidePrice = cache(async (assetId: string, variantId: string | null = null): Promise<GuidePrice | null> => {713 const x = await one<Record<string, unknown>>(sql`714 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_label715 FROM price_observations o JOIN sources src ON src.id = o.source_id LEFT JOIN asset_variants v ON v.id = o.variant_id716 WHERE o.asset_id = ${assetId} ${variantId ? sql`AND o.variant_id = ${variantId}` : sql``}717 ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC LIMIT 1718 `);719 if (!x) return null;720 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) };721});722723/** Other assets in the same set, ordered by collector number (never a dead end on catalog-only pages). */724export const getSetSiblings = cache(async (asset: { id: string; setSlug: string | null }, limit = 12): Promise<AssetCard[]> => {725 if (!asset.setSlug) return [];726 const r = await rows<Record<string, unknown>>(sql`727 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id728 WHERE a.set_slug = ${asset.setSlug} AND a.id <> ${asset.id}729 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}730 `);731 return r.map(toAssetCard);732});733734/** Similar assets by title trigram within the category (works for any catalog record). */735export const getSimilarAssets = cache(async (asset: { id: string; name: string; categorySlug: string }, limit = 8): Promise<AssetCard[]> => {736 const r = await rows<Record<string, unknown>>(sql`737 SELECT ${ASSET_CARD_SELECT}, similarity(a.title, ${asset.name}) AS sim FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id738 WHERE a.id <> ${asset.id} AND a.category_slug = ${asset.categorySlug} AND a.title % ${asset.name}739 ORDER BY sim DESC, coalesce(s.sales_count, 0) DESC LIMIT ${limit}740 `);741 return r.map(toAssetCard);742});743744/** Sets/releases derived from the assets table itself (correct even when the `sets` table is sparse). */745export interface SetGroup {746 slug: string;747 name: string;748 code: string | null;749 year: number | null;750 assets: number;751 priced: number;752 sales: number;753 withImage: number;754 thumb: string | null;755}756export const getSetGroups = cache(async (scope: string[], opts: { limit?: number; offset?: number; sort?: 'assets' | 'sales' | 'year' | 'name' } = {}): Promise<{ items: SetGroup[]; total: number }> => {757 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`;758 const r = await rows<Record<string, unknown>>(sql`759 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,760 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,761 (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,762 count(*) OVER() AS total763 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id LEFT JOIN sets st ON st.slug = a.set_slug764 WHERE a.category_slug IN ${scope} AND a.set_slug IS NOT NULL765 GROUP BY a.set_slug, st.name, st.code, st.release_year766 ORDER BY ${order} LIMIT ${opts.limit ?? 60} OFFSET ${opts.offset ?? 0}767 `);768 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 };769});770771/** Set facts derived from its assets when the `sets` table has no row (fallback for /set/[slug]). */772export const getSetFromAssets = cache(async (slug: string) => {773 const x = await one<Record<string, unknown>>(sql`774 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 language775 FROM assets a WHERE a.set_slug = ${slug}776 `);777 if (!x || int(x.assets) === 0) return null;778 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> };779});780781/** Representative thumbnail per category (one cheap index lookup per slug). */782export const getCategoryThumbnails = cache(async (slugs: string[]): Promise<Map<string, string>> => {783 if (!slugs.length) return new Map();784 const r = await rows<Record<string, unknown>>(sql`785 SELECT c.slug, (786 SELECT a.hero_image_url FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id787 WHERE (a.category_slug = c.slug OR a.family_slug = c.slug) AND a.hero_image_url IS NOT NULL788 ORDER BY s.sales_count DESC NULLS LAST, s.riv_usd DESC NULLS LAST, a.created_at DESC LIMIT 1789 ) AS url790 FROM unnest(${textArray(slugs)}) AS c(slug)791 `);792 return new Map(r.filter((x) => x.url).map((x) => [String(x.slug), String(x.url)]));793});794795/** Latest guide observations across the site (fallback for the home "latest transactions" block until sales exist). */796export 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 }>> => {797 const r = await rows<Record<string, unknown>>(sql`798 WITH recent AS (799 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.id800 WHERE coalesce(s.observations_count, 0) > 0 ORDER BY s.updated_at DESC LIMIT ${limit * 3}801 )802 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_name803 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 true804 JOIN sources src ON src.id = o.source_id805 ORDER BY o.observation_date DESC LIMIT ${limit}806 `);807 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) }));808});809810/** Counts for a browse scope, straight from canonical tables (for "13,384 assets · 0 priced yet" lines). */811export 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 }> => {812 const where: SQL[] = [];813 if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);814 if (f.set) where.push(sql`a.set_slug = ${f.set}`);815 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);816 const x = await one<Record<string, unknown>>(sql`817 SELECT count(*) AS assets, count(s.riv_usd) AS priced, count(*) FILTER (WHERE coalesce(s.sales_count, 0) > 0) AS with_sales,818 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 listings819 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE ${joinAnd(where)}820 `);821 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) };822});823824/** Valuation history (RIV with low/high band) from the valuations table, one point per day. */825export 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 }>> => {826 const r = await rows<Record<string, unknown>>(sql`827 SELECT DISTINCT ON (computed_at::date) computed_at::date AS d, riv_usd, low_usd, high_usd, confidence, sample_size828 FROM valuations829 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')::interval830 ORDER BY computed_at::date, computed_at DESC831 `);832 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) }));833});834835/** Whether the signed-in user watches this asset (null when anonymous). */836export async function isWatchedBy(userId: string | null, assetId: string): Promise<boolean> {837 if (!userId) return false;838 const x = await one<Record<string, unknown>>(sql`839 SELECT 1 AS ok FROM watchlist_items wi JOIN watchlists w ON w.id = wi.watchlist_id840 WHERE w.user_id = ${userId} AND wi.target_type = 'asset' AND wi.target_id = ${assetId} LIMIT 1841 `);842 return Boolean(x);843}844