"""Tests for BattingSeasonStats and PitchingSeasonStats Peewee models.

Unit tests verify model structure and defaults on unsaved instances without
touching a database. Integration tests use an in-memory SQLite database to
verify table creation, unique constraints, indexes, and the delta-update
(increment) pattern.
"""

import pytest
from peewee import SqliteDatabase, IntegrityError

from app.models.season_stats import BattingSeasonStats, PitchingSeasonStats
from app.db_engine import Rarity, Event, Cardset, MlbPlayer, Player, Team, StratGame

# Dependency order matters for FK resolution.
_TEST_MODELS = [
    Rarity,
    Event,
    Cardset,
    MlbPlayer,
    Player,
    Team,
    StratGame,
    BattingSeasonStats,
    PitchingSeasonStats,
]

_test_db = SqliteDatabase(":memory:", pragmas={"foreign_keys": 1})


@pytest.fixture(autouse=True)
def setup_test_db():
    """Bind all models to an in-memory SQLite database, create tables, and
    tear them down after each test so each test starts from a clean state."""
    _test_db.bind(_TEST_MODELS)
    _test_db.create_tables(_TEST_MODELS)
    yield _test_db
    _test_db.drop_tables(list(reversed(_TEST_MODELS)), safe=True)


# ── Fixture helpers ─────────────────────────────────────────────────────────


def make_rarity():
    return Rarity.create(value=1, name="Common", color="#ffffff")


def make_cardset():
    return Cardset.create(name="2025", description="2025 Season", total_cards=100)


def make_player(cardset, rarity, player_id=1):
    return Player.create(
        player_id=player_id,
        p_name="Test Player",
        cost=100,
        image="test.png",
        mlbclub="BOS",
        franchise="Boston",
        cardset=cardset,
        set_num=1,
        rarity=rarity,
        pos_1="OF",
        description="Test",
    )


def make_team(abbrev="TEST", gmid=123456789):
    return Team.create(
        abbrev=abbrev,
        sname=abbrev,
        lname=f"Team {abbrev}",
        gmid=gmid,
        gmname="testuser",
        gsheet="https://example.com",
        wallet=1000,
        team_value=1000,
        collection_value=1000,
        season=1,
    )


def make_game(home_team, away_team, season=10):
    return StratGame.create(
        season=season,
        game_type="ranked",
        away_team=away_team,
        home_team=home_team,
    )


def make_batting_stats(player, team, season=10, **kwargs):
    return BattingSeasonStats.create(player=player, team=team, season=season, **kwargs)


def make_pitching_stats(player, team, season=10, **kwargs):
    return PitchingSeasonStats.create(player=player, team=team, season=season, **kwargs)


# ── Shared column-list constants ─────────────────────────────────────────────

_BATTING_STAT_COLS = [
    "games",
    "pa",
    "ab",
    "hits",
    "doubles",
    "triples",
    "hr",
    "rbi",
    "runs",
    "bb",
    "strikeouts",
    "hbp",
    "sac",
    "ibb",
    "gidp",
    "sb",
    "cs",
]

_PITCHING_STAT_COLS = [
    "games",
    "games_started",
    "outs",
    "strikeouts",
    "bb",
    "hits_allowed",
    "runs_allowed",
    "earned_runs",
    "hr_allowed",
    "hbp",
    "wild_pitches",
    "balks",
    "wins",
    "losses",
    "holds",
    "saves",
    "blown_saves",
]

_KEY_COLS = ["player", "team", "season"]
_META_COLS = ["last_game", "last_updated_at"]


# ── Shared index helper ───────────────────────────────────────────────────────


def _get_index_columns(db_conn, table: str) -> set:
    """Return a set of frozensets, each being the column set of one index."""
    indexes = db_conn.execute_sql(f"PRAGMA index_list({table})").fetchall()
    result = set()
    for idx in indexes:
        idx_name = idx[1]
        cols = db_conn.execute_sql(f"PRAGMA index_info({idx_name})").fetchall()
        result.add(frozenset(col[2] for col in cols))
    return result


# ── Unit: column completeness ────────────────────────────────────────────────


