"""Integration tests for lambda function."""

import os
from decimal import Decimal
from unittest.mock import MagicMock, patch

import pymysql
import pytest

from config import config as app_config
from src import app
from src.connectors.mysql import MySQLConfig

DB_HOST = os.environ.get('MYSQL_DB_HOST', 'mysql')
DB_USER = os.environ.get('MYSQL_USER', 'royalties')
DB_PASS = os.environ.get('MYSQL_PASSWORD', '1234')
DB_NAME = os.environ.get('MYSQL_DATABASE', 'royalty_accounting')
DB_PORT = int(os.environ.get('MYSQL_DB_PORT', 3306))

# Test IDs — kept below smallint unsigned max (65535) for narrowest columns
_SP_ID = 60001
_SPPE_ID = 60001
_RPE_ID = 4
_ACCOUNT_ID_1 = 60011
_ACCOUNT_ID_2 = 60012
_CONTRACT_ID_1 = 60021
_CONTRACT_ID_2 = 60022
_CONTRACT_ID_3 = 60023
_PAYEE_ID_1 = 60031
_PAYEE_ID_2 = 60032
_ABACUS_EVENT_ID = 60041


def _make_event(sppe_id: int = _SPPE_ID) -> dict:
    """Create a close_balance.completed EventBridge event."""
    return {
        'detail-type': 'close_balance.completed',
        'detail': {
            'metadata': {
                'target_id': sppe_id,
                'target_type': 'statement_period_payment_entity',
            },
        },
    }


def _mysql_config() -> MySQLConfig:
    return MySQLConfig(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
    )


@pytest.fixture(scope='module')
def db_conn():
    """Create a database connection for seeding and assertions."""
    conn = pymysql.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
    )
    yield conn
    conn.close()


def _cleanup(cursor):
    """Remove test data in reverse FK order."""
    cursor.execute(
        'DELETE pala FROM payment_allocation_ledger_adjustment pala '
        'INNER JOIN payment_allocation pa '
        '  ON pala.payment_allocation_id = pa.payment_allocation_id '
        'WHERE pa.statement_period_id = %s',
        (_SP_ID,),
    )
    cursor.execute(
        'DELETE FROM payment_allocation WHERE statement_period_id = %s',
        (_SP_ID,),
    )
    cursor.execute(
        'DELETE FROM abacus_state '
        'WHERE parent_table_name = %s AND parent_table_id = %s',
        ('statement_period_payment_entity', _SPPE_ID),
    )
    cursor.execute(
        'DELETE FROM ledger_adjustment_applied WHERE statement_period_id = %s',
        (_SP_ID,),
    )
    cursor.execute(
        'DELETE FROM account_payee WHERE account_id IN (%s, %s)',
        (_ACCOUNT_ID_1, _ACCOUNT_ID_2),
    )
    cursor.execute(
        'DELETE FROM account_payment_term WHERE account_id IN (%s, %s)',
        (_ACCOUNT_ID_1, _ACCOUNT_ID_2),
    )
    cursor.execute(
        'DELETE FROM statement_period_payment_entity '
        'WHERE statement_period_payment_entity_id = %s',
        (_SPPE_ID,),
    )
    cursor.execute(
        'DELETE FROM abacus_event WHERE abacus_event_id = %s',
        (_ABACUS_EVENT_ID,),
    )
    cursor.execute(
        'DELETE FROM contract WHERE contract_id IN (%s, %s, %s)',
        (_CONTRACT_ID_1, _CONTRACT_ID_2, _CONTRACT_ID_3),
    )
    cursor.execute(
        'DELETE FROM statement_period WHERE statement_period_id = %s',
        (_SP_ID,),
    )
    cursor.execute(
        'DELETE FROM account WHERE account_id IN (%s, %s)',
        (_ACCOUNT_ID_1, _ACCOUNT_ID_2),
    )


