import 'server-only';
import { sql, type SQL } from 'drizzle-orm';
import { categoryScope } from '@/lib/queries/assets';
import { int, joinAnd, num, rows, str } from '@/lib/queries/_util';
import { SCREENER_EXPORT_MAX, type ScreenerFilters, type ScreenerSort } from '@/lib/screener';

/**
 * Market Screener rows (§158). Every derived metric is computed once in the inner query so filters,
 * sorts and the output agree exactly:
 *  - drawdown  = RIV / ATH − 1 (≤ 0, null without an ATH)
 *  - spread    = (lowest ask of the representative variant − RIV) / RIV, only when the valuation is
 *                transaction-grade (≥ 5 sales, confidence ≥ 0.5) and the ratio is plausible (0.1×–10×)
 *  - accel     = sales_30d / max(sales_1y / 12, 1)   (volume acceleration vs the trailing year)
 * Data-quality gates always on: a valuation exists and rests on ≥ 3 sales (§143, §196).
 */
export interface ScreenerRow {
  id: string;
  slug: string;
  title: string;
  categorySlug: string;
  familySlug: string;
  heroImageUrl: string | null;
  year: number | null;
  brand: string | null;
  setName: string | null;
  rivUsd: number;
  rivLowUsd: number | null;
  rivHighUsd: number | null;
  rivConfidence: number | null;
  rivSampleSize: number;
  change30d: number | null;
  change1y: number | null;
  liquidityScore: number | null;
  rarityScore: number | null;
  sales30d: number;
  sales1y: number;
  salesCount: number;
  activeListings: number;
  minAskUsd: number | null;
  spread: number | null;
  valueOpportunity: number | null;
  athUsd: number | null;
  drawdown: number | null;
  volume30dUsd: number | null;
  accel: number | null;
  updatedAt: Date | null;
}

const SORT_COL: Record<ScreenerSort, SQL> = {
  riv: sql`riv_usd`,
  confidence: sql`riv_confidence`,
  change30d: sql`change_30d`,
  change1y: sql`change_1y`,
  liquidity: sql`liquidity_score`,
  rarity: sql`rarity_score`,
  sales30d: sql`sales_30d`,
  sales1y: sql`sales_1y`,
  listings: sql`active_listings`,
  spread: sql`spread`,
  drawdown: sql`drawdown`,
  volume30d: sql`volume_30d_usd`,
  opportunity: sql`value_opportunity`,
  name: sql`title`,
};

const INNER = sql`
  SELECT a.id, a.slug, a.title, a.category_slug, a.family_slug, a.hero_image_url, a.year, a.brand, a.set_name, a.set_slug,
    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.change_30d, s.change_1y, s.liquidity_score, s.rarity_score,
    coalesce(s.sales_30d, 0) AS sales_30d, coalesce(s.sales_1y, 0) AS sales_1y, coalesce(s.sales_count, 0) AS sales_count,
    coalesce(s.active_listings, 0) AS active_listings, s.min_ask_usd, s.value_opportunity, s.ath_usd, s.volume_30d_usd, s.updated_at,
    CASE WHEN s.ath_usd > 0 THEN s.riv_usd / s.ath_usd - 1 END AS drawdown,
    CASE WHEN s.min_ask_usd > 0 AND s.riv_usd > 0 AND coalesce(s.riv_sample_size, 0) >= 5 AND s.riv_confidence >= 0.5
              AND s.min_ask_usd / s.riv_usd BETWEEN 0.1 AND 10
         THEN s.min_ask_usd / s.riv_usd - 1 END AS spread,
    coalesce(s.sales_30d, 0)::float / greatest(coalesce(s.sales_1y, 0) / 12.0, 1) AS accel
  FROM assets a JOIN asset_stats s ON s.asset_id = a.id
  WHERE s.riv_usd IS NOT NULL AND coalesce(s.riv_sample_size, 0) >= 3`;

