import { Pool } from 'pg';
import { asyncBufferFromUrl, parquetReadObjects } from 'hyparquet/src/node.js';

const DEFAULT_MATCHUP_LIMIT = 10;
const DEFAULT_RECENT_WINDOW = 'season';
const DEFAULT_SPLIT_KEY = 'overall';
const DEFAULT_WEIGHTED_MODE = 'weighted';
const DEFAULT_FALLBACK_DAYS = 7;
const DEFAULT_ROTOWIRE_URL = 'https://www.rotowire.com/baseball/daily-lineups.php';
const COCKROACH_RETRY_CODES = new Set(['40001']);
const PROFILE_CANDIDATE_LIMIT = 5;
const ROTOWIRE_LINEUP_STATUS = new Map([
  ['Confirmed Lineup', 'confirmed'],
  ['Expected Lineup', 'expected'],
  ['Unknown Lineup', 'unknown'],
]);
const ROTOWIRE_POSITION_TOKENS = new Set(['C', '1B', '2B', '3B', 'SS', 'LF', 'CF', 'RF', 'DH', 'P']);
const ROTOWIRE_STOP_TOKENS = new Set(['Home Run Odds', 'Starting Pitcher Intel']);
const ROTOWIRE_TIME_RE = /^\d{1,2}:\d{2}\s(?:AM|PM)\sET$/;
const ROTOWIRE_PRICE_RE = /^\$\d[\d,]*$/;
const MLB_TEAM_ALIASES = new Map([
  ['ARI', ['ari', 'arizona', 'diamondbacks', 'arizona diamondbacks']],
  ['ATL', ['atl', 'atlanta', 'braves', 'atlanta braves']],
  ['BAL', ['bal', 'baltimore', 'orioles', 'baltimore orioles']],
  ['BOS', ['bos', 'boston', 'red sox', 'boston red sox']],
  ['CHC', ['chc', 'chicago cubs', 'cubs']],
  ['CWS', ['cws', 'chw', 'chicago white sox', 'white sox']],
  ['CIN', ['cin', 'cincinnati', 'reds', 'cincinnati reds']],
  ['CLE', ['cle', 'cleveland', 'guardians', 'cleveland guardians']],
  ['COL', ['col', 'colorado', 'rockies', 'colorado rockies']],
  ['DET', ['det', 'detroit', 'tigers', 'detroit tigers']],
  ['HOU', ['hou', 'houston', 'astros', 'houston astros']],
  ['KC', ['kc', 'kcr', 'kansas city', 'royals', 'kansas city royals']],
  ['LAA', ['laa', 'los angeles angels', 'angels', 'anaheim angels']],
  ['LAD', ['lad', 'los angeles dodgers', 'dodgers']],
  ['MIA', ['mia', 'miami', 'marlins', 'miami marlins', 'florida marlins']],
  ['MIL', ['mil', 'milwaukee', 'brewers', 'milwaukee brewers']],
  ['MIN', ['min', 'minnesota', 'twins', 'minnesota twins']],
  ['NYM', ['nym', 'mets', 'new york mets']],
  ['NYY', ['nyy', 'yankees', 'new york yankees']],
  ['ATH', ['ath', 'athletics', 'a\'s', 'as', 'oakland', 'oakland athletics']],
  ['PHI', ['phi', 'philadelphia', 'phillies', 'philadelphia phillies']],
  ['PIT', ['pit', 'pittsburgh', 'pirates', 'pittsburgh pirates']],
  ['SD', ['sd', 'sdp', 'san diego', 'padres', 'san diego padres']],
  ['SEA', ['sea', 'seattle', 'mariners', 'seattle mariners']],
  ['SF', ['sf', 'sfg', 'san francisco', 'giants', 'san francisco giants']],
  ['STL', ['stl', 'cardinals', 'st louis', 'st. louis', 'st louis cardinals', 'st. louis cardinals']],
  ['TB', ['tb', 'tbr', 'tampa bay', 'rays', 'tampa bay rays']],
  ['TEX', ['tex', 'texas', 'rangers', 'texas rangers']],
  ['TOR', ['tor', 'toronto', 'blue jays', 'toronto blue jays']],
  ['WSH', ['wsh', 'was', 'washington', 'nationals', 'washington nationals']],
]);

const HITTER_COLUMNS = [
  'game_pk',
  'team',
  'opponent_team',
  'opposing_pitcher_name',
  'opposing_pitcher_hand',
  'batter',
  'player_id',
  'hitter_name',
  'stand',
  'split_key',
  'recent_window',
  'weighted_mode',
  'matchup_score',
  'ceiling_score',
  'zone_fit_score',
  'likely_starter_score',
  'xwoba',
  'swstr_pct',
  'barrel_bbe_pct',
  'fb_pct',
  'pulled_barrel_pct',
  'sweet_spot_pct',
  'barrel_bip_pct',
  'hard_hit_pct',
  'avg_launch_angle',
];

const PITCHER_COLUMNS = [
  'game_pk',
  'team',
  'opponent_team',
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'p_throws',
  'split_key',
  'recent_window',
  'weighted_mode',
  'pitcher_score',
  'strikeout_score',
  'raw_pitcher_score',
  'raw_strikeout_score',
  'pitcher_matchup_adjustment',
  'strikeout_matchup_adjustment',
  'opponent_lineup_quality',
  'opponent_contact_threat',
  'opponent_whiff_tendency',
  'opponent_family_fit_allowed',
  'lineup_source',
  'lineup_hitter_count',
  'xwoba',
  'csw_pct',
  'swstr_pct',
  'putaway_pct',
  'ball_pct',
  'siera',
  'gb_pct',
  'gb_fb_ratio',
  'barrel_bip_pct',
  'hard_hit_pct',
];

const SLATE_COLUMNS = [
  'game_pk',
  'away_team',
  'home_team',
  'away_probable_pitcher_id',
  'home_probable_pitcher_id',
  'away_probable_pitcher',
  'home_probable_pitcher',
  'away_probable_hand',
  'home_probable_hand',
];

const ROSTER_COLUMNS = ['team', 'player_id', 'player_name'];
const EXCLUSION_COLUMNS = ['player_id', 'exclude_from_hitter_tables'];

const REUSABLE_HITTER_COLUMNS = [
  'team',
  'batter',
  'player_id',
  'hitter_name',
  'split_key',
  'recent_window',
  'weighted_mode',
  'matchup_score',
  'ceiling_score',
  'zone_fit_score',
  'likely_starter_score',
  'xwoba',
  'fb_pct',
  'pulled_barrel_pct',
  'sweet_spot_pct',
  'barrel_bip_pct',
  'hard_hit_pct',
  'avg_launch_angle',
];

const REUSABLE_PITCHER_COLUMNS = [
  'team',
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'p_throws',
  'split_key',
  'recent_window',
  'weighted_mode',
  'pitcher_score',
  'strikeout_score',
  'raw_pitcher_score',
  'raw_strikeout_score',
  'pitcher_matchup_adjustment',
  'strikeout_matchup_adjustment',
  'xwoba',
  'csw_pct',
  'swstr_pct',
  'putaway_pct',
  'ball_pct',
  'siera',
  'gb_pct',
  'gb_fb_ratio',
  'barrel_bip_pct',
  'hard_hit_pct',
];

const HITTER_ROLLING_COLUMNS = [
  'batter',
  'player_id',
  'hitter_name',
  'window_label',
  'window_size',
  'xwoba',
  'hard_hit_pct',
  'barrel_bip_pct',
  'sweet_spot_pct',
];

const PITCHER_ROLLING_COLUMNS = [
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'window_label',
  'window_size',
  'xwoba',
  'csw_pct',
  'swstr_pct',
  'putaway_pct',
  'ball_pct',
];

const BATTER_ZONE_COLUMNS = [
  'batter_id',
  'pitcher_hand_key',
  'batter',
  'player_id',
  'hitter_name',
  'pitch_type',
  'zone_label',
  'zone',
  'hit_rate',
  'hr_rate',
  'xwoba',
  'hard_hit_pct',
  'barrel_bip_pct',
  'sample_size',
  'bip',
];

const PITCHER_ZONE_COLUMNS = [
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'batter_side_key',
  'pitch_type',
  'zone_label',
  'zone',
  'usage_rate',
  'xwoba_allowed',
  'hard_hit_pct_allowed',
  'barrel_bip_pct_allowed',
  'sample_size',
  'bip',
];

const ARSENAL_COLUMNS = [
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'pitch_type',
  'usage_pct',
  'avg_velocity',
  'avg_spin_rate',
  'whiff_rate',
  'called_strike_rate',
  'putaway_rate',
];

const COUNT_USAGE_COLUMNS = [
  'pitcher_id',
  'player_id',
  'pitcher_name',
  'count_bucket',
  'batter_side_key',
  'pitch_type',
  'usage_pct',
];

function addDays(value, delta) {
  const next = new Date(`${value}T12:00:00Z`);
  next.setUTCDate(next.getUTCDate() + delta);
  return next.toISOString().slice(0, 10);
}

function parseDateOrToday(value) {
  if (!value) {
    return new Date().toISOString().slice(0, 10);
  }

  if (/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    return value;
  }

  throw new Error('Dates must use YYYY-MM-DD format.');
}

function limitOrDefault(value, max = 15) {
  const numeric = Number(value ?? DEFAULT_MATCHUP_LIMIT);
  if (!Number.isFinite(numeric) || numeric <= 0) {
    return DEFAULT_MATCHUP_LIMIT;
  }
  return Math.min(max, Math.max(1, Math.floor(numeric)));
}

function normalizeText(value) {
  return String(value ?? '').trim().toLowerCase();
}