class TestBattingColumnCompleteness:
    """All required columns are present in BattingSeasonStats."""

    EXPECTED_COLS = _BATTING_STAT_COLS
    KEY_COLS = _KEY_COLS
    META_COLS = _META_COLS

    def test_stat_columns_present(self):
        """All batting aggregate columns are present."""
        fields = BattingSeasonStats._meta.fields
        for col in self.EXPECTED_COLS:
            assert col in fields, f"Missing batting column: {col}"

    def test_key_columns_present(self):
        """player, team, and season columns are present."""
        fields = BattingSeasonStats._meta.fields
        for col in self.KEY_COLS:
            assert col in fields, f"Missing key column: {col}"

    def test_meta_columns_present(self):
        """Meta columns last_game and last_updated_at are present."""
        fields = BattingSeasonStats._meta.fields
        for col in self.META_COLS:
            assert col in fields, f"Missing meta column: {col}"


class TestPitchingColumnCompleteness:
    """All required columns are present in PitchingSeasonStats."""

    EXPECTED_COLS = _PITCHING_STAT_COLS
    KEY_COLS = _KEY_COLS
    META_COLS = _META_COLS

    def test_stat_columns_present(self):
        """All pitching aggregate columns are present."""
        fields = PitchingSeasonStats._meta.fields
        for col in self.EXPECTED_COLS:
            assert col in fields, f"Missing pitching column: {col}"

    def test_key_columns_present(self):
        """player, team, and season columns are present."""
        fields = PitchingSeasonStats._meta.fields
        for col in self.KEY_COLS:
            assert col in fields, f"Missing key column: {col}"

    def test_meta_columns_present(self):
        """Meta columns last_game and last_updated_at are present."""
        fields = PitchingSeasonStats._meta.fields
        for col in self.META_COLS:
            assert col in fields, f"Missing meta column: {col}"


# ── Unit: default values ─────────────────────────────────────────────────────


class TestBattingDefaultValues:
    """All integer stat columns default to 0; nullable meta fields default to None."""

    INT_STAT_COLS = _BATTING_STAT_COLS

    def test_all_int_columns_default_to_zero(self):
        """Every integer stat column defaults to 0 on an unsaved instance."""
        row = BattingSeasonStats()
        for col in self.INT_STAT_COLS:
            val = getattr(row, col)
            assert val == 0, f"Column {col!r} default is {val!r}, expected 0"

    def test_last_game_defaults_to_none(self):
        """last_game FK is nullable and defaults to None."""
        row = BattingSeasonStats()
        assert row.last_game_id is None

    def test_last_updated_at_defaults_to_none(self):
        """last_updated_at defaults to None."""
        row = BattingSeasonStats()
        assert row.last_updated_at is None


class TestPitchingDefaultValues:
    """All integer stat columns default to 0; nullable meta fields default to None."""

    INT_STAT_COLS = _PITCHING_STAT_COLS

    def test_all_int_columns_default_to_zero(self):
        """Every integer stat column defaults to 0 on an unsaved instance."""
        row = PitchingSeasonStats()
        for col in self.INT_STAT_COLS:
            val = getattr(row, col)
            assert val == 0, f"Column {col!r} default is {val!r}, expected 0"

    def test_last_game_defaults_to_none(self):
        """last_game FK is nullable and defaults to None."""
        row = PitchingSeasonStats()
        assert row.last_game_id is None

    def test_last_updated_at_defaults_to_none(self):
        """last_updated_at defaults to None."""
        row = PitchingSeasonStats()
        assert row.last_updated_at is None


# ── Integration: unique constraint ───────────────────────────────────────────


