"""Integration tests for ALL repository classes with real PostgreSQL.

These tests use the ``db_session`` fixture from ``conftest.py`` which
connects to a real PostgreSQL instance and rolls back after each test.

Covers every public method in every repository class:
    MeetingRepository, RaceRepository, HorseRepository, DriverRepository,
    TrainerRepository, StarterRepository, RatingSnapshotRepository,
    BarrierAdjustmentRepository, HandicapAdjustmentRepository
"""

from datetime import date, timedelta

import pytest

from packages.core.storage.models import EntityType
from packages.core.storage.repositories import (
    BarrierAdjustmentRepository,
    DriverRepository,
    HandicapAdjustmentRepository,
    HorseRepository,
    MeetingRepository,
    RaceRepository,
    RatingSnapshotRepository,
    StarterRepository,
    TrainerRepository,
    generate_driver_id,
    generate_trainer_id,
    normalize_entity_id,
)

pytestmark = pytest.mark.integration


# ── Helper factories ─────────────────────────────────────────────────


def make_meeting_data(
    meeting_id: str = "int_meeting_1",
    meeting_date: str = "2026-01-15",
    venue: str = "Integration Track",
    category: str = "H",
) -> dict:
    return {
        "meeting": meeting_id,
        "date": meeting_date,
        "name": venue,
        "category": category,
    }


def make_race_data(race_number: int = 1, distance: int = 2000, **kwargs) -> dict:
    data = {"race_number": race_number, "distance": distance}
    data.update(kwargs)
    return data


# ── TestMeetingRepository ─────────────────────────────────────────────


class TestMeetingRepository:
    """Integration tests for MeetingRepository."""

    def test_upsert_creates_meeting(self, db_session):
        """A meeting can be upserted and returns a valid Meeting instance."""
        meeting = MeetingRepository.upsert(db_session, make_meeting_data())
        assert meeting is not None
        assert meeting.id == "int_meeting_1"
        assert meeting.venue == "Integration Track"
        assert meeting.category == "H"

    def test_upsert_idempotent(self, db_session):
        """Upserting the same meeting ID updates fields in-place."""
        MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="idem_meeting", venue="Original")
        )
        # Second upsert with changed venue
        updated = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="idem_meeting", venue="Updated")
        )
        # Expire to avoid cached ORM object
        db_session.expire_all()
        refetched = MeetingRepository.get_by_id(db_session, "idem_meeting")
        assert refetched is not None
        assert refetched.venue == "Updated"

    def test_get_by_id_returns_none_if_missing(self, db_session):
        """get_by_id returns None for a non-existent meeting."""
        assert MeetingRepository.get_by_id(db_session, "nonexistent") is None

    def test_get_by_date_range(self, db_session):
        """get_by_date_range returns only meetings within the date window."""
        MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="m1", meeting_date="2026-01-01")
        )
        MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="m2", meeting_date="2026-01-15")
        )
        MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="m3", meeting_date="2026-02-01")
        )

        results = MeetingRepository.get_by_date_range(
            db_session, date(2026, 1, 1), date(2026, 1, 31)
        )
        ids = {m.id for m in results}
        assert ids == {"m1", "m2"}
        assert "m3" not in ids

    def test_get_by_date_range_returns_empty(self, db_session):
        """get_by_date_range returns empty list when nothing matches."""
        MeetingRepository.upsert(
            db_session,
            make_meeting_data(meeting_id="m_outside", meeting_date="2026-06-01"),
        )
        results = MeetingRepository.get_by_date_range(
            db_session, date(2025, 1, 1), date(2025, 12, 31)
        )
        assert results == []


# ── TestRaceRepository ────────────────────────────────────────────────


