"""Unit tests for WorksheetAdjustment model."""

from decimal import Decimal

from sqlalchemy import event

from abacus_worksheet.api import db
from abacus_worksheet.models.statement_period_adjustment_file import (
    StatementPeriodAdjustmentFile,
)
from abacus_worksheet.models.worksheet_adjustment import WorksheetAdjustment

from tests.utils.factories import (
    WorksheetAdjustmentDetailFactory,
    WorksheetAdjustmentFactory,
)


def test_worksheet_adjustment_create(mock_worksheet_adjustment_data):
    """Test worksheet_adjustment model."""
    mock_data = mock_worksheet_adjustment_data
    response = WorksheetAdjustment.build(**mock_data)

    assert (
        response.statement_period_adjustment_file_id
        == mock_data['statement_period_adjustment_file_id']
    )
    assert response.abacus_event_id == mock_data['abacus_event_id']
    assert response.account_id == mock_data['account_id']
    assert response.contract_id == mock_data['contract_id']
    assert (
        response.activity_statement_period_id
        == mock_data['activity_statement_period_id']
    )
    assert (
        response.apply_to_statement_period_id
        == mock_data['apply_to_statement_period_id']
    )
    assert (
        response.reference_adjustment_type_id
        == mock_data['reference_adjustment_type_id']
    )
    assert response.adjustment_amount == mock_data['adjustment_amount']
    assert response.adjustment_currency_code == mock_data['adjustment_currency_code']
    assert response.note == mock_data['note']


def test_get_by_statement_period_adjustment_file_id():
    """Test to get worksheet adjustments by statement_period_adjustment_file_id."""
    WorksheetAdjustmentFactory.create_batch(4)
    statement_period_adjustment_file_id = 1
    limit = 2
    offset = 0

    WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=statement_period_adjustment_file_id,
        deleted_by='Test User',
        deleted_at='2023-10-06',
    )
    items, total_count = WorksheetAdjustment.get_by_statement_period_adjustment_file_id(
        statement_period_adjustment_file_id, limit, offset
    )

    assert total_count == 4
    assert len(items) == 2
    assert (
        items[0].statement_period_adjustment_file_id
        == statement_period_adjustment_file_id
    )
    assert items[0].details == []


def test_get_by_adjustment_file_id_with_details_field():
    """Test to get worksheet adjustments by statement_period_adjustment_file_id."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create_batch(2)
    WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=statement_period_adjustment_file_id,
        deleted_by='Test User',
        deleted_at='2023-10-06',
    )
    mock_worksheet_adjustment = WorksheetAdjustmentFactory.create()
    mock_worksheet_adjustment_detail = WorksheetAdjustmentDetailFactory.create(
        worksheet_adjustment=mock_worksheet_adjustment
    )
    limit = 4
    offset = 0
    items, total_count = WorksheetAdjustment.get_by_statement_period_adjustment_file_id(
        statement_period_adjustment_file_id, limit, offset
    )

    assert total_count == 3
    assert len(items) == 3
    assert (
        items[0].statement_period_adjustment_file_id
        == statement_period_adjustment_file_id
    )
    assert items[0].details == []

    assert (
        items[2].statement_period_adjustment_file_id
        == statement_period_adjustment_file_id
    )
    assert items[2].details == [mock_worksheet_adjustment_detail]


def test_soft_delete_worksheet_adjustments():
    """Test to soft delete worksheet adjustments."""
    WorksheetAdjustmentFactory.create_batch(4)
    worksheet_adjustments = WorksheetAdjustment.query.all()

    assert (
        all(
            [
                adjustment.deleted_by is None and adjustment.deleted_at is None
                for adjustment in worksheet_adjustments
            ]
        )
        is True
    )

    statement_period_adjustment_file_id = 1

    WorksheetAdjustment.soft_delete_worksheet_adjustments(
        statement_period_adjustment_file_id
    )

    assert (
        all(
            [
                adjustment.deleted_by is not None and adjustment.deleted_at is not None
                for adjustment in worksheet_adjustments
            ]
        )
        is True
    )


def test_get_worksheet_adjustments_and_details():
    """Test to get worksheet adjustments and details."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        adjustment_amount=Decimal('100.00'), adjustment_currency_code='EUR'
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        statement_period_adjustment_file_id
    )

    assert total_count == 3
    assert items[0].upc is None
    assert items[1].upc == '555444333222111'
    assert items[2].upc == '1234567891'

    assert items[0].adjustment_amount == Decimal('125.42')
    assert items[1].adjustment_amount == Decimal('50.00')
    assert items[2].adjustment_amount == Decimal('50.00')

    assert items[0].adjustment_currency_code == 'USD'
    assert items[1].adjustment_currency_code == 'EUR'
    assert items[2].adjustment_currency_code == 'EUR'


