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

interface OpportunityCandidate {
  game_id: number;
  book: string;
  market_type: string;
  opportunity_type: string;
  current_line: number;
  consensus_line: number;
  edge_estimate: number;
  score: number;
}

/**
 * Opportunity Scanner — finds trading opportunities from LineMovement data.
 * Only runs when sc_global_state.mode = 'normal'.
 *
 * Scans for:
 * 1. Steam moves with stale books (consensus moved but line hasn't settled)
 * 2. Reverse line moves (contrarian edge)
 * 3. Large sharp-money divergences
 * 4. Pre-game value (big movements close to game time)
 */
export async function runOpportunityScanner(): Promise<void> {
  try {
    // Check global state mode
    const stateResult = await query(`SELECT mode FROM sc_global_state WHERE id = 'primary'`);
    if (stateResult.rows[0]?.mode !== 'normal') return;

    const candidates: OpportunityCandidate[] = [];

    await scanSteamMoveOpps(candidates);
    await scanReverseLineMoveOpps(candidates);
    await scanSharpDivergences(candidates);

    // Score and store opportunities
    let stored = 0;
    const highScoreOpps: OpportunityCandidate[] = [];

    for (const candidate of candidates) {
      // Apply scoring weight adjustments if available
      try {
        const weightResult = await query(`
          SELECT weight_adjustment FROM sc_scoring_weights
          WHERE dimension = 'opportunity_type' AND dimension_value = $1
            AND effective_from <= CURRENT_DATE
          ORDER BY effective_from DESC LIMIT 1
        `, [candidate.opportunity_type]);

        const weightAdj = weightResult.rows[0]?.weight_adjustment
          ? parseFloat(weightResult.rows[0].weight_adjustment) : 1.0;

        candidate.score = Math.min(100, Math.max(0, candidate.score * weightAdj));
      } catch {
        // No weights yet, use raw score
      }

      // Dedup check
      const existing = await query(`
        SELECT id FROM sc_opportunities
        WHERE game_id = $1 AND market_type = $2
          AND opportunity_type = $3 AND status = 'open'
        LIMIT 1
      `, [candidate.game_id, candidate.market_type, candidate.opportunity_type]);

      if (existing.rows.length > 0) continue;

      await query(`
        INSERT INTO sc_opportunities (game_id, book, market_type, opportunity_type, current_line, consensus_line, edge_estimate, score, expires_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW() + INTERVAL '2 hours')
      `, [
        candidate.game_id, candidate.book, candidate.market_type,
        candidate.opportunity_type, candidate.current_line,
        candidate.consensus_line, candidate.edge_estimate, candidate.score,
      ]);

      stored++;
      if (candidate.score > 80) highScoreOpps.push(candidate);
    }

    // Expire old opportunities
    await query(`
      UPDATE sc_opportunities SET status = 'expired', resolved_at = NOW()
      WHERE status = 'open' AND expires_at < NOW()
    `);

    // Alert on high-score opportunities
    if (highScoreOpps.length > 0) {
      const lines = highScoreOpps.map(o =>
        `• ${o.market_type} | ${o.opportunity_type} | Score: ${o.score.toFixed(0)} | Edge: ${o.edge_estimate.toFixed(1)}pts`
      ).join('\n');

      await alerter.alertAdmins(
        'info',
        `🎯 ${highScoreOpps.length} high-score opportunity detected:\n\n${lines}`
      );
    }

    if (stored > 0) {
      await alerter.logEvent(
        'opportunities_found', 'info', 'opportunity-scanner',
        `Found ${stored} new opportunities (${highScoreOpps.length} high-score)`,
        { total: stored, high_score: highScoreOpps.length }
      );
    }
  } catch (err) {
    console.error('[opportunity-scanner] Error:', err);
    throw err;
  }
}

/**
 * Steam moves that happened recently — these create windows
 * where lagging books haven't adjusted yet.
 */
