"""SCPPID DB Auditor."""
import json
import re

from datetime import datetime
from os.path import exists, split, join, splitext
from sys import stderr
from typing import Tuple, Match, NamedTuple, List, Any

from loguru import logger as log
import pandas as pd

from config import SCPP_AUDIT_FILE, OUTPUT_FILE_DIR, PPB_DATABASE, \
    SCPPID_TABLE, PPB_HOST

from connectors.mysql import rds_cursor, rds_con, ar_con, ar_cursor

from constants import INPUT_FILE_EXT_LIST, MYSQL_SELECT_LIMIT

from sql.select_count_scppid import SELECT_COUNT_SCPPID
from sql.select_scppid_by_chunks import SELECT_SCPPID_BY_CHUNKS
from sql.select_track_metadata_by_isrc import SELECT_TRACK_METADATA_BY_ISRC
from sql.select_track_metadata_by_isrc_list \
    import SELECT_TRACK_METADATA_BY_ISRC_LIST
from sql.select_track_isrc_by_isrc_list import SELECT_TRACK_ISRC_BY_ISRC_LIST


# Loguru log formatting constant
default_format = '<g>{time:YYYY-MM-DD at h:mm:ss A zz}</g> | <b><e>{' \
                 'level: ^10}</e></b> | {message}'

# Get script run datetime
script_run_at = datetime.now().strftime('%Y-%m-%d_%H_%M_%S')

# Remove and replace default logger
log.remove(0)

# Default Main Logger
log.add(stderr, format=default_format, colorize=True)

input_file_name = split(SCPP_AUDIT_FILE)[1]
output_path = split(SCPP_AUDIT_FILE)[0]

processing_errors = []


def is_isrc(isrc: str) -> Match:
    """Verify the string is an ISRC."""
    return re.match(
        r'^[A-Za-z]{2}[-]?[0-9A-Za-z]{3}[-]?[0-9]{2}[-]?[0-9]{5}$', isrc)


def ingest_audit_file(scpp_audit_file: str) -> dict:
    """Ingest an Excel file to a dict, sifted and pivoted on various fields.

    Args:
        scpp_audit_file (string): The path and filename of the file to ingest.

    Raises:
        FileNotFoundError: If the passed file does not exist.

    Returns:
        dict: The contents of the file, sifted and pivoted.
    """    
    
    # Init vars
    verified_file_scpp_ids = []
    verified_file_isrcs = []
    
    scpp_id_to_isrc_dict = {}
    isrc_to_scpp_id_dict = {}
    
    # Log opening and loading file to user
    msg = f'Opening and loading \'{scpp_audit_file}\'.'
    log.info(msg)

    # Check path exists
    if not exists(scpp_audit_file):
        raise FileNotFoundError(
            f'SCPP Audit file \'{scpp_audit_file}\' not found.')
        
    # Check valid extension
    if not splitext(scpp_audit_file)[1].lower() in INPUT_FILE_EXT_LIST:
        extension_list = '\', \''.join(INPUT_FILE_EXT_LIST)
        raise RuntimeError(
            f'SCPP Audit file \'{scpp_audit_file}\' extension is not one '
            f'of (\'{extension_list}\').')
    
    # Open Excel file as dataframe
    with pd.ExcelFile(scpp_audit_file) as xl:
        
        # TODO: Check format of sheet
        
        # Loop through all sheets
        for sheet_name in xl.sheet_names:
            log.info(f'Processing sheet: \'{sheet_name}\'')
            sheet = pd.read_excel(xl, sheet_name=sheet_name)
            
            # Convert sheet to dict with separated fields
            sheet_dict = sheet.to_dict('split')
            
            # Generate various slices
            for item in sheet_dict['data']:
                try:
                    scpp_id = int(item[0])
                except Exception as e:
                    processing_errors.append(
                        f'Error while processing SCPPID \'{item[0]}\' from '
                        f'input file: {str(e)}.'
                    )
                    continue
                
                # Add SCPPID to full lists of input file's SCPPID's
                verified_file_scpp_ids.append(scpp_id)
                
                 # Compile list of SCPPID -> ISRC's in input file
                if not scpp_id_to_isrc_dict.get(scpp_id):
                    scpp_id_to_isrc_dict[scpp_id] = dict(file=[], db=[])
                
                # Ensure all ISRC's have UPPERCASE alpha component
                isrc = str(item[1]).strip().upper()
                
                if not isrc:
                    continue

                # Add ISRC to full lists of input file's ISRC's
                verified_file_isrcs.append(isrc)

                if not isrc in scpp_id_to_isrc_dict[scpp_id]['file']:
                    scpp_id_to_isrc_dict[scpp_id]['file'].append(isrc)

                # Compile list of ISRC -> SCPPID's in input file
                if not isrc_to_scpp_id_dict.get(isrc):
                    isrc_to_scpp_id_dict[isrc] = dict(file=[], db=[])  

                if not scpp_id in isrc_to_scpp_id_dict[isrc]['file']:
                    isrc_to_scpp_id_dict[isrc]['file'].append(scpp_id)

        # Clean up memory
        del(sheet_dict)
        
    return verified_file_scpp_ids, verified_file_isrcs, scpp_id_to_isrc_dict, \
        isrc_to_scpp_id_dict


