import 'server-only';
import { cache } from 'react';
import { CATEGORIES, FAMILIES, getCategory, type CategoryNode } from '@rareindex/taxonomy';
import { rows, one, sql, num, int, str, date, textArray } from './_util';
import { categoryScope, getSetFromAssets } from './assets';

export interface CategorySnapshot {
  categorySlug: string;
  date: string;
  indexValue: number | null;
  trackedAssets: number;
  assetsWithValuation: number;
  sales: number;
  volumeUsd: number | null;
  medianSaleUsd: number | null;
  activeListings: number;
  marketCapEstUsd: number | null;
  liquidityScore: number | null;
  change1d: number | null;
  change7d: number | null;
  change30d: number | null;
  change1y: number | null;
}

function toSnap(x: Record<string, unknown>): CategorySnapshot {
  return { categorySlug: String(x.category_slug), date: String(x.date), indexValue: num(x.index_value), trackedAssets: int(x.tracked_assets), assetsWithValuation: int(x.assets_with_valuation), sales: int(x.sales), volumeUsd: num(x.volume_usd), medianSaleUsd: num(x.median_sale_usd), activeListings: int(x.active_listings), marketCapEstUsd: num(x.market_cap_est_usd), liquidityScore: num(x.liquidity_score), change1d: num(x.change_1d), change7d: num(x.change_7d), change30d: num(x.change_30d), change1y: num(x.change_1y) };
}

/** Latest snapshot per category (may be missing for categories without data). */
export const getLatestCategorySnapshots = cache(async (): Promise<Map<string, CategorySnapshot>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT DISTINCT ON (category_slug) * FROM category_snapshots ORDER BY category_slug, date DESC`);
  return new Map(r.map((x) => [String(x.category_slug), toSnap(x)]));
});

/** Live counts straight from the canonical tables (works even before snapshots exist). */
export interface CategoryCounts {
  slug: string;
  assets: number;
  priced: number;
  sales: number;
  sales30d: number;
  volume30dUsd: number | null;
  listings: number;
  observations: number;
  medianRivUsd: number | null;
  lastSaleAt: Date | null;
}

export const getCategoryCounts = cache(async (): Promise<Map<string, CategoryCounts>> => {
  // Counts come from the canonical tables directly (not asset_stats) so they are right even before the valuation worker runs.
  const r = await rows<Record<string, unknown>>(sql`
    WITH sa AS (
      SELECT a.category_slug AS slug, count(*) AS sales, count(*) FILTER (WHERE s.sale_date >= now() - interval '30 days') AS sales_30d,
             sum(s.price_usd) FILTER (WHERE s.sale_date >= now() - interval '30 days') AS volume_30d, max(s.sale_date) AS last_sale_at
      FROM sales s JOIN assets a ON a.id = s.asset_id WHERE s.status = 'valid' GROUP BY a.category_slug
    ), li AS (
      SELECT a.category_slug AS slug, count(*) AS listings FROM listings l JOIN assets a ON a.id = l.asset_id WHERE l.availability = 'available' GROUP BY a.category_slug
    ), ob AS (
      SELECT a.category_slug AS slug, count(*) AS observations FROM price_observations o JOIN assets a ON a.id = o.asset_id GROUP BY a.category_slug
    ), st AS (
      SELECT a.category_slug AS slug, count(*) AS assets, count(s.riv_usd) AS priced, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv
      FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id GROUP BY a.category_slug
    )
    SELECT st.slug, st.assets, st.priced, coalesce(sa.sales, 0) AS sales, coalesce(sa.sales_30d, 0) AS sales_30d, sa.volume_30d, coalesce(li.listings, 0) AS listings,
           coalesce(ob.observations, 0) AS observations, st.median_riv, sa.last_sale_at
    FROM st LEFT JOIN sa ON sa.slug = st.slug LEFT JOIN li ON li.slug = st.slug LEFT JOIN ob ON ob.slug = st.slug
  `);
  return new Map(r.map((x) => [String(x.slug), { slug: String(x.slug), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), sales30d: int(x.sales_30d), volume30dUsd: num(x.volume_30d), listings: int(x.listings), observations: int(x.observations), medianRivUsd: num(x.median_riv), lastSaleAt: date(x.last_sale_at) }]));
});

export interface MarketRow {
  node: CategoryNode;
  snapshot: CategorySnapshot | null;
  counts: CategoryCounts; // aggregated over the node and its descendants
}

function aggregate(slugs: string[], counts: Map<string, CategoryCounts>, slug: string): CategoryCounts {
  const out: CategoryCounts = { slug, assets: 0, priced: 0, sales: 0, sales30d: 0, volume30dUsd: null, listings: 0, observations: 0, medianRivUsd: null, lastSaleAt: null };
  for (const s of slugs) {
    const c = counts.get(s);
    if (!c) continue;
    out.assets += c.assets;
    out.priced += c.priced;
    out.sales += c.sales;
    out.sales30d += c.sales30d;
    out.listings += c.listings;
    out.observations += c.observations;
    if (c.volume30dUsd != null) out.volume30dUsd = (out.volume30dUsd ?? 0) + c.volume30dUsd;
    if (c.lastSaleAt && (!out.lastSaleAt || c.lastSaleAt > out.lastSaleAt)) out.lastSaleAt = c.lastSaleAt;
    if (out.medianRivUsd == null && c.medianRivUsd != null) out.medianRivUsd = c.medianRivUsd;
  }
  return out;
}

/** Market overview rows for families (level 0) or children of a family. */
export const getMarketRows = cache(async (parent: string | null = null): Promise<MarketRow[]> => {
  const [snaps, counts] = await Promise.all([getLatestCategorySnapshots(), getCategoryCounts()]);
  const nodes = parent ? CATEGORIES.filter((c) => c.parent === parent) : FAMILIES;
  return nodes.map((node) => ({ node, snapshot: snaps.get(node.slug) ?? null, counts: aggregate(categoryScope(node.slug), counts, node.slug) }));
});

export const getCategorySeries = cache(async (slug: string, days: number | null = null): Promise<CategorySnapshot[]> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT * FROM category_snapshots WHERE category_slug = ${slug} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date`);
  return r.map(toSnap);
});

