import { pool } from '../db/index';
import type { TweetContent, TweetStatus } from './types';

// ── Tweet CRUD ──────────────────────────────────────────────

export async function insertTweet(tweet: TweetContent): Promise<number> {
  const result = await pool.query(
    `INSERT INTO sc_twitter_tweets
     (content_type, tweet_text, league, media_type, in_reply_to, thread_parent_id, blog_post_id, game_key, pick_direction, status)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'pending')
     RETURNING id`,
    [
      tweet.contentType,
      tweet.text,
      tweet.league || null,
      tweet.mediaType || null,
      tweet.inReplyTo || null,
      tweet.threadParentId || null,
      tweet.blogPostId || null,
      tweet.gameKey || null,
      tweet.pickDirection || null,
    ]
  );
  return result.rows[0].id;
}

export async function updateTweetStatus(
  id: number,
  status: TweetStatus,
  extra?: { error?: string; media_task_id?: string; media_url?: string; twitter_media_id?: string }
): Promise<void> {
  const sets: string[] = ['status = $2', 'updated_at = NOW()'];
  const params: unknown[] = [id, status];
  let idx = 3;

  if (extra?.error !== undefined) {
    sets.push(`error = $${idx++}`);
    params.push(extra.error);
  }
  if (extra?.media_task_id !== undefined) {
    sets.push(`media_task_id = $${idx++}`);
    params.push(extra.media_task_id);
  }
  if (extra?.media_url !== undefined) {
    sets.push(`media_url = $${idx++}`);
    params.push(extra.media_url);
  }
  if (extra?.twitter_media_id !== undefined) {
    sets.push(`twitter_media_id = $${idx++}`);
    params.push(extra.twitter_media_id);
  }

  await pool.query(
    `UPDATE sc_twitter_tweets SET ${sets.join(', ')} WHERE id = $1`,
    params
  );
}

export async function updateTweetPosted(id: number, tweetId: string): Promise<void> {
  await pool.query(
    `UPDATE sc_twitter_tweets
     SET status = 'posted', tweet_id = $2, posted_at = NOW(), updated_at = NOW()
     WHERE id = $1`,
    [id, tweetId]
  );
}

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

// ── Worker Run Idempotency ──────────────────────────────────

