import { and, eq, sql } from 'drizzle-orm';
import { assetVariants, assets, brands, sets } from '@rareindex/database';
import { categoryFamily, getCategory, variantKey } from '@rareindex/taxonomy';
import { deterministicId, extractYear, jaccard, newId, normalizeForMatch, slugify, type AssetAttributes, type Grade } from '@rareindex/shared';
import { db } from '../lib/db.ts';
import { buildCanonicalKey, composeTitle, deterministicIdentifiers } from './canonical-key.ts';

export type MatchMethod = 'identifier' | 'canonical_key' | 'fuzzy' | 'created';

export interface Resolution {
  assetId: string;
  method: MatchMethod;
  confidence: number;
  created: boolean;
  canonicalKey: string;
}

const keyCache = new Map<string, string>();
const MAX_CACHE = 200_000;
function remember(key: string, id: string) {
  if (keyCache.size > MAX_CACHE) keyCache.clear();
  keyCache.set(key, id);
}

const REFERENCE_FAMILIES = new Set(['watches', 'sneakers', 'lego', 'cameras', 'audio_equipment']);

/**
 * Score a fuzzy candidate against attributes: 0 when a hard discriminator disagrees
 * (card number, reference/model number, year, set). When only one side carries a discriminator
 * the match is penalised; for reference-driven families (watches, sneakers, LEGO) it is refused —
 * a wrong merge is worse than a duplicate asset (data quality first).
 */