def _seed_base(cursor):
    """Insert common prerequisite data (everything except adjustments).

    Creates: statement_period, SPPE, two accounts linked to payment entity,
    two account payees, and abacus_state with close_balance=complete.
    """
    cursor.execute(
        """
        INSERT INTO statement_period (
            statement_period_id, statement_period_name,
            statement_period_status, statement_month, statement_year
        ) VALUES (%s, 'Integration Test Period', 'current', 1, 2024)
        """,
        (_SP_ID,),
    )
    cursor.execute(
        """
        INSERT INTO abacus_event (
            abacus_event_id, statement_period_id,
            event_name, target_type, target_id, event_date
        ) VALUES (%s, %s, 'test_event', 'statement_period', %s, NOW())
        """,
        (_ABACUS_EVENT_ID, _SP_ID, _SP_ID),
    )
    cursor.execute(
        """
        INSERT INTO statement_period_payment_entity (
            statement_period_payment_entity_id, statement_period_id,
            reference_payment_entity_id
        ) VALUES (%s, %s, %s)
        """,
        (_SPPE_ID, _SP_ID, _RPE_ID),
    )
    cursor.execute(
        """
        INSERT INTO account (
            account_id, account_name,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES
            (%s, 'Test Account 1', 'test', NOW(), 'test', NOW()),
            (%s, 'Test Account 2', 'test', NOW(), 'test', NOW())
        """,
        (_ACCOUNT_ID_1, _ACCOUNT_ID_2),
    )
    cursor.execute(
        """
        INSERT INTO account_payment_term (
            account_id, payment_entity_id, currency_code,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES
            (%s, %s, 'USD', 'test', NOW(), 'test', NOW()),
            (%s, %s, 'USD', 'test', NOW(), 'test', NOW())
        """,
        (_ACCOUNT_ID_1, _RPE_ID, _ACCOUNT_ID_2, _RPE_ID),
    )
    cursor.execute(
        """
        INSERT INTO account_payee (
            account_payee_id, account_id,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES
            (%s, %s, 'test', NOW(), 'test', NOW()),
            (%s, %s, 'test', NOW(), 'test', NOW())
        """,
        (_PAYEE_ID_1, _ACCOUNT_ID_1, _PAYEE_ID_2, _ACCOUNT_ID_2),
    )
    cursor.execute(
        """
        INSERT INTO contract (
            contract_id, reference_signing_entity_id,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES
            (%s, 1, 'test', NOW(), 'test', NOW()),
            (%s, 1, 'test', NOW(), 'test', NOW()),
            (%s, 1, 'test', NOW(), 'test', NOW())
        """,
        (_CONTRACT_ID_1, _CONTRACT_ID_2, _CONTRACT_ID_3),
    )
    cursor.execute(
        """
        INSERT INTO abacus_state (
            parent_table_name, parent_table_id,
            action_name, action_status,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (%s, %s, 'close_balance', 'complete',
                  'test', NOW(), 'test', NOW())
        """,
        ('statement_period_payment_entity', _SPPE_ID),
    )


def _insert_adjustments(cursor, rows):
    """Insert ledger_adjustment_applied rows.

    Each row is a tuple of:
        (account_id, contract_id, adj_amount, adj_currency,
         payee_amount, payee_currency, flowthrough_flag)

    statement_period_id is always _SP_ID.
    """
    if not rows:
        return
    placeholders = ', '.join(
        [
            f'(%s, {_ABACUS_EVENT_ID}, %s, %s, %s, %s, %s, %s, %s,'
            " 'test', NOW(), 'test', NOW())"
        ]
        * len(rows)
    )
    values = []
    for acct, contract, amt, cur, payee_amt, payee_cur, flag in rows:
        values.extend([acct, contract, _SP_ID, amt, cur, payee_amt, payee_cur, flag])
    cursor.execute(
        f"""
        INSERT INTO ledger_adjustment_applied (
            account_id, abacus_event_id, contract_id, statement_period_id,
            adjustment_amount, adjustment_currency_code,
            adjustment_amount_payee_currency, adjustment_payee_currency_code,
            apply_to_flowthrough_payment,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES {placeholders}
        """,
        values,
    )


