"""Tests for database module using local pandas data."""

from unittest.mock import patch

import pandas as pd
import pytest

from src.config import PLAYER_COLUMNS
from src.database.connection import (
    DatabaseConnectionError,
    QueryExecutionError,
    get_data,
    load_data,
)
from src.database.queries import (
    get_away_team_by_stats,
    get_players_by_full_names,
    search_player_by_name,
)


class TestLoadData:
    """Tests for load_data and get_data functions."""

    def test_load_data_returns_dataframe(self) -> None:
        """Test that load_data returns a DataFrame with uppercase columns."""
        df = load_data()
        assert isinstance(df, pd.DataFrame)
        assert not df.empty
        # All columns should be uppercase
        for col in df.columns:
            assert col == col.upper()

    def test_get_data_returns_dataframe(self) -> None:
        """Test that get_data returns a DataFrame."""
        df = get_data()
        assert isinstance(df, pd.DataFrame)
        assert not df.empty

    @patch("src.database.connection.CSV_PATH")
    def test_load_data_missing_file_raises_error(self, mock_path) -> None:  # type: ignore[no-untyped-def]
        """Test that missing CSV raises DatabaseConnectionError."""
        mock_path.exists.return_value = False
        with pytest.raises(DatabaseConnectionError, match="not found"):
            load_data()

    @patch("src.database.connection.pd.read_csv")
    @patch("src.database.connection.CSV_PATH")
    def test_load_data_parser_error_raises_connection_error(
        self,
        mock_path,
        mock_read_csv,  # type: ignore[no-untyped-def]
    ) -> None:
        """Test that CSV parse errors raise DatabaseConnectionError."""
        mock_path.exists.return_value = True
        mock_read_csv.side_effect = pd.errors.ParserError("bad csv")
        with pytest.raises(DatabaseConnectionError):
            load_data()


class TestSearchPlayerByName:
    """Tests for search_player_by_name function."""

    def test_search_by_full_name(self, sample_player_df: pd.DataFrame) -> None:
        """Verify search finds player by full name."""
        result = search_player_by_name(sample_player_df, "LeBron James")
        assert result == [("LeBron James",)]

    def test_search_by_first_name(self, sample_player_df: pd.DataFrame) -> None:
        """Verify search finds player by first name."""
        result = search_player_by_name(sample_player_df, "LeBron")
        assert result == [("LeBron James",)]

    def test_search_by_last_name(self, sample_player_df: pd.DataFrame) -> None:
        """Verify search finds player by last name."""
        result = search_player_by_name(sample_player_df, "Jordan")
        assert result == [("Michael Jordan",)]

    def test_search_case_insensitive(self, sample_player_df: pd.DataFrame) -> None:
        """Verify search is case-insensitive."""
        result = search_player_by_name(sample_player_df, "lebron")
        assert result == [("LeBron James",)]

    def test_search_partial_name(self, sample_player_df: pd.DataFrame) -> None:
        """Verify search finds player by partial name."""
        result = search_player_by_name(sample_player_df, "Jord")
        assert result == [("Michael Jordan",)]

    def test_returns_empty_on_no_match(self, sample_player_df: pd.DataFrame) -> None:
        """Verify empty list returned when no player found."""
        result = search_player_by_name(sample_player_df, "NonExistent Player")
        assert result == []


class TestGetPlayersByFullNames:
    """Tests for get_players_by_full_names batch query."""

    def test_returns_correct_players(self, sample_player_df: pd.DataFrame) -> None:
        """Verify correct players are returned in DataFrame."""
        names = ["LeBron James", "Michael Jordan"]
        result = get_players_by_full_names(sample_player_df, names)

        assert isinstance(result, pd.DataFrame)
        assert len(result) == 2
        assert set(result["FULL_NAME"]) == set(names)
        assert list(result.columns) == PLAYER_COLUMNS

    def test_empty_names_returns_empty_dataframe(
        self, sample_player_df: pd.DataFrame
    ) -> None:
        """Test that empty input returns empty DataFrame."""
        result = get_players_by_full_names(sample_player_df, [])

        assert isinstance(result, pd.DataFrame)
        assert result.empty
        assert list(result.columns) == PLAYER_COLUMNS


class TestGetAwayTeamByStats:
    """Tests for get_away_team_by_stats."""

    def test_max_attempts_raises_error(self) -> None:
        """Test that max_attempts limit works when population is too small."""
        # Create a DF with only 2 players
        df = pd.DataFrame(
            [
                {"FULL_NAME": "P1", "PTS": 1001, "REB": 501, "AST": 301, "STL": 101},
                {"FULL_NAME": "P2", "PTS": 1001, "REB": 501, "AST": 301, "STL": 101},
            ]
        )
        # Add missing columns to avoid errors if needed, though queries only use these
        for col in PLAYER_COLUMNS:
            if col not in df.columns:
                df[col] = 0

        with pytest.raises(QueryExecutionError) as exc_info:
            get_away_team_by_stats(
                df,
                pts_threshold=1000,
                reb_threshold=500,
                ast_threshold=300,
                stl_threshold=100,
                max_attempts=3,
            )

        assert "3 attempts" in str(exc_info.value)

    def test_success_with_enough_players(self) -> None:
        """Test successful generation with sufficient population."""
        # Create a DF with 10 players meeting criteria
        data = []
        for i in range(10):
            data.append(
                {
                    "FULL_NAME": f"Player{i}",
                    "PTS": 2000,
                    "REB": 1000,
                    "AST": 500,
                    "STL": 200,
                }
            )
        df = pd.DataFrame(data)
        for col in PLAYER_COLUMNS:
            if col not in df.columns:
                df[col] = 0

        result = get_away_team_by_stats(
            df,
            pts_threshold=1000,
            reb_threshold=500,
            ast_threshold=300,
            stl_threshold=100,
        )

        assert isinstance(result, pd.DataFrame)
        assert len(result) == 5


class TestCsvColumnValidation:
    """Integration tests validating CSV data matches config."""

    def test_csv_columns_match_config(self) -> None:
        """Verify that actual CSV columns match PLAYER_COLUMNS in config."""
        df = load_data()
        assert not df.empty, "CSV file should not be empty"
        assert list(df.columns) == PLAYER_COLUMNS