/**
 * FanGraphs MLB Advanced Stats Client
 *
 * Fetches batting leaders, pitching leaders, and projections from FanGraphs'
 * public JSON API. Data stored in fg_batting_stats, fg_pitching_stats,
 * fg_projections tables.
 *
 * Used by: fangraphs-sync worker (daily cron), fangraphs RIE signal, Grok prompt
 */

import pool from '../db';

const FG_BASE = 'https://www.fangraphs.com/api';
const FG_LEADERS = `${FG_BASE}/leaders/major-league/data`;
const FG_PROJECTIONS = `${FG_BASE}/projections`;

// FanGraphs month param → split label
export const FG_SPLITS: Record<string, number> = {
  full: 0,
  last7: 1,
  last14: 2,
  last30: 3,
};

// FanGraphs team abbreviation normalization
const TEAM_MAP: Record<string, string> = {
  WSN: 'WSH', SFG: 'SF', SDP: 'SD',
  TBR: 'TB', KCR: 'KC', CHW: 'CWS', ATH: 'OAK',
};

function normTeam(fg: string): string {
  // FanGraphs leaders API wraps team in HTML: <a href="...">NYY</a>
  // Strip HTML if present
  const match = fg.match(/>([A-Z]{2,4})<\/a>/);
  const abbr = match ? match[1] : fg;
  return TEAM_MAP[abbr] || abbr;
}

// ---------------------------------------------------------------------------
// Fetch helper
// ---------------------------------------------------------------------------

async function fgFetch(url: string): Promise<any> {
  const res = await fetch(url, {
    signal: AbortSignal.timeout(20_000),
    headers: {
      'User-Agent': 'RainmakerSports/1.0 (analytics)',
      'Accept': 'application/json',
    },
  });
  if (!res.ok) throw new Error(`FanGraphs ${res.status} ${res.statusText} for ${url}`);
  return res.json();
}

// ---------------------------------------------------------------------------
// Interfaces
// ---------------------------------------------------------------------------

export interface FgBatter {
  fgPlayerId: number;
  mlbamId: number;
  name: string;
  team: string;
  g: number; ab: number; pa: number; h: number;
  singles: number; doubles: number; triples: number; hr: number;
  r: number; rbi: number; bb: number; so: number; sb: number; cs: number;
  hbp: number; sf: number;
  avg: number; obp: number; slg: number; ops: number;
  iso: number; babip: number; woba: number; wrcPlus: number;
  bbPct: number; kPct: number;
  ldPct: number; gbPct: number; fbPct: number; hrFb: number;
  ev: number; hardHitPct: number; barrelPct: number;
  war: number; off: number; def: number; spd: number;
}

export interface FgPitcher {
  fgPlayerId: number;
  mlbamId: number;
  name: string;
  team: string;
  w: number; l: number; era: number; g: number; gs: number;
  sv: number; hld: number; ip: number;
  h: number; r: number; er: number; hr: number; bb: number; so: number; hbp: number;
  whip: number; kPer9: number; bbPer9: number;
  kPct: number; bbPct: number; kBbPct: number;
  hrPer9: number; avg: number; babip: number; lobPct: number;
  fip: number; xfip: number; siera: number; xera: number;
  gbPct: number; fbPct: number; ldPct: number; hrFb: number; hardHitPct: number;
  ev: number; barrelPct: number;
  fbPctPitch: number; fbVelo: number;
  slPct: number; slVelo: number;
  cbPct: number; cbVelo: number;
  chPct: number; chVelo: number;
  war: number;
}

export interface FgProjection {
  fgPlayerId: number;
  mlbamId: number;
  name: string;
  team: string;
  statType: 'bat' | 'pit';
  system: string;
  // Batting
  projPa?: number; projHr?: number; projRbi?: number; projR?: number; projSb?: number;
  projAvg?: number; projObp?: number; projSlg?: number; projOps?: number;
  projWoba?: number; projWrcPlus?: number; projWar?: number;
  projKPct?: number; projBbPct?: number;
  // Pitching
  projW?: number; projL?: number; projEra?: number;
  projIp?: number; projSo?: number; projBb?: number;
  projWhip?: number; projFip?: number; projWarPit?: number;
  projKPer9?: number; projBbPer9?: number;
}

export interface TeamBattingProjection {
  team: string;
  playerCount: number;
  projPa: number;
  projWrcPlus: number;
  projWoba: number;
  projOps: number;
  projIso: number;
  projKPct: number;
  projBbPct: number;
}

export interface TeamPitchingProjection {
  team: string;
  playerCount: number;
  projIp: number;
  projEra: number;
  projFip: number;
  projWhip: number;
  projKPer9: number;
  projBbPer9: number;
}

// ---------------------------------------------------------------------------
// Fetch & parse
// ---------------------------------------------------------------------------

function num(v: any): number {
  const n = Number(v);
  return isNaN(n) ? 0 : n;
}

function parseBatter(d: any): FgBatter {
  return {
    fgPlayerId: num(d.playerid),
    mlbamId: num(d.xMLBAMID),
    name: String(d.PlayerName || d.Name || ''),
    team: normTeam(String(d.Team || '')),
    g: num(d.G), ab: num(d.AB), pa: num(d.PA), h: num(d.H),
    singles: num(d['1B']), doubles: num(d['2B']), triples: num(d['3B']), hr: num(d.HR),
    r: num(d.R), rbi: num(d.RBI), bb: num(d.BB), so: num(d.SO), sb: num(d.SB), cs: num(d.CS),
    hbp: num(d.HBP), sf: num(d.SF),
    avg: num(d.AVG), obp: num(d.OBP), slg: num(d.SLG), ops: num(d.OPS),
    iso: num(d.ISO), babip: num(d.BABIP), woba: num(d.wOBA), wrcPlus: num(d['wRC+']),
    bbPct: num(d['BB%']), kPct: num(d['K%']),
    ldPct: num(d['LD%']), gbPct: num(d['GB%']), fbPct: num(d['FB%']), hrFb: num(d['HR/FB']),
    ev: num(d.EV), hardHitPct: num(d['HardHit%']), barrelPct: num(d['Barrel%']),
    war: num(d.WAR), off: num(d.Off), def: num(d.Def), spd: num(d.Spd),
  };
}

