import pool from '../db';
import { normalizeTeamNameKey, teamNameKeySql } from '../lib/team-abbreviations';

const TEAM_MATCH_WINDOW_HOURS = 18;

export interface RmForecast {
  id: string;
  event_id: string;
  league: string;
  home_team: string;
  away_team: string;
  forecast_data: any;
  confidence_score: number;
  starts_at: string;
  expires_at: string;
  created_at: string;
  odds_data: any;
  odds_updated_at: string | null;
  composite_confidence: number | null;
  model_signals: any;
  composite_version: string | null;
  last_refresh_at: string | null;
  last_refresh_type: string | null;
  refresh_count: number;
  material_change: any;
  input_quality: any;
  narrative_metadata: any;
}

export async function getCachedForecast(eventId: string): Promise<RmForecast | null> {
  const { rows } = await pool.query(
    'SELECT * FROM rm_forecast_cache WHERE event_id = $1 ORDER BY created_at DESC LIMIT 1',
    [eventId]
  );
  return rows[0] || null;
}

function buildNearestStartDeltaSql(column: string): string {
  return `ABS(EXTRACT(EPOCH FROM (${column} - $3::timestamptz)))`;
}

function buildNearestStartsAtFilterSql(column: string): string {
  return `${column} BETWEEN $3::timestamptz - INTERVAL '${TEAM_MATCH_WINDOW_HOURS} hours'
       AND $3::timestamptz + INTERVAL '${TEAM_MATCH_WINDOW_HOURS} hours'`;
}

/**
 * Resolve any event ID (SGO random or custom) to the canonical rm_events event_id.
 * Returns the rm_events.event_id if a match is found by team+date, otherwise the original ID.
 */
export async function resolveCanonicalEventId(
  eventId: string,
  homeTeam: string,
  awayTeam: string,
  startsAt: string,
): Promise<string> {
  const homeTeamKey = normalizeTeamNameKey(homeTeam);
  const awayTeamKey = normalizeTeamNameKey(awayTeam);
  const { rows } = await pool.query(
    `SELECT event_id FROM rm_events
     WHERE ${teamNameKeySql('home_team')} = $1
       AND ${teamNameKeySql('away_team')} = $2
       AND ${buildNearestStartsAtFilterSql('starts_at')}
     ORDER BY ${buildNearestStartDeltaSql('starts_at')} ASC, event_id ASC
     LIMIT 1`,
    [homeTeamKey, awayTeamKey, startsAt]
  );
  return rows[0]?.event_id || eventId;
}

/** Fallback: find cached forecast by team names + date (handles SGO vs custom ID mismatch) */
export async function getCachedForecastByTeams(homeTeam: string, awayTeam: string, startsAt: string): Promise<RmForecast | null> {
  const homeTeamKey = normalizeTeamNameKey(homeTeam);
  const awayTeamKey = normalizeTeamNameKey(awayTeam);
  const { rows } = await pool.query(
    `SELECT * FROM rm_forecast_cache
     WHERE ${teamNameKeySql('home_team')} = $1
       AND ${teamNameKeySql('away_team')} = $2
       AND ${buildNearestStartsAtFilterSql('starts_at')}
     ORDER BY ${buildNearestStartDeltaSql('starts_at')} ASC, created_at DESC
     LIMIT 1`,
    [homeTeamKey, awayTeamKey, startsAt]
  );
  return rows[0] || null;
}

