import { sql } from 'drizzle-orm';
import { getDb } from '@rareindex/database';
import { descendants, getCategory, CATEGORIES } from '@rareindex/taxonomy';
import { parseQuery, toTsQuery, type ParsedQuery } from './parse.js';

export { parseQuery, toTsQuery, type ParsedQuery };

/**
 * Hybrid asset search (§138): lexical (tsvector, weighted A/B/C) + trigram similarity on the title
 * + popularity (sales count, watchers, observations) + recency, with structured filters parsed
 * from the natural-language query. Runs entirely in PostgreSQL; vector re-ranking can be added
 * on top when `asset_embeddings` is populated.
 */
export interface SearchOptions {
  limit?: number;
  offset?: number;
  categorySlug?: string | null;
  grader?: string | null;
  grade?: string | null;
  priceMinUsd?: number | null;
  priceMaxUsd?: number | null;
  yearFrom?: number | null;
  yearTo?: number | null;
}

export interface SearchHit {
  id: string;
  slug: string;
  title: string;
  name: string;
  categorySlug: string;
  familySlug: string;
  setName: string | null;
  number: string | null;
  year: number | null;
  variant: string | null;
  heroImageUrl: string | null;
  rivUsd: number | null;
  rivConfidence: number | null;
  latestSaleUsd: number | null;
  change30d: number | null;
  salesCount: number;
  liquidityScore: number | null;
  score: number;
}

export interface SearchResult {
  parsed: ParsedQuery;
  hits: SearchHit[];
  total: number;
  tookMs: number;
  categories: Array<{ slug: string; name: string; count: number }>;
}

