import { PoolClient } from 'pg';

import pool from '../db';
import { normalizeGroupedMlbMarkets } from './mlb-market-gapfill';
import {
  buildFallbackMlbEventKey,
  groupRawMlbMarketSources,
  mapPlayerPropLineRowToRawMarketSource,
  NormalizedPlayerPropMarket,
  SUPPORTED_MLB_PROP_TYPES,
} from './mlb-market-normalizer';

export interface MlbCompletionEvent {
  eventId: string;
  startsAt: string;
  homeTeam: string;
  awayTeam: string;
}

export async function fetchActiveMlbEvents(): Promise<MlbCompletionEvent[]> {
  const { rows } = await pool.query(
    `SELECT event_id, starts_at, home_short, away_short
     FROM rm_events
     WHERE league = 'mlb'
       AND (
         (starts_at AT TIME ZONE 'America/New_York')::date = (NOW() AT TIME ZONE 'America/New_York')::date
         OR (
           status IN ('live', 'in_progress')
           AND starts_at >= NOW() - INTERVAL '8 hours'
         )
       )
       AND COALESCE(status, 'scheduled') NOT IN ('final', 'closed', 'cancelled', 'postponed')
     ORDER BY starts_at ASC`,
  );

  return rows.map((row: any) => ({
    eventId: String(row.event_id),
    startsAt: new Date(row.starts_at).toISOString(),
    homeTeam: String(row.home_short || '').toUpperCase(),
    awayTeam: String(row.away_short || '').toUpperCase(),
  }));
}

type RawPlayerPropLineRow = {
  playerExternalId: string | null;
  propType: string;
  lineValue: number | null;
  fdLine: number | null;
  dkLine: number | null;
  oddsAmerican: number | null;
  fdOverOdds: number | null;
  dkOverOdds: number | null;
  vendor: string | null;
  oddId: string | null;
  marketName: string | null;
  snapshotAt: string | null;
  updatedAt: string | null;
  createdAt: string | null;
  homeTeam: string | null;
  awayTeam: string | null;
  gameStart: string | null;
  raw: any;
};

async function fetchRawPlayerPropLineRows(params: {
  startsAt: string;
  homeTeam: string;
  awayTeam?: string | null;
}): Promise<RawPlayerPropLineRow[]> {
  const { rows } = await pool.query(
    `SELECT
       "playerExternalId" AS "playerExternalId",
       "propType" AS "propType",
       "lineValue" AS "lineValue",
       "fdLine" AS "fdLine",
       "dkLine" AS "dkLine",
       "oddsAmerican" AS "oddsAmerican",
       "fdOverOdds" AS "fdOverOdds",
       "dkOverOdds" AS "dkOverOdds",
       vendor,
       "oddId" AS "oddId",
       "marketName" AS "marketName",
       "snapshotAt" AS "snapshotAt",
       "updatedAt" AS "updatedAt",
       "createdAt" AS "createdAt",
       "homeTeam" AS "homeTeam",
       "awayTeam" AS "awayTeam",
       "gameStart" AS "gameStart",
       raw
     FROM "PlayerPropLine"
     WHERE league = 'mlb'
       AND COALESCE("marketScope", 'full_game') = 'full_game'
       AND (("gameStart")::timestamptz AT TIME ZONE 'America/New_York')::date = (($1::timestamptz) AT TIME ZONE 'America/New_York')::date
       AND ("gameStart")::timestamptz BETWEEN ($1::timestamptz - INTERVAL '3 hours') AND ($1::timestamptz + INTERVAL '3 hours')
       AND ($3::text IS NULL OR (
         COALESCE("homeTeam", '') IN ($2::text, $3::text)
         AND COALESCE("awayTeam", '') IN ($2::text, $3::text)
       ))
       AND (
         COALESCE("homeTeam", '') = $2::text
         OR COALESCE("awayTeam", '') = $2::text
         OR COALESCE("homeTeam", '') = COALESCE($3::text, '')
         OR COALESCE("awayTeam", '') = COALESCE($3::text, '')
       )
       AND "propType" = ANY($4::text[])
     ORDER BY "snapshotAt" DESC NULLS LAST, "updatedAt" DESC NULLS LAST`,
    [
      params.startsAt,
      params.homeTeam.toUpperCase(),
      params.awayTeam?.toUpperCase() || null,
      Object.keys(SUPPORTED_MLB_PROP_TYPES),
    ],
  );

  return rows as RawPlayerPropLineRow[];
}