class TestRaceRepository:
    """Integration tests for RaceRepository."""

    def test_upsert_creates_race(self, db_session):
        """A race can be upserted under a meeting."""
        meeting = MeetingRepository.upsert(db_session, make_meeting_data())
        race = RaceRepository.upsert(db_session, meeting.id, make_race_data())
        assert race is not None
        assert race.race_number == 1
        assert race.meeting_id == meeting.id
        assert race.distance_m == 2000

    def test_get_by_meeting_returns_ordered(self, db_session):
        """Races for a meeting are returned in race_number order."""
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="race_order_meeting")
        )
        RaceRepository.upsert(
            db_session, meeting.id, make_race_data(race_number=2, distance=2200)
        )
        RaceRepository.upsert(
            db_session, meeting.id, make_race_data(race_number=1, distance=1600)
        )
        races = RaceRepository.get_by_meeting(db_session, meeting.id)
        assert len(races) == 2
        assert [r.race_number for r in races] == [1, 2]
        assert races[0].distance_m == 1600

    def test_get_by_meeting_empty(self, db_session):
        """A meeting with no races returns an empty list."""
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="empty_meeting_races")
        )
        assert RaceRepository.get_by_meeting(db_session, meeting.id) == []

    def test_upsert_updates_existing_race(self, db_session):
        """Upserting the same meeting_id + race_number updates the record."""
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="race_update_meeting")
        )
        race = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(race_number=1, track_condition="Good"),
        )
        original_id = race.id
        updated = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(race_number=1, track_condition="Soft"),
        )
        assert updated.id == original_id
        assert updated.track_condition == "Soft"

    def test_get_races_for_recompute(self, db_session):
        """get_races_for_recompute returns races in date/number order."""
        m1 = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="rm1", meeting_date="2026-01-02")
        )
        m2 = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="rm2", meeting_date="2026-01-01")
        )
        # Create in reverse order to test ordering
        RaceRepository.upsert(db_session, m1.id, make_race_data(race_number=1))
        RaceRepository.upsert(db_session, m2.id, make_race_data(race_number=2))
        RaceRepository.upsert(db_session, m2.id, make_race_data(race_number=1))

        results = RaceRepository.get_races_for_recompute(
            db_session, date(2026, 1, 1), date(2026, 1, 2)
        )
        # Expected order: m2 race 1, m2 race 2, m1 race 1
        assert len(results) == 3
        race_ids = [(r.meeting_id, r.race_number) for r in results]
        assert race_ids == [("rm2", 1), ("rm2", 2), ("rm1", 1)]

    def test_get_races_for_recompute_outside_range(self, db_session):
        """get_races_for_recompute returns empty list when nothing matches."""
        meeting = MeetingRepository.upsert(
            db_session,
            make_meeting_data(meeting_id="rm_outside", meeting_date="2026-06-01"),
        )
        RaceRepository.upsert(db_session, meeting.id, make_race_data())
        results = RaceRepository.get_races_for_recompute(
            db_session, date(2025, 1, 1), date(2025, 12, 31)
        )
        assert results == []


# ── TestHorseRepository ───────────────────────────────────────────────


class TestHorseRepository:
    """Integration tests for HorseRepository."""

    def test_upsert_creates_horse(self, db_session):
        """A horse can be upserted."""
        horse = HorseRepository.upsert(db_session, horse_id=5001, name="Thunder Bolt")
        assert horse.id == 5001
        assert horse.name == "Thunder Bolt"

    def test_upsert_idempotent(self, db_session):
        """Upserting the same horse_id updates the name."""
        HorseRepository.upsert(db_session, horse_id=5002, name="Original Name")
        HorseRepository.upsert(db_session, horse_id=5002, name="Updated Name")
        db_session.expire_all()
        horse = HorseRepository.upsert(db_session, horse_id=5002, name="Updated Name")
        # Fetch from DB via the upsert return
        assert horse.name == "Updated Name"

    def test_upsert_with_raw_data(self, db_session):
        """Upserting with raw_data stores it in raw_json."""
        raw = {"source": "test", "color": "bay"}
        horse = HorseRepository.upsert(
            db_session, horse_id=5003, name="Data Horse", raw_data=raw
        )
        assert horse.raw_json == raw


# ── TestDriverRepository ──────────────────────────────────────────────


class TestDriverRepository:
    """Integration tests for DriverRepository."""

    def test_upsert_creates_driver(self, db_session):
        """A driver can be upserted with a generated ID."""
        driver = DriverRepository.upsert(db_session, "John Driver")
        assert driver.id == generate_driver_id("John Driver")
        assert driver.name == "John Driver"

    def test_upsert_with_explicit_id(self, db_session):
        """A driver can be upserted with an explicit ID."""
        driver = DriverRepository.upsert(
            db_session, "Explicit Driver", driver_id=999001
        )
        assert driver.id == 999001
        assert driver.name == "Explicit Driver"

    def test_upsert_idempotent(self, db_session):
        """Upserting the same name returns the same ID."""
        d1 = DriverRepository.upsert(db_session, "Idem Driver")
        d2 = DriverRepository.upsert(db_session, "Idem Driver")
        assert d1.id == d2.id

    def test_upsert_raises_on_empty_name(self, db_session):
        """An empty driver name raises ValueError."""
        with pytest.raises(ValueError, match="Driver name is required"):
            DriverRepository.upsert(db_session, "")

    def test_normalize_entity_id_on_driver_name(self):
        """normalize_entity_id produces a deterministic integer from a name."""
        result = normalize_entity_id("John Driver", fallback_name="John Driver")
        assert isinstance(result, int)
        assert result > 0


