import { desc, eq, sql } from 'drizzle-orm';
import { fxRates } from '@rareindex/database';
import { SUPPORTED_CURRENCIES, logger, toDateOnly } from '@rareindex/shared';
import { db } from './lib/db.ts';

/**
 * FX sync from the ECB reference rates published through frankfurter (https://api.frankfurter.app,
 * an open-data mirror of the ECB euro reference rates). Stored as base USD → quote per 1 USD.
 * Non-ECB currencies (e.g. TWD, AED) stay absent; sales in those currencies keep price_usd NULL
 * until a rate source exists — never approximated.
 */
const API = 'https://api.frankfurter.app';
const log = logger.child({ component: 'fx' });

interface RangeResponse {
  base: string;
  start_date: string;
  end_date: string;
  rates: Record<string, Record<string, number>>;
}

async function availableCurrencies(): Promise<string[]> {
  const res = await fetch(`${API}/currencies`, { headers: { 'user-agent': 'RareIndexBot/0.1 (fx sync)' } });
  if (!res.ok) throw new Error(`frankfurter currencies HTTP ${res.status}`);
  const json = (await res.json()) as Record<string, string>;
  return Object.keys(json);
}

export async function syncFxRange(start: string, end: string): Promise<number> {
  const avail = new Set(await availableCurrencies());
  const quotes = SUPPORTED_CURRENCIES.filter((c) => c !== 'USD' && avail.has(c));
  const res = await fetch(`${API}/${start}..${end}?from=USD&to=${quotes.join(',')}`, { headers: { 'user-agent': 'RareIndexBot/0.1 (fx sync)' } });
  if (!res.ok) throw new Error(`frankfurter range HTTP ${res.status}`);
  const json = (await res.json()) as RangeResponse;
  const rows: Array<typeof fxRates.$inferInsert> = [];
  for (const [date, byQuote] of Object.entries(json.rates ?? {})) {
    for (const [quote, rate] of Object.entries(byQuote)) rows.push({ date, base: 'USD', quote, rate, source: 'ecb' });
  }
  for (let i = 0; i < rows.length; i += 1000) {
    await db()
      .insert(fxRates)
      .values(rows.slice(i, i + 1000))
      .onConflictDoUpdate({ target: [fxRates.date, fxRates.base, fxRates.quote], set: { rate: sql`excluded.rate` } });
  }
  return rows.length;
}

/** Daily sync: from the last stored date (or 30 days back) to today. `backfill` loads 1999→today in yearly chunks. */
export async function syncFx(opts: { backfill?: boolean } = {}): Promise<{ rows: number; from: string; to: string }> {
  const today = toDateOnly(new Date());
  let from: string;
  if (opts.backfill) from = '1999-01-04';
  else {
    const [last] = await db().select({ date: fxRates.date }).from(fxRates).where(eq(fxRates.base, 'USD')).orderBy(desc(fxRates.date)).limit(1);
    from = last ? last.date : toDateOnly(new Date(Date.now() - 30 * 86_400_000));
  }
  let total = 0;
  let cursor = from;
  while (cursor <= today) {
    const year = Number(cursor.slice(0, 4));
    const end = `${year}-12-31` < today ? `${year}-12-31` : today;
    const n = await syncFxRange(cursor, end);
    total += n;
    log.info({ from: cursor, to: end, rows: n }, 'fx range synced');
    cursor = `${year + 1}-01-01`;
  }
  return { rows: total, from, to: today };
}