Skip to content

Enterprise agent platform for building, deploying, and operating AI teammates.

56.4 KB
Raw Blame
import json
import subprocess
import sys
from unittest.mock import Mock

import pytest
import requests

from xagent.config import get_tool_max_output_length
from xagent.web.tools.mcp import excel


def test_excel_mcp_does_not_import_agent_package() -> None:
    probe = (
        "import sys; "
        "import xagent.web.tools.mcp.excel; "
        "print(len([n for n in sys.modules "
        "if n.startswith('xagent.core.agent')]))"
    )
    completed = subprocess.run(
        [sys.executable, "-c", probe],
        capture_output=True,
        text=True,
        check=True,
    )

    assert completed.stdout.strip() == "0"


class MockResponse:
    def __init__(self, json_data=None, status_code=200, content=None, url=None):
        self._json_data = json_data if json_data is not None else {}
        self.status_code = status_code
        self.content = (
            json.dumps(self._json_data).encode("utf-8") if content is None else content
        )
        self.text = self.content.decode("utf-8", errors="replace")
        self.url = url or "https://graph.microsoft.com/v1.0/example"
        self.closed = False

    def json(self):
        return self._json_data

    def iter_content(self, chunk_size):
        for offset in range(0, len(self.content), chunk_size):
            yield self.content[offset : offset + chunk_size]

    def close(self):
        self.closed = True

    def raise_for_status(self):
        if self.status_code >= 400:
            raise requests.HTTPError(
                f"{self.status_code} Client Error: Error for url: {self.url}",
                response=self,
            )


def range_response(values, *, text=None, row_count=None, column_count=None):
    if text is None:
        text = values
    return MockResponse(
        {
            "rowCount": len(values) if row_count is None else row_count,
            "columnCount": (
                max((len(row) for row in values), default=0)
                if column_count is None
                else column_count
            ),
            "values": values,
            "text": text,
        }
    )


@pytest.fixture(autouse=True)
def _credentials(monkeypatch):
    monkeypatch.setenv("AUTH_TOKEN", "test-graph-token")


# ---------------------------------------------------------------------------
# path/address helpers
# ---------------------------------------------------------------------------


def test_workbook_base_defaults_to_own_onedrive():
    assert (
        excel._workbook_base("Reports/Q1.xlsx", None, None)
        == "/me/drive/root:/Reports/Q1.xlsx:/workbook"
    )


def test_workbook_base_uses_drive_id_only():
    assert (
        excel._workbook_base("book.xlsx", None, "drive-1")
        == "/drives/drive-1/root:/book.xlsx:/workbook"
    )


def test_workbook_base_uses_globally_addressable_drive_when_site_is_also_supplied():
    assert (
        excel._workbook_base("book.xlsx", "root", "drive-1")
        == "/drives/drive-1/root:/book.xlsx:/workbook"
    )


def test_workbook_base_uses_site_default_drive():
    assert (
        excel._workbook_base("book.xlsx", "root", None)
        == "/sites/root/drive/root:/book.xlsx:/workbook"
    )


def test_workbook_base_terminates_path_form_site_before_default_drive():
    assert (
        excel._workbook_base("book.xlsx", "contoso.sharepoint.com:/teams/finance", None)
        == "/sites/contoso.sharepoint.com:/teams/finance:/drive/root:/book.xlsx:/workbook"
    )


def test_workbook_base_ignores_path_form_site_when_specific_drive_is_supplied():
    assert (
        excel._workbook_base(
            "book.xlsx", "contoso.sharepoint.com:/teams/finance", "drive-1"
        )
        == "/drives/drive-1/root:/book.xlsx:/workbook"
    )


def test_workbook_base_validates_site_with_specific_drive():
    with pytest.raises(ValueError, match="must not be '.' or '..'"):
        excel._workbook_base("book.xlsx", "..", "drive-1")


def test_workbook_base_rejects_dot_segments():
    with pytest.raises(ValueError, match="must not contain"):
        excel._workbook_base("../secret.xlsx", None, None)


def test_workbook_base_rejects_trailing_slash():
    with pytest.raises(ValueError, match="must include a filename"):
        excel._workbook_base("Reports/", None, None)


def test_workbook_base_rejects_consecutive_slashes():
    with pytest.raises(ValueError, match="empty segments"):
        excel._workbook_base("Reports//Q1.xlsx", None, None)


def test_workbook_base_rejects_trailing_period_filename():
    """A trailing-dot filename can be silently normalized by Graph/SharePoint's
    backing storage to the name without the dot, so "Report.xlsx." could
    silently resolve to a real, different "Report.xlsx" workbook."""
    with pytest.raises(ValueError, match="must not end with a period"):
        excel._workbook_base("Report.xlsx.", None, None)


def test_workbook_base_rejects_malicious_site_id():
    with pytest.raises(ValueError, match="must not contain"):
        excel._workbook_base("book.xlsx", "contoso.sharepoint.com:/../etc", None)


@pytest.mark.parametrize("site_id", [".", ".."])
def test_workbook_base_rejects_dot_only_site_id(site_id):
    with pytest.raises(ValueError, match="must not be"):
        excel._workbook_base("book.xlsx", site_id, None)


def test_workbook_base_rejects_empty_string_site_id():
    with pytest.raises(ValueError, match="site_id is required"):
        excel._workbook_base("book.xlsx", "", None)


def test_workbook_base_rejects_whitespace_only_drive_id():
    with pytest.raises(ValueError, match="drive_id"):
        excel._workbook_base("book.xlsx", None, "   ")


def test_workbook_base_rejects_whitespace_only_site_id():
    with pytest.raises(ValueError, match="site_id"):
        excel._workbook_base("book.xlsx", "   ", "drive-1")


