import 'server-only';
import { cache } from 'react';
import { maxDrawdown, volatility, pctChange } from '@rareindex/shared';
import { rows, one, sql, num, int, str } from './_util';

export interface IndexRow {
  id: string;
  ticker: string;
  name: string;
  description: string | null;
  color: string | null;
  isFlagship: boolean;
  parentTicker: string | null;
  familySlugs: string[];
  categorySlugs: string[];
  minConstituents: number;
  baseDate: string;
  baseValue: number;
  methodology: string;
  weighting: string;
  latest: IndexPoint | null;
  change1d: number | null;
  change7d: number | null;
  change30d: number | null;
  changeYtd: number | null;
  change1y: number | null;
  /** priced constituents today (from index_constituents active) */
  constituents: number;
  pricedAssets: number;
  firstDate: string | null;
  points: number;
}

export interface IndexPoint {
  date: string;
  value: number;
  constituentsCount: number;
  transactions: number;
  volumeUsd: number | null;
  medianSaleUsd: number | null;
  avgSaleUsd: number | null;
  marketCapEstUsd: number | null;
  marketCapConfidence: string | null;
  liquidityScore: number | null;
  momentum: number | null;
  breadth: number | null;
  trackedAssets: number | null;
  coverage: number | null;
}

function toPoint(x: Record<string, unknown>): IndexPoint {
  return {
    date: String(x.date),
    value: Number(x.value),
    constituentsCount: int(x.constituents_count),
    transactions: int(x.transactions),
    volumeUsd: num(x.volume_usd),
    medianSaleUsd: num(x.median_sale_usd),
    avgSaleUsd: num(x.avg_sale_usd),
    marketCapEstUsd: num(x.market_cap_est_usd),
    marketCapConfidence: str(x.market_cap_confidence),
    liquidityScore: num(x.liquidity_score),
    momentum: num(x.momentum),
    breadth: num(x.breadth),
    trackedAssets: num(x.tracked_assets),
    coverage: num(x.coverage),
  };
}

/** All indices with latest value and window changes computed from index_values. */
export const listIndices = cache(async (): Promise<IndexRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    WITH latest AS (
      SELECT DISTINCT ON (index_id) * FROM index_values ORDER BY index_id, date DESC
    ), agg AS (
      SELECT index_id, min(date) AS first_date, count(*) AS points FROM index_values GROUP BY index_id
    ), cons AS (
      SELECT index_id, count(*) AS n FROM index_constituents WHERE removed_at IS NULL GROUP BY index_id
    )
    SELECT i.*, to_jsonb(l.*) AS latest, g.first_date, coalesce(g.points, 0) AS points, coalesce(c.n, 0) AS constituents,
      (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 1 ORDER BY v.date DESC LIMIT 1) AS v_1d,
      (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 7 ORDER BY v.date DESC LIMIT 1) AS v_7d,
      (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 30 ORDER BY v.date DESC LIMIT 1) AS v_30d,
      (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= date_trunc('year', l.date)::date ORDER BY v.date DESC LIMIT 1) AS v_ytd,
      (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 365 ORDER BY v.date DESC LIMIT 1) AS v_1y,
      (SELECT count(*) FROM assets a JOIN asset_stats s ON s.asset_id = a.id WHERE s.riv_usd IS NOT NULL AND (i.is_flagship OR a.family_slug = ANY(i.family_slugs))) AS priced_assets
    FROM indices i
    LEFT JOIN latest l ON l.index_id = i.id
    LEFT JOIN agg g ON g.index_id = i.id
    LEFT JOIN cons c ON c.index_id = i.id
    WHERE i.active
    ORDER BY i.is_flagship DESC, i.ticker
  `);
  return r.map((x) => {
    const latest = x.latest ? toPoint(x.latest as Record<string, unknown>) : null;
    return {
      id: String(x.id),
      ticker: String(x.ticker),
      name: String(x.name),
      description: str(x.description),
      color: str(x.color),
      isFlagship: Boolean(x.is_flagship),
      parentTicker: str(x.parent_ticker),
      familySlugs: (x.family_slugs as string[]) ?? [],
      categorySlugs: (x.category_slugs as string[]) ?? [],
      minConstituents: int(x.min_constituents),
      baseDate: String(x.base_date),
      baseValue: Number(x.base_value),
      methodology: String(x.methodology),
      weighting: String(x.weighting),
      latest,
      change1d: latest ? pctChange(num(x.v_1d), latest.value) : null,
      change7d: latest ? pctChange(num(x.v_7d), latest.value) : null,
      change30d: latest ? pctChange(num(x.v_30d), latest.value) : null,
      changeYtd: latest ? pctChange(num(x.v_ytd), latest.value) : null,
      change1y: latest ? pctChange(num(x.v_1y), latest.value) : null,
      constituents: int(x.constituents),
      pricedAssets: int(x.priced_assets),
      firstDate: str(x.first_date),
      points: int(x.points),
    };
  });
});

export const getIndex = cache(async (ticker: string): Promise<IndexRow | null> => {
  const all = await listIndices();
  return all.find((i) => i.ticker.toLowerCase() === ticker.toLowerCase()) ?? null;
});

export const getIndexSeries = cache(async (indexId: string, days: number | null = null): Promise<IndexPoint[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT * FROM index_values WHERE index_id = ${indexId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date ASC
  `);
  return r.map(toPoint);
});

