import 'server-only';
import { cache } from 'react';
import type { SQL } from 'drizzle-orm';
import { rows, one, sql, num, int, str, date, joinAnd, textArray, iso } from './_util';
import { categoryScope, SALE_SELECT, LISTING_SELECT, toSaleRow, toListingRow, type SaleRow, type ListingRow } from './assets';
import { houseSlug } from '@/lib/auction-house';

// ---------- Sales ----------
export interface SalesFilters {
  category?: string | null;
  source?: string | null;
  grader?: string | null;
  minUsd?: number | null;
  maxUsd?: number | null;
  days?: number | null;
  saleType?: string | null;
  q?: string | null;
  page?: number;
  pageSize?: number;
  sort?: 'date' | 'price';
}

export async function listSales(f: SalesFilters): Promise<{ items: SaleRow[]; total: number; page: number; pageSize: number }> {
  const pageSize = Math.min(f.pageSize ?? 50, 200);
  const page = Math.max(1, f.page ?? 1);
  const where: SQL[] = [sql`s.status = 'valid'`];
  if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);
  if (f.source) where.push(sql`s.source_id = ${f.source}`);
  if (f.grader) where.push(sql`s.grader = ${f.grader}`);
  if (f.minUsd != null) where.push(sql`s.price_usd >= ${f.minUsd}`);
  if (f.maxUsd != null) where.push(sql`s.price_usd <= ${f.maxUsd}`);
  if (f.days) where.push(sql`s.sale_date >= now() - (${f.days}::int || ' days')::interval`);
  if (f.saleType) where.push(sql`s.sale_type = ${f.saleType}`);
  if (f.q && f.q.trim().length >= 2) where.push(sql`(a.title ILIKE ${'%' + f.q.trim() + '%'} OR s.raw_title ILIKE ${'%' + f.q.trim() + '%'})`);
  const order = f.sort === 'price' ? sql`s.price_usd DESC` : sql`s.sale_date DESC, s.created_at DESC`;
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, count(*) OVER() AS total
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE ${joinAnd(where)} ORDER BY ${order} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
  `);
  return { items: r.map(toSaleRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };
}

export const listSaleSources = cache(async (): Promise<Array<{ id: string; name: string; sales: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT src.id, src.name, count(*) AS sales FROM sales s JOIN sources src ON src.id = s.source_id WHERE s.status = 'valid' GROUP BY src.id, src.name ORDER BY sales DESC`);
  return r.map((x) => ({ id: String(x.id), name: String(x.name), sales: int(x.sales) }));
});

/** Record sales (§154): highest verified sale overall and per family/category. */
export const getRecordSales = cache(async (): Promise<Array<SaleRow & { familySlug: string }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT DISTINCT ON (a.family_slug) ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.family_slug, a.hero_image_url
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE s.status = 'valid' AND s.confidence >= 0.8 ORDER BY a.family_slug, s.price_usd DESC
  `);
  return r.map((x) => ({ ...toSaleRow(x), familySlug: String(x.family_slug) })).sort((a, b) => b.priceUsd - a.priceUsd);
});

export const getTopSales = cache(async (limit = 50, scope?: string[]): Promise<SaleRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE s.status = 'valid' AND s.confidence >= 0.8 ${scope?.length ? sql`AND a.category_slug IN ${scope}` : sql``} ORDER BY s.price_usd DESC LIMIT ${limit}
  `);
  return r.map(toSaleRow);
});