function parsePitcher(d: any): FgPitcher {
  return {
    fgPlayerId: num(d.playerid),
    mlbamId: num(d.xMLBAMID),
    name: String(d.PlayerName || d.Name || ''),
    team: normTeam(String(d.Team || '')),
    w: num(d.W), l: num(d.L), era: num(d.ERA), g: num(d.G), gs: num(d.GS),
    sv: num(d.SV), hld: num(d.HLD), ip: num(d.IP),
    h: num(d.H), r: num(d.R), er: num(d.ER), hr: num(d.HR), bb: num(d.BB), so: num(d.SO), hbp: num(d.HBP),
    whip: num(d.WHIP), kPer9: num(d['K/9']), bbPer9: num(d['BB/9']),
    kPct: num(d['K%']), bbPct: num(d['BB%']), kBbPct: num(d['K-BB%']),
    hrPer9: num(d['HR/9']), avg: num(d.AVG), babip: num(d.BABIP), lobPct: num(d['LOB%']),
    fip: num(d.FIP), xfip: num(d.xFIP), siera: num(d.SIERA), xera: num(d.xERA),
    gbPct: num(d['GB%']), fbPct: num(d['FB%']), ldPct: num(d['LD%']),
    hrFb: num(d['HR/FB']), hardHitPct: num(d['HardHit%']),
    ev: num(d.EV), barrelPct: num(d['Barrel%']),
    fbPctPitch: num(d['FB%1'] || d['FA% (pi)']), fbVelo: num(d.FBv || d['vFA (pi)']),
    slPct: num(d['SL%'] || d['SL% (pi)']), slVelo: num(d.SLv || d['vSL (pi)']),
    cbPct: num(d['CB%'] || d['CU% (pi)']), cbVelo: num(d.CBv || d['vCU (pi)']),
    chPct: num(d['CH%'] || d['CH% (pi)']), chVelo: num(d.CHv || d['vCH (pi)']),
    war: num(d.WAR),
  };
}

function parseBatProj(d: any, system: string): FgProjection {
  return {
    fgPlayerId: num(d.playerid),
    mlbamId: num(d.xMLBAMID),
    name: String(d.PlayerName || d.ShortName || ''),
    team: normTeam(String(d.Team || '')),
    statType: 'bat',
    system,
    projPa: Math.round(num(d.PA)), projHr: Math.round(num(d.HR)),
    projRbi: Math.round(num(d.RBI)), projR: Math.round(num(d.R)), projSb: Math.round(num(d.SB)),
    projAvg: num(d.AVG), projObp: num(d.OBP), projSlg: num(d.SLG), projOps: num(d.OPS),
    projWoba: num(d.wOBA), projWrcPlus: num(d['wRC+']), projWar: num(d.WAR),
    projKPct: num(d['K%']), projBbPct: num(d['BB%']),
  };
}

function parsePitProj(d: any, system: string): FgProjection {
  return {
    fgPlayerId: num(d.playerid),
    mlbamId: num(d.xMLBAMID),
    name: String(d.PlayerName || d.ShortName || ''),
    team: normTeam(String(d.Team || '')),
    statType: 'pit',
    system,
    projW: Math.round(num(d.W)), projL: Math.round(num(d.L)), projEra: num(d.ERA),
    projIp: num(d.IP), projSo: Math.round(num(d.SO || d.K)), projBb: Math.round(num(d.BB)),
    projWhip: num(d.WHIP), projFip: num(d.FIP), projWarPit: num(d.WAR),
    projKPer9: num(d['K/9']), projBbPer9: num(d['BB/9']),
  };
}

// ---------------------------------------------------------------------------
// Public fetch functions
// ---------------------------------------------------------------------------

export async function fetchBattingLeaders(season: number, split: string = 'full', qual: number = 0): Promise<FgBatter[]> {
  const month = FG_SPLITS[split] ?? 0;
  // type=8 = dashboard view (includes advanced + statcast fields)
  // pageItems=500 to get more than default 30; paginate if needed
  const allBatters: FgBatter[] = [];
  let page = 1;
  const MAX_PAGES = 10; // safety cap: 5000 players max
  while (page <= MAX_PAGES) {
    const url = `${FG_LEADERS}?pos=all&stats=bat&lg=all&qual=${qual}&season=${season}&month=${month}&type=8&ind=0&pageItems=500&page=${page}`;
    const data = await fgFetch(url);
    const rows = data?.data || [];
    const totalCount = Number(data?.totalCount) || 0;
    if (!Array.isArray(rows) || rows.length === 0) break;
    allBatters.push(...rows.map(parseBatter));
    // Stop if we've fetched all rows (using totalCount) or got a partial page
    if (allBatters.length >= totalCount || rows.length < 500) break;
    page++;
  }
  return allBatters;
}

