#!/usr/bin/perl
#=======================================================================
# This script ingests Looker catalog data.  It will create missing
# catalog metadata where possible.
#
# See RSD-985 for more information.
#
# Usage:
#   ./looker_import.pl -f filename -e -d
#
#   -f filename
#      Process the specified Looker file from the local filesystem.
#
#      By default, we'll scan the S3 'orchard-transfers' bucket for
#      Looker CSV files and copy them locally prior to ingesting.
#      The local directory is stored in the kTargetDirectory constant.
#
#   -e Save changes to each client's database.  If omitted then no
#      changes will be made to client databases.
#
#   -d Output debugging information.  By default this is turned off
#      and you'll see minimal output.  For example, to see everything
#      you can use "--debug debug".
#
#   --force  Use this with "-f" to force a specific filename to be
#      processed, regardless of it has been already processed (as
#      recorded in md5registry.json)
#=======================================================================
use strict;
use Getopt::Long;
use IO::File;
use Date::Calc;
use Data::Dumper;
use Digest::MD5 qw(md5_hex);
use utf8;
use JSON;

use Text::CSV_XS;
use File::Basename;
use File::Path qw[make_path];
use Net::Amazon::S3;
use Net::Amazon::S3::Client;
use Net::Amazon::S3::Bucket;
use Time::HiRes qw ( time );

use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date trimspaces clean normalize_upc normalize_isrc clean_name_catalog);
use Common::RSDB;
use Common::RSApp;
use Common::Assert;
use Common::Amazon::Config;
use Common::DB::Item::File;
use Common::Log;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Artist;
use RPS::DB::Item::Label;
use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::Master;
use RPS::DB::Item::Song;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductTrack;

use RPS::DB::Item::Product;
use RPS::DB::Item::MediaType;

use constant kErrorMultipleAlbumUPC => 1;
use constant kErrorTrackNotFound    => 2;
use constant kErrorTrackNotUnique   => 3;
use constant kErrorISRCNotUnique    => 4;
use constant kErrorNonExecMode      => 5;
use constant kErrorMissingUPC       => 6;

binmode STDOUT, ':utf8';
binmode STDERR, ':utf8';

# This is where we'll download the S3 files to so that we can process them
#
use constant kTargetDirectory => '/app/data/looker_import';

# Parse the command-line.
#
my %args;
parseCommandLine( \%args );

my $inputFile   = $args{file};
my $_debug      = $args{debug};
my $execMode    = $args{execMode};
my $gForce      = $args{force};
my $gDebugLevel = lc $args{debugLevel};

my $gDebug = scalar @$_debug;

Log->init( );
Log->setLogLevel( $gDebugLevel ) if ( $gDebugLevel );

Log->increaseLogLevel( $gDebug ) if ( $gDebug );

$| = 1;

# Instantiate the application singleton object.
#
my $appSingleton = Common::RSApp->new( clientID => 0 );
my $dbo          = Common::RSApp::GetClientDB();
my $cdbo         = Common::RSApp::GetCommonDB();

# RSCOMMON.orchard_catalog maps orchard accounts to RPS clientIDs.
# A status of '1' means that we will process any incoming data
# from that account, otherwise '0' means to not process data
# (e.g., 'unknown vendorid' exception).
#
my %clientIDMap;
my $sql = "SELECT account_id, client_id, status FROM orchard_catalog";
my $sth = $cdbo->DoCmd($sql);
while ( my ( $accountID, $clientID, $status ) = $sth->fetchrow_array() ) {
    $clientIDMap{$accountID} = join( '-', $clientID, $status );
}

# gHeaderShown (flag): set if we've output the header row to the exception file
#
my $gHeaderShown;

# gBlockMap: hash of client-specific UPC and ISRC (optional) that we should block from processing
#
my %gBlockMap = ();

# Initialize gBlockMap
#
my $blockFile = kTargetDirectory .'/blocklist.txt';
_loadblockfile( $blockFile, \%gBlockMap );


# We'll fill some caches as we process the Looker file(s)
#
my %artistMap = ();    # cache of client artist IDs
my %labelMap  = ();    # cache of client label IDs

# Setup connection to S3
#
my ( $access, $secret, $region ) = get_aws_creds('orch-transfer');    # See Common/Amazon/Config.yml

my $s3 = Net::Amazon::S3->new(
    aws_access_key_id     => $access,
    aws_secret_access_key => $secret,
    retry                 => 1,
);

my $response;

# Array of files to be ingested.  We'll scan the S3 bucket
# containing the Looker files and check if we've already
# processed the file.  If not, we'll copy the file locally
# and then add it to @files.
#
my @files;

if ($inputFile) {

    print "### Using user-specified file ...\n";

    my $filename         = basename $inputFile;
    my $targetpath       = kTargetDirectory . '/' . $filename;
    my $compressedTarget = $targetpath . '.gz';

    if ( -e $compressedTarget ) {

        # Use existing compressed target if available
        #
        `gzip -df $compressedTarget`;

    } elsif ( !-e $targetpath ) {

        # Force ingestion from target directory
        #
        `cp $inputFile $targetpath`;
    }

    my $file = Common::DB::Item::File->Lookup( file_name => $filename );

    if ( !$file ) {
        $file = Common::DB::Item::File->Create(
            file_name => $filename,
            type_id   => Common::DB::Item::File::kFileTypeLooker,
        );
        $file->save;
    }

    push @files, { path => $targetpath, file_name => $filename, file_id => $file->file_id };

} else {

    # The Looker files are kept here
    #
    my $bucket = $s3->bucket('orchard-transfers');

    print "### Using S3 'orchard-transfers' bucket ...\n";

    # Get everything in the 'orchard-transfers' bucket
    #
    $response = $bucket->list_all or die $s3->err . ": " . $s3->errstr;

    # The only way to detect if there's an issue is if no keys are present.  If
    # that's the case then assume that our credentials (Common/Amazon/Config.yml)
    # are out of date.
    if ( !exists $response->{keys} ) {
        die("NO BUCKET KEYS (FILES) FOUND -- CHECK YOUR BUCKET CREDENTIALS !!!");
#    } else {
#        print "   ### Found ". @{$response->{keys}} . " key(s) ...\n";
    }

    for my $key ( @{ $response->{keys} } ) {
        my $name = $key->{key};

        # We only want to look at CSV files in the bucket...

        my $filename = basename $name;

        if ( $filename =~ /(.+)\.csv$/i ) {

            # Check if we've already downloaded the filename.  Copy it from S3 if we haven't.

            my $file = Common::DB::Item::File->Lookup( file_name => $filename );

            if ( !$file ) {
                $file = Common::DB::Item::File->Create(
                    file_name => $filename,
                    type_id   => Common::DB::Item::File::kFileTypeLooker,
                );
                $file->save;

                unless ( -d kTargetDirectory ) {
                    make_path(kTargetDirectory);
                }

                my $targetpath       = kTargetDirectory . '/' . $filename;
                my $compressedTarget = $targetpath . '.gz';

                if ( -e $compressedTarget ) {

                    # We shouldn't have a gzipped version of the Looker file
                    # without an entry in RSCOMMON.file.
                    # If there's a compressed version of a Looker file sitting
                    # in the target directory, then we'll use it instead of
                    # grabbing it from S3.

                    `gzip -d $compressedTarget`;

                } elsif ( !-e $targetpath ) {

                    print "  ### Downloading from S3 to $targetpath\n";
                    $response = $bucket->get_key_filename( $name, 'GET', $targetpath );

                }

                $file->file_md5sum( Common::Util::md5sum($targetpath) );
                $file->save;

                push @files, { path => $targetpath, file_name => $filename, file_id => $file->file_id };

            } else {
                print "File '$filename' already processed ... skipping\n";
            }

        }

    }    # bucket loop
}

