use anyhow::Result;
use sqlx::SqlitePool;
use std::collections::HashMap;

use super::models::{
    BatterCard, Lineup, MatchupCache, PitcherCard, Player, Roster, StandardizedScoreCache,
    SyncStatus, Team,
};

// =============================================================================
// Team Queries
// =============================================================================

pub async fn get_all_teams(pool: &SqlitePool, season: i64, active_only: bool) -> Result<Vec<Team>> {
    let teams = if active_only {
        sqlx::query_as::<_, Team>(
            "SELECT * FROM teams \
             WHERE season = ? \
               AND abbrev NOT LIKE '%IL' \
               AND abbrev NOT LIKE '%MiL' \
             ORDER BY abbrev",
        )
        .bind(season)
        .fetch_all(pool)
        .await?
    } else {
        sqlx::query_as::<_, Team>("SELECT * FROM teams WHERE season = ? ORDER BY abbrev")
            .bind(season)
            .fetch_all(pool)
            .await?
    };
    Ok(teams)
}

pub async fn get_team_by_abbrev(
    pool: &SqlitePool,
    abbrev: &str,
    season: i64,
) -> Result<Option<Team>> {
    let team = sqlx::query_as::<_, Team>(
        "SELECT * FROM teams WHERE abbrev = ? AND season = ?",
    )
    .bind(abbrev)
    .bind(season)
    .fetch_optional(pool)
    .await?;
    Ok(team)
}

pub async fn get_team_by_id(pool: &SqlitePool, team_id: i64) -> Result<Option<Team>> {
    let team = sqlx::query_as::<_, Team>("SELECT * FROM teams WHERE id = ?")
        .bind(team_id)
        .fetch_optional(pool)
        .await?;
    Ok(team)
}

// =============================================================================
// Player Queries
// =============================================================================

pub async fn get_players_by_team(pool: &SqlitePool, team_id: i64) -> Result<Vec<Player>> {
    let players = sqlx::query_as::<_, Player>(
        "SELECT * FROM players WHERE team_id = ? ORDER BY name",
    )
    .bind(team_id)
    .fetch_all(pool)
    .await?;
    Ok(players)
}

pub async fn get_player_by_id(pool: &SqlitePool, player_id: i64) -> Result<Option<Player>> {
    let player = sqlx::query_as::<_, Player>("SELECT * FROM players WHERE id = ?")
        .bind(player_id)
        .fetch_optional(pool)
        .await?;
    Ok(player)
}

pub async fn get_player_by_name(
    pool: &SqlitePool,
    name: &str,
    season: i64,
) -> Result<Option<Player>> {
    let player = sqlx::query_as::<_, Player>(
        "SELECT * FROM players WHERE LOWER(name) = LOWER(?) AND season = ?",
    )
    .bind(name)
    .bind(season)
    .fetch_optional(pool)
    .await?;
    Ok(player)
}

pub async fn search_players(
    pool: &SqlitePool,
    query_str: &str,
    season: i64,
    limit: i64,
) -> Result<Vec<Player>> {
    let pattern = format!("%{}%", query_str);
    let players = sqlx::query_as::<_, Player>(
        "SELECT * FROM players WHERE name LIKE ? AND season = ? ORDER BY name LIMIT ?",
    )
    .bind(pattern)
    .bind(season)
    .bind(limit)
    .fetch_all(pool)
    .await?;
    Ok(players)
}

pub async fn get_pitchers(
    pool: &SqlitePool,
    team_id: Option<i64>,
    season: Option<i64>,
) -> Result<Vec<Player>> {
    let mut sql = String::from(
        "SELECT * FROM players \
         WHERE (pos_1 IN ('SP', 'RP', 'CP') OR pos_2 IN ('SP', 'RP', 'CP'))",
    );
    if team_id.is_some() {
        sql.push_str(" AND team_id = ?");
    }
    if season.is_some() {
        sql.push_str(" AND season = ?");
    }
    sql.push_str(" ORDER BY name");

    let mut query = sqlx::query_as::<_, Player>(&sql);
    if let Some(tid) = team_id {
        query = query.bind(tid);
    }
    if let Some(s) = season {
        query = query.bind(s);
    }
    Ok(query.fetch_all(pool).await?)
}

