//! CSV card data importer for Strat-o-Matic card values.
//!
//! Ports the Python `api/importer.py` helpers and import functions.

use std::collections::HashMap;
use std::path::Path;
use std::sync::LazyLock;

use anyhow::Result;
use regex::Regex;
use sqlx::SqlitePool;

// ---------------------------------------------------------------------------
// Result types
// ---------------------------------------------------------------------------

/// Outcome of a single CSV import operation (batters or pitchers).
#[derive(Debug, Default)]
pub struct ImportResult {
    pub imported: usize,
    pub skipped: usize,
    pub errors: Vec<String>,
}

/// Combined result from importing both batter and pitcher card CSVs.
#[derive(Debug, Default)]
pub struct AllImportResult {
    pub batters: ImportResult,
    pub pitchers: ImportResult,
}

// ---------------------------------------------------------------------------
// Compiled regexes (stable LazyLock since Rust 1.80)
// ---------------------------------------------------------------------------

static RE_START: LazyLock<Regex> =
    LazyLock::new(|| Regex::new(r"S\((\d+)\*?\)").expect("valid regex"));

static RE_RELIEF: LazyLock<Regex> =
    LazyLock::new(|| Regex::new(r"R\((\d+)\)").expect("valid regex"));

static RE_CLOSE: LazyLock<Regex> =
    LazyLock::new(|| Regex::new(r"C\((\d+)\)").expect("valid regex"));

// ---------------------------------------------------------------------------
// Parse helpers
// ---------------------------------------------------------------------------

/// Safely parse a float from a string, returning `default` on failure or empty input.
pub fn parse_float(value: &str, default: f64) -> f64 {
    let trimmed = value.trim();
    if trimmed.is_empty() {
        return default;
    }
    trimmed.parse::<f64>().unwrap_or(default)
}

/// Safely parse an integer from a string, returning `default` on failure or empty input.
///
/// Parses as `f64` first so that values like `"5.0"` are handled correctly.
pub fn parse_int(value: &str, default: i32) -> i32 {
    let trimmed = value.trim();
    if trimmed.is_empty() {
        return default;
    }
    match trimmed.parse::<f64>() {
        Ok(f) => f as i32,
        Err(_) => default,
    }
}

/// Parse an endurance string like `"S(5) R(4)"` or `"R(1) C(6)"`.
///
/// Returns `(start, relief, close)` — any component may be `None` if absent.
/// Handles starred starters like `"S(5*)"`.
pub fn parse_endurance(value: &str) -> (Option<i32>, Option<i32>, Option<i32>) {
    if value.trim().is_empty() {
        return (None, None, None);
    }

    let start = RE_START
        .captures(value)
        .and_then(|c| c.get(1))
        .and_then(|m| m.as_str().parse::<i32>().ok());

    let relief = RE_RELIEF
        .captures(value)
        .and_then(|c| c.get(1))
        .and_then(|m| m.as_str().parse::<i32>().ok());

    let close = RE_CLOSE
        .captures(value)
        .and_then(|c| c.get(1))
        .and_then(|m| m.as_str().parse::<i32>().ok());

    (start, relief, close)
}

// ---------------------------------------------------------------------------
// Import functions
// ---------------------------------------------------------------------------