undef $appSingleton;

chdir(kTargetDirectory);

my $csv = Text::CSV_XS->new( {
        binary       => 1,
        always_quote => 1,
        auto_diag    => 0,
        eol          => "\n"
    }
) or die "Cannot use CSV: " . Text::CSV_XS->error_diag();

foreach my $f (@files) {
    my $file   = $f->{file_name};
    my $fileID = $f->{file_id};
    print "#### Processing file '$file' ...\n";

    my $st = _processLookerFile($f);

    undef $gHeaderShown;    # reset for next file
}

#####################
#
# --- Subroutines ---
#
#####################


sub _loadblockfile {
    my ( $blockFile, $bmap )  = @_;
    if ( -e $blockFile ) {
        open OFILE, "<", $blockFile;
        my $row=0;
        while ( my $line = <OFILE> ) {
            chomp $line;
            if ( $line !~ /^#/ && $line !~ /^$/ ) {
                my( $accountID, $upc, $isrc ) = split(" ", $line);  # space means split on whitespace, not just space
                if ( ! exists $bmap->{$accountID} ) {
                    $bmap->{$accountID}{$upc} = ();
                }

                if ( $isrc ) {
                    $bmap->{$accountID}{$upc}{$isrc} = 1;
                }
            }
            $row++;
        }
        close OFILE;
    } else {
        die( "ERROR: $blockFile not found !!!" );
    }
} # _loadblockfile

sub _isBlocked {
    my (%args) = @_;

    my $accountID = $args{accountID};
    my $upc       = $args{upc};
    my $isrc      = $args{isrc};

    my $blocked;

    if ( exists $gBlockMap{$accountID} ) {
        if ( exists $gBlockMap{$accountID}->{$upc} ) {
            my $isrcMapRef = $gBlockMap{$accountID}->{$upc};
            my $nkeys      = (keys %$isrcMapRef);
            if ( $nkeys == 0 ) {
                $blocked=1;
            } else {
                if ( exists $isrcMapRef->{$isrc} ) {
                    $blocked=1;
                }
            }
        }
    }
    return $blocked;
} # _isBlocked

