"""

Tests for voteview.py  -  cast_code mapping, crosswalk building, schema setup,

source URL construction, and year-index helpers.

All network access is mocked; no live HTTP calls are made.

"""

from __future__ import annotations

import os

import sqlite3

import tempfile

import pytest

from ingest.db import get_connection, init_schema

from ingest.pollers.members import ensure_votes_schema

from ingest.pollers.voteview import (

    CAST_CODE_MAP,

    _derive_session,

    _house_source_url,

    _senate_source_url,

    build_crosswalk,

    cast_code_to_position,

    completeness_report,

    ensure_voteview_schema,

    votes_by_member_year,

    votes_in_year,

)

def _make_db() -> tuple[sqlite3.Connection, str]:

    """Create a fresh temp-file DB with all schemas applied."""

    tmp = tempfile.mktemp(suffix=".db")

    conn = get_connection(tmp)

    init_schema(conn)

    ensure_votes_schema(conn)

    ensure_voteview_schema(conn)

    return conn, tmp

class TestCastCodeToPosition:

    """Verify the full Voteview cast-code → canonical position mapping."""

    def test_code_1_yea(self):

        assert cast_code_to_position(1) == "Yea"

    def test_code_2_paired_yea(self):

        assert cast_code_to_position(2) == "Yea"

    def test_code_3_announced_yea(self):

        assert cast_code_to_position(3) == "Yea"

    def test_code_4_announced_nay(self):

        assert cast_code_to_position(4) == "Nay"

    def test_code_5_paired_nay(self):

        assert cast_code_to_position(5) == "Nay"

    def test_code_6_nay(self):

        assert cast_code_to_position(6) == "Nay"

    def test_code_7_present_v1(self):

        assert cast_code_to_position(7) == "Present"

    def test_code_8_present_v2(self):

        assert cast_code_to_position(8) == "Present"

    def test_code_9_not_voting(self):

        assert cast_code_to_position(9) == "Not Voting"

    def test_code_0_not_a_member(self):

        """Code 0 = not a member / absent no record → Not Voting."""

        assert cast_code_to_position(0) == "Not Voting"

    def test_all_codes_covered(self):

        """Every code in the map must return a valid canonical position."""

        valid = {"Yea", "Nay", "Present", "Not Voting"}

        for code in range(0, 10):

            pos = cast_code_to_position(code)

            assert pos in valid, f"cast_code {code} → {pos!r} is not in {valid}"

    def test_string_codes_accepted(self):

        """CSV columns are strings; the function must coerce them."""

        assert cast_code_to_position("1") == "Yea"

        assert cast_code_to_position("6") == "Nay"

        assert cast_code_to_position("9") == "Not Voting"

    def test_float_string_accepted(self):

        """Voteview CSVs sometimes encode integers as floats (e.g. '1.0')."""

        assert cast_code_to_position("1.0") == "Yea"

        assert cast_code_to_position("6.0") == "Nay"

    def test_unknown_code_returns_none(self):

        assert cast_code_to_position(99)  is None

        assert cast_code_to_position(-1)  is None

        assert cast_code_to_position(10)  is None

    def test_bad_input_returns_none(self):

        assert cast_code_to_position("") is None

        assert cast_code_to_position(None) is None

        assert cast_code_to_position("x") is None

    def test_map_has_exactly_ten_entries(self):

        """The codebook defines codes 0-9; no other codes should exist."""

        assert set(CAST_CODE_MAP.keys()) == set(range(0, 10))

    def test_yea_group_codes_all_map_to_yea(self):

        for code in (1, 2, 3):

            assert CAST_CODE_MAP[code] == "Yea", f"code {code} should be Yea"

    def test_nay_group_codes_all_map_to_nay(self):

        for code in (4, 5, 6):

            assert CAST_CODE_MAP[code] == "Nay", f"code {code} should be Nay"

    def test_present_group_codes_map_to_present(self):

        for code in (7, 8):

            assert CAST_CODE_MAP[code] == "Present", f"code {code} should be Present"

    def test_not_voting_group_codes_map_to_not_voting(self):

        for code in (0, 9):

            assert CAST_CODE_MAP[code] == "Not Voting", f"code {code} should be Not Voting"