export async function fetchPitchingLeaders(season: number, split: string = 'full', qual: number = 0): Promise<FgPitcher[]> {
  const month = FG_SPLITS[split] ?? 0;
  const allPitchers: FgPitcher[] = [];
  let page = 1;
  const MAX_PAGES = 10; // safety cap: 5000 players max
  while (page <= MAX_PAGES) {
    const url = `${FG_LEADERS}?pos=all&stats=pit&lg=all&qual=${qual}&season=${season}&month=${month}&type=8&ind=0&pageItems=500&page=${page}`;
    const data = await fgFetch(url);
    const rows = data?.data || [];
    const totalCount = Number(data?.totalCount) || 0;
    if (!Array.isArray(rows) || rows.length === 0) break;
    allPitchers.push(...rows.map(parsePitcher));
    if (allPitchers.length >= totalCount || rows.length < 500) break;
    page++;
  }
  return allPitchers;
}

export async function fetchBattingProjections(season: number, system: string = 'steamer'): Promise<FgProjection[]> {
  const url = `${FG_PROJECTIONS}?pos=all&stats=bat&type=${system}&team=0&lg=all&players=0`;
  const data = await fgFetch(url);
  if (!Array.isArray(data)) return [];
  return data.map((d: any) => parseBatProj(d, system));
}

export async function fetchPitchingProjections(season: number, system: string = 'steamer'): Promise<FgProjection[]> {
  const url = `${FG_PROJECTIONS}?pos=all&stats=pit&type=${system}&team=0&lg=all&players=0`;
  const data = await fgFetch(url);
  if (!Array.isArray(data)) return [];
  return data.map((d: any) => parsePitProj(d, system));
}

// ---------------------------------------------------------------------------
// DB upsert helpers
// ---------------------------------------------------------------------------

export async function upsertBatters(batters: FgBatter[], season: number, split: string): Promise<number> {
  let count = 0;
  for (const b of batters) {
    await pool.query(
      `INSERT INTO fg_batting_stats
        (fg_player_id, mlbam_id, player_name, team, season, split,
         g, ab, pa, h, singles, doubles, triples, hr, r, rbi, bb, so, sb, cs, hbp, sf,
         avg, obp, slg, ops, iso, babip, woba, wrc_plus, bb_pct, k_pct,
         ld_pct, gb_pct, fb_pct, hr_fb, ev, hard_hit_pct, barrel_pct,
         war, off, def, spd, pull_date)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,CURRENT_DATE)
       ON CONFLICT (fg_player_id, season, split, pull_date) DO UPDATE SET
         player_name=EXCLUDED.player_name, team=EXCLUDED.team,
         g=EXCLUDED.g, ab=EXCLUDED.ab, pa=EXCLUDED.pa, h=EXCLUDED.h,
         singles=EXCLUDED.singles, doubles=EXCLUDED.doubles, triples=EXCLUDED.triples, hr=EXCLUDED.hr,
         r=EXCLUDED.r, rbi=EXCLUDED.rbi, bb=EXCLUDED.bb, so=EXCLUDED.so, sb=EXCLUDED.sb, cs=EXCLUDED.cs,
         hbp=EXCLUDED.hbp, sf=EXCLUDED.sf,
         avg=EXCLUDED.avg, obp=EXCLUDED.obp, slg=EXCLUDED.slg, ops=EXCLUDED.ops,
         iso=EXCLUDED.iso, babip=EXCLUDED.babip, woba=EXCLUDED.woba, wrc_plus=EXCLUDED.wrc_plus,
         bb_pct=EXCLUDED.bb_pct, k_pct=EXCLUDED.k_pct,
         ld_pct=EXCLUDED.ld_pct, gb_pct=EXCLUDED.gb_pct, fb_pct=EXCLUDED.fb_pct, hr_fb=EXCLUDED.hr_fb,
         ev=EXCLUDED.ev, hard_hit_pct=EXCLUDED.hard_hit_pct, barrel_pct=EXCLUDED.barrel_pct,
         war=EXCLUDED.war, off=EXCLUDED.off, def=EXCLUDED.def, spd=EXCLUDED.spd`,
      [b.fgPlayerId, b.mlbamId, b.name, b.team, season, split,
       b.g, b.ab, b.pa, b.h, b.singles, b.doubles, b.triples, b.hr,
       b.r, b.rbi, b.bb, b.so, b.sb, b.cs, b.hbp, b.sf,
       b.avg, b.obp, b.slg, b.ops, b.iso, b.babip, b.woba, b.wrcPlus,
       b.bbPct, b.kPct, b.ldPct, b.gbPct, b.fbPct, b.hrFb,
       b.ev, b.hardHitPct, b.barrelPct, b.war, b.off, b.def, b.spd],
    );
    count++;
  }
  return count;
}

