Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

41.8 KB
Raw Blame
/**
 * Trader track-record truth layer.
 *
 * The single source of truth for every "how good is this trader" number on
 * three.ws — the leaderboard, the trader profile, the Proof tab, the copy-trading
 * verification badge. Built on `agent_sniper_positions`, which is the canonical
 * per-position ledger: each row already carries realized P&L (lamports + pct),
 * entry/exit SOL, hold window (opened_at → closed_at), and the on-chain buy/sell
 * signatures that PROVE every number. We never invent a metric we can't trace to
 * a tx.
 *
 * Two layers, by design:
 *   - computeTraderMetrics(positions, opts)  — PURE. No DB, no network. Deterministic
 *     over an array of position rows. This is what the tests pin to fixtures, and
 *     what guarantees the leaderboard and the profile can never disagree.
 *   - getTraderStats / getLeaderboard        — fetch rows, price SOL→USD, attach
 *     identity, then defer all arithmetic to computeTraderMetrics.
 *
 * Honesty rules baked in:
 *   - SOL amounts are exact (from chain). USD is an enrichment that degrades to
 *     null if the price feed is down — we never fabricate a dollar figure.
 *   - `churn_pct` is an explicit heuristic (near-flat in-and-out churn) and is
 *     named as such.
 *   - Survivorship-honest: closed losers are counted, never hidden.
 *   - Anti-gaming, when the caller supplies the evidence: realized P&L from
 *     round-trips on the trader's OWN coins (`selfDealMints`) is NOT credited to
 *     the verifiable record — you cannot pump a token you launched and call it a
 *     track record. The excluded total is reported transparently, never silently
 *     dropped. Snipe hit-rate (`mintCreatedAt`) is the win rate on entries made
 *     within minutes of a coin's on-chain birth — the hardest, most-faked flex,
 *     here computed only over launches whose creation slot we can actually prove.
 */

import { sql } from './db.js';
import { solUsdPrice } from './avatar-wallet.js';
import { actionsSummary } from './oracle/store.js';

const LAMPORTS_PER_SOL = 1e9;

/** Supported leaderboard / profile time windows. */
export const WINDOWS = new Set(['24h', '7d', '30d', 'all']);
const WINDOW_MS = { '24h': 86_400_000, '7d': 604_800_000, '30d': 2_592_000_000 };

/** ISO lower-bound for a window, or null for 'all'. `now` injectable for tests. */
export function windowStartIso(window, now = Date.now()) {
	const span = WINDOW_MS[window];
	return span ? new Date(now - span).toISOString() : null;
}

// --- Score / badge tuning. Transparent, named, tunable. -----------------------
// A trader's composite score (0–100) is a documented weighted blend of win rate,
// profit factor, signed realized P&L, consistency (Sharpe-ish), drawdown, and
// churn — regressed toward neutral until they have enough closed trades to trust.
const SCORE_WEIGHTS = { winRate: 0.30, profitFactor: 0.22, pnl: 0.26, consistency: 0.12, drawdown: 0.10 };
const CHURN_SCORE_PENALTY = 0.10; // churn subtracts up to this much from raw score
const CONFIDENCE_FULL_AT = 14;    // closed trades for full statistical confidence
const NEUTRAL_RAW = 0.42;         // unproven traders regress toward this (slightly below mid)
const PNL_TANH_SOL = 5;           // ~5 SOL realized ≈ a strongly positive pnl sub-score
const SHARPE_TANH = 2;            // consistency normalization

// Verification badge ("proven track record") gate — every condition must hold.
const BADGE = { minClosed: 12, minUniqueCoins: 5, maxChurnPct: 40 };

// Churn heuristic: a position opened and closed inside this window with a
// near-flat result is in-and-out churn that pads trade counts without real risk.
const CHURN_HOLD_SECONDS = 25;
const CHURN_FLAT_PCT = 1.5;

// Snipe window: an entry is a "snipe" when it lands within this long after the
// coin's on-chain birth. 5 minutes is generous enough to survive the gap between
// our DB open timestamp and the chain's block time, tight enough to mean "early".
const SNIPE_WINDOW_MS = 5 * 60_000;
// Grace for clock skew between our recorded open time and the launch block time —
// a buy can never truly precede the mint, so a few seconds of negative drift is noise.
const SNIPE_SKEW_GRACE_MS = 3_000;

const big = (v) => {
	try { return Number(BigInt(v)); } catch { return Number(v) || 0; }
};
const clamp = (x, lo, hi) => Math.min(hi, Math.max(lo, x));
const tanh = (x) => Math.tanh(x);

function median(sorted) {
	if (!sorted.length) return 0;
	const mid = Math.floor(sorted.length / 2);
	return sorted.length % 2 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;
}

function stddev(values, mean) {
	if (values.length < 2) return 0;
	const variance = values.reduce((a, v) => a + (v - mean) ** 2, 0) / (values.length - 1);
	return Math.sqrt(variance);
}

/**
 * Max drawdown over the realized equity curve (cumulative realized P&L in
 * lamports, ordered oldest→newest close). Returns both the absolute SOL drop from
 * a running peak and that drop as a pct of the peak equity. A monotonically
 * rising book has zero drawdown.
 */
function maxDrawdown(closedOrderedLamports) {
	let equity = 0, peak = 0, maxDropLamports = 0, maxDropPct = 0;
	for (const pnl of closedOrderedLamports) {
		equity += pnl;
		if (equity > peak) peak = equity;
		const drop = peak - equity;
		if (drop > maxDropLamports) {
			maxDropLamports = drop;
			maxDropPct = peak > 0 ? (drop / peak) * 100 : 0;
		}
	}
	return { sol: maxDropLamports / LAMPORTS_PER_SOL, pct: maxDropPct };
}