export async function cacheForecast(data: {
  eventId: string;
  league: string;
  homeTeam: string;
  awayTeam: string;
  forecastData: any;
  confidenceScore: number;
  startsAt: string;
  expiresAt: string;
  oddsData?: any;
}): Promise<RmForecast> {
  // ── Publish-time validation gate: ensure clip_metadata exists ──
  const fd = data.forecastData;
  const hasPicks = fd && (fd.winner_pick || fd.spread_analysis || fd.total_analysis);
  const hasClipMeta = Array.isArray(fd?.clip_metadata) && fd.clip_metadata.length > 0;
  if (hasPicks && !hasClipMeta) {
    console.error(`[CLIP VALIDATION] forecast for ${data.eventId} has picks but MISSING clip_metadata — clipping will be unavailable for this forecast`);
  }

  // Always resolve to the canonical rm_events event_id so cache entries
  // match regardless of whether the caller used an SGO ID or custom ID.
  const homeTeamKey = normalizeTeamNameKey(data.homeTeam);
  const awayTeamKey = normalizeTeamNameKey(data.awayTeam);
  const { rows: canonicalRows } = await pool.query(
    `SELECT event_id FROM rm_events
     WHERE ${teamNameKeySql('home_team')} = $1
       AND ${teamNameKeySql('away_team')} = $2
       AND ${buildNearestStartsAtFilterSql('starts_at')}
     ORDER BY ${buildNearestStartDeltaSql('starts_at')} ASC, event_id ASC
     LIMIT 1`,
    [homeTeamKey, awayTeamKey, data.startsAt]
  );
  const canonicalId = canonicalRows[0]?.event_id || data.eventId;

  // Check if a forecast already exists for this team+date combo
  const { rows: existing } = await pool.query(
    `SELECT id, event_id FROM rm_forecast_cache
     WHERE ${teamNameKeySql('home_team')} = $1
       AND ${teamNameKeySql('away_team')} = $2
       AND ${buildNearestStartsAtFilterSql('starts_at')}
     ORDER BY ${buildNearestStartDeltaSql('starts_at')} ASC, created_at DESC
     LIMIT 1`,
    [homeTeamKey, awayTeamKey, data.startsAt]
  );

  if (existing.length > 0) {
    // Update existing forecast — always stamp the canonical event_id
    const { rows } = await pool.query(
      `UPDATE rm_forecast_cache SET
         event_id = $1, forecast_data = $2, confidence_score = $3, expires_at = $4,
         odds_data = $5, odds_updated_at = NOW()
       WHERE id = $6
       RETURNING *`,
      [canonicalId, JSON.stringify(data.forecastData), data.confidenceScore, data.expiresAt,
       data.oddsData ? JSON.stringify(data.oddsData) : null, existing[0].id]
    );
    return rows[0];
  }

  const { rows } = await pool.query(
    `INSERT INTO rm_forecast_cache (event_id, league, home_team, away_team, forecast_data, confidence_score, starts_at, expires_at, odds_data, odds_updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
     ON CONFLICT (event_id) DO UPDATE SET
       forecast_data = EXCLUDED.forecast_data,
       confidence_score = EXCLUDED.confidence_score,
       expires_at = EXCLUDED.expires_at,
       odds_data = EXCLUDED.odds_data,
       odds_updated_at = NOW()
     RETURNING *`,
    [canonicalId, data.league, data.homeTeam, data.awayTeam, JSON.stringify(data.forecastData), data.confidenceScore, data.startsAt, data.expiresAt, data.oddsData ? JSON.stringify(data.oddsData) : null]
  );
  return rows[0];
}

/** Update only the odds on a cached forecast (no Grok re-generation) */
export async function updateCachedOdds(eventId: string, oddsData: any): Promise<void> {
  const result = await pool.query(
    `UPDATE rm_forecast_cache SET odds_data = $1, odds_updated_at = NOW() WHERE event_id = $2`,
    [JSON.stringify(oddsData), eventId]
  );
  // Fallback: if no rows matched, the cache may use a different event_id format.
  // Find by team+date via rm_events and update that row instead.
  if (result.rowCount === 0) {
    await pool.query(
      `WITH matched_event AS (
         SELECT event_id, home_team, away_team, starts_at
         FROM rm_events
         WHERE event_id = $2
         LIMIT 1
       ), matched_cache AS (
         SELECT fc.id
         FROM rm_forecast_cache fc
       JOIN matched_event re
           ON ${teamNameKeySql('re.home_team')} = ${teamNameKeySql('fc.home_team')}
          AND ${teamNameKeySql('re.away_team')} = ${teamNameKeySql('fc.away_team')}
         WHERE fc.starts_at BETWEEN re.starts_at - INTERVAL '${TEAM_MATCH_WINDOW_HOURS} hours'
                               AND re.starts_at + INTERVAL '${TEAM_MATCH_WINDOW_HOURS} hours'
         ORDER BY ABS(EXTRACT(EPOCH FROM (fc.starts_at - re.starts_at))) ASC, fc.created_at DESC
         LIMIT 1
       )
       UPDATE rm_forecast_cache fc
       SET odds_data = $1, odds_updated_at = NOW()
       FROM matched_cache
       WHERE fc.id = matched_cache.id`,
      [JSON.stringify(oddsData), eventId]
    );
  }
}

export async function getTodayForecasts(): Promise<RmForecast[]> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_forecast_cache
     WHERE DATE(starts_at) = CURRENT_DATE
     ORDER BY starts_at ASC`
  );
  return rows;
}

export async function getAllForecasts(limit = 200): Promise<RmForecast[]> {
  const { rows } = await pool.query(
    'SELECT * FROM rm_forecast_cache ORDER BY created_at DESC LIMIT $1',
    [limit]
  );
  return rows;
}

export async function getResolvedForecasts(): Promise<any[]> {
  const { rows } = await pool.query(
    `SELECT a.*, c.home_team, c.away_team, c.league, c.forecast_data
     FROM rm_forecast_accuracy_v2 a
     LEFT JOIN rm_forecast_cache c ON a.forecast_id = c.id
     ORDER BY a.resolved_at DESC LIMIT 500`
  );
  return rows;
}

export async function getAccuracyByBucket(): Promise<any[]> {
  const { rows } = await pool.query(
    `SELECT accuracy_bucket, COUNT(*) as count,
       AVG(accuracy_pct) as avg_accuracy
     FROM rm_forecast_accuracy_v2
     GROUP BY accuracy_bucket
     ORDER BY accuracy_bucket`
  );
  return rows;
}