export async function upsertPitchers(pitchers: FgPitcher[], season: number, split: string): Promise<number> {
  let count = 0;
  for (const p of pitchers) {
    await pool.query(
      `INSERT INTO fg_pitching_stats
        (fg_player_id, mlbam_id, player_name, team, season, split,
         w, l, era, g, gs, sv, hld, ip, h, r, er, hr, bb, so, hbp,
         whip, k_per_9, bb_per_9, k_pct, bb_pct, k_bb_pct, hr_per_9,
         avg, babip, lob_pct, fip, xfip, siera, xera,
         gb_pct, fb_pct, ld_pct, hr_fb, hard_hit_pct, ev, barrel_pct,
         fb_pct_pitch, fb_velo, sl_pct, sl_velo, cb_pct, cb_velo, ch_pct, ch_velo,
         war, pull_date)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,CURRENT_DATE)
       ON CONFLICT (fg_player_id, season, split, pull_date) DO UPDATE SET
         player_name=EXCLUDED.player_name, team=EXCLUDED.team,
         w=EXCLUDED.w, l=EXCLUDED.l, era=EXCLUDED.era, g=EXCLUDED.g, gs=EXCLUDED.gs,
         sv=EXCLUDED.sv, hld=EXCLUDED.hld, ip=EXCLUDED.ip,
         h=EXCLUDED.h, r=EXCLUDED.r, er=EXCLUDED.er, hr=EXCLUDED.hr, bb=EXCLUDED.bb, so=EXCLUDED.so, hbp=EXCLUDED.hbp,
         whip=EXCLUDED.whip, k_per_9=EXCLUDED.k_per_9, bb_per_9=EXCLUDED.bb_per_9,
         k_pct=EXCLUDED.k_pct, bb_pct=EXCLUDED.bb_pct, k_bb_pct=EXCLUDED.k_bb_pct, hr_per_9=EXCLUDED.hr_per_9,
         avg=EXCLUDED.avg, babip=EXCLUDED.babip, lob_pct=EXCLUDED.lob_pct,
         fip=EXCLUDED.fip, xfip=EXCLUDED.xfip, siera=EXCLUDED.siera, xera=EXCLUDED.xera,
         gb_pct=EXCLUDED.gb_pct, fb_pct=EXCLUDED.fb_pct, ld_pct=EXCLUDED.ld_pct,
         hr_fb=EXCLUDED.hr_fb, hard_hit_pct=EXCLUDED.hard_hit_pct, ev=EXCLUDED.ev, barrel_pct=EXCLUDED.barrel_pct,
         fb_pct_pitch=EXCLUDED.fb_pct_pitch, fb_velo=EXCLUDED.fb_velo,
         sl_pct=EXCLUDED.sl_pct, sl_velo=EXCLUDED.sl_velo,
         cb_pct=EXCLUDED.cb_pct, cb_velo=EXCLUDED.cb_velo,
         ch_pct=EXCLUDED.ch_pct, ch_velo=EXCLUDED.ch_velo,
         war=EXCLUDED.war`,
      [p.fgPlayerId, p.mlbamId, p.name, p.team, season, split,
       p.w, p.l, p.era, p.g, p.gs, p.sv, p.hld, p.ip,
       p.h, p.r, p.er, p.hr, p.bb, p.so, p.hbp,
       p.whip, p.kPer9, p.bbPer9, p.kPct, p.bbPct, p.kBbPct, p.hrPer9,
       p.avg, p.babip, p.lobPct, p.fip, p.xfip, p.siera, p.xera,
       p.gbPct, p.fbPct, p.ldPct, p.hrFb, p.hardHitPct, p.ev, p.barrelPct,
       p.fbPctPitch, p.fbVelo, p.slPct, p.slVelo, p.cbPct, p.cbVelo, p.chPct, p.chVelo,
       p.war],
    );
    count++;
  }
  return count;
}

export async function upsertProjections(projections: FgProjection[], season: number): Promise<number> {
  let count = 0;
  for (const p of projections) {
    await pool.query(
      `INSERT INTO fg_projections
        (fg_player_id, mlbam_id, player_name, team, season, stat_type, projection_system,
         proj_pa, proj_hr, proj_rbi, proj_r, proj_sb,
         proj_avg, proj_obp, proj_slg, proj_ops, proj_woba, proj_wrc_plus, proj_war,
         proj_k_pct, proj_bb_pct,
         proj_w, proj_l, proj_era, proj_ip, proj_so, proj_bb,
         proj_whip, proj_fip, proj_war_pit, proj_k_per_9, proj_bb_per_9,
         pull_date)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,CURRENT_DATE)
       ON CONFLICT (fg_player_id, season, stat_type, projection_system, pull_date) DO UPDATE SET
         player_name=EXCLUDED.player_name, team=EXCLUDED.team,
         proj_pa=EXCLUDED.proj_pa, proj_hr=EXCLUDED.proj_hr, proj_rbi=EXCLUDED.proj_rbi,
         proj_r=EXCLUDED.proj_r, proj_sb=EXCLUDED.proj_sb,
         proj_avg=EXCLUDED.proj_avg, proj_obp=EXCLUDED.proj_obp, proj_slg=EXCLUDED.proj_slg,
         proj_ops=EXCLUDED.proj_ops, proj_woba=EXCLUDED.proj_woba,
         proj_wrc_plus=EXCLUDED.proj_wrc_plus, proj_war=EXCLUDED.proj_war,
         proj_k_pct=EXCLUDED.proj_k_pct, proj_bb_pct=EXCLUDED.proj_bb_pct,
         proj_w=EXCLUDED.proj_w, proj_l=EXCLUDED.proj_l, proj_era=EXCLUDED.proj_era,
         proj_ip=EXCLUDED.proj_ip, proj_so=EXCLUDED.proj_so, proj_bb=EXCLUDED.proj_bb,
         proj_whip=EXCLUDED.proj_whip, proj_fip=EXCLUDED.proj_fip,
         proj_war_pit=EXCLUDED.proj_war_pit,
         proj_k_per_9=EXCLUDED.proj_k_per_9, proj_bb_per_9=EXCLUDED.proj_bb_per_9`,
      [p.fgPlayerId, p.mlbamId, p.name, p.team, season, p.statType, p.system,
       p.projPa ?? null, p.projHr ?? null, p.projRbi ?? null, p.projR ?? null, p.projSb ?? null,
       p.projAvg ?? null, p.projObp ?? null, p.projSlg ?? null, p.projOps ?? null,
       p.projWoba ?? null, p.projWrcPlus ?? null, p.projWar ?? null,
       p.projKPct ?? null, p.projBbPct ?? null,
       p.projW ?? null, p.projL ?? null, p.projEra ?? null,
       p.projIp ?? null, p.projSo ?? null, p.projBb ?? null,
       p.projWhip ?? null, p.projFip ?? null, p.projWarPit ?? null,
       p.projKPer9 ?? null, p.projBbPer9 ?? null],
    );
    count++;
  }
  return count;
}

// ---------------------------------------------------------------------------
// Query helpers — used by RIE signal + Grok prompt
// ---------------------------------------------------------------------------

