import { and, desc, eq, lte } from 'drizzle-orm';
import { fxRates } from '@rareindex/database';
import { toDateOnly, type CurrencyCode } from '@rareindex/shared';
import { db } from './db.ts';

/**
 * Historical FX lookup (§136): rate quoted as 1 <base> = rate <quote>, base USD in fx_rates.
 * Converts `amount` in `currency` to USD using the latest rate on or before `date`
 * (weekends/holidays fall back to the previous business day). Returns null when no rate exists —
 * callers must then trigger an FX sync or leave price_usd unresolved, never guess.
 */
const cache = new Map<string, { rate: number; date: string } | null>();

export async function usdRateFor(currency: string, date: Date): Promise<{ rate: number; date: string } | null> {
  if (currency === 'USD') return { rate: 1, date: toDateOnly(date) };
  const key = `${currency}|${toDateOnly(date)}`;
  if (cache.has(key)) return cache.get(key)!;
  const [row] = await db()
    .select({ rate: fxRates.rate, date: fxRates.date })
    .from(fxRates)
    .where(and(eq(fxRates.base, 'USD'), eq(fxRates.quote, currency), lte(fxRates.date, toDateOnly(date))))
    .orderBy(desc(fxRates.date))
    .limit(1);
  const out = row ? { rate: row.rate, date: row.date } : null;
  // Only cache misses briefly (map grows per date otherwise)
  if (cache.size > 50_000) cache.clear();
  cache.set(key, out);
  return out;
}

export async function toUsd(amount: number, currency: string, date: Date): Promise<{ usd: number; rate: number; fxDate: string } | null> {
  const r = await usdRateFor(currency, date);
  if (!r || r.rate <= 0) return null;
  // fx_rates.rate = quote units per 1 USD → USD = amount / rate
  return { usd: amount / r.rate, rate: r.rate, fxDate: r.date };
}

export function pickRate(rates: Array<{ date: string; rate: number }>, date: string): { date: string; rate: number } | null {
  let best: { date: string; rate: number } | null = null;
  for (const r of rates) if (r.date <= date && (!best || r.date > best.date)) best = r;
  return best;
}

export type { CurrencyCode };