sub _processLookerFile {

    my $f             = shift;
    my $inputFile     = $f->{file_name};
    my $inputFilePath = $f->{path};
    my $fileID        = $f->{file_id};

    my $start = Time::HiRes::gettimeofday();

    # name of the file containing exceptions; it'll be based on the input filename
    my $errFile;

    # keeps track of exceptions in this file; we'll output a summary at the end
    my %excount;

    if ( $inputFile =~ /(.+)\.csv$/i ) {

        # If we're unable to successfully process a Looker line, the line will be
        # output to an exception file whose name is the same as the Looker file,
        # but with "__EXCEPTIONS" appended to the filename.  An extra column will
        # be appended to the file containing a description of why the line was not
        # processed.
        #
        $errFile = $1 . '__EXCEPTIONS' . '.csv';
    } else {
        die("ERROR: Unable to create exceptions file for '$inputFile'");
    }

    # processedMap contains the MD5 hashes that we've already seen.  It is used
    # to control which lines of the current report we will process.  We read/write
    # the hash using a file.
    my $hashFile = 'md5registry.json';
    my %processedMap;
    open OFILE, "<", $hashFile;
    my $data = <OFILE> || '{}';
    my $href = decode_json($data);
    %processedMap = %$href;
    close OFILE;

    open my $efh, ">:encoding(utf8)", "$errFile"   or die "$errFile $!";
    open my $fh,  "<:encoding(utf8)", "$inputFile" or die "$inputFile: $!";

    my $linenum = 0;  # Looker report line number

    my $ignored = 0;  # line(s) ignored due to existing MD5 match

    my $blocked = 0;  # line(s) blocked due to explicit UPC/ISRC blacklist

    my %columns;      # map of column header names to index value
    my @colarray;     # array of header names
    my $errColumn;    # the index of the exception report error column

    # Loop over each line from the Looker file
    #
    while ( my $record = <$fh> ) {

        my $row;
        my @fields;
        if ( $csv->parse($record) ) {
            @fields = $csv->fields();
        } else {
            die("Unable to parse line: $record");
        }

        if ( $linenum == 0 ) {
            @colarray = @fields;
            my $ic = 0;
            foreach my $c (@fields) {
                $columns{$c} = $ic;    # record the index for each column header
                $ic++;
            }
            $errColumn = $ic;
        }

        my $digest = md5_hex( utf8::is_utf8($record) ? Encode::encode_utf8($record) : $record );

        my $vendorID         = _getByFieldName( \@fields, \%columns, 'Vendor Vendor ID' );
        my $vendorName       = _getByFieldName( \@fields, \%columns, 'Vendor Vendor Name' );
        my $artistName       = _getByFieldName( \@fields, \%columns, 'Artist Info Name' );
        my $releaseName      = _getByFieldName( \@fields, \%columns, 'Release Release Name' );
        my $releaseUpc       = _getByFieldName( \@fields, \%columns, 'Release Display Upc' );
        my $trackName        = _getByFieldName( \@fields, \%columns, 'Track Track Name' );
        my $trackArtist      = _getByFieldName( \@fields, \%columns, 'Track Artist Track Artist (Performer)' );
        my $isrc             = _getByFieldName( \@fields, \%columns, 'Track ISRC' );
        my $trackNo          = _getByFieldName( \@fields, \%columns, 'Track Track Number' );
        my $trackMin         = _getByFieldName( \@fields, \%columns, 'Track Length Minute' );
        my $trackSec         = _getByFieldName( \@fields, \%columns, 'Track Length Seconds' );
        my $trackType        = _getByFieldName( \@fields, \%columns, 'Track Track Type' );
        my $notReadyForDist  = _getByFieldName( \@fields, \%columns, 'Release Is Not for Distribution? (Yes / No)' );
        my $releaseDeletions = _getByFieldName( \@fields, \%columns, 'Release Deletions' );
        my $contextType      = _getByFieldName( \@fields, \%columns, 'Distribution Format Context Type' );
        my $mediaFormat      = _getByFieldName( \@fields, \%columns, 'Distribution Format Media Format' );
        my $releaseImprint   = _getByFieldName( \@fields, \%columns, 'Release Imprint' );
        my $releaseDate      = _getByFieldName( \@fields, \%columns, 'Release Release Date' );
        my $projectCode      = _getByFieldName( \@fields, \%columns, 'Project Project Code' );

        if ( $vendorID eq 'Vendor Vendor ID' ) {    # skip header
            next;
        }

        my $errmsg;    # set if there are any errors with this Looker line

        if ( !$gForce && exists $processedMap{$digest} ) {      # skip lines we've already processed
            $ignored++;
            next;
        }

        # Block any lines with blacklisted UPC/ISRC
        #
        if ( _isBlocked( accountID => $vendorID, upc => $releaseUpc, isrc => $isrc ) ) {
            Log->debug( "BLOCKED: vendorID($vendorID) upc($releaseUpc) isrc($isrc)" );
            ++$excount{blocked};
            my $e   = "BLOCKED";
            $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
            $blocked++;
        }

        if ( '' eq $releaseImprint ) {
            ++$excount{missing_release_imprint};
            my $e = "MISSING RELEASE IMPRINT";
            $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
            #Log->warn("$e");
        }

        my $releaseNameClean = clean_name_catalog($releaseName);

        my $catalogNumber = ( $projectCode && '' ne $projectCode ) ? $projectCode : $releaseUpc;    # RSP-532

        # Check if the Workstation vendorID maps to a RPS clientID.
        # If we do have a mapping, We also check if the feed is 'active'.
        #
        my $clientID;
        my $clientStatus;
        if ( !exists $clientIDMap{$vendorID} ) {

            # ERROR: unknown vendorID
            ++$excount{unknown_vendorid};
            my $e = "UNKNOWN VENDORID '$vendorID'";
            $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
            #Log->warn("$e");

        } else {
            ( $clientID, $clientStatus ) = split( '-', $clientIDMap{$vendorID} );
            if ( 0 == $clientStatus ) {

                # ERROR:  inactive vendorID
                ++$excount{inactive_vendorid};
                my $e = "INACTIVE CLIENTID/VENDORID $clientID/$vendorID";
                $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
                #Log->warn("$e");
            }
        }

        my $prefix = "_processLookerFile(client=$clientID):";

        # Check if the media format can be mapped to an RPS product and media type
        #
        my ( $rsProductTypeID, $rsMediaType ) = _getProductAndMediaTypeID($mediaFormat, $trackType);

        if ( !$rsProductTypeID ) {

            # ERROR: invalid media format detected
            my $e = "UNKNOWN MEDIA FORMAT";
            ++$excount{unknown_media_format};
            $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
        }

        if ( (!$trackName || '' eq $trackName) && $isrc ) {
            my $e = "NO TRACK TITLE";
            ++$excount{no_track_title};
            $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
        }

        # Make sure the release date is in the correct format
        #
        if ( $releaseDate && $releaseDate !~ /\d{4}-\d{2}-\d{2}/ ) {
            $releaseDate = normalize_date($releaseDate);

            if ( $releaseDate !~ /\d{4}-\d{2}-\d{2}/ ) {
                my $e = "INVALID DATE";
                ++$excount{invalid_date};
                $errmsg = ($errmsg) ? "$errmsg; $e" : $e;
            }
        }

        # Skip line if there are errors
        #
        if ($errmsg) {
            _logException( msg => $errmsg, fh => $efh, rowdata => \@fields );
            next;    # we're done with this line
        }

        my $appSingleton = Common::RSApp->new( clientID => $clientID );
        my $dbo = Common::RSApp::GetClientDB();

        my $duration = ( $trackMin * 60 ) + $trackSec;

        # createTrackElements (flag): set if the Looker data contains a track
        # name and ISRC.  If the current line doesn't have either, then we'll
        # assume that it's an album-level product and will skip creating anything
        # track-related.
        #
        my $createTrackElements = ( $trackName ) ? 1 : 0;

        # The following will get or create the RPS album with the supplied Looker data
        # If the album already exists, then we may update the label, artist, title and/or
        # catalog number.
        # If the product already exists, then we may update the release date and/or
        # product title.
        #
        my $albumStatus = _getOrCreateAlbum(
            release_imprint => $releaseImprint,
            album_artist    => $artistName,
            album_title     => $releaseName,
            catalog_number  => $catalogNumber,
            upc             => $releaseUpc,
            release_date    => $releaseDate,
            product_type_id => $rsProductTypeID,
            media_type      => $rsMediaType,
            dbo             => $dbo,
            client_id       => $clientID,
            artist_cache    => \%artistMap,
            label_cache     => \%labelMap,
        );
        my $albumID = $albumStatus->{album_id};
        my $err     = $albumStatus->{error};
        my $code    = $albumStatus->{code};

        # Generate exception if error

        if ($err) {
            Log->warn( "$prefix  album error detected albumID($albumID) err($err)" ) if ( $code && $code != kErrorNonExecMode );
            if ( $code == kErrorMultipleAlbumUPC ) {
                ++$excount{multi_album_upc};
                _logException( msg => "MULTI-ALBUM UPC", fh => $efh, rowdata => \@fields );
                next;                                                           # we're done with this line
            } elsif ( $code == kErrorNonExecMode ) {
                ++$excount{non_exec};
                _logException( msg => "NON-EXEC MODE", fh => $efh, rowdata => \@fields );
                next;                                                           # we're done with this line
            } elsif ( $code == kErrorMissingUPC ) {
                ++$excount{missing_upc};
                _logException( msg => "MISSING UPC", fh => $efh, rowdata => \@fields );
                next;                                                           # we're done with this line
            } else {
                # default catch-all
                ++$excount{unknown_exception};
                my $msg = "Unknown exception: $code";
                _logException( msg => $msg, fh => $efh, rowdata => \@fields );
                next;    # we're done with this line
            }
        }

        if ($createTrackElements) {
            my $trackStatus = _getOrCreateTrack(
                album_status    => $albumStatus,
                track_artist    => $trackArtist,
                track_title     => $trackName,
                isrc            => $isrc,
                track_number    => $trackNo,
                duration        => $duration,
                product_type_id => $rsProductTypeID,
                media_type      => $rsMediaType,
                dbo             => $dbo,
                client_id       => $clientID,
                artist_cache    => \%artistMap,
                upc             => $releaseUpc,
                release_date    => $releaseDate,
            );

            # Generate exception if error

            my $err  = $trackStatus->{error};
            my $code = $trackStatus->{code};

            if ($err) {
                my $errClean = clean $err;
                ++$excount{$errClean};
                Log->warn( "$prefix  track error detected: code($code) err($err) errClean($errClean)" );
                _logException( msg => $err, fh => $efh, rowdata => \@fields );
                next; # we're done with this line
            }
        }

        # The line has been successfully processed
        #
        $processedMap{$digest} = 1;

    } continue {
        $linenum++;
        undef $appSingleton;
    }

    close $efh;
    close $fh;

    # Write the processedMap back to disk
    #
    my $data = encode_json( \%processedMap );
    open OFILE, ">", $hashFile;
    print OFILE $data;
    close OFILE;

    # Calculate statistics
    #
    my $end       = Time::HiRes::gettimeofday();
    my $totalTime = $end - $start;

    my $totalExceptions = 0;
    foreach my $c ( keys %excount ) {
        my $v = $excount{$c};
        printf( "%30s %6d\n", $c, $v );
        $totalExceptions += $v;
    }
    printf( "%30s %6d\n", "TOTAL EXCEPTIONS", $totalExceptions );

    printf( "  >> Processed %d line(s) (%d ignored, %d blocked) with %d exceptions(s) in %.2f second(s)\n",
        $linenum, $ignored, $blocked, $totalExceptions, $totalTime );

    # Record the file results
    #
    my $appSingleton = Common::RSApp->new( clientID => 0 );
    my $file = Common::DB::Item::File->Lookup( file_id => $fileID );
    $file->records($linenum);
    $file->total_exceptions($totalExceptions);
    $file->file_status(Common::DB::Item::File::STATUS_DONE);
    $file->total_time($totalTime);
    $file->save;
    undef $appSingleton;

    # Compress the input and exception files
    #
    print "  >> Zipping inputFile($inputFile)\n";
    `gzip $inputFile`;

    print "  >> Zipping errFile($errFile)\n";
    `gzip -f $errFile`;

}    # _processLookerFile