def test__query_to_get_worksheet_adjustments_and_details():
    """Test _query_to_get_worksheet_adjustments_and_details method."""
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        adjustment_amount=Decimal('100.00'), adjustment_currency_code='EUR'
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        worksheet_adjustment=worksheet_adjustment_2,
    )

    result = WorksheetAdjustment._query_to_get_worksheet_adjustments_and_details().query
    assert result[0].upc is None
    assert result[1].upc == '555444333222111'
    assert result[2].upc == '1234567891'


def test_get_worksheet_adjustments_and_details_is_deleted():
    """False (default) hides deleted; True shows only deleted; None shows both."""
    file_id = 1
    live = WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id
    )
    deleted = WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        deleted_by='t',
        deleted_at='2024-01-01',
    )

    def ids(is_deleted):
        items, _ = WorksheetAdjustment.get_worksheet_adjustments_and_details(
            file_id, is_deleted=is_deleted
        )
        return {row.worksheet_adjustment_id for row in items}

    assert ids(False) == {live.worksheet_adjustment_id}
    assert ids(True) == {deleted.worksheet_adjustment_id}
    assert ids(None) == {live.worksheet_adjustment_id, deleted.worksheet_adjustment_id}


def test_get_worksheet_adjustments_and_details_is_deleted_asymmetric_markers():
    """A row with only one deletion marker set counts as deleted, never active.

    active and deleted are exact complements, so a half-marked row (one of
    deleted_at/deleted_by null) must fall on the deleted side of the split and
    never leak into active. This guards the OR in the deleted predicate.
    """
    file_id = 1
    only_deleted_at = WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        deleted_by=None,
        deleted_at='2024-01-01',
    )
    only_deleted_by = WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        deleted_by='t',
        deleted_at=None,
    )

    def ids(is_deleted):
        items, _ = WorksheetAdjustment.get_worksheet_adjustments_and_details(
            file_id, is_deleted=is_deleted
        )
        return {row.worksheet_adjustment_id for row in items}

    half_marked = {
        only_deleted_at.worksheet_adjustment_id,
        only_deleted_by.worksheet_adjustment_id,
    }
    assert ids(False) == set()
    assert ids(True) == half_marked
    assert ids(None) == half_marked


def test_get_pending_worksheet_adjustments(mock_applied_worksheet_adjustment):
    """Test get_pending_worksheet_adjustments method."""
    unapplied_worksheet1 = WorksheetAdjustmentFactory.create()
    unapplied_worksheet2 = WorksheetAdjustmentFactory.create(
        apply_to_statement_period_id=unapplied_worksheet1.apply_to_statement_period_id
        + 1
    )

    items, total_count = WorksheetAdjustment.get_pending_worksheet_adjustments(10, 0)
    assert total_count == 2
    assert len(items) == 2
    assert (
        items[0].worksheet_adjustment_id == unapplied_worksheet1.worksheet_adjustment_id
    )
    assert (
        items[1].worksheet_adjustment_id == unapplied_worksheet2.worksheet_adjustment_id
    )

    items, total_count = WorksheetAdjustment.get_pending_worksheet_adjustments(1, 1)
    assert total_count == 2
    assert len(items) == 1
    assert (
        items[0].worksheet_adjustment_id == unapplied_worksheet2.worksheet_adjustment_id
    )

    items, total_count = WorksheetAdjustment.get_pending_worksheet_adjustments(
        10, 0, unapplied_worksheet1.apply_to_statement_period_id, 1
    )
    assert total_count == 1
    assert len(items) == 1
    assert (
        items[0].worksheet_adjustment_id == unapplied_worksheet1.worksheet_adjustment_id
    )

    items, total_count = WorksheetAdjustment.get_pending_worksheet_adjustments(
        10, 0, unapplied_worksheet1.apply_to_statement_period_id, 2
    )
    assert total_count == 0
    assert len(items) == 0


