# bulk-insert-label-participants

This script facilitates ingestion of label participants from an input table.

## Tech design
### Given
#### Input table
Table with participants, roles, ISRC's, label ID's, subaccounts
```sql 
-- This is the table that stores the data we need to attach
CREATE TABLE TRACK_LABEL_PARTICIPANTS_88RISING_2023_12_08_01 (
	ID INT AUTOINCREMENT,
	UPC VARCHAR(20),
	ISRC VARCHAR(20),
	ARTIST_NAME VARCHAR(255) NOT NULL,
	ARTIST_ROLE VARCHAR(100),
	VENDOR_ID VARCHAR(10) NOT NULL,
	SUBACCOUNT_ID VARCHAR(10) NOT NULL,
	SPOTIFY_ID VARCHAR(255),
	APPLE_MUSIC_ID VARCHAR(255)
);
```
#### Role Mappings
Mapping with Orchad roles to WMG roles.
[wmg_studio_role_mapping.py](./constants/wmg_studio_role_mapping.py)

#### Steps
A [graphql cheatsheet](./docs/GRAPHQL_CHEATSHEET.md) of the queries used in this script.
1. Get all unique names, vendors, and subacounts from Snowflake table.
2. Run [`get_or_create_label_participant()`](./logic/graphql_logic.py#L101) on each name for the label and subaccount, and store the ~~UUID's~~ **participant id's** that are returned with the name, label, and subaccount. This will produce a data structure that looks like this:
	```python
	participant_list = [
		('Ted Franklin', '82315', '28493', '12372390'),
		('Bill Johnson', '82315', '28493', '39442039'),
	]

	# Incorrect
	# UUID's are NOT USED by the setLabelSoundRecordingParticipations mutation!
	#  participant_list = [
	#	('Ted Franklin', '82315', '28493', 'a19d08be-b07f-4d22-a29b-0797014e3d48'),
	#	('Bill Johnson', '82315', '28493', '67bf9c19-66a1-449f-8049-9bc726acf365'),
	#]
	```
	In addition, [`get_or_create_label_participant()`](./logic/graphql_logic.py#L101) will set the Spotify ID and Apple Music ID of the participants, if it is provided in the input table.

1. Select all values from the Input Table.

	*(N.B. The next 2 steps are combined into one method)*

2. Pivot the data by ISRC, so that each ISRC is a key, and at that key is a list of dicts that each contain the name, and the role. This produce a data structure that looks like this:
	```python
	isrc_dict = {
		'ISRC0192348': [
			{
				'name': 'Ted Franklin',
				'role': 'A&R',  # ('Label Personnel', 'A&R Specialist'),

			},
			{
				'name': 'Bill Johnson',
				'role': 'Drum Mixer',  # ('Studio Personnel'), ('Drum Mixer'),
			}
		],
		'IASN2934023': [
			{
				'name': 'Ted Franklin',
				'role': 'DJ Mixer',  # ('Studio Personnel', 'DJ Mixer'),
			},
			{
				'name': 'Bill Johnson',
				'role': 'Disc Handler',  # ('Studio Personnel', 'Disc Flipper'),
			}
		]
	}
	```

1. Map all the roles from the WMG to The Orchard.  Add all the collected uuids from `participant_list`. This will produce a dict with lists of dicts, like this:
	```python
	isrc_dict = {
		'ISRC0192348': {
			'vendor_id': 23823,
			'subaccount_id': 5434,
			'participations': [
			{
				'name': 'Ted Franklin',
				'ddex_role': 'A&R',
				'role': 'A&R Specialist',
				'category': 'Label Personnel',
				'participant_id': '12372390'
			},
			{
				'name': 'Bill Johnson',
				'ddex_role': 'Drum Mixer',
				'role': 'Drum Mixer',
				'category': 'Studio Personnel',
				'participant_id': '39442039'
			}
		],
		'IASN2934023': {
			'vendor_id': 83748,
			'subaccount_id': 3456,
			'participations': [
			{
				'name': 'Ted Franklin',
				'ddex_role': 'DJ Mixer',
				'role': 'DJ Mixer',
				'category': 'Studio Personnel',
				'participant_id': '12372390'
			},
			{
				'name': 'Bill Johnson',
				'ddex_role': 'Disc Handler',
				'role': 'Disc Flipper',
				'category': 'Studio Personnel',
				'participant_id': '39442039'
			}
		]
	}
	```
2. Iterate through this list, and execute [`set_label_participants_isrc()`](./logic/graphql_logic.py#L235) on each ISRC, using the values from the `isrc_dict` to generate and execute GraphQL mutations that will update the participants on every passed ISRC.

## Flags

### SET_TRACK_PARTICIPANTS
This environment variable controls whether participants are attached to tracks. 

If this value is `True`, the code executes as described above.

If this value is `False`, then only [`get_or_create_label_participant`](./logic/graphql_logic.py#L101) will be executed on the input set. This means only participant ID's, UUID's, Apple Music ID's, and Spotify ID's are affected. No track-level mutations occur. This is can be useful for bulk updating spotify and apple id's