import pool from '../db';

const API_BASE = process.env.SPORTSGAMEODDS_BASE_URL || 'https://api.sportsgameodds.com/v2';
const API_KEY = process.env.SPORTSGAMEODDS_API_KEY || '';
const API_HEADER = process.env.SPORTSGAMEODDS_HEADER || 'x-api-key';
const FETCH_TIMEOUT_MS = 15_000;
const MAX_FETCH_RETRIES = 2;
const BASE_RETRY_DELAY_MS = 1_000;

/** Track SGO data feed API call in rm_api_usage */
async function trackSgoCall(league: string, success: boolean, responseTimeMs: number, eventCount: number, errorMessage?: string): Promise<void> {
  try {
    await pool.query(
      `INSERT INTO rm_api_usage (category, subcategory, provider, model, league, input_tokens, output_tokens, total_tokens, response_time_ms, success, error_message, metadata)
       VALUES ('data_feed', $1, 'sgo', 'sportsgameodds-v2', $2, 0, 0, 0, $3, $4, $5, $6)`,
      [league, league, responseTimeMs, success, errorMessage?.slice(0, 200) || null, JSON.stringify({ eventCount })]
    );
  } catch (err) {
    // Non-fatal
  }
}

export const LEAGUE_MAP: Record<string, { leagueID: string; sportID: string }> = {
  nba: { leagueID: 'NBA', sportID: 'BASKETBALL' },
  nfl: { leagueID: 'NFL', sportID: 'FOOTBALL' },
  mlb: { leagueID: 'MLB', sportID: 'BASEBALL' },
  nhl: { leagueID: 'NHL', sportID: 'HOCKEY' },
  ncaab: { leagueID: 'NCAAB', sportID: 'BASKETBALL' },
  ncaaf: { leagueID: 'NCAAF', sportID: 'FOOTBALL' },
  wnba: { leagueID: 'WNBA', sportID: 'BASKETBALL' },
  mma: { leagueID: 'UFC', sportID: 'MMA' },
  epl: { leagueID: 'EPL', sportID: 'SOCCER' },
  la_liga: { leagueID: 'LA_LIGA', sportID: 'SOCCER' },
  bundesliga: { leagueID: 'BUNDESLIGA', sportID: 'SOCCER' },
  serie_a: { leagueID: 'IT_SERIE_A', sportID: 'SOCCER' },
  ligue_1: { leagueID: 'FR_LIGUE_1', sportID: 'SOCCER' },
  champions_league: { leagueID: 'UEFA_CHAMPIONS_LEAGUE', sportID: 'SOCCER' },
};

export interface SgoOddValue {
  bookOdds?: string;         // American odds e.g. "-250", "+197"
  fairOdds?: string;
  openBookOdds?: string;
  openBookSpread?: string;
  openBookOverUnder?: string;
  bookSpread?: string;       // Spread line e.g. "-6.5"
  fairSpread?: string;
  bookOverUnder?: string;    // Total line e.g. "218.5"
  fairOverUnder?: string;
  statEntityID?: string;
  marketName?: string;
  started?: boolean;
  ended?: boolean;
}

export interface SgoEvent {
  eventID: string;
  teams: {
    home: { names: { long: string; short: string } };
    away: { names: { long: string; short: string } };
  };
  status: {
    startsAt: string;
    started?: boolean;
    ended?: boolean;
    displayShort: string;
    oddsPresent: boolean;
  };
  odds: Record<string, SgoOddValue>;
}

export function deriveEventLifecycleStatus(
  event: Pick<SgoEvent, 'status'>,
  now: Date = new Date(),
): 'scheduled' | 'started' | 'ended' {
  if (event.status?.ended === true) return 'ended';
  if (event.status?.started === true) return 'started';

  const startsAt = event.status?.startsAt ? new Date(event.status.startsAt) : null;
  if (startsAt && Number.isFinite(startsAt.getTime()) && startsAt <= now) {
    return 'started';
  }

  return 'scheduled';
}

function isRetryableStatus(status: number): boolean {
  return status === 408 || status === 425 || status === 429 || status >= 500;
}

