# Automation Improvements Plan
# publishing-sales-accounting-run-v2
# Tabled for future implementation — do not action without reviewing current state first.
# All constants referenced below are already staged in config.py ready for use.

---

## 1. Email / Slack Notifications

**Value**: High  
**Effort**: Low  
**Dependencies**: `smtplib` or Gmail API (`google-api-python-client`); `slack-sdk`

### What to automate
- After `royalty_output()` (step 1), auto-compose the Finance Team email body
  (subject line, total royalty, negative count/amount, non-standard rows) and
  either send directly or open a draft.
- Post the checksum notice to the `#pub-sales-processing` Slack channel
  (total row count + sum of column W) before sending the file to James Kass.

### How
- **Email**: `smtplib` + Python `email` stdlib, or the Gmail API since this is a
  Google Workspace org. Recipient list is already in `config.FINANCE_EMAIL_RECIPIENTS`.
  Message body is already assembled as a `Textobj` in `royalty_output()` — it is one
  step away from being sent.
- **Slack**: Slack Web API (`slack-sdk`) with a bot token.
  One `chat.postMessage` call to `config.SLACK_CHECKSUM_CHANNEL`.

### Config constants already in place
- `config.FINANCE_EMAIL_RECIPIENTS`
- `config.SLACK_CHECKSUM_CHANNEL`

---

## 2. Google Sheets — Read Deal Tracker Live

**Value**: High  
**Effort**: Medium  
**Dependencies**: `gspread`, Google service account credentials

### What to automate
- Read the Publishing Admin Deal Tracker directly from Google Sheets at runtime
  in `contract_rate_cal()` (step 5) instead of requiring a manual XLSX download.
- This ensures the deal tracker is always current and eliminates the versioned
  file clutter (`Publishing Admin Deal Tracker_Official-YYYYMMDD.xlsx`, etc.).

### How
- `gspread` library with a service account.
- Replace `pd.read_excel(label_map_file, sheet_name=None)` with a `gspread` call
  using `config.DEAL_TRACKER_GDRIVE_URL` as the source.
- The service account needs read access to the Google Sheet.
- Would require provisioning a Google Cloud service account in the org's GCP project.

### Config constants already in place
- `config.DEAL_TRACKER_GDRIVE_URL`

---

## 3. Google Sheets — Write Missing Songs Output + Read Back Corrections

**Value**: High  
**Effort**: Medium  
**Dependencies**: `gspread`, Google service account credentials (same as item 2)

### What to automate
- After `reconcile_song_ids()` (step 2) generates the missing songs file, write
  it directly to a Google Sheet instead of a local XLSX.
- After Aidan Miller fills in the corrections, read the sheet back directly in
  `update_master_lookup()` (step 3) instead of waiting for a file to be shared.
- This replaces the entire manual download/upload cycle for the missing songs handoff.

### How
- `gspread`: write the missing songs DataFrame to a new or named sheet in a
  shared Google Drive folder under `config.GDRIVE_REVENUE_FOLDER_ID`.
- Store the sheet URL/ID after creation so step 3 can read it back.
- The sheet URL could be written to a small local state file (e.g. `run_state.json`)
  or printed for the user to share with Aidan.

### Config constants already in place
- `config.MASTER_LOOKUP_GDRIVE_URL`
- `config.GDRIVE_REVENUE_FOLDER_ID`

---

## 4. Google Drive — Auto-Upload Generated Reports

**Value**: Medium  
**Effort**: Low  
**Dependencies**: `google-api-python-client`, same service account as items 2–3

### What to automate
- After each output file is generated (royalty output XLSX, contract rates sheet,
  run results), auto-upload it to the correct quarterly subfolder in the Revenue
  Google Drive directory.
- Auto-create the quarterly folder if it does not exist, following the existing
  naming convention already found in the directory.

### How
- Google Drive API v3: `files().create()` with the parent folder ID.
- Parent folder ID: `config.GDRIVE_REVENUE_FOLDER_ID`.
- Quarterly subfolder name: derived from `config.FILESERIES` or `config.PERIOD`.

### Config constants already in place
- `config.GDRIVE_REVENUE_FOLDER_ID`

---

## 5. Elysium Melodies (793219) Tiered Rate Calculation

**Value**: Medium  
**Effort**: Low  
**Dependencies**: pandas only (already a dependency)

### What to automate
- After James's file is loaded in `contract_rate_cal()` (step 5), automatically
  calculate the gross sales total for account 793219, apply the tier logic from
  `config.ELYSIUM_RATE_TIERS`, and inject the determined rate into the contract
  rates output sheet.
- Print a clear summary of the tier determination for audit purposes.

