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

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

type MovementRow = {
  gameDay: string;
  homeTeam: string | null;
  awayTeam: string | null;
  marketType: string | null;
  gameExternalId: string | null;
  recordedAt: string | Date | null;
  gameStartTime: string | Date | null;
};

type EventFinding = {
  eventId: string;
  gameDay: string;
  homeTeam: string;
  awayTeam: string;
  reason: string;
  candidateRows: number;
  exactCandidateRows: number;
  nearbyExactRows: number;
  nearestOffsetDays: number | null;
  lateRecordedNearbyRows: number;
  sampleNearby: Array<{ gameDay: string; marketType: string | null; recordedAt: string | Date | null; gameStartTime: string | Date | null; gameExternalId: string | null }>;
};

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 dateOffsetKeys(value: string | Date, offsets: number[]): string[] {
  const ms = timestampMs(value);
  if (ms == null) {
    const key = toIsoDateKey(value);
    return key ? [key] : [];
  }
  return [...new Set(offsets.map((offset) => new Date(ms + offset * 86400000).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);
}

async function loadRows(): Promise<AuditRow[]> {
  const { rows } = await pool.query(
    `SELECT fa.event_id, 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,
      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 loadMovement(dateKeys: string[]): Promise<Map<string, MovementRow[]>> {
  const { rows } = await pool.query(
    `SELECT DATE("gameDate")::text AS "gameDay",
            "homeTeam" AS "homeTeam",
            "awayTeam" AS "awayTeam",
            "marketType",
            "gameExternalId",
            "recordedAt",
            "gameStartTime"
       FROM "LineMovement"
      WHERE league = 'mlb'
        AND DATE("gameDate") = ANY($1::date[])`,
    [dateKeys],
  );
  return createGameDateMap(rows as MovementRow[]);
}

function signedDayDelta(dayA: string, dayB: string): number {
  const a = Date.parse(`${dayA}T00:00:00Z`);
  const b = Date.parse(`${dayB}T00:00:00Z`);
  return Math.round((a - b) / 86400000);
}

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) => dateOffsetKeys(row.startsAt, [-3,-2,-1,0,1,2,3])))].sort();
  const movementByDate = await loadMovement(dateKeys);

  const findings: EventFinding[] = [];
  for (const row of rows) {
    const homeKeys = buildExactKeys({ teamName: row.homeTeam, teamShort: row.homeShort });
    const awayKeys = buildExactKeys({ teamName: row.awayTeam, teamShort: row.awayShort });
    const candidateDays = dateOffsetKeys(row.startsAt, [-1, 0]);
    const extendedDays = dateOffsetKeys(row.startsAt, [-3,-2,-1,0,1,2,3]);
    const gameDay = toIsoDateKey(row.startsAt);

    const candidateRows = candidateDays.flatMap((day) => movementByDate.get(day) || []);
    const exactCandidateRows = candidateRows.filter((entry) => matchesExactGame(entry, homeKeys, awayKeys));
    if (exactCandidateRows.length > 0) continue;

    const nearbyRows = extendedDays.flatMap((day) => movementByDate.get(day) || []);
    const nearbyExactRows = nearbyRows.filter((entry) => matchesExactGame(entry, homeKeys, awayKeys));
    const lateRecordedNearbyRows = nearbyExactRows.filter((entry) => {
      const recordedAtMs = timestampMs(entry.recordedAt);
      const gameStartTimeMs = timestampMs(entry.gameStartTime);
      return recordedAtMs != null && gameStartTimeMs != null && recordedAtMs > gameStartTimeMs;
    }).length;

    let reason = 'no_movement_rows_nearby';
    let nearestOffsetDays: number | null = null;
    if (nearbyExactRows.length > 0) {
      const offsets = nearbyExactRows.map((entry) => signedDayDelta(entry.gameDay, gameDay));
      nearestOffsetDays = offsets.sort((a, b) => Math.abs(a) - Math.abs(b))[0] ?? null;
      reason = lateRecordedNearbyRows === nearbyExactRows.length
        ? 'exact_game_exists_but_recorded_after_start'
        : 'exact_game_exists_outside_candidate_window';
    } else if (candidateRows.length > 0) {
      reason = 'candidate_bucket_has_other_games_only';
    }

    findings.push({
      eventId: row.eventId,
      gameDay,
      homeTeam: row.homeTeam,
      awayTeam: row.awayTeam,
      reason,
      candidateRows: candidateRows.length,
      exactCandidateRows: exactCandidateRows.length,
      nearbyExactRows: nearbyExactRows.length,
      nearestOffsetDays,
      lateRecordedNearbyRows,
      sampleNearby: nearbyExactRows.slice(0, 5).map((entry) => ({
        gameDay: entry.gameDay,
        marketType: entry.marketType,
        recordedAt: entry.recordedAt,
        gameStartTime: entry.gameStartTime,
        gameExternalId: entry.gameExternalId,
      })),
    });
  }

  const totals = {
    gradedRows: rows.length,
    uncoveredRows: findings.length,
  };
  const reasonCounts = new Map<string, number>();
  for (const finding of findings) reasonCounts.set(finding.reason, (reasonCounts.get(finding.reason) || 0) + 1);
  const rowLevel = await pool.query(
    `SELECT
        COUNT(*)::int AS total_rows,
        COUNT(*) FILTER (WHERE "recordedAt" > "gameStartTime")::int AS recorded_after_start_rows,
        COUNT(*) FILTER (WHERE DATE("gameStartTime")::text <> DATE("gameDate")::text)::int AS game_date_mismatch_rows,
        COUNT(DISTINCT "gameExternalId")::int AS external_ids
       FROM "LineMovement"
      WHERE league = 'mlb'`
  );

  const result = {
    totals,
    rowLevel: rowLevel.rows[0],
    reasons: [...reasonCounts.entries()].map(([code, count]) => ({ code, count })).sort((a, b) => b.count - a.count || a.code.localeCompare(b.code)),
    samples: findings.slice(0, args.limit),
  };

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

  console.log('MLB line movement audit');
  console.log(`graded rows: ${totals.gradedRows}`);
  console.log(`uncovered rows: ${totals.uncoveredRows}`);
  console.log(`row-level: total=${result.rowLevel.total_rows} recorded_after_start=${result.rowLevel.recorded_after_start_rows} game_date_mismatch=${result.rowLevel.game_date_mismatch_rows} external_ids=${result.rowLevel.external_ids}`);
  console.log('reasons:');
  for (const reason of result.reasons) console.log(`  - ${reason.code}: ${reason.count}`);
  console.log('samples:');
  for (const finding of result.samples) console.log(`  - ${finding.gameDay} ${finding.eventId} :: ${finding.reason} nearby=${finding.nearbyExactRows} nearestOffset=${finding.nearestOffsetDays}`);
}

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