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

const FREEZE_TRIGGERS = {
  clvNegativeStreak: 100,       // consecutive negative CLV
  pipelineDownHours: 6,         // hours of stale data
  unresolvedCriticalEvents: 3,  // open critical events
  schemaUnresolvedHours: 2,     // hours since schema drift
  classifierConfidenceMin: 0.4, // system-wide avg confidence
  dataFreshnessMultiple: 3,     // times normal baseline
  falsePositiveRateMax: 0.65,   // max false positive rate
};

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

    // Get recent CLV data for drawdown calculation
    const clvResult = await query(`
      SELECT clv FROM sc_clv_records
      WHERE clv IS NOT NULL
      ORDER BY created_at DESC LIMIT 200
    `);

    const clvValues = clvResult.rows.map(r => parseFloat(r.clv));

    // Compute drawdown
    let peakEquity = 0;
    let currentEquity = 0;
    let maxDrawdown = 0;
    for (const clv of clvValues.reverse()) {
      currentEquity += clv;
      peakEquity = Math.max(peakEquity, currentEquity);
      const drawdown = peakEquity > 0 ? ((peakEquity - currentEquity) / peakEquity) * 100 : 0;
      maxDrawdown = Math.max(maxDrawdown, drawdown);
    }

    // Count daily exposure (opportunities acted on today)
    const exposureResult = await query(`
      SELECT COUNT(*) as count FROM sc_opportunities
      WHERE created_at::date = CURRENT_DATE AND status != 'expired'
    `);
    const dailyExposure = parseInt(exposureResult.rows[0]?.count || '0');

    // Determine risk level
    let riskLevel = 'normal';
    const recommendations: string[] = [];

    if (maxDrawdown > 10) {
      riskLevel = 'high';
      recommendations.push('Drawdown >10%: recommend 50% stake reduction');
    }
    if (dailyExposure > 20) {
      riskLevel = maxDrawdown > 5 ? 'high' : 'elevated';
      recommendations.push(`High daily exposure: ${dailyExposure} opportunities`);
    }

    // Check consecutive losses
    let consecutiveLosses = 0;
    for (const clv of clvValues.slice(-50)) {
      if (clv < 0) consecutiveLosses++;
      else consecutiveLosses = 0;
    }
    if (consecutiveLosses >= 5) {
      recommendations.push(`${consecutiveLosses} consecutive losses: recommend reduced sizing for 10 picks`);
      if (riskLevel === 'normal') riskLevel = 'elevated';
    }

    // Store daily risk state
    await query(`
      INSERT INTO sc_risk_state (state_date, daily_exposure, drawdown_pct, risk_level, active_limits, recommendation)
      VALUES ($1, $2, $3, $4, $5, $6)
      ON CONFLICT (state_date) DO UPDATE SET
        daily_exposure = $2, drawdown_pct = $3, risk_level = $4,
        active_limits = $5, recommendation = $6
    `, [
      today, dailyExposure, maxDrawdown.toFixed(2), riskLevel,
      JSON.stringify({ consecutive_losses: consecutiveLosses, max_drawdown: maxDrawdown }),
      recommendations.join('; ') || 'No active risk concerns',
    ]);

    // --- Auto-Freeze Logic ---
    await checkFreezeConditions();

    if (riskLevel === 'high') {
      await alerter.alertAdmins(
        'warn',
        `⚠️ Risk Level: HIGH\n\nDrawdown: ${maxDrawdown.toFixed(1)}%\nDaily Exposure: ${dailyExposure}\nConsecutive Losses: ${consecutiveLosses}\n\nRecommendations:\n${recommendations.map(r => `• ${r}`).join('\n')}`,
        { risk_level: riskLevel, drawdown: maxDrawdown, daily_exposure: dailyExposure }
      );
    }
  } catch (err) {
    console.error('[risk-manager] Error:', err);
    throw err;
  }
}

