/**
 * Data Enrichment Layer for SEO Content
 * Pulls from ALL our data sources to build rich context for articles.
 * This is what makes our content unique — real data, not generic AI fluff.
 */
import { pool } from '../db/index';
import { getTeamAbbrev, getPiffPropsForGame, formatPiffForPrompt } from '../twitter/piff-loader';

export interface EnrichedGameData {
  // Base game info
  league: string;
  homeTeam: string;
  awayTeam: string;
  gameDate: string;
  // Resolved full team names (for NCAAB abbreviations)
  homeTeamFull: string;
  awayTeamFull: string;
  // Odds from multiple books
  consensus: { spread: number | null; total: number | null; homeML: number | null; awayML: number | null };
  bookOdds: { book: string; homeML: number; awayML: number; spread: number; total: number }[];
  // Team form (last 5-10 games)
  homeForm: { record: string; ats: string; ouRecord: string; avgPts: number; avgPtsAllowed: number; streak: string };
  awayForm: { record: string; ats: string; ouRecord: string; avgPts: number; avgPtsAllowed: number; streak: string };
  // Head to head
  h2h: { games: number; results: string[] };
  // Key player stats
  homeKeyPlayers: { name: string; stat: string; value: number; avg: number }[];
  awayKeyPlayers: { name: string; stat: string; value: number; avg: number }[];
  // Player props available
  topProps: { player: string; prop: string; line: number; overOdds: number | null }[];
  // DVP matchup edges
  dvpEdges: { team: string; position: string; stat: string; rank: number; avgAllowed: number }[];
  // Injuries
  injuries: { team: string; player: string; status: string; position: string }[];
  // Line movement
  lineMovement: { market: string; open: number; current: number; direction: string }[];
  // Our model pick (if available)
  modelPick: { pick: string; confidence: string; edge: number; reasoning: string } | null;
  // PIFF 3.0 player prop intelligence
  piffProps: Array<{ name: string; stat: string; line: number; direction: string; tier_label: string; edge: number; prob: number; dvp_tier?: string }>;
  piffSummary: string;
}

// Team name resolution — maps SGO short codes to full names
const TEAM_NAMES: Record<string, string> = {};
let teamNamesLoaded = false;

async function loadTeamNames(): Promise<void> {
  if (teamNamesLoaded) return;
  try {
    // Load from static JSON file (built from SGO API)
    const fs = await import('fs');
    const path = await import('path');
    const jsonPath = path.resolve(__dirname, 'team-names.json');
    if (fs.existsSync(jsonPath)) {
      const data = JSON.parse(fs.readFileSync(jsonPath, 'utf-8'));
      for (const [short, long] of Object.entries(data)) {
        TEAM_NAMES[short] = long as string;
        TEAM_NAMES[short.toLowerCase()] = long as string;
        TEAM_NAMES[short.toUpperCase()] = long as string;
      }
      console.log(`[enrichment] Loaded ${Object.keys(data).length} team name mappings from JSON`);
    } else {
      console.warn('[enrichment] team-names.json not found');
    }
  } catch (e) {
    console.warn('[enrichment] Failed to load team names:', (e as Error).message);
  }
  teamNamesLoaded = true;
}

function resolveTeamName(abbrev: string, league: string): string {
  // Try exact match first, then uppercase
  if (TEAM_NAMES[abbrev]) return TEAM_NAMES[abbrev];
  if (TEAM_NAMES[abbrev.toUpperCase()]) return TEAM_NAMES[abbrev.toUpperCase()];
  if (TEAM_NAMES[abbrev.toLowerCase()]) return TEAM_NAMES[abbrev.toLowerCase()];
  // If it's already a long name (>10 chars), return as-is
  if (abbrev.length > 10) return abbrev;
  return abbrev;
}

