import 'server-only';
import { cache } from 'react';
import type { SQL } from 'drizzle-orm';
import { descendants } from '@rareindex/taxonomy';
import { rows, one, sql, num, int, str, date, joinAnd, textArray } from './_util';
import { classifySale, type SaleVerification } from '@rareindex/valuation';

export interface AssetCard {
  id: string;
  slug: string;
  title: string;
  name: string;
  categorySlug: string;
  familySlug: string;
  setName: string | null;
  setSlug: string | null;
  number: string | null;
  year: number | null;
  variant: string | null;
  edition: string | null;
  brand: string | null;
  heroImageUrl: string | null;
  rivUsd: number | null;
  rivLowUsd: number | null;
  rivHighUsd: number | null;
  rivConfidence: number | null;
  rivSampleSize: number;
  latestSaleUsd: number | null;
  latestSaleAt: Date | null;
  change1d: number | null;
  change7d: number | null;
  change30d: number | null;
  change1y: number | null;
  salesCount: number;
  sales30d: number;
  activeListings: number;
  minAskUsd: number | null;
  liquidityScore: number | null;
  rarityScore: number | null;
  momentum30d: number | null;
  trendingScore: number | null;
  valueOpportunity: number | null;
  dataQuality: number | null;
  watchers: number;
  observationsCount: number;
  updatedAt: Date | null;
  /** latest price-guide observation (never a transaction); attached on demand by attachGuidePrices() */
  guideUsd?: number | null;
  guideKind?: string | null;
  guideDate?: string | null;
  guideSource?: string | null;
}

export const ASSET_CARD_SELECT = sql`a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.set_name, a.set_slug, a.number, a.year, a.variant, a.edition, a.brand, a.hero_image_url,
  s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, coalesce(s.riv_sample_size, 0) AS riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_1d, s.change_7d, s.change_30d, s.change_1y,
  coalesce(s.sales_count, 0) AS sales_count, coalesce(s.sales_30d, 0) AS sales_30d, coalesce(s.active_listings, 0) AS active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.momentum_30d, s.trending_score, s.value_opportunity, s.data_quality, coalesce(s.watchers, 0) AS watchers, coalesce(s.observations_count, 0) AS observations_count, s.updated_at AS stats_updated_at`;

export function toAssetCard(x: Record<string, unknown>): AssetCard {
  return {
    id: String(x.id),
    slug: String(x.slug),
    title: String(x.title),
    name: String(x.name),
    categorySlug: String(x.category_slug),
    familySlug: String(x.family_slug),
    setName: str(x.set_name),
    setSlug: str(x.set_slug),
    number: str(x.number),
    year: num(x.year),
    variant: str(x.variant),
    edition: str(x.edition),
    brand: str(x.brand),
    heroImageUrl: str(x.hero_image_url),
    rivUsd: num(x.riv_usd),
    rivLowUsd: num(x.riv_low_usd),
    rivHighUsd: num(x.riv_high_usd),
    rivConfidence: num(x.riv_confidence),
    rivSampleSize: int(x.riv_sample_size),
    latestSaleUsd: num(x.latest_sale_usd),
    latestSaleAt: date(x.latest_sale_at),
    change1d: num(x.change_1d),
    change7d: num(x.change_7d),
    change30d: num(x.change_30d),
    change1y: num(x.change_1y),
    salesCount: int(x.sales_count),
    sales30d: int(x.sales_30d),
    activeListings: int(x.active_listings),
    minAskUsd: num(x.min_ask_usd),
    liquidityScore: num(x.liquidity_score),
    rarityScore: num(x.rarity_score),
    momentum30d: num(x.momentum_30d),
    trendingScore: num(x.trending_score),
    valueOpportunity: num(x.value_opportunity),
    dataQuality: num(x.data_quality),
    watchers: int(x.watchers),
    observationsCount: int(x.observations_count),
    updatedAt: date(x.stats_updated_at),
  };
}

export type ExploreSort = 'relevance' | 'riv' | 'change30d' | 'change7d' | 'sales' | 'liquidity' | 'rarity' | 'trending' | 'newest' | 'latest_sale' | 'opportunity' | 'name' | 'number';
export type HasFilter = 'sales' | 'valuation' | 'listings' | 'observations' | 'images';

export interface ExploreFilters {
  category?: string | null;
  grader?: string | null;
  grade?: string | null;
  priceMin?: number | null;
  priceMax?: number | null;
  liquidityMin?: number | null;
  rarityMin?: number | null;
  momentumMin?: number | null;
  yearFrom?: number | null;
  yearTo?: number | null;
  brand?: string | null;
  set?: string | null;
  hasValuation?: boolean;
  has?: HasFilter | null;
  /** explicit list of category slugs (overrides `category` scope) */
  scope?: string[] | null;
  q?: string | null;
  sort?: ExploreSort;
  page?: number;
  pageSize?: number;
}

const SORTS: Record<ExploreSort, SQL> = {
  // Data-rich records first, catalog-only records last — never hidden.
  relevance: sql`(s.riv_usd IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, coalesce(s.observations_count, 0) DESC, (a.hero_image_url IS NOT NULL) DESC, a.created_at DESC`,
  riv: sql`s.riv_usd DESC NULLS LAST`,
  change30d: sql`s.change_30d DESC NULLS LAST`,
  change7d: sql`s.change_7d DESC NULLS LAST`,
  sales: sql`s.sales_count DESC NULLS LAST`,
  liquidity: sql`s.liquidity_score DESC NULLS LAST`,
  rarity: sql`s.rarity_score DESC NULLS LAST`,
  trending: sql`s.trending_score DESC NULLS LAST`,
  newest: sql`a.created_at DESC`,
  latest_sale: sql`s.latest_sale_at DESC NULLS LAST`,
  opportunity: sql`s.value_opportunity ASC NULLS LAST`, // most below RIV first
  name: sql`a.name ASC`,
  number: sql`a.set_name ASC NULLS LAST, (substring(a.number from '^[0-9]+'))::int ASC NULLS LAST, a.number ASC NULLS LAST`,
};

const HAS: Record<HasFilter, SQL> = {
  sales: sql`coalesce(s.sales_count, 0) > 0`,
  valuation: sql`s.riv_usd IS NOT NULL`,
  listings: sql`coalesce(s.active_listings, 0) > 0`,
  observations: sql`coalesce(s.observations_count, 0) > 0 OR EXISTS (SELECT 1 FROM price_observations o WHERE o.asset_id = a.id)`,
  images: sql`a.hero_image_url IS NOT NULL`,
};

