import 'dotenv/config';

import pool from '../db';
import { generateTeamProps } from '../services/grok';
import { getMlbPlayerRole, describeForecastAsset } from '../services/forecast-asset-taxonomy';
import { resolvePlayerPropStatIdentity } from '../services/player-prop-market-registry';
import { buildPlayerPropSignal } from '../services/player-prop-signals';
import { buildStoredPlayerPropPayload, shouldPersistStandalonePlayerPropMetadata } from '../services/player-prop-storage';
import { resolveCanonicalName } from '../services/canonical-names';
import { shouldPersistExtractedPlayerProp } from './weather-report';

type Side = 'home' | 'away';

type 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;
  team: Side;
};

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

function buildForecastPrecomputedConflictClause(forecastType: string): 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'`;
}

async function createRun(dateET: string) {
  const { rows } = await pool.query(
    `INSERT INTO rm_weather_report_runs
       (date_et, schedule_name, status, total_contests_found, total_forecasts_generated, total_skipped_due_to_cap, total_failures, total_odds_refreshed, duration_ms, error_message, log_blob)
     VALUES ($1, 'MLB_TEAM_PROPS_REPAIR', 'RUNNING', 0, 0, 0, 0, 0, 0, NULL, NULL)
     RETURNING id`,
    [dateET],
  );
  return rows[0]?.id as string;
}

async function finalizeRun(runId: string, totals: {
  contests: number;
  generated: number;
  failures: number;
  startedAtMs: number;
  log: string[];
  fatal?: string | null;
}) {
  const status = totals.fatal
    ? 'FAILED'
    : totals.failures > 0
      ? 'PARTIAL'
      : 'SUCCESS';
  await pool.query(
    `UPDATE rm_weather_report_runs
     SET status = $2,
         total_contests_found = $3,
         total_forecasts_generated = $4,
         total_failures = $5,
         duration_ms = $6,
         error_message = $7,
         log_blob = $8
     WHERE id = $1`,
    [
      runId,
      status,
      totals.contests,
      totals.generated,
      totals.failures,
      Date.now() - totals.startedAtMs,
      totals.fatal || null,
      totals.log.join('\n').slice(-50000),
    ],
  );
}

async function storePrecomputed(data: {
  dateET: string;
  league: string;
  eventId: string;
  forecastType: string;
  teamId: string | null;
  teamSide: string | null;
  playerName: string | null;
  modelVersion: string;
  vendorInputs: any;
  payload: any;
  confidence: number | null;
  expiresAt: string;
  runId: string;
}) {
  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)
     ${buildForecastPrecomputedConflictClause(data.forecastType)}
     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;
  if (data.forecastType !== 'PLAYER_PROP' || !assetId) return;

  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]
  ).catch(() => {});
}

