"""Local end-to-end test. Runs the full app.handler() pipeline with a real Snowflake connection (using .env credentials) and mocked OWS API + S3. Transfer records are derived from the UAT backfill SQL fixture (ACC-10250). Writes the output XLSX to the resolved ``OUTPUT_DIR`` (the ``tmp`` directory under the earnings_transfer project directory, not the current working directory) so you can inspect it. """ from __future__ import annotations import os from pathlib import Path from unittest.mock import MagicMock, patch from openpyxl import load_workbook _PROJECT_ROOT = Path(__file__).resolve().parents[2] OUTPUT_DIR = _PROJECT_ROOT / 'tmp' # -- OWS fixture records (from ACC-10250_backfill-uat-earnings-transfers.sql) -- # Scenario 14 is inactive (active=0), all others active. OWS_API_RECORDS = [ # Scenario 1: two-member band split { 'earnings_transfer_id': 1, 'from_contract_id': 333453, 'to_contract_id': 326052, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.5', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 2, 'from_contract_id': 333453, 'to_contract_id': 321261, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.5', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 2 { 'earnings_transfer_id': 3, 'from_contract_id': 286500, 'to_contract_id': 317898, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.5', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 4, 'from_contract_id': 286500, 'to_contract_id': 37619, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.5', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 3: unequal four-piece split { 'earnings_transfer_id': 5, 'from_contract_id': 317328, 'to_contract_id': 317907, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.4', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 6, 'from_contract_id': 317328, 'to_contract_id': 332267, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 7, 'from_contract_id': 317328, 'to_contract_id': 36128, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 8, 'from_contract_id': 317328, 'to_contract_id': 340021, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 4: unequal four-piece split { 'earnings_transfer_id': 9, 'from_contract_id': 40473, 'to_contract_id': 339031, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.4', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 10, 'from_contract_id': 40473, 'to_contract_id': 42186, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 11, 'from_contract_id': 40473, 'to_contract_id': 323731, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 12, 'from_contract_id': 40473, 'to_contract_id': 317916, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.2', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 5: 5% of gross_revenue { 'earnings_transfer_id': 13, 'from_contract_id': 540170, 'to_contract_id': 504998, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'gross_revenue', 'negative': 0, 'active': 1, }, # Scenario 6: 5% of net_revenue { 'earnings_transfer_id': 14, 'from_contract_id': 505003, 'to_contract_id': 519892, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'net_revenue', 'negative': 0, 'active': 1, }, # Scenario 7: 5% of gross_revenue { 'earnings_transfer_id': 15, 'from_contract_id': 520183, 'to_contract_id': 505308, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'gross_revenue', 'negative': 0, 'active': 1, }, # Scenario 8: 100% of gross_revenue { 'earnings_transfer_id': 16, 'from_contract_id': 505325, 'to_contract_id': 506690, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'gross_revenue', 'negative': 0, 'active': 1, }, # Scenario 9: 5% of closing_balance { 'earnings_transfer_id': 17, 'from_contract_id': 520214, 'to_contract_id': 505329, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 10: 100% of gross_revenue { 'earnings_transfer_id': 18, 'from_contract_id': 520226, 'to_contract_id': 520228, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'gross_revenue', 'negative': 0, 'active': 1, }, # Scenario 11: 100% of net_revenue { 'earnings_transfer_id': 19, 'from_contract_id': 505355, 'to_contract_id': 505356, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'net_revenue', 'negative': 0, 'active': 1, }, # Scenario 12: 5% of net_revenue (unrecouped) { 'earnings_transfer_id': 20, 'from_contract_id': 520249, 'to_contract_id': 505364, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'net_revenue', 'negative': 0, 'active': 1, }, # Scenario 13: 5% of gross_revenue (unrecouped) { 'earnings_transfer_id': 21, 'from_contract_id': 505379, 'to_contract_id': 506588, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'gross_revenue', 'negative': 0, 'active': 1, }, # Scenario 14: INACTIVE — should be filtered out { 'earnings_transfer_id': 22, 'from_contract_id': 520281, 'to_contract_id': 521471, 'transfer_type': 'override', 'rate_type': 'percent', 'transfer_amount': '0.05', 'input': 'net_revenue', 'negative': 0, 'active': 0, }, # Scenario 15: flat_rate $1000 { 'earnings_transfer_id': 23, 'from_contract_id': 520296, 'to_contract_id': 505411, 'transfer_type': 'override', 'rate_type': 'flat_rate', 'transfer_amount': '1000', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 16: flat_rate $1000, negative=true { 'earnings_transfer_id': 24, 'from_contract_id': 520300, 'to_contract_id': 520301, 'transfer_type': 'override', 'rate_type': 'flat_rate', 'transfer_amount': '1000', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, # Scenario 17: 3 sublabels reclass 100% { 'earnings_transfer_id': 25, 'from_contract_id': 339283, 'to_contract_id': 339193, 'transfer_type': 'reclass', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 26, 'from_contract_id': 339688, 'to_contract_id': 339193, 'transfer_type': 'reclass', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 27, 'from_contract_id': 307600, 'to_contract_id': 339193, 'transfer_type': 'reclass', 'rate_type': 'percent', 'transfer_amount': '1.0', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 18: flat_rate $1000, negative=true { 'earnings_transfer_id': 28, 'from_contract_id': 129567, 'to_contract_id': 41443, 'transfer_type': 'override', 'rate_type': 'flat_rate', 'transfer_amount': '1000', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, # Scenario 19: four-piece split 25% each { 'earnings_transfer_id': 29, 'from_contract_id': 520121, 'to_contract_id': 520122, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 30, 'from_contract_id': 520121, 'to_contract_id': 505245, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 31, 'from_contract_id': 520121, 'to_contract_id': 520124, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 32, 'from_contract_id': 520121, 'to_contract_id': 520125, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 21: four-piece split 25% each with FT { 'earnings_transfer_id': 33, 'from_contract_id': 40539, 'to_contract_id': 314051, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 34, 'from_contract_id': 40539, 'to_contract_id': 38312, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 35, 'from_contract_id': 40539, 'to_contract_id': 326132, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 36, 'from_contract_id': 40539, 'to_contract_id': 317938, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, # Scenario 22: four-piece + flat_rate manager fee, negative=true { 'earnings_transfer_id': 37, 'from_contract_id': 505669, 'to_contract_id': 520564, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, { 'earnings_transfer_id': 38, 'from_contract_id': 505669, 'to_contract_id': 505670, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, { 'earnings_transfer_id': 39, 'from_contract_id': 505669, 'to_contract_id': 505671, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, { 'earnings_transfer_id': 40, 'from_contract_id': 505669, 'to_contract_id': 520566, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, { 'earnings_transfer_id': 41, 'from_contract_id': 505669, 'to_contract_id': 520567, 'transfer_type': 'transfer', 'rate_type': 'flat_rate', 'transfer_amount': '100', 'input': 'closing_balance', 'negative': 1, 'active': 1, }, # Scenario 23: four-piece + flat_rate manager fee, negative=false { 'earnings_transfer_id': 42, 'from_contract_id': 521496, 'to_contract_id': 505676, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 43, 'from_contract_id': 521496, 'to_contract_id': 506087, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 44, 'from_contract_id': 521496, 'to_contract_id': 520571, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 45, 'from_contract_id': 521496, 'to_contract_id': 520574, 'transfer_type': 'transfer', 'rate_type': 'percent', 'transfer_amount': '0.25', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, { 'earnings_transfer_id': 46, 'from_contract_id': 521496, 'to_contract_id': 520575, 'transfer_type': 'transfer', 'rate_type': 'flat_rate', 'transfer_amount': '100', 'input': 'closing_balance', 'negative': 0, 'active': 1, }, ] ACTIVE_RECORDS = [r for r in OWS_API_RECORDS if r['active'] == 1] # -- Test --------------------------------------------------------------------- class TestLocalE2E: """Run the full handler pipeline with real Snowflake, write output XLSX to tmp dir.""" @patch('app.get_ows_client') def test_full_pipeline_writes_xlsx( self, mock_get_ows: MagicMock, ) -> None: """Execute handler end-to-end with real Snowflake, capture XLSX output to ./tmp.""" OUTPUT_DIR.mkdir(exist_ok=True) # -- Mock OWS client: GET /earnings-transfers/ returns our fixture records mock_ows = MagicMock() mock_response = MagicMock() mock_response.status_code = 200 mock_response.json.return_value = {'items': OWS_API_RECORDS} mock_ows.get.return_value = mock_response mock_get_ows.return_value = mock_ows # -- Mock S3: intercept upload_buffer and write to ./tmp instead captured: dict[str, bytes] = {} def fake_upload(bucket: str, key: str, data: bytes) -> None: captured[key] = data out = OUTPUT_DIR / os.path.basename(key) out.write_bytes(data) with patch('src.processor.get_s3_connector') as mock_s3: mock_s3_conn = MagicMock() mock_s3_conn.upload_buffer.side_effect = fake_upload mock_s3.return_value = mock_s3_conn from app import handler context = MagicMock() context.invoked_function_arn = ( 'arn:aws:lambda:us-east-1:123:function:local-e2e' ) result = handler({}, context) # -- Verify handler response assert result['status'] == 'OK', f'Handler failed: {result}' assert result['record_count'] == len(ACTIVE_RECORDS) # -- Verify XLSX was written to ./tmp assert len(captured) == 1 output_key = next(iter(captured)) output_file = OUTPUT_DIR / os.path.basename(output_key) assert output_file.exists() # -- Verify XLSX structure wb = load_workbook(output_file) assert 'Adjustments' in wb.sheetnames assert 'Summary' in wb.sheetnames # Summary sheet: one row per active record summary = wb['Summary'] summary_rows = list(summary.iter_rows(min_row=2, values_only=True)) assert len(summary_rows) == len(ACTIVE_RECORDS) # Print balances for inspection print(f'\n{"=" * 80}') print(f'Output written to: {output_file}') print(f'Active records: {len(ACTIVE_RECORDS)}') print(f'Summary rows: {len(summary_rows)}') adj = wb['Adjustments'] adj_rows = list(adj.iter_rows(min_row=2, values_only=True)) print(f'Adjustment rows: {len(adj_rows)}') if 'Errors' in wb.sheetnames: errors = wb['Errors'] error_rows = list(errors.iter_rows(min_row=2, values_only=True)) print(f'Error rows: {len(error_rows)}') for row in error_rows: print(f' {row}') print(f'{"=" * 80}')