import 'dotenv/config';
import pool from '../db';
import { getTeamAbbr, normalizeTeamNameKey } from '../lib/team-abbreviations';

type AuditRow = {
  eventId: string;
  resolvedAt: string | Date;
  startsAt: string | Date;
  homeTeam: string;
  awayTeam: string;
  homeShort: string;
  awayShort: string;
};

type MarketHistoryRow = {
  gameDay: string;
  homeTeam: string | null;
  awayTeam: string | null;
  marketType: string | null;
  recordedAt: string | Date | null;
};

type BookSnapshotRow = {
  gameDay: string;
  homeTeam: string | null;
  awayTeam: string | null;
  bookmaker: string | null;
  market: string | null;
  lineValue: number | null;
  homeOdds: number | null;
  awayOdds: number | null;
  overOdds: number | null;
  underOdds: number | null;
  openingLineValue: number | null;
  openingHomeOdds: number | null;
  openingAwayOdds: number | null;
  openingOverOdds: number | null;
  openingUnderOdds: number | null;
  fetchedAt: string | Date | null;
};

type EventAudit = {
  eventId: string;
  gameDay: string;
  homeTeam: string;
  awayTeam: string;
  movementCoverage: boolean;
  bookCoverage: boolean;
  bookCoverageWithOpener: boolean;
  movementReasons: string[];
  bookReasons: string[];
  movement: {
    dateRows: number;
    exactRows: number;
    spreadRows: number;
  };
  books: {
    dateRows: number;
    exactRows: number;
    preStartRows: number;
    openerFallbackRows: number;
    moneylineRows: number;
    homeProbSamples: number;
  };
};

type ReasonSummary = {
  code: string;
  count: number;
};

function timestampMs(value: string | Date | null | undefined): number | null {
  if (value instanceof Date) return value.getTime();
  if (typeof value === 'string' && value) {
    const parsed = Date.parse(value);
    return Number.isFinite(parsed) ? parsed : null;
  }
  return null;
}

function toIsoDateKey(value: string | Date): string {
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  return String(value || '').slice(0, 10);
}

function candidateGameDateKeys(value: string | Date): string[] {
  const rawMs = timestampMs(value);
  if (rawMs == null) {
    const key = toIsoDateKey(value);
    return key ? [key] : [];
  }
  const sameDay = new Date(rawMs);
  const priorDay = new Date(rawMs - (24 * 60 * 60 * 1000));
  return [...new Set([sameDay.toISOString().slice(0, 10), priorDay.toISOString().slice(0, 10)])];
}

function createGameDateMap<T extends { gameDay: string }>(rows: T[]): Map<string, T[]> {
  const map = new Map<string, T[]>();
  for (const row of rows) {
    const bucket = map.get(row.gameDay);
    if (bucket) bucket.push(row);
    else map.set(row.gameDay, [row]);
  }
  return map;
}

function buildExactKeys(params: { teamName: string | null | undefined; teamShort?: string | null | undefined }): string[] {
  const keys = new Set<string>();
  for (const value of [params.teamName, params.teamShort, getTeamAbbr(params.teamName || null) || null]) {
    const key = normalizeTeamNameKey(value);
    if (key.length >= 2) keys.add(key);
  }
  return [...keys];
}

function matchesExactGame(row: { homeTeam: string | null; awayTeam: string | null }, homeKeys: string[], awayKeys: string[]): boolean {
  const homeKey = normalizeTeamNameKey(row.homeTeam);
  const awayKey = normalizeTeamNameKey(row.awayTeam);
  return homeKeys.includes(homeKey) && awayKeys.includes(awayKey);
}

function americanToProbability(odds: number | null | undefined): number | null {
  if (odds == null || !Number.isFinite(odds) || odds === 0) return null;
  return odds > 0 ? 100 / (odds + 100) : (-odds) / ((-odds) + 100);
}

async function loadRows(): Promise<AuditRow[]> {
  const { rows } = await pool.query(
    `SELECT fa.event_id,
            fa.resolved_at,
            fc.starts_at,
            fc.home_team,
            fc.away_team,
            ev.home_short,
            ev.away_short
       FROM rm_forecast_accuracy_v2 fa
       JOIN rm_forecast_cache fc ON fc.event_id = fa.event_id
       LEFT JOIN rm_events ev ON ev.event_id = fa.event_id
      WHERE fa.model_version = 'rm_2.0'
        AND fa.forecast_type = 'spread'
        AND fa.league = 'mlb'
        AND fa.resolved_at >= NOW() - INTERVAL '120 days'
        AND fa.predicted_winner IS NOT NULL
        AND fa.actual_winner IS NOT NULL
        AND fc.model_signals ? 'rieSignals'
      ORDER BY fa.resolved_at ASC`,
  );

  return rows
    .map((row: any) => ({
      eventId: row.event_id,
      resolvedAt: row.resolved_at,
      startsAt: row.starts_at,
      homeTeam: row.home_team,
      awayTeam: row.away_team,
      homeShort: row.home_short || '',
      awayShort: row.away_short || '',
    }))
    .filter((row) => row.homeShort && row.awayShort);
}

