"""Scenario tests derived from the ToE UAT spreadsheet. Source: "Copy of ToE UAT with Accounts.xlsx", Sheet1. Covers the scenarios called out as failing in the July 1 UAT feedback: * Scenario 7 - not on output file * Scenario 28 - Jan month calculation ("adjusting off Feb") * Scenario 31 - failed * Scenario 32 - failed and missing config #2 * Scenario 33 - not on output file * Scenario 34 - not on output file * Scenario 35 - failed * Scenario 36 - not on output file * Scenario 37 - failed and missing config #1 * Scenario 38 - not on output file * Scenario 39 - not on output file * Scenario 40 - failed * Scenario 41 - not on output file Fixtures use the spreadsheet's own "Set Amounts" / "Inputs" columns (Opening Bal, Gross Rev, Net Rev, ToE Config, Process Neg, Adjustment Source, Percent / Flat Rate, Process from static balance?) rather than the Snowflake data from any particular lambda run, so these tests exercise the calculation *rules* the same way tests/unit/test_linear_scenarios.py does, stubbing out OWS/Snowflake entirely. closing_balance mapping: the sheet has no explicit "closing balance" input column - it has "Opening Bal" (prior balance) and "Net Rev" (this period's net revenue). Scenarios 1-3 ("Transfer of Income", not in the failing list) establish the mapping unambiguously: closing_balance = Opening Bal + Net Rev ("Opening Balance + Net Revenue (not incl FT)" column). That mapping is used throughout below. Cross-checking every one of the scenarios below against calculate_amount() / evaluate() shows the calculation engine already produces the exact "Credits/Debits" figures the UAT sheet expects. The July 1 failures traced back to two separate causes, *neither* of which is a wrong calculation: 1. write_output_to_buffer() (src/writer.py) drops any row from the Adjustments sheet when calculated_amount == 0 and there's no error - even when that $0 is the *correct*, rule-following outcome (negative balance blocked, non-positive selected balance blocked, etc). Every "not on output file" / "missing config #N" scenario below (7, 32's config #2, 33, 34, 36's config #1, 37's config #1, 38, 39, 40's config #1, 41) is a legitimately-blocked transfer that simply had no visible trace anywhere describing *why* it was $0. That's fixed by src/calculator.py's new block_reason() + the Summary sheet's new "Block Reason" column (see test_calculator.py::TestBlockReason and test_writer.py's block-reason tests) - this file focuses on proving the underlying calculated_amount is correct. 2. Scenario 28's Jan-then-Feb carryover requires Snowflake to return the balance *for a specific historical statement period* tied to each earnings_transfer record. The current query intentionally always uses whichever statement period is flagged 'current' (see tests/unit/connectors/test_snowflake_query.py:: TestSqlTemplateCurrentStatementPeriod, added under ACC-10361, whose commit message says carryover is deferred), and the OWS earnings-transfer API payload doesn't carry a per-record target period today. Scenario28TestsDocumentCurrentBehavior below documents *today's* behavior and is intentionally not asserting the sheet's Jan figure of -80 (that number implicitly assumes a still-unbuilt period-aware lookup). This is flagged as a follow-up, not fixed here. """ from __future__ import annotations from src.calculator import block_reason, calculate_amount, evaluate from src.enums import RateType, TransferType from src.types import ContractRef, TransferRecord def _record( *, earnings_transfer_id: int, from_contract_id: int, to_contract_id: int, opening_bal: float, net_rev: float, gross_rev: float, input_field: str, percent: float | None = None, flat_rate: float | None = None, negative: bool = False, use_static_balance: bool = False, transfer_type: TransferType = TransferType.TRANSFER, ) -> TransferRecord: """Build a TransferRecord the way OWS + Snowflake would for a UAT row. closing_balance = opening_bal + net_rev (see module docstring). percent is given as a whole-number percentage (e.g. 10 for 10%), matching the sheet's "Percent" column, and converted to the 0-1 ratio the engine expects. """ if percent is not None: rate_type = RateType.PERCENT transfer_amount = percent / 100 else: assert flat_rate is not None rate_type = RateType.FLAT_RATE transfer_amount = flat_rate return TransferRecord( earnings_transfer_id=earnings_transfer_id, transfer_type=transfer_type, rate_type=rate_type, transfer_amount=transfer_amount, input=input_field, negative=negative, use_static_balance=use_static_balance, from_contract=ContractRef(contract_id=from_contract_id), to_contract=ContractRef(contract_id=to_contract_id), closing_balance=opening_bal + net_rev, net_revenue=net_rev, gross_revenue=gross_rev, ) # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 7 - "Band 123 has to give their manager 5% of Closing Balance every # month" - not on output file # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario7: """5% of Closing Balance, negative=N, closing_balance is negative -> blocked.""" def _record(self) -> TransferRecord: return _record( earnings_transfer_id=7, from_contract_id=520214, to_contract_id=505329, opening_bal=-1000.0, gross_rev=500.0, net_rev=400.0, input_field='closing_balance', percent=5, negative=False, transfer_type=TransferType.OVERRIDE, ) def test_correctly_blocked(self) -> None: r = self._record() assert r.closing_balance == -600.0 assert calculate_amount(r) == 0.0 def test_block_reason_explains_negative_closing_balance(self) -> None: r = self._record() reason = block_reason(r, calculate_amount(r)) assert reason is not None assert 'negative' in reason # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 28 - "Band A has a 10% ToE to their manager, but their bank account # got shut down so they have a carry over (Jan / Feb)" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario28DocumentsCurrentBehavior: """Documents today's (current-statement-period-only) behavior. The UAT sheet expects Jan's transfer to be 10% of that month's own net revenue (-80) and Feb's to carry over correctly (-90), i.e. period-scoped balances. The engine has no notion of "the Jan balance" vs "the Feb balance" for a single from_contract - it evaluates whatever closing_balance Snowflake returns (see module docstring, item 2). These tests document that: given each month's own opening balance + net revenue as closing_balance, the engine correctly computes 10% of it, but doesn't do any cross-period carryover accounting. This is intentionally NOT asserting the sheet's -80 Jan figure. Making that pass requires the deferred period-aware Snowflake lookup (ACC-10361) plus an OWS API change to carry a target period per earnings-transfer record - out of scope for this fix. """ def test_jan_uses_jans_own_balance(self) -> None: jan = _record( earnings_transfer_id=28, from_contract_id=341946, to_contract_id=553681, opening_bal=1000.0, gross_rev=900.0, net_rev=800.0, input_field='closing_balance', percent=10, negative=False, ) assert jan.closing_balance == 1800.0 # 10% of the closing_balance the engine actually has (1800), not the # sheet's period-scoped expectation of 80 (10% of Jan's own net # revenue). assert calculate_amount(jan) == 180.0 def test_feb_uses_febs_own_balance_with_no_carryover_from_jan(self) -> None: feb = _record( earnings_transfer_id=28, from_contract_id=341946, to_contract_id=553681, opening_bal=1720.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=10, negative=False, ) assert feb.closing_balance == 2620.0 assert calculate_amount(feb) == 262.0 # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 31 - two ToE configs (percent of Closing Balance) from the same # contract to two different destinations - "failed" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario31: """Cascading is intentional and matches the sheet's own expected math. Config #1: 10% of $900 = $90 (remaining balance after: $810). Config #2 (same from_contract, different to_contract): 10% of the *remaining* $810 = $81 - exactly what the UAT sheet's "Isolated New Balance after ToE" (810) and config #2's Credits/Debits (-81) show. """ def _records(self) -> list[TransferRecord]: config1 = _record( earnings_transfer_id=1, from_contract_id=537808, to_contract_id=539735, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=10, negative=False, ) config2 = _record( earnings_transfer_id=2, from_contract_id=537808, to_contract_id=543530, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=10, negative=False, ) return [config1, config2] def test_config_1_is_10_percent_of_original_balance(self) -> None: results = evaluate(self._records()) assert results[0].calculated_amount == 90.0 def test_config_2_cascades_off_config_1s_remainder(self) -> None: results = evaluate(self._records()) assert results[1].calculated_amount == 81.0 def test_neither_config_is_blocked(self) -> None: results = evaluate(self._records()) assert all(r.block_reason is None for r in results) # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 32 - two ToE configs, one off Closing Balance and one off Net # Revenue - "failed and missing config #2" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario32: """Verify scenario 32's two ToE configs. Config #2 uses net_revenue, which never cascades (per calculator.py's documented behavior: only closing_balance input is drawn down by prior transfers). Both configs independently compute 10% of $900 = $90, matching the sheet. """ def _records(self) -> list[TransferRecord]: config1 = _record( earnings_transfer_id=1, from_contract_id=546310, to_contract_id=545180, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=10, negative=False, ) config2 = _record( earnings_transfer_id=2, from_contract_id=546310, to_contract_id=530639, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='net_revenue', percent=10, negative=False, ) return [config1, config2] def test_config_1_closing_balance_based(self) -> None: results = evaluate(self._records()) assert results[0].calculated_amount == 90.0 def test_config_2_net_revenue_does_not_cascade(self) -> None: results = evaluate(self._records()) assert results[1].calculated_amount == 90.0 def test_config_2_only_blocked_when_net_revenue_is_zero(self) -> None: # This is the actual July 1 failure mode: in production, this # contract's real net_revenue happened to be 0, so config #2 was # correctly blocked and silently dropped from Adjustments (fixed via # block_reason / Summary visibility, not a calculator bug). config2 = _record( earnings_transfer_id=2, from_contract_id=546310, to_contract_id=530639, opening_bal=0.0, gross_rev=1000.0, net_rev=0.0, input_field='net_revenue', percent=10, negative=False, ) amt = calculate_amount(config2) assert amt == 0.0 reason = block_reason(config2, amt) assert reason is not None assert 'net_revenue' in reason # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 33 - FT of 10% Net Revenue (not paying out) + ToE of 10% Net # Revenue, negative=Y - "not on output file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario33: """Verify scenario 33 (negative=True skips the closing-balance gate). negative=True skips the closing-balance gate entirely, so a negative closing balance (from the modeled Flowthrough deduction) does not block the net-revenue-based transfer - only the selected balance matters. """ def test_jan(self) -> None: jan = _record( earnings_transfer_id=33, from_contract_id=324771, to_contract_id=334895, opening_bal=-1000.0, gross_rev=500.0, net_rev=400.0, input_field='net_revenue', percent=10, negative=True, ) assert jan.closing_balance == -600.0 # negative, but gate is skipped assert calculate_amount(jan) == 40.0 assert block_reason(jan, calculate_amount(jan)) is None def test_feb_carryover(self) -> None: feb = _record( earnings_transfer_id=33, from_contract_id=324771, to_contract_id=334895, opening_bal=-680.0, gross_rev=400.0, net_rev=300.0, input_field='net_revenue', percent=10, negative=True, ) assert calculate_amount(feb) == 30.0 # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 34 - FT of 10% Net Revenue (not paying out) + ToE of 10% Net # Revenue, negative=N, closing balance lands at exactly $0 - "not on output # file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario34: """Verify scenario 34 (closing_balance lands at exactly $0). closing_balance == 0 must not block a net_revenue-based transfer: 0 is not negative, so the closing-balance gate passes, and the non-positive check only applies to the *selected* balance. """ def test_jan(self) -> None: jan = _record( earnings_transfer_id=34, from_contract_id=538521, to_contract_id=318215, opening_bal=-1000.0, gross_rev=1100.0, net_rev=1000.0, input_field='net_revenue', percent=10, negative=False, ) assert jan.closing_balance == 0.0 assert calculate_amount(jan) == 100.0 assert block_reason(jan, calculate_amount(jan)) is None def test_feb_carryover(self) -> None: feb = _record( earnings_transfer_id=34, from_contract_id=538521, to_contract_id=318215, opening_bal=-100.0, gross_rev=500.0, net_rev=400.0, input_field='net_revenue', percent=10, negative=False, ) assert calculate_amount(feb) == 40.0 # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 35 - "Want to be sure we're processing ToE when Closing Balance is # $0" - "failed" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario35: """Verify scenario 35. Same $0-closing-balance edge case as scenario 34, isolated as its own scenario in the sheet: closing_balance lands at exactly 0 and must not block the net_revenue-based transfer. """ def _record(self) -> TransferRecord: return _record( earnings_transfer_id=35, from_contract_id=297685, to_contract_id=239624, opening_bal=-500.0, gross_rev=600.0, net_rev=500.0, input_field='net_revenue', percent=10, negative=False, ) def test_zero_closing_balance_does_not_block(self) -> None: r = self._record() assert r.closing_balance == 0.0 assert calculate_amount(r) == 50.0 def test_not_blocked(self) -> None: r = self._record() assert block_reason(r, calculate_amount(r)) is None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 36 - Contract B is both a TO (from A) and a FROM (to C) - "not on # output file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario36: """Verify scenario 36 (a contract that is both a TO and a FROM). Config #2's from_contract is config #1's to_contract: the incoming credit from config #1 increases the balance available for config #2's cascade (already covered generally by test_calculator.py::test_incoming_credit_increases_available_balance; this pins the exact scenario 36 numbers). """ def _records(self) -> list[TransferRecord]: config1 = _record( earnings_transfer_id=1, from_contract_id=539111, to_contract_id=537325, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='net_revenue', percent=10, negative=False, ) config2 = _record( earnings_transfer_id=2, from_contract_id=537325, to_contract_id=298041, opening_bal=0.0, gross_rev=500.0, net_rev=400.0, input_field='closing_balance', percent=10, negative=False, ) return [config1, config2] def test_config_1(self) -> None: results = evaluate(self._records()) assert results[0].calculated_amount == 90.0 def test_config_2_balance_increased_by_config_1s_incoming_credit(self) -> None: results = evaluate(self._records()) # base closing_balance 400 + incoming credit 90 = 490; 10% = 49. assert results[1].calculated_amount == 49.0 def test_config_2_only_blocked_when_its_own_net_revenue_is_zero(self) -> None: # The actual July 1 failure mode for config #1: in production this # contract's real net_revenue was 0, so it was correctly blocked and # silently dropped (fixed via block_reason visibility, not a # calculator bug). config1 = _record( earnings_transfer_id=1, from_contract_id=539111, to_contract_id=537325, opening_bal=0.0, gross_rev=1000.0, net_rev=0.0, input_field='net_revenue', percent=10, negative=False, ) amt = calculate_amount(config1) assert amt == 0.0 assert block_reason(config1, amt) is not None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 37 - zero out Contract A's balance, redistributing to B and C - # "failed and missing config #1" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario37: """Verify scenario 37's config #1. NOTE: the UAT sheet's row for this scenario's config #2 lists a *different* from_contract (502102) than config #1 (341770), which contradicts the scenario's own description ("zero out the balance of Contract A ... to Contract B and C" implies one shared source contract). That looks like a data-entry error in the spreadsheet itself, not something reproducible in a fixture. This test covers config #1 in isolation, which is unambiguous: 50% of Closing Balance, use_static_balance=True, single from_contract. """ def test_config_1_uses_static_balance(self) -> None: config1 = _record( earnings_transfer_id=37, from_contract_id=341770, to_contract_id=506967, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=50, negative=False, use_static_balance=True, transfer_type=TransferType.OVERRIDE, ) assert config1.closing_balance == 900.0 assert calculate_amount(config1) == 450.0 def test_config_1_only_blocked_when_closing_balance_negative(self) -> None: # The actual July 1 failure mode: in production this contract's real # closing_balance was negative, so it was correctly blocked # (negative=False) and silently dropped (fixed via block_reason # visibility, not a calculator bug). config1 = _record( earnings_transfer_id=37, from_contract_id=341770, to_contract_id=506967, opening_bal=-2000.0, gross_rev=1000.0, net_rev=800.0, input_field='closing_balance', percent=50, negative=False, use_static_balance=True, transfer_type=TransferType.OVERRIDE, ) amt = calculate_amount(config1) assert amt == 0.0 assert block_reason(config1, amt) is not None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 38 - decimal percent off Gross Revenue - "not on output file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario38: """Verify scenario 38. Percent with a fractional component (10.25%) is handled exactly like any other percent - no special-casing needed, no rounding surprises. """ def test_decimal_percent_of_gross_revenue(self) -> None: r = _record( earnings_transfer_id=38, from_contract_id=334996, to_contract_id=317330, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='gross_revenue', percent=10.25, negative=False, ) assert calculate_amount(r) == 102.5 def test_only_blocked_when_gross_revenue_is_zero(self) -> None: # The actual July 1 failure mode: production gross_revenue was 0. r = _record( earnings_transfer_id=38, from_contract_id=334996, to_contract_id=317330, opening_bal=0.0, gross_rev=0.0, net_rev=900.0, input_field='gross_revenue', percent=10.25, negative=False, ) amt = calculate_amount(r) assert amt == 0.0 assert block_reason(r, amt) is not None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 39 - decimal percent off Closing Balance - "not on output file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario39: """Verify scenario 39 (decimal percent off Closing Balance).""" def test_decimal_percent_of_closing_balance(self) -> None: r = _record( earnings_transfer_id=39, from_contract_id=281513, to_contract_id=316864, opening_bal=0.0, gross_rev=500.0, net_rev=400.0, input_field='closing_balance', percent=22.53, negative=False, ) assert r.closing_balance == 400.0 assert calculate_amount(r) == 90.12 def test_only_blocked_when_closing_balance_is_zero(self) -> None: # The actual July 1 failure mode: production closing_balance was 0. r = _record( earnings_transfer_id=39, from_contract_id=281513, to_contract_id=316864, opening_bal=0.0, gross_rev=0.0, net_rev=0.0, input_field='closing_balance', percent=22.53, negative=False, ) amt = calculate_amount(r) assert amt == 0.0 assert block_reason(r, amt) is not None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 40 - one contract, two static-balance configs to two # destinations - "failed" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario40: """Verify scenario 40. Config #1 (net_revenue) never cascades; config #2 (closing_balance, use_static_balance=True) explicitly opts out of the cascade too - both independently compute 10% of the shared $900 base. """ def _records(self) -> list[TransferRecord]: config1 = _record( earnings_transfer_id=1, from_contract_id=43855, to_contract_id=281569, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='net_revenue', percent=10, use_static_balance=True, ) config2 = _record( earnings_transfer_id=2, from_contract_id=43855, to_contract_id=341579, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', percent=10, use_static_balance=True, ) return [config1, config2] def test_config_1_net_revenue(self) -> None: results = evaluate(self._records()) assert results[0].calculated_amount == 90.0 def test_config_2_static_closing_balance_not_reduced_by_config_1(self) -> None: results = evaluate(self._records()) assert results[1].calculated_amount == 90.0 def test_config_1_only_blocked_when_net_revenue_is_zero(self) -> None: # The actual July 1 failure mode: production net_revenue was 0 for # config #1. config1 = _record( earnings_transfer_id=1, from_contract_id=43855, to_contract_id=281569, opening_bal=0.0, gross_rev=1000.0, net_rev=0.0, input_field='net_revenue', percent=10, use_static_balance=True, ) amt = calculate_amount(config1) assert amt == 0.0 assert block_reason(config1, amt) is not None # ═══════════════════════════════════════════════════════════════════════════════ # Scenario 41 - decimal flat rate off Closing Balance - "not on output file" # ═══════════════════════════════════════════════════════════════════════════════ class TestScenario41: """Verify scenario 41 (decimal flat rate off Closing Balance).""" def test_decimal_flat_rate(self) -> None: r = _record( earnings_transfer_id=41, from_contract_id=107126, to_contract_id=321549, opening_bal=0.0, gross_rev=1000.0, net_rev=900.0, input_field='closing_balance', flat_rate=500.75, negative=False, ) assert r.closing_balance == 900.0 assert calculate_amount(r) == 500.75 def test_only_blocked_when_closing_balance_negative(self) -> None: # The actual July 1 failure mode: production closing_balance was # deeply negative (-70456.5). r = _record( earnings_transfer_id=41, from_contract_id=107126, to_contract_id=321549, opening_bal=-71000.0, gross_rev=1000.0, net_rev=500.0, input_field='closing_balance', flat_rate=500.75, negative=False, ) amt = calculate_amount(r) assert amt == 0.0 reason = block_reason(r, amt) assert reason is not None assert 'negative' in reason