1import { getDb, sql } from '@rareindex/database';23/**4 * Read-only query helpers for the public API. Plain SQL through drizzle's `sql` tag: every5 * function returns already-shaped rows so route handlers stay thin. Nothing here writes.6 */7type Row = Record<string, unknown>;8async function rows<T = Row>(q: ReturnType<typeof sql>): Promise<T[]> {9 const res = await getDb().execute(q);10 return res as unknown as T[];11}1213export const ASSET_COLUMNS = sql`a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.brand, a.franchise, a.set_name, a.set_code, a.number, a.year, a.edition, a.variant, a.language, a.hero_image_url, a.identifiers, a.data_quality,14 s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, s.riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_7d, s.change_30d, s.change_1y, s.ath_usd, s.atl_usd,15 s.sales_count, s.sales_30d, s.active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.momentum_30d, s.trending_score, s.value_opportunity, s.updated_at as stats_updated_at`;1617export async function searchAssets(opts: { q?: string; category?: string; limit: number; offset: number }) {18 const q = opts.q?.trim();19 const catFilter = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;20 if (q) {21 const tsq = q22 .split(/\s+/)23 .filter(Boolean)24 .map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, ''))25 .filter(Boolean)26 .map((t) => `${t}:*`)27 .join(' & ');28 return rows(sql`29 select ${ASSET_COLUMNS},30 (coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})), 0) * 2 + similarity(a.title, ${q}) + coalesce(log(1 + s.sales_count), 0) * 0.05) as score31 from assets a left join asset_stats s on s.asset_id = a.id32 where (a.search @@ to_tsquery('simple', ${tsq}) or a.title % ${q} or a.identifiers::text ilike ${'%' + q + '%'}) ${catFilter}33 order by score desc, s.sales_count desc nulls last34 limit ${opts.limit} offset ${opts.offset}`);35 }36 return rows(sql`37 select ${ASSET_COLUMNS}, 0 as score from assets a left join asset_stats s on s.asset_id = a.id38 where true ${catFilter}39 order by s.sales_count desc nulls last, a.updated_at desc40 limit ${opts.limit} offset ${opts.offset}`);41}4243export async function getAsset(idOrSlug: string) {44 const [asset] = await rows(sql`select ${ASSET_COLUMNS}, a.description, a.reference, a.model, a.series, a.rarity, a.production_quantity, a.original_msrp, a.original_msrp_currency, a.release_date, a.metadata, a.created_at, a.updated_at45 from assets a left join asset_stats s on s.asset_id = a.id where a.id = ${idOrSlug} or a.slug = ${idOrSlug} limit 1`);46 if (!asset) return null;47 const id = asset.id as string;48 const variants = await rows(sql`select v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,49 vs.riv_usd, vs.riv_low_usd, vs.riv_high_usd, vs.riv_confidence, vs.riv_sample_size, vs.latest_sale_usd, vs.latest_sale_at, vs.change_30d, vs.change_1y, vs.sales_count, vs.sales_30d, vs.active_listings, vs.min_ask_usd, vs.liquidity_score50 from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${id} order by vs.sales_count desc nulls last, v.label`);51 const [valuation] = await rows(sql`select id, variant_id, computed_at, riv_usd, low_usd, high_usd, confidence, confidence_label, sample_size, window_days, methods, method, notes from valuations where asset_id = ${id} and variant_id is null order by computed_at desc limit 1`);52 const sources = await rows(sql`select source_id, count(*)::int as sales from sales where asset_id = ${id} and status = 'valid' group by source_id order by sales desc`);53 return { ...(asset as Row & { id: string }), variants, valuation: valuation ?? null, sources };54}5556export async function assetSales(assetId: string, opts: { limit: number; offset: number; variantId?: string; includeFlagged?: boolean }) {57 const statusFilter = opts.includeFlagged ? sql`and status <> 'excluded'` : sql`and status = 'valid'`;58 const variantFilter = opts.variantId ? sql`and variant_id = ${opts.variantId}` : sql``;59 return rows(sql`select id, variant_id, source_id, source_url, sale_type, sale_date, price, currency, price_usd, buyer_premium_included, condition, grader, grade, certification_number, auction_house, lot_number, image_urls, raw_title, confidence, data_quality, status, flags60 from sales where asset_id = ${assetId} ${statusFilter} ${variantFilter} order by sale_date desc limit ${opts.limit} offset ${opts.offset}`);61}6263export async function assetListings(assetId: string, opts: { limit: number; offset: number; availability?: string }) {64 const avail = opts.availability ?? 'available';65 return rows(sql`select id, variant_id, source_id, source_url, listing_type, price, currency, price_usd, seller, location, shipping_cost, condition, grader, grade, certification_number, image_urls, raw_title, listed_at, ends_at, availability, bid_count, first_seen_at, last_seen_at, discount_to_riv, flags66 from listings where asset_id = ${assetId} and availability = ${avail} order by price_usd asc nulls last limit ${opts.limit} offset ${opts.offset}`);67}6869export async function assetHistory(assetId: string, opts: { variantId?: string; from?: string; to?: string }) {70 const variant = opts.variantId ?? '';71 const from = opts.from ?? '1900-01-01';72 const to = opts.to ?? '2999-12-31';73 return rows(sql`select date, riv_usd, latest_sale_usd, median_usd, sales_count, volume_usd, listings_count, min_ask_usd, observation_usd74 from price_snapshots where asset_id = ${assetId} and variant_id = ${variant} and date between ${from} and ${to} order by date asc`);75}7677export async function listCategories() {78 return rows(sql`select c.slug, c.parent_slug, c.family_slug, c.name, c.short_name, c.description, c.level, c.phase, c.active, c.index_ticker, c.condition_scale, c.graders,79 (select count(*)::int from assets a where a.category_slug = c.slug) as tracked_assets80 from categories c where c.active order by c.sort_order`);81}8283export async function listIndices() {84 return rows(sql`with latest as (85 select distinct on (index_id) index_id, date, value, constituents_count, transactions, volume_usd, median_sale_usd, market_cap_est_usd, market_cap_confidence, liquidity_score, momentum, tracked_assets, coverage86 from index_values order by index_id, date desc)87 select i.id, i.ticker, i.name, i.description, i.parent_ticker, i.family_slugs, i.methodology, i.weighting, i.base_date, i.base_value, i.min_constituents, i.is_flagship, i.color,88 l.date as as_of, l.value, l.constituents_count, l.transactions, l.volume_usd, l.median_sale_usd, l.market_cap_est_usd, l.market_cap_confidence, l.liquidity_score, l.momentum, l.tracked_assets, l.coverage,89 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '1 day' order by date desc limit 1) as value_1d,90 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '7 day' order by date desc limit 1) as value_7d,91 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '30 day' order by date desc limit 1) as value_30d,92 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '365 day' order by date desc limit 1) as value_1y,93 (select value from index_values v where v.index_id = i.id and v.date <= date_trunc('year', l.date)::date order by date desc limit 1) as value_ytd94 from indices i left join latest l on l.index_id = i.id where i.active order by i.is_flagship desc, i.ticker`);95}9697export async function indexHistory(ticker: string, opts: { from?: string; to?: string }) {98 const from = opts.from ?? '1900-01-01';99 const to = opts.to ?? '2999-12-31';100 return rows(sql`select v.date, v.value, v.constituents_count, v.transactions, v.volume_usd, v.median_sale_usd, v.avg_sale_usd, v.market_cap_est_usd, v.liquidity_score, v.momentum, v.coverage101 from index_values v join indices i on i.id = v.index_id where i.ticker = ${ticker} and v.date between ${from} and ${to} order by v.date asc`);102}103104export async function listMarkets() {105 return rows(sql`with latest as (106 select distinct on (category_slug) * from category_snapshots order by category_slug, date desc)107 select c.slug, c.name, c.family_slug, c.level, c.index_ticker, l.date as as_of, l.index_value, l.tracked_assets, l.assets_with_valuation, l.sales, l.volume_usd, l.median_sale_usd, l.active_listings, l.market_cap_est_usd, l.liquidity_score, l.change_1d, l.change_7d, l.change_30d, l.change_1y,108 (select count(*)::int from assets a where a.family_slug = c.slug or a.category_slug = c.slug) as assets_now109 from categories c left join latest l on l.category_slug = c.slug where c.active and c.level = 0 order by c.sort_order`);110}111112export async function getMarket(slug: string) {113 const [category] = await rows(sql`select slug, parent_slug, family_slug, name, description, level, phase, index_ticker, condition_scale, graders from categories where slug = ${slug}`);114 if (!category) return null;115 const [snapshot] = await rows(sql`select * from category_snapshots where category_slug = ${slug} order by date desc limit 1`);116 const scope = sql`(a.category_slug = ${slug} or a.family_slug = ${slug})`;117 const [counts] = await rows(sql`select count(*)::int as tracked_assets, count(s.riv_usd)::int as valued_assets, coalesce(sum(s.sales_count),0)::int as sales_total, coalesce(sum(s.active_listings),0)::int as active_listings from assets a left join asset_stats s on s.asset_id = a.id where ${scope}`);118 const gainers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d desc limit 10`);119 const losers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d asc limit 10`);120 const mostValuable = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.riv_usd is not null order by s.riv_usd desc limit 10`);121 const mostLiquid = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.liquidity_score is not null order by s.liquidity_score desc limit 10`);122 const recentSales = await rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade from sales sa join assets a on a.id = sa.asset_id where ${scope} and sa.status = 'valid' order by sa.sale_date desc limit 20`);123 const history = await rows(sql`select date, index_value, sales, volume_usd, median_sale_usd, active_listings, tracked_assets from category_snapshots where category_slug = ${slug} order by date asc`);124 return { category, snapshot: snapshot ?? null, counts, gainers, losers, most_valuable: mostValuable, most_liquid: mostLiquid, recent_sales: recentSales, history };125}126127export async function trending(opts: { category?: string; limit: number; offset: number }) {128 const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;129 return rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where s.trending_score is not null ${cat} order by s.trending_score desc limit ${opts.limit} offset ${opts.offset}`);130}131132export async function latestSales(opts: { category?: string; minUsd?: number; limit: number; offset: number }) {133 const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;134 const min = opts.minUsd ? sql`and sa.price_usd >= ${opts.minUsd}` : sql``;135 return rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, a.category_slug, a.hero_image_url, sa.source_id, sa.source_url, sa.sale_type, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.condition, sa.auction_house, sa.confidence136 from sales sa join assets a on a.id = sa.asset_id where sa.status = 'valid' ${cat} ${min} order by sa.sale_date desc, sa.created_at desc limit ${opts.limit} offset ${opts.offset}`);137}138139/** Record sales: highest verified transaction per family (§154). */140export async function recordSales() {141 return rows(sql`select distinct on (a.family_slug) a.family_slug, c.name as family_name, sa.id as sale_id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.auction_house142 from sales sa join assets a on a.id = sa.asset_id join categories c on c.slug = a.family_slug143 where sa.status = 'valid' and sa.confidence >= 0.8 and sa.is_bundle = false144 order by a.family_slug, sa.price_usd desc`);145}146147export async function platformStats() {148 const [r] = await rows(sql`select149 (select count(*)::int from assets) as assets,150 (select count(*)::int from sales where status = 'valid') as sales,151 (select count(*)::int from listings where availability = 'available') as listings,152 (select count(*)::int from sources where active) as sources,153 (select count(*)::int from connectors where status = 'active') as connectors,154 (select count(*)::int from categories where active) as categories`);155 return r;156}157