import pool from '../db';

/**
 * Contest-aware TTL computation.
 * Queries the SportsGame table to determine game state,
 * then applies rules based on query category + game state.
 *
 * Returns TTL in seconds, or 0 if the query should NOT be cached.
 */

// 4:00 AM ET daily cutoff — all entries expire at most until then
const DAILY_CUTOFF_HOUR_ET = parseInt(process.env.CACHE_DAILY_CUTOFF_HOUR_ET || '4');

interface GameState {
  status: 'pre_game_far' | 'pre_game_close' | 'live' | 'final' | 'unknown';
  startsAt: Date | null;
}

async function getGameState(league: string | null, teams: string[], gameDate: string | null): Promise<GameState> {
  if (!league && teams.length === 0) {
    return { status: 'unknown', startsAt: null };
  }

  try {
    // Build query to find the most relevant game
    let conditions = `"gameDate" >= CURRENT_DATE - interval '1 day'`;
    const params: any[] = [];
    let paramIdx = 1;

    if (league) {
      conditions += ` AND LOWER(league) = $${paramIdx}`;
      params.push(league.toLowerCase());
      paramIdx++;
    }

    if (teams.length > 0) {
      // Match any of the team abbreviations against homeTeam or awayTeam
      const teamConditions = teams.map((_, i) => {
        const idx = paramIdx + i;
        return `(UPPER("homeTeam") = $${idx} OR UPPER("awayTeam") = $${idx})`;
      }).join(' OR ');
      conditions += ` AND (${teamConditions})`;
      params.push(...teams.map(t => t.toUpperCase()));
      paramIdx += teams.length;
    }

    if (gameDate) {
      conditions += ` AND "gameDate"::date = $${paramIdx}::date`;
      params.push(gameDate);
      paramIdx++;
    }

    const result = await pool.query(`
      SELECT status, "gameDate"
      FROM "SportsGame"
      WHERE ${conditions}
      ORDER BY "gameDate" DESC
      LIMIT 1
    `, params);

    if (result.rows.length === 0) {
      return { status: 'unknown', startsAt: null };
    }

    const game = result.rows[0];
    const now = new Date();
    const gameDateTime = game.gameDate ? new Date(game.gameDate) : null;
    const status = (game.status || '').toLowerCase();

    // Determine game state
    if (status === 'final' || status === 'closed' || status === 'complete') {
      return { status: 'final', startsAt: gameDateTime };
    }

    if (status === 'inprogress' || status === 'in_progress' || status === 'live' ||
        status === 'halftime' || status === 'delayed') {
      return { status: 'live', startsAt: gameDateTime };
    }

    // Pre-game: check how far out using gameDate as approximate start time
    if (gameDateTime) {
      const hoursUntilStart = (gameDateTime.getTime() - now.getTime()) / (1000 * 60 * 60);
      if (hoursUntilStart <= 0) {
        // Game date has passed but status isn't set — assume pre-game close
        return { status: 'pre_game_close', startsAt: gameDateTime };
      }
      if (hoursUntilStart <= 6) {
        return { status: 'pre_game_close', startsAt: gameDateTime };
      }
      return { status: 'pre_game_far', startsAt: gameDateTime };
    }

    return { status: 'unknown', startsAt: null };
  } catch (err) {
    console.error('TTL game state lookup error:', err);
    return { status: 'unknown', startsAt: null };
  }
}

function getSecondsUntilDailyCutoff(): number {
  const now = new Date();
  // Calculate next 4AM ET
  const etNow = new Date(now.toLocaleString('en-US', { timeZone: 'America/New_York' }));
  const cutoff = new Date(etNow);
  cutoff.setHours(DAILY_CUTOFF_HOUR_ET, 0, 0, 0);
  if (cutoff <= etNow) {
    cutoff.setDate(cutoff.getDate() + 1);
  }
  // Convert back to UTC diff
  const diffMs = cutoff.getTime() - etNow.getTime();
  return Math.max(60, Math.floor(diffMs / 1000)); // At least 60s
}