async function scanSteamMoveOpps(candidates: OpportunityCandidate[]): Promise<void> {
  try {
    const result = await query(`
      SELECT
        lm.id as lm_id,
        lm."gameExternalId",
        lm.league,
        lm."marketType",
        lm."openLine",
        lm."currentLine",
        lm."lineMovement",
        lm."recordedAt",
        lm."gameStartTime"
      FROM "LineMovement" lm
      WHERE lm."steamMove" = true
        AND lm."recordedAt" > NOW() - INTERVAL '30 minutes'
        AND lm."lineMovement" IS NOT NULL
        AND ABS(lm."lineMovement") >= 1.0
        AND (lm."gameStartTime" IS NULL OR lm."gameStartTime" > NOW())
      ORDER BY ABS(lm."lineMovement") DESC
      LIMIT 20
    `);

    for (const row of result.rows) {
      const movement = Math.abs(parseFloat(row.lineMovement));
      const currentLine = parseFloat(row.currentLine || '0');
      const openLine = parseFloat(row.openLine || '0');

      // Score: bigger steam moves = better opportunity
      const score = Math.min(95, 50 + movement * 15);

      candidates.push({
        game_id: row.lm_id,
        book: 'consensus',
        market_type: row.marketType,
        opportunity_type: 'steam_move',
        current_line: currentLine,
        consensus_line: openLine,
        edge_estimate: movement,
        score,
      });
    }
  } catch {
    // No steam moves found
  }
}

/**
 * Reverse line moves — line moved opposite to public money.
 * Strong contrarian signal.
 */
async function scanReverseLineMoveOpps(candidates: OpportunityCandidate[]): Promise<void> {
  try {
    const result = await query(`
      SELECT
        lm.id as lm_id,
        lm."gameExternalId",
        lm.league,
        lm."marketType",
        lm."openLine",
        lm."currentLine",
        lm."lineMovement",
        lm."publicPctHome",
        lm."publicPctAway",
        lm."recordedAt"
      FROM "LineMovement" lm
      WHERE lm."reverseLineMove" = true
        AND lm."recordedAt" > NOW() - INTERVAL '2 hours'
        AND lm."lineMovement" IS NOT NULL
        AND ABS(lm."lineMovement") >= 0.5
        AND (lm."gameStartTime" IS NULL OR lm."gameStartTime" > NOW())
      ORDER BY ABS(lm."lineMovement") DESC
      LIMIT 20
    `);

    for (const row of result.rows) {
      const movement = Math.abs(parseFloat(row.lineMovement));
      const publicSkew = Math.abs(
        parseFloat(row.publicPctHome || '50') - parseFloat(row.publicPctAway || '50')
      );

      // Higher score when public heavily favors one side but line moves opposite
      const score = Math.min(90, 45 + movement * 10 + publicSkew * 0.3);

      candidates.push({
        game_id: row.lm_id,
        book: 'consensus',
        market_type: row.marketType,
        opportunity_type: 'reverse_line_move',
        current_line: parseFloat(row.currentLine || '0'),
        consensus_line: parseFloat(row.openLine || '0'),
        edge_estimate: movement,
        score,
      });
    }
  } catch {
    // No RLMs found
  }
}

/**
 * Sharp-money divergences — significant movement flagged as sharp action.
 */
async function scanSharpDivergences(candidates: OpportunityCandidate[]): Promise<void> {
  try {
    const result = await query(`
      SELECT
        lm.id as lm_id,
        lm."gameExternalId",
        lm.league,
        lm."marketType",
        lm."openLine",
        lm."currentLine",
        lm."lineMovement",
        lm."sharpAction",
        lm."recordedAt"
      FROM "LineMovement" lm
      WHERE lm."sharpAction" IS NOT NULL
        AND lm."sharpAction" NOT IN ('NONE', '')
        AND lm."recordedAt" > NOW() - INTERVAL '1 hour'
        AND lm."lineMovement" IS NOT NULL
        AND ABS(lm."lineMovement") >= 1.5
        AND (lm."gameStartTime" IS NULL OR lm."gameStartTime" > NOW())
      ORDER BY ABS(lm."lineMovement") DESC
      LIMIT 20
    `);

    for (const row of result.rows) {
      const movement = Math.abs(parseFloat(row.lineMovement));

      candidates.push({
        game_id: row.lm_id,
        book: 'consensus',
        market_type: row.marketType,
        opportunity_type: 'sharp_divergence',
        current_line: parseFloat(row.currentLine || '0'),
        consensus_line: parseFloat(row.openLine || '0'),
        edge_estimate: movement,
        score: Math.min(85, 40 + movement * 12),
      });
    }
  } catch {
    // No sharp divergences
  }
}