def test_workbook_base_rejects_trailing_period_on_intermediate_segment():
    """The trailing-period hazard applies to every path segment, not just
    the filename -- "Reports./Q1.xlsx" must be rejected even though the
    filename itself is fine."""
    with pytest.raises(ValueError, match="must not end with a period"):
        excel._workbook_base("Reports./Q1.xlsx", None, None)


def test_workbook_base_rejects_segment_with_trailing_whitespace():
    with pytest.raises(ValueError, match="whitespace"):
        excel._workbook_base("Reports /Q1.xlsx", None, None)


def test_workbook_base_rejects_segment_with_leading_whitespace():
    with pytest.raises(ValueError, match="whitespace"):
        excel._workbook_base("Reports/ Q1.xlsx", None, None)


@pytest.mark.parametrize("path", [" Reports/Q1.xlsx", "Reports/Q1.xlsx "])
def test_workbook_base_rejects_boundary_whitespace(path):
    with pytest.raises(ValueError, match="whitespace"):
        excel._workbook_base(path, None, None)


def test_workbook_base_rejects_leading_separator():
    with pytest.raises(ValueError, match="must be relative"):
        excel._workbook_base("/Reports/Q1.xlsx", None, None)


def test_site_segment_rejects_empty_segment():
    with pytest.raises(ValueError, match="empty segments"):
        excel._site_segment("contoso.sharepoint.com:/a//b")


@pytest.mark.parametrize("site_id", ["foo/bar", "foo:bar", "host:/a:/b"])
def test_site_segment_rejects_malformed_structural_delimiters(site_id):
    with pytest.raises(ValueError, match="site_id"):
        excel._site_segment(site_id)


def test_site_segment_encodes_each_path_component_without_losing_structure():
    assert (
        excel._site_segment("contoso.sharepoint.com:/teams/Finance & Legal")
        == "contoso.sharepoint.com:/teams/Finance%20%26%20Legal"
    )


def test_odata_key_segment_escapes_quote():
    assert (
        excel._odata_key_segment("worksheets", "O'Brien")
        == "worksheets('O%27%27Brien')"
    )


def test_odata_key_segment_preserves_padded_worksheet_name():
    assert (
        excel._odata_key_segment("worksheets", " Sheet ") == "worksheets('%20Sheet%20')"
    )


def test_odata_string_literal_escapes_quote():
    assert excel._odata_string_literal("A1:B2") == "A1%3AB2"
    assert excel._odata_string_literal("O'Brien!A1") == "O%27%27Brien%21A1"


def test_normalize_relative_path_rejects_non_string():
    with pytest.raises(TypeError, match="must be a string"):
        excel._normalize_relative_path(123)


def test_odata_string_literal_rejects_non_string():
    with pytest.raises(TypeError, match="must be a string"):
        excel._odata_string_literal(123)


def test_odata_string_literal_rejects_empty_string():
    with pytest.raises(ValueError, match="address is required"):
        excel._odata_string_literal("")


def test_valid_clear_apply_to_is_immutable():
    assert isinstance(excel._VALID_CLEAR_APPLY_TO, frozenset)


def test_parse_values_json_requires_array_of_arrays():
    with pytest.raises(ValueError, match="array of arrays"):
        excel._parse_values_json('["a", "b"]')


def test_parse_values_json_rejects_invalid_json():
    with pytest.raises(ValueError, match="not valid JSON"):
        excel._parse_values_json("{not json")


@pytest.mark.parametrize("constant", ["NaN", "Infinity", "-Infinity"])
def test_parse_values_json_rejects_non_json_numeric_constants(constant):
    with pytest.raises(ValueError, match="non-JSON numeric value"):
        excel._parse_values_json(f"[[{constant}]]")


def test_parse_values_json_success():
    assert excel._parse_values_json('[["a", 1], ["b", 2]]') == [["a", 1], ["b", 2]]


def test_parse_values_json_rejects_non_string():
    with pytest.raises(TypeError, match="must be a string"):
        excel._parse_values_json(123)


# ---------------------------------------------------------------------------
# worksheets
# ---------------------------------------------------------------------------


def test_list_worksheets_success(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse({"value": [{"id": "1", "name": "Sheet1"}]})
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["status"] == "success"
    assert result["worksheets"] == [{"id": "1", "name": "Sheet1"}]
    assert mock_request.call_args.kwargs["url"].endswith(
        "/me/drive/root:/book.xlsx:/workbook/worksheets"
    )


def test_add_worksheet_with_name(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"id": "1", "name": "NewSheet"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_add_worksheet("book.xlsx", name="NewSheet"))

    assert result["status"] == "success"
    kwargs = mock_request.call_args.kwargs
    assert kwargs["url"].endswith("/workbook/worksheets/add")
    assert kwargs["json"] == {"name": "NewSheet"}


def test_add_worksheet_without_name_sends_empty_body(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"id": "1", "name": "Sheet2"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_add_worksheet("book.xlsx"))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["json"] == {}


@pytest.mark.parametrize("name", ["", "   "])
def test_add_worksheet_rejects_explicit_blank_name_without_request(monkeypatch, name):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_add_worksheet("book.xlsx", name=name))

    assert result["status"] == "error"
    assert "name" in result["message"]
    mock_request.assert_not_called()