/**
 * Compact cumulative-realized-equity series (SOL) for a sparkline. Takes the
 * per-close realized P&L in lamports, oldest→newest, and returns the running
 * equity in SOL downsampled to at most `maxPoints` points — small enough to ship
 * on every one of the ~100 leaderboard rows without bloating the payload, yet
 * enough to read the shape of the run. The last point is always the final
 * realized equity so the sparkline's endpoint matches the row's headline P&L.
 * Fewer than two closes returns [] (the caller renders a flat "no trend yet"
 * placeholder rather than a misleading single dot).
 */
function cumulativeEquitySeries(pnlsLamports, maxPoints = 24) {
	if (!Array.isArray(pnlsLamports) || pnlsLamports.length < 2) return [];
	const equity = [];
	let running = 0;
	for (const p of pnlsLamports) {
		running += p;
		equity.push(running / LAMPORTS_PER_SOL);
	}
	if (equity.length <= maxPoints) return equity.map((v) => Number(v.toFixed(4)));
	// Even stride, but force-include the final point so the endpoint is exact.
	const out = [];
	const stride = (equity.length - 1) / (maxPoints - 1);
	for (let i = 0; i < maxPoints - 1; i++) out.push(equity[Math.round(i * stride)]);
	out.push(equity[equity.length - 1]);
	return out.map((v) => Number(v.toFixed(4)));
}

/**
 * Compute the full metric set for one trader from their position rows.
 *
 * @param {Array<object>} positions  agent_sniper_positions rows (closed + open).
 *   Required fields per row: status, realized_pnl_lamports, realized_pnl_pct,
 *   entry_quote_lamports, exit_quote_lamports, last_value_lamports, mint,
 *   opened_at, closed_at.
 * @param {object} [opts]
 * @param {number|null} [opts.solUsd]  USD per SOL, or null to omit USD fields.
 * @param {Set<string>|Record<string,true>|null} [opts.selfDealMints]  Mints the
 *   trader's own account launched/created. Positions on these are split out of the
 *   credited record (anti-gaming) and reported under `self_dealing_*`. Omit/null
 *   to credit every position (legacy behaviour).
 * @param {Map<string,number|string>|Record<string,number|string>|null} [opts.mintCreatedAt]
 *   Mint → on-chain creation time (ms epoch or ISO). Drives snipe hit-rate. Only
 *   mints present here count toward the snipe sample — unknown launches are not
 *   guessed at.
 * @returns {object} canonical metrics — see fields below.
 */
