Accounting - Masters Registry Integration 
========================================

Python script for processing the accounting report.
Script requires python 3.13.

PLEASE NOTE: This is not a lambda, despite the name of the repository. It's a script that is run
on a jenkins scheduler job: https://scheduler.theorchard.io/job/lambda-masters-registry-producer/

##### Running producer

To run the producer script locally you have to create virtualenv and install requirements first.
```
$ virtualenv venv/lambda-producer
$ source venv/lambda-producer/bin/activate
$ pip install -r requirements.txt
```

Then you need to set up and export environment variables.
```
$ cp .env.shadow .env
```
Fill-in all values in `.env` file. Then export environment variables:
```
$ set -o allexport; source .env; set +o allexport
```

Now you can run the producer locally:

```
$ cd accounting
$ python producer.py
```

The producer will query records from source DB and export to CSV file.
After that CSV file will be imported to Snowflake, processed by joining
the ISRC and TERRITORY to find corresponding orchard_sound_recording rows and
fingerprinting rules that apply to that OSR in that territory for that date for that service.
Then the processed CSV file will be downloaded from Snowflake and imported back to MySQL.

#### Docker 

You'll need to run 
```
aws awsume
aws ecr get-login-password --region us-east-1 | docker login --username AWS --password-stdin 086679231553.dkr.ecr.us-east-1.amazonaws.com
```
Before you can use Docker

#### Required Environment Variables
| Env Variable  | Purpose  |
| ------------- |--------- |
| Environment | Execution environment name |
| MYSQL_HOST | Source DB host |
| MYSQL_PORT | Source DB port (usually 3306) |
| MYSQL_USER | DB user |
| MYSQL_PASSWORD | DB password |
| MYSQL_DB | DB name (stmt-db) |
| MYSQL_TABLE_NAME | DB table name (usually video_youtube_mrr_candidates) |
| SNOWFLAKE_ACCOUNT | Snowflake account name |
| SNOWFLAKE_USER | Snowflake user |
| SNOWFLAKE_PRIVATE_KEY | Snowflake private key |
| SNOWFLAKE_DB | Snowflake database |
| SNOWFLAKE_ROLE | Snowflake role |
| SNOWFLAKE_SCHEMA = | Snowflake schema |
| SNOWFLAKE_WAREHOUSE | Snowflake warehouse |


#### Typical run
In Jenkins, we run kick off a manual run of this job:
https://scheduler.theorchard.io/job/lambda-masters-registry-producer/

In the parameters, we need to provide:
MYSQL_TABLE_NAME
for this we would typically use:
"video_youtube_mrr_candidates"
This is a table in stmt-db against which the producer script will run the GET_BATCH sql, so something like:

```
SELECT service, date, isrc, territory
FROM {table_name}
WHERE tuid = -1
LIMIT %(limit)s
OFFSET %(offset)s;
```

That SQL will be loaded in export_csv() in producer.py and then written to the CSV file with name EXPORT_FILENAME, typically:
export.csv

From there the producer will run process_snowflake() which will:
1. create a tmp table in Snowflake called mrr_video_candidate_tmp
2. upload the CSV file
3. Run SQL in Snowflake to enrich the data in the tmp table with the data from Fingerprinting rules
4. load the data from the Snowflake table mrr_video_candidate_tmp into a CSV
5. Load the data from the CSV file into stmt-db


## Running with Fingerprints
As of April 2026 we are moving away from using data in the master rights registry, to using data held in 
various snowflake tables, which contain fingerprinting data. 
To enable this new data source to be switched in and out per run we use the environment variable USE_MRR, which defaults to true. 
This is a string which you can set to 'False' or 'True'.  Using this means that should our new data source, and corresponding logic, prove
inaccurate during testing or on initial usage, then the s-house team can run the job again using the old data and logic. 
When we are happy, this env var will be removed so that we only us fingerprinting data.