import 'server-only';
import { and, desc, eq, lte, sql } from '@/lib/db';
import { db, fxRates } from '@/lib/db';

/**
 * Convert an amount to USD at the rate of a given date (never today's rate for historical values).
 * fx_rates convention: base='USD', quote=<currency>, rate = units of quote per 1 USD.
 * Falls back to the latest rate on/before the date; returns null when no rate exists.
 */
export async function toUsdAt(amount: number, currency: string, date: string | null): Promise<{ usd: number; rate: number; fxDate: string } | null> {
  if (currency === 'USD') return { usd: amount, rate: 1, fxDate: date ?? new Date().toISOString().slice(0, 10) };
  const d = date ?? new Date().toISOString().slice(0, 10);
  const rows = await db()
    .select({ rate: fxRates.rate, date: fxRates.date })
    .from(fxRates)
    .where(and(eq(fxRates.base, 'USD'), eq(fxRates.quote, currency), lte(fxRates.date, d)))
    .orderBy(desc(fxRates.date))
    .limit(1);
  const r = rows[0];
  if (!r || !r.rate) return null;
  return { usd: amount / r.rate, rate: r.rate, fxDate: String(r.date) };
}

/** Latest USD→currency rate for display conversion. 1 when unknown (caller shows USD then). */
export async function latestRate(currency: string): Promise<number | null> {
  if (currency === 'USD') return 1;
  const rows = await db().select({ rate: fxRates.rate }).from(fxRates).where(and(eq(fxRates.base, 'USD'), eq(fxRates.quote, currency))).orderBy(desc(fxRates.date)).limit(1);
  return rows[0]?.rate ?? null;
}

export async function fxCoverage(): Promise<number> {
  const rows = (await db().execute(sql`select count(*)::int as n from fx_rates`)) as unknown as Array<{ n: number }>;
  return rows[0]?.n ?? 0;
}