import importlib.util import pathlib import unittest from datetime import date import pandas as pd MODULE_PATH = pathlib.Path(__file__).with_name("monthly_cloud_billing_revrec.py") SPEC = importlib.util.spec_from_file_location("monthly_cloud_billing_revrec", MODULE_PATH) monthly_cloud_billing_revrec = importlib.util.module_from_spec(SPEC) SPEC.loader.exec_module(monthly_cloud_billing_revrec) class BillingInputValidationTest(unittest.TestCase): def test_rejects_two_cent_reconciliation_mismatch_with_row_identity(self): billing = { "tenant_id": "1372813089209260919", "billing_month": "2026-08", "total_amount": 230_360.20, } for category in monthly_cloud_billing_revrec.REVENUE_CATEGORIES: billing[f"raw_{category}_amount"] = 0.0 billing["raw_starter_amount"] = 208_252.30 billing["raw_dedicated_amount"] = 22_107.92 with self.assertRaisesRegex( ValueError, r"1372813089209260919.*2026-08.*-0\.02", ): monthly_cloud_billing_revrec.validate_billing_reconciliation( pd.DataFrame([billing]) ) def test_explicit_august_predicted_preview_is_isolated(self): billing_date, revenue_mode, preview_run_id, order_snapshot_date = ( monthly_cloud_billing_revrec.resolve_run_parameters( today=date(2026, 9, 4), environ={ 'MONTHLY_CLOUD_BILLING_BILL_MONTH': '2026-08', 'MONTHLY_CLOUD_BILLING_REVENUE_MODE': 'predicted', 'MONTHLY_CLOUD_BILLING_PREVIEW_RUN_ID': 'manus_fix_20260904', 'MONTHLY_CLOUD_BILLING_ORDER_SNAPSHOT_DATE': '2026-09-02', }, ) ) targets = monthly_cloud_billing_revrec.build_output_table_targets( billing_date, revenue_mode, preview_run_id, ) self.assertEqual(billing_date, date(2026, 8, 1)) self.assertEqual(revenue_mode, 'predicted') self.assertEqual(order_snapshot_date, date(2026, 9, 2)) self.assertEqual( targets, [ ( 'collection', 'gcp-digital-bi-dev.sandbox_dwh_ywang.' 'monthly_revrec_collections_2026_08_predicted_preview_manus_fix_20260904', ), ( 'order', 'gcp-digital-bi-dev.sandbox_dwh_ywang.' 'monthly_revrec_orders_2026_08_predicted_preview_manus_fix_20260904', ), ], ) def test_default_output_targets_preserve_existing_publish_contract(self): targets = monthly_cloud_billing_revrec.build_output_table_targets( date(2026, 8, 1), 'recognised', ) self.assertEqual(len(targets), 4) self.assertIn( ( 'collection', 'gcp-digital-bi-dev.sandbox_dwh_ywang.monthly_revrec_collections', ), targets, ) self.assertIn( ( 'order', 'gcp-digital-bi-dev.sandbox_dwh_ywang.' 'monthly_revrec_orders_2026_08_recognised', ), targets, ) class ActualClosingShortfallTest(unittest.TestCase): def _actual_billing(self, total_amount, tenant_id="tenant-1"): categories = monthly_cloud_billing_revrec.REVENUE_CATEGORIES billing = { "tenant_id": tenant_id, "accumulate_revenue_to_org_tenant_id": tenant_id, "billing_month": "2026-06", "billing_start_date": "6/1/2026", "billing_end_date": "6/30/2026", "total_amount": total_amount, "account_id": f"account-{tenant_id}", "customer_surrogate_key": f"customer-{tenant_id}", "account_number_for_netsuite": f"IBG-{tenant_id}", "customer_name": "Example customer", "region": "Japan", "payment_type": "marketplace", "wire_transfer_amount": 0.0, "marketplace_amount": total_amount, "stripe_amount": None, "stripe_tax_rate": None, "stripe_amount_including_tax": None, "stripe_invoice_number": None, "stripe_invoice": None, "stripe_invoice_paid": None, "stripe_invoice_finalized_date": None, "stripe_invoice_paid_date": None, "invoice_amount": total_amount, "is_salesforce_customer": True, "actual_revenue_recognition_date": "2026-07-28", } for category in categories: billing[f"raw_{category}_amount"] = 0.0 for credit_type in monthly_cloud_billing_revrec.CREDIT_TYPES: billing[f"raw_{credit_type}_credit_amount_{category}"] = 0.0 billing[f"segment_a_start_date"] = None billing[f"segment_a_end_date"] = None billing[f"segment_a_amount"] = None billing[f"segment_b_start_date"] = None billing[f"segment_b_end_date"] = None billing[f"segment_b_amount"] = None billing[f"segment_c_start_date"] = None billing[f"segment_c_end_date"] = None billing[f"segment_c_amount"] = None billing["raw_dedicated_amount"] = total_amount return billing def _estimate_billing(self, total_amount, tenant_id="tenant-1"): billing = self._actual_billing(total_amount, tenant_id) billing.update({ "billing_month": "2026-07", "billing_start_date": "7/1/2026", "billing_end_date": "7/31/2026", "actual_revenue_recognition_date": None, }) return billing def _commitment_order(self, order_number, tenant_id, net_fees, prior_actual): return { "order_name": order_number, "accumulate_revenue_to_org_tenant_id": tenant_id, "order_status": "Activated", "order_product_family": "TiDB Cloud", "cloud_order_type": "Commitment Plan", "order_service_start_date": pd.Timestamp("2025-01-01").date(), "order_service_end_date": pd.Timestamp("2027-12-31").date(), "order_is_terminated": False, "order_termination_date": None, "real_order_termination_date": None, "reason": "", "order_net_fees": net_fees, "accumulated_revenue_before_adjust": net_fees, "accumulated_revenue": net_fees, "accumulated_credit_amount": 0.0, "accumulated_actual_revenue_before_billing": prior_actual, "accumulated_actual_adjusted_revenue_before_billing": prior_actual, "accumulated_actual_credit_amount_before_billing": 0.0, } def test_uses_actual_history_and_current_billing_to_calculate_shortfall(self): revenue_amount, shortfall_amount = ( monthly_cloud_billing_revrec.calculate_actual_closing_revenue( order_total_fees=40_000.00, accumulated_actual_revenue_before_billing=27_330.29, billing_allocation_amount=2_966.00, ) ) self.assertAlmostEqual(revenue_amount, 12_669.71, places=2) self.assertAlmostEqual(shortfall_amount, 9_703.71, places=2) def test_order_query_supplies_prior_actual_balances_for_cp_allocation(self): order_sql = monthly_cloud_billing_revrec.build_order_sql( "`project.dataset.salesforce_order_daily_view`", "2026-08-04", "2026-07", ) self.assertIn("accumulated_actual_revenue_before_billing", order_sql) self.assertIn("accumulated_actual_adjusted_revenue_before_billing", order_sql) self.assertIn("accumulated_actual_credit_amount_before_billing", order_sql) self.assertIn("< DATE '2026-07-01'", order_sql) self.assertIn("revenue_recognition_type", order_sql) self.assertRegex( order_sql, r"SUM\(COALESCE\(\s*" r"SAFE_CAST\(NULLIF\(revrec\.revenue_amount, ''\) AS FLOAT64\)", ) def test_actual_run_replaces_closing_estimate_with_actual_cp_revenue(self): billing = self._actual_billing(2_966.00) orders = pd.DataFrame([ { "order_name": "JP-OF-25060301", "accumulate_revenue_to_org_tenant_id": "tenant-1", "order_status": "Activated", "order_product_family": "TiDB Cloud", "cloud_order_type": "Commitment Plan", "order_service_start_date": pd.Timestamp("2025-07-01").date(), "order_service_end_date": pd.Timestamp("2026-06-30").date(), "order_is_terminated": False, "order_termination_date": None, "real_order_termination_date": None, "reason": "", "order_net_fees": 40_000.00, # This is contaminated by the 6/30 estimate and must not make # the actual closing run skip the order. "accumulated_revenue_before_adjust": 40_000.00, "accumulated_revenue": 40_000.00, "accumulated_credit_amount": 0.0, "accumulated_actual_revenue_before_billing": 27_330.29, } ]) _, actual_orders = monthly_cloud_billing_revrec.process( orders, pd.DataFrame([billing]), is_recognised_revenue=True, ) self.assertEqual(len(actual_orders), 1) row = actual_orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["order_number"], "JP-OF-25060301") self.assertAlmostEqual(row["revenue_amount"], 12_669.71, places=2) self.assertAlmostEqual(row["end_of_contract_shortfall_amount"], 9_703.71, places=2) def test_actual_run_uses_actual_history_for_nonclosing_cp_before_creating_on_demand(self): billing = self._actual_billing(113_547.18) orders = pd.DataFrame([ { "order_name": "JP-OF-25030306", "accumulate_revenue_to_org_tenant_id": "tenant-1", "order_status": "Activated", "order_product_family": "TiDB Cloud", "cloud_order_type": "Commitment Plan", "order_service_start_date": pd.Timestamp("2025-04-01").date(), "order_service_end_date": pd.Timestamp("2028-03-31").date(), "order_is_terminated": False, "order_termination_date": None, "real_order_termination_date": None, "reason": "", "order_net_fees": 2_400_000.00, # The daily view contains both estimate and actual entries. "accumulated_revenue_before_adjust": 2_633_694.97, "accumulated_revenue": 2_633_694.97, "accumulated_credit_amount": 0.0, "accumulated_actual_revenue_before_billing": 2_088_515.15, } ]) _, actual_orders = monthly_cloud_billing_revrec.process( orders, pd.DataFrame([billing]), is_recognised_revenue=True, ) self.assertEqual(len(actual_orders), 1) row = actual_orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["order_number"], "JP-OF-25030306") self.assertAlmostEqual(row["revenue_amount"], 113_547.18, places=2) def test_cygames_actual_remains_entirely_on_cp(self): """Regression: a mixed estimate/actual snapshot must not create $36,128.25 on-demand.""" tenant_id = "1372813089191061283" billing = self._actual_billing(149_675.43, tenant_id) order = self._commitment_order( "JP-OF-25030306", tenant_id, net_fees=2_400_000.00, prior_actual=2_088_515.15, ) # The old allocator saw this mixed daily-view balance as fully consumed. order.update({ "accumulated_revenue_before_adjust": 2_633_694.97, "accumulated_revenue": 2_633_694.97, }) _, orders = monthly_cloud_billing_revrec.process( pd.DataFrame([order]), pd.DataFrame([billing]), is_recognised_revenue=True, ) self.assertEqual(len(orders), 1) row = orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["order_number"], "JP-OF-25030306") self.assertAlmostEqual(row["revenue_amount"], 149_675.43, places=2) def test_cygames_august_estimate_stays_entirely_on_demand_after_raw_cp_cap(self): tenant_id = "1372813089191061283" billing = self._estimate_billing(188_084.456, tenant_id) billing.update({ "billing_month": "2026-08", "billing_start_date": "8/1/2026", "billing_end_date": "8/31/2026", }) order = self._commitment_order( "JP-OF-25030306", tenant_id, net_fees=2_400_000.00, prior_actual=2_400_627.25, ) order.update({ "accumulated_actual_adjusted_revenue_before_billing": 2_400_000.00, "accumulated_actual_credit_amount_before_billing": 39_998.22, }) _, orders = monthly_cloud_billing_revrec.process( pd.DataFrame([order]), pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(orders), 1) row = orders.iloc[0] self.assertTrue(row["is_new_order"]) self.assertEqual( row["order_number"], "2026_08__ON_DEMAND__IBG_1372813089191061283", ) self.assertAlmostEqual(row["revenue_amount"], 188_084.456, places=3) self.assertAlmostEqual(row["adjusted_revenue_amount"], 188_084.456, places=3) def test_estimate_run_uses_prior_actual_history_for_cp_allocation(self): billing = self._estimate_billing(25_610.38) orders = pd.DataFrame([ { "order_name": "JP-OF-26021701", "accumulate_revenue_to_org_tenant_id": "tenant-1", "order_status": "Activated", "order_product_family": "TiDB Cloud", "cloud_order_type": "Commitment Plan", "order_service_start_date": pd.Timestamp("2026-04-01").date(), "order_service_end_date": pd.Timestamp("2027-03-31").date(), "order_is_terminated": False, "order_termination_date": None, "real_order_termination_date": None, "reason": "", "order_net_fees": 140_000.00, # The daily view has already counted April-July estimate rows # and actual rows, making this CP falsely appear fully consumed. "accumulated_revenue_before_adjust": 140_000.00, "accumulated_revenue": 140_000.00, "accumulated_credit_amount": 0.0, "accumulated_actual_revenue_before_billing": 78_362.00, "accumulated_actual_adjusted_revenue_before_billing": 78_362.00, "accumulated_actual_credit_amount_before_billing": 0.0, } ]) _, estimate_orders = monthly_cloud_billing_revrec.process( orders, pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(estimate_orders), 1) row = estimate_orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["order_number"], "JP-OF-26021701") self.assertAlmostEqual(row["revenue_amount"], 25_610.38, places=2) def test_existing_cp_uses_matched_crm_account_metadata(self): tenant_id = "1372813089209327004" billing = self._estimate_billing(687.46, tenant_id) billing.update({ "account_number_for_netsuite": tenant_id, "customer_name": "wow's Org", "is_salesforce_customer": False, }) order = self._commitment_order( "SG-OF-26052001", tenant_id, net_fees=60_000.00, prior_actual=0.00, ) order.update({ "account_number": "IBG-B-20250915-003174", "account_name": "SIRAYA Technologies Pte. Ltd.", }) _, estimate_orders = monthly_cloud_billing_revrec.process( pd.DataFrame([order]), pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(estimate_orders), 1) row = estimate_orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["order_number"], "SG-OF-26052001") self.assertEqual(row["account_id"], "IBG-B-20250915-003174") self.assertEqual(row["account_name"], "SIRAYA Technologies Pte. Ltd.") def test_generated_on_demand_keeps_billing_org_account_metadata(self): tenant_id = "1372813089209327004" billing = self._estimate_billing(687.46, tenant_id) billing.update({ "account_number_for_netsuite": tenant_id, "customer_name": "wow's Org", "is_salesforce_customer": False, }) consumed_order = self._commitment_order( "SG-OF-26052001", tenant_id, net_fees=60_000.00, prior_actual=60_000.00, ) consumed_order.update({ "account_number": "IBG-B-20250915-003174", "account_name": "SIRAYA Technologies Pte. Ltd.", }) _, estimate_orders = monthly_cloud_billing_revrec.process( pd.DataFrame([consumed_order]), pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(estimate_orders), 1) row = estimate_orders.iloc[0] self.assertTrue(row["is_new_order"]) self.assertEqual(row["account_id"], tenant_id) self.assertEqual(row["account_name"], "wow's Org") def test_july_classmethod_uses_approved_draft_cp_special_case(self): tenant_id = "1372813089209222473" billing = self._estimate_billing(38_351.42, tenant_id) billing.update({ "account_number_for_netsuite": "IBG-B-20230907-001835", "customer_name": "Classmethod, Inc.", }) draft_cp = self._commitment_order( 'JP-OF-25021801', "", net_fees=1_300_000.00, prior_actual=0.00, ) draft_cp.update({ "order_id": "801RC00000OFxDIYA1", "order_status": "Draft", "order_service_start_date": pd.Timestamp("2025-04-01").date(), "order_service_end_date": pd.Timestamp("2026-03-31").date(), "account_number": "IBG-B-20230907-001835", "account_name": "Classmethod, Inc.", }) _, estimate_orders = monthly_cloud_billing_revrec.process( pd.DataFrame([draft_cp]), pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(estimate_orders), 1) row = estimate_orders.iloc[0] self.assertFalse(row["is_new_order"]) self.assertEqual(row["tenant_id"], tenant_id) self.assertEqual(row["order_number"], "JP-OF-25021801") self.assertAlmostEqual(row["revenue_amount"], 38_351.42, places=2) def test_classmethod_special_case_does_not_enable_other_drafts_or_months(self): tenant_id = "1372813089209222473" draft_cp = self._commitment_order( "JP-OF-25021801", "", net_fees=1_300_000.00, prior_actual=0.00, ) draft_cp.update({ "order_id": "801RC00000OFxDIYA1", "order_status": "Draft", "order_service_start_date": pd.Timestamp("2025-04-01").date(), "order_service_end_date": pd.Timestamp("2026-03-31").date(), }) other_draft = dict(draft_cp) other_draft["order_id"] = "801RC00000OTHER001" other_draft["order_name"] = "JP-OF-OTHER" august_billing = self._estimate_billing(100.00, tenant_id) august_billing.update({ "billing_month": "2026-08", "billing_start_date": "8/1/2026", "billing_end_date": "8/31/2026", }) for billing, order in ( (self._estimate_billing(100.00, tenant_id), other_draft), (august_billing, draft_cp), ): with self.subTest(bill_month=billing["billing_month"], order_id=order["order_id"]): _, estimate_orders = monthly_cloud_billing_revrec.process( pd.DataFrame([order]), pd.DataFrame([billing]), is_recognised_revenue=False, ) self.assertEqual(len(estimate_orders), 1) self.assertTrue(estimate_orders.iloc[0]["is_new_order"]) def test_estimate_run_splits_only_true_cp_excess_to_on_demand(self): billing = self._estimate_billing(384_863.15) orders = pd.DataFrame([ { "order_name": "JP-OF-25030306", "accumulate_revenue_to_org_tenant_id": "tenant-1", "order_status": "Activated", "order_product_family": "TiDB Cloud", "cloud_order_type": "Commitment Plan", "order_service_start_date": pd.Timestamp("2025-04-01").date(), "order_service_end_date": pd.Timestamp("2028-03-31").date(), "order_is_terminated": False, "order_termination_date": None, "real_order_termination_date": None, "reason": "", "order_net_fees": 2_400_000.00, "accumulated_revenue_before_adjust": 2_400_000.00, "accumulated_revenue": 2_400_000.00, "accumulated_credit_amount": 0.0, "accumulated_actual_revenue_before_billing": 2_124_016.15, "accumulated_actual_adjusted_revenue_before_billing": 2_124_016.15, "accumulated_actual_credit_amount_before_billing": 0.0, } ]) _, estimate_orders = monthly_cloud_billing_revrec.process( orders, pd.DataFrame([billing]), is_recognised_revenue=False, ) is_new_order = estimate_orders["is_new_order"].map( lambda value: bool(value) if pd.notna(value) else False ) cp_rows = estimate_orders[~is_new_order] on_demand_rows = estimate_orders[is_new_order] self.assertEqual(len(cp_rows), 1) self.assertEqual(len(on_demand_rows), 1) self.assertAlmostEqual(cp_rows.iloc[0]["revenue_amount"], 275_983.85, places=2) self.assertAlmostEqual(on_demand_rows.iloc[0]["revenue_amount"], 108_879.30, places=2) def test_estimate_run_reallocates_affected_orgs_without_erasing_true_excess(self): cases = [ ("1372813089201471287", "JP-OF-26020201", 800_000.00, 287_309.43, 557_728.82), ("1372813089191061283", "JP-OF-25030306", 2_400_000.00, 2_124_016.15, 384_863.15), ("1372813089209031895", "JP-OF-26030502", 250_000.00, 180_803.26, 61_495.00), ("1372813089209248230", "CN-OF-26032301", 200_000.00, 139_894.52, 54_274.51), ("1372813089209001464", "JP-OF-26031901", 350_000.00, 189_038.86, 70_398.54), ("1372813089209212394", "JP-OF-25121001", 150_000.00, 89_554.70, 34_815.68), ("1372813089208611456", "JP-OF-26032701", 300_000.00, 157_188.96, 55_386.21), ("1372813089209218394", "JP-OF-26021701", 140_000.00, 78_362.00, 25_610.38), ("1372813089209031615", "SG-OF-25091501", 150_000.00, 126_111.79, 10_283.20), ("1372813089190911285", "JP-OF-25121101", 160_000.00, 113_135.42, 19_419.72), ("1372813089200631282", "ID-OF-25102001", 75_000.00, 58_162.00, 7_860.94), ("1372813089209239871", "JP-OF-25100201", 25_000.00, 24_423.84, 5_212.47), ("1372813089209214865", "SG-OF-26031901", 350_000.00, 189_447.00, 43_215.18), ] orders = pd.DataFrame([ self._commitment_order(order_number, tenant_id, net_fees, prior_actual) for tenant_id, order_number, net_fees, prior_actual, _ in cases ]) billings = pd.DataFrame([ self._estimate_billing(billing_amount, tenant_id) for tenant_id, _, _, _, billing_amount in cases ]) _, estimate_orders = monthly_cloud_billing_revrec.process( orders, billings, is_recognised_revenue=False, ) is_new_order = estimate_orders["is_new_order"].map( lambda value: bool(value) if pd.notna(value) else False ) cp_rows = estimate_orders[~is_new_order] on_demand_rows = estimate_orders[is_new_order] self.assertEqual(len(cp_rows), 13) self.assertEqual(len(on_demand_rows), 3) self.assertAlmostEqual(cp_rows["revenue_amount"].sum(), 1_172_009.94, places=2) self.assertAlmostEqual(on_demand_rows["revenue_amount"].sum(), 158_553.86, places=2) if __name__ == "__main__": unittest.main()