class TestSourceUrls:

    def test_house_url_format(self):

        url = _house_source_url(2023, 42)

        assert url == "https://clerk.house.gov/Votes/2023/roll042"

        assert url.startswith("https://clerk.house.gov")

    def test_house_url_zero_pads_to_3_digits(self):

        assert _house_source_url(2024, 1).endswith("roll001")

        assert _house_source_url(2024, 99).endswith("roll099")

        assert _house_source_url(2024, 100).endswith("roll100")

    def test_senate_url_format(self):

        url = _senate_source_url(118, 1, 42)

        assert url == (

            "https://www.senate.gov/legislative/LIS/roll_call_votes/"

            "vote1181/vote_118_1_00042.xml"

        )

        assert "senate.gov" in url

    def test_senate_url_zero_pads_roll_to_5_digits(self):

        url = _senate_source_url(119, 2, 7)

        assert "vote_119_2_00007.xml" in url

    def test_house_url_host_is_gov(self):

        from ingest.source_validator import is_allowlisted

        assert is_allowlisted(_house_source_url(2025, 1))

    def test_senate_url_host_is_gov(self):

        from ingest.source_validator import is_allowlisted

        assert is_allowlisted(_senate_source_url(119, 1, 1))

class TestDeriveSession:

    def test_csv_session_preferred(self):

        assert _derive_session("2023-01-15", "1") == 1

        assert _derive_session("2024-06-01", "2") == 2

    def test_odd_year_fallback_to_session_1(self):

        assert _derive_session("2023-03-01", "") == 1

        assert _derive_session("2021-07-04", "") == 1

    def test_even_year_fallback_to_session_2(self):

        assert _derive_session("2024-11-01", "") == 2

        assert _derive_session("2022-01-01", "") == 2

    def test_bad_date_defaults_to_1(self):

        assert _derive_session("", "") == 1

        assert _derive_session("bad-date", "x") == 1

class TestEnsureVoteviewSchema:

    def test_crosswalk_table_created(self):

        conn, tmp = _make_db()

        tables = {

            row[0]

            for row in conn.execute(

                "SELECT name FROM sqlite_master WHERE type='table'"

            )

        }

        assert "voteview_crosswalk" in tables

        conn.close()

        os.unlink(tmp)

    def test_year_column_added_to_votes(self):

        conn, tmp = _make_db()

        cols = {

            row[1]

            for row in conn.execute("PRAGMA table_info(votes)")

        }

        assert "year" in cols, "year column must be added to votes table"

        assert "description" in cols

        assert "voteview_rollnumber" in cols

        conn.close()

        os.unlink(tmp)

    def test_year_index_exists(self):

        conn, tmp = _make_db()

        indexes = {

            row[0]

            for row in conn.execute(

                "SELECT name FROM sqlite_master WHERE type='index'"

            )

        }

        assert "idx_votes_year" in indexes

        conn.close()

        os.unlink(tmp)

    def test_idempotent_multiple_calls(self):

        conn, tmp = _make_db()

        ensure_voteview_schema(conn)

        ensure_voteview_schema(conn)

        conn.close()

        os.unlink(tmp)