export const getRecentSalesInScope = cache(async (scope: string[], limit = 20): Promise<SaleRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE s.status = 'valid' AND a.category_slug IN ${scope} ORDER BY s.sale_date DESC LIMIT ${limit}
  `);
  return r.map(toSaleRow);
});

// ---------- Listings ----------
export interface ListingsFilters {
  category?: string | null;
  source?: string | null;
  grader?: string | null;
  minUsd?: number | null;
  maxUsd?: number | null;
  minDiscount?: number | null;
  listingType?: string | null;
  q?: string | null;
  sort?: 'newest' | 'price_asc' | 'price_desc' | 'discount' | 'ending';
  page?: number;
  pageSize?: number;
}

export async function listListings(f: ListingsFilters): Promise<{ items: ListingRow[]; total: number; page: number; pageSize: number }> {
  const pageSize = Math.min(f.pageSize ?? 50, 200);
  const page = Math.max(1, f.page ?? 1);
  const where: SQL[] = [sql`l.availability = 'available'`];
  if (f.category) where.push(sql`a.category_slug IN ${categoryScope(f.category)}`);
  if (f.source) where.push(sql`l.source_id = ${f.source}`);
  if (f.grader) where.push(sql`l.grader = ${f.grader}`);
  if (f.minUsd != null) where.push(sql`l.price_usd >= ${f.minUsd}`);
  if (f.maxUsd != null) where.push(sql`l.price_usd <= ${f.maxUsd}`);
  if (f.minDiscount != null) where.push(sql`l.discount_to_riv <= ${-Math.abs(f.minDiscount)} AND NOT ('riv_review' = ANY(l.flags))`); // stored negative = below RIV; review cases excluded
  if (f.sort === 'discount') where.push(sql`NOT ('riv_review' = ANY(l.flags))`);
  if (f.listingType) where.push(sql`l.listing_type = ${f.listingType}`);
  if (f.q && f.q.trim().length >= 2) where.push(sql`(a.title ILIKE ${'%' + f.q.trim() + '%'} OR l.raw_title ILIKE ${'%' + f.q.trim() + '%'})`);
  const orders: Record<NonNullable<ListingsFilters['sort']>, SQL> = {
    newest: sql`l.first_seen_at DESC`,
    price_asc: sql`l.price_usd ASC NULLS LAST`,
    price_desc: sql`l.price_usd DESC NULLS LAST`,
    discount: sql`l.discount_to_riv ASC NULLS LAST`, // most below RIV first
    ending: sql`l.ends_at ASC NULLS LAST`,
  };
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${LISTING_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, st.riv_usd AS asset_riv_usd, st.riv_confidence AS asset_riv_confidence, count(*) OVER() AS total
    FROM listings l JOIN assets a ON a.id = l.asset_id JOIN sources src ON src.id = l.source_id LEFT JOIN asset_stats st ON st.asset_id = a.id
    WHERE ${joinAnd(where)} ORDER BY ${orders[f.sort ?? 'newest']} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
  `);
  return { items: r.map(toListingRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };
}