function whereFor(f: ScreenerFilters): SQL {
  const w: SQL[] = [];
  if (f.category) w.push(sql`category_slug IN ${categoryScope(f.category)}`);
  if (f.brand) w.push(sql`lower(brand) = lower(${f.brand})`);
  if (f.set) w.push(sql`set_slug = ${f.set}`);
  if (f.rivMin !== null) w.push(sql`riv_usd >= ${f.rivMin}`);
  if (f.rivMax !== null) w.push(sql`riv_usd <= ${f.rivMax}`);
  if (f.confidenceMin !== null) w.push(sql`riv_confidence >= ${f.confidenceMin}`);
  if (f.liquidityMin !== null) w.push(sql`liquidity_score >= ${f.liquidityMin}`);
  if (f.rarityMin !== null) w.push(sql`rarity_score >= ${f.rarityMin}`);
  // change filters only consider plausible moves (|Δ| ≤ 500 %); artefacts never match a screen
  if (f.change30dMin !== null) w.push(sql`change_30d >= ${f.change30dMin} AND abs(change_30d) <= 5`);
  if (f.change30dMax !== null) w.push(sql`change_30d <= ${f.change30dMax} AND abs(change_30d) <= 5`);
  if (f.change1yMin !== null) w.push(sql`change_1y >= ${f.change1yMin} AND abs(change_1y) <= 5`);
  if (f.change1yMax !== null) w.push(sql`change_1y <= ${f.change1yMax} AND abs(change_1y) <= 5`);
  if (f.sales30dMin !== null) w.push(sql`sales_30d >= ${f.sales30dMin}`);
  if (f.sales1yMin !== null) w.push(sql`sales_1y >= ${f.sales1yMin}`);
  if (f.listingsMin !== null) w.push(sql`active_listings >= ${f.listingsMin}`);
  if (f.drawdownMin !== null) w.push(sql`drawdown >= ${f.drawdownMin}`);
  if (f.drawdownMax !== null) w.push(sql`drawdown <= ${f.drawdownMax}`);
  if (f.spreadMin !== null) w.push(sql`spread >= ${f.spreadMin}`);
  if (f.spreadMax !== null) w.push(sql`spread <= ${f.spreadMax}`);
  if (f.volumeAccelMin !== null) w.push(sql`accel >= ${f.volumeAccelMin}`);
  if (f.yearFrom !== null) w.push(sql`year >= ${f.yearFrom}`);
  if (f.yearTo !== null) w.push(sql`year <= ${f.yearTo}`);
  if (f.grader) w.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = x.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();
    w.push(sql`(title ILIKE ${'%' + q + '%'} OR title % ${q} OR set_name ILIKE ${'%' + q + '%'})`);
  }
  // the "Potentially Underpriced" sort/preset must never surface an ungated or implausible discount
  if (f.sort === 'opportunity') w.push(sql`value_opportunity IS NOT NULL AND value_opportunity >= -0.5 AND value_opportunity <= -0.1 AND riv_sample_size >= 5 AND riv_confidence >= 0.5`);
  return joinAnd(w);
}

function orderFor(f: ScreenerFilters): SQL {
  const col = SORT_COL[f.sort];
  const dir = f.dir === 'asc' ? sql`ASC` : sql`DESC`;
  return sql`${col} ${dir} NULLS LAST, riv_usd DESC NULLS LAST, id ASC`;
}

function toRow(x: Record<string, unknown>): ScreenerRow {
  return {
    id: String(x.id),
    slug: String(x.slug),
    title: String(x.title),
    categorySlug: String(x.category_slug),
    familySlug: String(x.family_slug),
    heroImageUrl: str(x.hero_image_url),
    year: num(x.year),
    brand: str(x.brand),
    setName: str(x.set_name),
    rivUsd: num(x.riv_usd) ?? 0,
    rivLowUsd: num(x.riv_low_usd),
    rivHighUsd: num(x.riv_high_usd),
    rivConfidence: num(x.riv_confidence),
    rivSampleSize: int(x.riv_sample_size),
    change30d: num(x.change_30d),
    change1y: num(x.change_1y),
    liquidityScore: num(x.liquidity_score),
    rarityScore: num(x.rarity_score),
    sales30d: int(x.sales_30d),
    sales1y: int(x.sales_1y),
    salesCount: int(x.sales_count),
    activeListings: int(x.active_listings),
    minAskUsd: num(x.min_ask_usd),
    spread: num(x.spread),
    valueOpportunity: num(x.value_opportunity),
    athUsd: num(x.ath_usd),
    drawdown: num(x.drawdown),
    volume30dUsd: num(x.volume_30d_usd),
    accel: num(x.accel),
    updatedAt: x.updated_at ? new Date(String(x.updated_at)) : null,
  };
}

export async function screenAssets(f: ScreenerFilters): Promise<{ items: ScreenerRow[]; total: number; page: number; pageSize: number }> {
  const pageSize = Math.min(Math.max(f.pageSize, 1), 100);
  const page = Math.max(1, f.page);
  const r = await rows<Record<string, unknown>>(sql`
    SELECT x.*, count(*) OVER() AS total FROM (${INNER}) x
    WHERE ${whereFor(f)} ORDER BY ${orderFor(f)} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
  `);
  return { items: r.map(toRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };
}

/** Export variant: no window count, hard cap (§160). */
export async function screenAssetsExport(f: ScreenerFilters, limit = SCREENER_EXPORT_MAX): Promise<ScreenerRow[]> {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT x.* FROM (${INNER}) x WHERE ${whereFor(f)} ORDER BY ${orderFor(f)} LIMIT ${Math.min(limit, SCREENER_EXPORT_MAX)}
  `);
  return r.map(toRow);
}