/// Import batter card data from a CSV file into the database.
///
/// For each row the function:
/// 1. Parses `player_id` — skips rows where it is 0 or missing.
/// 2. Verifies the player exists; records an error and continues if not.
/// 3. Updates `players.hand` when the CSV supplies "L", "R", or "S".
/// 4. Skips the row (incrementing `skipped`) when `update_existing` is false
///    and a `batter_cards` row already exists for this player.
/// 5. Inserts or replaces the batter card with all mapped fields.
///
/// All DB writes happen inside a single transaction.
pub async fn import_batter_cards(
    pool: &SqlitePool,
    csv_path: &Path,
    update_existing: bool,
) -> Result<ImportResult> {
    let mut result = ImportResult::default();

    let source = csv_path
        .file_name()
        .and_then(|n| n.to_str())
        .unwrap_or("unknown")
        .to_string();

    let mut rdr = csv::ReaderBuilder::new().has_headers(true).from_path(csv_path)?;

    // Build O(1) header → column-index lookup before iterating records.
    let headers = rdr.headers()?.clone();
    let header_index: HashMap<String, usize> =
        headers.iter().enumerate().map(|(i, h)| (h.to_string(), i)).collect();

    let mut tx = pool.begin().await?;

    for record_result in rdr.records() {
        let record = match record_result {
            Ok(r) => r,
            Err(e) => {
                result.errors.push(format!("CSV parse error: {e}"));
                continue;
            }
        };

        // Returns the field value for `name`, or "" if the column is absent.
        let get = |name: &str| -> &str {
            header_index.get(name).and_then(|&i| record.get(i)).unwrap_or("")
        };

        let player_id = parse_int(get("player_id"), 0);
        if player_id == 0 {
            continue;
        }
        let player_id = player_id as i64;

        // Lightweight existence check — no need to SELECT the full row.
        let player_exists: Option<i64> =
            sqlx::query_scalar("SELECT id FROM players WHERE id = ?")
                .bind(player_id)
                .fetch_optional(&mut *tx)
                .await?;

        if player_exists.is_none() {
            result.errors.push(format!(
                "Player {} ({}) not found in database",
                player_id,
                get("Name")
            ));
            continue;
        }

        // Update batting hand when the CSV provides a valid value.
        let hand = get("hand").trim();
        if matches!(hand, "L" | "R" | "S") {
            sqlx::query("UPDATE players SET hand = ? WHERE id = ?")
                .bind(hand)
                .bind(player_id)
                .execute(&mut *tx)
                .await?;
        }

        // Skip if card already exists and caller asked not to overwrite.
        if !update_existing {
            let existing: Option<i64> =
                sqlx::query_scalar("SELECT id FROM batter_cards WHERE player_id = ?")
                    .bind(player_id)
                    .fetch_optional(&mut *tx)
                    .await?;
            if existing.is_some() {
                result.skipped += 1;
                continue;
            }
        }

        // cArm and CA are alternate column names for catcher arm rating.
        let catcher_arm_str = {
            let v = get("cArm");
            if v.trim().is_empty() { get("CA") } else { v }
        };
        let catcher_arm: Option<i64> = if catcher_arm_str.trim().is_empty() {
            None
        } else {
            Some(parse_int(catcher_arm_str, 0) as i64)
        };

        // Helpers for optional DB fields.
        let opt_str = |v: &str| -> Option<String> {
            let t = v.trim();
            if t.is_empty() { None } else { Some(t.to_string()) }
        };
        let opt_float = |v: &str| -> Option<f64> {
            if v.trim().is_empty() { None } else { Some(parse_float(v, 0.0)) }
        };

        sqlx::query(
            "INSERT OR REPLACE INTO batter_cards (
                player_id,
                so_vlhp, bb_vlhp, hit_vlhp, ob_vlhp, tb_vlhp, hr_vlhp, dp_vlhp,
                bphr_vlhp, bp1b_vlhp,
                so_vrhp, bb_vrhp, hit_vrhp, ob_vrhp, tb_vrhp, hr_vrhp, dp_vrhp,
                bphr_vrhp, bp1b_vrhp,
                stealing, steal_rating, speed,
                bunt, hit_run, fielding,
                catcher_arm,
                rating_vl, rating_vr, rating_overall,
                imported_at, source
            ) VALUES (
                ?,
                ?, ?, ?, ?, ?, ?, ?,
                ?, ?,
                ?, ?, ?, ?, ?, ?, ?,
                ?, ?,
                ?, ?, ?,
                ?, ?, ?,
                ?,
                ?, ?, ?,
                CURRENT_TIMESTAMP, ?
            )",
        )
        .bind(player_id)
        // vs LHP
        .bind(parse_float(get("SO vlhp"), 0.0))
        .bind(parse_float(get("BB v lhp"), 0.0))
        .bind(parse_float(get("HIT v lhp"), 0.0))
        .bind(parse_float(get("OB v lhp"), 0.0))
        .bind(parse_float(get("TB v lhp"), 0.0))
        .bind(parse_float(get("HR v lhp"), 0.0))
        .bind(parse_float(get("DP v lhp"), 0.0))
        .bind(parse_float(get("BPHR v lhp"), 0.0))
        .bind(parse_float(get("BP1B v lhp"), 0.0))
        // vs RHP
        .bind(parse_float(get("SO v rhp"), 0.0))
        .bind(parse_float(get("BB v rhp"), 0.0))
        .bind(parse_float(get("HIT v rhp"), 0.0))
        .bind(parse_float(get("OB v rhp"), 0.0))
        .bind(parse_float(get("TB v rhp"), 0.0))
        .bind(parse_float(get("HR v rhp"), 0.0))
        .bind(parse_float(get("DP v rhp"), 0.0))
        .bind(parse_float(get("BPHR v rhp"), 0.0))
        .bind(parse_float(get("BP1B v rhp"), 0.0))
        // Running game
        .bind(opt_str(get("STEALING")))
        .bind(opt_str(get("STL")))
        .bind(parse_int(get("SPD"), 10) as i64)
        // Batting extras
        .bind(opt_str(get("B")))
        .bind(opt_str(get("H")))
        // Fielding
        .bind(opt_str(get("FIELDING")))
        // Catcher
        .bind(catcher_arm)
        // Pre-calculated ratings from the spreadsheet
        .bind(opt_float(get("vL")))
        .bind(opt_float(get("vR")))
        .bind(opt_float(get("Total")))
        // Metadata
        .bind(&source)
        .execute(&mut *tx)
        .await?;

        result.imported += 1;
    }

    tx.commit().await?;
    Ok(result)
}