pub async fn get_batters(
    pool: &SqlitePool,
    team_id: Option<i64>,
    season: Option<i64>,
) -> Result<Vec<Player>> {
    let mut sql = String::from(
        "SELECT * FROM players \
         WHERE pos_1 IN ('C', '1B', '2B', '3B', 'SS', 'LF', 'CF', 'RF', 'DH')",
    );
    if team_id.is_some() {
        sql.push_str(" AND team_id = ?");
    }
    if season.is_some() {
        sql.push_str(" AND season = ?");
    }
    sql.push_str(" ORDER BY name");

    let mut query = sqlx::query_as::<_, Player>(&sql);
    if let Some(tid) = team_id {
        query = query.bind(tid);
    }
    if let Some(s) = season {
        query = query.bind(s);
    }
    Ok(query.fetch_all(pool).await?)
}

pub async fn get_players_missing_cards(
    pool: &SqlitePool,
    season: i64,
    card_type: &str,
) -> Result<Vec<Player>> {
    let players = if card_type == "batter" {
        sqlx::query_as::<_, Player>(
            "SELECT * FROM players \
             WHERE season = ? \
               AND pos_1 IN ('C', '1B', '2B', '3B', 'SS', 'LF', 'CF', 'RF', 'DH') \
               AND id NOT IN (SELECT player_id FROM batter_cards) \
             ORDER BY name",
        )
        .bind(season)
        .fetch_all(pool)
        .await?
    } else {
        sqlx::query_as::<_, Player>(
            "SELECT * FROM players \
             WHERE season = ? \
               AND pos_1 IN ('SP', 'RP', 'CP') \
               AND id NOT IN (SELECT player_id FROM pitcher_cards) \
             ORDER BY name",
        )
        .bind(season)
        .fetch_all(pool)
        .await?
    };
    Ok(players)
}

// =============================================================================
// Card Queries
// =============================================================================

pub async fn get_batter_card(pool: &SqlitePool, player_id: i64) -> Result<Option<BatterCard>> {
    let card = sqlx::query_as::<_, BatterCard>(
        "SELECT * FROM batter_cards WHERE player_id = ?",
    )
    .bind(player_id)
    .fetch_optional(pool)
    .await?;
    Ok(card)
}

pub async fn get_pitcher_card(pool: &SqlitePool, player_id: i64) -> Result<Option<PitcherCard>> {
    let card = sqlx::query_as::<_, PitcherCard>(
        "SELECT * FROM pitcher_cards WHERE player_id = ?",
    )
    .bind(player_id)
    .fetch_optional(pool)
    .await?;
    Ok(card)
}

// =============================================================================
// Sync Status Queries
// =============================================================================

pub async fn get_sync_status(
    pool: &SqlitePool,
    entity_type: &str,
) -> Result<Option<SyncStatus>> {
    let status = sqlx::query_as::<_, SyncStatus>(
        "SELECT * FROM sync_status WHERE entity_type = ?",
    )
    .bind(entity_type)
    .fetch_optional(pool)
    .await?;
    Ok(status)
}

pub async fn update_sync_status(
    pool: &SqlitePool,
    entity_type: &str,
    count: i64,
    error: Option<&str>,
) -> Result<()> {
    sqlx::query(
        "INSERT INTO sync_status (entity_type, last_sync, last_sync_count, last_error) \
         VALUES (?, datetime('now'), ?, ?) \
         ON CONFLICT(entity_type) DO UPDATE SET \
             last_sync = excluded.last_sync, \
             last_sync_count = excluded.last_sync_count, \
             last_error = excluded.last_error",
    )
    .bind(entity_type)
    .bind(count)
    .bind(error)
    .execute(pool)
    .await?;
    Ok(())
}

// =============================================================================
// Sync Status — Bulk
// =============================================================================