@pytest.fixture()
def seed(db_conn):
    """Seed base data and custom adjustments, then clean up after the test.

    Usage::

        def test_example(seed, db_conn):
            seed([
                (account, contract, '100.00', 'USD', '100.00', 'USD', 1),
            ])
    """

    def _do(adjustments):
        with db_conn.cursor() as cursor:
            _cleanup(cursor)
            _seed_base(cursor)
            _insert_adjustments(cursor, adjustments)

    yield _do

    with db_conn.cursor() as cursor:
        _cleanup(cursor)


def _run_handler():
    """Run the lambda handler with patched MySQL config."""
    with patch.object(app_config, 'mysql', _mysql_config()):
        return app.handler(_make_event(), MagicMock())


def _get_allocations(cursor):
    """Fetch payment_allocation records for the test statement period."""
    cursor.execute(
        """
        SELECT contract_id, payee_type, payee_id,
               payment_allocation_type, amount_to_payment,
               amount_to_ledger, currency_code, payment_status,
               ledger_status, description, created_by
        FROM payment_allocation
        WHERE statement_period_id = %s
        ORDER BY contract_id, currency_code
        """,
        (_SP_ID,),
    )
    return cursor.fetchall()


def _count_links(cursor):
    """Count payment_allocation_ledger_adjustment links for the test period."""
    cursor.execute(
        'SELECT COUNT(*) AS cnt '
        'FROM payment_allocation_ledger_adjustment pala '
        'INNER JOIN payment_allocation pa '
        '  ON pala.payment_allocation_id = pa.payment_allocation_id '
        'WHERE pa.statement_period_id = %s',
        (_SP_ID,),
    )
    return cursor.fetchone()['cnt']


# ---------------------------------------------------------------------------
# Tests
# ---------------------------------------------------------------------------


def test_handler_creates_allocations(seed, db_conn):
    """Test end-to-end: handler creates payment allocations from seeded adjustments.

    Two contracts, two currencies. Contract 1 has two USD adjustments that
    are summed into one allocation. Contract 2 has one EUR adjustment.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '50.00', 'USD', '50.00', 'USD', 1),
            (_ACCOUNT_ID_2, _CONTRACT_ID_2, '200.00', 'EUR', '200.00', 'EUR', 1),
        ]
    )

    result = _run_handler()

    assert result['statement_period_id'] == _SP_ID
    assert result['statement_period_payment_entity_id'] == _SPPE_ID
    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 3

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 2

    # Contract 1: two adjustments summed (100 + 50 = 150 USD)
    pa1 = allocations[0]
    assert pa1['contract_id'] == _CONTRACT_ID_1
    assert pa1['payee_type'] == 'account_payee'
    assert pa1['payee_id'] == _PAYEE_ID_1
    assert pa1['payment_allocation_type'] == 'flowthrough'
    assert pa1['amount_to_payment'] == Decimal('150.00')
    assert pa1['amount_to_ledger'] == Decimal('150.00')
    assert pa1['currency_code'] == 'USD'
    assert pa1['payment_status'] == 'init'
    assert pa1['ledger_status'] == 'init'
    assert pa1['created_by'] == app_config.app_name

    # Contract 2: single adjustment (200 EUR)
    pa2 = allocations[1]
    assert pa2['contract_id'] == _CONTRACT_ID_2
    assert pa2['payee_id'] == _PAYEE_ID_2
    assert pa2['amount_to_payment'] == Decimal('200.00')
    assert pa2['amount_to_ledger'] == Decimal('200.00')
    assert pa2['currency_code'] == 'EUR'

    with db_conn.cursor() as cursor:
        assert _count_links(cursor) == 3


def test_handler_idempotent(seed, db_conn):
    """Test re-running handler creates no duplicates (NOT EXISTS guard)."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '50.00', 'USD', '50.00', 'USD', 1),
            (_ACCOUNT_ID_2, _CONTRACT_ID_2, '200.00', 'EUR', '200.00', 'EUR', 1),
        ]
    )

    with patch.object(app_config, 'mysql', _mysql_config()):
        first_run = app.handler(_make_event(), MagicMock())
        second_run = app.handler(_make_event(), MagicMock())

    assert first_run['allocations_created'] == 2
    assert first_run['ledger_adjustments_linked'] == 3
    assert second_run['allocations_created'] == 0
    assert second_run['ledger_adjustments_linked'] == 0

    with db_conn.cursor() as cursor:
        assert len(_get_allocations(cursor)) == 2
        assert _count_links(cursor) == 3


