/**
 * Rainmaker Twitter Bot — Database Queries
 * Uses the shared sportsdb via the existing pool.
 */
import pool from '../db/index';

// ── Tweet Tracking ──────────────────────────────────────────

export async function insertTweet(
  tweetId: string,
  contentType: string,
  tweetText: string,
  league?: string,
  forecastId?: string,
  gameKey?: string,
  pickDirection?: string,
  status: string = 'posted'
): Promise<number> {
  const result = await pool.query(
    `INSERT INTO rm_twitter_tweets
       (tweet_id, content_type, tweet_text, league, forecast_id, game_key, pick_direction, status, created_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
     RETURNING id`,
    [tweetId, contentType, tweetText, league || null, forecastId || null, gameKey || null, pickDirection || null, status]
  );
  return result.rows[0].id;
}

export async function getTweetCountLast24Hours(): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_twitter_tweets WHERE created_at > NOW() - INTERVAL '24 hours'`
  );
  return parseInt(result.rows[0].cnt);
}

// ── Slot Execution Tracking ─────────────────────────────────

export async function hasSlotExecuted(slotName: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM rm_twitter_worker_runs
     WHERE slot_name = $1
       AND executed_at > (NOW() AT TIME ZONE 'America/New_York')::date + INTERVAL '0 hours'
     LIMIT 1`,
    [slotName]
  );
  return result.rows.length > 0;
}

export async function insertWorkerRun(slotName: string, tweetsPosted: number, status: string = 'completed'): Promise<void> {
  await pool.query(
    `INSERT INTO rm_twitter_worker_runs (slot_name, tweets_posted, status, executed_at)
     VALUES ($1, $2, $3, NOW())`,
    [slotName, tweetsPosted, status]
  );
}

// ── Duplicate Check ─────────────────────────────────────────

export async function isDuplicateForecast(forecastId: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM rm_twitter_tweets
     WHERE forecast_id = $1 AND created_at > NOW() - INTERVAL '24 hours'
     LIMIT 1`,
    [forecastId]
  );
  return result.rows.length > 0;
}

export async function isDuplicateContent(text: string): Promise<boolean> {
  const snippet = text.substring(0, 100);
  const result = await pool.query(
    `SELECT 1 FROM rm_twitter_tweets
     WHERE tweet_text LIKE $1 AND created_at > NOW() - INTERVAL '12 hours'
     LIMIT 1`,
    [`${snippet}%`]
  );
  return result.rows.length > 0;
}

// ── Engagement Tracking ────────────────────────────────────

export async function insertEngagement(data: {
  actionType: string;
  targetTweetId: string;
  targetUser?: string;
  replyText?: string;
  ourTweetId?: number;
}): Promise<void> {
  await pool.query(
    `INSERT INTO rm_twitter_engagement
       (action_type, target_tweet_id, target_user, reply_text, our_tweet_id, created_at)
     VALUES ($1, $2, $3, $4, $5, NOW())`,
    [data.actionType, data.targetTweetId, data.targetUser || null, data.replyText || null, data.ourTweetId || null]
  );
}

export async function hasEngagedWith(targetTweetId: string, actionType: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM rm_twitter_engagement
     WHERE target_tweet_id = $1 AND action_type = $2
     LIMIT 1`,
    [targetTweetId, actionType]
  );
  return result.rows.length > 0;
}

export async function getEngagementCountLast24Hours(): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_twitter_engagement WHERE created_at > NOW() - INTERVAL '24 hours'`
  );
  return parseInt(result.rows[0].cnt);
}

// ── Free Question Funnel ────────────────────────────────────

export async function getFreeQuestionCount(twitterUserId: string): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_twitter_free_questions
     WHERE twitter_user_id = $1`,
    [twitterUserId]
  );
  return parseInt(result.rows[0].cnt);
}

export async function insertFreeQuestion(data: {
  twitterUserId: string;
  questionTweetId?: string;
  questionText?: string;
  answerText?: string;
}): Promise<number> {
  const result = await pool.query(
    `INSERT INTO rm_twitter_free_questions
     (twitter_user_id, question_tweet_id, question_text, answer_text)
     VALUES ($1, $2, $3, $4)
     RETURNING id`,
    [data.twitterUserId, data.questionTweetId || null, data.questionText || null, data.answerText || null]
  );
  return result.rows[0].id;
}