async function checkFreezeConditions(): Promise<void> {
  const freezeReasons: Array<{ reason: string; triggered_at: string; source: string }> = [];

  // 1. CLV negative streak
  try {
    const streakResult = await query(`
      SELECT clv FROM sc_clv_records
      WHERE clv IS NOT NULL ORDER BY created_at DESC LIMIT ${FREEZE_TRIGGERS.clvNegativeStreak}
    `);
    const allNegative = streakResult.rows.length >= FREEZE_TRIGGERS.clvNegativeStreak &&
      streakResult.rows.every(r => parseFloat(r.clv) < 0);
    if (allNegative) {
      freezeReasons.push({ reason: `CLV negative for ${FREEZE_TRIGGERS.clvNegativeStreak}+ consecutive picks`, triggered_at: new Date().toISOString(), source: 'risk-manager' });
    }
  } catch {}

  // 2. Data pipeline down
  try {
    const freshnessResult = await query(`SELECT MAX("updatedAt") as latest FROM "PlayerPropLine"`);
    if (freshnessResult.rows[0]?.latest) {
      const ageHours = (Date.now() - new Date(freshnessResult.rows[0].latest).getTime()) / 3600000;
      if (ageHours > FREEZE_TRIGGERS.pipelineDownHours) {
        freezeReasons.push({ reason: `Data pipeline stale for ${ageHours.toFixed(1)}h`, triggered_at: new Date().toISOString(), source: 'risk-manager' });
      }
    }
  } catch {}

  // 3. Unresolved critical events
  try {
    const critResult = await query(
      `SELECT COUNT(*) as count FROM sc_system_events WHERE severity = 'critical' AND resolved = FALSE`
    );
    const critCount = parseInt(critResult.rows[0]?.count || '0');
    if (critCount >= FREEZE_TRIGGERS.unresolvedCriticalEvents) {
      freezeReasons.push({ reason: `${critCount} unresolved critical events`, triggered_at: new Date().toISOString(), source: 'risk-manager' });
    }
  } catch {}

  // 4. Classifier confidence too low
  try {
    const confResult = await query(`
      SELECT AVG(confidence) as avg_conf FROM sc_line_movements
      WHERE detected_at > NOW() - INTERVAL '24 hours'
    `);
    if (confResult.rows[0]?.avg_conf) {
      const avgConf = parseFloat(confResult.rows[0].avg_conf);
      if (avgConf < FREEZE_TRIGGERS.classifierConfidenceMin) {
        freezeReasons.push({ reason: `Classifier confidence ${avgConf.toFixed(2)} below threshold`, triggered_at: new Date().toISOString(), source: 'risk-manager' });
      }
    }
  } catch {}

  // 5. False positive rate too high
  try {
    const fpResult = await query(`
      SELECT false_positive_rate FROM sc_scoring_weights
      WHERE dimension = 'global' AND dimension_value = 'all'
      ORDER BY effective_from DESC LIMIT 1
    `);
    if (fpResult.rows[0]?.false_positive_rate) {
      const fpRate = parseFloat(fpResult.rows[0].false_positive_rate);
      if (fpRate > FREEZE_TRIGGERS.falsePositiveRateMax) {
        freezeReasons.push({ reason: `False positive rate ${(fpRate * 100).toFixed(1)}% exceeds threshold`, triggered_at: new Date().toISOString(), source: 'risk-manager' });
      }
    }
  } catch {}

  // Apply freeze if conditions met
  if (freezeReasons.length > 0) {
    const currentState = await query(`SELECT mode FROM sc_global_state WHERE id = 'primary'`);
    const currentMode = currentState.rows[0]?.mode || 'normal';

    if (currentMode !== 'frozen') {
      await query(`
        UPDATE sc_global_state
        SET mode = 'frozen',
            active_freeze_reasons = $1,
            last_updated = NOW(),
            updated_by = 'risk-manager'
        WHERE id = 'primary'
      `, [JSON.stringify(freezeReasons)]);

      await alerter.alertAdmins(
        'critical',
        `🧊 SYSTEM FROZEN\n\nReasons:\n${freezeReasons.map(r => `• ${r.reason}`).join('\n')}\n\nUnfreeze via Admin Dashboard when resolved.`,
        { freeze_reasons: freezeReasons }
      );
    }
  }

  // Schedule 4-hour re-alert for frozen state
  try {
    const stateResult = await query(`SELECT mode, last_updated FROM sc_global_state WHERE id = 'primary'`);
    if (stateResult.rows[0]?.mode === 'frozen') {
      const lastUpdated = new Date(stateResult.rows[0].last_updated);
      const hoursSinceFrozen = (Date.now() - lastUpdated.getTime()) / 3600000;

      // Re-alert every 4 hours
      const lastAlertResult = await query(`
        SELECT created_at FROM sc_system_events
        WHERE event_type = 'freeze_reminder' AND source = 'risk-manager'
        ORDER BY created_at DESC LIMIT 1
      `);

      const lastAlertTime = lastAlertResult.rows[0]?.created_at
        ? new Date(lastAlertResult.rows[0].created_at).getTime() : 0;
      const hoursSinceLastAlert = (Date.now() - lastAlertTime) / 3600000;

      if (hoursSinceLastAlert >= 4) {
        await alerter.logEvent(
          'freeze_reminder', 'warn', 'risk-manager',
          `System still FROZEN for ${hoursSinceFrozen.toFixed(1)} hours. Manual unfreeze required.`
        );
        await alerter.alertAdmins(
          'warn',
          `🧊 System still FROZEN (${hoursSinceFrozen.toFixed(1)}h). Manual unfreeze required via Admin Dashboard.`
        );
      }
    }
  } catch {}
}