/// Import pitcher card data from a CSV file into the database.
///
/// For each row the function:
/// 1. Parses `player_id` — skips rows where it is 0 or missing.
/// 2. Verifies the player exists; records an error and continues if not.
/// 3. Updates `players.hand` when the CSV supplies "L" or "R" (no switch for pitchers).
/// 4. Skips the row (incrementing `skipped`) when `update_existing` is false
///    and a `pitcher_cards` row already exists for this player.
/// 5. Parses endurance from the "ENDURANCE"/"cleanEndur" column first, then
///    overrides individual components if "SP"/"RP"/"CP" columns are present.
/// 6. Inserts or replaces the pitcher card with all mapped fields.
///
/// Note: The CSV uses "vlhp" in column names (vs Left-Handed Pitchers), but the
/// pitcher card DB fields use "vlhb" (vs Left-Handed Batters). This naming
/// inconsistency is an artifact of the source spreadsheet.
///
/// All DB writes happen inside a single transaction.
pub async fn import_pitcher_cards(
    pool: &SqlitePool,
    csv_path: &Path,
    update_existing: bool,
) -> Result<ImportResult> {
    let mut result = ImportResult::default();

    let source = csv_path
        .file_name()
        .and_then(|n| n.to_str())
        .unwrap_or("unknown")
        .to_string();

    let mut rdr = csv::ReaderBuilder::new().has_headers(true).from_path(csv_path)?;

    let headers = rdr.headers()?.clone();
    let header_index: HashMap<String, usize> =
        headers.iter().enumerate().map(|(i, h)| (h.to_string(), i)).collect();

    let mut tx = pool.begin().await?;

    for record_result in rdr.records() {
        let record = match record_result {
            Ok(r) => r,
            Err(e) => {
                result.errors.push(format!("CSV parse error: {e}"));
                continue;
            }
        };

        let get = |name: &str| -> &str {
            header_index.get(name).and_then(|&i| record.get(i)).unwrap_or("")
        };

        let player_id = parse_int(get("player_id"), 0);
        if player_id == 0 {
            continue;
        }
        let player_id = player_id as i64;

        let player_exists: Option<i64> =
            sqlx::query_scalar("SELECT id FROM players WHERE id = ?")
                .bind(player_id)
                .fetch_optional(&mut *tx)
                .await?;

        if player_exists.is_none() {
            result.errors.push(format!(
                "Player {} ({}) not found in database",
                player_id,
                get("Name")
            ));
            continue;
        }

        // Pitchers only throw L or R — no switch pitchers.
        let hand = get("hand").trim();
        if matches!(hand, "L" | "R") {
            sqlx::query("UPDATE players SET hand = ? WHERE id = ?")
                .bind(hand)
                .bind(player_id)
                .execute(&mut *tx)
                .await?;
        }

        if !update_existing {
            let existing: Option<i64> =
                sqlx::query_scalar("SELECT id FROM pitcher_cards WHERE player_id = ?")
                    .bind(player_id)
                    .fetch_optional(&mut *tx)
                    .await?;
            if existing.is_some() {
                result.skipped += 1;
                continue;
            }
        }

        // Parse endurance from the combined column first, then override with
        // individual SP/RP/CP columns if they are non-empty.
        let endur_raw = {
            let v = get("ENDURANCE");
            if v.trim().is_empty() { get("cleanEndur") } else { v }
        };
        let (mut endur_start, mut endur_relief, mut endur_close) = parse_endurance(endur_raw);

        let sp_raw = get("SP").trim();
        if !sp_raw.is_empty() {
            endur_start = Some(parse_int(sp_raw, 0));
        }
        let rp_raw = get("RP").trim();
        if !rp_raw.is_empty() {
            endur_relief = Some(parse_int(rp_raw, 0));
        }
        let cp_raw = get("CP").trim();
        if !cp_raw.is_empty() {
            endur_close = Some(parse_int(cp_raw, 0));
        }

        let opt_str = |v: &str| -> Option<String> {
            let t = v.trim();
            if t.is_empty() { None } else { Some(t.to_string()) }
        };
        let opt_float = |v: &str| -> Option<f64> {
            if v.trim().is_empty() { None } else { Some(parse_float(v, 0.0)) }
        };
        let opt_int = |v: &str| -> Option<i64> {
            if v.trim().is_empty() { None } else { Some(parse_int(v, 0) as i64) }
        };

        sqlx::query(
            "INSERT OR REPLACE INTO pitcher_cards (
                player_id,
                so_vlhb, bb_vlhb, hit_vlhb, ob_vlhb, tb_vlhb, hr_vlhb, dp_vlhb,
                bphr_vlhb, bp1b_vlhb,
                so_vrhb, bb_vrhb, hit_vrhb, ob_vrhb, tb_vrhb, hr_vrhb, dp_vrhb,
                bphr_vrhb, bp1b_vrhb,
                hold_rating,
                endurance_start, endurance_relief, endurance_close,
                fielding_range, fielding_error,
                wild_pitch, balk,
                batting_rating,
                rating_vlhb, rating_vrhb, rating_overall,
                imported_at, source
            ) VALUES (
                ?,
                ?, ?, ?, ?, ?, ?, ?,
                ?, ?,
                ?, ?, ?, ?, ?, ?, ?,
                ?, ?,
                ?,
                ?, ?, ?,
                ?, ?,
                ?, ?,
                ?,
                ?, ?, ?,
                CURRENT_TIMESTAMP, ?
            )",
        )
        .bind(player_id)
        // vs Left-Handed Batters (CSV uses "vlhp" suffix, DB uses "vlhb")
        .bind(parse_float(get("SO vlhp"), 0.0))
        .bind(parse_float(get("BB vlhp"), 0.0))
        .bind(parse_float(get("HIT v lhp"), 0.0))
        .bind(parse_float(get("OB v lhp"), 0.0))
        .bind(parse_float(get("TB v lhp"), 0.0))
        .bind(parse_float(get("HR v lhp"), 0.0))
        .bind(parse_float(get("DP vlhp"), 0.0)) // no space after DP
        .bind(parse_float(get("BPHR vL"), 0.0)) // different format from batter
        .bind(parse_float(get("BP1B v lhp"), 0.0))
        // vs Right-Handed Batters
        .bind(parse_float(get("SO v rhp"), 0.0))
        .bind(parse_float(get("BB v rhp"), 0.0))
        .bind(parse_float(get("HIT v rhp"), 0.0))
        .bind(parse_float(get("OB v rhp"), 0.0))
        .bind(parse_float(get("TB v rhp"), 0.0))
        .bind(parse_float(get("HR v rhp"), 0.0))
        .bind(parse_float(get("DP v rhp"), 0.0))
        .bind(parse_float(get("BPHR vR"), 0.0)) // different format from batter
        .bind(parse_float(get("BP1b v rhp"), 0.0)) // lowercase 'b'
        // Pitcher attributes
        .bind(parse_int(get("HO"), 0) as i64)
        .bind(endur_start.map(|v| v as i64))
        .bind(endur_relief.map(|v| v as i64))
        .bind(endur_close.map(|v| v as i64))
        .bind(opt_int(get("Range")))
        .bind(opt_int(get("Error")))
        .bind(parse_int(get("WP"), 0) as i64)
        .bind(parse_int(get("BK"), 0) as i64)
        .bind(opt_str(get("BAT-B")))
        // Pre-calculated ratings from the spreadsheet
        .bind(opt_float(get("vL")))
        .bind(opt_float(get("vR")))
        .bind(opt_float(get("Total")))
        // Metadata
        .bind(&source)
        .execute(&mut *tx)
        .await?;

        result.imported += 1;
    }

    tx.commit().await?;
    Ok(result)
}