export async function hasSlotExecuted(slotName: string, windowMins: number = 45): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM sc_twitter_worker_runs
     WHERE slot_name = $1 AND executed_at > NOW() - $2::interval
     LIMIT 1`,
    [slotName, `${windowMins} minutes`]
  );
  return result.rows.length > 0;
}

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

// ── Engagement ──────────────────────────────────────────────

export async function insertEngagement(action: {
  actionType: string;
  targetTweetId?: string;
  targetUser?: string;
  searchQuery?: string;
  replyText?: string;
  ourTweetId?: number;
  status?: string;
}): Promise<number> {
  const result = await pool.query(
    `INSERT INTO sc_twitter_engagement
     (action_type, target_tweet_id, target_user, search_query, reply_text, our_tweet_id, status)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     RETURNING id`,
    [
      action.actionType,
      action.targetTweetId || null,
      action.targetUser || null,
      action.searchQuery || null,
      action.replyText || null,
      action.ourTweetId || null,
      action.status || 'completed',
    ]
  );
  return result.rows[0].id;
}

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

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

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

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

/** @deprecated Use getFreeQuestionCount() >= 2 instead */
export async function hasUsedFreeQuestion(twitterUserId: string): Promise<boolean> {
  return (await getFreeQuestionCount(twitterUserId)) >= 2;
}

// ── User Cooldowns ──────────────────────────────────────────

export async function getUserCooldown(twitterUserId: string): Promise<Date | null> {
  const result = await pool.query(
    `SELECT last_replied_at FROM sc_twitter_user_cooldowns
     WHERE twitter_user_id = $1`,
    [twitterUserId]
  );
  if (result.rows.length === 0) return null;
  return new Date(result.rows[0].last_replied_at);
}

export async function updateUserCooldown(twitterUserId: string): Promise<void> {
  await pool.query(
    `INSERT INTO sc_twitter_user_cooldowns (twitter_user_id, last_replied_at)
     VALUES ($1, NOW())
     ON CONFLICT (twitter_user_id) DO UPDATE SET last_replied_at = NOW()`,
    [twitterUserId]
  );
}

export function isUserOnCooldown(lastRepliedAt: Date | null, cooldownHours: number = 12): boolean {
  if (!lastRepliedAt) return false;
  const cutoff = new Date(Date.now() - cooldownHours * 60 * 60 * 1000);
  return lastRepliedAt > cutoff;
}

// ── Duplicate Detection ─────────────────────────────────────

export async function isDuplicateContent(text: string, hours: number = 24): Promise<boolean> {
  const prefix = text.substring(0, 100).trim();
  const result = await pool.query(
    `SELECT 1 FROM sc_twitter_tweets
     WHERE LEFT(tweet_text, 100) = $1
       AND created_at > NOW() - $2::interval
     LIMIT 1`,
    [prefix, `${hours} hours`]
  );
  return result.rows.length > 0;
}

// ── Event Alert Dedup (prevent re-alerting same game) ───────

/** Extract meaningful search tokens from a team name.
 *  "West Ham United" → ["West", "Ham"]  (drop generic words like United/City/FC)
 *  "Bournemouth" → ["Bournemouth"]
 *  "Golden State Warriors" → ["Golden", "State", "Warriors"]
 */
function teamSearchTokens(name: string): string[] {
  const GENERIC = new Set(['united', 'city', 'fc', 'cf', 'sc', 'afc', 'of', 'the', 'and']);
  const words = name.trim().split(/\s+/).filter(w => w.length >= 2 && !GENERIC.has(w.toLowerCase()));
  // Use the first distinctive word (most unique part of the name)
  return words.length > 0 ? [words[0]] : [name.trim()];
}

export async function hasRecentEventAlert(gameKey: string, hours: number = 4): Promise<boolean> {
  // gameKey like "Bournemouth@West Ham United"
  const teams = gameKey.split('@');
  if (teams.length !== 2) return false;

  const awayTokens = teamSearchTokens(teams[0]);
  const homeTokens = teamSearchTokens(teams[1]);

  // Match if tweet contains at least one token from each team
  const awayPattern = `%${awayTokens[0]}%`;
  const homePattern = `%${homeTokens[0]}%`;

  const result = await pool.query(
    `SELECT 1 FROM sc_twitter_tweets
     WHERE content_type = 'event_driven'
       AND tweet_text ILIKE $1
       AND tweet_text ILIKE $2
       AND created_at > NOW() - $3::interval
     LIMIT 1`,
    [awayPattern, homePattern, `${hours} hours`]
  );
  return result.rows.length > 0;
}

// ── Pick Contradiction Guard ─────────────────────────────────

/** Returns the existing pick direction for a game, if any was posted/pending in the last N hours */
export async function getExistingPickForGame(
  gameKey: string,
  hours: number = 48
): Promise<{ pick_direction: string; content_type: string } | null> {
  const result = await pool.query(
    `SELECT pick_direction, content_type FROM sc_twitter_tweets
     WHERE game_key = $1
       AND pick_direction IS NOT NULL
       AND status IN ('posted', 'pending', 'posting')
       AND created_at > NOW() - $2::interval
     ORDER BY created_at DESC
     LIMIT 1`,
    [gameKey, `${hours} hours`]
  );
  return result.rows[0] || null;
}

/** Returns ALL existing picks for the given game keys (batch lookup for hot takes) */
export async function getExistingPicksForGames(
  gameKeys: string[],
  hours: number = 48
): Promise<Map<string, string>> {
  if (gameKeys.length === 0) return new Map();
  const result = await pool.query(
    `SELECT DISTINCT ON (game_key) game_key, pick_direction
     FROM sc_twitter_tweets
     WHERE game_key = ANY($1)
       AND pick_direction IS NOT NULL
       AND status IN ('posted', 'pending', 'posting')
       AND created_at > NOW() - $2::interval
     ORDER BY game_key, created_at DESC`,
    [gameKeys, `${hours} hours`]
  );
  const map = new Map<string, string>();
  for (const row of result.rows) {
    map.set(row.game_key, row.pick_direction);
  }
  return map;
}

// ── Pick Grading ────────────────────────────────────────────

export interface UngradedPick {
  id: number;
  game_key: string;
  league: string;
  pick_type: string;
  selection: string;
  pick_direction: string;
  line_value: number | null;
  game_date: string | null;
  inputs_snapshot: any;
}

export async function getUngradedPicks(limit: number = 200): Promise<UngradedPick[]> {
  const result = await pool.query(
    `SELECT id, game_key, league, pick_type, selection, pick_direction,
            line_value, game_date, inputs_snapshot
     FROM sc_picks
     WHERE grade_result IS NULL
       AND game_date < CURRENT_DATE
       AND game_date >= '2026-02-28'
     ORDER BY game_date ASC
     LIMIT $1`,
    [limit]
  );
  return result.rows;
}

export async function updatePickGrade(
  pickId: number,
  gradeResult: string,
  homeScore: number | null,
  awayScore: number | null,
  total: number | null,
  notes: string | null
): Promise<void> {
  await pool.query(
    `UPDATE sc_picks
     SET grade_result = $2, final_score_home = $3, final_score_away = $4,
         final_total = $5, graded_at = NOW(), grading_notes = $6, updated_at = NOW()
     WHERE id = $1`,
    [pickId, gradeResult, homeScore, awayScore, total, notes]
  );
}

export interface TallySummary {
  recent: { wins: number; losses: number; pushes: number; byLeague: Array<{ league: string; wins: number; losses: number; pushes: number }> };
  overall: { wins: number; losses: number; pushes: number; winPct: number; total: number; byLeague: Array<{ league: string; wins: number; losses: number; pushes: number }> };
}

export async function getPickTallySummary(recentHours: number = 36): Promise<TallySummary> {
  const recentResult = await pool.query(
    `SELECT league,
       COUNT(*) FILTER (WHERE grade_result = 'win') AS wins,
       COUNT(*) FILTER (WHERE grade_result = 'loss') AS losses,
       COUNT(*) FILTER (WHERE grade_result = 'push') AS pushes
     FROM sc_picks
     WHERE grade_result IS NOT NULL AND graded_at >= NOW() - $1::interval
       AND game_date >= '2026-02-28'
     GROUP BY league`,
    [`${recentHours} hours`]
  );
  const recentByLeague = recentResult.rows.map((r: any) => ({
    league: r.league,
    wins: parseInt(r.wins),
    losses: parseInt(r.losses),
    pushes: parseInt(r.pushes),
  }));
  const recentWins = recentByLeague.reduce((s, l) => s + l.wins, 0);
  const recentLosses = recentByLeague.reduce((s, l) => s + l.losses, 0);
  const recentPushes = recentByLeague.reduce((s, l) => s + l.pushes, 0);

  const overallResult = await pool.query(
    `SELECT league,
       COUNT(*) FILTER (WHERE grade_result = 'win') AS wins,
       COUNT(*) FILTER (WHERE grade_result = 'loss') AS losses,
       COUNT(*) FILTER (WHERE grade_result = 'push') AS pushes
     FROM sc_picks
     WHERE grade_result IS NOT NULL
       AND game_date >= '2026-02-28'
     GROUP BY league`
  );
  const overallByLeague = overallResult.rows.map((r: any) => ({
    league: r.league,
    wins: parseInt(r.wins),
    losses: parseInt(r.losses),
    pushes: parseInt(r.pushes),
  }));
  const overallWins = overallByLeague.reduce((s, l) => s + l.wins, 0);
  const overallLosses = overallByLeague.reduce((s, l) => s + l.losses, 0);
  const overallPushes = overallByLeague.reduce((s, l) => s + l.pushes, 0);
  const overallTotal = overallWins + overallLosses + overallPushes;
  const overallWinPct = overallTotal > 0 ? Math.round((overallWins / (overallWins + overallLosses)) * 100) : 0;

  return {
    recent: { wins: recentWins, losses: recentLosses, pushes: recentPushes, byLeague: recentByLeague },
    overall: { wins: overallWins, losses: overallLosses, pushes: overallPushes, winPct: overallWinPct, total: overallTotal, byLeague: overallByLeague },
  };
}

export async function getPicksForCompletedGame(gameKey: string): Promise<{ id: number; selection: string; pick_direction: string; grade_result: string } | null> {
  const result = await pool.query(
    `SELECT id, selection, pick_direction, grade_result
     FROM sc_picks
     WHERE game_key = $1 AND grade_result IS NOT NULL
     ORDER BY created_at DESC
     LIMIT 1`,
    [gameKey]
  );
  return result.rows[0] ?? null;
}

// ── Blog Post Cross-Promo ───────────────────────────────────

export async function getUntweetedBlogPosts(limit: number = 5): Promise<Array<{
  id: number;
  slug: string;
  title: string;
  excerpt: string;
  league: string;
  content_type: string;
}>> {
  const result = await pool.query(
    `SELECT bp.id, bp.slug, bp.title, bp.excerpt, bp.league, bp.content_type
     FROM sc_blog_posts bp
     LEFT JOIN sc_twitter_tweets tt ON tt.blog_post_id = bp.id AND tt.status = 'posted'
     WHERE bp.status = 'published'
       AND tt.id IS NULL
       AND bp.published_at >= NOW() - INTERVAL '7 days'
     ORDER BY bp.published_at DESC
     LIMIT $1`,
    [limit]
  );
  return result.rows;
}