import 'dotenv/config';
import pool from '../db';
import { normalizeClvPropStat } from '../services/player-prop-clv-profile';

type HistRow = {
  prop_stat: string | null;
  direction: string | null;
  rec_odds: number | null;
  rec_line: number | null;
  model_confidence: number | null;
  result: 'W' | 'L';
};

type Rule = {
  name: string;
  matches: (row: NormalizedRow) => boolean;
};

type NormalizedRow = {
  stat: string;
  direction: string;
  odds: number | null;
  line: number | null;
  confidencePct: number | null;
  result: 'W' | 'L';
};

function normalizeMlbBacktestStat(value: string | null | undefined): string {
  const raw = (normalizeClvPropStat(value, 'mlb') || String(value || ''))
    .trim()
    .toLowerCase()
    .replace(/[\s-]+/g, '_');

  if (['pitching_hits', 'hitsallowed', 'hitallowed'].includes(raw)) return 'pitching_hits';
  if (['pitching_strikeouts', 'strikeouts', 'strikeout'].includes(raw)) return 'pitching_strikeouts';
  if ([
    'pitching_basesonballs',
    'pitching_walks',
    'pitchingwalks',
    'walksallowed',
    'walkallowed',
    'walks',
  ].includes(raw)) return 'pitching_basesonballs';
  if (['pitching_outs', 'outsrecorded', 'outs'].includes(raw)) return 'pitching_outs';
  if (['pitching_earnedruns', 'earnedruns', 'earnedrun'].includes(raw)) return 'pitching_earnedruns';
  if (['batting_rbi', 'rbi', 'rbis'].includes(raw)) return 'batting_rbi';
  if (['batting_basesonballs', 'batting_walks', 'battingwalks', 'walk', 'walks'].includes(raw)) return 'batting_basesonballs';
  if (['batting_homeruns', 'homerun', 'homeruns', 'home_run', 'home_runs'].includes(raw)) return 'batting_homeruns';

  return raw;
}

function pct(wins: number, total: number): string {
  return total > 0 ? `${((wins / total) * 100).toFixed(1)}%` : 'n/a';
}

function summarize(rows: NormalizedRow[]): { total: number; wins: number; losses: number; winPct: number } {
  const total = rows.length;
  const wins = rows.filter((row) => row.result === 'W').length;
  const losses = total - wins;
  return {
    total,
    wins,
    losses,
    winPct: total > 0 ? Number(((wins / total) * 100).toFixed(1)) : 0,
  };
}

async function main(): Promise<void> {
  const result = await pool.query<HistRow>(
    `SELECT prop_stat, direction, rec_odds, rec_line, model_confidence, result
     FROM rm_pick_clv
     WHERE league = 'mlb'
       AND pick_type = 'player_prop'
       AND result IN ('W', 'L')`,
  );

  const rows: NormalizedRow[] = result.rows.map((row) => ({
    stat: normalizeMlbBacktestStat(row.prop_stat),
    direction: String(row.direction || '').trim().toLowerCase(),
    odds: row.rec_odds,
    line: row.rec_line,
    confidencePct: row.model_confidence == null
      ? null
      : row.model_confidence <= 1
        ? row.model_confidence * 100
        : row.model_confidence,
    result: row.result,
  }));

  const baseline = summarize(rows);

  const rules: Rule[] = [
    {
      name: 'all_mlb_props',
      matches: () => true,
    },
    {
      name: 'unders_only',
      matches: (row) => row.direction === 'under',
    },
    {
      name: 'unders_max_plus_110',
      matches: (row) => row.direction === 'under'
        && (row.odds == null || row.odds <= 110),
    },
    {
      name: 'unders_max_plus_110_no_low_k_or_er_lines',
      matches: (row) => row.direction === 'under'
        && (row.odds == null || row.odds <= 110)
        && !(row.stat === 'pitching_strikeouts' && row.line != null && row.line < 4.5)
        && !(row.stat === 'pitching_earnedruns' && row.line != null && row.line < 1.5),
    },
    {
      name: 'pitcher_unders_only',
      matches: (row) => row.direction === 'under'
        && ['pitching_hits', 'pitching_strikeouts', 'pitching_basesonballs', 'pitching_outs', 'pitching_earnedruns'].includes(row.stat),
    },
    {
      name: 'pitcher_unders_max_plus_110',
      matches: (row) => row.direction === 'under'
        && ['pitching_hits', 'pitching_strikeouts', 'pitching_basesonballs', 'pitching_outs', 'pitching_earnedruns'].includes(row.stat)
        && (row.odds == null || row.odds <= 110),
    },
    {
      name: 'pitcher_unders_max_plus_110_no_low_k_or_er_lines',
      matches: (row) => row.direction === 'under'
        && ['pitching_hits', 'pitching_strikeouts', 'pitching_basesonballs', 'pitching_outs', 'pitching_earnedruns'].includes(row.stat)
        && (row.odds == null || row.odds <= 110)
        && !(row.stat === 'pitching_strikeouts' && row.line != null && row.line < 4.5)
        && !(row.stat === 'pitching_earnedruns' && row.line != null && row.line < 1.5),
    },
    {
      name: 'pitcher_unders_per_stat_lines',
      matches: (row) => row.direction === 'under'
        && (row.odds == null || row.odds <= 110)
        && (
          (row.stat === 'pitching_strikeouts' && row.line != null && row.line >= 4.5)
          || (row.stat === 'pitching_hits' && row.line != null && row.line >= 4.5)
          || (row.stat === 'pitching_basesonballs' && row.line === 1.5)
          || (row.stat === 'pitching_earnedruns' && row.line != null && row.line >= 2.5)
          || (row.stat === 'pitching_outs' && row.line != null)
        ),
    },
    {
      name: 'pitcher_unders_per_stat_lines_tighter_outs',
      matches: (row) => row.direction === 'under'
        && (row.odds == null || row.odds <= 110)
        && (
          (row.stat === 'pitching_strikeouts' && row.line != null && row.line >= 4.5)
          || (row.stat === 'pitching_hits' && row.line != null && row.line >= 4.5)
          || (row.stat === 'pitching_basesonballs' && row.line === 1.5)
          || (row.stat === 'pitching_earnedruns' && row.line != null && row.line >= 2.5)
          || (row.stat === 'pitching_outs' && row.line != null && row.line <= 15.5)
        ),
    },
  ];

  console.log(`MLB player-prop backtest rows: ${baseline.total}`);
  console.log(`Baseline: ${baseline.wins}-${baseline.losses} (${pct(baseline.wins, baseline.total)})`);

  for (const rule of rules) {
    const matched = rows.filter(rule.matches);
    const stats = summarize(matched);
    const coveragePct = baseline.total > 0 ? Number(((stats.total / baseline.total) * 100).toFixed(1)) : 0;
    const lift = Number((stats.winPct - baseline.winPct).toFixed(1));
    console.log(
      `${rule.name}: ${stats.wins}-${stats.losses} (${pct(stats.wins, stats.total)})`
      + ` | sample=${stats.total}`
      + ` | coverage=${coveragePct}%`
      + ` | lift=${lift >= 0 ? '+' : ''}${lift}pts`,
    );
  }
}

main()
  .catch((error) => {
    console.error('[mlb-prop-settings-backtest] failed', error);
    process.exitCode = 1;
  })
  .finally(async () => {
    await pool.end().catch(() => undefined);
  });