def test_list_worksheets_exposes_next_link(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse(
            {
                "value": [{"id": "1", "name": "Sheet1"}],
                "@odata.nextLink": "https://graph.microsoft.com/v1.0/next-page",
            }
        )
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["next_link"] == "https://graph.microsoft.com/v1.0/next-page"
    assert mock_request.call_args.kwargs["params"] == {"$top": 20}


def test_list_worksheets_next_link_is_none_on_last_page(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse({"value": [{"id": "1", "name": "Sheet1"}]})
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["next_link"] is None


def test_list_worksheets_consumes_valid_next_link(monkeypatch):
    next_link = (
        "https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/workbook/"
        "worksheets?$skiptoken=page-2"
    )
    mock_request = Mock(return_value=MockResponse({"value": [{"id": "2"}]}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx", next_link=next_link))

    assert result["worksheets"] == [{"id": "2"}]
    assert mock_request.call_args.kwargs["url"] == next_link
    assert mock_request.call_args.kwargs["params"] is None


def test_list_worksheets_applies_requested_page_size(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"value": []}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx", page_size=5))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["params"] == {"$top": 5}


@pytest.mark.parametrize("page_size", [True, 0, 101, "5"])
def test_list_worksheets_rejects_invalid_page_size(monkeypatch, page_size):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx", page_size=page_size))

    assert result["status"] == "error"
    assert "page_size" in result["message"]
    mock_request.assert_not_called()


def test_list_worksheets_rejects_next_link_for_another_collection(monkeypatch):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_worksheets(
            "book.xlsx",
            next_link="https://graph.microsoft.com/v1.0/me/messages?$skiptoken=x",
        )
    )

    assert result["status"] == "error"
    mock_request.assert_not_called()


def test_delete_worksheet_uses_odata_key_segment(monkeypatch):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_delete_worksheet("book.xlsx", "Sheet1"))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith("worksheets('Sheet1')")


@pytest.mark.parametrize(
    "side_effect",
    [
        requests.ReadTimeout("response timed out"),
        requests.ConnectionError("connection dropped"),
    ],
)
def test_delete_worksheet_transport_failure_is_indeterminate(monkeypatch, side_effect):
    monkeypatch.setattr(excel.requests, "request", Mock(side_effect=side_effect))

    result = json.loads(excel.excel_delete_worksheet("book.xlsx", "Sheet1"))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "may already have been applied" in result["message"]


def test_delete_worksheet_server_failure_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "gateway"}, status_code=503)),
    )

    result = json.loads(excel.excel_delete_worksheet("book.xlsx", "Sheet1"))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "HTTP 503" in result["message"]


@pytest.mark.parametrize(
    ("start_column", "end_column", "expected"),
    [("L", "M", "L:M"), ("L", "L", "L:L"), (" a ", "xFd", "A:XFD")],
)
def test_delete_columns_uses_structural_left_shift(
    monkeypatch, start_column, end_column, expected
):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_delete_columns("book.xlsx", "Sheet1", start_column, end_column)
    )

    assert result["status"] == "success"
    assert result["deleted_range"] == expected
    kwargs = mock_request.call_args.kwargs
    assert kwargs["method"] == "POST"
    assert kwargs["url"].endswith(
        f"worksheets('Sheet1')/range(address='{expected.replace(':', '%3A')}')/delete"
    )
    assert kwargs["json"] == {"shift": "Left"}


def test_delete_columns_uses_drive_route_when_site_and_drive_are_supplied(monkeypatch):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_delete_columns(
            "Marketing.xlsx",
            "Campaign Performance",
            "G",
            "G",
            site_id="contoso.sharepoint.com,site-id,web-id",
            drive_id="b!documents-drive",
        )
    )

    assert result["status"] == "success"
    kwargs = mock_request.call_args.kwargs
    assert kwargs["url"] == (
        f"{excel.GRAPH_BASE_URL}/drives/b%21documents-drive/root:/Marketing.xlsx:/workbook/"
        "worksheets('Campaign%20Performance')/range(address='G%3AG')/delete"
    )


@pytest.mark.parametrize("label", ["$L", "L1", "Sheet1!L", "ß", "ff", "ı"])
def test_delete_columns_rejects_malformed_or_non_ascii_labels(monkeypatch, label):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_delete_columns("book.xlsx", "Sheet1", label, "M"))

    assert result["status"] == "error"
    assert "column must be an Excel column label" in result["message"]
    mock_request.assert_not_called()


@pytest.mark.parametrize(
    ("start_column", "end_column", "message"),
    [
        ("", "M", "column must be an Excel column label"),
        ("L", "XFE", "column must be an Excel column label"),
        ("M", "L", "start_column must not be after end_column"),
    ],
)
def test_delete_columns_rejects_invalid_ranges(
    monkeypatch, start_column, end_column, message
):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_delete_columns("book.xlsx", "Sheet1", start_column, end_column)
    )

    assert result["status"] == "error"
    assert message in result["message"]
    mock_request.assert_not_called()


def test_delete_columns_transport_failure_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(side_effect=requests.ConnectionError("connection dropped")),
    )

    result = json.loads(excel.excel_delete_columns("book.xlsx", "Sheet1", "L", "M"))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False


@pytest.mark.parametrize(
    ("status_code", "expected_status"), [(400, "error"), (503, "indeterminate")]
)
def test_delete_columns_maps_graph_failures(monkeypatch, status_code, expected_status):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "failure"}, status_code=status_code)),
    )

    result = json.loads(excel.excel_delete_columns("book.xlsx", "Sheet1", "L", "M"))

    assert result["status"] == expected_status
    if expected_status == "indeterminate":
        assert result["retry_safe"] is False


def test_delete_columns_declares_destructive_non_idempotent_annotation():
    tool = excel.mcp._tool_manager.get_tool("excel_delete_columns")

    assert tool.annotations is not None
    assert tool.annotations.destructiveHint is True
    assert tool.annotations.idempotentHint is False


@pytest.mark.parametrize(
    ("start_row", "end_row", "expected"),
    [(5, 5, "5:5"), (5, 7, "5:7"), (1, 1_048_576, "1:1048576")],
)
def test_delete_rows_uses_structural_up_shift(
    monkeypatch, start_row, end_row, expected
):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_delete_rows("book.xlsx", "Sheet1", start_row, end_row)
    )

    assert result["status"] == "success"
    assert result["deleted_range"] == expected
    # One whole-row delete and nothing else: no read-modify-write that could
    # shift only part of a row and leave the other columns misaligned.
    assert mock_request.call_count == 1
    kwargs = mock_request.call_args.kwargs
    assert kwargs["method"] == "POST"
    assert kwargs["url"].endswith(
        f"worksheets('Sheet1')/range(address='{expected.replace(':', '%3A')}')/delete"
    )
    assert kwargs["json"] == {"shift": "Up"}


