import 'dotenv/config';
import pool from '../db';
import {
  fetchHistoricalEventOdds,
  fetchHistoricalEvents,
  getTheOddsPlayerPropMarketKey,
  getTheOddsSportKey,
  hasTheOddsApiConfigured,
  matchHistoricalEvent,
  selectBestPlayerPropOutcome,
} from '../services/the-odds';

type CandidateRow = {
  id: string;
  event_id: string;
  league: string;
  player_name: string;
  prop_stat: string;
  direction: 'over' | 'under';
  rec_line: number | null;
  rec_odds: number | null;
  close_line: number | null;
  close_odds: number | null;
  starts_at: string;
  home_team: string;
  away_team: string;
  forecast_created_at: string | null;
};

type EventGroup = {
  eventId: string;
  league: string;
  startsAt: string;
  homeTeam: string;
  awayTeam: string;
  forecastCreatedAt: string | null;
  rows: CandidateRow[];
};

const BACKFILL_STATUS_TABLE = 'rm_theodds_backfill_status';
const EVENT_MISS_COOLDOWN_MS = 6 * 60 * 60 * 1000;
const SNAPSHOT_ERROR_COOLDOWN_MS = 2 * 60 * 60 * 1000;
const NO_MATCH_COOLDOWN_MS = 12 * 60 * 60 * 1000;
const PARTIAL_UPDATE_COOLDOWN_MS = 2 * 60 * 60 * 1000;
const UNSUPPORTED_ROW_COOLDOWN_MS = 7 * 24 * 60 * 60 * 1000;

function parsePositiveInt(value: string | undefined, fallback: number): number {
  const parsed = Number.parseInt(String(value || ''), 10);
  if (!Number.isFinite(parsed) || parsed <= 0) return fallback;
  return parsed;
}

function toIsoString(value: string | Date | null | undefined): string | null {
  if (!value) return null;
  const date = value instanceof Date ? value : new Date(value);
  if (Number.isNaN(date.getTime())) return null;
  return date.toISOString().replace(/\.\d{3}Z$/, 'Z');
}

function buildCloseSnapshotTime(startsAt: string): string {
  const date = new Date(startsAt);
  return toIsoString(new Date(date.getTime() - 60_000))!;
}

function buildEventLookupTime(group: EventGroup): string {
  return toIsoString(group.forecastCreatedAt)
    || new Date(new Date(group.startsAt).getTime() - 2 * 60 * 60 * 1000).toISOString();
}

function computeClvLine(direction: string, recLine: number | null, closeLine: number | null): number | null {
  if (recLine == null || closeLine == null) return null;
  if (direction === 'over') return recLine - closeLine;
  if (direction === 'under') return closeLine - recLine;
  return null;
}

function buildCooldownDate(ms: number): Date {
  return new Date(Date.now() + ms);
}

function groupByEvent(rows: CandidateRow[]): EventGroup[] {
  const groups = new Map<string, EventGroup>();
  for (const row of rows) {
    if (!groups.has(row.event_id)) {
      groups.set(row.event_id, {
        eventId: row.event_id,
        league: row.league,
        startsAt: row.starts_at,
        homeTeam: row.home_team,
        awayTeam: row.away_team,
        forecastCreatedAt: row.forecast_created_at,
        rows: [],
      });
    }
    groups.get(row.event_id)!.rows.push(row);
  }
  return [...groups.values()];
}