/** Compact per-category index series (values only) for sparklines; categories without snapshots are absent. */
export const getCategorySparklines = cache(async (slugs: string[], days = 90): Promise<Map<string, number[]>> => {
  if (!slugs.length) return new Map();
  const r = await rows<{ category_slug: string; values: unknown }>(sql`
    SELECT category_slug, array_agg(index_value ORDER BY date) AS values
    FROM category_snapshots
    WHERE category_slug = ANY(${textArray(slugs)}) AND index_value IS NOT NULL AND date >= current_date - ${days}::int
    GROUP BY category_slug
  `);
  const out = new Map<string, number[]>();
  for (const x of r) {
    const vals = (Array.isArray(x.values) ? x.values : []).map((v) => Number(v)).filter((v) => Number.isFinite(v));
    if (vals.length >= 3) out.set(String(x.category_slug), vals);
  }
  return out;
});

export const getCategoryRow = cache(async (slug: string): Promise<MarketRow | null> => {
  const node = getCategory(slug);
  if (!node) return null;
  const [snaps, counts] = await Promise.all([getLatestCategorySnapshots(), getCategoryCounts()]);
  return { node, snapshot: snaps.get(slug) ?? null, counts: aggregate(categoryScope(slug), counts, slug) };
});

export const getSetsInCategory = cache(async (slug: string, limit = 60): Promise<Array<{ slug: string; name: string; code: string | null; releaseYear: number | null; assets: number; priced: number; sales: number; thumb: string | null }>> => {
  // Grouped from the assets table (LEFT JOIN sets for metadata) so releases appear as soon as catalog items land.
  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 release_year,
      count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales,
      (array_agg(a.hero_image_url) FILTER (WHERE a.hero_image_url IS NOT NULL))[1] AS thumb
    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 ${categoryScope(slug)} AND a.set_slug IS NOT NULL
    GROUP BY a.set_slug, st.name, st.code, st.release_year ORDER BY sales DESC, assets DESC, release_year DESC NULLS LAST LIMIT ${limit}
  `);
  return r.map((x) => ({ slug: String(x.slug), name: String(x.name ?? x.slug), code: str(x.code), releaseYear: num(x.release_year), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), thumb: str(x.thumb) }));
});

export const getBrandsInCategory = cache(async (slug: string, limit = 40): Promise<Array<{ brand: string; assets: number; priced: number; sales: number; medianRivUsd: number | null }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT a.brand, count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.brand IS NOT NULL AND a.category_slug IN ${categoryScope(slug)}
    GROUP BY a.brand ORDER BY sales DESC, assets DESC LIMIT ${limit}
  `);
  return r.map((x) => ({ brand: String(x.brand), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), medianRivUsd: num(x.median_riv) }));
});