function normalizeName(value) {
  return String(value ?? '')
    .normalize('NFKD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, ' ')
    .trim()
    .replace(/\s+/g, ' ');
}

function normalizeTeam(value) {
  return String(value ?? '').trim().toUpperCase();
}

function resolveTeamAliases(value) {
  const normalized = normalizeText(value);
  if (!normalized) {
    return [];
  }

  const matches = [];
  for (const [canonical, aliases] of MLB_TEAM_ALIASES.entries()) {
    if (aliases.includes(normalized) || canonical.toLowerCase() === normalized) {
      matches.push(canonical);
    }
  }

  if (matches.length > 0) {
    return matches;
  }

  return [normalizeTeam(value)];
}

function rowMatchesTeamFilter(rowTeam, teamFilter) {
  if (!teamFilter) {
    return true;
  }

  const candidates = resolveTeamAliases(teamFilter);
  const normalizedRowTeam = normalizeTeam(rowTeam);
  return candidates.includes(normalizedRowTeam);
}

function resolveCanonicalTeamFilter(value) {
  if (!value) {
    return null;
  }

  const candidates = resolveTeamAliases(value);
  if (candidates.length === 1) {
    return candidates[0];
  }

  return normalizeTeam(value);
}

function numberOrNull(value) {
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : null;
}

function compareNullableDescending(left, right) {
  const leftValue = numberOrNull(left);
  const rightValue = numberOrNull(right);
  if (leftValue === null && rightValue === null) {
    return 0;
  }
  if (leftValue === null) {
    return 1;
  }
  if (rightValue === null) {
    return -1;
  }
  return rightValue - leftValue;
}

function sortHittersLiveApp(rows) {
  return [...rows].sort((left, right) =>
    compareNullableDescending(left.matchup_score, right.matchup_score)
    || compareNullableDescending(left.xwoba, right.xwoba)
    || String(left.hitter_name ?? '').localeCompare(String(right.hitter_name ?? ''))
  );
}

function sortHitters(rows) {
  return [...rows].sort((left, right) =>
    compareNullableDescending(left.matchup_score, right.matchup_score)
    || compareNullableDescending(left.ceiling_score, right.ceiling_score)
    || compareNullableDescending(left.likely_starter_score, right.likely_starter_score)
    || compareNullableDescending(left.xwoba, right.xwoba)
    || String(left.hitter_name ?? '').localeCompare(String(right.hitter_name ?? ''))
  );
}

function sortPitchers(rows) {
  return [...rows].sort((left, right) =>
    compareNullableDescending(left.pitcher_score, right.pitcher_score)
    || compareNullableDescending(left.strikeout_score, right.strikeout_score)
    || compareNullableDescending(left.pitcher_matchup_adjustment, right.pitcher_matchup_adjustment)
    || compareNullableDescending(left.xwoba, right.xwoba)
    || String(left.pitcher_name ?? '').localeCompare(String(right.pitcher_name ?? ''))
  );
}

function normalizeSeries(values, inverse = false) {
  const numerics = values.map((value) => numberOrNull(value));
  const finite = numerics.filter((value) => value !== null);
  if (!finite.length) {
    return values.map(() => 0.5);
  }

  const min = Math.min(...finite);
  const max = Math.max(...finite);
  const normalized = numerics.map((value) => {
    if (value === null || Math.abs(max - min) < 1e-9) {
      return 0.5;
    }
    return (value - min) / (max - min);
  });

  return normalized.map((value) => inverse ? 1 - value : value);
}

function launchAngleBandScore(value, low = 20, ideal = 27.5, high = 35) {
  const numeric = numberOrNull(value);
  if (numeric === null) {
    return 0.5;
  }
  if (numeric >= low && numeric <= high) {
    if (numeric <= ideal) {
      return 0.8 + 0.2 * ((numeric - low) / Math.max(ideal - low, 1e-9));
    }
    return 0.8 + 0.2 * ((high - numeric) / Math.max(high - ideal, 1e-9));
  }
  if (numeric < low) {
    return Math.max(0, 0.8 - ((low - numeric) / Math.max(low, 1e-9)) * 0.8);
  }
  return Math.max(0, 0.8 - ((numeric - high) / Math.max(high, 1e-9)) * 0.8);
}

function weightedAverage(pairs) {
  const valid = pairs.filter(({ value, weight }) => value !== null && weight !== null && weight > 0);
  if (!valid.length) {
    return null;
  }
  const totalWeight = valid.reduce((sum, item) => sum + item.weight, 0);
  if (totalWeight <= 0) {
    return null;
  }
  return valid.reduce((sum, item) => sum + (item.value * item.weight), 0) / totalWeight;
}

function aggregateBatterZoneMap(rows) {
  if (!rows.length) {
    return [];
  }

  const grouped = new Map();
  for (const row of rows) {
    const zone = numberOrNull(row.zone);
    if (zone === null) {
      continue;
    }
    const key = String(Math.trunc(zone));
    const bucket = grouped.get(key) ?? [];
    bucket.push(row);
    grouped.set(key, bucket);
  }

  const output = [];
  for (const [key, group] of grouped.entries()) {
    const zone = Number(key);
    const zoneValue = (
      (weightedAverage(group.map((row) => ({ value: numberOrNull(row.hit_rate), weight: numberOrNull(row.sample_size) }))) ?? 0) * 0.6
      + (weightedAverage(group.map((row) => ({ value: numberOrNull(row.hr_rate), weight: numberOrNull(row.sample_size) }))) ?? 0) * 0.4
    );
    output.push({
      zone,
      sample_size: group.reduce((sum, row) => sum + (numberOrNull(row.sample_size) ?? 0), 0),
      zone_value: zoneValue,
    });
  }

  return output;
}

function aggregatePitcherZoneMap(rows) {
  if (!rows.length) {
    return [];
  }

  const grouped = new Map();
  for (const row of rows) {
    const zone = numberOrNull(row.zone);
    if (zone === null) {
      continue;
    }
    const key = String(Math.trunc(zone));
    const bucket = grouped.get(key) ?? [];
    bucket.push(row);
    grouped.set(key, bucket);
  }

  const output = [];
  for (const [key, group] of grouped.entries()) {
    const zone = Number(key);
    output.push({
      zone,
      sample_size: group.reduce((sum, row) => sum + (numberOrNull(row.sample_size) ?? 0), 0),
      zone_value: group.reduce((sum, row) => sum + (numberOrNull(row.usage_rate) ?? 0), 0),
    });
  }

  return output;
}

function buildZoneOverlayMap(batterMap, pitcherMap) {
  if (!batterMap.length || !pitcherMap.length) {
    return [];
  }

  const pitcherByZone = new Map(pitcherMap.map((row) => [row.zone, row]));
  const merged = batterMap
    .filter((row) => pitcherByZone.has(row.zone))
    .map((row) => ({
      zone: row.zone,
      sample_size: Math.min(row.sample_size ?? 0, pitcherByZone.get(row.zone)?.sample_size ?? 0),
      batter_zone_value: row.zone_value,
      pitcher_zone_value: pitcherByZone.get(row.zone)?.zone_value ?? null,
    }));

  if (!merged.length) {
    return [];
  }

  const batterScale = normalizeSeries(merged.map((row) => row.batter_zone_value));
  const pitcherScale = normalizeSeries(merged.map((row) => row.pitcher_zone_value));
  return merged.map((row, index) => ({
    zone: row.zone,
    sample_size: row.sample_size,
    zone_value: batterScale[index] * pitcherScale[index],
  }));
}

function overlayZoneFitScore(batterMap, pitcherMap) {
  const overlay = buildZoneOverlayMap(batterMap, pitcherMap);
  if (!overlay.length) {
    return 0.5;
  }
  const sampleSum = overlay.reduce((sum, row) => sum + (numberOrNull(row.sample_size) ?? 0), 0);
  if (sampleSum < 15) {
    return 0.5;
  }
  const score = weightedAverage(overlay.map((row) => ({ value: numberOrNull(row.zone_value), weight: numberOrNull(row.sample_size) })));
  if (score === null) {
    return 0.5;
  }
  return Math.max(0, Math.min(1, score));
}

function selectBatterZoneRows(rows, batterId, opposingPitcherHand) {
  const hitterRows = rows.filter((row) => String(row.batter_id ?? row.batter ?? '') === String(batterId));
  if (!hitterRows.length) {
    return [];
  }
  const handKey = opposingPitcherHand === 'R' ? 'vs_rhp' : opposingPitcherHand === 'L' ? 'vs_lhp' : 'overall';
  const specific = hitterRows.filter((row) => String(row.pitcher_hand_key ?? '') === handKey);
  if (specific.length) {
    return specific;
  }
  const overall = hitterRows.filter((row) => String(row.pitcher_hand_key ?? '') === 'overall');
  return overall.length ? overall : hitterRows;
}

function selectPitcherZoneRows(rows, pitcherId, hitterSide) {
  const pitcherRows = rows.filter((row) => String(row.pitcher_id ?? '') === String(pitcherId));
  if (!pitcherRows.length) {
    return [];
  }
  const sideKey = hitterSide === 'L' ? 'vs_lhh' : hitterSide === 'R' ? 'vs_rhh' : 'overall';
  const specific = pitcherRows.filter((row) => String(row.batter_side_key ?? '') === sideKey);
  if (specific.length) {
    return specific;
  }
  const overall = pitcherRows.filter((row) => String(row.batter_side_key ?? '') === 'overall');
  return overall.length ? overall : pitcherRows;
}

function buildZoneFitScores(rows, batterZoneRows, pitcherZoneRows) {
  const pitcherCache = new Map();
  const batterCache = new Map();

  return rows.map((row) => {
    const batterId = String(row.batter ?? row.player_id ?? '');
    const pitcherId = String(row.opposing_pitcher_id ?? '');
    if (!batterId || !pitcherId) {
      return 0.5;
    }

    const batterKey = `${batterId}|${row.opposing_pitcher_hand ?? 'overall'}`;
    const pitcherKey = `${pitcherId}|${row.stand ?? 'overall'}`;

    if (!batterCache.has(batterKey)) {
      batterCache.set(batterKey, aggregateBatterZoneMap(selectBatterZoneRows(batterZoneRows, batterId, row.opposing_pitcher_hand)));
    }
    if (!pitcherCache.has(pitcherKey)) {
      pitcherCache.set(pitcherKey, aggregatePitcherZoneMap(selectPitcherZoneRows(pitcherZoneRows, pitcherId, row.stand)));
    }

    return overlayZoneFitScore(batterCache.get(batterKey) ?? [], pitcherCache.get(pitcherKey) ?? []);
  });
}

function addHitterMatchupScore(rows, batterZoneRows, pitcherZoneRows) {
  if (!rows.length) {
    return [];
  }

  const zoneFitScores = buildZoneFitScores(rows, batterZoneRows, pitcherZoneRows);
  const swstrScores = normalizeSeries(rows.map((row) => row.swstr_pct), true);
  const barrelScores = normalizeSeries(rows.map((row) => row.barrel_bbe_pct));
  const sweetSpotScores = normalizeSeries(rows.map((row) => row.sweet_spot_pct));
  const barrelSupportScores = normalizeSeries(rows.map((row) => row.barrel_bbe_pct));
  const shapeScores = rows.map((row, index) => (
    (launchAngleBandScore(row.avg_launch_angle, 12, 22, 32) * 0.55)
    + (sweetSpotScores[index] * 0.35)
    + (barrelSupportScores[index] * 0.10)
  ));
  const pulledBarrelScales = normalizeSeries(rows.map((row) => row.pulled_barrel_pct));

  const matchupScored = rows.map((row, index) => {
    const baseScore = ((swstrScores[index] * 0.35) + (barrelScores[index] * 0.30) + (shapeScores[index] * 0.20) + (zoneFitScores[index] * 0.15)) * 100;
    const pulledBarrelBonus = Math.max(0, (pulledBarrelScales[index] - 0.5) / 0.5) * 0.08;
    const matchupScore = Math.max(0, Math.min(100, baseScore * (1 + pulledBarrelBonus)));
    return {
      ...row,
      zone_fit_score: zoneFitScores[index],
      matchup_score: matchupScore,
      _shape_score: shapeScores[index],
      _pulled_barrel_scale: pulledBarrelScales[index],
    };
  });

  const matchupNorm = normalizeSeries(matchupScored.map((row) => row.matchup_score));
  const barrelBipNorm = normalizeSeries(matchupScored.map((row) => row.barrel_bip_pct));
  const hhNorm = normalizeSeries(matchupScored.map((row) => row.hard_hit_pct));

  return sortHitters(matchupScored.map((row, index) => ({
    ...row,
    ceiling_score: Math.max(0, Math.min(100,
      (
        (matchupNorm[index] * 0.35)
        + ((row._pulled_barrel_scale ?? 0.5) * 0.30)
        + (barrelBipNorm[index] * 0.20)
        + ((row._shape_score ?? 0.5) * 0.10)
        + (hhNorm[index] * 0.05)
      ) * 100
    )),
  }))).map((row) => {
    const { _shape_score, _pulled_barrel_scale, ...clean } = row;
    return clean;
  });
}

function buildBestMatchupBoardRows(rows) {
  if (!rows?.length) {
    return [];
  }

  const rowsWithGame = rows.filter((row) => row.game_pk !== null && row.game_pk !== undefined && row.game_pk !== '');
  if (!rowsWithGame.length) {
    return sortHitters(rows);
  }

  const grouped = new Map();
  for (const row of rowsWithGame) {
    const key = String(row.game_pk);
    const existing = grouped.get(key) ?? [];
    existing.push(row);
    grouped.set(key, existing);
  }

  const boardRows = [];
  for (const gameRows of grouped.values()) {
    boardRows.push(...sortHitters(gameRows).slice(0, 3));
  }

  return sortHitters(boardRows);
}

function buildBestMatchupBoardRowsLiveApp(rows) {
  if (!rows?.length) {
    return [];
  }

  const rowsWithGame = rows.filter((row) => row.game_pk !== null && row.game_pk !== undefined && row.game_pk !== '');
  if (!rowsWithGame.length) {
    return sortHittersLiveApp(rows);
  }

  const grouped = new Map();
  for (const row of rowsWithGame) {
    const key = String(row.game_pk);
    const bucket = grouped.get(key) ?? [];
    bucket.push(row);
    grouped.set(key, bucket);
  }

  const boardRows = [];
  for (const gameRows of grouped.values()) {
    boardRows.push(...sortHittersLiveApp(gameRows).slice(0, 3));
  }

  return sortHittersLiveApp(boardRows);
}

function keepRowForDefaults(row) {
  const splitKey = String(row.split_key ?? DEFAULT_SPLIT_KEY);
  const recentWindow = String(row.recent_window ?? DEFAULT_RECENT_WINDOW);
  const weightedMode = String(row.weighted_mode ?? DEFAULT_WEIGHTED_MODE);
  return splitKey === DEFAULT_SPLIT_KEY
    && recentWindow === DEFAULT_RECENT_WINDOW
    && weightedMode === DEFAULT_WEIGHTED_MODE;
}

function mapSlateTeams(slateRows) {
  const lookup = new Map();
  for (const row of slateRows) {
    const awayTeam = String(row.away_team ?? '').trim();
    const homeTeam = String(row.home_team ?? '').trim();
    if (!awayTeam || !homeTeam) {
      continue;
    }

    const awayEntry = {
      gamePk: row.game_pk ?? null,
      opponentTeam: homeTeam,
      opposingPitcherId: row.home_probable_pitcher_id ?? null,
      opposingPitcherName: row.home_probable_pitcher ?? null,
      opposingPitcherHand: row.home_probable_hand ?? null,
    };
    const homeEntry = {
      gamePk: row.game_pk ?? null,
      opponentTeam: awayTeam,
      opposingPitcherId: row.away_probable_pitcher_id ?? null,
      opposingPitcherName: row.away_probable_pitcher ?? null,
      opposingPitcherHand: row.away_probable_hand ?? null,
    };

    lookup.set(awayTeam, awayEntry);
    lookup.set(homeTeam, homeEntry);

    const normalizedAwayTeam = normalizeTeam(awayTeam);
    const normalizedHomeTeam = normalizeTeam(homeTeam);
    if (normalizedAwayTeam) {
      lookup.set(normalizedAwayTeam, awayEntry);
    }
    if (normalizedHomeTeam) {
      lookup.set(normalizedHomeTeam, homeEntry);
    }
  }
  return lookup;
}

function buildExclusionSet(rows) {
  const values = new Set();
  for (const row of rows) {
    const playerId = row.player_id;
    if (playerId === null || playerId === undefined || playerId === '') {
      continue;
    }
    const shouldExclude = row.exclude_from_hitter_tables === true
      || String(row.exclude_from_hitter_tables ?? '').toLowerCase() === 'true'
      || String(row.exclude_from_hitter_tables ?? '') === '1';
    if (shouldExclude) {
      values.add(String(playerId));
    }
  }
  return values;
}

function buildRosterLookup(rows) {
  const lookup = new Map();
  for (const row of rows) {
    const playerId = String(row.player_id ?? '').trim();
    if (!playerId) {
      continue;
    }
    lookup.set(playerId, row.player_name ?? null);
  }
  return lookup;
}

function buildRosterIdsByTeam(rows) {
  const lookup = new Map();
  for (const row of rows) {
    const team = normalizeTeam(row.team);
    const playerId = String(row.player_id ?? '').trim();
    if (!team || !playerId) {
      continue;
    }
    const bucket = lookup.get(team) ?? new Set();
    bucket.add(playerId);
    lookup.set(team, bucket);
  }
  return lookup;
}

function buildRosterNameLookupByTeam(rows) {
  const lookup = new Map();
  for (const row of rows) {
    const team = normalizeTeam(row.team);
    const playerName = String(row.player_name ?? '').trim();
    const playerId = String(row.player_id ?? '').trim();
    if (!team || !playerName || !playerId) {
      continue;
    }
    let teamLookup = lookup.get(team);
    if (!teamLookup) {
      teamLookup = {
        exact: new Map(),
        normalized: new Map(),
      };
      lookup.set(team, teamLookup);
    }
    teamLookup.exact.set(playerName.toLowerCase(), playerId);
    teamLookup.normalized.set(normalizeName(playerName), playerId);
  }
  return lookup;
}

function resolveHostedPlayerName(row, rosterLookup, type = 'hitter') {
  const rawName = type === 'pitcher'
    ? String(row.pitcher_name ?? row.player_name ?? '').trim()
    : String(row.hitter_name ?? row.player_name ?? '').trim();
  const playerId = String(
    row.player_id
    ?? row.batter
    ?? row.pitcher_id
    ?? ''
  ).trim();

  const rosterName = rosterLookup.get(playerId) ?? null;
  const looksLikeId = /^\d+$/.test(rawName);
  if (rosterName && (!rawName || looksLikeId)) {
    return rosterName;
  }

  return rawName || rosterName || null;
}

function firstNonBlankValue(...values) {
  for (const value of values) {
    if (value === null || value === undefined) {
      continue;
    }
    if (typeof value === 'string') {
      const trimmed = value.trim();
      if (trimmed) {
        return trimmed;
      }
      continue;
    }
    if (value !== '') {
      return value;
    }
  }
  return null;
}

function withDefaults(row, slateLookup, options = {}) {
  const team = String(row.team ?? '').trim();
  const slate = slateLookup.get(team) ?? slateLookup.get(normalizeTeam(team)) ?? {};
  const rosterLookup = options.rosterLookup ?? new Map();
  const playerType = options.playerType ?? 'hitter';
  return {
    ...row,
    team,
    hitter_name: playerType === 'hitter'
      ? resolveHostedPlayerName(row, rosterLookup, 'hitter')
      : row.hitter_name,
    pitcher_name: playerType === 'pitcher'
      ? resolveHostedPlayerName(row, rosterLookup, 'pitcher')
      : row.pitcher_name,
    game_pk: firstNonBlankValue(row.game_pk, slate.gamePk),
    opponent_team: firstNonBlankValue(row.opponent_team, slate.opponentTeam),
    opposing_pitcher_id: firstNonBlankValue(row.opposing_pitcher_id, slate.opposingPitcherId),
    opposing_pitcher_name: firstNonBlankValue(row.opposing_pitcher_name, slate.opposingPitcherName),
    opposing_pitcher_hand: firstNonBlankValue(row.opposing_pitcher_hand, slate.opposingPitcherHand),
  };
}

function decodeHtmlEntities(value) {
  return String(value ?? '')
    .replace(/&nbsp;/gi, ' ')
    .replace(/&amp;/gi, '&')
    .replace(/&quot;/gi, '"')
    .replace(/&#39;/gi, '\'')
    .replace(/&apos;/gi, '\'')
    .replace(/&lt;/gi, '<')
    .replace(/&gt;/gi, '>');
}

function extractRotowireTokens(html) {
  return decodeHtmlEntities(
    String(html ?? '')
      .replace(/<script[\s\S]*?<\/script>/gi, ' ')
      .replace(/<style[\s\S]*?<\/style>/gi, ' ')
      .replace(/<[^>]+>/g, '\n')
  )
    .split(/\r?\n/)
    .map((token) => token.trim())
    .filter(Boolean);
}

function nextNonPriceToken(tokens, startIndex) {
  let index = startIndex;
  while (index < tokens.length) {
    if (!ROTOWIRE_PRICE_RE.test(tokens[index])) {
      return { token: tokens[index], index };
    }
    index += 1;
  }
  return { token: null, index };
}

function parseRotowireLineupSide(tokens, startIndex) {
  const rawStatus = tokens[startIndex];
  const status = ROTOWIRE_LINEUP_STATUS.get(rawStatus) ?? 'unknown';
  let index = startIndex + 1;
  const players = [];

  if (status === 'unknown') {
    while (index < tokens.length) {
      const token = tokens[index];
      if (
        ROTOWIRE_LINEUP_STATUS.has(token)
        || ROTOWIRE_STOP_TOKENS.has(token)
        || token.startsWith('Umpire:')
        || ROTOWIRE_TIME_RE.test(token)
      ) {
        break;
      }
      index += 1;
    }
    return { lineup: { status, players }, nextIndex: index };
  }

  let slot = 1;
  while (index < tokens.length) {
    const token = tokens[index];
    if (
      ROTOWIRE_LINEUP_STATUS.has(token)
      || ROTOWIRE_STOP_TOKENS.has(token)
      || token.startsWith('Umpire:')
      || ROTOWIRE_TIME_RE.test(token)
    ) {
      break;
    }
    if (ROTOWIRE_POSITION_TOKENS.has(token)) {
      const next = nextNonPriceToken(tokens, index + 1);
      if (
        next.token
        && !ROTOWIRE_POSITION_TOKENS.has(next.token)
        && !ROTOWIRE_STOP_TOKENS.has(next.token)
        && !ROTOWIRE_LINEUP_STATUS.has(next.token)
      ) {
        players.push({
          slot,
          player_name: next.token,
          position: token,
        });
        slot += 1;
        index = next.index + 1;
        continue;
      }
    }
    index += 1;
  }

  return { lineup: { status, players }, nextIndex: index };
}

function parseRotowireLineups(html, validTeams) {
  const tokens = extractRotowireTokens(html);
  const validTeamSet = new Set(validTeams.map((team) => normalizeTeam(team)));
  const lineups = {};
  let index = 0;

  while (index < tokens.length) {
    if (!ROTOWIRE_TIME_RE.test(tokens[index])) {
      index += 1;
      continue;
    }

    const teamTokens = [];
    let scanIndex = index + 1;
    while (scanIndex < tokens.length && scanIndex < index + 25 && teamTokens.length < 2) {
      const token = normalizeTeam(tokens[scanIndex]);
      if (validTeamSet.has(token) && !teamTokens.includes(token)) {
        teamTokens.push(token);
      }
      scanIndex += 1;
    }

    if (teamTokens.length !== 2) {
      index += 1;
      continue;
    }

    const [awayTeam, homeTeam] = teamTokens;
    let firstStatusIndex = null;
    for (let candidate = scanIndex; candidate < Math.min(tokens.length, scanIndex + 80); candidate += 1) {
      if (ROTOWIRE_LINEUP_STATUS.has(tokens[candidate])) {
        firstStatusIndex = candidate;
        break;
      }
    }
    if (firstStatusIndex === null) {
      index += 1;
      continue;
    }

    const awayResult = parseRotowireLineupSide(tokens, firstStatusIndex);
    let secondStatusIndex = null;
    for (let candidate = awayResult.nextIndex; candidate < Math.min(tokens.length, awayResult.nextIndex + 80); candidate += 1) {
      if (ROTOWIRE_LINEUP_STATUS.has(tokens[candidate])) {
        secondStatusIndex = candidate;
        break;
      }
    }
    if (secondStatusIndex === null) {
      index = awayResult.nextIndex;
      continue;
    }

    const homeResult = parseRotowireLineupSide(tokens, secondStatusIndex);
    lineups[awayTeam] = awayResult.lineup;
    lineups[homeTeam] = homeResult.lineup;
    index = homeResult.nextIndex;
  }

  return lineups;
}

function resolveRotowireLineups(lineups, rosterRows) {
  if (!lineups || !Object.keys(lineups).length || !rosterRows?.length) {
    return lineups ?? {};
  }

  const rosterLookupByTeam = buildRosterNameLookupByTeam(rosterRows);
  const resolved = {};

  for (const [team, payload] of Object.entries(lineups)) {
    const teamLookup = rosterLookupByTeam.get(normalizeTeam(team)) ?? { exact: new Map(), normalized: new Map() };
    const players = (payload?.players ?? []).map((player) => {
      const playerName = String(player.player_name ?? '');
      const exactPlayerId = teamLookup.exact.get(playerName.toLowerCase());
      const normalizedPlayerId = teamLookup.normalized.get(normalizeName(playerName));
      return {
        ...player,
        player_id: exactPlayerId ?? normalizedPlayerId ?? null,
      };
    });
    resolved[normalizeTeam(team)] = {
      status: payload?.status ?? 'unknown',
      players,
    };
  }

  return resolved;
}

function applyProjectedLineup(rows, team, rotowireLineups) {
  if (!rows.length || !rotowireLineups) {
    return rows;
  }

  const payload = rotowireLineups[normalizeTeam(team)];
  if (!payload || !['confirmed', 'expected'].includes(String(payload.status ?? ''))) {
    return rows;
  }

  const slotByPlayerId = new Map();
  for (const player of payload.players ?? []) {
    const playerId = String(player.player_id ?? '').trim();
    if (!playerId) {
      continue;
    }
    slotByPlayerId.set(playerId, Number(player.slot ?? null));
  }

  if (slotByPlayerId.size < 7) {
    return rows;
  }

  return rows
    .filter((row) => slotByPlayerId.has(String(row.batter ?? row.player_id ?? '').trim()))
    .map((row) => ({
      ...row,
      projected_lineup_slot: slotByPlayerId.get(String(row.batter ?? row.player_id ?? '').trim()) ?? null,
    }))
    .sort((left, right) => {
      const leftSlot = numberOrNull(left.projected_lineup_slot);
      const rightSlot = numberOrNull(right.projected_lineup_slot);
      if (leftSlot === null && rightSlot === null) {
        return 0;
      }
      if (leftSlot === null) {
        return 1;
      }
      if (rightSlot === null) {
        return -1;
      }
      return leftSlot - rightSlot;
    });
}

function pickMetrics(row, metricKeys) {
  return metricKeys
    .map(([label, key]) => ({ label, key, value: numberOrNull(row[key]) }))
    .filter((item) => item.value !== null);
}

function buildRollingSummary(rows, metricKey, valueKey) {
  return rows
    .map((row) => ({
      label: row[metricKey] ?? (row.window_size ? `Rolling ${row.window_size}` : null),
      value: numberOrNull(row[valueKey]),
    }))
    .filter((row) => row.label && row.value !== null)
    .slice(0, 3);
}

function buildZoneSummary(rows, nameKey, valueKeys) {
  return [...rows]
    .map((row) => {
      const label = row.zone_label ?? row.zone ?? null;
      const metric = valueKeys
        .map((key) => ({ key, value: numberOrNull(row[key]) }))
        .find((item) => item.value !== null);
      return {
        label,
        metricKey: metric?.key ?? null,
        value: metric?.value ?? null,
        sample: numberOrNull(row.sample_size ?? row.bip),
        playerName: row[nameKey] ?? null,
      };
    })
    .filter((row) => row.label && row.value !== null)
    .sort((left, right) => compareNullableDescending(left.value, right.value))
    .slice(0, 3);
}

function buildArsenalSummary(rows) {
  return [...rows]
    .map((row) => ({
      pitchType: row.pitch_type ?? null,
      usagePct: numberOrNull(row.usage_pct),
      velocity: numberOrNull(row.avg_velocity),
      whiffRate: numberOrNull(row.whiff_rate),
      calledStrikeRate: numberOrNull(row.called_strike_rate),
    }))
    .filter((row) => row.pitchType && row.usagePct !== null)
    .sort((left, right) => compareNullableDescending(left.usagePct, right.usagePct))
    .slice(0, 4);
}

function buildCountUsageSummary(rows) {
  return [...rows]
    .map((row) => ({
      countBucket: row.count_bucket ?? null,
      batterSide: row.batter_side_key ?? 'all',
      pitchType: row.pitch_type ?? null,
      usagePct: numberOrNull(row.usage_pct),
    }))
    .filter((row) => row.countBucket && row.pitchType && row.usagePct !== null)
    .sort((left, right) => compareNullableDescending(left.usagePct, right.usagePct))
    .slice(0, 4);
}

function uniqueRowsByKey(rows, keyBuilder) {
  const lookup = new Map();
  for (const row of rows ?? []) {
    const key = keyBuilder(row);
    if (key && !lookup.has(key)) {
      lookup.set(key, row);
    }
  }
  return [...lookup.values()];
}

function formatDateLabel(value) {
  return String(value ?? '').slice(5);
}

function normalizeToRadarScore(value, { min = 0, max = 100, scalePercent = false, inverse = false } = {}) {
  const numeric = numberOrNull(value);
  if (numeric === null) {
    return 0;
  }
  const raw = scalePercent && Math.abs(numeric) <= 1 ? numeric * 100 : numeric;
  const normalized = max === min ? 0 : ((raw - min) / (max - min)) * 100;
  const clamped = Math.max(0, Math.min(100, normalized));
  return inverse ? 100 - clamped : clamped;
}

function averageOrNull(values) {
  const valid = values.map(numberOrNull).filter((value) => value !== null);
  if (!valid.length) {
    return null;
  }
  return valid.reduce((sum, value) => sum + value, 0) / valid.length;
}

function impliedProbabilityFromAmerican(odds) {
  const numeric = numberOrNull(odds);
  if (numeric === null || numeric === 0) {
    return null;
  }
  if (numeric > 0) {
    return 100 / (numeric + 100);
  }
  return Math.abs(numeric) / (Math.abs(numeric) + 100);
}

function opponentLabelFromPitcherRow(row) {
  return row?.opponent_team ?? null;
}

function buildZoneOverlayInsights(cells) {
  if (!cells?.length) {
    return {
      bestOverlay: 'No qualifying zone overlap',
      shapeSummary: 'Zone inputs were not available.',
    };
  }

  const sorted = [...cells].sort((left, right) => compareNullableDescending(left.overlayValue, right.overlayValue));
  const best = sorted[0] ?? null;
  const topTwoAverage = averageOrNull(sorted.slice(0, 2).map((cell) => cell.overlayValue));
  return {
    bestOverlay: best ? `Zone ${best.zone} at ${((numberOrNull(best.overlayValue) ?? 0) * 100).toFixed(0)}% fit` : 'No qualifying zone overlap',
    shapeSummary: topTwoAverage !== null
      ? `Top-zone overlap averages ${(topTwoAverage * 100).toFixed(0)}% across the strongest damage pockets.`
      : 'Zone inputs were not available.',
  };
}

function formatMetricValue(value, type = 'number') {
  const numeric = numberOrNull(value);
  if (numeric === null) {
    return 'N/A';
  }
  if (type === 'pct') {
    const scaled = Math.abs(numeric) <= 1 ? numeric * 100 : numeric;
    return `${scaled.toFixed(1)}%`;
  }
  if (Math.abs(numeric) < 1) {
    return numeric.toFixed(3);
  }
  return numeric.toFixed(1);
}

function getPitcherWindowPointLimit(window) {
  switch (String(window ?? 'last_5')) {
    case 'last_10':
      return 10;
    case 'season_2026':
      return 15;
    case 'career':
      return 6;
    default:
      return 5;
  }
}

function windowLabel(window) {
  switch (String(window ?? 'last_5')) {
    case 'last_10':
      return 'last 10';
    case 'season_2026':
      return 'season 2026';
    case 'career':
      return 'career';
    default:
      return 'last 5';
  }
}

function splitLabel(split) {
  switch (String(split ?? 'overall')) {
    case 'vs_lhb':
      return 'vs LHB';
    case 'vs_rhb':
      return 'vs RHB';
    default:
      return 'overall';
  }
}

function countBucketLabel(bucket) {
  switch (String(bucket ?? 'all')) {
    case 'first_pitch':
      return 'first pitch';
    case 'ahead':
      return 'ahead';
    case 'behind':
      return 'behind';
    case 'putaway':
      return 'putaway';
    case 'two_strike':
      return 'two strike';
    default:
      return 'all counts';
  }
}

function labelForCompareTarget(compareTo) {
  switch (String(compareTo ?? 'career')) {
    case 'season_2026':
      return 'Season 2026';
    case 'prior_5':
      return 'Prior 5';
    case 'prior_10':
      return 'Prior 10';
    default:
      return 'Career';
  }
}

function pitcherTrendTitle(view) {
  switch (view) {
    case 'velo':
      return 'Velocity Trend';
    case 'spin':
      return 'Spin Trend';
    case 'release':
      return 'Release Trend';
    default:
      return 'Pitcher Trend';
  }
}

function pitcherTrendPrimaryLabel(view) {
  switch (view) {
    case 'velo':
      return 'Release Speed';
    case 'spin':
      return 'Spin Rate';
    case 'release':
      return 'Extension';
    default:
      return 'Pitcher Trend';
  }
}

function pitcherTrendOverlayLabel(view, index) {
  if (view === 'velo') {
    return index === 0 ? 'Effective Speed' : 'Move Z';
  }
  if (view === 'spin') {
    return index === 0 ? 'Spin Efficiency Proxy' : 'Spin Axis';
  }
  return index === 0 ? 'Release X' : 'Release Z';
}

function formatTrendOverlayValue(view, index, value) {
  const numeric = numberOrNull(value) ?? 0;
  if (view === 'spin' && index === 0) {
    return numeric * 100;
  }
  return numeric;
}

function pitcherTrendRead(view, points, pitchType) {
  const latest = points[points.length - 1];
  if (view === 'velo') {
    return `${pitchType ? `${pitchType} ` : ''}velocity most recently checked in at ${formatMetricValue(latest?.value)}.`;
  }
  if (view === 'spin') {
    return `${pitchType ? `${pitchType} ` : ''}spin most recently checked in at ${formatMetricValue(latest?.value)}.`;
  }
  return `${pitchType ? `${pitchType} ` : ''}release metrics track extension with position overlays across the selected window.`;
}

function pitcherArsenalTitle(view) {
  switch (view) {
    case 'shape':
      return 'Pitch Shape Card';
    case 'movement':
      return 'Movement Cluster';
    case 'usage':
      return 'Pitch Mix Usage';
    case 'evolution':
      return 'Arsenal Evolution';
    case 'outcomes':
      return 'Pitch Outcomes';
    case 'platoon':
      return 'Platoon Arsenal';
    default:
      return 'Pitcher Arsenal';
  }
}

function pitcherLocationTitle(view) {
  switch (view) {
    case 'bypitch':
      return 'Location by Pitch';
    case 'twostrike':
      return 'Two Strike Location';
    case 'chase':
      return 'Chase Attack Map';
    case 'damage':
      return 'Damage Allowed Map';
    case 'miss':
      return 'Miss Location Map';
    default:
      return 'Location Heatmap';
  }
}

function pitcherLocationRead(view) {
  switch (view) {
    case 'bypitch':
      return 'Each dot is a pitch. Colors separate pitch types so you can see where each offering lives.';
    case 'damage':
      return 'Hotter cells flag where contact quality has done the most damage.';
    case 'miss':
      return 'Hotter cells flag where swings and misses show up most often.';
    case 'chase':
      return 'Hotter cells flag where the pitcher attacks for chase or edge pressure.';
    default:
      return 'Hotter cells show where the pitcher lives most often in the selected view.';
  }
}

function pitcherLocationMetricConfig(view) {
  switch (view) {
    case 'damage':
      return {
        headline: 'Color = contact damage',
        primaryLabel: 'xwOBA',
        secondaryLabel: 'Usage',
        suffix: '',
      };
    case 'miss':
      return {
        headline: 'Color = miss rate',
        primaryLabel: 'Miss',
        secondaryLabel: 'Usage',
        suffix: '%',
      };
    case 'chase':
      return {
        headline: 'Color = chase pressure',
        primaryLabel: 'Chase',
        secondaryLabel: 'Usage',
        suffix: '%',
      };
    default:
      return {
        headline: 'Color = usage rate',
        primaryLabel: 'Usage',
        secondaryLabel: 'Miss',
        suffix: '%',
      };
  }
}

function pitcherApproachTitle(view) {
  switch (view) {
    case 'count_whiff':
      return 'Whiff by Count';
    case 'ahead_behind':
      return 'Ahead vs Behind';
    case 'first_pitch':
      return 'First Pitch Usage';
    case 'putaway':
      return 'Putaway Distribution';
    default:
      return 'Pitch Usage by Count';
  }
}

function pitcherCompareTitle(view) {
  switch (view) {
    case 'recent_vs_baseline':
      return 'Recent vs Baseline';
    case 'year_over_year':
      return 'Year Over Year';
    default:
      return 'Current vs Career';
  }
}

function buildPitchTypeSqlFilter(pitchTypeColumn, pitchNameColumn, parameterIndex) {
  const pitchTypeExpr = pitchTypeColumn === 'NULL'
    ? 'NULL'
    : `UPPER(COALESCE(NULLIF(${pitchTypeColumn}, ''), NULLIF(${pitchNameColumn}, '')))`;
  const pitchNameExpr = `UPPER(COALESCE(NULLIF(${pitchNameColumn}, ''), NULLIF(${pitchTypeColumn}, '')))`;
  return `AND ($${parameterIndex}::text IS NULL OR ${pitchTypeExpr} = UPPER($${parameterIndex}) OR ${pitchNameExpr} = UPPER($${parameterIndex}))`;
}

function buildSplitSqlFilter(columnExpression, parameterIndex) {
  return `AND ($${parameterIndex}::text IS NULL OR $${parameterIndex}::text = 'overall' OR LOWER(${columnExpression}) = CASE WHEN $${parameterIndex}::text = 'vs_lhb' THEN 'l' WHEN $${parameterIndex}::text = 'vs_rhb' THEN 'r' ELSE LOWER(${columnExpression}) END)`;
}

function buildPitcherIdentitySql(nameColumn, idColumn, nameParameterIndex, idParameterIndex) {
  return `(($${idParameterIndex}::text IS NOT NULL AND ${idColumn}::text = $${idParameterIndex}) OR ($${idParameterIndex}::text IS NULL AND LOWER(${nameColumn}) = LOWER($${nameParameterIndex})))`;
}

function deriveCountBucket(row) {
  const balls = numberOrNull(row.balls) ?? 0;
  const strikes = numberOrNull(row.strikes) ?? 0;
  if (balls === 0 && strikes === 0) {
    return 'first_pitch';
  }
  if (strikes >= 2) {
    return 'putaway';
  }
  if (strikes > balls) {
    return 'ahead';
  }
  if (balls > strikes) {
    return 'behind';
  }
  return 'even';
}

function matchesCountBucket(row, countBucket) {
  if (!countBucket || countBucket === 'all') {
    return true;
  }
  if (countBucket === 'two_strike') {
    return (numberOrNull(row.strikes) ?? 0) >= 2;
  }
  return deriveCountBucket(row) === countBucket;
}

function bucketPlateCell(plateX, plateZ) {
  const x = numberOrNull(plateX) ?? 0;
  const z = numberOrNull(plateZ) ?? 2.8;
  const col = x < -0.28 ? 0 : x > 0.28 ? 2 : 1;
  const row = z > 3.3 ? 0 : z < 2.5 ? 2 : 1;
  return row * 3 + col + 1;
}

function isWhiffDescription(description) {
  const normalized = normalizeText(description);
  return ['swinging strike', 'swinging strike blocked', 'foul tip'].some((token) => normalized.includes(token));
}

function isChaseLikeLocation(plateX, plateZ) {
  const x = Math.abs(numberOrNull(plateX) ?? 0);
  const z = numberOrNull(plateZ) ?? 2.8;
  return x > 0.85 || z < 1.7 || z > 3.9;
}

function buildPitcherLocationCells(rows, view) {
  const buckets = new Map();
  const totalRows = rows.length || 1;
  for (const row of rows) {
    const zone = bucketPlateCell(row.plate_x, row.plate_z);
    const entry = buckets.get(zone) ?? { zone, pitches: 0, whiffs: 0, xwobaTotal: 0, xwobaCount: 0, chasePressure: 0 };
    entry.pitches += 1;
    if (isWhiffDescription(row.description)) {
      entry.whiffs += 1;
    }
    const xwoba = numberOrNull(row.estimated_woba_using_speedangle);
    if (xwoba !== null) {
      entry.xwobaTotal += xwoba;
      entry.xwobaCount += 1;
    }
    if (isChaseLikeLocation(row.plate_x, row.plate_z)) {
      entry.chasePressure += 1;
    }
    buckets.set(zone, entry);
  }

  return [1, 2, 3, 4, 5, 6, 7, 8, 9].map((zone) => {
    const entry = buckets.get(zone) ?? { zone, pitches: 0, whiffs: 0, xwobaTotal: 0, xwobaCount: 0, chasePressure: 0 };
    const usage = entry.pitches / totalRows;
    const whiffRate = entry.pitches > 0 ? entry.whiffs / entry.pitches : 0;
    const damage = entry.xwobaCount > 0 ? entry.xwobaTotal / entry.xwobaCount : 0;
    const chaseRate = entry.pitches > 0 ? entry.chasePressure / entry.pitches : 0;
    let overlayValue = usage * 100;
    if (view === 'miss') {
      overlayValue = whiffRate * 100;
    } else if (view === 'damage') {
      overlayValue = damage * 100;
    } else if (view === 'chase') {
      overlayValue = chaseRate * 100;
    }
    return {
      zone,
      batterValue: whiffRate,
      pitcherValue: usage,
      overlayValue,
    };
  });
}

function buildPitcherLocationSummary(cells, view) {
  const ranked = [...cells].sort((left, right) => compareNullableDescending(left.overlayValue, right.overlayValue)).slice(0, 3);
  const zones = ranked.map((cell) => `Z${cell.zone}`).join(', ');
  if (!zones) {
    return 'No location summary available.';
  }
  if (view === 'damage') {
    return `Most dangerous contact pockets sit in ${zones}.`;
  }
  if (view === 'miss') {
    return `Best miss pockets sit in ${zones}.`;
  }
  return `Most frequent attack lanes sit in ${zones}.`;
}

function locationMetricSuffix(view) {
  if (view === 'damage') {
    return 'xwOBA';
  }
  return '%';
}

function buildPitchLocationPlot(rows) {
  const counts = new Map();
  const points = rows
    .filter((row) => numberOrNull(row.plate_x) !== null && numberOrNull(row.plate_z) !== null)
    .slice(0, 600)
    .map((row) => {
      const pitchName = firstNonBlankValue(row.pitch_name, row.pitch_type, 'Unknown');
      counts.set(pitchName, (counts.get(pitchName) ?? 0) + 1);
      return {
        x: numberOrNull(row.plate_x) ?? 0,
        y: numberOrNull(row.plate_z) ?? 0,
        pitchName,
      };
    });

  const breakdown = [...counts.entries()]
    .sort((left, right) => right[1] - left[1])
    .slice(0, 5)
    .map(([pitchName, count]) => ({
      pitchName,
      count,
      pct: points.length ? (count / points.length) * 100 : 0,
    }));

  return {
    points,
    breakdown,
  };
}

function buildPitcherApproachDatasets(rows, view) {
  const buckets = view === 'ahead_behind'
    ? ['ahead', 'even', 'behind']
    : view === 'first_pitch'
      ? ['first_pitch']
      : view === 'putaway'
        ? ['putaway']
        : ['first_pitch', 'ahead', 'even', 'behind', 'putaway'];

  const pitchUsage = new Map();
  const pitchWhiffs = new Map();
  const topCounts = new Map();

  for (const row of rows) {
    const bucket = deriveCountBucket(row);
    const pitch = firstNonBlankValue(row.pitch_name, row.pitch_type, 'Unknown');
    const key = `${pitch}|${bucket}`;
    pitchUsage.set(key, (pitchUsage.get(key) ?? 0) + 1);
    if (isWhiffDescription(row.description)) {
      pitchWhiffs.set(key, (pitchWhiffs.get(key) ?? 0) + 1);
    }
    topCounts.set(pitch, (topCounts.get(pitch) ?? 0) + 1);
  }

  const topPitches = [...topCounts.entries()]
    .sort((left, right) => right[1] - left[1])
    .slice(0, 4)
    .map(([pitch]) => pitch);

  const datasets = topPitches.map((pitch) => ({
    label: pitch,
    values: buckets.map((bucket) => {
      const key = `${pitch}|${bucket}`;
      const usage = pitchUsage.get(key) ?? 0;
      if (view === 'count_whiff') {
        return usage > 0 ? ((pitchWhiffs.get(key) ?? 0) / usage) * 100 : 0;
      }
      const bucketTotal = [...topPitches].reduce((sum, name) => sum + (pitchUsage.get(`${name}|${bucket}`) ?? 0), 0);
      return bucketTotal > 0 ? (usage / bucketTotal) * 100 : 0;
    }),
  })).filter((dataset) => dataset.values.some((value) => value > 0));

  return {
    labels: buckets.map((bucket) => countBucketLabel(bucket)),
    datasets,
    read: view === 'count_whiff'
      ? 'Higher bars show where each pitch shape generates the most swing-and-miss pressure.'
      : 'Higher bars show which pitch shapes own the selected count states most often.',
  };
}

function findBestPlayerMatch(rows, key, playerName) {
  const normalizedNeedle = normalizeText(playerName);
  const exact = rows.find((row) => normalizeText(row[key]) === normalizedNeedle);
  if (exact) {
    return exact;
  }
  return rows.find((row) => normalizeText(row[key]).includes(normalizedNeedle)) ?? null;
}

async function sleep(ms) {
  await new Promise((resolve) => setTimeout(resolve, ms));
}

export class HostedArtifactSource {
  constructor(config = {}, options = {}) {
    this.baseUrl = String(config.baseUrl ?? '').trim().replace(/\/$/, '');
    this.cacheTtlMs = Number(config.cacheTtlMs ?? 5 * 60 * 1000);
    this.fallbackDays = Number(config.fallbackDays ?? DEFAULT_FALLBACK_DAYS);
    this.logger = options.logger ?? console;
    this.readParquetImpl = options.readParquetImpl ?? readParquetFromUrl;
    this.fetchTextImpl = options.fetchTextImpl ?? defaultFetchText;
    this.rotowireUrl = String(config.rotowireUrl ?? DEFAULT_ROTOWIRE_URL);
    this.cache = new Map();
  }

  async getCachedValue(cacheKey, producer) {
    const now = Date.now();
    const cached = this.cache.get(cacheKey);
    if (cached && cached.expiresAt > now) {
      return cached.rows ?? cached.value;
    }

    const pending = Promise.resolve().then(producer);
    this.cache.set(cacheKey, {
      expiresAt: now + this.cacheTtlMs,
      value: pending,
    });

    try {
      const rows = await pending;
      this.cache.set(cacheKey, {
        expiresAt: now + this.cacheTtlMs,
        rows,
      });
      return rows;
    } catch (error) {
      const current = this.cache.get(cacheKey);
      if (current?.value === pending) {
        this.cache.delete(cacheKey);
      }
      throw error;
    }
  }

  isConfigured() {
    return Boolean(this.baseUrl);
  }

  async getLatestAvailableDate(targetDate) {
    if (!this.isConfigured()) {
      return null;
    }

    const safeTargetDate = parseDateOrToday(targetDate);
    return this.getCachedValue(`latest-date|${safeTargetDate}`, async () => {
      for (let offset = 0; offset <= this.fallbackDays; offset += 1) {
        const candidate = addDays(safeTargetDate, -offset);
        try {
          await this.readDailyFile(candidate, 'slate.parquet', SLATE_COLUMNS);
          return candidate;
        } catch (error) {
          this.logger?.debug?.('Hosted slate check failed', { candidate, error: error.message });
        }
      }

      return null;
    });
  }

  async readDailyFile(targetDate, filename, columns) {
    const safeDate = parseDateOrToday(targetDate);
    const url = `${this.baseUrl}/daily/${safeDate}/${filename}`;
    return this.readCached(url, columns);
  }

  async readReusableFile(filename, columns) {
    const url = `${this.baseUrl}/reusable/${filename}`;
    return this.readCached(url, columns);
  }

  async readCached(url, columns) {
    const cacheKey = `${url}|${(columns ?? []).join(',')}`;
    const now = Date.now();
    const cached = this.cache.get(cacheKey);
    if (cached && cached.expiresAt > now) {
      return cached.rows;
    }

    const rows = await this.readParquetImpl(url, columns);
    this.cache.set(cacheKey, {
      expiresAt: now + this.cacheTtlMs,
      rows,
    });
    return rows;
  }

  async fetchRotowireLineups(targetDate, validTeams, rosterRows = []) {
    if (!this.fetchTextImpl || !validTeams?.length) {
      return {};
    }

    const cacheKey = `rotowire|${parseDateOrToday(targetDate)}|${[...validTeams].sort().join(',')}`;
    try {
      return await this.getCachedValue(cacheKey, async () => {
        const html = await this.fetchTextImpl(this.rotowireUrl, parseDateOrToday(targetDate));
        const parsed = parseRotowireLineups(html, validTeams);
        return resolveRotowireLineups(parsed, rosterRows);
      });
    } catch (error) {
      this.logger?.debug?.('Rotowire lineup fetch failed', {
        targetDate: parseDateOrToday(targetDate),
        error: error.message,
      });
      return {};
    }
  }

  getHostedHitterParityCacheKey(options = {}) {
    return [
      'hosted-hitter-parity',
      parseDateOrToday(options.date),
      String(options.split ?? DEFAULT_SPLIT_KEY),
      String(options.recentWindow ?? DEFAULT_RECENT_WINDOW),
      String(options.weightedMode ?? DEFAULT_WEIGHTED_MODE),
      options.likelyOnly === true ? 'likely' : 'all',
    ].join('|');
  }

  applyHostedTeamFilter(result, team) {
    return {
      ...result,
      rows: result.rows.filter((row) => rowMatchesTeamFilter(row.team, team)),
      boardRows: result.boardRows.filter((row) => rowMatchesTeamFilter(row.team, team)),
    };
  }

  async buildHostedHitterParityBase(options = {}) {
    return this.getCachedValue(this.getHostedHitterParityCacheKey(options), async () => {
      const targetDate = parseDateOrToday(options.date);
      const resolvedDate = await this.getLatestAvailableDate(targetDate);
      if (!resolvedDate) {
        throw new Error('No hosted matchup slate was available in the fallback window.');
      }

      const [slateRows, hitterRows, pitcherRows, exclusionRows, rosterRows, batterZoneRows, pitcherZoneRows] = await Promise.all([
        this.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
        this.readDailyFile(resolvedDate, 'daily_hitter_metrics.parquet', HITTER_COLUMNS),
        this.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
        this.readDailyFile(resolvedDate, 'hitter_pitcher_exclusions.parquet', EXCLUSION_COLUMNS).catch(() => []),
        this.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
        this.readDailyFile(resolvedDate, 'daily_batter_zone_profiles.parquet', BATTER_ZONE_COLUMNS).catch(() => []),
        this.readDailyFile(resolvedDate, 'daily_pitcher_zone_profiles.parquet', PITCHER_ZONE_COLUMNS).catch(() => []),
      ]);

      const split = String(options.split ?? DEFAULT_SPLIT_KEY);
      const recentWindow = String(options.recentWindow ?? DEFAULT_RECENT_WINDOW);
      const weightedMode = String(options.weightedMode ?? DEFAULT_WEIGHTED_MODE);
      const likelyOnly = options.likelyOnly === true;
      const slateLookup = mapSlateTeams(slateRows);
      const exclusionSet = buildExclusionSet(exclusionRows);
      const rosterLookup = buildRosterLookup(rosterRows);
      const rosterIdsByTeam = buildRosterIdsByTeam(rosterRows);
      const validTeams = [...new Set(slateRows.flatMap((row) => [normalizeTeam(row.away_team), normalizeTeam(row.home_team)]).filter(Boolean))];
      const rotowireLineups = await this.fetchRotowireLineups(resolvedDate, validTeams, rosterRows);
      const filteredPitchers = pitcherRows.filter((row) =>
        String(row.split_key ?? DEFAULT_SPLIT_KEY) === split
        && String(row.recent_window ?? DEFAULT_RECENT_WINDOW) === recentWindow
        && String(row.weighted_mode ?? DEFAULT_WEIGHTED_MODE) === weightedMode
      );
      const pitchersById = new Map();
      for (const row of filteredPitchers) {
        pitchersById.set(String(row.pitcher_id ?? row.player_id ?? ''), row);
      }

      const allRows = [];
      const boardRows = [];
      for (const slateRow of slateRows) {
        const awayTeam = normalizeTeam(slateRow.away_team);
        const homeTeam = normalizeTeam(slateRow.home_team);
        const awayPitcher = pitchersById.get(String(slateRow.away_probable_pitcher_id ?? '')) ?? null;
        const homePitcher = pitchersById.get(String(slateRow.home_probable_pitcher_id ?? '')) ?? null;
        const awayHand = String(homePitcher?.p_throws ?? slateRow.home_probable_hand ?? '').trim().toUpperCase() || null;
        const homeHand = String(awayPitcher?.p_throws ?? slateRow.away_probable_hand ?? '').trim().toUpperCase() || null;

        const awayRows = this.prepareHostedTeamHitters({
          team: awayTeam,
          slateLookup,
          rosterLookup,
          rosterIdsByTeam,
          exclusionSet,
          rotowireLineups,
          hitterRows,
          opposingPitcherHand: awayHand,
          split,
          recentWindow,
          weightedMode,
          likelyOnly,
        });
        const homeRows = this.prepareHostedTeamHitters({
          team: homeTeam,
          slateLookup,
          rosterLookup,
          rosterIdsByTeam,
          exclusionSet,
          rotowireLineups,
          hitterRows,
          opposingPitcherHand: homeHand,
          split,
          recentWindow,
          weightedMode,
          likelyOnly,
        });

        const combinedGameRows = sortHittersLiveApp([
          ...addHitterMatchupScore(awayRows, batterZoneRows, pitcherZoneRows),
          ...addHitterMatchupScore(homeRows, batterZoneRows, pitcherZoneRows),
        ]);
        allRows.push(...combinedGameRows);
        boardRows.push(...combinedGameRows.slice(0, 3));
      }

      return {
        source: 'hosted',
        resolvedDate,
        parityRanked: true,
        rows: sortHittersLiveApp(allRows),
        boardRows: sortHittersLiveApp(boardRows),
      };
    });
  }

  async buildHostedHitterParityResult(options = {}) {
    const result = await this.buildHostedHitterParityBase(options);
    return this.applyHostedTeamFilter(result, options.team);
  }

  prepareHostedTeamHitters({
    team,
    slateLookup,
    rosterLookup,
    rosterIdsByTeam,
    exclusionSet,
    rotowireLineups,
    hitterRows,
    opposingPitcherHand,
    split,
    recentWindow,
    weightedMode,
    likelyOnly,
  }) {
    const normalizedTeam = normalizeTeam(team);
    const effectiveSplit = split === DEFAULT_SPLIT_KEY && opposingPitcherHand === 'R'
      ? 'vs_rhp'
      : split === DEFAULT_SPLIT_KEY && opposingPitcherHand === 'L'
        ? 'vs_lhp'
        : split;
    const rosterPlayerIds = rosterIdsByTeam.get(normalizedTeam) ?? null;

    const preparedRows = hitterRows
      .filter((row) => normalizeTeam(row.team) === normalizedTeam)
      .filter((row) => String(row.split_key ?? DEFAULT_SPLIT_KEY) === effectiveSplit)
      .filter((row) => String(row.recent_window ?? DEFAULT_RECENT_WINDOW) === recentWindow)
      .filter((row) => String(row.weighted_mode ?? DEFAULT_WEIGHTED_MODE) === weightedMode)
      .filter((row) => !rosterPlayerIds || rosterPlayerIds.has(String(row.batter ?? row.player_id ?? '').trim()))
      .filter((row) => !exclusionSet.has(String(row.batter ?? row.player_id ?? '').trim()))
      .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }))
      .filter((row) => !likelyOnly || (numberOrNull(row.likely_starter_score) ?? 0) > 0);

    return applyProjectedLineup(preparedRows, normalizedTeam, rotowireLineups);
  }

  async getTopHitters(options = {}) {
    const result = await this.buildHostedHitterParityResult(options);
    return {
      source: result.source,
      resolvedDate: result.resolvedDate,
      parityRanked: true,
      rows: result.rows.slice(0, limitOrDefault(options.limit)),
    };
  }

  async getTopPitchers(options = {}) {
    const targetDate = parseDateOrToday(options.date);
    const resolvedDate = await this.getLatestAvailableDate(targetDate);
    if (!resolvedDate) {
      throw new Error('No hosted pitcher slate was available in the fallback window.');
    }

    const [slateRows, pitcherRows, rosterRows] = await Promise.all([
      this.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
      this.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
      this.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
    ]);

    const slateLookup = mapSlateTeams(slateRows);
    const rosterLookup = buildRosterLookup(rosterRows);
    const filteredRows = pitcherRows
      .filter(keepRowForDefaults)
      .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'pitcher' }))
      .filter((row) => rowMatchesTeamFilter(row.team, options.team));

    return {
      source: 'hosted',
      resolvedDate,
      rows: sortPitchers(filteredRows).slice(0, limitOrDefault(options.limit)),
    };
  }

  async getBestMatchups(options = {}) {
    const result = await this.buildHostedHitterParityResult(options);
    return {
      source: result.source,
      resolvedDate: result.resolvedDate,
      parityRanked: true,
      rows: result.boardRows.slice(0, limitOrDefault(options.limit ?? (options.team ? 3 : 12), 12)),
    };
  }

  async getTeamBestMatchups(options = {}) {
    if (!options.team) {
      throw new Error('Team Best Matchups requires a team filter.');
    }

    const result = await this.buildHostedHitterParityResult(options);
    return {
      source: result.source,
      resolvedDate: result.resolvedDate,
      parityRanked: true,
      rows: result.rows.slice(0, limitOrDefault(options.limit ?? 3, 12)),
    };
  }

  async getPlayerContext(options = {}) {
    const targetDate = parseDateOrToday(options.date);
    const resolvedDate = await this.getLatestAvailableDate(targetDate);
    if (!resolvedDate) {
      throw new Error('No hosted player context was available in the fallback window.');
    }

    const [slateRows, dailyHitterRows, dailyPitcherRows, reusableHitters, reusablePitchers, hitterRollingRows, pitcherRollingRows, batterZoneRows, pitcherZoneRows, arsenalRows, countUsageRows, rosterRows] = await Promise.all([
      this.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
      this.readDailyFile(resolvedDate, 'daily_hitter_metrics.parquet', HITTER_COLUMNS),
      this.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
      this.readReusableFile('hitter_metrics.parquet', REUSABLE_HITTER_COLUMNS),
      this.readReusableFile('pitcher_metrics.parquet', REUSABLE_PITCHER_COLUMNS),
      this.readReusableFile('hitter_rolling.parquet', HITTER_ROLLING_COLUMNS).catch(() => []),
      this.readReusableFile('pitcher_rolling.parquet', PITCHER_ROLLING_COLUMNS).catch(() => []),
      this.readReusableFile('batter_zone_profiles.parquet', BATTER_ZONE_COLUMNS).catch(() => []),
      this.readReusableFile('pitcher_zone_profiles.parquet', PITCHER_ZONE_COLUMNS).catch(() => []),
      this.readReusableFile('pitcher_arsenal.parquet', ARSENAL_COLUMNS).catch(() => []),
      this.readReusableFile('pitcher_usage_by_count.parquet', COUNT_USAGE_COLUMNS).catch(() => []),
      this.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
    ]);

    const slateLookup = mapSlateTeams(slateRows);
    const rosterLookup = buildRosterLookup(rosterRows);
    const normalizedType = normalizeText(options.playerType || 'auto');

    const dailyHitters = dailyHitterRows
      .filter(keepRowForDefaults)
      .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }));
    const dailyPitchers = dailyPitcherRows
      .filter(keepRowForDefaults)
      .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'pitcher' }));
    const baseHitters = reusableHitters
      .filter(keepRowForDefaults)
      .map((row) => ({ ...row, hitter_name: resolveHostedPlayerName(row, rosterLookup, 'hitter') }));
    const basePitchers = reusablePitchers
      .filter(keepRowForDefaults)
      .map((row) => ({ ...row, pitcher_name: resolveHostedPlayerName(row, rosterLookup, 'pitcher') }));

    const hitterMatch = normalizedType === 'pitcher' ? null : findBestPlayerMatch(dailyHitters, 'hitter_name', options.player)
      ?? findBestPlayerMatch(baseHitters, 'hitter_name', options.player);
    const pitcherMatch = normalizedType === 'hitter' ? null : findBestPlayerMatch(dailyPitchers, 'pitcher_name', options.player)
      ?? findBestPlayerMatch(basePitchers, 'pitcher_name', options.player);

    if (hitterMatch) {
      const playerId = String(hitterMatch.batter ?? hitterMatch.player_id ?? '');
      return {
        source: 'hosted',
        resolvedDate,
        playerType: 'hitter',
        name: hitterMatch.hitter_name,
        team: hitterMatch.team ?? null,
        opponentTeam: hitterMatch.opponent_team ?? null,
        opposingPitcherName: hitterMatch.opposing_pitcher_name ?? null,
        hand: hitterMatch.opposing_pitcher_hand ?? null,
        overview: hitterMatch,
        metrics: pickMetrics(hitterMatch, [
          ['Matchup', 'matchup_score'],
          ['Ceiling', 'ceiling_score'],
          ['Zone Fit', 'zone_fit_score'],
          ['Likely', 'likely_starter_score'],
          ['xwOBA', 'xwoba'],
          ['HH%', 'hard_hit_pct'],
          ['Brl/BIP%', 'barrel_bip_pct'],
        ]),
        rolling: buildRollingSummary(
          hitterRollingRows.filter((row) => String(row.batter ?? row.player_id ?? '') === playerId),
          'window_label',
          'xwoba'
        ),
        zones: buildZoneSummary(
          batterZoneRows.filter((row) => String(row.batter ?? row.player_id ?? '') === playerId),
          'hitter_name',
          ['xwoba', 'hard_hit_pct', 'barrel_bip_pct']
        ),
        arsenal: [],
        countUsage: [],
      };
    }

    if (pitcherMatch) {
      const playerId = String(pitcherMatch.pitcher_id ?? pitcherMatch.player_id ?? '');
      return {
        source: 'hosted',
        resolvedDate,
        playerType: 'pitcher',
        name: pitcherMatch.pitcher_name,
        team: pitcherMatch.team ?? null,
        opponentTeam: pitcherMatch.opponent_team ?? null,
        hand: pitcherMatch.p_throws ?? null,
        overview: pitcherMatch,
        metrics: pickMetrics(pitcherMatch, [
          ['Pitch Score', 'pitcher_score'],
          ['Strikeout', 'strikeout_score'],
          ['Matchup Adj', 'pitcher_matchup_adjustment'],
          ['K Adj', 'strikeout_matchup_adjustment'],
          ['xwOBA', 'xwoba'],
          ['CSW%', 'csw_pct'],
          ['SwStr%', 'swstr_pct'],
        ]),
        rolling: buildRollingSummary(
          pitcherRollingRows.filter((row) => String(row.pitcher_id ?? row.player_id ?? '') === playerId),
          'window_label',
          'xwoba'
        ),
        zones: buildZoneSummary(
          pitcherZoneRows.filter((row) => String(row.pitcher_id ?? row.player_id ?? '') === playerId),
          'pitcher_name',
          ['xwoba_allowed', 'hard_hit_pct_allowed', 'barrel_bip_pct_allowed']
        ),
        arsenal: buildArsenalSummary(
          arsenalRows.filter((row) => String(row.pitcher_id ?? row.player_id ?? '') === playerId)
        ),
        countUsage: buildCountUsageSummary(
          countUsageRows.filter((row) => String(row.pitcher_id ?? row.player_id ?? '') === playerId)
        ),
      };
    }

    throw new Error(`No hosted matchup profile matched "${options.player}".`);
  }

  async getHealth(options = {}) {
    const targetDate = parseDateOrToday(options.date);
    const latestDate = await this.getLatestAvailableDate(targetDate);
    return {
      configured: this.isConfigured(),
      baseUrl: this.baseUrl || null,
      latestDate,
      cacheEntries: this.cache.size,
      cacheTtlMs: this.cacheTtlMs,
    };
  }
}