# ── TestTrainerRepository ─────────────────────────────────────────────


class TestTrainerRepository:
    """Integration tests for TrainerRepository."""

    def test_upsert_creates_trainer(self, db_session):
        """A trainer can be upserted with a generated ID."""
        trainer = TrainerRepository.upsert(db_session, "Jane Trainer")
        assert trainer.id == generate_trainer_id("Jane Trainer")
        assert trainer.name == "Jane Trainer"

    def test_upsert_with_explicit_id(self, db_session):
        """A trainer can be upserted with an explicit ID."""
        trainer = TrainerRepository.upsert(
            db_session, "Explicit Trainer", trainer_id=888001
        )
        assert trainer.id == 888001
        assert trainer.name == "Explicit Trainer"

    def test_upsert_idempotent(self, db_session):
        """Upserting the same name returns the same ID."""
        t1 = TrainerRepository.upsert(db_session, "Idem Trainer")
        t2 = TrainerRepository.upsert(db_session, "Idem Trainer")
        assert t1.id == t2.id

    def test_upsert_raises_on_empty_name(self, db_session):
        """An empty trainer name raises ValueError."""
        with pytest.raises(ValueError, match="Trainer name is required"):
            TrainerRepository.upsert(db_session, "")


# ── TestStarterRepository ─────────────────────────────────────────────