@pytest.mark.parametrize("row", [True, "5", 5.0, None])
@pytest.mark.parametrize("field", ["start_row", "end_row"])
def test_delete_rows_rejects_non_integer_rows(monkeypatch, field, row):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)
    row_args = {"start_row": 5, "end_row": 7, field: row}

    result = json.loads(excel.excel_delete_rows("book.xlsx", "Sheet1", **row_args))

    assert result["status"] == "error"
    assert f"{field} must be an integer" in result["message"]
    mock_request.assert_not_called()


@pytest.mark.parametrize(
    ("start_row", "end_row", "message"),
    [
        (0, 5, "start_row must be between 1 and 1048576"),
        (-1, 5, "start_row must be between 1 and 1048576"),
        (5, 1_048_577, "end_row must be between 1 and 1048576"),
        (7, 5, "start_row must not be after end_row"),
    ],
)
def test_delete_rows_rejects_invalid_ranges(monkeypatch, start_row, end_row, message):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_delete_rows("book.xlsx", "Sheet1", start_row, end_row)
    )

    assert result["status"] == "error"
    assert message in result["message"]
    mock_request.assert_not_called()


def test_delete_rows_transport_failure_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(side_effect=requests.ConnectionError("connection dropped")),
    )

    result = json.loads(excel.excel_delete_rows("book.xlsx", "Sheet1", 5, 5))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False


@pytest.mark.parametrize(
    ("status_code", "expected_status"), [(400, "error"), (503, "indeterminate")]
)
def test_delete_rows_maps_graph_failures(monkeypatch, status_code, expected_status):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "failure"}, status_code=status_code)),
    )

    result = json.loads(excel.excel_delete_rows("book.xlsx", "Sheet1", 5, 5))

    assert result["status"] == expected_status
    if expected_status == "indeterminate":
        assert result["retry_safe"] is False


def test_delete_rows_declares_destructive_non_idempotent_annotation():
    tool = excel.mcp._tool_manager.get_tool("excel_delete_rows")

    assert tool.annotations is not None
    assert tool.annotations.destructiveHint is True
    assert tool.annotations.idempotentHint is False


# ---------------------------------------------------------------------------
# ranges
# ---------------------------------------------------------------------------


def test_get_range_with_address(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"address": "Sheet1!A1:B2"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_get_range("book.xlsx", "Sheet1", address="A1:B2"))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith(
        "worksheets('Sheet1')/range(address='A1%3AB2')"
    )


def test_get_range_without_address_omits_function_call(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"address": "Sheet1!A1:Z100"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_get_range("book.xlsx", "Sheet1"))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith("worksheets('Sheet1')/range")


def test_get_range_rejects_explicit_empty_address(monkeypatch):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_get_range("book.xlsx", "Sheet1", address=""))

    assert result["status"] == "error"
    assert "address is required" in result["message"]
    mock_request.assert_not_called()


def test_update_range_sends_parsed_values(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"address": "Sheet1!A1:B1"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_update_range("book.xlsx", "Sheet1", "A1:B1", '[["Name", "Score"]]')
    )

    assert result["status"] == "success"
    kwargs = mock_request.call_args.kwargs
    assert kwargs["method"] == "PATCH"
    assert kwargs["json"] == {"values": [["Name", "Score"]]}


def test_update_range_omits_oversized_confirmed_response(monkeypatch):
    max_output_length = get_tool_max_output_length()
    mock_request = Mock(
        return_value=MockResponse({"values": [["x" * (max_output_length + 1000)]]})
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_update_range("book.xlsx", "Sheet1", "A1", '[["x"]]')
    )

    assert result["status"] == "success"
    assert result["response_omitted"] is True
    assert "updated successfully" in result["message"]


def test_update_range_rejects_invalid_values_json():
    result = json.loads(
        excel.excel_update_range("book.xlsx", "Sheet1", "A1:B1", "not json")
    )
    assert result["status"] == "error"
    assert "not valid JSON" in result["message"]


def test_clear_range_validates_apply_to():
    result = json.loads(
        excel.excel_clear_range("book.xlsx", "Sheet1", "A1:B1", apply_to="Bogus")
    )
    assert result["status"] == "error"
    assert "apply_to must be one of" in result["message"]


def test_clear_range_rejects_non_string_apply_to():
    result = json.loads(
        excel.excel_clear_range("book.xlsx", "Sheet1", "A1:B1", apply_to=["Contents"])
    )
    assert result["status"] == "error"
    assert "apply_to must be a string" in result["message"]


def test_clear_range_success(monkeypatch):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_clear_range("book.xlsx", "Sheet1", "A1:B1"))

    assert result["status"] == "success"
    kwargs = mock_request.call_args.kwargs
    assert kwargs["url"].endswith("range(address='A1%3AB1')/clear")
    assert kwargs["json"] == {"applyTo": "Contents"}


def test_get_range_rejects_oversized_ingress(monkeypatch):
    max_output_length = get_tool_max_output_length()
    oversized_row = ["x" * (max_output_length + 1000)]
    mock_request = Mock(
        return_value=MockResponse(
            {"address": "Sheet1!A1:B2", "values": [oversized_row]}
        )
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_get_range("book.xlsx", "Sheet1", address="A1:B2"))

    assert result["status"] == "error"
    assert "smaller address" in result["message"]
    assert mock_request.return_value.closed is True