def test_same_contract_different_currency(seed, db_conn):
    """Same contract and payee but different adjustment_currency_code -> separate allocations."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '80.00', 'GBP', '80.00', 'GBP', 1),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 2
    assert allocations[0]['currency_code'] == 'GBP'
    assert allocations[0]['amount_to_payment'] == Decimal('80.00')
    assert allocations[1]['currency_code'] == 'USD'
    assert allocations[1]['amount_to_payment'] == Decimal('100.00')


def test_same_contract_different_payee_currency(seed, db_conn):
    """Same contract and adjustment currency but different payee currency -> separate allocations.

    Grouping key includes both adjustment_currency_code and
    adjustment_payee_currency_code, so cross-currency payee splits
    produce separate payment_allocation records.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '90.00', 'USD', '85.00', 'EUR', 1),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    # Both have currency_code='USD' (adjustment currency) but different payee currencies.
    # ORDER BY doesn't distinguish them, so use set-based assertions.
    assert len(allocations) == 2
    assert all(a['currency_code'] == 'USD' for a in allocations)
    ledger_payment_pairs = {
        (a['amount_to_ledger'], a['amount_to_payment']) for a in allocations
    }
    assert ledger_payment_pairs == {
        (Decimal('100.00'), Decimal('100.00')),
        (Decimal('90.00'), Decimal('85.00')),
    }


def test_non_flowthrough_excluded(seed, db_conn):
    """Adjustments with apply_to_flowthrough_payment=0 are not processed."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '999.00', 'USD', '999.00', 'USD', 0),
            (_ACCOUNT_ID_2, _CONTRACT_ID_2, '200.00', 'EUR', '200.00', 'EUR', 0),
        ]
    )

    result = _run_handler()

    # Only the one flowthrough adjustment is processed
    assert result['allocations_created'] == 1
    assert result['ledger_adjustments_linked'] == 1

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 1
    assert allocations[0]['contract_id'] == _CONTRACT_ID_1
    assert allocations[0]['amount_to_payment'] == Decimal('100.00')


def test_same_contract_different_payees(seed, db_conn):
    """Same contract but different accounts/payees -> separate allocations.

    Two accounts (with different payees) both have adjustments for the same
    contract. The grouping key includes account_payee_id, so each payee
    gets its own allocation.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_2, _CONTRACT_ID_1, '75.00', 'USD', '75.00', 'USD', 1),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 2
    payee_ids = {a['payee_id'] for a in allocations}
    assert payee_ids == {_PAYEE_ID_1, _PAYEE_ID_2}


def test_no_adjustments(seed, db_conn):
    """No adjustments to process -> zero allocations, zero links."""
    seed([])

    result = _run_handler()

    assert result['allocations_created'] == 0
    assert result['ledger_adjustments_linked'] == 0

    with db_conn.cursor() as cursor:
        assert len(_get_allocations(cursor)) == 0
        assert _count_links(cursor) == 0


def test_negative_amounts(seed, db_conn):
    """Negative adjustment amounts are summed correctly."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '-30.00', 'USD', '-30.00', 'USD', 1),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 1
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 1
    assert allocations[0]['amount_to_payment'] == Decimal('70.00')
    assert allocations[0]['amount_to_ledger'] == Decimal('70.00')


def test_amounts_net_to_zero(seed, db_conn):
    """Adjustments that cancel out still create an allocation with zero amount."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '-100.00', 'USD', '-100.00', 'USD', 1),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 1
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)
        assert len(allocations) == 1
        assert allocations[0]['amount_to_payment'] == Decimal('0.00')
        assert allocations[0]['amount_to_ledger'] == Decimal('0.00')
        assert _count_links(cursor) == 2