function isRetryableFetchError(err: unknown): boolean {
  const message = err instanceof Error ? err.message.toLowerCase() : '';
  const name = err instanceof Error ? err.name : '';
  return name === 'TimeoutError' || name === 'AbortError' || message.includes('timeout') || message.includes('aborted');
}

export function computeSgoRetryDelayMs(attempt: number): number {
  return BASE_RETRY_DELAY_MS * (2 ** attempt);
}

function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

export async function fetchEvents(league: string, startsAfter?: string): Promise<SgoEvent[]> {
  const mapping = LEAGUE_MAP[league.toLowerCase()];
  if (!mapping) return [];

  let url = `${API_BASE}/events/?sportID=${mapping.sportID}&leagueID=${mapping.leagueID}&limit=50`;
  if (startsAfter) {
    url += `&startsAfter=${encodeURIComponent(startsAfter)}`;
  }

  const startMs = Date.now();

  for (let attempt = 0; attempt <= MAX_FETCH_RETRIES; attempt++) {
    try {
      const res = await fetch(url, {
        headers: { [API_HEADER]: API_KEY },
        signal: AbortSignal.timeout(FETCH_TIMEOUT_MS),
      });

      if (!res.ok) {
        const errText = await res.text();
        if (attempt < MAX_FETCH_RETRIES && isRetryableStatus(res.status)) {
          const delayMs = computeSgoRetryDelayMs(attempt);
          console.warn(`SGO API error ${res.status} for ${league}; retrying in ${delayMs}ms (attempt ${attempt + 1}/${MAX_FETCH_RETRIES + 1})`);
          await sleep(delayMs);
          continue;
        }

        const responseTimeMs = Date.now() - startMs;
        console.error(`SGO API error ${res.status} for ${league}:`, errText);
        trackSgoCall(league, false, responseTimeMs, 0, `HTTP ${res.status}`).catch(() => {});
        return [];
      }

      const json = await res.json() as { data?: SgoEvent[] };
      const events = json.data || [];
      const responseTimeMs = Date.now() - startMs;
      trackSgoCall(league, true, responseTimeMs, events.length).catch(() => {});
      return events;
    } catch (err: any) {
      if (attempt < MAX_FETCH_RETRIES && isRetryableFetchError(err)) {
        const delayMs = computeSgoRetryDelayMs(attempt);
        console.warn(`SGO fetch error for ${league}; retrying in ${delayMs}ms (attempt ${attempt + 1}/${MAX_FETCH_RETRIES + 1}):`, err);
        await sleep(delayMs);
        continue;
      }

      const responseTimeMs = Date.now() - startMs;
      console.error(`SGO fetch error for ${league}:`, err);
      trackSgoCall(league, false, responseTimeMs, 0, err.message?.slice(0, 200)).catch(() => {});
      return [];
    }
  }

  return [];
}

export async function fetchAllLeagueEvents(startsAfter?: string): Promise<Record<string, SgoEvent[]>> {
  const results: Record<string, SgoEvent[]> = {};
  const leagues = Object.keys(LEAGUE_MAP);

  // Default: start of today in ET to avoid getting old/historical events
  if (!startsAfter) {
    const now = new Date();
    const etStr = now.toLocaleString('en-US', { timeZone: 'America/New_York' });
    const et = new Date(etStr);
    const y = et.getFullYear();
    const m = String(et.getMonth() + 1).padStart(2, '0');
    const d = String(et.getDate()).padStart(2, '0');
    startsAfter = `${y}-${m}-${d}T00:00:00-05:00`;
  }

  // Fetch in batches of 3 to avoid rate limiting
  for (let i = 0; i < leagues.length; i += 3) {
    const batch = leagues.slice(i, i + 3);
    const promises = batch.map(async (league) => {
      try {
        const events = await fetchEvents(league, startsAfter);
        if (events.length > 0) {
          results[league] = events;
        }
      } catch (err) {
        console.error(`Failed to fetch ${league}:`, err);
      }
    });
    await Promise.all(promises);
  }

  return results;
}