// ── Forecast Recap ──────────────────────────────────────────

export async function getRecentlySettledForecasts(): Promise<Array<{
  league: string;
  home_team: string;
  away_team: string;
  winner_pick: string;
  outcome: string;
  actual_winner: string;
  actual_score: string;
  settled_at: string;
}>> {
  const result = await pool.query(
    `SELECT league, home_team, away_team, winner_pick, outcome, actual_winner, actual_score, settled_at
     FROM rm_archived_forecasts
     WHERE outcome IN ('win', 'loss')
       AND settled_at >= NOW() - INTERVAL '36 hours'
       AND settled_at >= '2026-02-28'
     ORDER BY settled_at DESC
     LIMIT 20`
  );
  return result.rows;
}

export async function getForecastRecordSince(sinceDate: string = '2026-02-28'): Promise<{
  wins: number; losses: number; total: number; winPct: number;
}> {
  const result = await pool.query(
    `SELECT
       COUNT(*) FILTER (WHERE outcome = 'win') AS wins,
       COUNT(*) FILTER (WHERE outcome = 'loss') AS losses,
       COUNT(*) AS total
     FROM rm_archived_forecasts
     WHERE outcome IN ('win', 'loss')
       AND settled_at >= $1`,
    [sinceDate]
  );
  const wins = parseInt(result.rows[0].wins);
  const losses = parseInt(result.rows[0].losses);
  const total = parseInt(result.rows[0].total);
  const winPct = total > 0 ? Math.round((wins / total) * 100) : 0;
  return { wins, losses, total, winPct };
}

// ── Win Streak / Momentum ───────────────────────────────────

export async function getCurrentWinStreak(): Promise<number> {
  // Get recent results ordered by settled_at desc, count consecutive wins
  const result = await pool.query(
    `SELECT outcome FROM rm_archived_forecasts
     WHERE outcome IN ('win', 'loss')
       AND settled_at >= '2026-02-28'
     ORDER BY settled_at DESC
     LIMIT 20`
  );

  let streak = 0;
  for (const row of result.rows) {
    if (row.outcome === 'win') {
      streak++;
    } else {
      break;
    }
  }
  return streak;
}

export async function getRecentDayRecord(): Promise<{
  wins: number; losses: number; total: number;
}> {
  // Today's results only
  const result = await pool.query(
    `SELECT
       COUNT(*) FILTER (WHERE outcome = 'win') AS wins,
       COUNT(*) FILTER (WHERE outcome = 'loss') AS losses,
       COUNT(*) AS total
     FROM rm_archived_forecasts
     WHERE outcome IN ('win', 'loss')
       AND settled_at >= (NOW() AT TIME ZONE 'America/New_York')::date`
  );
  return {
    wins: parseInt(result.rows[0].wins),
    losses: parseInt(result.rows[0].losses),
    total: parseInt(result.rows[0].total),
  };
}

export async function getLastNRecord(n: number): Promise<{
  wins: number; losses: number; total: number;
}> {
  const result = await pool.query(
    `SELECT outcome FROM rm_archived_forecasts
     WHERE outcome IN ('win', 'loss')
       AND settled_at >= '2026-02-28'
     ORDER BY settled_at DESC
     LIMIT $1`,
    [n]
  );

  let wins = 0, losses = 0;
  for (const row of result.rows) {
    if (row.outcome === 'win') wins++;
    else losses++;
  }
  return { wins, losses, total: result.rows.length };
}

// ── Live Edge Alert Tracking ────────────────────────────────

export async function hasAlertedGame(gameKey: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM rm_twitter_tweets
     WHERE content_type = 'live_edge'
       AND game_key = $1
       AND created_at > (NOW() AT TIME ZONE 'America/New_York')::date
     LIMIT 1`,
    [gameKey]
  );
  return result.rows.length > 0;
}

// ── Content Type Counts (for rotation) ──────────────────────

export async function getContentTypeCountToday(contentType: string): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_twitter_tweets
     WHERE content_type = $1
       AND created_at > (NOW() AT TIME ZONE 'America/New_York')::date`,
    [contentType]
  );
  return parseInt(result.rows[0].cnt);
}