export interface TeamBattingProfile {
  team: string;
  wrcPlus: number; woba: number; ops: number; iso: number;
  kPct: number; bbPct: number;
  ev: number; hardHitPct: number; barrelPct: number;
  hr: number; avg: number; obp: number; slg: number;
  playerCount: number;
}

export async function getTeamBatting(team: string, season: number, split: string = 'full'): Promise<TeamBattingProfile | null> {
  const { rows } = await pool.query(
    `SELECT team,
       AVG(wrc_plus) as wrc_plus, AVG(woba) as woba, AVG(ops) as ops, AVG(iso) as iso,
       AVG(k_pct) as k_pct, AVG(bb_pct) as bb_pct,
       AVG(ev) as ev, AVG(hard_hit_pct) as hard_hit_pct, AVG(barrel_pct) as barrel_pct,
       SUM(hr) as hr, AVG(avg) as avg, AVG(obp) as obp, AVG(slg) as slg,
       COUNT(*) as player_count
     FROM fg_batting_stats
     WHERE team = $1 AND season = $2 AND split = $3
       AND pull_date = (SELECT MAX(pull_date) FROM fg_batting_stats WHERE team = $1 AND season = $2 AND split = $3)
     GROUP BY team`,
    [team, season, split],
  );
  if (rows.length === 0) return null;
  const r = rows[0];
  return {
    team: r.team,
    wrcPlus: Number(r.wrc_plus), woba: Number(r.woba), ops: Number(r.ops), iso: Number(r.iso),
    kPct: Number(r.k_pct), bbPct: Number(r.bb_pct),
    ev: Number(r.ev), hardHitPct: Number(r.hard_hit_pct), barrelPct: Number(r.barrel_pct),
    hr: Number(r.hr), avg: Number(r.avg), obp: Number(r.obp), slg: Number(r.slg),
    playerCount: Number(r.player_count),
  };
}

export interface PitcherProfile {
  name: string;
  team: string;
  era: number; fip: number; xfip: number; siera: number;
  kPct: number; bbPct: number; kBbPct: number;
  whip: number; babip: number; hrPer9: number;
  ev: number; barrelPct: number; hardHitPct: number;
  fbVelo: number; gbPct: number;
  ip: number; gs: number; war: number;
  // Pitch mix
  fbPctPitch: number; slPct: number; cbPct: number; chPct: number;
  slVelo: number; cbVelo: number; chVelo: number;
}

function mapPitcherProfile(p: any): PitcherProfile {
  return {
    name: p.player_name, team: p.team,
    era: Number(p.era), fip: Number(p.fip), xfip: Number(p.xfip), siera: Number(p.siera),
    kPct: Number(p.k_pct), bbPct: Number(p.bb_pct), kBbPct: Number(p.k_bb_pct),
    whip: Number(p.whip), babip: Number(p.babip), hrPer9: Number(p.hr_per_9),
    ev: Number(p.ev), barrelPct: Number(p.barrel_pct), hardHitPct: Number(p.hard_hit_pct),
    fbVelo: Number(p.fb_velo), gbPct: Number(p.gb_pct),
    ip: Number(p.ip), gs: Number(p.gs), war: Number(p.war),
    fbPctPitch: Number(p.fb_pct_pitch), slPct: Number(p.sl_pct),
    cbPct: Number(p.cb_pct), chPct: Number(p.ch_pct),
    slVelo: Number(p.sl_velo), cbVelo: Number(p.cb_velo), chVelo: Number(p.ch_velo),
  };
}

export interface BullpenProfile {
  team: string;
  era: number;
  fip: number;
  xfip: number;
  whip: number;
  kPct: number;
  bbPct: number;
  barrelPct: number;
  ev: number;
  ip: number;
  relieverCount: number;
}

async function getStarterProfileForSplit(name: string, team: string, season: number, split: string): Promise<PitcherProfile | null> {
  const { rows } = await pool.query(
    `SELECT * FROM fg_pitching_stats
     WHERE team = $1 AND season = $2 AND split = $4 AND gs > 0
       AND pull_date = (SELECT MAX(pull_date) FROM fg_pitching_stats WHERE team = $1 AND season = $2 AND split = $4)
       AND (LOWER(player_name) = LOWER($3) OR player_name ILIKE '%' || $3 || '%')
     ORDER BY gs DESC
     LIMIT 1`,
    [team, season, name, split],
  );
  if (rows.length === 0) return null;
  return mapPitcherProfile(rows[0]);
}

export async function getStarterProfile(name: string, team: string, season: number): Promise<PitcherProfile | null> {
  return getStarterProfileForSplit(name, team, season, 'full');
}

export async function getStarterRecentProfile(name: string, team: string, season: number): Promise<PitcherProfile | null> {
  return getStarterProfileForSplit(name, team, season, 'last30');
}

export async function getTeamPitching(team: string, season: number, split: string = 'full'): Promise<{era: number; fip: number; xfip: number; kPct: number; bbPct: number; whip: number; ev: number; barrelPct: number} | null> {
  const { rows } = await pool.query(
    `SELECT
       AVG(era) as era, AVG(fip) as fip, AVG(xfip) as xfip,
       AVG(k_pct) as k_pct, AVG(bb_pct) as bb_pct, AVG(whip) as whip,
       AVG(ev) as ev, AVG(barrel_pct) as barrel_pct
     FROM fg_pitching_stats
     WHERE team = $1 AND season = $2 AND split = $3
       AND pull_date = (SELECT MAX(pull_date) FROM fg_pitching_stats WHERE team = $1 AND season = $2 AND split = $3)`,
    [team, season, split],
  );
  if (rows.length === 0 || !rows[0].era) return null;
  const r = rows[0];
  return {
    era: Number(r.era), fip: Number(r.fip), xfip: Number(r.xfip),
    kPct: Number(r.k_pct), bbPct: Number(r.bb_pct), whip: Number(r.whip),
    ev: Number(r.ev), barrelPct: Number(r.barrel_pct),
  };
}

