#!/usr/bin/env npx tsx
/**
 * Data Quality Verification Script
 *
 * Checks for common data issues that could cause stale/incorrect information:
 * - Players showing wrong teams after trades
 * - Low canonical linkage coverage
 * - Missing aliases for key players
 * - Stale roster data
 *
 * Run: npx tsx scripts/verify-data-quality.ts
 */

import { PrismaClient } from '../prisma_sports/generated/sports-client';

const prisma = new PrismaClient();

interface Issue {
  severity: 'critical' | 'warning' | 'info';
  category: string;
  message: string;
  details?: string;
}

const issues: Issue[] = [];

function addIssue(severity: 'critical' | 'warning' | 'info', category: string, message: string, details?: string) {
  issues.push({ severity, category, message, details });
}

async function checkCoverage() {
  console.log('\n=== COVERAGE CHECK ===\n');

  const thresholds = {
    propLine: { warning: 85, critical: 70 },
    metric: { warning: 75, critical: 50 },
  };

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    // Prop line coverage
    const propTotal = await prisma.playerPropLine.count({ where: { league } });
    const propLinked = await prisma.playerPropLine.count({
      where: { league, NOT: { canonicalPlayerId: null } }
    });
    const propPct = propTotal > 0 ? (propLinked / propTotal) * 100 : 0;

    console.log(`${league.toUpperCase()} Props: ${propLinked.toLocaleString()}/${propTotal.toLocaleString()} (${propPct.toFixed(1)}%)`);

    if (propTotal > 100 && propPct < thresholds.propLine.critical) {
      addIssue('critical', 'coverage', `${league.toUpperCase()} prop coverage critically low`, `${propPct.toFixed(1)}% linked`);
    } else if (propTotal > 100 && propPct < thresholds.propLine.warning) {
      addIssue('warning', 'coverage', `${league.toUpperCase()} prop coverage below target`, `${propPct.toFixed(1)}% linked`);
    }

    // Metric coverage
    const metricTotal = await prisma.playerGameMetric.count({ where: { league } });
    const metricLinked = await prisma.playerGameMetric.count({
      where: { league, NOT: { canonicalPlayerId: null } }
    });
    const metricPct = metricTotal > 0 ? (metricLinked / metricTotal) * 100 : 0;

    console.log(`${league.toUpperCase()} Metrics: ${metricLinked.toLocaleString()}/${metricTotal.toLocaleString()} (${metricPct.toFixed(1)}%)`);

    if (metricTotal > 1000 && metricPct < thresholds.metric.critical) {
      addIssue('critical', 'coverage', `${league.toUpperCase()} metric coverage critically low`, `${metricPct.toFixed(1)}% linked`);
    } else if (metricTotal > 1000 && metricPct < thresholds.metric.warning) {
      addIssue('warning', 'coverage', `${league.toUpperCase()} metric coverage below target`, `${metricPct.toFixed(1)}% linked`);
    }
  }
}

async function checkRosterFreshness() {
  console.log('\n=== ROSTER FRESHNESS CHECK ===\n');

  // Check for players without team assignments
  const playersWithoutTeam = await prisma.canonicalPlayer.count({
    where: { teamId: null }
  });
  const totalPlayers = await prisma.canonicalPlayer.count();
  const withoutTeamPct = (playersWithoutTeam / totalPlayers) * 100;

  console.log(`Players without team: ${playersWithoutTeam.toLocaleString()} (${withoutTeamPct.toFixed(1)}%)`);

  if (withoutTeamPct > 50) {
    addIssue('critical', 'roster', 'Majority of players lack team assignment', `${withoutTeamPct.toFixed(1)}% unassigned`);
  } else if (withoutTeamPct > 30) {
    addIssue('warning', 'roster', 'Many players lack team assignment', `${withoutTeamPct.toFixed(1)}% unassigned`);
  }

  // Check update freshness
  const yesterday = new Date(Date.now() - 24 * 60 * 60 * 1000);
  const recentlyUpdated = await prisma.canonicalPlayer.count({
    where: { updatedAt: { gte: yesterday } }
  });

  console.log(`Players updated in last 24h: ${recentlyUpdated.toLocaleString()}`);

  if (recentlyUpdated === 0) {
    addIssue('warning', 'roster', 'No roster updates in the last 24 hours', 'Run sync-rosters.ts');
  }
}