export function categoryScope(slug: string): string[] {
  return [slug, ...descendants(slug)];
}

export async function exploreAssets(f: ExploreFilters): Promise<{ items: AssetCard[]; total: number; page: number; pageSize: number }> {
  const pageSize = Math.min(Math.max(f.pageSize ?? 48, 1), 200);
  const page = Math.max(1, f.page ?? 1);
  const where: SQL[] = [];
  if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);
  else if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);
  if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);
  if (f.set) where.push(sql`a.set_slug = ${f.set}`);
  if (f.priceMin != null) where.push(sql`s.riv_usd >= ${f.priceMin}`);
  if (f.priceMax != null) where.push(sql`s.riv_usd <= ${f.priceMax}`);
  if (f.liquidityMin != null) where.push(sql`s.liquidity_score >= ${f.liquidityMin}`);
  if (f.rarityMin != null) where.push(sql`s.rarity_score >= ${f.rarityMin}`);
  if (f.momentumMin != null) where.push(sql`s.momentum_30d >= ${f.momentumMin}`);
  if (f.yearFrom != null) where.push(sql`a.year >= ${f.yearFrom}`);
  if (f.yearTo != null) where.push(sql`a.year <= ${f.yearTo}`);
  if (f.hasValuation) where.push(sql`s.riv_usd IS NOT NULL`);
  if (f.has) where.push(sql`(${HAS[f.has]})`);
  if (f.grader) where.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = a.id AND v.grader = ${f.grader} ${f.grade ? sql`AND v.grade = ${f.grade}` : sql``})`);
  if (f.q && f.q.trim().length >= 2) {
    const q = f.q.trim();
    where.push(sql`(a.title ILIKE ${'%' + q + '%'} OR a.title % ${q} OR a.set_name ILIKE ${'%' + q + '%'} OR a.set_code ILIKE ${q} OR a.number ILIKE ${q} OR a.reference ILIKE ${q})`);
  }
  const order = SORTS[f.sort ?? 'relevance'];
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT}, count(*) OVER() AS total
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE ${joinAnd(where)}
    ORDER BY ${order}, a.title ASC
    LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
  `);
  return { items: r.map(toAssetCard), total: r.length ? int(r[0]!.total) : 0, page, pageSize };
}

/** Ranked lists for home/markets widgets. `scope` restricts to category slugs. */
export type RankedKind = 'trending' | 'gainers' | 'losers' | 'volume' | 'watched' | 'newest' | 'expensive' | 'opportunity' | 'liquid' | 'newest_priced' | 'documented' | 'with_sales';
export async function rankedAssets(kind: RankedKind, opts: { scope?: string[]; limit?: number; window?: '1d' | '7d' | '30d' } = {}): Promise<AssetCard[]> {
  const limit = opts.limit ?? 10;
  const scope = opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``;
  const chg = opts.window === '1d' ? sql`s.change_1d` : opts.window === '30d' ? sql`s.change_30d` : sql`s.change_7d`;
  const spec: Record<typeof kind, { where: SQL; order: SQL }> = {
    // Trending quality gates (§140): canonical identity with a transaction-based valuation, a real
    // 30-day market and a plausible price history — noisy titles with one odd sale must not lead.
    trending: { where: sql`s.trending_score IS NOT NULL AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_30d, 0) >= 2 AND (s.change_30d IS NULL OR abs(s.change_30d) <= 5)`, order: sql`s.trending_score DESC` },
    // Movers need a valuation that can move: ≥ 5 transactions, medium confidence, a plausible move (≤ ±500 %) and
    // at least one sale in the last year (a stale series cannot "move").
    gainers: { where: sql`${chg} IS NOT NULL AND ${chg} > 0 AND ${chg} <= 5 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_1y, 0) >= 3`, order: sql`${chg} DESC` },
    losers: { where: sql`${chg} IS NOT NULL AND ${chg} < 0 AND ${chg} >= -0.99 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5 AND coalesce(s.sales_1y, 0) >= 3`, order: sql`${chg} ASC` },
    volume: { where: sql`s.volume_30d_usd IS NOT NULL AND s.volume_30d_usd > 0`, order: sql`s.volume_30d_usd DESC` },
    watched: { where: sql`s.watchers > 0`, order: sql`s.watchers DESC, s.views_30d DESC` },
    newest: { where: sql`true`, order: sql`a.created_at DESC` },
    expensive: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.riv_usd DESC` },
    // value_opportunity = (best gated ask − RIV) / RIV: negative = below the valuation. A deal is between −10 % and
    // −50 %; deeper discounts are review cases (§174), implausible ratios never reach the table (§84, §196).
    opportunity: { where: sql`s.value_opportunity IS NOT NULL AND s.value_opportunity <= -0.1 AND s.value_opportunity >= -0.5 AND s.riv_sample_size >= 5 AND s.riv_confidence >= 0.5`, order: sql`s.value_opportunity ASC` },
    liquid: { where: sql`s.liquidity_score IS NOT NULL`, order: sql`s.liquidity_score DESC` },
    // Fallback rails used while valuations are still being computed (§192: honest, data-backed)
    newest_priced: { where: sql`s.riv_usd IS NOT NULL`, order: sql`s.updated_at DESC` },
    documented: { where: sql`a.hero_image_url IS NOT NULL AND (coalesce(s.observations_count, 0) > 0 OR coalesce(s.sales_count, 0) > 0)`, order: sql`coalesce(s.sales_count, 0) DESC, coalesce(s.observations_count, 0) DESC, s.updated_at DESC` },
    with_sales: { where: sql`coalesce(s.sales_count, 0) > 0`, order: sql`s.latest_sale_at DESC` },
  };
  const s = spec[kind];
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE ${s.where} ${scope} ORDER BY ${s.order} NULLS LAST LIMIT ${limit}
  `);
  return r.map(toAssetCard);
}