async function getTeamForm(league: string, team: string, limit: number = 10): Promise<any> {
  const result = await pool.query(`
    SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::date
    FROM "SportsGame"
    WHERE league = $1 AND ("homeTeam" = $2 OR "awayTeam" = $2)
      AND "homeScore" IS NOT NULL AND status IN ('final', 'completed', 'closed')
    ORDER BY "gameDate" DESC LIMIT $3
  `, [league, team, limit]);

  let wins = 0, losses = 0, streak = '', lastResult = '';
  const scores: number[] = [];
  const allowed: number[] = [];

  for (const g of result.rows) {
    const isHome = g.homeTeam === team;
    const pts = isHome ? g.homeScore : g.awayScore;
    const opp = isHome ? g.awayScore : g.homeScore;
    const won = pts > opp;
    if (won) wins++; else losses++;
    scores.push(pts);
    allowed.push(opp);
    if (!lastResult) lastResult = won ? 'W' : 'L';
  }

  // Build streak
  let streakCount = 0;
  for (const g of result.rows) {
    const isHome = g.homeTeam === team;
    const won = (isHome ? g.homeScore : g.awayScore) > (isHome ? g.awayScore : g.homeScore);
    const r = won ? 'W' : 'L';
    if (streakCount === 0 || r === lastResult) {
      streakCount++;
      lastResult = r;
    } else break;
  }

  return {
    record: `${wins}-${losses}`,
    ats: '-', // Would need spread data per game
    ouRecord: '-',
    avgPts: scores.length > 0 ? Math.round(scores.reduce((a, b) => a + b, 0) / scores.length * 10) / 10 : 0,
    avgPtsAllowed: allowed.length > 0 ? Math.round(allowed.reduce((a, b) => a + b, 0) / allowed.length * 10) / 10 : 0,
    streak: `${lastResult}${streakCount}`,
  };
}

async function getKeyPlayers(league: string, team: string, limit: number = 5): Promise<any[]> {
  const primaryStat = ['nba', 'ncaab'].includes(league) ? 'points' :
    league === 'nhl' ? 'goals' :
    ['epl', 'laliga', 'seriea', 'bundesliga', 'ligue1'].includes(league) ? 'goals' : 'points';

  const result = await pool.query(`
    SELECT "playerName", "statKey",
           AVG(value) as avg_val, MAX(value) as max_val, COUNT(*) as games
    FROM "PlayerGameMetric"
    WHERE league = $1 AND team = $2 AND "statKey" = $3
      AND "gameDate" > (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date - INTERVAL '30 days'
    GROUP BY "playerName", "statKey"
    HAVING COUNT(*) >= 2
    ORDER BY AVG(value) DESC
    LIMIT $4
  `, [league, team, primaryStat, limit]);

  return result.rows.map(r => ({
    name: r.playerName,
    stat: r.statKey,
    value: parseFloat(r.max_val),
    avg: Math.round(parseFloat(r.avg_val) * 10) / 10,
  }));
}

async function getTopProps(league: string, homeTeam: string, awayTeam: string, limit: number = 10): Promise<any[]> {
  const result = await pool.query(`
    SELECT ppl."marketName", ppl."propType", ppl."lineValue", ppl."oddsAmerican"
    FROM "PlayerPropLine" ppl
    JOIN "SportsGame" sg ON ppl."gameId" = sg.id
    WHERE ppl.league = $1
      AND ppl."snapshotAt" > NOW() - INTERVAL '12 hours'
      AND (sg."gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date = (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
      AND (sg.status IS NULL OR sg.status IN ('scheduled', 'pre-game', 'not_started'))
      AND ppl."marketName" NOT ILIKE '%Quarter%'
      AND ppl."marketName" NOT ILIKE '%Half%'
    ORDER BY ppl."snapshotAt" DESC
    LIMIT $2
  `, [league, limit * 3]);

  // Deduplicate by player+prop
  const seen = new Set<string>();
  const props: any[] = [];
  for (const r of result.rows) {
    const key = `${r.marketName}_${r.propType}`;
    if (!seen.has(key) && props.length < limit) {
      seen.add(key);
      props.push({
        player: r.marketName,
        prop: r.propType,
        line: parseFloat(r.lineValue),
        overOdds: r.oddsAmerican ? parseInt(r.oddsAmerican) : null,
      });
    }
  }
  return props;
}