def dedupe_list(items: list) -> Tuple[int, list]:
    """De-dupe a list of items."""

    deduped_item_list = list(set(items))
    
    diff = len(items) - len(deduped_item_list)
        
    return diff, deduped_item_list


def process_db_items(
    scppid_isrc_db_list: list, full_audit_dict: dict) -> Tuple[dict, list, list]:
    """Process the items from the DB."""
    
    # Init vars
    db_isrcs = []
    db_scpp_ids = []
    
    # Split DB responses into groups
    for item in scppid_isrc_db_list:
        # Cast double form DB to int
        try:
            scpp_id = int(item.scpp_id)
        except Exception as e:
            processing_errors.append(
                f'Error while processing SCPPID \'{item.scpp_id}\' in DB: ',
                str(e)
            )
            continue
        
        # Add SCPPID to full lists of db's SCPPID's
        db_scpp_ids.append(int(scpp_id))
        
        # Compile list of SCPPID -> ISRC's in db
        if not full_audit_dict['scpp_id_to_isrc_dict'].get(scpp_id):
            full_audit_dict['scpp_id_to_isrc_dict'][scpp_id] = \
                dict(file=[], db=[])
        
        isrc = str(item.isrc).strip().upper()
        
        if not isrc:
            continue
        
        # Add SCPPID to full lists of db's SCPPID's
        db_isrcs.append(isrc)
        
        if not isrc in full_audit_dict['scpp_id_to_isrc_dict'][scpp_id]['db']:
            full_audit_dict['scpp_id_to_isrc_dict'][scpp_id]['db'].append(isrc)
        
        # Compile list of ISRC -> SCPPID's in db
        if not full_audit_dict['isrc_to_scpp_id_dict'].get(isrc):
            full_audit_dict['isrc_to_scpp_id_dict'][isrc] = dict(file=[], db=[])
        
        if not scpp_id in full_audit_dict['isrc_to_scpp_id_dict'][isrc]['db']:
            full_audit_dict['isrc_to_scpp_id_dict'][isrc]['db'].append(scpp_id)
            
    return full_audit_dict, db_isrcs, db_scpp_ids

    
def get_db_items(item_count: int) -> list:
    """Get rows from the DB.

    Args:
        item_count (int): The count of rows in the DB

    Returns:
        list: The list of items from the DB (as named tuples in current config)
    """    
    
    scppid_isrc_db_list = []
    
    # Query DB for all SCPPID's and ISRC's in chunks
    for i in range(0, item_count, MYSQL_SELECT_LIMIT):
        
        log.info(f'Retrieving rows {i + 1} - {i + MYSQL_SELECT_LIMIT}')        
        
        rds_cursor.execute(SELECT_SCPPID_BY_CHUNKS, (MYSQL_SELECT_LIMIT, i))
        
        scppid_isrc_db_list = scppid_isrc_db_list + rds_cursor.fetchall()

    return scppid_isrc_db_list

def get_multiples(elements: dict, type: str) -> list:
    """Create a list of items that have multiple associations"""
    return [k for k,v in elements.items() if len(v[type]) > 1]