export interface AssetDetail extends AssetCard {
  canonicalKey: string;
  subcategorySlug: string | null;
  franchise: string | null;
  series: string | null;
  setCode: string | null;
  model: string | null;
  reference: string | null;
  language: string | null;
  region: string | null;
  country: string | null;
  material: string | null;
  size: string | null;
  color: string | null;
  rarity: string | null;
  productionQuantity: number | null;
  originalMsrp: number | null;
  originalMsrpCurrency: string | null;
  releaseDate: string | null;
  description: string | null;
  identifiers: Record<string, string>;
  metadata: Record<string, unknown>;
  verified: boolean;
  createdAt: Date;
  athUsd: number | null;
  athAt: Date | null;
  atlUsd: number | null;
  atlAt: Date | null;
  sales1y: number;
  volume30dUsd: number | null;
  sourcesCount: number;
  momentum7d: number | null;
  momentum90d: number | null;
  momentum1y: number | null;
  change90d: number | null;
  rivVariantId: string | null;
}

export const getAssetBySlug = cache(async (slug: string): Promise<AssetDetail | null> => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT}, a.canonical_key, a.subcategory_slug, a.franchise, a.series, a.set_code, a.model, a.reference, a.language, a.region, a.country, a.material, a.size, a.color, a.rarity, a.production_quantity, a.original_msrp, a.original_msrp_currency, a.release_date, a.description, a.identifiers, a.metadata, a.verified, a.created_at,
      s.ath_usd, s.ath_at, s.atl_usd, s.atl_at, coalesce(s.sales_1y, 0) AS sales_1y, s.volume_30d_usd, coalesce(s.sources_count, 0) AS sources_count, s.momentum_7d, s.momentum_90d, s.momentum_1y, s.change_90d, s.riv_variant_id
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug = ${slug} LIMIT 1
  `);
  if (!x) return null;
  return {
    ...toAssetCard(x),
    canonicalKey: String(x.canonical_key),
    subcategorySlug: str(x.subcategory_slug),
    franchise: str(x.franchise),
    series: str(x.series),
    setCode: str(x.set_code),
    model: str(x.model),
    reference: str(x.reference),
    language: str(x.language),
    region: str(x.region),
    country: str(x.country),
    material: str(x.material),
    size: str(x.size),
    color: str(x.color),
    rarity: str(x.rarity),
    productionQuantity: num(x.production_quantity),
    originalMsrp: num(x.original_msrp),
    originalMsrpCurrency: str(x.original_msrp_currency),
    releaseDate: str(x.release_date),
    description: str(x.description),
    identifiers: (x.identifiers as Record<string, string>) ?? {},
    metadata: (x.metadata as Record<string, unknown>) ?? {},
    verified: Boolean(x.verified),
    createdAt: date(x.created_at)!,
    athUsd: num(x.ath_usd),
    athAt: date(x.ath_at),
    atlUsd: num(x.atl_usd),
    atlAt: date(x.atl_at),
    sales1y: int(x.sales_1y),
    volume30dUsd: num(x.volume_30d_usd),
    sourcesCount: int(x.sources_count),
    momentum7d: num(x.momentum_7d),
    momentum90d: num(x.momentum_90d),
    momentum1y: num(x.momentum_1y),
    change90d: num(x.change_90d),
    rivVariantId: str(x.riv_variant_id),
  };
});

export interface VariantRow {
  id: string;
  variantKey: string;
  label: string;
  grader: string | null;
  grade: string | null;
  qualifier: string | null;
  condition: string | null;
  completeness: string | null;
  isDefault: boolean;
  rivUsd: number | null;
  rivLowUsd: number | null;
  rivHighUsd: number | null;
  rivConfidence: number | null;
  rivSampleSize: number;
  latestSaleUsd: number | null;
  latestSaleAt: Date | null;
  change30d: number | null;
  change1y: number | null;
  salesCount: number;
  sales30d: number;
  activeListings: number;
  minAskUsd: number | null;
  liquidityScore: number | null;
}

export const getAssetVariants = cache(async (assetId: string): Promise<VariantRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,
      vs.riv_usd, vs.riv_low_usd, vs.riv_high_usd, vs.riv_confidence, coalesce(vs.riv_sample_size, 0) AS riv_sample_size, vs.latest_sale_usd, vs.latest_sale_at, vs.change_30d, vs.change_1y,
      coalesce(vs.sales_count, 0) AS sales_count, coalesce(vs.sales_30d, 0) AS sales_30d, coalesce(vs.active_listings, 0) AS active_listings, vs.min_ask_usd, vs.liquidity_score
    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, v.is_default DESC, v.label
  `);
  return r.map((x) => ({ id: String(x.id), variantKey: String(x.variant_key), label: String(x.label), grader: str(x.grader), grade: str(x.grade), qualifier: str(x.qualifier), condition: str(x.condition), completeness: str(x.completeness), isDefault: Boolean(x.is_default), rivUsd: num(x.riv_usd), rivLowUsd: num(x.riv_low_usd), rivHighUsd: num(x.riv_high_usd), rivConfidence: num(x.riv_confidence), rivSampleSize: int(x.riv_sample_size), latestSaleUsd: num(x.latest_sale_usd), latestSaleAt: date(x.latest_sale_at), change30d: num(x.change_30d), change1y: num(x.change_1y), salesCount: int(x.sales_count), sales30d: int(x.sales_30d), activeListings: int(x.active_listings), minAskUsd: num(x.min_ask_usd), liquidityScore: num(x.liquidity_score) }));
});

export interface SaleRow {
  id: string;
  saleDate: Date;
  price: number;
  currency: string;
  priceUsd: number;
  saleType: string;
  grader: string | null;
  grade: string | null;
  condition: string | null;
  certificationNumber: string | null;
  sourceId: string;
  sourceName: string;
  sourceUrl: string;
  auctionHouse: string | null;
  location: string | null;
  rawTitle: string;
  imageUrls: string[];
  confidence: number;
  status: string;
  flags: string[];
  variantId: string | null;
  buyerPremiumIncluded: boolean | null;
  /** buyer-pays USD (price + estimated buyer premium when hammer-only), fee basis and rate (§35); null until the fees worker has run */
  allInUsd: number | null;
  feeBasis: string | null;
  buyerPremiumRate: number | null;
  /** source metadata used by the heuristic verification label (§39) */
  sourceType: string | null;
  sourceTrust: number | null;
  verification: SaleVerification;
  verificationReason: string;
  assetSlug?: string;
  assetTitle?: string;
  categorySlug?: string;
  heroImageUrl?: string | null;
}

