import { sql } from 'drizzle-orm';
import { benchmarks } from '@rareindex/database';
import { logger } from '@rareindex/shared';
import { db } from './lib/db.ts';

/**
 * Benchmark series (§187) from free public datasets:
 *  - SPX, XAUUSD, BTCUSD: stooq.com daily CSV (public quotes archive)
 *  - CPIAUCSL: FRED CSV export (no API key required)
 * Sources that fail are logged and left empty — never approximated.
 */
const log = logger.child({ component: 'benchmarks' });
const UA = { 'user-agent': 'RareIndexBot/0.1 (benchmark sync)' };

async function stooq(symbol: string, ticker: string): Promise<number> {
  const res = await fetch(`https://stooq.com/q/d/l/?s=${encodeURIComponent(symbol)}&i=d`, { headers: UA });
  if (!res.ok) throw new Error(`stooq ${symbol} HTTP ${res.status}`);
  const text = await res.text();
  const lines = text.trim().split('\n');
  if (lines.length < 2 || !lines[0]!.toLowerCase().startsWith('date')) throw new Error(`stooq ${symbol}: unexpected payload (${lines[0]?.slice(0, 40)})`);
  const rows: Array<typeof benchmarks.$inferInsert> = [];
  for (const line of lines.slice(1)) {
    const [date, , , , close] = line.split(',');
    const v = Number(close);
    if (date && /^\d{4}-\d{2}-\d{2}$/.test(date) && Number.isFinite(v) && v > 0) rows.push({ ticker, date, value: v, source: 'stooq' });
  }
  await upsert(rows);
  return rows.length;
}

async function fred(seriesId: string, ticker: string): Promise<number> {
  const res = await fetch(`https://fred.stlouisfed.org/graph/fredgraph.csv?id=${seriesId}`, { headers: UA });
  if (!res.ok) throw new Error(`fred ${seriesId} HTTP ${res.status}`);
  const text = await res.text();
  const rows: Array<typeof benchmarks.$inferInsert> = [];
  for (const line of text.trim().split('\n').slice(1)) {
    const [date, val] = line.split(',');
    const v = Number(val);
    if (date && Number.isFinite(v) && val !== '.') rows.push({ ticker, date, value: v, source: 'fred' });
  }
  await upsert(rows);
  return rows.length;
}

async function upsert(rows: Array<typeof benchmarks.$inferInsert>): Promise<void> {
  for (let i = 0; i < rows.length; i += 1000) {
    await db().insert(benchmarks).values(rows.slice(i, i + 1000)).onConflictDoUpdate({ target: [benchmarks.ticker, benchmarks.date], set: { value: sql`excluded.value` } });
  }
}

export async function syncBenchmarks(): Promise<Record<string, number | string>> {
  const out: Record<string, number | string> = {};
  // stooq first (long history); FRED as fallback (S&P 500 / NASDAQ / Coinbase BTC published by FRED, ~10y)
  const withFallback = (primary: () => Promise<number>, fallback: () => Promise<number>) => async () => {
    try {
      return await primary();
    } catch (err) {
      log.warn({ err }, 'primary benchmark source failed; trying fallback');
      return fallback();
    }
  };
  const jobs: Array<[string, () => Promise<number>]> = [
    ['SPX', withFallback(() => stooq('^spx', 'SPX'), () => fred('SP500', 'SPX'))],
    ['NDX', withFallback(() => stooq('^ndx', 'NDX'), () => fred('NASDAQCOM', 'NDX'))],
    ['GOLD', () => stooq('xauusd', 'GOLD')],
    ['BTC', withFallback(() => stooq('btcusd', 'BTC'), () => fred('CBBTCUSD', 'BTC'))],
    ['CPI', () => fred('CPIAUCSL', 'CPI')],
    ['CASE_SHILLER', () => fred('CSUSHPINSA', 'CASE_SHILLER')],
  ];
  for (const [name, fn] of jobs) {
    try {
      out[name] = await fn();
      log.info({ benchmark: name, rows: out[name] }, 'benchmark synced');
    } catch (err) {
      out[name] = `unavailable: ${err instanceof Error ? err.message : String(err)}`;
      log.warn({ err, benchmark: name }, 'benchmark unavailable');
    }
  }
  return out;
}