import 'server-only';
import { getDb, sql } from '@rareindex/database';
import { descendants } from '@rareindex/taxonomy';
import type { Identification } from '@rareindex/ai';

export interface Candidate {
  assetId: string;
  slug: string;
  title: string;
  categorySlug: string;
  heroImageUrl: string | null;
  year: number | null;
  setName: string | null;
  number: string | null;
  variant: string | null;
  score: number;
  rivUsd: number | null;
  rivLowUsd: number | null;
  rivHighUsd: number | null;
  rivConfidence: number | null;
  rivSampleSize: number;
  latestSaleUsd: number | null;
  latestSaleAt: string | null;
  salesCount: number;
  activeListings: number;
  minAskUsd: number | null;
}

/**
 * Candidate assets for an identification guess (§114): identifier hits first, then trigram/FTS
 * over titles restricted to the guessed category subtree. Pure DB, no AI.
 */
export async function findCandidates(guess: Pick<Identification, 'categorySlug' | 'name' | 'set' | 'number' | 'year' | 'variant' | 'brand' | 'searchQueries' | 'certificationNumber'>, identifiers: Record<string, string> = {}, limit = 8): Promise<Candidate[]> {
  const db = getDb();
  const scope = guess.categorySlug ? [guess.categorySlug, ...descendants(guess.categorySlug)] : null;
  const scopeSql = scope ? sql`and a.category_slug in (${sql.join(scope.map((s) => sql`${s}`), sql`, `)})` : sql``;
  const found = new Map<string, Candidate>();

  const cols = sql`a.id as "assetId", a.slug, a.title, a.category_slug as "categorySlug", a.hero_image_url as "heroImageUrl", a.year, a.set_name as "setName", a.number, a.variant,
    s.riv_usd as "rivUsd", s.riv_low_usd as "rivLowUsd", s.riv_high_usd as "rivHighUsd", s.riv_confidence as "rivConfidence", coalesce(s.riv_sample_size,0) as "rivSampleSize", s.latest_sale_usd as "latestSaleUsd", s.latest_sale_at as "latestSaleAt", coalesce(s.sales_count,0) as "salesCount", coalesce(s.active_listings,0) as "activeListings", s.min_ask_usd as "minAskUsd"`;

  // 1) deterministic identifiers
  const idEntries = Object.entries(identifiers).filter(([, v]) => v);
  for (const [k, v] of idEntries) {
    const rows = (await db.execute(sql`select ${cols}, 1.0 as score from assets a left join asset_stats s on s.asset_id = a.id where a.identifiers ->> ${k} = ${v} limit 3`)) as unknown as Candidate[];
    for (const r of rows) found.set(r.assetId, { ...r, score: 1 });
  }

  // 2) text queries: model-proposed search strings + a composed one
  const composed = [guess.year, guess.brand, guess.set, guess.name, guess.number, guess.variant].filter(Boolean).join(' ');
  const queries = [...new Set([composed, ...(guess.searchQueries ?? [])].map((q) => q.trim()).filter((q) => q.length >= 3))].slice(0, 4);
  for (const q of queries) {
    const tsq = q
      .split(/\s+/)
      .map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, ''))
      .filter(Boolean)
      .map((t) => `${t}:*`)
      .join(' | ');
    const rows = (await db.execute(sql`select ${cols},
        (similarity(a.title, ${q}) * 0.6 + coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})), 0) * 0.4
          + case when ${guess.number ?? null}::text is not null and a.number = ${guess.number ?? null}::text then 0.25 else 0 end
          + case when ${guess.year ?? null}::int is not null and a.year = ${guess.year ?? null}::int then 0.1 else 0 end) as score
      from assets a left join asset_stats s on s.asset_id = a.id
      where (a.title % ${q} or a.search @@ to_tsquery('simple', ${tsq})) ${scopeSql}
      order by score desc limit ${limit}`)) as unknown as Candidate[];
    for (const r of rows) {
      const prev = found.get(r.assetId);
      if (!prev || prev.score < Number(r.score)) found.set(r.assetId, { ...r, score: Number(r.score) });
    }
  }
  return [...found.values()].sort((a, b) => b.score - a.score).slice(0, limit);
}

export async function assetContext(assetId: string) {
  const db = getDb();
  const sales = (await db.execute(sql`select id, sale_date as "saleDate", price, currency, price_usd as "priceUsd", grader, grade, source_id as "sourceId", source_url as "sourceUrl", raw_title as "rawTitle" from sales where asset_id = ${assetId} and status = 'valid' order by sale_date desc limit 8`)) as unknown as Array<Record<string, unknown>>;
  const listings = (await db.execute(sql`select id, price, currency, price_usd as "priceUsd", source_id as "sourceId", source_url as "sourceUrl", grader, grade, condition, raw_title as "rawTitle", discount_to_riv as "discountToRiv" from listings where asset_id = ${assetId} and availability = 'available' order by price_usd asc nulls last limit 8`)) as unknown as Array<Record<string, unknown>>;
  const variants = (await db.execute(sql`select v.id, v.label, v.grader, v.grade, vs.riv_usd as "rivUsd", vs.riv_confidence as "rivConfidence", vs.riv_sample_size as "rivSampleSize", vs.sales_count as "salesCount" from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${assetId} order by vs.sales_count desc nulls last limit 12`)) as unknown as Array<Record<string, unknown>>;
  return { sales, listings, variants };
}