export function computeTraderMetrics(positions, { solUsd = null, selfDealMints = null, mintCreatedAt = null } = {}) {
	const isSelfDeal = (mint) =>
		!!(mint && selfDealMints && (typeof selfDealMints.has === 'function' ? selfDealMints.has(mint) : selfDealMints[mint]));
	const launchOf = (mint) => {
		if (!mint || !mintCreatedAt) return null;
		const v = typeof mintCreatedAt.get === 'function' ? mintCreatedAt.get(mint) : mintCreatedAt[mint];
		if (v == null) return null;
		const t = typeof v === 'number' ? v : Date.parse(v);
		return Number.isFinite(t) ? t : null;
	};

	// Anti-gaming: round-trips on the trader's OWN coins are split out before any
	// credited metric is computed. They are counted and reported, never hidden, but
	// they cannot inflate the verifiable record.
	const selfDealPositions = positions.filter((p) => isSelfDeal(p.mint));
	const credited = selfDealPositions.length ? positions.filter((p) => !isSelfDeal(p.mint)) : positions;

	const closed = credited.filter((p) => p.status === 'closed');
	const open = credited.filter((p) => p.status === 'open' || p.status === 'opening' || p.status === 'closing');

	// --- Realized (closed positions) ---
	let realizedLamports = 0n;
	let grossProfit = 0n, grossLoss = 0n; // grossLoss kept positive
	let wins = 0, losses = 0;
	let invested = 0n;
	const pnlPcts = [];
	const winPcts = [], lossPcts = [];
	const holdSeconds = [];
	const coins = new Set();
	// Per-coin realized P&L aggregation — the "what they made it on" breakdown. A
	// trader can round-trip the same mint several times, so we sum lamports per mint
	// and pick the winner(s) after the loop. Provable: every lamport traces to a
	// closed position's realized_pnl_lamports.
	const coinAgg = new Map(); // mint -> { pnl, invested, trades, wins, symbol, name }
	let churn = 0;
	let bestPct = null, worstPct = null;
	let firstActive = null, lastActive = null;
	// Snipe hit-rate: only positions whose mint has a proven on-chain birth count
	// toward the sample, so the number is honest about its own coverage.
	let snipeSample = 0, snipeCount = 0, snipeWins = 0;

	// Oldest→newest by close time for the equity curve.
	const closedOrdered = [...closed].sort(
		(a, b) => new Date(a.closed_at || a.opened_at).getTime() - new Date(b.closed_at || b.opened_at).getTime(),
	);
	const equityPnls = [];

	for (const p of closedOrdered) {
		const pnl = BigInt(p.realized_pnl_lamports ?? 0);
		realizedLamports += pnl;
		equityPnls.push(big(p.realized_pnl_lamports));
		if (pnl > 0n) { wins += 1; grossProfit += pnl; } else if (pnl < 0n) { losses += 1; grossLoss += -pnl; }
		invested += BigInt(p.entry_quote_lamports ?? 0);
		if (p.mint) {
			coins.add(p.mint);
			let ca = coinAgg.get(p.mint);
			if (!ca) { ca = { pnl: 0n, invested: 0n, trades: 0, wins: 0, symbol: p.symbol || null, name: p.name || null }; coinAgg.set(p.mint, ca); }
			ca.pnl += pnl;
			ca.invested += BigInt(p.entry_quote_lamports ?? 0);
			ca.trades += 1;
			if (pnl > 0n) ca.wins += 1;
			if (!ca.symbol && p.symbol) ca.symbol = p.symbol;
			if (!ca.name && p.name) ca.name = p.name;
		}

		const pct = p.realized_pnl_pct != null ? Number(p.realized_pnl_pct) : null;
		if (pct != null && Number.isFinite(pct)) {
			pnlPcts.push(pct);
			if (pct > 0) winPcts.push(pct); else if (pct < 0) lossPcts.push(pct);
			bestPct = bestPct == null ? pct : Math.max(bestPct, pct);
			worstPct = worstPct == null ? pct : Math.min(worstPct, pct);
		}

		const opened = new Date(p.opened_at).getTime();
		const closedAt = new Date(p.closed_at || p.opened_at).getTime();
		const held = Math.max(0, (closedAt - opened) / 1000);
		holdSeconds.push(held);
		if (held <= CHURN_HOLD_SECONDS && pct != null && Math.abs(pct) <= CHURN_FLAT_PCT) churn += 1;

		const launchMs = launchOf(p.mint);
		if (launchMs != null) {
			snipeSample += 1;
			const sinceLaunch = opened - launchMs;
			if (sinceLaunch >= -SNIPE_SKEW_GRACE_MS && sinceLaunch <= SNIPE_WINDOW_MS) {
				snipeCount += 1;
				if (pnl > 0n) snipeWins += 1;
			}
		}

		if (firstActive == null || opened < firstActive) firstActive = opened;
		if (lastActive == null || closedAt > lastActive) lastActive = closedAt;
	}

	// --- Unrealized (open positions) ---
	let openExposure = 0n;
	let unrealizedLamports = 0n;
	for (const p of open) {
		const entry = BigInt(p.entry_quote_lamports ?? 0);
		const last = p.last_value_lamports != null ? BigInt(p.last_value_lamports) : entry;
		openExposure += entry;
		unrealizedLamports += last - entry;
		const opened = new Date(p.opened_at).getTime();
		if (firstActive == null || opened < firstActive) firstActive = opened;
		if (lastActive == null || opened > lastActive) lastActive = opened;
	}

	const closedCount = closed.length;
	const realizedSol = big(realizedLamports.toString()) / LAMPORTS_PER_SOL;
	const investedSol = big(invested.toString()) / LAMPORTS_PER_SOL;
	const unrealizedSol = big(unrealizedLamports.toString()) / LAMPORTS_PER_SOL;
	const openExposureSol = big(openExposure.toString()) / LAMPORTS_PER_SOL;

	const winRate = closedCount ? wins / closedCount : 0;
	const profitFactor = grossLoss > 0n
		? big(grossProfit.toString()) / big(grossLoss.toString())
		: (grossProfit > 0n ? Infinity : 0);
	const roiPct = investedSol > 0 ? (realizedSol / investedSol) * 100 : 0;
	const avgPnlPct = pnlPcts.length ? pnlPcts.reduce((a, v) => a + v, 0) / pnlPcts.length : 0;
	const avgWinPct = winPcts.length ? winPcts.reduce((a, v) => a + v, 0) / winPcts.length : 0;
	const avgLossPct = lossPcts.length ? lossPcts.reduce((a, v) => a + v, 0) / lossPcts.length : 0;
	const holdSorted = [...holdSeconds].sort((a, b) => a - b);
	const avgHoldSeconds = holdSeconds.length ? holdSeconds.reduce((a, v) => a + v, 0) / holdSeconds.length : 0;
	const sharpe = pnlPcts.length > 1 ? avgPnlPct / (stddev(pnlPcts, avgPnlPct) || 1) : 0;
	const churnPct = closedCount ? (churn / closedCount) * 100 : 0;
	const drawdown = maxDrawdown(equityPnls);

	// --- Composite score (0–100), transparent + confidence-regressed ---
	const pfComponent = profitFactor === Infinity ? 1 : profitFactor / (profitFactor + 1);
	const pnlComponent = 0.5 + 0.5 * tanh(realizedSol / PNL_TANH_SOL);
	const consistencyComponent = 0.5 + 0.5 * tanh(sharpe / SHARPE_TANH);
	const drawdownComponent = 1 - clamp(drawdown.pct / 100, 0, 1);
	let raw =
		SCORE_WEIGHTS.winRate * winRate +
		SCORE_WEIGHTS.profitFactor * pfComponent +
		SCORE_WEIGHTS.pnl * pnlComponent +
		SCORE_WEIGHTS.consistency * consistencyComponent +
		SCORE_WEIGHTS.drawdown * drawdownComponent;
	raw = clamp(raw - CHURN_SCORE_PENALTY * clamp(churnPct / 100, 0, 1), 0, 1);
	const confidence = clamp(closedCount / CONFIDENCE_FULL_AT, 0, 1);
	const effective = raw * confidence + NEUTRAL_RAW * (1 - confidence);
	const score = Math.round(100 * effective);

	const verified =
		closedCount >= BADGE.minClosed &&
		realizedSol > 0 &&
		coins.size >= BADGE.minUniqueCoins &&
		churnPct <= BADGE.maxChurnPct;

	const usd = (sol) => (solUsd != null ? sol * solUsd : null);

	// --- "What they made it on" — per-coin realized P&L, best first ---
	// Ranked by realized SOL contribution. The trades a coin cost them count too
	// (survivorship-honest), so a coin can rank with a negative total; the surfaces
	// only badge `top_coin` as a signature win when it is net-positive.
	const coinPnl = [...coinAgg.entries()]
		.map(([mint, c]) => {
			const pnlSol = big(c.pnl.toString()) / LAMPORTS_PER_SOL;
			const investedSol = big(c.invested.toString()) / LAMPORTS_PER_SOL;
			return {
				mint,
				symbol: c.symbol,
				name: c.name,
				pnl_sol: Number(pnlSol.toFixed(6)),
				pnl_usd: usd(pnlSol) != null ? Number(usd(pnlSol).toFixed(2)) : null,
				roi_pct: investedSol > 0 ? Number(((pnlSol / investedSol) * 100).toFixed(2)) : null,
				trades: c.trades,
				wins: c.wins,
			};
		})
		.sort((a, b) => b.pnl_sol - a.pnl_sol);
	const topCoin = coinPnl.length ? coinPnl[0] : null;

	// Self-dealing summary (transparent, never folded into the credited numbers).
	let selfDealClosed = 0;
	let selfDealPnlLamports = 0n;
	for (const p of selfDealPositions) {
		if (p.status === 'closed') {
			selfDealClosed += 1;
			selfDealPnlLamports += BigInt(p.realized_pnl_lamports ?? 0);
		}
	}
	const selfDealPnlSol = big(selfDealPnlLamports.toString()) / LAMPORTS_PER_SOL;
	const snipeHitRate = snipeCount ? snipeWins / snipeCount : null;

	return {
		score,
		verified,
		confidence: Number(confidence.toFixed(3)),

		closed_count: closedCount,
		open_count: open.length,
		wins,
		losses,
		win_rate: Number(winRate.toFixed(4)),

		realized_pnl_lamports: realizedLamports.toString(),
		realized_pnl_sol: Number(realizedSol.toFixed(6)),
		realized_pnl_usd: usd(realizedSol) != null ? Number(usd(realizedSol).toFixed(2)) : null,
		unrealized_pnl_sol: Number(unrealizedSol.toFixed(6)),
		unrealized_pnl_usd: usd(unrealizedSol) != null ? Number(usd(unrealizedSol).toFixed(2)) : null,

		invested_sol: Number(investedSol.toFixed(6)),
		roi_pct: Number(roiPct.toFixed(2)),
		open_exposure_sol: Number(openExposureSol.toFixed(6)),
		open_exposure_usd: usd(openExposureSol) != null ? Number(usd(openExposureSol).toFixed(2)) : null,

		profit_factor: profitFactor === Infinity ? null : Number(profitFactor.toFixed(3)),
		avg_pnl_pct: Number(avgPnlPct.toFixed(2)),
		avg_win_pct: Number(avgWinPct.toFixed(2)),
		avg_loss_pct: Number(avgLossPct.toFixed(2)),
		best_pnl_pct: bestPct != null ? Number(bestPct.toFixed(2)) : null,
		worst_pnl_pct: worstPct != null ? Number(worstPct.toFixed(2)) : null,
		sharpe: Number(sharpe.toFixed(3)),

		max_drawdown_sol: Number(drawdown.sol.toFixed(6)),
		max_drawdown_pct: Number(drawdown.pct.toFixed(2)),

		// Compact cumulative-realized-equity curve (SOL, oldest→newest, ≤24 pts) so
		// the leaderboard row and profile can draw a P&L sparkline without a second
		// query. Endpoint equals realized_pnl_sol; [] when < 2 closed trades.
		pnl_series: cumulativeEquitySeries(equityPnls),

		avg_hold_seconds: Math.round(avgHoldSeconds),
		median_hold_seconds: Math.round(median(holdSorted)),
		unique_coins: coins.size,
		churn_pct: Number(churnPct.toFixed(2)),

		// Moon-bag policy in numbers: closed rows where the initials came out but
		// tokens still ride. "Closed" here means the stake is recovered, not that
		// the trader sold 100% — these two fields are what makes that legible.
		moonbags_held: closed.filter((p) => Number(p.moonbag_base_amount || 0) > 0).length,
		moonbag_value_sol: Number((closed.reduce(
			(a, p) => a + (Number(p.moonbag_base_amount || 0) > 0 ? big(p.moonbag_last_value_lamports || 0) : 0), 0,
		) / LAMPORTS_PER_SOL).toFixed(6)),

		// What they made it on — the single top-earning coin plus a short breakdown,
		// each number traceable to the closed positions above. `top_coin` is null
		// only when the trader has no closed positions with a mint in this window.
		top_coin: topCoin,
		top_coins: coinPnl.slice(0, 3),

		// Snipe hit-rate — win rate on entries inside SNIPE_WINDOW_MS of a coin's
		// proven on-chain birth. null until at least one such launch is in the
		// sample; `snipe_sample` exposes the coverage so the rate is never overclaimed.
		snipe_count: snipeCount,
		snipe_wins: snipeWins,
		snipe_sample: snipeSample,
		snipe_hit_rate: snipeHitRate != null ? Number(snipeHitRate.toFixed(4)) : null,

		// Anti-gaming: round-trips on the trader's OWN coins, excluded from every
		// credited metric above and surfaced here so the exclusion is auditable.
		self_dealing_count: selfDealClosed,
		self_dealing_excluded_pnl_sol: Number(selfDealPnlSol.toFixed(6)),
		self_dealing_excluded_pnl_usd: usd(selfDealPnlSol) != null ? Number(usd(selfDealPnlSol).toFixed(2)) : null,

		first_active_at: firstActive ? new Date(firstActive).toISOString() : null,
		last_active_at: lastActive ? new Date(lastActive).toISOString() : null,
	};
}

