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';67// ---------- Sales ----------8export interface SalesFilters {9 category?: string | null;10 source?: string | null;11 grader?: string | null;12 minUsd?: number | null;13 maxUsd?: number | null;14 days?: number | null;15 saleType?: string | null;16 q?: string | null;17 page?: number;18 pageSize?: number;19 sort?: 'date' | 'price';20}2122export async function listSales(f: SalesFilters): Promise<{ items: SaleRow[]; total: number; page: number; pageSize: number }> {23 const pageSize = Math.min(f.pageSize ?? 50, 200);24 const page = Math.max(1, f.page ?? 1);25 const where: SQL[] = [sql`s.status = 'valid'`];26 if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);27 if (f.source) where.push(sql`s.source_id = ${f.source}`);28 if (f.grader) where.push(sql`s.grader = ${f.grader}`);29 if (f.minUsd != null) where.push(sql`s.price_usd >= ${f.minUsd}`);30 if (f.maxUsd != null) where.push(sql`s.price_usd <= ${f.maxUsd}`);31 if (f.days) where.push(sql`s.sale_date >= now() - (${f.days}::int || ' days')::interval`);32 if (f.saleType) where.push(sql`s.sale_type = ${f.saleType}`);33 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() + '%'})`);34 const order = f.sort === 'price' ? sql`s.price_usd DESC` : sql`s.sale_date DESC, s.created_at DESC`;35 const r = await rows<Record<string, unknown>>(sql`36 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, count(*) OVER() AS total37 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id38 WHERE ${joinAnd(where)} ORDER BY ${order} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}39 `);40 return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };41}4243export const listSaleSources = cache(async (): Promise<Array<{ id: string; name: string; sales: number }>> => {44 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`);45 return r.map((x) => ({ id: String(x.id), name: String(x.name), sales: int(x.sales) }));46});4748/** Record sales (§154): highest verified sale overall and per family/category. */49export const getRecordSales = cache(async (): Promise<Array<SaleRow & { familySlug: string }>> => {50 const r = await rows<Record<string, unknown>>(sql`51 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_url52 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id53 WHERE s.status = 'valid' AND s.confidence >= 0.8 ORDER BY a.family_slug, s.price_usd DESC54 `);55 return r.map((x) => ({ ...toSaleRow(x), familySlug: String(x.family_slug) })).sort((a, b) => b.priceUsd - a.priceUsd);56});5758export const getTopSales = cache(async (limit = 50, scope?: string[]): Promise<SaleRow[]> => {59 const r = await rows<Record<string, unknown>>(sql`60 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url61 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id62 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}63 `);64 return r.map(toSaleRow);65});6667export const getRecentSalesInScope = cache(async (scope: string[], limit = 20): Promise<SaleRow[]> => {68 const r = await rows<Record<string, unknown>>(sql`69 SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url70 FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id71 WHERE s.status = 'valid' AND a.category_slug IN ${scope} ORDER BY s.sale_date DESC LIMIT ${limit}72 `);73 return r.map(toSaleRow);74});7576// ---------- Listings ----------77export interface ListingsFilters {78 category?: string | null;79 source?: string | null;80 grader?: string | null;81 minUsd?: number | null;82 maxUsd?: number | null;83 minDiscount?: number | null;84 listingType?: string | null;85 q?: string | null;86 sort?: 'newest' | 'price_asc' | 'price_desc' | 'discount' | 'ending';87 page?: number;88 pageSize?: number;89}9091export async function listListings(f: ListingsFilters): Promise<{ items: ListingRow[]; total: number; page: number; pageSize: number }> {92 const pageSize = Math.min(f.pageSize ?? 50, 200);93 const page = Math.max(1, f.page ?? 1);94 const where: SQL[] = [sql`l.availability = 'available'`];95 if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);96 if (f.source) where.push(sql`l.source_id = ${f.source}`);97 if (f.grader) where.push(sql`l.grader = ${f.grader}`);98 if (f.minUsd != null) where.push(sql`l.price_usd >= ${f.minUsd}`);99 if (f.maxUsd != null) where.push(sql`l.price_usd <= ${f.maxUsd}`);100 if (f.minDiscount != null) where.push(sql`l.discount_to_riv >= ${f.minDiscount}`);101 if (f.listingType) where.push(sql`l.listing_type = ${f.listingType}`);102 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() + '%'})`);103 const orders: Record<NonNullable<ListingsFilters['sort']>, SQL> = {104 newest: sql`l.first_seen_at DESC`,105 price_asc: sql`l.price_usd ASC NULLS LAST`,106 price_desc: sql`l.price_usd DESC NULLS LAST`,107 discount: sql`l.discount_to_riv DESC NULLS LAST`,108 ending: sql`l.ends_at ASC NULLS LAST`,109 };110 const r = await rows<Record<string, unknown>>(sql`111 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 total112 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.id113 WHERE ${joinAnd(where)} ORDER BY ${orders[f.sort ?? 'newest']} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}114 `);115 return { items: r.map(toListingRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };116}117118export const listListingSources = cache(async (): Promise<Array<{ id: string; name: string; listings: number }>> => {119 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`);120 return r.map((x) => ({ id: String(x.id), name: String(x.name), listings: int(x.listings) }));121});122123// ---------- Auctions ----------124export interface AuctionRow {125 id: string;126 sourceId: string;127 auctionHouse: string;128 name: string;129 url: string;130 startsAt: Date | null;131 endsAt: Date | null;132 location: string | null;133 categorySlugs: string[];134 lotCount: number | null;135 status: string;136 currency: string | null;137 lotsTracked: number;138}139140export async function listAuctions(opts: { status?: 'upcoming' | 'live' | 'ended' | null; from?: Date; to?: Date; category?: string | null; house?: string | null; limit?: number } = {}): Promise<AuctionRow[]> {141 const where: SQL[] = [];142 if (opts.status) where.push(sql`au.status = ${opts.status}`);143 if (opts.from) where.push(sql`coalesce(au.ends_at, au.starts_at) >= ${iso(opts.from)}::timestamptz`);144 if (opts.to) where.push(sql`coalesce(au.starts_at, au.ends_at) <= ${iso(opts.to)}::timestamptz`);145 if (opts.category) where.push(sql`au.category_slugs && ${textArray(categoryScope(opts.category))}`);146 if (opts.house) where.push(sql`au.auction_house = ${opts.house}`);147 const r = await rows<Record<string, unknown>>(sql`148 SELECT au.*, (SELECT count(*) FROM auction_lots l WHERE l.auction_id = au.id) AS lots_tracked FROM auctions au WHERE ${joinAnd(where)}149 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}150 `);151 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) }));152}153154export interface LotRow {155 id: string;156 auctionId: string;157 auctionName: string | null;158 auctionHouse: string | null;159 assetSlug: string | null;160 assetTitle: string | null;161 categorySlug: string | null;162 lotNumber: string | null;163 title: string;164 url: string;165 estimateLow: number | null;166 estimateHigh: number | null;167 currentBid: number | null;168 hammerPrice: number | null;169 currency: string | null;170 bidCount: number | null;171 startsAt: Date | null;172 endsAt: Date | null;173 status: string;174 imageUrls: string[];175 grader: string | null;176 grade: string | null;177 rivUsd: number | null;178}179180export async function listLots(opts: { status?: 'upcoming' | 'live' | 'ended' | null; endingWithinHours?: number | null; auctionId?: string | null; category?: string | null; limit?: number; sort?: 'ending' | 'value' } = {}): Promise<LotRow[]> {181 const where: SQL[] = [];182 if (opts.status) where.push(sql`l.status = ${opts.status}`);183 if (opts.endingWithinHours) where.push(sql`l.ends_at BETWEEN now() AND now() + (${opts.endingWithinHours}::int || ' hours')::interval`);184 if (opts.auctionId) where.push(sql`l.auction_id = ${opts.auctionId}`);185 if (opts.category) where.push(sql`a.category_slug IN ${categoryScope(opts.category)}`);186 const order = opts.sort === 'value' ? sql`coalesce(l.hammer_price, l.current_bid, l.estimate_high) DESC NULLS LAST` : sql`l.ends_at ASC NULLS LAST`;187 const r = await rows<Record<string, unknown>>(sql`188 SELECT l.*, au.name AS auction_name, au.auction_house, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, st.riv_usd189 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.id190 WHERE ${joinAnd(where)} ORDER BY ${order} LIMIT ${opts.limit ?? 50}191 `);192 return r.map((x) => ({ id: String(x.id), auctionId: String(x.auction_id), auctionName: str(x.auction_name), auctionHouse: str(x.auction_house), assetSlug: str(x.asset_slug), assetTitle: str(x.asset_title), categorySlug: str(x.category_slug), lotNumber: str(x.lot_number), title: String(x.title), url: String(x.url), estimateLow: num(x.estimate_low), estimateHigh: num(x.estimate_high), currentBid: num(x.current_bid), hammerPrice: num(x.hammer_price), currency: str(x.currency), bidCount: num(x.bid_count), startsAt: date(x.starts_at), endsAt: date(x.ends_at), status: String(x.status), imageUrls: (x.image_urls as string[]) ?? [], grader: str(x.grader), grade: str(x.grade), rivUsd: num(x.riv_usd) }));193}194195export const listAuctionHouses = cache(async (): Promise<Array<{ house: string; auctions: number; lots: number; upcoming: number }>> => {196 const r = await rows<Record<string, unknown>>(sql`197 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 upcoming198 FROM auctions au LEFT JOIN auction_lots l ON l.auction_id = au.id GROUP BY au.auction_house ORDER BY auctions DESC199 `);200 return r.map((x) => ({ house: String(x.house), auctions: int(x.auctions), lots: int(x.lots), upcoming: int(x.upcoming) }));201});202203// ---------- Radar / records / news ----------204export interface RadarRow {205 id: string;206 kind: string;207 score: number;208 evidence: Record<string, unknown>;209 entityType: string | null;210 entityId: string | null;211 detectedAt: Date;212 assetSlug: string;213 assetTitle: string;214 categorySlug: string;215 heroImageUrl: string | null;216 rivUsd: number | null;217}218219export async function listRadar(opts: { kind?: string | null; limit?: number; scope?: string[] } = {}): Promise<RadarRow[]> {220 const r = await rows<Record<string, unknown>>(sql`221 SELECT r.*, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, st.riv_usd222 FROM radar_findings r JOIN assets a ON a.id = r.asset_id LEFT JOIN asset_stats st ON st.asset_id = a.id223 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``}224 ORDER BY r.detected_at DESC, r.score DESC LIMIT ${opts.limit ?? 50}225 `);226 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) }));227}228229export interface NewsRow {230 id: string;231 sourceId: string;232 sourceName: string;233 url: string;234 title: string;235 summary: string | null;236 aiSummary: string | null;237 publishedAt: Date | null;238 categorySlugs: string[];239 newsType: string | null;240 imageUrl: string | null;241}242243export async function listNews(opts: { type?: string | null; category?: string | null; limit?: number; page?: number } = {}): Promise<{ items: NewsRow[]; total: number }> {244 const limit = opts.limit ?? 40;245 const page = Math.max(1, opts.page ?? 1);246 const where: SQL[] = [];247 if (opts.type) where.push(sql`n.news_type = ${opts.type}`);248 if (opts.category) where.push(sql`n.category_slugs && ${textArray(categoryScope(opts.category))}`);249 const r = await rows<Record<string, unknown>>(sql`250 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)}251 ORDER BY n.published_at DESC NULLS LAST, n.fetched_at DESC LIMIT ${limit} OFFSET ${(page - 1) * limit}252 `);253 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 };254}255256// ---------- Grading ----------257export const getGradePremiums = cache(async (category?: string | null): Promise<Array<{ categorySlug: string; grader: string; grade: string; marketMultiplier: number; sampleSize: number; computedAt: Date }>> => {258 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`);259 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)! }));260});261262export const getGraderActivity = cache(async (): Promise<Array<{ grader: string; sales: number; assets: number; medianUsd: number | null; populationReports: number }>> => {263 const r = await rows<Record<string, unknown>>(sql`264 SELECT g.slug AS grader,265 (SELECT count(*) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS sales,266 (SELECT count(DISTINCT s.asset_id) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS assets,267 (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,268 (SELECT count(*) FROM population_reports p WHERE p.grader = g.slug) AS population_reports269 FROM graders g WHERE g.active ORDER BY sales DESC, g.slug270 `);271 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) }));272});273274export const getDbGraders = cache(async () => {275 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`);276 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) }));277});278279// ---------- Coverage (Data page) ----------280export const getConnectorCoverage = cache(async () => {281 const r = await rows<Record<string, unknown>>(sql`282 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,283 (SELECT count(*) FROM raw_records rr WHERE rr.connector_id = c.id) AS raw_records,284 (SELECT count(*) FROM sales s WHERE s.connector_id = c.id) AS sales,285 (SELECT count(*) FROM listings l WHERE l.connector_id = c.id) AS listings,286 (SELECT count(*) FROM price_observations o WHERE o.connector_id = c.id) AS observations,287 h.status AS health_status, h.health288 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.id289 `);290 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 }));291});292293export const getFxCoverage = cache(async () => {294 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`);295 return { rows: int(x?.n), first: str(x?.first), last: str(x?.last), quotes: int(x?.quotes) };296});297298export const getTrendingCategories = cache(async (limit = 8) => {299 const r = await rows<Record<string, unknown>>(sql`300 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}301 `);302 return r.map((x) => ({ categorySlug: String(x.category_slug), trending: Number(x.trending), momentum: num(x.momentum), assets: int(x.assets) }));303});304