export function toSaleRow(x: Record<string, unknown>): SaleRow {
  return {
    id: String(x.id),
    saleDate: date(x.sale_date)!,
    price: Number(x.price),
    currency: String(x.currency),
    priceUsd: Number(x.price_usd),
    saleType: String(x.sale_type),
    grader: str(x.grader),
    grade: str(x.grade),
    condition: str(x.condition),
    certificationNumber: str(x.certification_number),
    sourceId: String(x.source_id),
    sourceName: String(x.source_name ?? x.source_id),
    sourceUrl: String(x.source_url),
    auctionHouse: str(x.auction_house),
    location: str(x.location),
    rawTitle: String(x.raw_title),
    imageUrls: (x.image_urls as string[]) ?? [],
    confidence: Number(x.confidence ?? 0),
    status: String(x.status),
    flags: (x.flags as string[]) ?? [],
    variantId: str(x.variant_id),
    buyerPremiumIncluded: x.buyer_premium_included === null || x.buyer_premium_included === undefined ? null : Boolean(x.buyer_premium_included),
    allInUsd: num(x.all_in_usd),
    feeBasis: str(x.fee_basis),
    buyerPremiumRate: num(x.buyer_premium_rate),
    sourceType: str(x.source_type),
    sourceTrust: num(x.trust_score),
    ...(() => {
      const v = classifySale({ status: str(x.status), confidence: num(x.confidence), flags: (x.flags as string[]) ?? [], sourceType: str(x.source_type), saleType: str(x.sale_type), trust: num(x.trust_score) });
      return { verification: v.label, verificationReason: v.reason };
    })(),
    assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,
    assetTitle: x.asset_title ? String(x.asset_title) : undefined,
    categorySlug: x.category_slug ? String(x.category_slug) : undefined,
    heroImageUrl: x.hero_image_url === undefined ? undefined : str(x.hero_image_url),
  };
}

export const SALE_SELECT = sql`s.id, s.sale_date, s.price, s.currency, s.price_usd, s.sale_type, s.grader, s.grade, s.condition, s.certification_number, s.source_id, src.name AS source_name, s.source_url, s.auction_house, s.location, s.raw_title, s.image_urls, s.confidence, s.status, s.flags, s.variant_id, s.buyer_premium_included, s.all_in_usd, s.fee_basis, s.buyer_premium_rate, src.source_type, src.trust_score`;

export async function getAssetSales(assetId: string, opts: { variantId?: string | null; limit?: number; offset?: number; includeFlagged?: boolean } = {}): Promise<{ items: SaleRow[]; total: number }> {
  const limit = opts.limit ?? 50;
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${SALE_SELECT}, count(*) OVER() AS total FROM sales s JOIN sources src ON src.id = s.source_id
    WHERE s.asset_id = ${assetId} ${opts.variantId ? sql`AND s.variant_id = ${opts.variantId}` : sql``} ${opts.includeFlagged ? sql`AND s.status <> 'excluded'` : sql`AND s.status = 'valid'`}
    ORDER BY s.sale_date DESC LIMIT ${limit} OFFSET ${opts.offset ?? 0}
  `);
  return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0 };
}

/** All valid sales (date, usd, variant) for the scatter chart, capped. */
export const getAssetSalePoints = cache(async (assetId: string, limit = 2000): Promise<Array<{ date: string; usd: number; variantId: string | null; grader: string | null; grade: string | null; sourceName: string }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT s.sale_date, s.price_usd, s.variant_id, s.grader, s.grade, src.name AS source_name FROM sales s JOIN sources src ON src.id = s.source_id
    WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT ${limit}
  `);
  return r.map((x) => ({ date: date(x.sale_date)!.toISOString().slice(0, 10), usd: Number(x.price_usd), variantId: str(x.variant_id), grader: str(x.grader), grade: str(x.grade), sourceName: String(x.source_name) })).reverse();
});

export interface ListingRow {
  id: string;
  price: number | null;
  currency: string | null;
  priceUsd: number | null;
  listingType: string;
  seller: string | null;
  location: string | null;
  shippingCost: number | null;
  condition: string | null;
  grader: string | null;
  grade: string | null;
  sourceId: string;
  sourceName: string;
  sourceUrl: string;
  rawTitle: string;
  imageUrls: string[];
  listedAt: Date | null;
  endsAt: Date | null;
  availability: string;
  bidCount: number | null;
  firstSeenAt: Date;
  lastSeenAt: Date;
  discountToRiv: number | null;
  confidence: number;
  flags: string[];
  assetSlug?: string;
  assetTitle?: string;
  categorySlug?: string;
  assetRivUsd?: number | null;
  assetRivConfidence?: number | null;
}

export const LISTING_SELECT = sql`l.id, l.price, l.currency, l.price_usd, l.listing_type, l.seller, l.location, l.shipping_cost, l.condition, l.grader, l.grade, l.source_id, src.name AS source_name, l.source_url, l.raw_title, l.image_urls, l.listed_at, l.ends_at, l.availability, l.bid_count, l.first_seen_at, l.last_seen_at, l.discount_to_riv, l.confidence, l.flags`;

export function toListingRow(x: Record<string, unknown>): ListingRow {
  return {
    id: String(x.id),
    price: num(x.price),
    currency: str(x.currency),
    priceUsd: num(x.price_usd),
    listingType: String(x.listing_type),
    seller: str(x.seller),
    location: str(x.location),
    shippingCost: num(x.shipping_cost),
    condition: str(x.condition),
    grader: str(x.grader),
    grade: str(x.grade),
    sourceId: String(x.source_id),
    sourceName: String(x.source_name ?? x.source_id),
    sourceUrl: String(x.source_url),
    rawTitle: String(x.raw_title),
    imageUrls: (x.image_urls as string[]) ?? [],
    listedAt: date(x.listed_at),
    endsAt: date(x.ends_at),
    availability: String(x.availability),
    bidCount: num(x.bid_count),
    firstSeenAt: date(x.first_seen_at)!,
    lastSeenAt: date(x.last_seen_at)!,
    discountToRiv: num(x.discount_to_riv),
    confidence: Number(x.confidence ?? 0),
    flags: (x.flags as string[]) ?? [],
    assetSlug: x.asset_slug ? String(x.asset_slug) : undefined,
    assetTitle: x.asset_title ? String(x.asset_title) : undefined,
    categorySlug: x.category_slug ? String(x.category_slug) : undefined,
    assetRivUsd: x.asset_riv_usd === undefined ? undefined : num(x.asset_riv_usd),
    assetRivConfidence: x.asset_riv_confidence === undefined ? undefined : num(x.asset_riv_confidence),
  };
}

