import { pool } from '../db/index';
import type { PickDetails } from './types';
import type { EnrichedGameData } from '../seo/data-enrichment';

export interface PickRow {
  id: number;
  game_key: string;
  league: string;
  pick_type: string;
  selection: string;
  pick_direction: string;
  blog_post_id: number | null;
  x_post_id: string | null;
  x_post_url: string | null;
  created_at: string;
}

/**
 * Insert a new pick into sc_picks.
 */
export async function insertPick(
  pick: PickDetails,
  enriched: EnrichedGameData,
  blogPostId: number | null,
): Promise<number> {
  const result = await pool.query(
    `INSERT INTO sc_picks
     (game_key, league, pick_type, selection, pick_direction, line_value, odds_american,
      confidence, edge_pct, short_reason, long_reason_md, math_breakdown,
      inputs_snapshot, blog_post_id, game_date, updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NOW())
     RETURNING id`,
    [
      pick.gameKey,
      pick.league,
      pick.pickType,
      pick.selection,
      pick.pickDirection,
      pick.lineValue ?? null,
      pick.oddsAmerican ?? null,
      pick.confidence ?? null,
      pick.edgePct ?? null,
      pick.shortReason,
      pick.longReasonMd ?? null,
      pick.mathBreakdown ? JSON.stringify(pick.mathBreakdown) : null,
      JSON.stringify(enriched),
      blogPostId,
      pick.gameDate ?? null,
    ]
  );
  return result.rows[0].id;
}

/**
 * Check for an existing pick on the same game+type within a time window (idempotency).
 */
export async function getRecentPick(
  gameKey: string,
  pickType: string,
  hours: number = 48,
): Promise<PickRow | null> {
  const result = await pool.query(
    `SELECT id, game_key, league, pick_type, selection, pick_direction,
            blog_post_id, x_post_id, x_post_url, created_at
     FROM sc_picks
     WHERE game_key = $1 AND pick_type = $2
       AND created_at > NOW() - INTERVAL '1 hour' * $3
     ORDER BY created_at DESC
     LIMIT 1`,
    [gameKey, pickType, hours]
  );
  return result.rows[0] ?? null;
}

/**
 * After X post succeeds, update the pick with tweet info.
 */
export async function updatePickXPost(
  pickId: number,
  xPostId: string,
  xPostUrl: string,
): Promise<void> {
  await pool.query(
    `UPDATE sc_picks SET x_post_id = $2, x_post_url = $3, updated_at = NOW() WHERE id = $1`,
    [pickId, xPostId, xPostUrl]
  );
}

/**
 * Replace {{X_POST_LINK}} in the blog post content with the actual tweet URL.
 */
export async function updateBlogXPostLink(
  blogPostId: number,
  xPostUrl: string,
): Promise<void> {
  await pool.query(
    `UPDATE sc_blog_posts
     SET content = REPLACE(content, '{{X_POST_LINK}}', $2),
         updated_at = NOW()
     WHERE id = $1`,
    [blogPostId, xPostUrl]
  );
}

/**
 * Link a pick to a tweet row ID (sc_twitter_tweets.id).
 */
export async function updatePickTweetRow(
  pickId: number,
  tweetRowId: number,
): Promise<void> {
  await pool.query(
    `UPDATE sc_picks SET tweet_row_id = $2, updated_at = NOW() WHERE id = $1`,
    [pickId, tweetRowId]
  );
}