"""Functional tests for the batch discovery query against a real MySQL schema.

Pins the trigger semantics, ordering, limit, and the account_contract join —
things the unit tests cannot verify because they never execute the SQL.
"""

from __future__ import annotations

from pymysql.connections import Connection
from pymysql.cursors import DictCursor

from sync_contract_sap.repository import Repository
from sync_contract_sap.schemas import ContractSyncRow

from tests.functional.conftest import (
    TEST_ID_START,
    insert_account_for_contract,
    insert_contract,
    insert_sap_sync_state,
)

NO_STATE = TEST_ID_START + 1
ERROR_STATE = TEST_ID_START + 2
INIT_STATE = TEST_ID_START + 3
STALE_SYNC = TEST_ID_START + 4
FRESH_SYNC = TEST_ID_START + 5


def _query(
    conn: Connection[DictCursor], limit: int = 500, stale_sync_minutes: int = 5
) -> list[ContractSyncRow]:
    """Run the discovery query and return only rows owned by these tests."""
    rows = Repository(conn).get_contracts_pending_sap_sync(
        limit=limit, stale_sync_minutes=stale_sync_minutes
    )
    return [row for row in rows if row.contract_id >= TEST_ID_START]


def test_trigger_semantics(conn: Connection[DictCursor]) -> None:
    """No-state, error, init, and stale-sync contracts are selected; fresh are not."""
    insert_contract(conn, NO_STATE)

    insert_contract(conn, ERROR_STATE)
    insert_sap_sync_state(conn, ERROR_STATE, 'error')

    insert_contract(conn, INIT_STATE)
    insert_sap_sync_state(conn, INIT_STATE, 'init')

    # Contract modified 10 minutes after its completed sync -> stale.
    insert_contract(conn, STALE_SYNC, last_modified='2024-01-01 00:10:00')
    insert_sap_sync_state(
        conn, STALE_SYNC, 'complete', last_modified='2024-01-01 00:00:00'
    )

    # Sync completed after the contract's last change -> fresh, excluded.
    insert_contract(conn, FRESH_SYNC, last_modified='2024-01-01 00:00:00')
    insert_sap_sync_state(
        conn, FRESH_SYNC, 'complete', last_modified='2024-01-01 00:10:00'
    )

    selected = {row.contract_id for row in _query(conn)}

    assert selected == {NO_STATE, ERROR_STATE, INIT_STATE, STALE_SYNC}


def test_stale_threshold_respects_parameter(conn: Connection[DictCursor]) -> None:
    """A contract modified less than the threshold after its sync is excluded."""
    insert_contract(conn, STALE_SYNC, last_modified='2024-01-01 00:03:00')
    insert_sap_sync_state(
        conn, STALE_SYNC, 'complete', last_modified='2024-01-01 00:00:00'
    )

    assert _query(conn, stale_sync_minutes=5) == []
    assert [row.contract_id for row in _query(conn, stale_sync_minutes=3)] == [
        STALE_SYNC
    ]


def test_ordering_errors_last_then_oldest_state(conn: Connection[DictCursor]) -> None:
    """Errors sort last; other states sort by created_at ascending."""
    insert_contract(conn, ERROR_STATE)
    insert_sap_sync_state(conn, ERROR_STATE, 'error', created_at='2024-01-01 00:00:00')

    insert_contract(conn, INIT_STATE)
    insert_sap_sync_state(conn, INIT_STATE, 'init', created_at='2024-06-01 00:00:00')

    older_init = TEST_ID_START + 6
    insert_contract(conn, older_init)
    insert_sap_sync_state(conn, older_init, 'init', created_at='2024-03-01 00:00:00')

    ordered = [row.contract_id for row in _query(conn)]

    assert ordered == [older_init, INIT_STATE, ERROR_STATE]


def test_limit_is_applied(conn: Connection[DictCursor]) -> None:
    """The LIMIT parameter bounds the result set."""
    insert_contract(conn, NO_STATE)
    insert_contract(conn, NO_STATE + 10)

    rows = Repository(conn).get_contracts_pending_sap_sync(
        limit=1, stale_sync_minutes=5
    )

    assert len(rows) == 1


def test_account_id_comes_from_account_contract(conn: Connection[DictCursor]) -> None:
    """account_id is sourced from the account_contract mapping; NULL without one."""
    insert_contract(conn, NO_STATE)
    insert_account_for_contract(conn, TEST_ID_START + 50, NO_STATE)

    insert_contract(conn, NO_STATE + 10)

    by_id = {row.contract_id: row for row in _query(conn)}

    assert by_id[NO_STATE].account_id == TEST_ID_START + 50
    assert by_id[NO_STATE + 10].account_id is None