pub async fn get_all_sync_statuses(pool: &SqlitePool) -> Result<Vec<SyncStatus>> {
    let rows = sqlx::query_as::<_, SyncStatus>("SELECT * FROM sync_status ORDER BY entity_type")
        .fetch_all(pool)
        .await?;
    Ok(rows)
}

// =============================================================================
// Missing Cards (team-scoped)
// =============================================================================

pub async fn get_players_missing_batter_cards(
    pool: &SqlitePool,
    team_id: i64,
    season: i64,
) -> Result<Vec<Player>> {
    let players = sqlx::query_as::<_, Player>(
        "SELECT * FROM players \
         WHERE team_id = ? AND season = ? \
           AND pos_1 IN ('C', '1B', '2B', '3B', 'SS', 'LF', 'CF', 'RF', 'DH') \
           AND id NOT IN (SELECT player_id FROM batter_cards) \
         ORDER BY name",
    )
    .bind(team_id)
    .bind(season)
    .fetch_all(pool)
    .await?;
    Ok(players)
}

pub async fn get_players_missing_pitcher_cards(
    pool: &SqlitePool,
    team_id: i64,
    season: i64,
) -> Result<Vec<Player>> {
    let players = sqlx::query_as::<_, Player>(
        "SELECT * FROM players \
         WHERE team_id = ? AND season = ? \
           AND (pos_1 IN ('SP', 'RP', 'CP') OR pos_2 IN ('SP', 'RP', 'CP')) \
           AND id NOT IN (SELECT player_id FROM pitcher_cards) \
         ORDER BY name",
    )
    .bind(team_id)
    .bind(season)
    .fetch_all(pool)
    .await?;
    Ok(players)
}

// =============================================================================
// Matchup Cache Queries
// =============================================================================

pub async fn get_cached_matchup(
    pool: &SqlitePool,
    batter_id: i64,
    pitcher_id: i64,
    weights_hash: &str,
) -> Result<Option<MatchupCache>> {
    let cache = sqlx::query_as::<_, MatchupCache>(
        "SELECT * FROM matchup_cache \
         WHERE batter_id = ? AND pitcher_id = ? AND weights_hash = ?",
    )
    .bind(batter_id)
    .bind(pitcher_id)
    .bind(weights_hash)
    .fetch_optional(pool)
    .await?;
    Ok(cache)
}

