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

export async function logMissedEdges(): Promise<void> {
  try {
    // Find expired opportunities with score >60 that don't have missed_edge records yet
    const expiredResult = await query(`
      SELECT o.id, o.game_id, o.book, o.market_type, o.opportunity_type,
             o.current_line, o.consensus_line, o.edge_estimate, o.score,
             o.created_at, o.resolved_at
      FROM sc_opportunities o
      LEFT JOIN sc_missed_edges me ON me.opportunity_id = o.id
      WHERE o.status = 'expired'
        AND o.score > 60
        AND me.id IS NULL
        AND o.resolved_at > NOW() - INTERVAL '48 hours'
      ORDER BY o.score DESC
      LIMIT 100
    `);

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

    let profitable = 0;
    let unprofitable = 0;
    let push = 0;

    for (const opp of expiredResult.rows) {
      // Calculate hypothetical CLV: did the line move in the predicted direction?
      let hypotheticalCLV: number | null = null;
      let outcome = 'unknown';

      try {
        // Get latest line for this game/book/market after the opportunity expired
        const closingResult = await query(`
          SELECT new_value FROM sc_line_movements
          WHERE game_id = $1 AND book = $2 AND market_type = $3
            AND detected_at > $4
          ORDER BY detected_at DESC LIMIT 1
        `, [opp.game_id, opp.book, opp.market_type, opp.created_at]);

        if (closingResult.rows.length > 0) {
          const closingLine = parseFloat(closingResult.rows[0].new_value);
          hypotheticalCLV = closingLine - parseFloat(opp.current_line || opp.consensus_line);

          if (Math.abs(hypotheticalCLV) < 0.5) {
            outcome = 'push';
            push++;
          } else if (hypotheticalCLV > 0) {
            outcome = 'profitable';
            profitable++;
          } else {
            outcome = 'unprofitable';
            unprofitable++;
          }
        }
      } catch {
        // No closing data available
      }

      const lesson = outcome === 'profitable'
        ? `Missed +${hypotheticalCLV?.toFixed(1)}pts CLV on ${opp.opportunity_type} (${opp.book})`
        : outcome === 'unprofitable'
        ? `Would have been ${hypotheticalCLV?.toFixed(1)}pts CLV — ${opp.opportunity_type} was false signal`
        : `No clear outcome for ${opp.opportunity_type}`;

      await query(`
        INSERT INTO sc_missed_edges (opportunity_id, hypothetical_clv, hypothetical_outcome, lesson)
        VALUES ($1, $2, $3, $4)
      `, [opp.id, hypotheticalCLV, outcome, lesson]);
    }

    const total = expiredResult.rows.length;
    await alerter.logEvent(
      'missed_edges_logged', 'info', 'missed-edge-logger',
      `Logged ${total} missed edges: ${profitable} profitable, ${unprofitable} unprofitable, ${push} push`,
      { total, profitable, unprofitable, push }
    );

    // Weekly Telegram summary (on Mondays)
    const now = new Date();
    if (now.getUTCDay() === 1) {
      const weeklyResult = await query(`
        SELECT hypothetical_outcome, COUNT(*) as count, AVG(hypothetical_clv) as avg_clv
        FROM sc_missed_edges
        WHERE created_at > NOW() - INTERVAL '7 days'
        GROUP BY hypothetical_outcome
      `);

      if (weeklyResult.rows.length > 0) {
        const lines = weeklyResult.rows.map(r =>
          `${r.hypothetical_outcome}: ${r.count} (avg CLV: ${parseFloat(r.avg_clv || 0).toFixed(2)})`
        );

        await alerter.alertAdmins(
          'info',
          `📊 Weekly Missed Edge Summary\n\n${lines.join('\n')}`
        );
      }
    }
  } catch (err) {
    console.error('[missed-edge-logger] Error:', err);
    throw err;
  }
}