use anyhow::Result;
use sqlx::SqlitePool;
use std::sync::{Mutex, OnceLock};

use crate::db::models::{BatterCard, PitcherCard};

// =============================================================================
// In-memory cache
// =============================================================================

static BATTER_CACHE: OnceLock<Mutex<Option<BatterLeagueStats>>> = OnceLock::new();
static PITCHER_CACHE: OnceLock<Mutex<Option<PitcherLeagueStats>>> = OnceLock::new();

/// Distribution statistics for a single stat across the league.
#[derive(Debug, Clone, Copy)]
pub struct StatDistribution {
    pub avg: f64,
    pub stdev: f64,
}

/// League-wide averages and standard deviations for batter card stats.
#[derive(Debug, Clone)]
pub struct BatterLeagueStats {
    // vs Left-Handed Pitchers
    pub so_vlhp: StatDistribution,
    pub bb_vlhp: StatDistribution,
    pub hit_vlhp: StatDistribution,
    pub ob_vlhp: StatDistribution,
    pub tb_vlhp: StatDistribution,
    pub hr_vlhp: StatDistribution,
    pub dp_vlhp: StatDistribution,
    pub bphr_vlhp: StatDistribution,
    pub bp1b_vlhp: StatDistribution,
    // vs Right-Handed Pitchers
    pub so_vrhp: StatDistribution,
    pub bb_vrhp: StatDistribution,
    pub hit_vrhp: StatDistribution,
    pub ob_vrhp: StatDistribution,
    pub tb_vrhp: StatDistribution,
    pub hr_vrhp: StatDistribution,
    pub dp_vrhp: StatDistribution,
    pub bphr_vrhp: StatDistribution,
    pub bp1b_vrhp: StatDistribution,
}

impl Default for BatterLeagueStats {
    fn default() -> Self {
        let d = StatDistribution { avg: 0.0, stdev: 1.0 };
        Self {
            so_vlhp: d.clone(),
            bb_vlhp: d.clone(),
            hit_vlhp: d.clone(),
            ob_vlhp: d.clone(),
            tb_vlhp: d.clone(),
            hr_vlhp: d.clone(),
            dp_vlhp: d.clone(),
            bphr_vlhp: d.clone(),
            bp1b_vlhp: d.clone(),
            so_vrhp: d.clone(),
            bb_vrhp: d.clone(),
            hit_vrhp: d.clone(),
            ob_vrhp: d.clone(),
            tb_vrhp: d.clone(),
            hr_vrhp: d.clone(),
            dp_vrhp: d.clone(),
            bphr_vrhp: d.clone(),
            bp1b_vrhp: d,
        }
    }
}

/// League-wide averages and standard deviations for pitcher card stats.
#[derive(Debug, Clone)]
pub struct PitcherLeagueStats {
    // vs Left-Handed Batters
    pub so_vlhb: StatDistribution,
    pub bb_vlhb: StatDistribution,
    pub hit_vlhb: StatDistribution,
    pub ob_vlhb: StatDistribution,
    pub tb_vlhb: StatDistribution,
    pub hr_vlhb: StatDistribution,
    pub dp_vlhb: StatDistribution,
    pub bphr_vlhb: StatDistribution,
    pub bp1b_vlhb: StatDistribution,
    // vs Right-Handed Batters
    pub so_vrhb: StatDistribution,
    pub bb_vrhb: StatDistribution,
    pub hit_vrhb: StatDistribution,
    pub ob_vrhb: StatDistribution,
    pub tb_vrhb: StatDistribution,
    pub hr_vrhb: StatDistribution,
    pub dp_vrhb: StatDistribution,
    pub bphr_vrhb: StatDistribution,
    pub bp1b_vrhb: StatDistribution,
}

impl Default for PitcherLeagueStats {
    fn default() -> Self {
        let d = StatDistribution { avg: 0.0, stdev: 1.0 };
        Self {
            so_vlhb: d.clone(),
            bb_vlhb: d.clone(),
            hit_vlhb: d.clone(),
            ob_vlhb: d.clone(),
            tb_vlhb: d.clone(),
            hr_vlhb: d.clone(),
            dp_vlhb: d.clone(),
            bphr_vlhb: d.clone(),
            bp1b_vlhb: d.clone(),
            so_vrhb: d.clone(),
            bb_vrhb: d.clone(),
            hit_vrhb: d.clone(),
            ob_vrhb: d.clone(),
            tb_vrhb: d.clone(),
            hr_vrhb: d.clone(),
            dp_vrhb: d.clone(),
            bphr_vrhb: d.clone(),
            bp1b_vrhb: d,
        }
    }
}

