#!/usr/bin/env npx tsx
/**
 * Migration script to populate BookmakerOdds from existing GameOdds
 * This consolidates fragmented odds data into the unified schema
 */

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

const sports = new SportsClient();

async function migrateGameOddsToBookmakerOdds() {
  console.log('=== Migrating GameOdds to BookmakerOdds ===\n');

  // Get all GameOdds records
  const gameOdds = await sports.gameOdds.findMany({
    orderBy: { id: 'asc' },
  });
  console.log(`Found ${gameOdds.length} GameOdds records to migrate`);

  let migrated = 0;
  let skipped = 0;
  let errors = 0;

  for (const go of gameOdds) {
    try {
      // Find the matching SportsGame
      const game = await sports.sportsGame.findFirst({
        where: {
          league: go.league,
          externalGameId: go.gameId,
        },
      });

      if (!game) {
        skipped++;
        continue;
      }

      // Determine market type and line values
      const market = go.market.toLowerCase();
      let lineValue: number | null = null;
      let homeOdds: number | null = null;
      let awayOdds: number | null = null;

      if (market === 'spread' || market === 'spreads') {
        lineValue = go.lineValue;
        homeOdds = go.homeOdds;
        awayOdds = go.awayOdds;
      } else if (market === 'totals' || market === 'total') {
        lineValue = go.lineValue;
        homeOdds = go.overOdds;  // over
        awayOdds = go.underOdds; // under
      } else if (market === 'h2h' || market === 'moneyline') {
        homeOdds = go.homeOdds;
        awayOdds = go.awayOdds;
      }

      // Upsert into BookmakerOdds
      await sports.bookmakerOdds.upsert({
        where: {
          gameId_bookmaker_market_lineValue: {
            gameId: game.id,
            bookmaker: go.bookmaker || 'unknown',
            market: market === 'h2h' ? 'moneyline' : market,
            lineValue: lineValue || 0,
          },
        },
        create: {
          gameId: game.id,
          league: go.league,
          gameDate: go.gameDate || game.gameDate,
          homeTeam: go.homeTeam || game.homeTeam,
          awayTeam: go.awayTeam || game.awayTeam,
          bookmaker: go.bookmaker || 'unknown',
          market: market === 'h2h' ? 'moneyline' : market,
          lineValue: lineValue,
          homeOdds: homeOdds,
          awayOdds: awayOdds,
          openingLineValue: go.openingLineValue,
          openingHomeOdds: go.openingHomeOdds || go.openingOverOdds,
          openingAwayOdds: go.openingAwayOdds || go.openingUnderOdds,
          isBestLine: go.isBestLine || false,
          source: go.source || 'odds-api',
          fetchedAt: go.fetchedAt || new Date(),
        },
        update: {
          homeOdds: homeOdds,
          awayOdds: awayOdds,
          fetchedAt: go.fetchedAt || new Date(),
        },
      });

      migrated++;
      if (migrated % 1000 === 0) {
        console.log(`  Migrated ${migrated} records...`);
      }
    } catch (err: any) {
      errors++;
      if (errors <= 5) {
        console.error(`  Error migrating GameOdds ${go.id}: ${err.message}`);
      }
    }
  }

  console.log(`\nGameOdds migration complete:`);
  console.log(`  Migrated: ${migrated}`);
  console.log(`  Skipped (no game found): ${skipped}`);
  console.log(`  Errors: ${errors}`);
}

async function migrateAltLinesToBookmakerOdds() {
  console.log('\n=== Migrating GameOddsAltLine to BookmakerOdds ===\n');

  const altLines = await sports.gameOddsAltLine.findMany({
    orderBy: { id: 'asc' },
  });
  console.log(`Found ${altLines.length} alt line records to migrate`);

  let migrated = 0;
  let skipped = 0;
  let errors = 0;

  for (const alt of altLines) {
    try {
      const game = await sports.sportsGame.findFirst({
        where: {
          league: alt.league,
          externalGameId: alt.gameId,
        },
      });

      if (!game) {
        skipped++;
        continue;
      }

      // Determine market type
      const market = alt.market.toLowerCase().includes('spread') ? 'spread_alt' : 'total_alt';

      await sports.bookmakerOdds.upsert({
        where: {
          gameId_bookmaker_market_lineValue: {
            gameId: game.id,
            bookmaker: alt.bookmaker || 'unknown',
            market: market,
            lineValue: alt.lineValue || 0,
          },
        },
        create: {
          gameId: game.id,
          league: alt.league,
          gameDate: alt.gameDate || game.gameDate,
          homeTeam: alt.homeTeam || game.homeTeam,
          awayTeam: alt.awayTeam || game.awayTeam,
          bookmaker: alt.bookmaker || 'unknown',
          market: market,
          lineValue: alt.lineValue,
          homeOdds: alt.oddsAmerican,
          awayOdds: null,
          source: alt.source || 'the-odds-api',
          fetchedAt: alt.fetchedAt || new Date(),
        },
        update: {
          homeOdds: alt.oddsAmerican,
          fetchedAt: alt.fetchedAt || new Date(),
        },
      });

      migrated++;
      if (migrated % 1000 === 0) {
        console.log(`  Migrated ${migrated} records...`);
      }
    } catch (err: any) {
      errors++;
      if (errors <= 5) {
        console.error(`  Error migrating alt line ${alt.id}: ${err.message}`);
      }
    }
  }

  console.log(`\nAlt lines migration complete:`);
  console.log(`  Migrated: ${migrated}`);
  console.log(`  Skipped: ${skipped}`);
  console.log(`  Errors: ${errors}`);
}