def test_get_range_bounds_streamed_http_error_body(monkeypatch):
    response = MockResponse(
        status_code=429,
        content=b"rate limited: " + (b"x" * 1_000),
    )
    monkeypatch.setattr(excel, "get_tool_max_output_length", lambda: 64)
    monkeypatch.setattr(excel.requests, "request", Mock(return_value=response))

    result = json.loads(excel.excel_get_range("book.xlsx", "Sheet1", address="A1"))

    assert result["status"] == "error"
    assert "429" in result["message"]
    assert "response body truncated" in result["message"]
    assert len(result["message"]) < 250
    assert response.closed is True


def test_get_used_range_with_values_only(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"address": "Sheet1!A1:C3"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_get_used_range("book.xlsx", "Sheet1", values_only=True)
    )

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith("usedRange(valuesOnly=true)")


def test_get_used_range_rejects_non_boolean_values_only(monkeypatch):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_get_used_range("book.xlsx", "Sheet1", values_only="false")
    )

    assert result["status"] == "error"
    assert "values_only must be a boolean" in result["message"]
    mock_request.assert_not_called()


def test_get_used_range_rejects_oversized_ingress(monkeypatch):
    max_output_length = get_tool_max_output_length()
    oversized_row = ["x" * (max_output_length + 1000)]
    mock_request = Mock(
        return_value=MockResponse(
            {"address": "Sheet1!A1:C3", "values": [oversized_row]}
        )
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_get_used_range("book.xlsx", "Sheet1"))

    assert result["status"] == "error"
    assert "smaller explicit addresses" in result["message"]


def test_bounded_range_omits_all_aligned_matrices_together(monkeypatch):
    monkeypatch.setattr(excel, "get_tool_max_output_length", lambda: 500)
    matrix = [["x" * 100], ["y" * 100]]
    result = json.loads(
        excel._success_with_bounded_range(
            {
                "address": "Sheet1!A1:A2",
                "rowCount": 2,
                "columnCount": 1,
                "values": matrix,
                "formulas": matrix,
                "text": matrix,
                "numberFormat": matrix,
                "valueTypes": matrix,
            }
        )
    )

    assert result["status"] == "success"
    assert result["truncated"] is True
    assert result["range"] == {
        "address": "Sheet1!A1:A2",
        "rowCount": 2,
        "columnCount": 1,
    }
    assert "matrices were omitted" in result["message"]


def test_count_values_scans_the_complete_used_range(monkeypatch):
    rows = [["Open"] if index in {0, 2222} else ["Closed"] for index in range(2223)]
    responses = [
        MockResponse(
            {
                "address": "Sheet1!H1:H2223",
                "rowCount": 2223,
                "columnCount": 1,
            }
        )
    ]
    responses.extend(
        range_response(rows[offset : offset + 100], column_count=1)
        for offset in range(0, len(rows), 100)
    )
    mock_request = Mock(side_effect=responses)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "H", "open"))

    assert result == {
        "status": "success",
        "column": "H",
        "value": "open",
        "count": 2,
        "scanned_range": "H1:H2223",
        "complete": True,
    }
    assert mock_request.call_count == 1 + 23
    assert mock_request.call_args_list[0].kwargs["params"] == {"$select": "address"}
    assert "range(address='H1%3AH100')" in mock_request.call_args_list[1].kwargs["url"]


def test_scan_reduces_chunk_after_oversized_response(monkeypatch):
    oversized = MockResponse({"values": [["x" * 1_000] for _ in range(100)]})
    mock_request = Mock(
        side_effect=[
            MockResponse({"address": "Sheet1!H1:H100"}),
            oversized,
            range_response([["Open"] for _ in range(50)], column_count=1),
            range_response([["Closed"] for _ in range(50)], column_count=1),
        ]
    )
    monkeypatch.setattr(excel, "get_tool_max_output_length", lambda: 10_000)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "H", "Open"))

    assert result["status"] == "success"
    assert result["count"] == 50
    assert mock_request.call_count == 4


def test_find_rows_returns_exact_match_count_and_row_number(monkeypatch):
    rows = [
        ["Closed", "unrelated", None, None, None, None, None, None] for _ in range(2223)
    ]
    rows[2199] = [
        "Open",
        "Facebook & Instagram connector",
        None,
        None,
        None,
        None,
        None,
        None,
    ]
    responses = [MockResponse({"address": "Sheet1!A1:H2223"})]
    responses.extend(
        range_response(rows[offset : offset + 100], column_count=8)
        for offset in range(0, len(rows), 100)
    )
    mock_request = Mock(side_effect=responses)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_find_rows("book.xlsx", "Sheet1", "facebook", max_matches=1)
    )

    assert result["status"] == "success"
    assert result["matched_count"] == 1
    assert result["rows"][0]["row_number"] == 2200
    assert result["complete"] is True
    assert result["scanned_range"] == "A1:H2223"
    assert "range(address='A1%3AH100')" in mock_request.call_args_list[1].kwargs["url"]


def test_queries_match_graph_displayed_text(monkeypatch):
    mock_request = Mock(
        side_effect=[
            MockResponse({"address": "Sheet1!A1:A1"}),
            range_response([["=A1"]], text=[["Open"]], column_count=1),
        ]
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "A", "open"))

    assert result["status"] == "success"
    assert result["count"] == 1


def test_query_rejects_incomplete_graph_matrix(monkeypatch):
    mock_request = Mock(
        side_effect=[
            MockResponse({"address": "Sheet1!A1:A2"}),
            range_response([["Open"]], column_count=1),
        ]
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "A", "Open"))

    assert result["status"] == "error"
    assert "incomplete range" in result["message"]


def test_query_rejects_ranges_over_row_cap(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"address": "Sheet1!A1:A10001"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "A", "Open"))

    assert result["status"] == "error"
    assert "narrow the range" in result["message"]
    assert mock_request.call_count == 1


