/**
 * CLV Resolver Worker
 *
 * Runs every 2 hours. For each unresolved pick in rm_pick_clv:
 * 1. Capture closing line from last snapshot before game start
 * 2. Calculate directional CLV
 * 3. Resolve W/L from final scores
 * 4. Refresh materialized views
 *
 * Usage: npx tsx src/workers/clv-resolver.ts
 */

import dotenv from 'dotenv';
import path from 'path';
dotenv.config({ path: path.join(__dirname, '../../.env') });

import pool from '../db';
import { getTeamAbbr } from '../lib/team-abbreviations';
import { normalizePlayerPropMarketStat } from '../services/player-prop-market-registry';
import { normalizeClvPropStat } from '../services/player-prop-clv-profile';
import { extractEspnPlayerStat, normalizeResolverPlayerName, supportsEspnPlayerStat } from './clv-player-stats';

const ESPN_LEAGUES: Record<string, { sport: string; league: string; groups?: string }> = {
  nba: { sport: 'basketball', league: 'nba' },
  nhl: { sport: 'hockey', league: 'nhl' },
  mlb: { sport: 'baseball', league: 'mlb' },
  ncaab: { sport: 'basketball', league: 'mens-college-basketball', groups: '50' },
  epl: { sport: 'soccer', league: 'eng.1' },
  la_liga: { sport: 'soccer', league: 'esp.1' },
  serie_a: { sport: 'soccer', league: 'ita.1' },
  bundesliga: { sport: 'soccer', league: 'ger.1' },
  ligue_1: { sport: 'soccer', league: 'fra.1' },
  champions_league: { sport: 'soccer', league: 'uefa.champions' },
};

type EspnScore = {
  id: string;
  homeTeam: string;
  awayTeam: string;
  homeScore: number;
  awayScore: number;
};

const espnScoreCache = new Map<string, Promise<EspnScore[]>>();
const espnSummaryCache = new Map<string, Promise<any | null>>();
const ESPN_FETCH_TIMEOUT_MS = 12000;

function normalizeStatKey(stat: string | null | undefined, league?: string | null): string {
  return normalizeClvPropStat(stat, league);
}

export function normalizePlayerPropLineType(stat: string | null | undefined, league?: string | null): string {
  const raw = (stat || '').trim();
  if (!raw) return '';

  const safeLeague = (league || '').trim().toLowerCase();
  const canonical = normalizePlayerPropMarketStat(safeLeague, raw) || raw;
  const normalized = canonical.toLowerCase().replace(/[\s-]+/g, '_');

  if (safeLeague === 'nhl') {
    if (['shots', 'shots_on_goal', 'shots_ongoal', 'sog'].includes(normalized)) return 'shots_onGoal';
    if (['saves', 'goalie_saves'].includes(normalized)) return 'goalie_saves';
  }

  if (safeLeague === 'mlb') {
    if (normalized === 'stolenbases') return 'batting_stolenBases';
  }

  if (['three_pointers_made', 'threepointersmade', 'threes'].includes(normalized)) {
    return 'threePointersMade';
  }

  if (['shots_on_target', 'shotsontarget'].includes(normalized)) {
    return 'shots_onTarget';
  }

  return canonical;
}

function sameNullableNumber(a: unknown, b: unknown): boolean {
  if (a == null && b == null) return true;
  if (a == null || b == null) return false;
  const left = Number(a);
  const right = Number(b);
  if (!Number.isFinite(left) || !Number.isFinite(right)) return false;
  return Math.abs(left - right) < 1e-9;
}

function normalizePeriodStatus(status: string | null | undefined): string {
  return (status || '').trim().toLowerCase();
}