export const listListingSources = cache(async (): Promise<Array<{ id: string; name: string; listings: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT src.id, src.name, count(*) AS listings FROM listings l JOIN sources src ON src.id = l.source_id WHERE l.availability = 'available' GROUP BY src.id, src.name ORDER BY listings DESC`);
  return r.map((x) => ({ id: String(x.id), name: String(x.name), listings: int(x.listings) }));
});

// ---------- Auctions ----------
export interface AuctionRow {
  id: string;
  sourceId: string;
  auctionHouse: string;
  name: string;
  url: string;
  startsAt: Date | null;
  endsAt: Date | null;
  location: string | null;
  categorySlugs: string[];
  lotCount: number | null;
  status: string;
  currency: string | null;
  lotsTracked: number;
}

export async function listAuctions(opts: { status?: 'upcoming' | 'live' | 'ended' | null; from?: Date; to?: Date; category?: string | null; house?: string | null; limit?: number } = {}): Promise<AuctionRow[]> {
  const where: SQL[] = [];
  if (opts.status) where.push(sql`au.status = ${opts.status}`);
  if (opts.from) where.push(sql`coalesce(au.ends_at, au.starts_at) >= ${iso(opts.from)}::timestamptz`);
  if (opts.to) where.push(sql`coalesce(au.starts_at, au.ends_at) <= ${iso(opts.to)}::timestamptz`);
  if (opts.category) where.push(sql`au.category_slugs && ${textArray(categoryScope(opts.category))}`);
  if (opts.house) where.push(sql`au.auction_house = ${opts.house}`);
  const r = await rows<Record<string, unknown>>(sql`
    SELECT au.*, (SELECT count(*) FROM auction_lots l WHERE l.auction_id = au.id) AS lots_tracked FROM auctions au WHERE ${joinAnd(where)}
    ORDER BY CASE au.status WHEN 'live' THEN 0 WHEN 'upcoming' THEN 1 ELSE 2 END, coalesce(au.ends_at, au.starts_at) ASC NULLS LAST LIMIT ${opts.limit ?? 100}
  `);
  return r.map((x) => ({ id: String(x.id), sourceId: String(x.source_id), auctionHouse: String(x.auction_house), name: String(x.name), url: String(x.url), startsAt: date(x.starts_at), endsAt: date(x.ends_at), location: str(x.location), categorySlugs: (x.category_slugs as string[]) ?? [], lotCount: num(x.lot_count), status: String(x.status), currency: str(x.currency), lotsTracked: int(x.lots_tracked) }));
}

export interface LotRow {
  id: string;
  auctionId: string;
  auctionName: string | null;
  auctionHouse: string | null;
  auctionHouseSlug: string | null;
  assetSlug: string | null;
  assetTitle: string | null;
  categorySlug: string | null;
  lotNumber: string | null;
  title: string;
  url: string;
  estimateLow: number | null;
  estimateHigh: number | null;
  currentBid: number | null;
  hammerPrice: number | null;
  currency: string | null;
  bidCount: number | null;
  startsAt: Date | null;
  endsAt: Date | null;
  status: string;
  imageUrls: string[];
  grader: string | null;
  grade: string | null;
  rivUsd: number | null;
  rivConfidence: number | null;
  rivSampleSize: number;
  // ---- USD normalisation + assessment (§33–§35); null until the auctions worker has assessed the lot ----
  estimateLowUsd: number | null;
  estimateHighUsd: number | null;
  currentBidUsd: number | null;
  hammerPriceUsd: number | null;
  buyerPremiumRate: number | null;
  feeBasis: string | null;
  allInBidUsd: number | null;
  allInEstimateLowUsd: number | null;
  allInEstimateHighUsd: number | null;
  rivUsdAtAssessment: number | null;
  /** (all-in bid − RIV) / RIV — negative = below the valuation */
  bidVsRiv: number | null;
  /** (all-in low estimate − RIV) / RIV */
  estimateVsRiv: number | null;
  assessmentVerdict: string | null;
  assessedAt: Date | null;
}

export type LotSort = 'ending' | 'value' | 'discount' | 'bids';

export interface LotFilters {
  status?: 'upcoming' | 'live' | 'ended' | null;
  /** live or upcoming and not yet ended */
  open?: boolean;
  endingWithinHours?: number | null;
  auctionId?: string | null;
  category?: string | null;
  house?: string | null;
  assetId?: string | null;
  /** only lots whose assessed all-in bid (or estimate) is a gated deal */
  verdict?: 'deal' | null;
  /** keep lots with bid_vs_riv (or estimate_vs_riv) ≤ this value, e.g. −0.1 */
  maxVsRiv?: number | null;
  hasEstimate?: boolean;
  hasBid?: boolean;
  limit?: number;
  page?: number;
  pageSize?: number;
  sort?: LotSort;
}

const LOT_SELECT = sql`l.*, au.name AS auction_name, au.auction_house, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, st.riv_usd, st.riv_confidence, coalesce(st.riv_sample_size, 0) AS riv_sample_size`;

function toLotRow(x: Record<string, unknown>): LotRow {
  const house = str(x.auction_house);
  return {
    id: String(x.id),
    auctionId: String(x.auction_id),
    auctionName: str(x.auction_name),
    auctionHouse: house,
    auctionHouseSlug: house ? houseSlug(house) : null,
    assetSlug: str(x.asset_slug),
    assetTitle: str(x.asset_title),
    categorySlug: str(x.category_slug),
    lotNumber: str(x.lot_number),
    title: String(x.title),
    url: String(x.url),
    estimateLow: num(x.estimate_low),
    estimateHigh: num(x.estimate_high),
    currentBid: num(x.current_bid),
    hammerPrice: num(x.hammer_price),
    currency: str(x.currency),
    bidCount: num(x.bid_count),
    startsAt: date(x.starts_at),
    endsAt: date(x.ends_at),
    status: String(x.status),
    imageUrls: (x.image_urls as string[]) ?? [],
    grader: str(x.grader),
    grade: str(x.grade),
    rivUsd: num(x.riv_usd),
    rivConfidence: num(x.riv_confidence),
    rivSampleSize: int(x.riv_sample_size),
    estimateLowUsd: num(x.estimate_low_usd),
    estimateHighUsd: num(x.estimate_high_usd),
    currentBidUsd: num(x.current_bid_usd),
    hammerPriceUsd: num(x.hammer_price_usd),
    buyerPremiumRate: num(x.buyer_premium_rate),
    feeBasis: str(x.fee_basis),
    allInBidUsd: num(x.all_in_bid_usd),
    allInEstimateLowUsd: num(x.all_in_estimate_low_usd),
    allInEstimateHighUsd: num(x.all_in_estimate_high_usd),
    rivUsdAtAssessment: num(x.riv_usd_at_assessment),
    bidVsRiv: num(x.bid_vs_riv),
    estimateVsRiv: num(x.estimate_vs_riv),
    assessmentVerdict: str(x.assessment_verdict),
    assessedAt: date(x.assessed_at),
  };
}

function lotWhere(opts: LotFilters): SQL[] {
  const where: SQL[] = [];
  if (opts.status) where.push(sql`l.status = ${opts.status}`);
  if (opts.open) where.push(sql`l.status IN ('live', 'upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())`);
  if (opts.endingWithinHours) where.push(sql`l.ends_at BETWEEN now() AND now() + (${opts.endingWithinHours}::int || ' hours')::interval`);
  if (opts.auctionId) where.push(sql`l.auction_id = ${opts.auctionId}`);
  if (opts.assetId) where.push(sql`l.asset_id = ${opts.assetId}`);
  if (opts.category) where.push(sql`a.category_slug IN ${categoryScope(opts.category)}`);
  if (opts.house) where.push(sql`au.auction_house = ${opts.house}`);
  if (opts.verdict === 'deal') where.push(sql`l.assessment_verdict = 'deal'`);
  if (opts.maxVsRiv != null) where.push(sql`coalesce(l.bid_vs_riv, l.estimate_vs_riv) <= ${opts.maxVsRiv} AND l.assessment_verdict IN ('deal', 'fair', 'premium')`);
  if (opts.hasEstimate) where.push(sql`l.estimate_low IS NOT NULL`);
  if (opts.hasBid) where.push(sql`l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0`);
  return where;
}

const LOT_ORDER: Record<LotSort, SQL> = {
  ending: sql`l.ends_at ASC NULLS LAST`,
  value: sql`coalesce(l.hammer_price_usd, l.all_in_bid_usd, l.all_in_estimate_high_usd, l.hammer_price, l.current_bid, l.estimate_high) DESC NULLS LAST`,
  // most below RIV first; unassessed lots last
  discount: sql`coalesce(l.bid_vs_riv, l.estimate_vs_riv) ASC NULLS LAST, l.ends_at ASC NULLS LAST`,
  bids: sql`l.bid_count DESC NULLS LAST, l.ends_at ASC NULLS LAST`,
};

const LOT_FROM = sql`FROM auction_lots l LEFT JOIN auctions au ON au.id = l.auction_id LEFT JOIN assets a ON a.id = l.asset_id LEFT JOIN asset_stats st ON st.asset_id = a.id`;

export async function listLots(opts: LotFilters = {}): Promise<LotRow[]> {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${LOT_SELECT} ${LOT_FROM}
    WHERE ${joinAnd(lotWhere(opts))} ORDER BY ${LOT_ORDER[opts.sort ?? 'ending']} LIMIT ${opts.limit ?? 50}
  `);
  return r.map(toLotRow);
}

/** Paginated lots for /auctions and house pages (count(*) OVER() total). */
export async function listLotsPaged(opts: LotFilters = {}): Promise<{ items: LotRow[]; total: number; page: number; pageSize: number }> {
  const pageSize = Math.min(Math.max(opts.pageSize ?? 50, 1), 200);
  const page = Math.max(1, opts.page ?? 1);
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${LOT_SELECT}, count(*) OVER() AS total ${LOT_FROM}
    WHERE ${joinAnd(lotWhere(opts))} ORDER BY ${LOT_ORDER[opts.sort ?? 'ending']} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
  `);
  return { items: r.map(toLotRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };
}

/** Live/upcoming lots for one asset (all variants), soonest ending first. */
export const listAssetLots = cache(async (assetId: string, limit = 50): Promise<LotRow[]> => listLots({ assetId, open: true, limit, sort: 'ending' }));

export const countAssetLots = cache(async (assetId: string): Promise<number> => {
  const r = await one<{ n: number }>(sql`SELECT count(*)::int AS n FROM auction_lots l WHERE l.asset_id = ${assetId} AND l.status IN ('live', 'upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())`);
  return r?.n ?? 0;
});

/** Headline numbers for the /auctions strip. */
export const getLotOverview = cache(async (): Promise<{ open: number; ending24h: number; withBids: number; belowRiv: number; assessed: number; houses: number }> => {
  const r = await one<Record<string, unknown>>(sql`
    SELECT count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())) AS open,
           count(*) FILTER (WHERE l.ends_at BETWEEN now() AND now() + interval '24 hours') AS ending24h,
           count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0) AS with_bids,
           count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.assessment_verdict = 'deal') AS below_riv,
           count(*) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now()) AND l.assessed_at IS NOT NULL) AS assessed,
           count(DISTINCT au.auction_house) FILTER (WHERE l.status IN ('live','upcoming')) AS houses
    FROM auction_lots l LEFT JOIN auctions au ON au.id = l.auction_id
  `);
  return { open: int(r?.open), ending24h: int(r?.ending24h), withBids: int(r?.with_bids), belowRiv: int(r?.below_riv), assessed: int(r?.assessed), houses: int(r?.houses) };
});

export interface AuctionHouseStats {
  house: string;
  auctions: number;
  upcomingAuctions: number;
  lotsTracked: number;
  openLots: number;
  liveLots: number;
  lotsWithBids: number;
  lotsAssessed: number;
  lotsBelowRiv: number;
  /** results = sales carrying this auction house */
  results: number;
  results365d: number;
  medianAllInUsd365d: number | null;
  maxAllInUsd365d: number | null;
  /** share of results whose premium had to be estimated (fee_basis added_*); null when no result has a fee basis yet */
  estimatedPremiumShare: number | null;
  /** lots ended with a recorded hammer / lots ended — null when the house's results are not recorded per lot */
  sellThrough: number | null;
  lastResultAt: Date | null;
}

export const getAuctionHouseStats = cache(async (house: string): Promise<AuctionHouseStats | null> => {
  const lots = await one<Record<string, unknown>>(sql`
    SELECT count(DISTINCT au.id) AS auctions,
           count(DISTINCT au.id) FILTER (WHERE au.status <> 'ended') AS upcoming_auctions,
           count(l.id) AS lots,
           count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND (l.ends_at IS NULL OR l.ends_at > now())) AS open_lots,
           count(l.id) FILTER (WHERE l.status = 'live') AS live_lots,
           count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.current_bid IS NOT NULL AND coalesce(l.bid_count, 1) > 0) AS with_bids,
           count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.assessed_at IS NOT NULL) AS assessed,
           count(l.id) FILTER (WHERE l.status IN ('live','upcoming') AND l.assessment_verdict = 'deal') AS below_riv,
           count(l.id) FILTER (WHERE l.status = 'ended') AS ended,
           count(l.id) FILTER (WHERE l.status = 'ended' AND l.hammer_price IS NOT NULL) AS ended_sold
    FROM auctions au LEFT JOIN auction_lots l ON l.auction_id = au.id WHERE au.auction_house = ${house}
  `);
  const sales = await one<Record<string, unknown>>(sql`
    SELECT count(*) AS results,
           count(*) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS results_365,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(s.all_in_usd, s.price_usd)) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS median_365,
           max(coalesce(s.all_in_usd, s.price_usd)) FILTER (WHERE s.sale_date >= now() - interval '365 days') AS max_365,
           count(*) FILTER (WHERE s.fee_basis IS NOT NULL) AS with_basis,
           count(*) FILTER (WHERE s.fee_basis LIKE 'added_%') AS estimated_basis,
           max(s.sale_date) AS last_result
    FROM sales s WHERE s.status = 'valid' AND s.auction_house = ${house}
  `);
  if (!lots && !sales) return null;
  const auctions = int(lots?.auctions);
  const results = int(sales?.results);
  if (!auctions && !results) return null;
  const ended = int(lots?.ended);
  const withBasis = int(sales?.with_basis);
  return {
    house,
    auctions,
    upcomingAuctions: int(lots?.upcoming_auctions),
    lotsTracked: int(lots?.lots),
    openLots: int(lots?.open_lots),
    liveLots: int(lots?.live_lots),
    lotsWithBids: int(lots?.with_bids),
    lotsAssessed: int(lots?.assessed),
    lotsBelowRiv: int(lots?.below_riv),
    results,
    results365d: int(sales?.results_365),
    medianAllInUsd365d: num(sales?.median_365),
    maxAllInUsd365d: num(sales?.max_365),
    estimatedPremiumShare: withBasis ? int(sales?.estimated_basis) / withBasis : null,
    sellThrough: ended >= 20 && int(lots?.ended_sold) > 0 ? int(lots?.ended_sold) / ended : null,
    lastResultAt: date(sales?.last_result),
  };
});

/** Recent results (sales) recorded for an auction house. */
export const listHouseResults = cache(async (house: string, limit = 50): Promise<SaleRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT ${SALE_SELECT}, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE s.status = 'valid' AND s.auction_house = ${house} ORDER BY s.sale_date DESC LIMIT ${limit}
  `);
  return r.map(toSaleRow);
});

export const listAuctionHouses = cache(async (): Promise<Array<{ house: string; auctions: number; lots: number; upcoming: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT au.auction_house AS house, count(DISTINCT au.id) AS auctions, count(l.id) AS lots, count(DISTINCT au.id) FILTER (WHERE au.status <> 'ended') AS upcoming
    FROM auctions au LEFT JOIN auction_lots l ON l.auction_id = au.id GROUP BY au.auction_house ORDER BY auctions DESC
  `);
  return r.map((x) => ({ house: String(x.house), auctions: int(x.auctions), lots: int(x.lots), upcoming: int(x.upcoming) }));
});

