1import { and, desc, eq, gte, inArray, isNull, or, sql } from 'drizzle-orm';2import { assetStats, assetVariants, assets, gradePremiums, listings, populationReports, priceObservations, priceSnapshots, sales, sources, valuations, variantStats } from '@rareindex/database';3import { adjustmentFactor, assetDataQuality, computeGradePremiums, computeValuation, detectOutliers, liquidityScore, momentumScore, rarityScore, trendingScore, valueOpportunity, type GradePremium, type SaleInput } from '@rareindex/valuation';4import { logger, median, newId, pctChange, toDateOnly } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { auditMany } from '../lib/audit.ts';7import { emit } from '../lib/events.ts';89const log = logger.child({ component: 'valuation' });10const DAY = 86_400_000;11const asDate = (v: unknown): Date | null => (v instanceof Date ? v : typeof v === 'string' ? new Date(v) : null);1213const premiumCache = new Map<string, { at: number; rows: GradePremium[] }>();14async function premiumsFor(categorySlug: string): Promise<GradePremium[]> {15 const hit = premiumCache.get(categorySlug);16 if (hit && Date.now() - hit.at < 3600_000) return hit.rows;17 const rows = await db().select({ grader: gradePremiums.grader, grade: gradePremiums.grade, marketMultiplier: gradePremiums.marketMultiplier, sampleSize: gradePremiums.sampleSize }).from(gradePremiums).where(eq(gradePremiums.categorySlug, categorySlug));18 premiumCache.set(categorySlug, { at: Date.now(), rows });19 return rows;20}2122const trustCache = new Map<string, number>();23async function trustMap(): Promise<Map<string, number>> {24 if (trustCache.size) return trustCache;25 for (const s of await db().select({ id: sources.id, t: sources.trustScore }).from(sources)) trustCache.set(s.id, Number(s.t));26 return trustCache;27}2829interface SaleRow {30 id: string;31 variantId: string | null;32 sourceId: string;33 saleDate: Date;34 priceUsd: number;35 grader: string | null;36 grade: string | null;37 quantity: number;38 isBundle: boolean;39 status: string;40 confidence: number;41 flags: string[];42}4344/** Value one asset: outliers → per-variant valuations → asset stats, snapshots, listing discounts. */45export async function valueAsset(assetId: string, opts: { now?: Date; rebuildHistory?: boolean } = {}): Promise<{ variants: number; riv: number | null }> {46 const now = opts.now ?? new Date();47 const [asset] = await db().select().from(assets).where(eq(assets.id, assetId)).limit(1);48 if (!asset) return { variants: 0, riv: null };49 const trust = await trustMap();50 const variants = await db().select().from(assetVariants).where(eq(assetVariants.assetId, assetId));51 const since3y = new Date(now.getTime() - 3 * 365 * DAY);52 const saleRows = (await db()53 .select({ id: sales.id, variantId: sales.variantId, sourceId: sales.sourceId, saleDate: sales.saleDate, priceUsd: sales.priceUsd, grader: sales.grader, grade: sales.grade, quantity: sales.quantity, isBundle: sales.isBundle, status: sales.status, confidence: sales.confidence, flags: sales.flags })54 .from(sales)55 .where(and(eq(sales.assetId, assetId), gte(sales.saleDate, since3y)))56 .orderBy(desc(sales.saleDate))) as SaleRow[];57 const allSalesCount = (await db().select({ n: sql<number>`count(*)::int`, min: sql<number>`min(${sales.priceUsd})`, max: sql<number>`max(${sales.priceUsd})`, minAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} asc))[1]`, maxAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} desc))[1]` }).from(sales).where(and(eq(sales.assetId, assetId), eq(sales.status, 'valid'))))[0]!;5859 // 1. outliers per variant (flag only new ones; never delete)60 const flagsToApply: Array<{ id: string; reason: string; score: number }> = [];61 const byVariant = new Map<string, SaleRow[]>();62 for (const s of saleRows) byVariant.set(s.variantId ?? '', [...(byVariant.get(s.variantId ?? '') ?? []), s]);63 for (const list of byVariant.values()) {64 const valid = list.filter((s) => s.status === 'valid');65 for (const f of detectOutliers(valid.map((s) => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, quantity: s.quantity, isBundle: s.isBundle })))) flagsToApply.push(f);66 }67 if (flagsToApply.length) {68 for (const f of flagsToApply) {69 await db().update(sales).set({ status: 'flagged', flags: sql`array_append(${sales.flags}, ${f.reason})` }).where(and(eq(sales.id, f.id), eq(sales.status, 'valid')));70 const row = saleRows.find((s) => s.id === f.id);71 if (row) row.status = 'flagged';72 }73 await auditMany(flagsToApply.map((f) => ({ entityType: 'sale', entityId: f.id, action: 'flagged', reason: f.reason, details: { modifiedZ: f.score, assetId } })));74 }7576 // 2. observations (guide prices) last 90d per variant77 const obsRows = await db()78 .select({ variantId: priceObservations.variantId, priceUsd: priceObservations.priceUsd, date: priceObservations.observationDate, priceKind: priceObservations.priceKind, sourceId: priceObservations.sourceId })79 .from(priceObservations)80 .where(and(eq(priceObservations.assetId, assetId), gte(priceObservations.observationDate, toDateOnly(new Date(now.getTime() - 90 * DAY)))));81 const obsByVariant = new Map<string, typeof obsRows>();82 for (const o of obsRows) obsByVariant.set(o.variantId ?? '', [...(obsByVariant.get(o.variantId ?? '') ?? []), o]);8384 const premiums = await premiumsFor(asset.categorySlug);85 const toInput = (s: SaleRow): SaleInput => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, trust: trust.get(s.sourceId) ?? 0.6, confidence: Number(s.confidence), grader: s.grader, grade: s.grade, quantity: s.quantity, isBundle: s.isBundle, status: s.status as SaleInput['status'] });8687 // 3. per-variant valuations88 const results: Array<{ variantId: string; out: ReturnType<typeof computeValuation>; salesCount: number; sales30d: number }> = [];89 const today = toDateOnly(now);90 for (const v of variants) {91 const own = (byVariant.get(v.id) ?? []).map(toInput);92 const comps: NonNullable<Parameters<typeof computeValuation>[0]['comps']> = [];93 if (own.filter((s) => (s.status ?? 'valid') === 'valid').length < 3 && premiums.length) {94 for (const other of variants) {95 if (other.id === v.id) continue;96 const adj = adjustmentFactor(premiums, { grader: other.grader, grade: other.grade }, { grader: v.grader, grade: v.grade });97 if (adj === null) continue;98 for (const s of byVariant.get(other.id) ?? []) if (s.status === 'valid') comps.push({ priceUsd: Number(s.priceUsd), date: s.saleDate, adjustment: adj, trust: trust.get(s.sourceId) });99 }100 }101 const out = computeValuation({102 sales: own,103 comps,104 observations: (obsByVariant.get(v.id) ?? []).map((o) => ({ priceUsd: Number(o.priceUsd), date: new Date(`${o.date}T00:00:00Z`), priceKind: o.priceKind, trust: trust.get(o.sourceId) })),105 now,106 categorySlug: asset.categorySlug,107 });108 const salesCount = own.filter((s) => (s.status ?? 'valid') === 'valid').length;109 const sales30d = own.filter((s) => (s.status ?? 'valid') === 'valid' && now.getTime() - s.date.getTime() <= 30 * DAY).length;110 results.push({ variantId: v.id, out, salesCount, sales30d });111 if (out.riv === null && salesCount === 0 && (obsByVariant.get(v.id) ?? []).length === 0) continue;112 await db().insert(valuations).values({113 id: newId('valuation'),114 assetId,115 variantId: v.id,116 computedAt: now,117 rivUsd: out.riv,118 lowUsd: out.low,119 highUsd: out.high,120 confidence: out.confidence,121 confidenceLabel: out.label,122 sampleSize: out.sampleSize,123 windowDays: out.windowDays,124 methods: out.methods,125 salesUsed: out.salesUsed,126 observationsUsed: out.observationsUsed,127 method: `ensemble_v1:${out.basis}`,128 notes: out.notes,129 });130 const prev30 = await snapshotValue(assetId, v.id, new Date(now.getTime() - 30 * DAY));131 const prev1y = await snapshotValue(assetId, v.id, new Date(now.getTime() - 365 * DAY));132 const activeL = await activeListingStats(assetId, v.id);133 const latest = own[0];134 await db()135 .insert(variantStats)136 .values({ variantId: v.id, assetId, rivUsd: out.riv, rivLowUsd: out.low, rivHighUsd: out.high, rivConfidence: out.confidence, rivSampleSize: out.sampleSize, latestSaleUsd: latest?.priceUsd ?? null, latestSaleAt: latest?.date ?? null, change30d: pctChange(prev30, out.riv), change1y: pctChange(prev1y, out.riv), salesCount, sales30d, activeListings: activeL.count, minAskUsd: activeL.minAsk, liquidityScore: liquidityScore({ salesPerMonth: (own.length / 36) || 0, activeListings: activeL.count, sources: new Set((byVariant.get(v.id) ?? []).map((s) => s.sourceId)).size, medianDaysBetweenSales: medianGapDays(own.map((s) => s.date)), askSoldSpread: activeL.minAsk && out.riv ? (activeL.minAsk - out.riv) / out.riv : null }), updatedAt: now })137 .onConflictDoUpdate({ target: variantStats.variantId, set: { rivUsd: out.riv, rivLowUsd: out.low, rivHighUsd: out.high, rivConfidence: out.confidence, rivSampleSize: out.sampleSize, latestSaleUsd: latest?.priceUsd ?? null, latestSaleAt: latest?.date ?? null, change30d: pctChange(prev30, out.riv), change1y: pctChange(prev1y, out.riv), salesCount, sales30d, activeListings: activeL.count, minAskUsd: activeL.minAsk, updatedAt: now } });138 await db()139 .insert(priceSnapshots)140 .values({ assetId, variantId: v.id, date: today, rivUsd: out.riv, latestSaleUsd: latest?.priceUsd ?? null, medianUsd: out.distribution.median, salesCount: own.filter((s) => toDateOnly(s.date) === today).length, volumeUsd: own.filter((s) => toDateOnly(s.date) === today).reduce((a, s) => a + s.priceUsd, 0) || null, listingsCount: activeL.count, minAskUsd: activeL.minAsk, observationUsd: out.methods.guide })141 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: out.riv, latestSaleUsd: latest?.priceUsd ?? null, medianUsd: out.distribution.median, listingsCount: activeL.count, minAskUsd: activeL.minAsk, observationUsd: out.methods.guide } });142 // value opportunity on active listings of this variant143 if (out.riv !== null && out.confidence >= 0.5) {144 await db()145 .update(listings)146 .set({ discountToRiv: sql`case when ${listings.priceUsd} is null then null else round((${listings.priceUsd} - ${out.riv}) / ${out.riv}, 4) end` })147 .where(and(eq(listings.variantId, v.id), eq(listings.availability, 'available')));148 }149 }150151 // 4. asset-level: representative variant = highest confidence, then most sales152 const priced = results.filter((r) => r.out.riv !== null).sort((a, b) => b.out.confidence - a.out.confidence || b.salesCount - a.salesCount);153 const rep = priced[0] ?? null;154 const validSales = saleRows.filter((s) => s.status === 'valid');155 const latest = validSales[0];156 const prevAsset = { d1: await snapshotValue(assetId, '', new Date(now.getTime() - 1 * DAY)), d7: await snapshotValue(assetId, '', new Date(now.getTime() - 7 * DAY)), d30: await snapshotValue(assetId, '', new Date(now.getTime() - 30 * DAY)), d90: await snapshotValue(assetId, '', new Date(now.getTime() - 90 * DAY)), y1: await snapshotValue(assetId, '', new Date(now.getTime() - 365 * DAY)) };157 const riv = rep?.out.riv ?? null;158 const activeAll = await activeListingStats(assetId, null);159 const salesCount = allSalesCount.n;160 const sales30d = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).length;161 const sales1y = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 365 * DAY).length;162 const salesPrev30 = validSales.filter((s) => { const age = now.getTime() - s.saleDate.getTime(); return age > 30 * DAY && age <= 60 * DAY; }).length;163 const pop = await latestPopulation(assetId);164 const [obsCount] = await db().select({ n: sql<number>`count(*)::int` }).from(priceObservations).where(eq(priceObservations.assetId, assetId));165 const sourcesCount = new Set([...saleRows.map((s) => s.sourceId), ...obsRows.map((o) => o.sourceId)]).size;166 const liq = liquidityScore({ salesPerMonth: sales1y / 12, activeListings: activeAll.count, sources: sourcesCount, medianDaysBetweenSales: medianGapDays(validSales.map((s) => s.saleDate)), askSoldSpread: activeAll.minAsk && riv ? (activeAll.minAsk - riv) / riv : null });167 const rar = rarityScore({ population: pop?.total ?? null, productionQuantity: asset.productionQuantity, listingsPerYear: activeAll.count > 0 || sales1y > 0 ? activeAll.count * 4 : null, salesPerYear: sales1y > 0 || salesCount > 0 ? sales1y : null, populationGrowthPct: pop?.growth ?? null });168 const ch = { d1: pctChange(prevAsset.d1, riv), d7: pctChange(prevAsset.d7, riv), d30: pctChange(prevAsset.d30, riv), d90: pctChange(prevAsset.d90, riv), y1: pctChange(prevAsset.y1, riv) };169 const mom = { m7: momentumScore({ priceChange: ch.d7, volumeNow: validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 7 * DAY).length, volumePrev: validSales.filter((s) => { const a = now.getTime() - s.saleDate.getTime(); return a > 7 * DAY && a <= 14 * DAY; }).length }), m30: momentumScore({ priceChange: ch.d30, volumeNow: sales30d, volumePrev: salesPrev30 }), m90: momentumScore({ priceChange: ch.d90, volumeNow: validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 90 * DAY).length, volumePrev: validSales.filter((s) => { const a = now.getTime() - s.saleDate.getTime(); return a > 90 * DAY && a <= 180 * DAY; }).length }), y1: momentumScore({ priceChange: ch.y1, volumeNow: sales1y, volumePrev: validSales.length - sales1y }) };170 const listingMomentum = activeAll.count > 0 ? Math.min(100, activeAll.count * 5) : null;171 const trending = trendingScore({ priceMomentum: mom.m30, volumeMomentum: momentumScore({ priceChange: null, volumeNow: sales30d, volumePrev: salesPrev30 }), searchMomentum: null, listingMomentum, newsMomentum: null });172 const [bestListing] = await db().select({ d: sql<number>`min(${listings.discountToRiv})` }).from(listings).where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available')));173 const fields = { brand: asset.brand, set: asset.setName, number: asset.number, year: asset.year, variant: asset.variant, image: asset.heroImageUrl, description: asset.description, identifiers: Object.keys(asset.identifiers).length ? 1 : null };174 const dq = assetDataQuality({ fieldsPresent: Object.values(fields).filter((x) => x !== null && x !== undefined).length, fieldsTotal: Object.keys(fields).length, sourceTrustAvg: sourcesCount ? median([...new Set(saleRows.map((s) => s.sourceId))].map((s) => trust.get(s) ?? 0.5)) : null, identificationConfidence: rep ? Number(median(validSales.map((s) => Number(s.confidence))) ?? 0.8) : null, hasImage: Boolean(asset.heroImageUrl), salesCount });175 const statsRow = {176 assetId,177 rivUsd: riv,178 rivLowUsd: rep?.out.low ?? null,179 rivHighUsd: rep?.out.high ?? null,180 rivConfidence: rep?.out.confidence ?? null,181 rivSampleSize: rep?.out.sampleSize ?? 0,182 rivVariantId: rep?.variantId ?? null,183 latestSaleUsd: latest ? Number(latest.priceUsd) : null,184 latestSaleAt: latest?.saleDate ?? null,185 change1d: ch.d1,186 change7d: ch.d7,187 change30d: ch.d30,188 change90d: ch.d90,189 change1y: ch.y1,190 athUsd: salesCount ? Number(allSalesCount.max) : null,191 athAt: salesCount ? asDate(allSalesCount.maxAt) : null,192 atlUsd: salesCount ? Number(allSalesCount.min) : null,193 atlAt: salesCount ? asDate(allSalesCount.minAt) : null,194 salesCount,195 sales30d,196 sales1y,197 volume30dUsd: sales30d ? validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).reduce((a, s) => a + Number(s.priceUsd), 0) : null,198 activeListings: activeAll.count,199 minAskUsd: activeAll.minAsk,200 observationsCount: obsCount?.n ?? 0,201 sourcesCount,202 liquidityScore: liq,203 rarityScore: rar,204 momentum7d: mom.m7,205 momentum30d: mom.m30,206 momentum90d: mom.m90,207 momentum1y: mom.y1,208 trendingScore: trending,209 valueOpportunity: bestListing?.d !== null && bestListing?.d !== undefined ? Number(bestListing.d) : (riv && activeAll.minAsk ? valueOpportunity(activeAll.minAsk, riv, rep?.out.confidence ?? null) : null),210 dataQuality: dq,211 updatedAt: now,212 };213 await db().insert(assetStats).values(statsRow).onConflictDoUpdate({ target: assetStats.assetId, set: { ...statsRow, watchers: sql`${assetStats.watchers}`, views30d: sql`${assetStats.views30d}` } });214 await db().update(assets).set({ dataQuality: dq, updatedAt: now }).where(eq(assets.id, assetId));215 await db()216 .insert(priceSnapshots)217 .values({ assetId, variantId: '', date: today, rivUsd: riv, latestSaleUsd: latest ? Number(latest.priceUsd) : null, medianUsd: rep?.out.distribution.median ?? null, salesCount: validSales.filter((s) => toDateOnly(s.saleDate) === today).length, volumeUsd: null, listingsCount: activeAll.count, minAskUsd: activeAll.minAsk, observationUsd: rep?.out.methods.guide ?? null })218 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: riv, latestSaleUsd: latest ? Number(latest.priceUsd) : null, listingsCount: activeAll.count, minAskUsd: activeAll.minAsk } });219 if (opts.rebuildHistory) await rebuildSnapshotHistory(assetId, saleRows, variants.map((v) => v.id), now);220 if (riv !== null) await emit('valuation_updated', { type: 'asset', id: assetId }, { riv, confidence: rep?.out.confidence, sampleSize: rep?.out.sampleSize });221 return { variants: results.length, riv };222}223224async function snapshotValue(assetId: string, variantId: string, at: Date): Promise<number | null> {225 const [row] = await db()226 .select({ v: priceSnapshots.rivUsd })227 .from(priceSnapshots)228 .where(and(eq(priceSnapshots.assetId, assetId), eq(priceSnapshots.variantId, variantId), sql`${priceSnapshots.date} <= ${toDateOnly(at)}`, sql`${priceSnapshots.rivUsd} is not null`))229 .orderBy(desc(priceSnapshots.date))230 .limit(1);231 return row?.v === null || row?.v === undefined ? null : Number(row.v);232}233234async function activeListingStats(assetId: string, variantId: string | null): Promise<{ count: number; minAsk: number | null }> {235 const [row] = await db()236 .select({ n: sql<number>`count(*)::int`, min: sql<number | null>`min(${listings.priceUsd})` })237 .from(listings)238 .where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), variantId ? eq(listings.variantId, variantId) : undefined));239 return { count: row?.n ?? 0, minAsk: row?.min === null || row?.min === undefined ? null : Number(row.min) };240}241242async function latestPopulation(assetId: string): Promise<{ total: number; growth: number | null } | null> {243 const rows = await db().select({ total: populationReports.total, date: populationReports.reportDate }).from(populationReports).where(eq(populationReports.assetId, assetId)).orderBy(desc(populationReports.reportDate)).limit(2);244 if (!rows.length) return null;245 const growth = rows.length === 2 && rows[1]!.total > 0 ? (rows[0]!.total - rows[1]!.total) / rows[1]!.total : null;246 return { total: rows[0]!.total, growth };247}248249function medianGapDays(dates: Date[]): number | null {250 if (dates.length < 2) return null;251 const s = [...dates].sort((a, b) => a.getTime() - b.getTime());252 const gaps: number[] = [];253 for (let i = 1; i < s.length; i++) gaps.push((s[i]!.getTime() - s[i - 1]!.getTime()) / DAY);254 return median(gaps);255}256257/**258 * Historical snapshots (weekly) reconstructed from transactions only, computing the valuation259 * as of each week with the sales known up to that date. Gives asset charts and index history a260 * past without inventing observations.261 */262export async function rebuildSnapshotHistory(assetId: string, saleRows: SaleRow[], variantIds: string[], now: Date): Promise<number> {263 const valid = saleRows.filter((s) => s.status === 'valid').sort((a, b) => a.saleDate.getTime() - b.saleDate.getTime());264 if (valid.length < 2) return 0;265 const start = valid[0]!.saleDate.getTime();266 const rows: Array<typeof priceSnapshots.$inferInsert> = [];267 const trust = await trustMap();268 for (let t = start + 7 * DAY; t < now.getTime() - DAY; t += 7 * DAY) {269 const asOf = new Date(t);270 const date = toDateOnly(asOf);271 const upTo = valid.filter((s) => s.saleDate.getTime() <= t);272 // asset-level: representative = variant with most sales up to date273 const counts = new Map<string, number>();274 for (const s of upTo) counts.set(s.variantId ?? '', (counts.get(s.variantId ?? '') ?? 0) + 1);275 for (const vid of [...variantIds, '']) {276 const subset = vid === '' ? upTo.filter((s) => (s.variantId ?? '') === [...counts.entries()].sort((a, b) => b[1] - a[1])[0]?.[0]) : upTo.filter((s) => s.variantId === vid);277 if (subset.length < 2) continue;278 const out = computeValuation({ sales: subset.map((s) => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, trust: trust.get(s.sourceId) ?? 0.6, confidence: Number(s.confidence) })), now: asOf });279 if (out.riv === null) continue;280 const weekSales = subset.filter((s) => t - s.saleDate.getTime() < 7 * DAY);281 rows.push({ assetId, variantId: vid, date, rivUsd: out.riv, latestSaleUsd: subset.at(-1)!.priceUsd, medianUsd: out.distribution.median, salesCount: weekSales.length, volumeUsd: weekSales.reduce((a, s) => a + Number(s.priceUsd), 0) || null, listingsCount: 0, minAskUsd: null, observationUsd: null });282 }283 }284 for (let i = 0; i < rows.length; i += 500) {285 await db()286 .insert(priceSnapshots)287 .values(rows.slice(i, i + 500))288 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: sql`excluded.riv_usd`, medianUsd: sql`excluded.median_usd`, latestSaleUsd: sql`excluded.latest_sale_usd`, salesCount: sql`excluded.sales_count`, volumeUsd: sql`excluded.volume_usd` } });289 }290 return rows.length;291}292293/** Assets whose evidence changed since their last stats update (or all). */294export async function assetsNeedingValuation(opts: { all?: boolean; limit?: number } = {}): Promise<string[]> {295 const limit = opts.limit ?? 5000;296 if (opts.all) {297 const rows = await db().select({ id: assets.id }).from(assets).orderBy(assets.createdAt).limit(limit);298 return rows.map((r) => r.id);299 }300 const rows = await db().execute(sql`301 with touched as (302 select asset_id, max(created_at) as t from sales group by asset_id303 union all select asset_id, max(updated_at) from listings group by asset_id304 union all select asset_id, max(created_at) from price_observations group by asset_id305 ), agg as (select asset_id, max(t) as t from touched group by asset_id)306 select a.asset_id from agg a left join asset_stats s on s.asset_id = a.asset_id307 where s.asset_id is null or s.updated_at < a.t308 limit ${limit}`);309 return (rows as unknown as Array<{ asset_id: string }>).map((r) => r.asset_id);310}311312export async function valueMany(ids: string[], opts: { rebuildHistory?: boolean; concurrency?: number } = {}): Promise<{ valued: number; priced: number }> {313 let valued = 0;314 let priced = 0;315 const conc = opts.concurrency ?? 4;316 for (let i = 0; i < ids.length; i += conc) {317 const chunk = ids.slice(i, i + conc);318 const res = await Promise.all(chunk.map((id) => valueAsset(id, { rebuildHistory: opts.rebuildHistory }).catch((err) => { log.error({ err, id }, 'valueAsset failed'); return null; })));319 for (const r of res) {320 if (!r) continue;321 valued++;322 if (r.riv !== null) priced++;323 }324 if ((i / conc) % 50 === 0 && i > 0) log.info({ done: i, total: ids.length, priced }, 'valuation progress');325 }326 return { valued, priced };327}328329/** Empirical grade premiums per category from sales in the last 2 years (§118). */330export async function computePremiums(categorySlug?: string): Promise<number> {331 const since = new Date(Date.now() - 2 * 365 * DAY);332 const cats = categorySlug ? [categorySlug] : (await db().selectDistinct({ c: assets.categorySlug }).from(assets)).map((r) => r.c);333 let written = 0;334 for (const cat of cats) {335 const rows = await db()336 .select({ assetId: sales.assetId, grader: sales.grader, grade: sales.grade, priceUsd: sales.priceUsd })337 .from(sales)338 .innerJoin(assets, eq(assets.id, sales.assetId))339 .where(and(eq(assets.categorySlug, cat), eq(sales.status, 'valid'), gte(sales.saleDate, since)));340 const prem = computeGradePremiums(rows.map((r) => ({ assetId: r.assetId, grader: r.grader, grade: r.grade, priceUsd: Number(r.priceUsd) })));341 for (const p of prem) {342 await db()343 .insert(gradePremiums)344 .values({ id: newId('valuation'), categorySlug: cat, grader: p.grader, grade: p.grade, marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() })345 .onConflictDoUpdate({ target: [gradePremiums.categorySlug, gradePremiums.grader, gradePremiums.grade], set: { marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() } });346 written++;347 }348 premiumCache.delete(cat);349 }350 return written;351}352353export { inArray, isNull, or };354