def test_query_reports_graph_rate_limit(monkeypatch):
    mock_request = Mock(
        side_effect=[
            MockResponse({"address": "Sheet1!A1:A1"}),
            MockResponse(status_code=429),
        ]
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_count_values("book.xlsx", "Sheet1", "A", "Open"))

    assert result["status"] == "error"
    assert "rate limited" in result["message"]


@pytest.mark.parametrize(
    ("tool_call", "message"),
    [
        (
            lambda: excel.excel_count_values("book.xlsx", "Sheet1", "1", "Open"),
            "column must be an Excel column label",
        ),
        (
            lambda: excel.excel_find_rows("book.xlsx", "Sheet1", "", max_matches=1),
            "query is required",
        ),
        (
            lambda: excel.excel_find_rows(
                "book.xlsx", "Sheet1", "Open", max_matches=101
            ),
            "max_matches must be between",
        ),
        (
            lambda: excel.excel_count_values("book.xlsx", "Sheet1", "A", ""),
            "value must not be empty",
        ),
    ],
)
def test_query_tools_validate_arguments(tool_call, message):
    result = json.loads(tool_call())

    assert result["status"] == "error"
    assert message in result["message"]


# ---------------------------------------------------------------------------
# tables
# ---------------------------------------------------------------------------


def test_list_tables_success(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"value": [{"id": "1"}]}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_tables("book.xlsx"))

    assert result["status"] == "success"
    assert result["tables"] == [{"id": "1"}]
    assert result["next_link"] is None


def test_list_tables_exposes_next_link(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse(
            {
                "value": [{"id": "1"}],
                "@odata.nextLink": "https://graph.microsoft.com/v1.0/next-page",
            }
        )
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_tables("book.xlsx"))

    assert result["next_link"] == "https://graph.microsoft.com/v1.0/next-page"


def test_add_table_sends_address_and_has_headers(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"id": "1", "name": "Table1"}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_add_table("book.xlsx", "Sheet1!A1:C5"))

    assert result["status"] == "success"
    kwargs = mock_request.call_args.kwargs
    assert kwargs["url"].endswith("/workbook/tables/add")
    assert kwargs["json"] == {"address": "Sheet1!A1:C5", "hasHeaders": True}


def test_add_table_rejects_non_boolean_has_headers(monkeypatch):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_add_table("book.xlsx", "Sheet1!A1:C5", has_headers="false")
    )

    assert result["status"] == "error"
    assert "has_headers must be a boolean" in result["message"]
    mock_request.assert_not_called()


def test_list_table_rows_success(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"value": [{"index": 0}]}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_table_rows("book.xlsx", "Table1"))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith("tables('Table1')/rows")
    assert mock_request.call_args.kwargs["params"] == {"$top": 20, "$skip": 0}
    assert result["next_link"] is None
    assert result["next_skip"] == 1


def test_list_table_rows_exposes_offset_continuation_for_full_page(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse({"value": [{"index": index} for index in range(2)]})
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_table_rows("book.xlsx", "Table1", page_size=2, skip=4)
    )

    assert result["next_skip"] == 6
    assert mock_request.call_args.kwargs["params"] == {"$top": 2, "$skip": 4}


def test_list_table_rows_advances_after_short_nonempty_offset_page(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"value": [{"index": 6}]}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_table_rows("book.xlsx", "Table1", page_size=2, skip=6)
    )

    assert result["rows"] == [{"index": 6}]
    assert result["next_skip"] == 7
    assert mock_request.call_args.kwargs["params"] == {"$top": 2, "$skip": 6}


def test_list_table_rows_stops_offset_paging_on_empty_page(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"value": []}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_table_rows("book.xlsx", "Table1", page_size=2, skip=7)
    )

    assert result["rows"] == []
    assert result["next_skip"] is None


@pytest.mark.parametrize("skip", [True, -1, "1"])
def test_list_table_rows_rejects_invalid_skip(monkeypatch, skip):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_table_rows("book.xlsx", "Table1", skip=skip))

    assert result["status"] == "error"
    assert "skip" in result["message"]
    mock_request.assert_not_called()


def test_list_table_rows_rejects_skip_with_next_link(monkeypatch):
    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_table_rows(
            "book.xlsx",
            "Table1",
            next_link="https://graph.microsoft.com/v1.0/next-page",
            skip=1,
        )
    )

    assert result["status"] == "error"
    assert "skip must be 0" in result["message"]
    mock_request.assert_not_called()


def test_list_table_rows_exposes_next_link(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse(
            {
                "value": [{"index": 0}],
                "@odata.nextLink": "https://graph.microsoft.com/v1.0/next-page",
            }
        )
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_table_rows("book.xlsx", "Table1"))

    assert result["next_link"] == "https://graph.microsoft.com/v1.0/next-page"
    assert result["next_skip"] is None


def test_list_table_rows_consumes_equivalently_encoded_next_link(monkeypatch):
    next_link = (
        "https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/workbook/"
        "tables(%27Table1%27)/rows?$skiptoken=page-2"
    )
    mock_request = Mock(return_value=MockResponse({"value": [{"index": 1}]}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_list_table_rows("book.xlsx", "Table1", next_link=next_link)
    )

    assert result["rows"] == [{"index": 1}]
    assert mock_request.call_args.kwargs["url"] == next_link


def test_list_table_rows_rejects_oversized_page_without_advancing_cursor(monkeypatch):
    max_output_length = get_tool_max_output_length()
    next_link = (
        "https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/workbook/"
        "tables('Table1')/rows?$skiptoken=page-2"
    )
    response = MockResponse(
        {
            "value": [{"values": [["x" * (max_output_length + 1000)]]}],
            "@odata.nextLink": next_link,
        }
    )
    mock_request = Mock(return_value=response)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_table_rows("book.xlsx", "Table1"))

    assert result["status"] == "error"
    assert "smaller page_size" in result["message"]
    assert "next_link" not in result
    assert response.closed is True
    assert mock_request.call_args.kwargs["stream"] is True


