/**
 * Weather Report — Daily Precompute Job
 *
 * Generates cached forecasts for today's events at 9:00 AM ET.
 * Per event, produces a precomputed asset board:
 *   - GAME_MARKETS legacy compatibility bundle
 *   - explicit market-family assets (GAME_TOTAL, MLB_RUN_LINE, MLB_F5_SIDE, MLB_F5_TOTAL)
 *   - TEAM_PROPS team bundles
 *   - PLAYER_PROP extracted assets
 *   - MLB_F5 legacy compatibility bundle
 *
 * Hard cap: default 600 forecasts/day → max 200 events per run.
 * Warning threshold: 90% usage triggers monitoring alerts.
 *
 * Usage: npx tsx src/workers/weather-report.ts [--dry-run] [--schedule CUSTOM_NAME]
 */

import 'dotenv/config';
import pool from '../db';
import { generateForecast, generateTeamProps, ForecastResult, __mlbPropInternals } from '../services/grok';
import { getCachedForecast, updateCachedOdds } from '../models/forecast';
import { getTeamRoster, resolveCanonicalName } from '../services/canonical-names';
import { buildForecastFromCuratedEvent } from '../services/forecast-builder';
import { mlbPhaseSignal } from '../services/rie/signals/mlb-phase-signal';
import { buildStoredMlbSnapshotFromPhase, summarizeMlbOperationalAlerts } from '../services/mlb-snapshot';
import { describeForecastAsset, ForecastAssetType, getMlbPlayerRole, summarizeForecastCountRows } from '../services/forecast-asset-taxonomy';
import { normalizePlayerPropMarketStat, resolvePlayerPropStatIdentity } from '../services/player-prop-market-registry';
import { americanOddsToImpliedProbability, buildPlayerPropMetadata, explainPlayerPropMetadataRejection } from '../services/player-prop-signals';
import {
  buildStoredPlayerPropPayload,
  shouldRenderTeamPropBundleEntry,
} from '../services/player-prop-storage';
import { recordClvPick } from '../services/clv-picks';
import { fetchInsightSourceData } from '../services/insight-source';
import { fetchDirectMlbPropCandidates, fetchMlbPropCandidates } from '../services/mlb-prop-markets';
import { EU_EVENT_LOOKAHEAD_DAYS, isLeagueEventInWindow } from '../lib/league-windows';
import { usesAssetBackedPropHighlights } from '../lib/prop-highlights';

const DAILY_CAP = Number(process.env.WEATHER_REPORT_DAILY_CAP || 600);
const CAP_WARN_THRESHOLD = Number(process.env.WEATHER_REPORT_CAP_WARN_THRESHOLD || 0.9);
const FORECASTS_PER_EVENT = 3;
const MAX_EVENTS = Math.floor(DAILY_CAP / FORECASTS_PER_EVENT);
const ENFORCE_CAP = process.env.WEATHER_REPORT_ENFORCE_CAP === 'true';
const MLB_MARKETS_ENABLED = process.env.MLB_MARKETS_ENABLED !== 'false';
const STALE_RUN_MAX_AGE_MINUTES = Number(process.env.WEATHER_REPORT_STALE_RUN_MAX_AGE_MINUTES || 120);

const dryRun = process.argv.includes('--dry-run');
const scheduleFlag = process.argv.indexOf('--schedule');
const scheduleName = scheduleFlag >= 0 && process.argv[scheduleFlag + 1]
  ? process.argv[scheduleFlag + 1]
  : 'DAILY_9AM_ET';
const leagueFlag = process.argv.indexOf('--league');
const leagueFilter = leagueFlag >= 0 && process.argv[leagueFlag + 1]
  ? process.argv[leagueFlag + 1].toLowerCase()
  : null;
const MIN_PLAYER_PROPS_PER_TEAM = Math.max(1, Number(process.env.MIN_PLAYER_PROPS_PER_TEAM || 5));
const MIN_PLAYER_PROPS_PER_EVENT = Math.max(MIN_PLAYER_PROPS_PER_TEAM * 2, Number(process.env.MIN_PLAYER_PROPS_PER_EVENT || 10));

function sleep(ms: number) {
  return new Promise(r => setTimeout(r, ms));
}

/** Get today's date in America/New_York */
function getTodayET(): string {
  return new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });
}

export function isWeatherReportEventInWindow(
  league: string,
  startsAt: string | Date,
  now: Date = new Date(),
): boolean {
  return isLeagueEventInWindow(league, startsAt, EU_EVENT_LOOKAHEAD_DAYS, now, 0);
}

export interface EventRow {
  event_id: string;
  league: string;
  home_team: string;
  away_team: string;
  home_short: string;
  away_short: string;
  starts_at: string;
  moneyline: any;
  spread: any;
  total: any;
  prop_count: number;
}

interface ForecastPickInsertInput {
  assetId: string;
  eventId: string;
  recommendation: string;
  line: number | null;
  odds: number | null;
  confidence: number | null;
  edge: number | null;
  gradingCategory: string | null;
  signalTier: string | null;
  marketImpliedProbability: number | null;
  projectedProbability: number | null;
  projectedOutcome: number | null;
}

interface ForecastPickInsertSpec {
  sql: string;
  values: Array<string | number | null>;
}

let forecastPickColumnsCache: Promise<Set<string>> | null = null;

const SOURCE_BACKED_EXPLICIT_MLB_TYPES: ForecastAssetType[] = ['GAME_TOTAL', 'MLB_RUN_LINE'];
const MODELED_EXPLICIT_MLB_TYPES: ForecastAssetType[] = ['MLB_F5_SIDE', 'MLB_F5_TOTAL'];
const LEGACY_MLB_BUNDLE_TYPES: ForecastAssetType[] = ['MLB_F5'];

function getRequiredMlbAssetTypes(event: EventRow): ForecastAssetType[] {
  if (!MLB_MARKETS_ENABLED || (event.league || '').toLowerCase() !== 'mlb') {
    return [];
  }

  const required: ForecastAssetType[] = [];
  if (event.total) required.push('GAME_TOTAL');
  if (event.spread) required.push('MLB_RUN_LINE');
  required.push(...LEGACY_MLB_BUNDLE_TYPES, ...MODELED_EXPLICIT_MLB_TYPES);
  return required;
}

async function fetchPrecomputeDbAnalytics(event: EventRow): Promise<any> {
  const predictions = await pool.query(
    `SELECT prediction_type, predicted_value, confidence, created_at
       FROM "PredictionHistory"
      WHERE league = $1
        AND (home_team ILIKE $2 OR away_team ILIKE $2 OR home_team ILIKE $3 OR away_team ILIKE $3)
      ORDER BY created_at DESC
      LIMIT 10`,
    [event.league, `%${event.home_team}%`, `%${event.away_team}%`],
  ).catch(() => ({ rows: [] as any[] }));

  const { sharpMoves, lineMovements } = await fetchInsightSourceData({
    homeTeam: event.home_team,
    awayTeam: event.away_team,
    league: event.league,
  });

  return {
    predictions: predictions.rows,
    sharpMoves,
    lineMovements,
  };
}

async function getForecastPickColumns(): Promise<Set<string>> {
  if (!forecastPickColumnsCache) {
    forecastPickColumnsCache = pool.query(
      `SELECT column_name
       FROM information_schema.columns
       WHERE table_schema = 'public'
         AND table_name = 'rm_forecast_picks'`,
    )
      .then((result) => new Set(result.rows.map((row: any) => String(row.column_name))))
      .catch(() => new Set<string>([
        'forecast_asset_id',
        'event_id',
        'pick_type',
        'selection',
        'line_value',
        'odds_snapshot',
        'confidence',
        'edge',
      ]));
  }

  return forecastPickColumnsCache;
}

export function buildForecastPickInsertSpec(
  columns: Set<string>,
  input: ForecastPickInsertInput,
): ForecastPickInsertSpec {
  const insertColumns = [
    'forecast_asset_id',
    'event_id',
    'pick_type',
    'selection',
    'line_value',
    'odds_snapshot',
    'confidence',
    'edge',
  ];
  const values: Array<string | number | null> = [
    input.assetId,
    input.eventId,
    'PLAYER_PROP',
    input.recommendation,
    input.line,
    input.odds,
    input.confidence,
    input.edge,
  ];

  const optionalFields: Array<{ column: string; value: string | number | null }> = [
    { column: 'grading_category', value: input.gradingCategory },
    { column: 'signal_tier', value: input.signalTier },
    { column: 'market_implied_probability', value: input.marketImpliedProbability },
    { column: 'projected_probability', value: input.projectedProbability },
    { column: 'projected_outcome', value: input.projectedOutcome },
  ];

  for (const field of optionalFields) {
    if (!columns.has(field.column)) continue;
    insertColumns.push(field.column);
    values.push(field.value);
  }

  return {
    sql: `INSERT INTO rm_forecast_picks
             (${insertColumns.join(', ')})
           VALUES (${values.map((_, index) => `$${index + 1}`).join(', ')})
           ON CONFLICT DO NOTHING`,
    values,
  };
}

function normalizeRosterName(name: string): string {
  return String(name || '')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z ]/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}

function normalizePropLookupToken(value: string | null | undefined): string {
  return String(value || '')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z0-9]/g, '');
}

function normalizeLineLookupValue(value: any): string {
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) return '';
  return String(Math.round(parsed * 1000) / 1000);
}