/// Calculate average and sample standard deviation for a slice of values.
///
/// CRITICAL MATH — two different means are used:
/// - `avg` = mean of NON-ZERO values only (matches spreadsheet AVERAGEIF behavior).
///   Falls back to 0.0 if fewer than 2 non-zero values exist.
/// - `stdev` = sample standard deviation (Bessel's correction, N-1 denominator) of
///   ALL values INCLUDING zeros (even when avg excludes them).
///   Falls back to 1.0 if fewer than 2 total values, or if computed stdev is 0.0.
///
/// The stdev variance sum uses mean_all (mean of all values), NOT avg.
pub fn calc_distribution(values: &[f64]) -> StatDistribution {
    let non_zero: Vec<f64> = values.iter().copied().filter(|&v| v > 0.0).collect();
    let avg = if non_zero.len() >= 2 {
        non_zero.iter().sum::<f64>() / non_zero.len() as f64
    } else {
        0.0
    };

    let n = values.len();
    if n < 2 {
        return StatDistribution { avg, stdev: 1.0 };
    }

    // stdev uses mean of ALL values (including zeros), per Python reference
    let mean_all = values.iter().sum::<f64>() / n as f64;
    let variance = values.iter().map(|&x| (x - mean_all).powi(2)).sum::<f64>() / (n - 1) as f64;
    let stdev_raw = variance.sqrt();
    let stdev = if stdev_raw == 0.0 { 1.0 } else { stdev_raw };

    StatDistribution { avg, stdev }
}

// =============================================================================
// DB computation functions
// =============================================================================

/// Compute league-wide batter stat distributions from all rows in `batter_cards`.
/// Returns `BatterLeagueStats::default()` if the table is empty.
pub async fn calculate_batter_league_stats(pool: &SqlitePool) -> Result<BatterLeagueStats> {
    let cards: Vec<BatterCard> = sqlx::query_as("SELECT * FROM batter_cards")
        .fetch_all(pool)
        .await?;

    if cards.is_empty() {
        return Ok(BatterLeagueStats::default());
    }

    Ok(BatterLeagueStats {
        so_vlhp: calc_distribution(&cards.iter().map(|c| c.so_vlhp).collect::<Vec<_>>()),
        bb_vlhp: calc_distribution(&cards.iter().map(|c| c.bb_vlhp).collect::<Vec<_>>()),
        hit_vlhp: calc_distribution(&cards.iter().map(|c| c.hit_vlhp).collect::<Vec<_>>()),
        ob_vlhp: calc_distribution(&cards.iter().map(|c| c.ob_vlhp).collect::<Vec<_>>()),
        tb_vlhp: calc_distribution(&cards.iter().map(|c| c.tb_vlhp).collect::<Vec<_>>()),
        hr_vlhp: calc_distribution(&cards.iter().map(|c| c.hr_vlhp).collect::<Vec<_>>()),
        dp_vlhp: calc_distribution(&cards.iter().map(|c| c.dp_vlhp).collect::<Vec<_>>()),
        bphr_vlhp: calc_distribution(&cards.iter().map(|c| c.bphr_vlhp).collect::<Vec<_>>()),
        bp1b_vlhp: calc_distribution(&cards.iter().map(|c| c.bp1b_vlhp).collect::<Vec<_>>()),
        so_vrhp: calc_distribution(&cards.iter().map(|c| c.so_vrhp).collect::<Vec<_>>()),
        bb_vrhp: calc_distribution(&cards.iter().map(|c| c.bb_vrhp).collect::<Vec<_>>()),
        hit_vrhp: calc_distribution(&cards.iter().map(|c| c.hit_vrhp).collect::<Vec<_>>()),
        ob_vrhp: calc_distribution(&cards.iter().map(|c| c.ob_vrhp).collect::<Vec<_>>()),
        tb_vrhp: calc_distribution(&cards.iter().map(|c| c.tb_vrhp).collect::<Vec<_>>()),
        hr_vrhp: calc_distribution(&cards.iter().map(|c| c.hr_vrhp).collect::<Vec<_>>()),
        dp_vrhp: calc_distribution(&cards.iter().map(|c| c.dp_vrhp).collect::<Vec<_>>()),
        bphr_vrhp: calc_distribution(&cards.iter().map(|c| c.bphr_vrhp).collect::<Vec<_>>()),
        bp1b_vrhp: calc_distribution(&cards.iter().map(|c| c.bp1b_vrhp).collect::<Vec<_>>()),
    })
}

