# Collection Tools

## SCPP XML Generator

### Permutations
The SCPP XML Generator has two checkboxes:
* Omit duplicate track/artist name pairs, regardless of TUID or ISRC?
* Resend ISRC's that already have SCPPID's?

The table below displays the result of how various types of inputs will be 
processed by the logic in the system given the condition of the two checkboxes at runtime.

---

#### Legend
|Options|
|--|
|Ineligible on TUID|
|Ineligible on ISRC|
|Ineligible on dupe track/artist|
|Use original TUID's SCPPID|
|Generate new SCPPID|

---

|Logic Flow|
|--|
|1. Check if TUID's exist in DB|
|2. Check if ISRC's exist in DB|
|3. Check if Artist / Track exist in DB|

---

#### Permutation Table
|TUID State|ISRC State|Track / Artist State|Dedupe on Track / Artist = Y|Resend Existing TUID = Y|Both = Y|Both = N|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|New|New|New|Generate new SCPPID|Generate new SCPPID|Generate new SCPPID|Generate new SCPPID|
|New|Dupe|New|Ineligible on ISRC|Use original TUID's SCPPID|Use original TUID's SCPPID|Ineligible on ISRC|
|New|New|Dupe|Ineligible on dupe track/artist|Generate new SCPPID|Ineligible on dupe track/artist|Generate new SCPPID|
|New|Dupe|Dupe|Ineligible on dupe track/artist|Use original TUID's SCPPID|Ineligible on dupe track/artist|Ineligible on ISRC|
|Dupe|New|New|Ineligible on TUID|Use original TUID's SCPPID|Use original TUID's SCPPID|Ineligible on TUID|
|Dupe|Dupe|New|Ineligible on TUID|Use original TUID's SCPPID|Use original TUID's SCPPID|Ineligible on TUID|
|Dupe|New|Dupe|Ineligible on TUID|Use original TUID's SCPPID|Ineligible on dupe track/artist|Ineligible on TUID|
|Dupe|Dupe|Dupe|Ineligible on TUID|Use original TUID's SCPPID|Ineligible on dupe track/artist|Ineligible on TUID|

---

## Notes

### Removals from SCPPID database
The SCPP XML generation portion of the logic provides that if a track's metadata 
contains `deletions = 'Y'`, it will be sent as a deletion to SCPP.

Currently, however, all `deletions = 'Y'` rows are removed during the eligibility
check provided by the `SELECT_SCPP_ELIGIBLE_METADATA` query.

### Track and artist name duplicates
A new indexed column has been added to the scppid_list. This GENERATED ALWAYS 
column contains `UNHEX(MD5(CONCAT(track_name, ' | ', artist_name)))` stored as 
`BINARY(16)`.

This allows for rapid `track_name` and `artist_name` duplicate checks with an
acceptable probability of collisions across ~3 million records where 
collision-based exclusion is effectively reported in side-car files.

SQL:
```
-- Add generated column
ALTER TABLE scppid_list ADD COLUMN `track_artist_hash` BINARY(16) GENERATED ALWAYS AS (UNHEX(MD5(CONCAT(track_name, ' | ', artist_name)))) STORED;

-- Add key for new column
ALTER TABLE scppid_list ADD KEY `track_artist_md5` (`track_artist_hash`);
```

### Missing Values
#### Languages
If the language for a track is missing, or unknown, it defaults to SCPP 
Language Code `en` for English.

If the SCPP label ID for a track is missing from `scpp_label_id_list`, or 
unknown, it defaults to `0`.

### Duplicate ISRC's in Input List
If the input list contains multiple TUID's which resolve to the same ISRC, the 
ISRC will be checked in the SCPPID list. If it exists in the list, AND 
`Resend existing SCPPID / ISRC for TUID's or ISRC's that already exist in the SCPPID table` 
is checked, then the original will be sent, and the others will be moved to the 
ineligible list. Otherwise, all ISRC's will be moved to the ineligible TUID 
list.