async function spotCheckTradedPlayers() {
  console.log('\n=== SPOT CHECK: KNOWN TRADED PLAYERS ===\n');

  // Known recent trades to verify (2024-25 season)
  const tradedPlayers = [
    { name: "De'Andre Hunter", expectedTeam: 'CLE', league: 'nba' },
    { name: "Dennis Schroder", expectedTeam: 'SAC', league: 'nba' },
    { name: "Cam Johnson", expectedTeam: 'BKN', league: 'nba' },
    { name: "Dorian Finney-Smith", expectedTeam: 'LAL', league: 'nba' },
  ];

  for (const { name, expectedTeam, league } of tradedPlayers) {
    const normalized = name.toLowerCase()
      .normalize('NFD').replace(/[\u0300-\u036f]/g, '')
      .replace(/[.']/g, '')
      .replace(/\s+/g, ' ')
      .trim();

    const player = await prisma.canonicalPlayer.findUnique({
      where: { league_normalizedName: { league, normalizedName: normalized } },
      include: { team: { select: { abbr: true } } }
    });

    if (!player) {
      console.log(`  ${name}: NOT FOUND in database`);
      addIssue('warning', 'roster', `Traded player not in database: ${name}`, '');
    } else if (player.team?.abbr !== expectedTeam) {
      console.log(`  ${name}: ${player.team?.abbr || 'NO TEAM'} (expected ${expectedTeam})`);
      addIssue('critical', 'roster', `${name} showing wrong team`, `Has ${player.team?.abbr || 'NO TEAM'}, expected ${expectedTeam}`);
    } else {
      console.log(`  ${name}: ${player.team?.abbr} ✓`);
    }
  }
}

async function checkAliasHealth() {
  console.log('\n=== ALIAS HEALTH CHECK ===\n');

  // Count aliases per source
  const aliasBySource = await prisma.playerAlias.groupBy({
    by: ['source'],
    _count: { _all: true }
  });

  console.log('Aliases by source:');
  for (const { source, _count } of aliasBySource) {
    console.log(`  ${source}: ${_count._all.toLocaleString()}`);
  }

  // Check for players with no aliases (potential linking issues)
  const playersWithAliases = await prisma.playerAlias.findMany({
    select: { playerId: true },
    distinct: ['playerId']
  });
  const playerIdsWithAliases = new Set(playersWithAliases.map(p => p.playerId));

  const totalPlayers = await prisma.canonicalPlayer.count();
  const playersWithoutAliases = totalPlayers - playerIdsWithAliases.size;
  const withoutAliasPct = (playersWithoutAliases / totalPlayers) * 100;

  console.log(`\nPlayers without aliases: ${playersWithoutAliases.toLocaleString()} (${withoutAliasPct.toFixed(1)}%)`);

  if (withoutAliasPct > 50) {
    addIssue('warning', 'aliases', 'Many players lack aliases', `${withoutAliasPct.toFixed(1)}% without aliases`);
  }
}

async function checkUnlinkedProps() {
  console.log('\n=== UNLINKED PROPS ANALYSIS ===\n');

  // Find patterns in unlinked props
  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const unlinkedCount = await prisma.playerPropLine.count({
      where: { league, canonicalPlayerId: null }
    });

    if (unlinkedCount > 100) {
      // Sample some unlinked external IDs
      const samples = await prisma.playerPropLine.findMany({
        where: { league, canonicalPlayerId: null },
        select: { playerExternalId: true },
        distinct: ['playerExternalId'],
        take: 5
      });

      console.log(`${league.toUpperCase()}: ${unlinkedCount.toLocaleString()} unlinked props`);
      console.log(`  Sample external IDs: ${samples.map(s => s.playerExternalId).join(', ')}`);
    }
  }
}

async function main() {
  console.log('='.repeat(60));
  console.log('DATA QUALITY VERIFICATION');
  console.log('='.repeat(60));
  console.log(`Timestamp: ${new Date().toISOString()}`);

  await checkCoverage();
  await checkRosterFreshness();
  await spotCheckTradedPlayers();
  await checkAliasHealth();
  await checkUnlinkedProps();

  // Summary
  console.log('\n' + '='.repeat(60));
  console.log('ISSUE SUMMARY');
  console.log('='.repeat(60) + '\n');

  const critical = issues.filter(i => i.severity === 'critical');
  const warnings = issues.filter(i => i.severity === 'warning');
  const info = issues.filter(i => i.severity === 'info');

  if (critical.length > 0) {
    console.log('CRITICAL ISSUES:');
    critical.forEach(i => console.log(`  [!] ${i.category}: ${i.message}${i.details ? ` (${i.details})` : ''}`));
    console.log('');
  }

  if (warnings.length > 0) {
    console.log('WARNINGS:');
    warnings.forEach(i => console.log(`  [?] ${i.category}: ${i.message}${i.details ? ` (${i.details})` : ''}`));
    console.log('');
  }

  if (info.length > 0) {
    console.log('INFO:');
    info.forEach(i => console.log(`  [i] ${i.category}: ${i.message}${i.details ? ` (${i.details})` : ''}`));
    console.log('');
  }

  if (issues.length === 0) {
    console.log('No issues found. Data quality is good.');
  } else {
    console.log(`Total: ${critical.length} critical, ${warnings.length} warnings, ${info.length} info`);
  }

  await prisma.$disconnect();

  // Exit with error code if critical issues
  if (critical.length > 0) {
    process.exit(1);
  }
}

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