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 /** source metadata used by the heuristic verification label (§39) */370 sourceType: string | null;371 sourceTrust: number | null;372 verification: SaleVerification;373 verificationReason: string;374 assetSlug?: string;375 assetTitle?: string;376 categorySlug?: string;377 heroImageUrl?: string | null;378}379380export function toSaleRow(x: Record<string, unknown>): SaleRow {381 return {382 id: String(x.id),383 saleDate: date(x.sale_date)!,384 price: Number(x.price),385 currency: String(x.currency),386 priceUsd: Number(x.price_usd),387 saleType: String(x.sale_type),388 grader: str(x.grader),389 grade: str(x.grade),390 condition: str(x.condition),391 certificationNumber: str(x.certification_number),392 sourceId: String(x.source_id),393 sourceName: String(x.source_name ?? x.source_id),394 sourceUrl: String(x.source_url),395 auctionHouse: str(x.auction_house),396 location: str(x.location),397 rawTitle: String(x.raw_title),398 imageUrls: (x.image_urls as string[]) ?? [],399 confidence: Number(x.confidence ?? 0),400 status: String(x.status),401 flags: (x.flags as string[]) ?? [],402 variantId: str(x.variant_id),403 buyerPremiumIncluded: x.buyer_premium_included === null || x.buyer_premium_included === undefined ? null : Boolean(x.buyer_premium_included),404 sourceType: str(x.source_type),405 sourceTrust: num(x.trust_score),406 ...(() => {407 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) });408 return { verification: v.label, verificationReason: v.reason };409 })(),410 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,411 assetTitle: x.asset_title ? String(x.asset_title) : undefined,412 categorySlug: x.category_slug ? String(x.category_slug) : undefined,413 heroImageUrl: x.hero_image_url === undefined ? undefined : str(x.hero_image_url),414 };415}416417export 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, src.source_type, src.trust_score`;418419export async function getAssetSales(assetId: string, opts: { variantId?: string | null; limit?: number; offset?: number; includeFlagged?: boolean } = {}): Promise<{ items: SaleRow[]; total: number }> {420 const limit = opts.limit ?? 50;421 const r = await rows<Record<string, unknown>>(sql`422 SELECT ${SALE_SELECT}, count(*) OVER() AS total FROM sales s JOIN sources src ON src.id = s.source_id423 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'`}424 ORDER BY s.sale_date DESC LIMIT ${limit} OFFSET ${opts.offset ?? 0}425 `);426 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0 };427}428429/** All valid sales (date, usd, variant) for the scatter chart, capped. */430export 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 }>> => {431 const r = await rows<Record<string, unknown>>(sql`432 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_id433 WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT ${limit}434 `);435 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();436});437438export interface ListingRow {439 id: string;440 price: number | null;441 currency: string | null;442 priceUsd: number | null;443 listingType: string;444 seller: string | null;445 location: string | null;446 shippingCost: number | null;447 condition: string | null;448 grader: string | null;449 grade: string | null;450 sourceId: string;451 sourceName: string;452 sourceUrl: string;453 rawTitle: string;454 imageUrls: string[];455 listedAt: Date | null;456 endsAt: Date | null;457 availability: string;458 bidCount: number | null;459 firstSeenAt: Date;460 lastSeenAt: Date;461 discountToRiv: number | null;462 confidence: number;463 flags: string[];464 assetSlug?: string;465 assetTitle?: string;466 categorySlug?: string;467 assetRivUsd?: number | null;468 assetRivConfidence?: number | null;469}470471export 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`;472473export function toListingRow(x: Record<string, unknown>): ListingRow {474 return {475 id: String(x.id),476 price: num(x.price),477 currency: str(x.currency),478 priceUsd: num(x.price_usd),479 listingType: String(x.listing_type),480 seller: str(x.seller),481 location: str(x.location),482 shippingCost: num(x.shipping_cost),483 condition: str(x.condition),484 grader: str(x.grader),485 grade: str(x.grade),486 sourceId: String(x.source_id),487 sourceName: String(x.source_name ?? x.source_id),488 sourceUrl: String(x.source_url),489 rawTitle: String(x.raw_title),490 imageUrls: (x.image_urls as string[]) ?? [],491 listedAt: date(x.listed_at),492 endsAt: date(x.ends_at),493 availability: String(x.availability),494 bidCount: num(x.bid_count),495 firstSeenAt: date(x.first_seen_at)!,496 lastSeenAt: date(x.last_seen_at)!,497 discountToRiv: num(x.discount_to_riv),498 confidence: Number(x.confidence ?? 0),499 flags: (x.flags as string[]) ?? [],500 assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,501 assetTitle: x.asset_title ? String(x.asset_title) : undefined,502 categorySlug: x.category_slug ? String(x.category_slug) : undefined,503 assetRivUsd: x.asset_riv_usd === undefined ? undefined : num(x.asset_riv_usd),504 assetRivConfidence: x.asset_riv_confidence === undefined ? undefined : num(x.asset_riv_confidence),505 };506}507508export async function getAssetListings(assetId: string, opts: { variantId?: string | null; availability?: 'available' | 'all'; limit?: number } = {}): Promise<ListingRow[]> {509 const r = await rows<Record<string, unknown>>(sql`510 SELECT ${LISTING_SELECT} FROM listings l JOIN sources src ON src.id = l.source_id511 WHERE l.asset_id = ${assetId} ${opts.variantId ? sql`AND l.variant_id = ${opts.variantId}` : sql``} ${opts.availability === 'all' ? sql`` : sql`AND l.availability = 'available'`}512 ORDER BY l.price_usd ASC NULLS LAST LIMIT ${opts.limit ?? 50}513 `);514 return r.map(toListingRow);515}516517export interface ObservationRow {518 id: string;519 priceKind: string;520 price: number;521 currency: string;522 priceUsd: number;523 observationDate: string;524 sampleSize: number | null;525 sourceId: string;526 sourceName: string;527 sourceUrl: string;528 variantId: string | null;529}530531export const getAssetObservations = cache(async (assetId: string, limit = 200): Promise<ObservationRow[]> => {532 const r = await rows<Record<string, unknown>>(sql`533 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_id534 FROM price_observations o JOIN sources src ON src.id = o.source_id WHERE o.asset_id = ${assetId}535 ORDER BY o.observation_date DESC, o.source_id, o.price_kind LIMIT ${limit}536 `);537 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) }));538});539540export interface SnapshotPoint {541 date: string;542 rivUsd: number | null;543 latestSaleUsd: number | null;544 medianUsd: number | null;545 salesCount: number;546 volumeUsd: number | null;547 listingsCount: number;548 minAskUsd: number | null;549 observationUsd: number | null;550}551552export const getAssetSnapshots = cache(async (assetId: string, variantId = '', days: number | null = null): Promise<SnapshotPoint[]> => {553 const r = await rows<Record<string, unknown>>(sql`554 SELECT * FROM price_snapshots WHERE asset_id = ${assetId} AND variant_id = ${variantId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date555 `);556 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) }));557});558559export interface ValuationRow {560 id: string;561 variantId: string | null;562 computedAt: Date;563 rivUsd: number | null;564 lowUsd: number | null;565 highUsd: number | null;566 confidence: number;567 confidenceLabel: string;568 sampleSize: number;569 windowDays: number;570 methods: Record<string, number | null>;571 observationsUsed: number;572 method: string;573 notes: string[];574}575576export const getLatestValuation = cache(async (assetId: string, variantId: string | null = null): Promise<ValuationRow | null> => {577 const x = await one<Record<string, unknown>>(sql`578 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 1579 `);580 if (!x) return null;581 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[]) ?? [] };582});583584export interface SourceBreakdown {585 sourceId: string;586 name: string;587 sourceType: string;588 url: string | null;589 trustScore: number;590 sales: number;591 listings: number;592 observations: number;593 lastSeen: Date | null;594}595596export const getAssetSources = cache(async (assetId: string): Promise<SourceBreakdown[]> => {597 const r = await rows<Record<string, unknown>>(sql`598 WITH u AS (599 SELECT source_id, 'sale' AS k, created_at AS seen FROM sales WHERE asset_id = ${assetId}600 UNION ALL SELECT source_id, 'listing', last_seen_at FROM listings WHERE asset_id = ${assetId}601 UNION ALL SELECT source_id, 'observation', created_at FROM price_observations WHERE asset_id = ${assetId}602 )603 SELECT src.id AS source_id, src.name, src.source_type, src.url, src.trust_score,604 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_seen605 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 DESC606 `);607 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) }));608});609610export 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 }>> => {611 const r = await rows<Record<string, unknown>>(sql`612 SELECT coalesce(grader, 'raw') AS grader, coalesce(grade, '') AS grade, count(*) AS sales,613 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,614 (array_agg(price_usd ORDER BY sale_date DESC))[1] AS last_usd, max(sale_date) AS last_at615 FROM sales WHERE asset_id = ${assetId} AND status = 'valid' GROUP BY 1, 2 ORDER BY grader, grade DESC616 `);617 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) }));618});619620export const getMarketplaceDistribution = cache(async (assetId: string): Promise<Array<{ sourceName: string; sales: number; volumeUsd: number }>> => {621 const r = await rows<Record<string, unknown>>(sql`622 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 DESC623 `);624 return r.map((x) => ({ sourceName: String(x.source_name), sales: int(x.sales), volumeUsd: Number(x.volume ?? 0) }));625});626627export const getPopulation = cache(async (assetId: string): Promise<Array<{ grader: string; reportDate: string; total: number; byGrade: Record<string, number>; sourceUrl: string | null }>> => {628 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`);629 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) }));630});631632export const getAssetImages = cache(async (assetId: string, limit = 24): Promise<Array<{ id: string; url: string; role: string; attribution: string | null; sourceId: string | null }>> => {633 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}`);634 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) }));635});636637/** Comparable assets: same set (or category) with closest RIV; similarity = crude score on shared attributes (§134). */638export 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 }>> => {639 const r = await rows<Record<string, unknown>>(sql`640 SELECT ${ASSET_CARD_SELECT},641 ((CASE WHEN a.set_slug IS NOT NULL AND a.set_slug = ${asset.setSlug ?? ''} THEN 0.4 ELSE 0 END)642 + (CASE WHEN a.category_slug = ${asset.categorySlug} THEN 0.25 ELSE 0.1 END)643 + (CASE WHEN a.brand IS NOT NULL AND lower(a.brand) = lower(${asset.brand ?? ''}) THEN 0.1 ELSE 0 END)644 + (CASE WHEN a.year = ${asset.year ?? -1} THEN 0.1 ELSE 0 END)645 + (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 similarity646 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id647 WHERE a.id <> ${asset.id} AND (a.category_slug = ${asset.categorySlug} OR a.set_slug = ${asset.setSlug ?? ''})648 ORDER BY similarity DESC, s.sales_count DESC NULLS LAST LIMIT ${limit}649 `);650 return r.map((x) => ({ ...toAssetCard(x), similarity: Number(x.similarity ?? 0) }));651});652653export const getAssetSlugsForSitemap = cache(async (offset: number, limit: number): Promise<Array<{ slug: string; updatedAt: Date }>> => {654 const r = await rows<Record<string, unknown>>(sql`SELECT slug, updated_at FROM assets ORDER BY id LIMIT ${limit} OFFSET ${offset}`);655 return r.map((x) => ({ slug: String(x.slug), updatedAt: date(x.updated_at) ?? new Date() }));656});657658/** Live counts straight from canonical tables — correct even before the valuation worker fills asset_stats. */659export const getAssetLiveCounts = cache(async (assetId: string): Promise<{ sales: number; sales30d: number; listings: number; sources: number; latestSaleUsd: number | null; latestSaleAt: Date | null; minAskUsd: number | null }> => {660 const x = await one<Record<string, unknown>>(sql`661 SELECT662 (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid') AS sales,663 (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,664 (SELECT count(*) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS listings,665 (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,666 (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,667 (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,668 (SELECT min(price_usd) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS min_ask_usd669 `);670 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) };671});672673export const countAssets = cache(async (): Promise<number> => int((await one<Record<string, unknown>>(sql`SELECT count(*) AS n FROM assets`))?.n));674675export const getAssetsBySlugs = cache(async (slugs: string[]): Promise<AssetCard[]> => {676 if (!slugs.length) return [];677 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}`);678 const map = new Map(r.map((x) => [String(x.slug), toAssetCard(x)]));679 return slugs.map((s) => map.get(s)).filter((x): x is AssetCard => Boolean(x));680});681682export 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> => {683 const x = await one<Record<string, unknown>>(sql`684 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``}),685 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)686 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,687 (SELECT avg(price_usd) FROM v, b WHERE price_usd BETWEEN b.lo AND b.hi) AS trimmed688 FROM v689 `);690 if (!x || int(x.n) === 0) return null;691 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) };692});693694/** Latest price-guide observation per asset (batch). Attached to cards so catalog-only assets still show a labelled guide price. */695export async function attachGuidePrices<T extends AssetCard>(cards: T[]): Promise<T[]> {696 const ids = cards.filter((c) => c.rivUsd === null).map((c) => c.id);697 if (!ids.length) return cards;698 const r = await rows<Record<string, unknown>>(sql`699 SELECT DISTINCT ON (o.asset_id) o.asset_id, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name700 FROM price_observations o JOIN sources src ON src.id = o.source_id701 WHERE o.asset_id IN ${ids}702 ORDER BY o.asset_id, o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC703 `);704 const map = new Map(r.map((x) => [String(x.asset_id), x]));705 for (const c of cards) {706 const g = map.get(c.id);707 if (g) {708 c.guideUsd = num(g.price_usd);709 c.guideKind = str(g.price_kind);710 c.guideDate = str(g.observation_date);711 c.guideSource = str(g.source_name);712 }713 }714 return cards;715}716717export interface GuidePrice {718 priceUsd: number;719 price: number;720 currency: string;721 priceKind: string;722 observationDate: string;723 sourceName: string;724 sourceUrl: string;725 variantId: string | null;726 variantLabel: string | null;727}728729/** Best current guide price for the header: newest observation, preferring 'market' in USD. */730export const getLatestGuidePrice = cache(async (assetId: string, variantId: string | null = null): Promise<GuidePrice | null> => {731 const x = await one<Record<string, unknown>>(sql`732 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_label733 FROM price_observations o JOIN sources src ON src.id = o.source_id LEFT JOIN asset_variants v ON v.id = o.variant_id734 WHERE o.asset_id = ${assetId} ${variantId ? sql`AND o.variant_id = ${variantId}` : sql``}735 ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC LIMIT 1736 `);737 if (!x) return null;738 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) };739});740741/** Other assets in the same set, ordered by collector number (never a dead end on catalog-only pages). */742export const getSetSiblings = cache(async (asset: { id: string; setSlug: string | null }, limit = 12): Promise<AssetCard[]> => {743 if (!asset.setSlug) return [];744 const r = await rows<Record<string, unknown>>(sql`745 SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id746 WHERE a.set_slug = ${asset.setSlug} AND a.id <> ${asset.id}747 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}748 `);749 return r.map(toAssetCard);750});751752/** Similar assets by title trigram within the category (works for any catalog record). */753export const getSimilarAssets = cache(async (asset: { id: string; name: string; categorySlug: string }, limit = 8): Promise<AssetCard[]> => {754 const r = await rows<Record<string, unknown>>(sql`755 SELECT ${ASSET_CARD_SELECT}, similarity(a.title, ${asset.name}) AS sim FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id756 WHERE a.id <> ${asset.id} AND a.category_slug = ${asset.categorySlug} AND a.title % ${asset.name}757 ORDER BY sim DESC, coalesce(s.sales_count, 0) DESC LIMIT ${limit}758 `);759 return r.map(toAssetCard);760});761762/** Sets/releases derived from the assets table itself (correct even when the `sets` table is sparse). */763export interface SetGroup {764 slug: string;765 name: string;766 code: string | null;767 year: number | null;768 assets: number;769 priced: number;770 sales: number;771 withImage: number;772 thumb: string | null;773}774export const getSetGroups = cache(async (scope: string[], opts: { limit?: number; offset?: number; sort?: 'assets' | 'sales' | 'year' | 'name' } = {}): Promise<{ items: SetGroup[]; total: number }> => {775 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`;776 const r = await rows<Record<string, unknown>>(sql`777 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,778 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,779 (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,780 count(*) OVER() AS total781 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id LEFT JOIN sets st ON st.slug = a.set_slug782 WHERE a.category_slug IN ${scope} AND a.set_slug IS NOT NULL783 GROUP BY a.set_slug, st.name, st.code, st.release_year784 ORDER BY ${order} LIMIT ${opts.limit ?? 60} OFFSET ${opts.offset ?? 0}785 `);786 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 };787});788789/** Set facts derived from its assets when the `sets` table has no row (fallback for /set/[slug]). */790export const getSetFromAssets = cache(async (slug: string) => {791 const x = await one<Record<string, unknown>>(sql`792 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 language793 FROM assets a WHERE a.set_slug = ${slug}794 `);795 if (!x || int(x.assets) === 0) return null;796 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> };797});798799/** Representative thumbnail per category (one cheap index lookup per slug). */800export const getCategoryThumbnails = cache(async (slugs: string[]): Promise<Map<string, string>> => {801 if (!slugs.length) return new Map();802 const r = await rows<Record<string, unknown>>(sql`803 SELECT c.slug, (804 SELECT a.hero_image_url FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id805 WHERE (a.category_slug = c.slug OR a.family_slug = c.slug) AND a.hero_image_url IS NOT NULL806 ORDER BY s.sales_count DESC NULLS LAST, s.riv_usd DESC NULLS LAST, a.created_at DESC LIMIT 1807 ) AS url808 FROM unnest(${textArray(slugs)}) AS c(slug)809 `);810 return new Map(r.filter((x) => x.url).map((x) => [String(x.slug), String(x.url)]));811});812813/** Latest guide observations across the site (fallback for the home "latest transactions" block until sales exist). */814export 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 }>> => {815 const r = await rows<Record<string, unknown>>(sql`816 WITH recent AS (817 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.id818 WHERE coalesce(s.observations_count, 0) > 0 ORDER BY s.updated_at DESC LIMIT ${limit * 3}819 )820 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_name821 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 true822 JOIN sources src ON src.id = o.source_id823 ORDER BY o.observation_date DESC LIMIT ${limit}824 `);825 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) }));826});827828/** Counts for a browse scope, straight from canonical tables (for "13,384 assets · 0 priced yet" lines). */829export 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 }> => {830 const where: SQL[] = [];831 if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);832 if (f.set) where.push(sql`a.set_slug = ${f.set}`);833 if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);834 const x = await one<Record<string, unknown>>(sql`835 SELECT count(*) AS assets, count(s.riv_usd) AS priced, count(*) FILTER (WHERE coalesce(s.sales_count, 0) > 0) AS with_sales,836 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 listings837 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE ${joinAnd(where)}838 `);839 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) };840});841842/** Valuation history (RIV with low/high band) from the valuations table, one point per day. */843export 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 }>> => {844 const r = await rows<Record<string, unknown>>(sql`845 SELECT DISTINCT ON (computed_at::date) computed_at::date AS d, riv_usd, low_usd, high_usd, confidence, sample_size846 FROM valuations847 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')::interval848 ORDER BY computed_at::date, computed_at DESC849 `);850 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) }));851});852853/** Whether the signed-in user watches this asset (null when anonymous). */854export async function isWatchedBy(userId: string | null, assetId: string): Promise<boolean> {855 if (!userId) return false;856 const x = await one<Record<string, unknown>>(sql`857 SELECT 1 AS ok FROM watchlist_items wi JOIN watchlists w ON w.id = wi.watchlist_id858 WHERE w.user_id = ${userId} AND wi.target_type = 'asset' AND wi.target_id = ${assetId} LIMIT 1859 `);860 return Boolean(x);861}862