export const getSet = cache(async (slug: string) => {
  const x = await one<Record<string, unknown>>(sql`SELECT * FROM sets WHERE slug = ${slug}`);
  if (!x) return getSetFromAssets(slug);
  return { slug: String(x.slug), name: String(x.name), code: str(x.code), categorySlug: String(x.category_slug), franchiseSlug: str(x.franchise_slug), brandSlug: str(x.brand_slug), releaseYear: num(x.release_year), releaseDate: str(x.release_date), language: str(x.language), totalItems: num(x.total_items), identifiers: (x.identifiers as Record<string, string>) ?? {}, metadata: (x.metadata as Record<string, unknown>) ?? {} };
});

export const getSetStats = cache(async (slug: string) => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT count(a.id) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, coalesce(sum(s.active_listings), 0) AS listings, sum(s.riv_usd) AS sum_riv, max(s.riv_usd) AS max_riv, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv, max(s.latest_sale_at) AS last_sale_at
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.set_slug = ${slug}
  `);
  return { assets: int(x?.assets), priced: int(x?.priced), sales: int(x?.sales), listings: int(x?.listings), sumRivUsd: num(x?.sum_riv), maxRivUsd: num(x?.max_riv), medianRivUsd: num(x?.median_riv), lastSaleAt: date(x?.last_sale_at) };
});

export const getBrandStats = cache(async (brand: string) => {
  const x = await one<Record<string, unknown>>(sql`
    SELECT min(a.brand) AS brand, count(a.id) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, coalesce(sum(s.active_listings), 0) AS listings, max(s.riv_usd) AS max_riv, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv, array_agg(DISTINCT a.category_slug) AS categories
    FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE lower(a.brand) = lower(${brand})
  `);
  if (!x || int(x.assets) === 0) return null;
  return { brand: String(x.brand), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), listings: int(x.listings), maxRivUsd: num(x.max_riv), medianRivUsd: num(x.median_riv), categories: (x.categories as string[]) ?? [] };
});

export const getSetSlugsForSitemap = cache(async (): Promise<string[]> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT slug FROM sets UNION SELECT DISTINCT set_slug FROM assets WHERE set_slug IS NOT NULL ORDER BY 1 LIMIT 20000`);
  return r.map((x) => String(x.slug));
});

export const getPopulationTrend = cache(async (slug: string): Promise<Array<{ grader: string; reportDate: string; total: number; assets: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT p.grader, p.report_date, sum(p.total) AS total, count(DISTINCT p.asset_id) AS assets FROM population_reports p JOIN assets a ON a.id = p.asset_id
    WHERE a.category_slug IN ${categoryScope(slug)} GROUP BY p.grader, p.report_date ORDER BY p.grader, p.report_date
  `);
  return r.map((x) => ({ grader: String(x.grader), reportDate: String(x.report_date), total: int(x.total), assets: int(x.assets) }));
});