export interface IndexAnalytics {
  returns: Record<'1d' | '7d' | '30d' | 'ytd' | '1y' | '3y' | '5y' | '10y' | 'all', number | null>;
  volatility: number | null;
  maxDrawdown: number | null;
  high: { value: number; date: string } | null;
  low: { value: number; date: string } | null;
}

export function analyzeSeries(points: IndexPoint[]): IndexAnalytics {
  const empty: IndexAnalytics = { returns: { '1d': null, '7d': null, '30d': null, ytd: null, '1y': null, '3y': null, '5y': null, '10y': null, all: null }, volatility: null, maxDrawdown: null, high: null, low: null };
  if (!points.length) return empty;
  const last = points[points.length - 1]!;
  const lastDate = new Date(`${last.date}T00:00:00Z`);
  const at = (daysBack: number): number | null => {
    const target = lastDate.getTime() - daysBack * 86_400_000;
    let best: IndexPoint | null = null;
    for (const p of points) {
      if (new Date(`${p.date}T00:00:00Z`).getTime() <= target) best = p;
      else break;
    }
    return best?.value ?? null;
  };
  const ytdStart = points.find((p) => p.date >= `${last.date.slice(0, 4)}-01-01`);
  const values = points.map((p) => p.value);
  let high = points[0]!;
  let low = points[0]!;
  for (const p of points) {
    if (p.value > high.value) high = p;
    if (p.value < low.value) low = p;
  }
  return {
    returns: {
      '1d': pctChange(at(1), last.value),
      '7d': pctChange(at(7), last.value),
      '30d': pctChange(at(30), last.value),
      ytd: ytdStart && ytdStart !== last ? pctChange(ytdStart.value, last.value) : null,
      '1y': pctChange(at(365), last.value),
      '3y': pctChange(at(365 * 3), last.value),
      '5y': pctChange(at(365 * 5), last.value),
      '10y': pctChange(at(365 * 10), last.value),
      all: points.length > 1 ? pctChange(points[0]!.value, last.value) : null,
    },
    volatility: volatility(values),
    maxDrawdown: maxDrawdown(values),
    high: { value: high.value, date: high.date },
    low: { value: low.value, date: low.date },
  };
}

export interface ConstituentRow {
  assetId: string;
  slug: string;
  title: string;
  categorySlug: string;
  variantLabel: string | null;
  weight: number;
  addedAt: string;
  rivUsd: number | null;
  change30d: number | null;
  liquidityScore: number | null;
  salesCount: number;
}

