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 } 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}4748export 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,49 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,50 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`;5152export function toAssetCard(x: Record<string, unknown>): AssetCard {53 return {54 id: String(x.id),55 slug: String(x.slug),56 title: String(x.title),57 name: String(x.name),58 categorySlug: String(x.category_slug),59 familySlug: String(x.family_slug),60 setName: str(x.set_name),61 setSlug: str(x.set_slug),62 number: str(x.number),63 year: num(x.year),64 variant: str(x.variant),65 edition: str(x.edition),66 brand: str(x.brand),67 heroImageUrl: str(x.hero_image_url),68 rivUsd: num(x.riv_usd),69 rivLowUsd: num(x.riv_low_usd),70 rivHighUsd: num(x.riv_high_usd),71 rivConfidence: num(x.riv_confidence),72 rivSampleSize: int(x.riv_sample_size),73 latestSaleUsd: num(x.latest_sale_usd),74 latestSaleAt: date(x.latest_sale_at),75 change1d: num(x.change_1d),76 change7d: num(x.change_7d),77 change30d: num(x.change_30d),78 change1y: num(x.change_1y),79 salesCount: int(x.sales_count),80 sales30d: int(x.sales_30d),81 activeListings: int(x.active_listings),82 minAskUsd: num(x.min_ask_usd),83 liquidityScore: num(x.liquidity_score),84 rarityScore: num(x.rarity_score),85 momentum30d: num(x.momentum_30d),86 trendingScore: num(x.trending_score),87 valueOpportunity: num(x.value_opportunity),88 dataQuality: num(x.data_quality),89 watchers: int(x.watchers),90 observationsCount: int(x.observations_count),91 updatedAt: date(x.stats_updated_at),92 };93}9495export type ExploreSort = 'riv' | 'change30d' | 'change7d' | 'sales' | 'liquidity' | 'rarity' | 'trending' | 'newest' | 'latest_sale' | 'opportunity';9697export interface ExploreFilters {98 category?: string | null;99 grader?: string | null;100 grade?: string | null;101 priceMin?: number | null;102 priceMax?: number | null;103 liquidityMin?: number | null;104 rarityMin?: number | null;105 momentumMin?: number | null;106 yearFrom?: number | null;107 yearTo?: number | null;108 brand?: string | null;109 set?: string | null;110 hasValuation?: boolean;111 q?: string | null;112 sort?: ExploreSort;113 page?: number;114 pageSize?: number;115}116117const SORTS: Record<ExploreSort, SQL> = {118 riv: sql`s.riv_usd DESC NULLS LAST`,119 change30d: sql`s.change_30d DESC NULLS LAST`,120 change7d: sql`s.change_7d DESC NULLS LAST`,121 sales: sql`s.sales_count DESC NULLS LAST`,122 liquidity: sql`s.liquidity_score DESC NULLS LAST`,123 rarity: sql`s.rarity_score DESC NULLS LAST`,124 trending: sql`s.trending_score DESC NULLS LAST`,125 newest: sql`a.created_at DESC`,126 latest_sale: sql`s.latest_sale_at DESC NULLS LAST`,127 opportunity: sql`s.value_opportunity DESC NULLS LAST`,128};129130export function categoryScope(slug: string): string[] {131 return [slug, ...descendants(slug)];132}133134export async function exploreAssets(f: ExploreFilters): Promise<{ items: AssetCard[]; total: number; page: number; pageSize: number }> {135 const pageSize = Math.min(Math.max(f.pageSize ?? 48, 1), 200);136 const page = Math.max(1, f.page ?? 1);137 const where: SQL[] = [];138 if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);139 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);140 if (f.set) where.push(sql`a.set_slug = ${f.set}`);141 if (f.priceMin != null) where.push(sql`s.riv_usd >= ${f.priceMin}`);142 if (f.priceMax != null) where.push(sql`s.riv_usd <= ${f.priceMax}`);143 if (f.liquidityMin != null) where.push(sql`s.liquidity_score >= ${f.liquidityMin}`);144 if (f.rarityMin != null) where.push(sql`s.rarity_score >= ${f.rarityMin}`);145 if (f.momentumMin != null) where.push(sql`s.momentum_30d >= ${f.momentumMin}`);146 if (f.yearFrom != null) where.push(sql`a.year >= ${f.yearFrom}`);147 if (f.yearTo != null) where.push(sql`a.year <= ${f.yearTo}`);148 if (f.hasValuation) where.push(sql`s.riv_usd IS NOT NULL`);149 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``})`);150 if (f.q && f.q.trim().length >= 2) {151 const q = f.q.trim();152 where.push(sql`(a.title ILIKE ${'%' + q + '%'} OR a.title % ${q})`);153 }154 const order = SORTS[f.sort ?? 'riv'];155 const r = await rows<Record<string, unknown>>(sql`156 SELECT ${ASSET_CARD_SELECT}, count(*) OVER() AS total157 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id158 WHERE ${joinAnd(where)}159 ORDER BY ${order}, a.title ASC160 LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}161 `);162 return { items: r.map(toAssetCard), total: r.length ? int(r[0]!.total) : 0, page, pageSize };163}164165/** Ranked lists for home/markets widgets. `scope` restricts to category slugs. */166export async function rankedAssets(kind: 'trending' | 'gainers' | 'losers' | 'volume' | 'watched' | 'newest' | 'expensive' | 'opportunity' | 'liquid', opts: { scope?: string[]; limit?: number; window?: '1d' | '7d' | '30d' } = {}): Promise<AssetCard[]> {167 const limit = opts.limit ?? 10;168 const scope = opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``;169 const chg = opts.window === '1d' ? sql`s.change_1d` : opts.window === '30d' ? sql`s.change_30d` : sql`s.change_7d`;170 const spec: Record<typeof kind, { where: SQL; order: SQL }> = {171 trending: { where: sql`s.trending_score IS NOT NULL`, order: sql`s.trending_score DESC` },172 gainers: { where: sql`${chg} IS NOT NULL AND ${chg} > 0 AND s.riv_sample_size >= 3`, order: sql`${chg} DESC` },173 losers: { where: sql`${chg} IS NOT NULL AND ${chg} < 0 AND s.riv_sample_size >= 3`, order: sql`${chg} ASC` },174 volume: { where: sql`s.volume_30d_usd IS NOT NULL AND s.volume_30d_usd > 0`, order: sql`s.volume_30d_usd DESC` },175 watched: { where: sql`s.watchers > 0`, order: sql`s.watchers DESC, s.views_30d DESC` },176 newest: { where: sql`true`, order: sql`a.created_at DESC` },177 expensive: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.riv_usd DESC` },178 opportunity: { where: sql`s.value_opportunity IS NOT NULL AND s.value_opportunity > 0`, order: sql`s.value_opportunity DESC` },179 liquid: { where: sql`s.liquidity_score IS NOT NULL`, order: sql`s.liquidity_score DESC` },180 };181 const s = spec[kind];182 const r = await rows<Record<string, unknown>>(sql`183 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id184 WHERE ${s.where} ${scope} ORDER BY ${s.order} NULLS LAST LIMIT ${limit}185 `);186 return r.map(toAssetCard);187}188189export interface AssetDetail extends AssetCard {190 canonicalKey: string;191 subcategorySlug: string | null;192 franchise: string | null;193 series: string | null;194 setCode: string | null;195 model: string | null;196 reference: string | null;197 language: string | null;198 region: string | null;199 country: string | null;200 material: string | null;201 size: string | null;202 color: string | null;203 rarity: string | null;204 productionQuantity: number | null;205 originalMsrp: number | null;206 originalMsrpCurrency: string | null;207 releaseDate: string | null;208 description: string | null;209 identifiers: Record<string, string>;210 metadata: Record<string, unknown>;211 verified: boolean;212 createdAt: Date;213 athUsd: number | null;214 athAt: Date | null;215 atlUsd: number | null;216 atlAt: Date | null;217 sales1y: number;218 volume30dUsd: number | null;219 sourcesCount: number;220 momentum7d: number | null;221 momentum90d: number | null;222 momentum1y: number | null;223 change90d: number | null;224 rivVariantId: string | null;225}226227export const getAssetBySlug = cache(async (slug: string): Promise<AssetDetail | null> => {228 const x = await one<Record<string, unknown>>(sql`229 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,230 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_id231 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug = ${slug} LIMIT 1232 `);233 if (!x) return null;234 return {235 ...toAssetCard(x),236 canonicalKey: String(x.canonical_key),237 subcategorySlug: str(x.subcategory_slug),238 franchise: str(x.franchise),239 series: str(x.series),240 setCode: str(x.set_code),241 model: str(x.model),242 reference: str(x.reference),243 language: str(x.language),244 region: str(x.region),245 country: str(x.country),246 material: str(x.material),247 size: str(x.size),248 color: str(x.color),249 rarity: str(x.rarity),250 productionQuantity: num(x.production_quantity),251 originalMsrp: num(x.original_msrp),252 originalMsrpCurrency: str(x.original_msrp_currency),253 releaseDate: str(x.release_date),254 description: str(x.description),255 identifiers: (x.identifiers as Record<string, string>) ?? {},256 metadata: (x.metadata as Record<string, unknown>) ?? {},257 verified: Boolean(x.verified),258 createdAt: date(x.created_at)!,259 athUsd: num(x.ath_usd),260 athAt: date(x.ath_at),261 atlUsd: num(x.atl_usd),262 atlAt: date(x.atl_at),263 sales1y: int(x.sales_1y),264 volume30dUsd: num(x.volume_30d_usd),265 sourcesCount: int(x.sources_count),266 momentum7d: num(x.momentum_7d),267 momentum90d: num(x.momentum_90d),268 momentum1y: num(x.momentum_1y),269 change90d: num(x.change_90d),270 rivVariantId: str(x.riv_variant_id),271 };272});273274export interface VariantRow {275 id: string;276 variantKey: string;277 label: string;278 grader: string | null;279 grade: string | null;280 qualifier: string | null;281 condition: string | null;282 completeness: string | null;283 isDefault: boolean;284 rivUsd: number | null;285 rivLowUsd: number | null;286 rivHighUsd: number | null;287 rivConfidence: number | null;288 rivSampleSize: number;289 latestSaleUsd: number | null;290 latestSaleAt: Date | null;291 change30d: number | null;292 change1y: number | null;293 salesCount: number;294 sales30d: number;295 activeListings: number;296 minAskUsd: number | null;297 liquidityScore: number | null;298}299300export const getAssetVariants = cache(async (assetId: string): Promise<VariantRow[]> => {301 const r = await rows<Record<string, unknown>>(sql`302 SELECT v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,303 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,304 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_score305 FROM asset_variants v LEFT JOIN variant_stats vs ON vs.variant_id = v.id WHERE v.asset_id = ${assetId}306 ORDER BY vs.sales_count DESC NULLS LAST, v.is_default DESC, v.label307 `);308 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) }));309});310311export interface SaleRow {312 id: string;313 saleDate: Date;314 price: number;315 currency: string;316 priceUsd: number;317 saleType: string;318 grader: string | null;319 grade: string | null;320 condition: string | null;321 certificationNumber: string | null;322 sourceId: string;323 sourceName: string;324 sourceUrl: string;325 auctionHouse: string | null;326 location: string | null;327 rawTitle: string;328 imageUrls: string[];329 confidence: number;330 status: string;331 flags: string[];332 variantId: string | null;333 buyerPremiumIncluded: boolean | null;334 assetSlug?: string;335 assetTitle?: string;336 categorySlug?: string;337 heroImageUrl?: string | null;338}339340export function toSaleRow(x: Record<string, unknown>): SaleRow {341 return {342 id: String(x.id),343 saleDate: date(x.sale_date)!,344 price: Number(x.price),345 currency: String(x.currency),346 priceUsd: Number(x.price_usd),347 saleType: String(x.sale_type),348 grader: str(x.grader),349 grade: str(x.grade),350 condition: str(x.condition),351 certificationNumber: str(x.certification_number),352 sourceId: String(x.source_id),353 sourceName: String(x.source_name ?? x.source_id),354 sourceUrl: String(x.source_url),355 auctionHouse: str(x.auction_house),356 location: str(x.location),357 rawTitle: String(x.raw_title),358 imageUrls: (x.image_urls as string[]) ?? [],359 confidence: Number(x.confidence ?? 0),360 status: String(x.status),361 flags: (x.flags as string[]) ?? [],362 variantId: str(x.variant_id),363 buyerPremiumIncluded: x.buyer_premium_included === null || x.buyer_premium_included === undefined ? null : Boolean(x.buyer_premium_included),364 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,365 assetTitle: x.asset_title ? String(x.asset_title) : undefined,366 categorySlug: x.category_slug ? String(x.category_slug) : undefined,367 heroImageUrl: x.hero_image_url === undefined ? undefined : str(x.hero_image_url),368 };369}370371export 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`;372373export async function getAssetSales(assetId: string, opts: { variantId?: string | null; limit?: number; offset?: number; includeFlagged?: boolean } = {}): Promise<{ items: SaleRow[]; total: number }> {374 const limit = opts.limit ?? 50;375 const r = await rows<Record<string, unknown>>(sql`376 SELECT ${SALE_SELECT}, count(*) OVER() AS total FROM sales s JOIN sources src ON src.id = s.source_id377 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'`}378 ORDER BY s.sale_date DESC LIMIT ${limit} OFFSET ${opts.offset ?? 0}379 `);380 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0 };381}382383/** All valid sales (date, usd, variant) for the scatter chart, capped. */384export 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 }>> => {385 const r = await rows<Record<string, unknown>>(sql`386 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_id387 WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT ${limit}388 `);389 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();390});391392export interface ListingRow {393 id: string;394 price: number | null;395 currency: string | null;396 priceUsd: number | null;397 listingType: string;398 seller: string | null;399 location: string | null;400 shippingCost: number | null;401 condition: string | null;402 grader: string | null;403 grade: string | null;404 sourceId: string;405 sourceName: string;406 sourceUrl: string;407 rawTitle: string;408 imageUrls: string[];409 listedAt: Date | null;410 endsAt: Date | null;411 availability: string;412 bidCount: number | null;413 firstSeenAt: Date;414 lastSeenAt: Date;415 discountToRiv: number | null;416 confidence: number;417 flags: string[];418 assetSlug?: string;419 assetTitle?: string;420 categorySlug?: string;421 assetRivUsd?: number | null;422 assetRivConfidence?: number | null;423}424425export 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`;426427export function toListingRow(x: Record<string, unknown>): ListingRow {428 return {429 id: String(x.id),430 price: num(x.price),431 currency: str(x.currency),432 priceUsd: num(x.price_usd),433 listingType: String(x.listing_type),434 seller: str(x.seller),435 location: str(x.location),436 shippingCost: num(x.shipping_cost),437 condition: str(x.condition),438 grader: str(x.grader),439 grade: str(x.grade),440 sourceId: String(x.source_id),441 sourceName: String(x.source_name ?? x.source_id),442 sourceUrl: String(x.source_url),443 rawTitle: String(x.raw_title),444 imageUrls: (x.image_urls as string[]) ?? [],445 listedAt: date(x.listed_at),446 endsAt: date(x.ends_at),447 availability: String(x.availability),448 bidCount: num(x.bid_count),449 firstSeenAt: date(x.first_seen_at)!,450 lastSeenAt: date(x.last_seen_at)!,451 discountToRiv: num(x.discount_to_riv),452 confidence: Number(x.confidence ?? 0),453 flags: (x.flags as string[]) ?? [],454 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,455 assetTitle: x.asset_title ? String(x.asset_title) : undefined,456 categorySlug: x.category_slug ? String(x.category_slug) : undefined,457 assetRivUsd: x.asset_riv_usd === undefined ? undefined : num(x.asset_riv_usd),458 assetRivConfidence: x.asset_riv_confidence === undefined ? undefined : num(x.asset_riv_confidence),459 };460}461462export async function getAssetListings(assetId: string, opts: { variantId?: string | null; availability?: 'available' | 'all'; limit?: number } = {}): Promise<ListingRow[]> {463 const r = await rows<Record<string, unknown>>(sql`464 SELECT ${LISTING_SELECT} FROM listings l JOIN sources src ON src.id = l.source_id465 WHERE l.asset_id = ${assetId} ${opts.variantId ? sql`AND l.variant_id = ${opts.variantId}` : sql``} ${opts.availability === 'all' ? sql`` : sql`AND l.availability = 'available'`}466 ORDER BY l.price_usd ASC NULLS LAST LIMIT ${opts.limit ?? 50}467 `);468 return r.map(toListingRow);469}470471export interface ObservationRow {472 id: string;473 priceKind: string;474 price: number;475 currency: string;476 priceUsd: number;477 observationDate: string;478 sampleSize: number | null;479 sourceId: string;480 sourceName: string;481 sourceUrl: string;482 variantId: string | null;483}484485export const getAssetObservations = cache(async (assetId: string, limit = 200): Promise<ObservationRow[]> => {486 const r = await rows<Record<string, unknown>>(sql`487 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_id488 FROM price_observations o JOIN sources src ON src.id = o.source_id WHERE o.asset_id = ${assetId}489 ORDER BY o.observation_date DESC, o.source_id, o.price_kind LIMIT ${limit}490 `);491 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) }));492});493494export interface SnapshotPoint {495 date: string;496 rivUsd: number | null;497 latestSaleUsd: number | null;498 medianUsd: number | null;499 salesCount: number;500 volumeUsd: number | null;501 listingsCount: number;502 minAskUsd: number | null;503 observationUsd: number | null;504}505506export const getAssetSnapshots = cache(async (assetId: string, variantId = '', days: number | null = null): Promise<SnapshotPoint[]> => {507 const r = await rows<Record<string, unknown>>(sql`508 SELECT * FROM price_snapshots WHERE asset_id = ${assetId} AND variant_id = ${variantId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date509 `);510 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) }));511});512513export interface ValuationRow {514 id: string;515 variantId: string | null;516 computedAt: Date;517 rivUsd: number | null;518 lowUsd: number | null;519 highUsd: number | null;520 confidence: number;521 confidenceLabel: string;522 sampleSize: number;523 windowDays: number;524 methods: Record<string, number | null>;525 observationsUsed: number;526 method: string;527 notes: string[];528}529530export const getLatestValuation = cache(async (assetId: string, variantId: string | null = null): Promise<ValuationRow | null> => {531 const x = await one<Record<string, unknown>>(sql`532 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 1533 `);534 if (!x) return null;535 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[]) ?? [] };536});537538export interface SourceBreakdown {539 sourceId: string;540 name: string;541 sourceType: string;542 url: string | null;543 trustScore: number;544 sales: number;545 listings: number;546 observations: number;547 lastSeen: Date | null;548}549550export const getAssetSources = cache(async (assetId: string): Promise<SourceBreakdown[]> => {551 const r = await rows<Record<string, unknown>>(sql`552 WITH u AS (553 SELECT source_id, 'sale' AS k, created_at AS seen FROM sales WHERE asset_id = ${assetId}554 UNION ALL SELECT source_id, 'listing', last_seen_at FROM listings WHERE asset_id = ${assetId}555 UNION ALL SELECT source_id, 'observation', created_at FROM price_observations WHERE asset_id = ${assetId}556 )557 SELECT src.id AS source_id, src.name, src.source_type, src.url, src.trust_score,558 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_seen559 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 DESC560 `);561 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) }));562});563564export 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 }>> => {565 const r = await rows<Record<string, unknown>>(sql`566 SELECT coalesce(grader, 'raw') AS grader, coalesce(grade, '') AS grade, count(*) AS sales,567 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,568 (array_agg(price_usd ORDER BY sale_date DESC))[1] AS last_usd, max(sale_date) AS last_at569 FROM sales WHERE asset_id = ${assetId} AND status = 'valid' GROUP BY 1, 2 ORDER BY grader, grade DESC570 `);571 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) }));572});573574export const getMarketplaceDistribution = cache(async (assetId: string): Promise<Array<{ sourceName: string; sales: number; volumeUsd: number }>> => {575 const r = await rows<Record<string, unknown>>(sql`576 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 DESC577 `);578 return r.map((x) => ({ sourceName: String(x.source_name), sales: int(x.sales), volumeUsd: Number(x.volume ?? 0) }));579});580581export const getPopulation = cache(async (assetId: string): Promise<Array<{ grader: string; reportDate: string; total: number; byGrade: Record<string, number>; sourceUrl: string | null }>> => {582 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`);583 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) }));584});585586export const getAssetImages = cache(async (assetId: string, limit = 24): Promise<Array<{ id: string; url: string; role: string; attribution: string | null; sourceId: string | null }>> => {587 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}`);588 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) }));589});590591/** Comparable assets: same set (or category) with closest RIV; similarity = crude score on shared attributes (§134). */592export 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 }>> => {593 const r = await rows<Record<string, unknown>>(sql`594 SELECT ${ASSET_CARD_SELECT},595 ((CASE WHEN a.set_slug IS NOT NULL AND a.set_slug = ${asset.setSlug ?? ''} THEN 0.4 ELSE 0 END)596 + (CASE WHEN a.category_slug = ${asset.categorySlug} THEN 0.25 ELSE 0.1 END)597 + (CASE WHEN a.brand IS NOT NULL AND lower(a.brand) = lower(${asset.brand ?? ''}) THEN 0.1 ELSE 0 END)598 + (CASE WHEN a.year = ${asset.year ?? -1} THEN 0.1 ELSE 0 END)599 + (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 similarity600 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id601 WHERE a.id <> ${asset.id} AND (a.category_slug = ${asset.categorySlug} OR a.set_slug = ${asset.setSlug ?? ''})602 ORDER BY similarity DESC, s.sales_count DESC NULLS LAST LIMIT ${limit}603 `);604 return r.map((x) => ({ ...toAssetCard(x), similarity: Number(x.similarity ?? 0) }));605});606607export const getAssetSlugsForSitemap = cache(async (offset: number, limit: number): Promise<Array<{ slug: string; updatedAt: Date }>> => {608 const r = await rows<Record<string, unknown>>(sql`SELECT slug, updated_at FROM assets ORDER BY id LIMIT ${limit} OFFSET ${offset}`);609 return r.map((x) => ({ slug: String(x.slug), updatedAt: date(x.updated_at) ?? new Date() }));610});611612/** Live counts straight from canonical tables — correct even before the valuation worker fills asset_stats. */613export const getAssetLiveCounts = cache(async (assetId: string): Promise<{ sales: number; sales30d: number; listings: number; sources: number; latestSaleUsd: number | null; latestSaleAt: Date | null; minAskUsd: number | null }> => {614 const x = await one<Record<string, unknown>>(sql`615 SELECT616 (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid') AS sales,617 (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,618 (SELECT count(*) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS listings,619 (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,620 (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,621 (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,622 (SELECT min(price_usd) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS min_ask_usd623 `);624 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) };625});626627export const countAssets = cache(async (): Promise<number> => int((await one<Record<string, unknown>>(sql`SELECT count(*) AS n FROM assets`))?.n));628629export const getAssetsBySlugs = cache(async (slugs: string[]): Promise<AssetCard[]> => {630 if (!slugs.length) return [];631 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}`);632 const map = new Map(r.map((x) => [String(x.slug), toAssetCard(x)]));633 return slugs.map((s) => map.get(s)).filter((x): x is AssetCard => Boolean(x));634});635636export 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> => {637 const x = await one<Record<string, unknown>>(sql`638 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``}),639 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)640 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,641 (SELECT avg(price_usd) FROM v, b WHERE price_usd BETWEEN b.lo AND b.hi) AS trimmed642 FROM v643 `);644 if (!x || int(x.n) === 0) return null;645 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) };646});647