import { pool } from '../db/index';

export interface GameData {
  id: number;
  league: string;
  gameDate: string;
  homeTeam: string;
  awayTeam: string;
  spreadHome: number | null;
  spreadAway: number | null;
  moneylineHome: number | null;
  moneylineAway: number | null;
  total: number | null;
  status: string | null;
  homeScore: number | null;
  awayScore: number | null;
}

export interface OddsRow {
  bookmaker: string;
  market: string;
  lineValue: number | null;
  homeOdds: number | null;
  awayOdds: number | null;
  overOdds: number | null;
  underOdds: number | null;
}

export interface PropData {
  playerExternalId: string;
  propType: string;
  lineValue: number;
  oddsAmerican: number | null;
  vendor: string | null;
  playerName?: string;
}

export interface LineMovementData {
  gameExternalId: string;
  league: string;
  homeTeam: string | null;
  awayTeam: string | null;
  marketType: string;
  openLine: number | null;
  currentLine: number | null;
  lineMovement: number | null;
  movementDirection: string | null;
  sharpAction: string | null;
  steamMove: boolean | null;
  reverseLineMove: boolean | null;
  gameDate: string | null;
}

export interface SharpMoveData {
  league: string | null;
  homeTeam: string | null;
  awayTeam: string | null;
  market: string | null;
  moveType: string | null;
  lineFrom: number | null;
  lineTo: number | null;
  lineChange: number | null;
  isSteamMove: boolean | null;
  isReverseLine: boolean | null;
  moveDetectedAt: string | null;
  gameDate: string | null;
}

export interface InjuryData {
  playerName: string;
  team: string | null;
  position: string | null;
  status: string;
  injuryType: string | null;
  description: string | null;
  expectedReturn: string | null;
}

export interface CLVData {
  league: string;
  homeTeam: string | null;
  awayTeam: string | null;
  market: string;
  side: string;
  openingLine: number | null;
  closingLine: number | null;
  clvPercent: number | null;
  beatClose: boolean | null;
  actualResult: string | null;
  profit: number | null;
}

// ET date helper — returns 'YYYY-MM-DD' in Eastern Time
function toETDateString(date: Date): string {
  return date.toLocaleDateString('en-CA', { timeZone: 'America/New_York' }); // 'en-CA' gives YYYY-MM-DD
}

// Get upcoming games for a specific league and date (ET-aware)
export async function getUpcomingGames(league: string, date: Date): Promise<GameData[]> {
  const etDate = toETDateString(date);

  const result = await pool.query(
    `SELECT id, league, "gameDate", "homeTeam", "awayTeam",
            "spreadHome", "spreadAway", "moneylineHome", "moneylineAway",
            total, status, "homeScore", "awayScore"
     FROM "SportsGame"
     WHERE league = $1
       AND ("gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date = $2::date
       AND (status IS NULL OR status IN ('scheduled', 'pre-game', 'not_started'))
     ORDER BY "gameDate" ASC`,
    [league, etDate]
  );
  return result.rows;
}

// Get odds from multiple bookmakers for a game
export async function getGameOdds(league: string, gameId: string): Promise<OddsRow[]> {
  const result = await pool.query(
    `SELECT bookmaker, market, "lineValue", "homeOdds", "awayOdds", "overOdds", "underOdds"
     FROM "GameOdds"
     WHERE league = $1 AND "gameId" = $2
     ORDER BY bookmaker, market`,
    [league, gameId]
  );
  return result.rows;
}

// Get injuries for teams playing today
export async function getTeamInjuries(league: string, teams: string[]): Promise<InjuryData[]> {
  if (teams.length === 0) return [];
  const placeholders = teams.map((_, i) => `$${i + 2}`).join(', ');
  const result = await pool.query(
    `SELECT "playerName", team, position, status, "injuryType", description, "expectedReturn"
     FROM "PlayerInjury"
     WHERE league = $1 AND team IN (${placeholders})
       AND status != 'Active'
     ORDER BY team, status`,
    [league, ...teams]
  );
  return result.rows;
}