pub async fn invalidate_matchup_cache(pool: &SqlitePool) -> Result<u64> {
    let result = sqlx::query("DELETE FROM matchup_cache")
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

// =============================================================================
// Standardized Score Cache Queries
// =============================================================================

pub async fn get_cached_batter_score(
    pool: &SqlitePool,
    batter_card_id: i64,
    split: &str,
) -> Result<Option<StandardizedScoreCache>> {
    let cache = sqlx::query_as::<_, StandardizedScoreCache>(
        "SELECT * FROM standardized_score_cache WHERE batter_card_id = ? AND split = ?",
    )
    .bind(batter_card_id)
    .bind(split)
    .fetch_optional(pool)
    .await?;
    Ok(cache)
}

pub async fn get_cached_pitcher_score(
    pool: &SqlitePool,
    pitcher_card_id: i64,
    split: &str,
) -> Result<Option<StandardizedScoreCache>> {
    let cache = sqlx::query_as::<_, StandardizedScoreCache>(
        "SELECT * FROM standardized_score_cache WHERE pitcher_card_id = ? AND split = ?",
    )
    .bind(pitcher_card_id)
    .bind(split)
    .fetch_optional(pool)
    .await?;
    Ok(cache)
}

pub async fn clear_score_cache(pool: &SqlitePool) -> Result<u64> {
    let result = sqlx::query("DELETE FROM standardized_score_cache")
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

pub async fn insert_score_cache(
    pool: &SqlitePool,
    batter_card_id: Option<i64>,
    pitcher_card_id: Option<i64>,
    split: &str,
    total_score: f64,
    stat_scores: &str,
    weights_hash: &str,
    league_stats_hash: &str,
) -> Result<()> {
    let computed_at = chrono::Utc::now().naive_utc();
    sqlx::query(
        "INSERT INTO standardized_score_cache \
         (batter_card_id, pitcher_card_id, split, total_score, stat_scores, computed_at, \
          weights_hash, league_stats_hash) \
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(batter_card_id)
    .bind(pitcher_card_id)
    .bind(split)
    .bind(total_score)
    .bind(stat_scores)
    .bind(computed_at)
    .bind(weights_hash)
    .bind(league_stats_hash)
    .execute(pool)
    .await?;
    Ok(())
}

// =============================================================================
// Lineup Queries
// =============================================================================

pub async fn get_lineups(pool: &SqlitePool) -> Result<Vec<Lineup>> {
    let lineups = sqlx::query_as::<_, Lineup>("SELECT * FROM lineups ORDER BY name")
        .fetch_all(pool)
        .await?;
    Ok(lineups)
}

pub async fn get_lineup_by_name(pool: &SqlitePool, name: &str) -> Result<Option<Lineup>> {
    let lineup = sqlx::query_as::<_, Lineup>("SELECT * FROM lineups WHERE name = ?")
        .bind(name)
        .fetch_optional(pool)
        .await?;
    Ok(lineup)
}

pub async fn save_lineup(
    pool: &SqlitePool,
    name: &str,
    batting_order: &[i64],
    positions: &HashMap<String, i64>,
    lineup_type: &str,
    description: Option<&str>,
    starting_pitcher_id: Option<i64>,
) -> Result<()> {
    let batting_order_json = serde_json::to_string(batting_order)?;
    let positions_json = serde_json::to_string(positions)?;

    // UPDATE existing lineup if found; INSERT if not
    let rows_updated = sqlx::query(
        "UPDATE lineups \
         SET description = ?, lineup_type = ?, batting_order = ?, positions = ?, \
             starting_pitcher_id = ?, updated_at = datetime('now') \
         WHERE name = ?",
    )
    .bind(description)
    .bind(lineup_type)
    .bind(&batting_order_json)
    .bind(&positions_json)
    .bind(starting_pitcher_id)
    .bind(name)
    .execute(pool)
    .await?
    .rows_affected();

    if rows_updated == 0 {
        sqlx::query(
            "INSERT INTO lineups \
             (name, description, lineup_type, batting_order, positions, starting_pitcher_id, \
              created_at, updated_at) \
             VALUES (?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))",
        )
        .bind(name)
        .bind(description)
        .bind(lineup_type)
        .bind(&batting_order_json)
        .bind(&positions_json)
        .bind(starting_pitcher_id)
        .execute(pool)
        .await?;
    }

    Ok(())
}

pub async fn delete_lineup(pool: &SqlitePool, name: &str) -> Result<bool> {
    let result = sqlx::query("DELETE FROM lineups WHERE name = ?")
        .bind(name)
        .execute(pool)
        .await?;
    Ok(result.rows_affected() > 0)
}

// =============================================================================
// Roster Queries
// =============================================================================

pub async fn get_my_roster(
    pool: &SqlitePool,
    team_abbrev: &str,
    season: i64,
) -> Result<Roster> {
    let majors_team = get_team_by_abbrev(pool, team_abbrev, season).await?;
    let majors = match majors_team {
        Some(t) => get_players_by_team(pool, t.id).await?,
        None => vec![],
    };

    let il_abbrev = format!("{}IL", team_abbrev);
    let il_team = get_team_by_abbrev(pool, &il_abbrev, season).await?;
    let il = match il_team {
        Some(t) => get_players_by_team(pool, t.id).await?,
        None => vec![],
    };

    let mil_abbrev = format!("{}MiL", team_abbrev);
    let mil_team = get_team_by_abbrev(pool, &mil_abbrev, season).await?;
    let minors = match mil_team {
        Some(t) => get_players_by_team(pool, t.id).await?,
        None => vec![],
    };

    Ok(Roster { majors, minors, il })
}