class TestStarterRepository:
    """Integration tests for StarterRepository."""

    @pytest.fixture
    def meeting_and_race(self, db_session):
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="starter_test_meeting")
        )
        race = RaceRepository.upsert(db_session, meeting.id, make_race_data())
        return meeting, race

    def test_upsert_creates_starter(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        starter = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Fast Horse",
                "horse_id": 7001,
                "horse_name": "Fast Horse",
                "runner_number": 1,
                "barrier": 3,
            },
            placing=1,
        )
        assert starter is not None
        assert starter.horse_id == 7001
        assert starter.runner_number == 1
        assert starter.barrier == 3
        assert starter.placing == 1

    def test_get_by_race_returns_starters(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        StarterRepository.upsert(
            db_session,
            race.id,
            {"name": "A", "horse_id": 7010, "horse_name": "A", "runner_number": 1},
            placing=1,
        )
        StarterRepository.upsert(
            db_session,
            race.id,
            {"name": "B", "horse_id": 7011, "horse_name": "B", "runner_number": 2},
            placing=2,
        )
        starters = StarterRepository.get_by_race(db_session, race.id)
        assert len(starters) == 2
        assert {s.horse_id for s in starters} == {7010, 7011}

    def test_scratched_returns_none(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        result = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Scratched",
                "horse_id": 7099,
                "horse_name": "Scratched",
                "is_scratched": True,
            },
        )
        assert result is None

    def test_upsert_updates_existing_starter(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        starter = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Updatable",
                "horse_id": 7020,
                "horse_name": "Updatable",
                "runner_number": 5,
            },
            placing=3,
        )
        original_id = starter.id
        updated = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Updatable",
                "horse_id": 7020,
                "horse_name": "Updatable",
                "runner_number": 5,
            },
            placing=1,
        )
        assert updated.id == original_id
        assert updated.placing == 1

    def test_driver_and_trainer_auto_created(self, db_session, meeting_and_race):
        """When starter data includes driver/trainer names, they are created."""
        from packages.core.storage.models import Driver, Trainer

        _, race = meeting_and_race
        starter = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Linked Horse",
                "horse_id": 7030,
                "horse_name": "Linked Horse",
                "runner_number": 7,
                "jockey": "Auto Driver",
                "driver_name": "Auto Driver",
                "trainer_name": "Auto Trainer",
            },
        )
        assert starter.driver_id is not None
        assert starter.trainer_id is not None
        # Verify the driver and trainer were persisted in the DB
        driver = db_session.query(Driver).filter(Driver.id == starter.driver_id).first()
        assert driver is not None
        assert driver.name == "Auto Driver"
        trainer = (
            db_session.query(Trainer).filter(Trainer.id == starter.trainer_id).first()
        )
        assert trainer is not None
        assert trainer.name == "Auto Trainer"

    def test_get_by_race_empty(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        assert StarterRepository.get_by_race(db_session, race.id) == []


# ── TestRatingSnapshotRepository ──────────────────────────────────────


class TestRatingSnapshotRepository:
    """Integration tests for RatingSnapshotRepository."""

    @pytest.fixture
    def meeting_and_race(self, db_session):
        meeting = MeetingRepository.upsert(
            db_session,
            make_meeting_data(meeting_id="rating_test", meeting_date="2026-01-15"),
        )
        race = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(
                race_number=1, advertised_start_string="2026-01-15T14:30:00+13:00"
            ),
        )
        return meeting, race

    def test_upsert_creates_snapshot(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        snap = RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8001,
            as_of_race_id=race.id,
            rating=1500.0,
            rd=100.0,
            meta={"race_count": 5},
        )
        assert snap.entity_type == EntityType.HORSE
        assert snap.entity_id == 8001
        assert snap.rating == 1500.0
        assert snap.rd == 100.0
        assert snap.meta["race_count"] == 5

    def test_upsert_on_conflict_updates(self, db_session, meeting_and_race):
        _, race = meeting_and_race
        RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8002,
            as_of_race_id=race.id,
            rating=1500.0,
            rd=100.0,
            meta={"count": 1},
        )
        db_session.expire_all()
        updated = RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8002,
            as_of_race_id=race.id,
            rating=1542.0,
            rd=80.0,
            meta={"count": 2},
        )
        assert updated.rating == 1542.0
        assert updated.rd == 80.0
        assert updated.meta["count"] == 2

    def test_upsert_for_driver_and_trainer(self, db_session, meeting_and_race):
        """Snapshots work for Driver and Trainer entity types too."""
        _, race = meeting_and_race
        for entity_type in (EntityType.DRIVER, EntityType.TRAINER):
            snap = RatingSnapshotRepository.upsert(
                db_session,
                entity_type=entity_type,
                entity_id=9000,
                as_of_race_id=race.id,
                rating=1600.0,
            )
            assert snap.entity_type == entity_type

    def test_get_latest_rating(self, db_session, meeting_and_race):
        meeting, race1 = meeting_and_race
        race2 = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(
                race_number=2,
                advertised_start_string="2026-01-15T14:35:00+13:00",
            ),
        )
        RatingSnapshotRepository.upsert(
            db_session, EntityType.HORSE, 8003, race1.id, 1500.0
        )
        RatingSnapshotRepository.upsert(
            db_session, EntityType.HORSE, 8003, race2.id, 1524.0
        )
        latest = RatingSnapshotRepository.get_latest_rating(
            db_session, EntityType.HORSE, 8003
        )
        assert latest is not None
        assert latest.rating == 1524.0
        assert latest.as_of_race_id == race2.id

    def test_get_latest_rating_none_for_unknown(self, db_session):
        assert (
            RatingSnapshotRepository.get_latest_rating(
                db_session, EntityType.HORSE, 99999
            )
            is None
        )

    def test_get_top_ratings(self, db_session, meeting_and_race):
        meeting, race = meeting_and_race
        # Create multiple horse snapshots with different ratings
        for i in range(4):
            RatingSnapshotRepository.upsert(
                db_session, EntityType.HORSE, 9000 + i, race.id, 1500.0 + i * 10
            )
        top = RatingSnapshotRepository.get_top_ratings(
            db_session, EntityType.HORSE, limit=2
        )
        assert len(top) == 2
        # Highest rating first
        assert top[0].rating > top[1].rating

    def test_get_top_ratings_empty(self, db_session):
        assert (
            RatingSnapshotRepository.get_top_ratings(db_session, EntityType.HORSE) == []
        )


# ── TestBarrierAdjustmentRepository ───────────────────────────────────