/// Compute league-wide pitcher stat distributions from all rows in `pitcher_cards`.
/// Returns `PitcherLeagueStats::default()` if the table is empty.
pub async fn calculate_pitcher_league_stats(pool: &SqlitePool) -> Result<PitcherLeagueStats> {
    let cards: Vec<PitcherCard> = sqlx::query_as("SELECT * FROM pitcher_cards")
        .fetch_all(pool)
        .await?;

    if cards.is_empty() {
        return Ok(PitcherLeagueStats::default());
    }

    Ok(PitcherLeagueStats {
        so_vlhb: calc_distribution(&cards.iter().map(|c| c.so_vlhb).collect::<Vec<_>>()),
        bb_vlhb: calc_distribution(&cards.iter().map(|c| c.bb_vlhb).collect::<Vec<_>>()),
        hit_vlhb: calc_distribution(&cards.iter().map(|c| c.hit_vlhb).collect::<Vec<_>>()),
        ob_vlhb: calc_distribution(&cards.iter().map(|c| c.ob_vlhb).collect::<Vec<_>>()),
        tb_vlhb: calc_distribution(&cards.iter().map(|c| c.tb_vlhb).collect::<Vec<_>>()),
        hr_vlhb: calc_distribution(&cards.iter().map(|c| c.hr_vlhb).collect::<Vec<_>>()),
        dp_vlhb: calc_distribution(&cards.iter().map(|c| c.dp_vlhb).collect::<Vec<_>>()),
        bphr_vlhb: calc_distribution(&cards.iter().map(|c| c.bphr_vlhb).collect::<Vec<_>>()),
        bp1b_vlhb: calc_distribution(&cards.iter().map(|c| c.bp1b_vlhb).collect::<Vec<_>>()),
        so_vrhb: calc_distribution(&cards.iter().map(|c| c.so_vrhb).collect::<Vec<_>>()),
        bb_vrhb: calc_distribution(&cards.iter().map(|c| c.bb_vrhb).collect::<Vec<_>>()),
        hit_vrhb: calc_distribution(&cards.iter().map(|c| c.hit_vrhb).collect::<Vec<_>>()),
        ob_vrhb: calc_distribution(&cards.iter().map(|c| c.ob_vrhb).collect::<Vec<_>>()),
        tb_vrhb: calc_distribution(&cards.iter().map(|c| c.tb_vrhb).collect::<Vec<_>>()),
        hr_vrhb: calc_distribution(&cards.iter().map(|c| c.hr_vrhb).collect::<Vec<_>>()),
        dp_vrhb: calc_distribution(&cards.iter().map(|c| c.dp_vrhb).collect::<Vec<_>>()),
        bphr_vrhb: calc_distribution(&cards.iter().map(|c| c.bphr_vrhb).collect::<Vec<_>>()),
        bp1b_vrhb: calc_distribution(&cards.iter().map(|c| c.bp1b_vrhb).collect::<Vec<_>>()),
    })
}

// =============================================================================
// Cached accessors
// =============================================================================

/// Return cached batter league stats, computing and caching on first call.
pub async fn get_batter_league_stats(pool: &SqlitePool) -> Result<BatterLeagueStats> {
    let cache = BATTER_CACHE.get_or_init(|| Mutex::new(None));
    {
        let guard = cache.lock().unwrap();
        if let Some(ref stats) = *guard {
            return Ok(stats.clone());
        }
    }
    let stats = calculate_batter_league_stats(pool).await?;
    *cache.lock().unwrap() = Some(stats.clone());
    Ok(stats)
}

/// Return cached pitcher league stats, computing and caching on first call.
pub async fn get_pitcher_league_stats(pool: &SqlitePool) -> Result<PitcherLeagueStats> {
    let cache = PITCHER_CACHE.get_or_init(|| Mutex::new(None));
    {
        let guard = cache.lock().unwrap();
        if let Some(ref stats) = *guard {
            return Ok(stats.clone());
        }
    }
    let stats = calculate_pitcher_league_stats(pool).await?;
    *cache.lock().unwrap() = Some(stats.clone());
    Ok(stats)
}