def check_isrc(isrc: str) -> List[NamedTuple]:
    """Get the metadata for a given isrc."""
    
    tracks = []
    
    ar_cursor.execute(SELECT_TRACK_METADATA_BY_ISRC, (isrc))
    
    for item in ar_cursor.fetchall():
        tracks.append(item)
        
    return tracks


def check_isrc_list(isrc_list: list) -> List[NamedTuple]:
    """Get the metadata for a given isrc."""
    
    tracks = []
    
    item_count = len(isrc_list)
    
    log.info(f'Getting metadata for {item_count} ISRC\'s.')
    
    for i in range(0, item_count, MYSQL_SELECT_LIMIT):
        
        log.info(f'Retrieving metadata for ISRC\'s ' 
                 f'{i + 1} - {i + MYSQL_SELECT_LIMIT}')
        
        # Craft MySQL list substitution fields
        sub_values = ['%s'] * len(isrc_list[i:i+MYSQL_SELECT_LIMIT])
        
        ar_cursor.execute(
            SELECT_TRACK_METADATA_BY_ISRC_LIST.format(
                isrc_list=','.join(sub_values)), 
            (isrc_list[i:i+MYSQL_SELECT_LIMIT])
        )
        
        for item in ar_cursor.fetchall():
            tracks.append(item)
        
    return tracks


def check_isrc_only_list(isrc_list: list) -> List[str]:
    """Get the metadata for a given isrc."""
    
    isrcs = []
    
    item_count = len(isrc_list)
    
    log.info(f'Checking catalog for {item_count} ISRC\'s.')   
    
    for i in range(0, item_count, MYSQL_SELECT_LIMIT):
        
        log.info(f'Retrieving ISRC\'s {i + 1} - {i + MYSQL_SELECT_LIMIT}')   
        
        # Craft MySQL list substitution fields
        sub_values = ['%s'] * len(isrc_list[i:i+MYSQL_SELECT_LIMIT])
        
        ar_cursor.execute(
            SELECT_TRACK_ISRC_BY_ISRC_LIST.format(
                isrc_list=','.join(sub_values)), 
            (isrc_list[i:i+MYSQL_SELECT_LIMIT])
        )
        
        for item in ar_cursor.fetchall():
            isrcs.append(str(item.isrc).upper())
        
    return isrcs

    