def test_all_non_flowthrough(seed, db_conn):
    """All adjustments have apply_to_flowthrough_payment=0 -> nothing processed."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 0),
            (_ACCOUNT_ID_2, _CONTRACT_ID_2, '200.00', 'EUR', '200.00', 'EUR', 0),
        ]
    )

    result = _run_handler()

    assert result['allocations_created'] == 0
    assert result['ledger_adjustments_linked'] == 0


def test_simple_event_format(seed, db_conn):
    """Handler accepts the simple {statement_period_payment_entity_id} event format."""
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
        ]
    )

    with patch.object(app_config, 'mysql', _mysql_config()):
        result = app.handler(
            {'statement_period_payment_entity_id': _SPPE_ID}, MagicMock()
        )

    assert result['allocations_created'] == 1
    assert result['ledger_adjustments_linked'] == 1


def test_multiple_batches(seed, db_conn):
    """Small batch_size forces adjustments across multiple batches.

    Three contracts with 1 adjustment each and batch_size=2 produces
    two batches: [C1, C2] and [C3]. All are processed correctly.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '10.00', 'USD', '10.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_2, '20.00', 'USD', '20.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_3, '30.00', 'USD', '30.00', 'USD', 1),
        ]
    )

    with (
        patch.object(app_config, 'mysql', _mysql_config()),
        patch.object(app_config, 'batch_size', 2),
    ):
        result = app.handler(_make_event(), MagicMock())

    assert result['allocations_created'] == 3
    assert result['ledger_adjustments_linked'] == 3

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)

    assert len(allocations) == 3
    amounts = {a['amount_to_payment'] for a in allocations}
    assert amounts == {Decimal('10.00'), Decimal('20.00'), Decimal('30.00')}


def test_oversized_contract_sub_batching(seed, db_conn):
    """Contract exceeding batch_size triggers LIMIT-based sub-batching.

    With batch_size=1, a contract with 2 adjustments in the same group
    is processed in two sub-batches. Each sub-batch independently groups
    its adjustments, producing separate allocations for what would
    normally be one.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '50.00', 'USD', '50.00', 'USD', 1),
        ]
    )

    with (
        patch.object(app_config, 'mysql', _mysql_config()),
        patch.object(app_config, 'batch_size', 1),
    ):
        result = app.handler(_make_event(), MagicMock())

    # Sub-batching splits the group into two separate allocations
    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        allocations = _get_allocations(cursor)
        assert len(allocations) == 2
        amounts = {a['amount_to_payment'] for a in allocations}
        assert amounts == {Decimal('100.00'), Decimal('50.00')}
        assert _count_links(cursor) == 2


def test_contract_discovery_pagination(seed, db_conn):
    """Small contract_batch_size forces outer discovery loop to iterate.

    With contract_batch_size=1, the processor discovers one contract
    per outer loop iteration, requiring multiple rounds to process all.
    """
    seed(
        [
            (_ACCOUNT_ID_1, _CONTRACT_ID_1, '100.00', 'USD', '100.00', 'USD', 1),
            (_ACCOUNT_ID_2, _CONTRACT_ID_2, '200.00', 'EUR', '200.00', 'EUR', 1),
        ]
    )

    with (
        patch.object(app_config, 'mysql', _mysql_config()),
        patch.object(app_config, 'contract_batch_size', 1),
    ):
        result = app.handler(_make_event(), MagicMock())

    assert result['allocations_created'] == 2
    assert result['ledger_adjustments_linked'] == 2

    with db_conn.cursor() as cursor:
        assert len(_get_allocations(cursor)) == 2
        assert _count_links(cursor) == 2