async function migrateSnapshotsToHistory() {
  console.log('\n=== Migrating OddsSnapshot to OddsHistoryV2 ===\n');

  const snapshots = await sports.oddsSnapshot.findMany({
    orderBy: { id: 'asc' },
  });
  console.log(`Found ${snapshots.length} snapshots to migrate`);

  let migrated = 0;
  let skipped = 0;
  let errors = 0;

  for (const snap of snapshots) {
    try {
      // Find game
      const game = snap.gameId
        ? await sports.sportsGame.findUnique({ where: { id: snap.gameId } })
        : await sports.sportsGame.findFirst({
            where: { league: snap.league, externalGameId: snap.externalGameId },
          });

      if (!game) {
        skipped++;
        continue;
      }

      // Create spread snapshot
      if (snap.spreadHome !== null) {
        await sports.oddsHistoryV2.upsert({
          where: {
            gameId_bookmaker_market_lineValue_snapshotAt: {
              gameId: game.id,
              bookmaker: snap.bookmaker || 'consensus',
              market: 'spread',
              lineValue: snap.spreadHome || 0,
              snapshotAt: snap.snapshotAt,
            },
          },
          create: {
            gameId: game.id,
            league: snap.league,
            gameDate: snap.gameDate,
            bookmaker: snap.bookmaker || 'consensus',
            market: 'spread',
            lineValue: snap.spreadHome,
            homeOdds: snap.spreadHomeOdds,
            awayOdds: snap.spreadAwayOdds,
            snapshotAt: snap.snapshotAt,
            hoursToGame: snap.hoursToGame,
            snapshotType: snap.snapshotType,
            source: snap.source,
          },
          update: {},
        });
        migrated++;
      }

      // Create total snapshot
      if (snap.total !== null) {
        await sports.oddsHistoryV2.upsert({
          where: {
            gameId_bookmaker_market_lineValue_snapshotAt: {
              gameId: game.id,
              bookmaker: snap.bookmaker || 'consensus',
              market: 'total',
              lineValue: snap.total || 0,
              snapshotAt: snap.snapshotAt,
            },
          },
          create: {
            gameId: game.id,
            league: snap.league,
            gameDate: snap.gameDate,
            bookmaker: snap.bookmaker || 'consensus',
            market: 'total',
            lineValue: snap.total,
            homeOdds: snap.totalOverOdds,
            awayOdds: snap.totalUnderOdds,
            snapshotAt: snap.snapshotAt,
            hoursToGame: snap.hoursToGame,
            snapshotType: snap.snapshotType,
            source: snap.source,
          },
          update: {},
        });
        migrated++;
      }

      // Create moneyline snapshot
      if (snap.moneylineHome !== null) {
        await sports.oddsHistoryV2.upsert({
          where: {
            gameId_bookmaker_market_lineValue_snapshotAt: {
              gameId: game.id,
              bookmaker: snap.bookmaker || 'consensus',
              market: 'moneyline',
              lineValue: 0,
              snapshotAt: snap.snapshotAt,
            },
          },
          create: {
            gameId: game.id,
            league: snap.league,
            gameDate: snap.gameDate,
            bookmaker: snap.bookmaker || 'consensus',
            market: 'moneyline',
            lineValue: 0,
            homeOdds: snap.moneylineHome,
            awayOdds: snap.moneylineAway,
            snapshotAt: snap.snapshotAt,
            hoursToGame: snap.hoursToGame,
            snapshotType: snap.snapshotType,
            source: snap.source,
          },
          update: {},
        });
        migrated++;
      }

      if (migrated % 1000 === 0) {
        console.log(`  Migrated ${migrated} records...`);
      }
    } catch (err: any) {
      errors++;
      if (errors <= 5) {
        console.error(`  Error migrating snapshot ${snap.id}: ${err.message}`);
      }
    }
  }

  console.log(`\nSnapshot migration complete:`);
  console.log(`  Migrated: ${migrated}`);
  console.log(`  Skipped: ${skipped}`);
  console.log(`  Errors: ${errors}`);
}

async function showStats() {
  console.log('\n=== Final Statistics ===\n');

  const [
    bookmakerOddsCount,
    oddsHistoryCount,
    gameOddsCount,
    altLineCount,
    snapshotCount,
  ] = await Promise.all([
    sports.bookmakerOdds.count(),
    sports.oddsHistoryV2.count(),
    sports.gameOdds.count(),
    sports.gameOddsAltLine.count(),
    sports.oddsSnapshot.count(),
  ]);

  console.log('New unified tables:');
  console.log(`  BookmakerOdds: ${bookmakerOddsCount.toLocaleString()}`);
  console.log(`  OddsHistoryV2: ${oddsHistoryCount.toLocaleString()}`);

  console.log('\nLegacy tables (can be deprecated):');
  console.log(`  GameOdds: ${gameOddsCount.toLocaleString()}`);
  console.log(`  GameOddsAltLine: ${altLineCount.toLocaleString()}`);
  console.log(`  OddsSnapshot: ${snapshotCount.toLocaleString()}`);
}

async function main() {
  console.log('====================================================');
  console.log('ODDS SCHEMA MIGRATION');
  console.log('Migrating from fragmented to unified odds structure');
  console.log('====================================================\n');

  await migrateGameOddsToBookmakerOdds();
  await migrateAltLinesToBookmakerOdds();
  await migrateSnapshotsToHistory();
  await showStats();

  console.log('\nMigration complete!');
  console.log('The new BookmakerOdds and OddsHistoryV2 tables are now populated.');
  console.log('Legacy tables have been preserved for backwards compatibility.');
}

main()
  .catch(console.error)
  .finally(() => sports.$disconnect());