// Get player props for today's games
export async function getPlayerProps(league: string, gameIds: number[], limit: number = 50): Promise<PropData[]> {
  if (gameIds.length === 0) return [];
  const placeholders = gameIds.map((_, i) => `$${i + 2}`).join(', ');
  const result = await pool.query(
    `SELECT ppl."playerExternalId", ppl."propType", ppl."lineValue", ppl."oddsAmerican", ppl.vendor,
            COALESCE(cp."fullName", ppl."playerExternalId") as "playerName"
     FROM "PlayerPropLine" ppl
     LEFT JOIN "CanonicalPlayer" cp ON ppl."canonicalPlayerId" = cp.id
     WHERE ppl.league = $1 AND ppl."gameId" IN (${placeholders})
     ORDER BY ppl."createdAt" DESC
     LIMIT ${limit}`,
    [league, ...gameIds]
  );
  return result.rows;
}

// Get recent line movements for a league (ET-aware, upcoming games only)
export async function getRecentLineMovements(league: string, limit: number = 20): Promise<LineMovementData[]> {
  const result = await pool.query(
    `SELECT lm."gameExternalId", lm.league, lm."homeTeam", lm."awayTeam", lm."marketType",
            lm."openLine", lm."currentLine", lm."lineMovement", lm."movementDirection",
            lm."sharpAction", lm."steamMove", lm."reverseLineMove", lm."gameDate"
     FROM "LineMovement" lm
     JOIN "SportsGame" sg ON sg.league = lm.league
       AND sg."homeTeam" = lm."homeTeam" AND sg."awayTeam" = lm."awayTeam"
       AND sg."gameDate"::date = lm."gameDate"
     WHERE lm.league = $1
       AND lm."gameDate" >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
       AND lm."gameDate" <= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date + INTERVAL '2 days'
       AND (sg.status IS NULL OR sg.status IN ('scheduled', 'pre-game', 'not_started'))
     ORDER BY ABS(lm."lineMovement") DESC NULLS LAST
     LIMIT $2`,
    [league, limit]
  );
  return result.rows;
}

// Get sharp moves for a league (ET-aware, upcoming games only)
export async function getRecentSharpMoves(league: string, limit: number = 15): Promise<SharpMoveData[]> {
  const result = await pool.query(
    `SELECT sm.league, sm."homeTeam", sm."awayTeam", sm.market, sm."moveType",
            sm."lineFrom", sm."lineTo", sm."lineChange", sm."isSteamMove", sm."isReverseLine",
            sm."moveDetectedAt", sm."gameDate"
     FROM "SharpMove" sm
     JOIN "SportsGame" sg ON sg.league = sm.league
       AND sg."homeTeam" = sm."homeTeam" AND sg."awayTeam" = sm."awayTeam"
       AND sg."gameDate"::date = sm."gameDate"::date
     WHERE sm.league = $1
       AND (sm."gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
       AND (sm."gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date <= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date + 2
       AND (sg.status IS NULL OR sg.status IN ('scheduled', 'pre-game', 'not_started'))
     ORDER BY sm."moveDetectedAt" DESC
     LIMIT $2`,
    [league, limit]
  );
  return result.rows;
}

// Get completed games for recap (ET-aware date boundaries)
export async function getCompletedGames(league: string, weekStart: Date, weekEnd: Date): Promise<GameData[]> {
  const startET = toETDateString(weekStart);
  const endET = toETDateString(weekEnd);

  const result = await pool.query(
    `SELECT id, league, "gameDate", "homeTeam", "awayTeam",
            "spreadHome", "spreadAway", "moneylineHome", "moneylineAway",
            total, status, "homeScore", "awayScore"
     FROM "SportsGame"
     WHERE league = $1
       AND ("gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date BETWEEN $2::date AND $3::date
       AND status IN ('final', 'completed', 'closed')
     ORDER BY "gameDate" ASC`,
    [league, startET, endET]
  );
  return result.rows;
}