def audit_scpp_ids(full_audit_dict: dict) -> Tuple[dict, dict]:
    """Perform auditing and cross-checking between file and db."""

    audit_output = {}
    
    not_in_catalog_isrcs = full_audit_dict['not_in_catalog_isrcs']
    scpp_id_to_isrc_dict = full_audit_dict['scpp_id_to_isrc_dict']
    
    # Find SCPP mismatches
    for scpp_id, isrcs in scpp_id_to_isrc_dict.items():
        # Init loop vars
        msg = []
        matched_isrcs = set()
        missing_isrcs = dict(not_in_db = [], not_in_file=[])
        bad_isrcs = dict(invalid=set(), non_orchard=set())
        candidate_isrcs = dict(db_update=[], resend=[])
        use_isrc = None
        missing_in_db = False
        missing_in_file = False
        perfect_match = None
        mismatches = []
        file_match = False
        db_match = False
        
        # Audit ISRC's listed in the File for this scpp_ID
        if len(isrcs['file']) > 1:
            msg.append('Provided audit file lists multiple ISRC\'s:')
            for isrc in isrcs['file']:
                msg.append(isrc)
                msg.append('\n')
        elif len(isrcs['file']) == 0:
            missing_in_file = True
            msg.append('This SCPPID is not listed in the provided audit file.')
                
        # Audit ISRC's listed in the DB for this scpp_ID
        if len(isrcs['db']) > 1:
            msg.append('DB lists multiple ISRC\'s:')
            for isrc in isrcs['db']:
                msg.append(isrc)
                msg.append('\n')
        elif len(isrcs['db']) == 0:
            missing_in_db = True
            msg.append('This SCPPID is not listed in the DB.')
        
        # Cross-check ISRC's from file with DB
        for isrc in isrcs['file']:
            # Check ISRC is valid
            if not is_isrc(isrc):
                 msg.append(f'\'{isrc}\' from file is not a valid ISRC.')
                 bad_isrcs['invalid'].add(isrc)
            # Check if ISRC is the Orchard catalog
            elif isrc in not_in_catalog_isrcs['file']:
                msg.append(f'\'{isrc}\' from file is not in the Orchard '
                           'catalog.')
                bad_isrcs['non_orchard'].add(isrc)
            elif isrc not in isrcs['db']:
                msg.append(f'{isrc} is associated with this SCPPPID in the '
                           'provided audit file, but is not listed in the DB.')
                missing_isrcs['not_in_db'].append(isrc)
            else:
                matched_isrcs.add(isrc)
        
        # Cross-check ISRC's from DB with file
        for isrc in isrcs['db']:
            # Check ISRC is valid
            if not is_isrc(isrc):
                 msg.append(f'\'{isrc}\' from DB is not a valid ISRC.')
                 bad_isrcs['invalid'].add(isrc)
            # Check if ISRC is the Orchard catalog
            elif isrc in not_in_catalog_isrcs['db']:
                msg.append(f'\'{isrc}\' from DB is not in the Orchard '
                           'catalog.')
                bad_isrcs['non_orchard'].add(isrc)
            elif isrc not in isrcs['file']:
                msg.append(f'\'{isrc}\' is associated with this SCPPPID in the DB, '
                           'but is not listed in the provided audit file.')
                missing_isrcs['not_in_file'].append(isrc)
            else:
                matched_isrcs.add(isrc)
        
        # Validate ISRC's across both the file and the DB
        if len(matched_isrcs) > 1:
            msg.append('There are multiple ISRC\'s present in both the DB and '
                       'the file:')
            for isrc in matched_isrcs:
                msg.append(isrc)
                
            msg.append('\n')
        elif len(matched_isrcs) == 0:
            if not missing_in_db and not missing_in_file:
                msg.append('All ISRC\'s for this SCPPID are mismatched.')
                for k,v in isrcs.items():
                    mismatches = list(set(mismatches + v))
                
        elif len(matched_isrcs) == 1 and len(msg) == 0:
            perfect_match = list(matched_isrcs)[0]
                
        # Recommendations
        if len(msg) > 0:
            if len(matched_isrcs) == 1:
                msg.append(
                    f'Recommend using {list(matched_isrcs)[0]} as correct ISRC.')
            if len(matched_isrcs) == 0:
                msg.append(f'This SCPPID entry needs to be repaired.')
                
                if len(missing_isrcs['not_in_db']) == 1:
                    candidate_isrcs['db_update'] = missing_isrcs['not_in_db'][0]
                    use_isrc = missing_isrcs['not_in_db'][0]
                    msg.append(
                        f'Recommend using {use_isrc} as correct ISRC and '
                        'updating the DB.') 
                elif len(missing_isrcs['not_in_db']) > 1:
                    msg.append(
                        f'Recommend choosing one of the following ISRC\'s, and '
                        'updating the DB.')
                    for isrc in missing_isrcs['not_in_db']:
                        msg.append(isrc) 
                else:
                    if len(missing_isrcs['not_in_file']) == 1:
                        candidate_isrcs['resend'] = missing_isrcs['not_in_file'][0]
                        use_isrc = missing_isrcs['not_in_file'][0]
                        msg.append(
                            f'Recommend marking {use_isrc} as correct ISRC '
                            'and resending to SCPP.') 
                    elif len(missing_isrcs['not_in_file']) > 1:
                        msg.append(
                            f'Recommend choosing one of the following ISRC\'s, '
                            'resending to SCPP, and marking the other as '
                            'in the DB.')
                        for isrc in missing_isrcs['not_in_file']:
                            msg.append(isrc)
                    else:
                        msg.append(
                            f'There is no good candidate ISRC for this SCPPID.')
            if len(matched_isrcs) > 1:
                msg.append('This SCPPID\'s multiple ISRC\'s must be corrected.')
                    
            # Add newline for legibility
            msg.append('\n')
            
        # Add SCPPID to audit output (Convert sets for JSON output)
        audit_output[scpp_id] = \
            dict(msg=msg, 
                 matched_isrcs=list(matched_isrcs), # Can't serialize a set
                 bad_isrcs=\
                     dict(zip(bad_isrcs.keys(), map(list, bad_isrcs.values()))),
                 missing_isrcs=missing_isrcs,
                 candidate_isrcs=candidate_isrcs,
                 perfect_match=perfect_match,
                 mismatches=mismatches)
                
    return audit_output