/** Parse a string odds value like "-250" or "+197" to a number */
function toNum(val: string | number | undefined | null): number | null {
  if (val == null) return null;
  const n = typeof val === 'number' ? val : parseFloat(String(val));
  return isNaN(n) ? null : n;
}

export function americanOddsToImpliedProbability(odds: number | null | undefined): number | null {
  if (odds == null || !Number.isFinite(odds) || odds === 0) return null;
  if (odds > 0) return 100 / (odds + 100);
  const abs = Math.abs(odds);
  return abs / (abs + 100);
}

export function sanitizeMoneylinePair(pair: { home?: number | null; away?: number | null } | null | undefined): {
  home: number | null;
  away: number | null;
} {
  const home = toNum(pair?.home);
  const away = toNum(pair?.away);
  if (home == null && away == null) return { home: null, away: null };
  if (home == null || away == null) return { home, away };

  // Both sides positive is not a real two-way market.
  if (home > 0 && away > 0) {
    return { home: null, away: null };
  }

  // Two negative prices can be valid around pick'em, but absurdly juiced
  // pairs are upstream corruption. Null those instead of fabricating.
  if (home < 0 && away < 0) {
    const impliedHome = americanOddsToImpliedProbability(home);
    const impliedAway = americanOddsToImpliedProbability(away);
    const combinedImplied = (impliedHome || 0) + (impliedAway || 0);
    if (combinedImplied > 1.2) {
      return { home: null, away: null };
    }
  }

  return { home, away };
}

function inferFavoriteFromSpreadPair(
  spread: { home: { line: number | null; odds: number | null } | null; away: { line: number | null; odds: number | null } | null },
): 'home' | 'away' | null {
  const homeLine = toNum(spread.home?.line);
  const awayLine = toNum(spread.away?.line);
  if (homeLine == null || awayLine == null) return null;
  if (homeLine === awayLine) return null;
  return homeLine < awayLine ? 'home' : 'away';
}

function inferFavoriteFromMoneylinePair(
  moneyline: { home: number | null; away: number | null },
): 'home' | 'away' | null {
  const home = toNum(moneyline.home);
  const away = toNum(moneyline.away);
  if (home == null || away == null || home === away) return null;

  if (home < 0 && away >= 0) return 'home';
  if (away < 0 && home >= 0) return 'away';
  if (home < 0 && away < 0) return home < away ? 'home' : 'away';
  if (home > 0 && away > 0) return home < away ? 'home' : 'away';
  return null;
}

function sanitizeMoneylineAgainstSpread(
  moneyline: { home: number | null; away: number | null },
  spread: { home: { line: number | null; odds: number | null } | null; away: { line: number | null; odds: number | null } | null },
): { home: number | null; away: number | null } {
  if (moneyline.home == null || moneyline.away == null) return moneyline;
  const favorite = inferFavoriteFromSpreadPair(spread);
  if (!favorite) return moneyline;

  const moneylineFavorite = inferFavoriteFromMoneylinePair(moneyline);
  if (moneylineFavorite && moneylineFavorite !== favorite) {
    const swapped = { home: moneyline.away, away: moneyline.home };
    if (inferFavoriteFromMoneylinePair(swapped) === favorite) {
      return swapped;
    }
    return { home: null, away: null };
  }
  if (moneyline.home === moneyline.away && !moneylineFavorite) {
    return { home: null, away: null };
  }

  return moneyline;
}

function hasAnyMoneylineValue(pair: { home: number | null; away: number | null }): boolean {
  return pair.home !== null || pair.away !== null;
}

function hasCompleteSpreadPair(spread: {
  home: { line: number | null; odds: number | null } | null;
  away: { line: number | null; odds: number | null } | null;
}): boolean {
  return spread.home?.line != null && spread.away?.line != null;
}

function hasCompleteTotalPair(total: {
  over: { line: number | null; odds: number | null } | null;
  under: { line: number | null; odds: number | null } | null;
}): boolean {
  return total.over?.line != null && total.under?.line != null;
}