def test_get_pending_worksheet_adjustments_excludes_deleted(
    mock_applied_worksheet_adjustment,
):
    """Soft-deleted rows never appear in the apply/pending reader."""
    live = WorksheetAdjustmentFactory.create()
    deleted = WorksheetAdjustmentFactory.create(deleted_by='t', deleted_at='2024-01-01')

    items, _ = WorksheetAdjustment.get_pending_worksheet_adjustments(10, 0)
    ids = {row.worksheet_adjustment_id for row in items}

    assert live.worksheet_adjustment_id in ids
    assert deleted.worksheet_adjustment_id not in ids


def test_get_worksheet_adjustments_and_details_filter_by_contract_ids():
    """Test to get worksheet adjustments and details filter by contract ids ."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        statement_period_adjustment_file_id, None, '2'
    )

    assert total_count == 2
    assert items[0].upc == '555444333222111'
    assert items[1].upc == '1234567891'

    assert items[0].adjustment_amount == Decimal('50.00')
    assert items[1].adjustment_amount == Decimal('50.00')

    assert items[0].adjustment_currency_code == 'EUR'
    assert items[1].adjustment_currency_code == 'EUR'


def test_get_worksheet_adjustments_and_details_filter_by_account_ids():
    """Test to get worksheet adjustments and details filter by account ids ."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        account_id=2,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        account_id=2,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        account_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        account_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        account_id=2,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        statement_period_adjustment_file_id, '2'
    )

    assert total_count == 2
    assert items[0].upc == '555444333222111'
    assert items[1].upc == '1234567891'

    assert items[0].adjustment_amount == Decimal('50.00')
    assert items[1].adjustment_amount == Decimal('50.00')

    assert items[0].adjustment_currency_code == 'EUR'
    assert items[1].adjustment_currency_code == 'EUR'


def test_get_adjustments_and_details_filter_by_flowthrough_payment_column():
    """Test to get worksheet adjustments and details filter by apply_to_flowthrough_payment column."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create(apply_to_flowthrough_payment=1)
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('200.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('150.00'),
        currency_code='EUR',
        contract_id=2,
        apply_to_flowthrough_payment=1,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        contract_id=2,
        apply_to_flowthrough_payment=None,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        contract_id=2,
        apply_to_flowthrough_payment=0,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        statement_period_adjustment_file_id, None, None, '1,null'
    )

    assert total_count == 3
    assert items[0].upc is None
    assert items[1].upc == '555444333222111'
    assert items[2].upc == '1234567891'

    assert items[0].adjustment_amount == Decimal('125.42')
    assert items[1].adjustment_amount == Decimal('150.00')
    assert items[2].adjustment_amount == Decimal('50.00')

    assert items[0].adjustment_currency_code == 'USD'
    assert items[1].adjustment_currency_code == 'EUR'
    assert items[2].adjustment_currency_code == 'EUR'


def test_get_worksheet_adjustments_and_details_filter_by_account_contract_ids():
    """Test to get worksheet adjustments and details filter by account and contract ids."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=4,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=5,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentFactory.create(
        account_id=3,
        contract_id=3,
        adjustment_amount=Decimal('101.00'),
        adjustment_currency_code='GBP',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        account_id=4,
        contract_id=5,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        statement_period_adjustment_file_id, '1,3,4', '1,4,5'
    )

    assert total_count == 3
    assert items[0].upc is None
    assert items[1].upc == '555444333222111'
    assert items[2].upc == '1234567891'

    assert items[0].adjustment_amount == Decimal('125.42')
    assert items[1].adjustment_amount == Decimal('50.00')
    assert items[2].adjustment_amount == Decimal('50.00')

    assert items[0].adjustment_currency_code == 'USD'
    assert items[1].adjustment_currency_code == 'EUR'
    assert items[2].adjustment_currency_code == 'EUR'


