import dotenv from 'dotenv';
import path from 'path';
dotenv.config({ path: path.join(__dirname, '../../.env') });

import pool from '../db';

async function repairGameSpreadResults(): Promise<number> {
  const { rows } = await pool.query(`
    WITH latest_scores AS (
      SELECT DISTINCT ON (event_id)
        event_id,
        home_score,
        away_score
      FROM rm_forecast_accuracy_v2
      WHERE home_score IS NOT NULL
        AND away_score IS NOT NULL
      ORDER BY event_id, resolved_at DESC NULLS LAST, id DESC
    ),
    repaired AS (
      UPDATE rm_pick_clv p
      SET result = CASE
            WHEN p.direction = 'home' THEN
              CASE
                WHEN (s.home_score - s.away_score) + p.rec_line > 0 THEN 'W'
                WHEN (s.home_score - s.away_score) + p.rec_line < 0 THEN 'L'
                ELSE 'P'
              END
            ELSE
              CASE
                WHEN -(s.home_score - s.away_score) + p.rec_line > 0 THEN 'W'
                WHEN -(s.home_score - s.away_score) + p.rec_line < 0 THEN 'L'
                ELSE 'P'
              END
          END,
          resolved_at = COALESCE(p.resolved_at, NOW())
      FROM latest_scores s
      WHERE p.event_id = s.event_id
        AND p.pick_type = 'game_spread'
        AND p.rec_line IS NOT NULL
        AND p.result IS DISTINCT FROM CASE
              WHEN p.direction = 'home' THEN
                CASE
                  WHEN (s.home_score - s.away_score) + p.rec_line > 0 THEN 'W'
                  WHEN (s.home_score - s.away_score) + p.rec_line < 0 THEN 'L'
                  ELSE 'P'
                END
              ELSE
                CASE
                  WHEN -(s.home_score - s.away_score) + p.rec_line > 0 THEN 'W'
                  WHEN -(s.home_score - s.away_score) + p.rec_line < 0 THEN 'L'
                  ELSE 'P'
                END
            END
      RETURNING p.id
    )
    SELECT COUNT(*)::int AS repaired_count FROM repaired
  `);

  return Number(rows[0]?.repaired_count || 0);
}

async function repairGameTotalResults(): Promise<number> {
  const { rows } = await pool.query(`
    WITH latest_scores AS (
      SELECT DISTINCT ON (event_id)
        event_id,
        home_score,
        away_score
      FROM rm_forecast_accuracy_v2
      WHERE home_score IS NOT NULL
        AND away_score IS NOT NULL
      ORDER BY event_id, resolved_at DESC NULLS LAST, id DESC
    ),
    repaired AS (
      UPDATE rm_pick_clv p
      SET result = CASE
            WHEN p.direction = 'over' THEN
              CASE
                WHEN (s.home_score + s.away_score) > p.rec_line THEN 'W'
                WHEN (s.home_score + s.away_score) < p.rec_line THEN 'L'
                ELSE 'P'
              END
            ELSE
              CASE
                WHEN (s.home_score + s.away_score) < p.rec_line THEN 'W'
                WHEN (s.home_score + s.away_score) > p.rec_line THEN 'L'
                ELSE 'P'
              END
          END,
          resolved_at = COALESCE(p.resolved_at, NOW())
      FROM latest_scores s
      WHERE p.event_id = s.event_id
        AND p.pick_type = 'game_total'
        AND p.rec_line IS NOT NULL
        AND p.result IS DISTINCT FROM CASE
              WHEN p.direction = 'over' THEN
                CASE
                  WHEN (s.home_score + s.away_score) > p.rec_line THEN 'W'
                  WHEN (s.home_score + s.away_score) < p.rec_line THEN 'L'
                  ELSE 'P'
                END
              ELSE
                CASE
                  WHEN (s.home_score + s.away_score) < p.rec_line THEN 'W'
                  WHEN (s.home_score + s.away_score) > p.rec_line THEN 'L'
                  ELSE 'P'
                END
            END
      RETURNING p.id
    )
    SELECT COUNT(*)::int AS repaired_count FROM repaired
  `);

  return Number(rows[0]?.repaired_count || 0);
}

async function repairGameMoneylineResults(): Promise<number> {
  const { rows } = await pool.query(`
    WITH latest_scores AS (
      SELECT DISTINCT ON (event_id)
        event_id,
        home_score,
        away_score
      FROM rm_forecast_accuracy_v2
      WHERE home_score IS NOT NULL
        AND away_score IS NOT NULL
      ORDER BY event_id, resolved_at DESC NULLS LAST, id DESC
    ),
    repaired AS (
      UPDATE rm_pick_clv p
      SET result = CASE
            WHEN s.home_score = s.away_score THEN 'P'
            WHEN p.direction = 'home' THEN CASE WHEN s.home_score > s.away_score THEN 'W' ELSE 'L' END
            ELSE CASE WHEN s.away_score > s.home_score THEN 'W' ELSE 'L' END
          END,
          resolved_at = COALESCE(p.resolved_at, NOW())
      FROM latest_scores s
      WHERE p.event_id = s.event_id
        AND p.pick_type = 'game_moneyline'
        AND p.result IS DISTINCT FROM CASE
              WHEN s.home_score = s.away_score THEN 'P'
              WHEN p.direction = 'home' THEN CASE WHEN s.home_score > s.away_score THEN 'W' ELSE 'L' END
              ELSE CASE WHEN s.away_score > s.home_score THEN 'W' ELSE 'L' END
            END
      RETURNING p.id
    )
    SELECT COUNT(*)::int AS repaired_count FROM repaired
  `);

  return Number(rows[0]?.repaired_count || 0);
}