export async function getBullpenPitching(team: string, season: number, split: string = 'full'): Promise<BullpenProfile | null> {
  const { rows } = await pool.query(
    `SELECT
       team,
       COUNT(*) as reliever_count,
       COALESCE(SUM(ip), 0) as ip,
       AVG(era) as era,
       AVG(fip) as fip,
       AVG(xfip) as xfip,
       AVG(k_pct) as k_pct,
       AVG(bb_pct) as bb_pct,
       AVG(whip) as whip,
       AVG(ev) as ev,
       AVG(barrel_pct) as barrel_pct
     FROM fg_pitching_stats
     WHERE team = $1 AND season = $2 AND split = $3
       AND pull_date = (SELECT MAX(pull_date) FROM fg_pitching_stats WHERE team = $1 AND season = $2 AND split = $3)
       AND COALESCE(gs, 0) = 0
     GROUP BY team`,
    [team, season, split],
  );
  if (rows.length === 0 || Number(rows[0].ip) === 0) return null;
  const r = rows[0];
  return {
    team: r.team,
    era: Number(r.era),
    fip: Number(r.fip),
    xfip: Number(r.xfip),
    whip: Number(r.whip),
    kPct: Number(r.k_pct),
    bbPct: Number(r.bb_pct),
    barrelPct: Number(r.barrel_pct),
    ev: Number(r.ev),
    ip: Number(r.ip),
    relieverCount: Number(r.reliever_count),
  };
}

export async function getSteamerProjection(name: string, team: string, statType: 'bat' | 'pit', season: number): Promise<FgProjection | null> {
  const { rows } = await pool.query(
    `SELECT * FROM fg_projections
     WHERE team = $1 AND season = $2 AND stat_type = $3 AND projection_system = 'steamer'
       AND pull_date = (SELECT MAX(pull_date) FROM fg_projections WHERE team = $1 AND season = $2 AND stat_type = $3 AND projection_system = 'steamer')
       AND (LOWER(player_name) = LOWER($4) OR player_name ILIKE '%' || $4 || '%')
     LIMIT 1`,
    [team, season, statType, name],
  );
  if (rows.length === 0) return null;
  const r = rows[0];
  return {
    fgPlayerId: r.fg_player_id, mlbamId: r.mlbam_id, name: r.player_name, team: r.team,
    statType: r.stat_type, system: r.projection_system,
    projPa: r.proj_pa, projHr: r.proj_hr, projRbi: r.proj_rbi, projR: r.proj_r, projSb: r.proj_sb,
    projAvg: Number(r.proj_avg), projObp: Number(r.proj_obp), projSlg: Number(r.proj_slg),
    projOps: Number(r.proj_ops), projWoba: Number(r.proj_woba),
    projWrcPlus: Number(r.proj_wrc_plus), projWar: Number(r.proj_war),
    projKPct: Number(r.proj_k_pct), projBbPct: Number(r.proj_bb_pct),
    projW: r.proj_w, projL: r.proj_l, projEra: Number(r.proj_era),
    projIp: Number(r.proj_ip), projSo: r.proj_so, projBb: r.proj_bb,
    projWhip: Number(r.proj_whip), projFip: Number(r.proj_fip),
    projWarPit: Number(r.proj_war_pit),
    projKPer9: Number(r.proj_k_per_9), projBbPer9: Number(r.proj_bb_per_9),
  };
}

export async function getTeamBattingProjection(team: string, season: number): Promise<TeamBattingProjection | null> {
  const { rows } = await pool.query(
    `SELECT
       team,
       COUNT(*) as player_count,
       COALESCE(SUM(proj_pa), 0) as proj_pa,
       COALESCE(SUM(proj_pa * proj_wrc_plus) / NULLIF(SUM(proj_pa), 0), 0) as proj_wrc_plus,
       COALESCE(SUM(proj_pa * proj_woba) / NULLIF(SUM(proj_pa), 0), 0) as proj_woba,
       COALESCE(SUM(proj_pa * proj_ops) / NULLIF(SUM(proj_pa), 0), 0) as proj_ops,
       COALESCE(SUM(proj_pa * (proj_slg - proj_avg)) / NULLIF(SUM(proj_pa), 0), 0) as proj_iso,
       COALESCE(SUM(proj_pa * proj_k_pct) / NULLIF(SUM(proj_pa), 0), 0) as proj_k_pct,
       COALESCE(SUM(proj_pa * proj_bb_pct) / NULLIF(SUM(proj_pa), 0), 0) as proj_bb_pct
     FROM fg_projections
     WHERE team = $1 AND season = $2 AND stat_type = 'bat' AND projection_system = 'steamer'
       AND pull_date = (
         SELECT MAX(pull_date) FROM fg_projections
         WHERE team = $1 AND season = $2 AND stat_type = 'bat' AND projection_system = 'steamer'
       )
     GROUP BY team`,
    [team, season],
  );
  if (rows.length === 0 || Number(rows[0].proj_pa) === 0) return null;
  const r = rows[0];
  return {
    team: r.team,
    playerCount: Number(r.player_count),
    projPa: Number(r.proj_pa),
    projWrcPlus: Number(r.proj_wrc_plus),
    projWoba: Number(r.proj_woba),
    projOps: Number(r.proj_ops),
    projIso: Number(r.proj_iso),
    projKPct: Number(r.proj_k_pct),
    projBbPct: Number(r.proj_bb_pct),
  };
}

