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

type MovementType = 'steam_move' | 'injury_reaction' | 'stale_line' | 'sharp_action' | 'public_money' | 'correction' | 'unclassified';

/**
 * Movement Classifier — reads from the existing LineMovement table (1.5M+ rows)
 * and classifies movements into sc_line_movements for downstream consumption.
 *
 * Uses ID-based watermarking (LineMovement.id is monotonically increasing)
 * to avoid timestamp-dedup issues. Batch INSERT ON CONFLICT for speed.
 */
export async function runMovementClassifier(): Promise<void> {
  try {
    // ID-based watermark: track the max LM id we've processed
    // We store LM id as game_id in sc_line_movements
    const lastResult = await query(`
      SELECT COALESCE(MAX(game_id), 0) as last_id FROM sc_line_movements
    `);
    const lastId = parseInt(lastResult.rows[0]?.last_id || '0');

    const recentMoves = await query(`
      SELECT
        lm.id as lm_id,
        lm."gameExternalId" as game_ext_id,
        lm.league,
        lm."marketType" as market_type,
        lm."openLine" as open_line,
        lm."currentLine" as current_line,
        lm."lineMovement" as line_movement,
        lm."movementDirection" as direction,
        lm."sharpAction" as sharp_action,
        lm."steamMove" as steam_move,
        lm."reverseLineMove" as reverse_line_move,
        lm."consensusPlay" as consensus_play,
        lm."recordedAt" as recorded_at,
        lm."publicPctHome",
        lm."publicPctAway"
      FROM "LineMovement" lm
      WHERE lm.id > $1
        AND lm."lineMovement" IS NOT NULL
        AND ABS(lm."lineMovement") > 0
      ORDER BY lm.id ASC
      LIMIT 10000
    `, [lastId]);

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

    // Process in batches of 500 with fresh parameter numbering per batch
    const batchSize = 500;
    let classified = 0;

    for (let i = 0; i < recentMoves.rows.length; i += batchSize) {
      const batch = recentMoves.rows.slice(i, i + batchSize);
      const values: any[] = [];
      const placeholders: string[] = [];
      let paramIdx = 1;

      for (const row of batch) {
        const { type, confidence } = classifyFromFlags(row);
        const openLine = row.open_line != null ? parseFloat(row.open_line) : null;
        const currentLine = row.current_line != null ? parseFloat(row.current_line) : null;

        const metadata = JSON.stringify({
          league: row.league,
          game_ext_id: row.game_ext_id,
          line_movement: row.line_movement,
          direction: row.direction,
          sharp_action: row.sharp_action,
          consensus_play: row.consensus_play,
          public_pct_home: row.publicPctHome,
          public_pct_away: row.publicPctAway,
        });

        placeholders.push(
          `($${paramIdx}::bigint, $${paramIdx + 1}, $${paramIdx + 2}, $${paramIdx + 3}::numeric, $${paramIdx + 4}::numeric, $${paramIdx + 5}, $${paramIdx + 6}::numeric, $${paramIdx + 7}::timestamptz, $${paramIdx + 8}::jsonb)`
        );
        values.push(
          row.lm_id, 'consensus', row.market_type,
          openLine, currentLine, type, confidence,
          row.recorded_at, metadata
        );
        paramIdx += 9;
      }

      const result = await query(`
        INSERT INTO sc_line_movements (game_id, book, market_type, old_value, new_value, movement_type, confidence, detected_at, metadata)
        VALUES ${placeholders.join(', ')}
        ON CONFLICT (game_id, market_type, detected_at) DO NOTHING
      `, values);

      classified += result.rowCount || 0;
    }

    if (classified > 0) {
      await alerter.logEvent(
        'movements_classified', 'info', 'movement-classifier',
        `Classified ${classified} line movements (LM ids ${lastId + 1}–${recentMoves.rows[recentMoves.rows.length - 1].lm_id})`,
        { count: classified, from_id: lastId + 1, to_id: recentMoves.rows[recentMoves.rows.length - 1].lm_id }
      );
    }
  } catch (err) {
    console.error('[movement-classifier] Error:', err);
    throw err;
  }
}

function classifyFromFlags(row: any): { type: MovementType; confidence: number } {
  const movement = row.line_movement != null ? Math.abs(parseFloat(row.line_movement)) : 0;

  // Steam move: already flagged by the data pipeline
  if (row.steam_move === true) {
    return { type: 'steam_move', confidence: 0.90 };
  }

  // Reverse line move (correction): line moved opposite to public money
  if (row.reverse_line_move === true) {
    return { type: 'correction', confidence: 0.85 };
  }

  // Sharp action: flagged or large movement with no public money explanation
  const sa = (row.sharp_action || '').toUpperCase();
  if (sa && sa !== '' && sa !== 'NONE') {
    return { type: 'sharp_action', confidence: 0.80 };
  }

  // Public money: when public% is heavily skewed and movement aligns
  const pubHome = row.publicPctHome != null ? parseFloat(row.publicPctHome) : 50;
  const pubAway = row.publicPctAway != null ? parseFloat(row.publicPctAway) : 50;
  const publicSkew = Math.abs(pubHome - pubAway);

  if (publicSkew > 20 && movement < 2.0) {
    return { type: 'public_money', confidence: 0.60 };
  }

  // Large movement without any flag = stale line correction
  if (movement >= 3.0) {
    return { type: 'stale_line', confidence: 0.55 };
  }

  // Moderate movement
  if (movement >= 1.0) {
    return { type: 'sharp_action', confidence: 0.45 };
  }

  return { type: 'unclassified', confidence: 0.30 };
}