class TestBattingUniqueConstraint:
    """UNIQUE on (player_id, team_id, season) is enforced at the DB level."""

    def test_duplicate_raises(self):
        """Inserting a second row for the same (player, team, season) raises IntegrityError."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        make_batting_stats(player, team, season=10)
        with pytest.raises(IntegrityError):
            make_batting_stats(player, team, season=10)

    def test_different_season_allowed(self):
        """Same (player, team) in a different season creates a separate row."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        make_batting_stats(player, team, season=10)
        row2 = make_batting_stats(player, team, season=11)
        assert row2.id is not None

    def test_different_team_allowed(self):
        """Same (player, season) on a different team creates a separate row."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team1 = make_team("TM1", gmid=111)
        team2 = make_team("TM2", gmid=222)
        make_batting_stats(player, team1, season=10)
        row2 = make_batting_stats(player, team2, season=10)
        assert row2.id is not None


class TestPitchingUniqueConstraint:
    """UNIQUE on (player_id, team_id, season) is enforced at the DB level."""

    def test_duplicate_raises(self):
        """Inserting a second row for the same (player, team, season) raises IntegrityError."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        make_pitching_stats(player, team, season=10)
        with pytest.raises(IntegrityError):
            make_pitching_stats(player, team, season=10)

    def test_different_season_allowed(self):
        """Same (player, team) in a different season creates a separate row."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        make_pitching_stats(player, team, season=10)
        row2 = make_pitching_stats(player, team, season=11)
        assert row2.id is not None


# ── Integration: delta update pattern ───────────────────────────────────────


class TestBattingDeltaUpdate:
    """Batting stats can be incremented (delta update) without replacing existing values."""

    def test_increment_batting_stats(self):
        """Updating pa and hits increments correctly."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        row = make_batting_stats(player, team, season=10, pa=5, hits=2)

        BattingSeasonStats.update(
            pa=BattingSeasonStats.pa + 3,
            hits=BattingSeasonStats.hits + 1,
        ).where(
            (BattingSeasonStats.player == player)
            & (BattingSeasonStats.team == team)
            & (BattingSeasonStats.season == 10)
        ).execute()

        updated = BattingSeasonStats.get_by_id(row.id)
        assert updated.pa == 8
        assert updated.hits == 3

    def test_last_game_fk_is_nullable(self):
        """last_game FK can be set to a StratGame instance or left NULL."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        row = make_batting_stats(player, team, season=10)
        assert row.last_game_id is None

        game = make_game(home_team=team, away_team=team)
        BattingSeasonStats.update(last_game=game).where(
            BattingSeasonStats.id == row.id
        ).execute()

        updated = BattingSeasonStats.get_by_id(row.id)
        assert updated.last_game_id == game.id


class TestPitchingDeltaUpdate:
    """Pitching stats can be incremented (delta update) without replacing existing values."""

    def test_increment_pitching_stats(self):
        """Updating outs and strikeouts increments correctly."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        row = make_pitching_stats(player, team, season=10, outs=9, strikeouts=3)

        PitchingSeasonStats.update(
            outs=PitchingSeasonStats.outs + 6,
            strikeouts=PitchingSeasonStats.strikeouts + 2,
        ).where(
            (PitchingSeasonStats.player == player)
            & (PitchingSeasonStats.team == team)
            & (PitchingSeasonStats.season == 10)
        ).execute()

        updated = PitchingSeasonStats.get_by_id(row.id)
        assert updated.outs == 15
        assert updated.strikeouts == 5

    def test_last_game_fk_is_nullable(self):
        """last_game FK can be set to a StratGame instance or left NULL."""
        rarity = make_rarity()
        cardset = make_cardset()
        player = make_player(cardset, rarity)
        team = make_team()
        row = make_pitching_stats(player, team, season=10)
        assert row.last_game_id is None

        game = make_game(home_team=team, away_team=team)
        PitchingSeasonStats.update(last_game=game).where(
            PitchingSeasonStats.id == row.id
        ).execute()

        updated = PitchingSeasonStats.get_by_id(row.id)
        assert updated.last_game_id == game.id


# ── Integration: index existence ─────────────────────────────────────────────


class TestBattingIndexExistence:
    """Required indexes exist on batting_season_stats."""

    def test_unique_index_on_player_team_season(self, setup_test_db):
        """A unique index covering (player_id, team_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "batting_season_stats")
        assert frozenset({"player_id", "team_id", "season"}) in index_sets

    def test_index_on_team_season(self, setup_test_db):
        """An index covering (team_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "batting_season_stats")
        assert frozenset({"team_id", "season"}) in index_sets

    def test_index_on_player_season(self, setup_test_db):
        """An index covering (player_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "batting_season_stats")
        assert frozenset({"player_id", "season"}) in index_sets


class TestPitchingIndexExistence:
    """Required indexes exist on pitching_season_stats."""

    def test_unique_index_on_player_team_season(self, setup_test_db):
        """A unique index covering (player_id, team_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "pitching_season_stats")
        assert frozenset({"player_id", "team_id", "season"}) in index_sets

    def test_index_on_team_season(self, setup_test_db):
        """An index covering (team_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "pitching_season_stats")
        assert frozenset({"team_id", "season"}) in index_sets

    def test_index_on_player_season(self, setup_test_db):
        """An index covering (player_id, season) exists."""
        index_sets = _get_index_columns(setup_test_db, "pitching_season_stats")
        assert frozenset({"player_id", "season"}) in index_sets