export class CockroachMatchupSource {
  constructor(databaseUrl, options = {}) {
    const usesSsl = /sslmode=(require|verify-ca|verify-full)/i.test(databaseUrl);
    this.pool = options.pool ?? new Pool({
      connectionString: databaseUrl,
      ssl: usesSsl ? { rejectUnauthorized: false } : undefined,
    });
    this.logger = options.logger ?? console;
    this.retryLimit = Number(options.retryLimit ?? 3);
  }

  async close() {
    await this.pool.end();
  }

  async query(text, values = [], attempt = 1) {
    try {
      return await this.pool.query(text, values);
    } catch (error) {
      if (COCKROACH_RETRY_CODES.has(error?.code) && attempt < this.retryLimit) {
        await sleep(50 * attempt);
        return this.query(text, values, attempt + 1);
      }
      throw error;
    }
  }

  async getLatestSnapshotDate() {
    const { rows } = await this.query(
      `
        SELECT GREATEST(
          COALESCE((SELECT MAX(slate_date)::date FROM public.hitter_model_snapshots), DATE '1970-01-01'),
          COALESCE((SELECT MAX(slate_date)::date FROM public.pitcher_model_snapshots), DATE '1970-01-01')
        ) AS latest_slate_date
      `
    );
    return rows[0]?.latest_slate_date ?? null;
  }