export function fuzzyCandidateScore(cand: { title: string; number: string | null; year: number | null; setName: string | null; reference?: string | null }, a: AssetAttributes, trgm: number): number {
  const norm = (s: string | null | undefined) => (s ? s.replace(/^#/, '').toLowerCase().replace(/[\s.\-/]+/g, '') : '');
  const numA = norm(a.number);
  const numC = norm(cand.number);
  if (numA && numC && numA !== numC) return 0;
  const refA = norm(a.reference ?? a.identifiers?.style_code ?? a.identifiers?.lego_set_number);
  const refC = norm(cand.reference);
  if (refA && refC && refA !== refC) return 0;
  const family = categoryFamily(a.categorySlug);
  if (REFERENCE_FAMILIES.has(family) && (refA ? !refC : refC)) return 0;
  if (a.year && cand.year && Math.abs(a.year - cand.year) > 1) return 0;
  if (a.set && cand.setName && jaccard(a.set, cand.setName) < 0.3) return 0;
  let penalty = 1;
  if ((numA && !numC) || (!numA && numC)) penalty *= 0.85;
  if ((refA && !refC) || (!refA && refC)) penalty *= 0.85;
  const j = jaccard(composeTitle(a), cand.title);
  return (Math.max(trgm, 0) * 0.6 + j * 0.4) * penalty;
}

const WATCH_REF_RE = /\b(?:ref(?:erence)?\.?\s*)([A-Z0-9][A-Z0-9./-]{2,24})/i;
/** Fill reference-like attributes from a raw title when the connector could not (watches). */
export function enrichAttributesFromTitle(a: AssetAttributes, rawTitle: string | null | undefined): AssetAttributes {
  if (!rawTitle) return a;
  if (categoryFamily(a.categorySlug) === 'watches' && !a.reference) {
    const m = rawTitle.match(WATCH_REF_RE);
    if (m) return { ...a, reference: m[1]!.replace(/[.,]$/, '') };
  }
  return a;
}

/**
 * Resolve attributes to a canonical asset (§112): identifiers → canonical key → fuzzy → create.
 * Existing assets are enriched with missing attributes when `enrich` is set (catalog sources).
 */
export async function resolveAsset(a: AssetAttributes, opts: { enrich?: boolean; imageUrls?: string[]; description?: string | null; allowCreate?: boolean; minFuzzy?: number } = {}): Promise<Resolution | null> {
  const canonicalKey = buildCanonicalKey(a);
  const ids = deterministicIdentifiers(a.identifiers);
  const cached = keyCache.get(canonicalKey);
  if (cached) {
    if (opts.enrich) await enrichAsset(cached, a, opts);
    return { assetId: cached, method: 'canonical_key', confidence: 0.97, created: false, canonicalKey };
  }

  // 1. deterministic identifiers. Some ids are card-level, not printing-level (pokemontcg_id, ygo_id share
  // one id across Holo / 1st Edition / Reverse variants), so a hit must also agree on the variant when both
  // sides state one; otherwise fall through to the canonical key (which includes the variant).
  const normVariant = (x: string | null | undefined) => (x ? x.toLowerCase().replace(/[\s.\-/]+/g, '') : '');
  const incomingVariant = normVariant(a.variant);
  for (const [k, v] of Object.entries(ids)) {
    const rows = await db()
      .select({ id: assets.id, variant: assets.variant })
      .from(assets)
      .where(and(sql`${assets.identifiers} @> ${JSON.stringify({ [k]: v })}::jsonb`, eq(assets.familySlug, categoryFamily(a.categorySlug))))
      .limit(5);
    const row = rows.find((r) => !incomingVariant || !normVariant(r.variant) || normVariant(r.variant) === incomingVariant);
    if (row) {
      remember(canonicalKey, row.id);
      if (opts.enrich) await enrichAsset(row.id, a, opts);
      return { assetId: row.id, method: 'identifier', confidence: 0.99, created: false, canonicalKey };
    }
  }

  // 2. canonical key
  const [byKey] = await db().select({ id: assets.id }).from(assets).where(eq(assets.canonicalKey, canonicalKey)).limit(1);
  if (byKey) {
    remember(canonicalKey, byKey.id);
    if (opts.enrich || Object.keys(ids).length) await enrichAsset(byKey.id, a, opts);
    return { assetId: byKey.id, method: 'canonical_key', confidence: 0.96, created: false, canonicalKey };
  }

  // 3. fuzzy (pg_trgm) within category
  const title = composeTitle(a);
  const minFuzzy = opts.minFuzzy ?? 0.55;
  const candidates = await db()
    .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})` })
    .from(assets)
    .where(and(eq(assets.categorySlug, a.categorySlug), sql`similarity(${assets.title}, ${title}) >= ${minFuzzy}`))
    .orderBy(sql`similarity(${assets.title}, ${title}) desc`)
    .limit(5);
  let best: { id: string; score: number } | null = null;
  for (const c of candidates) {
    const score = fuzzyCandidateScore({ title: c.title, number: c.number, year: c.year, setName: c.setName, reference: c.reference }, a, Number(c.sim));
    if (score >= 0.72 && (!best || score > best.score)) best = { id: c.id, score };
  }
  if (best) {
    remember(canonicalKey, best.id);
    if (Object.keys(ids).length) await enrichAsset(best.id, a, opts);
    return { assetId: best.id, method: 'fuzzy', confidence: Math.min(0.9, best.score), created: false, canonicalKey };
  }

  if (opts.allowCreate === false) return null;
  // 4. create
  const id = await createAsset(a, canonicalKey, title, opts);
  remember(canonicalKey, id);
  return { assetId: id, method: 'created', confidence: 0.9, created: true, canonicalKey };
}

async function createAsset(a: AssetAttributes, canonicalKey: string, title: string, opts: { imageUrls?: string[]; description?: string | null }): Promise<string> {
  const id = deterministicId('asset', canonicalKey);
  const family = categoryFamily(a.categorySlug);
  const base = slugify(title) || slugify(canonicalKey) || 'asset';
  const slug = `${base}-${id.slice(-6)}`;
  const setSlug = a.set || a.setCode ? await ensureSet(a) : null;
  if (a.brand) await ensureBrand(a.brand, a.categorySlug);
  await db()
    .insert(assets)
    .values({
      id,
      slug,
      canonicalKey,
      categorySlug: a.categorySlug,
      subcategorySlug: a.subcategorySlug,
      familySlug: family,
      franchise: a.franchise,
      brand: a.brand,
      series: a.series,
      setSlug,
      setName: a.set,
      setCode: a.setCode,
      name: a.name,
      title,
      model: a.model,
      reference: a.reference,
      number: a.number,
      year: a.year ?? extractYear(title),
      edition: a.edition,
      variant: a.variant,
      language: a.language,
      region: a.region,
      country: a.country,
      material: a.material,
      size: a.size,
      color: a.color,
      rarity: a.rarity,
      productionQuantity: a.productionQuantity,
      originalMsrp: a.originalMsrp,
      originalMsrpCurrency: a.originalMsrpCurrency,
      description: opts.description ?? null,
      heroImageUrl: opts.imageUrls?.[0] ?? null,
      identifiers: deterministicIdentifiers(a.identifiers),
      metadata: a.metadata ?? {},
      dataQuality: 0,
    })
    .onConflictDoNothing({ target: assets.canonicalKey });
  return id;
}

/** Fill missing attributes / identifiers on an existing asset (coalesce semantics; never overwrite with null). */
async function enrichAsset(assetId: string, a: AssetAttributes, opts: { imageUrls?: string[]; description?: string | null }): Promise<void> {
  const ids = deterministicIdentifiers(a.identifiers);
  await db()
    .update(assets)
    .set({
      identifiers: Object.keys(ids).length ? sql`${assets.identifiers} || ${JSON.stringify(ids)}::jsonb` : sql`${assets.identifiers}`,
      franchise: sql`coalesce(${assets.franchise}, ${a.franchise ?? null})`,
      brand: sql`coalesce(${assets.brand}, ${a.brand ?? null})`,
      series: sql`coalesce(${assets.series}, ${a.series ?? null})`,
      setName: sql`coalesce(${assets.setName}, ${a.set ?? null})`,
      setCode: sql`coalesce(${assets.setCode}, ${a.setCode ?? null})`,
      number: sql`coalesce(${assets.number}, ${a.number ?? null})`,
      year: sql`coalesce(${assets.year}, ${a.year ?? null})`,
      edition: sql`coalesce(${assets.edition}, ${a.edition ?? null})`,
      variant: sql`coalesce(${assets.variant}, ${a.variant ?? null})`,
      language: sql`coalesce(${assets.language}, ${a.language ?? null})`,
      rarity: sql`coalesce(${assets.rarity}, ${a.rarity ?? null})`,
      reference: sql`coalesce(${assets.reference}, ${a.reference ?? null})`,
      model: sql`coalesce(${assets.model}, ${a.model ?? null})`,
      productionQuantity: sql`coalesce(${assets.productionQuantity}, ${a.productionQuantity ?? null})`,
      originalMsrp: sql`coalesce(${assets.originalMsrp}, ${a.originalMsrp ?? null})`,
      originalMsrpCurrency: sql`coalesce(${assets.originalMsrpCurrency}, ${a.originalMsrpCurrency ?? null})`,
      description: sql`coalesce(${assets.description}, ${opts.description ?? null})`,
      heroImageUrl: sql`coalesce(${assets.heroImageUrl}, ${opts.imageUrls?.[0] ?? null})`,
      metadata: a.metadata && Object.keys(a.metadata).length ? sql`${JSON.stringify(a.metadata)}::jsonb || ${assets.metadata}` : sql`${assets.metadata}`,
      updatedAt: new Date(),
    })
    .where(eq(assets.id, assetId));
}

const setCache = new Set<string>();
export async function ensureSet(a: AssetAttributes): Promise<string> {
  const name = a.set ?? a.setCode!;
  const slug = slugify(`${a.categorySlug}-${a.setCode ?? name}`);
  if (setCache.has(slug)) return slug;
  await db()
    .insert(sets)
    .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 })
    .onConflictDoNothing();
  setCache.add(slug);
  return slug;
}

const brandCache = new Set<string>();
export async function ensureBrand(name: string, categorySlug: string): Promise<void> {
  const slug = slugify(name);
  if (!slug || brandCache.has(slug)) return;
  await db()
    .insert(brands)
    .values({ slug, name, categorySlugs: [categorySlug] })
    .onConflictDoUpdate({ target: brands.slug, set: { categorySlugs: sql`(select array_agg(distinct x) from unnest(${brands.categorySlugs} || ${sql`ARRAY[${categorySlug}]::text[]`}) x)` } });
  brandCache.add(slug);
}

const variantCache = new Map<string, string>();
/** Ensure the grade/condition variant row for an asset; returns variant id (§117–§118). */
export async function ensureVariant(assetId: string, grade: Grade, condition: { condition?: string | null; completeness?: string | null }, size?: string | null): Promise<{ id: string; key: string }> {
  const { key, label } = variantKey({ grader: grade.grader, grade: grade.grade, qualifier: grade.qualifier, condition: condition.condition, completeness: condition.completeness, size });
  const cacheKey = `${assetId}|${key}`;
  const hit = variantCache.get(cacheKey);
  if (hit) return { id: hit, key };
  const id = deterministicId('variant', assetId, key);
  await db()
    .insert(assetVariants)
    .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' })
    .onConflictDoNothing();
  if (variantCache.size > MAX_CACHE) variantCache.clear();
  variantCache.set(cacheKey, id);
  return { id, key };
}

export function categoryLabel(slug: string): string {
  return getCategory(slug)?.name ?? slug;
}

export { normalizeForMatch, newId };