// --- DB layer ---------------------------------------------------------------
//
// NOTE: the Neon HTTP driver's tagged template does not compose nested `sql`
// fragments (a `${sql`…`}` would bind as a parameter), so the position column
// list is written literally into each query below. It is static SQL with no user
// input — duplication here is the safe, correct trade-off.

// A trader's verifiable round-trip record is NOT confined to the sniper arena.
// Strategy Objects (`agent_strategy_positions`) close real, on-chain, sell-signed
// positions with the same realized-P&L shape — an agent that earns its track
// record by running strategies is exactly as proven as one that snipes. Both
// surfaces feed this single truth layer, so the leaderboard, profiles, copy-trade
// badge, and the Back-an-Agent vault gate all credit the same real history.
//
// agent_strategy_positions uses different column names (entry_lamports, exit_sig,
// …) and is a newer table, so each query aliases its rows into the canonical
// position shape and the fetch degrades to [] if the table isn't migrated —
// matching every other optional-source read below. It has no per-trade wallet
// column (custody is at the agent level), so wallet is null for strategy-origin
// rows; that only affects the display wallet, never a metric.

/** Strategy-Objects round-trips for one agent, canonical-shaped. Best-effort. */
async function fetchStrategyPositions({ agentId, network, start }) {
	try {
		return start
			? await sql`
				select s.id, s.agent_id, null::text as wallet, s.mint, s.symbol, s.name, s.status, s.exit_reason,
				       s.entry_lamports as entry_quote_lamports, s.exit_lamports as exit_quote_lamports,
				       s.last_value_lamports, s.peak_value_lamports,
				       s.realized_pnl_lamports, s.realized_pnl_pct,
				       s.entry_sig as buy_sig, s.exit_sig as sell_sig,
				       s.opened_at, s.closed_at
				from agent_strategy_positions s
				where s.agent_id = ${agentId} and s.network = ${network}
				  and (s.status in ('open','closing') or s.closed_at >= ${start})
			`
			: await sql`
				select s.id, s.agent_id, null::text as wallet, s.mint, s.symbol, s.name, s.status, s.exit_reason,
				       s.entry_lamports as entry_quote_lamports, s.exit_lamports as exit_quote_lamports,
				       s.last_value_lamports, s.peak_value_lamports,
				       s.realized_pnl_lamports, s.realized_pnl_pct,
				       s.entry_sig as buy_sig, s.exit_sig as sell_sig,
				       s.opened_at, s.closed_at
				from agent_strategy_positions s
				where s.agent_id = ${agentId} and s.network = ${network}
			`;
	} catch {
		return []; // table not migrated yet → sniper record stands alone
	}
}

