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`];
  if (tsq && likeText.length >= 2) {
    conditions.push(sql`(a.search @@ to_tsquery('simple', ${tsq}) OR a.title % ${likeText} OR a.title ILIKE ${'%' + likeText + '%'})`);
  }
  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))`
    : 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 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``}
    ORDER BY 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, category_slug FROM sets WHERE name ILIKE ${q + '%'} ORDER BY name LIMIT 3`)) as unknown as Array<{ slug: string; name: string; category_slug: string }>;
  for (const s of sets) out.push({ type: 'set', label: s.name, sublabel: `Set · ${getCategory(s.category_slug)?.name ?? s.category_slug}`, href: `/set/${s.slug}` });
  return out.slice(0, limit + 3);
}