export async function getAssetListings(assetId: string, opts: { variantId?: string | null; availability?: 'available' | 'all'; limit?: number } = {}): Promise<ListingRow[]> {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${LISTING_SELECT} FROM listings l JOIN sources src ON src.id = l.source_id
    WHERE l.asset_id = ${assetId} ${opts.variantId ? sql`AND l.variant_id = ${opts.variantId}` : sql``} ${opts.availability === 'all' ? sql`` : sql`AND l.availability = 'available'`}
    ORDER BY l.price_usd ASC NULLS LAST LIMIT ${opts.limit ?? 50}
  `);
  return r.map(toListingRow);
}

export interface ObservationRow {
  id: string;
  priceKind: string;
  price: number;
  currency: string;
  priceUsd: number;
  observationDate: string;
  sampleSize: number | null;
  sourceId: string;
  sourceName: string;
  sourceUrl: string;
  variantId: string | null;
}

export const getAssetObservations = cache(async (assetId: string, limit = 200): Promise<ObservationRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT o.id, o.price_kind, o.price, o.currency, o.price_usd, o.observation_date, o.sample_size, o.source_id, src.name AS source_name, o.source_url, o.variant_id
    FROM price_observations o JOIN sources src ON src.id = o.source_id WHERE o.asset_id = ${assetId}
    ORDER BY o.observation_date DESC, o.source_id, o.price_kind LIMIT ${limit}
  `);
  return r.map((x) => ({ id: String(x.id), priceKind: String(x.price_kind), price: Number(x.price), currency: String(x.currency), priceUsd: Number(x.price_usd), observationDate: String(x.observation_date), sampleSize: num(x.sample_size), sourceId: String(x.source_id), sourceName: String(x.source_name), sourceUrl: String(x.source_url), variantId: str(x.variant_id) }));
});

export interface SnapshotPoint {
  date: string;
  rivUsd: number | null;
  latestSaleUsd: number | null;
  medianUsd: number | null;
  salesCount: number;
  volumeUsd: number | null;
  listingsCount: number;
  minAskUsd: number | null;
  observationUsd: number | null;
}

export const getAssetSnapshots = cache(async (assetId: string, variantId = '', days: number | null = null): Promise<SnapshotPoint[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT * FROM price_snapshots WHERE asset_id = ${assetId} AND variant_id = ${variantId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date
  `);
  return r.map((x) => ({ date: String(x.date), rivUsd: num(x.riv_usd), latestSaleUsd: num(x.latest_sale_usd), medianUsd: num(x.median_usd), salesCount: int(x.sales_count), volumeUsd: num(x.volume_usd), listingsCount: int(x.listings_count), minAskUsd: num(x.min_ask_usd), observationUsd: num(x.observation_usd) }));
});

export interface ValuationRow {
  id: string;
  variantId: string | null;
  computedAt: Date;
  rivUsd: number | null;
  lowUsd: number | null;
  highUsd: number | null;
  confidence: number;
  confidenceLabel: string;
  sampleSize: number;
  windowDays: number;
  methods: Record<string, number | null>;
  observationsUsed: number;
  method: string;
  notes: string[];
}

export const getLatestValuation = cache(async (assetId: string, variantId: string | null = null): Promise<ValuationRow | null> => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT * FROM valuations WHERE asset_id = ${assetId} ${variantId ? sql`AND variant_id = ${variantId}` : sql`AND variant_id IS NULL`} ORDER BY computed_at DESC LIMIT 1
  `);
  if (!x) return null;
  return { id: String(x.id), variantId: str(x.variant_id), computedAt: date(x.computed_at)!, rivUsd: num(x.riv_usd), lowUsd: num(x.low_usd), highUsd: num(x.high_usd), confidence: Number(x.confidence ?? 0), confidenceLabel: String(x.confidence_label), sampleSize: int(x.sample_size), windowDays: int(x.window_days), methods: (x.methods as Record<string, number | null>) ?? {}, observationsUsed: int(x.observations_used), method: String(x.method), notes: (x.notes as string[]) ?? [] };
});

export interface SourceBreakdown {
  sourceId: string;
  name: string;
  sourceType: string;
  url: string | null;
  trustScore: number;
  sales: number;
  listings: number;
  observations: number;
  lastSeen: Date | null;
}

export const getAssetSources = cache(async (assetId: string): Promise<SourceBreakdown[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    WITH u AS (
      SELECT source_id, 'sale' AS k, created_at AS seen FROM sales WHERE asset_id = ${assetId}
      UNION ALL SELECT source_id, 'listing', last_seen_at FROM listings WHERE asset_id = ${assetId}
      UNION ALL SELECT source_id, 'observation', created_at FROM price_observations WHERE asset_id = ${assetId}
    )
    SELECT src.id AS source_id, src.name, src.source_type, src.url, src.trust_score,
      count(*) FILTER (WHERE k = 'sale') AS sales, count(*) FILTER (WHERE k = 'listing') AS listings, count(*) FILTER (WHERE k = 'observation') AS observations, max(seen) AS last_seen
    FROM u JOIN sources src ON src.id = u.source_id GROUP BY src.id, src.name, src.source_type, src.url, src.trust_score ORDER BY sales DESC, observations DESC
  `);
  return r.map((x) => ({ sourceId: String(x.source_id), name: String(x.name), sourceType: String(x.source_type), url: str(x.url), trustScore: Number(x.trust_score ?? 0), sales: int(x.sales), listings: int(x.listings), observations: int(x.observations), lastSeen: date(x.last_seen) }));
});

export const getGradeDistribution = cache(async (assetId: string): Promise<Array<{ grader: string; grade: string; sales: number; medianUsd: number | null; lastSaleUsd: number | null; lastSaleAt: Date | null; minUsd: number | null; maxUsd: number | null }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT coalesce(grader, 'raw') AS grader, coalesce(grade, '') AS grade, count(*) AS sales,
      percentile_cont(0.5) WITHIN GROUP (ORDER BY price_usd) AS median_usd, min(price_usd) AS min_usd, max(price_usd) AS max_usd,
      (array_agg(price_usd ORDER BY sale_date DESC))[1] AS last_usd, max(sale_date) AS last_at
    FROM sales WHERE asset_id = ${assetId} AND status = 'valid' GROUP BY 1, 2 ORDER BY grader, grade DESC
  `);
  return r.map((x) => ({ grader: String(x.grader), grade: String(x.grade), sales: int(x.sales), medianUsd: num(x.median_usd), lastSaleUsd: num(x.last_usd), lastSaleAt: date(x.last_at), minUsd: num(x.min_usd), maxUsd: num(x.max_usd) }));
});