export async function getTeamPitchingProjection(team: string, season: number): Promise<TeamPitchingProjection | null> {
  const { rows } = await pool.query(
    `SELECT
       team,
       COUNT(*) as player_count,
       COALESCE(SUM(proj_ip), 0) as proj_ip,
       COALESCE(SUM(proj_ip * proj_era) / NULLIF(SUM(proj_ip), 0), 0) as proj_era,
       COALESCE(SUM(proj_ip * proj_fip) / NULLIF(SUM(proj_ip), 0), 0) as proj_fip,
       COALESCE(SUM(proj_ip * proj_whip) / NULLIF(SUM(proj_ip), 0), 0) as proj_whip,
       COALESCE(SUM(proj_ip * proj_k_per_9) / NULLIF(SUM(proj_ip), 0), 0) as proj_k_per_9,
       COALESCE(SUM(proj_ip * proj_bb_per_9) / NULLIF(SUM(proj_ip), 0), 0) as proj_bb_per_9
     FROM fg_projections
     WHERE team = $1 AND season = $2 AND stat_type = 'pit' AND projection_system = 'steamer'
       AND pull_date = (
         SELECT MAX(pull_date) FROM fg_projections
         WHERE team = $1 AND season = $2 AND stat_type = 'pit' AND projection_system = 'steamer'
       )
     GROUP BY team`,
    [team, season],
  );
  if (rows.length === 0 || Number(rows[0].proj_ip) === 0) return null;
  const r = rows[0];
  return {
    team: r.team,
    playerCount: Number(r.player_count),
    projIp: Number(r.proj_ip),
    projEra: Number(r.proj_era),
    projFip: Number(r.proj_fip),
    projWhip: Number(r.proj_whip),
    projKPer9: Number(r.proj_k_per_9),
    projBbPer9: Number(r.proj_bb_per_9),
  };
}

// ---------------------------------------------------------------------------
// Prompt formatter — injected into Grok for MLB games
// ---------------------------------------------------------------------------

function fmt(v: number | undefined | null, decimals: number = 3): string {
  if (v === undefined || v === null || isNaN(v)) return 'N/A';
  return v.toFixed(decimals);
}

function pct(v: number | undefined | null): string {
  if (v === undefined || v === null || isNaN(v)) return 'N/A';
  return (v * 100).toFixed(1) + '%';
}

