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

export async function runFalsePositiveTracker(): Promise<void> {
  try {
    const today = new Date().toISOString().split('T')[0];

    // Analyze resolved opportunities to compute false positive rates
    // A "false positive" is an opportunity that was surfaced but didn't materialize

    // By opportunity type
    await computeFPRate('opportunity_type', `
      SELECT opportunity_type as dim_value,
             COUNT(*) as total,
             COUNT(CASE WHEN resolution->>'profitable' = 'false' THEN 1 END) as false_positives
      FROM sc_opportunities
      WHERE status IN ('resolved', 'expired')
        AND created_at > NOW() - INTERVAL '30 days'
      GROUP BY opportunity_type
      HAVING COUNT(*) >= 20
    `, today);

    // By book
    await computeFPRate('book', `
      SELECT book as dim_value,
             COUNT(*) as total,
             COUNT(CASE WHEN resolution->>'profitable' = 'false' THEN 1 END) as false_positives
      FROM sc_opportunities
      WHERE status IN ('resolved', 'expired')
        AND created_at > NOW() - INTERVAL '30 days'
      GROUP BY book
      HAVING COUNT(*) >= 20
    `, today);

    // By league (via game join)
    await computeFPRate('league', `
      SELECT LOWER(sg.league) as dim_value,
             COUNT(*) as total,
             COUNT(CASE WHEN o.resolution->>'profitable' = 'false' THEN 1 END) as false_positives
      FROM sc_opportunities o
      JOIN "SportsGame" sg ON o.game_id = sg.id
      WHERE o.status IN ('resolved', 'expired')
        AND o.created_at > NOW() - INTERVAL '30 days'
      GROUP BY LOWER(sg.league)
      HAVING COUNT(*) >= 20
    `, today);

    // Global false positive rate
    const globalResult = await query(`
      SELECT COUNT(*) as total,
             COUNT(CASE WHEN resolution->>'profitable' = 'false' THEN 1 END) as false_positives
      FROM sc_opportunities
      WHERE status IN ('resolved', 'expired')
        AND created_at > NOW() - INTERVAL '30 days'
    `);

    if (parseInt(globalResult.rows[0]?.total || '0') >= 20) {
      const total = parseInt(globalResult.rows[0].total);
      const fps = parseInt(globalResult.rows[0].false_positives || '0');
      const fpRate = fps / total;

      await query(`
        INSERT INTO sc_scoring_weights (dimension, dimension_value, weight_adjustment, false_positive_rate, sample_size, effective_from)
        VALUES ('global', 'all', $1, $2, $3, $4)
        ON CONFLICT (dimension, dimension_value, effective_from) DO UPDATE SET
          weight_adjustment = $1, false_positive_rate = $2, sample_size = $3
      `, [
        Math.max(0.5, 1.0 - (fpRate - 0.3)), // reduce weight if FP > 30%
        fpRate,
        total,
        today,
      ]);
    }

    // Also resolve opportunities where we can determine outcome using missed_edges data
    await resolveExpiredOpportunities();

    await alerter.logEvent(
      'false_positive_check', 'info', 'false-positive-tracker',
      'False positive rates updated',
      { date: today }
    );
  } catch (err) {
    console.error('[false-positive-tracker] Error:', err);
    throw err;
  }
}

async function computeFPRate(dimension: string, sql: string, today: string): Promise<void> {
  try {
    const result = await query(sql);

    for (const row of result.rows) {
      const total = parseInt(row.total);
      const fps = parseInt(row.false_positives || '0');
      const fpRate = fps / total;

      // Weight adjustment: reduce weight for high FP dimensions
      const weightAdj = Math.max(0.5, 1.0 - Math.max(0, fpRate - 0.3));

      await query(`
        INSERT INTO sc_scoring_weights (dimension, dimension_value, weight_adjustment, false_positive_rate, sample_size, effective_from)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (dimension, dimension_value, effective_from) DO UPDATE SET
          weight_adjustment = $3, false_positive_rate = $4, sample_size = $5
      `, [dimension, row.dim_value, weightAdj, fpRate, total, today]);
    }
  } catch {
    // Skip dimensions that fail
  }
}

async function resolveExpiredOpportunities(): Promise<void> {
  // Use missed_edges to mark opportunity resolution
  try {
    await query(`
      UPDATE sc_opportunities o
      SET status = 'resolved',
          resolved_at = NOW(),
          resolution = jsonb_build_object(
            'profitable', CASE WHEN me.hypothetical_outcome = 'profitable' THEN true ELSE false END,
            'clv', me.hypothetical_clv,
            'outcome', me.hypothetical_outcome
          )
      FROM sc_missed_edges me
      WHERE me.opportunity_id = o.id
        AND o.status = 'expired'
        AND me.hypothetical_outcome IN ('profitable', 'unprofitable')
    `);
  } catch {
    // Best effort resolution
  }
}