/**
 * Fetch a trader's positions for a window. Closed positions are window-bounded by
 * close time; open positions are ALWAYS included (current exposure is "now",
 * regardless of window). Unifies the sniper arena and Strategy Objects ledgers so
 * the whole reputation surface counts every real round-trip.
 */
export async function fetchTraderPositions({ agentId, network, window = 'all', now = Date.now() }) {
	const start = windowStartIso(window, now);
	const [sniper, strategy] = await Promise.all([
		start
			? sql`
				select p.id, p.agent_id, p.wallet, p.mint, p.symbol, p.name, p.status, p.exit_reason,
				       p.entry_quote_lamports, p.exit_quote_lamports, p.last_value_lamports, p.peak_value_lamports,
				       p.realized_pnl_lamports, p.realized_pnl_pct, p.buy_sig, p.sell_sig,
				       p.opened_at, p.closed_at,
				       p.moonbag_base_amount, p.moonbag_last_value_lamports, p.initials_recovered
				from agent_sniper_positions p
				where p.agent_id = ${agentId} and p.network = ${network}
				  and (p.status in ('open','opening','closing') or p.closed_at >= ${start})
				order by coalesce(p.closed_at, p.opened_at) desc
			`
			: sql`
				select p.id, p.agent_id, p.wallet, p.mint, p.symbol, p.name, p.status, p.exit_reason,
				       p.entry_quote_lamports, p.exit_quote_lamports, p.last_value_lamports, p.peak_value_lamports,
				       p.realized_pnl_lamports, p.realized_pnl_pct, p.buy_sig, p.sell_sig,
				       p.opened_at, p.closed_at,
				       p.moonbag_base_amount, p.moonbag_last_value_lamports, p.initials_recovered
				from agent_sniper_positions p
				where p.agent_id = ${agentId} and p.network = ${network}
				order by coalesce(p.closed_at, p.opened_at) desc
			`,
		fetchStrategyPositions({ agentId, network, start }),
	]);
	if (!strategy.length) return sniper;
	// Merge both real ledgers, newest activity first — computeTraderMetrics re-sorts
	// the closed subset for its equity curve, so this ordering is only for callers
	// that render the raw list (profile history, open positions).
	return [...sniper, ...strategy].sort(
		(a, b) =>
			new Date(b.closed_at || b.opened_at).getTime() - new Date(a.closed_at || a.opened_at).getTime(),
	);
}

/** SOL/USD with a short module cache so a burst of stat requests prices once. */
let _solCache = { usd: null, at: 0 };
export async function cachedSolUsd() {
	const now = Date.now();
	if (_solCache.usd != null && now - _solCache.at < 60_000) return _solCache.usd;
	try {
		const usd = await solUsdPrice();
		_solCache = { usd, at: now };
		return usd;
	} catch {
		// Degrade to last known price if we have one, else null (SOL stays exact).
		return _solCache.usd;
	}
}

function solscanUrl(sig, network) {
	if (!sig || sig === 'SIMULATED') return null;
	return network === 'devnet' ? `https://solscan.io/tx/${sig}?cluster=devnet` : `https://solscan.io/tx/${sig}`;
}

/**
 * Active copiers per leader on a network → Map(agentId → count). Best-effort: if
 * the copy_subscriptions table isn't migrated yet, degrade to an empty map rather
 * than failing the whole leaderboard.
 */
async function activeCopierCounts(network) {
	try {
		const rows = await sql`
			select leader_agent_id, count(*)::int as copiers
			from copy_subscriptions
			where network = ${network} and status = 'active'
			group by leader_agent_id
		`;
		return new Map(rows.map((r) => [r.leader_agent_id, Number(r.copiers) || 0]));
	} catch {
		return new Map();
	}
}

/** Active copier count for one leader. Best-effort (see activeCopierCounts). */
async function copierCountForAgent(agentId, network) {
	try {
		const [row] = await sql`
			select count(*)::int as copiers from copy_subscriptions
			where leader_agent_id = ${agentId} and network = ${network} and status = 'active'
		`;
		return Number(row?.copiers) || 0;
	} catch {
		return 0;
	}
}