sub _getByFieldName {
    my $aref = shift;
    my $href = shift;
    my $name = shift;
    return ( exists $href->{$name} ) ? @$aref[ $href->{$name} ] : undef;
}

# _logException( msg => $errmsg, fh => $efh, rowdata => \@fields );
sub _logException {
    my (%args)  = @_;
    my $msg     = $args{msg};
    my $efh     = $args{fh};
    my $rowdata = $args{rowdata};

    # Note: 'gHeaderShown' and 'csv' are global.
    printHeader($efh) if ( !$gHeaderShown++ );
    push @$rowdata, $msg;
    $csv->print( $efh, $rowdata );
}

#======================================================================
# _getOrCreateAlbum will create or retrieve an RPS album.  It will also
# create or retrieve an album product.
# A hash is returned containing the following:
# If the album and product are successfully created/retrieved:
#   album_id    => albumID
#   new_album   => 1 if album created, 0 if album_id is existing
#   product_id  => productID of album product
#   new_product => 1 if product created, 0 if product_id is existing
#   artist_id   => the artistID associated with the album
#
# If an error is encountered:
#   error => error message indicating what went wrong
#   code  => error code
#======================================================================
sub _getOrCreateAlbum {
    my (%args)         = @_;
    my $releaseImprint = $args{release_imprint};    # RPS label name
    my $albumArtist    = $args{album_artist};
    my $releaseName    = $args{album_title};
    my $catalogNumber  = $args{catalog_number};
    my $releaseUpc     = $args{upc};
    my $releaseDate    = $args{release_date};
    my $productTypeID  = $args{product_type_id};
    my $mediaType      = $args{media_type};
    my $dbo            = $args{dbo};
    my $clientID       = $args{client_id};
    my $artistMap      = $args{artist_cache};
    my $labelMap       = $args{label_cache};

    my %status;

    my $releaseNameClean = clean_name_catalog($releaseName);

    my $prefix = "_getOrCreateAlbum(client=$clientID):";

    Log->info( "### $prefix  label($releaseImprint) upc($releaseUpc) pType($productTypeID)" );

    # Does the client have the UPC in their catalog?
    #
    my $productID;
    my $assetID;
    my $albumID;

    if ( !$releaseUpc || '' eq $releaseUpc ) {
        $status{error} = "Missing UPC";
        $status{code}  = kErrorMissingUPC;
        return \%status;
    }

    my $sql =
      "SELECT product_id, release_date, product_type_id, asset_id " . "FROM product WHERE upc_ean = $releaseUpc AND product_type_id != 4";
    my $sth = $dbo->DoCmd($sql);
    if ( $sth->rows == 0 ) {
        Log->debug( "$prefix  UPC '$releaseUpc' not found !!!" );

        if ( !$execMode ) {
            $status{error} = "Non-exec mode";
            $status{code}  = kErrorNonExecMode;
            return \%status;
        }

        # UPC not found, check if the catalog number exists.  If so we'll use the
        # existing album, otherwise we'll create a new album.  In either case, add
        # a new album product to the album.
        #
        if ( $catalogNumber ) {
            my $sql2 =
              "SELECT album_id, artist_id FROM album WHERE catalog_number = " . $dbo->DBQuote($catalogNumber);
            my $sth2 = $dbo->DoCmd($sql2);
            if ( $sth2->rows >= 1 ) {
                my ($_albumID, $_artistID) = $sth2->fetchrow_array();  # grab first album that matches
                $albumID = $_albumID;
                $status{album_id}  = $albumID;
                $status{new_album} = 0;
                $status{artist_id} = $_artistID;

                Log->debug( "$prefix  UPC '$releaseUpc' not found, but found existing "
                    . "albumID $albumID using catalogNumber($catalogNumber) !!!" );
            }
        }

        if ( !$albumID ) {
            my $labelID  = _getOrCreateLabel( $clientID, $releaseImprint, $labelMap );
            my $artistID = _getOrCreateArtist( $clientID, $albumArtist, $artistMap );

            my %albumArgs = (
                title          => $releaseName,
                title_clean    => $releaseNameClean,
                artist_id      => $artistID,
                label_id       => $labelID,
                catalog_number => $catalogNumber,
                status         => 1, # active
            );
            my $album = RPS::DB::Item::Album->Create(%albumArgs);
            $album->save;
            $albumID = $album->album_id;
            Log->debug( "$prefix  Created album $albumID : " . Dumper( \%albumArgs ) );

            $status{album_id}  = $albumID;
            $status{new_album} = 1;
            $status{artist_id} = $artistID;
        }

        # Create new album product
        my $productID;
        my %pArgs = (
            asset_id          => $albumID,
            title             => $releaseName,
            title_clean       => $releaseNameClean,
            upc_ean           => $releaseUpc,
            release_date      => $releaseDate,
            product_status_id => RPS::DB::Item::Product::kProductStatusActive,
            product_type_id   => $productTypeID,
        );
        $productID = _createProduct( \%pArgs );

        Log->debug( "$prefix  Created product $productID for UPC '$releaseUpc': " . Dumper( \%pArgs ) );

        $status{product_id}  = $productID;
        $status{new_product} = 1;

    } else {

        Log->debug( "$prefix  UPC '$releaseUpc' exists" );

        if ( !$execMode ) {
            $status{error} = "Non-exec mode";
            $status{code}  = kErrorNonExecMode;
            return \%status;
        }

        # UPC exists -- create new product if needed.
        # Also, check if we need to update any album data.
        my %assetMap;
        my $targetProductID;
        my $targetReleaseDate;
        while ( my ( $_productID, $_releaseDate, $_typeID, $_assetID ) = $sth->fetchrow_array() ) {
            Log->debug( "$prefix  UPC '$releaseUpc' found --> productID($_productID) typeID($_typeID) assetID($_assetID)" );

            if ( !$targetProductID && $_typeID == $productTypeID ) {
                $targetProductID   = $_productID;
                $targetReleaseDate = $_releaseDate;
            }
            $assetMap{$_assetID} = 1;
            $assetID = $_assetID;
        }

        # Generate exception if UPC appears on multiple albums
        if ( keys %assetMap > 1 ) {
            $status{error} = "UPC '$releaseUpc' appears on multiple albums: " . join( ", ", ( keys %assetMap ) );
            $status{code} = kErrorMultipleAlbumUPC;
            return \%status;
        }

        $albumID = $assetID;

        # If targetProductID is not set, then we'll need to create a product with the UPC
        if ( !$targetProductID ) {

            # ... Create album-level product ...
            my %pArgs = (
                asset_id          => $assetID,
                title             => $releaseName,
                title_clean       => $releaseNameClean,
                upc_ean           => $releaseUpc,
                release_date      => $releaseDate,
                product_status_id => RPS::DB::Item::Product::kProductStatusActive,
                product_type_id   => $productTypeID,
            );
            my $productID = _createProduct( \%pArgs );
            Log->debug( "$prefix  Created product $productID with existing UPC '$releaseUpc': " . Dumper( \%pArgs ) );

            $status{product_id}  = $productID;
            $status{new_product} = 1;

        } else {

            # Check for product update
            my $product = RPS::DB::Item::Product->Lookup( product_id => $targetProductID );

            # We'll write out information received via Looker; only fields that differ
            # from the live data will be flushed to DB when we call save()
            #

            if ( $releaseDate ne $targetReleaseDate ) {
                Log->debug( "$prefix  product $targetProductID release_date $targetReleaseDate -> $releaseDate" );
                $product->release_date($releaseDate);
            }
            if ( $product->title ne $releaseName ) {
                Log->debug( "$prefix  product $targetProductID title " . $product->title . " -> $releaseName" );
                $product->title($releaseName);
                $product->title_clean($releaseNameClean);
            }

            Log->debug( "$prefix  Updating productID($targetProductID) (dirty=" . $product->isDirty . ")" );
            _showDirty( $product );
            $product->save;

            $status{product_id}  = $targetProductID;
            $status{new_product} = 0;
        }

        $status{album_id}  = $assetID;
        $status{new_album} = 0;
    } # existing UPC

    if ( $status{new_album} ==  0 ) {
        # Check for album updates
        my $album = RPS::DB::Item::Album->Lookup( album_id => $albumID );
        my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $album->artist_id );
        my $rsAlbumArtistID = $album->artist_id;
        $status{artist_id} = $rsAlbumArtistID;

        if ( $artist->name ne $albumArtist ) {
            $rsAlbumArtistID = _getOrCreateArtist( $clientID, $albumArtist, $artistMap );
            $album->artist_id( $rsAlbumArtistID );
        }

        my $label = RPS::DB::Item::Label->Lookup( label_id => $album->label_id );
        if ( $label->label_name ne $releaseImprint ) {
            my $labelID = _getOrCreateLabel( $clientID, $releaseImprint, $labelMap );
            $album->label_id( $labelID );
        }

        # Note: don't update the catalog number or title if we're using an existing album (new_album == 0)

        _showDirty( $album );
        Log->debug( "$prefix  Updating albumID $albumID (dirty=" . $album->isDirty . ")\n" );
        $album->save;
    } # existing album update

    return \%status;
}    # _getOrCreateAlbum