function isPlausibleMlbSpreadPair(spread: {
  home: { line: number | null; odds: number | null } | null;
  away: { line: number | null; odds: number | null } | null;
}): boolean {
  const homeLine = toNum(spread.home?.line);
  const awayLine = toNum(spread.away?.line);
  if (homeLine == null || awayLine == null) return true;
  const homeOdds = toNum(spread.home?.odds);
  const awayOdds = toNum(spread.away?.odds);

  const sameMagnitude = Math.abs(homeLine + awayLine) <= 0.15;
  const opposingSides = Math.sign(homeLine || 0) !== Math.sign(awayLine || 0);
  const maxAbs = Math.max(Math.abs(homeLine), Math.abs(awayLine));
  const oddsPlausible =
    (homeOdds == null || Math.abs(homeOdds) <= 700) &&
    (awayOdds == null || Math.abs(awayOdds) <= 700);

  return sameMagnitude && opposingSides && maxAbs <= 2.5 && oddsPlausible;
}

function isPlausibleMlbMoneylinePair(moneyline: {
  home: number | null;
  away: number | null;
}): boolean {
  const home = toNum(moneyline.home);
  const away = toNum(moneyline.away);
  if (home == null || away == null) return true;
  return Math.max(Math.abs(home), Math.abs(away)) <= 700;
}

function isPlausibleMlbTotalPair(total: {
  over: { line: number | null; odds: number | null } | null;
  under: { line: number | null; odds: number | null } | null;
}): boolean {
  const overLine = toNum(total.over?.line);
  const underLine = toNum(total.under?.line);
  if (overLine == null || underLine == null) return true;

  const overOdds = toNum(total.over?.odds);
  const underOdds = toNum(total.under?.odds);
  const sameLine = Math.abs(overLine - underLine) <= 0.15;
  const positiveLine = overLine > 0 && underLine > 0;
  const maxLine = Math.max(overLine, underLine);
  const oddsPlausible =
    (overOdds == null || Math.abs(overOdds) <= 300) &&
    (underOdds == null || Math.abs(underOdds) <= 300);

  return sameLine && positiveLine && maxLine >= 4.5 && maxLine <= 12.5 && oddsPlausible;
}

export function sanitizeGameOddsForLeague<T extends {
  moneyline: { home: number | null; away: number | null };
  spread: { home: { line: number | null; odds: number | null } | null; away: { line: number | null; odds: number | null } | null };
  total: { over: { line: number | null; odds: number | null } | null; under: { line: number | null; odds: number | null } | null };
}>(league: string | null | undefined, odds: T): T {
  const normalizedLeague = String(league || '').trim().toLowerCase();
  if (normalizedLeague !== 'mlb') return odds;

  const spread = hasCompleteSpreadPair(odds.spread) && !isPlausibleMlbSpreadPair(odds.spread)
    ? { home: null, away: null }
    : odds.spread;

  let moneyline = sanitizeMoneylineAgainstSpread(odds.moneyline, spread);
  if (hasAnyMoneylineValue(moneyline) && !isPlausibleMlbMoneylinePair(moneyline)) {
    moneyline = { home: null, away: null };
  }

  const total = hasCompleteTotalPair(odds.total) && !isPlausibleMlbTotalPair(odds.total)
    ? { over: null, under: null }
    : odds.total;

  return {
    ...odds,
    moneyline,
    spread,
    total,
  };
}

function pickMoneylinePair(
  home: SgoOddValue | undefined,
  away: SgoOddValue | undefined,
  options: { preferOpen: boolean; allowCurrentFallback: boolean },
): { home: number | null; away: number | null } {
  const openPair = sanitizeMoneylinePair({
    home: toNum(home?.openBookOdds),
    away: toNum(away?.openBookOdds),
  });
  const currentPair = sanitizeMoneylinePair({
    home: toNum(home?.bookOdds),
    away: toNum(away?.bookOdds),
  });

  if (options.preferOpen && hasAnyMoneylineValue(openPair)) {
    return openPair;
  }

  if (options.allowCurrentFallback && hasAnyMoneylineValue(currentPair)) {
    return currentPair;
  }

  return options.preferOpen ? openPair : currentPair;
}