/// Import both batter and pitcher card CSVs in sequence.
///
/// Default paths are `docs/sheets_export/BatterCalcs.csv` and
/// `docs/sheets_export/PitcherCalcs.csv`. Pass `None` to use the defaults.
pub async fn import_all_cards(
    pool: &SqlitePool,
    batter_csv: Option<&Path>,
    pitcher_csv: Option<&Path>,
    update_existing: bool,
) -> Result<AllImportResult> {
    let default_batter = Path::new("docs/sheets_export/BatterCalcs.csv");
    let default_pitcher = Path::new("docs/sheets_export/PitcherCalcs.csv");

    let batter_path = batter_csv.unwrap_or(default_batter);
    let pitcher_path = pitcher_csv.unwrap_or(default_pitcher);

    // Import each CSV independently — if one is missing, still attempt the other.
    let batters = match import_batter_cards(pool, batter_path, update_existing).await {
        Ok(result) => result,
        Err(e) => {
            eprintln!("Batter CSV import failed ({}): {}", batter_path.display(), e);
            ImportResult { imported: 0, skipped: 0, errors: vec![format!("Batter import failed: {e}")] }
        }
    };
    let pitchers = match import_pitcher_cards(pool, pitcher_path, update_existing).await {
        Ok(result) => result,
        Err(e) => {
            eprintln!("Pitcher CSV import failed ({}): {}", pitcher_path.display(), e);
            ImportResult { imported: 0, skipped: 0, errors: vec![format!("Pitcher import failed: {e}")] }
        }
    };

    // Invalidate league stats cache and rebuild score cache after card import.
    // Only rebuild if at least one card was actually imported.
    if batters.imported > 0 || pitchers.imported > 0 {
        crate::calc::league_stats::clear_league_stats_cache();
        if let Err(e) = crate::calc::score_cache::rebuild_score_cache(pool).await {
            eprintln!("Score cache rebuild failed after import: {e}");
        }
    }

    Ok(AllImportResult { batters, pitchers })
}

