/**
 * Pick Grading Engine
 * Matches sc_picks against SportsGame final scores and computes win/loss/push.
 *
 * Usage: npx tsx src/twitter/grading-engine.ts
 */
import { pool } from '../db/index';
import { getUngradedPicks, updatePickGrade, type UngradedPick } from './twitter-data-queries';

export interface GradingReport {
  graded: number;
  wins: number;
  losses: number;
  pushes: number;
  skipped: number;
  errors: number;
}

interface GameRow {
  id: number;
  homeTeam: string;
  awayTeam: string;
  homeScore: number;
  awayScore: number;
  league: string;
  gameDate: string;
}

// ── Team Name Normalization ─────────────────────────────────

function normalizeForMatch(name: string): string {
  return name
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}

function teamTokens(name: string): string[] {
  return normalizeForMatch(name).split(' ').filter(t => t.length >= 3);
}

function teamMatchScore(gameTeam: string, pickTeam: string): number {
  const gameTokens = teamTokens(gameTeam);
  const pickTokens = teamTokens(pickTeam);
  if (gameTokens.length === 0 || pickTokens.length === 0) return 0;

  // Exact normalized match
  if (normalizeForMatch(gameTeam) === normalizeForMatch(pickTeam)) return 1.0;

  // Token overlap
  const matches = pickTokens.filter(t => gameTokens.includes(t)).length;
  return matches / Math.max(gameTokens.length, pickTokens.length);
}

// ── Game Key Parsing ────────────────────────────────────────

function parseGameKey(gameKey: string): { league: string; away: string; home: string } | null {
  // Format: league:away-team@home-team (normalized with dashes)
  if (gameKey.startsWith('prop:')) return null; // prop keys aren't matchable to game scores

  const colonIdx = gameKey.indexOf(':');
  if (colonIdx < 0) return null;

  const league = gameKey.substring(0, colonIdx);
  const rest = gameKey.substring(colonIdx + 1);
  const atIdx = rest.indexOf('@');
  if (atIdx < 0) return null;

  return {
    league,
    away: rest.substring(0, atIdx).replace(/-/g, ' ').trim(),
    home: rest.substring(atIdx + 1).replace(/-/g, ' ').trim(),
  };
}

// ── Match Pick to SportsGame ────────────────────────────────

async function matchPickToGame(pick: UngradedPick): Promise<GameRow | null> {
  const parsed = parseGameKey(pick.game_key);
  if (!parsed) return null;

  // Query final games for this league on the pick's game_date
  const result = await pool.query(
    `SELECT id, "homeTeam", "awayTeam", "homeScore", "awayScore", league, "gameDate"
     FROM "SportsGame"
     WHERE LOWER(league) = $1
       AND status = 'final'
       AND "gameDate"::date = $2
       AND "homeScore" IS NOT NULL
       AND "awayScore" IS NOT NULL
     ORDER BY "gameDate" DESC`,
    [parsed.league, pick.game_date]
  );

  if (result.rows.length === 0) return null;

  // Try matching from game_key first
  let bestMatch: GameRow | null = null;
  let bestScore = 0;

  for (const row of result.rows) {
    const homeScore = teamMatchScore(row.homeTeam, parsed.home);
    const awayScore = teamMatchScore(row.awayTeam, parsed.away);
    const combined = (homeScore + awayScore) / 2;

    if (combined > bestScore) {
      bestScore = combined;
      bestMatch = row;
    }
  }

  // Fall back to inputs_snapshot if game_key match is weak
  if (bestScore < 0.4 && pick.inputs_snapshot) {
    const snapshot = typeof pick.inputs_snapshot === 'string'
      ? JSON.parse(pick.inputs_snapshot)
      : pick.inputs_snapshot;

    const snapshotHome = snapshot.homeTeam || snapshot.homeTeamFull || '';
    const snapshotAway = snapshot.awayTeam || snapshot.awayTeamFull || '';

    if (snapshotHome && snapshotAway) {
      for (const row of result.rows) {
        const homeScore = teamMatchScore(row.homeTeam, snapshotHome);
        const awayScore = teamMatchScore(row.awayTeam, snapshotAway);
        const combined = (homeScore + awayScore) / 2;

        if (combined > bestScore) {
          bestScore = combined;
          bestMatch = row;
        }
      }
    }
  }

  // Require a minimum match confidence
  return bestScore >= 0.3 ? bestMatch : null;
}

// ── Grade Computation ───────────────────────────────────────

