"""Integration tests for repositories 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.

Requires:
  - PostgreSQL 16 running (``docker compose up -d db``)
  - Test database ``tipsharks_test`` (or ``TEST_DATABASE_URL`` env var)
"""

import pytest

from packages.core.storage.models import EntityType
from packages.core.storage.repositories import (
    MeetingRepository,
    RaceRepository,
    RatingSnapshotRepository,
    StarterRepository,
)

pytestmark = pytest.mark.integration


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


def make_meeting_data(
    meeting_id: str = "int_meeting_1",
    meeting_date: str = "2025-05-06",
    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,
    start_type: str = "mobile",
    gait: str = "pace",
    **kwargs,
) -> dict:
    data = {
        "race_number": race_number,
        "distance": distance,
        "start_type": start_type,
        "gait": gait,
    }
    data.update(kwargs)
    return data


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


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

    def test_upsert_creates_race(self, db_session):
        """A race can be upserted and returns a valid Race instance."""
        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
        assert race.start_type == "mobile"

    def test_get_by_meeting_returns_ordered_races(self, db_session):
        """Races for a meeting are returned in race_number order."""
        meeting = MeetingRepository.upsert(db_session, make_meeting_data())

        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
        # Order should be by race_number ascending
        assert [r.race_number for r in races] == [1, 2]
        assert races[0].distance_m == 1600

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

    def test_upsert_updates_existing_race(self, db_session):
        """Upserting the same meeting_id + race_number updates in-place."""
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="int_meeting_update")
        )

        # First insert
        race = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(race_number=1, distance=1600, track_condition="Good"),
        )
        original_id = race.id

        # Second upsert with changed fields
        updated = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(race_number=1, distance=1800, track_condition="Soft"),
        )

        assert updated.id == original_id  # Same record
        assert updated.distance_m == 1800  # New value
        assert updated.track_condition == "Soft"  # New value


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


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

    @pytest.fixture
    def meeting_and_race(self, db_session):
        """Create a meeting + race pair for starter tests."""
        meeting = MeetingRepository.upsert(
            db_session, make_meeting_data(meeting_id="int_meeting_starter")
        )
        race = RaceRepository.upsert(db_session, meeting.id, make_race_data())
        return meeting, race

    def test_upsert_creates_starter(self, db_session, meeting_and_race):
        """A starter can be upserted and returns a valid Starter instance."""
        _, race = meeting_and_race

        starter = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Fast Galloper",
                "horse_id": 9001,
                "horse_name": "Fast Galloper",
                "runner_number": 1,
                "barrier": 3,
            },
            placing=1,
        )

        assert starter is not None
        assert starter.horse_id == 9001
        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):
        """Starters for a race are returned correctly."""
        _, race = meeting_and_race

        StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Horse A",
                "horse_id": 9010,
                "horse_name": "Horse A",
                "runner_number": 1,
            },
            placing=1,
        )
        StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Horse B",
                "horse_id": 9011,
                "horse_name": "Horse B",
                "runner_number": 2,
            },
            placing=2,
        )

        starters = StarterRepository.get_by_race(db_session, race.id)
        assert len(starters) == 2
        horse_ids = {s.horse_id for s in starters}
        assert horse_ids == {9010, 9011}

    def test_scratched_runner_returns_none(self, db_session, meeting_and_race):
        """A scratched runner is skipped and returns None."""
        _, race = meeting_and_race

        result = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Scratched Horse",
                "horse_id": 9099,
                "horse_name": "Scratched Horse",
                "is_scratched": True,
            },
        )

        assert result is None

    def test_upsert_updates_existing_starter(self, db_session, meeting_and_race):
        """Upserting the same horse in the same race updates the record."""
        _, race = meeting_and_race

        # First insert
        starter = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Updatable Horse",
                "horse_id": 9020,
                "horse_name": "Updatable Horse",
                "runner_number": 5,
                "barrier": 2,
            },
            placing=3,
        )
        original_id = starter.id

        # Second upsert with changed placing
        updated = StarterRepository.upsert(
            db_session,
            race.id,
            {
                "name": "Updatable Horse",
                "horse_id": 9020,
                "horse_name": "Updatable Horse",
                "runner_number": 5,
                "barrier": 2,
            },
            placing=1,  # Changed from 3 → 1
        )

        assert updated.id == original_id
        assert updated.placing == 1  # Updated value


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


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

    @pytest.fixture
    def meeting_and_race(self, db_session):
        """Create a meeting + race with race_datetime for rating tests."""
        meeting = MeetingRepository.upsert(
            db_session,
            make_meeting_data(
                meeting_id="int_meeting_rating", meeting_date="2025-05-06"
            ),
        )
        race = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(
                race_number=1, advertised_start_string="2025-05-06T14:30:00+12:00"
            ),
        )
        return meeting, race

    def test_upsert_creates_snapshot(self, db_session, meeting_and_race):
        """A rating snapshot can be upserted and returned."""
        _, race = meeting_and_race

        snapshot = 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": 1},
        )

        assert snapshot is not None
        assert snapshot.entity_type == EntityType.HORSE
        assert snapshot.entity_id == 8001
        assert snapshot.as_of_race_id == race.id
        assert snapshot.rating == 1500.0
        assert snapshot.rd == 100.0

    def test_get_latest_rating_returns_latest(self, db_session, meeting_and_race):
        """get_latest_rating returns the most recent snapshot for an entity."""
        meeting, race1 = meeting_and_race

        # Create a second race (later in the same meeting)
        race2 = RaceRepository.upsert(
            db_session,
            meeting.id,
            make_race_data(
                race_number=2,
                advertised_start_string="2025-05-06T14:35:00+12:00",
            ),
        )

        # First snapshot after race 1
        RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8002,
            as_of_race_id=race1.id,
            rating=1500.0,
        )

        # Second (later) snapshot after race 2
        RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8002,
            as_of_race_id=race2.id,
            rating=1524.0,
        )

        latest = RatingSnapshotRepository.get_latest_rating(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8002,
        )

        assert latest is not None
        assert latest.rating == 1524.0  # Latest rating
        assert latest.as_of_race_id == race2.id

    def test_get_latest_rating_none_for_unknown(self, db_session):
        """get_latest_rating returns None for an entity with no snapshots."""
        latest = RatingSnapshotRepository.get_latest_rating(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=99999,
        )
        assert latest is None

    def test_upsert_on_conflict_updates_rating(self, db_session, meeting_and_race):
        """ON CONFLICT DO UPDATE: upserting same (type, id, race) updates rating.

        This verifies the PostgreSQL ``INSERT … ON CONFLICT DO UPDATE``
        behaviour in RatingSnapshotRepository.
        """
        _, race = meeting_and_race

        # First upsert
        snapshot = RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8003,
            as_of_race_id=race.id,
            rating=1500.0,
            rd=100.0,
            meta={"count": 1},
        )
        original_id = snapshot.id

        # Expire the identity map so the second query goes to the DB.
        # Without this, SQLAlchemy returns the cached ORM object which
        # still has the old values.
        db_session.expire_all()

        # Second upsert — same composite key, different values
        updated = RatingSnapshotRepository.upsert(
            db_session,
            entity_type=EntityType.HORSE,
            entity_id=8003,
            as_of_race_id=race.id,
            rating=1542.0,
            rd=80.0,
            meta={"count": 2},
        )

        assert updated.id == original_id  # Same row
        assert updated.rating == 1542.0  # Updated
        assert updated.rd == 80.0  # Updated
        assert updated.meta["count"] == 2  # Updated