/**
 * Comprehensive Data Coverage, Gaps & Accuracy Audit
 *
 * Checks:
 * 1. Data coverage by league, season, and data type
 * 2. Gaps in data (missing dates, incomplete records)
 * 3. Accuracy verification against external sources
 * 4. API connectivity health
 * 5. Data freshness analysis
 */

import { getSportsDb } from '../src/lib/sportsDb';
import * as directQuery from '../src/lib/directSportsQuery';

interface AuditResult {
  category: string;
  check: string;
  status: 'PASS' | 'WARN' | 'FAIL' | 'INFO';
  details: string;
  count?: number;
  recommendation?: string;
}

const results: AuditResult[] = [];

function log(result: AuditResult) {
  results.push(result);
  const emoji = result.status === 'PASS' ? '✅' : result.status === 'WARN' ? '⚠️' : result.status === 'FAIL' ? '❌' : 'ℹ️';
  console.log(`${emoji} [${result.category}] ${result.check}: ${result.details}`);
}

async function main() {
  console.log('═'.repeat(80));
  console.log('           COMPREHENSIVE DATA COVERAGE, GAPS & ACCURACY AUDIT');
  console.log('═'.repeat(80));
  console.log(`           Run Date: ${new Date().toISOString()}`);
  console.log('═'.repeat(80));
  console.log();

  const db = getSportsDb();

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 1: OVERALL DATA COVERAGE
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n📊 SECTION 1: OVERALL DATA COVERAGE\n');

  // 1.1 Total games by league
  const gamesByLeague = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(*) as total_games,
      COUNT(CASE WHEN "homeScore" IS NOT NULL THEN 1 END) as completed_games,
      COUNT(CASE WHEN "moneylineHome" IS NOT NULL THEN 1 END) as games_with_odds,
      MIN("gameDate")::date as earliest_game,
      MAX("gameDate")::date as latest_game
    FROM "SportsGame"
    GROUP BY league
    ORDER BY total_games DESC
  `;

  for (const row of gamesByLeague) {
    const completionRate = ((Number(row.completed_games) / Number(row.total_games)) * 100).toFixed(1);
    const oddsRate = ((Number(row.games_with_odds) / Number(row.total_games)) * 100).toFixed(1);
    log({
      category: 'Coverage',
      check: `${row.league.toUpperCase()} Games`,
      status: Number(row.total_games) > 100 ? 'PASS' : 'WARN',
      details: `${row.total_games} games (${completionRate}% completed, ${oddsRate}% with odds) | ${row.earliest_game} to ${row.latest_game}`,
      count: Number(row.total_games)
    });
  }

  // 1.2 Player data coverage
  const playerCoverage = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(DISTINCT "playerName") as unique_players,
      COUNT(*) as total_metrics,
      COUNT(DISTINCT "gameDate") as game_dates
    FROM "PlayerGameMetric"
    WHERE "gameDate" >= NOW() - INTERVAL '1 year'
    GROUP BY league
    ORDER BY total_metrics DESC
  `;

  for (const row of playerCoverage) {
    log({
      category: 'Coverage',
      check: `${row.league.toUpperCase()} Player Stats`,
      status: Number(row.unique_players) > 50 ? 'PASS' : 'WARN',
      details: `${row.unique_players} players, ${row.total_metrics} metrics across ${row.game_dates} game dates`,
      count: Number(row.total_metrics)
    });
  }

  // 1.3 Player props coverage
  const propsCoverage = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(*) as total_props,
      COUNT(DISTINCT "playerExternalId") as unique_players,
      COUNT(DISTINCT market) as prop_types,
      MIN("createdAt")::date as earliest,
      MAX("createdAt")::date as latest
    FROM "PlayerPropLine"
    GROUP BY league
    ORDER BY total_props DESC
  `;

  for (const row of propsCoverage) {
    log({
      category: 'Coverage',
      check: `${row.league.toUpperCase()} Player Props`,
      status: Number(row.total_props) > 1000 ? 'PASS' : Number(row.total_props) > 100 ? 'WARN' : 'FAIL',
      details: `${row.total_props} props for ${row.unique_players} players, ${row.prop_types} markets | ${row.earliest} to ${row.latest}`,
      count: Number(row.total_props)
    });
  }

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 2: DATA GAPS ANALYSIS
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n🔍 SECTION 2: DATA GAPS ANALYSIS\n');

  // 2.1 Missing scores for past games
  const missingScores = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(*) as missing_count,
      MIN("gameDate")::date as oldest_missing,
      MAX("gameDate")::date as newest_missing
    FROM "SportsGame"
    WHERE "homeScore" IS NULL
      AND "gameDate" < NOW() - INTERVAL '2 hours'
      AND "gameDate" > NOW() - INTERVAL '30 days'
    GROUP BY league
    ORDER BY missing_count DESC
  `;

  for (const row of missingScores) {
    log({
      category: 'Gaps',
      check: `${row.league.toUpperCase()} Missing Scores`,
      status: Number(row.missing_count) < 10 ? 'PASS' : Number(row.missing_count) < 50 ? 'WARN' : 'FAIL',
      details: `${row.missing_count} games missing scores (${row.oldest_missing} to ${row.newest_missing})`,
      count: Number(row.missing_count),
      recommendation: Number(row.missing_count) > 10 ? 'Run score backfill script' : undefined
    });
  }

  // 2.2 Missing odds for upcoming games
  const missingOdds = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(*) as missing_count
    FROM "SportsGame"
    WHERE "moneylineHome" IS NULL
      AND "gameDate" > NOW()
      AND "gameDate" < NOW() + INTERVAL '7 days'
    GROUP BY league
    ORDER BY missing_count DESC
  `;

  for (const row of missingOdds) {
    log({
      category: 'Gaps',
      check: `${row.league.toUpperCase()} Missing Odds (Next 7 Days)`,
      status: Number(row.missing_count) < 5 ? 'PASS' : Number(row.missing_count) < 20 ? 'WARN' : 'FAIL',
      details: `${row.missing_count} upcoming games without odds`,
      count: Number(row.missing_count),
      recommendation: Number(row.missing_count) > 5 ? 'Fetch odds from SportsGameOdds API' : undefined
    });
  }

  // 2.3 Gaps in daily data
  const today = new Date();
  const thirtyDaysAgo = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const dailyGames = await db.$queryRaw<any[]>`
      SELECT
        "gameDate"::date as game_date,
        COUNT(*) as game_count
      FROM "SportsGame"
      WHERE league = ${league}
        AND "gameDate" >= ${thirtyDaysAgo}
        AND "gameDate" < ${today}
      GROUP BY "gameDate"::date
      ORDER BY game_date
    `;

    // Check for missing days (when games should exist)
    const gamesPerDay = dailyGames.map(d => Number(d.game_count));
    const avgGames = gamesPerDay.length > 0 ? gamesPerDay.reduce((a, b) => a + b, 0) / gamesPerDay.length : 0;
    const daysWithGames = dailyGames.length;

    log({
      category: 'Gaps',
      check: `${league.toUpperCase()} Daily Continuity`,
      status: daysWithGames > 20 ? 'PASS' : daysWithGames > 10 ? 'WARN' : 'INFO',
      details: `${daysWithGames} days with games in last 30 days, avg ${avgGames.toFixed(1)} games/day`,
      count: daysWithGames
    });
  }

  // 2.4 Player metrics gaps
  const metricsGaps = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(DISTINCT "playerName") as players_with_stats,
      COUNT(DISTINCT CASE WHEN "gameDate" >= NOW() - INTERVAL '7 days' THEN "playerName" END) as recent_players
    FROM "PlayerGameMetric"
    WHERE league IN ('nba', 'nfl', 'nhl', 'mlb')
    GROUP BY league
  `;

  for (const row of metricsGaps) {
    const recentPct = Number(row.players_with_stats) > 0
      ? ((Number(row.recent_players) / Number(row.players_with_stats)) * 100).toFixed(1)
      : '0';
    log({
      category: 'Gaps',
      check: `${row.league.toUpperCase()} Recent Player Stats`,
      status: Number(row.recent_players) > 50 ? 'PASS' : Number(row.recent_players) > 10 ? 'WARN' : 'FAIL',
      details: `${row.recent_players}/${row.players_with_stats} players have stats in last 7 days (${recentPct}%)`,
      count: Number(row.recent_players)
    });
  }

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 3: DATA ACCURACY VERIFICATION
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n🎯 SECTION 3: DATA ACCURACY VERIFICATION\n');

  // 3.1 Verify recent NBA scores against ESPN
  try {
    const espnScores = await directQuery.fetchLiveScoresFromApi('nba');
    if (espnScores && espnScores.games.length > 0) {
      const completedGames = espnScores.games.filter(g => g.status === 'Final' || g.status?.includes('Final'));
      log({
        category: 'Accuracy',
        check: 'ESPN API Connectivity',
        status: 'PASS',
        details: `Connected - ${espnScores.games.length} games fetched (${completedGames.length} final)`,
        count: espnScores.games.length
      });

      // Cross-reference with our DB
      for (const espnGame of completedGames.slice(0, 3)) {
        const dbGame = await db.sportsGame.findFirst({
          where: {
            league: 'nba',
            OR: [
              { homeTeam: { contains: espnGame.homeTeam, mode: 'insensitive' } },
              { awayTeam: { contains: espnGame.awayTeam, mode: 'insensitive' } }
            ],
            gameDate: { gte: new Date(Date.now() - 3 * 24 * 60 * 60 * 1000) }
          }
        });

        if (dbGame && dbGame.homeScore !== null) {
          const scoresMatch = dbGame.homeScore === espnGame.homeScore && dbGame.awayScore === espnGame.awayScore;
          log({
            category: 'Accuracy',
            check: `Score Verification: ${espnGame.awayTeam}@${espnGame.homeTeam}`,
            status: scoresMatch ? 'PASS' : 'WARN',
            details: scoresMatch
              ? `DB: ${dbGame.awayScore}-${dbGame.homeScore} = ESPN: ${espnGame.awayScore}-${espnGame.homeScore}`
              : `MISMATCH - DB: ${dbGame.awayScore}-${dbGame.homeScore}, ESPN: ${espnGame.awayScore}-${espnGame.homeScore}`
          });
        }
      }
    }
  } catch (e: any) {
    log({
      category: 'Accuracy',
      check: 'ESPN API Connectivity',
      status: 'FAIL',
      details: `Failed to connect: ${e.message}`
    });
  }

  // 3.2 Verify odds consistency
  const oddsConsistency = await db.$queryRaw<any[]>`
    SELECT
      league,
      COUNT(*) as total,
      COUNT(CASE WHEN "moneylineHome" > 0 AND "moneylineAway" > 0 THEN 1 END) as both_positive,
      COUNT(CASE WHEN "moneylineHome" < -500 OR "moneylineAway" < -500 THEN 1 END) as extreme_odds,
      COUNT(CASE WHEN "spreadHome" IS NOT NULL AND "total" IS NOT NULL THEN 1 END) as complete_odds
    FROM "SportsGame"
    WHERE "moneylineHome" IS NOT NULL
      AND "gameDate" >= NOW() - INTERVAL '30 days'
    GROUP BY league
  `;

  for (const row of oddsConsistency) {
    const bothPositiveRate = ((Number(row.both_positive) / Number(row.total)) * 100).toFixed(1);
    const extremeRate = ((Number(row.extreme_odds) / Number(row.total)) * 100).toFixed(1);
    log({
      category: 'Accuracy',
      check: `${row.league.toUpperCase()} Odds Validity`,
      status: Number(row.both_positive) < Number(row.total) * 0.01 ? 'PASS' : 'WARN',
      details: `${bothPositiveRate}% both positive (should be rare), ${extremeRate}% extreme odds (< -500)`,
      count: Number(row.total)
    });
  }

  // 3.3 Backtest accuracy check
  console.log('\n  Running backtest accuracy verification...');

  for (const league of ['nba', 'nfl']) {
    // Direct DB query
    const directCount = await db.sportsGame.count({
      where: {
        league,
        season: league === 'nba' ? 2025 : 2025,
        homeScore: { not: null },
        moneylineHome: { gt: 0 }
      }
    });

    // Backtest function
    const backtestResult = await directQuery.runStrategyBacktest({
      strategy: 'home underdogs',
      league,
      season: 2026
    });

    const match = backtestResult?.totalBets === directCount;
    log({
      category: 'Accuracy',
      check: `${league.toUpperCase()} Backtest Count`,
      status: match ? 'PASS' : 'WARN',
      details: match
        ? `Backtest (${backtestResult?.totalBets}) = DB Query (${directCount})`
        : `MISMATCH - Backtest: ${backtestResult?.totalBets || 0}, DB: ${directCount}`,
      count: backtestResult?.totalBets || 0
    });
  }

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 4: API HEALTH CHECK
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n🌐 SECTION 4: API HEALTH CHECK\n');

  const apis = directQuery.getAvailableApis();
  for (const api of apis) {
    log({
      category: 'API Health',
      check: api.name,
      status: api.status === 'configured' || api.status === 'available' ? 'PASS' : 'WARN',
      details: `${api.status} - ${api.description} | Leagues: ${api.leagues.join(', ')}`
    });
  }

  // Test live API calls
  const systemHealth = await directQuery.getSystemHealth();
  log({
    category: 'API Health',
    check: 'Overall System Health',
    status: systemHealth.status === 'healthy' ? 'PASS' : systemHealth.status === 'degraded' ? 'WARN' : 'FAIL',
    details: `${systemHealth.status.toUpperCase()} - ${systemHealth.checks.length - systemHealth.recentErrors}/${systemHealth.checks.length} checks passed, ${(systemHealth.querySuccessRate * 100).toFixed(0)}% query success`
  });

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 5: DATA FRESHNESS
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n⏰ SECTION 5: DATA FRESHNESS\n');

  const freshness = await directQuery.getDataFreshness();

  if (freshness.lastOddsUpdate) {
    const oddsAgeHours = (Date.now() - freshness.lastOddsUpdate.getTime()) / (1000 * 60 * 60);
    log({
      category: 'Freshness',
      check: 'Odds Data',
      status: oddsAgeHours < 6 ? 'PASS' : oddsAgeHours < 24 ? 'WARN' : 'FAIL',
      details: `Last update: ${freshness.lastOddsUpdate.toISOString()} (${oddsAgeHours.toFixed(1)} hours ago)`
    });
  }

  if (freshness.lastScoresUpdate) {
    const scoresAgeHours = (Date.now() - freshness.lastScoresUpdate.getTime()) / (1000 * 60 * 60);
    log({
      category: 'Freshness',
      check: 'Scores Data',
      status: scoresAgeHours < 2 ? 'PASS' : scoresAgeHours < 12 ? 'WARN' : 'FAIL',
      details: `Last update: ${freshness.lastScoresUpdate.toISOString()} (${scoresAgeHours.toFixed(1)} hours ago)`
    });
  }

  if (freshness.lastPropsUpdate) {
    const propsAgeHours = (Date.now() - freshness.lastPropsUpdate.getTime()) / (1000 * 60 * 60);
    log({
      category: 'Freshness',
      check: 'Player Props Data',
      status: propsAgeHours < 12 ? 'PASS' : propsAgeHours < 48 ? 'WARN' : 'FAIL',
      details: `Last update: ${freshness.lastPropsUpdate.toISOString()} (${propsAgeHours.toFixed(1)} hours ago)`
    });
  }

  log({
    category: 'Freshness',
    check: 'Games with Odds Today',
    status: freshness.gamesWithOddsToday > 0 ? 'PASS' : 'INFO',
    details: `${freshness.gamesWithOddsToday} games have odds for today`,
    count: freshness.gamesWithOddsToday
  });

  // ══════════════════════════════════════════════════════════════════════════════
  // SECTION 6: SEASON-SPECIFIC ANALYSIS
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n📅 SECTION 6: SEASON-SPECIFIC ANALYSIS\n');

  const seasonStats = await db.$queryRaw<any[]>`
    SELECT
      league,
      season,
      COUNT(*) as games,
      COUNT(CASE WHEN "homeScore" IS NOT NULL THEN 1 END) as completed,
      COUNT(CASE WHEN "moneylineHome" IS NOT NULL THEN 1 END) as with_odds,
      AVG(ABS("spreadHome")) as avg_spread,
      AVG("total") as avg_total
    FROM "SportsGame"
    WHERE season >= 2024
    GROUP BY league, season
    ORDER BY league, season DESC
  `;

  for (const row of seasonStats) {
    const completionPct = ((Number(row.completed) / Number(row.games)) * 100).toFixed(0);
    const oddsPct = ((Number(row.with_odds) / Number(row.games)) * 100).toFixed(0);
    log({
      category: 'Seasons',
      check: `${row.league.toUpperCase()} ${row.season}`,
      status: Number(row.games) > 50 ? 'PASS' : 'INFO',
      details: `${row.games} games (${completionPct}% complete, ${oddsPct}% odds) | Avg spread: ${row.avg_spread ? Number(row.avg_spread).toFixed(1) : 'N/A'}, Avg total: ${row.avg_total ? Number(row.avg_total).toFixed(1) : 'N/A'}`,
      count: Number(row.games)
    });
  }

  // ══════════════════════════════════════════════════════════════════════════════
  // SUMMARY
  // ══════════════════════════════════════════════════════════════════════════════
  console.log('\n\n' + '═'.repeat(80));
  console.log('                              AUDIT SUMMARY');
  console.log('═'.repeat(80) + '\n');

  const passed = results.filter(r => r.status === 'PASS').length;
  const warnings = results.filter(r => r.status === 'WARN').length;
  const failed = results.filter(r => r.status === 'FAIL').length;
  const info = results.filter(r => r.status === 'INFO').length;

  console.log(`  ✅ PASSED:   ${passed}`);
  console.log(`  ⚠️  WARNINGS: ${warnings}`);
  console.log(`  ❌ FAILED:   ${failed}`);
  console.log(`  ℹ️  INFO:     ${info}`);
  console.log(`  ─────────────────`);
  console.log(`  📊 TOTAL:    ${results.length}`);

  const healthScore = ((passed / (passed + warnings + failed)) * 100).toFixed(1);
  console.log(`\n  📈 DATA HEALTH SCORE: ${healthScore}%`);

  // Recommendations
  const recommendations = results.filter(r => r.recommendation);
  if (recommendations.length > 0) {
    console.log('\n\n📋 RECOMMENDATIONS:\n');
    recommendations.forEach((r, i) => {
      console.log(`  ${i + 1}. [${r.check}] ${r.recommendation}`);
    });
  }

  // Critical issues
  const criticalIssues = results.filter(r => r.status === 'FAIL');
  if (criticalIssues.length > 0) {
    console.log('\n\n🚨 CRITICAL ISSUES:\n');
    criticalIssues.forEach((r, i) => {
      console.log(`  ${i + 1}. [${r.category}] ${r.check}: ${r.details}`);
    });
  }

  console.log('\n' + '═'.repeat(80));
  console.log('                           AUDIT COMPLETE');
  console.log('═'.repeat(80) + '\n');

  // Save results to JSON
  const outputPath = '/var/www/html/eventheodds/data-audit-results.json';
  const fs = await import('fs');
  fs.writeFileSync(outputPath, JSON.stringify({
    runDate: new Date().toISOString(),
    healthScore: parseFloat(healthScore),
    summary: { passed, warnings, failed, info, total: results.length },
    results,
    recommendations: recommendations.map(r => ({ check: r.check, recommendation: r.recommendation })),
    criticalIssues: criticalIssues.map(r => ({ category: r.category, check: r.check, details: r.details }))
  }, null, 2));

  console.log(`Results saved to: ${outputPath}`);

  process.exit(failed > 0 ? 1 : 0);
}

main().catch(e => {
  console.error('Audit failed:', e);
  process.exit(1);
});