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';

// ---------- 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 >= ${f.minDiscount}`);
  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 DESC NULLS LAST`,
    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;
  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;
}

export async function listLots(opts: { status?: 'upcoming' | 'live' | 'ended' | null; endingWithinHours?: number | null; auctionId?: string | null; category?: string | null; limit?: number; sort?: 'ending' | 'value' } = {}): Promise<LotRow[]> {
  const where: SQL[] = [];
  if (opts.status) where.push(sql`l.status = ${opts.status}`);
  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.category) where.push(sql`a.category_slug IN ${categoryScope(opts.category)}`);
  const order = opts.sort === 'value' ? sql`coalesce(l.hammer_price, l.current_bid, l.estimate_high) DESC NULLS LAST` : sql`l.ends_at ASC NULLS LAST`;
  const r = await rows<Record<string, unknown>>(sql`
    SELECT 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
    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
    WHERE ${joinAnd(where)} ORDER BY ${order} LIMIT ${opts.limit ?? 50}
  `);
  return r.map((x) => ({ id: String(x.id), auctionId: String(x.auction_id), auctionName: str(x.auction_name), auctionHouse: str(x.auction_house), 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) }));
}

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) }));
});