#!/usr/bin/env npx tsx
/**
 * Comprehensive Data Gap Analysis
 * Identifies missing links, unlinked data, and coverage issues
 */

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

const prisma = new PrismaClient();

async function analyzeGaps() {
  console.log('='.repeat(70));
  console.log('COMPREHENSIVE DATA GAP ANALYSIS');
  console.log('='.repeat(70));

  // 1. Unlinked props by source pattern
  console.log('\n=== UNLINKED PROPS ANALYSIS ===\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const unlinked = await prisma.playerPropLine.findMany({
      where: { league, canonicalPlayerId: null },
      select: { playerExternalId: true },
      distinct: ['playerExternalId'],
    });

    if (unlinked.length > 0) {
      // Categorize by ID pattern
      const patterns = { numeric: 0, sgo_style: 0, other: 0 };

      for (const { playerExternalId } of unlinked) {
        if (/^\d+$/.test(playerExternalId)) {
          patterns.numeric++;
        } else if (/_[A-Z]+$/.test(playerExternalId)) {
          patterns.sgo_style++;
        } else {
          patterns.other++;
        }
      }

      console.log(`${league.toUpperCase()}: ${unlinked.length} unlinked player IDs`);
      console.log(`  Numeric: ${patterns.numeric}, SGO-style: ${patterns.sgo_style}, Other: ${patterns.other}`);

      const samples = unlinked.slice(0, 3).map(u => u.playerExternalId);
      console.log(`  Samples: ${samples.join(', ')}`);
    } else {
      console.log(`${league.toUpperCase()}: All props linked!`);
    }
  }

  // 2. Unlinked metrics by source pattern
  console.log('\n=== UNLINKED METRICS ANALYSIS ===\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const unlinked = await prisma.playerGameMetric.findMany({
      where: { league, canonicalPlayerId: null },
      select: { playerExternalId: true, playerName: true },
      distinct: ['playerExternalId'],
      take: 500
    });

    if (unlinked.length > 0) {
      const withNames = unlinked.filter(u => u.playerName && u.playerName.trim());
      const withoutNames = unlinked.filter(u => !u.playerName || !u.playerName.trim());

      console.log(`${league.toUpperCase()}: ${unlinked.length}+ unlinked player IDs`);
      console.log(`  With names (matchable): ${withNames.length}, Without names: ${withoutNames.length}`);

      if (withNames.length > 0) {
        const sampleNames = withNames.slice(0, 3).map(u => u.playerName);
        console.log(`  Sample names: ${sampleNames.join(', ')}`);
      }
    } else {
      console.log(`${league.toUpperCase()}: All metrics linked!`);
    }
  }

  // 3. Players without teams
  console.log('\n=== PLAYERS WITHOUT TEAM ASSIGNMENTS ===\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const noTeam = await prisma.canonicalPlayer.count({
      where: { league, teamId: null }
    });
    const total = await prisma.canonicalPlayer.count({ where: { league } });
    const pct = total > 0 ? ((noTeam / total) * 100).toFixed(1) : '0';
    console.log(`${league.toUpperCase()}: ${noTeam}/${total} without team (${pct}%)`);
  }

  // 4. Games canonical coverage
  console.log('\n=== GAMES CANONICAL COVERAGE ===\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const total = await prisma.sportsGame.count({ where: { league } });
    const canonicalGames = await prisma.canonicalGame.count({ where: { league } });
    console.log(`${league.toUpperCase()}: ${canonicalGames.toLocaleString()} canonical vs ${total.toLocaleString()} raw games`);
  }

  // 5. Alias coverage
  console.log('\n=== ALIAS COVERAGE BY SOURCE ===\n');

  const aliasCounts = await prisma.playerAlias.groupBy({
    by: ['source'],
    _count: true
  });

  for (const { source, _count } of aliasCounts) {
    console.log(`${source}: ${_count.toLocaleString()} aliases`);
  }

  // 6. Data freshness
  console.log('\n=== DATA FRESHNESS (LAST 24H) ===\n');

  const yesterday = new Date(Date.now() - 24 * 60 * 60 * 1000);

  const newProps = await prisma.playerPropLine.count({
    where: { createdAt: { gte: yesterday } }
  });
  const newMetrics = await prisma.playerGameMetric.count({
    where: { createdAt: { gte: yesterday } }
  });
  const newGames = await prisma.canonicalGame.count({
    where: { createdAt: { gte: yesterday } }
  });

  console.log(`New props (24h): ${newProps.toLocaleString()}`);
  console.log(`New metrics (24h): ${newMetrics.toLocaleString()}`);
  console.log(`New canonical games (24h): ${newGames.toLocaleString()}`);

  // 7. Missing critical aliases
  console.log('\n=== MISSING CRITICAL ALIASES ===\n');

  // Find props with no matching alias
  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    const propsWithoutAlias = await prisma.$queryRaw`
      SELECT DISTINCT p."playerExternalId"
      FROM "PlayerPropLine" p
      LEFT JOIN "PlayerAlias" a ON p."playerExternalId" = a."alias"
      WHERE p.league = ${league}
        AND p."canonicalPlayerId" IS NULL
        AND a.id IS NULL
      LIMIT 10
    ` as any[];

    if (propsWithoutAlias.length > 0) {
      console.log(`${league.toUpperCase()}: ${propsWithoutAlias.length}+ props with no alias match`);
      console.log(`  IDs: ${propsWithoutAlias.slice(0, 5).map((p: any) => p.playerExternalId).join(', ')}`);
    }
  }

  // 8. Summary stats
  console.log('\n=== SUMMARY STATS ===\n');

  const totalPlayers = await prisma.canonicalPlayer.count();
  const totalTeams = await prisma.canonicalTeam.count();
  const totalGames = await prisma.canonicalGame.count();
  const totalAliases = await prisma.playerAlias.count();
  const totalProps = await prisma.playerPropLine.count();
  const linkedProps = await prisma.playerPropLine.count({ where: { NOT: { canonicalPlayerId: null } } });
  const totalMetrics = await prisma.playerGameMetric.count();
  const linkedMetrics = await prisma.playerGameMetric.count({ where: { NOT: { canonicalPlayerId: null } } });

  console.log(`Canonical Players: ${totalPlayers.toLocaleString()}`);
  console.log(`Canonical Teams: ${totalTeams.toLocaleString()}`);
  console.log(`Canonical Games: ${totalGames.toLocaleString()}`);
  console.log(`Player Aliases: ${totalAliases.toLocaleString()}`);
  console.log(`Props: ${linkedProps.toLocaleString()}/${totalProps.toLocaleString()} linked (${((linkedProps/totalProps)*100).toFixed(1)}%)`);
  console.log(`Metrics: ${linkedMetrics.toLocaleString()}/${totalMetrics.toLocaleString()} linked (${((linkedMetrics/totalMetrics)*100).toFixed(1)}%)`);

  await prisma.$disconnect();
}

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