export async function searchAssets(query: string, opts: SearchOptions = {}): Promise<SearchResult> {
  const t0 = Date.now();
  const parsed = parseQuery(query);
  const limit = Math.min(Math.max(opts.limit ?? 24, 1), 100);
  const offset = Math.max(opts.offset ?? 0, 0);
  const db = getDb();

  const tsq = toTsQuery(parsed.text || parsed.raw);
  const likeText = (parsed.text || parsed.raw).trim();
  const catSlug = opts.categorySlug ?? (parsed.categorySlugs.length === 1 ? parsed.categorySlugs[0]! : null);
  const catSlugs = catSlug ? [catSlug, ...descendants(catSlug)] : parsed.categorySlugs.length > 1 ? parsed.categorySlugs.flatMap((s) => [s, ...descendants(s)]) : null;
  const grader = opts.grader ?? parsed.grader;
  const grade = opts.grade ?? parsed.grade;
  const priceMin = opts.priceMinUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMin : null);
  const priceMax = opts.priceMaxUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMax : null);

  const conditions = [sql`true`];
  // Sets and codes: resolve matching releases first (small table, indexed join on assets.set_slug) so
  // "base set", "neo genesis", "LOB" or "2XM" find every card of the release, not only titles.
  const setSlugs = likeText.length >= 2 ? await matchingSetSlugs(db, likeText) : [];
  const codeToken = codeLike(likeText);
  if (tsq && likeText.length >= 2) {
    const parts = [sql`a.search @@ to_tsquery('simple', ${tsq})`, sql`a.title % ${likeText}`, sql`a.title ILIKE ${'%' + likeText + '%'}`];
    if (setSlugs.length) parts.push(sql`a.set_slug IN ${setSlugs}`);
    if (codeToken) parts.push(sql`(a.number ILIKE ${codeToken} OR a.reference ILIKE ${codeToken} OR a.set_code ILIKE ${codeToken} OR a.identifiers @> ${JSON.stringify({ style_code: likeText })}::jsonb OR a.identifiers @> ${JSON.stringify({ reference: likeText })}::jsonb OR a.identifiers @> ${JSON.stringify({ lego_set_number: likeText })}::jsonb)`);
    conditions.push(sql`(${sql.join(parts, sql` OR `)})`);
  }
  if (catSlugs?.length) conditions.push(sql`a.category_slug IN ${catSlugs}`);
  if (opts.yearFrom) conditions.push(sql`a.year >= ${opts.yearFrom}`);
  if (opts.yearTo) conditions.push(sql`a.year <= ${opts.yearTo}`);
  if (priceMin !== null && priceMin !== undefined) conditions.push(sql`s.riv_usd >= ${priceMin}`);
  if (priceMax !== null && priceMax !== undefined) conditions.push(sql`s.riv_usd <= ${priceMax}`);
  if (grader && grader !== 'raw') {
    conditions.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = a.id AND v.grader = ${grader} ${grade ? sql`AND v.grade = ${grade}` : sql``})`);
  }
  const where = sql.join(conditions, sql` AND `);

  const rank = tsq
    ? sql`(coalesce(ts_rank_cd(a.search, to_tsquery('simple', ${tsq})), 0) * 4 + coalesce(similarity(a.title, ${likeText}), 0) * 3 + ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.observations_count, 0)) * 0.1 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN a.year = ${parsed.year ?? -1} THEN 0.8 ELSE 0 END) + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END) + (CASE WHEN a.hero_image_url IS NOT NULL THEN 0.15 ELSE 0 END) + (CASE WHEN ${codeToken ?? ''} <> '' AND (a.number ILIKE ${codeToken ?? ''} OR a.reference ILIKE ${codeToken ?? ''}) THEN 3 ELSE 0 END))`
    : sql`(ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END))`;

  const rows = (await db.execute(sql`
    SELECT a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.set_name, a.number, a.year, a.variant, a.hero_image_url,
           s.riv_usd, s.riv_confidence, s.latest_sale_usd, s.change_30d, coalesce(s.sales_count, 0) AS sales_count, s.liquidity_score,
           ${rank} AS score, count(*) OVER() AS total
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE ${where}
    ORDER BY score DESC, a.updated_at DESC
    LIMIT ${limit} OFFSET ${offset}
  `)) as unknown as Array<Record<string, unknown>>;

  const hits: SearchHit[] = rows.map((r) => ({
    id: String(r.id),
    slug: String(r.slug),
    title: String(r.title),
    name: String(r.name),
    categorySlug: String(r.category_slug),
    familySlug: String(r.family_slug),
    setName: (r.set_name as string | null) ?? null,
    number: (r.number as string | null) ?? null,
    year: r.year === null ? null : Number(r.year),
    variant: (r.variant as string | null) ?? null,
    heroImageUrl: (r.hero_image_url as string | null) ?? null,
    rivUsd: r.riv_usd === null || r.riv_usd === undefined ? null : Number(r.riv_usd),
    rivConfidence: r.riv_confidence === null || r.riv_confidence === undefined ? null : Number(r.riv_confidence),
    latestSaleUsd: r.latest_sale_usd === null || r.latest_sale_usd === undefined ? null : Number(r.latest_sale_usd),
    change30d: r.change_30d === null || r.change_30d === undefined ? null : Number(r.change_30d),
    salesCount: Number(r.sales_count ?? 0),
    liquidityScore: r.liquidity_score === null || r.liquidity_score === undefined ? null : Number(r.liquidity_score),
    score: Number(r.score ?? 0),
  }));
  const total = rows.length ? Number(rows[0]!.total) : 0;

  // Category facets for the current lexical match (cheap, capped)
  let categories: SearchResult['categories'] = [];
  if (tsq && likeText.length >= 2) {
    const facets = (await db.execute(sql`
      SELECT a.category_slug AS slug, count(*)::int AS count FROM assets a
      WHERE (a.search @@ to_tsquery('simple', ${tsq}) OR a.title % ${likeText})
      GROUP BY a.category_slug ORDER BY count DESC LIMIT 8
    `)) as unknown as Array<{ slug: string; count: number }>;
    categories = facets.map((f) => ({ slug: f.slug, name: getCategory(f.slug)?.name ?? f.slug, count: Number(f.count) }));
  }

  return { parsed, hits, total, tookMs: Date.now() - t0, categories };
}

export interface Suggestion {
  type: 'asset' | 'category' | 'set' | 'brand' | 'index';
  label: string;
  sublabel: string | null;
  href: string;
}

/** Instant suggestions for the header search (prefix / trigram). */
export async function suggest(prefix: string, limit = 8): Promise<Suggestion[]> {
  const q = prefix.trim();
  if (q.length < 2) return [];
  const db = getDb();
  const out: Suggestion[] = [];
  const lower = q.toLowerCase();
  for (const c of CATEGORIES) {
    if (c.name.toLowerCase().startsWith(lower) || c.name.toLowerCase().includes(` ${lower}`)) {
      out.push({ type: 'category', label: c.name, sublabel: c.level === 0 ? 'Category' : `${getCategory(c.familySlug)?.name ?? ''} · Category`, href: `/markets/${c.slug}` });
      if (out.length >= 3) break;
    }
  }
  const tsq = toTsQuery(q);
  const code = codeLike(q);
  const rows = (await db.execute(sql`
    SELECT a.slug, a.title, a.category_slug, s.riv_usd
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE a.title ILIKE ${q + '%'} OR a.title ILIKE ${'% ' + q + '%'} ${tsq ? sql`OR a.search @@ to_tsquery('simple', ${tsq})` : sql``} ${code ? sql`OR a.number ILIKE ${code} OR a.reference ILIKE ${code}` : sql``}
    ORDER BY (a.hero_image_url IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, similarity(a.title, ${q}) DESC
    LIMIT ${limit}
  `)) as unknown as Array<{ slug: string; title: string; category_slug: string; riv_usd: number | null }>;
  for (const r of rows) {
    out.push({ type: 'asset', label: r.title, sublabel: getCategory(r.category_slug)?.name ?? r.category_slug, href: `/asset/${r.slug}` });
  }
  const sets = (await db.execute(sql`SELECT slug, name, code, category_slug FROM sets WHERE name ILIKE ${q + '%'} OR name ILIKE ${'% ' + q + '%'} OR code ILIKE ${q} ORDER BY (code ILIKE ${q}) DESC, name LIMIT 3`)) as unknown as Array<{ slug: string; name: string; code: string | null; category_slug: string }>;
  for (const s of sets) out.push({ type: 'set', label: `${s.name}${s.code ? ` (${s.code})` : ''}`, sublabel: `Set · ${getCategory(s.category_slug)?.name ?? s.category_slug}`, href: `/set/${s.slug}` });
  return out.slice(0, limit + 3);
}

/** A token that looks like a collector number, reference or set code (e.g. "LOB-001", "116500LN", "4/102", "10179"). */
export function codeLike(text: string): string | null {
  const t = text.trim();
  if (!t || t.length > 24 || /\s/.test(t)) return null;
  if (!/\d/.test(t)) return /^[A-Z]{2,6}$/i.test(t) ? t : null; // bare set codes like "LOB", "2XM" handled via sets; short caps allowed
  if (!/^[A-Za-z0-9./#-]+$/.test(t)) return null;
  return t.replace(/^#/, '');
}

/** Set slugs whose name or code matches the text (sets table is small and cached per query). */
export async function matchingSetSlugs(db: ReturnType<typeof getDb>, text: string, limit = 25): Promise<string[]> {
  const t = text.trim();
  if (t.length < 2) return [];
  const rows = (await db.execute(sql`
    SELECT slug FROM sets WHERE code ILIKE ${t} OR name ILIKE ${t} OR name ILIKE ${t + '%'} OR name ILIKE ${'% ' + t + '%'} OR name ILIKE ${'% ' + t}
    ORDER BY (code ILIKE ${t}) DESC, (name ILIKE ${t}) DESC, name LIMIT ${limit}
  `)) as unknown as Array<{ slug: string }>;
  return rows.map((r) => r.slug);
}

export interface SetHit {
  slug: string;
  name: string;
  code: string | null;
  categorySlug: string;
  releaseYear: number | null;
  assets: number;
  thumb: string | null;
}

/** Sets matching a query, with asset counts (grouped results block). */
export async function searchSets(query: string, limit = 8): Promise<SetHit[]> {
  const t = query.trim();
  if (t.length < 2) return [];
  const db = getDb();
  const rows = (await db.execute(sql`
    SELECT st.slug, st.name, st.code, st.category_slug, st.release_year,
      (SELECT count(*) FROM assets a WHERE a.set_slug = st.slug) AS assets,
      (SELECT a.hero_image_url FROM assets a WHERE a.set_slug = st.slug AND a.hero_image_url IS NOT NULL LIMIT 1) AS thumb
    FROM sets st
    WHERE st.code ILIKE ${t} OR st.name ILIKE ${'%' + t + '%'}
    ORDER BY (st.code ILIKE ${t}) DESC, (st.name ILIKE ${t}) DESC, assets DESC LIMIT ${limit}
  `)) as unknown as Array<Record<string, unknown>>;
  return rows.map((r) => ({ slug: String(r.slug), name: String(r.name), code: (r.code as string | null) ?? null, categorySlug: String(r.category_slug), releaseYear: r.release_year == null ? null : Number(r.release_year), assets: Number(r.assets ?? 0), thumb: (r.thumb as string | null) ?? null }));
}