function normalizeMlbMarketLookupName(value: string | null | undefined): string {
  return String(value || '')
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .toLowerCase()
    .replace(/[^a-z0-9 ]/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}

function normalizeMlbMarketLookupLine(value: any): string {
  if (value == null || value === '') return '';
  const parsed = Number(value);
  return Number.isFinite(parsed) ? String(parsed) : '';
}

function normalizePropLookupDirection(value: string | null | undefined): 'over' | 'under' | null {
  const normalized = String(value || '').trim().toLowerCase();
  if (!normalized) return null;
  if (normalized === 'over' || normalized.startsWith('over') || normalized.endsWith('over') || normalized.includes('_over')) return 'over';
  if (normalized === 'under' || normalized.startsWith('under') || normalized.endsWith('under') || normalized.includes('_under')) return 'under';
  if (normalized.startsWith('o')) return 'over';
  if (normalized.startsWith('u')) return 'under';
  return null;
}

export function buildPlayerPropOddsLookupKey(
  player: string | null | undefined,
  statType: string | null | undefined,
  line: any,
  direction: string | null | undefined,
): string {
  const playerKey = normalizeRosterName(player || '');
  const statKey = normalizePropLookupToken(statType);
  const lineKey = normalizeLineLookupValue(line);
  const directionKey = normalizePropLookupDirection(direction);
  if (!playerKey || !statKey || !lineKey || !directionKey) return '';
  return `${playerKey}|${statKey}|${lineKey}|${directionKey}`;
}

function buildMlbPlayerPropOddsLookupKey(
  player: string | null | undefined,
  statType: string | null | undefined,
  line: any,
): string {
  const playerKey = normalizeMlbMarketLookupName(player);
  const statKey = String(normalizePlayerPropMarketStat('mlb', statType) || statType || '').toLowerCase();
  const lineKey = normalizeMlbMarketLookupLine(line);
  if (!playerKey || !statKey || !lineKey) return '';
  return `${playerKey}|${statKey}|${lineKey}`;
}

function parsePlayerNameFromExternalId(playerExternalId: string | null | undefined): string {
  const raw = String(playerExternalId || '').trim();
  if (!raw) return '';
  return raw
    .replace(/_\d+_[A-Z]+$/, '')
    .replace(/_/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()
    .toLowerCase()
    .replace(/\b\w/g, (char) => char.toUpperCase());
}

export async function fetchEventPlayerPropOddsLookup(event: EventRow): Promise<Map<string, { odds: number; source: string | null }>> {
  const homeShort = String(event.home_short || '').toUpperCase();
  const awayShort = String(event.away_short || '').toUpperCase();
  if (!homeShort || !awayShort) return new Map();

  const { rows } = await pool.query(
    `SELECT "playerExternalId", "propType", "lineValue", "oddsAmerican", market, vendor, raw
     FROM "PlayerPropLine"
     WHERE LOWER(league) = LOWER($1)
       AND (
         COALESCE("marketScope", '') IN ('full_game', 'game')
         OR LOWER(COALESCE(raw->>'period', raw #>> '{sportsgameodds,periodID}', '')) = 'game'
         OR LOWER(COALESCE(market, '')) LIKE 'game_%'
       )
       AND ("gameStart")::timestamptz BETWEEN ($2::timestamptz - INTERVAL '4 hours') AND ($2::timestamptz + INTERVAL '4 hours')
       AND COALESCE("homeTeam", '') IN ($3::text, $4::text)
       AND COALESCE("awayTeam", '') IN ($3::text, $4::text)
       AND "oddsAmerican" IS NOT NULL
       AND "lineValue" IS NOT NULL
     ORDER BY
       CASE LOWER(COALESCE(raw->>'bookmaker', raw #>> '{sportsgameodds,bookmaker}', vendor, ''))
         WHEN 'fanduel' THEN 1
         WHEN 'draftkings' THEN 2
         WHEN 'consensus' THEN 3
         WHEN 'bet365' THEN 4
         ELSE 9
       END,
       "updatedAt" DESC`,
    [event.league, event.starts_at, homeShort, awayShort],
  ).catch(() => ({ rows: [] as any[] }));

  const lookup = new Map<string, { odds: number; source: string | null }>();
  for (const row of rows) {
    const direction = normalizePropLookupDirection(row.raw?.side || row.market);
    const playerName = resolveCanonicalName(parsePlayerNameFromExternalId(row.playerExternalId), event.league);
    const key = buildPlayerPropOddsLookupKey(playerName, row.propType, row.lineValue, direction);
    const odds = Number(row.oddsAmerican);
    if (!key || !Number.isFinite(odds) || lookup.has(key)) continue;
    lookup.set(
      key,
      {
        odds,
        source: String(row.raw?.bookmaker || row.raw?.sportsgameodds?.bookmaker || row.vendor || '').toLowerCase() || null,
      },
    );
  }

  return lookup;
}

type MlbNormalizedPropOddsLookupValue = {
  over: number | null;
  under: number | null;
  primarySource: string | null;
  completenessStatus: 'source_complete' | 'multi_source_complete' | 'incomplete' | null;
};

function readNormalizedMarketOdds(value: any): number | null {
  if (!value || typeof value !== 'object') return null;
  const candidate = value.odds ?? value.oddsAmerican ?? null;
  const parsed = Number(candidate);
  return Number.isFinite(parsed) ? parsed : null;
}

async function fetchEventMlbNormalizedPropOddsLookup(event: EventRow): Promise<Map<string, MlbNormalizedPropOddsLookupValue>> {
  if ((event.league || '').toLowerCase() !== 'mlb') return new Map();

  const { rows } = await pool.query(
    `SELECT player_name, stat_type, line, over_payload, under_payload, primary_source, completeness_status
       FROM rm_mlb_normalized_player_prop_markets
      WHERE event_id = $1`,
    [event.event_id],
  ).catch(() => ({ rows: [] as any[] }));

  const lookup = new Map<string, MlbNormalizedPropOddsLookupValue>();
  for (const row of rows) {
    const key = buildMlbPlayerPropOddsLookupKey(row.player_name, row.stat_type, row.line);
    if (!key) continue;
    lookup.set(key, {
      over: readNormalizedMarketOdds(row.over_payload),
      under: readNormalizedMarketOdds(row.under_payload),
      primarySource: row.primary_source ?? null,
      completenessStatus: row.completeness_status ?? null,
    });
  }

  return lookup;
}

async function fetchEventMlbCandidateOddsLookup(params: {
  event: EventRow;
  side: 'home' | 'away';
}): Promise<Map<string, MlbNormalizedPropOddsLookupValue>> {
  if ((params.event.league || '').toLowerCase() !== 'mlb') return new Map();

  const teamShort = params.side === 'home' ? params.event.home_short : params.event.away_short;
  const opponentShort = params.side === 'home' ? params.event.away_short : params.event.home_short;
  const teamName = params.side === 'home' ? params.event.home_team : params.event.away_team;
  if (!teamShort || !opponentShort || !teamName) return new Map();

  const candidates = await fetchMlbPropCandidates({
    teamShort,
    teamName,
    opponentShort,
    startsAt: params.event.starts_at,
  }).catch(() => [] as any[]);

  const lookup = new Map<string, MlbNormalizedPropOddsLookupValue>();
  for (const candidate of candidates) {
    const key = buildMlbPlayerPropOddsLookupKey(
      candidate.player,
      candidate.normalizedStatType || candidate.statType,
      candidate.marketLineValue,
    );
    if (!key) continue;
    lookup.set(key, {
      over: candidate.overOdds ?? null,
      under: candidate.underOdds ?? null,
      primarySource: candidate.source ?? null,
      completenessStatus: candidate.completenessStatus ?? null,
    });
  }

  return lookup;
}

function findUniqueMlbPlayerLineLookup(
  lookup: Map<string, MlbNormalizedPropOddsLookupValue>,
  player: string | null | undefined,
  line: any,
): MlbNormalizedPropOddsLookupValue | null {
  const playerKey = normalizeMlbMarketLookupName(player);
  const lineKey = normalizeMlbMarketLookupLine(line);
  if (!playerKey || !lineKey) return null;

  const suffix = `|${lineKey}`;
  const prefix = `${playerKey}|`;
  let match: MlbNormalizedPropOddsLookupValue | null = null;
  for (const [key, value] of lookup.entries()) {
    if (!key.startsWith(prefix) || !key.endsWith(suffix)) continue;
    if (match) return null;
    match = value;
  }

  return match;
}

export function shouldPersistExtractedPlayerProp(params: {
  league: string;
  teamShort?: string | null;
  canonicalPlayer: string;
  odds?: number | null;
}): boolean {
  if (!hasPersistablePlayerPropPricing(params.odds)) return false;
  const teamShort = String(params.teamShort || '').toUpperCase();
  if (!teamShort) return true;

  const roster = getTeamRoster(teamShort, params.league);
  if (roster.length === 0) return true;

  const candidate = normalizeRosterName(params.canonicalPlayer);
  return roster.some((player) => normalizeRosterName(player) === candidate);
}

export function hasPersistablePlayerPropPricing(odds: number | null | undefined): boolean {
  return odds !== null && odds !== undefined && Number.isFinite(Number(odds));
}

export interface TeamPropsPersistenceDiagnostics {
  rawPropCount: number;
  filteredPropCount: number;
  publishablePropCount: number;
  playerPropsStored: number;
  supplementalPropCount: number;
  sourceBackedRescueUsed: boolean;
  sourceBackedRescueShortfall: number;
  droppedMissingPricing: number;
  droppedMissingMetadata: number;
  droppedNonRoster: number;
  storeFailures: number;
  candidateCount: number | null;
  publishableCandidateCount: number | null;
  feedRowCount: number | null;
  sourceBackedCandidateCount: number | null;
  sourceBackedSelectedCount: number | null;
  sourceBackedCandidateSource: string | null;
  sourceBackedSuppressionReasons: Record<string, number>;
  sourceBackedRejectedCount: number;
  sourceBackedRejectedReasons: Record<string, number>;
  sourceBackedRejectedPlayers: string[];
  droppedMetadataReasons: Record<string, number>;
  missingPricingPlayers: string[];
  missingMetadataPlayers: string[];
  nonRosterPlayers: string[];
  failedStorePlayers: string[];
}

function pushDiagnosticSample(target: string[], value: string | null | undefined): void {
  const normalized = String(value || '').trim();
  if (!normalized || target.includes(normalized) || target.length >= 5) return;
  target.push(normalized);
}

function coerceDiagnosticCount(value: any): number | null {
  if (value == null || value === '') return null;
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : null;
}

function recordDiagnosticReason(target: Record<string, number>, reason: string | null | undefined): void {
  const normalized = String(reason || '').trim().toLowerCase() || 'unknown';
  target[normalized] = (target[normalized] || 0) + 1;
}

function explainNonRenderableTeamPropEntry(params: {
  prop: any;
  league: string;
  teamName: string;
  teamShort?: string | null;
  teamSide: 'home' | 'away';
}): string {
  const { prop } = params;
  if (!prop.player || !String(prop.player).trim()) return 'missing_player';
  if (!prop.prop || !String(prop.prop).trim()) return 'missing_prop_label';
  if ((prop.projected_probability ?? prop.prob) == null) return 'missing_projected_probability';
  if ((prop.market_line_value ?? prop.line) == null) return 'missing_market_line';
  if (!prop.forecast_direction) {
    return explainPlayerPropMetadataRejection({
      player: prop.player,
      team: params.teamShort || params.teamName,
      teamSide: params.teamSide,
      league: params.league,
      prop: prop.prop,
      statType: prop.stat_type ?? null,
      normalizedStatType: prop.normalized_stat_type ?? prop.stat_type ?? null,
      marketLine: prop.market_line_value ?? prop.line ?? null,
      odds: prop.odds ?? null,
      projectedProbability: prop.projected_probability ?? prop.prob ?? null,
      projectedOutcome: prop.projected_stat_value ?? null,
      edgePct: prop.edge_pct ?? prop.edge ?? null,
      recommendation: prop.recommendation ?? null,
      playerRole: prop.player_role ?? getMlbPlayerRole(prop.stat_type ?? null),
      modelContext: prop.model_context ?? null,
      marketSource: prop.market_source ?? null,
      marketCompletenessStatus: prop.market_completeness_status ?? null,
      sourceBacked: true,
    }) || 'missing_forecast_direction';
  }
  if (prop.agreement_score == null) return 'missing_agreement_score';
  if (!prop.market_quality_label) return 'missing_market_quality_label';
  return 'non_renderable_unknown';
}

export function summarizeTeamPropsPersistenceDiagnostics(params: {
  league: string;
  teamName: string;
  teamShort?: string | null;
  teamSide: 'home' | 'away';
  propsResult: any;
  propOddsLookup?: Map<string, { odds: number; source: string | null }>;
  mlbOddsLookup?: Map<string, MlbNormalizedPropOddsLookupValue>;
  mlbCandidateOddsLookup?: Map<string, MlbNormalizedPropOddsLookupValue>;
}): {
  filteredProps: any[];
  diagnostics: TeamPropsPersistenceDiagnostics;
} {
  const normalizedPropsResult = normalizeTeamPropsResultForStorage(params.propsResult);
  const propOddsLookup = params.propOddsLookup ?? new Map<string, { odds: number; source: string | null }>();
  const mlbOddsLookup = params.mlbOddsLookup ?? new Map<string, MlbNormalizedPropOddsLookupValue>();
  const mlbCandidateOddsLookup = params.mlbCandidateOddsLookup ?? new Map<string, MlbNormalizedPropOddsLookupValue>();
  const sourceProps = Array.isArray(normalizedPropsResult.props) ? normalizedPropsResult.props.filter(Boolean) : [];
  const diagnostics: TeamPropsPersistenceDiagnostics = {
    rawPropCount: sourceProps.length,
    filteredPropCount: 0,
    publishablePropCount: 0,
    playerPropsStored: 0,
    supplementalPropCount: 0,
    sourceBackedRescueUsed: false,
    sourceBackedRescueShortfall: 0,
    droppedMissingPricing: 0,
    droppedMissingMetadata: 0,
    droppedNonRoster: 0,
    storeFailures: 0,
    candidateCount: coerceDiagnosticCount(normalizedPropsResult.metadata?.mlb_candidate_count ?? normalizedPropsResult.metadata?.candidate_count ?? null),
    publishableCandidateCount: coerceDiagnosticCount(normalizedPropsResult.metadata?.mlb_publishable_candidate_count ?? normalizedPropsResult.metadata?.publishable_candidate_count ?? null),
    feedRowCount: coerceDiagnosticCount(normalizedPropsResult.metadata?.mlb_feed_row_count ?? normalizedPropsResult.metadata?.feed_row_count ?? null),
    sourceBackedCandidateCount: null,
    sourceBackedSelectedCount: null,
    sourceBackedCandidateSource: null,
    sourceBackedSuppressionReasons: {},
    sourceBackedRejectedCount: 0,
    sourceBackedRejectedReasons: {},
    sourceBackedRejectedPlayers: [],
    droppedMetadataReasons: {},
    missingPricingPlayers: [],
    missingMetadataPlayers: [],
    nonRosterPlayers: [],
    failedStorePlayers: [],
  };
  const filteredProps: any[] = [];

  for (const prop of sourceProps) {
    const canonicalPlayer = resolveCanonicalName(prop.player, params.league);
    const marketLookup = propOddsLookup.get(
      buildPlayerPropOddsLookupKey(
        canonicalPlayer,
        prop.stat_type ?? null,
        prop.market_line_value ?? null,
        prop.recommendation ?? null,
      ),
    ) ?? null;
    const mlbMarketLookup = (params.league || '').toLowerCase() === 'mlb'
      ? mlbOddsLookup.get(buildMlbPlayerPropOddsLookupKey(
          canonicalPlayer,
          prop.stat_type ?? null,
          prop.market_line_value ?? null,
        )) ?? null
      : null;
    const mlbCandidateMarketLookup = (params.league || '').toLowerCase() === 'mlb'
      ? mlbCandidateOddsLookup.get(buildMlbPlayerPropOddsLookupKey(
          canonicalPlayer,
          prop.stat_type ?? null,
          prop.market_line_value ?? null,
        )) ?? null
      : null;
    const mlbCandidateLineLookup = (params.league || '').toLowerCase() === 'mlb' && mlbCandidateMarketLookup == null
      ? findUniqueMlbPlayerLineLookup(
          mlbCandidateOddsLookup,
          canonicalPlayer,
          prop.market_line_value ?? null,
        )
      : null;
    const recommendation = normalizePropLookupDirection(prop.recommendation ?? null);
    const resolvedOdds = prop.odds
      ?? marketLookup?.odds
      ?? (recommendation === 'over' ? mlbMarketLookup?.over : recommendation === 'under' ? mlbMarketLookup?.under : null)
      ?? (recommendation === 'over' ? mlbCandidateMarketLookup?.over : recommendation === 'under' ? mlbCandidateMarketLookup?.under : null)
      ?? (recommendation === 'over' ? mlbCandidateLineLookup?.over : recommendation === 'under' ? mlbCandidateLineLookup?.under : null)
      ?? null;
    const resolvedMarketLookup = resolvedOdds == null
      ? marketLookup
      : {
          odds: resolvedOdds,
          source: marketLookup?.source ?? mlbMarketLookup?.primarySource ?? mlbCandidateMarketLookup?.primarySource ?? mlbCandidateLineLookup?.primarySource ?? null,
        };

    if (!hasPersistablePlayerPropPricing(resolvedOdds)) {
      diagnostics.droppedMissingPricing++;
      pushDiagnosticSample(diagnostics.missingPricingPlayers, canonicalPlayer);
      continue;
    }

    if (!shouldPersistExtractedPlayerProp({
      league: params.league,
      teamShort: params.teamShort ?? null,
      canonicalPlayer,
      odds: resolvedOdds,
    })) {
      diagnostics.droppedNonRoster++;
      pushDiagnosticSample(diagnostics.nonRosterPlayers, canonicalPlayer);
      continue;
    }

    const entry = buildSourceBackedTeamPropBundleEntry({
      prop,
      league: params.league,
      teamName: params.teamName,
      teamShort: params.teamShort ?? null,
      teamSide: params.teamSide,
      marketLookup: resolvedMarketLookup,
    });

    if (!entry) {
      diagnostics.droppedNonRoster++;
      pushDiagnosticSample(diagnostics.nonRosterPlayers, canonicalPlayer);
      continue;
    }

    filteredProps.push(entry);
  }

  diagnostics.filteredPropCount = filteredProps.length;
  return { filteredProps, diagnostics };
}

function formatTeamPropsPipelineLog(params: {
  teamShort?: string | null;
  teamName: string;
  side: 'home' | 'away';
  diagnostics: TeamPropsPersistenceDiagnostics;
  reusedCache?: boolean;
}): string {
  const { diagnostics } = params;
  const samples = [
    diagnostics.missingPricingPlayers.length > 0
      ? `missing_pricing_players=${diagnostics.missingPricingPlayers.join(', ')}`
      : null,
    diagnostics.missingMetadataPlayers.length > 0
      ? `missing_metadata_players=${diagnostics.missingMetadataPlayers.join(', ')}`
      : null,
    diagnostics.nonRosterPlayers.length > 0
      ? `non_roster_players=${diagnostics.nonRosterPlayers.join(', ')}`
      : null,
    diagnostics.failedStorePlayers.length > 0
      ? `store_fail_players=${diagnostics.failedStorePlayers.join(', ')}`
      : null,
  ].filter(Boolean);

  return [
    `[PIPELINE-PROPS]`,
    `${params.side.toUpperCase()}`,
    `${params.teamShort || params.teamName}`,
    `raw=${diagnostics.rawPropCount}`,
    `filtered=${diagnostics.filteredPropCount}`,
    `publishable=${diagnostics.publishablePropCount}`,
    `stored=${diagnostics.playerPropsStored}`,
    diagnostics.sourceBackedRescueUsed ? `rescue_added=${diagnostics.supplementalPropCount}` : null,
    diagnostics.sourceBackedRescueUsed ? `rescue_shortfall=${diagnostics.sourceBackedRescueShortfall}` : null,
    `drop_no_pricing=${diagnostics.droppedMissingPricing}`,
    `drop_no_metadata=${diagnostics.droppedMissingMetadata}`,
    `drop_non_roster=${diagnostics.droppedNonRoster}`,
    `store_failures=${diagnostics.storeFailures}`,
    diagnostics.candidateCount != null ? `candidates=${diagnostics.candidateCount}` : null,
    diagnostics.publishableCandidateCount != null ? `publishable_candidates=${diagnostics.publishableCandidateCount}` : null,
    diagnostics.feedRowCount != null ? `feed_rows=${diagnostics.feedRowCount}` : null,
    diagnostics.sourceBackedCandidateCount != null ? `rescue_candidates=${diagnostics.sourceBackedCandidateCount}` : null,
    diagnostics.sourceBackedSelectedCount != null ? `rescue_selected=${diagnostics.sourceBackedSelectedCount}` : null,
    diagnostics.sourceBackedCandidateSource ? `rescue_source=${diagnostics.sourceBackedCandidateSource}` : null,
    diagnostics.sourceBackedRejectedCount > 0 ? `rescue_rejected=${diagnostics.sourceBackedRejectedCount}` : null,
    params.reusedCache ? `reused_cache=true` : null,
    Object.keys(diagnostics.sourceBackedSuppressionReasons || {}).length > 0
      ? `rescue_suppressed=${Object.entries(diagnostics.sourceBackedSuppressionReasons).map(([reason, count]) => `${reason}:${count}`).join(',')}`
      : null,
    Object.keys(diagnostics.sourceBackedRejectedReasons || {}).length > 0
      ? `rescue_reject_reasons=${Object.entries(diagnostics.sourceBackedRejectedReasons).map(([reason, count]) => `${reason}:${count}`).join(',')}`
      : null,
    diagnostics.sourceBackedRejectedPlayers.length > 0
      ? `rescue_reject_players=${diagnostics.sourceBackedRejectedPlayers.join(', ')}`
      : null,
    Object.keys(diagnostics.droppedMetadataReasons || {}).length > 0
      ? `drop_reasons=${Object.entries(diagnostics.droppedMetadataReasons).map(([reason, count]) => `${reason}:${count}`).join(',')}`
      : null,
    ...samples,
  ].filter(Boolean).join(' ');
}

function coercePropMetric(value: any): number | null {
  if (value == null || value === '') return null;
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : null;
}

export function buildSourceBackedTeamPropBundleEntry(params: {
  prop: any;
  league: string;
  teamName: string;
  teamShort?: string | null;
  teamSide: 'home' | 'away';
  marketLookup?: { odds: number; source: string | null } | null;
}): any | null {
  const { prop } = params;
  const playerRole = getMlbPlayerRole(prop.stat_type ?? null);
  const canonicalPlayer = resolveCanonicalName(prop.player, params.league);
  const resolvedOdds = prop.odds ?? params.marketLookup?.odds ?? null;

  if (!shouldPersistExtractedPlayerProp({
    league: params.league,
    teamShort: params.teamShort ?? null,
    canonicalPlayer,
    odds: resolvedOdds,
  })) {
    return null;
  }

  const metadata = buildPlayerPropMetadata({
    player: canonicalPlayer,
    team: params.teamShort || params.teamName,
    teamSide: params.teamSide,
    league: params.league,
    prop: prop.prop,
    statType: prop.stat_type ?? null,
    normalizedStatType: prop.stat_type ?? null,
    marketLine: prop.market_line_value ?? null,
    odds: resolvedOdds,
    projectedProbability: prop.prob ?? null,
    projectedOutcome: prop.projected_stat_value ?? null,
    edgePct: prop.edge_pct ?? prop.edge ?? null,
    recommendation: prop.recommendation ?? null,
    playerRole,
    modelContext: prop.model_context ?? null,
    marketSource: params.marketLookup?.source ?? null,
    sourceBacked: true,
  });

  return {
    ...prop,
    player: canonicalPlayer,
    odds: resolvedOdds,
    player_role: playerRole,
    signal_tier: metadata?.signalTier ?? null,
    signal_label: metadata?.signalLabel ?? null,
    forecast_direction: metadata?.forecastDirection ?? null,
    market_implied_probability: metadata?.marketImpliedProbability ?? americanOddsToImpliedProbability(resolvedOdds),
    projected_probability: metadata?.projectedProbability ?? coercePropMetric(prop.prob),
    edge_pct: metadata?.edgePct ?? coercePropMetric(prop.edge_pct ?? prop.edge),
    agreement_score: metadata?.agreementScore ?? null,
    agreement_label: metadata?.agreementLabel ?? null,
    agreement_sources: metadata?.agreementSources ?? [],
    market_source: params.marketLookup?.source ?? null,
    market_quality_score: metadata?.marketQualityScore ?? null,
    market_quality_label: metadata?.marketQualityLabel ?? null,
    source_backed: true,
    signal_table_row: metadata?.tableRow ?? null,
  };
}

function buildForecastPrecomputedConflictClause(forecastType: ForecastAssetType): string {
  if (forecastType === 'PLAYER_PROP') {
    return `ON CONFLICT (
      date_et,
      event_id,
      forecast_type,
      COALESCE(team_id, ''),
      COALESCE(player_name, ''),
      COALESCE((forecast_payload->>'stat_type'), (forecast_payload->>'normalized_stat_type'), ''),
      COALESCE((forecast_payload->>'market_line_value'), (forecast_payload->>'line'), '')
    )
    WHERE forecast_type = 'PLAYER_PROP'`;
  }

  return `ON CONFLICT (
    date_et,
    event_id,
    forecast_type,
    COALESCE(team_id, ''),
    COALESCE(player_name, '')
  )
  WHERE forecast_type <> 'PLAYER_PROP'`;
}

export function shouldGenerateMlbF5(event: EventRow, existingRows: Array<{ forecast_type: string }>): boolean {
  return MLB_MARKETS_ENABLED
    && (event.league || '').toLowerCase() === 'mlb'
    && !existingRows.some((row) => row.forecast_type === 'MLB_F5');
}

export function getExpectedAssetTypesForEvent(
  event: EventRow,
  existingRows: Array<{ forecast_type: string }>,
): ForecastAssetType[] {
  const existing = new Set(existingRows.map((row) => row.forecast_type));
  const expected: ForecastAssetType[] = [];

  if (!existing.has('GAME_MARKETS')) expected.push('GAME_MARKETS');
  if (!existing.has('TEAM_PROPS')) {
    // TEAM_PROPS is team-specific, so the caller must still check home/away separately.
  }

  for (const type of getRequiredMlbAssetTypes(event)) {
    if (!existing.has(type)) expected.push(type);
  }

  return expected;
}

export function hasRequiredAssetsForSkip(
  event: EventRow,
  existingRows: Array<{ forecast_type: string; team_id?: string | null }>,
): boolean {
  const isMlb = (event.league || '').toLowerCase() === 'mlb';
  const homeTeamId = event.home_short || event.home_team;
  const awayTeamId = event.away_short || event.away_team;
  const hasGameMarkets = existingRows.some(r => r.forecast_type === 'GAME_MARKETS');
  const hasGameTotal = existingRows.some(r => r.forecast_type === 'GAME_TOTAL');
  const hasRunLine = existingRows.some(r => r.forecast_type === 'MLB_RUN_LINE');
  const hasHomeProps = existingRows.some(r => r.forecast_type === 'TEAM_PROPS' && r.team_id === homeTeamId);
  const hasAwayProps = existingRows.some(r => r.forecast_type === 'TEAM_PROPS' && r.team_id === awayTeamId);
  const homePlayerPropsCount = existingRows.filter(r => r.forecast_type === 'PLAYER_PROP' && r.team_id === homeTeamId).length;
  const awayPlayerPropsCount = existingRows.filter(r => r.forecast_type === 'PLAYER_PROP' && r.team_id === awayTeamId).length;
  const playerPropsCount = homePlayerPropsCount + awayPlayerPropsCount;
  const hasMlbF5 = existingRows.some(r => r.forecast_type === 'MLB_F5');
  const hasMlbF5Side = existingRows.some(r => r.forecast_type === 'MLB_F5_SIDE');
  const hasMlbF5Total = existingRows.some(r => r.forecast_type === 'MLB_F5_TOTAL');
  const needsGameTotal = isMlb && MLB_MARKETS_ENABLED && !!event.total;
  const needsRunLine = isMlb && MLB_MARKETS_ENABLED && !!event.spread;
  const needsFirstFiveFamily = isMlb && MLB_MARKETS_ENABLED;

  return (
    hasGameMarkets
    && hasHomeProps
    && hasAwayProps
    && homePlayerPropsCount >= MIN_PLAYER_PROPS_PER_TEAM
    && awayPlayerPropsCount >= MIN_PLAYER_PROPS_PER_TEAM
    && playerPropsCount >= MIN_PLAYER_PROPS_PER_EVENT
    && (!needsGameTotal || hasGameTotal)
    && (!needsRunLine || hasRunLine)
    && (!needsFirstFiveFamily || (hasMlbF5 && hasMlbF5Side && hasMlbF5Total))
  );
}

export function canReuseTeamPropsAsset(params: {
  hasTeamProps: boolean;
  playerPropsCount: number;
}): boolean {
  if (!params.hasTeamProps) {
    return false;
  }
  if (params.playerPropsCount < MIN_PLAYER_PROPS_PER_TEAM) {
    return false;
  }
  return true;
}

async function countActivePlayerPropsForTeam(eventId: string, teamId: string | null | undefined): Promise<number> {
  if (!teamId) return 0;
  const { rows } = await pool.query(
    `SELECT COUNT(*)::int AS cnt
       FROM rm_forecast_precomputed
      WHERE event_id = $1
        AND forecast_type = 'PLAYER_PROP'
        AND team_id = $2
        AND status = 'ACTIVE'`,
    [eventId, teamId],
  );
  return Number(rows[0]?.cnt || 0);
}

function requiredForecastSlots(event: EventRow): number {
  return FORECASTS_PER_EVENT + getRequiredMlbAssetTypes(event).length;
}

async function getAssetUsageSummary(dateET: string): Promise<{
  total: number;
  byType: Record<string, number>;
  byFamily: Record<string, number>;
  byLeague: Record<string, number>;
}> {
  const { rows } = await pool.query(
    `SELECT forecast_type, league, COUNT(*)::int AS cnt
       FROM rm_forecast_precomputed
      WHERE date_et = $1
      GROUP BY forecast_type, league`,
    [dateET]
  );
  const byTypeRows = rows.map((row) => ({ forecast_type: row.forecast_type, count: Number(row.cnt || 0) }));
  const { byType, byFamily, total } = summarizeForecastCountRows(byTypeRows);
  const byLeague: Record<string, number> = {};
  for (const row of rows) {
    const league = String(row.league || 'unknown');
    byLeague[league] = (byLeague[league] || 0) + Number(row.cnt || 0);
  }
  return { total, byType, byFamily, byLeague };
}

async function collectMlbPhase(event: EventRow): Promise<{ phase: any; snapshot: any | null } | null> {
  if ((event.league || '').toLowerCase() !== 'mlb') return null;

  try {
    const phase = await mlbPhaseSignal.collect({
      league: 'mlb',
      homeTeam: event.home_team,
      awayTeam: event.away_team,
      homeShort: event.home_short,
      awayShort: event.away_short,
      startsAt: event.starts_at,
      eventId: event.event_id,
    });
    return {
      phase,
      snapshot: buildStoredMlbSnapshotFromPhase(phase),
    };
  } catch (err: any) {
    return {
      phase: null,
      snapshot: {
        captured_at: new Date().toISOString(),
        error: err.message || String(err),
      },
    };
  }
}

function buildTeamPropIdentityKeyFromStoredEntry(prop: any, league: string): string | null {
  const player = resolveCanonicalName(prop?.player, league);
  const statIdentity = resolvePlayerPropStatIdentity({
    league,
    statType: prop?.stat_type ?? null,
    normalizedStatType: prop?.normalized_stat_type ?? null,
    propText: prop?.prop ?? null,
  });
  const recommendation = normalizePropLookupDirection(prop?.recommendation ?? prop?.forecast_direction ?? null);
  const line = normalizeLineLookupValue(prop?.market_line_value ?? prop?.line ?? null);
  if (!player || !statIdentity.statType || !recommendation || !line) return null;
  return [
    normalizeRosterName(player),
    String(statIdentity.statType).toLowerCase(),
    line,
    recommendation,
  ].join('|');
}

async function buildSupplementalMlbPublishableProps(params: {
  event: EventRow;
  side: 'home' | 'away';
  phase: any | null;
}): Promise<{
  props: any[];
  candidateCount: number;
  selectedCount: number;
  candidateSource: string | null;
  suppressionReasons: Record<string, number>;
  rejectedCount: number;
  rejectedReasons: Record<string, number>;
  rejectedPlayers: string[];
}> {
  if ((params.event.league || '').toLowerCase() !== 'mlb') {
    return {
      props: [],
      candidateCount: 0,
      selectedCount: 0,
      candidateSource: null,
      suppressionReasons: {},
      rejectedCount: 0,
      rejectedReasons: {},
      rejectedPlayers: [],
    };
  }

  const teamShort = params.side === 'home' ? params.event.home_short : params.event.away_short;
  const opponentShort = params.side === 'home' ? params.event.away_short : params.event.home_short;
  const teamName = params.side === 'home' ? params.event.home_team : params.event.away_team;
  const opponentName = params.side === 'home' ? params.event.away_team : params.event.home_team;
  if (!teamShort || !opponentShort || !teamName || !opponentName) {
    return {
      props: [],
      candidateCount: 0,
      selectedCount: 0,
      candidateSource: null,
      suppressionReasons: {},
      rejectedCount: 0,
      rejectedReasons: {},
      rejectedPlayers: [],
    };
  }

  const localCandidates = await fetchMlbPropCandidates({
    teamShort,
    teamName,
    opponentShort,
    startsAt: params.event.starts_at,
  }).catch(() => []);

  const directCandidates = localCandidates.length === 0
    ? await fetchDirectMlbPropCandidates({
        teamShort,
        teamName,
        opponentShort,
        startsAt: params.event.starts_at,
      }).catch(() => [])
    : [];

  const sourceCandidates = (localCandidates.length > 0 ? localCandidates : directCandidates)
    .map((candidate) => ({
      player: resolveCanonicalName(candidate.player, 'mlb'),
      statType: candidate.normalizedStatType || candidate.statType,
      marketLineValue: Number(candidate.marketLineValue),
      source: 'player_prop_line' as const,
      propLabel: candidate.prop,
      overOdds: candidate.overOdds ?? null,
      underOdds: candidate.underOdds ?? null,
      marketSource: (Array.isArray(candidate.sourceMap) && candidate.sourceMap[0]?.source) || candidate.source || null,
      marketCompletenessStatus: candidate.completenessStatus ?? null,
      recommendationHint: null,
      publishScore: null,
      edge: null,
      prob: null,
      suppressionReason: null,
      reactivatedForFloor: null,
    }))
    .filter((candidate) => candidate.player && Number.isFinite(candidate.marketLineValue));

  if (sourceCandidates.length === 0 || !params.phase) {
    return {
      props: [],
      candidateCount: sourceCandidates.length,
      selectedCount: 0,
      candidateSource: localCandidates.length > 0 ? 'player_prop_line' : directCandidates.length > 0 ? 'theodds_live' : null,
      suppressionReasons: {},
      rejectedCount: 0,
      rejectedReasons: {},
      rejectedPlayers: [],
    };
  }

  const scoredCandidates = __mlbPropInternals.scoreMlbCandidatesForPublishing(
    sourceCandidates as any,
    params.phase,
    params.side,
  );
  const suppressionReasons = scoredCandidates.reduce((acc, candidate: any) => {
    const reason = String(candidate?.suppressionReason || '').trim().toLowerCase();
    if (!reason) return acc;
    acc[reason] = (acc[reason] || 0) + 1;
    return acc;
  }, {} as Record<string, number>);
  const selectedCandidates = __mlbPropInternals.selectMlbCandidatesForPublishing(
    scoredCandidates,
    MIN_PLAYER_PROPS_PER_TEAM,
  );
  if (selectedCandidates.length === 0) {
    return {
      props: [],
      candidateCount: sourceCandidates.length,
      selectedCount: 0,
      candidateSource: localCandidates.length > 0 ? 'player_prop_line' : directCandidates.length > 0 ? 'theodds_live' : null,
      suppressionReasons,
      rejectedCount: 0,
      rejectedReasons: {},
      rejectedPlayers: [],
    };
  }

  const fallbackProps = __mlbPropInternals.buildMlbFallbackProps(
    selectedCandidates,
    MIN_PLAYER_PROPS_PER_TEAM,
  );
  const enrichedProps = await __mlbPropInternals.enrichMlbPropsWithContext(
    fallbackProps,
    selectedCandidates,
    params.phase,
    {
      teamShort,
      opponentShort,
      homeShort: params.event.home_short,
      teamSide: params.side,
    },
  );
  const rejectedReasons: Record<string, number> = {};
  const rejectedPlayers: string[] = [];
  const props = enrichedProps
    .map((prop, index) => {
      const candidate = selectedCandidates[index] as any;
      const metadata = buildPlayerPropMetadata({
        player: prop.player,
        team: teamShort || teamName,
        teamSide: params.side,
        league: 'mlb',
        prop: prop.prop,
        statType: prop.stat_type ?? null,
        normalizedStatType: prop.stat_type ?? null,
        marketLine: prop.market_line_value ?? null,
        odds: prop.odds ?? null,
        projectedProbability: prop.prob ?? null,
        projectedOutcome: prop.projected_stat_value ?? null,
        edgePct: prop.edge ?? null,
        recommendation: prop.recommendation ?? null,
        playerRole: getMlbPlayerRole(prop.stat_type ?? null),
        modelContext: prop.model_context ?? null,
        marketSource: candidate?.marketSource ?? null,
        marketCompletenessStatus: candidate?.marketCompletenessStatus ?? null,
        sourceBacked: true,
      });
      if (!metadata) {
        const reason = explainPlayerPropMetadataRejection({
          player: prop.player,
          team: teamShort || teamName,
          teamSide: params.side,
          league: 'mlb',
          prop: prop.prop,
          statType: prop.stat_type ?? null,
          normalizedStatType: prop.stat_type ?? null,
          marketLine: prop.market_line_value ?? null,
          odds: prop.odds ?? null,
          projectedProbability: prop.prob ?? null,
          projectedOutcome: prop.projected_stat_value ?? null,
          edgePct: prop.edge ?? null,
          recommendation: prop.recommendation ?? null,
          playerRole: getMlbPlayerRole(prop.stat_type ?? null),
          modelContext: prop.model_context ?? null,
          marketSource: candidate?.marketSource ?? null,
          marketCompletenessStatus: candidate?.marketCompletenessStatus ?? null,
          sourceBacked: true,
        }) || 'unknown';
        rejectedReasons[reason] = (rejectedReasons[reason] || 0) + 1;
        pushDiagnosticSample(rejectedPlayers, prop.player);
        return null;
      }

      return {
        ...prop,
        player_role: getMlbPlayerRole(prop.stat_type ?? null),
        signal_tier: metadata.signalTier,
        signal_label: metadata.signalLabel,
        forecast_direction: metadata.forecastDirection,
        market_implied_probability: metadata.marketImpliedProbability,
        projected_probability: metadata.projectedProbability,
        edge_pct: metadata.edgePct,
        agreement_score: metadata.agreementScore,
        agreement_label: metadata.agreementLabel,
        agreement_sources: metadata.agreementSources,
        market_source: candidate?.marketSource ?? null,
        market_quality_score: metadata.marketQualityScore,
        market_quality_label: metadata.marketQualityLabel,
        market_completeness_status: candidate?.marketCompletenessStatus ?? null,
        source_backed: true,
        signal_table_row: metadata.tableRow,
      };
    })
    .filter(Boolean);
  return {
    props,
    candidateCount: sourceCandidates.length,
    selectedCount: selectedCandidates.length,
    candidateSource: localCandidates.length > 0 ? 'player_prop_line' : directCandidates.length > 0 ? 'theodds_live' : null,
    suppressionReasons,
    rejectedCount: Object.values(rejectedReasons).reduce((sum, count) => sum + Number(count || 0), 0),
    rejectedReasons,
    rejectedPlayers,
  };
}

function recordSupplementalPropRejection(
  diagnostics: TeamPropsPersistenceDiagnostics,
  playerName: string | null | undefined,
  reason: string | null | undefined,
): void {
  const normalizedReason = String(reason || '').trim().toLowerCase() || 'unknown';
  diagnostics.sourceBackedRejectedCount += 1;
  diagnostics.sourceBackedRejectedReasons[normalizedReason] = (diagnostics.sourceBackedRejectedReasons[normalizedReason] || 0) + 1;
  pushDiagnosticSample(diagnostics.sourceBackedRejectedPlayers, playerName);
}

export async function recoverStaleRuns(dateET: string): Promise<number> {
  const recoveredReasons = {
    superseded: 'Recovered stale RUNNING row: superseded by a newer completed run',
    timedOut: `Recovered stale RUNNING row: older than ${STALE_RUN_MAX_AGE_MINUTES} minutes`,
  };

  const superseded = await pool.query(
    `UPDATE rm_weather_report_runs AS stale
        SET status = 'FAILED',
            error_message = COALESCE(NULLIF(stale.error_message, ''), $2),
            duration_ms = COALESCE(
              stale.duration_ms,
              GREATEST(0, FLOOR(EXTRACT(EPOCH FROM (newer.created_at - stale.created_at)) * 1000))::int
            )
       FROM rm_weather_report_runs AS newer
      WHERE stale.date_et = $1
        AND stale.status = 'RUNNING'
        AND newer.date_et = stale.date_et
        AND newer.schedule_name = stale.schedule_name
        AND newer.created_at > stale.created_at
        AND newer.status IN ('SUCCESS', 'PARTIAL', 'FAILED')
      RETURNING stale.id`,
    [dateET, recoveredReasons.superseded],
  );

  const timedOut = await pool.query(
    `UPDATE rm_weather_report_runs
        SET status = 'FAILED',
            error_message = COALESCE(NULLIF(error_message, ''), $2),
            duration_ms = COALESCE(
              duration_ms,
              GREATEST(0, FLOOR(EXTRACT(EPOCH FROM (NOW() - created_at)) * 1000))::int
            )
      WHERE date_et = $1
        AND status = 'RUNNING'
        AND created_at < NOW() - ($3::int * INTERVAL '1 minute')
      RETURNING id`,
    [dateET, recoveredReasons.timedOut, STALE_RUN_MAX_AGE_MINUTES],
  );

  return Number(superseded.rowCount || 0) + Number(timedOut.rowCount || 0);
}

/**
 * Create a run record and return its ID
 */
async function createRun(dateET: string): Promise<string> {
  await recoverStaleRuns(dateET);
  const { rows } = await pool.query(
    `INSERT INTO rm_weather_report_runs (date_et, schedule_name, status)
     VALUES ($1, $2, 'RUNNING') RETURNING id`,
    [dateET, scheduleName]
  );
  return rows[0].id;
}

/**
 * Update the run record with final results
 */
async function updateRun(runId: string, data: {
  total_contests_found: number;
  total_forecasts_generated: number;
  total_skipped_due_to_cap: number;
  total_failures: number;
  total_odds_refreshed: number;
  duration_ms: number;
  status: string;
  error_message?: string;
  log_blob?: string;
}) {
  await pool.query(
    `UPDATE rm_weather_report_runs SET
       total_contests_found = $2,
       total_forecasts_generated = $3,
       total_skipped_due_to_cap = $4,
       total_failures = $5,
       total_odds_refreshed = $6,
       duration_ms = $7,
       status = $8,
       error_message = $9,
       log_blob = $10
     WHERE id = $1`,
    [runId, data.total_contests_found, data.total_forecasts_generated,
     data.total_skipped_due_to_cap, data.total_failures, data.total_odds_refreshed,
     data.duration_ms, data.status, data.error_message || null, data.log_blob || null]
  );
}

/**
 * Store a precomputed forecast
 */
async function storePrecomputed(data: {
  dateET: string;
  league: string;
  eventId: string;
  forecastType: ForecastAssetType;
  teamId: string | null;
  teamSide: string | null;
  playerName: string | null;
  modelVersion: string;
  vendorInputs: any;
  payload: any;
  confidence: number | null;
  expiresAt: string;
  runId: string | null;
}) {
  const conflictClause = buildForecastPrecomputedConflictClause(data.forecastType);
  const { rows } = await pool.query(
    `INSERT INTO rm_forecast_precomputed
       (date_et, league, event_id, forecast_type, team_id, team_side, player_name, model_id, model_version,
        vendor_inputs_summary, forecast_payload, confidence_score, expires_at, status, run_id)
     VALUES ($1,$2,$3,$4,$5,$6,$7,'grok-claw',$8,$9,$10,$11,$12,'ACTIVE',$13)
     ${conflictClause}
     DO UPDATE SET
       forecast_payload = EXCLUDED.forecast_payload,
       confidence_score = EXCLUDED.confidence_score,
       model_version = EXCLUDED.model_version,
       vendor_inputs_summary = EXCLUDED.vendor_inputs_summary,
       expires_at = EXCLUDED.expires_at,
       status = 'ACTIVE',
       run_id = EXCLUDED.run_id,
       generated_at = NOW()
     RETURNING id`,
    [data.dateET, data.league, data.eventId, data.forecastType,
     data.teamId, data.teamSide, data.playerName, data.modelVersion,
     data.vendorInputs ? JSON.stringify(data.vendorInputs) : null,
     JSON.stringify(data.payload), data.confidence, data.expiresAt, data.runId]
  );

  const assetId = rows[0]?.id;

  // Log PLAYER_PROP assets to generation log + forecast picks
  if (data.forecastType === 'PLAYER_PROP' && assetId) {
    try {
      await pool.query(
        `INSERT INTO rm_forecast_generation_log
           (forecast_asset_id, run_id, event_id, league, forecast_type, player_name, team_side, model_version, confidence_score)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [assetId, data.runId, data.eventId, data.league, data.forecastType,
         data.playerName, data.teamSide, data.modelVersion, data.confidence]
      );

      // Auto-populate forecast picks with the asset's recommendation
      const recommendation = data.payload?.recommendation;
      const line = data.payload?.line;
      const odds = data.payload?.odds;
      const edge = data.payload?.edge_pct ?? data.payload?.edge;
      const marketImpliedProbability = data.payload?.market_implied_probability ?? null;
      const projectedProbability = data.payload?.projected_probability ?? data.payload?.prob ?? null;
      const projectedOutcome = data.payload?.projected_stat_value ?? null;
      const signalTier = data.payload?.signal_tier ?? null;
      const gradingCategory = data.payload?.grading_category ?? 'PLAYER_PROPS';
      if (recommendation) {
        const pickColumns = await getForecastPickColumns();
        const pickInsert = buildForecastPickInsertSpec(pickColumns, {
          assetId,
          eventId: data.eventId,
          recommendation,
          line: line || null,
          odds: odds || null,
          confidence: data.confidence,
          edge: edge || null,
          gradingCategory,
          signalTier,
          marketImpliedProbability,
          projectedProbability,
          projectedOutcome,
        });
        await pool.query(pickInsert.sql, pickInsert.values);
      }
    } catch (logErr) {
      // Non-fatal — generation logging is supplementary
      console.error('Generation log/pick recording error:', logErr);
    }
  }
}

function buildAssetMetadata(forecastType: ForecastAssetType, payload: any = {}): Record<string, any> {
  const descriptor = describeForecastAsset(forecastType, payload);
  return {
    marketType: descriptor.assetType,
    marketFamily: descriptor.marketFamily,
    marketOrigin: descriptor.marketOrigin,
    sourceBacked: descriptor.sourceBacked,
    legacyBundle: descriptor.legacyBundle,
    ...(descriptor.playerRole ? { playerRole: descriptor.playerRole } : {}),
  };
}

function buildGameTotalPayload(event: EventRow, forecast: ForecastResult, mlbSnapshot: any | null) {
  return {
    ...buildAssetMetadata('GAME_TOTAL'),
    summary: forecast.total_analysis || forecast.summary,
    recommendation: forecast.total_direction || null,
    reasoning: forecast.total_analysis || null,
    edge: forecast.total_edge ?? null,
    projected_total_points: forecast.projected_total_points ?? forecast.projected_lines?.total ?? null,
    market_line_value: event.total?.over?.line ?? event.total?.under?.line ?? null,
    over_odds: event.total?.over?.odds ?? null,
    under_odds: event.total?.under?.odds ?? null,
    mlb_phase_context: mlbSnapshot,
  };
}

function buildRunLinePayload(event: EventRow, forecast: ForecastResult, mlbSnapshot: any | null) {
  return {
    ...buildAssetMetadata('MLB_RUN_LINE'),
    summary: forecast.spread_analysis || forecast.summary,
    recommendation: forecast.forecast_side || forecast.winner_pick || null,
    reasoning: forecast.spread_analysis || null,
    edge: forecast.spread_edge ?? null,
    projected_margin: forecast.projected_margin ?? null,
    home_line: event.spread?.home?.line ?? null,
    away_line: event.spread?.away?.line ?? null,
    home_odds: event.spread?.home?.odds ?? null,
    away_odds: event.spread?.away?.odds ?? null,
    mlb_phase_context: mlbSnapshot,
  };
}

function buildLegacyMlbFirstFivePayload(forecast: ForecastResult, mlbSnapshot: any | null) {
  return {
    ...buildAssetMetadata('MLB_F5'),
    summary: forecast.summary,
    winner_pick: forecast.winner_pick,
    confidence: forecast.confidence,
    value_rating: forecast.value_rating,
    key_factors: forecast.key_factors,
    projected_lines: forecast.projected_lines,
    mlb_phase_context: mlbSnapshot,
  };
}

function buildMlbFirstFiveSidePayload(forecast: ForecastResult, mlbSnapshot: any | null) {
  return {
    ...buildAssetMetadata('MLB_F5_SIDE'),
    summary: forecast.summary,
    recommendation: forecast.winner_pick || forecast.forecast_side || null,
    reasoning: forecast.summary || null,
    confidence: forecast.confidence,
    projected_margin: forecast.projected_margin ?? null,
    mlb_phase_context: mlbSnapshot,
  };
}

function buildMlbFirstFiveTotalPayload(forecast: ForecastResult, mlbSnapshot: any | null) {
  return {
    ...buildAssetMetadata('MLB_F5_TOTAL'),
    summary: forecast.total_analysis || forecast.summary,
    recommendation: forecast.total_direction || null,
    reasoning: forecast.total_analysis || forecast.summary || null,
    edge: forecast.total_edge ?? null,
    projected_total_points: forecast.projected_total_points ?? forecast.projected_lines?.total ?? null,
    mlb_phase_context: mlbSnapshot,
  };
}

export function getMissingMlbSourceBackedAssetTypes(
  event: EventRow,
  existingRows: Array<{ forecast_type: string }>,
): ForecastAssetType[] {
  if (!MLB_MARKETS_ENABLED || (event.league || '').toLowerCase() !== 'mlb') {
    return [];
  }

  const existing = new Set(existingRows.map((row) => row.forecast_type));
  const missing: ForecastAssetType[] = [];

  if (event.total && !existing.has('GAME_TOTAL')) {
    missing.push('GAME_TOTAL');
  }
  if (event.spread && !existing.has('MLB_RUN_LINE')) {
    missing.push('MLB_RUN_LINE');
  }

  return missing;
}

async function storeMlbSourceBackedMarkets(params: {
  event: EventRow;
  dateET: string;
  runId: string;
  forecast: ForecastResult;
  mlbSnapshot: any | null;
  includeTypes?: ForecastAssetType[];
}): Promise<number> {
  if (!MLB_MARKETS_ENABLED || (params.event.league || '').toLowerCase() !== 'mlb') {
    return 0;
  }

  const include = new Set(params.includeTypes || ['GAME_TOTAL', 'MLB_RUN_LINE']);
  let writes = 0;

  if (include.has('GAME_TOTAL') && params.event.total) {
    await storePrecomputed({
      dateET: params.dateET,
      league: params.event.league,
      eventId: params.event.event_id,
      forecastType: 'GAME_TOTAL',
      teamId: null,
      teamSide: null,
      playerName: null,
      modelVersion: process.env.GROK_MODEL || 'grok-4-1-fast-reasoning',
      vendorInputs: {
        odds_source: 'rm_events',
        market_origin: 'source_backed',
        mlb_snapshot: params.mlbSnapshot,
      },
      payload: buildGameTotalPayload(params.event, params.forecast, params.mlbSnapshot),
      confidence: params.forecast.confidence,
      expiresAt: params.event.starts_at,
      runId: params.runId,
    });
    writes++;
  }

  if (include.has('MLB_RUN_LINE') && params.event.spread) {
    await storePrecomputed({
      dateET: params.dateET,
      league: params.event.league,
      eventId: params.event.event_id,
      forecastType: 'MLB_RUN_LINE',
      teamId: null,
      teamSide: null,
      playerName: null,
      modelVersion: process.env.GROK_MODEL || 'grok-4-1-fast-reasoning',
      vendorInputs: {
        odds_source: 'rm_events',
        market_origin: 'source_backed',
        mlb_snapshot: params.mlbSnapshot,
      },
      payload: buildRunLinePayload(params.event, params.forecast, params.mlbSnapshot),
      confidence: params.forecast.confidence,
      expiresAt: params.event.starts_at,
      runId: params.runId,
    });
    writes++;
  }

  return writes;
}

async function generateMlbFirstFiveMarket(
  event: EventRow,
  dateET: string,
  runId: string,
  mlbSnapshot: any | null = null,
): Promise<number> {
  if (!MLB_MARKETS_ENABLED || (event.league || '').toLowerCase() !== 'mlb') {
    return 0;
  }

  const modelVersion = process.env.GROK_MODEL || 'grok-4-1-fast-reasoning';
  const dbAnalytics = await fetchPrecomputeDbAnalytics(event);
  const forecast: ForecastResult = await generateForecast({
    homeTeam: event.home_team,
    awayTeam: event.away_team,
    homeShort: event.home_short,
    awayShort: event.away_short,
    league: event.league,
    startsAt: event.starts_at,
    moneyline: event.moneyline || { home: null, away: null },
    spread: event.spread || { home: null, away: null },
    total: event.total || { over: null, under: null },
    dbAnalytics,
    eventId: event.event_id,
  });

  let writes = 0;

  await storePrecomputed({
    dateET,
    league: event.league,
    eventId: event.event_id,
    forecastType: 'MLB_F5',
    teamId: null,
    teamSide: null,
    playerName: null,
    modelVersion,
    vendorInputs: {
      odds_source: event.moneyline ? 'rm_events' : 'none',
      mlb_snapshot: mlbSnapshot,
      market_scope: 'FIRST_FIVE',
      market_origin: 'modeled',
    },
    payload: buildLegacyMlbFirstFivePayload(forecast, mlbSnapshot),
    confidence: forecast.confidence,
    expiresAt: event.starts_at,
    runId,
  });
  writes++;

  await storePrecomputed({
    dateET,
    league: event.league,
    eventId: event.event_id,
    forecastType: 'MLB_F5_SIDE',
    teamId: null,
    teamSide: null,
    playerName: null,
    modelVersion,
    vendorInputs: {
      odds_source: 'modeled',
      mlb_snapshot: mlbSnapshot,
      market_scope: 'FIRST_FIVE',
      market_origin: 'modeled',
    },
    payload: buildMlbFirstFiveSidePayload(forecast, mlbSnapshot),
    confidence: forecast.confidence,
    expiresAt: event.starts_at,
    runId,
  });
  writes++;

  await storePrecomputed({
    dateET,
    league: event.league,
    eventId: event.event_id,
    forecastType: 'MLB_F5_TOTAL',
    teamId: null,
    teamSide: null,
    playerName: null,
    modelVersion,
    vendorInputs: {
      odds_source: 'modeled',
      mlb_snapshot: mlbSnapshot,
      market_scope: 'FIRST_FIVE',
      market_origin: 'modeled',
    },
    payload: buildMlbFirstFiveTotalPayload(forecast, mlbSnapshot),
    confidence: forecast.confidence,
    expiresAt: event.starts_at,
    runId,
  });
  writes++;

  return writes;
}

/**
 * Generate GAME_MARKETS forecast for an event
 */
async function generateGameMarkets(event: EventRow, dateET: string, runId: string, mlbSnapshot: any | null = null): Promise<number> {
  const modelVersion = process.env.GROK_MODEL || 'grok-4-1-fast-reasoning';
  const result = await buildForecastFromCuratedEvent(event, { ignoreCache: true });
  const forecast = result.forecast;
  const confidence = result.confidenceScore;

  // Build GAME_MARKETS payload with spread + total analysis combined
  const gameMarketsPayload = {
    ...buildAssetMetadata('GAME_MARKETS'),
    summary: forecast.summary,
    winner_pick: forecast.winner_pick,
    confidence,
    spread_analysis: forecast.spread_analysis,
    total_analysis: forecast.total_analysis,
    key_factors: forecast.key_factors,
    sharp_money_indicator: forecast.sharp_money_indicator,
    line_movement_analysis: forecast.line_movement_analysis,
    injury_notes: forecast.injury_notes,
    historical_trend: forecast.historical_trend,
    weather_impact: forecast.weather_impact,
    value_rating: forecast.value_rating,
    projected_lines: forecast.projected_lines,
    prop_highlights: forecast.prop_highlights,
  };

  // Store in precomputed table
  await storePrecomputed({
    dateET,
    league: event.league,
    eventId: event.event_id,
    forecastType: 'GAME_MARKETS',
    teamId: null,
    teamSide: null,
    playerName: null,
    modelVersion,
    vendorInputs: {
      odds_source: event.moneyline ? 'rm_events' : 'none',
      piff_available: true,
      mlb_snapshot: mlbSnapshot,
      market_origin: 'bundle',
    },
    payload: gameMarketsPayload,
    confidence,
    expiresAt: event.starts_at,
    runId,
  });

  let writes = 1;
  writes += await storeMlbSourceBackedMarkets({
    event,
    dateET,
    runId,
    forecast,
    mlbSnapshot,
  });

  return writes;
}

function normalizeTeamPropsResultForStorage(propsResult: any): any {
  if (!propsResult || typeof propsResult !== 'object') {
    return { props: [] };
  }
  if (Array.isArray(propsResult.props)) {
    return propsResult;
  }
  if (Array.isArray(propsResult.recommendations)) {
    return {
      ...propsResult,
      props: propsResult.recommendations,
    };
  }
  return {
    ...propsResult,
    props: [],
  };
}

export async function buildAssetBackedPropHighlights(eventId: string): Promise<any[]> {
  const { rows: playerPropRows } = await pool.query(
    `SELECT player_name, forecast_payload, confidence_score
     FROM rm_forecast_precomputed
     WHERE event_id = $1
       AND forecast_type = 'PLAYER_PROP'
       AND status = 'ACTIVE'
     ORDER BY confidence_score DESC NULLS LAST, generated_at DESC NULLS LAST
     LIMIT 6`,
    [eventId],
  ).catch(() => ({ rows: [] as any[] }));

  if (playerPropRows.length > 0) {
    return playerPropRows.map((row: any) => ({
      player: row.player_name || row.forecast_payload?.player || null,
      prop: row.forecast_payload?.prop || null,
      recommendation: row.forecast_payload?.recommendation || null,
      reasoning: row.forecast_payload?.reasoning || null,
    }));
  }

  const { rows: teamPropRows } = await pool.query(
    `SELECT forecast_payload
     FROM rm_forecast_precomputed
     WHERE event_id = $1
       AND forecast_type = 'TEAM_PROPS'
       AND status = 'ACTIVE'
     ORDER BY generated_at DESC NULLS LAST
     LIMIT 2`,
    [eventId],
  ).catch(() => ({ rows: [] as any[] }));

  return teamPropRows
    .flatMap((row: any) => (
      Array.isArray(row.forecast_payload?.props)
        ? row.forecast_payload.props.filter((prop: any) => shouldRenderTeamPropBundleEntry(prop))
        : []
    ))
    .slice(0, 6)
    .map((prop: any) => ({
      player: prop.player || null,
      prop: prop.prop || null,
      recommendation: prop.recommendation || null,
      reasoning: prop.reasoning || null,
    }));
}

export async function hydrateStoredPropHighlights(eventId: string): Promise<void> {
  const propHighlights = await buildAssetBackedPropHighlights(eventId);
  if (propHighlights.length === 0) return;

  const propHighlightsJson = JSON.stringify(propHighlights);

  await pool.query(
    `UPDATE rm_forecast_cache
     SET forecast_data = jsonb_set(COALESCE(forecast_data, '{}'::jsonb), '{prop_highlights}', $1::jsonb, true)
     WHERE event_id = $2
       AND COALESCE(jsonb_array_length(COALESCE(forecast_data->'prop_highlights', '[]'::jsonb)), 0) = 0`,
    [propHighlightsJson, eventId],
  ).catch(() => ({ rowCount: 0 }));

  await pool.query(
    `UPDATE rm_forecast_precomputed
     SET forecast_payload = jsonb_set(COALESCE(forecast_payload, '{}'::jsonb), '{prop_highlights}', $1::jsonb, true),
         generated_at = NOW()
     WHERE event_id = $2
       AND forecast_type = 'GAME_MARKETS'
       AND status = 'ACTIVE'
       AND COALESCE(jsonb_array_length(COALESCE(forecast_payload->'prop_highlights', '[]'::jsonb)), 0) = 0`,
    [propHighlightsJson, eventId],
  ).catch(() => ({ rowCount: 0 }));
}

async function persistTeamPropsBundleAssets(params: {
  event: EventRow;
  side: 'home' | 'away';
  dateET: string;
  runId: string | null;
  mlbSnapshot?: any | null;
  mlbPhase?: any | null;
  propsResult: any;
  countTeamBundleWrite?: boolean;
}): Promise<{ success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics }> {
  const modelVersion = process.env.GROK_MODEL || 'grok-4-1-fast-reasoning';
  const teamName = params.side === 'home' ? params.event.home_team : params.event.away_team;
  const teamShort = params.side === 'home' ? params.event.home_short : params.event.away_short;
  const normalizedPropsResult = normalizeTeamPropsResultForStorage(params.propsResult);
  const [propOddsLookup, mlbOddsLookup, mlbCandidateOddsLookup] = await Promise.all([
    fetchEventPlayerPropOddsLookup(params.event),
    fetchEventMlbNormalizedPropOddsLookup(params.event),
    fetchEventMlbCandidateOddsLookup({ event: params.event, side: params.side }),
  ]);
  const { filteredProps, diagnostics } = summarizeTeamPropsPersistenceDiagnostics({
    league: params.event.league,
    teamName,
    teamShort,
    teamSide: params.side,
    propsResult: normalizedPropsResult,
    propOddsLookup,
    mlbOddsLookup,
    mlbCandidateOddsLookup,
  });
  const publishableProps = filteredProps.filter((prop) => {
    const canonicalPlayer = resolveCanonicalName(prop.player, params.event.league);
    const renderable = shouldRenderTeamPropBundleEntry(prop);
    if (!renderable) {
      diagnostics.droppedMissingMetadata++;
      pushDiagnosticSample(diagnostics.missingMetadataPlayers, canonicalPlayer);
      recordDiagnosticReason(diagnostics.droppedMetadataReasons, explainNonRenderableTeamPropEntry({
        prop,
        league: params.event.league,
        teamName,
        teamShort,
        teamSide: params.side,
      }));
    }
    return renderable;
  });
  const publishablePropsByKey = new Map<string, any>();
  for (const prop of publishableProps) {
    const key = buildTeamPropIdentityKeyFromStoredEntry(prop, params.event.league);
    if (!key || publishablePropsByKey.has(key)) continue;
    publishablePropsByKey.set(key, prop);
  }
  if ((params.event.league || '').toLowerCase() === 'mlb' && publishablePropsByKey.size < MIN_PLAYER_PROPS_PER_TEAM) {
    const publishableCountBeforeRescue = publishablePropsByKey.size;
    const supplemental = await buildSupplementalMlbPublishableProps({
      event: params.event,
      side: params.side,
      phase: params.mlbPhase ?? null,
    });
    diagnostics.sourceBackedCandidateCount = supplemental.candidateCount;
    diagnostics.sourceBackedSelectedCount = supplemental.selectedCount;
    diagnostics.sourceBackedCandidateSource = supplemental.candidateSource;
    diagnostics.sourceBackedSuppressionReasons = supplemental.suppressionReasons;
    diagnostics.sourceBackedRejectedCount = supplemental.rejectedCount;
    diagnostics.sourceBackedRejectedReasons = supplemental.rejectedReasons;
    diagnostics.sourceBackedRejectedPlayers = supplemental.rejectedPlayers;
    for (const prop of supplemental.props) {
      if (!shouldRenderTeamPropBundleEntry(prop)) {
        let reason: string | null = null;
        if (!prop.player || !String(prop.player).trim()) reason = 'missing_player';
        else if (!prop.prop || !String(prop.prop).trim()) reason = 'missing_prop_label';
        else if ((prop.projected_probability ?? prop.prob) == null) reason = 'missing_projected_probability';
        else if ((prop.market_line_value ?? prop.line) == null) reason = 'missing_market_line';
        else if (!prop.forecast_direction) reason = 'missing_forecast_direction';
        else if (prop.agreement_score == null) {
          reason = explainPlayerPropMetadataRejection({
            player: prop.player,
            team: teamShort || teamName,
            teamSide: params.side,
            league: params.event.league,
            prop: prop.prop,
            statType: prop.stat_type ?? null,
            normalizedStatType: prop.normalized_stat_type ?? prop.stat_type ?? null,
            marketLine: prop.market_line_value ?? prop.line ?? null,
            odds: prop.odds ?? null,
            projectedProbability: prop.projected_probability ?? prop.prob ?? null,
            projectedOutcome: prop.projected_stat_value ?? null,
            edgePct: prop.edge_pct ?? prop.edge ?? null,
            recommendation: prop.recommendation ?? prop.forecast_direction ?? null,
            playerRole: prop.player_role ?? getMlbPlayerRole(prop.stat_type ?? null),
            modelContext: prop.model_context ?? null,
            marketSource: prop.market_source ?? null,
            marketCompletenessStatus: prop.market_completeness_status ?? null,
            sourceBacked: true,
          }) || 'missing_agreement_score';
        } else if (!prop.market_quality_label) {
          reason = 'missing_market_quality_label';
        } else {
          reason = 'non_renderable_unknown';
        }
        recordSupplementalPropRejection(diagnostics, prop.player, reason);
        continue;
      }
      const key = buildTeamPropIdentityKeyFromStoredEntry(prop, params.event.league);
      if (!key || publishablePropsByKey.has(key)) continue;
      publishablePropsByKey.set(key, prop);
      if (publishablePropsByKey.size >= MIN_PLAYER_PROPS_PER_TEAM) break;
    }
    diagnostics.supplementalPropCount = Math.max(0, publishablePropsByKey.size - publishableCountBeforeRescue);
    diagnostics.sourceBackedRescueUsed = diagnostics.supplementalPropCount > 0;
    diagnostics.sourceBackedRescueShortfall = Math.max(0, MIN_PLAYER_PROPS_PER_TEAM - publishablePropsByKey.size);
  }
  const finalPublishableProps = Array.from(publishablePropsByKey.values());
  diagnostics.publishablePropCount = finalPublishableProps.length;

  let playerPropsExtracted = 0;
  if (finalPublishableProps.length > 0) {
    for (const prop of finalPublishableProps) {
      if (!prop.player || !prop.prop) continue;
      const canonicalPlayer = resolveCanonicalName(prop.player, params.event.league);
      try {
        const statIdentity = resolvePlayerPropStatIdentity({
          league: params.event.league,
          statType: prop.stat_type ?? null,
          normalizedStatType: prop.normalized_stat_type ?? null,
          propText: prop.prop ?? null,
        });
        await storePrecomputed({
          dateET: params.dateET,
          league: params.event.league,
          eventId: params.event.event_id,
          forecastType: 'PLAYER_PROP',
          teamId: teamShort || teamName,
          teamSide: params.side,
          playerName: canonicalPlayer,
          modelVersion,
          vendorInputs: {
            source: 'TEAM_PROPS_EXTRACT',
            mlb_snapshot: params.mlbSnapshot ?? null,
            mlb_alerts: summarizeMlbOperationalAlerts({
              snapshot: params.mlbSnapshot ?? null,
              startsAt: params.event.starts_at,
              feedRowCount: normalizedPropsResult.metadata?.mlb_feed_row_count ?? null,
              candidateCount: normalizedPropsResult.metadata?.mlb_candidate_count ?? null,
              teamName,
            }),
          },
          payload: buildStoredPlayerPropPayload({
            assetMetadata: buildAssetMetadata('PLAYER_PROP', { stat_type: statIdentity.statType }),
            league: params.event.league,
            playerName: canonicalPlayer,
            statType: statIdentity.statType,
            normalizedStatType: statIdentity.normalizedStatType,
            prop,
          }),
          confidence: prop.prob ? prop.prob / 100 : null,
          expiresAt: params.event.starts_at,
          runId: params.runId,
        });
        await recordClvPick({
          eventId: params.event.event_id,
          league: params.event.league,
          pickType: 'player_prop',
          direction: prop.recommendation ?? '',
          recLine: prop.market_line_value ?? null,
          recOdds: prop.odds ?? null,
          playerName: canonicalPlayer,
          propStat: statIdentity.statType ?? prop.prop ?? null,
          modelConfidence: prop.prob ? prop.prob / 100 : null,
          stormTier: null,
        });
        playerPropsExtracted++;
      } catch (e: any) {
        diagnostics.storeFailures++;
        pushDiagnosticSample(diagnostics.failedStorePlayers, canonicalPlayer);
        console.log(`    [WARN] PLAYER_PROP ${prop.player}: ${e.message}`);
      }
    }
    if (playerPropsExtracted > 0) {
      console.log(`    -> Extracted ${playerPropsExtracted} PLAYER_PROP rows for ${teamName}`);
    }
  }
  diagnostics.playerPropsStored = playerPropsExtracted;

  await storePrecomputed({
    dateET: params.dateET,
    league: params.event.league,
    eventId: params.event.event_id,
    forecastType: 'TEAM_PROPS',
    teamId: teamShort || teamName,
    teamSide: params.side,
    playerName: null,
    modelVersion,
    vendorInputs: {
      piff_team: teamShort,
      prop_count: params.event.prop_count || 0,
      mlb_snapshot: params.mlbSnapshot ?? null,
      market_origin: 'bundle',
      player_prop_pipeline: diagnostics,
      mlb_alerts: summarizeMlbOperationalAlerts({
        snapshot: params.mlbSnapshot ?? null,
        startsAt: params.event.starts_at,
        feedRowCount: normalizedPropsResult.metadata?.mlb_feed_row_count ?? null,
        candidateCount: normalizedPropsResult.metadata?.mlb_candidate_count ?? null,
        teamName,
      }),
    },
    payload: {
      ...normalizedPropsResult,
      props: finalPublishableProps,
      metadata: {
        ...(normalizedPropsResult.metadata && typeof normalizedPropsResult.metadata === 'object'
          ? normalizedPropsResult.metadata
          : {}),
        player_prop_pipeline: diagnostics,
      },
      ...buildAssetMetadata('TEAM_PROPS'),
    },
    confidence: null,
    expiresAt: params.event.starts_at,
    runId: params.runId,
  });

  await pool.query(
    `INSERT INTO rm_team_props_cache (event_id, team, team_name, team_short, league, props_data)
     VALUES ($1, $2, $3, $4, $5, $6)
     ON CONFLICT (event_id, team) DO UPDATE SET props_data = EXCLUDED.props_data`,
    [params.event.event_id, params.side, teamName, teamShort, params.event.league, JSON.stringify({
      ...normalizedPropsResult,
      props: finalPublishableProps,
      metadata: {
        ...(normalizedPropsResult.metadata && typeof normalizedPropsResult.metadata === 'object'
          ? normalizedPropsResult.metadata
          : {}),
        player_prop_pipeline: diagnostics,
      },
    })]
  );

  return {
    success: true,
    playerPropsExtracted,
    assetWrites: (params.countTeamBundleWrite === false ? 0 : 1) + playerPropsExtracted,
    diagnostics,
  };
}

async function markTeamPropAssetsStale(params: {
  dateET: string;
  eventId: string;
  side: 'home' | 'away';
}): Promise<number> {
  const result = await pool.query(
    `UPDATE rm_forecast_precomputed
        SET status = 'STALE',
            generated_at = NOW()
      WHERE date_et = $1
        AND event_id = $2
        AND team_side = $3
        AND forecast_type IN ('TEAM_PROPS', 'PLAYER_PROP')
        AND status IN ('ACTIVE', 'STALE')
      RETURNING id`,
    [params.dateET, params.eventId, params.side],
  ).catch(() => ({ rowCount: 0 }));

  return Number(result.rowCount || 0);
}

function buildEmptyTeamPropsPersistenceDiagnostics(): TeamPropsPersistenceDiagnostics {
  return {
    rawPropCount: 0,
    filteredPropCount: 0,
    publishablePropCount: 0,
    playerPropsStored: 0,
    supplementalPropCount: 0,
    sourceBackedRescueUsed: false,
    sourceBackedRescueShortfall: 0,
    droppedMissingPricing: 0,
    droppedMissingMetadata: 0,
    droppedNonRoster: 0,
    storeFailures: 0,
    candidateCount: null,
    publishableCandidateCount: null,
    feedRowCount: null,
    sourceBackedCandidateCount: null,
    sourceBackedSelectedCount: null,
    sourceBackedCandidateSource: null,
    sourceBackedSuppressionReasons: {},
    sourceBackedRejectedCount: 0,
    sourceBackedRejectedReasons: {},
    sourceBackedRejectedPlayers: [],
    droppedMetadataReasons: {},
    missingPricingPlayers: [],
    missingMetadataPlayers: [],
    nonRosterPlayers: [],
    failedStorePlayers: [],
  };
}

async function hasNewerTeamPropsWrite(params: {
  dateET: string;
  eventId: string;
  side: 'home' | 'away';
  generationStartedAt: Date;
}): Promise<boolean> {
  const { rows } = await pool.query(
    `SELECT MAX(generated_at) AS latest_generated_at
     FROM rm_forecast_precomputed
     WHERE date_et = $1
       AND event_id = $2
       AND team_side = $3
       AND forecast_type IN ('TEAM_PROPS', 'PLAYER_PROP')
       AND status IN ('ACTIVE', 'STALE')`,
    [params.dateET, params.eventId, params.side],
  ).catch(() => ({ rows: [] as any[] }));

  const latestGeneratedAt = rows[0]?.latest_generated_at;
  if (!latestGeneratedAt) {
    return false;
  }

  return new Date(latestGeneratedAt).getTime() > params.generationStartedAt.getTime();
}

async function withTeamPropsWriteLock<T>(params: {
  dateET: string;
  eventId: string;
  side: 'home' | 'away';
  generationStartedAt: Date;
  skipLogLabel: string;
  onSkip: () => T;
  execute: () => Promise<T>;
}): Promise<T> {
  const lockPool = pool as typeof pool & {
    connect?: () => Promise<{
      query: (sql: string, values?: any[]) => Promise<any>;
      release: () => void;
    }>;
  };
  const lockKey = `${params.dateET}:${params.eventId}:${params.side}`;
  const lockClient = typeof lockPool.connect === 'function'
    ? await lockPool.connect().catch(() => null)
    : null;

  try {
    if (lockClient) {
      await lockClient.query(
        `SELECT pg_advisory_lock(hashtext($1), hashtext($2))`,
        ['rm_team_props_write', lockKey],
      );
    }

    if (await hasNewerTeamPropsWrite(params)) {
      console.log(
        `    -> Skip ${params.skipLogLabel}: newer ${params.side} TEAM_PROPS/PLAYER_PROP rows were written after this run started`,
      );
      return params.onSkip();
    }

    return await params.execute();
  } finally {
    if (lockClient) {
      try {
        await lockClient.query(
          `SELECT pg_advisory_unlock(hashtext($1), hashtext($2))`,
          ['rm_team_props_write', lockKey],
        );
      } catch {
        // Ignore unlock failures; releasing the connection will still drop the session lock.
      }
      lockClient.release();
    }
  }
}

export async function rehydrateCachedTeamPropsForTeam(
  event: EventRow,
  side: 'home' | 'away',
  dateET: string,
  runId: string,
  mlbSnapshot: any | null = null,
  mlbPhase: any | null = null,
): Promise<{ success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics; reusedCache: boolean }> {
  const { rows } = await pool.query(
    `SELECT props_data
     FROM rm_team_props_cache
     WHERE event_id = $1 AND team = $2
     LIMIT 1`,
    [event.event_id, side],
  ).catch(() => ({ rows: [] as any[] }));

  const generationStartedAt = new Date();
  const cachedPropsResult = normalizeTeamPropsResultForStorage(rows[0]?.props_data);
  const cachedProps = Array.isArray(cachedPropsResult?.props) ? cachedPropsResult.props.filter(Boolean) : [];
  if (cachedProps.length === 0) {
    return {
      success: false,
      playerPropsExtracted: 0,
      assetWrites: 0,
      reusedCache: false,
      diagnostics: buildEmptyTeamPropsPersistenceDiagnostics(),
    };
  }

  const persisted = await withTeamPropsWriteLock({
    dateET,
    eventId: event.event_id,
    side,
    generationStartedAt,
    skipLogLabel: `${event.event_id} ${side} cache rehydrate`,
    onSkip: () => ({
      success: true,
      playerPropsExtracted: 0,
      assetWrites: 0,
      diagnostics: buildEmptyTeamPropsPersistenceDiagnostics(),
      reusedCache: false,
    }),
    execute: async () => {
      const writeResult = await persistTeamPropsBundleAssets({
        event,
        side,
        dateET,
        runId,
        mlbSnapshot,
        mlbPhase,
        propsResult: cachedPropsResult,
        countTeamBundleWrite: false,
      });
      return { ...writeResult, reusedCache: true };
    },
  });
  return persisted;
}

export async function replayTeamPropsForTeam(
  event: EventRow,
  side: 'home' | 'away',
  dateET: string,
  runId: string | null,
  mlbSnapshot: any | null = null,
  mlbPhase: any | null = null,
  options?: {
    skipTheOddsVerification?: boolean;
    throwOnSourceQueryError?: boolean;
  },
): Promise<{ success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics; staleWrites: number }> {
  const teamShort = side === 'home' ? event.home_short : event.away_short;
  const opponentName = side === 'home' ? event.away_team : event.home_team;
  const opponentShort = side === 'home' ? event.away_short : event.home_short;
  const teamName = side === 'home' ? event.home_team : event.away_team;
  const generationStartedAt = new Date();

  const propsResult = normalizeTeamPropsResultForStorage(await generateTeamProps({
    teamName,
    teamShort: teamShort || '',
    opponentName,
    opponentShort: opponentShort || '',
    league: event.league,
    isHome: side === 'home',
    startsAt: event.starts_at,
    moneyline: event.moneyline || { home: null, away: null },
    spread: event.spread || { home: null, away: null },
    total: event.total || { over: null, under: null },
    skipTheOddsVerification: options?.skipTheOddsVerification,
    throwOnSourceQueryError: options?.throwOnSourceQueryError,
  }));

  return withTeamPropsWriteLock({
    dateET,
    eventId: event.event_id,
    side,
    generationStartedAt,
    skipLogLabel: `${event.event_id} ${side} replay`,
    onSkip: () => ({
      success: true,
      playerPropsExtracted: 0,
      assetWrites: 0,
      diagnostics: buildEmptyTeamPropsPersistenceDiagnostics(),
      staleWrites: 0,
    }),
    execute: async () => {
      const staleWrites = await markTeamPropAssetsStale({
        dateET,
        eventId: event.event_id,
        side,
      });

      const persisted = await persistTeamPropsBundleAssets({
        event,
        side,
        dateET,
        runId,
        mlbSnapshot,
        mlbPhase,
        propsResult,
      });

      return {
        ...persisted,
        staleWrites,
      };
    },
  });
}

/**
 * Generate TEAM_PROPS forecast for one team
 */
export async function generateTeamPropsForTeam(
  event: EventRow,
  side: 'home' | 'away',
  dateET: string,
  runId: string | null,
  mlbSnapshot: any | null = null,
  mlbPhase: any | null = null,
  options?: {
    skipTheOddsVerification?: boolean;
    throwOnSourceQueryError?: boolean;
  },
): Promise<{ success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics }> {
  const teamShort = side === 'home' ? event.home_short : event.away_short;
  const opponentName = side === 'home' ? event.away_team : event.home_team;
  const opponentShort = side === 'home' ? event.away_short : event.home_short;
  const generationStartedAt = new Date();

  const propsResult = normalizeTeamPropsResultForStorage(await generateTeamProps({
    teamName: side === 'home' ? event.home_team : event.away_team,
    teamShort: teamShort || '',
    opponentName,
    opponentShort: opponentShort || '',
    league: event.league,
    isHome: side === 'home',
    startsAt: event.starts_at,
    moneyline: event.moneyline || { home: null, away: null },
    spread: event.spread || { home: null, away: null },
    total: event.total || { over: null, under: null },
    skipTheOddsVerification: options?.skipTheOddsVerification,
    throwOnSourceQueryError: options?.throwOnSourceQueryError,
  }));

  return withTeamPropsWriteLock({
    dateET,
    eventId: event.event_id,
    side,
    generationStartedAt,
    skipLogLabel: `${event.event_id} ${side} generation`,
    onSkip: () => ({
      success: true,
      playerPropsExtracted: 0,
      assetWrites: 0,
      diagnostics: buildEmptyTeamPropsPersistenceDiagnostics(),
    }),
    execute: () => persistTeamPropsBundleAssets({
      event,
      side,
      dateET,
      runId,
      mlbSnapshot,
      mlbPhase,
      propsResult,
    }),
  });
}

export async function main(): Promise<number> {
  const startTime = Date.now();
  const dateET = getTodayET();
  const logs: string[] = [];
  let runId: string | null = null;
  let eventsFound = 0;
  let skippedDueToCap = 0;
  let totalGenerated = 0;
  let totalFailures = 0;
  let totalOddsRefreshed = 0;
  let totalPlayerPropsExtracted = 0;

  const log = (msg: string) => {
    console.log(msg);
    logs.push(msg);
  };

  try {
    log('='.repeat(60));
    log('WEATHER REPORT — DAILY PRECOMPUTE');
    log(`Date (ET): ${dateET}`);
    log(`Schedule: ${scheduleName}`);
    log(`Mode: ${dryRun ? 'DRY RUN' : 'LIVE'}`);
    log(`Cap: ${DAILY_CAP} forecasts (${MAX_EVENTS} events max)`);
    log('='.repeat(60));

    // Asset-aware usage accounting: count real precomputed assets already stored today.
    const usage = await getAssetUsageSummary(dateET);
    const alreadyGenerated = usage.total;
    const remainingBudget = DAILY_CAP - alreadyGenerated;
    const usagePct = DAILY_CAP > 0 ? alreadyGenerated / DAILY_CAP : 0;

    log(`\nExisting assets today: ${alreadyGenerated}`);
    log(`Asset breakdown: ${JSON.stringify(usage.byType)}`);
    log(`Asset families: ${JSON.stringify(usage.byFamily)}`);
    log(`Assets by league: ${JSON.stringify(usage.byLeague)}`);
    log(`Configured cap: ${DAILY_CAP} assets/day`);

    if (usagePct >= CAP_WARN_THRESHOLD) {
      log(`WARNING: Daily cap usage at ${(usagePct * 100).toFixed(1)}% (${alreadyGenerated}/${DAILY_CAP})`);
    }
    if (remainingBudget <= 0) {
      if (ENFORCE_CAP) {
        log(`\nDaily cap already reached (${alreadyGenerated}/${DAILY_CAP}). Exiting because WEATHER_REPORT_ENFORCE_CAP=true.`);
        return 0;
      }
      log(`\nNOTICE: Daily cap exceeded (${alreadyGenerated}/${DAILY_CAP}) but continuing because cap enforcement is disabled.`);
    }

    // Create run record
    runId = dryRun ? 'dry-run' : await createRun(dateET);

    const maxLookaheadDays = Math.max(0, EU_EVENT_LOOKAHEAD_DAYS);

    // Fetch future scheduled events inside the broadest supported lookahead, then
    // apply league-specific window rules in-process so non-EU leagues stay today-only.
    const { rows: events } = await pool.query(`
      SELECT event_id, league, home_team, away_team, home_short, away_short,
             starts_at, moneyline, spread, total, COALESCE(prop_count, 0) as prop_count
      FROM rm_events
      WHERE starts_at > NOW()
        AND starts_at < NOW() + (($1::int + 1) * INTERVAL '1 day')
        AND status = 'scheduled'
        ${leagueFilter ? `AND LOWER(league) = '${leagueFilter.replace(/'/g, "''")}'` : ''}
      ORDER BY starts_at ASC
    `, [maxLookaheadDays]);

    const eventsInWindow = events.filter((event: EventRow) => isWeatherReportEventInWindow(event.league, event.starts_at));

    eventsFound = eventsInWindow.length;
    log(`\nEligible events in precompute window: ${eventsInWindow.length}`);
    log(`Window policy: non-EU leagues = today only, EU leagues = ${EU_EVENT_LOOKAHEAD_DAYS} day lookahead`);

    if (eventsInWindow.length === 0) {
      log('No eligible events in precompute window. Exiting.');
      if (!dryRun) {
        await updateRun(runId, {
          total_contests_found: 0,
          total_forecasts_generated: 0,
          total_skipped_due_to_cap: 0,
          total_failures: 0,
          total_odds_refreshed: 0,
          duration_ms: Date.now() - startTime,
          status: 'SUCCESS',
          log_blob: logs.join('\n'),
        });
      }
      return 0;
    }

    // Asset-aware mode: process the full event list. Caps are monitored against real writes,
    // and only enforced if WEATHER_REPORT_ENFORCE_CAP=true.
    const eventsToProcess = eventsInWindow;

    log('\n' + '-'.repeat(60));

    for (let i = 0; i < eventsToProcess.length; i++) {
      const event = eventsToProcess[i] as EventRow;
      const label = `[${i + 1}/${eventsToProcess.length}] ${event.away_team} @ ${event.home_team} (${event.league.toUpperCase()})`;

      // Advisory asset estimate before this event
      const slotsRequired = requiredForecastSlots(event);
      if (ENFORCE_CAP && (alreadyGenerated + totalGenerated + slotsRequired > DAILY_CAP)) {
        log(`  [CAP] Budget exhausted at event ${i + 1}. Stopping.`);
        skippedDueToCap = eventsToProcess.length - i;
        break;
      }

      if (dryRun) {
        log(`  [DRY] ${label} — would generate at least ${slotsRequired} base assets${(event.league || '').toLowerCase() === 'mlb' ? ' (+ player props depending on markets)' : ''}`);
        totalGenerated += slotsRequired;
        continue;
      }

      // Check if already precomputed today
      const { rows: existing } = await pool.query(
        `SELECT forecast_type, team_id FROM rm_forecast_precomputed
         WHERE date_et = $1 AND event_id = $2 AND status = 'ACTIVE'`,
        [dateET, event.event_id]
      );

      const homeTeamId = event.home_short || event.home_team;
      const awayTeamId = event.away_short || event.away_team;
      const hasGameMarkets = existing.some(r => r.forecast_type === 'GAME_MARKETS');
      const hasGameTotal = existing.some(r => r.forecast_type === 'GAME_TOTAL');
      const hasRunLine = existing.some(r => r.forecast_type === 'MLB_RUN_LINE');
      const hasHomeProps = existing.some(r => r.forecast_type === 'TEAM_PROPS' && r.team_id === homeTeamId);
      const hasAwayProps = existing.some(r => r.forecast_type === 'TEAM_PROPS' && r.team_id === awayTeamId);
      const homePlayerPropsCount = existing.filter(r => r.forecast_type === 'PLAYER_PROP' && r.team_id === homeTeamId).length;
      const awayPlayerPropsCount = existing.filter(r => r.forecast_type === 'PLAYER_PROP' && r.team_id === awayTeamId).length;
      const hasMlbF5 = existing.some(r => r.forecast_type === 'MLB_F5');
      const hasMlbF5Side = existing.some(r => r.forecast_type === 'MLB_F5_SIDE');
      const hasMlbF5Total = existing.some(r => r.forecast_type === 'MLB_F5_TOTAL');
      const missingMlbSourceBacked = getMissingMlbSourceBackedAssetTypes(event, existing);
      const reuseHomeProps = canReuseTeamPropsAsset({ hasTeamProps: hasHomeProps, playerPropsCount: homePlayerPropsCount });
      const reuseAwayProps = canReuseTeamPropsAsset({ hasTeamProps: hasAwayProps, playerPropsCount: awayPlayerPropsCount });
      const mlbPhaseData = (event.league || '').toLowerCase() === 'mlb'
        ? await collectMlbPhase(event)
        : null;
      const mlbSnapshot = mlbPhaseData?.snapshot ?? null;
      const mlbPhase = mlbPhaseData?.phase ?? null;

      if (hasRequiredAssetsForSkip(event, existing)) {
        log(`  [CACHED] ${label} — all required forecasts exist, refreshing odds`);
        await updateCachedOdds(event.event_id, {
          moneyline: event.moneyline,
          spread: event.spread,
          total: event.total,
        });
        totalOddsRefreshed++;
        continue;
      }

      // 1) GAME_MARKETS
      if (!hasGameMarkets) {
        try {
          log(`  [GAME] ${label}...`);
          const writes = await generateGameMarkets(event, dateET, runId, mlbSnapshot);
          totalGenerated += writes;
        } catch (err: any) {
          log(`  [FAIL-GAME] ${label}: ${err.message}`);
          totalFailures++;
        }
        await sleep(3000); // Rate limit between Grok calls
      } else {
        log(`  [SKIP-GAME] ${label} — already cached`);
        if (missingMlbSourceBacked.length > 0) {
          try {
            log(`  [MLB-SOURCE] ${label} — backfilling ${missingMlbSourceBacked.join(', ')}`);
            const cachedForecast = await getCachedForecast(event.event_id);
            if (cachedForecast?.forecast_data) {
              const writes = await storeMlbSourceBackedMarkets({
                event,
                dateET,
                runId,
                forecast: cachedForecast.forecast_data as ForecastResult,
                mlbSnapshot,
                includeTypes: missingMlbSourceBacked,
              });
              totalGenerated += writes;
            } else {
              const writes = await generateGameMarkets(event, dateET, runId, mlbSnapshot);
              totalGenerated += writes;
            }
          } catch (err: any) {
            log(`  [FAIL-MLB-SOURCE] ${label}: ${err.message}`);
            totalFailures++;
          }
        }
      }

      // 2) TEAM_PROPS home
      if (!reuseHomeProps) {
        let usedHomeGrok = false;
        try {
          let homeResult: { success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics; reusedCache?: boolean };
          if (hasHomeProps && homePlayerPropsCount < MIN_PLAYER_PROPS_PER_TEAM) {
            log(`  [PROPS-HOME-CACHE] ${label} — extracting player props from cached ${event.home_team} bundle...`);
            homeResult = await rehydrateCachedTeamPropsForTeam(event, 'home', dateET, runId, mlbSnapshot, mlbPhase);
            const refreshedHomePlayerPropsCount = homeResult.reusedCache
              ? await countActivePlayerPropsForTeam(event.event_id, homeTeamId)
              : 0;
            if (!homeResult.reusedCache || refreshedHomePlayerPropsCount < MIN_PLAYER_PROPS_PER_TEAM) {
              log(`  [PROPS-HOME] ${label} — ${event.home_team}...`);
              usedHomeGrok = true;
              homeResult = await generateTeamPropsForTeam(event, 'home', dateET, runId, mlbSnapshot, mlbPhase);
            }
          } else {
            log(`  [PROPS-HOME] ${label} — ${event.home_team}...`);
            usedHomeGrok = true;
            homeResult = await generateTeamPropsForTeam(event, 'home', dateET, runId, mlbSnapshot, mlbPhase);
          }
          totalGenerated += homeResult.assetWrites;
          totalPlayerPropsExtracted += homeResult.playerPropsExtracted;
          log(`    ${formatTeamPropsPipelineLog({
            teamShort: homeTeamId,
            teamName: event.home_team,
            side: 'home',
            diagnostics: homeResult.diagnostics,
            reusedCache: homeResult.reusedCache,
          })}`);
        } catch (err: any) {
          log(`  [FAIL-PROPS-HOME] ${label}: ${err.message}`);
          totalFailures++;
        }
        if (usedHomeGrok) {
          await sleep(3000);
        }
      }

      // 3) TEAM_PROPS away
      if (!reuseAwayProps) {
        let usedAwayGrok = false;
        try {
          let awayResult: { success: boolean; playerPropsExtracted: number; assetWrites: number; diagnostics: TeamPropsPersistenceDiagnostics; reusedCache?: boolean };
          if (hasAwayProps && awayPlayerPropsCount < MIN_PLAYER_PROPS_PER_TEAM) {
            log(`  [PROPS-AWAY-CACHE] ${label} — extracting player props from cached ${event.away_team} bundle...`);
            awayResult = await rehydrateCachedTeamPropsForTeam(event, 'away', dateET, runId, mlbSnapshot, mlbPhase);
            const refreshedAwayPlayerPropsCount = awayResult.reusedCache
              ? await countActivePlayerPropsForTeam(event.event_id, awayTeamId)
              : 0;
            if (!awayResult.reusedCache || refreshedAwayPlayerPropsCount < MIN_PLAYER_PROPS_PER_TEAM) {
              log(`  [PROPS-AWAY] ${label} — ${event.away_team}...`);
              usedAwayGrok = true;
              awayResult = await generateTeamPropsForTeam(event, 'away', dateET, runId, mlbSnapshot, mlbPhase);
            }
          } else {
            log(`  [PROPS-AWAY] ${label} — ${event.away_team}...`);
            usedAwayGrok = true;
            awayResult = await generateTeamPropsForTeam(event, 'away', dateET, runId, mlbSnapshot, mlbPhase);
          }
          totalGenerated += awayResult.assetWrites;
          totalPlayerPropsExtracted += awayResult.playerPropsExtracted;
          log(`    ${formatTeamPropsPipelineLog({
            teamShort: awayTeamId,
            teamName: event.away_team,
            side: 'away',
            diagnostics: awayResult.diagnostics,
            reusedCache: awayResult.reusedCache,
          })}`);
        } catch (err: any) {
          log(`  [FAIL-PROPS-AWAY] ${label}: ${err.message}`);
          totalFailures++;
        }
        if (usedAwayGrok) {
          await sleep(3000);
        }
      }

      if (usesAssetBackedPropHighlights(event.league)) {
        try {
          await hydrateStoredPropHighlights(event.event_id);
        } catch (err: any) {
          log(`  [FAIL-PROP-HIGHLIGHTS] ${label}: ${err.message}`);
        }
      }

      if (MLB_MARKETS_ENABLED && (event.league || '').toLowerCase() === 'mlb' && (!hasGameTotal || !hasRunLine || !hasMlbF5 || !hasMlbF5Side || !hasMlbF5Total)) {
        const missingMlbAssets = [
          !hasGameTotal ? 'GAME_TOTAL' : null,
          !hasRunLine ? 'MLB_RUN_LINE' : null,
          !hasMlbF5 ? 'MLB_F5' : null,
          !hasMlbF5Side ? 'MLB_F5_SIDE' : null,
          !hasMlbF5Total ? 'MLB_F5_TOTAL' : null,
        ].filter(Boolean);
        log(`  [MLB-ASSETS] ${label} — missing ${missingMlbAssets.join(', ')}`);
      }

      if (shouldGenerateMlbF5(event, existing) || (MLB_MARKETS_ENABLED && (event.league || '').toLowerCase() === 'mlb' && (!hasMlbF5Side || !hasMlbF5Total))) {
        try {
          log(`  [MLB-F5] ${label}...`);
          const writes = await generateMlbFirstFiveMarket(event, dateET, runId, mlbSnapshot);
          totalGenerated += writes;
        } catch (err: any) {
          log(`  [FAIL-MLB-F5] ${label}: ${err.message}`);
          totalFailures++;
        }
        await sleep(3000);
      } else if (MLB_MARKETS_ENABLED && (event.league || '').toLowerCase() === 'mlb' && hasMlbF5 && hasMlbF5Side && hasMlbF5Total) {
        log(`  [SKIP-MLB-F5] ${label} — already cached`);
      }
    }

    const durationMs = Date.now() - startTime;
    const status = totalFailures === 0 ? 'SUCCESS' :
                   totalGenerated > 0 ? 'PARTIAL' : 'FAILED';

    log('\n' + '='.repeat(60));
    log('WEATHER REPORT COMPLETE');
    log('='.repeat(60));
    log(`Events found:      ${events.length}`);
    log(`Events processed:  ${eventsToProcess.length}`);
    log(`Forecasts gen'd:   ${totalGenerated}`);
    log(`Player props:      ${totalPlayerPropsExtracted}`);
    log(`Odds refreshed:    ${totalOddsRefreshed}`);
    log(`Failures:          ${totalFailures}`);
    log(`Skipped (cap):     ${skippedDueToCap}`);
    log(`Duration:          ${Math.round(durationMs / 1000)}s`);
    log(`Status:            ${status}`);
    log('='.repeat(60));

    // Update run record
    if (!dryRun) {
      await updateRun(runId, {
        total_contests_found: events.length,
        total_forecasts_generated: totalGenerated,
        total_skipped_due_to_cap: skippedDueToCap,
        total_failures: totalFailures,
        total_odds_refreshed: totalOddsRefreshed,
        duration_ms: durationMs,
        status,
        log_blob: logs.join('\n'),
      });

      // Update inventory last_success
      await pool.query(
        `UPDATE rm_weather_report_inventory SET last_success_at = NOW(), updated_at = NOW()
         WHERE bucket = 'AGENT' AND name = 'Weather Report Generator'`
      );
    }

    return totalFailures > 0 ? 1 : 0;
  } catch (err: any) {
    const errorMessage = err?.message || String(err);
    log(`FATAL: ${errorMessage}`);
    if (!dryRun && runId) {
      await updateRun(runId, {
        total_contests_found: eventsFound,
        total_forecasts_generated: totalGenerated,
        total_skipped_due_to_cap: skippedDueToCap,
        total_failures: totalFailures + 1,
        total_odds_refreshed: totalOddsRefreshed,
        duration_ms: Date.now() - startTime,
        status: totalGenerated > 0 ? 'PARTIAL' : 'FAILED',
        error_message: errorMessage,
        log_blob: logs.join('\n'),
      });
    }
    throw err;
  } finally {
    await pool.end().catch(() => undefined);
  }
}

if (require.main === module) {
  main()
    .then((code) => process.exit(code))
    .catch((err) => {
      console.error('Fatal error:', err);
      process.exit(1);
    });
}