async function getDVPEdges(league: string, homeTeam: string, awayTeam: string): Promise<any[]> {
  const result = await pool.query(`
    SELECT team, "positionGroup", "statKey", rank, "avgAllowed"
    FROM "DefenseVsPosition"
    WHERE league = $1 AND team IN ($2, $3)
      AND rank <= 5
    ORDER BY rank ASC
    LIMIT 10
  `, [league, homeTeam, awayTeam]);

  return result.rows.map(r => ({
    team: r.team,
    position: r.positionGroup,
    stat: r.statKey,
    rank: parseInt(r.rank),
    avgAllowed: parseFloat(r.avgAllowed),
  }));
}

async function getInjuries(league: string, teams: string[]): Promise<any[]> {
  if (teams.length === 0) return [];
  const result = await pool.query(`
    SELECT team, "playerName", status, position
    FROM "PlayerInjury"
    WHERE league = $1 AND team = ANY($2)
      AND status IN ('Out', 'Doubtful', 'Questionable', 'Day-To-Day')
    ORDER BY CASE status WHEN 'Out' THEN 1 WHEN 'Doubtful' THEN 2 ELSE 3 END
    LIMIT 15
  `, [league, teams]);

  return result.rows.map(r => ({
    team: r.team,
    player: r.playerName,
    status: r.status,
    position: r.position || '',
  }));
}

async function getLineMovement(league: string, gameKey: string): Promise<any[]> {
  const result = await pool.query(`
    SELECT "marketType", line, odds, timestamp
    FROM "LineMovement"
    WHERE league = $1 AND "gameKey" ILIKE $2
    ORDER BY timestamp ASC
  `, [league, `%${gameKey}%`]);

  if (result.rows.length < 2) return [];

  // Group by market and show open → current
  const markets: Record<string, any[]> = {};
  for (const r of result.rows) {
    if (!markets[r.marketType]) markets[r.marketType] = [];
    markets[r.marketType].push(r);
  }

  return Object.entries(markets).map(([market, rows]) => ({
    market,
    open: parseFloat(rows[0].line),
    current: parseFloat(rows[rows.length - 1].line),
    direction: parseFloat(rows[rows.length - 1].line) > parseFloat(rows[0].line) ? 'up' : 'down',
  }));
}

async function getH2H(league: string, team1: string, team2: string): Promise<any> {
  const result = await pool.query(`
    SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::date
    FROM "SportsGame"
    WHERE league = $1
      AND (("homeTeam" = $2 AND "awayTeam" = $3) OR ("homeTeam" = $3 AND "awayTeam" = $2))
      AND "homeScore" IS NOT NULL
    ORDER BY "gameDate" DESC LIMIT 5
  `, [league, team1, team2]);

  return {
    games: result.rows.length,
    results: result.rows.map(r => `${r.awayTeam} ${r.awayScore} @ ${r.homeTeam} ${r.homeScore}`),
  };
}

async function getModelPick(league: string, homeTeam: string, awayTeam: string, gameDate: string): Promise<any> {
  // Check SoccerPick for soccer leagues
  const soccerLeagues = ['epl', 'laliga', 'seriea', 'bundesliga', 'ligue1'];
  if (soccerLeagues.includes(league)) {
    const result = await pool.query(`
      SELECT bet_type, pick, score, confidence, reasoning, edge_pct, kelly_pct
      FROM "SoccerPick"
      WHERE league = $1 AND home_team = $2 AND away_team = $3 AND game_date = $4
      ORDER BY score DESC LIMIT 1
    `, [league, homeTeam, awayTeam, gameDate]);

    if (result.rows.length > 0) {
      const r = result.rows[0];
      return {
        pick: `${r.bet_type}: ${r.pick}`,
        confidence: r.confidence,
        edge: parseFloat(r.edge_pct),
        reasoning: r.reasoning,
      };
    }
  }

  // Check DailyPick for NBA/NCAAB
  const result = await pool.query(`
    SELECT "betType", pick, "ensembleScore", confidence, reasoning, edge
    FROM "DailyPick"
    WHERE league = $1 AND "gameDate"::date = $2
      AND "homeTeam" = $3 AND "awayTeam" = $4
    ORDER BY "ensembleScore" DESC NULLS LAST LIMIT 1
  `, [league, gameDate, homeTeam, awayTeam]);

  if (result.rows.length > 0) {
    const r = result.rows[0];
    return {
      pick: `${r.betType}: ${r.pick}`,
      confidence: r.confidence,
      edge: parseFloat(r.edge || 0),
      reasoning: r.reasoning,
    };
  }

  return null;
}