export function parseOdds(event: SgoEvent): {
  moneyline: { home: number | null; away: number | null };
  spread: { home: { line: number; odds: number | null } | null; away: { line: number; odds: number | null } | null };
  total: { over: { line: number; odds: number | null } | null; under: { line: number; odds: number | null } | null };
  props: Array<{ player: string; stat: string; line: number; overOdds: number | null; underOdds: number | null }>;
} {
  const result = {
    moneyline: { home: null as number | null, away: null as number | null },
    spread: { home: null as any, away: null as any },
    total: { over: null as any, under: null as any },
    props: [] as any[],
  };

  if (!event.odds) return result;

  // SGO v2 API returns:
  //   bookOdds: string (American odds like "-250", "+197") — current/live
  //   openBookOdds: string — pre-game opening line
  //   bookSpread/openBookSpread: string (spread line like "-6.5")
  //   bookOverUnder/openBookOverUnder: string (total line like "218.5")
  // Key format: points-{side}-{period}-{type}-{direction}

  /** Pick best current odds: prefer current book fields, fallback to opening only if missing */
  function pickOdds(val: SgoOddValue): number | null {
    return toNum(val.bookOdds) ?? toNum((val as any).openBookOdds);
  }

  function pickSpread(val: SgoOddValue): number | null {
    return toNum(val.bookSpread) ?? toNum((val as any).openBookSpread);
  }

  function pickTotal(val: SgoOddValue): number | null {
    return toNum(val.bookOverUnder) ?? toNum((val as any).openBookOverUnder);
  }

  // Direct key lookups for game-level markets (most reliable)
  const mlHome = event.odds['points-home-game-ml-home'];
  const mlAway = event.odds['points-away-game-ml-away'];
  const spHome = event.odds['points-home-game-sp-home'];
  const spAway = event.odds['points-away-game-sp-away'];
  const ouOver = event.odds['points-all-game-ou-over'];
  const ouUnder = event.odds['points-all-game-ou-under'];

  result.moneyline = pickMoneylinePair(mlHome, mlAway, {
    preferOpen: false,
    allowCurrentFallback: true,
  });

  // Spread
  if (spHome) {
    result.spread.home = { line: pickSpread(spHome) ?? 0, odds: pickOdds(spHome) };
  }
  if (spAway) {
    result.spread.away = { line: pickSpread(spAway) ?? 0, odds: pickOdds(spAway) };
  }

  // Total (Over/Under)
  if (ouOver) {
    result.total.over = { line: pickTotal(ouOver) ?? 0, odds: pickOdds(ouOver) };
  }
  if (ouUnder) {
    result.total.under = { line: pickTotal(ouUnder) ?? 0, odds: pickOdds(ouUnder) };
  }

  result.moneyline = sanitizeMoneylineAgainstSpread(result.moneyline, result.spread);

  // Player props — look for keys with statEntityID matching player format
  for (const [key, val] of Object.entries(event.odds)) {
    if (val.statEntityID && /^[A-Z]+_[A-Z]+_\d+_/.test(val.statEntityID)) {
      const k = key.toLowerCase();
      result.props.push({
        player: val.statEntityID,
        stat: key,
        line: toNum(val.bookSpread ?? val.bookOverUnder) ?? 0,
        overOdds: k.includes('over') ? toNum(val.bookOdds) : null,
        underOdds: k.includes('under') ? toNum(val.bookOdds) : null,
      });
    }
  }

  const league = String((event as any).leagueID || '').toLowerCase();
  return sanitizeGameOddsForLeague(league, result);
}

export function hasAnyOpeningOdds(odds: {
  moneyline: { home: number | null; away: number | null };
  spread: { home: { line: number; odds: number | null } | null; away: { line: number; odds: number | null } | null };
  total: { over: { line: number; odds: number | null } | null; under: { line: number; odds: number | null } | null };
}): boolean {
  return (
    odds.moneyline.home !== null ||
    odds.moneyline.away !== null ||
    odds.spread.home !== null ||
    odds.spread.away !== null ||
    odds.total.over !== null ||
    odds.total.under !== null
  );
}

