import { query } from '../../db';
import { alerter } from '../../services/alerter';

/**
 * Book Profiler — computes market behavior profiles per league from LineMovement data.
 * Since LineMovement is consensus-level (not per-book), we profile at the league level:
 * - Average movement magnitude per league
 * - Steam move frequency
 * - Sharp action frequency
 * - Movement volatility
 * - Avg public betting skew
 *
 * Uses sc_book_profiles with book='consensus' and league as the key.
 */
export async function updateBookProfiles(): Promise<void> {
  try {
    // Compute profiles per league from LineMovement (30-day window)
    const profileResult = await query(`
      SELECT
        league,
        COUNT(*) as total_moves,
        AVG(ABS("lineMovement")) as avg_movement,
        STDDEV(ABS("lineMovement")) as movement_volatility,
        SUM(CASE WHEN "steamMove" = true THEN 1 ELSE 0 END) as steam_count,
        SUM(CASE WHEN "sharpAction" IS NOT NULL AND "sharpAction" != 'NONE' AND "sharpAction" != '' THEN 1 ELSE 0 END) as sharp_count,
        SUM(CASE WHEN "reverseLineMove" = true THEN 1 ELSE 0 END) as rlm_count,
        AVG(ABS(COALESCE("publicPctHome", 50) - COALESCE("publicPctAway", 50))) as avg_public_skew,
        AVG(
          CASE WHEN "gameStartTime" IS NOT NULL AND "recordedAt" IS NOT NULL
          THEN EXTRACT(EPOCH FROM "gameStartTime" - "recordedAt")
          END
        ) as avg_time_to_game_seconds
      FROM "LineMovement"
      WHERE "recordedAt" > NOW() - INTERVAL '30 days'
        AND "lineMovement" IS NOT NULL
      GROUP BY league
      HAVING COUNT(*) >= 50
      ORDER BY COUNT(*) DESC
    `);

    let updated = 0;

    for (const row of profileResult.rows) {
      const totalMoves = parseInt(row.total_moves);
      const steamCount = parseInt(row.steam_count || '0');
      const sharpCount = parseInt(row.sharp_count || '0');
      const avgMovement = parseFloat(row.avg_movement || '0');
      const volatility = row.movement_volatility ? Math.round(parseFloat(row.movement_volatility) * 100) : null;

      // leads_pct = steam move rate (market-leading moves)
      const steamPct = (steamCount / totalMoves) * 100;
      // follows_pct = non-steam, non-sharp moves
      const followsPct = 100 - steamPct - ((sharpCount / totalMoves) * 100);

      await query(`
        INSERT INTO sc_book_profiles (book, league, avg_reaction_lag_seconds, lag_volatility_seconds, leads_pct, follows_pct, avg_margin_from_consensus, sample_size, updated_at)
        VALUES ('consensus', $1, $2, $3, $4, $5, $6, $7, NOW())
        ON CONFLICT (book, league) DO UPDATE SET
          avg_reaction_lag_seconds = $2, lag_volatility_seconds = $3,
          leads_pct = $4, follows_pct = $5,
          avg_margin_from_consensus = $6, sample_size = $7, updated_at = NOW()
      `, [
        row.league,
        row.avg_time_to_game_seconds ? Math.round(parseFloat(row.avg_time_to_game_seconds)) : null,
        volatility,
        steamPct.toFixed(2),
        Math.max(0, followsPct).toFixed(2),
        avgMovement.toFixed(2),
        totalMoves,
      ]);

      updated++;
    }

    if (updated > 0) {
      await alerter.logEvent(
        'book_profiles_updated', 'info', 'book-profiler',
        `Updated ${updated} league profiles from LineMovement data`,
        { updated, source: 'LineMovement' }
      );
    }
  } catch (err) {
    console.error('[book-profiler] Error:', err);
    throw err;
  }
}