  async getTopHitters(options = {}) {
    const limit = limitOrDefault(options.limit);
    const values = [options.date ?? null, resolveCanonicalTeamFilter(options.team), limit];
    const { rows } = await this.query(
      `
        WITH target_date AS (
          SELECT COALESCE($1::date, (SELECT MAX(slate_date)::date FROM public.hitter_model_snapshots)) AS slate_date
        )
        SELECT
          slate_date::date AS slate_date,
          team,
          hitter_name,
          matchup_score,
          ceiling_score,
          zone_fit_score,
          likely_starter_score,
          xwoba
        FROM public.hitter_model_snapshots
        WHERE slate_date::date = (SELECT slate_date FROM target_date)
          AND split_key = $4
          AND recent_window = $5
          AND weighted_mode = $6
          AND ($2::text IS NULL OR team = $2::text)
        ORDER BY matchup_score DESC NULLS LAST, ceiling_score DESC NULLS LAST, likely_starter_score DESC NULLS LAST, xwoba DESC NULLS LAST
        LIMIT $3
      `,
      [...values, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE]
    );

    return {
      source: 'cockroach',
      resolvedDate: rows[0]?.slate_date ?? null,
      rows,
    };
  }

  async getTopPitchers(options = {}) {
    const limit = limitOrDefault(options.limit);
    const values = [options.date ?? null, resolveCanonicalTeamFilter(options.team), limit];
    const { rows } = await this.query(
      `
        WITH target_date AS (
          SELECT COALESCE($1::date, (SELECT MAX(slate_date)::date FROM public.pitcher_model_snapshots)) AS slate_date
        )
        SELECT
          slate_date::date AS slate_date,
          team,
          pitcher_name,
          p_throws,
          pitcher_score,
          strikeout_score,
          raw_pitcher_score,
          raw_strikeout_score,
          pitcher_matchup_adjustment,
          strikeout_matchup_adjustment,
          opponent_lineup_quality,
          opponent_contact_threat,
          opponent_whiff_tendency,
          lineup_source,
          lineup_hitter_count,
          xwoba,
          csw_pct,
          swstr_pct,
          putaway_pct,
          ball_pct,
          siera,
          gb_pct,
          gb_fb_ratio,
          barrel_bip_pct,
          hard_hit_pct
        FROM public.pitcher_model_snapshots
        WHERE slate_date::date = (SELECT slate_date FROM target_date)
          AND split_key = $4
          AND recent_window = $5
          AND weighted_mode = $6
          AND ($2::text IS NULL OR team = $2::text)
        ORDER BY pitcher_score DESC NULLS LAST, strikeout_score DESC NULLS LAST, pitcher_matchup_adjustment DESC NULLS LAST, xwoba ASC NULLS LAST
        LIMIT $3
      `,
      [...values, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE]
    );

    return {
      source: 'cockroach',
      resolvedDate: rows[0]?.slate_date ?? null,
      rows,
    };
  }