export const getMarketplaceDistribution = cache(async (assetId: string): Promise<Array<{ sourceName: string; sales: number; volumeUsd: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT src.name AS source_name, count(*) AS sales, sum(s.price_usd) AS volume FROM sales s JOIN sources src ON src.id = s.source_id WHERE s.asset_id = ${assetId} AND s.status = 'valid' GROUP BY src.name ORDER BY sales DESC
  `);
  return r.map((x) => ({ sourceName: String(x.source_name), sales: int(x.sales), volumeUsd: Number(x.volume ?? 0) }));
});

export const getPopulation = cache(async (assetId: string): Promise<Array<{ grader: string; reportDate: string; total: number; byGrade: Record<string, number>; sourceUrl: string | null }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT grader, report_date, total, by_grade, source_url FROM population_reports WHERE asset_id = ${assetId} ORDER BY grader, report_date`);
  return r.map((x) => ({ grader: String(x.grader), reportDate: String(x.report_date), total: int(x.total), byGrade: (x.by_grade as Record<string, number>) ?? {}, sourceUrl: str(x.source_url) }));
});

export const getAssetImages = cache(async (assetId: string, limit = 24): Promise<Array<{ id: string; url: string; role: string; attribution: string | null; sourceId: string | null }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT id, url, role, attribution, source_id FROM images WHERE asset_id = ${assetId} ORDER BY (role = 'hero') DESC, created_at LIMIT ${limit}`);
  return r.map((x) => ({ id: String(x.id), url: String(x.url), role: String(x.role), attribution: str(x.attribution), sourceId: str(x.source_id) }));
});

/** Comparable assets: same set (or category) with closest RIV; similarity = crude score on shared attributes (§134). */
export const getComparables = cache(async (asset: { id: string; categorySlug: string; setSlug: string | null; rivUsd: number | null; brand: string | null; year: number | null }, limit = 8): Promise<Array<AssetCard & { similarity: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT},
      ((CASE WHEN a.set_slug IS NOT NULL AND a.set_slug = ${asset.setSlug ?? ''} THEN 0.4 ELSE 0 END)
       + (CASE WHEN a.category_slug = ${asset.categorySlug} THEN 0.25 ELSE 0.1 END)
       + (CASE WHEN a.brand IS NOT NULL AND lower(a.brand) = lower(${asset.brand ?? ''}) THEN 0.1 ELSE 0 END)
       + (CASE WHEN a.year = ${asset.year ?? -1} THEN 0.1 ELSE 0 END)
       + (CASE WHEN s.riv_usd IS NOT NULL AND ${asset.rivUsd ?? 0} > 0 THEN greatest(0, 0.15 - abs(ln(s.riv_usd / ${asset.rivUsd ?? 1})) * 0.1) ELSE 0 END)) AS similarity
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE a.id <> ${asset.id} AND (a.category_slug = ${asset.categorySlug} OR a.set_slug = ${asset.setSlug ?? ''})
    ORDER BY similarity DESC, s.sales_count DESC NULLS LAST LIMIT ${limit}
  `);
  return r.map((x) => ({ ...toAssetCard(x), similarity: Number(x.similarity ?? 0) }));
});

export const getAssetSlugsForSitemap = cache(async (offset: number, limit: number): Promise<Array<{ slug: string; updatedAt: Date }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT slug, updated_at FROM assets ORDER BY id LIMIT ${limit} OFFSET ${offset}`);
  return r.map((x) => ({ slug: String(x.slug), updatedAt: date(x.updated_at) ?? new Date() }));
});

/** Live counts straight from canonical tables — correct even before the valuation worker fills asset_stats. */
export const getAssetLiveCounts = cache(async (assetId: string): Promise<{ sales: number; sales30d: number; listings: number; sources: number; latestSaleUsd: number | null; latestSaleAt: Date | null; minAskUsd: number | null }> => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT
      (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid') AS sales,
      (SELECT count(*) FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' AND s.sale_date >= now() - interval '30 days') AS sales_30d,
      (SELECT count(*) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS listings,
      (SELECT count(DISTINCT source_id) FROM (SELECT source_id FROM sales WHERE asset_id = ${assetId} UNION SELECT source_id FROM listings WHERE asset_id = ${assetId} UNION SELECT source_id FROM price_observations WHERE asset_id = ${assetId}) u) AS sources,
      (SELECT price_usd FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT 1) AS latest_sale_usd,
      (SELECT sale_date FROM sales s WHERE s.asset_id = ${assetId} AND s.status = 'valid' ORDER BY s.sale_date DESC LIMIT 1) AS latest_sale_at,
      (SELECT min(price_usd) FROM listings l WHERE l.asset_id = ${assetId} AND l.availability = 'available') AS min_ask_usd
  `);
  return { sales: int(x?.sales), sales30d: int(x?.sales_30d), listings: int(x?.listings), sources: int(x?.sources), latestSaleUsd: num(x?.latest_sale_usd), latestSaleAt: date(x?.latest_sale_at), minAskUsd: num(x?.min_ask_usd) };
});

export const countAssets = cache(async (): Promise<number> => int((await one<Record<string, unknown>>(sql`SELECT count(*) AS n FROM assets`))?.n));

export const getAssetsBySlugs = cache(async (slugs: string[]): Promise<AssetCard[]> => {
  if (!slugs.length) return [];
  const r = await rows<Record<string, unknown>>(sql`SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.slug IN ${slugs}`);
  const map = new Map(r.map((x) => [String(x.slug), toAssetCard(x)]));
  return slugs.map((s) => map.get(s)).filter((x): x is AssetCard => Boolean(x));
});