export function formatFangraphsForPrompt(data: {
  homeBat?: TeamBattingProfile | null;
  awayBat?: TeamBattingProfile | null;
  homePit?: { era: number; fip: number; xfip: number; kPct: number; bbPct: number; whip: number; ev: number; barrelPct: number } | null;
  awayPit?: { era: number; fip: number; xfip: number; kPct: number; bbPct: number; whip: number; ev: number; barrelPct: number } | null;
  homeStarter?: PitcherProfile | null;
  awayStarter?: PitcherProfile | null;
  homeStarterName?: string | null;
  awayStarterName?: string | null;
  homeBatProj?: TeamBattingProjection | null;
  awayBatProj?: TeamBattingProjection | null;
  homePitProj?: TeamPitchingProjection | null;
  awayPitProj?: TeamPitchingProjection | null;
  homeTeam: string;
  awayTeam: string;
}): string {
  if (
    !data.homeBat && !data.awayBat &&
    !data.homeStarter && !data.awayStarter &&
    !data.homeBatProj && !data.awayBatProj &&
    !data.homePitProj && !data.awayPitProj
  ) return '';

  let s = '\n--- FANGRAPHS ADVANCED ANALYTICS (MLB) ---\n';

  // Home batting
  if (data.homeBat) {
    const b = data.homeBat;
    s += `\nHOME: ${data.homeTeam} (${b.playerCount} qualified batters)\n`;
    s += `  Offense: wRC+ ${fmt(b.wrcPlus, 1)} | wOBA ${fmt(b.woba)} | OPS ${fmt(b.ops)} | ISO ${fmt(b.iso)} | AVG ${fmt(b.avg)}\n`;
    s += `  Discipline: K% ${pct(b.kPct)} | BB% ${pct(b.bbPct)}\n`;
    s += `  Statcast: EV ${fmt(b.ev, 1)}mph | Barrel% ${pct(b.barrelPct)} | HardHit% ${pct(b.hardHitPct)}\n`;
  }

  // Home starter
  if (data.homeStarter) {
    const p = data.homeStarter;
    s += `  Probable Starter: ${p.name}\n`;
    s += `    Season: ${fmt(p.era, 2)} ERA | ${fmt(p.fip, 2)} FIP | ${fmt(p.xfip, 2)} xFIP | ${fmt(p.siera, 2)} SIERA\n`;
    s += `    K% ${pct(p.kPct)} | BB% ${pct(p.bbPct)} | K-BB% ${pct(p.kBbPct)} | WHIP ${fmt(p.whip, 2)}\n`;
    if (p.fbVelo > 0) {
      s += `    Pitch Mix: FB ${pct(p.fbPctPitch)} (${fmt(p.fbVelo, 1)}mph)`;
      if (p.slPct > 0) s += `, SL ${pct(p.slPct)} (${fmt(p.slVelo, 1)})`;
      if (p.cbPct > 0) s += `, CB ${pct(p.cbPct)} (${fmt(p.cbVelo, 1)})`;
      if (p.chPct > 0) s += `, CH ${pct(p.chPct)} (${fmt(p.chVelo, 1)})`;
      s += '\n';
    }
    s += `    IP: ${fmt(p.ip, 1)} | GS: ${p.gs} | WAR: ${fmt(p.war, 1)} | GB% ${pct(p.gbPct)}\n`;
    s += `    Statcast vs: EV ${fmt(p.ev, 1)} | Barrel% ${pct(p.barrelPct)} | HardHit% ${pct(p.hardHitPct)}\n`;
  } else if (data.homeStarterName) {
    s += `  Probable Starter: ${data.homeStarterName} (advanced FanGraphs profile unavailable)\n`;
  }

  // Away batting
  if (data.awayBat) {
    const b = data.awayBat;
    s += `\nAWAY: ${data.awayTeam} (${b.playerCount} qualified batters)\n`;
    s += `  Offense: wRC+ ${fmt(b.wrcPlus, 1)} | wOBA ${fmt(b.woba)} | OPS ${fmt(b.ops)} | ISO ${fmt(b.iso)} | AVG ${fmt(b.avg)}\n`;
    s += `  Discipline: K% ${pct(b.kPct)} | BB% ${pct(b.bbPct)}\n`;
    s += `  Statcast: EV ${fmt(b.ev, 1)}mph | Barrel% ${pct(b.barrelPct)} | HardHit% ${pct(b.hardHitPct)}\n`;
  }

  // Away starter
  if (data.awayStarter) {
    const p = data.awayStarter;
    s += `  Probable Starter: ${p.name}\n`;
    s += `    Season: ${fmt(p.era, 2)} ERA | ${fmt(p.fip, 2)} FIP | ${fmt(p.xfip, 2)} xFIP | ${fmt(p.siera, 2)} SIERA\n`;
    s += `    K% ${pct(p.kPct)} | BB% ${pct(p.bbPct)} | K-BB% ${pct(p.kBbPct)} | WHIP ${fmt(p.whip, 2)}\n`;
    if (p.fbVelo > 0) {
      s += `    Pitch Mix: FB ${pct(p.fbPctPitch)} (${fmt(p.fbVelo, 1)}mph)`;
      if (p.slPct > 0) s += `, SL ${pct(p.slPct)} (${fmt(p.slVelo, 1)})`;
      if (p.cbPct > 0) s += `, CB ${pct(p.cbPct)} (${fmt(p.cbVelo, 1)})`;
      if (p.chPct > 0) s += `, CH ${pct(p.chPct)} (${fmt(p.chVelo, 1)})`;
      s += '\n';
    }
    s += `    IP: ${fmt(p.ip, 1)} | GS: ${p.gs} | WAR: ${fmt(p.war, 1)} | GB% ${pct(p.gbPct)}\n`;
    s += `    Statcast vs: EV ${fmt(p.ev, 1)} | Barrel% ${pct(p.barrelPct)} | HardHit% ${pct(p.hardHitPct)}\n`;
  } else if (data.awayStarterName) {
    s += `  Probable Starter: ${data.awayStarterName} (advanced FanGraphs profile unavailable)\n`;
  }

  // Edge signals
  if (data.homeBat && data.awayBat) {
    s += '\nEDGE SIGNALS:\n';
    const wrcDiff = (data.homeBat.wrcPlus - data.awayBat.wrcPlus);
    const evDiff = (data.homeBat.ev - data.awayBat.ev);
    const barrelDiff = (data.homeBat.barrelPct - data.awayBat.barrelPct);
    s += `  Offense: ${wrcDiff > 0 ? 'Home' : 'Away'} +${Math.abs(wrcDiff).toFixed(1)} wRC+ edge\n`;
    s += `  Statcast: ${evDiff > 0 ? 'Home' : 'Away'} +${Math.abs(evDiff).toFixed(1)}mph EV edge\n`;
    if (data.homeStarter && data.awayStarter) {
      const fipDiff = data.awayStarter.fip - data.homeStarter.fip;
      s += `  Starting Pitching: ${data.homeStarter.name} (${fmt(data.homeStarter.fip, 2)} FIP) vs ${data.awayStarter.name} (${fmt(data.awayStarter.fip, 2)} FIP) — ${Math.abs(fipDiff) > 0.5 ? 'significant' : 'marginal'} ${fipDiff > 0 ? 'home' : 'away'} advantage\n`;
    }
  }

  if (data.homeBatProj || data.awayBatProj || data.homePitProj || data.awayPitProj) {
    s += '\n2026 STEAMER TEAM PROJECTIONS:\n';
    if (data.homeBatProj || data.homePitProj) {
      s += `  ${data.homeTeam}:`;
      if (data.homeBatProj) s += ` bat wRC+ ${fmt(data.homeBatProj.projWrcPlus, 1)} | OPS ${fmt(data.homeBatProj.projOps)} | K% ${pct(data.homeBatProj.projKPct)}`;
      if (data.homePitProj) s += ` | pit ERA ${fmt(data.homePitProj.projEra, 2)} | FIP ${fmt(data.homePitProj.projFip, 2)} | WHIP ${fmt(data.homePitProj.projWhip, 2)}`;
      s += '\n';
    }
    if (data.awayBatProj || data.awayPitProj) {
      s += `  ${data.awayTeam}:`;
      if (data.awayBatProj) s += ` bat wRC+ ${fmt(data.awayBatProj.projWrcPlus, 1)} | OPS ${fmt(data.awayBatProj.projOps)} | K% ${pct(data.awayBatProj.projKPct)}`;
      if (data.awayPitProj) s += ` | pit ERA ${fmt(data.awayPitProj.projEra, 2)} | FIP ${fmt(data.awayPitProj.projFip, 2)} | WHIP ${fmt(data.awayPitProj.projWhip, 2)}`;
      s += '\n';
    }
  }

  if (!data.homeStarterName && !data.awayStarterName) {
    s += '\nSTARTER STATUS:\n';
    s += '  Probable starters are not linked in the current feed. Do not overstate a starting-pitcher edge unless corroborated elsewhere.\n';
  }

  s += '--- END FANGRAPHS ---\n';
  return s;
}