// ---------------------------------------------------------------------------
// Tests
// ---------------------------------------------------------------------------

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

    // --- parse_float ---

    /// Normal float string should parse correctly.
    #[test]
    fn parse_float_normal() {
        assert_eq!(parse_float("3.14", 0.0), 3.14);
    }

    /// Integer-looking string should parse as float.
    #[test]
    fn parse_float_integer_string() {
        assert_eq!(parse_float("5", 0.0), 5.0);
    }

    /// Empty string returns the default.
    #[test]
    fn parse_float_empty() {
        assert_eq!(parse_float("", 99.0), 99.0);
    }

    /// Whitespace-only string returns the default.
    #[test]
    fn parse_float_whitespace() {
        assert_eq!(parse_float("   ", -1.0), -1.0);
    }

    /// Non-numeric string returns the default.
    #[test]
    fn parse_float_unparseable() {
        assert_eq!(parse_float("N/A", 0.0), 0.0);
    }

    // --- parse_int ---

    /// Normal integer string should parse correctly.
    #[test]
    fn parse_int_normal() {
        assert_eq!(parse_int("7", 0), 7);
    }

    /// Float-formatted integer like "5.0" should truncate to 5.
    #[test]
    fn parse_int_float_string() {
        assert_eq!(parse_int("5.0", 0), 5);
    }

    /// Float with fractional part truncates toward zero.
    #[test]
    fn parse_int_float_fractional() {
        assert_eq!(parse_int("3.9", 0), 3);
    }

    /// Empty string returns the default.
    #[test]
    fn parse_int_empty() {
        assert_eq!(parse_int("", 42), 42);
    }

    /// Whitespace-only string returns the default.
    #[test]
    fn parse_int_whitespace() {
        assert_eq!(parse_int("  ", -1), -1);
    }

    /// Non-numeric string returns the default.
    #[test]
    fn parse_int_unparseable() {
        assert_eq!(parse_int("abc", 0), 0);
    }

    // --- parse_endurance ---

    /// Start-only endurance string.
    #[test]
    fn parse_endurance_start_only() {
        assert_eq!(parse_endurance("S(5)"), (Some(5), None, None));
    }

    /// Relief-only endurance string.
    #[test]
    fn parse_endurance_relief_only() {
        assert_eq!(parse_endurance("R(4)"), (None, Some(4), None));
    }

    /// Close-only endurance string.
    #[test]
    fn parse_endurance_close_only() {
        assert_eq!(parse_endurance("C(6)"), (None, None, Some(6)));
    }

    /// Start + Relief combination.
    #[test]
    fn parse_endurance_start_relief() {
        assert_eq!(parse_endurance("S(5) R(4)"), (Some(5), Some(4), None));
    }

    /// Relief + Close combination.
    #[test]
    fn parse_endurance_relief_close() {
        assert_eq!(parse_endurance("R(1) C(6)"), (None, Some(1), Some(6)));
    }

    /// All three components present.
    #[test]
    fn parse_endurance_all() {
        assert_eq!(parse_endurance("S(5) R(3) C(6)"), (Some(5), Some(3), Some(6)));
    }

    /// Starred starter notation "S(5*)" should match start = 5.
    #[test]
    fn parse_endurance_starred_starter() {
        assert_eq!(parse_endurance("S(5*)"), (Some(5), None, None));
    }

    /// Starred starter with relief component.
    #[test]
    fn parse_endurance_starred_with_relief() {
        assert_eq!(parse_endurance("S(5*) R(3) C(6)"), (Some(5), Some(3), Some(6)));
    }

    /// Empty string returns all None.
    #[test]
    fn parse_endurance_empty() {
        assert_eq!(parse_endurance(""), (None, None, None));
    }

    /// Whitespace-only returns all None.
    #[test]
    fn parse_endurance_whitespace() {
        assert_eq!(parse_endurance("   "), (None, None, None));
    }
}