def test_get_worksheet_adjustments_contracts_by_contract_name():
    """Test to get worksheet adjustments contracts by contract name."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = (
        WorksheetAdjustment.get_worksheet_adjustments_contracts_by_file_id(
            statement_period_adjustment_file_id, '1-2'
        )
    )

    assert total_count == 1
    assert items[0].contract_id == 2


def test_get_worksheet_adjustments_contracts_by_any_number():
    """Test to get worksheet adjustments contracts by any number."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        contract_id=2,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        contract_id=2,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = (
        WorksheetAdjustment.get_worksheet_adjustments_contracts_by_file_id(
            statement_period_adjustment_file_id, '1'
        )
    )

    assert total_count == 2
    assert items[0].contract_id == 1
    assert items[1].contract_id == 2


def test_get_worksheet_adjustments_contracts_by_account_name():
    """Test to get worksheet adjustments accounts by account name."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=4,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=5,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentFactory.create(
        account_id=3,
        contract_id=3,
        adjustment_amount=Decimal('101.00'),
        adjustment_currency_code='GBP',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        account_id=4,
        contract_id=5,
        worksheet_adjustment=worksheet_adjustment_3,
    )
    items, total_count = (
        WorksheetAdjustment.get_worksheet_adjustments_accounts_by_file_id(
            statement_period_adjustment_file_id, 'Stephen'
        )
    )

    assert total_count == 1
    assert items[0].account_id == 4


def test_get_worksheet_adjustments_accounts_by_any_number():
    """Test to get worksheet adjustments accounts by any number."""
    statement_period_adjustment_file_id = 1
    WorksheetAdjustmentFactory.create()
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=4,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='EUR',
    )
    worksheet_adjustment_3 = WorksheetAdjustmentFactory.create(
        account_id=4,
        contract_id=5,
        adjustment_amount=Decimal('150.00'),
        adjustment_currency_code='EUR',
        deleted_by='Test',
        deleted_at='2024-02-01',
    )
    WorksheetAdjustmentFactory.create(
        account_id=3,
        contract_id=3,
        adjustment_amount=Decimal('101.00'),
        adjustment_currency_code='GBP',
    )
    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('50.00'),
        currency_code='EUR',
        upc='1234567891',
        account_id=4,
        contract_id=4,
        worksheet_adjustment=worksheet_adjustment_2,
    )

    WorksheetAdjustmentDetailFactory.create(
        amount=Decimal('80.00'),
        currency_code='EUR',
        upc='1234567892',
        account_id=4,
        contract_id=5,
        worksheet_adjustment=worksheet_adjustment_3,
    )

    items, total_count = (
        WorksheetAdjustment.get_worksheet_adjustments_accounts_by_file_id(
            statement_period_adjustment_file_id, '3'
        )
    )

    assert total_count == 1
    assert items[0].account_id == 3


def test_get_adjustments_by_period_and_type_id(fresh_db):
    """Test model method get_adjustments_by_period_and_type_id.

    Ensures it returns items, total_count and the currency-agnostic total amount
    for matching records, excluding deleted ones and non-matching filters.
    """
    period_id = 1
    type_id = 1

    worksheet_adjustment_1 = WorksheetAdjustmentFactory.create(
        activity_statement_period_id=period_id,
        reference_adjustment_type_id=type_id,
        adjustment_amount=Decimal('100.00'),
        adjustment_currency_code='USD',
    )
    worksheet_adjustment_2 = WorksheetAdjustmentFactory.create(
        activity_statement_period_id=period_id,
        reference_adjustment_type_id=type_id,
        adjustment_amount=Decimal('50.00'),
        adjustment_currency_code='EUR',
    )

    WorksheetAdjustmentFactory.create(
        activity_statement_period_id=period_id,
        reference_adjustment_type_id=type_id,
        adjustment_amount=Decimal('25.00'),
        adjustment_currency_code='USD',
        deleted_by='Test User',
        deleted_at='2024-10-01',
    )

    # Seed applied ledger rows to satisfy inner join filter, using raw SQL
    sql = f"""
        INSERT INTO ledger_adjustment_applied (
            `ledger_adjustment_applied_id`,
            `abacus_event_id`,
            `account_id`,
            `contract_id`,
            `statement_period_id`,
            `ledger_adjustment_id`,
            `worksheet_adjustment_id`,
            `adjustment_amount`,
            `adjustment_currency_code`,
            `adjustment_amount_payee_currency`,
            `adjustment_payee_currency_code`,
            `created_by`,
            `created_at`,
            `last_modified_by`,
            `last_modified`
        ) VALUES
        (
            1,
            1,
            {worksheet_adjustment_1.account_id},
            {worksheet_adjustment_1.contract_id if worksheet_adjustment_1.contract_id is not None else 'NULL'},
            {worksheet_adjustment_1.apply_to_statement_period_id},
            NULL,
            {worksheet_adjustment_1.worksheet_adjustment_id},
            '{worksheet_adjustment_1.adjustment_amount}',
            '{worksheet_adjustment_1.adjustment_currency_code}',
            '{worksheet_adjustment_1.adjustment_amount}',
            '{worksheet_adjustment_1.adjustment_currency_code}',
            'test',
            NOW(),
            'test',
            NOW()
        ),
        (
            2,
            1,
            {worksheet_adjustment_2.account_id},
            {worksheet_adjustment_2.contract_id if worksheet_adjustment_2.contract_id is not None else 'NULL'},
            {worksheet_adjustment_2.apply_to_statement_period_id},
            NULL,
            {worksheet_adjustment_2.worksheet_adjustment_id},
            '{worksheet_adjustment_2.adjustment_amount}',
            '{worksheet_adjustment_2.adjustment_currency_code}',
            '{worksheet_adjustment_2.adjustment_amount}',
            '{worksheet_adjustment_2.adjustment_currency_code}',
            'test',
            NOW(),
            'test',
            NOW()
        );
    """
    # Ensure insert is committed and visible to the current session
    db.session.execute(sql)
    db.session.commit()

    items, total_count, total_amount = (
        WorksheetAdjustment.get_adjustments_by_period_and_type_id(period_id, type_id)
    )

    assert total_count == 2
    assert len(items) == 2
    assert {items[0].worksheet_adjustment_id, items[1].worksheet_adjustment_id} == {
        worksheet_adjustment_1.worksheet_adjustment_id,
        worksheet_adjustment_2.worksheet_adjustment_id,
    }
    assert total_amount == float(Decimal('150.00'))


def test_get_applied_ids_returns_only_applied(mock_applied_worksheet_adjustment):
    """get_applied_ids returns the subset of ids present in ledger_adjustment_applied."""
    applied = mock_applied_worksheet_adjustment
    unapplied = WorksheetAdjustmentFactory.create()

    result = WorksheetAdjustment.get_applied_ids(
        [applied.worksheet_adjustment_id, unapplied.worksheet_adjustment_id]
    )

    assert result == {applied.worksheet_adjustment_id}


def test_get_applied_ids_empty_input_returns_empty_set():
    """An empty id list returns an empty set without querying."""
    assert WorksheetAdjustment.get_applied_ids([]) == set()


def test_is_apply_complete_true_when_state_row_complete(insert_apply_file_state):
    """is_apply_complete reflects an abacus_state apply_file/complete row."""
    file_id = insert_apply_file_state(status='complete')
    assert StatementPeriodAdjustmentFile.is_apply_complete(file_id) is True


def test_is_apply_complete_false_when_state_row_init(insert_apply_file_state):
    """is_apply_complete is False for a non-complete apply_file state row."""
    file_id = insert_apply_file_state(status='init')
    assert StatementPeriodAdjustmentFile.is_apply_complete(file_id) is False


def test_is_apply_complete_false_when_no_state_row():
    """is_apply_complete is False when there is no apply_file row for the file."""
    assert StatementPeriodAdjustmentFile.is_apply_complete(424242) is False


def test_select_for_update_by_ids_does_not_join_details():
    """The FOR UPDATE lock query must not join the detail table.

    That keeps it from locking detail rows, which would invert lock order
    against the whole-file delete.
    """
    parent = WorksheetAdjustmentFactory.create()
    WorksheetAdjustmentDetailFactory.create(worksheet_adjustment=parent)

    statements = []

    def _capture(conn, cursor, statement, params, context, executemany):
        statements.append(statement)

    engine = db.session.get_bind()
    event.listen(engine, 'before_cursor_execute', _capture)
    try:
        locked = WorksheetAdjustment.select_for_update_by_ids(
            [parent.worksheet_adjustment_id]
        )
    finally:
        event.remove(engine, 'before_cursor_execute', _capture)

    assert len(locked) == 1
    lock_sql = next(s for s in statements if 'for update' in s.lower())
    assert 'worksheet_adjustment_detail' not in lock_sql


def test_get_worksheet_adjustments_deleted_aggregate():
    """Deleted-only; count at detail grain; amount reuses the detail/parent case; currency-agnostic."""
    file_id = 1
    # active row (must be IGNORED by the deleted aggregate)
    WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id, adjustment_amount=Decimal('125.42')
    )
    # deleted no-detail parent -> 1 row, amount = parent.adjustment_amount (USD)
    WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        adjustment_amount=Decimal('10.00'),
        adjustment_currency_code='USD',
        deleted_by='t',
        deleted_at='2024-01-01',
    )
    # deleted expense-grouped parent (parent amount 999 IGNORED) -> 2 detail rows (EUR)
    dp = WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        adjustment_amount=Decimal('999.99'),
        adjustment_currency_code='EUR',
        deleted_by='t',
        deleted_at='2024-01-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        worksheet_adjustment=dp,
        amount=Decimal('50.00'),
        currency_code='EUR',
        deleted_by='t',
        deleted_at='2024-01-01',
    )
    WorksheetAdjustmentDetailFactory.create(
        worksheet_adjustment=dp,
        amount=Decimal('30.00'),
        currency_code='EUR',
        upc='1234567890',
        deleted_by='t',
        deleted_at='2024-01-01',
    )

    agg = WorksheetAdjustment.get_worksheet_adjustments_deleted_aggregate(file_id)
    # deleted rows: 1 (no-detail) + 2 (details) = 3; amount 10 + 50 + 30 across currencies
    assert agg['deleted_count'] == 3
    assert agg['currency_agnostic_deleted_amount'] == Decimal('90.00')
    # count matches the grid's deleted total_count at the same grain
    _, total = WorksheetAdjustment.get_worksheet_adjustments_and_details(
        file_id, is_deleted=True
    )
    assert total == agg['deleted_count']


def test_deleted_aggregate_empty_and_no_detail_deleted_parent():
    """Empty file aggregates to zero; a deleted no-detail parent counts as one."""
    file_id = 2
    assert WorksheetAdjustment.get_worksheet_adjustments_deleted_aggregate(file_id) == {
        'deleted_count': 0,
        'currency_agnostic_deleted_amount': Decimal('0'),
    }
    WorksheetAdjustmentFactory.create(
        statement_period_adjustment_file_id=file_id,
        adjustment_amount=Decimal('7.00'),
        deleted_by='t',
        deleted_at='2024-01-01',
    )
    agg = WorksheetAdjustment.get_worksheet_adjustments_deleted_aggregate(file_id)
    assert agg == {
        'deleted_count': 1,
        'currency_agnostic_deleted_amount': Decimal('7.00'),
    }