# Mock contract balances from `VW_ABACUS_BALANCES_LOOKER_V2`

## Overview

One-off operational script that generates SQL to seed mock contract balance data into the royalty accounting database, mirroring the structure of `ROYALTY_ACCOUNTING_REPORTING.QA.VW_ABACUS_BALANCES_LOOKER_V2`. Used to set up reproducible Abacus test fixtures in QA.

The script reads a CSV of mock balances, and renders a SQL script from `mock_contract_balances/template/mock_transfer_of_earnings_balance.sql.j2`. The SQL is idempotent: every insert is tagged `created_by = 'abacus_reset_balance_transfer_of_earnings_script'`, and the cleanup section deletes prior rows with that tag before re-inserting, so it's safe to rerun.

Source ticket: ACC-10299.

## Running the script

1. Copy the `.env.shadow` template to `.env` and fill in the values (`ENVIRONMENT`, `CSV_FILE_PATH`, and Orchard auth headers: `AUTH_JWT`, `ORCHARD_IDENTITY_ID`, `ORCHARD_PROFILE_TYPE`, `ORCHARD_PROFILE_ID`).
2. Install dependencies: `make env` (or `make dev_env` for ruff).
3. Run the script: `make run`. Generated SQL lands in `outputs/`.
4. Execute the generated SQL against the target environment manually.

## Adding a mock contract balance

For now, adding a contract is a manual CSV edit:

1. Open `mock_data/mock_balance_data.csv`.
2. Append a row with the columns:
   `ACCOUNT_ID,CONTRACT_ID,OPENING_BALANCE,GROSS_REVENUE,NET_REVENUE,CLOSING_BALANCE,CLOSING_BALANCE_WITH_FT`
3. Make sure `CLOSING_BALANCE = OPENING_BALANCE + NET_REVENUE` for the rows you want clean (no flowthrough); divergence between `CLOSING_BALANCE` and `CLOSING_BALANCE_WITH_FT` is interpreted as a flowthrough adjustment by the SQL template.
4. Rerun `make run` to regenerate the SQL, then execute it.

## Verification

After running the generated SQL, verify the seeded balances in Snowflake:

```sql
SELECT CONTRACT_ID,
       STATEMENT_PERIOD_ID,
       OPENING_BAL,
       GROSS_REVENUE,
       NET_REVENUE,
       EST_CLOSING_BAL,
       EST_CLOSING_BAL_W_APPLIED_FT
FROM ROYALTY_ACCOUNTING_REPORTING.QA.VW_ABACUS_BALANCES_LOOKER_V2;
```

Filter by the `CONTRACT_ID`s you added to confirm the values match the CSV.

## Future plans

- **Generify the mock.** Today the template targets mocking only some columns from the `VW_ABACUS_BALANCES_LOOKER_V2` view. In future we could extend that list of columns.
- **Support multiple input files.** Add support for different CSV files, enabling teams to seed different contract balances independantly.
- **Automate SQL execution.** Execute the generated SQL directly in the database.