async function loadPrefetchedMarketData(dateKeys: string[]): Promise<{
  movementByDate: Map<string, MarketHistoryRow[]>;
  booksByDate: Map<string, BookSnapshotRow[]>;
  minMovementDate: string | null;
  maxMovementDate: string | null;
  minBookDate: string | null;
  maxBookDate: string | null;
}> {
  const [movementRes, booksRes, spanRes] = await Promise.all([
    pool.query(
      `SELECT DATE("gameDate")::text AS "gameDay",
              "homeTeam" AS "homeTeam",
              "awayTeam" AS "awayTeam",
              "marketType",
              "recordedAt"
         FROM "LineMovement"
        WHERE league = 'mlb'
          AND DATE("gameDate") = ANY($1::date[])`,
      [dateKeys],
    ),
    pool.query(
      `SELECT DATE("gameDate")::text AS "gameDay",
              "homeTeam" AS "homeTeam",
              "awayTeam" AS "awayTeam",
              bookmaker, market, "lineValue", "homeOdds", "awayOdds", "overOdds", "underOdds",
              "openingLineValue", "openingHomeOdds", "openingAwayOdds", "openingOverOdds", "openingUnderOdds", "fetchedAt"
         FROM "GameOdds"
        WHERE league = 'mlb'
          AND DATE("gameDate") = ANY($1::date[])`,
      [dateKeys],
    ),
    pool.query(
      `SELECT
          (SELECT MIN(DATE("gameDate"))::text FROM "LineMovement" WHERE league = 'mlb') AS min_movement_date,
          (SELECT MAX(DATE("gameDate"))::text FROM "LineMovement" WHERE league = 'mlb') AS max_movement_date,
          (SELECT MIN(DATE("gameDate"))::text FROM "GameOdds" WHERE league = 'mlb') AS min_book_date,
          (SELECT MAX(DATE("gameDate"))::text FROM "GameOdds" WHERE league = 'mlb') AS max_book_date`
    ),
  ]);

  return {
    movementByDate: createGameDateMap(movementRes.rows as MarketHistoryRow[]),
    booksByDate: createGameDateMap(booksRes.rows as BookSnapshotRow[]),
    minMovementDate: spanRes.rows[0]?.min_movement_date || null,
    maxMovementDate: spanRes.rows[0]?.max_movement_date || null,
    minBookDate: spanRes.rows[0]?.min_book_date || null,
    maxBookDate: spanRes.rows[0]?.max_book_date || null,
  };
}