function computePickResult(
  pick: UngradedPick,
  game: GameRow
): { result: 'win' | 'loss' | 'push'; notes: string } {
  const homeScore = Number(game.homeScore);
  const awayScore = Number(game.awayScore);
  const actualTotal = homeScore + awayScore;
  const margin = homeScore - awayScore; // positive = home won
  // pg returns NUMERIC as string — force to number
  const line = pick.line_value != null ? Number(pick.line_value) : null;

  switch (pick.pick_type) {
    case 'spread': {
      if (line == null) return { result: 'loss', notes: 'spread: no line value' };
      // pick_direction: home or away
      if (pick.pick_direction === 'home') {
        // Home covers if homeScore + spread > awayScore (spread is typically negative for favorites)
        // Spread from the book's perspective: home team spread = line_value
        const homeMarginVsSpread = margin + line;
        if (homeMarginVsSpread > 0) return { result: 'win', notes: `home ${margin} + spread ${line} = ${homeMarginVsSpread}` };
        if (homeMarginVsSpread === 0) return { result: 'push', notes: `home ${margin} + spread ${line} = push` };
        return { result: 'loss', notes: `home ${margin} + spread ${line} = ${homeMarginVsSpread}` };
      } else {
        // Away covers: awayScore + (-spread) > homeScore => margin - line < 0 => -(margin + line) > 0
        const awayMarginVsSpread = -(margin + line);
        if (awayMarginVsSpread > 0) return { result: 'win', notes: `away covers: margin ${margin}, line ${line}` };
        if (awayMarginVsSpread === 0) return { result: 'push', notes: `away push: margin ${margin}, line ${line}` };
        return { result: 'loss', notes: `away fails: margin ${margin}, line ${line}` };
      }
    }

    case 'total': {
      if (line == null) return { result: 'loss', notes: 'total: no line value' };
      if (pick.pick_direction === 'over') {
        if (actualTotal > line) return { result: 'win', notes: `over: actual ${actualTotal} > line ${line}` };
        if (actualTotal === line) return { result: 'push', notes: `over: actual ${actualTotal} = line ${line}` };
        return { result: 'loss', notes: `over: actual ${actualTotal} < line ${line}` };
      } else {
        if (actualTotal < line) return { result: 'win', notes: `under: actual ${actualTotal} < line ${line}` };
        if (actualTotal === line) return { result: 'push', notes: `under: actual ${actualTotal} = line ${line}` };
        return { result: 'loss', notes: `under: actual ${actualTotal} > line ${line}` };
      }
    }

    case 'moneyline': {
      if (pick.pick_direction === 'home') {
        if (margin > 0) return { result: 'win', notes: `ML home won ${homeScore}-${awayScore}` };
        if (margin === 0) return { result: 'push', notes: `ML tie ${homeScore}-${awayScore}` };
        return { result: 'loss', notes: `ML home lost ${homeScore}-${awayScore}` };
      } else {
        if (margin < 0) return { result: 'win', notes: `ML away won ${awayScore}-${homeScore}` };
        if (margin === 0) return { result: 'push', notes: `ML tie ${homeScore}-${awayScore}` };
        return { result: 'loss', notes: `ML away lost ${awayScore}-${homeScore}` };
      }
    }

    case 'prop_over':
    case 'prop_under':
      return { result: 'loss', notes: 'prop: no player stat source — skipped' };

    default:
      return { result: 'loss', notes: `unknown pick_type: ${pick.pick_type}` };
  }
}

// ── Main Grading Function ───────────────────────────────────

export async function gradeAllPendingPicks(): Promise<GradingReport> {
  const report: GradingReport = { graded: 0, wins: 0, losses: 0, pushes: 0, skipped: 0, errors: 0 };

  const picks = await getUngradedPicks(200);
  console.log(`[grading] Found ${picks.length} ungraded picks`);

  for (const pick of picks) {
    try {
      // Skip prop picks — no stat source
      if (pick.pick_type === 'prop_over' || pick.pick_type === 'prop_under') {
        await updatePickGrade(pick.id, 'skip', null, null, null, 'prop: no player stat source');
        report.skipped++;
        continue;
      }

      const game = await matchPickToGame(pick);
      if (!game) {
        report.skipped++;
        continue; // game not final yet or no match found
      }

      const { result, notes } = computePickResult(pick, game);
      const total = game.homeScore + game.awayScore;

      await updatePickGrade(pick.id, result, game.homeScore, game.awayScore, total, notes);

      report.graded++;
      if (result === 'win') report.wins++;
      else if (result === 'loss') report.losses++;
      else if (result === 'push') report.pushes++;
    } catch (err) {
      console.error(`[grading] Error grading pick #${pick.id}:`, (err as Error).message);
      report.errors++;
    }
  }

  console.log(`[grading] Done: ${report.graded} graded (${report.wins}W-${report.losses}L-${report.pushes}P), ${report.skipped} skipped, ${report.errors} errors`);
  return report;
}

// ── CLI Entry Point ─────────────────────────────────────────

if (require.main === module) {
  // Load env for standalone execution
  require('dotenv').config({ path: '/var/www/html/sportsclaw-guru/backend/.env' });

  gradeAllPendingPicks()
    .then((report) => {
      console.log('\n=== Grading Report ===');
      console.log(JSON.stringify(report, null, 2));
      process.exit(0);
    })
    .catch((err) => {
      console.error('Fatal grading error:', err);
      process.exit(1);
    });
}