/** Shape a closed position for the profile history + Proof tab (every number → tx). */
export function shapeClosed(p, network) {
	// A closed row with a moon-bag is NOT a 100% sell: the initials came out and
	// the remaining tokens still ride (owner policy: sell the buy-in at 2x, hold
	// the rest even to zero). Surface that or the track record misreads as
	// full exits.
	const moonbagTokens = p.moonbag_base_amount != null ? Number(p.moonbag_base_amount) : 0;
	return {
		id: p.id,
		mint: p.mint,
		symbol: p.symbol,
		name: p.name,
		entry_sol: p.entry_quote_lamports != null ? big(p.entry_quote_lamports) / LAMPORTS_PER_SOL : null,
		exit_sol: p.exit_quote_lamports != null ? big(p.exit_quote_lamports) / LAMPORTS_PER_SOL : null,
		pnl_sol: p.realized_pnl_lamports != null ? big(p.realized_pnl_lamports) / LAMPORTS_PER_SOL : null,
		pnl_pct: p.realized_pnl_pct != null ? Number(p.realized_pnl_pct) : null,
		exit_reason: p.exit_reason,
		opened_at: p.opened_at,
		closed_at: p.closed_at,
		buy_url: solscanUrl(p.buy_sig, network),
		sell_url: solscanUrl(p.sell_sig, network),
		moonbag_held: moonbagTokens > 0,
		moonbag_value_sol: moonbagTokens > 0 && p.moonbag_last_value_lamports != null
			? big(p.moonbag_last_value_lamports) / LAMPORTS_PER_SOL
			: null,
	};
}

/** Shape an open position with live unrealized P&L. */
export function shapeOpen(p, network) {
	const entry = p.entry_quote_lamports != null ? big(p.entry_quote_lamports) : 0;
	const last = p.last_value_lamports != null ? big(p.last_value_lamports) : entry;
	return {
		id: p.id,
		mint: p.mint,
		symbol: p.symbol,
		name: p.name,
		entry_sol: entry / LAMPORTS_PER_SOL,
		current_sol: last / LAMPORTS_PER_SOL,
		unrealized_pct: entry > 0 ? ((last - entry) / entry) * 100 : 0,
		opened_at: p.opened_at,
		buy_url: solscanUrl(p.buy_sig, network),
	};
}

/**
 * Mints the trader's own account launched/created — trading these is self-dealing,
 * not skill, so the truth layer excludes their P&L. Sourced from real launch
 * records: pump_agent_mints (coins launched through the platform by this user) plus
 * the user's agent-identity token mints. Best-effort: degrades to an empty set if a
 * table is absent, which simply credits every position (legacy behaviour).
 */
export async function selfDealMintsForUser(userId, network) {
	const set = new Set();
	if (!userId) return set;
	try {
		const rows = await sql`
			select mint from pump_agent_mints where user_id = ${userId} and network = ${network}
			union
			select meta->'token'->>'mint' as mint from agent_identities
			where user_id = ${userId} and meta->'token'->>'mint' is not null
		`;
		for (const r of rows) if (r.mint) set.add(r.mint);
	} catch { /* launch tables not migrated → no exclusions */ }
	return set;
}

/**
 * On-chain birth time for a set of mints, from the radar's `create` precursors
 * (observed_ts = the block time of the create instruction — provable on-chain).
 * Returns Map(mint → ms-epoch). Best-effort: an unmigrated radar table or a launch
 * we never observed just yields no snipe sample for that mint — we never guess.
 */
export async function mintLaunchTimes(mints, network) {
	const map = new Map();
	const list = [...new Set((mints || []).filter(Boolean))];
	if (!list.length) return map;
	try {
		const rows = await sql`
			select mint, min(observed_ts) as created_at
			from radar_events
			where network = ${network} and kind = 'create' and observed_ts is not null
			  and mint = any(${list}::text[])
			group by mint
		`;
		for (const r of rows) if (r.mint && r.created_at) map.set(r.mint, new Date(r.created_at).getTime());
	} catch { /* radar table not migrated → no snipe sample */ }
	return map;
}

/** Self-deal mint sets for many users at once (leaderboard). Map(userId → Set(mint)). */
export async function selfDealMintsByUsers(userIds, network) {
	const byUser = new Map();
	const ids = [...new Set((userIds || []).filter(Boolean))];
	if (!ids.length) return byUser;
	try {
		const rows = await sql`
			select user_id, mint from pump_agent_mints
			where network = ${network} and user_id = any(${ids}::uuid[])
			union
			select user_id, meta->'token'->>'mint' as mint from agent_identities
			where user_id = any(${ids}::uuid[]) and meta->'token'->>'mint' is not null
		`;
		for (const r of rows) {
			if (!r.mint) continue;
			let s = byUser.get(r.user_id);
			if (!s) { s = new Set(); byUser.set(r.user_id, s); }
			s.add(r.mint);
		}
	} catch { /* launch tables not migrated → no exclusions */ }
	return byUser;
}

/** Tag a shaped closed-trade row with its self-deal / snipe provenance for the Proof tab. */
function annotateProvenance(row, position, { selfDealMints, mintCreatedAt }) {
	row.self_dealing = !!(selfDealMints && selfDealMints.has(position.mint));
	const launch = mintCreatedAt ? mintCreatedAt.get(position.mint) : null;
	if (launch != null) {
		const dt = new Date(position.opened_at).getTime() - launch;
		row.launch_at = new Date(launch).toISOString();
		row.seconds_after_launch = Math.round(dt / 1000);
		row.snipe = dt >= -SNIPE_SKEW_GRACE_MS && dt <= SNIPE_WINDOW_MS;
	}
	return row;
}

/**
 * Full trader profile: identity + metrics for the window + closed history (proof)
 * + open positions. Returns null if the agent has no positions on this network.
 */