export const getIndexConstituents = cache(async (indexId: string, limit = 100): Promise<ConstituentRow[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT c.asset_id, a.slug, a.title, a.category_slug, v.label AS variant_label, c.weight, c.added_at, s.riv_usd, s.change_30d, s.liquidity_score, coalesce(s.sales_count, 0) AS sales_count
    FROM index_constituents c JOIN assets a ON a.id = c.asset_id
    LEFT JOIN asset_variants v ON v.id = c.variant_id LEFT JOIN asset_stats s ON s.asset_id = a.id
    WHERE c.index_id = ${indexId} AND c.removed_at IS NULL
    ORDER BY c.weight DESC, s.riv_usd DESC NULLS LAST LIMIT ${limit}
  `);
  return r.map((x) => ({ assetId: String(x.asset_id), slug: String(x.slug), title: String(x.title), categorySlug: String(x.category_slug), variantLabel: str(x.variant_label), weight: Number(x.weight), addedAt: String(x.added_at), rivUsd: num(x.riv_usd), change30d: num(x.change_30d), liquidityScore: num(x.liquidity_score), salesCount: int(x.sales_count) }));
});

export const getCorrelations = cache(async (ticker: string): Promise<Array<{ other: string; coefficient: number; observations: number; windowDays: number }>> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT CASE WHEN a = ${ticker} THEN b ELSE a END AS other, coefficient, observations, window_days FROM correlations WHERE a = ${ticker} OR b = ${ticker} ORDER BY window_days, other
  `);
  return r.map((x) => ({ other: String(x.other), coefficient: Number(x.coefficient), observations: int(x.observations), windowDays: int(x.window_days) }));
});

export const getCorrelationMatrix = cache(async (windowDays = 365): Promise<{ tickers: string[]; matrix: Record<string, Record<string, number>> }> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT a, b, coefficient FROM correlations WHERE window_days = ${windowDays}`);
  const tickers = new Set<string>();
  const matrix: Record<string, Record<string, number>> = {};
  for (const x of r) {
    const a = String(x.a);
    const b = String(x.b);
    tickers.add(a);
    tickers.add(b);
    (matrix[a] ??= {})[b] = Number(x.coefficient);
    (matrix[b] ??= {})[a] = Number(x.coefficient);
  }
  return { tickers: [...tickers].sort(), matrix };
});

export const getBenchmarkSeries = cache(async (ticker: string, days: number | null = null): Promise<Array<{ date: string; value: number; source: string }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT date, value, source FROM benchmarks WHERE ticker = ${ticker} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date`);
  return r.map((x) => ({ date: String(x.date), value: Number(x.value), source: String(x.source) }));
});

export const listBenchmarkTickers = cache(async (): Promise<Array<{ ticker: string; source: string; points: number; last: string }>> => {
  const r = await rows<Record<string, unknown>>(sql`SELECT ticker, min(source) AS source, count(*) AS points, max(date) AS last FROM benchmarks GROUP BY ticker ORDER BY ticker`);
  return r.map((x) => ({ ticker: String(x.ticker), source: String(x.source), points: int(x.points), last: String(x.last) }));
});

export const getIndexLatestOnly = cache(async (): Promise<Record<string, { value: number; change1d: number | null; date: string }>> => {
  const all = await listIndices();
  const out: Record<string, { value: number; change1d: number | null; date: string }> = {};
  for (const i of all) if (i.latest) out[i.ticker] = { value: i.latest.value, change1d: i.change1d, date: i.latest.date };
  return out;
});

export async function getIndexById(id: string): Promise<IndexRow | null> {
  const r = await one<Record<string, unknown>>(sql`SELECT ticker FROM indices WHERE id = ${id}`);
  return r ? getIndex(String(r.ticker)) : null;
}