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

export async function runCLVTracker(): Promise<void> {
  // Find settled games that have picks but no CLV records yet
  // We look at sc_picks for user picks and PlayerPropLine for line data
  try {
    // Get picks that don't yet have CLV records
    // sc_picks uses: id (bigint), game_key (text), league, pick_type, line_value, created_at
    const picksResult = await query(`
      SELECT sp.id as pick_id, sp.game_key as game_id, sp.league,
             sp.pick_type as market_type,
             COALESCE((sp.inputs_snapshot->>'book')::text, 'consensus') as book,
             sp.line_value as pick_line, sp.created_at as pick_at
      FROM sc_picks sp
      LEFT JOIN sc_clv_records clv ON clv.pick_id = sp.id
      WHERE clv.id IS NULL
        AND sp.created_at < NOW() - INTERVAL '4 hours'
        AND sp.line_value IS NOT NULL
      ORDER BY sp.created_at DESC
      LIMIT 500
    `);

    if (picksResult.rows.length === 0) return;

    let processed = 0;
    let withCLV = 0;

    for (const pick of picksResult.rows) {
      try {
        // Find closing line — try LineMovement first (consensus closing lines),
        // then fall back to PlayerPropLine
        let closingLine: string | null = null;
        let closedAt: string | null = null;

        // Try LineMovement (has explicit closingLine column)
        const lmResult = await query(`
          SELECT "closingLine" as line, "recordedAt" as closed_at
          FROM "LineMovement"
          WHERE "gameExternalId" = $1
            AND "closingLine" IS NOT NULL
          ORDER BY "recordedAt" DESC
          LIMIT 1
        `, [pick.game_id]);

        if (lmResult.rows[0]?.line) {
          closingLine = lmResult.rows[0].line;
          closedAt = lmResult.rows[0].closed_at;
        } else {
          // Fallback to PlayerPropLine
          const pplResult = await query(`
            SELECT ppl."lineValue" as line, ppl."updatedAt" as closed_at
            FROM "PlayerPropLine" ppl
            WHERE ppl."gameKey" = $1
              AND ppl."updatedAt" > $2
            ORDER BY ppl."updatedAt" DESC
            LIMIT 1
          `, [pick.game_id, pick.pick_at]);

          closingLine = pplResult.rows[0]?.line || null;
          closedAt = pplResult.rows[0]?.closed_at || null;
        }
        const clv = closingLine != null ? parseFloat(closingLine) - parseFloat(pick.pick_line) : null;

        await query(`
          INSERT INTO sc_clv_records (pick_id, game_id, league, market_type, book, pick_line, closing_line, clv, pick_at, closed_at)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        `, [pick.pick_id, pick.game_id, pick.league, pick.market_type, pick.book,
            pick.pick_line, closingLine, clv, pick.pick_at, closedAt]);

        processed++;
        if (clv !== null) withCLV++;
      } catch {
        // Skip individual pick errors
      }
    }

    // Compute rolling aggregates
    await computeEdgeMetrics();

    if (processed > 0) {
      await alerter.logEvent(
        'clv_batch', 'info', 'clv-tracker',
        `Processed ${processed} picks (${withCLV} with closing lines)`,
        { processed, with_clv: withCLV }
      );
    }
  } catch (err) {
    console.error('[clv-tracker] Error:', err);
    throw err;
  }
}

async function computeEdgeMetrics(): Promise<void> {
  const today = new Date().toISOString().split('T')[0];
  const leagues = ['nba', 'nfl', 'nhl', 'mlb', 'ncaab', 'ncaaf'];
  const windows = [50, 200, 0]; // 0 = all-time

  for (const league of leagues) {
    for (const windowSize of windows) {
      try {
        let sql: string;
        const params: any[] = [league];

        if (windowSize > 0) {
          sql = `
            SELECT
              AVG(clv) as avg_clv,
              AVG(CASE WHEN clv > 0 THEN 1.0 ELSE 0.0 END) as positive_rate,
              COUNT(*) as sample_size
            FROM (
              SELECT clv FROM sc_clv_records
              WHERE league = $1 AND clv IS NOT NULL
              ORDER BY created_at DESC
              LIMIT $2
            ) sub
          `;
          params.push(windowSize);
        } else {
          sql = `
            SELECT
              AVG(clv) as avg_clv,
              AVG(CASE WHEN clv > 0 THEN 1.0 ELSE 0.0 END) as positive_rate,
              COUNT(*) as sample_size
            FROM sc_clv_records
            WHERE league = $1 AND clv IS NOT NULL
          `;
        }

        const result = await query(sql, params);
        const row = result.rows[0];
        if (!row || row.sample_size === 0) continue;

        // Determine trend
        let edgeTrend = 'stable';
        if (row.avg_clv < 0) edgeTrend = 'critical';
        else if (row.positive_rate < 0.48) edgeTrend = 'critical';
        else if (row.positive_rate < 0.52) edgeTrend = 'decaying';
        else if (row.avg_clv > 0.5) edgeTrend = 'improving';

        await query(`
          INSERT INTO sc_edge_metrics (metric_date, league, window_size, avg_clv, positive_clv_rate, edge_trend, sample_size)
          VALUES ($1, $2, $3, $4, $5, $6, $7)
          ON CONFLICT (metric_date, league, window_size)
          DO UPDATE SET avg_clv = $4, positive_clv_rate = $5, edge_trend = $6, sample_size = $7
        `, [today, league, windowSize, row.avg_clv, row.positive_rate, edgeTrend, parseInt(row.sample_size)]);
      } catch {
        // Skip individual metric errors
      }
    }
  }
}