// ── MAIN ENRICHMENT FUNCTION ──────────────────────────────────
export async function enrichGameData(
  game: { id: number; league: string; homeTeam: string; awayTeam: string; gameDate: string;
           spreadHome: number | null; total: number | null; moneylineHome: number | null; moneylineAway: number | null },
): Promise<EnrichedGameData> {
  await loadTeamNames();

  const league = game.league;
  const homeTeamFull = resolveTeamName(game.homeTeam, league);
  const awayTeamFull = resolveTeamName(game.awayTeam, league);

  const [homeForm, awayForm, homeKeyPlayers, awayKeyPlayers, topProps, dvpEdges, injuries, lineMovement, h2h, modelPick] =
    await Promise.all([
      getTeamForm(league, game.homeTeam).catch(() => ({ record: '-', ats: '-', ouRecord: '-', avgPts: 0, avgPtsAllowed: 0, streak: '-' })),
      getTeamForm(league, game.awayTeam).catch(() => ({ record: '-', ats: '-', ouRecord: '-', avgPts: 0, avgPtsAllowed: 0, streak: '-' })),
      getKeyPlayers(league, game.homeTeam).catch(() => []),
      getKeyPlayers(league, game.awayTeam).catch(() => []),
      getTopProps(league, game.homeTeam, game.awayTeam).catch(() => []),
      getDVPEdges(league, game.homeTeam, game.awayTeam).catch(() => []),
      getInjuries(league, [game.homeTeam, game.awayTeam]).catch(() => []),
      getLineMovement(league, `${game.homeTeam}`).catch(() => []),
      getH2H(league, game.homeTeam, game.awayTeam).catch(() => ({ games: 0, results: [] })),
      getModelPick(league, game.homeTeam, game.awayTeam, game.gameDate).catch(() => null),
    ]);

  // PIFF 3.0: resolve team abbreviations and load prop intelligence (sync, no DB query)
  const homeAbbrev = getTeamAbbrev(game.homeTeam, league);
  const awayAbbrev = getTeamAbbrev(game.awayTeam, league);
  const rawPiffProps = getPiffPropsForGame(homeAbbrev, awayAbbrev);
  const piffProps = rawPiffProps.map(p => ({
    name: p.name,
    stat: p.stat,
    line: p.line,
    direction: p.direction || 'over',
    tier_label: p.tier_label || 'T3_SOLID',
    edge: p.edge,
    prob: p.prob,
    dvp_tier: p.dvp_tier,
  }));
  const piffSummary = formatPiffForPrompt(rawPiffProps);

  return {
    league,
    homeTeam: game.homeTeam,
    awayTeam: game.awayTeam,
    gameDate: game.gameDate,
    homeTeamFull,
    awayTeamFull,
    consensus: {
      spread: game.spreadHome,
      total: game.total,
      homeML: game.moneylineHome,
      awayML: game.moneylineAway,
    },
    bookOdds: [], // TODO: pull from SportsGameOdds
    homeForm,
    awayForm,
    h2h,
    homeKeyPlayers,
    awayKeyPlayers,
    topProps,
    dvpEdges,
    injuries,
    lineMovement,
    modelPick,
    piffProps,
    piffSummary,
  };
}