"""Integration tests for XER parsing and database loading."""

from pathlib import Path

import pytest

from xer_mcp.db import db


@pytest.fixture(autouse=True)
def setup_db():
    """Initialize and clear database for each test."""
    db.initialize()
    yield
    db.clear()


class TestXerParsing:
    """Integration tests for parsing XER files and loading into database."""

    def test_load_single_project_xer(self, sample_xer_single_project: Path) -> None:
        """Should parse XER and load data into SQLite database."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_single_project)

        # Load all data for the single project
        load_parsed_data(parsed, project_id="1001")

        # Verify data in database
        with db.cursor() as cur:
            cur.execute("SELECT COUNT(*) FROM projects")
            assert cur.fetchone()[0] == 1

            cur.execute("SELECT COUNT(*) FROM activities")
            assert cur.fetchone()[0] == 5

            cur.execute("SELECT COUNT(*) FROM relationships")
            assert cur.fetchone()[0] == 5

            cur.execute("SELECT COUNT(*) FROM wbs")
            assert cur.fetchone()[0] == 3

    def test_load_preserves_date_precision(self, sample_xer_single_project: Path) -> None:
        """Should preserve date/time precision from XER file."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_single_project)
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            cur.execute("SELECT target_start_date FROM activities WHERE task_code = ?", ("A1010",))
            date_str = cur.fetchone()[0]
            # Should be ISO8601 with time component
            assert "T" in date_str
            assert "07:00" in date_str

    def test_load_activities_indexed_by_type(self, sample_xer_single_project: Path) -> None:
        """Should be able to efficiently query activities by type."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_single_project)
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            # Query milestones
            cur.execute("SELECT COUNT(*) FROM activities WHERE task_type = ?", ("TT_Mile",))
            milestone_count = cur.fetchone()[0]
            assert milestone_count == 2

            # Query tasks
            cur.execute("SELECT COUNT(*) FROM activities WHERE task_type = ?", ("TT_Task",))
            task_count = cur.fetchone()[0]
            assert task_count == 3

    def test_load_critical_path_activities(self, sample_xer_single_project: Path) -> None:
        """Should be able to query critical path activities via index."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_single_project)
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            cur.execute("SELECT COUNT(*) FROM activities WHERE driving_path_flag = 1")
            critical_count = cur.fetchone()[0]
            # Activities A1000, A1010, A1030, A1040 are on critical path
            assert critical_count == 4

    def test_load_replaces_previous_data(self, sample_xer_single_project: Path) -> None:
        """Loading a new file should replace previous data."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_single_project)

        # Load first time
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            cur.execute("SELECT COUNT(*) FROM activities")
            first_count = cur.fetchone()[0]

        # Clear and load again
        db.clear()
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            cur.execute("SELECT COUNT(*) FROM activities")
            second_count = cur.fetchone()[0]

        assert first_count == second_count


class TestMultiProjectHandling:
    """Integration tests for multi-project XER file handling."""

    def test_load_selected_project_from_multi(self, sample_xer_multi_project: Path) -> None:
        """Should load only the selected project from multi-project file."""
        from xer_mcp.db.loader import load_parsed_data
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_multi_project)

        # Load only Project Alpha
        load_parsed_data(parsed, project_id="1001")

        with db.cursor() as cur:
            cur.execute("SELECT proj_short_name FROM projects")
            names = [row[0] for row in cur.fetchall()]
            assert names == ["Project Alpha"]

            cur.execute("SELECT COUNT(*) FROM activities WHERE proj_id = ?", ("1001",))
            assert cur.fetchone()[0] == 1

    def test_multi_project_list_available(self, sample_xer_multi_project: Path) -> None:
        """Parser should report all available projects."""
        from xer_mcp.parser.xer_parser import XerParser

        parser = XerParser()
        parsed = parser.parse(sample_xer_multi_project)

        assert len(parsed.projects) == 2
        proj_ids = {p["proj_id"] for p in parsed.projects}
        assert proj_ids == {"1001", "1002"}