function auditEvent(
  row: AuditRow,
  prefetched: {
    movementByDate: Map<string, MarketHistoryRow[]>;
    booksByDate: Map<string, BookSnapshotRow[]>;
    minMovementDate: string | null;
    minBookDate: string | null;
  },
): EventAudit {
  const homeKeys = buildExactKeys({ teamName: row.homeTeam, teamShort: row.homeShort });
  const awayKeys = buildExactKeys({ teamName: row.awayTeam, teamShort: row.awayShort });
  const gameDays = candidateGameDateKeys(row.startsAt);
  const startsAtMs = timestampMs(row.startsAt) ?? Number.POSITIVE_INFINITY;

  const dateMovementRows = gameDays.flatMap((gameDay) => prefetched.movementByDate.get(gameDay) || []);
  const exactMovementRows = dateMovementRows.filter((entry) => matchesExactGame(entry, homeKeys, awayKeys));
  const spreadMovementRows = exactMovementRows.filter((entry) => String(entry.marketType || '').toLowerCase() === 'spread');

  const dateBookRows = gameDays.flatMap((gameDay) => prefetched.booksByDate.get(gameDay) || []);
  const exactBookRows = dateBookRows.filter((entry) => matchesExactGame(entry, homeKeys, awayKeys));
  const preStartBookRows = exactBookRows.filter((entry) => {
    const fetchedAtMs = timestampMs(entry.fetchedAt);
    return fetchedAtMs == null || fetchedAtMs <= startsAtMs;
  });
  const openerFallbackRows = exactBookRows.filter((entry) => {
    const fetchedAtMs = timestampMs(entry.fetchedAt);
    const hasOpening = [entry.openingLineValue, entry.openingHomeOdds, entry.openingAwayOdds, entry.openingOverOdds, entry.openingUnderOdds]
      .some((value) => value != null && Number.isFinite(Number(value)));
    return Boolean(hasOpening) && (fetchedAtMs == null || fetchedAtMs > startsAtMs);
  });
  const resolvedBookRows = preStartBookRows.length > 0 ? preStartBookRows : openerFallbackRows.map((entry) => ({
    ...entry,
    lineValue: entry.openingLineValue ?? entry.lineValue,
    homeOdds: entry.openingHomeOdds ?? entry.homeOdds,
    awayOdds: entry.openingAwayOdds ?? entry.awayOdds,
    overOdds: entry.openingOverOdds ?? entry.overOdds,
    underOdds: entry.openingUnderOdds ?? entry.underOdds,
    fetchedAt: null,
  }));
  const moneylineBookRows = resolvedBookRows.filter((entry) => ['moneyline', 'h2h'].includes(String(entry.market || '').toLowerCase()));
  const homeProbSamples = moneylineBookRows
    .map((entry) => {
      const homeProb = americanToProbability(entry.homeOdds);
      const awayProb = americanToProbability(entry.awayOdds);
      if (homeProb == null || awayProb == null) return null;
      const vig = homeProb + awayProb;
      return vig > 0 ? homeProb / vig : homeProb;
    })
    .filter((value): value is number => value != null);

  const movementReasons: string[] = [];
  const bookReasons: string[] = [];

  if (dateMovementRows.length === 0) {
    movementReasons.push(gameDays.every((day) => prefetched.minMovementDate && day < prefetched.minMovementDate)
      ? 'movement_table_starts_after_game'
      : 'no_movement_rows_for_date');
  } else if (exactMovementRows.length === 0) {
    movementReasons.push('movement_team_match_miss');
  } else if (spreadMovementRows.length === 0) {
    movementReasons.push('movement_has_no_spread_rows');
  }

  if (dateBookRows.length === 0) {
    bookReasons.push(gameDays.every((day) => prefetched.minBookDate && day < prefetched.minBookDate)
      ? 'books_table_starts_after_game'
      : 'no_book_rows_for_date');
  } else if (exactBookRows.length === 0) {
    bookReasons.push('book_team_match_miss');
  } else if (preStartBookRows.length === 0 && openerFallbackRows.length === 0) {
    bookReasons.push('book_rows_only_after_start');
  } else if (moneylineBookRows.length === 0) {
    bookReasons.push('no_moneyline_book_rows');
  } else if (homeProbSamples.length === 0) {
    bookReasons.push('no_valid_moneyline_prob_samples');
  }

  return {
    eventId: row.eventId,
    gameDay: toIsoDateKey(row.startsAt),
    homeTeam: row.homeTeam,
    awayTeam: row.awayTeam,
    movementCoverage: exactMovementRows.length > 0,
    bookCoverage: preStartBookRows.length > 0 && homeProbSamples.length > 0,
    bookCoverageWithOpener: homeProbSamples.length > 0,
    movementReasons,
    bookReasons,
    movement: {
      dateRows: dateMovementRows.length,
      exactRows: exactMovementRows.length,
      spreadRows: spreadMovementRows.length,
    },
    books: {
      dateRows: dateBookRows.length,
      exactRows: exactBookRows.length,
      preStartRows: preStartBookRows.length,
      openerFallbackRows: openerFallbackRows.length,
      moneylineRows: moneylineBookRows.length,
      homeProbSamples: homeProbSamples.length,
    },
  };
}

function summarizeReasons(reasonLists: string[][]): ReasonSummary[] {
  const counts = new Map<string, number>();
  for (const reasons of reasonLists) {
    for (const reason of reasons) {
      counts.set(reason, (counts.get(reason) || 0) + 1);
    }
  }
  return [...counts.entries()]
    .map(([code, count]) => ({ code, count }))
    .sort((left, right) => right.count - left.count || left.code.localeCompare(right.code));
}

function parseArgs(argv: string[]): { json: boolean; limit: number } {
  let json = false;
  let limit = 20;
  for (let i = 0; i < argv.length; i += 1) {
    const arg = argv[i];
    if (arg === '--json') json = true;
    else if (arg === '--limit') limit = Number(argv[i + 1] || 20) || 20;
  }
  return { json, limit };
}