export const getPriceDistribution = cache(async (assetId: string, variantId: string | null = null, days = 365): Promise<{ n: number; min: number; p25: number; median: number; p75: number; max: number; trimmedMean: number } | null> => {
  const x = await one<Record<string, unknown>>(sql`
    WITH v AS (SELECT price_usd FROM sales WHERE asset_id = ${assetId} AND status = 'valid' AND sale_date >= now() - (${days}::int || ' days')::interval ${variantId ? sql`AND variant_id = ${variantId}` : sql``}),
    b AS (SELECT percentile_cont(0.1) WITHIN GROUP (ORDER BY price_usd) AS lo, percentile_cont(0.9) WITHIN GROUP (ORDER BY price_usd) AS hi FROM v)
    SELECT count(*) AS n, min(price_usd) AS min, percentile_cont(0.25) WITHIN GROUP (ORDER BY price_usd) AS p25, percentile_cont(0.5) WITHIN GROUP (ORDER BY price_usd) AS median, percentile_cont(0.75) WITHIN GROUP (ORDER BY price_usd) AS p75, max(price_usd) AS max,
      (SELECT avg(price_usd) FROM v, b WHERE price_usd BETWEEN b.lo AND b.hi) AS trimmed
    FROM v
  `);
  if (!x || int(x.n) === 0) return null;
  return { n: int(x.n), min: Number(x.min), p25: Number(x.p25), median: Number(x.median), p75: Number(x.p75), max: Number(x.max), trimmedMean: Number(x.trimmed ?? x.median) };
});

/** Latest price-guide observation per asset (batch). Attached to cards so catalog-only assets still show a labelled guide price. */
export async function attachGuidePrices<T extends AssetCard>(cards: T[]): Promise<T[]> {
  const ids = cards.filter((c) => c.rivUsd === null).map((c) => c.id);
  if (!ids.length) return cards;
  const r = await rows<Record<string, unknown>>(sql`
    SELECT DISTINCT ON (o.asset_id) o.asset_id, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name
    FROM price_observations o JOIN sources src ON src.id = o.source_id
    WHERE o.asset_id IN ${ids}
    ORDER BY o.asset_id, o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC
  `);
  const map = new Map(r.map((x) => [String(x.asset_id), x]));
  for (const c of cards) {
    const g = map.get(c.id);
    if (g) {
      c.guideUsd = num(g.price_usd);
      c.guideKind = str(g.price_kind);
      c.guideDate = str(g.observation_date);
      c.guideSource = str(g.source_name);
    }
  }
  return cards;
}

export interface GuidePrice {
  priceUsd: number;
  price: number;
  currency: string;
  priceKind: string;
  observationDate: string;
  sourceName: string;
  sourceUrl: string;
  variantId: string | null;
  variantLabel: string | null;
}

/** Best current guide price for the header: newest observation, preferring 'market' in USD. */
export const getLatestGuidePrice = cache(async (assetId: string, variantId: string | null = null): Promise<GuidePrice | null> => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT o.price_usd, o.price, o.currency, o.price_kind, o.observation_date, src.name AS source_name, o.source_url, o.variant_id, v.label AS variant_label
    FROM price_observations o JOIN sources src ON src.id = o.source_id LEFT JOIN asset_variants v ON v.id = o.variant_id
    WHERE o.asset_id = ${assetId} ${variantId ? sql`AND o.variant_id = ${variantId}` : sql``}
    ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC, (o.currency = 'USD') DESC LIMIT 1
  `);
  if (!x) return null;
  return { priceUsd: Number(x.price_usd), price: Number(x.price), currency: String(x.currency), priceKind: String(x.price_kind), observationDate: String(x.observation_date), sourceName: String(x.source_name), sourceUrl: String(x.source_url), variantId: str(x.variant_id), variantLabel: str(x.variant_label) };
});

/** Other assets in the same set, ordered by collector number (never a dead end on catalog-only pages). */
export const getSetSiblings = cache(async (asset: { id: string; setSlug: string | null }, limit = 12): Promise<AssetCard[]> => {
  if (!asset.setSlug) return [];
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT} FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE a.set_slug = ${asset.setSlug} AND a.id <> ${asset.id}
    ORDER BY (s.riv_usd IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, (substring(a.number from '^[0-9]+'))::int ASC NULLS LAST, a.number ASC NULLS LAST LIMIT ${limit}
  `);
  return r.map(toAssetCard);
});

/** Similar assets by title trigram within the category (works for any catalog record). */
export const getSimilarAssets = cache(async (asset: { id: string; name: string; categorySlug: string }, limit = 8): Promise<AssetCard[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${ASSET_CARD_SELECT}, similarity(a.title, ${asset.name}) AS sim FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE a.id <> ${asset.id} AND a.category_slug = ${asset.categorySlug} AND a.title % ${asset.name}
    ORDER BY sim DESC, coalesce(s.sales_count, 0) DESC LIMIT ${limit}
  `);
  return r.map(toAssetCard);
});

/** Sets/releases derived from the assets table itself (correct even when the `sets` table is sparse). */
export interface SetGroup {
  slug: string;
  name: string;
  code: string | null;
  year: number | null;
  assets: number;
  priced: number;
  sales: number;
  withImage: number;
  thumb: string | null;
}
export const getSetGroups = cache(async (scope: string[], opts: { limit?: number; offset?: number; sort?: 'assets' | 'sales' | 'year' | 'name' } = {}): Promise<{ items: SetGroup[]; total: number }> => {
  const order = opts.sort === 'sales' ? sql`sales DESC, assets DESC` : opts.sort === 'year' ? sql`year DESC NULLS LAST, assets DESC` : opts.sort === 'name' ? sql`name ASC` : sql`assets DESC, sales DESC`;
  const r = await rows<Record<string, unknown>>(sql`
    SELECT a.set_slug AS slug, coalesce(st.name, min(a.set_name)) AS name, coalesce(st.code, min(a.set_code)) AS code, coalesce(st.release_year, min(a.year)) AS year,
      count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, count(a.hero_image_url) AS with_image,
      (array_agg(a.hero_image_url ORDER BY (substring(a.number from '^[0-9]+'))::int NULLS LAST) FILTER (WHERE a.hero_image_url IS NOT NULL))[1] AS thumb,
      count(*) OVER() AS total
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id LEFT JOIN sets st ON st.slug = a.set_slug
    WHERE a.category_slug IN ${scope} AND a.set_slug IS NOT NULL
    GROUP BY a.set_slug, st.name, st.code, st.release_year
    ORDER BY ${order} LIMIT ${opts.limit ?? 60} OFFSET ${opts.offset ?? 0}
  `);
  return { items: r.map((x) => ({ slug: String(x.slug), name: String(x.name ?? x.slug), code: str(x.code), year: num(x.year), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), withImage: int(x.with_image), thumb: str(x.thumb) })), total: r.length ? int(r[0]!.total) : 0 };
});

/** Set facts derived from its assets when the `sets` table has no row (fallback for /set/[slug]). */
export const getSetFromAssets = cache(async (slug: string) => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT min(a.set_name) AS name, min(a.set_code) AS code, min(a.category_slug) AS category_slug, min(a.year) AS year, count(*) AS assets, min(a.language) AS language
    FROM assets a WHERE a.set_slug = ${slug}
  `);
  if (!x || int(x.assets) === 0) return null;
  return { slug, name: String(x.name ?? slug), code: str(x.code), categorySlug: String(x.category_slug), franchiseSlug: null, brandSlug: null, releaseYear: num(x.year), releaseDate: null, language: str(x.language), totalItems: null, identifiers: {} as Record<string, string>, metadata: {} as Record<string, unknown> };
});