#======================================================================
# _getOrCreateTrack will create or retrieve an RPS track.  It will also
# create or retrieve a track product.
# A hash is returned containing the following:
# If the track and product are successfully created/retrieved:
#   track_id    => trackID
#   new_track   => 1 if track created, 0 if track_id is existing
#   product_id  => productID of track product
#   new_product => 1 if product created, 0 if product_id is existing
#
# If an error is encountered:
#   error => error message indicating what went wrong
#   code  => error code value
#======================================================================
sub _getOrCreateTrack {
    my (%args)        = @_;
    my $albumStatus   = $args{album_status};
    my $trackArtist   = $args{track_artist};
    my $trackName     = $args{track_title};
    my $isrc          = $args{isrc};
    my $trackNo       = $args{track_number};
    my $duration      = $args{duration};
    my $productTypeID = $args{product_type_id};    # album product type
    my $rsMediaType   = $args{media_type};
    my $clientID      = $args{client_id};
    my $dbo           = $args{dbo};
    my $artistMap     = $args{artist_cache};
    my $releaseUpc    = $args{upc};
    my $releaseDate   = $args{release_date};

    my %status = ();

    my $albumID         = $albumStatus->{album_id};
    my $albumArtistID   = $albumStatus->{artist_id};
    my $parentProductID = $albumStatus->{product_id};

    # flags: are the album and album product new (1) or existing (0)?
    my $newParentProduct = $albumStatus->{new_product};
    my $newAlbum         = $albumStatus->{new_album};

    my $prefix = "  _getOrCreateTrack(client=$clientID):";

    Log->info("$prefix  albumID($albumID) newAlbum($newAlbum) pProductID($parentProductID) newProduct($newParentProduct)");
    my $trackID;

    # Find or create track if artist defined, otherwise use album artist
    #
    my $trackArtistID = ( $trackArtist && '' ne $trackArtist ) ? _getOrCreateArtist( $clientID, $trackArtist, $artistMap ) : $albumArtistID;

    if ($newAlbum) {
        # Create a new track
        $trackID = _createTrack(
            track_title  => $trackName,
            isrc         => $isrc,
            duration     => $duration,
            track_number => $trackNo,
            media_type   => $rsMediaType,
            artist_id    => $trackArtistID,
            album_id     => $albumID,
            client_id    => $clientID,
        );
        $status{track_id}  = $trackID;
        $status{new_track} = 1

    } else {
        # Search for track on existing album, create if not found, update otherwise
        #
        my $result = _searchTrack(
            track_title => $trackName,
            isrc        => $isrc,
            media_type  => $rsMediaType,
            album_id    => $albumID,
            client_id   => $clientID,
            dbo         => $dbo,
        );
        my $err = ( exists $result->{code} ) ? $result->{code} : undef;

        if ($err) {

            if ( $err == kErrorTrackNotFound ) {

                $trackID = _createTrack(
                    track_title  => $trackName,
                    isrc         => $isrc,
                    duration     => $duration,
                    track_number => $trackNo,
                    media_type   => $rsMediaType,
                    artist_id    => $trackArtistID,
                    album_id     => $albumID,
                    client_id    => $clientID,
                );
                $status{track_id}  = $trackID;
                $status{new_track} = 1;

                # When we add a track to an existing album, we'll use the Looker track number
                # instead of trying to renumber the tracks on the album.
#                RPS::DB::Item::Album->RenumberTracks($albumID) if ($execMode);

            } else {

                # propagate error back to caller
                $status{code}  = $result->{code};
                $status{error} = $result->{error};
                return \%status;
            }

        } else {

            # No error; found a track
            $trackID           = $result->{track_id};
            $status{track_id}  = $trackID;
            $status{new_track} = 0;

            # Check for track updates
            # From RSD-537, we need to check for updates to the following:
            #  Track Track Name
            #  Track Artist (Performer)
            #  Track Track Number
            #  Track Length Minute
            #  Track Length Seconds

            my $track = RPS::DB::Item::Track->Lookup( track_id => $trackID );
            my $master = RPS::DB::Item::Master->Lookup( master_id => $track->master_id );
            my $song = RPS::DB::Item::Song->Lookup( song_id => $master->song_id );

            # We'll write out information received via Looker; only fields that differ
            # from the live data will be flushed to DB when we call save()
            #


            # Update artist info if changed
            if ( $track->artist_id != $trackArtistID ) {
                Log->debug( "$prefix  trackID($trackID) artist_id " . $track->artist_id . " --> $trackArtistID" );
                $track->artist_id( $trackArtistID );
                $master->artist_id( $trackArtistID );
            }

            # Update title info on track and master if changed
            if ( $track->title ne $trackName ) {
                Log->debug( "$prefix  trackID($trackID) title " . $track->title . " --> $trackName" );
                $track->title( $trackName );
                $track->title_clean( clean_name_catalog($trackName) );
                $master->title( $trackName );
                $song->title( $trackName );
            }

            # Update track order (digital only) if changed
            if ( $track->track_order != $trackNo &&
                 $productTypeID == RPS::DB::Item::Product::kProductTypeDigital ) {
                Log->debug( "$prefix  trackID($trackID) track_order " . $track->track_order . " --> $trackNo" );
                $track->track_order( $trackNo );
            }

            # Update master duration if changed
            if ( 0 != $duration && $master->duration != $duration ) {
                Log->debug( "$prefix  masterID(". $master->master_id . ") duration " . $master->duration . " --> $duration" );
                $master->duration( $duration );
            }

            # Update ISRC only if it's blank
            if ( '' eq $master->isrc && $isrc ) {
                if ( $master->isrc ) {
                    Log->debug( "$prefix  masterID(". $master->master_id . ") existing isrc ". $master->isrc . " --> $isrc" );
                } else {
                    Log->debug( "$prefix  masterID(". $master->master_id . ") blank isrc --> $isrc" );
                }
                $master->isrc( $isrc );
            }

            _showDirty($track);
            _showDirty($master);
            _showDirty($song);

            if ($execMode) {
                Log->debug( "$prefix  Updating trackID($trackID)");
                $track->save;
                $master->save;
                $song->save;
            } else {
                Log->debug( "$prefix  trackID($trackID) Skipped update");
            }
        }    # existing track
    }    # existing album

    assert($trackID);

    # There are two reasons we'd want to create a new product track:
    # - we just created a new album product; or
    # - no product track exists for the existing album product

    my $pt;

    # Check if we have a product track for an existing track (new_track=0)
    # on an existing parent product.  Note that we don't have to check
    # for a PT if we just created a new track.

    if ( !$newParentProduct && $status{new_track} == 0 ) {
        $pt = RPS::DB::Item::ProductTrack->Lookup(
            track_id   => $trackID,
            product_id => $parentProductID
        );

        # Update product track order (digital only) if changed
        if ( $pt &&
             $pt->disc_track != $trackNo &&
             $productTypeID == RPS::DB::Item::Product::kProductTypeDigital ) {
            Log->debug( "$prefix  trackID($trackID) disc_track " . $pt->disc_track . " --> $trackNo" );
            $pt->disc_track( $trackNo );
            $pt->save if ( $execMode ) ;
        }

        # Update the DT too
        if ( $productTypeID == RPS::DB::Item::Product::kProductTypeDigital ) {
            my $dt = RPS::DB::Item::Product->Lookup(
                asset_id          => $trackID,
                parent_product_id => $parentProductID,
                product_type_id   => RPS::DB::Item::Product::kProductTypeDigitalTrack,
            );
            if ( $dt ) {
                $dt->release_date($releaseDate);
                $dt->save if ( $execMode );
                Log->debug( "$prefix  Updated release_date DT productID ". $dt->product_id );
            } else {
                Log->error( "$prefix  No DT found for trackID($trackID) parent($parentProductID)" );
            }
        }
    }

    if ($execMode) {
        if ( $newParentProduct || !$pt ) {

            # attach track to album product, create DT if album product is DA
            my %ptArgs = (
                product_id  => $parentProductID,
                track_id    => $trackID,
                disc_number => 1,
                disc_track  => $trackNo,
            );
            my $pTrack = RPS::DB::Item::ProductTrack->Create(%ptArgs);
            $pTrack->save;
            Log->debug( "$prefix  Created product_track "
                  . $pTrack->product_track_id . " : "
                  . Dumper( \%ptArgs ) );

            if ( $productTypeID == RPS::DB::Item::Product::kProductTypeDigital ) {

                # ... Create DT product ...
                my %dtArgs = (
                    parent_product_id => $parentProductID,
                    asset_id          => $trackID,
                    upc_ean           => $releaseUpc,
                    release_date      => $releaseDate,
                    product_status_id => RPS::DB::Item::Product::kProductStatusActive,
                    product_type_id   => RPS::DB::Item::Product::kProductTypeDigitalTrack,
                );
                my $dtProductID = _createProduct( \%dtArgs );
                Log->debug( "$prefix  Existing UPC - Created DT product $dtProductID : " . Dumper( \%dtArgs ) );
            }
        } else {
            Log->debug( "$prefix  Skipped product creation with track $trackID" );
        }
    } else {
        $status{code}  = kErrorNonExecMode;
        $status{error} = "Non-exec mode";
    }

    return \%status;
}    # _getOrCreateTrack