async function ensureBackfillStatusTable() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS ${BACKFILL_STATUS_TABLE} (
      pick_clv_id text PRIMARY KEY,
      event_id text NOT NULL,
      last_attempted_at timestamptz NOT NULL DEFAULT NOW(),
      blocked_until timestamptz NULL,
      attempt_count integer NOT NULL DEFAULT 0,
      last_result text NULL
    )
  `);
  await pool.query(`
    CREATE INDEX IF NOT EXISTS idx_rm_theodds_backfill_status_blocked_until
    ON ${BACKFILL_STATUS_TABLE} (blocked_until)
  `);
}

async function recordBackfillAttempt(
  pickClvId: string,
  eventId: string,
  result: string,
  cooldownMs: number,
) {
  await pool.query(
    `INSERT INTO ${BACKFILL_STATUS_TABLE} (
       pick_clv_id,
       event_id,
       last_attempted_at,
       blocked_until,
       attempt_count,
       last_result
     )
     VALUES ($1, $2, NOW(), $3, 1, $4)
     ON CONFLICT (pick_clv_id) DO UPDATE
     SET event_id = EXCLUDED.event_id,
         last_attempted_at = NOW(),
         blocked_until = EXCLUDED.blocked_until,
         attempt_count = ${BACKFILL_STATUS_TABLE}.attempt_count + 1,
         last_result = EXCLUDED.last_result`,
    [pickClvId, eventId, buildCooldownDate(cooldownMs), result],
  );
}

async function clearBackfillAttempt(pickClvId: string) {
  await pool.query(`DELETE FROM ${BACKFILL_STATUS_TABLE} WHERE pick_clv_id = $1`, [pickClvId]);
}

export async function backfillTheOddsPlayerPropOdds(options: {
  limitRows?: number;
  refreshViews?: boolean;
  closePool?: boolean;
} = {}) {
  const {
    limitRows = parsePositiveInt(process.env.THE_ODDS_BACKFILL_ROW_LIMIT, 200),
    refreshViews = true,
    closePool = false,
  } = options;

  if (!hasTheOddsApiConfigured()) {
    console.log('[theodds-backfill] THE_ODDS_API_KEY is not configured; skipping');
    return {
      enabled: false,
      rowsScanned: 0,
      rowsUpdated: 0,
      recOddsUpdated: 0,
      closeOddsUpdated: 0,
      closeLinesUpdated: 0,
      eventMisses: 0,
      unsupportedRows: 0,
    };
  }

  await ensureBackfillStatusTable();

  const { rows } = await pool.query(
    `SELECT
       p.id,
       p.event_id,
       p.league,
       p.player_name,
       p.prop_stat,
       p.direction,
       p.rec_line,
       p.rec_odds,
       p.close_line,
       p.close_odds,
       e.starts_at,
       e.home_team,
       e.away_team,
       fc.created_at AS forecast_created_at,
       s.last_attempted_at
     FROM rm_pick_clv p
     JOIN rm_events e ON e.event_id = p.event_id
     LEFT JOIN rm_forecast_cache fc ON fc.event_id = p.event_id
     LEFT JOIN ${BACKFILL_STATUS_TABLE} s ON s.pick_clv_id = p.id::text
     WHERE p.pick_type = 'player_prop'
       AND (
         p.rec_odds IS NULL
         OR p.close_odds IS NULL
         OR p.close_line IS NULL
       )
       AND (s.blocked_until IS NULL OR s.blocked_until <= NOW())
     ORDER BY COALESCE(s.last_attempted_at, to_timestamp(0)) ASC, e.starts_at DESC, p.created_at DESC
     LIMIT $1`,
    [limitRows],
  );

  const candidates = rows as CandidateRow[];
  const eventGroups = groupByEvent(candidates);
  const eventCache = new Map<string, string | null>();

  let rowsUpdated = 0;
  let recOddsUpdated = 0;
  let closeOddsUpdated = 0;
  let closeLinesUpdated = 0;
  let eventMisses = 0;
  let unsupportedRows = 0;

  console.log(`[theodds-backfill] Processing ${candidates.length} rows across ${eventGroups.length} events`);

  for (const group of eventGroups) {
    const sportKey = getTheOddsSportKey(group.league);
    if (!sportKey) {
      unsupportedRows += group.rows.length;
      for (const row of group.rows) {
        await recordBackfillAttempt(row.id, row.event_id, 'unsupported_sport', UNSUPPORTED_ROW_COOLDOWN_MS);
      }
      continue;
    }

    const marketKeys = [...new Set(group.rows
      .map((row) => getTheOddsPlayerPropMarketKey(row.league, row.prop_stat))
      .filter((value): value is string => Boolean(value)))];
    if (marketKeys.length === 0) {
      unsupportedRows += group.rows.length;
      for (const row of group.rows) {
        await recordBackfillAttempt(row.id, row.event_id, 'unsupported_market', UNSUPPORTED_ROW_COOLDOWN_MS);
      }
      continue;
    }

    const eventLookupKey = `${sportKey}:${group.eventId}`;
    let theOddsEventId = eventCache.get(eventLookupKey) ?? null;
    if (!eventCache.has(eventLookupKey)) {
      try {
        const historicalEvents = await fetchHistoricalEvents(sportKey, buildEventLookupTime(group));
        const matchedEvent = matchHistoricalEvent(historicalEvents, {
          homeTeam: group.homeTeam,
          awayTeam: group.awayTeam,
          startsAt: group.startsAt,
        });
        theOddsEventId = matchedEvent?.id || null;
        eventCache.set(eventLookupKey, theOddsEventId);
      } catch (err: any) {
        console.error(`[theodds-backfill] Event lookup failed for ${group.awayTeam} @ ${group.homeTeam}: ${err.message}`);
        theOddsEventId = null;
        eventCache.set(eventLookupKey, null);
        for (const row of group.rows) {
          await recordBackfillAttempt(row.id, row.event_id, 'event_lookup_failed', SNAPSHOT_ERROR_COOLDOWN_MS);
        }
      }
    }

    if (!theOddsEventId) {
      eventMisses += group.rows.length;
      for (const row of group.rows) {
        await recordBackfillAttempt(row.id, row.event_id, 'event_miss', EVENT_MISS_COOLDOWN_MS);
      }
      continue;
    }

    const recSnapshotTime = toIsoString(group.forecastCreatedAt);
    let recSnapshot: Awaited<ReturnType<typeof fetchHistoricalEventOdds>> | null = null;
    if (recSnapshotTime) {
      try {
        recSnapshot = await fetchHistoricalEventOdds({
          sportKey,
          eventId: theOddsEventId,
          snapshotTime: recSnapshotTime,
          markets: marketKeys,
        });
      } catch (err: any) {
        console.error(`[theodds-backfill] Recommendation snapshot failed for ${group.awayTeam} @ ${group.homeTeam}: ${err.message}`);
        for (const row of group.rows.filter((candidate) => candidate.rec_odds == null)) {
          await recordBackfillAttempt(row.id, row.event_id, 'rec_snapshot_failed', SNAPSHOT_ERROR_COOLDOWN_MS);
        }
      }
    }

    let closeSnapshot: Awaited<ReturnType<typeof fetchHistoricalEventOdds>> | null = null;
    try {
      closeSnapshot = await fetchHistoricalEventOdds({
        sportKey,
        eventId: theOddsEventId,
        snapshotTime: buildCloseSnapshotTime(group.startsAt),
        markets: marketKeys,
      });
    } catch (err: any) {
      console.error(`[theodds-backfill] Closing snapshot failed for ${group.awayTeam} @ ${group.homeTeam}: ${err.message}`);
      for (const row of group.rows.filter((candidate) => candidate.close_odds == null || candidate.close_line == null)) {
        await recordBackfillAttempt(row.id, row.event_id, 'close_snapshot_failed', SNAPSHOT_ERROR_COOLDOWN_MS);
      }
    }

    for (const row of group.rows) {
      const marketKey = getTheOddsPlayerPropMarketKey(row.league, row.prop_stat);
      if (!marketKey) {
        unsupportedRows++;
        await recordBackfillAttempt(row.id, row.event_id, 'unsupported_market', UNSUPPORTED_ROW_COOLDOWN_MS);
        continue;
      }

      const recOutcome = row.rec_odds == null && recSnapshot
        ? selectBestPlayerPropOutcome(recSnapshot, {
            marketKey,
            playerName: row.player_name,
            direction: row.direction,
            targetLine: row.rec_line,
            preferNearestLine: true,
          })
        : null;

      const closeOutcome = closeSnapshot
        ? selectBestPlayerPropOutcome(closeSnapshot, {
            marketKey,
            playerName: row.player_name,
            direction: row.direction,
            targetLine: row.close_line ?? row.rec_line,
            preferNearestLine: true,
          })
        : null;

      const nextRecOdds = row.rec_odds ?? (recOutcome?.odds != null ? Math.round(recOutcome.odds) : null);
      const nextCloseOdds = row.close_odds ?? (closeOutcome?.odds != null ? Math.round(closeOutcome.odds) : null);
      const nextCloseLine = row.close_line ?? closeOutcome?.line ?? null;
      const nextClvLine = computeClvLine(row.direction, row.rec_line, nextCloseLine);
      const recResolved = nextRecOdds != null;
      const closeOddsResolved = nextCloseOdds != null;
      const closeLineResolved = nextCloseLine != null;
      const fullyResolved = recResolved && closeOddsResolved && closeLineResolved;

      if (nextRecOdds == null && nextCloseOdds == null && nextCloseLine == null) {
        await recordBackfillAttempt(row.id, row.event_id, 'no_matching_market', NO_MATCH_COOLDOWN_MS);
        continue;
      }

      await pool.query(
        `UPDATE rm_pick_clv
         SET rec_odds = COALESCE($1, rec_odds),
             close_odds = COALESCE($2, close_odds),
             close_line = COALESCE($3, close_line),
             clv_line = CASE
               WHEN $4::double precision IS NOT NULL THEN $4::double precision
               ELSE clv_line
             END
         WHERE id = $5`,
        [nextRecOdds, nextCloseOdds, nextCloseLine, nextClvLine, row.id],
      );

      rowsUpdated++;
      if (row.rec_odds == null && nextRecOdds != null) recOddsUpdated++;
      if (row.close_odds == null && nextCloseOdds != null) closeOddsUpdated++;
      if (row.close_line == null && nextCloseLine != null) closeLinesUpdated++;
      if (fullyResolved) {
        await clearBackfillAttempt(row.id);
      } else {
        await recordBackfillAttempt(row.id, row.event_id, 'partial_update', PARTIAL_UPDATE_COOLDOWN_MS);
      }
    }
  }

  if (refreshViews && rowsUpdated > 0) {
    await pool.query('REFRESH MATERIALIZED VIEW rm_clv_summary').catch(() => undefined);
    await pool.query('REFRESH MATERIALIZED VIEW rm_model_kpi_stats').catch(() => undefined);
  }

  const summary = {
    enabled: true,
    rowsScanned: candidates.length,
    rowsUpdated,
    recOddsUpdated,
    closeOddsUpdated,
    closeLinesUpdated,
    eventMisses,
    unsupportedRows,
  };
  console.log(`[theodds-backfill] Done ${JSON.stringify(summary)}`);

  if (closePool) {
    await pool.end().catch(() => undefined);
  }

  return summary;
}

async function main() {
  await backfillTheOddsPlayerPropOdds({ closePool: true });
}

if (require.main === module) {
  main().catch((err) => {
    console.error('[theodds-backfill] Fatal error:', err);
    process.exit(1);
  });
}