async function main(): Promise<void> {
  const args = parseArgs(process.argv.slice(2));
  const rows = await loadRows();
  const dateKeys = [...new Set(rows.flatMap((row) => candidateGameDateKeys(row.startsAt)))].sort();
  const prefetched = await loadPrefetchedMarketData(dateKeys);
  const events = rows.map((row) => auditEvent(row, prefetched));
  const movementCovered = events.filter((event) => event.movementCoverage).length;
  const bookCovered = events.filter((event) => event.bookCoverage).length;
  const uncoveredMovement = events.filter((event) => !event.movementCoverage);
  const uncoveredBooks = events.filter((event) => !event.bookCoverageWithOpener);
  const movementByDay = new Map<string, number>();
  const bookByDay = new Map<string, number>();
  for (const event of uncoveredMovement) movementByDay.set(event.gameDay, (movementByDay.get(event.gameDay) || 0) + 1);
  for (const event of uncoveredBooks) bookByDay.set(event.gameDay, (bookByDay.get(event.gameDay) || 0) + 1);

  const result = {
    totals: {
      gradedRows: events.length,
      movementCovered,
      movementMissing: events.length - movementCovered,
      movementCoveragePct: events.length > 0 ? movementCovered / events.length : 0,
      bookCovered,
      bookMissing: events.length - bookCovered,
      bookCoveragePct: events.length > 0 ? bookCovered / events.length : 0,
      bookCoveredWithOpener: events.filter((event) => event.bookCoverageWithOpener).length,
      bookMissingWithOpener: events.filter((event) => !event.bookCoverageWithOpener).length,
      bookCoverageWithOpenerPct: events.length > 0 ? events.filter((event) => event.bookCoverageWithOpener).length / events.length : 0,
    },
    sourceSpans: {
      movement: { minDate: prefetched.minMovementDate, maxDate: prefetched.maxMovementDate },
      books: { minDate: prefetched.minBookDate, maxDate: prefetched.maxBookDate },
    },
    reasons: {
      movementMissing: summarizeReasons(uncoveredMovement.map((event) => event.movementReasons)),
      bookMissing: summarizeReasons(uncoveredBooks.map((event) => event.bookReasons)),
    },
    uncoveredByDay: {
      movement: [...movementByDay.entries()].map(([gameDay, count]) => ({ gameDay, count })).sort((a, b) => a.gameDay.localeCompare(b.gameDay)),
      books: [...bookByDay.entries()].map(([gameDay, count]) => ({ gameDay, count })).sort((a, b) => a.gameDay.localeCompare(b.gameDay)),
    },
    sampleUncoveredEvents: {
      movement: uncoveredMovement.slice(0, args.limit),
      books: uncoveredBooks.slice(0, args.limit),
    },
  };

  if (args.json) {
    console.log(JSON.stringify(result, null, 2));
    return;
  }

  console.log(`MLB market history audit`);
  console.log(`graded rows: ${result.totals.gradedRows}`);
  console.log(`movement coverage: ${result.totals.movementCovered}/${result.totals.gradedRows} (${(result.totals.movementCoveragePct * 100).toFixed(1)}%)`);
  console.log(`book coverage prestart-only: ${result.totals.bookCovered}/${result.totals.gradedRows} (${(result.totals.bookCoveragePct * 100).toFixed(1)}%)`);
  console.log(`book coverage with opener fallback: ${result.totals.bookCoveredWithOpener}/${result.totals.gradedRows} (${(result.totals.bookCoverageWithOpenerPct * 100).toFixed(1)}%)`);
  console.log(`movement span: ${result.sourceSpans.movement.minDate || 'none'} -> ${result.sourceSpans.movement.maxDate || 'none'}`);
  console.log(`books span: ${result.sourceSpans.books.minDate || 'none'} -> ${result.sourceSpans.books.maxDate || 'none'}`);
  console.log(`movement miss reasons:`);
  for (const reason of result.reasons.movementMissing) console.log(`  - ${reason.code}: ${reason.count}`);
  console.log(`book miss reasons:`);
  for (const reason of result.reasons.bookMissing) console.log(`  - ${reason.code}: ${reason.count}`);
  console.log(`sample movement misses:`);
  for (const event of result.sampleUncoveredEvents.movement) console.log(`  - ${event.gameDay} ${event.eventId} :: ${event.movementReasons.join(', ') || 'none'}`);
  console.log(`sample book misses:`);
  for (const event of result.sampleUncoveredEvents.books) console.log(`  - ${event.gameDay} ${event.eventId} :: ${event.bookReasons.join(', ') || 'none'}`);
}

main().catch(async (error) => {
  console.error(error);
  await pool.end();
  process.exit(1);
}).finally(async () => {
  await pool.end();
});