/** Parse opening odds from SGO event using only openBook* fields. */
export function parseOpeningOdds(event: SgoEvent): {
  moneyline: { home: number | null; away: number | null };
  spread: { home: { line: number; odds: number | null } | null; away: { line: number; odds: number | null } | null };
  total: { over: { line: number; odds: number | null } | null; under: { line: number; odds: number | null } | null };
} {
  const result = {
    moneyline: { home: null as number | null, away: null as number | null },
    spread: { home: null as any, away: null as any },
    total: { over: null as any, under: null as any },
  };

  if (!event.odds) return result;

  const mlHome = event.odds['points-home-game-ml-home'];
  const mlAway = event.odds['points-away-game-ml-away'];
  const spHome = event.odds['points-home-game-sp-home'];
  const spAway = event.odds['points-away-game-sp-away'];
  const ouOver = event.odds['points-all-game-ou-over'];
  const ouUnder = event.odds['points-all-game-ou-under'];

  result.moneyline = pickMoneylinePair(mlHome, mlAway, {
    preferOpen: true,
    allowCurrentFallback: false,
  });

  // Spread — opening only
  if (spHome) {
    const line = toNum(spHome.openBookSpread);
    const odds = toNum(spHome.openBookOdds);
    if (line !== null) result.spread.home = { line, odds };
  }
  if (spAway) {
    const line = toNum(spAway.openBookSpread);
    const odds = toNum(spAway.openBookOdds);
    if (line !== null) result.spread.away = { line, odds };
  }

  // Total — opening only
  if (ouOver) {
    const line = toNum(ouOver.openBookOverUnder);
    const odds = toNum(ouOver.openBookOdds);
    if (line !== null) result.total.over = { line, odds };
  }
  if (ouUnder) {
    const line = toNum(ouUnder.openBookOverUnder);
    const odds = toNum(ouUnder.openBookOdds);
    if (line !== null) result.total.under = { line, odds };
  }

  result.moneyline = sanitizeMoneylineAgainstSpread(result.moneyline, result.spread);

  const league = String((event as any).leagueID || '').toLowerCase();
  return sanitizeGameOddsForLeague(league, result);
}

/** Build deterministic event ID from league, teams, and date */
export function makeEventId(league: string, awayShort: string, homeShort: string, dateStr: string): string {
  const datePart = dateStr.replace(/-/g, '');
  return `${league}-${awayShort.toLowerCase()}-${homeShort.toLowerCase()}-${datePart}`;
}

// ─── NCAAB D1 Filter ──────────────────────────────────────────────
// kp_teams contains all D1 teams with lowercase canonical names.
// Cache the list in memory so we don't hit DB on every event.

let d1TeamCache: Set<string> | null = null;
let d1CacheExpiry = 0;

async function loadD1Teams(): Promise<Set<string>> {
  if (d1TeamCache && Date.now() < d1CacheExpiry) return d1TeamCache;
  try {
    const { rows } = await pool.query(
      `SELECT DISTINCT team_name_canonical FROM kp_teams`
    );
    d1TeamCache = new Set(rows.map(r => r.team_name_canonical.toLowerCase()));
    d1CacheExpiry = Date.now() + 6 * 3600_000; // 6-hour cache
  } catch {
    d1TeamCache = new Set();
    d1CacheExpiry = Date.now() + 300_000; // retry in 5 min on failure
  }
  return d1TeamCache;
}

/**
 * Check if a team name matches a D1 program via kp_teams.
 * Uses substring matching: "Purdue Boilermakers" matches kp "purdue".
 */
export async function isNcaabD1(teamName: string): Promise<boolean> {
  const d1Teams = await loadD1Teams();
  const lower = teamName.toLowerCase();
  for (const d1 of d1Teams) {
    if (lower.includes(d1) || d1.includes(lower.split(' ')[0].toLowerCase())) return true;
  }
  return false;
}