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

interface SimulationParams {
  scoreThreshold: number;
  maxDailyOpps: number;
  fpWeightPenalty: number;
}

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

    // Get last 30 days of resolved opportunities
    const oppsResult = await query(`
      SELECT o.*, me.hypothetical_clv, me.hypothetical_outcome
      FROM sc_opportunities o
      LEFT JOIN sc_missed_edges me ON me.opportunity_id = o.id
      WHERE o.created_at > NOW() - INTERVAL '30 days'
        AND o.status IN ('resolved', 'expired')
      ORDER BY o.created_at
    `);

    if (oppsResult.rows.length < 50) {
      await alerter.logEvent(
        'simulation_skipped', 'info', 'nightly-simulator',
        `Insufficient data for simulation (${oppsResult.rows.length} opportunities, need 50+)`
      );
      return;
    }

    const opportunities = oppsResult.rows;

    // Current parameters (baseline)
    const currentParams: SimulationParams = {
      scoreThreshold: 60,
      maxDailyOpps: 20,
      fpWeightPenalty: 0.3,
    };

    // Variations to test
    const variations: SimulationParams[] = [
      { scoreThreshold: 50, maxDailyOpps: 20, fpWeightPenalty: 0.3 },
      { scoreThreshold: 70, maxDailyOpps: 20, fpWeightPenalty: 0.3 },
      { scoreThreshold: 80, maxDailyOpps: 20, fpWeightPenalty: 0.3 },
      { scoreThreshold: 60, maxDailyOpps: 10, fpWeightPenalty: 0.3 },
      { scoreThreshold: 60, maxDailyOpps: 30, fpWeightPenalty: 0.3 },
      { scoreThreshold: 60, maxDailyOpps: 20, fpWeightPenalty: 0.2 },
      { scoreThreshold: 60, maxDailyOpps: 20, fpWeightPenalty: 0.4 },
      { scoreThreshold: 70, maxDailyOpps: 15, fpWeightPenalty: 0.35 },
    ];

    const currentResult = simulateStrategy(opportunities, currentParams);
    let bestResult = currentResult;
    let bestParams = currentParams;

    const variationResults: Array<{ params: SimulationParams; result: SimResult }> = [];

    for (const params of variations) {
      const result = simulateStrategy(opportunities, params);
      variationResults.push({ params, result });
      if (result.totalCLV > bestResult.totalCLV && result.winRate > 0.45) {
        bestResult = result;
        bestParams = params;
      }
    }

    // Compare best vs current
    const improvement = currentResult.totalCLV !== 0
      ? ((bestResult.totalCLV - currentResult.totalCLV) / Math.abs(currentResult.totalCLV)) * 100
      : bestResult.totalCLV > 0 ? 100 : 0;

    let recommendation = 'No significant improvement found. Current parameters optimal.';
    if (improvement > 5 && bestParams !== currentParams) {
      recommendation = `Recommend: threshold=${bestParams.scoreThreshold}, maxDaily=${bestParams.maxDailyOpps}, fpPenalty=${bestParams.fpWeightPenalty}. Expected improvement: +${improvement.toFixed(1)}% CLV.`;
    }

    await query(`
      INSERT INTO sc_simulation_runs (run_date, parameters, results, vs_current, recommendation)
      VALUES ($1, $2, $3, $4, $5)
    `, [
      today,
      JSON.stringify({
        current: currentParams,
        variations: variationResults.map(v => v.params),
      }),
      JSON.stringify({
        current: currentResult,
        best: bestResult,
        all_variations: variationResults.map(v => ({ ...v.params, ...v.result })),
      }),
      JSON.stringify({
        current_clv: currentResult.totalCLV,
        best_clv: bestResult.totalCLV,
        improvement_pct: improvement,
        best_params: bestParams,
      }),
      recommendation,
    ]);

    await alerter.logEvent(
      'simulation_complete', 'info', 'nightly-simulator',
      `Nightly simulation: current CLV=${currentResult.totalCLV.toFixed(2)}, best=${bestResult.totalCLV.toFixed(2)} (${improvement > 0 ? '+' : ''}${improvement.toFixed(1)}%)`,
      { improvement_pct: improvement, recommendation }
    );
  } catch (err) {
    console.error('[nightly-simulator] Error:', err);
    throw err;
  }
}

interface SimResult {
  totalCLV: number;
  avgCLV: number;
  winRate: number;
  totalOpps: number;
  filteredOpps: number;
}

function simulateStrategy(opportunities: any[], params: SimulationParams): SimResult {
  // Group by date
  const byDate: Record<string, any[]> = {};
  for (const opp of opportunities) {
    const date = new Date(opp.created_at).toISOString().split('T')[0];
    if (!byDate[date]) byDate[date] = [];
    byDate[date].push(opp);
  }

  let totalCLV = 0;
  let wins = 0;
  let filteredOpps = 0;

  for (const [, dayOpps] of Object.entries(byDate)) {
    // Filter by score threshold
    const qualifying = dayOpps
      .filter(o => parseFloat(o.score) >= params.scoreThreshold)
      .sort((a, b) => parseFloat(b.score) - parseFloat(a.score))
      .slice(0, params.maxDailyOpps);

    for (const opp of qualifying) {
      const clv = parseFloat(opp.hypothetical_clv || '0');
      totalCLV += clv;
      if (clv > 0) wins++;
      filteredOpps++;
    }
  }

  return {
    totalCLV,
    avgCLV: filteredOpps > 0 ? totalCLV / filteredOpps : 0,
    winRate: filteredOpps > 0 ? wins / filteredOpps : 0,
    totalOpps: opportunities.length,
    filteredOpps,
  };
}