  async getBestMatchups(options = {}) {
    return this.getTopHitters({
      ...options,
      limit: limitOrDefault(options.limit, 12),
    });
  }

  async getTeamBestMatchups(options = {}) {
    if (!options.team) {
      throw new Error('Team Best Matchups requires a team filter.');
    }
    return this.getTopHitters({
      ...options,
      limit: limitOrDefault(options.limit ?? 3, 12),
    });
  }

  async getHitterSnapshotRows(options = {}) {
    const values = [options.date ?? null, resolveCanonicalTeamFilter(options.team)];
    const { rows } = await this.query(
      `
        WITH target_date AS (
          SELECT COALESCE($1::date, (SELECT MAX(slate_date)::date FROM public.hitter_model_snapshots)) AS slate_date
        )
        SELECT
          slate_date::date AS slate_date,
          team,
          hitter_name,
          matchup_score,
          ceiling_score,
          zone_fit_score,
          likely_starter_score,
          xwoba,
          hard_hit_pct,
          barrel_bip_pct
        FROM public.hitter_model_snapshots
        WHERE slate_date::date = (SELECT slate_date FROM target_date)
          AND split_key = $3
          AND recent_window = $4
          AND weighted_mode = $5
          AND ($2::text IS NULL OR team = $2::text)
      `,
      [...values, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE]
    );

    return rows;
  }

  async getPlayerContext(options = {}) {
    const hitterResult = normalizeText(options.playerType) === 'pitcher'
      ? { rows: [] }
      : await this.query(
        `
          SELECT
            slate_date::date AS slate_date,
            team,
            hitter_name,
            matchup_score,
            ceiling_score,
            zone_fit_score,
            likely_starter_score,
            xwoba,
            hard_hit_pct,
            barrel_bip_pct
          FROM public.hitter_model_snapshots
          WHERE slate_date::date = COALESCE($2::date, (SELECT MAX(slate_date)::date FROM public.hitter_model_snapshots))
            AND split_key = $3
            AND recent_window = $4
            AND weighted_mode = $5
            AND LOWER(hitter_name) LIKE LOWER($1)
          ORDER BY CASE WHEN LOWER(hitter_name) = LOWER($6) THEN 0 ELSE 1 END, matchup_score DESC NULLS LAST
          LIMIT $7
        `,
        [`%${options.player}%`, options.date ?? null, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE, options.player, PROFILE_CANDIDATE_LIMIT]
      );

    const pitcherResult = normalizeText(options.playerType) === 'hitter'
      ? { rows: [] }
      : await this.query(
        `
          SELECT
            slate_date::date AS slate_date,
            team,
            pitcher_name,
            p_throws,
            pitcher_score,
            strikeout_score,
            pitcher_matchup_adjustment,
            strikeout_matchup_adjustment,
            xwoba,
            csw_pct,
            swstr_pct
          FROM public.pitcher_model_snapshots
          WHERE slate_date::date = COALESCE($2::date, (SELECT MAX(slate_date)::date FROM public.pitcher_model_snapshots))
            AND split_key = $3
            AND recent_window = $4
            AND weighted_mode = $5
            AND LOWER(pitcher_name) LIKE LOWER($1)
          ORDER BY CASE WHEN LOWER(pitcher_name) = LOWER($6) THEN 0 ELSE 1 END, pitcher_score DESC NULLS LAST
          LIMIT $7
        `,
        [`%${options.player}%`, options.date ?? null, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE, options.player, PROFILE_CANDIDATE_LIMIT]
      );

    const hitter = hitterResult.rows[0] ?? null;
    const pitcher = pitcherResult.rows[0] ?? null;

    if (hitter) {
      return {
        source: 'cockroach',
        resolvedDate: hitter.slate_date ?? null,
        playerType: 'hitter',
        name: hitter.hitter_name,
        team: hitter.team ?? null,
        opponentTeam: null,
        opposingPitcherName: null,
        hand: null,
        overview: hitter,
        metrics: pickMetrics(hitter, [
          ['Matchup', 'matchup_score'],
          ['Ceiling', 'ceiling_score'],
          ['Zone Fit', 'zone_fit_score'],
          ['Likely', 'likely_starter_score'],
          ['xwOBA', 'xwoba'],
          ['HH%', 'hard_hit_pct'],
          ['Brl/BIP%', 'barrel_bip_pct'],
        ]),
        rolling: [],
        zones: [],
        arsenal: [],
        countUsage: [],
      };
    }

    if (pitcher) {
      return {
        source: 'cockroach',
        resolvedDate: pitcher.slate_date ?? null,
        playerType: 'pitcher',
        name: pitcher.pitcher_name,
        team: pitcher.team ?? null,
        opponentTeam: null,
        hand: pitcher.p_throws ?? null,
        overview: pitcher,
        metrics: pickMetrics(pitcher, [
          ['Pitch Score', 'pitcher_score'],
          ['Strikeout', 'strikeout_score'],
          ['Matchup Adj', 'pitcher_matchup_adjustment'],
          ['K Adj', 'strikeout_matchup_adjustment'],
          ['xwOBA', 'xwoba'],
          ['CSW%', 'csw_pct'],
          ['SwStr%', 'swstr_pct'],
        ]),
        rolling: [],
        zones: [],
        arsenal: [],
        countUsage: [],
      };
    }

    throw new Error(`No Cockroach matchup profile matched "${options.player}".`);
  }

  async getHealth(options = {}) {
    const latestDate = await this.getLatestSnapshotDate();
    return {
      configured: true,
      latestDate: latestDate ?? null,
      requestedDate: options.date ?? null,
    };
  }
}

export class MatchupService {
  constructor(config = {}, options = {}) {
    this.logger = options.logger ?? console;
    this.hosted = options.hosted ?? new HostedArtifactSource(config.hosted ?? {}, { logger: this.logger });
    this.fallback = options.fallback ?? new CockroachMatchupSource(config.databaseUrl, { logger: this.logger });
    this.oddsProvider = options.oddsProvider ?? null;
    this.chartCache = new Map();
    this.chartCacheTtlMs = Number(config.chartCacheTtlMs ?? config.hosted?.cacheTtlMs ?? 5 * 60 * 1000);
  }

  async close() {
    await this.fallback?.close?.();
  }

  setOddsProvider(provider) {
    this.oddsProvider = provider ?? null;
  }

  async getCachedChartValue(cacheKey, producer) {
    const now = Date.now();
    const cached = this.chartCache.get(cacheKey);
    if (cached && cached.expiresAt > now) {
      return cached.value;
    }
    const value = await producer();
    this.chartCache.set(cacheKey, {
      value,
      expiresAt: now + this.chartCacheTtlMs,
    });
    return value;
  }

  buildChartCacheKey(prefix, options = {}) {
    const stable = Object.entries(options)
      .filter(([, value]) => value !== undefined && value !== null && value !== '')
      .sort(([left], [right]) => left.localeCompare(right))
      .map(([key, value]) => `${key}:${String(value)}`);
    return [prefix, ...stable].join('|');
  }

  async runHostedFirst(methodName, options = {}) {
    let hostedError = null;
    if (this.hosted?.isConfigured?.()) {
      try {
        const hostedResult = await this.hosted[methodName](options);
        if (methodName === 'getTopHitters' && !hostedResult?.parityRanked) {
          return await this.enrichHostedHitters(hostedResult, options, methodName);
        }
        return hostedResult;
      } catch (error) {
        hostedError = error;
        this.logger?.warn?.(`Hosted matchup source failed for ${methodName}`, {
          error: error.message,
          options,
        });
      }
    }

    const result = await this.fallback[methodName](options);
    if (hostedError) {
      result.warning = hostedError.message;
    }
    return result;
  }

  async getTopHitters(options = {}) {
    return this.runHostedFirst('getTopHitters', options);
  }

  async getTopPitchers(options = {}) {
    return this.runHostedFirst('getTopPitchers', options);
  }

  async getBestMatchups(options = {}) {
    return this.runHostedFirst('getBestMatchups', options);
  }

  async getTeamBestMatchups(options = {}) {
    return this.runHostedFirst('getTeamBestMatchups', options);
  }

  async getPlayerContext(options = {}) {
    return this.runHostedFirst('getPlayerContext', options);
  }

  async getHealth(options = {}) {
    const [hosted, fallback] = await Promise.all([
      this.hosted.getHealth(options).catch((error) => ({
        configured: this.hosted?.isConfigured?.() ?? false,
        latestDate: null,
        error: error.message,
      })),
      this.fallback.getHealth(options).catch((error) => ({
        configured: true,
        latestDate: null,
        error: error.message,
      })),
    ]);

    return {
      hosted,
      fallback,
    };
  }