# _showDirty: show which RPS field(s) have been modified
sub _showDirty {
    my $o      = shift;
    my $config = $o->_config();
    return if ( '' eq $gDebugLevel || 'debug' ne $gDebugLevel );
    Log->debug( "### _showDirty(". ref($o) . ")" );
    foreach my $fieldName ( keys %$config ) {
        if ( $o->{_dirty}{$fieldName} ) {
            Log->debug( "  field '$fieldName' is dirty" );
        }
    }
}

#======================================================================
# _searchTrack will search for a track on an album using ISRC and
# track title.
#
# Returns: a hash containing the following
#   track_id => trackID of matching track if found
#
# If a track is not found:
#   error => error message
#   code  => error code value
#======================================================================
sub _searchTrack {
    my (%args)    = @_;
    my $trackName = $args{track_title};
    my $isrc      = $args{isrc};
    my $mediaType = $args{media_type};
    my $upc       = $args{upc};
    my $albumID   = $args{album_id};
    my $clientID  = $args{client_id};
    my $dbo       = $args{dbo};

    my %status;

    my $prefix = "    _searchTrack(client=$clientID):";

    # Search for track using ISRC.  If no ISRC is specified then we'll default
    # to a title-based search.
    #
    if ( $isrc && '' ne $isrc ) {
        my $sql = "SELECT t.track_id FROM track t JOIN master m USING(master_id) "
          . "WHERE t.album_id=$albumID AND m.isrc='$isrc'";
        my $sth = $dbo->DoCmd($sql);

        if ( $sth->rows == 1 ) {

            my ($trackID) = $sth->fetchrow_array();
            $status{track_id} = $trackID;

            Log->info( "$prefix  title($trackName) --> trackID($trackID) (isrc/media search)" );

        } elsif ( $sth->rows > 1 ) {

            # ERROR: ISRC not unique

            Log->warn( "$prefix  UPC($upc) ISRC($isrc) not unique !!!" );
            $status{error} = "ISRC not unique";
            $status{code}  = kErrorISRCNotUnique;
            return \%status;  # stop track search

        } elsif ( $sth->rows == 0 ) {

            # ERROR: Didn't find track using ISRC

            Log->warn( "$prefix  UPC($upc) TRACK($trackName) ISRC($isrc) not found !!!" );
            $status{error} = "not found";
            $status{code}  = kErrorTrackNotFound;
            return \%status;  # stop track search
        }
    }


    if ( !exists $status{track_id} ) {

        # No ISRC specified, try title search.

        Log->warn( "$prefix  UPC($upc) TRACK($trackName) ISRC($isrc) MTYPE($mediaType) not found !!!" );

        my $sql =
            "SELECT t.track_id FROM track t JOIN master m USING(master_id) "
          . "WHERE t.album_id=$albumID AND t.title="
          . $dbo->DBQuote($trackName);
        my $sth = $dbo->DoCmd($sql);

        if ( $sth->rows == 1 ) {

            my ($trackID) = $sth->fetchrow_array();
            $status{track_id} = $trackID;

            Log->info( "$prefix  title($trackName) --> trackID($trackID) (name/media search)" );

        } elsif ( $sth->rows > 1 ) {

            # ERROR: duplicate track title detected

            Log->warn( "$prefix  UPC($upc) TRACK($trackName) ISRC($isrc) not unique !!!");
            $status{error} = "not unique";
            $status{code}  = kErrorTrackNotUnique;

        } else {

            # ERROR: track not found using title

            Log->warn( "$prefix  UPC($upc) TRACK($trackName) ISRC($isrc) not found !!!");
            $status{error} = "not found";
            $status{code}  = kErrorTrackNotFound;
        }

    }

    return \%status;
}

