1import { and, eq, sql } from 'drizzle-orm';2import { assetVariants, assets, brands, sets } from '@rareindex/database';3import { categoryFamily, getCategory, variantKey } from '@rareindex/taxonomy';4import { deterministicId, extractYear, jaccard, newId, normalizeForMatch, slugify, type AssetAttributes, type Grade } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { buildCanonicalKey, composeTitle, deterministicIdentifiers } from './canonical-key.ts';78export type MatchMethod = 'identifier' | 'canonical_key' | 'fuzzy' | 'created';910export interface Resolution {11 assetId: string;12 method: MatchMethod;13 confidence: number;14 created: boolean;15 canonicalKey: string;16}1718const keyCache = new Map<string, string>();19const MAX_CACHE = 200_000;20function remember(key: string, id: string) {21 if (keyCache.size > MAX_CACHE) keyCache.clear();22 keyCache.set(key, id);23}2425const REFERENCE_FAMILIES = new Set(['watches', 'sneakers', 'lego', 'cameras', 'audio_equipment']);2627/**28 * Score a fuzzy candidate against attributes: 0 when a hard discriminator disagrees29 * (card number, reference/model number, year, set). When only one side carries a discriminator30 * the match is penalised; for reference-driven families (watches, sneakers, LEGO) it is refused —31 * a wrong merge is worse than a duplicate asset (data quality first).32 */33export function fuzzyCandidateScore(cand: { title: string; number: string | null; year: number | null; setName: string | null; reference?: string | null }, a: AssetAttributes, trgm: number): number {34 const norm = (s: string | null | undefined) => (s ? s.replace(/^#/, '').toLowerCase().replace(/[\s.\-/]+/g, '') : '');35 const numA = norm(a.number);36 const numC = norm(cand.number);37 if (numA && numC && numA !== numC) return 0;38 const refA = norm(a.reference ?? a.identifiers?.style_code ?? a.identifiers?.lego_set_number);39 const refC = norm(cand.reference);40 if (refA && refC && refA !== refC) return 0;41 const family = categoryFamily(a.categorySlug);42 if (REFERENCE_FAMILIES.has(family) && (refA ? !refC : refC)) return 0;43 if (a.year && cand.year && Math.abs(a.year - cand.year) > 1) return 0;44 if (a.set && cand.setName && jaccard(a.set, cand.setName) < 0.3) return 0;45 let penalty = 1;46 if ((numA && !numC) || (!numA && numC)) penalty *= 0.85;47 if ((refA && !refC) || (!refA && refC)) penalty *= 0.85;48 const j = jaccard(composeTitle(a), cand.title);49 return (Math.max(trgm, 0) * 0.6 + j * 0.4) * penalty;50}5152const WATCH_REF_RE = /\b(?:ref(?:erence)?\.?\s*)([A-Z0-9][A-Z0-9./-]{2,24})/i;53/** Fill reference-like attributes from a raw title when the connector could not (watches). */54export function enrichAttributesFromTitle(a: AssetAttributes, rawTitle: string | null | undefined): AssetAttributes {55 if (!rawTitle) return a;56 if (categoryFamily(a.categorySlug) === 'watches' && !a.reference) {57 const m = rawTitle.match(WATCH_REF_RE);58 if (m) return { ...a, reference: m[1]!.replace(/[.,]$/, '') };59 }60 return a;61}6263/**64 * Resolve attributes to a canonical asset (§112): identifiers → canonical key → fuzzy → create.65 * Existing assets are enriched with missing attributes when `enrich` is set (catalog sources).66 */67export async function resolveAsset(a: AssetAttributes, opts: { enrich?: boolean; imageUrls?: string[]; description?: string | null; allowCreate?: boolean; minFuzzy?: number } = {}): Promise<Resolution | null> {68 const canonicalKey = buildCanonicalKey(a);69 const ids = deterministicIdentifiers(a.identifiers);70 const cached = keyCache.get(canonicalKey);71 if (cached) {72 if (opts.enrich) await enrichAsset(cached, a, opts);73 return { assetId: cached, method: 'canonical_key', confidence: 0.97, created: false, canonicalKey };74 }7576 // 1. deterministic identifiers77 for (const [k, v] of Object.entries(ids)) {78 const [row] = await db()79 .select({ id: assets.id })80 .from(assets)81 .where(and(sql`${assets.identifiers} @> ${JSON.stringify({ [k]: v })}::jsonb`, eq(assets.familySlug, categoryFamily(a.categorySlug))))82 .limit(1);83 if (row) {84 remember(canonicalKey, row.id);85 if (opts.enrich) await enrichAsset(row.id, a, opts);86 return { assetId: row.id, method: 'identifier', confidence: 0.99, created: false, canonicalKey };87 }88 }8990 // 2. canonical key91 const [byKey] = await db().select({ id: assets.id }).from(assets).where(eq(assets.canonicalKey, canonicalKey)).limit(1);92 if (byKey) {93 remember(canonicalKey, byKey.id);94 if (opts.enrich || Object.keys(ids).length) await enrichAsset(byKey.id, a, opts);95 return { assetId: byKey.id, method: 'canonical_key', confidence: 0.96, created: false, canonicalKey };96 }9798 // 3. fuzzy (pg_trgm) within category99 const title = composeTitle(a);100 const minFuzzy = opts.minFuzzy ?? 0.55;101 const candidates = await db()102 .select({ id: assets.id, title: assets.title, number: assets.number, year: assets.year, setName: assets.setName, reference: assets.reference, sim: sql<number>`similarity(${assets.title}, ${title})` })103 .from(assets)104 .where(and(eq(assets.categorySlug, a.categorySlug), sql`similarity(${assets.title}, ${title}) >= ${minFuzzy}`))105 .orderBy(sql`similarity(${assets.title}, ${title}) desc`)106 .limit(5);107 let best: { id: string; score: number } | null = null;108 for (const c of candidates) {109 const score = fuzzyCandidateScore({ title: c.title, number: c.number, year: c.year, setName: c.setName, reference: c.reference }, a, Number(c.sim));110 if (score >= 0.72 && (!best || score > best.score)) best = { id: c.id, score };111 }112 if (best) {113 remember(canonicalKey, best.id);114 if (Object.keys(ids).length) await enrichAsset(best.id, a, opts);115 return { assetId: best.id, method: 'fuzzy', confidence: Math.min(0.9, best.score), created: false, canonicalKey };116 }117118 if (opts.allowCreate === false) return null;119 // 4. create120 const id = await createAsset(a, canonicalKey, title, opts);121 remember(canonicalKey, id);122 return { assetId: id, method: 'created', confidence: 0.9, created: true, canonicalKey };123}124125async function createAsset(a: AssetAttributes, canonicalKey: string, title: string, opts: { imageUrls?: string[]; description?: string | null }): Promise<string> {126 const id = deterministicId('asset', canonicalKey);127 const family = categoryFamily(a.categorySlug);128 const base = slugify(title) || slugify(canonicalKey) || 'asset';129 const slug = `${base}-${id.slice(-6)}`;130 const setSlug = a.set || a.setCode ? await ensureSet(a) : null;131 if (a.brand) await ensureBrand(a.brand, a.categorySlug);132 await db()133 .insert(assets)134 .values({135 id,136 slug,137 canonicalKey,138 categorySlug: a.categorySlug,139 subcategorySlug: a.subcategorySlug,140 familySlug: family,141 franchise: a.franchise,142 brand: a.brand,143 series: a.series,144 setSlug,145 setName: a.set,146 setCode: a.setCode,147 name: a.name,148 title,149 model: a.model,150 reference: a.reference,151 number: a.number,152 year: a.year ?? extractYear(title),153 edition: a.edition,154 variant: a.variant,155 language: a.language,156 region: a.region,157 country: a.country,158 material: a.material,159 size: a.size,160 color: a.color,161 rarity: a.rarity,162 productionQuantity: a.productionQuantity,163 originalMsrp: a.originalMsrp,164 originalMsrpCurrency: a.originalMsrpCurrency,165 description: opts.description ?? null,166 heroImageUrl: opts.imageUrls?.[0] ?? null,167 identifiers: deterministicIdentifiers(a.identifiers),168 metadata: a.metadata ?? {},169 dataQuality: 0,170 })171 .onConflictDoNothing({ target: assets.canonicalKey });172 return id;173}174175/** Fill missing attributes / identifiers on an existing asset (coalesce semantics; never overwrite with null). */176async function enrichAsset(assetId: string, a: AssetAttributes, opts: { imageUrls?: string[]; description?: string | null }): Promise<void> {177 const ids = deterministicIdentifiers(a.identifiers);178 await db()179 .update(assets)180 .set({181 identifiers: Object.keys(ids).length ? sql`${assets.identifiers} || ${JSON.stringify(ids)}::jsonb` : sql`${assets.identifiers}`,182 franchise: sql`coalesce(${assets.franchise}, ${a.franchise ?? null})`,183 brand: sql`coalesce(${assets.brand}, ${a.brand ?? null})`,184 series: sql`coalesce(${assets.series}, ${a.series ?? null})`,185 setName: sql`coalesce(${assets.setName}, ${a.set ?? null})`,186 setCode: sql`coalesce(${assets.setCode}, ${a.setCode ?? null})`,187 number: sql`coalesce(${assets.number}, ${a.number ?? null})`,188 year: sql`coalesce(${assets.year}, ${a.year ?? null})`,189 edition: sql`coalesce(${assets.edition}, ${a.edition ?? null})`,190 variant: sql`coalesce(${assets.variant}, ${a.variant ?? null})`,191 language: sql`coalesce(${assets.language}, ${a.language ?? null})`,192 rarity: sql`coalesce(${assets.rarity}, ${a.rarity ?? null})`,193 reference: sql`coalesce(${assets.reference}, ${a.reference ?? null})`,194 model: sql`coalesce(${assets.model}, ${a.model ?? null})`,195 productionQuantity: sql`coalesce(${assets.productionQuantity}, ${a.productionQuantity ?? null})`,196 originalMsrp: sql`coalesce(${assets.originalMsrp}, ${a.originalMsrp ?? null})`,197 originalMsrpCurrency: sql`coalesce(${assets.originalMsrpCurrency}, ${a.originalMsrpCurrency ?? null})`,198 description: sql`coalesce(${assets.description}, ${opts.description ?? null})`,199 heroImageUrl: sql`coalesce(${assets.heroImageUrl}, ${opts.imageUrls?.[0] ?? null})`,200 metadata: a.metadata && Object.keys(a.metadata).length ? sql`${JSON.stringify(a.metadata)}::jsonb || ${assets.metadata}` : sql`${assets.metadata}`,201 updatedAt: new Date(),202 })203 .where(eq(assets.id, assetId));204}205206const setCache = new Set<string>();207export async function ensureSet(a: AssetAttributes): Promise<string> {208 const name = a.set ?? a.setCode!;209 const slug = slugify(`${a.categorySlug}-${a.setCode ?? name}`);210 if (setCache.has(slug)) return slug;211 await db()212 .insert(sets)213 .values({ slug, name, code: a.setCode ?? null, categorySlug: a.categorySlug, franchiseSlug: a.franchise ? slugify(a.franchise) : null, brandSlug: a.brand ? slugify(a.brand) : null, releaseYear: a.year ?? null, language: a.language ?? null })214 .onConflictDoNothing();215 setCache.add(slug);216 return slug;217}218219const brandCache = new Set<string>();220export async function ensureBrand(name: string, categorySlug: string): Promise<void> {221 const slug = slugify(name);222 if (!slug || brandCache.has(slug)) return;223 await db()224 .insert(brands)225 .values({ slug, name, categorySlugs: [categorySlug] })226 .onConflictDoUpdate({ target: brands.slug, set: { categorySlugs: sql`(select array_agg(distinct x) from unnest(${brands.categorySlugs} || ${sql`ARRAY[${categorySlug}]::text[]`}) x)` } });227 brandCache.add(slug);228}229230const variantCache = new Map<string, string>();231/** Ensure the grade/condition variant row for an asset; returns variant id (§117–§118). */232export async function ensureVariant(assetId: string, grade: Grade, condition: { condition?: string | null; completeness?: string | null }, size?: string | null): Promise<{ id: string; key: string }> {233 const { key, label } = variantKey({ grader: grade.grader, grade: grade.grade, qualifier: grade.qualifier, condition: condition.condition, completeness: condition.completeness, size });234 const cacheKey = `${assetId}|${key}`;235 const hit = variantCache.get(cacheKey);236 if (hit) return { id: hit, key };237 const id = deterministicId('variant', assetId, key);238 await db()239 .insert(assetVariants)240 .values({ id, assetId, variantKey: key, grader: grade.grader && grade.grader !== 'raw' ? grade.grader : null, grade: grade.grade ?? null, qualifier: grade.qualifier ?? null, condition: condition.condition ?? null, completeness: condition.completeness ?? null, sizeLabel: size ?? null, label, isDefault: key === 'raw' })241 .onConflictDoNothing();242 if (variantCache.size > MAX_CACHE) variantCache.clear();243 variantCache.set(cacheKey, id);244 return { id, key };245}246247export function categoryLabel(slug: string): string {248 return getCategory(slug)?.name ?? slug;249}250251export { normalizeForMatch, newId };252