  async getHrBoardChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('hr-board', options), async () => {
      const result = await this.getTopHitters({
        ...options,
        limit: limitOrDefault(options.limit ?? 10, 15),
      });
      return {
        source: result.source,
        resolvedDate: result.resolvedDate,
        rows: result.rows.map((row) => ({
          ...row,
          label: `${row.hitter_name ?? 'Unknown'}${row.team ? ` (${row.team})` : ''}`,
          matchup: row.matchup_score,
          ceiling: row.ceiling_score,
        })),
      };
    });
  }

  async getHrProfileData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('hr-profile', options), async () => {
      const result = await this.getFastHitterChartContext(options.player, options);
      const overview = result.overview ?? {};
      return {
        ...result,
        playerName: result.name,
        labels: ['Matchup', 'Ceiling', 'Zone Fit', 'Barrel', 'Pulled Brl', 'SwStr', 'Sweet Spot', 'xwOBA'],
        values: [
          normalizeToRadarScore(overview.matchup_score),
          normalizeToRadarScore(overview.ceiling_score),
          normalizeToRadarScore(numberOrNull(overview.zone_fit_score) === null ? null : numberOrNull(overview.zone_fit_score) * 100, { min: 0, max: 100 }),
          normalizeToRadarScore(overview.barrel_bip_pct, { min: 0, max: 25, scalePercent: true }),
          normalizeToRadarScore(overview.pulled_barrel_pct, { min: 0, max: 20, scalePercent: true }),
          normalizeToRadarScore(overview.swstr_pct, { min: 5, max: 18, scalePercent: true, inverse: true }),
          normalizeToRadarScore(overview.sweet_spot_pct, { min: 20, max: 50, scalePercent: true }),
          normalizeToRadarScore(overview.xwoba, { min: 0.25, max: 0.45 }),
        ],
        zone_fit_score: overview.zone_fit_score,
        matchup_score: overview.matchup_score,
        ceiling_score: overview.ceiling_score,
        team: result.team,
        opposingPitcherName: result.opposingPitcherName,
        opposingPitcherHand: result.hand,
      };
    });
  }

  async getKProfileData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('k-profile', options), async () => {
      const result = await this.getFastPitcherChartContext(options.pitcher, options);
      const overview = result.overview ?? {};
      return {
        ...result,
        pitcherName: result.name,
        labels: ['Pitch Score', 'K Score', 'Pitch Adj', 'K Adj', 'CSW', 'SwStr', 'PutAway', 'Opp Whiff'],
        values: [
          normalizeToRadarScore(overview.pitcher_score),
          normalizeToRadarScore(overview.strikeout_score),
          normalizeToRadarScore(overview.pitcher_matchup_adjustment, { min: -10, max: 15 }),
          normalizeToRadarScore(overview.strikeout_matchup_adjustment, { min: -10, max: 15 }),
          normalizeToRadarScore(overview.csw_pct, { min: 20, max: 38, scalePercent: true }),
          normalizeToRadarScore(overview.swstr_pct, { min: 8, max: 20, scalePercent: true }),
          normalizeToRadarScore(overview.putaway_pct, { min: 10, max: 35, scalePercent: true }),
          normalizeToRadarScore(overview.opponent_whiff_tendency, { min: 18, max: 32 }),
        ],
        pitcher_score: overview.pitcher_score,
        strikeout_score: overview.strikeout_score,
        strikeout_matchup_adjustment: overview.strikeout_matchup_adjustment,
        pitcherName: result.name,
        opponentTeam: result.opponentTeam,
      };
    });
  }

  async getHrTrendData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('hr-trend', options), async () => {
      const base = await this.getHrProfileData(options);
      const points = await this.buildHostedTrendPoints({
        date: options.date,
        window: options.window,
        kind: 'hitter',
        playerName: options.player,
      });
      return {
        ...base,
        points: points.map((point) => ({ label: point.label, value: point.matchup_score })),
        primaryLabel: 'Matchup',
        overlays: [
          { label: 'Barrel%', values: points.map((point) => point.barrel_pct), color: '#f97316' },
          { label: 'xwOBA', values: points.map((point) => point.xwoba), color: '#fca5a5' },
        ],
      };
    });
  }

  async getKTrendData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('k-trend', options), async () => {
      const base = await this.getKProfileData(options);
      const points = await this.buildHostedTrendPoints({
        date: options.date,
        window: options.window,
        kind: 'pitcher',
        playerName: options.pitcher,
      });
      return {
        ...base,
        points: points.map((point) => ({ label: point.label, value: point.strikeout_score })),
        primaryLabel: 'Strikeout Score',
        overlays: [
          { label: 'CSW%', values: points.map((point) => point.csw_pct), color: '#93c5fd' },
          { label: 'SwStr%', values: points.map((point) => point.swstr_pct), color: '#c4b5fd' },
        ],
      };
    });
  }

  async getHrValueChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('hr-value', options), async () => {
      if (!this.oddsProvider?.getPlayerHomeRunOdds) {
        throw new Error('Home run value charts require the live odds provider.');
      }
      const board = await this.getHrBoardChartData({
        ...options,
        limit: limitOrDefault(options.limit ?? 10, 15),
      });
      const oddsPayloads = await Promise.all(board.rows.map(async (row) => {
        try {
          const odds = await this.oddsProvider.getPlayerHomeRunOdds(row.hitter_name, { book: options.book });
          const probabilities = odds.entries
            .map((entry) => impliedProbabilityFromAmerican(entry.oddsInput))
            .filter((value) => value !== null);
          return {
            row,
            book: odds.bookFilter ?? options.book ?? null,
            impliedProbability: averageOrNull(probabilities),
          };
        } catch {
          return null;
        }
      }));

      const points = oddsPayloads
        .filter(Boolean)
        .filter((item) => item.impliedProbability !== null)
        .map((item) => ({
          x: item.impliedProbability,
          y: numberOrNull(item.row.matchup_score) ?? 0,
          label: `${item.row.hitter_name} (${item.row.team})`,
          highlight: (numberOrNull(item.row.ceiling_score) ?? 0) >= 75,
        }));

      if (!points.length) {
        throw new Error('No home run odds were available for the current board.');
      }

      return {
        source: board.source,
        resolvedDate: board.resolvedDate,
        points,
      };
    });
  }

  async getHrZoneData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('hr-zone', options), async () => {
      if (!this.hosted?.isConfigured?.()) {
        throw new Error('HR zone charts require hosted artifacts.');
      }

      const targetDate = parseDateOrToday(options.date);
      const resolvedDate = await this.hosted.getLatestAvailableDate(targetDate);
      if (!resolvedDate) {
        throw new Error('No hosted matchup slate was available in the fallback window.');
      }

      const [slateRows, hitterRows, pitcherRows, rosterRows, batterZoneRows, pitcherZoneRows] = await Promise.all([
        this.hosted.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'daily_hitter_metrics.parquet', HITTER_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
        this.hosted.readDailyFile(resolvedDate, 'daily_batter_zone_profiles.parquet', BATTER_ZONE_COLUMNS).catch(() => []),
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_zone_profiles.parquet', PITCHER_ZONE_COLUMNS).catch(() => []),
      ]);

      const rosterLookup = buildRosterLookup(rosterRows);
      const pitcherLookup = new Map(
        pitcherRows
          .filter((row) => keepRowForDefaults(row))
          .map((row) => [String(row.pitcher_id ?? row.player_id ?? ''), row])
      );
      const slateLookup = mapSlateTeams(slateRows);
      for (const [team, slate] of slateLookup.entries()) {
        const pitcher = pitcherLookup.get(String(slate.opposingPitcherId ?? ''));
        if (pitcher?.p_throws) {
          slateLookup.set(team, { ...slate, opposingPitcherHand: pitcher.p_throws });
        }
      }

      const preparedRows = hitterRows
        .filter((row) => keepRowForDefaults(row))
        .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }));
      const hitterMatch = findBestPlayerMatch(preparedRows, 'hitter_name', options.player);
      if (!hitterMatch) {
        throw new Error(`No hitter matchup profile matched "${options.player}".`);
      }

      const hitterSlate = slateLookup.get(String(hitterMatch.team ?? '').trim())
        ?? slateLookup.get(normalizeTeam(hitterMatch.team))
        ?? {};
      const opposingPitcherId = String(
        firstNonBlankValue(hitterMatch.opposing_pitcher_id, hitterSlate.opposingPitcherId) ?? ''
      ).trim();
      const opposingPitcherRow = pitcherLookup.get(opposingPitcherId) ?? null;
      const opposingPitcherRowName = opposingPitcherRow
        ? resolveHostedPlayerName(opposingPitcherRow, rosterLookup, 'pitcher')
        : null;
      const opposingPitcherName = firstNonBlankValue(
        hitterMatch.opposing_pitcher_name,
        hitterSlate.opposingPitcherName,
        opposingPitcherRowName,
      );
      const opposingPitcherHand = firstNonBlankValue(
        hitterMatch.opposing_pitcher_hand,
        hitterSlate.opposingPitcherHand,
      );

      const batterMap = aggregateBatterZoneMap(selectBatterZoneRows(
        batterZoneRows,
        String(hitterMatch.batter ?? hitterMatch.player_id ?? ''),
        opposingPitcherHand
      ));
      const pitcherMap = aggregatePitcherZoneMap(selectPitcherZoneRows(
        pitcherZoneRows,
        opposingPitcherId,
        hitterMatch.stand
      ));
      const overlay = buildZoneOverlayMap(batterMap, pitcherMap);
      if (!overlay.length) {
        throw new Error('Zone profile inputs were unavailable for that hitter.');
      }

      const batterByZone = new Map(batterMap.map((row) => [row.zone, row]));
      const pitcherByZone = new Map(pitcherMap.map((row) => [row.zone, row]));
      const overlayByZone = new Map(overlay.map((row) => [row.zone, row]));
      const cells = [1, 2, 3, 4, 5, 6, 7, 8, 9].map((zone) => ({
        zone,
        batterValue: batterByZone.get(zone)?.zone_value ?? 0,
        pitcherValue: pitcherByZone.get(zone)?.zone_value ?? 0,
        overlayValue: overlayByZone.get(zone)?.zone_value ?? 0,
      }));
      const insights = buildZoneOverlayInsights(cells);

      return {
        source: 'hosted',
        resolvedDate,
        playerName: hitterMatch.hitter_name,
        team: hitterMatch.team,
        opposingPitcherName,
        opposingPitcherHand,
        zone_fit_score: hitterMatch.zone_fit_score ?? overlayZoneFitScore(batterMap, pitcherMap),
        cells,
        bestOverlay: insights.bestOverlay,
        shapeSummary: insights.shapeSummary,
        read: 'Green cells show the strongest overlap between batter damage and pitcher attack lanes.',
      };
    });
  }

  async getKLadderData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('k-ladder', options), async () => {
      if (!this.oddsProvider?.getPlayerMarketOdds) {
        throw new Error('Strikeout ladder charts require the live odds provider.');
      }

      const profile = await this.getKProfileData(options);
      const booksToTry = options.book ? [options.book] : ['FanDuel', 'DraftKings', 'BetMGM', 'Caesars'];
      let oddsResult = null;
      for (const book of booksToTry) {
        try {
          oddsResult = await this.oddsProvider.getPlayerMarketOdds('pitcher_strikeouts', options.pitcher, { book });
          if (oddsResult?.entries?.length) {
            break;
          }
        } catch {
          continue;
        }
      }

      if (!oddsResult?.entries?.length) {
        throw new Error(`No strikeout ladder odds were found for ${options.pitcher}.`);
      }

      const rows = oddsResult.entries.map((entry) => ({
        label: entry.selectionDisplay ?? `${Math.floor(numberOrNull(entry.lineValue) ?? 0) + 1}+`,
        probability: impliedProbabilityFromAmerican(entry.oddsInput) ?? 0,
        price: entry.oddsInput,
      }));

      return {
        ...profile,
        source: 'odds',
        book: oddsResult.bookFilter ?? null,
        rows,
        bestLabel: rows[0]?.label ?? null,
        bestPrice: rows[0]?.price ?? null,
      };
    });
  }

  async getKMatchupData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('k-matchup', options), async () => {
      const profile = await this.getKProfileData(options);
      const overview = profile.overview ?? {};
      const pitcherMetrics = [
        { label: 'Strikeout Score', value: formatMetricValue(overview.strikeout_score), normalized: normalizeToRadarScore(overview.strikeout_score) / 100 },
        { label: 'CSW%', value: formatMetricValue(overview.csw_pct, 'pct'), normalized: normalizeToRadarScore(overview.csw_pct, { min: 20, max: 38, scalePercent: true }) / 100 },
        { label: 'SwStr%', value: formatMetricValue(overview.swstr_pct, 'pct'), normalized: normalizeToRadarScore(overview.swstr_pct, { min: 8, max: 20, scalePercent: true }) / 100 },
        { label: 'PutAway%', value: formatMetricValue(overview.putaway_pct, 'pct'), normalized: normalizeToRadarScore(overview.putaway_pct, { min: 10, max: 35, scalePercent: true }) / 100 },
      ];
      const opponentMetrics = [
        { label: 'Opp Whiff', value: formatMetricValue(overview.opponent_whiff_tendency), normalized: normalizeToRadarScore(overview.opponent_whiff_tendency, { min: 18, max: 32 }) / 100 },
        { label: 'Lineup Quality', value: formatMetricValue(overview.opponent_lineup_quality), normalized: normalizeToRadarScore(overview.opponent_lineup_quality, { min: 60, max: 110, inverse: true }) / 100 },
        { label: 'Contact Threat', value: formatMetricValue(overview.opponent_contact_threat), normalized: normalizeToRadarScore(overview.opponent_contact_threat, { min: 60, max: 110, inverse: true }) / 100 },
        { label: 'K Adjustment', value: formatMetricValue(overview.strikeout_matchup_adjustment), normalized: normalizeToRadarScore(overview.strikeout_matchup_adjustment, { min: -10, max: 15 }) / 100 },
      ];
      return {
        ...profile,
        pitcherMetrics,
        opponentMetrics,
        read: `${profile.pitcherName} projects as a ${numberOrNull(overview.strikeout_score) !== null && overview.strikeout_score >= 70 ? 'high-upside' : 'moderate'} strikeout spot versus ${profile.opponentTeam ?? 'the current opponent'}.`,
      };
    });
  }

  async getKCountData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('k-count', options), async () => {
      const profile = await this.getKProfileData(options);
      if (!this.hosted?.isConfigured?.()) {
        throw new Error('Count leverage charts require hosted artifacts.');
      }

      const targetDate = parseDateOrToday(options.date);
      const resolvedDate = await this.hosted.getLatestAvailableDate(targetDate);
      const [pitchers, countRows] = await Promise.all([
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
        this.hosted.readReusableFile('pitcher_usage_by_count.parquet', COUNT_USAGE_COLUMNS).catch(() => []),
      ]);
      const pitcherMatch = findBestPlayerMatch(
        pitchers.filter((row) => keepRowForDefaults(row)).map((row) => ({ ...row, pitcher_name: row.pitcher_name })),
        'pitcher_name',
        options.pitcher
      );
      const pitcherId = String(pitcherMatch?.pitcher_id ?? pitcherMatch?.player_id ?? profile.overview?.pitcher_id ?? '');
      const filtered = countRows.filter((row) => String(row.pitcher_id ?? row.player_id ?? '') === pitcherId);
      if (!filtered.length) {
        throw new Error('No count-usage rows were available for that pitcher.');
      }

      const buckets = [...new Set(filtered.map((row) => row.count_bucket).filter(Boolean))];
      const pitchTypes = uniqueRowsByKey(
        [...filtered].sort((left, right) => compareNullableDescending(left.usage_pct, right.usage_pct)),
        (row) => row.pitch_type
      )
        .slice(0, 4)
        .map((row) => row.pitch_type);

      const datasets = pitchTypes.map((pitchType) => ({
        label: pitchType,
        values: buckets.map((bucket) => {
          const bucketRows = filtered.filter((row) => row.count_bucket === bucket && row.pitch_type === pitchType);
          const usage = averageOrNull(bucketRows.map((row) => row.usage_pct));
          const numeric = numberOrNull(usage);
          return numeric === null ? 0 : (Math.abs(numeric) <= 1 ? numeric * 100 : numeric);
        }),
      }));

      return {
        ...profile,
        labels: buckets,
        datasets,
        read: 'Higher bars show which pitch types carry the most leverage in each count bucket.',
      };
    });
  }

  async queryCockroachRows(text, values = []) {
    if (typeof this.fallback?.query !== 'function') {
      throw new Error('Cockroach query access is required for this pitcher chart.');
    }
    const result = await this.fallback.query(text, values);
    return result?.rows ?? [];
  }

  async resolvePitcherSuiteContext(playerName, options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-suite-context', {
      pitcher: playerName,
      date: options.date,
    }), async () => {
      const candidates = await this.queryCockroachRows(
        `
          WITH candidates AS (
            SELECT
              pitcher_name AS pitcher_name,
              pitcher_id::text AS pitcher_id,
              team,
              opponent AS opponent_team,
              p_throws AS pitcher_hand,
              slate_date::date AS latest_date,
              1 AS source_rank
            FROM public.pitcher_model_snapshots
            WHERE LOWER(pitcher_name) LIKE LOWER($1)

            UNION ALL

            SELECT
              pitcher_name AS pitcher_name,
              pitcher_id::text AS pitcher_id,
              team,
              NULL::text AS opponent_team,
              NULL::text AS pitcher_hand,
              slate_date::date AS latest_date,
              2 AS source_rank
            FROM public.pitcher_game_outcomes
            WHERE LOWER(pitcher_name) LIKE LOWER($1)

            UNION ALL

            SELECT
              pitcher_name AS pitcher_name,
              pitcher::text AS pitcher_id,
              NULL::text AS team,
              NULL::text AS opponent_team,
              p_throws AS pitcher_hand,
              game_date::date AS latest_date,
              3 AS source_rank
            FROM public.live_pitch_mix_2026
            WHERE LOWER(pitcher_name) LIKE LOWER($1)

            UNION ALL

            SELECT
              pitcher_name AS pitcher_name,
              pitcher::text AS pitcher_id,
              NULL::text AS team,
              NULL::text AS opponent_team,
              COALESCE(pitcher_hand, p_throws) AS pitcher_hand,
              game_date::date AS latest_date,
              4 AS source_rank
            FROM public.shared_pitcher_baseline_event_rows
            WHERE LOWER(pitcher_name) LIKE LOWER($1)
          )
          SELECT pitcher_name, pitcher_id, team, opponent_team, pitcher_hand, latest_date
          FROM candidates
          ORDER BY
            CASE WHEN LOWER(pitcher_name) = LOWER($2) THEN 0 ELSE 1 END,
            source_rank,
            latest_date DESC NULLS LAST
          LIMIT 1
        `,
        [`%${playerName}%`, playerName]
      );

      const identity = candidates[0];
      if (!identity) {
        throw new Error(`No pitcher chart profile matched "${playerName}".`);
      }

      const overviewRows = await this.queryCockroachRows(
        `
          SELECT
            slate_date::date AS slate_date,
            team,
            opponent AS opponent_team,
            pitcher_name,
            p_throws,
            pitcher_score,
            strikeout_score,
            pitcher_matchup_adjustment,
            strikeout_matchup_adjustment,
            opponent_lineup_quality,
            opponent_contact_threat,
            opponent_whiff_tendency,
            xwoba,
            csw_pct,
            swstr_pct,
            putaway_pct,
            ball_pct,
            siera,
            gb_pct,
            gb_fb_ratio,
            barrel_bip_pct,
            hard_hit_pct
          FROM public.pitcher_model_snapshots
          WHERE LOWER(pitcher_name) = LOWER($1)
            AND ($2::date IS NULL OR slate_date::date = $2::date)
            AND split_key = $3
            AND recent_window = $4
            AND weighted_mode = $5
          ORDER BY slate_date::date DESC
          LIMIT 1
        `,
        [identity.pitcher_name, options.date ?? null, DEFAULT_SPLIT_KEY, DEFAULT_RECENT_WINDOW, DEFAULT_WEIGHTED_MODE]
      );

      const overview = overviewRows[0] ?? {};

      return {
        source: 'cockroach',
        resolvedDate: identity.latest_date ?? overview.slate_date ?? null,
        pitcherName: identity.pitcher_name,
        pitcherId: String(identity.pitcher_id ?? '').trim() || null,
        team: identity.team ?? overview.team ?? null,
        opponentTeam: identity.opponent_team ?? overview.opponent_team ?? null,
        hand: identity.pitcher_hand ?? overview.p_throws ?? null,
        overview,
      };
    });
  }

  async getPitcherTrendChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-trend-suite', options), async () => {
      const context = await this.resolvePitcherSuiteContext(options.pitcher, options);
      const view = String(options.view ?? 'velo');
      const window = String(options.window ?? 'last_5');
      const split = String(options.split ?? 'overall');
      const pitchType = options.pitchType ?? options.pitch_type ?? null;
      const pitcherId = context.pitcherId ?? null;

      if (view === 'results') {
        const rows = await this.queryCockroachRows(
          `
            SELECT slate_date::date AS point_date, strikeouts, walks, hits_allowed, home_runs_allowed, outs_recorded
            FROM public.pitcher_game_outcomes
            WHERE LOWER(pitcher_name) = LOWER($1)
            ORDER BY slate_date::date DESC
            LIMIT $2
          `,
          [context.pitcherName, getPitcherWindowPointLimit(window)]
        );
        if (!rows.length) {
          throw new Error(`No game outcome trend was available for ${context.pitcherName}.`);
        }
        const ordered = [...rows].reverse();
        return {
          ...context,
          chartType: 'line',
          view,
          window,
          title: `Pitcher Results - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | ${windowLabel(window)}`,
          points: ordered.map((row) => ({ label: formatDateLabel(row.point_date), value: numberOrNull(row.strikeouts) ?? 0 })),
          primaryLabel: 'Strikeouts',
          overlays: [
            { label: 'Walks', values: ordered.map((row) => numberOrNull(row.walks) ?? 0), color: '#f59e0b' },
            { label: 'Hits Allowed', values: ordered.map((row) => numberOrNull(row.hits_allowed) ?? 0), color: '#f87171' },
          ],
          read: `Recent results show ${context.pitcherName} averaging ${averageOrNull(rows.map((row) => numberOrNull(row.strikeouts)))?.toFixed(1) ?? 'N/A'} strikeouts across the selected window.`,
        };
      }

      if (view === 'form') {
        const rows = await this.queryCockroachRows(
          `
            SELECT *
            FROM public.shared_pitcher_rolling_summary
            WHERE LOWER(player_name) = LOWER($1)
            LIMIT 1
          `,
          [context.pitcherName]
        );
        const row = rows[0];
        if (!row) {
          throw new Error(`No rolling form summary was available for ${context.pitcherName}.`);
        }
        return {
          ...context,
          chartType: 'radar',
          view,
          window,
          title: `Pitcher Form - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | rolling dashboard`,
          labels: ['Velo 5G', 'Spin 5G', 'EV Allowed', 'HH Allowed', 'Barrel Allowed', 'HR Allowed'],
          values: [
            normalizeToRadarScore(row.pitcher_avg_release_speed_5g, { min: 88, max: 100 }),
            normalizeToRadarScore(row.pitcher_avg_release_spin_rate_5g, { min: 1800, max: 2800 }),
            normalizeToRadarScore(row.pitcher_ev_allowed_5g, { min: 80, max: 95, inverse: true }),
            normalizeToRadarScore(row.pitcher_hard_hit_rate_allowed_5g, { min: 20, max: 50, inverse: true }),
            normalizeToRadarScore(row.pitcher_barrel_rate_allowed_5g, { min: 2, max: 14, inverse: true }),
            normalizeToRadarScore(row.pitcher_hr_allowed_rate_5g, { min: 0.005, max: 0.08, inverse: true }),
          ],
          read: `Recent form confidence is ${formatMetricValue(row.pitcher_rolling_confidence)} with ${numberOrNull(row.pitcher_games_in_window_5g) ?? 0} games in the five-game window.`,
        };
      }

      if (view === 'baseline') {
        const currentRows = await this.queryCockroachRows(
          `
            SELECT
              AVG(release_speed) AS avg_release_speed,
              AVG(release_spin_rate) AS avg_release_spin_rate,
              AVG(release_extension) AS avg_release_extension,
              AVG(pfx_x) AS avg_pfx_x,
              AVG(pfx_z) AS avg_pfx_z
            FROM public.live_pitch_mix_2026
            WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
              ${buildPitchTypeSqlFilter('pitch_type', 'pitch_name', 3)}
              ${buildSplitSqlFilter('stand', 4)}
          `,
          [context.pitcherName, pitcherId, pitchType, split]
        );
        const baselineRows = await this.queryCockroachRows(
          `
            SELECT
              AVG(release_speed) AS avg_release_speed,
              AVG(release_spin_rate) AS avg_release_spin_rate,
              AVG(release_extension) AS avg_release_extension,
              AVG(pfx_x) AS avg_pfx_x,
              AVG(pfx_z) AS avg_pfx_z
            FROM public.shared_pitcher_baseline_event_rows
            WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
              ${buildPitchTypeSqlFilter('pitch_type', 'pitch_name', 3)}
              ${buildSplitSqlFilter('COALESCE(batter_stand, stand)', 4)}
          `,
          [context.pitcherName, pitcherId, pitchType, split]
        );
        const current = currentRows[0] ?? {};
        const baseline = baselineRows[0] ?? {};
        return {
          ...context,
          chartType: 'bar',
          view,
          window,
          title: `Pitcher Baseline - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | current vs baseline`,
          labels: ['Velocity', 'Spin', 'Extension', 'Move X', 'Move Z'],
          datasets: [
            {
              label: 'Season 2026',
              values: [
                numberOrNull(current.avg_release_speed) ?? 0,
                (numberOrNull(current.avg_release_spin_rate) ?? 0) / 100,
                (numberOrNull(current.avg_release_extension) ?? 0) * 10,
                (numberOrNull(current.avg_pfx_x) ?? 0) * 10,
                (numberOrNull(current.avg_pfx_z) ?? 0) * 10,
              ],
            },
            {
              label: labelForCompareTarget(options.compareTo ?? 'career'),
              values: [
                numberOrNull(baseline.avg_release_speed) ?? 0,
                (numberOrNull(baseline.avg_release_spin_rate) ?? 0) / 100,
                (numberOrNull(baseline.avg_release_extension) ?? 0) * 10,
                (numberOrNull(baseline.avg_pfx_x) ?? 0) * 10,
                (numberOrNull(baseline.avg_pfx_z) ?? 0) * 10,
              ],
            },
          ],
          read: `This view compares the current-season pitch shape and release baseline against ${labelForCompareTarget(options.compareTo ?? 'career').toLowerCase()}.`,
        };
      }

      const tableName = window === 'career' ? 'public.shared_pitcher_baseline_event_rows' : 'public.live_pitch_mix_2026';
      const dateExpr = window === 'career' ? 'source_season::text' : 'game_date::date';
      const splitExpr = window === 'career' ? 'COALESCE(batter_stand, stand)' : 'stand';
      const primaryExpr = view === 'velo'
        ? 'AVG(release_speed)'
        : view === 'spin'
          ? 'AVG(release_spin_rate)'
          : 'AVG(release_extension)';
      const overlayAExpr = view === 'velo'
        ? 'AVG(effective_speed)'
        : view === 'spin'
          ? 'AVG(spin_efficiency_proxy)'
          : 'AVG(release_pos_x)';
      const overlayBExpr = view === 'velo'
        ? 'AVG(pfx_z)'
        : view === 'spin'
          ? 'AVG(spin_axis)'
          : 'AVG(release_pos_z)';
      const rows = await this.queryCockroachRows(
        `
          WITH grouped AS (
            SELECT
              ${dateExpr} AS point_key,
              ${primaryExpr} AS primary_value,
              ${overlayAExpr} AS overlay_a,
              ${overlayBExpr} AS overlay_b
            FROM ${tableName}
            WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
              ${buildPitchTypeSqlFilter('pitch_type', 'pitch_name', 3)}
              ${buildSplitSqlFilter(splitExpr, 4)}
            GROUP BY point_key
          )
          SELECT point_key, primary_value, overlay_a, overlay_b
          FROM grouped
          ORDER BY point_key DESC
          LIMIT $5
        `,
        [context.pitcherName, pitcherId, pitchType, split, getPitcherWindowPointLimit(window)]
      );
      if (!rows.length) {
        throw new Error(`No ${view} trend points were available for ${context.pitcherName}.`);
      }
      const ordered = [...rows].reverse();
      return {
        ...context,
        chartType: 'line',
        view,
        window,
        pitchType,
        split,
        title: `${pitcherTrendTitle(view)} - ${context.pitcherName}`,
        subtitle: `${context.team ?? 'N/A'} | ${windowLabel(window)}${pitchType ? ` | ${pitchType}` : ''}${split !== 'overall' ? ` | ${splitLabel(split)}` : ''}`,
        points: ordered.map((row) => ({ label: String(row.point_key), value: numberOrNull(row.primary_value) ?? 0 })),
        primaryLabel: pitcherTrendPrimaryLabel(view),
        overlays: [
          { label: pitcherTrendOverlayLabel(view, 0), values: ordered.map((row) => formatTrendOverlayValue(view, 0, row.overlay_a)), color: '#93c5fd' },
          { label: pitcherTrendOverlayLabel(view, 1), values: ordered.map((row) => formatTrendOverlayValue(view, 1, row.overlay_b)), color: '#c084fc' },
        ],
        read: pitcherTrendRead(view, ordered, pitchType),
      };
    });
  }

  async getPitcherArsenalChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-arsenal-suite', options), async () => {
      const context = await this.resolvePitcherSuiteContext(options.pitcher, options);
      const view = String(options.view ?? 'shape');
      const window = String(options.window ?? 'last_5');
      const split = String(options.split ?? 'overall');
      const pitchType = options.pitchType ?? options.pitch_type ?? null;

      if (['shape', 'movement'].includes(view)) {
        const rows = await this.queryCockroachRows(
          `
            SELECT pitch_type, usage_rate, avg_velocity, avg_spin_rate, avg_extension, avg_pfx_x, avg_pfx_z, avg_spin_axis
            FROM public.pitcher_arsenal_profiles
            WHERE pitcher::text = $1
              AND ($2::text IS NULL OR UPPER(pitch_type) = UPPER($2))
            ORDER BY usage_rate DESC NULLS LAST, sample_size DESC NULLS LAST
            LIMIT 6
          `,
          [context.pitcherId, pitchType]
        );
        if (!rows.length) {
          throw new Error(`No arsenal shape profile was available for ${context.pitcherName}.`);
        }
        return {
          ...context,
          view,
          window,
          pitchType,
          title: `${pitcherArsenalTitle(view)} - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | ${windowLabel(window)}`,
          columns: view === 'shape'
            ? [
                { key: 'usage_rate', label: 'Usage', type: 'pct' },
                { key: 'avg_velocity', label: 'Velo' },
                { key: 'avg_spin_rate', label: 'Spin' },
                { key: 'avg_pfx_z', label: 'Move Z' },
                { key: 'avg_pfx_x', label: 'Move X' },
              ]
            : [
                { key: 'usage_rate', label: 'Usage', type: 'pct' },
                { key: 'avg_extension', label: 'Ext' },
                { key: 'avg_pfx_x', label: 'Move X' },
                { key: 'avg_pfx_z', label: 'Move Z' },
                { key: 'avg_spin_axis', label: 'Axis' },
              ],
          rows: rows.map((row) => ({ label: row.pitch_type, ...row })),
          read: `The arsenal card highlights ${rows[0]?.pitch_type ?? 'the primary pitch'} as the backbone of ${context.pitcherName}'s current shape profile.`,
        };
      }

      const rows = await this.queryCockroachRows(
        `
          WITH latest_date AS (
            SELECT MAX(slate_date)::date AS slate_date
            FROM public.pitcher_arsenal_snapshots
            WHERE LOWER(pitcher_name) = LOWER($1)
          )
          SELECT slate_date::date AS slate_date, pitch_name, batter_side_key, usage_pct, swstr_pct, hard_hit_pct, avg_release_speed, avg_spin_rate, xwoba_con
          FROM public.pitcher_arsenal_snapshots
          WHERE LOWER(pitcher_name) = LOWER($1)
            AND slate_date::date = (SELECT slate_date FROM latest_date)
            AND ($2::text IS NULL OR LOWER(batter_side_key) = LOWER($2))
            AND ($3::text IS NULL OR UPPER(pitch_name) = UPPER($3))
          ORDER BY usage_pct DESC NULLS LAST
        `,
        [context.pitcherName, split === 'overall' ? null : split, pitchType]
      );
      if (!rows.length) {
        throw new Error(`No arsenal snapshot rows were available for ${context.pitcherName}.`);
      }

      if (view === 'evolution') {
        const evolutionRows = await this.queryCockroachRows(
          `
            SELECT
              pitch_name,
              MIN(slate_date::date) AS first_date,
              MAX(slate_date::date) AS last_date,
              AVG(CASE WHEN slate_date::date = (SELECT MIN(slate_date::date) FROM public.pitcher_arsenal_snapshots WHERE LOWER(pitcher_name) = LOWER($1)) THEN usage_pct END) AS early_usage_pct,
              AVG(CASE WHEN slate_date::date = (SELECT MAX(slate_date::date) FROM public.pitcher_arsenal_snapshots WHERE LOWER(pitcher_name) = LOWER($1)) THEN usage_pct END) AS latest_usage_pct,
              AVG(CASE WHEN slate_date::date = (SELECT MAX(slate_date::date) FROM public.pitcher_arsenal_snapshots WHERE LOWER(pitcher_name) = LOWER($1)) THEN avg_spin_rate END) AS latest_spin_rate
            FROM public.pitcher_arsenal_snapshots
            WHERE LOWER(pitcher_name) = LOWER($1)
            GROUP BY pitch_name
            ORDER BY latest_usage_pct DESC NULLS LAST
            LIMIT 6
          `,
          [context.pitcherName]
        );
        return {
          ...context,
          view,
          window,
          title: `${pitcherArsenalTitle(view)} - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | season evolution`,
          columns: [
            { key: 'early_usage_pct', label: 'Early', type: 'pct' },
            { key: 'latest_usage_pct', label: 'Latest', type: 'pct' },
            { key: 'usage_delta', label: 'Delta', type: 'pct_signed' },
            { key: 'latest_spin_rate', label: 'Spin' },
          ],
          rows: evolutionRows.map((row) => ({
            label: row.pitch_name,
            ...row,
            usage_delta: (numberOrNull(row.latest_usage_pct) ?? 0) - (numberOrNull(row.early_usage_pct) ?? 0),
          })),
          read: `${context.pitcherName}'s pitch mix evolution shows where usage has moved most since the start of 2026.`,
        };
      }

      if (view === 'platoon') {
        const platoonRows = await this.queryCockroachRows(
          `
            SELECT
              pitch_name,
              AVG(CASE WHEN LOWER(batter_side_key) = 'vs_lhb' THEN usage_pct END) AS usage_vs_lhb,
              AVG(CASE WHEN LOWER(batter_side_key) = 'vs_rhb' THEN usage_pct END) AS usage_vs_rhb,
              AVG(CASE WHEN LOWER(batter_side_key) = 'vs_lhb' THEN swstr_pct END) AS swstr_vs_lhb,
              AVG(CASE WHEN LOWER(batter_side_key) = 'vs_rhb' THEN swstr_pct END) AS swstr_vs_rhb
            FROM public.pitcher_arsenal_snapshots
            WHERE LOWER(pitcher_name) = LOWER($1)
            GROUP BY pitch_name
            ORDER BY GREATEST(COALESCE(AVG(usage_pct), 0), COALESCE(AVG(swstr_pct), 0)) DESC
            LIMIT 6
          `,
          [context.pitcherName]
        );
        return {
          ...context,
          view,
          window,
          title: `${pitcherArsenalTitle(view)} - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | platoon split`,
          columns: [
            { key: 'usage_vs_lhb', label: 'Vs LHB', type: 'pct' },
            { key: 'usage_vs_rhb', label: 'Vs RHB', type: 'pct' },
            { key: 'swstr_vs_lhb', label: 'Whiff L', type: 'pct' },
            { key: 'swstr_vs_rhb', label: 'Whiff R', type: 'pct' },
          ],
          rows: platoonRows.map((row) => ({ label: row.pitch_name, ...row })),
          read: `The platoon view shows how ${context.pitcherName} changes usage and whiff shape by hitter handedness.`,
        };
      }

      return {
        ...context,
        view,
        window,
        split,
        pitchType,
        title: `${pitcherArsenalTitle(view)} - ${context.pitcherName}`,
        subtitle: `${context.team ?? 'N/A'} | ${splitLabel(split)}${pitchType ? ` | ${pitchType}` : ''}`,
        columns: view === 'usage'
          ? [
              { key: 'usage_pct', label: 'Usage', type: 'pct' },
              { key: 'avg_release_speed', label: 'Velo' },
              { key: 'swstr_pct', label: 'Whiff', type: 'pct' },
              { key: 'hard_hit_pct', label: 'HH', type: 'pct' },
            ]
          : [
              { key: 'usage_pct', label: 'Usage', type: 'pct' },
              { key: 'swstr_pct', label: 'Whiff', type: 'pct' },
              { key: 'hard_hit_pct', label: 'HH', type: 'pct' },
              { key: 'xwoba_con', label: 'xwOBAcon', type: 'decimal' },
            ],
        rows: rows.slice(0, 6).map((row) => ({ label: row.pitch_name, ...row })),
        read: `${context.pitcherName}'s ${view === 'usage' ? 'usage tree' : 'pitch outcomes'} are led by ${rows[0]?.pitch_name ?? 'the primary offering'}.`,
      };
    });
  }

  async getPitcherLocationChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-location-suite', options), async () => {
      const context = await this.resolvePitcherSuiteContext(options.pitcher, options);
      const view = String(options.view ?? 'heatmap');
      const split = String(options.split ?? 'overall');
      const pitchType = options.pitchType ?? options.pitch_type ?? null;
      const countBucket = String(options.countBucket ?? options.count_bucket ?? 'all');
      const pitcherId = context.pitcherId ?? null;
      const rows = await this.queryCockroachRows(
        `
          SELECT plate_x, plate_z, zone, pitch_type, pitch_name, stand, balls, strikes, description, events, estimated_woba_using_speedangle
          FROM public.live_pitch_mix_2026
          WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
            ${buildPitchTypeSqlFilter('pitch_type', 'pitch_name', 3)}
            ${buildSplitSqlFilter('stand', 4)}
          ORDER BY game_date::date DESC, pitch_number DESC
          LIMIT 4000
        `,
        [context.pitcherName, pitcherId, pitchType, split]
      );
      if (!rows.length) {
        throw new Error(`No location rows were available for ${context.pitcherName}.`);
      }
      const filtered = rows.filter((row) => matchesCountBucket(row, countBucket));
      if (!filtered.length) {
        throw new Error(`No location rows matched the ${countBucketLabel(countBucket).toLowerCase()} filter for ${context.pitcherName}.`);
      }
      if (view === 'bypitch') {
        const plot = buildPitchLocationPlot(filtered);
        if (!plot.points.length) {
          throw new Error(`No pitch plot points were available for ${context.pitcherName}.`);
        }
        return {
          ...context,
          view,
          split,
          pitchType,
          countBucket,
          title: `${pitcherLocationTitle(view)} - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | ${splitLabel(split)} | ${countBucketLabel(countBucket)}${pitchType ? ` | ${pitchType}` : ''}`,
          sampleSize: filtered.length,
          plotPoints: plot.points,
          pitchBreakdown: plot.breakdown,
          read: pitcherLocationRead(view),
        };
      }
      const cells = buildPitcherLocationCells(filtered, view);
      const bestCell = [...cells].sort((left, right) => compareNullableDescending(left.overlayValue, right.overlayValue))[0];
      return {
        ...context,
        view,
        split,
        pitchType,
        countBucket,
        title: `${pitcherLocationTitle(view)} - ${context.pitcherName}`,
        subtitle: `${context.team ?? 'N/A'} | ${splitLabel(split)} | ${countBucketLabel(countBucket)}${pitchType ? ` | ${pitchType}` : ''}`,
        cells,
        sampleSize: filtered.length,
        metricConfig: pitcherLocationMetricConfig(view),
        bestOverlay: bestCell ? `Zone ${bestCell.zone} at ${(numberOrNull(bestCell.overlayValue) ?? 0).toFixed(0)} ${locationMetricSuffix(view)}` : 'No clear hot zone',
        shapeSummary: buildPitcherLocationSummary(cells, view),
        read: pitcherLocationRead(view),
      };
    });
  }

  async getPitcherApproachChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-approach-suite', options), async () => {
      const context = await this.resolvePitcherSuiteContext(options.pitcher, options);
      const view = String(options.view ?? 'count_usage');
      const split = String(options.split ?? 'overall');
      const pitchType = options.pitchType ?? options.pitch_type ?? null;
      const pitcherId = context.pitcherId ?? null;
      const rows = await this.queryCockroachRows(
        `
          SELECT pitch_name, balls, strikes, description, stand
          FROM public.live_pitch_mix_2026
          WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
            ${buildPitchTypeSqlFilter('NULL', 'pitch_name', 3)}
            ${buildSplitSqlFilter('stand', 4)}
          ORDER BY game_date::date DESC, pitch_number DESC
          LIMIT 4000
        `,
        [context.pitcherName, pitcherId, pitchType, split]
      );
      if (!rows.length) {
        throw new Error(`No approach rows were available for ${context.pitcherName}.`);
      }

      const grouped = buildPitcherApproachDatasets(rows, view);
      if (!grouped.datasets.length) {
        throw new Error(`No ${view.replaceAll('_', ' ')} rows were available for ${context.pitcherName}.`);
      }
      return {
        ...context,
        view,
        split,
        pitchType,
        title: `${pitcherApproachTitle(view)} - ${context.pitcherName}`,
        subtitle: `${context.team ?? 'N/A'} | ${splitLabel(split)}${pitchType ? ` | ${pitchType}` : ''}`,
        labels: grouped.labels,
        datasets: grouped.datasets,
        sampleSize: rows.length,
        read: grouped.read,
      };
    });
  }

  async getPitcherCompareChartData(options = {}) {
    return this.getCachedChartValue(this.buildChartCacheKey('pitcher-compare-suite', options), async () => {
      const context = await this.resolvePitcherSuiteContext(options.pitcher, options);
      const view = String(options.view ?? 'current_vs_career');
      const window = String(options.window ?? 'last_5');
      const pitcherId = context.pitcherId ?? null;

      if (view === 'risk_reward') {
        const rows = await this.queryCockroachRows(
          `
            SELECT slate_date::date AS point_date, strikeout_score, hard_hit_pct, xwoba, pitcher_score
            FROM public.pitcher_model_snapshots
            WHERE LOWER(pitcher_name) = LOWER($1)
            ORDER BY slate_date::date DESC
            LIMIT $2
          `,
          [context.pitcherName, getPitcherWindowPointLimit(window)]
        );
        if (!rows.length) {
          throw new Error(`No risk/reward snapshot points were available for ${context.pitcherName}.`);
        }
        return {
          ...context,
          chartType: 'scatter',
          view,
          window,
          title: `Risk Reward - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | ${windowLabel(window)}`,
          points: rows.map((row) => ({
            x: numberOrNull(row.hard_hit_pct) ?? 0,
            y: numberOrNull(row.strikeout_score) ?? 0,
            label: formatDateLabel(row.point_date),
            highlight: false,
          })),
          read: `Upper-left points pair stronger strikeout upside with lower hard-hit risk across recent snapshot dates.`,
        };
      }

      const currentRows = await this.queryCockroachRows(
        `
          SELECT AVG(release_speed) AS avg_release_speed, AVG(release_spin_rate) AS avg_release_spin_rate, AVG(release_extension) AS avg_release_extension, AVG(pfx_x) AS avg_pfx_x, AVG(pfx_z) AS avg_pfx_z
          FROM public.live_pitch_mix_2026
          WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
        `,
        [context.pitcherName, pitcherId]
      );
      const baselineRows = await this.queryCockroachRows(
        `
          SELECT AVG(release_speed) AS avg_release_speed, AVG(release_spin_rate) AS avg_release_spin_rate, AVG(release_extension) AS avg_release_extension, AVG(pfx_x) AS avg_pfx_x, AVG(pfx_z) AS avg_pfx_z
          FROM public.shared_pitcher_baseline_event_rows
          WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
        `,
        [context.pitcherName, pitcherId]
      );
      const current = currentRows[0] ?? {};
      const baseline = baselineRows[0] ?? {};

      if (view === 'year_over_year') {
        const seasonRows = await this.queryCockroachRows(
          `
            SELECT source_season::text AS season_label, AVG(release_speed) AS avg_release_speed, AVG(release_spin_rate) AS avg_release_spin_rate, AVG(release_extension) AS avg_release_extension
            FROM public.shared_pitcher_baseline_event_rows
            WHERE ${buildPitcherIdentitySql('pitcher_name', 'pitcher', 1, 2)}
            GROUP BY source_season
            ORDER BY source_season DESC
            LIMIT 6
          `,
          [context.pitcherName, pitcherId]
        );
        return {
          ...context,
          chartType: 'compare',
          view,
          window,
          title: `Year Over Year - ${context.pitcherName}`,
          subtitle: `${context.team ?? 'N/A'} | arsenal evolution`,
          compareLabel: 'Season',
          baselineLabel: 'Metrics',
          rows: seasonRows.map((row) => ({
            label: row.season_label,
            currentValue: formatMetricValue(row.avg_release_speed),
            baselineValue: `${formatMetricValue(row.avg_release_spin_rate)} spin | ${formatMetricValue(row.avg_release_extension)} ext`,
          })),
          read: `${context.pitcherName}'s year-over-year card shows how velocity, spin, and extension have changed season to season.`,
        };
      }

      const baselineLabel = view === 'recent_vs_baseline' ? 'Baseline' : 'Career';
      return {
        ...context,
        chartType: 'compare',
        view,
        window,
        title: `${pitcherCompareTitle(view)} - ${context.pitcherName}`,
        subtitle: `${context.team ?? 'N/A'} | ${windowLabel(window)}`,
        compareLabel: 'Current',
        baselineLabel,
        rows: [
          { label: 'Velocity', currentValue: formatMetricValue(current.avg_release_speed), baselineValue: formatMetricValue(baseline.avg_release_speed) },
          { label: 'Spin', currentValue: formatMetricValue(current.avg_release_spin_rate), baselineValue: formatMetricValue(baseline.avg_release_spin_rate) },
          { label: 'Extension', currentValue: formatMetricValue(current.avg_release_extension), baselineValue: formatMetricValue(baseline.avg_release_extension) },
          { label: 'Move X', currentValue: formatMetricValue(current.avg_pfx_x), baselineValue: formatMetricValue(baseline.avg_pfx_x) },
          { label: 'Move Z', currentValue: formatMetricValue(current.avg_pfx_z), baselineValue: formatMetricValue(baseline.avg_pfx_z) },
        ],
        read: `This compare card shows where ${context.pitcherName}'s current pitch traits sit versus ${baselineLabel.toLowerCase()}.`,
      };
    });
  }

  async buildHostedTrendPoints({ date, window, kind, playerName }) {
    const requestedWindow = Math.max(3, Math.min(14, Number(window ?? 7)));
    if (!this.hosted?.isConfigured?.()) {
      throw new Error('Trend charts require hosted artifacts.');
    }

    const targetDate = parseDateOrToday(date);
    const points = [];
    for (let offset = 0; offset < requestedWindow * 4 && points.length < requestedWindow; offset += 1) {
      const candidate = addDays(targetDate, -offset);
      try {
        if (kind === 'hitter') {
          const [slateRows, hitterRows, pitcherRows, rosterRows] = await Promise.all([
            this.hosted.readDailyFile(candidate, 'slate.parquet', SLATE_COLUMNS),
            this.hosted.readDailyFile(candidate, 'daily_hitter_metrics.parquet', HITTER_COLUMNS),
            this.hosted.readDailyFile(candidate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
            this.hosted.readDailyFile(candidate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
          ]);
          const rosterLookup = buildRosterLookup(rosterRows);
          const pitcherLookup = new Map(
            pitcherRows
              .filter((row) => keepRowForDefaults(row))
              .map((row) => [String(row.pitcher_id ?? row.player_id ?? ''), row])
          );
          const slateLookup = mapSlateTeams(slateRows);
          for (const [team, slate] of slateLookup.entries()) {
            const pitcher = pitcherLookup.get(String(slate.opposingPitcherId ?? ''));
            if (pitcher?.p_throws) {
              slateLookup.set(team, { ...slate, opposingPitcherHand: pitcher.p_throws });
            }
          }

          const preparedRows = hitterRows
            .filter((row) => keepRowForDefaults(row))
            .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }));
          const match = findBestPlayerMatch(preparedRows, 'hitter_name', playerName);
          if (!match) {
            continue;
          }
          points.push({
            label: formatDateLabel(candidate),
            matchup_score: numberOrNull(match.matchup_score) ?? 0,
            xwoba: numberOrNull(match.xwoba) ?? 0,
            barrel_pct: (Math.abs(numberOrNull(match.barrel_bip_pct) ?? 0) <= 1 ? (numberOrNull(match.barrel_bip_pct) ?? 0) * 100 : (numberOrNull(match.barrel_bip_pct) ?? 0)),
          });
          continue;
        }

        const pitcherRows = await this.hosted.readDailyFile(candidate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS);
        const match = findBestPlayerMatch(
          pitcherRows.filter((row) => keepRowForDefaults(row)),
          'pitcher_name',
          playerName
        );
        if (!match) {
          continue;
        }
        points.push({
          label: formatDateLabel(candidate),
          strikeout_score: numberOrNull(match.strikeout_score) ?? 0,
          csw_pct: (Math.abs(numberOrNull(match.csw_pct) ?? 0) <= 1 ? (numberOrNull(match.csw_pct) ?? 0) * 100 : (numberOrNull(match.csw_pct) ?? 0)),
          swstr_pct: (Math.abs(numberOrNull(match.swstr_pct) ?? 0) <= 1 ? (numberOrNull(match.swstr_pct) ?? 0) * 100 : (numberOrNull(match.swstr_pct) ?? 0)),
        });
      } catch {
        continue;
      }
    }

    if (!points.length) {
      throw new Error(`No recent trend points were available for ${playerName}.`);
    }

    return points.reverse();
  }

  async getFastHitterChartContext(playerName, options = {}) {
    if (!this.hosted?.isConfigured?.()) {
      const fallback = await this.getPlayerContext({
        ...options,
        player: playerName,
        playerType: 'hitter',
      });
      if (fallback.playerType !== 'hitter') {
        throw new Error(`No hitter matchup profile matched "${playerName}".`);
      }
      return fallback;
    }

    const targetDate = parseDateOrToday(options.date);
    const resolvedDate = await this.hosted.getLatestAvailableDate(targetDate);
    if (!resolvedDate) {
      throw new Error('No hosted hitter slate was available in the fallback window.');
    }

    const cacheKey = this.buildChartCacheKey('hitter-chart-base', { date: resolvedDate, player: playerName });
    const base = await this.getCachedChartValue(cacheKey, async () => {
      const [slateRows, hitterRows, pitcherRows, exclusionRows, rosterRows, batterZoneRows, pitcherZoneRows] = await Promise.all([
        this.hosted.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'daily_hitter_metrics.parquet', HITTER_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'hitter_pitcher_exclusions.parquet', EXCLUSION_COLUMNS).catch(() => []),
        this.hosted.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
        this.hosted.readDailyFile(resolvedDate, 'daily_batter_zone_profiles.parquet', BATTER_ZONE_COLUMNS).catch(() => []),
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_zone_profiles.parquet', PITCHER_ZONE_COLUMNS).catch(() => []),
      ]);
      const rosterLookup = buildRosterLookup(rosterRows);
      const rosterIdsByTeam = buildRosterIdsByTeam(rosterRows);
      const exclusionSet = buildExclusionSet(exclusionRows);
      const pitcherLookup = new Map(
        pitcherRows
          .filter((row) => keepRowForDefaults(row))
          .map((row) => [String(row.pitcher_id ?? row.player_id ?? ''), row])
      );
      const slateLookup = mapSlateTeams(slateRows);
      for (const [team, slate] of slateLookup.entries()) {
        const pitcher = pitcherLookup.get(String(slate.opposingPitcherId ?? ''));
        if (pitcher?.p_throws) {
          slateLookup.set(team, { ...slate, opposingPitcherHand: pitcher.p_throws });
        }
      }

      const preparedRows = hitterRows
        .filter((row) => keepRowForDefaults(row))
        .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }));

      const matchedPlayer = findBestPlayerMatch(preparedRows, 'hitter_name', playerName);
      if (!matchedPlayer) {
        return {
          resolvedDate,
          rows: [],
          scoredRows: [],
          batterZoneRows,
          pitcherZoneRows,
        };
      }

      const normalizedTeam = normalizeTeam(matchedPlayer.team);
      const effectiveSplit = matchedPlayer.opposing_pitcher_hand === 'R'
        ? 'vs_rhp'
        : matchedPlayer.opposing_pitcher_hand === 'L'
          ? 'vs_lhp'
          : DEFAULT_SPLIT_KEY;

      const teamRows = hitterRows
        .filter((row) => normalizeTeam(row.team) === normalizedTeam)
        .filter((row) => String(row.split_key ?? DEFAULT_SPLIT_KEY) === effectiveSplit)
        .filter((row) => String(row.recent_window ?? DEFAULT_RECENT_WINDOW) === DEFAULT_RECENT_WINDOW)
        .filter((row) => String(row.weighted_mode ?? DEFAULT_WEIGHTED_MODE) === DEFAULT_WEIGHTED_MODE)
        .filter((row) => {
          const rosterPlayerIds = rosterIdsByTeam.get(normalizedTeam) ?? null;
          return !rosterPlayerIds || rosterPlayerIds.has(String(row.batter ?? row.player_id ?? '').trim());
        })
        .filter((row) => !exclusionSet.has(String(row.batter ?? row.player_id ?? '').trim()))
        .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'hitter' }));

      return {
        resolvedDate,
        rows: preparedRows,
        scoredRows: addHitterMatchupScore(teamRows, batterZoneRows, pitcherZoneRows),
      };
    });

    const hitterMatch = findBestPlayerMatch(base.scoredRows ?? [], 'hitter_name', playerName)
      ?? findBestPlayerMatch(base.rows ?? [], 'hitter_name', playerName);
    if (!hitterMatch) {
      throw new Error(`No hitter matchup profile matched "${playerName}".`);
    }

    return {
      source: 'hosted',
      resolvedDate,
      playerType: 'hitter',
      name: hitterMatch.hitter_name,
      team: hitterMatch.team ?? null,
      opponentTeam: hitterMatch.opponent_team ?? null,
      opposingPitcherName: hitterMatch.opposing_pitcher_name ?? null,
      hand: hitterMatch.opposing_pitcher_hand ?? null,
      overview: hitterMatch,
    };
  }

  async getFastPitcherChartContext(playerName, options = {}) {
    if (!this.hosted?.isConfigured?.()) {
      const fallback = await this.getPlayerContext({
        ...options,
        player: playerName,
        playerType: 'pitcher',
      });
      if (fallback.playerType !== 'pitcher') {
        throw new Error(`No pitcher matchup profile matched "${playerName}".`);
      }
      return fallback;
    }

    const targetDate = parseDateOrToday(options.date);
    const resolvedDate = await this.hosted.getLatestAvailableDate(targetDate);
    if (!resolvedDate) {
      throw new Error('No hosted pitcher slate was available in the fallback window.');
    }

    const cacheKey = this.buildChartCacheKey('pitcher-chart-base', { date: resolvedDate });
    const base = await this.getCachedChartValue(cacheKey, async () => {
      const [slateRows, pitcherRows, rosterRows] = await Promise.all([
        this.hosted.readDailyFile(resolvedDate, 'slate.parquet', SLATE_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'daily_pitcher_metrics.parquet', PITCHER_COLUMNS),
        this.hosted.readDailyFile(resolvedDate, 'rosters.parquet', ROSTER_COLUMNS).catch(() => []),
      ]);
      const slateLookup = mapSlateTeams(slateRows);
      const rosterLookup = buildRosterLookup(rosterRows);
      return pitcherRows
        .filter(keepRowForDefaults)
        .map((row) => withDefaults(row, slateLookup, { rosterLookup, playerType: 'pitcher' }));
    });

    const pitcherMatch = findBestPlayerMatch(base, 'pitcher_name', playerName);
    if (pitcherMatch) {
      return {
        source: 'hosted',
        resolvedDate,
        playerType: 'pitcher',
        name: pitcherMatch.pitcher_name,
        team: pitcherMatch.team ?? null,
        opponentTeam: pitcherMatch.opponent_team ?? null,
        hand: pitcherMatch.p_throws ?? null,
        overview: pitcherMatch,
      };
    }

    const broaderContext = await this.getPlayerContext({
      ...options,
      player: playerName,
      playerType: 'pitcher',
    });
    if (broaderContext.playerType !== 'pitcher') {
      throw new Error(`No pitcher matchup profile matched "${playerName}".`);
    }
    return broaderContext;
  }

  async enrichHostedHitters(result, options = {}, methodName = 'getTopHitters') {
    if (!result?.rows?.length || !this.fallback?.getHitterSnapshotRows) {
      return result;
    }

    try {
      const snapshotRows = await this.fallback.getHitterSnapshotRows({
        date: result.resolvedDate ?? options.date,
        team: options.team ?? null,
      });
      const snapshotLookup = new Map(
        snapshotRows.map((row) => [`${normalizeText(row.hitter_name)}|${normalizeTeam(row.team)}`, row])
      );

      const enrichedRows = result.rows.map((row) => {
        const snapshot = snapshotLookup.get(`${normalizeText(row.hitter_name)}|${normalizeTeam(row.team)}`);
        return {
          ...row,
          matchup_score: row.matchup_score ?? snapshot?.matchup_score ?? null,
          ceiling_score: row.ceiling_score ?? snapshot?.ceiling_score ?? null,
          zone_fit_score: row.zone_fit_score ?? snapshot?.zone_fit_score ?? null,
          likely_starter_score: row.likely_starter_score ?? snapshot?.likely_starter_score ?? null,
        };
      });

      const sortedRows = sortHitters(enrichedRows);
      const boardRows = methodName === 'getBestMatchups'
        ? buildBestMatchupBoardRows(sortedRows)
        : sortedRows;
      const limit = methodName === 'getBestMatchups'
        ? limitOrDefault(options.limit ?? (options.team ? 3 : 12), 12)
        : limitOrDefault(options.limit);
      return {
        ...result,
        rows: boardRows.slice(0, limit),
      };
    } catch (error) {
      this.logger?.warn?.('Hosted hitter enrichment failed', { error: error.message, options });
      return result;
    }
  }
}

async function defaultFetchText(url, targetDate) {
  const requestUrl = new URL(url);
  requestUrl.searchParams.set('date', parseDateOrToday(targetDate));
  try {
    const response = await fetch(requestUrl, {
      headers: {
        'User-Agent': 'KasperMLB/1.0',
      },
      signal: AbortSignal.timeout(10_000),
    });
    if (!response.ok) {
      throw new Error(`Rotowire request failed with status ${response.status}`);
    }
    return response.text();
  } catch (error) {
    throw new Error(`Rotowire request failed: ${error.message}`);
  }
}

export async function readParquetFromUrl(url, columns) {
  try {
    const file = await asyncBufferFromUrl({ url });
    return parquetReadObjects({
      file,
      columns,
    });
  } catch (error) {
    throw new Error(`Hosted parquet fetch failed: ${error.message}`);
  }
}