// Get CLV analysis for recap
export async function getCLVResults(league: string, weekStart: Date, weekEnd: Date): Promise<CLVData[]> {
  const result = await pool.query(
    `SELECT league, "homeTeam", "awayTeam", market, side,
            "openingLine", "closingLine", "clvPercent", "beatClose",
            "actualResult", profit
     FROM "CLVAnalysis"
     WHERE league = $1
       AND "gameDate" BETWEEN $2 AND $3
     ORDER BY ABS("clvPercent") DESC NULLS LAST
     LIMIT 30`,
    [league, weekStart, weekEnd]
  );
  return result.rows;
}

// Get all active leagues with upcoming games (ET-aware)
export async function getActiveLeagues(): Promise<string[]> {
  const result = await pool.query(
    `SELECT DISTINCT league FROM "SportsGame"
     WHERE ("gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date >= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
       AND ("gameDate" AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::date <= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date + 2
       AND (status IS NULL OR status IN ('scheduled', 'pre-game', 'not_started'))
     ORDER BY league`
  );
  return result.rows.map((r: { league: string }) => r.league);
}

// Check if post already exists for date/league/type
export async function postExists(slug: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM sc_blog_posts WHERE slug = $1 LIMIT 1`,
    [slug]
  );
  return result.rows.length > 0;
}

// Get today's post count for safety limit (ET-aware)
export async function getTodayPostCount(): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM sc_blog_posts
     WHERE (COALESCE(published_at, created_at) AT TIME ZONE 'America/New_York')::date
       = (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date`
  );
  return parseInt(result.rows[0]?.cnt || '0');
}

export async function hasJobStartedOnEtDate(
  jobType: string,
  date: Date,
  statuses: string[] = ['running', 'completed']
): Promise<boolean> {
  const etDate = toETDateString(date);
  const result = await pool.query(
    `SELECT 1
     FROM sc_seo_agent_jobs
     WHERE job_type = $1
       AND status = ANY($2::text[])
       AND (started_at AT TIME ZONE 'America/New_York')::date = $3::date
     LIMIT 1`,
    [jobType, statuses, etDate]
  );
  return result.rows.length > 0;
}

// Insert a blog post
export async function insertBlogPost(post: {
  slug: string;
  title: string;
  meta_description: string;
  h1: string;
  content: string;
  excerpt: string;
  quick_facts: unknown;
  faq_schema: unknown;
  data_tables: unknown;
  sport: string;
  content_type: string;
  league: string;
  game_date: Date | null;
  schema_markup: unknown;
  status: string;
  published_at: Date | null;
}): Promise<number> {
  const result = await pool.query(
    `INSERT INTO sc_blog_posts
     (slug, title, meta_description, h1, content, excerpt, quick_facts, faq_schema,
      data_tables, sport, content_type, league, game_date, schema_markup, status, published_at, updated_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, NOW())
     RETURNING id`,
    [
      post.slug,
      post.title,
      post.meta_description,
      post.h1,
      post.content,
      post.excerpt,
      JSON.stringify(post.quick_facts),
      JSON.stringify(post.faq_schema),
      JSON.stringify(post.data_tables),
      post.sport,
      post.content_type,
      post.league,
      post.game_date,
      JSON.stringify(post.schema_markup),
      post.status,
      post.published_at,
    ]
  );
  return result.rows[0].id;
}

// Log a job
export async function logJob(job: {
  agent_type: string;
  job_type: string;
  config?: unknown;
  status: string;
  result?: unknown;
  error?: string;
  started_at?: Date;
  completed_at?: Date;
}): Promise<number> {
  const result = await pool.query(
    `INSERT INTO sc_seo_agent_jobs (agent_type, job_type, config, status, result, error, started_at, completed_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     RETURNING id`,
    [
      job.agent_type,
      job.job_type,
      job.config ? JSON.stringify(job.config) : null,
      job.status,
      job.result ? JSON.stringify(job.result) : null,
      job.error || null,
      job.started_at || null,
      job.completed_at || null,
    ]
  );
  return result.rows[0].id;
}

export async function updateJobStatus(jobId: number, status: string, result?: unknown, error?: string): Promise<void> {
  await pool.query(
    `UPDATE sc_seo_agent_jobs SET status = $1, result = $2, error = $3, completed_at = NOW() WHERE id = $4`,
    [status, result ? JSON.stringify(result) : null, error || null, jobId]
  );
}