export async function buildNormalizedMlbMarketsForEvent(event: MlbCompletionEvent): Promise<NormalizedPlayerPropMarket[]> {
  const rawRows = await fetchRawPlayerPropLineRows({
    startsAt: event.startsAt,
    homeTeam: event.homeTeam,
    awayTeam: event.awayTeam,
  });

  const rawMarkets = rawRows
    .map((row) => mapPlayerPropLineRowToRawMarketSource(row, {
      eventId: event.eventId,
      startsAt: event.startsAt,
      homeTeam: event.homeTeam,
      awayTeam: event.awayTeam,
    }))
    .filter((row): row is NonNullable<typeof row> => Boolean(row));

  return normalizeGroupedMlbMarkets(groupRawMlbMarketSources(rawMarkets));
}

export async function buildNormalizedMlbMarketsForQuery(params: {
  startsAt: string;
  teamShort: string;
  opponentShort?: string | null;
}): Promise<NormalizedPlayerPropMarket[]> {
  const rawRows = await fetchRawPlayerPropLineRows({
    startsAt: params.startsAt,
    homeTeam: params.teamShort,
    awayTeam: params.opponentShort,
  });

  const eventId = buildFallbackMlbEventKey({
    startsAt: params.startsAt,
    homeTeam: params.teamShort,
    awayTeam: params.opponentShort || null,
  });

  const rawMarkets = rawRows
    .map((row) => mapPlayerPropLineRowToRawMarketSource(row, {
      eventId,
      startsAt: params.startsAt,
      homeTeam: row.homeTeam || params.teamShort,
      awayTeam: row.awayTeam || params.opponentShort || null,
    }))
    .filter((row): row is NonNullable<typeof row> => Boolean(row));

  return normalizeGroupedMlbMarkets(groupRawMlbMarketSources(rawMarkets));
}

export async function storeNormalizedMlbMarketsForEvent(
  event: MlbCompletionEvent,
  markets: NormalizedPlayerPropMarket[],
  client?: PoolClient,
): Promise<void> {
  const db = client || pool;
  await db.query('DELETE FROM rm_mlb_normalized_player_prop_markets WHERE event_id = $1', [event.eventId]);

  for (const market of markets) {
    await db.query(
      `INSERT INTO rm_mlb_normalized_player_prop_markets
         (event_id, league, starts_at, home_team, away_team, player_id, player_name, team_short,
          stat_type, normalized_stat_type, line, market_name, primary_source, completion_method,
          completeness_status, is_gap_filled, available_sides, over_payload, under_payload,
          source_map, raw_market_count, updated_at)
       VALUES
         ($1, 'mlb', $2, $3, $4, $5, $6, $7,
          $8, $9, $10, $11, $12, $13,
          $14, $15, $16::text[], $17::jsonb, $18::jsonb,
          $19::jsonb, $20, COALESCE($21::timestamptz, NOW()))
       ON CONFLICT (event_id, player_id, stat_type, line)
       DO UPDATE SET
         starts_at = EXCLUDED.starts_at,
         home_team = EXCLUDED.home_team,
         away_team = EXCLUDED.away_team,
         player_name = EXCLUDED.player_name,
         team_short = EXCLUDED.team_short,
         normalized_stat_type = EXCLUDED.normalized_stat_type,
         market_name = EXCLUDED.market_name,
         primary_source = EXCLUDED.primary_source,
         completion_method = EXCLUDED.completion_method,
         completeness_status = EXCLUDED.completeness_status,
         is_gap_filled = EXCLUDED.is_gap_filled,
         available_sides = EXCLUDED.available_sides,
         over_payload = EXCLUDED.over_payload,
         under_payload = EXCLUDED.under_payload,
         source_map = EXCLUDED.source_map,
         raw_market_count = EXCLUDED.raw_market_count,
         updated_at = EXCLUDED.updated_at`,
      [
        event.eventId,
        event.startsAt,
        event.homeTeam,
        event.awayTeam,
        market.playerId,
        market.playerName,
        market.teamShort,
        market.statType,
        market.normalizedStatType,
        market.line,
        market.marketName,
        market.primarySource,
        market.completionMethod,
        market.completenessStatus,
        market.isGapFilled,
        market.availableSides,
        market.over ? JSON.stringify(market.over) : null,
        market.under ? JSON.stringify(market.under) : null,
        JSON.stringify(market.sourceMap),
        market.rawMarketCount,
        market.updatedAt,
      ],
    );
  }
}