export async function getTraderStats({ agentId, network, window = 'all', now = Date.now() }) {
	const [idRows, positions, solUsd, copiers, oracleSummary, projection] = await Promise.all([
		sql`
			select id, user_id, name, description, avatar_url, profile_image_url, is_public
			from agent_identities where id = ${agentId} limit 1
		`,
		fetchTraderPositions({ agentId, network, window, now }),
		cachedSolUsd(),
		copierCountForAgent(agentId, network),
		actionsSummary(agentId, network).catch(() => null),
		latestProjection(agentId, network).catch(() => null),
	]);
	const identity = idRows[0];
	if (!identity) return null;

	// Real anti-gaming evidence: which of this trader's coins are self-launched, and
	// the proven on-chain birth time of every coin they touched (for snipe hit-rate).
	const mints = [...new Set(positions.map((p) => p.mint).filter(Boolean))];
	const [selfDealMints, mintCreatedAt] = await Promise.all([
		selfDealMintsForUser(identity.user_id, network),
		mintLaunchTimes(mints, network),
	]);

	const metrics = computeTraderMetrics(positions, { solUsd, selfDealMints, mintCreatedAt });
	const closed = positions
		.filter((p) => p.status === 'closed')
		.map((p) => annotateProvenance(shapeClosed(p, network), p, { selfDealMints, mintCreatedAt }));
	const open = positions
		.filter((p) => p.status === 'open' || p.status === 'opening' || p.status === 'closing')
		.map((p) => shapeOpen(p, network));
	const wallet = positions[0]?.wallet || null;

	return {
		agent: {
			id: identity.id,
			name: identity.name,
			description: identity.description || null,
			image: identity.profile_image_url || identity.avatar_url || null,
			is_public: identity.is_public !== false,
			wallet,
			copiers,
		},
		network,
		window,
		sol_usd: solUsd,
		metrics,
		closed,
		open,
		oracle: oracleSummary && oracleSummary.total > 0 ? oracleSummary : null,
		projection: projection ? buildProjectionComparison(projection, metrics) : null,
	};
}

/**
 * Latest backtest snapshot linked to this agent — the projection the owner armed
 * against. Read-only; returns null when the table is absent or no snapshot exists.
 */
async function latestProjection(agentId, network) {
	const [row] = await sql`
		select metrics, sample_size, window_days, ran_at
		from strategy_backtests
		where agent_id = ${agentId} and network = ${network}
		order by ran_at desc limit 1
	`;
	return row || null;
}

/**
 * Pair the armed projection against what actually happened, so the projection is
 * accountable — not marketing. Realized numbers come straight from
 * computeTraderMetrics (chain-proven); projected numbers from the stored backtest.
 */
function buildProjectionComparison(snap, metrics) {
	const m = snap.metrics || {};
	const bt = m.metrics || {};
	const projWinRate = typeof bt.win_rate === 'number' ? bt.win_rate : null;
	const projEv = typeof bt.expected_value_pct === 'number' ? bt.expected_value_pct : null;
	const projDrawdown = typeof bt.max_drawdown_pct === 'number' ? bt.max_drawdown_pct : null;
	return {
		ran_at: snap.ran_at,
		window_days: snap.window_days,
		sample_size: snap.sample_size,
		confidence: m.caveats?.confidence || null,
		insufficient_data: !!m.insufficient_data,
		projected: {
			win_rate: projWinRate,
			expected_value_pct: projEv,
			roi_median_pct: typeof bt.roi_median_pct === 'number' ? bt.roi_median_pct : null,
			max_drawdown_pct: projDrawdown,
		},
		realized: {
			win_rate: metrics.win_rate,
			avg_pnl_pct: metrics.avg_pnl_pct,
			roi_pct: metrics.roi_pct,
			max_drawdown_pct: metrics.max_drawdown_pct,
			closed_count: metrics.closed_count,
		},
	};
}

/**
 * Public, canonical-shaped Strategy-Objects positions across a network, for the
 * leaderboard. Mirrors the sniper leaderboard query (joined to agent_identities,
 * public agents only) so strategy traders rank alongside snipers. Best-effort: an
 * unmigrated table yields no extra rows rather than failing the whole board.
 */
/**
 * Every public agent's positions for one network, board-shaped: the sniper arena
 * ledger plus Strategy Objects, aliased into the one canonical position shape.
 * `start` (ISO) window-bounds CLOSED positions by close time; open positions are
 * always included, because current exposure is "now" regardless of window. Pass
 * `start = null` for the all-time board.
 *
 * Extracted so the leaderboard and the rivalry engine read the same rows through
 * the same joins and the same is_public gate: two boards computed from one fetch
 * can never disagree about who is on them.
 */
export async function fetchLeaderboardPositions({ network, start }) {
	const [rows, strategyRows] = await Promise.all([
		start
			? sql`
				select p.id, p.agent_id, p.wallet, p.mint, p.symbol, p.name, p.status, p.exit_reason,
				       p.entry_quote_lamports, p.exit_quote_lamports, p.last_value_lamports, p.peak_value_lamports,
				       p.realized_pnl_lamports, p.realized_pnl_pct, p.buy_sig, p.sell_sig,
				       p.opened_at, p.closed_at,
				       p.moonbag_base_amount, p.moonbag_last_value_lamports, p.initials_recovered,
				       a.user_id as agent_user_id, a.name as agent_name, a.avatar_url as agent_avatar, a.profile_image_url as agent_image
				from agent_sniper_positions p
				join agent_identities a on a.id = p.agent_id
				where p.network = ${network} and a.is_public is not false
				  and (p.status in ('open','opening','closing') or p.closed_at >= ${start})
			`
			: sql`
				select p.id, p.agent_id, p.wallet, p.mint, p.symbol, p.name, p.status, p.exit_reason,
				       p.entry_quote_lamports, p.exit_quote_lamports, p.last_value_lamports, p.peak_value_lamports,
				       p.realized_pnl_lamports, p.realized_pnl_pct, p.buy_sig, p.sell_sig,
				       p.opened_at, p.closed_at,
				       p.moonbag_base_amount, p.moonbag_last_value_lamports, p.initials_recovered,
				       a.user_id as agent_user_id, a.name as agent_name, a.avatar_url as agent_avatar, a.profile_image_url as agent_image
				from agent_sniper_positions p
				join agent_identities a on a.id = p.agent_id
				where p.network = ${network} and a.is_public is not false
			`,
		fetchStrategyLeaderboardRows({ network, start }),
	]);
	return strategyRows.length ? [...rows, ...strategyRows] : rows;
}