// ---------- Radar / records / news ----------
export interface RadarRow {
  id: string;
  kind: string;
  score: number;
  evidence: Record<string, unknown>;
  entityType: string | null;
  entityId: string | null;
  detectedAt: Date;
  assetSlug: string;
  assetTitle: string;
  categorySlug: string;
  heroImageUrl: string | null;
  rivUsd: number | null;
}

export async function listRadar(opts: { kind?: string | null; limit?: number; scope?: string[] } = {}): Promise<RadarRow[]> {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT r.*, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, st.riv_usd
    FROM radar_findings r JOIN assets a ON a.id = r.asset_id LEFT JOIN asset_stats st ON st.asset_id = a.id
    WHERE (r.expires_at IS NULL OR r.expires_at > now()) ${opts.kind ? sql`AND r.kind = ${opts.kind}` : sql``} ${opts.scope?.length ? sql`AND a.category_slug IN ${opts.scope}` : sql``}
    ORDER BY r.detected_at DESC, r.score DESC LIMIT ${opts.limit ?? 50}
  `);
  return r.map((x) => ({ id: String(x.id), kind: String(x.kind), score: Number(x.score), evidence: (x.evidence as Record<string, unknown>) ?? {}, entityType: str(x.entity_type), entityId: str(x.entity_id), detectedAt: date(x.detected_at)!, assetSlug: String(x.asset_slug), assetTitle: String(x.asset_title), categorySlug: String(x.category_slug), heroImageUrl: str(x.hero_image_url), rivUsd: num(x.riv_usd) }));
}

export interface NewsRow {
  id: string;
  sourceId: string;
  sourceName: string;
  url: string;
  title: string;
  summary: string | null;
  aiSummary: string | null;
  publishedAt: Date | null;
  categorySlugs: string[];
  newsType: string | null;
  imageUrl: string | null;
}

export async function listNews(opts: { type?: string | null; category?: string | null; limit?: number; page?: number } = {}): Promise<{ items: NewsRow[]; total: number }> {
  const limit = opts.limit ?? 40;
  const page = Math.max(1, opts.page ?? 1);
  const where: SQL[] = [];
  if (opts.type) where.push(sql`n.news_type = ${opts.type}`);
  if (opts.category) where.push(sql`n.category_slugs && ${textArray(categoryScope(opts.category))}`);
  const r = await rows<Record<string, unknown>>(sql`
    SELECT n.*, src.name AS source_name, count(*) OVER() AS total FROM news n JOIN sources src ON src.id = n.source_id WHERE ${joinAnd(where)}
    ORDER BY n.published_at DESC NULLS LAST, n.fetched_at DESC LIMIT ${limit} OFFSET ${(page - 1) * limit}
  `);
  return { items: r.map((x) => ({ id: String(x.id), sourceId: String(x.source_id), sourceName: String(x.source_name), url: String(x.url), title: String(x.title), summary: str(x.summary), aiSummary: str(x.ai_summary), publishedAt: date(x.published_at), categorySlugs: (x.category_slugs as string[]) ?? [], newsType: str(x.news_type), imageUrl: str(x.image_url) })), total: r.length ? int(r[0]!.total) : 0 };
}

// ---------- Grading ----------
export const getGradePremiums = cache(async (category?: string | null): Promise<Array<{ categorySlug: string; grader: string; grade: string; marketMultiplier: number; sampleSize: number; computedAt: Date }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT * FROM grade_premiums ${category ? sql`WHERE category_slug IN ${categoryScope(category)}` : sql``} ORDER BY category_slug, grader, market_multiplier DESC`);
  return r.map((x) => ({ categorySlug: String(x.category_slug), grader: String(x.grader), grade: String(x.grade), marketMultiplier: Number(x.market_multiplier), sampleSize: int(x.sample_size), computedAt: date(x.computed_at)! }));
});