/** Representative thumbnail per category (one cheap index lookup per slug). */
export const getCategoryThumbnails = cache(async (slugs: string[]): Promise<Map<string, string>> => {
  if (!slugs.length) return new Map();
  const r = await rows<Record<string, unknown>>(sql`
    SELECT c.slug, (
      SELECT a.hero_image_url FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id
      WHERE (a.category_slug = c.slug OR a.family_slug = c.slug) AND a.hero_image_url IS NOT NULL
      ORDER BY s.sales_count DESC NULLS LAST, s.riv_usd DESC NULLS LAST, a.created_at DESC LIMIT 1
    ) AS url
    FROM unnest(${textArray(slugs)}) AS c(slug)
  `);
  return new Map(r.filter((x) => x.url).map((x) => [String(x.slug), String(x.url)]));
});

/** Latest guide observations across the site (fallback for the home "latest transactions" block until sales exist). */
export const getLatestObservationsFeed = cache(async (limit = 12): Promise<Array<{ id: string; assetSlug: string; assetTitle: string; categorySlug: string; heroImageUrl: string | null; priceUsd: number; priceKind: string; observationDate: string; sourceName: string }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    WITH recent AS (
      SELECT a.id, a.slug, a.title, a.category_slug, a.hero_image_url FROM assets a JOIN asset_stats s ON s.asset_id = a.id
      WHERE coalesce(s.observations_count, 0) > 0 ORDER BY s.updated_at DESC LIMIT ${limit * 3}
    )
    SELECT o.id, r.slug AS asset_slug, r.title AS asset_title, r.category_slug, r.hero_image_url, o.price_usd, o.price_kind, o.observation_date, src.name AS source_name
    FROM recent r JOIN LATERAL (SELECT * FROM price_observations o WHERE o.asset_id = r.id ORDER BY o.observation_date DESC, (o.price_kind = 'market') DESC LIMIT 1) o ON true
    JOIN sources src ON src.id = o.source_id
    ORDER BY o.observation_date DESC LIMIT ${limit}
  `);
  return r.map((x) => ({ id: String(x.id), assetSlug: String(x.asset_slug), assetTitle: String(x.asset_title), categorySlug: String(x.category_slug), heroImageUrl: str(x.hero_image_url), priceUsd: Number(x.price_usd), priceKind: String(x.price_kind), observationDate: String(x.observation_date), sourceName: String(x.source_name) }));
});

/** Counts for a browse scope, straight from canonical tables (for "13,384 assets · 0 priced yet" lines). */
export const getScopeCounts = cache(async (f: { scope?: string[] | null; set?: string | null; brand?: string | null }): Promise<{ assets: number; priced: number; withSales: number; withObservations: number; withImages: number; listings: number }> => {
  const where: SQL[] = [];
  if (f.scope?.length) where.push(sql`a.category_slug IN ${f.scope}`);
  if (f.set) where.push(sql`a.set_slug = ${f.set}`);
  if (f.brand) where.push(sql`lower(a.brand) = lower(${f.brand})`);
  const x = await one<Record<string, unknown>>(sql`
    SELECT count(*) AS assets, count(s.riv_usd) AS priced, count(*) FILTER (WHERE coalesce(s.sales_count, 0) > 0) AS with_sales,
      count(*) FILTER (WHERE coalesce(s.observations_count, 0) > 0) AS with_obs, count(a.hero_image_url) AS with_images, coalesce(sum(s.active_listings), 0) AS listings
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE ${joinAnd(where)}
  `);
  return { assets: int(x?.assets), priced: int(x?.priced), withSales: int(x?.with_sales), withObservations: int(x?.with_obs), withImages: int(x?.with_images), listings: int(x?.listings) };
});

/** Valuation history (RIV with low/high band) from the valuations table, one point per day. */
export const getValuationHistory = cache(async (assetId: string, variantId: string | null = null, days = 1830): Promise<Array<{ date: string; rivUsd: number; lowUsd: number | null; highUsd: number | null; confidence: number; sampleSize: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT DISTINCT ON (computed_at::date) computed_at::date AS d, riv_usd, low_usd, high_usd, confidence, sample_size
    FROM valuations
    WHERE asset_id = ${assetId} AND ${variantId ? sql`variant_id = ${variantId}` : sql`variant_id IS NULL`} AND riv_usd IS NOT NULL AND computed_at >= now() - (${days}::int || ' days')::interval
    ORDER BY computed_at::date, computed_at DESC
  `);
  return r.map((x) => ({ date: String(x.d), rivUsd: Number(x.riv_usd), lowUsd: num(x.low_usd), highUsd: num(x.high_usd), confidence: Number(x.confidence ?? 0), sampleSize: int(x.sample_size) }));
});

/** Whether the signed-in user watches this asset (null when anonymous). */
export async function isWatchedBy(userId: string | null, assetId: string): Promise<boolean> {
  if (!userId) return false;
  const x = await one<Record<string, unknown>>(sql`
    SELECT 1 AS ok FROM watchlist_items wi JOIN watchlists w ON w.id = wi.watchlist_id
    WHERE w.user_id = ${userId} AND wi.target_type = 'asset' AND wi.target_id = ${assetId} LIMIT 1
  `);
  return Boolean(x);
}