import pool from '../db';

/**
 * Maps a raw probability (0-100) into a bucket: 50, 60, 70, 80, 90, 100.
 * Returns null if below 50.
 */
export function computeBucket(probabilityRaw: number): number | null {
  if (probabilityRaw < 50) return null;
  if (probabilityRaw >= 100) return 100;
  return Math.floor(probabilityRaw / 10) * 10;
}

export async function archiveForecast(params: {
  forecastId: string;
  eventId: string;
  league: string;
  homeTeam: string;
  awayTeam: string;
  startsAt: string;
  winnerPick: string;
  probabilityRaw: number;
  forecastData: any;
  confidenceScore: number;
  compositeConfidence: number;
  oddsData?: any;
  modelSignals?: any;
}): Promise<string | null> {
  const bucket = computeBucket(params.probabilityRaw);

  const { rows } = await pool.query(
    `INSERT INTO rm_archived_forecasts
      (forecast_id, event_id, league, home_team, away_team, starts_at,
       winner_pick, probability_raw, probability_bucket, forecast_data,
       confidence_score, composite_confidence, odds_data, model_signals)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
     ON CONFLICT (event_id) DO UPDATE SET
       forecast_id = EXCLUDED.forecast_id,
       league = EXCLUDED.league,
       home_team = EXCLUDED.home_team,
       away_team = EXCLUDED.away_team,
       starts_at = EXCLUDED.starts_at,
       winner_pick = EXCLUDED.winner_pick,
       probability_raw = EXCLUDED.probability_raw,
       probability_bucket = EXCLUDED.probability_bucket,
       forecast_data = EXCLUDED.forecast_data,
       confidence_score = EXCLUDED.confidence_score,
       composite_confidence = EXCLUDED.composite_confidence,
       odds_data = EXCLUDED.odds_data,
       model_signals = EXCLUDED.model_signals,
       updated_at = NOW()
     RETURNING id`,
    [
      params.forecastId,
      params.eventId,
      params.league,
      params.homeTeam,
      params.awayTeam,
      params.startsAt,
      params.winnerPick,
      params.probabilityRaw,
      bucket,
      JSON.stringify(params.forecastData),
      params.confidenceScore,
      params.compositeConfidence,
      params.oddsData ? JSON.stringify(params.oddsData) : null,
      params.modelSignals ? JSON.stringify(params.modelSignals) : null,
    ]
  );

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

  await refreshBucketStats();

  return rows[0].id;
}

export async function settleArchivedForecast(
  archivedId: string,
  outcome: 'win' | 'loss' | 'push' | 'void',
  actualWinner: string,
  actualScore: string
): Promise<void> {
  await pool.query(
    `UPDATE rm_archived_forecasts
     SET outcome = $1, actual_winner = $2, actual_score = $3, settled_at = NOW(), updated_at = NOW()
     WHERE id = $4 AND outcome = 'pending'`,
    [outcome, actualWinner, actualScore, archivedId]
  );
}

export async function voidArchivedForecast(
  archivedId: string,
  reason: string,
  actualWinner = 'VOID_INVALID_MATCHUP',
): Promise<void> {
  await pool.query(
    `UPDATE rm_archived_forecasts
     SET outcome = 'void',
         actual_winner = $1,
         actual_score = $2,
         settled_at = NOW(),
         updated_at = NOW()
     WHERE id = $3
       AND outcome = 'pending'`,
    [actualWinner, reason, archivedId],
  );
}

export async function refreshBucketStats(): Promise<void> {
  await pool.query(`
    WITH stats AS (
      SELECT probability_bucket AS bucket,
        COUNT(*) FILTER (WHERE outcome != 'void') AS total,
        COUNT(*) FILTER (WHERE outcome = 'win') AS wins,
        COUNT(*) FILTER (WHERE outcome = 'loss') AS losses,
        COUNT(*) FILTER (WHERE outcome = 'push') AS pushes,
        COUNT(*) FILTER (WHERE outcome = 'pending') AS pending
      FROM rm_archived_forecasts
      WHERE probability_bucket IS NOT NULL
      GROUP BY probability_bucket
    )
    UPDATE rm_archive_bucket_stats bs
    SET total_forecasts = COALESCE(stats.total, 0),
        wins = COALESCE(stats.wins, 0),
        losses = COALESCE(stats.losses, 0),
        pushes = COALESCE(stats.pushes, 0),
        pending = COALESCE(stats.pending, 0),
        win_rate = CASE
          WHEN COALESCE(stats.wins, 0) + COALESCE(stats.losses, 0) > 0
            THEN ROUND(
              COALESCE(stats.wins, 0)::numeric
              / (COALESCE(stats.wins, 0) + COALESCE(stats.losses, 0))
              * 100,
              2
            )
          ELSE NULL
        END,
        updated_at = NOW()
    FROM stats
    RIGHT JOIN rm_archive_bucket_stats buckets ON buckets.bucket = stats.bucket
    WHERE bs.bucket = buckets.bucket
  `);
}

export async function linkBlogPost(archivedId: string, blogPostId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_archived_forecasts SET blog_post_id = $1, updated_at = NOW() WHERE id = $2`,
    [blogPostId, archivedId]
  );
}