@pytest.mark.parametrize(
    ("tool_name", "args"),
    [
        ("excel_list_worksheets", ("book.xlsx",)),
        ("excel_list_tables", ("book.xlsx",)),
        ("excel_list_table_rows", ("book.xlsx", "Table1")),
    ],
)
def test_list_collections_reject_oversized_final_page(monkeypatch, tool_name, args):
    max_output_length = get_tool_max_output_length()
    response = MockResponse(
        {"value": [{"values": [["x" * (max_output_length + 1000)]]}]}
    )
    mock_request = Mock(return_value=response)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(getattr(excel, tool_name)(*args))

    assert result["status"] == "error"
    assert "smaller page_size" in result["message"]
    assert "next_link" not in result
    assert response.closed is True
    assert mock_request.call_args.kwargs["stream"] is True


def test_add_table_rows_with_index(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"index": 0}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(
        excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]", index=0)
    )

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["json"] == {"values": [[1, 2, 3]], "index": 0}


def test_add_table_rows_without_index_omits_field(monkeypatch):
    mock_request = Mock(return_value=MockResponse({"index": 5}))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    json.loads(excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]"))

    assert "index" not in mock_request.call_args.kwargs["json"]


@pytest.mark.parametrize(
    ("tool_name", "arguments"),
    [
        (
            "excel_add_table_rows",
            {
                "file_path": "book.xlsx",
                "table": "Table1",
                "values_json": "[[1]]",
                "index": True,
            },
        ),
        (
            "excel_add_table_rows",
            {
                "file_path": "book.xlsx",
                "table": "Table1",
                "values_json": "[[1]]",
                "index": "3",
            },
        ),
        (
            "excel_add_table_rows",
            {
                "file_path": "book.xlsx",
                "table": "Table1",
                "values_json": "[[1]]",
                "index": 1.0,
            },
        ),
        (
            "excel_delete_table_row",
            {"file_path": "book.xlsx", "table": "Table1", "row_index": True},
        ),
        (
            "excel_delete_table_row",
            {"file_path": "book.xlsx", "table": "Table1", "row_index": "3"},
        ),
        (
            "excel_delete_table_row",
            {"file_path": "book.xlsx", "table": "Table1", "row_index": 1.0},
        ),
    ],
)
async def test_row_indexes_are_strict_at_mcp_ingress(monkeypatch, tool_name, arguments):
    from mcp.server.fastmcp.exceptions import ToolError

    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    with pytest.raises(ToolError, match="validation error"):
        await excel.mcp.call_tool(tool_name, arguments)
    mock_request.assert_not_called()


async def test_delete_columns_validates_arguments_at_mcp_ingress(monkeypatch):
    from mcp.server.fastmcp.exceptions import ToolError

    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    with pytest.raises(ToolError, match="validation error"):
        await excel.mcp.call_tool(
            "excel_delete_columns",
            {
                "file_path": "book.xlsx",
                "worksheet": "Sheet1",
                "start_column": None,
                "end_column": "M",
            },
        )
    mock_request.assert_not_called()


@pytest.mark.parametrize(
    "arguments",
    [
        {"start_row": True, "end_row": 7},
        {"start_row": "5", "end_row": 7},
        {"start_row": 5.0, "end_row": 7},
        {"start_row": 5, "end_row": None},
        {"start_row": 0, "end_row": 7},
        {"start_row": 5, "end_row": 1_048_577},
    ],
)
async def test_delete_rows_validates_arguments_at_mcp_ingress(monkeypatch, arguments):
    from mcp.server.fastmcp.exceptions import ToolError

    mock_request = Mock()
    monkeypatch.setattr(excel.requests, "request", mock_request)

    with pytest.raises(ToolError, match="validation error"):
        await excel.mcp.call_tool(
            "excel_delete_rows",
            {"file_path": "book.xlsx", "worksheet": "Sheet1", **arguments},
        )
    mock_request.assert_not_called()


async def test_delete_rows_accepts_the_last_worksheet_row_at_mcp_ingress(monkeypatch):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = await excel.mcp.call_tool(
        "excel_delete_rows",
        {
            "file_path": "book.xlsx",
            "worksheet": "Sheet1",
            "start_row": 1,
            "end_row": 1_048_576,
        },
    )

    blocks = result[0] if isinstance(result, tuple) else result
    assert json.loads(blocks[0].text)["deleted_range"] == "1:1048576"
    assert mock_request.call_count == 1


@pytest.mark.parametrize(
    ("call", "side_effect"),
    [
        (
            lambda: excel.excel_add_worksheet("book.xlsx"),
            requests.ReadTimeout("response timed out"),
        ),
        (
            lambda: excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]"),
            requests.ConnectionError("connection dropped"),
        ),
    ],
)
def test_non_idempotent_mutation_transport_failure_is_indeterminate(
    monkeypatch, call, side_effect
):
    monkeypatch.setattr(excel.requests, "request", Mock(side_effect=side_effect))

    result = json.loads(call())

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "may already have been applied" in result["message"]


def test_non_idempotent_mutation_truncated_response_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(
            side_effect=requests.exceptions.ChunkedEncodingError("response truncated")
        ),
    )

    result = json.loads(excel.excel_add_worksheet("book.xlsx"))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "may already have been applied" in result["message"]


def test_non_idempotent_mutation_invalid_success_body_is_indeterminate(monkeypatch):
    response = MockResponse(content=b"{invalid")
    response.json = Mock(
        side_effect=requests.exceptions.JSONDecodeError("invalid JSON", "{invalid", 1)
    )
    monkeypatch.setattr(excel.requests, "request", Mock(return_value=response))

    result = json.loads(excel.excel_add_worksheet("book.xlsx"))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "may already have been applied" in result["message"]


