TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { cache } from 'react';3import type { SQL } from 'drizzle-orm';4import { rows, one, sql, num, int, str, date, joinAnd, textArray, iso } from './_util';5import { categoryScope, SALE_SELECT, LISTING_SELECT, toSaleRow, toListingRow, type SaleRow, type ListingRow } from './assets';6import { houseSlug } from '@/lib/auction-house';78// ---------- Sales ----------9export interface SalesFilters {10 category?: string | null;11 source?: string | null;12 grader?: string | null;13 minUsd?: number | null;14 maxUsd?: number | null;15 days?: number | null;16 saleType?: string | null;17 q?: string | null;18 page?: number;19 pageSize?: number;20 sort?: 'date' | 'price';21}2223export async function listSales(f: SalesFilters): Promise<{ items: SaleRow[]; total: number; page: number; pageSize: number }> {24 const pageSize = Math.min(f.pageSize ?? 50, 200);25 const page = Math.max(1, f.page ?? 1);26 const where: SQL[] = [sql`s.status = 'valid'`];27 if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);28 if (f.source) where.push(sql`s.source_id = ${f.source}`);29 if (f.grader) where.push(sql`s.grader = ${f.grader}`);30 if (f.minUsd != null) where.push(sql`s.price_usd >= ${f.minUsd}`);31 if (f.maxUsd != null) where.push(sql`s.price_usd <= ${f.maxUsd}`);32 if (f.days) where.push(sql`s.sale_date >= now() - (${f.days}::int || ' days')::interval`);33 if (f.saleType) where.push(sql`s.sale_type = ${f.saleType}`);34 if (f.q && f.q.trim().length >= 2) where.push(sql`(a.title ILIKE ${'%' + f.q.trim() + '%'} OR s.raw_title ILIKE ${'%' + f.q.trim() + '%'})`);35 const order = f.sort === 'price' ? sql`s.price_usd DESC` : sql`s.sale_date DESC, s.created_at DESC`;36 const r = await rows<Record<string, unknown>>(sql`37 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, count(*) OVER() AS total38 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id39 WHERE ${joinAnd(where)} ORDER BY ${order} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}40 `);41 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };42}4344export const listSaleSources = cache(async (): Promise<Array<{ id: string; name: string; sales: number }>> => {45 const r = await rows<Record<string, unknown>>(sql`SELECT src.id, src.name, count(*) AS sales FROM sales s JOIN sources src ON src.id = s.source_id WHERE s.status = 'valid' GROUP BY src.id, src.name ORDER BY sales DESC`);46 return r.map((x) => ({ id: String(x.id), name: String(x.name), sales: int(x.sales) }));47});4849/** Record sales (§154): highest verified sale overall and per family/category. */50export const getRecordSales = cache(async (): Promise<Array<SaleRow & { familySlug: string }>> => {51 const r = await rows<Record<string, unknown>>(sql`52 SELECT DISTINCT ON (a.family_slug) ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.family_slug, a.hero_image_url53 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id54 WHERE s.status = 'valid' AND s.confidence >= 0.8 ORDER BY a.family_slug, s.price_usd DESC55 `);56 return r.map((x) => ({ ...toSaleRow(x), familySlug: String(x.family_slug) })).sort((a, b) => b.priceUsd - a.priceUsd);57});5859export const getTopSales = cache(async (limit = 50, scope?: string[]): Promise<SaleRow[]> => {60 const r = await rows<Record<string, unknown>>(sql`61 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url62 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id63 WHERE s.status = 'valid' AND s.confidence >= 0.8 ${scope?.length ? sql`AND a.category_slug IN ${scope}` : sql``} ORDER BY s.price_usd DESC LIMIT ${limit}64 `);65 return r.map(toSaleRow);66});6768export const getRecentSalesInScope = cache(async (scope: string[], limit = 20): Promise<SaleRow[]> => {69 const r = await rows<Record<string, unknown>>(sql`70 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url71 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id72 WHERE s.status = 'valid' AND a.category_slug IN ${scope} ORDER BY s.sale_date DESC LIMIT ${limit}73 `);74 return r.map(toSaleRow);75});7677// ---------- Listings ----------78export interface ListingsFilters {79 category?: string | null;80 source?: string | null;81 grader?: string | null;82 minUsd?: number | null;83 maxUsd?: number | null;84 minDiscount?: number | null;85 listingType?: string | null;86 q?: string | null;87 sort?: 'newest' | 'price_asc' | 'price_desc' | 'discount' | 'ending';88 page?: number;89 pageSize?: number;90}9192export async function listListings(f: ListingsFilters): Promise<{ items: ListingRow[]; total: number; page: number; pageSize: number }> {93 const pageSize = Math.min(f.pageSize ?? 50, 200);94 const page = Math.max(1, f.page ?? 1);95 const where: SQL[] = [sql`l.availability = 'available'`];96 if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);97 if (f.source) where.push(sql`l.source_id = ${f.source}`);98 if (f.grader) where.push(sql`l.grader = ${f.grader}`);99 if (f.minUsd != null) where.push(sql`l.price_usd >= ${f.minUsd}`);100 if (f.maxUsd != null) where.push(sql`l.price_usd <= ${f.maxUsd}`);101 if (f.minDiscount != null) where.push(sql`l.discount_to_riv <= ${-Math.abs(f.minDiscount)} AND NOT ('riv_review' = ANY(l.flags))`); // stored negative = below RIV; review cases excluded102 if (f.sort === 'discount') where.push(sql`NOT ('riv_review' = ANY(l.flags))`);103 if (f.listingType) where.push(sql`l.listing_type = ${f.listingType}`);104 if (f.q && f.q.trim().length >= 2) where.push(sql`(a.title ILIKE ${'%' + f.q.trim() + '%'} OR l.raw_title ILIKE ${'%' + f.q.trim() + '%'})`);105 const orders: Record<NonNullable<ListingsFilters['sort']>, SQL> = {106 newest: sql`l.first_seen_at DESC`,107 price_asc: sql`l.price_usd ASC NULLS LAST`,108 price_desc: sql`l.price_usd DESC NULLS LAST`,109 discount: sql`l.discount_to_riv ASC NULLS LAST`, // most below RIV first110 ending: sql`l.ends_at ASC NULLS LAST`,111 };112 const r = await rows<Record<string, unknown>>(sql`113 SELECT ${LISTING_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, st.riv_usd AS asset_riv_usd, st.riv_confidence AS asset_riv_confidence, count(*) OVER() AS total114 FROM listings l JOIN assets a ON a.id = l.asset_id JOIN sources src ON src.id = l.source_id LEFT JOIN asset_stats st ON st.asset_id = a.id115 WHERE ${joinAnd(where)} ORDER BY ${orders[f.sort ?? 'newest']} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}116 `);117 return { items: r.map(toListingRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };118}119120export const listListingSources = cache(async (): Promise<Array<{ id: string; name: string; listings: number }>> => {121 const r = await rows<Record<string, unknown>>(sql`SELECT src.id, src.name, count(*) AS listings FROM listings l JOIN sources src ON src.id = l.source_id WHERE l.availability = 'available' GROUP BY src.id, src.name ORDER BY listings DESC`);122 return r.map((x) => ({ id: String(x.id), name: String(x.name), listings: int(x.listings) }));123});124125// ---------- Auctions ----------126export interface AuctionRow {127 id: string;128 sourceId: string;129 auctionHouse: string;130 name: string;131 url: string;132 startsAt: Date | null;133 endsAt: Date | null;134 location: string | null;135 categorySlugs: string[];136 lotCount: number | null;137 status: string;138 currency: string | null;139 lotsTracked: number;140}141142export async function listAuctions(opts: { status?: 'upcoming' | 'live' | 'ended' | null; from?: Date; to?: Date; category?: string | null; house?: string | null; limit?: number } = {}): Promise<AuctionRow[]> {143 const where: SQL[] = [];144 if (opts.status) where.push(sql`au.status = ${opts.status}`);145 if (opts.from) where.push(sql`coalesce(au.ends_at, au.starts_at) >= ${iso(opts.from)}::timestamptz`);146 if (opts.to) where.push(sql`coalesce(au.starts_at, au.ends_at) <= ${iso(opts.to)}::timestamptz`);147 if (opts.category) where.push(sql`au.category_slugs && ${textArray(categoryScope(opts.category))}`);148 if (opts.house) where.push(sql`au.auction_house = ${opts.house}`);149 const r = await rows<Record<string, unknown>>(sql`150 SELECT au.*, (SELECT count(*) FROM auction_lots l WHERE l.auction_id = au.id) AS lots_tracked FROM auctions au WHERE ${joinAnd(where)}151 ORDER BY CASE au.status WHEN 'live' THEN 0 WHEN 'upcoming' THEN 1 ELSE 2 END, coalesce(au.ends_at, au.starts_at) ASC NULLS LAST LIMIT ${opts.limit ?? 100}152 `);153 return r.map((x) => ({ id: String(x.id), sourceId: String(x.source_id), auctionHouse: String(x.auction_house), name: String(x.name), url: String(x.url), startsAt: date(x.starts_at), endsAt: date(x.ends_at), location: str(x.location), categorySlugs: (x.category_slugs as string[]) ?? [], lotCount: num(x.lot_count), status: String(x.status), currency: str(x.currency), lotsTracked: int(x.lots_tracked) }));154}155156export interface LotRow {157 id: string;158 auctionId: string;159 auctionName: string | null;160 auctionHouse: string | null;161 auctionHouseSlug: string | null;162 assetSlug: string | null;163 assetTitle: string | null;164 categorySlug: string | null;165 lotNumber: string | null;166 title: string;167 url: string;168 estimateLow: number | null;169 estimateHigh: number | null;170 currentBid: number | null;171 hammerPrice: number | null;172 currency: string | null;173 bidCount: number | null;174 startsAt: Date | null;175 endsAt: Date | null;176 status: string;177 imageUrls: string[];178 grader: string | null;179 grade: string | null;180 rivUsd: number | null;181 rivConfidence: number | null;182 rivSampleSize: number;183 // ---- USD normalisation + assessment (§33–§35); null until the auctions worker has assessed the lot ----184 estimateLowUsd: number | null;185 estimateHighUsd: number | null;186 currentBidUsd: number | null;187 hammerPriceUsd: number | null;188 buyerPremiumRate: number | null;189 feeBasis: string | null;190 allInBidUsd: number | null;191 allInEstimateLowUsd: number | null;192 allInEstimateHighUsd: number | null;193 rivUsdAtAssessment: number | null;194 /** (all-in bid − RIV) / RIV — negative = below the valuation */195 bidVsRiv: number | null;196 /** (all-in low estimate − RIV) / RIV */197 estimateVsRiv: number | null;198 assessmentVerdict: string | null;199 assessedAt: Date | null;200}201202export type LotSort = 'ending' | 'value' | 'discount' | 'bids';203204export interface LotFilters {205 status?: 'upcoming' | 'live' | 'ended' | null;206 /** live or upcoming and not yet ended */207 open?: boolean;208 endingWithinHours?: number | null;209 auctionId?: string | null;210 category?: string | null;211 house?: string | null;212 assetId?: string | null;213 /** only lots whose assessed all-in bid (or estimate) is a gated deal */214 verdict?: 'deal' | null;215 /** keep lots with bid_vs_riv (or estimate_vs_riv) ≤ this value, e.g. −0.1 */216 maxVsRiv?: number | null;217 hasEstimate?: boolean;218 hasBid?: boolean;219 limit?: number;220 page?: number;221 pageSize?: number;222 sort?: LotSort;223}224225const LOT_SELECT = sql`l.*, au.name AS auction_name, au.auction_house, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, st.riv_usd, st.riv_confidence, coalesce(st.riv_sample_size, 0) AS riv_sample_size`;226227function toLotRow(x: Record<string, unknown>): LotRow {228 const house = str(x.auction_house);229 return {230 id: String(x.id),231 auctionId: String(x.auction_id),232 auctionName: str(x.auction_name),233 auctionHouse: house,234 auctionHouseSlug: house ? houseSlug(house) : null,235 assetSlug: str(x.asset_slug),236 assetTitle: str(x.asset_title),237 categorySlug: str(x.category_slug),238 lotNumber: str(x.lot_number),239 title: String(x.title),240 url: String(x.url),241 estimateLow: num(x.estimate_low),242 estimateHigh: num(x.estimate_high),243 currentBid: num(x.current_bid),244 hammerPrice: num(x.hammer_price),245 currency: str(x.currency),246 bidCount: num(x.bid_count),247 startsAt: date(x.starts_at),248 endsAt: date(x.ends_at),249 status: String(x.status),250 imageUrls: (x.image_urls as string[]) ?? [],251 grader: str(x.grader),252 grade: str(x.grade),253 rivUsd: num(x.riv_usd),254 rivConfidence: num(x.riv_confidence),255 rivSampleSize: int(x.riv_sample_size),256 estimateLowUsd: num(x.estimate_low_usd),257 estimateHighUsd: num(x.estimate_high_usd),258 currentBidUsd: num(x.current_bid_usd),259 hammerPriceUsd: num(x.hammer_price_usd),260 buyerPremiumRate: num(x.buyer_premium_rate),261 feeBasis: str(x.fee_basis),262 allInBidUsd: num(x.all_in_bid_usd),263 allInEstimateLowUsd: num(x.all_in_estimate_low_usd),264 allInEstimateHighUsd: num(x.all_in_estimate_high_usd),265 rivUsdAtAssessment: num(x.riv_usd_at_assessment),266 bidVsRiv: num(x.bid_vs_riv),267 estimateVsRiv: num(x.estimate_vs_riv),268 assessmentVerdict: str(x.assessment_verdict),269 assessedAt: date(x.assessed_at),270 };271}272273function lotWhere(opts: LotFilters): SQL[] {274 const where: SQL[] = [];275 if (opts.status) where.push(sql`l.status = ${opts.status}`);276 if (opts.open) where.push(sql`l.status IN ('live', 'upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())`);277 if (opts.endingWithinHours) where.push(sql`l.ends_at BETWEEN now() AND now() + (${opts.endingWithinHours}::int || ' hours')::interval`);278 if (opts.auctionId) where.push(sql`l.auction_id = ${opts.auctionId}`);279 if (opts.assetId) where.push(sql`l.asset_id = ${opts.assetId}`);280 if (opts.category) where.push(sql`a.category_slug IN ${categoryScope(opts.category)}`);281 if (opts.house) where.push(sql`au.auction_house = ${opts.house}`);282 if (opts.verdict === 'deal') where.push(sql`l.assessment_verdict = 'deal'`);283 if (opts.maxVsRiv != null) where.push(sql`coalesce(l.bid_vs_riv, l.estimate_vs_riv) <= ${opts.maxVsRiv} AND l.assessment_verdict IN ('deal', 'fair', 'premium')`);284 if (opts.hasEstimate) where.push(sql`l.estimate_low IS NOT NULL`);285 if (opts.hasBid) where.push(sql`l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0`);286 return where;287}288289const LOT_ORDER: Record<LotSort, SQL> = {290 ending: sql`l.ends_at ASC NULLS LAST`,291 value: sql`coalesce(l.hammer_price_usd, l.all_in_bid_usd, l.all_in_estimate_high_usd, l.hammer_price, l.current_bid, l.estimate_high) DESC NULLS LAST`,292 // most below RIV first; unassessed lots last293 discount: sql`coalesce(l.bid_vs_riv, l.estimate_vs_riv) ASC NULLS LAST, l.ends_at ASC NULLS LAST`,294 bids: sql`l.bid_count DESC NULLS LAST, l.ends_at ASC NULLS LAST`,295};296297const LOT_FROM = sql`FROM auction_lots l LEFT JOIN auctions au ON au.id = l.auction_id LEFT JOIN assets a ON a.id = l.asset_id LEFT JOIN asset_stats st ON st.asset_id = a.id`;298299export async function listLots(opts: LotFilters = {}): Promise<LotRow[]> {300 const r = await rows<Record<string, unknown>>(sql`301 SELECT ${LOT_SELECT} ${LOT_FROM}302 WHERE ${joinAnd(lotWhere(opts))} ORDER BY ${LOT_ORDER[opts.sort ?? 'ending']} LIMIT ${opts.limit ?? 50}303 `);304 return r.map(toLotRow);305}306307/** Paginated lots for /auctions and house pages (count(*) OVER() total). */308export async function listLotsPaged(opts: LotFilters = {}): Promise<{ items: LotRow[]; total: number; page: number; pageSize: number }> {309 const pageSize = Math.min(Math.max(opts.pageSize ?? 50, 1), 200);310 const page = Math.max(1, opts.page ?? 1);311 const r = await rows<Record<string, unknown>>(sql`312 SELECT ${LOT_SELECT}, count(*) OVER() AS total ${LOT_FROM}313 WHERE ${joinAnd(lotWhere(opts))} ORDER BY ${LOT_ORDER[opts.sort ?? 'ending']} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}314 `);315 return { items: r.map(toLotRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };316}317318/** Live/upcoming lots for one asset (all variants), soonest ending first. */319export const listAssetLots = cache(async (assetId: string, limit = 50): Promise<LotRow[]> => listLots({ assetId, open: true, limit, sort: 'ending' }));320321export const countAssetLots = cache(async (assetId: string): Promise<number> => {322 const r = await one<{ n: number }>(sql`SELECT count(*)::int AS n FROM auction_lots l WHERE l.asset_id = ${assetId} AND l.status IN ('live', 'upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())`);323 return r?.n ?? 0;324});325326/** Headline numbers for the /auctions strip. */327export const getLotOverview = cache(async (): Promise<{ open: number; ending24h: number; withBids: number; belowRiv: number; assessed: number; houses: number }> => {328 const r = await one<Record<string, unknown>>(sql`329 SELECT count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())) AS open,330 count(*) FILTER (WHERE l.ends_at BETWEEN now() AND now() + interval '24 hours') AS ending24h,331 count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0) AS with_bids,332 count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.assessment_verdict = 'deal') AS below_riv,333 count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.assessed_at IS NOT NULL) AS assessed,334 count(DISTINCT au.auction_house) FILTER (WHERE l.status IN ('live','upcoming')) AS houses335 FROM auction_lots l LEFT JOIN auctions au ON au.id = l.auction_id336 `);337 return { open: int(r?.open), ending24h: int(r?.ending24h), withBids: int(r?.with_bids), belowRiv: int(r?.below_riv), assessed: int(r?.assessed), houses: int(r?.houses) };338});339340export interface AuctionHouseStats {341 house: string;342 auctions: number;343 upcomingAuctions: number;344 lotsTracked: number;345 openLots: number;346 liveLots: number;347 lotsWithBids: number;348 lotsAssessed: number;349 lotsBelowRiv: number;350 /** results = sales carrying this auction house */351 results: number;352 results365d: number;353 medianAllInUsd365d: number | null;354 maxAllInUsd365d: number | null;355 /** share of results whose premium had to be estimated (fee_basis added_*); null when no result has a fee basis yet */356 estimatedPremiumShare: number | null;357 /** lots ended with a recorded hammer / lots ended — null when the house's results are not recorded per lot */358 sellThrough: number | null;359 lastResultAt: Date | null;360}361362export const getAuctionHouseStats = cache(async (house: string): Promise<AuctionHouseStats | null> => {363 const lots = await one<Record<string, unknown>>(sql`364 SELECT count(DISTINCT au.id) AS auctions,365 count(DISTINCT au.id) FILTER (WHERE au.status <> 'ended') AS upcoming_auctions,366 count(l.id) AS lots,367 count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())) AS open_lots,368 count(l.id) FILTER (WHERE l.status = 'live') AS live_lots,369 count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0) AS with_bids,370 count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.assessed_at IS NOT NULL) AS assessed,371 count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.assessment_verdict = 'deal') AS below_riv,372 count(l.id) FILTER (WHERE l.status = 'ended') AS ended,373 count(l.id) FILTER (WHERE l.status = 'ended' AND l.hammer_price IS NOT NULL) AS ended_sold374 FROM auctions au LEFT JOIN auction_lots l ON l.auction_id = au.id WHERE au.auction_house = ${house}375 `);376 const sales = await one<Record<string, unknown>>(sql`377 SELECT count(*) AS results,378 count(*) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS results_365,379 percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(s.all_in_usd, s.price_usd)) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS median_365,380 max(coalesce(s.all_in_usd, s.price_usd)) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS max_365,381 count(*) FILTER (WHERE s.fee_basis IS NOT NULL) AS with_basis,382 count(*) FILTER (WHERE s.fee_basis LIKE 'added_%') AS estimated_basis,383 max(s.sale_date) AS last_result384 FROM sales s WHERE s.status = 'valid' AND s.auction_house = ${house}385 `);386 if (!lots && !sales) return null;387 const auctions = int(lots?.auctions);388 const results = int(sales?.results);389 if (!auctions && !results) return null;390 const ended = int(lots?.ended);391 const withBasis = int(sales?.with_basis);392 return {393 house,394 auctions,395 upcomingAuctions: int(lots?.upcoming_auctions),396 lotsTracked: int(lots?.lots),397 openLots: int(lots?.open_lots),398 liveLots: int(lots?.live_lots),399 lotsWithBids: int(lots?.with_bids),400 lotsAssessed: int(lots?.assessed),401 lotsBelowRiv: int(lots?.below_riv),402 results,403 results365d: int(sales?.results_365),404 medianAllInUsd365d: num(sales?.median_365),405 maxAllInUsd365d: num(sales?.max_365),406 estimatedPremiumShare: withBasis ? int(sales?.estimated_basis) / withBasis : null,407 sellThrough: ended >= 20 && int(lots?.ended_sold) > 0 ? int(lots?.ended_sold) / ended : null,408 lastResultAt: date(sales?.last_result),409 };410});411412/** Recent results (sales) recorded for an auction house. */413export const listHouseResults = cache(async (house: string, limit = 50): Promise<SaleRow[]> => {414 const r = await rows<Record<string, unknown>>(sql`415 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url416 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id417 WHERE s.status = 'valid' AND s.auction_house = ${house} ORDER BY s.sale_date DESC LIMIT ${limit}418 `);419 return r.map(toSaleRow);420});421422export const listAuctionHouses = cache(async (): Promise<Array<{ house: string; auctions: number; lots: number; upcoming: number }>> => {423 const r = await rows<Record<string, unknown>>(sql`424 SELECT au.auction_house AS house, count(DISTINCT au.id) AS auctions, count(l.id) AS lots, count(DISTINCT au.id) FILTER (WHERE au.status <> 'ended') AS upcoming425 FROM auctions au LEFT JOIN auction_lots l ON l.auction_id = au.id GROUP BY au.auction_house ORDER BY auctions DESC426 `);427 return r.map((x) => ({ house: String(x.house), auctions: int(x.auctions), lots: int(x.lots), upcoming: int(x.upcoming) }));428});429430// ---------- Radar / records / news ----------431export interface RadarRow {432 id: string;433 kind: string;434 score: number;435 evidence: Record<string, unknown>;436 entityType: string | null;437 entityId: string | null;438 detectedAt: Date;439 assetSlug: string;440 assetTitle: string;441 categorySlug: string;442 heroImageUrl: string | null;443 rivUsd: number | null;444}445446export async function listRadar(opts: { kind?: string | null; limit?: number; scope?: string[] } = {}): Promise<RadarRow[]> {447 const r = await rows<Record<string, unknown>>(sql`448 SELECT r.*, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, st.riv_usd449 FROM radar_findings r JOIN assets a ON a.id = r.asset_id LEFT JOIN asset_stats st ON st.asset_id = a.id450 WHERE (r.expires_at IS NULL OR r.expires_at > now()) ${opts.kind ? sql`AND r.kind = ${opts.kind}` : sql``} ${opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``}451 ORDER BY r.detected_at DESC, r.score DESC LIMIT ${opts.limit ?? 50}452 `);453 return r.map((x) => ({ id: String(x.id), kind: String(x.kind), score: Number(x.score), evidence: (x.evidence as Record<string, unknown>) ?? {}, entityType: str(x.entity_type), entityId: str(x.entity_id), detectedAt: date(x.detected_at)!, assetSlug: String(x.asset_slug), assetTitle: String(x.asset_title), categorySlug: String(x.category_slug), heroImageUrl: str(x.hero_image_url), rivUsd: num(x.riv_usd) }));454}455456export interface NewsRow {457 id: string;458 sourceId: string;459 sourceName: string;460 url: string;461 title: string;462 summary: string | null;463 aiSummary: string | null;464 publishedAt: Date | null;465 categorySlugs: string[];466 newsType: string | null;467 imageUrl: string | null;468}469470export async function listNews(opts: { type?: string | null; category?: string | null; limit?: number; page?: number } = {}): Promise<{ items: NewsRow[]; total: number }> {471 const limit = opts.limit ?? 40;472 const page = Math.max(1, opts.page ?? 1);473 const where: SQL[] = [];474 if (opts.type) where.push(sql`n.news_type = ${opts.type}`);475 if (opts.category) where.push(sql`n.category_slugs && ${textArray(categoryScope(opts.category))}`);476 const r = await rows<Record<string, unknown>>(sql`477 SELECT n.*, src.name AS source_name, count(*) OVER() AS total FROM news n JOIN sources src ON src.id = n.source_id WHERE ${joinAnd(where)}478 ORDER BY n.published_at DESC NULLS LAST, n.fetched_at DESC LIMIT ${limit} OFFSET ${(page - 1) * limit}479 `);480 return { items: r.map((x) => ({ id: String(x.id), sourceId: String(x.source_id), sourceName: String(x.source_name), url: String(x.url), title: String(x.title), summary: str(x.summary), aiSummary: str(x.ai_summary), publishedAt: date(x.published_at), categorySlugs: (x.category_slugs as string[]) ?? [], newsType: str(x.news_type), imageUrl: str(x.image_url) })), total: r.length ? int(r[0]!.total) : 0 };481}482483// ---------- Grading ----------484export const getGradePremiums = cache(async (category?: string | null): Promise<Array<{ categorySlug: string; grader: string; grade: string; marketMultiplier: number; sampleSize: number; computedAt: Date }>> => {485 const r = await rows<Record<string, unknown>>(sql`SELECT * FROM grade_premiums ${category ? sql`WHERE category_slug IN ${categoryScope(category)}` : sql``} ORDER BY category_slug, grader, market_multiplier DESC`);486 return r.map((x) => ({ categorySlug: String(x.category_slug), grader: String(x.grader), grade: String(x.grade), marketMultiplier: Number(x.market_multiplier), sampleSize: int(x.sample_size), computedAt: date(x.computed_at)! }));487});488489export const getGraderActivity = cache(async (): Promise<Array<{ grader: string; sales: number; assets: number; medianUsd: number | null; populationReports: number }>> => {490 const r = await rows<Record<string, unknown>>(sql`491 SELECT g.slug AS grader,492 (SELECT count(*) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS sales,493 (SELECT count(DISTINCT s.asset_id) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS assets,494 (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY s.price_usd) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS median_usd,495 (SELECT count(*) FROM population_reports p WHERE p.grader = g.slug) AS population_reports496 FROM graders g WHERE g.active ORDER BY sales DESC, g.slug497 `);498 return r.map((x) => ({ grader: String(x.grader), sales: int(x.sales), assets: int(x.assets), medianUsd: num(x.median_usd), populationReports: int(x.population_reports) }));499});500501export const getDbGraders = cache(async () => {502 const r = await rows<Record<string, unknown>>(sql`SELECT slug, name, category_slugs, scale, population_url, verify_url FROM graders WHERE active ORDER BY slug`);503 return r.map((x) => ({ slug: String(x.slug), name: String(x.name), categorySlugs: (x.category_slugs as string[]) ?? [], scale: (x.scale as { values: string[]; top: string; type: string }) ?? { values: [], top: '', type: 'numeric' }, populationUrl: str(x.population_url), verifyUrl: str(x.verify_url) }));504});505506// ---------- Coverage (Data page) ----------507export const getConnectorCoverage = cache(async () => {508 const r = await rows<Record<string, unknown>>(sql`509 SELECT c.id, c.display_name, c.source_id, src.name AS source_name, src.source_type, src.url AS source_url, src.trust_score, c.categories, c.status, c.refresh_frequency_minutes, c.last_success_at, c.supports_sold, c.supports_listings, c.supports_catalog, c.supports_auctions, c.supports_population,510 (SELECT count(*) FROM raw_records rr WHERE rr.connector_id = c.id) AS raw_records,511 (SELECT count(*) FROM sales s WHERE s.connector_id = c.id) AS sales,512 (SELECT count(*) FROM listings l WHERE l.connector_id = c.id) AS listings,513 (SELECT count(*) FROM price_observations o WHERE o.connector_id = c.id) AS observations,514 h.status AS health_status, h.health515 FROM connectors c JOIN sources src ON src.id = c.source_id LEFT JOIN connector_health h ON h.connector_id = c.id ORDER BY sales DESC, observations DESC, c.id516 `);517 return r.map((x) => ({ id: String(x.id), displayName: String(x.display_name), sourceId: String(x.source_id), sourceName: String(x.source_name), sourceType: String(x.source_type), sourceUrl: str(x.source_url), trustScore: Number(x.trust_score ?? 0), categories: (x.categories as string[]) ?? [], status: String(x.status), refreshMinutes: int(x.refresh_frequency_minutes), lastSuccessAt: date(x.last_success_at), supportsSold: Boolean(x.supports_sold), supportsListings: Boolean(x.supports_listings), supportsCatalog: Boolean(x.supports_catalog), supportsAuctions: Boolean(x.supports_auctions), supportsPopulation: Boolean(x.supports_population), rawRecords: int(x.raw_records), sales: int(x.sales), listings: int(x.listings), observations: int(x.observations), healthStatus: str(x.health_status), health: (x.health as Record<string, unknown>) ?? null }));518});519520export const getFxCoverage = cache(async () => {521 const x = await one<Record<string, unknown>>(sql`SELECT count(*) AS n, min(date) AS first, max(date) AS last, count(DISTINCT quote) AS quotes FROM fx_rates`);522 return { rows: int(x?.n), first: str(x?.first), last: str(x?.last), quotes: int(x?.quotes) };523});524525export const getTrendingCategories = cache(async (limit = 8) => {526 const r = await rows<Record<string, unknown>>(sql`527 SELECT a.category_slug, avg(s.trending_score) AS trending, avg(s.momentum_30d) AS momentum, count(*) AS assets FROM asset_stats s JOIN assets a ON a.id = s.asset_id WHERE s.trending_score IS NOT NULL GROUP BY a.category_slug ORDER BY trending DESC LIMIT ${limit}528 `);529 return r.map((x) => ({ categorySlug: String(x.category_slug), trending: Number(x.trending), momentum: num(x.momentum), assets: int(x.assets) }));530});531