### How
- Pure pandas: filter James's DataFrame for `vendor_id == config.ELYSIUM_ACCOUNT_ID`,
  sum `roy_amt`, walk `config.ELYSIUM_RATE_TIERS` to find the applicable tier.
- A small helper function `_elysium_rate(gross: float) -> float` inside `pipeline.py`.
- The determined rate overwrites the row for account 793219 in the contract rates
  DataFrame before the XLSX is written.

### Config constants already in place
- `config.ELYSIUM_ACCOUNT_ID`
- `config.ELYSIUM_RATE_TIERS`

---

## 6. S3 File Operations (Sales File Upload + Statement Sync)

**Value**: Medium  
**Effort**: Medium  
**Dependencies**: `boto3` (already in requirements.txt), IAM role assumption

### What to automate

#### Sales file upload (before Snowflake load)
- Rename and gzip the Sony Publishing sales file.
- Upload to `qa-royalties-sales-files/publishing` or
  `prod-royalties-sales-files/publishing` depending on `SF_ROYALTY_SCHEMA` in `.env`.

#### Statement file download (after Snowflake calculation)
- Sync statement files down from `qa-publish-client-report` S3 bucket
  in the `youtube-audit` AWS account.

#### Statement file upload (final step)
- Upload statement `.xlsx` files to `prod-orcdbucket/prod-statement-attachments`.
- Handle the >20MB file dance: upload a placeholder first, then overwrite with
  the full file directly in `prod-orcdbucket/prod-per-label-statement-attachments`.

### How
- `boto3` with `sts.assume_role()` for cross-account access to `youtube-audit`.
- The bucket names and key prefixes are known and should be added to `config.py`
  or `.env` as constants before implementation.
- `awsume` is the current manual mechanism — this would replace the CLI steps.

### Notes
- This touches two separate AWS accounts. Cross-account role assumption config
  needs to be confirmed before implementing.
- The >20MB placeholder dance should be a clearly documented separate code path.

---

## 7. Jira Ticket Enrichment and DBPR Attachment

**Value**: Low  
**Effort**: Low  
**Dependencies**: `jira` library (already a dependency)

### What to automate
- Include the full list of affected `pub_song_id` values in the Jira ticket
  description when creating the DBPR sync ticket in `_check_swa_neo4j()`.
- Attach the generated `PUB-{key}_touch_composition_node.xml` file directly
  to the Jira ticket after it is created.

### How
- Description enrichment: already assembled in `_check_swa_neo4j()` — just
  include `ids_summary` in the body (partially done in v2; can be expanded).
- Attachment: `jira_client.add_attachment(issue_key, filepath)` using the
  `jira` Python library. Call after `generate_swa_dbpr()` returns the file path.

---

## 8. Period Auto-Detection

**Value**: Low  
**Effort**: Very Low  
**Dependencies**: Snowflake connection (already required)

### What to automate
- The startup period-mismatch check in `pub.py` already queries Snowflake
  for the latest period and warns if it differs from `config.PERIOD`.
- This could be extended to prompt the user to confirm or auto-update `config.py`
  in-place if the period has advanced.

### How
- Already partially implemented in `pub.py` (`_check_period_mismatch()`).
- Extension: if mismatch detected, prompt `Update config.PERIOD to {max_period}? (Y/N)`.
- If Y: use `ast` or a simple regex to rewrite the `PERIOD = ...` line in `config.py`.
- Low risk since the user confirms before any change is made.

---

## Implementation Notes

### Shared prerequisite for items 2, 3, 4 (Google APIs)
A Google Cloud service account is required. Steps:
1. Create a service account in the org's GCP project.
2. Grant it read/write access to the relevant Google Drive folder and Sheets.
3. Download the service account JSON key.
4. Add the key path to `.env` as `GOOGLE_SERVICE_ACCOUNT_KEY_PATH`.
5. Add `gspread` and `google-api-python-client` to `requirements.txt`.

### Recommended implementation order
If prioritising by value-to-effort ratio:

1. Elysium tiered rate (item 5) — pure pandas, no new dependencies, very contained
2. Jira enrichment (item 7) — one extra API call, already have the data
3. Period auto-update prompt (item 8) — already mostly done
4. Slack checksum post (item 1, Slack part) — one API call once a bot is set up
5. Email draft (item 1, email part) — after Slack is working
6. Google Drive upload (item 4) — once service account is provisioned
7. Google Sheets deal tracker read (item 2) — once Drive auth is working
8. Google Sheets missing songs write/read (item 3) — builds on item 2
9. S3 operations (item 6) — most complex; confirm AWS cross-account setup first