async function repairPlayerPropResults(): Promise<{ matched: number; repaired: number }> {
  const { rows: matchedRows } = await pool.query(`
    SELECT COUNT(*)::int AS matched_count
    FROM rm_pick_clv p
    LEFT JOIN rm_events e ON e.event_id = p.event_id
    JOIN LATERAL (
      SELECT pgm.value
      FROM "PlayerGameMetric" pgm
      WHERE pgm.league = p.league
        AND LOWER(TRIM(COALESCE(pgm."playerName", ''))) = LOWER(TRIM(COALESCE(p.player_name, '')))
        AND pgm."statKey" = p.prop_stat
        AND pgm."gameDate"::date = DATE(COALESCE(e.starts_at, p.created_at))
      ORDER BY pgm."createdAt" DESC, pgm.id DESC
      LIMIT 1
    ) pgm ON true
    WHERE p.pick_type = 'player_prop'
      AND p.rec_line IS NOT NULL
  `);

  const { rows } = await pool.query(`
    WITH metric_rows AS (
      SELECT
        p.id,
        p.direction,
        p.rec_line,
        pgm.value AS actual_value
      FROM rm_pick_clv p
      LEFT JOIN rm_events e ON e.event_id = p.event_id
      JOIN LATERAL (
        SELECT pgm.value
        FROM "PlayerGameMetric" pgm
        WHERE pgm.league = p.league
          AND LOWER(TRIM(COALESCE(pgm."playerName", ''))) = LOWER(TRIM(COALESCE(p.player_name, '')))
          AND pgm."statKey" = p.prop_stat
          AND pgm."gameDate"::date = DATE(COALESCE(e.starts_at, p.created_at))
        ORDER BY pgm."createdAt" DESC, pgm.id DESC
        LIMIT 1
      ) pgm ON true
      WHERE p.pick_type = 'player_prop'
        AND p.rec_line IS NOT NULL
    ),
    repaired AS (
      UPDATE rm_pick_clv p
      SET result = CASE
            WHEN m.direction = 'over' THEN
              CASE
                WHEN m.actual_value > m.rec_line THEN 'W'
                WHEN m.actual_value < m.rec_line THEN 'L'
                ELSE 'P'
              END
            ELSE
              CASE
                WHEN m.actual_value < m.rec_line THEN 'W'
                WHEN m.actual_value > m.rec_line THEN 'L'
                ELSE 'P'
              END
          END,
          resolved_at = COALESCE(p.resolved_at, NOW())
      FROM metric_rows m
      WHERE p.id = m.id
        AND p.result IS DISTINCT FROM CASE
              WHEN m.direction = 'over' THEN
                CASE
                  WHEN m.actual_value > m.rec_line THEN 'W'
                  WHEN m.actual_value < m.rec_line THEN 'L'
                  ELSE 'P'
                END
              ELSE
                CASE
                  WHEN m.actual_value < m.rec_line THEN 'W'
                  WHEN m.actual_value > m.rec_line THEN 'L'
                  ELSE 'P'
                END
            END
      RETURNING p.id
    )
    SELECT COUNT(*)::int AS repaired_count FROM repaired
  `);

  return {
    matched: Number(matchedRows[0]?.matched_count || 0),
    repaired: Number(rows[0]?.repaired_count || 0),
  };
}

async function refreshViews(): Promise<void> {
  await pool.query('REFRESH MATERIALIZED VIEW rm_clv_summary').catch(() => undefined);
  await pool.query('REFRESH MATERIALIZED VIEW rm_model_kpi_stats').catch(() => undefined);
}

async function main() {
  console.log(`[clv-repair] Starting at ${new Date().toISOString()}`);

  const repairedGameSpread = await repairGameSpreadResults();
  const repairedGameTotal = await repairGameTotalResults();
  const repairedGameMoneyline = await repairGameMoneylineResults();
  const repairedPlayerProps = await repairPlayerPropResults();
  await refreshViews();

  const summary = {
    repairedGameSpread,
    repairedGameTotal,
    repairedGameMoneyline,
    matchedPlayerProps: repairedPlayerProps.matched,
    repairedPlayerProps: repairedPlayerProps.repaired,
  };

  console.log(`[clv-repair] ${JSON.stringify(summary)}`);
  await pool.end();
}

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