async function main() {
  const startedAtMs = Date.now();
  const dateET = new Intl.DateTimeFormat('en-CA', {
    timeZone: 'America/New_York',
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  }).format(new Date());
  const targetEventId = process.env.TARGET_EVENT_ID || null;
  const targetSide = process.env.TARGET_SIDE || null;
  const modelVersion = process.env.GROK_MODEL || 'grok-4-1-fast-reasoning';
  const log: string[] = [];
  let generated = 0;
  let failures = 0;
  const runId = await createRun(dateET);

  try {
    const params: any[] = [dateET];
    let targetClause = '';
    if (targetEventId) {
      params.push(targetEventId);
      targetClause += ` AND e.event_id = $${params.length}`;
    }
    if (targetSide) {
      params.push(targetSide);
      targetClause += ` AND stale.team = $${params.length}`;
    }

    const { rows } = await pool.query(
      `WITH stale AS (
         SELECT e.event_id, e.league, e.home_team, e.away_team, e.home_short, e.away_short,
                e.starts_at, e.moneyline, e.spread, e.total, e.prop_count,
                'home'::text AS team
         FROM rm_events e
         LEFT JOIN rm_team_props_cache c
           ON c.event_id = e.event_id AND c.team = 'home'
         LEFT JOIN LATERAL (
           SELECT COUNT(*) FILTER (
             WHERE fp.forecast_type = 'PLAYER_PROP'
               AND fp.status = 'ACTIVE'
               AND fp.team_side = 'home'
               AND (fp.forecast_payload->'model_context'->>'lineup_certainty') IS NULL
           ) AS null_lineup_props
           FROM rm_forecast_precomputed fp
           WHERE fp.event_id = e.event_id
         ) p ON true
         WHERE e.league = 'mlb'
           AND (e.starts_at AT TIME ZONE 'America/New_York')::date = $1::date
           AND (
             COALESCE(jsonb_array_length(COALESCE(c.props_data->'props', '[]'::jsonb)), 0) = 0
             OR COALESCE(p.null_lineup_props, 0) > 0
           )
         UNION ALL
         SELECT e.event_id, e.league, e.home_team, e.away_team, e.home_short, e.away_short,
                e.starts_at, e.moneyline, e.spread, e.total, e.prop_count,
                'away'::text AS team
         FROM rm_events e
         LEFT JOIN rm_team_props_cache c
           ON c.event_id = e.event_id AND c.team = 'away'
         LEFT JOIN LATERAL (
           SELECT COUNT(*) FILTER (
             WHERE fp.forecast_type = 'PLAYER_PROP'
               AND fp.status = 'ACTIVE'
               AND fp.team_side = 'away'
               AND (fp.forecast_payload->'model_context'->>'lineup_certainty') IS NULL
           ) AS null_lineup_props
           FROM rm_forecast_precomputed fp
           WHERE fp.event_id = e.event_id
         ) p ON true
         WHERE e.league = 'mlb'
           AND (e.starts_at AT TIME ZONE 'America/New_York')::date = $1::date
           AND (
             COALESCE(jsonb_array_length(COALESCE(c.props_data->'props', '[]'::jsonb)), 0) = 0
             OR COALESCE(p.null_lineup_props, 0) > 0
           )
       )
       SELECT stale.*
       FROM stale
       JOIN rm_events e ON e.event_id = stale.event_id
       WHERE 1=1 ${targetClause}
       ORDER BY stale.starts_at ASC, stale.event_id ASC, stale.team ASC`,
      params,
    );

    log.push(`[repair] date=${dateET} targets=${rows.length}`);
    console.log(log[log.length - 1]);

    for (const row of rows as EventRow[]) {
      const side = row.team;
      const teamName = side === 'home' ? row.home_team : row.away_team;
      const teamShort = side === 'home' ? row.home_short : row.away_short;
      const opponentName = side === 'home' ? row.away_team : row.home_team;
      const opponentShort = side === 'home' ? row.away_short : row.home_short;

      try {
        console.log(`[repair] regenerating ${row.event_id} ${side} (${teamName})`);
        const propsResult = await generateTeamProps({
          teamName,
          teamShort: teamShort || '',
          opponentName,
          opponentShort: opponentShort || '',
          league: row.league,
          isHome: side === 'home',
          startsAt: row.starts_at,
          moneyline: row.moneyline || { home: null, away: null },
          spread: row.spread || { home: null, away: null },
          total: row.total || { over: null, under: null },
        });

        const filteredProps = Array.isArray(propsResult.props)
          ? propsResult.props
              .map((prop: any) => {
                const playerRole = getMlbPlayerRole(prop.stat_type ?? null);
                const signal = buildPlayerPropSignal({
                  player: prop.player,
                  team: teamShort || teamName,
                  teamSide: side,
                  league: row.league,
                  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_pct ?? prop.edge ?? null,
                  recommendation: prop.recommendation ?? null,
                  playerRole,
                });
                if (!signal) return null;
                return {
                  ...prop,
                  odds: prop.odds ?? null,
                  player_role: playerRole,
                  signal_tier: signal.signalTier,
                  signal_label: signal.signalLabel,
                  forecast_direction: signal.forecastDirection,
                  market_implied_probability: signal.marketImpliedProbability,
                  projected_probability: signal.projectedProbability,
                  edge_pct: signal.edgePct,
                  signal_table_row: signal.tableRow,
                };
              })
              .filter(Boolean)
          : [];

        await pool.query(
          `UPDATE rm_forecast_precomputed
           SET status = 'STALE'
           WHERE date_et = $1
             AND event_id = $2
             AND team_side = $3
             AND forecast_type IN ('TEAM_PROPS','PLAYER_PROP')
             AND status = 'ACTIVE'`,
          [dateET, row.event_id, side],
        );

        await storePrecomputed({
          dateET,
          league: row.league,
          eventId: row.event_id,
          forecastType: 'TEAM_PROPS',
          teamId: teamShort || teamName,
          teamSide: side,
          playerName: null,
          modelVersion,
          vendorInputs: { source: 'MLB_TEAM_PROPS_REPAIR' },
          payload: {
            ...propsResult,
            props: filteredProps,
            ...buildAssetMetadata('TEAM_PROPS'),
          },
          confidence: null,
          expiresAt: row.starts_at,
          runId,
        });

        let extracted = 0;
        for (const prop of filteredProps) {
          if (!prop.player || !prop.prop) continue;
          const canonicalPlayer = resolveCanonicalName(prop.player, row.league);
          if (!shouldPersistExtractedPlayerProp({
            league: row.league,
            teamShort,
            canonicalPlayer,
            odds: prop.odds ?? null,
          })) {
            continue;
          }
          if (!shouldPersistStandalonePlayerPropMetadata(prop)) {
            continue;
          }

          const statIdentity = resolvePlayerPropStatIdentity({
            league: row.league,
            statType: prop.stat_type ?? null,
            normalizedStatType: prop.normalized_stat_type ?? null,
            propText: prop.prop ?? null,
          });

          await storePrecomputed({
            dateET,
            league: row.league,
            eventId: row.event_id,
            forecastType: 'PLAYER_PROP',
            teamId: teamShort || teamName,
            teamSide: side,
            playerName: canonicalPlayer,
            modelVersion,
            vendorInputs: { source: 'MLB_TEAM_PROPS_REPAIR_EXTRACT' },
            payload: buildStoredPlayerPropPayload({
              assetMetadata: buildAssetMetadata('PLAYER_PROP', { stat_type: statIdentity.statType }),
              league: row.league,
              playerName: canonicalPlayer,
              statType: statIdentity.statType,
              normalizedStatType: statIdentity.normalizedStatType,
              prop,
            }),
            confidence: prop.prob ? prop.prob / 100 : null,
            expiresAt: row.starts_at,
            runId,
          });
          extracted++;
        }

        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,
               team_name = EXCLUDED.team_name,
               team_short = EXCLUDED.team_short,
               league = EXCLUDED.league`,
          [row.event_id, side, teamName, teamShort, row.league, JSON.stringify({ ...propsResult, props: filteredProps })],
        );

        generated += filteredProps.length + 1;
        log.push(`[repair] ${row.event_id} ${side}: props=${filteredProps.length} extracted=${extracted}`);
        console.log(log[log.length - 1]);
      } catch (err: any) {
        failures++;
        log.push(`[repair] ${row.event_id} ${side} failed: ${err?.message || err}`);
        console.error(log[log.length - 1]);
      }
    }

    await finalizeRun(runId, {
      contests: rows.length,
      generated,
      failures,
      startedAtMs,
      log,
    });
  } catch (err: any) {
    const fatal = String(err?.message || err);
    console.error('[repair] fatal', fatal);
    await finalizeRun(runId, {
      contests: 0,
      generated,
      failures: failures + 1,
      startedAtMs,
      log,
      fatal,
    });
    throw err;
  } finally {
    await pool.end();
  }
}

main().catch(() => process.exit(1));