#!/usr/bin/env npx tsx
/**
 * Link Props via Player Table
 *
 * Props have numeric playerExternalId that match Player.externalPlayerId
 * Player table has the name, which we can use to find/create canonical player
 */

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

const prisma = new PrismaClient();

function normalizePlayerName(name: string): string {
  if (!name) return '';
  return name.toLowerCase()
    .normalize('NFD').replace(/[\u0300-\u036f]/g, '')
    .replace(/[.']/g, '')
    .replace(/\s+(jr|sr|iii|ii|iv|v)$/i, '')
    .replace(/\s+/g, ' ')
    .trim();
}

async function linkPropsViaPlayerTable() {
  console.log('='.repeat(60));
  console.log('LINKING PROPS VIA PLAYER TABLE');
  console.log('='.repeat(60) + '\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    console.log(`\nProcessing ${league.toUpperCase()}...`);

    // Get unlinked prop player IDs
    const unlinkedProps = await prisma.playerPropLine.findMany({
      where: { league, canonicalPlayerId: null },
      select: { playerExternalId: true },
      distinct: ['playerExternalId'],
    });

    const playerIds = unlinkedProps.map(p => p.playerExternalId);
    console.log(`  Found ${playerIds.length} distinct unlinked player IDs`);

    if (playerIds.length === 0) continue;

    let linked = 0;
    let aliasesCreated = 0;
    let canonicalCreated = 0;

    // Separate numeric IDs from name-based IDs
    const numericIds = playerIds.filter(id => /^\d+$/.test(id));
    const nameIds = playerIds.filter(id => !/^\d+$/.test(id));

    console.log(`  Numeric IDs: ${numericIds.length}, Name-based IDs: ${nameIds.length}`);

    // Handle name-based IDs directly (they ARE the player name)
    for (const playerName of nameIds) {
      if (!playerName || playerName === 'unknown') continue;

      const normalized = normalizePlayerName(playerName);
      if (!normalized) continue;

      // Find or create canonical player
      let canonical = await prisma.canonicalPlayer.findUnique({
        where: { league_normalizedName: { league, normalizedName: normalized } }
      });

      if (!canonical) {
        try {
          canonical = await prisma.canonicalPlayer.create({
            data: {
              league,
              fullName: playerName,
              normalizedName: normalized,
            }
          });
        } catch (e: any) {
          if (e.code === 'P2002') {
            canonical = await prisma.canonicalPlayer.findUnique({
              where: { league_normalizedName: { league, normalizedName: normalized } }
            });
          }
        }
      }

      if (canonical) {
        const updated = await prisma.playerPropLine.updateMany({
          where: { league, playerExternalId: playerName, canonicalPlayerId: null },
          data: { canonicalPlayerId: canonical.id }
        });
        if (updated.count > 0) {
          linked += updated.count;
          // Create alias
          try {
            await prisma.playerAlias.upsert({
              where: { playerId_alias: { playerId: canonical.id, alias: playerName } },
              create: { playerId: canonical.id, source: 'name_id', alias: playerName, aliasType: 'name' },
              update: {}
            });
            aliasesCreated++;
          } catch (e) { /* ignore */ }
        }
      }
    }

    console.log(`  Linked ${linked} props via name-based IDs`);

    // Find matching Player records with names for numeric IDs
    const players = numericIds.length > 0 ? await prisma.player.findMany({
      where: {
        league,
        externalPlayerId: { in: numericIds },
      }
    }) : [];

    console.log(`  Found ${players.length} Player records for numeric IDs`);

    for (const player of players) {
      if (!player.name || !player.externalPlayerId) continue;

      const normalized = normalizePlayerName(player.name);
      if (!normalized) continue;

      // Find or create canonical player
      let canonical = await prisma.canonicalPlayer.findUnique({
        where: { league_normalizedName: { league, normalizedName: normalized } }
      });

      if (!canonical) {
        // Create new canonical player
        try {
          canonical = await prisma.canonicalPlayer.create({
            data: {
              league,
              fullName: player.name,
              normalizedName: normalized,
            }
          });
          canonicalCreated++;
        } catch (e: any) {
          if (e.code === 'P2002') {
            // Already exists, fetch it
            canonical = await prisma.canonicalPlayer.findUnique({
              where: { league_normalizedName: { league, normalizedName: normalized } }
            });
          } else {
            console.error(`  Error creating canonical player for ${player.name}:`, e);
            continue;
          }
        }
      }

      if (!canonical) continue;

      // Link all props with this playerExternalId
      const updated = await prisma.playerPropLine.updateMany({
        where: {
          league,
          playerExternalId: player.externalPlayerId,
          canonicalPlayerId: null
        },
        data: { canonicalPlayerId: canonical.id }
      });
      linked += updated.count;

      // Create alias if needed
      const aliasExists = await prisma.playerAlias.findFirst({
        where: { alias: player.externalPlayerId }
      });
      if (!aliasExists) {
        try {
          await prisma.playerAlias.create({
            data: {
              playerId: canonical.id,
              source: 'player_table_link',
              alias: player.externalPlayerId,
              aliasType: 'external_id'
            }
          });
          aliasesCreated++;
        } catch (e) { /* ignore duplicates */ }
      }
    }

    console.log(`  Linked: ${linked.toLocaleString()} props`);
    console.log(`  Created: ${canonicalCreated} canonical players, ${aliasesCreated} aliases`);
  }

  // Now try to link remaining props by looking them up via alias
  console.log('\n' + '='.repeat(60));
  console.log('SECOND PASS: LINK VIA EXISTING ALIASES');
  console.log('='.repeat(60) + '\n');

  for (const league of ['nba', 'nfl', 'nhl', 'mlb']) {
    // Get remaining unlinked props
    const remaining = await prisma.playerPropLine.findMany({
      where: { league, canonicalPlayerId: null },
      select: { id: true, playerExternalId: true },
      take: 50000
    });

    if (remaining.length === 0) continue;

    console.log(`${league.toUpperCase()}: ${remaining.length} remaining unlinked props`);

    // Group by playerExternalId
    const byPlayer = new Map<string, bigint[]>();
    for (const prop of remaining) {
      const existing = byPlayer.get(prop.playerExternalId) || [];
      existing.push(prop.id);
      byPlayer.set(prop.playerExternalId, existing);
    }

    let linked = 0;

    for (const [externalId, propIds] of byPlayer) {
      // Check if we have an alias for this ID
      const alias = await prisma.playerAlias.findFirst({
        where: { alias: externalId }
      });

      if (alias?.playerId) {
        // Link all these props
        await prisma.playerPropLine.updateMany({
          where: { id: { in: propIds } },
          data: { canonicalPlayerId: alias.playerId }
        });
        linked += propIds.length;
      }
    }

    console.log(`  Linked via alias: ${linked.toLocaleString()} props`);
  }

  // Final coverage report
  console.log('\n' + '='.repeat(60));
  console.log('FINAL PROP COVERAGE');
  console.log('='.repeat(60) + '\n');

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

  await prisma.$disconnect();
}

linkPropsViaPlayerTable().catch(e => {
  console.error('Failed:', e);
  process.exit(1);
});