function normForMatch(name: string | null | undefined): string {
  return (name || '')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/&/g, 'and')
    .replace(/['\.\-,;:!?()]/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}

function fuzzyTeamMatch(a: string | null | undefined, b: string | null | undefined): boolean {
  const na = normForMatch(a);
  const nb = normForMatch(b);
  if (!na || !nb) return false;
  if (na === nb) return true;
  if (na.includes(nb) || nb.includes(na)) return true;
  const wordsA = na.split(' ').slice(0, 2).join(' ');
  const wordsB = nb.split(' ').slice(0, 2).join(' ');
  if (wordsA.length >= 6 && wordsA === wordsB) return true;
  const lastA = na.split(' ').pop() || '';
  const lastB = nb.split(' ').pop() || '';
  if (lastA.length > 3 && lastA === lastB) {
    const firstA = na.split(' ')[0];
    const firstB = nb.split(' ')[0];
    if (firstA === firstB) return true;
  }
  return false;
}

function buildTeamVariants(team: string | null | undefined): string[] {
  const variants = new Set<string>();
  if (team) {
    variants.add(team);
    variants.add(team.trim());
  }
  const abbr = getTeamAbbr(team || null);
  if (abbr && abbr !== '???') variants.add(abbr);
  return [...variants].filter(Boolean);
}

function pickBestMatchedGame(games: any[], homeVariants: string[], awayVariants: string[]): any | null {
  let best: { game: any; score: number } | null = null;

  for (const game of games) {
    const sameOrientation = homeVariants.some(v => fuzzyTeamMatch(game.homeTeam, v))
      && awayVariants.some(v => fuzzyTeamMatch(game.awayTeam, v));
    const swappedOrientation = homeVariants.some(v => fuzzyTeamMatch(game.awayTeam, v))
      && awayVariants.some(v => fuzzyTeamMatch(game.homeTeam, v));
    if (!sameOrientation && !swappedOrientation) continue;

    const gameHomeNorm = normForMatch(game.homeTeam);
    const gameAwayNorm = normForMatch(game.awayTeam);
    const exactAbbrMatch = homeVariants.some(v => normForMatch(v) === gameHomeNorm || normForMatch(v) === gameAwayNorm)
      && awayVariants.some(v => normForMatch(v) === gameAwayNorm || normForMatch(v) === gameHomeNorm);
    const exactFullMatch = homeVariants.some(v => normForMatch(v) === gameHomeNorm)
      && awayVariants.some(v => normForMatch(v) === gameAwayNorm);

    let score = 1;
    if (sameOrientation) score += 2;
    if (exactAbbrMatch) score += 2;
    if (exactFullMatch) score += 2;

    if (!best || score > best.score) {
      best = { game, score };
    }
  }

  return best?.game || null;
}

function teamMatchScore(a: string, b: string): number {
  const na = normForMatch(a);
  const nb = normForMatch(b);
  if (!na || !nb) return 0;
  if (na === nb) return 1.0;
  const lastA = a.trim().split(/\s+/).pop()?.toLowerCase() || '';
  const lastB = b.trim().split(/\s+/).pop()?.toLowerCase() || '';
  if (lastA === lastB && lastA.length > 2) return 0.9;
  if (na.includes(nb) || nb.includes(na)) return 0.8;
  const wordsA = a.trim().split(/\s+/).slice(0, 2).join(' ').toLowerCase();
  const wordsB = b.trim().split(/\s+/).slice(0, 2).join(' ').toLowerCase();
  if (wordsA.length >= 6 && wordsA === wordsB) return 0.75;
  return 0;
}

function parseResolverBatchSize(raw: string | undefined): number {
  const parsed = parseInt(raw || '', 10);
  if (!Number.isFinite(parsed) || parsed <= 0) return 300;
  return Math.min(parsed, 5000);
}

function normalizePropSide(value: string | null | undefined): string {
  return (value || '').trim().toLowerCase();
}

function normalizePropPeriod(value: string | null | undefined): string {
  const normalized = (value || '').trim().toLowerCase();
  if (!normalized || normalized === 'full_game' || normalized === 'fullgame') return 'game';
  return normalized;
}

function playerNameMatchScore(playerName: string | null | undefined, rawText: string | null | undefined): number {
  const needle = normalizeResolverPlayerName(playerName);
  const haystack = normalizeResolverPlayerName(rawText);
  if (!needle || !haystack) return 0;
  if (haystack === needle) return 3;
  if (haystack.includes(needle)) return 2;
  const needleParts = needle.split(' ').filter(Boolean);
  if (needleParts.length >= 2 && needleParts.every((part) => haystack.includes(part))) return 1;
  return 0;
}

export function pickBestPlayerPropLineRow(rows: any[], pick: {
  player_name?: string | null;
  direction?: string | null;
  rec_line?: number | null;
}): any | null {
  const targetSide = normalizePropSide(pick.direction);

  const scored = rows
    .map((row) => {
      const rawPeriod = row.period ?? row.raw?.period ?? row.raw?.sportsgameodds?.periodID ?? null;
      const rawSide = row.side ?? row.raw?.side ?? row.raw?.sportsgameodds?.sideID ?? null;
      const nameScore = playerNameMatchScore(pick.player_name, row.raw_text ?? JSON.stringify(row.raw ?? {}));
      const period = normalizePropPeriod(rawPeriod);
      const side = normalizePropSide(rawSide);
      const lineValue = row.close_line != null ? Number(row.close_line) : null;
      const lineDelta = lineValue != null && pick.rec_line != null ? Math.abs(lineValue - Number(pick.rec_line)) : Number.POSITIVE_INFINITY;
      const snapshotAt = row.snapshot_at ? new Date(row.snapshot_at).getTime() : 0;

      return {
        row,
        nameScore,
        period,
        side,
        lineDelta,
        snapshotAt: Number.isFinite(snapshotAt) ? snapshotAt : 0,
        isClosingLine: Boolean(row.is_closing_line),
      };
    })
    .filter((candidate) => {
      if (candidate.nameScore <= 0) return false;
      if (candidate.period !== 'game') return false;
      if (targetSide && candidate.side && candidate.side !== targetSide) return false;
      return true;
    })
    .sort((a, b) => (
      b.nameScore - a.nameScore
      || a.lineDelta - b.lineDelta
      || Number(b.isClosingLine) - Number(a.isClosingLine)
      || b.snapshotAt - a.snapshotAt
    ));

  return scored[0]?.row || null;
}

async function fetchEspnSummary(league: string, eventId: string): Promise<any | null> {
  const cfg = ESPN_LEAGUES[league];
  if (!cfg || !eventId) return null;

  const cacheKey = `${league}:summary:${eventId}`;
  if (!espnSummaryCache.has(cacheKey)) {
    espnSummaryCache.set(cacheKey, (async () => {
      const url = `https://site.api.espn.com/apis/site/v2/sports/${cfg.sport}/${cfg.league}/summary?event=${encodeURIComponent(eventId)}`;
      try {
        const res = await fetch(url, { signal: AbortSignal.timeout(ESPN_FETCH_TIMEOUT_MS) });
        if (!res.ok) return null;
        return await res.json();
      } catch {
        return null;
      }
    })());
  }

  return espnSummaryCache.get(cacheKey)!;
}

async function persistEspnPlayerMetric(params: {
  league: string;
  gameDate: string;
  gameKey: string;
  playerExternalId: string;
  playerName: string;
  team: string | null;
  opponent: string | null;
  statKey: string;
  value: number;
}): Promise<void> {
  if (!params.playerExternalId || !params.statKey) return;

  const season = parseInt(params.gameDate.slice(0, 4), 10) || new Date(params.gameDate).getUTCFullYear();
  await pool.query(
    `INSERT INTO "PlayerGameMetric"
     (id, league, season, "gameKey", "gameDate", "playerExternalId", "playerName", position, team, opponent, "statKey", value, "createdAt")
     VALUES (
       nextval(pg_get_serial_sequence('"PlayerGameMetric"', 'id')),
       $1, $2, $3, $4::timestamp, $5, $6, NULL, $7, $8, $9, $10, NOW()
     )
     ON CONFLICT (league, season, "gameKey", "playerExternalId", "statKey")
     DO UPDATE SET
       "playerName" = EXCLUDED."playerName",
       team = COALESCE(EXCLUDED.team, "PlayerGameMetric".team),
       opponent = COALESCE(EXCLUDED.opponent, "PlayerGameMetric".opponent),
       value = EXCLUDED.value`,
    [
      params.league,
      season,
      params.gameKey,
      params.gameDate,
      params.playerExternalId,
      params.playerName,
      params.team,
      params.opponent,
      params.statKey,
      params.value,
    ],
  ).catch(() => undefined);
}

async function fetchEspnScoresForDate(league: string, dateStr: string): Promise<EspnScore[]> {
  const cfg = ESPN_LEAGUES[league];
  if (!cfg) return [];
  const cacheKey = `${league}:${dateStr}`;
  if (!espnScoreCache.has(cacheKey)) {
    espnScoreCache.set(cacheKey, (async () => {
      let url = `https://site.api.espn.com/apis/site/v2/sports/${cfg.sport}/${cfg.league}/scoreboard?dates=${dateStr}&limit=500`;
      if (cfg.groups) url += `&groups=${cfg.groups}`;
      try {
        const res = await fetch(url, { signal: AbortSignal.timeout(ESPN_FETCH_TIMEOUT_MS) });
        if (!res.ok) return [];
        const data = await res.json() as any;
        const scores: EspnScore[] = [];
        for (const event of data.events || []) {
          const comp = event.competitions?.[0];
          if (!comp) continue;
          const statusName = comp.status?.type?.name || '';
          if (statusName !== 'STATUS_FINAL' && statusName !== 'STATUS_FULL_TIME') continue;
          const teams = comp.competitors || [];
          const home = teams.find((t: any) => t.homeAway === 'home');
          const away = teams.find((t: any) => t.homeAway === 'away');
          if (!home || !away) continue;
          scores.push({
            id: String(event.id || comp.id || ''),
            homeTeam: home.team?.displayName || home.team?.name || '',
            awayTeam: away.team?.displayName || away.team?.name || '',
            homeScore: parseInt(home.score || '0', 10),
            awayScore: parseInt(away.score || '0', 10),
          });
        }
        return scores;
      } catch {
        return [];
      }
    })());
  }
  return espnScoreCache.get(cacheKey)!;
}

async function fetchEspnMatchedScore(league: string, startsAt: string | Date | null | undefined, homeTeam: string | null | undefined, awayTeam: string | null | undefined): Promise<EspnScore | null> {
  const cfg = ESPN_LEAGUES[league];
  if (!cfg || !startsAt) return null;
  const start = new Date(startsAt);
  const dates = [
    new Date(start.getTime() - 86400000),
    start,
    new Date(start.getTime() + 86400000),
  ];
  const homeVariants = buildTeamVariants(homeTeam);
  const awayVariants = buildTeamVariants(awayTeam);

  for (const date of dates) {
    const dateStr = date.toISOString().slice(0, 10).replace(/-/g, '');
    const scores = await fetchEspnScoresForDate(league, dateStr);
    let best: { score: EspnScore; match: number } | null = null;
    for (const score of scores) {
      const direct = Math.min(
        Math.max(...homeVariants.map(v => teamMatchScore(score.homeTeam, v)), 0),
        Math.max(...awayVariants.map(v => teamMatchScore(score.awayTeam, v)), 0),
      );
      const reversed = Math.min(
        Math.max(...homeVariants.map(v => teamMatchScore(score.awayTeam, v)), 0),
        Math.max(...awayVariants.map(v => teamMatchScore(score.homeTeam, v)), 0),
      );
      const match = Math.max(direct, reversed);
      if (match >= 0.8 && (!best || match > best.match)) {
        best = { score, match };
      }
    }
    if (best) return best.score;
  }

  return null;
}

async function queryPlayerPropLineCandidates(
  pick: any,
  propType: string,
  propEventTime: string | Date,
): Promise<any[]> {
  const side = normalizePropSide(pick.direction);
  if (!propType || !side) return [];

  const { rows } = await pool.query(`
    SELECT COALESCE("fdLine", "lineValue") AS close_line,
           COALESCE("fdOverOdds", "oddsAmerican") AS close_odds,
           raw,
           raw::text AS raw_text,
           COALESCE(raw->>'period', raw->'sportsgameodds'->>'periodID', 'game') AS period,
           LOWER(COALESCE(raw->>'side', raw->'sportsgameodds'->>'sideID', '')) AS side,
           COALESCE("snapshotAt", "updatedAt", "createdAt") AS snapshot_at,
           COALESCE("isClosingLine", false) AS is_closing_line
    FROM "PlayerPropLine"
    WHERE league = $1
      AND "propType" = $2
      AND COALESCE("marketScope", 'full_game') = 'full_game'
      AND DATE("gameStart") = DATE($3::timestamptz)
      AND LOWER(COALESCE(raw->>'side', raw->'sportsgameodds'->>'sideID', '')) = $4
      AND COALESCE(raw->>'period', raw->'sportsgameodds'->>'periodID', 'game') = 'game'
    ORDER BY COALESCE("isClosingLine", false) DESC,
             COALESCE("snapshotAt", "updatedAt", "createdAt") DESC NULLS LAST
    LIMIT 500
  `, [pick.league, propType, propEventTime, side]).catch(() => ({ rows: [] }));

  return rows;
}

async function findPlayerPropCloseLine(pick: any, propType: string, propEventTime: string | Date): Promise<{ closeLine: number | null; closeOdds: number | null }> {
  const candidates = await queryPlayerPropLineCandidates(pick, propType, propEventTime);
  const matched = pickBestPlayerPropLineRow(candidates, pick);
  if (!matched) return { closeLine: null, closeOdds: null };

  return {
    closeLine: matched.close_line != null ? parseFloat(matched.close_line) : null,
    closeOdds: matched.close_odds != null ? parseFloat(matched.close_odds) : null,
  };
}

async function findPlayerPropRecommendationOdds(pick: any, propType: string, propEventTime: string | Date): Promise<number | null> {
  if (pick.rec_odds != null) return parseFloat(pick.rec_odds);

  const candidates = (await queryPlayerPropLineCandidates(pick, propType, propEventTime))
    .filter((row) => {
      if (!row.snapshot_at || !pick.created_at) return true;
      const snapshotTime = new Date(row.snapshot_at).getTime();
      const createdTime = new Date(pick.created_at).getTime();
      return Number.isFinite(snapshotTime) && Number.isFinite(createdTime) && snapshotTime <= createdTime;
    });
  const matched = pickBestPlayerPropLineRow(candidates, pick);
  if (!matched) return null;
  return matched.close_odds != null ? parseFloat(matched.close_odds) : null;
}

async function findPlayerMetricValue(pick: any, statKey: string, propEventTime: string | Date): Promise<number | null> {
  const metricRows = await pool.query(`
    SELECT value
    FROM "PlayerGameMetric"
    WHERE league = $1
      AND "playerName" ILIKE $2
      AND "statKey" = $3
      AND "gameDate" = DATE($4::timestamptz)
    ORDER BY "gameDate" DESC, "createdAt" DESC, id DESC
    LIMIT 1
  `, [pick.league, pick.player_name, statKey, propEventTime]).catch(() => ({ rows: [] }));

  if (metricRows.rows.length > 0) {
    return parseFloat(metricRows.rows[0].value);
  }

  const normalizedNeedle = normalizeResolverPlayerName(pick.player_name);
  if (normalizedNeedle) {
    const candidateRows = await pool.query(`
      SELECT "playerName", value
      FROM "PlayerGameMetric"
      WHERE league = $1
        AND "statKey" = $2
        AND "gameDate" = DATE($3::timestamptz)
      LIMIT 250
    `, [pick.league, statKey, propEventTime]).catch(() => ({ rows: [] }));

    const matched = candidateRows.rows.find((row: any) => normalizeResolverPlayerName(row.playerName) === normalizedNeedle);
    if (matched) {
      return parseFloat(matched.value);
    }
  }

  if (!supportsEspnPlayerStat(pick.league, statKey)) return null;

  const espnEvent = await fetchEspnMatchedScore(
    pick.league,
    pick.starts_at || pick.created_at,
    pick.home_team,
    pick.away_team,
  );
  if (!espnEvent?.id) return null;

  const summary = await fetchEspnSummary(pick.league, espnEvent.id);
  if (!summary) return null;

  const match = extractEspnPlayerStat(summary, pick.league, pick.player_name, statKey);
  if (!match) return null;

  const gameDate = new Date(pick.starts_at || pick.created_at).toISOString().slice(0, 10);
  await persistEspnPlayerMetric({
    league: pick.league,
    gameDate,
    gameKey: espnEvent.id,
    playerExternalId: match.playerExternalId,
    playerName: match.playerName,
    team: match.team,
    opponent: match.opponent,
    statKey,
    value: match.value,
  });

  return match.value;
}

export async function resolveClv(options: {
  closePool?: boolean;
  refreshViews?: boolean;
  selection?: 'unresolved' | 'all_started';
  batchSize?: number;
  forceOverwrite?: boolean;
} = {}) {
  const {
    closePool = false,
    refreshViews = true,
    selection = 'unresolved',
    batchSize: batchSizeOverride,
    forceOverwrite = false,
  } = options;
  console.log(`[CLV Resolver] Starting at ${new Date().toISOString()}`);
  const batchSize = batchSizeOverride ?? parseResolverBatchSize(process.env.CLV_RESOLVER_BATCH_SIZE);

  const orphanCleanup = await pool.query(`
    DELETE FROM rm_pick_clv p
    WHERE p.created_at < NOW() - INTERVAL '3 days'
      AND NOT EXISTS (SELECT 1 FROM rm_events e WHERE e.event_id = p.event_id)
      AND NOT EXISTS (SELECT 1 FROM rm_forecast_cache fc WHERE fc.event_id = p.event_id)
    RETURNING p.id, p.event_id, p.league
  `).catch(() => ({ rows: [] }));
  if (orphanCleanup.rows.length > 0) {
    console.log(`[CLV Resolver] Removed ${orphanCleanup.rows.length} orphan CLV rows`);
  }

  // 1. Find unresolved picks where the game has started
  const selectionSql = selection === 'all_started'
    ? `
      SELECT p.*, e.starts_at, e.home_team, e.away_team
      FROM rm_pick_clv p
      LEFT JOIN rm_events e ON e.event_id = p.event_id
      WHERE COALESCE(e.starts_at, p.created_at) < NOW()
      ORDER BY
        CASE
          WHEN COALESCE(e.starts_at, p.created_at) >= NOW() - INTERVAL '7 days' THEN 0
          ELSE 1
        END ASC,
        COALESCE(e.starts_at, p.created_at) DESC,
        p.created_at DESC
      LIMIT $1
    `
    : `
      SELECT p.*, e.starts_at, e.home_team, e.away_team
      FROM rm_pick_clv p
      LEFT JOIN rm_events e ON e.event_id = p.event_id
      WHERE (
          p.close_line IS NULL
          OR p.close_odds IS NULL
          OR p.rec_odds IS NULL
          OR p.result IS NULL
          OR (p.clv_line IS NULL AND p.close_line IS NOT NULL AND p.rec_line IS NOT NULL)
        )
        AND (e.starts_at IS NOT NULL AND e.starts_at < NOW())
      ORDER BY
        CASE
          WHEN COALESCE(e.starts_at, p.created_at) >= NOW() - INTERVAL '7 days' THEN 0
          ELSE 1
        END ASC,
        COALESCE(e.starts_at, p.created_at) DESC,
        p.created_at DESC
      LIMIT $1
    `;

  const { rows: unresolvedPicks } = await pool.query(selectionSql, [batchSize]);

  console.log(`[CLV Resolver] ${unresolvedPicks.length} picks to process (selection=${selection})`);

  let resolved = 0;
  let errors = 0;

  for (const pick of unresolvedPicks) {
    try {
      let closeLine: number | null = null;
      let closeOdds: number | null = null;
      let recOdds: number | null = null;
      let result: string | null = null;

      if (pick.pick_type === 'player_prop') {
        const propEventTime = pick.starts_at || pick.created_at;
        const dbStat = normalizeStatKey(pick.prop_stat, pick.league);
        const linePropType = normalizePlayerPropLineType(pick.prop_stat, pick.league);
        const closing = await findPlayerPropCloseLine(pick, linePropType, propEventTime);
        closeLine = closing.closeLine;
        closeOdds = closing.closeOdds;
        recOdds = await findPlayerPropRecommendationOdds(pick, linePropType, propEventTime);

        const actual = await findPlayerMetricValue(pick, dbStat, propEventTime);
        if (actual != null) {
          if (pick.direction === 'over') {
            result = actual > pick.rec_line ? 'W' : (actual === pick.rec_line ? 'P' : 'L');
          } else {
            result = actual < pick.rec_line ? 'W' : (actual === pick.rec_line ? 'P' : 'L');
          }
        }

      } else {
        // Game-level picks: get closing odds from rm_events.
        const { rows: eventRows } = await pool.query(`
          SELECT spread, moneyline, total FROM rm_events
          WHERE event_id = $1
        `, [pick.event_id]).catch(() => ({ rows: [] }));

        if (eventRows.length > 0) {
          const ev = eventRows[0];
          if (pick.pick_type === 'game_spread') {
            const spreadData = typeof ev.spread === 'string' ? JSON.parse(ev.spread) : ev.spread;
            closeLine = pick.direction === 'home'
              ? spreadData?.home?.line
              : spreadData?.away?.line;
            closeOdds = pick.direction === 'home'
              ? spreadData?.home?.odds
              : spreadData?.away?.odds;
          } else if (pick.pick_type === 'game_moneyline') {
            const mlData = typeof ev.moneyline === 'string' ? JSON.parse(ev.moneyline) : ev.moneyline;
            closeLine = pick.direction === 'home' ? mlData?.home : mlData?.away;
            closeOdds = closeLine != null ? Math.round(closeLine) : null;
          } else if (pick.pick_type === 'game_total') {
            const totalData = typeof ev.total === 'string' ? JSON.parse(ev.total) : ev.total;
            closeLine = pick.direction === 'over'
              ? totalData?.over?.line
              : totalData?.under?.line;
            closeOdds = pick.direction === 'over'
              ? totalData?.over?.odds
              : totalData?.under?.odds;
          }
        }

        // Check game result from SportsGame using fuzzy full-name/abbreviation matching.
        const { rows: candidateGames } = await pool.query(`
          SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", status, "gameDate"
          FROM "SportsGame"
          WHERE league = $1
            AND "gameDate"::date >= ($2::timestamptz - interval '36 hours')::date
            AND "gameDate"::date <= ($2::timestamptz + interval '36 hours')::date
            AND LOWER(COALESCE(status, '')) = 'final'
            AND "homeScore" IS NOT NULL
            AND "awayScore" IS NOT NULL
          ORDER BY ABS(EXTRACT(EPOCH FROM ("gameDate"::timestamp - $2::timestamp))) ASC
        `, [pick.league, pick.starts_at || pick.created_at]).catch(() => ({ rows: [] }));

        const homeVariants = buildTeamVariants(pick.home_team);
        const awayVariants = buildTeamVariants(pick.away_team);
        const matchedGame = pickBestMatchedGame(candidateGames, homeVariants, awayVariants);

        if (matchedGame && normalizePeriodStatus(matchedGame.status) === 'final') {
          const swapped = homeVariants.some(v => fuzzyTeamMatch(matchedGame.awayTeam, v))
            && awayVariants.some(v => fuzzyTeamMatch(matchedGame.homeTeam, v));
          const homeScore = parseFloat(swapped ? matchedGame.awayScore : matchedGame.homeScore);
          const awayScore = parseFloat(swapped ? matchedGame.homeScore : matchedGame.awayScore);

          if (pick.pick_type === 'game_spread') {
            const margin = homeScore - awayScore;
            if (pick.direction === 'home') {
              result = margin + pick.rec_line > 0 ? 'W' : (margin + pick.rec_line === 0 ? 'P' : 'L');
            } else {
              result = -margin + pick.rec_line > 0 ? 'W' : (-margin + pick.rec_line === 0 ? 'P' : 'L');
            }
          } else if (pick.pick_type === 'game_total') {
            const totalScore = homeScore + awayScore;
            if (pick.direction === 'over') {
              result = totalScore > pick.rec_line ? 'W' : (totalScore === pick.rec_line ? 'P' : 'L');
            } else {
              result = totalScore < pick.rec_line ? 'W' : (totalScore === pick.rec_line ? 'P' : 'L');
            }
          } else if (pick.pick_type === 'game_moneyline') {
            if (homeScore !== awayScore) {
              const homeWon = homeScore > awayScore;
              result = pick.direction === 'home'
                ? (homeWon ? 'W' : 'L')
                : (!homeWon ? 'W' : 'L');
            } else {
              result = 'P';
            }
          }
        } else {
          const espnScore = await fetchEspnMatchedScore(
            pick.league,
            pick.starts_at || pick.created_at,
            pick.home_team,
            pick.away_team
          );
          if (espnScore) {
            const direct = teamMatchScore(espnScore.homeTeam, pick.home_team || '') >= 0.8
              || teamMatchScore(espnScore.homeTeam, getTeamAbbr(pick.home_team || null)) >= 0.8;
            const homeScore = direct ? espnScore.homeScore : espnScore.awayScore;
            const awayScore = direct ? espnScore.awayScore : espnScore.homeScore;

            if (pick.pick_type === 'game_spread') {
              const margin = homeScore - awayScore;
              if (pick.direction === 'home') {
                result = margin + pick.rec_line > 0 ? 'W' : (margin + pick.rec_line === 0 ? 'P' : 'L');
              } else {
                result = -margin + pick.rec_line > 0 ? 'W' : (-margin + pick.rec_line === 0 ? 'P' : 'L');
              }
            } else if (pick.pick_type === 'game_total') {
              const totalScore = homeScore + awayScore;
              if (pick.direction === 'over') {
                result = totalScore > pick.rec_line ? 'W' : (totalScore === pick.rec_line ? 'P' : 'L');
              } else {
                result = totalScore < pick.rec_line ? 'W' : (totalScore === pick.rec_line ? 'P' : 'L');
              }
            } else if (pick.pick_type === 'game_moneyline') {
              if (homeScore !== awayScore) {
                const homeWon = homeScore > awayScore;
                result = pick.direction === 'home'
                  ? (homeWon ? 'W' : 'L')
                  : (!homeWon ? 'W' : 'L');
              } else {
                result = 'P';
              }
            }
          }
        }
      }

      // Calculate CLV
      let clvLine: number | null = null;
      if (closeLine != null && pick.rec_line != null) {
        if (pick.pick_type === 'game_spread') {
          // Spread CLV: if we picked home -3 and it closed -4, CLV = +1 (moved our way)
          clvLine = pick.direction === 'home'
            ? pick.rec_line - closeLine  // e.g. -3 - (-4) = +1
            : closeLine - pick.rec_line; // away: closed moved toward us
        } else if (pick.pick_type === 'player_prop' || pick.pick_type === 'game_total') {
          // Props/Total: if over at 27.5 and closed at 26.5, CLV = +1 (easier)
          if (pick.direction === 'over') {
            clvLine = pick.rec_line - closeLine; // higher rec = more CLV for over
          } else {
            clvLine = closeLine - pick.rec_line; // lower rec = more CLV for under
          }
        }
      }

      // Update the pick
      const closeLineChanged = closeLine != null && !sameNullableNumber(pick.close_line, closeLine);
      const closeOddsChanged = closeOdds != null && !sameNullableNumber(pick.close_odds, closeOdds);
      const recOddsChanged = recOdds != null && !sameNullableNumber(pick.rec_odds, recOdds);
      const clvLineChanged = clvLine != null && !sameNullableNumber(pick.clv_line, clvLine);
      const resultChanged = result != null && String(pick.result || '') !== String(result);
      const shouldUpdate = forceOverwrite
        ? (closeLineChanged || closeOddsChanged || recOddsChanged || clvLineChanged || resultChanged)
        : (closeLine != null || closeOdds != null || recOdds != null || clvLine != null || result != null);

      if (shouldUpdate) {
        await pool.query(`
          UPDATE rm_pick_clv SET
            close_line = CASE WHEN $1::double precision IS NOT NULL THEN $1::double precision ELSE close_line END,
            close_odds = CASE WHEN $2::integer IS NOT NULL THEN $2::integer ELSE close_odds END,
            clv_line = CASE WHEN $3::double precision IS NOT NULL THEN $3::double precision ELSE clv_line END,
            result = CASE WHEN $4::varchar IS NOT NULL THEN $4::varchar ELSE result END,
            rec_odds = CASE WHEN $5::integer IS NOT NULL THEN $5::integer ELSE rec_odds END,
            resolved_at = CASE
              WHEN $4 IS NOT NULL AND resolved_at IS NULL THEN NOW()
              ELSE resolved_at
            END
          WHERE id = $6
        `, [
          closeLine,
          closeOdds != null ? Math.round(closeOdds) : null,
          clvLine,
          result,
          recOdds != null ? Math.round(recOdds) : null,
          pick.id,
        ]);
        resolved++;
      }
    } catch (err) {
      console.error(`[CLV Resolver] Error processing pick ${pick.id}:`, err);
      errors++;
    }
  }

  if (refreshViews) {
    try {
      await pool.query('REFRESH MATERIALIZED VIEW rm_clv_summary');
      console.log('[CLV Resolver] Refreshed rm_clv_summary');
    } catch (err) {
      console.error('[CLV Resolver] Failed to refresh rm_clv_summary:', err);
    }

    try {
      await pool.query('REFRESH MATERIALIZED VIEW rm_model_kpi_stats');
      console.log('[CLV Resolver] Refreshed rm_model_kpi_stats');
    } catch (err) {
      console.error('[CLV Resolver] Failed to refresh rm_model_kpi_stats:', err);
    }
  }

  console.log(`[CLV Resolver] Done. Resolved: ${resolved}, Errors: ${errors}`);
  if (closePool) {
    await pool.end().catch(() => undefined);
  }
  return { scanned: unresolvedPicks.length, resolved, errors };
}

async function main() {
  await resolveClv({ closePool: true });
}

if (require.main === module) {
  main().catch(err => {
    console.error('[CLV Resolver] Fatal error:', err);
    process.exit(1);
  });
}