class TestBuildCrosswalk:

    """Build the crosswalk from a mock legislators JSON (no HTTP)."""

    _MOCK_LEGISLATORS = [

        {

            "id": {"bioguide": "A000001", "icpsr": 12345},

            "name": {"official_full": "Alice Anderson"},

            "terms": [{"type": "rep", "state": "NY"}],

        },

        {

            "id": {"bioguide": "B000002", "icpsr": 67890},

            "name": {"official_full": "Bob Brown"},

            "terms": [{"type": "sen", "state": "CA"}],

        },

        {

            "id": {"bioguide": "C000003"},

            "name": {"official_full": "Carol Carter"},

            "terms": [{"type": "rep", "state": "TX"}],

        },

        {

            "id": {"bioguide": "D000004", "icpsr": 11111, "icpsr_hn": 22222},

            "name": {"official_full": "Dave Davis"},

            "terms": [{"type": "rep", "state": "FL"}],

        },

    ]

    def _make_crosswalk_from_mock(self) -> tuple[dict, sqlite3.Connection, str]:

        """

        Build the crosswalk by directly inserting mock data, bypassing HTTP.

        Returns (crosswalk_dict, conn, tmp_path).

        """

        conn, tmp = _make_db()

        now = "2025-01-01T00:00:00+00:00"

        for leg in self._MOCK_LEGISLATORS:

            ids      = leg.get("id", {})

            bioguide = ids.get("bioguide", "").strip()

            full_name = leg.get("name", {}).get("official_full", "")

            for field in ("icpsr", "icpsr_hn"):

                raw = ids.get(field)

                if raw is None:

                    continue

                try:

                    icpsr_int = int(float(raw))

                except (ValueError, TypeError):

                    continue

                conn.execute(

                    """

                    INSERT INTO voteview_crosswalk

                        (icpsr_id, bioguide_id, full_name, fetched_at)

                    VALUES (?, ?, ?, ?)

                    ON CONFLICT(icpsr_id) DO UPDATE SET

                        bioguide_id = excluded.bioguide_id,

                        full_name   = excluded.full_name,

                        fetched_at  = excluded.fetched_at

                    """,

                    (icpsr_int, bioguide, full_name, now),

                )

        conn.commit()

        crosswalk = build_crosswalk(conn, force_refresh=False)

        return crosswalk, conn, tmp

    def test_primary_icpsr_mapped(self):

        cw, conn, tmp = self._make_crosswalk_from_mock()

        assert cw[12345] == "A000001"

        assert cw[67890] == "B000002"

        conn.close()

        os.unlink(tmp)

    def test_icpsr_hn_also_mapped(self):

        """Both icpsr and icpsr_hn for D000004 should map to the same bioguide."""

        cw, conn, tmp = self._make_crosswalk_from_mock()

        assert cw[11111] == "D000004"

        assert cw[22222] == "D000004"

        conn.close()

        os.unlink(tmp)

    def test_member_without_icpsr_not_in_crosswalk(self):

        cw, conn, tmp = self._make_crosswalk_from_mock()

        bioguides = set(cw.values())

        assert "C000003" not in bioguides or all(

            v != "C000003" for v in cw.values()

        )

        conn.close()

        os.unlink(tmp)

    def test_cached_load_returns_same_data(self):

        """Second call (force_refresh=False) should return the same crosswalk."""

        cw1, conn, tmp = self._make_crosswalk_from_mock()

        cw2 = build_crosswalk(conn, force_refresh=False)

        assert cw1 == cw2

        conn.close()

        os.unlink(tmp)

    def test_unknown_icpsr_returns_none_from_get(self):

        cw, conn, tmp = self._make_crosswalk_from_mock()

        assert cw.get(99999) is None

        conn.close()

        os.unlink(tmp)

