from collections.abc import Iterator
from contextlib import contextmanager
from unittest import mock

from payoneer_status_sync import db


@contextmanager
def mocked_mysql_connection(connection: mock.MagicMock) -> Iterator[mock.MagicMock]:
    yield connection


@mock.patch("payoneer_status_sync.db.mysql_connection")
def test_fetch_target_payments_uses_abacus_statement_period_ids_when_provided(
    mock_mysql_connection: mock.MagicMock,
) -> None:
    cursor = mock.MagicMock()
    connection = mock.MagicMock()
    connection.cursor.return_value.__enter__.return_value = cursor
    rows = [{"dp_payment_id": 10, "payoneer_program_id": "prog", "payoneer_payment_id": "pay"}]
    cursor.fetchall.return_value = rows
    mock_mysql_connection.return_value = mocked_mysql_connection(connection)

    result = db.fetch_target_payments(dp_payment_ids=[1, 2], abacus_sp_ids=[11, 12], limit=50)

    assert result == rows
    cursor.execute.assert_called_once()
    sql, params = cursor.execute.call_args.args
    assert "abacus_statement_period_id IN (%s, %s)" in sql
    assert "WHERE id IN" not in sql
    assert params == [11, 12, 50]


@mock.patch("payoneer_status_sync.db.mysql_connection")
def test_fetch_target_payments_filters_by_dp_payment_ids_when_no_abacus_ids(
    mock_mysql_connection: mock.MagicMock,
) -> None:
    cursor = mock.MagicMock()
    connection = mock.MagicMock()
    connection.cursor.return_value.__enter__.return_value = cursor
    cursor.fetchall.return_value = []
    mock_mysql_connection.return_value = mocked_mysql_connection(connection)

    db.fetch_target_payments(dp_payment_ids=[3, 4], abacus_sp_ids=[], limit=25)

    cursor.execute.assert_called_once()
    sql, params = cursor.execute.call_args.args
    assert "id IN (%s, %s)" in sql
    assert "abacus_statement_period_id IN" not in sql
    assert params == [3, 4, 25]


@mock.patch("payoneer_status_sync.db.mysql_connection")
def test_update_payoneer_status_bulk_returns_zero_without_query(
    mock_mysql_connection: mock.MagicMock,
) -> None:
    result = db.update_payoneer_status_bulk([])

    assert result == 0
    mock_mysql_connection.assert_not_called()


@mock.patch("payoneer_status_sync.db.mysql_connection")
def test_update_payoneer_status_bulk_executes_single_join_update(
    mock_mysql_connection: mock.MagicMock,
) -> None:
    cursor = mock.MagicMock()
    cursor.rowcount = 2
    connection = mock.MagicMock()
    connection.cursor.return_value.__enter__.return_value = cursor
    mock_mysql_connection.return_value = mocked_mysql_connection(connection)

    result = db.update_payoneer_status_bulk(
        [(1, "paid", None), (2, "returned", "Bank cannot process")]
    )

    assert result == 2
    cursor.execute.assert_called_once()
    sql, params = cursor.execute.call_args.args
    assert "UPDATE dp_payment AS d" in sql
    assert "JOIN (" in sql
    assert sql.count("SELECT %s AS id, %s AS status, %s AS reason") == 2
    assert "SET d.payoneer_payment_status = payload.status" in sql
    assert "d.latest_payoneer_event_reason = payload.reason" in sql
    assert "WHERE d.payoneer_payment_status IS NULL" in sql
    assert params == [1, "paid", None, 2, "returned", "Bank cannot process"]