# _createTrack - create an RPS track along with the underlying song and master objects.
# It is the caller's responsiblity to ensure the track numbers are ordered properly
# on the album.
# Returns: the trackID of the new track (only if execMode is set), undef otherwise
sub _createTrack {
    my (%args)    = @_;
    my $trackName = $args{track_title};
    my $isrc      = $args{isrc};
    my $duration  = $args{duration};
    my $trackNo   = $args{track_number};
    my $mediaType = $args{media_type};
    my $artistID  = $args{artist_id};
    my $albumID   = $args{album_id};
    my $clientID  = $args{client_id};

    my $trackID;

    my $prefix = "    _createTrack(client=$clientID):";

    my %trackArgs = (
        title       => $trackName,
        title_clean => clean_name_catalog($trackName),
        media_type  => $mediaType,
        artist_id   => $artistID,
        track_order => $trackNo,
    );

    if ($execMode) {

        # ... Create song ...
        my %songArgs = ( title => $trackName, );
        my $song = RPS::DB::Item::Song->Create(%songArgs);
        $song->save;
        my $songID = $song->song_id;
        Log->debug( "$prefix  Created song $songID : " . Dumper( \%songArgs ) );

        # ... Create master ...
        my %masterArgs = (
            song_id   => $song->song_id,
            title     => $trackName,
            isrc      => $isrc,
            duration  => $duration,
            artist_id => $artistID,
        );
        my $master = RPS::DB::Item::Master->Create(%masterArgs);
        $master->save;
        my $masterID = $master->master_id;

        Log->debug( "$prefix  Created master $masterID : " . Dumper( \%masterArgs ) );

        # ... Create track ...
        $trackArgs{album_id}  = $albumID;
        $trackArgs{master_id} = $master->master_id;

        my $track = RPS::DB::Item::Track->Create(%trackArgs);
        $track->save;
        $trackID = $track->track_id;
        Log->debug( "$prefix  Created track $trackID : " . Dumper( \%trackArgs ) );

    } else {
        Log->debug( "$prefix  Non-exec: skipped track creation : " . Dumper( \%trackArgs ) );
    }

    return $trackID;

}    # _createTrack