/// Invalidate both in-memory league stats caches (e.g. after a card import).
pub fn clear_league_stats_cache() {
    if let Some(cache) = BATTER_CACHE.get() {
        *cache.lock().unwrap() = None;
    }
    if let Some(cache) = PITCHER_CACHE.get() {
        *cache.lock().unwrap() = None;
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    /// Helper: assert two f64s are approximately equal within a tolerance.
    fn approx_eq(a: f64, b: f64, eps: f64) -> bool {
        (a - b).abs() < eps
    }

    /// Empty slice should return the safe default: avg=0.0, stdev=1.0.
    /// This ensures callers that produce no data don't panic or divide by zero.
    #[test]
    fn test_empty_slice() {
        let dist = calc_distribution(&[]);
        assert_eq!(dist.avg, 0.0);
        assert_eq!(dist.stdev, 1.0);
    }

    /// Single value has < 2 non-zero entries AND < 2 total entries, so both
    /// avg and stdev fall back to their safe defaults.
    #[test]
    fn test_single_value() {
        let dist = calc_distribution(&[5.0]);
        assert_eq!(dist.avg, 0.0, "< 2 non-zero values → avg=0.0");
        assert_eq!(dist.stdev, 1.0, "< 2 total values → stdev=1.0");
    }

    /// All-zeros slice: no non-zero values → avg=0.0. The stdev of [0,0,...] is
    /// also 0.0, which triggers the zero-stdev fallback to 1.0.
    #[test]
    fn test_all_zeros() {
        let dist = calc_distribution(&[0.0, 0.0, 0.0]);
        assert_eq!(dist.avg, 0.0);
        assert_eq!(dist.stdev, 1.0);
    }

    /// [4.0, 6.0]: avg of non-zeros = 5.0; sample stdev = sqrt(((4-5)²+(6-5)²)/1) = sqrt(2).
    #[test]
    fn test_two_nonzero_values() {
        let dist = calc_distribution(&[4.0, 6.0]);
        assert!(approx_eq(dist.avg, 5.0, 1e-10), "avg={}", dist.avg);
        let expected_stdev = 2.0_f64.sqrt();
        assert!(
            approx_eq(dist.stdev, expected_stdev, 1e-10),
            "stdev={} expected={}",
            dist.stdev,
            expected_stdev
        );
    }

    /// [0.0, 4.0, 6.0]:
    /// - avg uses only non-zeros [4.0, 6.0] → avg = 5.0
    /// - stdev uses ALL values [0.0, 4.0, 6.0] with mean_all = 10/3 ≈ 3.333...
    ///   variance = ((0-3.333)²+(4-3.333)²+(6-3.333)²) / 2
    ///            = (11.111 + 0.444 + 7.111) / 2 = 18.667 / 2 = 9.333...
    ///   stdev = sqrt(9.333...) ≈ 3.0551
    #[test]
    fn test_mixed_zeros_and_nonzeros() {
        let dist = calc_distribution(&[0.0, 4.0, 6.0]);
        assert!(approx_eq(dist.avg, 5.0, 1e-10), "avg={}", dist.avg);

        // Compute expected stdev manually
        let mean_all = 10.0 / 3.0;
        let variance =
            ((0.0_f64 - mean_all).powi(2) + (4.0_f64 - mean_all).powi(2) + (6.0_f64 - mean_all).powi(2))
                / 2.0;
        let expected_stdev = variance.sqrt();
        assert!(
            approx_eq(dist.stdev, expected_stdev, 1e-10),
            "stdev={} expected={}",
            dist.stdev,
            expected_stdev
        );
    }

    /// [3.0, 3.0, 3.0]: all values equal → sample stdev = 0.0 → fallback to 1.0.
    /// avg = 3.0 (non-zero mean).
    #[test]
    fn test_zero_stdev_fallback() {
        let dist = calc_distribution(&[3.0, 3.0, 3.0]);
        assert_eq!(dist.avg, 3.0);
        assert_eq!(dist.stdev, 1.0, "zero stdev must fall back to 1.0");
    }

    /// BatterLeagueStats::default() should have all fields set to avg=0.0, stdev=1.0.
    #[test]
    fn test_batter_league_stats_default() {
        let stats = BatterLeagueStats::default();
        assert_eq!(stats.so_vlhp.avg, 0.0);
        assert_eq!(stats.so_vlhp.stdev, 1.0);
        assert_eq!(stats.bp1b_vrhp.avg, 0.0);
        assert_eq!(stats.bp1b_vrhp.stdev, 1.0);
    }

    /// PitcherLeagueStats::default() should have all fields set to avg=0.0, stdev=1.0.
    #[test]
    fn test_pitcher_league_stats_default() {
        let stats = PitcherLeagueStats::default();
        assert_eq!(stats.so_vlhb.avg, 0.0);
        assert_eq!(stats.so_vlhb.stdev, 1.0);
        assert_eq!(stats.bp1b_vrhb.avg, 0.0);
        assert_eq!(stats.bp1b_vrhb.stdev, 1.0);
    }
}