async function fetchStrategyLeaderboardRows({ network, start }) {
	try {
		return start
			? await sql`
				select s.id, s.agent_id, null::text as wallet, s.mint, s.symbol, s.name, s.status, s.exit_reason,
				       s.entry_lamports as entry_quote_lamports, s.exit_lamports as exit_quote_lamports,
				       s.last_value_lamports, s.peak_value_lamports,
				       s.realized_pnl_lamports, s.realized_pnl_pct,
				       s.entry_sig as buy_sig, s.exit_sig as sell_sig,
				       s.opened_at, s.closed_at,
				       a.user_id as agent_user_id, a.name as agent_name, a.avatar_url as agent_avatar, a.profile_image_url as agent_image
				from agent_strategy_positions s
				join agent_identities a on a.id = s.agent_id
				where s.network = ${network} and a.is_public is not false
				  and (s.status in ('open','closing') or s.closed_at >= ${start})
			`
			: await sql`
				select s.id, s.agent_id, null::text as wallet, s.mint, s.symbol, s.name, s.status, s.exit_reason,
				       s.entry_lamports as entry_quote_lamports, s.exit_lamports as exit_quote_lamports,
				       s.last_value_lamports, s.peak_value_lamports,
				       s.realized_pnl_lamports, s.realized_pnl_pct,
				       s.entry_sig as buy_sig, s.exit_sig as sell_sig,
				       s.opened_at, s.closed_at,
				       a.user_id as agent_user_id, a.name as agent_name, a.avatar_url as agent_avatar, a.profile_image_url as agent_image
				from agent_strategy_positions s
				join agent_identities a on a.id = s.agent_id
				where s.network = ${network} and a.is_public is not false
			`;
	} catch {
		return [];
	}
}

/**
 * Leaderboard: every agent with real trading activity in the window, ranked by
 * composite score. Pulls in-window positions from both the sniper arena and
 * Strategy Objects, groups by agent, and runs each group through the SAME pure
 * `computeTraderMetrics` the profile uses, so the two surfaces can never disagree.
 * Scale note: the trader set is curated — if it ever grows past a few hundred
 * active traders per window, move the grouping into SQL with a windowed aggregate.
 */
export const LEADERBOARD_SORTS = new Set(['score', 'pnl', 'winrate', 'roi']);

const SORT_COMPARATORS = {
	score: (a, b) => b.score - a.score || b.realized_pnl_sol - a.realized_pnl_sol,
	pnl: (a, b) => b.realized_pnl_sol - a.realized_pnl_sol || b.score - a.score,
	winrate: (a, b) => b.win_rate - a.win_rate || b.closed - a.closed,
	roi: (a, b) => b.roi_pct - a.roi_pct || b.realized_pnl_sol - a.realized_pnl_sol,
};

export async function getLeaderboard({
	network, window = '30d', limit = 100, sort = 'score', verifiedOnly = false, now = Date.now(),
}) {
	const start = windowStartIso(window, now);
	const allRows = await fetchLeaderboardPositions({ network, start });
	const solUsd = await cachedSolUsd();
	const copiers = await activeCopierCounts(network);

	const byAgent = new Map();
	for (const r of allRows) {
		let g = byAgent.get(r.agent_id);
		if (!g) {
			g = {
				agent_id: r.agent_id,
				user_id: r.agent_user_id,
				agent_name: r.agent_name,
				image: r.agent_image || r.agent_avatar || null,
				wallet: r.wallet,
				positions: [],
			};
			byAgent.set(r.agent_id, g);
		}
		g.positions.push(r);
	}

	// One batch each for the whole board: self-deal mints per user, launch times per
	// mint. The same anti-gaming evidence the profile uses, so the two never disagree.
	const allMints = [...new Set(allRows.map((r) => r.mint).filter(Boolean))];
	const [selfDealByUser, mintCreatedAt] = await Promise.all([
		selfDealMintsByUsers([...byAgent.values()].map((g) => g.user_id), network),
		mintLaunchTimes(allMints, network),
	]);
	const EMPTY = new Set();

	const board = [...byAgent.values()]
		.map((g) => {
			const m = computeTraderMetrics(g.positions, {
				solUsd,
				selfDealMints: selfDealByUser.get(g.user_id) || EMPTY,
				mintCreatedAt,
			});
			return {
				agent_id: g.agent_id,
				agent_name: g.agent_name,
				image: g.image,
				wallet: g.wallet,
				score: m.score,
				verified: m.verified,
				closed: m.closed_count,
				open_positions: m.open_count,
				wins: m.wins,
				losses: m.losses,
				win_rate: m.win_rate,
				realized_pnl_lamports: m.realized_pnl_lamports,
				realized_pnl_sol: m.realized_pnl_sol,
				realized_pnl_usd: m.realized_pnl_usd,
				roi_pct: m.roi_pct,
				profit_factor: m.profit_factor,
				avg_pnl_pct: m.avg_pnl_pct,
				best_pnl_pct: m.best_pnl_pct,
				max_drawdown_pct: m.max_drawdown_pct,
				pnl_series: m.pnl_series,
				avg_hold_seconds: m.avg_hold_seconds,
				unique_coins: m.unique_coins,
				churn_pct: m.churn_pct,
				top_coin: m.top_coin,
				snipe_hit_rate: m.snipe_hit_rate,
				snipe_count: m.snipe_count,
				self_dealing_count: m.self_dealing_count,
				last_active_at: m.last_active_at,
				copiers: copiers.get(g.agent_id) || 0,
			};
		})
		.filter((r) => (verifiedOnly ? r.verified : true))
		.sort(SORT_COMPARATORS[sort] || SORT_COMPARATORS.score)
		.slice(0, limit)
		.map((r, i) => ({ rank: i + 1, ...r }));

	return { network, window, sort, sol_usd: solUsd, leaderboard: board, t: now };
}