class TestYearIndexHelpers:

    """

    Seed a minimal DB with known votes + positions, then verify the

    year-index helper queries return the correct rows.

    """

    def _seed_db(self) -> tuple[sqlite3.Connection, str]:

        conn, tmp = _make_db()

        for bioguide, name, state in [

            ("N000002", "Jerry Nadler", "NY"),

            ("C000714", "James Clyburn", "SC"),

        ]:

            conn.execute(

                "INSERT INTO members "

                "(bioguide_id, full_name, party, state, chamber, source_url) "

                "VALUES (?, ?, ?, ?, ?, ?)",

                (

                    bioguide, name, "D", state, "house",

                    f"https://bioguide.congress.gov/search/bio/{bioguide}",

                ),

            )

        votes_data = [

            ("house", "2023-01-19", 2023, 213, 215, "Failed",

             "https://clerk.house.gov/Votes/2023/roll010", 118, 10),

            ("house", "2023-06-01", 2023, 220, 210, "Passed",

             "https://clerk.house.gov/Votes/2023/roll200", 118, 200),

            ("house", "2024-03-15", 2024, 230, 200, "Passed",

             "https://clerk.house.gov/Votes/2024/roll100", 118, 100),

        ]

        vote_ids = []

        for row in votes_data:

            conn.execute(

                """

                INSERT INTO votes

                    (chamber, date, year, yea, nay, result, source_url,

                     congress, roll_number, recorded, present, not_voting)

                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 0, 0)

                """,

                row,

            )

            vid = conn.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]

            vote_ids.append(vid)

        for vid, pos in zip(vote_ids, ["Yea", "Nay", "Yea"]):

            conn.execute(

                "INSERT INTO vote_positions (vote_id, member_bioguide, member_id, position) "

                "VALUES (?, ?, NULL, ?)",

                (vid, "N000002", pos),

            )

        conn.execute(

            "INSERT INTO vote_positions (vote_id, member_bioguide, member_id, position) "

            "VALUES (?, ?, NULL, ?)",

            (vote_ids[0], "C000714", "Yea"),

        )

        conn.commit()

        return conn, tmp

    def test_votes_by_member_year_returns_correct_count(self):

        conn, tmp = self._seed_db()

        rows = conn.execute(

            """

            SELECT * FROM vote_positions vp

            JOIN votes v ON v.id = vp.vote_id

            WHERE vp.member_bioguide = 'N000002' AND v.year = 2023

            """

        ).fetchall()

        assert len(rows) == 2, "N000002 voted on 2 rolls in 2023"

        conn.close()

        os.unlink(tmp)

    def test_votes_by_member_year_2024(self):

        conn, tmp = self._seed_db()

        rows = conn.execute(

            """

            SELECT * FROM vote_positions vp

            JOIN votes v ON v.id = vp.vote_id

            WHERE vp.member_bioguide = 'N000002' AND v.year = 2024

            """

        ).fetchall()

        assert len(rows) == 1

        assert dict(rows[0])["date"] == "2024-03-15"

        conn.close()

        os.unlink(tmp)

    def test_votes_by_member_year_empty_for_nonexistent_year(self):

        conn, tmp = self._seed_db()

        rows = conn.execute(

            """

            SELECT * FROM vote_positions vp

            JOIN votes v ON v.id = vp.vote_id

            WHERE vp.member_bioguide = 'N000002' AND v.year = 2019

            """

        ).fetchall()

        assert rows == []

        conn.close()

        os.unlink(tmp)

    def test_votes_in_year_2023(self):

        conn, tmp = self._seed_db()

        rows = conn.execute(

            "SELECT * FROM votes WHERE year = 2023 AND recorded = 1"

        ).fetchall()

        assert len(rows) == 2

        dates = sorted(dict(r)["date"] for r in rows)

        assert dates == ["2023-01-19", "2023-06-01"]

        conn.close()

        os.unlink(tmp)

    def test_votes_in_year_2024(self):

        conn, tmp = self._seed_db()

        rows = conn.execute(

            "SELECT * FROM votes WHERE year = 2024 AND recorded = 1"

        ).fetchall()

        assert len(rows) == 1

        assert dict(rows[0])["roll_number"] == 100

        conn.close()

        os.unlink(tmp)

    def test_year_index_is_used(self):

        """EXPLAIN QUERY PLAN should mention the year index (not a full scan)."""

        conn, tmp = self._seed_db()

        plan = conn.execute(

            "EXPLAIN QUERY PLAN SELECT * FROM votes WHERE year = 2023"

        ).fetchall()

        plan_text = " ".join(

            " ".join(str(col) for col in tuple(row))

            for row in plan

        ).lower()

        assert "idx_votes_year" in plan_text, (

            f"Expected idx_votes_year in query plan; got: {plan_text!r}"

        )

        conn.close()

        os.unlink(tmp)

    def test_position_enum_enforced_for_voteview_positions(self):

        """vote_positions CHECK constraint rejects cast_code-derived invalid positions."""

        conn, tmp = _make_db()

        conn.execute(

            "INSERT INTO votes (chamber, date, year, yea, nay, result, source_url, recorded) "

            "VALUES ('house', '2023-01-01', 2023, 1, 0, 'Passed', "

            "'https://clerk.house.gov/Votes/2023/roll001', 1)"

        )

        vid = conn.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]

        with pytest.raises(sqlite3.IntegrityError):

            conn.execute(

                "INSERT INTO vote_positions (vote_id, member_bioguide, position) "

                "VALUES (?, ?, ?)",

                (vid, "Z000001", "Abstain"),

            )

            conn.commit()

        conn.close()

        os.unlink(tmp)

class TestSchemaIdempotency:

    """Running schema setup multiple times must not corrupt the DB."""

    def test_full_schema_setup_multiple_times(self):

        conn, tmp = _make_db()

        for _ in range(3):

            init_schema(conn)

            ensure_votes_schema(conn)

            ensure_voteview_schema(conn)

        tables = {

            row[0]

            for row in conn.execute(

                "SELECT name FROM sqlite_master WHERE type='table'"

            )

        }

        for t in ("votes", "vote_positions", "members", "voteview_crosswalk"):

            assert t in tables

        conn.close()

        os.unlink(tmp)

    def test_cast_code_coverage_against_schema_enum(self):

        """

        Every position value produced by CAST_CODE_MAP must satisfy the

        vote_positions CHECK constraint (Yea, Nay, Present, Not Voting).

        """

        valid = {"Yea", "Nay", "Present", "Not Voting"}

        for code, position in CAST_CODE_MAP.items():

            assert position in valid, (

                f"CAST_CODE_MAP[{code}] = {position!r} is not in the DB enum {valid}"

            )