def test_non_idempotent_mutation_server_failure_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "gateway"}, status_code=504)),
    )

    result = json.loads(
        excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]")
    )

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "HTTP 504" in result["message"]


def test_add_table_rows_caps_oversized_response(monkeypatch):
    max_output_length = get_tool_max_output_length()
    response = MockResponse({"values": [["x" * (max_output_length + 1000)]]})
    mock_request = Mock(return_value=response)
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_add_table_rows("book.xlsx", "Table1", '[["x"]]'))

    assert result["status"] == "success"
    assert result["response_omitted"] is True
    assert response.closed is True
    assert mock_request.call_args.kwargs["stream"] is True


def test_add_table_rows_rejects_negative_index():
    result = json.loads(
        excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]", index=-1)
    )
    assert result["status"] == "error"
    assert "index" in result["message"]


def test_add_table_rows_rejects_bool_index():
    """bool is a subclass of int, so an unguarded isinstance(index, int)
    check would silently accept index=True and forward it to Graph as the
    literal 1 instead of raising a clear local error."""
    result = json.loads(
        excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]", index=True)
    )
    assert result["status"] == "error"
    assert "index" in result["message"]


def test_add_table_rows_rejects_non_integer_index():
    result = json.loads(
        excel.excel_add_table_rows("book.xlsx", "Table1", "[[1, 2, 3]]", index="0")
    )
    assert result["status"] == "error"
    assert "index" in result["message"]


def test_delete_table_row_rejects_negative_index():
    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", -1))
    assert result["status"] == "error"
    assert "row_index" in result["message"]


def test_delete_table_row_rejects_bool_index():
    """bool is a subclass of int in Python, so `isinstance(True, int)` is
    True and `True < 0` is False -- without an explicit bool exclusion,
    row_index=True would silently pass validation and get interpolated into
    the URL as the literal string "True", producing a broken Graph request
    instead of a clear local validation error."""
    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", True))
    assert result["status"] == "error"
    assert "row_index" in result["message"]


def test_delete_table_row_rejects_non_integer():
    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", "3"))
    assert result["status"] == "error"
    assert "row_index" in result["message"]


def test_delete_table_row_success(monkeypatch):
    mock_request = Mock(return_value=MockResponse({}, status_code=204, content=b""))
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", 3))

    assert result["status"] == "success"
    assert mock_request.call_args.kwargs["url"].endswith("tables('Table1')/rows/3")


@pytest.mark.parametrize(
    "side_effect",
    [
        requests.ReadTimeout("response timed out"),
        requests.ConnectionError("connection dropped"),
    ],
)
def test_delete_table_row_transport_failure_is_indeterminate(monkeypatch, side_effect):
    monkeypatch.setattr(excel.requests, "request", Mock(side_effect=side_effect))

    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", 0))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "may already have been applied" in result["message"]


def test_delete_table_row_server_failure_is_indeterminate(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "gateway"}, status_code=504)),
    )

    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", 0))

    assert result["status"] == "indeterminate"
    assert result["retry_safe"] is False
    assert "HTTP 504" in result["message"]


def test_delete_table_row_client_failure_is_deterministic_error(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "not found"}, status_code=404)),
    )

    result = json.loads(excel.excel_delete_table_row("book.xlsx", "Table1", 0))

    assert result["status"] == "error"
    assert "404" in result["message"]


# ---------------------------------------------------------------------------
# error propagation
# ---------------------------------------------------------------------------


def test_graph_error_response_is_surfaced_as_error(monkeypatch):
    mock_request = Mock(
        return_value=MockResponse({"error": {"message": "Not Found"}}, status_code=404)
    )
    monkeypatch.setattr(excel.requests, "request", mock_request)

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["status"] == "error"
    assert "Not Found" in result["message"]


@pytest.mark.parametrize(
    ("status_code", "body", "failure_code"),
    [
        (403, {"error": {"code": "accessDenied"}}, "excel_workbook_access_denied"),
        (404, {"error": {"code": "itemNotFound"}}, "excel_site_not_found"),
        (423, {"error": {"code": "resourceLocked"}}, "excel_workbook_locked"),
    ],
)
def test_graph_failures_carry_allowlisted_failure_code(
    monkeypatch, status_code, body, failure_code
):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse(body, status_code=status_code)),
    )

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["status"] == "error"
    assert result["failure_code"] == failure_code


def test_access_denied_with_non_forbidden_status_does_not_carry_failure_code():
    error = excel._GraphRequestError(
        "denied",
        status_code=401,
        graph_error_code="accessDenied",
    )

    assert excel._excel_failure_code(error) is None


def test_graph_other_failure_does_not_carry_failure_code(monkeypatch):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(return_value=MockResponse({"error": "bad request"}, status_code=400)),
    )

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["status"] == "error"
    assert "failure_code" not in result


def test_graph_item_not_found_for_a_worksheet_does_not_look_like_missing_workbook(
    monkeypatch,
):
    monkeypatch.setattr(
        excel.requests,
        "request",
        Mock(
            return_value=MockResponse(
                {"error": {"code": "itemNotFound"}}, status_code=404
            )
        ),
    )

    result = json.loads(excel.excel_get_range("book.xlsx", "Missing", address="A1"))

    assert result["status"] == "error"
    assert "failure_code" not in result


def test_missing_auth_token_is_reported(monkeypatch):
    monkeypatch.delenv("AUTH_TOKEN", raising=False)

    result = json.loads(excel.excel_list_worksheets("book.xlsx"))

    assert result["status"] == "error"
    assert "AUTH_TOKEN" in result["message"]