# Publishing Sales Accounting Run

Python tools for the Sony Music Publishing quarterly sales accounting run.

This project automates the data preparation, reconciliation, and calculation
steps of the quarterly publishing royalty process. It runs interactively from
a terminal menu.

---

## Prerequisites

- **Python 3.10 or later**
- **Snowflake access** with role `ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE`
  and warehouse `PROD_ABACUS_WH`
- **Snowflake RSA key-pair authentication** configured for your user account.
  See the [Snowflake key-pair auth guide](https://docs.snowflake.com/en/user-guide/key-pair-auth).
- **Neo4j access** (VPN required for dev/qa/prod clusters)
- **Jira access** with an API key for `theorchard.atlassian.net`

---

## Setup

```bash
# 1. Clone the repo
git clone https://github.com/deltaex1/publishing-sales-accounting-run-v2
cd publishing-sales-accounting-run-v2

# 2. Install dependencies
pip install -r requirements.txt

# 3. Configure environment
cp .env.example .env
# Edit .env and fill in all values — see .env.example for descriptions

# 4. Install the pre-commit hook (strips data rows from XLSX before any commit)
pre-commit install
```

---

## Before Each Quarterly Run

Update these values in `config.py`:

| Variable | Description | Example |
|---|---|---|
| `PERIOD` | Snowflake period ID | `327` |
| `FILENAME` | Sony Publishing sales file name | `Orchard_Dec_2025.csv` |
| `FILESERIES` | Output file prefix (`{YYYY}{MON}_{YYYYMMDD}`) | `2025DEC_20260313` |
| `STANDARD_ROY_PCT` | Standard royalty percentage(s) | `[90.0]` |

---

## External Files to Download Before Running

These files are not committed to the repo. Download them fresh each quarter:

| File | Source | When Needed | Save As |
|---|---|---|---|
| Sony Publishing sales file | SMP SharePoint link (emailed each quarter) | Step 0 | Value of `FILENAME` in config |
| Publishing Admin Deal Tracker | [Google Sheets](https://docs.google.com/spreadsheets/d/1zSGK7pNypsQMfo-1qxHx8Gbs1_yJ6CCl) | Step 5 | `Publishing Admin Deal Tracker_Official.xlsx` |
| Payout Rates (exchange rates) | Abacus > Reports > Payout Rates (export as XLS) | Step 6 | `Payout Rates {Month} {Year} ({Period}).xls` |

The **Master Lookup file** (`Missing Pub Song Correction Master Lookup.xlsx`) persists
across quarters and accumulates song ID corrections. The source is:
[Google Sheets](https://docs.google.com/spreadsheets/d/1V2dSWIA0HvQ8-0nPtY2lK7YRa3CSKQGBF5MCAQeucsU).
Keep your local copy up to date with any additions made since the last run.

---

## Running

```bash
python pub.py
```

The menu displays the current config values on each loop so you can confirm
settings before running each step.

---

## Menu Reference

| # | Function | Workflow Step | Notes |
|---|---|---|---|
| 0 | Read sales file into memory | Load file | Auto-applies Excel font fix if needed |
| 1 | Royalty output | Get the numbers | Outputs XLSX + text report for Finance/Slack |
| 2 | Song ID reconciliation | Missing Song IDs | Re-run after each master lookup update |
| 3 | Update Master Lookup | Incorporate Aidan's corrections | Run after receiving corrected lite file back |
| 4 | Missing songwriter agreement check | Missing SWAs | Checks Snowflake then Neo4j; generates DBPR XML |
| 5 | Contract rate calculation | Compile rates | Requires James's file + fresh deal tracker |
| 6 | Payment summary calculation | Prep for payment approval | Requires approved contract rates + payout rates XLS |
| 7 | [FUTURE] Load sales to Snowflake | Calculate the Sales | Manual Snowflake steps — see workflow doc |
| 8 | Fix malformed Sony XLSX | Pre-step 0 | Only needed if step 0 errors on font issue |
| 9 | Clean up and archive files | End of run | Archives output files to `archive/{period}/` |
| 10 | Reload reconciled DataFrame | Resume session | Loads pickle from a completed step 2 |

---

## Workflow Coverage

The following steps from the full workflow are covered by this tool:

| Workflow Step | Covered | Notes |
|---|---|---|
| Get the numbers (royalties, non-standard %, negatives) | Yes | Step 1 |
| Missing Song IDs — generate file for Aidan | Yes | Step 2 |
| Update Master Lookup with Aidan's corrections | Yes | Step 3 |
| Re-reconcile after corrections | Yes | Step 2 (re-run) |
| Missing songwriter agreement check | Yes | Step 4 |
| Generate Neo4j DBPR XML changeset | Yes | Step 4 (auto-prompted) |
| Create Jira ticket for DBPR | Yes | Step 4 (auto-prompted) |
| Compile contract rates | Yes | Step 5 |
| Payment summary / run results | Yes | Step 6 |
| Fix malformed Sony XLSX | Yes | Step 8 / auto-retry in step 0 |
| Archive generated files | Yes | Step 9 |
| **Send numbers email to Finance** | No | Manual — recipients in `config.FINANCE_EMAIL_RECIPIENTS` |
| **Post checksum to Slack** | No | Manual — channel in `config.SLACK_CHECKSUM_CHANNEL` |
| **Send file to James Kass** | No | Manual — share the output from step 2 |
| **Load sales file to S3/Snowflake** | No | Manual Snowflake steps — see workflow doc |
| **Run Snowflake calculation queries** | No | Manual — see Snowflake "Publishing Run" folder |
| **Upload/download statement files (S3)** | No | Manual AWS CLI — see workflow doc |
| **Create manual adjustments** | No | Manual — download template from Abacus |

---

## Automation Opportunities (Future)

The following constants are already in `config.py` ready for automation when the time comes:

- `FINANCE_EMAIL_RECIPIENTS` — Finance/Publishing team email list (email step 1 output)
- `SLACK_CHECKSUM_CHANNEL` — Slack channel for the checksum notice
- `GDRIVE_REVENUE_FOLDER_ID` — Google Drive Revenue parent folder (auto-upload outputs)
- `MASTER_LOOKUP_GDRIVE_URL` / `DEAL_TRACKER_GDRIVE_URL` — Google Sheets source URLs (read live instead of downloading)

See `ABACUS_CURRENCY_QUERY` in `config.py` for the Snowflake query used to pull currency
codes live from Abacus (already implemented in step 6).

---

## Special Account Handling

Two accounts require manual rate determination before loading contract rates into Snowflake:

**Elysium Melodies Limited (account 793219)** — tiered base fee based on gross sales.
The tier schedule is in `config.ELYSIUM_RATE_TIERS`. Determine the gross total for this
account in James's file and set the appropriate rate in the contract rates sheet manually.

**Lovely Bastards (account 25223)** — 0% base fee for specific song IDs.
Song IDs listed in `config.LOVELY_BASTARDS_SONG_IDS`. Applied via manual SQL update in
Snowflake's `contract_transaction_publishing` table — see workflow doc for the query.

---

## Notes on Excluded Rows

If Matt or the Publishing team asks that rows be excluded from the run:

1. Run step 2 (reconciliation). The master lookup file controls exclusions —
   add rows with a blank `Correct` column to drop them from processing.
2. Those rows are automatically written to `{fileseries}-LinesDroppedFromProcessing.xlsx`
   for audit purposes.
3. They may be processed separately in a later run.

---

## Repo Structure

```
pub.py                          Entry point — interactive menu
config.py                       All per-quarter + org constants (update here each run)

pubsalesacc/
  pipeline.py                   Core business logic — all workflow step functions
  connectors/
    snowflake.py                Snowflake connector (SQLAlchemy, lazy private key load)
    neo4j.py                    Neo4j connector (lazy driver)
    jira.py                     Jira connector (lazy client)
  utils/
    io.py                       DataFrame Excel/CSV output, SQL file reader
    text.py                     Textobj / Htmlobj accumulator classes
    dates.py                    Date and reporting month helpers
    excel_fix.py                Sony XLSX font-family XML patcher
    misc.py                     currency(), ordinal(), error_prompt()

sql/
  accountingrun.sql             All Snowflake queries (6 statements)
  SWAlookupV3.cypher            Neo4j SWA existence query
  n4j-dbpr-template.txt         Liquibase changeset template for Neo4j sync

hooks/
  pre_commit_xlsx.py            Pre-commit hook: strips data rows from protected XLSX files

Missing Pub Song Correction Master Lookup.xlsx   Blank template (headers only)

archive/
  publishing-sales-accounting-run.py   Original v1 script (reference only)
```