export const getGraderActivity = cache(async (): Promise<Array<{ grader: string; sales: number; assets: number; medianUsd: number | null; populationReports: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT g.slug AS grader,
      (SELECT count(*) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS sales,
      (SELECT count(DISTINCT s.asset_id) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS assets,
      (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY s.price_usd) FROM sales s WHERE s.grader = g.slug AND s.status = 'valid') AS median_usd,
      (SELECT count(*) FROM population_reports p WHERE p.grader = g.slug) AS population_reports
    FROM graders g WHERE g.active ORDER BY sales DESC, g.slug
  `);
  return r.map((x) => ({ grader: String(x.grader), sales: int(x.sales), assets: int(x.assets), medianUsd: num(x.median_usd), populationReports: int(x.population_reports) }));
});

export const getDbGraders = cache(async () => {
  const r = await rows<Record<string, unknown>>(sql`SELECT slug, name, category_slugs, scale, population_url, verify_url FROM graders WHERE active ORDER BY slug`);
  return r.map((x) => ({ slug: String(x.slug), name: String(x.name), categorySlugs: (x.category_slugs as string[]) ?? [], scale: (x.scale as { values: string[]; top: string; type: string }) ?? { values: [], top: '', type: 'numeric' }, populationUrl: str(x.population_url), verifyUrl: str(x.verify_url) }));
});

// ---------- Coverage (Data page) ----------
export const getConnectorCoverage = cache(async () => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT c.id, c.display_name, c.source_id, src.name AS source_name, src.source_type, src.url AS source_url, src.trust_score, c.categories, c.status, c.refresh_frequency_minutes, c.last_success_at, c.supports_sold, c.supports_listings, c.supports_catalog, c.supports_auctions, c.supports_population,
      (SELECT count(*) FROM raw_records rr WHERE rr.connector_id = c.id) AS raw_records,
      (SELECT count(*) FROM sales s WHERE s.connector_id = c.id) AS sales,
      (SELECT count(*) FROM listings l WHERE l.connector_id = c.id) AS listings,
      (SELECT count(*) FROM price_observations o WHERE o.connector_id = c.id) AS observations,
      h.status AS health_status, h.health
    FROM connectors c JOIN sources src ON src.id = c.source_id LEFT JOIN connector_health h ON h.connector_id = c.id ORDER BY sales DESC, observations DESC, c.id
  `);
  return r.map((x) => ({ id: String(x.id), displayName: String(x.display_name), sourceId: String(x.source_id), sourceName: String(x.source_name), sourceType: String(x.source_type), sourceUrl: str(x.source_url), trustScore: Number(x.trust_score ?? 0), categories: (x.categories as string[]) ?? [], status: String(x.status), refreshMinutes: int(x.refresh_frequency_minutes), lastSuccessAt: date(x.last_success_at), supportsSold: Boolean(x.supports_sold), supportsListings: Boolean(x.supports_listings), supportsCatalog: Boolean(x.supports_catalog), supportsAuctions: Boolean(x.supports_auctions), supportsPopulation: Boolean(x.supports_population), rawRecords: int(x.raw_records), sales: int(x.sales), listings: int(x.listings), observations: int(x.observations), healthStatus: str(x.health_status), health: (x.health as Record<string, unknown>) ?? null }));
});

export const getFxCoverage = cache(async () => {
  const x = await one<Record<string, unknown>>(sql`SELECT count(*) AS n, min(date) AS first, max(date) AS last, count(DISTINCT quote) AS quotes FROM fx_rates`);
  return { rows: int(x?.n), first: str(x?.first), last: str(x?.last), quotes: int(x?.quotes) };
});

export const getTrendingCategories = cache(async (limit = 8) => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT a.category_slug, avg(s.trending_score) AS trending, avg(s.momentum_30d) AS momentum, count(*) AS assets FROM asset_stats s JOIN assets a ON a.id = s.asset_id WHERE s.trending_score IS NOT NULL GROUP BY a.category_slug ORDER BY trending DESC LIMIT ${limit}
  `);
  return r.map((x) => ({ categorySlug: String(x.category_slug), trending: Number(x.trending), momentum: num(x.momentum), assets: int(x.assets) }));
});