sub _createProduct {
    my $args    = shift;
    my $product = RPS::DB::Item::Product->Create(%$args);
    $product->save;
    return $product->product_id;
}

sub _getOrCreateArtist {
    my $clientID   = shift;
    my $artistName = shift;
    my $artistMap  = shift;
    my $artistID   = 0;

    if ( $artistName && '' ne $artistName ) {
        if ( !exists $artistMap->{$clientID}{$artistName} ) {
            my $a = RPS::DB::Item::Artist->Lookup( name => $artistName );
            if ( !$a ) {
                if ($execMode) {
                    $a = RPS::DB::Item::Artist->Create( name => $artistName, name_clean => clean_name_catalog($artistName) );
                    $a->save;
                    $artistMap->{$clientID}{$artistName} = $a->artist_id;
                    $artistID = $artistMap->{$clientID}{$artistName};
                }
            } else {
                $artistMap->{$clientID}{$artistName} = $a->artist_id;
                $artistID = $artistMap->{$clientID}{$artistName};
            }
        } else {
            $artistID = $artistMap->{$clientID}{$artistName};
        }
    }
    return $artistID;
}

sub _getOrCreateLabel {
    my $clientID  = shift;
    my $labelName = shift;
    my $labelMap  = shift;
    my $labelID   = 0;

    if ( $labelName && '' ne $labelName ) {
        if ( !exists $labelMap->{$clientID}{$labelName} ) {
            my $a = RPS::DB::Item::Label->Lookup( label_name => $labelName );
            if ( !$a ) {
                if ($execMode) {
                    $a = RPS::DB::Item::Label->Create( label_name => $labelName, label_name_clean => clean_name_catalog($labelName) );
                    $a->save;
                    $labelMap->{$clientID}{$labelName} = $a->label_id;
                    $labelID = $labelMap->{$clientID}{$labelName};
                }
            } else {
                $labelMap->{$clientID}{$labelName} = $a->label_id;
                $labelID = $labelMap->{$clientID}{$labelName};
            }
        } else {
            $labelID = $labelMap->{$clientID}{$labelName};
        }
    }
    return $labelID;
}

sub _getProductAndMediaTypeID {
    my $mediaFormat = shift;
    my $trackType   = shift;

    my $productTypeID;
    my $mediaType;

    my %mediaFormatMap = (
        '7" Vinyl' => {
            product_type => RPS::DB::Item::Product::kProductTypeLP,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        '10" Vinyl' => {
            product_type => RPS::DB::Item::Product::kProductTypeLP,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        '12" Vinyl' => {
            product_type => RPS::DB::Item::Product::kProductTypeLP,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        'Blu-ray' => {
            product_type => RPS::DB::Item::Product::kProductTypeBluRay,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeVideo,
        },
        'Cassette' => {
            product_type => RPS::DB::Item::Product::kProductTypeCass,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        'CD' => {
            product_type => RPS::DB::Item::Product::kProductTypeCD,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        'Digital' => {
            product_type => RPS::DB::Item::Product::kProductTypeDigital,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
        },
        'DVD' => {
            product_type => RPS::DB::Item::Product::kProductTypeDVD,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeVideo,
        },
        'Music Video' => {
            product_type => RPS::DB::Item::Product::kProductTypeDigital,
            media_type   => RPS::DB::Item::MediaType::kMediaTypeVideo,
        },
    );

    if ( '' eq $mediaFormat || $mediaFormat =~ /^\s+$/i ) {    # YouTube Video
        $productTypeID = RPS::DB::Item::Product::kProductTypeDigital;
        $mediaType     = RPS::DB::Item::MediaType::kMediaTypeVideo;
    } else {
        foreach my $k ( keys %mediaFormatMap ) {
            if ( $mediaFormat =~ /$k/i ) {
                $productTypeID = $mediaFormatMap{$k}{product_type};
                $mediaType     = $mediaFormatMap{$k}{media_type};
            }
        }
    }

    $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo if ( $trackType =~ /video/i );

    return ( $productTypeID, $mediaType );
}

sub parseCommandLine {
    my ( $a ) = @_;

    my $inputFile;
    my $debugLevel;
    my @debug;
    my $execMode;
    my $force;

    GetOptions(
        'f|file=s' => \$inputFile,
        'd'        => \@debug,
        'debug=s'  => \$debugLevel,
        'e|exec'   => \$execMode,
        'force'    => \$force,
    );

    unless ( '' eq $debugLevel || $debugLevel =~ /^(emerg|alert|crit|error|warn|notice|info|debug)$/i ) {
        die(usage("Invalid debug level '$debugLevel'"));
    }

    $a->{file}       = $inputFile;
    $a->{debug}      = \@debug;
    $a->{debugLevel} = $debugLevel;
    $a->{execMode}   = $execMode;
    $a->{force}      = $force;
}

sub printHeader {
    my $fh = shift;
    print $fh join( ",",
        "Vendor Vendor ID",
        "Vendor Vendor Name",
        "Artist Info Name",
        "Release Release Name",
        "Release Display Upc",
        "Track Track Name",
        "Track Artist Track Artist (Performer)",
        "Track ISRC",
        "Track TrackNo",
        "Track Length Minute",
        "Track Length Seconds",
        "Track Track Type",
        "Release Is Not for Distribution? (Yes / No)",
        "Release Deletions",
        "Distribution Format Context Type",
        "Distribution Format Media Format",
        "Error" )
      . "\n";
}

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 [ -f <inputFile>] -e [--force] [-d] [--debug emerg|alert|crit|error|warn|notice|info|debug]\n";
    $text .= "Each '-d' increases debug level, alternatively you can use debug to set exact level\n";
    $text .= "--force will ignore previous MD5 line signatures\n";
    return $text;
}