def write_var_to_file(var: Any) -> str:
    """Writes a variable to a file.

    Args:
        var (Any): The variable to write

    Returns:
        str: The filename generated
    """        
    # Get a timestamp
    output_var_time = datetime.now().strftime('%Y-%m-%d_%H_%M_%S')
    
    # Write var to file
    output_file = \
        join(OUTPUT_FILE_DIR, f'output_var_{output_var_time}.json')
    
    log.info(f'Writing {output_file}.')
    
    with open(output_file, 'w') as var_file:
        json.dump(var, var_file, 2)
    
    log.info(f'{output_file} written.')
    
    return output_file


def main():
    """The main function."""

    # Final Audit Data Structure
    full_audit_dict = dict(
        isrcs={},
        scpp_ids={},
        deduped_scpp_ids={},
        deduped_isrcs={},
        scpp_ids_with_dupe_isrc={},
        isrcs_with_dupe_scpp_id={},
        item_count = {},
        not_in_catalog_isrcs = {}
    )
    
    # Log to user
    msg = f'Using DB: \'{PPB_HOST}\'.'
    log.info(msg)
    
    # Ingest the audit file
    verified_file_scpp_ids, verified_file_isrcs, scpp_id_to_isrc_dict, \
        isrc_to_scpp_id_dict = \
        ingest_audit_file(SCPP_AUDIT_FILE)
    
    verified_file_isrcs_len = len(verified_file_isrcs)
    verified_file_scpp_ids_len = len(verified_file_scpp_ids)
    
    # Report count of items.
    log.info(f"{verified_file_isrcs_len} ISRC's found in file.")
    log.info(f"{verified_file_scpp_ids_len} SCPPID's found in file.")
    
    # Add to full audit data structure
    full_audit_dict['item_count']['file'] = verified_file_scpp_ids_len
    
    # Add to full audit data structure
    full_audit_dict['isrcs']['file'] = verified_file_isrcs
    full_audit_dict['scpp_ids']['file'] = verified_file_scpp_ids
    full_audit_dict['scpp_id_to_isrc_dict'] = scpp_id_to_isrc_dict
    full_audit_dict['isrc_to_scpp_id_dict'] = isrc_to_scpp_id_dict
    
    # Clear Memory
    del(verified_file_scpp_ids)
    del(verified_file_isrcs)
    del(scpp_id_to_isrc_dict)
    del(isrc_to_scpp_id_dict)

    # De-dupe SCPPID's
    diff, deduped_file_scppid_list = \
        dedupe_list(full_audit_dict['scpp_ids']['file'])
        
    # Add de-duped SCPPID list to full audit data structure
    full_audit_dict['deduped_scpp_ids']['file'] = deduped_file_scppid_list
    
    # Report number of SCPPID duplicates
    if diff != 0:
        log.warning(f'Verified file\'s SCPPID list contains {diff} duplicates')
    
    # De-dupe ISRC's
    diff, deduped_file_isrc_list = dedupe_list(full_audit_dict['isrcs']['file'])
    
    # Add de-duped ISRC list to full audit data structure
    full_audit_dict['deduped_isrcs']['file'] = deduped_file_isrc_list
    
    # Clear Memory
    del(deduped_file_scppid_list)
    
    # Report number of ISRC duplicates
    if diff != 0:
        log.warning(f'Verified file\'s ISRC list contains {diff} duplicates') 
        
    log.info('Begin checking ISRC\'s (from file) in The Orchard\'s catalog.')
    
    # Check if ISRC's are in the Catalog
    catalog_file_isrcs = \
        check_isrc_only_list(full_audit_dict['deduped_isrcs']['file'])
    
    # Get all ISRCS not in the Orchard Catalog
    not_in_catalog_file_isrcs = \
        set(deduped_file_isrc_list) - set(catalog_file_isrcs)
        
    # Add all non-Orchard isrcs to the full audit data structure
    full_audit_dict['not_in_catalog_isrcs']['file'] = \
        list(not_in_catalog_file_isrcs)
        
    # Clear Memory
    del(not_in_catalog_file_isrcs)
    del(deduped_file_isrc_list)
    
    # Check for SCPPID's with multiple ISRCS
    scpp_ids_with_dupe_isrc = \
        get_multiples(full_audit_dict['scpp_id_to_isrc_dict'], 'file')
    
    if len(scpp_ids_with_dupe_isrc) > 0:
        log.warning(
            f'{len(scpp_ids_with_dupe_isrc)} SCPPID\'s ' \
            'found in file with more than one ISRC defined.')
    
    # Add SCPPID's with duplicate ISRC's from file to full audit data structure
    full_audit_dict['scpp_ids_with_dupe_isrc']['file'] = scpp_ids_with_dupe_isrc
    
    # Check for ISRC's with multiple SCPPID's
    isrcs_with_dupe_scpp_id = \
        get_multiples(full_audit_dict['isrc_to_scpp_id_dict'], 'file')
    
    if len(isrcs_with_dupe_scpp_id) > 0:
        log.warning(
            f'{len(isrcs_with_dupe_scpp_id)} ISRC\'s ' \
            'found in file with more than one SCPPID defined.')
    
    # Add ISRC's with duplicate SCPPID's from file to full audit data structure
    full_audit_dict['isrcs_with_dupe_scpp_id']['file'] = isrcs_with_dupe_scpp_id

    # ------------------------------------------------------------------------
    # -- DB Processing -------------------------------------------------------
    # ------------------------------------------------------------------------
    
    log.info(f'Begin processing DB table \'{PPB_DATABASE}.{SCPPID_TABLE}\'')
    
    # Get count of SCPPID's in DB
    rds_cursor.execute(SELECT_COUNT_SCPPID)
    
    item_count = rds_cursor.fetchone().scppid_count
    
    # Add to full audit data structure
    full_audit_dict['item_count']['db'] = item_count
    
    log.info(
        f'{item_count} SCPPID\'s found in \'{PPB_DATABASE}.{SCPPID_TABLE}\'')
    
    # Query DB for SCPPIDS and ISRCS
    scppid_isrc_db_list = get_db_items(item_count)
    
    full_audit_dict, db_isrcs, db_scpp_ids = \
        process_db_items(scppid_isrc_db_list, full_audit_dict)
    
    # Report count of items.
    log.info(f'{len(db_isrcs)} ISRC\'s found in db.')
    log.info(f'{len(db_scpp_ids)} SCPPID\'s found in db.')
    
    # Add to full audit data structure
    full_audit_dict['isrcs']['db'] = db_isrcs
    full_audit_dict['scpp_ids']['db'] = db_scpp_ids
    
    # Clear Memory
    del(db_isrcs)
    del(db_scpp_ids)
    
     # De-dupe SCPPID's
    diff, deduped_db_scpp_id_list = \
        dedupe_list(full_audit_dict['scpp_ids']['db'])
    
    # Report number of SCPPID duplicates
    if diff != 0:
        log.warning(f'DB\'s SCPPID list contains {diff} duplicates')
    
    # Add de-duped SCPPID list to full audit data structure
    full_audit_dict['deduped_scpp_ids']['db'] = deduped_db_scpp_id_list
    
    # De-dupe ISRC's
    diff, deduped_db_isrc_list = dedupe_list(full_audit_dict['isrcs']['db'])
       
    # Report number of ISRC duplicates
    if diff != 0:
        log.warning(f'DB\'s ISRC list contains {diff} duplicates') 
        
    log.info('Begin checking ISRC\'s (from DB SCPPID list) in The Orchard\'s '
             'catalog.')
    
    # Add de-duped ISRC list to full audit data structure
    full_audit_dict['deduped_isrcs']['db'] = deduped_db_isrc_list
    
    # Clear Memory
    del(deduped_db_isrc_list)
    del(deduped_db_scpp_id_list)
    
    # Check if ISRC's are in the Catalog
    catalog_db_isrcs = \
        check_isrc_only_list(full_audit_dict['deduped_isrcs']['db'])
    
    # Get all ISRCS not in the Orchard Catalog
    not_in_catalog_db_isrcs = \
        set(full_audit_dict['deduped_isrcs']['db']) - set(catalog_db_isrcs)
        
    # Add all non-Orchard isrcs to the full audit data structure
    full_audit_dict['not_in_catalog_isrcs']['db'] = \
        list(not_in_catalog_db_isrcs)
    
    # Clear Memory
    del(not_in_catalog_db_isrcs)
    
    # Check for SCPPID's with multiple ISRCS
    scpp_ids_with_dupe_isrc = \
        get_multiples(full_audit_dict['scpp_id_to_isrc_dict'], 'db')
    
    if len(scpp_ids_with_dupe_isrc) > 0:
        log.warning(
            f'{len(scpp_ids_with_dupe_isrc)} SCPPID\'s ' \
            'found in DB with more than one ISRC defined.')
    
    # Add SCPPID's with duplicate ISRC's from DB to full audit data structure
    full_audit_dict['scpp_ids_with_dupe_isrc']['db'] = scpp_ids_with_dupe_isrc
    
    # Check for ISRC's with multiple SCPPID's
    isrcs_with_dupe_scpp_id = \
        get_multiples(full_audit_dict['isrc_to_scpp_id_dict'], 'db')
    
    if len(isrcs_with_dupe_scpp_id) > 0:
        log.warning(
            f'{len(isrcs_with_dupe_scpp_id)} ISRC\'s ' \
            'found in DB with more than one SCPPID defined.')
    # Add ISRC's with duplicate SCPPID's from DB to full audit data structure
    full_audit_dict['isrcs_with_dupe_scpp_id']['db'] = isrcs_with_dupe_scpp_id
    
    # Clear Memory
    del(scpp_ids_with_dupe_isrc)
    del(isrcs_with_dupe_scpp_id)
    
    # ------------------------------------------------------------------------
    # Perform Audit Analysis -------------------------------------------------
    # ------------------------------------------------------------------------
    log.info('Beginning audit of SCPPID\'s...')
    
    full_scppid_audit_report = audit_scpp_ids(full_audit_dict)
    
    full_audit_dict['scpp_id_report'] = full_scppid_audit_report
    
    log.info('Audit of SCPPID\'s complete.')
    # TODO ISRC-side-based audit?
    
    # Produce output of all ISRC's with multiple SCPPID's
    
    # ------------------------------------------------------------------------
    # Write output files -----------------------------------------------------
    # ------------------------------------------------------------------------
    
    log.info(f'Writing output files to \'{OUTPUT_FILE_DIR}\'.')
    
    # Write SCPPID Errors to file
    scpp_error_output_file = \
        join(OUTPUT_FILE_DIR, f'scpp_audit_errors_{script_run_at}.txt')
    
    log.info(f'Writing \'scpp_audit_errors_{script_run_at}.txt\'.')
    
    with open(scpp_error_output_file, 'w') as scpp_error_file:
        for scpp_id, audit in full_scppid_audit_report.items():
            if len(audit['msg']) > 0:
                scpp_error_file.write(str(scpp_id) + ':\n')
                scpp_error_file.write('\n'.join(audit['msg']))
                scpp_error_file.write('\n')
    
    log.info(f'\'scpp_audit_errors_{script_run_at}.txt\' written.')
    
    # Write 100% Valid SCPPID's to CSV
    scpp_valid_output_file = \
        join(OUTPUT_FILE_DIR, f'scpp_audit_valid_{script_run_at}.csv')
    
    log.info(f'Writing \'scpp_audit_valid_{script_run_at}.csv\'.')
    
    with open(scpp_valid_output_file, 'w') as scpp_valid_file:
        scpp_valid_file.write('SCPPID,ISRC\n')
        for scpp_id, audit in full_scppid_audit_report.items():
            if audit['perfect_match']:
                scpp_valid_file.write(
                    f"{str(scpp_id)},{audit['perfect_match']}\n")
                
    log.info(f'\'scpp_audit_valid_{script_run_at}.csv\' written.')
    
    # Write recommended Updated SCPPID's to CSV
    scpp_recommend_output_file = \
        join(OUTPUT_FILE_DIR, f'scpp_audit_recommendations_{script_run_at}.csv')
        
    log.info(f'Writing \'scpp_audit_recommendations_{script_run_at}.csv\'.')
    
    with open(scpp_recommend_output_file, 'w') as scpp_recommend_file:
        scpp_recommend_file.write('SCPPID,ISRC,METHOD\n')
        for scpp_id, audit in full_scppid_audit_report.items():
            for method, isrc in audit['candidate_isrcs'].items():
                if isrc:
                    scpp_recommend_file.write(
                        f'{str(scpp_id)},{isrc},{method}\n')
            
    log.info(f'\'scpp_audit_recommendations_{script_run_at}.csv\' written.')
    
    # Write Invalid ISRC's to CSV
    isrc_invalid_output_file = \
        join(OUTPUT_FILE_DIR, f'isrc_invalid_{script_run_at}.csv')
    
    log.info(f'Writing \'isrc_invalid_{script_run_at}.csv\'.')
    
    with open(isrc_invalid_output_file, 'w') as isrc_invalid_file:
        isrc_invalid_file.write('SCPPID,ISRC,REASON\n')
        for scpp_id, audit in full_scppid_audit_report.items():
            for reason, isrcs in audit['bad_isrcs'].items():
                for isrc in isrcs: 
                    isrc_invalid_file.write(f"{str(scpp_id)},{isrc},{reason}\n")
                    
    log.info(f'\'isrc_invalid_{script_run_at}.csv\' written.')
    
    # Write Mismatched ISRC's to CSV
    mismatched_output_file = \
        join(OUTPUT_FILE_DIR, f'isrc_mismatched_{script_run_at}.csv')
    
    log.info(f'Writing \'isrc_mismatched_{script_run_at}.csv\'.')
    
    with open(mismatched_output_file, 'w') as mismatched_file:
        # Find max number of mismatched isrcs across all SCPPID's
        max_count = \
            max([len(audit['mismatches']) for scppid, audit in full_scppid_audit_report.items()])
            
        # Format a string to write to the file with that many items
        isrc_header = \
            ','.join(['ISRC_'+str(n).zfill(2) for n in range(1, max_count+1)])
            
        mismatched_file.write(f'SCPPID,{isrc_header}\n')
        for scpp_id, audit in full_scppid_audit_report.items():
            if len(audit['mismatches']):
                mismatch_values = \
                    audit['mismatches'] + [' '*(max_count-len(audit['mismatches']))]
                isrc_values = ','.join(mismatch_values)
                mismatched_file.write(f"{str(scpp_id)},{isrc_values}\n")
                
    log.info(f'\'isrc_mismatched_{script_run_at}.csv\' written.')
    
    # Write Processing errors to file
    processing_error_output_file = \
        join(OUTPUT_FILE_DIR, f'processing_errors_{script_run_at}.txt')
        
    log.info(f'Writing \'processing_errors_{script_run_at}.txt\'.')
    
    with open(processing_error_output_file, 'w') as proc_error_file:
        proc_error_file.writelines(s + '\n' for s in processing_errors)

    log.info(f'\'processing_errors_{script_run_at}.txt\' written.')
    
    rds_con.close()
    ar_con.close()

    log.info('Finished.')
    
    ###########
    # DEBUG
    ###########
    # Write SCPPID audit output data structure to file
    # json_output_file = \
    #     join(OUTPUT_FILE_DIR, f'full_scppid_audit_report_{script_run_at}.json')
    
    # log.info(f'Writing \'full_scppid_audit_report_{script_run_at}.json\'.')
    
    # with open(json_output_file, 'w') as f:
    #     json.dump(full_scppid_audit_report, f, indent=2)
    
    # log.info(f'\'full_scppid_audit_report_{script_run_at}.json\' written.')
    ###########
    # END DEBUG
    ###########
    
    ###########
    # DEBUG
    ###########
    # Write full audit output data structure to file
    # json_output_file = \
    #     join(OUTPUT_FILE_DIR, f'full_audit_dict_{script_run_at}.json')
    
    # log.info(f'Writing {json_output_file}.')
    
    # with open(json_output_file, 'w') as f:
    #     json.dump(full_audit_dict, f, indent=2)
    
    # log.info(f'{json_output_file} written.')
    ###########
    # END DEBUG
    ###########
    
        
if __name__ == '__main__':
    # Run main function    
    main()