"""NetSuite GL ↔ Helix bottom-up reconciliation + close cockpit + the account → cost_type heuristic. Pins the reconciliation LOGIC: per-cost-type internal-vs-GL diff with tolerant matching, the unclassified-GL exception list, the close-cockpit go/no-go checklist, and the NULL-only account classification heuristic (never overrides a human/`__none__` mapping). """ from __future__ import annotations from decimal import Decimal import pytest from app.core.coordinators import finance_netsuite_coordinator as ns from app.domains.finance import read_service from app.domains.finance.models import ( FinanceGLActual, FinanceNetSuiteAccount, FinanceNetSuiteAccountingPeriod, FinanceSpendFact, ) PERIOD = "2026-05" # ─── Account → cost_type heuristic (pure) ─────────────────────────── def test_infer_cost_type_streams(): assert ns.infer_cost_type("Expense", "AWS Hosting Fees") == "cloud" assert ns.infer_cost_type("Expense", "GitHub Enterprise Subscription") == "software" assert ns.infer_cost_type("Expense", "百炼 API 用量") == "ai" assert ns.infer_cost_type("Expense", "Laptop Depreciation") == "hardware" # A cost account with no stream keyword → generic procurement opex. assert ns.infer_cost_type("Expense", "Office Snacks") == "procurement" assert ns.infer_cost_type("COGS", "Hosting - Production") == "cloud" def test_infer_cost_type_non_cost_accounts_unclassified(): # NetSuite accttype CODE forms (what SuiteQL actually returns) — every # balance-sheet / income code must stay unclassified (None). assert ns.infer_cost_type("Income", "Subscription Revenue") is None assert ns.infer_cost_type("OthIncome", "FX Gain") is None assert ns.infer_cost_type("Bank", "Operating Cash") is None assert ns.infer_cost_type("AcctRec", "AR Trade") is None assert ns.infer_cost_type("AcctPay", "Trade Payables") is None assert ns.infer_cost_type("FixedAsset", "Laptops at cost") is None assert ns.infer_cost_type("OthCurrAsset", "Prepaid") is None assert ns.infer_cost_type("OthCurrLiab", "Accrued") is None assert ns.infer_cost_type("OthAsset", "Deposit") is None assert ns.infer_cost_type("LongTermLiab", "Loan") is None assert ns.infer_cost_type("Equity", "Retained Earnings") is None # DeferExpense is a prepaid/deferred ASSET, not a current cost — it must # NOT be auto-classified to 'procurement' (the old bare-substring bug). assert ns.infer_cost_type("DeferExpense", "Prepaid Insurance") is None # OthExpense IS a P&L cost account → classified. assert ns.infer_cost_type("OthExpense", "Bank Service Charges") == "procurement" # ─── gl_reconcile + close_cockpit (DB-backed) ─────────────────────── def _fact(cost_type, basis, usd, **kw): return FinanceSpendFact( fact_key=f"k|{cost_type}|{basis}|{usd}|{kw.get('vendor','')}", period=PERIOD, cost_type=cost_type, basis=basis, amount_native=Decimal(str(usd)), currency_native="USD", amount_usd=Decimal(str(usd)), fx_basis="native_usd", allocation_source="none", source_domain=cost_type, rollup_fingerprint="test", is_provisional=False, **kw, ) def _acct(account_id, number, name, atype, mapped): return FinanceNetSuiteAccount( account_id=account_id, account_number=number, account_name=name, account_type=atype, mapped_cost_type=mapped, ) def _gl(account_id, number, name, atype, usd): return FinanceGLActual( gl_key=ns.gl_key(PERIOD, "10", account_id, ""), source="netsuite", period=PERIOD, subsidiary_id="10", account_id=account_id, account_number=number, account_name=name, account_type=atype, amount_native=Decimal(str(usd)), currency="USD", amount_usd=Decimal(str(usd)), fx_basis="month_frozen", is_closed=True, ) @pytest.mark.asyncio async def test_gl_reconcile_match_diff_and_unmapped(db_session): db = db_session # cloud: Helix 1000 (consumption) vs GL 950 → within tol(1000) → match db.add(_fact("cloud", "consumption", 1000)) db.add(_acct("a1", "6000", "AWS Hosting", "Expense", "cloud")) db.add(_gl("a1", "6000", "AWS Hosting", "Expense", 950)) # software: Helix 5000 vs GL 100 → diff 4900 > tol → mismatch db.add(_fact("software", "consumption", 5000)) db.add(_acct("a2", "6100", "SaaS", "Expense", "software")) db.add(_gl("a2", "6100", "SaaS", "Expense", 100)) # A material expense booked to an UNCLASSIFIED account (NULL) → exception db.add(_acct("a3", "6200", "Consulting", "Expense", None)) db.add(_gl("a3", "6200", "Consulting", "Expense", 8000)) await db.flush() res = await read_service.gl_reconcile(db, PERIOD) legs = {leg["cost_type"]: leg for leg in res["legs"]} assert legs["cloud"]["match"] is True assert legs["cloud"]["diff_usd"] == "50.00" assert legs["software"]["match"] is False assert legs["software"]["diff_usd"] == "4900.00" assert res["all_match"] is False # Unmapped exception surfaced with its GL amount. unmapped = {u["account_number"]: u for u in res["unmapped_accounts"]} assert "6200" in unmapped assert unmapped["6200"]["amount_usd"] == "8000.00" @pytest.mark.asyncio async def test_gl_reconcile_excludes_none_sentinel(db_session): db = db_session # An account explicitly excluded (__none__) must not contribute to any # leg nor appear as an unmapped exception. db.add(_acct("x1", "4000", "Revenue", "Income", "__none__")) db.add(_gl("x1", "4000", "Revenue", "Income", -99999)) await db.flush() res = await read_service.gl_reconcile(db, PERIOD) assert all(leg["cost_type"] != "__none__" for leg in res["legs"]) assert all(u["account_number"] != "4000" for u in res["unmapped_accounts"]) @pytest.mark.asyncio async def test_gl_reconcile_excludes_balance_sheet_accounts_from_unmapped(db_session): # The GL pull lands ALL posting accounts (the SuiteQL only filters # posting='T' + period), so balance-sheet / income accounts arrive with # mapped_cost_type=NULL and often huge amounts (a Bank account at # -359,589.75). They are LEGITIMATELY unmapped — not cost-classification # work items — and must NOT pollute the unclassified worklist / # unmapped_total_usd (which would permanently jam close readiness). Only # expense-shaped unclassified accounts are exceptions. db = db_session # CODE-form accttype (what NetSuite SuiteQL actually returns) — the # display-string set used to miss AcctPay/FixedAsset/OthCurrAsset/... db.add(_acct("b1", "1001", "Operating Cash", "Bank", None)) db.add(_gl("b1", "1001", "Operating Cash", "Bank", -359589.75)) db.add(_acct("b2", "2001", "Trade Payables", "AcctPay", None)) db.add(_gl("b2", "2001", "Trade Payables", "AcctPay", 120000)) db.add(_acct("b3", "1500", "Laptops at cost", "FixedAsset", None)) db.add(_gl("b3", "1500", "Laptops at cost", "FixedAsset", 88000)) db.add(_acct("b4", "1200", "Prepaid Insurance", "OthCurrAsset", None)) db.add(_gl("b4", "1200", "Prepaid Insurance", "OthCurrAsset", 50000)) db.add(_acct("b5", "1600", "Security Deposit", "OthAsset", None)) db.add(_gl("b5", "1600", "Security Deposit", "OthAsset", 30000)) # A genuine expense account with NULL mapping IS still surfaced. db.add(_acct("e1", "6300", "Legal Fees", "Expense", None)) db.add(_gl("e1", "6300", "Legal Fees", "Expense", 7000)) await db.flush() res = await read_service.gl_reconcile(db, PERIOD) nums = {u["account_number"] for u in res["unmapped_accounts"]} assert nums == {"6300"} # only the expense account assert res["unmapped_total_usd"] == "7000.00" # BS amounts excluded @pytest.mark.asyncio async def test_close_cockpit_checklist_shape(db_session): db = db_session db.add(_fact("cloud", "consumption", 1000)) db.add(FinanceNetSuiteAccountingPeriod( period_id="11", period_name="May 2026", start_date="2026-05-01", period=PERIOD, closed=False)) await db.flush() res = await read_service.close_cockpit(db, PERIOD) keys = {c["key"] for c in res["checklist"]} assert keys == { "data_complete", "gl_synced", "ns_period_closed", "recon_clean", "no_open_restatements", } assert res["close_state"] == "open" # No GL pulled + NS period open → not ready to close. assert res["ready_to_close"] is False assert res["ns_period_closed"] is False