// TTL rules: returns seconds, 0 = do not cache
const TTL_RULES: Record<string, Record<string, number>> = {
  props: {
    pre_game_far: 4 * 3600,     // 4 hours
    pre_game_close: 1 * 3600,   // 1 hour
    live: 0,                     // DO NOT CACHE
    final: 24 * 3600,           // 24 hours
    unknown: 2 * 3600,          // 2 hours default
  },
  odds: {
    pre_game_far: 2 * 3600,     // 2 hours
    pre_game_close: 30 * 60,    // 30 minutes
    live: 0,                     // DO NOT CACHE
    final: 24 * 3600,           // 24 hours
    unknown: 1 * 3600,          // 1 hour default
  },
  scores: {
    pre_game_far: 4 * 3600,     // Until game starts (capped)
    pre_game_close: 30 * 60,    // 30 min
    live: 0,                     // DO NOT CACHE
    final: 24 * 3600,           // 24 hours
    unknown: 1 * 3600,          // 1 hour
  },
  analysis: {
    pre_game_far: 6 * 3600,     // 6 hours
    pre_game_close: 3 * 3600,   // 3 hours
    live: 1 * 3600,             // 1 hour (analysis is still valid during game)
    final: 24 * 3600,           // 24 hours
    unknown: 6 * 3600,          // 6 hours
  },
  injury: {
    pre_game_far: 1 * 3600,     // 1 hour
    pre_game_close: 30 * 60,    // 30 minutes
    live: 30 * 60,              // 30 minutes
    final: 4 * 3600,            // 4 hours
    unknown: 1 * 3600,          // 1 hour
  },
  general: {
    pre_game_far: 7 * 24 * 3600,  // 7 days
    pre_game_close: 7 * 24 * 3600,
    live: 7 * 24 * 3600,
    final: 7 * 24 * 3600,
    unknown: 7 * 24 * 3600,
  },
};

/**
 * Compute TTL jitter from teams/league to spread expirations
 * and prevent thundering herd when many entries expire at once.
 * Produces 0-1800 seconds (0-30 minutes) of jitter.
 */
function computeJitter(teams: string[], league: string | null, gameDate: string | null): number {
  const seed = [league || '', ...teams, gameDate || ''].join(':');
  let hash = 0;
  for (let i = 0; i < seed.length; i++) {
    hash = ((hash << 5) - hash + seed.charCodeAt(i)) | 0;
  }
  return Math.abs(hash % 30) * 60; // 0-29 minutes in seconds
}

export async function computeTTL(
  category: string,
  league: string | null,
  teams: string[],
  gameDate: string | null
): Promise<{ ttlSeconds: number; gameState: string }> {
  const gameState = await getGameState(league, teams, gameDate);
  const rules = TTL_RULES[category] || TTL_RULES.general;
  let ttl = rules[gameState.status] ?? rules.unknown;

  if (ttl === 0) {
    return { ttlSeconds: 0, gameState: gameState.status };
  }

  // For pre-game scores, cap TTL to time until game starts
  if (category === 'scores' && gameState.startsAt) {
    const secondsUntilStart = Math.max(0, (gameState.startsAt.getTime() - Date.now()) / 1000);
    if (secondsUntilStart > 0 && secondsUntilStart < ttl) {
      ttl = Math.floor(secondsUntilStart);
    }
  }

  // Cap at daily cutoff (4AM ET)
  const cutoffSeconds = getSecondsUntilDailyCutoff();
  ttl = Math.min(ttl, cutoffSeconds);

  // Apply jitter to prevent thundering herd (±0-30 min spread)
  const jitter = computeJitter(teams, league, gameDate);
  ttl = ttl + jitter;

  // Re-cap after jitter (don't exceed daily cutoff)
  ttl = Math.min(ttl, cutoffSeconds);

  // Minimum 60 seconds
  ttl = Math.max(60, ttl);

  return { ttlSeconds: ttl, gameState: gameState.status };
}