class TestBarrierAdjustmentRepository:
    """Integration tests for BarrierAdjustmentRepository."""

    def test_upsert_creates_adjustment(self, db_session):
        ba = BarrierAdjustmentRepository.upsert(
            db_session,
            venue="Test Track",
            start_type="mobile",
            distance_bucket="1700-2000",
            barrier=1,
            adjustment=0.5,
        )
        assert ba.venue == "Test Track"
        assert ba.start_type == "mobile"
        assert ba.barrier == 1
        assert ba.adjustment == 0.5
        assert ba.sample_count == 1

    def test_upsert_with_null_venue(self, db_session):
        """Global barrier adjustments (NULL venue) are supported."""
        ba = BarrierAdjustmentRepository.upsert(
            db_session,
            venue=None,
            start_type=None,
            distance_bucket="<1700",
            barrier=3,
            adjustment=0.2,
        )
        assert ba.venue is None
        assert ba.adjustment == 0.2

    def test_upsert_idempotent(self, db_session):
        BarrierAdjustmentRepository.upsert(
            db_session, "Venue", "mobile", "1700-2000", 1, 0.5
        )
        BarrierAdjustmentRepository.upsert(
            db_session, "Venue", "mobile", "1700-2000", 1, 0.8
        )
        db_session.expire_all()
        all_items = BarrierAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        assert all_items[0].adjustment == 0.8

    def test_get_all(self, db_session):
        BarrierAdjustmentRepository.upsert(db_session, None, None, "<1700", 1, 0.1)
        BarrierAdjustmentRepository.upsert(db_session, None, None, "<1700", 2, 0.2)
        all_items = BarrierAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 2

    def test_get_all_empty(self, db_session):
        assert BarrierAdjustmentRepository.get_all(db_session) == []

    def test_increment_sample_creates_new(self, db_session):
        """increment_sample creates a new record if none exists."""
        BarrierAdjustmentRepository.increment_sample(
            db_session, "New Track", None, "1700-2000", 5, delta=1.0, learning_rate=0.1
        )
        all_items = BarrierAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        # adj_new = learning_rate * delta = 0.1 * 1.0 = 0.1
        assert all_items[0].adjustment == pytest.approx(0.1)
        assert all_items[0].sample_count == 1

    def test_increment_sample_updates_existing(self, db_session):
        """increment_sample updates an existing record."""
        BarrierAdjustmentRepository.upsert(
            db_session, "Inc Track", "mobile", "1700-2000", 3, 0.5, sample_count=5
        )
        BarrierAdjustmentRepository.increment_sample(
            db_session,
            "Inc Track",
            "mobile",
            "1700-2000",
            3,
            delta=0.5,
            learning_rate=0.2,
        )
        all_items = BarrierAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        # new_adj = 0.5 + 0.2 * 0.5 = 0.6
        assert all_items[0].adjustment == pytest.approx(0.6)
        assert all_items[0].sample_count == 6


# ── TestHandicapAdjustmentRepository ──────────────────────────────────


class TestHandicapAdjustmentRepository:
    """Integration tests for HandicapAdjustmentRepository."""

    def test_upsert_creates_adjustment(self, db_session):
        ha = HandicapAdjustmentRepository.upsert(
            db_session,
            venue="Test Track",
            start_type="mobile",
            distance_bucket="1700-2000",
            handicap_m=10,
            adjustment=0.3,
        )
        assert ha.venue == "Test Track"
        assert ha.handicap_m == 10
        assert ha.adjustment == 0.3
        assert ha.sample_count == 1

    def test_upsert_with_null_venue(self, db_session):
        ha = HandicapAdjustmentRepository.upsert(
            db_session,
            venue=None,
            start_type=None,
            distance_bucket="<1700",
            handicap_m=0,
            adjustment=0.0,
        )
        assert ha.venue is None
        assert ha.adjustment == 0.0

    def test_upsert_idempotent(self, db_session):
        HandicapAdjustmentRepository.upsert(
            db_session, "Venue", "mobile", "1700-2000", 10, 0.3
        )
        HandicapAdjustmentRepository.upsert(
            db_session, "Venue", "mobile", "1700-2000", 10, 0.5
        )
        db_session.expire_all()
        all_items = HandicapAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        assert all_items[0].adjustment == 0.5

    def test_get_all(self, db_session):
        HandicapAdjustmentRepository.upsert(db_session, None, None, "<1700", 0, 0.1)
        HandicapAdjustmentRepository.upsert(db_session, None, None, "<1700", 10, 0.2)
        all_items = HandicapAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 2

    def test_get_all_empty(self, db_session):
        assert HandicapAdjustmentRepository.get_all(db_session) == []

    def test_increment_sample_creates_new(self, db_session):
        HandicapAdjustmentRepository.increment_sample(
            db_session, "New Track", None, "1700-2000", 10, delta=2.0, learning_rate=0.1
        )
        all_items = HandicapAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        assert all_items[0].adjustment == pytest.approx(0.2)
        assert all_items[0].sample_count == 1

    def test_increment_sample_updates_existing(self, db_session):
        HandicapAdjustmentRepository.upsert(
            db_session, "Inc Track", "mobile", "1700-2000", 10, 0.5, sample_count=3
        )
        HandicapAdjustmentRepository.increment_sample(
            db_session,
            "Inc Track",
            "mobile",
            "1700-2000",
            10,
            delta=1.0,
            learning_rate=0.2,
        )
        all_items = HandicapAdjustmentRepository.get_all(db_session)
        assert len(all_items) == 1
        # new_adj = 0.5 + 0.2 * 1.0 = 0.7
        assert all_items[0].adjustment == pytest.approx(0.7)
        assert all_items[0].sample_count == 4