#!/usr/bin/perl
#-----------------------------------------------------------------
# DDEX catalog importer
#   Script to import DDEX files.  The DDEX file will be parsed and
#   converted to an RPS catalog import template, and then imported
#   using the RPS metadata importer system.  Since we're using the
#   metadata importer system, there are some considerations to be
#   aware of:
#   - Only one import per client can be active at a time.  Imports
#   can be started via the UI or by using this script.  In case of
#   an import error, the error must be cleared through the UI.
#   - Any notifications/warnings/errors must be viewed through the UI.
#
# Usage:
#   ./ddex_import.pl -c clientID  [ -d directory | -f filename ]
#
# where
#   '-c clientID' specifies the RPS clientID
#
#   '-d directory' specifies a directory containing DDEX files.
#   The script will attempt to process each DDEX file in the
#   directory.  If a file errors out then you'll have to check
#   the UI to see what happened (and to clear the error).  Any
#   file errors will leave the import in a "staged" condition and
#   will prevent other files from being imported.
#   Once a file is successfully imported, it is removed from
#   the directory.  This script will also remove a DDEX file if
#   we detect that the UPC already exists within RPS.
#
#  or
#
#   '-f filename' specifies the DDEX file to be imported.  This
#   option can be used to manually upload a DDEX file.  Unlike
#   the "-d" option, the original file is not deleted once the
#   file is successfully loaded.
#
#-----------------------------------------------------------------
use strict;
use warnings;

use IO::File;
use Data::Dumper;

use XML::LibXML;
use Data::Dumper;
use Getopt::Long;
use Spreadsheet::WriteExcel;
use File::Spec;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::UTF8;
use Common::Util qw(clean_name_catalog);

use lib '/app/tools/metadata/lib';
use Validate;
use Metadata::Importer;
use Metadata::DB::Item::ContentImport;
use Metadata::DB::Item::ContentImportData;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Album;
use RPS::DB::Item::Artist;
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 constant kCatalogNumberExists   => 1;
use constant kCatalogNumberNotFound => 2;

my %args;
parseCommandLine( \%args );

my $clientID = $args{clientID};
my $filename = $args{file};
my $dirPath  = $args{dir};

my @filelist;

# The dirPath, if specified, acts as a file queue.  If a file
# is successfully imported, it is removed from the directory.
#
if ($dirPath) {

    # Grab all of the XML files.
    #
    while ( defined( my $file = glob( $dirPath . '/*.xml' ) ) ) {
        $filename = $file;    # grab the first file we see
        push @filelist, $file;
    }
} else {
    push @filelist, $filename;
}

foreach my $file (@filelist) {

    print STDERR ">>> Processing file $file ...\n";

    if ( !processFile( dirname => $dirPath, file => $file ) ) {
        print STDERR "<<< An error occurred while processing file $file\n";
    } else {
        print STDERR "<<< File $file was successfully processed\n";
    }

    print STDERR "\n";
}

exit(0);

#
#  ... Subroutines below ...
#

my $dbo;

sub processFile {
    my %args     = @_;
    my $dirPath  = $args{dirname};
    my $filename = $args{file};

    die("No files found") if ( $dirPath && !$filename );
    die("Unknown file format... stopping !!!") if ( $filename !~ /\.xml$/i );

    # Extract the file basename (UPC) as well as the path information.
    # Note: when we generate the Excel file, it'll be placed in the same
    # directory as the XML file.
    #
    my ( $vol, $dir, $file ) = File::Spec->splitpath($filename);
    $file =~ /(.+)\.xml$/;
    my $fileUPC   = $1;                    # The file basename is the UPC
    my $excelFile = $dir . $1 . ".xls";    # RPS catalog templates must be in Excel 2003 format

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

    $dbo = Common::RSApp::GetClientDB();

    # Before we do anything, does the DDEX UPC already exist in the DB?
    # If so, then halt the upload.  If we're processing a directory of
    # DDEX files then delete the DDEX file with the duplicate UPC.
    #
    # We'll do a numeric compare on UPC as it appears Orchard considers
    # leading zeros optional in the filename. Fortunately, they don't do
    # this with the proper ICPN field within the DDEX file itself.
    my $sql =
        "SELECT a.album_id, a.title, a.catalog_number, p.product_id, p.product_type_id "
      . "FROM product p JOIN album a ON( p.product_type_id != 4 AND a.album_id=p.asset_id ) "
      . "WHERE p.upc_ean = $fileUPC";
    my $sth = $dbo->DoCmd($sql);
    if ( $sth->rows ) {
        while ( my ( $albumID, $title, $catNo, $productID, $typeID ) = $sth->fetchrow_array() ) {
            print STDERR "INFO: UPC $fileUPC found on album $albumID, cat($catNo), productID($productID) type($typeID)\n";
        }

        print STDERR "UPC $fileUPC exists, checking for catalog updates ...\n";

        # We know the UPC is being used by at least one product; let's see if
        # we can make updates

        return checkForDDEXUpdates( dirname => $dirPath, ddex => $filename, excel => $excelFile );

    } else {

        print STDERR "UPC $fileUPC does not exist, uploading new catalog ...\n";

        # If the UPC does not exist, we need to check if the catalog number exists.  This is
        # done in _convertDDEXtoExcel.  In that method, if an album is found with the same
        # UPC and title, we'll add the product to the existing catalog.  Otherwise we'll setup
        # a new album containing the product.  An $albumID is returned if an existing album
        # was matched.
        #

        my $albumID = convertDDEXtoExcel( dirname => $dirPath, ddex => $filename, excel => $excelFile );

        if ( !$albumID ) {
            # convertDDEXtoExcel returned no albumID, importing new catalog data
            return importExcelFile( dirname => $dirPath, ddex => $filename, excel => $excelFile );
        }

        # If we get here, convertDDEXtoExcel returned an albumID so we'll update existing catalog data instead

        # Delete the Excel file since it's not needed for an update.
        print STDERR "Deleting excel($excelFile) from directory($dirPath)\n";
        unlink $excelFile;

        # Catalog number exists -- treat as an update to existing album

        return checkForDDEXUpdates( dirname => $dirPath, album_id => $albumID, ddex => $filename, excel => $excelFile );

    }

}    # processFile

sub convertDDEXtoExcel {
    my %args      = @_;
    my $dirPath   = $args{dirname};
    my $filename  = $args{ddex};
    my $excelFile = $args{excel};

    # We'll do a check on the catalog number once we have it; if it exists then
    # we'll return the albumID of the existing album.  It is the caller's responsiblity
    # to _not_ import the Excel template if an albumID is returned, otherwise you'll
    # end up with more than one album with the same catalog number.
    my $albumID;

    #============================================================================
    # Convert the DDEX file into an RPS Catalog Template.  The DDEX file
    # contains the sound recording (track) information followed by the release(s)
    # that contain the sound recordings.  We explicitly check for 'Album' release
    # types and treat these as a digital album when generating the RPS template.
    #============================================================================
    my $dom = XML::LibXML->new->parse_file($filename);

    my %ddexData;
    _parseDDEX( dom => $dom, ddex_data => \%ddexData );

    my $albumData = $ddexData{album_data};
    my $trackData = $ddexData{track_data};

    # Create the Excel worksheet.
    #
    my $workbook = Spreadsheet::WriteExcel->new($excelFile);
    die "ERROR: Unable to create Excel file: $!" unless defined $workbook;

    my $worksheet = $workbook->add_worksheet();
    my $row       = 0;

    # Create the header
    #
    my @header = (
        'album-title',              'primary-album-artist', 'secondary-album-artists', 'label-name',
        'catalog-number',           'client-album-id',      'digital-product-id',      'digital-upc',
        'digital-release-date',     'digital-disc-number',  'digital-track-number',    'cd-product-id',
        'cd-upc',                   'cd-release-date',      'cd-disc-number',          'cd-track-number',
        'vinyl-product-id',         'vinyl-upc',            'vinyl-release-date',      'vinyl-disc-number',
        'vinyl-track-number',       'dvd-product-id',       'dvd-upc',                 'dvd-release-date',
        'dvd-disc-number',          'dvd-track-number',     'primary-genre',           'secondary-genre',
        'c-line',                   'p-line',               'territories-allowed',     'territories-denied',
        'cleared-for-distribution', 'album-custom-1',       'album-custom-2',          'album-custom-3',
        'price-tiers',              'track-title',          'album-purchase-only',     'primary-track-artist',
        'secondary-track-artists',  'track-composer',       'track-minutes',           'track-seconds',
        'explicit',                 'client-track-id',      'isrc',                    'media-type',
        'exclude-digitally',        'track-custom-1',       'track-custom-2',          'track-custom-3'
    );

    my %headerCols;    # Map of column names to their respective column number

    my $col = 0;
    foreach my $name (@header) {
        $headerCols{$name} = $col;
        $worksheet->write_string( $row, $col++, $name );
    }
    $row++;

    # Process the album data extracted from the DDEX XML
    #
    my %seenCatalogNumber;

    foreach my $release (@$albumData) {

        my $releaseType   = $release->{release_type};

        my $catalogNumber = $release->{catalog_number};
        my $upcEan        = $release->{upc_ean};
        my $label         = $release->{label};
        my $albumArtist   = $release->{album_artist};
        my $cline         = $release->{cline};
        my $releaseTitle  = $release->{release_title};
        my $releaseDate   = $release->{release_date};


        # Check if the catalog number exists; if so then we'll use the existing
        # album instead of importing a new album
        #
        if ( !exists $seenCatalogNumber{$catalogNumber} ) {
            my $sql = "SELECT album_id, catalog_number FROM album "
                . "WHERE catalog_number = ". $dbo->DBQuote($catalogNumber);
            my $sth = $dbo->DoCmd($sql);

            if ( $sth->rows > 0 ) { # return 1st match
                ( $albumID ) = $sth->fetchrow_array() if ( !$albumID );
            }
            $seenCatalogNumber{$catalogNumber} = 1;
        }

        # There's a whole bunch of release types in the DDEX ERN standard.
        # We'll key off 'Album' and ignore 'TrackRelease'.  If anything else
        # comes in then error out so we can take a closer look at it.
        #
        # And so we add 'Single', supposedly the only other type we'll see.
        # We'll see...
        #
        die("ERROR: Unknown release type '$releaseType'") if ( $releaseType !~ /^Album|Single|VideoSingle$/i );

        # The resource groups define the separate components (discs) for a release.


        my $discdata = $release->{disc_data};

        foreach my $discNo (keys %$discdata) {

            my $disctracks = $discdata->{$discNo};

            foreach my $seqNo (sort { $a <=> $b } keys %$disctracks) {
                my $resourceRef = $disctracks->{$seqNo};

                my $title     = $trackData->{$resourceRef}{title};
                my $isrc      = $trackData->{$resourceRef}{isrc};
                my $mediaType = $trackData->{$resourceRef}{mediaType};
                my $artist    = $trackData->{$resourceRef}{artist};

                my $minutes   = $trackData->{$resourceRef}{minutes};
                my $seconds   = $trackData->{$resourceRef}{seconds};

                # Output a report line
                #
                $worksheet->write_string( $row, $headerCols{'album-title'},          $releaseTitle );
                $worksheet->write_string( $row, $headerCols{'catalog-number'},       $catalogNumber );
                $worksheet->write_string( $row, $headerCols{'primary-album-artist'}, $albumArtist );
                $worksheet->write_string( $row, $headerCols{'c-line'},               $cline );

                $worksheet->write_string( $row, $headerCols{'digital-upc'},          $upcEan );
                $worksheet->write_string( $row, $headerCols{'digital-release-date'}, $releaseDate );
                $worksheet->write_number( $row, $headerCols{'digital-disc-number'},  $discNo );
                $worksheet->write_number( $row, $headerCols{'digital-track-number'}, $seqNo );

                $worksheet->write_string( $row, $headerCols{'track-title'},          $title );
                $worksheet->write_string( $row, $headerCols{'isrc'},                 $isrc );
                $worksheet->write_string( $row, $headerCols{'media-type'},           $mediaType );
                $worksheet->write_string( $row, $headerCols{'label-name'},           $label );
                $worksheet->write_string( $row, $headerCols{'primary-track-artist'}, $artist );
                $worksheet->write_number( $row, $headerCols{'track-minutes'},        $minutes );
                $worksheet->write_number( $row, $headerCols{'track-seconds'},        $seconds );

                $row++;

            }#disctracks loop


        }# discdata loop

    }#release loop

    $workbook->close();

    return $albumID;

}    # convertDDEXtoExcel


sub importExcelFile {
    my %args      = @_;
    my $dirPath   = $args{dirname};
    my $filename  = $args{ddex};
    my $excelFile = $args{excel};

    # Import the XLS file using the Metadata import controller.
    #
    my $importer = new Metadata::Importer(
        clientID  => $clientID,
        infile    => $excelFile,
        automated => 1
    );

    if ( $importer->is_staged() ) {
        my $importSummary = Metadata::DB::Item::ContentImportData::GetImportSummary();
        print STDERR "Staged import detected:\n" . Dumper( \%$importSummary ) . "\n";
        print STDERR "   *** Upload of $excelFile aborted -- please see UI for more information\n";
        return undef;
    }

    my $line_count = $importer->import_file();

    if ( $importer->errors() ) {
        print STDERR "   *** An import error occured with $excelFile: " . $importer->errors() . "\n";
        return undef;
    } else {
        print STDERR "   Import of $excelFile completed successfully ... calling validate\n";
        $importer->validate();
    }

    my $importSummary = Metadata::DB::Item::ContentImportData::GetImportSummary();

    if (   $importSummary->{Notice}
        or $importSummary->{Warnings}
        or $importSummary->{Errors} ) {

        # If there are any issues which need to be looked at before proceeding,
        # then let's stop here and direct the user to the UI
        #
        print STDERR "Import issues detected:\n" . Dumper( \%$importSummary ) . "\n";
        print STDERR "   *** Upload of $excelFile aborted -- please see UI for more information\n";
        return undef;
    } else {
        my $numTracks = $importSummary->{TotalTracks};
        my $numAlbums = $importSummary->{TotalAlbums};

        if ( $numTracks and $numAlbums ) {
            print STDERR "Importing $numAlbums album(s) and $numTracks track(s) from $excelFile\n";

            # Commit the staged catalog data to RPS
            #
            $importer->store();

            # If this was a directory-based upload, remove the DDEX file
            # (and the Excel equivalent) from the upload directory.
            #
            if ($dirPath) {
                print STDERR "Deleting file($filename) / excel($excelFile) from directory($dirPath)\n";
                unlink $filename;
                unlink $excelFile;
            }
            return 1;
        }
    }

}    # importExcelFile

sub checkForDDEXUpdates {
    my %args      = @_;
    my $dirPath   = $args{dirname};
    my $filename  = $args{ddex};
    my $excelFile = $args{excel};
    my $albumID   = $args{album_id};


    my ( $vol, $dir, $file ) = File::Spec->splitpath($filename);
    $file =~ /(.+)\.xml$/;
    my $fileUPC   = $1;                    # The file basename is the UPC
    my $productID;
    my @productList; # array of album products with the same UPC

    # If an albumID is passed in, then use it do create a new product vs.
    # doing a UPC lookup to find an existing product.

    my $skipUpdate;  # Flag: set if we're unable to update an album
    my $needProduct; # Flag: set if we need to create a product

    if ( !$albumID ) {

        # Check if the UPC appears on more then one product or on more than one album.
        # If the same UPC is used across a single album then we'll update the product(s).
        # However if the same UPC is used across albums then we won't change anything.
        #
        my $sql = "SELECT product_id, asset_id FROM product WHERE upc_ean = $fileUPC AND product_type_id != 4";
        my $sth = $dbo->DoCmd($sql);
        my %seenAlbum;
        while( my($_productID, $_assetID) = $sth->fetchrow_array() ) {
            $seenAlbum{$_assetID} = 1;
            $albumID = $_assetID;
            push @productList, $_productID;
        }

        if ( (keys %seenAlbum) > 1 ) {
            print STDERR "checkForDDEXUpdates - UPC($fileUPC) appears on more than one album, skipping update !!!\n";
            $skipUpdate = 1;
        }

        if ( scalar @productList > 1 ) {
            print STDERR "checkForDDEXUpdates - UPC($fileUPC) appears multiple times on album $albumID !!!\n";
        }
    } else {
        $needProduct = 1;
    }


    my $catalogUpdated; # set if we update anything

    if( !$skipUpdate ) {

        my $album  = RPS::DB::Item::Album->Lookup( album_id => $albumID );
        my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $album->artist_id );

        my $rsAlbumTitle           = $album->title;
        my $rsAlbumTitleClean      = $album->title_clean;
        my $rsAlbumArtistName      = $artist->name;
        my $rsAlbumArtistNameClean = $artist->name_clean;

        if ( $needProduct ) {
            # Create a new DA product
            my %args = (
                upc_ean           => $fileUPC,
                product_type_id   => RPS::DB::Item::Product::kProductTypeDigital,
                asset_id          => $albumID,
                product_status_id => RPS::DB::Item::Product::kProductStatusActive,
                title             => $rsAlbumTitle,
                title_clean       => $rsAlbumTitleClean,
            );
            my $product = RPS::DB::Item::Product->Create( %args );
            $product->save;
            my $_productID = $product->product_id;
            push @productList, $_productID;
        }

        #==================================================================================
        # Parse the DDEX XML the same way as in convertDDEXtoExcel(), except here we update
        # or add elements immediately versus through an intermediate Excel template.
        #==================================================================================
        my $dom = XML::LibXML->new->parse_file($filename);

        my %ddexData;
        _parseDDEX( dom => $dom, ddex_data => \%ddexData );

        my $albumData = $ddexData{album_data};
        my $trackData = $ddexData{track_data};

        my $row       = 0;

        my $albumProcessed; # set once we've checked for album-level changes

        foreach my $release (@$albumData) {

            my $releaseType   = $release->{release_type};

            my $catalogNumber     = $release->{catalog_number};
            my $upcEan            = $release->{upc_ean};
            my $label             = $release->{label};
            my $albumArtist       = $release->{album_artist};
            my $cline             = $release->{cline};
            my $releaseTitle      = $release->{release_title};
            my $releaseDate       = $release->{release_date};
            my $releaseTitleClean = $release->{release_title_clean};
            my $albumArtistClean  = $release->{album_artist_clean};



            # There's a whole bunch of release types in the DDEX ERN standard.
            # We'll key off 'Album' and ignore 'TrackRelease'.  If anything else
            # comes in then error out so we can take a closer look at it.
            #
            # And so we add 'Single', supposedly the only other type we'll see.
            # We'll see...
            #
            die("ERROR: Unknown release type '$releaseType'") if ( $releaseType !~ /^Album|Single|VideoSingle$/i );

            # Do we have new album information?
            #
            my $albumChanged;
            if ( !$albumProcessed ) {

                if ( $releaseDate ) {
                    foreach my $productID (@productList) {
                        my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );
                        $product->release_date($releaseDate);
                        $product->save();
                    }
                }

                # Since we try to used existing albums (based on catalog number), it's possible
                # that the name on the album may not match what coming in from Vector even though
                # the catalog number is the same.  We'll leave the album title and catalog number
                # intact.  Product title updates are allowed, however.

                #if ( $rsAlbumTitleClean ne $releaseTitleClean ) {
                #    print STDERR "albumID ". $album->album_id . ": Updating album title '". $album->title . "' to '$releaseTitle'\n";
                #    # Update album title, title_clean information
                #    $album->title($releaseTitle);
                #    $album->title_clean($releaseTitleClean);
                #    $album->save();
                #    $albumChanged = 1;
                #}
                #
                #if ( $album->catalog_number ne $catalogNumber ) {
                #
                #    print STDERR "albumID ". $album->album_id . ": cat# '". $album->catalog_number . "' to '$catalogNumber'\n";
                #
                #    # Update album catalog_number
                #    $album->catalog_number($catalogNumber);
                #    $albumChanged = 1;
                #}

                # Update product title information
                foreach my $productID (@productList) {
                    my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );
                    $product->title($releaseTitle);
                    $product->title_clean($releaseTitleClean);
                    $product->save();
                }

                if ( $rsAlbumArtistNameClean ne $albumArtistClean ) {

                    # Find/create artist
                    my $artistID = _getOrCreateArtist($albumArtist);

                    print STDERR "albumID ". $album->album_id . ": artistID ". $album->artist_id . " to $artistID\n";

                    # Update album artist
                    $album->artist_id($artistID);
                    $albumChanged = 1;
                }

                if ( $albumChanged ) {
                    $album->save();
                    $catalogUpdated = 1;
                }

                $albumProcessed = 1;
            }


            # Examine the disc(s) associated with the relesae.
            #
            my $discdata = $release->{disc_data};

            foreach my $discNo (keys %$discdata) {

                my $disctracks = $discdata->{$discNo};


                # Examine the track(s) associated with the disc
                #
                foreach my $seqNo (sort { $a <=> $b } keys %$disctracks) {
                    my $resourceRef = $disctracks->{$seqNo};

                    my $title        = $trackData->{$resourceRef}{title};
                    my $isrc         = $trackData->{$resourceRef}{isrc};
                    my $mediaType    = $trackData->{$resourceRef}{mediaType};
                    my $artist       = $trackData->{$resourceRef}{artist};

                    my $minutes      = $trackData->{$resourceRef}{minutes};
                    my $seconds      = $trackData->{$resourceRef}{seconds};
                    my $totalSeconds = ($minutes*60) + $seconds;


                    # If we can find the track with the matching ISRC, check if we need to update anything.
                    # Otherwise, if no matching ISRC is found then treat this as a new track and add it
                    # to the album and any products.

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

                    if( $sth->rows == 1 ) {
                        my ( $trackID, $masterID ) = $sth->fetchrow_array();

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

                        my $trackChanged;
                        my $masterChanged;

                        my $rsTrackTitleClean      = $track->title_clean;

                        my $rsTrackArtistNameClean = ''; # set if the track has a valid artist_id

                        my $artistClean = clean_name_catalog($artist);

                        if ( $track->artist_id ) {
                            my $trackArtist         = RPS::DB::Item::Artist->Lookup( artist_id => $track->artist_id );
                            $rsTrackArtistNameClean = $trackArtist->name_clean;
                        }

                        # Prevent NULLing of artist_id if for some reason we can't find track artist in DDEX file
                        if ( defined $artistClean && '' ne $artistClean && $rsTrackArtistNameClean ne $artistClean ) {

                            # Find/Create artist
                            my $artistID = _getOrCreateArtist($artist);

                            my $ta = ($track->artist_id) ? $track->artist_id : 'NULL';
                            print STDERR "albumID $albumID trackID ". $track->track_id . " artistID ". $ta . " --> $artistID\n";

                            # Update track artist
                            $track->artist_id($artistID);
                            $trackChanged = 1;

                            # Update master artist
                            $master->artist_id($artistID);
                            $masterChanged = 1;
                        }


                        my $titleClean  = clean_name_catalog($title);

                        if ( $master->duration != $totalSeconds ) { # RSD-4778
                            $master->duration($totalSeconds);
                            $masterChanged = 1;
                        }

                        if ( $rsTrackTitleClean ne $titleClean ) {

                            print STDERR "trackID ". $track->track_id . ": title '". $track->title . "' --> '$title'\n";

                            # Update track title, title_clean information
                            $track->title($title);
                            $track->title_clean($titleClean);
                            $trackChanged = 1;

                            # Update master
                            $master->title($title);
                            $masterChanged = 1;

                            # Update song
                            if( defined $master->song_id ) {
                                my $song = RPS::DB::Item::Song->Lookup( song_id => $master->song_id );
                                $song->title($title);
                                $song->save();
                            }
                        }

                        $master->save() if ( $masterChanged );
                        $track->save() if ( $trackChanged );

                        $catalogUpdated = 1 if ( $masterChanged || $trackChanged );


                        # Add track to product if needed

                        foreach my $productID ( @productList ) {
                            _addTrackToProduct( $track->track_id, $productID, $seqNo );
                        }

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

                        # New track; add it to album

                        # Find/Create track artist
                        my $trackArtistID = _getOrCreateArtist($artist);

                        # Create song
                        my %sArgs = (
                            title => $title,
                        );
                        my $song = RPS::DB::Item::Song->Create( %sArgs );
                        $song->save();

                        # Create master
                        my %mArgs = (
                            song_id   => $song->song_id,
                            title     => $title,
                            isrc      => $isrc,
                            duration  => ($minutes * 60) + $seconds,
                            artist_id => $trackArtistID,
                            date_recorded => $releaseDate,
                        );
                        my $master = RPS::DB::Item::Master->Create( %mArgs );
                        $master->save();

                        # Create track
                        my %tArgs = (
                            album_id    => $albumID,
                            title       => $title,
                            title_clean => clean_name_catalog($title),
                            artist_id   => $trackArtistID,
                            master_id   => $master->master_id,
                            track_order => $seqNo,
                        );
                        my $track = RPS::DB::Item::Track->Create( %tArgs );
                        $track->save();

                        print STDERR "Created new trackID ". $track->track_id . "\n";

                        # Add track to product

                        foreach my $productID ( @productList ) {
                            _addTrackToProduct( $track->track_id, $productID, $seqNo );
                        }

                        $catalogUpdated = 1;
                    }

                    $row++;
                } # disctrack (contentItem) loop

                print "\n";
            } # resourceGroup loop

        }

    }# update catalog

    # If this was a directory-based upload, remove the DDEX file
    # (and the Excel equivalent) from the upload directory.
    #
    if ($dirPath) {
        print STDERR "Deleting file($filename) / excel($excelFile) from directory($dirPath)\n";
        unlink $filename;
        unlink $excelFile;
    }

    if( $catalogUpdated ) {
        print STDERR "Catalog update complete\n";
    } else { 
        print STDERR "... Catalog was not updated\n";
    }
    return 1;

}    # checkForDDEXUpdates

# Add a track to the specified product.  For physical products this will result in a new
# product_track.  For digital products a DT product will also be created in addition to
# the product_track.  In either situation, if the DT or product_track already exists then
# we'll update the existing entity instead of creating new ones.
#
sub _addTrackToProduct {
    my $trackID   = shift;
    my $productID = shift;
    my $seqNo     = shift;

    # if parent is DA, check for DT
    my $p = RPS::DB::Item::Product->Lookup( product_id => $productID );
    if ( $p->product_type_id == RPS::DB::Item::Product::kProductTypeDigital ) {

        # Create DT if it doesn't exist, otherwise update the existing DT
        my $dt = RPS::DB::Item::Product->Lookup(
            parent_product_id => $productID,
            asset_id          => $trackID,
            product_type_id   => RPS::DB::Item::Product::kProductTypeDigitalTrack,
        );

        if( !$dt ) {
            my %args = (
                product_type_id   => RPS::DB::Item::Product::kProductTypeDigitalTrack,
                asset_id          => $trackID,
                product_status_id => RPS::DB::Item::Product::kProductStatusActive,
                parent_product_id => $productID,
                upc_ean           => $p->upc_ean,
                release_date      => $p->release_date
            );
            my $product = RPS::DB::Item::Product->Create( %args );
            $product->save;
        } else {
            $dt->upc_ean($p->upc_ean);
            $dt->release_date($p->release_date);
            $dt->save;
        }
    }

    # Create product_track if it doesn't exist, otherwise update the existing product_track
    my $pt = RPS::DB::Item::ProductTrack->Lookup(
        product_id  => $productID,
        track_id    => $trackID,
    );

    if( !$pt ) {
        my %args = (
            product_id  => $productID,
            track_id    => $trackID,
            disc_number => 1,
            disc_track  => $seqNo,
        );
        my $productTrack = RPS::DB::Item::ProductTrack->Create( %args );
        $productTrack->save();
    } else {
       $pt->disc_track($seqNo);
       $pt->save;
    }
}

sub _getOrCreateArtist {
    my $name = shift;
    my $sql = "SELECT artist_id FROM artist WHERE name=" . $dbo->DBQuote($name);
    my $sth = $dbo->DoCmd($sql);
    my($artistID) = $sth->fetchrow_array();

    if ( !$artistID ) {
        my %args = (
            name       => $name,
            name_clean => clean_name_catalog($name)
        );
        my $artist = RPS::DB::Item::Artist->Create( %args );
        $artist->save();
        $artistID = $artist->artist_id;
    }
    return $artistID;
}

# Method _getNodeValue will search a node with an xpath value.  It uses
# findnodes to do the searching (which returns an XML::LibXML::NodeList array),
# and then grabs the textContent of the first node it finds (or undef if no
# node was found).  Since we're looking for the first node element, do not
# use this method if you're expecting there to be more than one node being
# returned.
sub _getNodeValue {
    my $node  = shift;
    my $path  = shift;
    my @_node = $node->findnodes($path);
    die("D: _getNodeValue: findnodes returned more than one node for '$path'") if ( scalar @_node > 1 );    # XXX

    return @_node ? $_node[0]->textContent() : undef;
}

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

    if (
        !GetOptions(
            'c|clientid=i' => \$clientID,
            'f|file=s'     => \$file,
            'd|dir=s'      => \$dir,
        )
      ) {
        die("Unknown option ... aborting");
    }

    die( usage("ClientID required") ) if ( !$clientID );
    die( usage("File required") ) if ( !$dir && !$file );

    if ( !$file ) {
        die( usage("Directory '$dir' not found !!!") ) unless ( $dir && -r $dir );
    }

    $a->{clientID} = $clientID;
    $a->{file}     = $file;
    $a->{dir}      = $dir;
}

sub usage {
    my ($err) = @_;
    my $text = ($err) ? "ERROR: $err\n" : '';

    $text .= "Usage: ./$0 -c clientID [-f inputfile] | -d directory]\n";
    return $text;
}

# Name: _parseDDEX - This method extracts catalog data from the specified DDEX file
# Arguments:
#    dom - The XML document containing DDEX data
#    ddex_data - Where to store the data
#
sub _parseDDEX {

    my %args       = @_;   # where we'll store track data
    my $dom        = $args{dom};
    my $ddexData = $args{ddex_data};

    # We'll do a check on the catalog number once we have it; if it exists then
    # we'll return the albumID of the existing album.  It is the caller's responsiblity
    # to _not_ import the Excel template if an albumID is returned, otherwise you'll
    # end up with more than one album with the same catalog number.
    my $albumID;


    # ResourceList/SoundRecording contains the SoundRecording (track) data that
    # can appear on a Release
    #
    my $soundRecordings = $dom->findnodes('/ern:NewReleaseMessage/ResourceList/SoundRecording');


    # Extract the sound recording (track) information.
    #
    foreach my $soundrec (@$soundRecordings) {
        my $resourceRef = _getNodeValue( $soundrec, 'ResourceReference' );
        my $isrc        = _getNodeValue( $soundrec, 'SoundRecordingId/ISRC' );
        my $title       = _getNodeValue( $soundrec, 'ReferenceTitle/TitleText' );
        my $subtitle    = _getNodeValue( $soundrec, 'ReferenceTitle/SubTitle' );
        $title .= ' (' . $subtitle . ')' if $subtitle;

        my $duration = _getNodeValue( $soundrec, 'Duration' );    # Ex: PT0H13M31S (0 hour, 13 min, 31 sec)

        # Get the duration and convert it to minutes/seconds
        #
        $duration =~ /^PT(\d+)H(\d+)M(\d+)S$/i;    # Ex: PT0H13M31S (0 hour, 13 min, 31 sec)
        my ( $hours, $minutes, $seconds ) = ( $1, $2, $3 );
        $minutes += $hours * 60 if ($hours);       # add hours to minutes field if neccessary

        $ddexData->{track_data}{$resourceRef}{isrc}      = $isrc;
        $ddexData->{track_data}{$resourceRef}{title}     = $title;
        $ddexData->{track_data}{$resourceRef}{duration}  = $duration; # in raw DDEX format
        $ddexData->{track_data}{$resourceRef}{minutes}   = $minutes;
        $ddexData->{track_data}{$resourceRef}{seconds}   = $seconds;
        $ddexData->{track_data}{$resourceRef}{mediaType} = 1;

    }

    # ResourceList/Video contains, um, Video details that can appear on a Release
    #
    my $videos = $dom->findnodes('/ern:NewReleaseMessage/ResourceList/Video');

    foreach my $video (@$videos) {
        my $resourceRef = _getNodeValue( $video, 'ResourceReference' );
        my $isrc        = _getNodeValue( $video, 'VideoId/ISRC' );
        my $title       = _getNodeValue( $video, 'ReferenceTitle/TitleText' );
        my $subtitle    = _getNodeValue( $video, 'ReferenceTitle/SubTitle' );
        $title .= ' (' . $subtitle . ')' if $subtitle;

        my $duration = _getNodeValue( $video, 'Duration' );    # Same as for sound recordings
        # Get the duration and convert it to minutes/seconds
        #
        $duration =~ /^PT(\d+)H(\d+)M(\d+)S$/i;    # Ex: PT0H13M31S (0 hour, 13 min, 31 sec)
        my ( $hours, $minutes, $seconds ) = ( $1, $2, $3 );
        $minutes += $hours * 60 if ($hours);       # add hours to minutes field if neccessary

        $ddexData->{track_data}{$resourceRef}{isrc}      = $isrc;
        $ddexData->{track_data}{$resourceRef}{title}     = $title;
        $ddexData->{track_data}{$resourceRef}{duration}  = $duration; # in raw DDEX format
        $ddexData->{track_data}{$resourceRef}{minutes}   = $minutes;
        $ddexData->{track_data}{$resourceRef}{seconds}   = $seconds;
        $ddexData->{track_data}{$resourceRef}{mediaType} = 2;
    }

    # ReleaseList/Release contains some track information we want as well, so grab stuff from TrackReleases
    #
    my $releases = $dom->findnodes('/ern:NewReleaseMessage/ReleaseList/Release');
    foreach my $release (@$releases) {
        my $releaseType = _getNodeValue( $release, 'ReleaseType' );
        if ( $releaseType =~ /TrackRelease$/i ) {
            my $resourceRef = _getNodeValue( $release, 'ReleaseResourceReferenceList/ReleaseResourceReference' );

            # The track artist info source may vary, depending on which version of DDEX we're dealing with.
            # We'll use 'DisplayArtistName' if it's there, otherwise we'll use 'DisplayArtist/PartyName/FullName'.
            #
            my $artist  = _getNodeValue( $release, 'ReleaseDetailsByTerritory/DisplayArtistName' );
            my $artist2;

            my $artists = $release->findnodes('ReleaseDetailsByTerritory/DisplayArtist' );
            foreach my $aaa (@$artists) {
                my $name = _getNodeValue( $aaa, 'PartyName/FullName' );
                #my $role = _getNodeValue( $aaa, 'ArtistRole' );
                #my $i    = $aaa->getAttribute('SequenceNumber');
                if ( !$artist2 && $name && '' ne $name ) {
                    $artist2 = $name;  # grab the first artist found (see RSD-4865)
                    last;
                }
            }

            my $_artist = ( defined $artist && '' ne $artist ) ? $artist : $artist2;
            $ddexData->{track_data}{$resourceRef}{artist} = $_artist;
        }
    }

    # Now let's gather album-level data.
    #
    my @ddexReleases; # array of releases (note: we assume _one_ 'Album' ReleaseType per DDEX...)

    $releases = $dom->findnodes('/ern:NewReleaseMessage/ReleaseList/Release');
    foreach my $release (@$releases) {
        my $releaseType = _getNodeValue( $release, 'ReleaseType' );

        next if ( $releaseType =~ /TrackRelease$/i );

        my %ddexRelease; # hash of data for this release


        my $catalogNumber = _getNodeValue( $release, 'ReleaseId/CatalogNumber' );
        my $upcEan        = _getNodeValue( $release, 'ReleaseId/ICPN' );
        my $label         = _getNodeValue( $release, 'ReleaseDetailsByTerritory/LabelName' );
        my $albumArtist   = _getNodeValue( $release, 'ReleaseDetailsByTerritory/DisplayArtistName' );
        my $cline         = _getNodeValue( $release, 'CLine/CLineText' );
        my $releaseTitle  = _getNodeValue( $release, 'ReferenceTitle/TitleText' );
        my $releaseDate   = _getNodeValue( $release, 'ReleaseDetailsByTerritory/OriginalReleaseDate' );

        $releaseDate   =~ s/0000-00-00//;
        $catalogNumber =~ s/'/\\'/g;
        $catalogNumber = $upcEan if ( !defined $catalogNumber || '' eq $catalogNumber ); # use UPC if release code is blank (RSD-2786)

        # There's a whole bunch of release types in the DDEX ERN standard.
        # We'll key off 'Album' and ignore 'TrackRelease'.  If anything else
        # comes in then error out so we can take a closer look at it.
        #
        # And so we add 'Single', supposedly the only other type we'll see.
        # We'll see...
        #
        die("ERROR: Unknown release type '$releaseType'") if ( $releaseType !~ /^Album|Single|VideoSingle$/i );

        # For RPS, we need the cline to be of the form 'YYYY rightsholder name',
        # so we simply strip off the leading (C) that DDEX uses.
        #
        $cline =~ s/\(C\)\s+//;

        # Strip out "extra" date information that may be present (RSD-1537)
        $cline =~ s/(\d{4}[;,]\s)//g;

        my $releaseTitleClean = clean_name_catalog($releaseTitle);
        my $albumArtistClean  = clean_name_catalog($albumArtist);

        $ddexRelease{release_type}        = $releaseType;
        $ddexRelease{catalog_number}      = $catalogNumber;
        $ddexRelease{upc_ean}             = $upcEan;
        $ddexRelease{label}               = $label;
        $ddexRelease{album_artist}        = $albumArtist;
        $ddexRelease{cline}               = $cline;
        $ddexRelease{release_title}       = $releaseTitle;
        $ddexRelease{release_date}        = $releaseDate;
        $ddexRelease{release_title_clean} = $releaseTitleClean;
        $ddexRelease{album_artist_clean}  = $albumArtistClean;

        # The resource groups define the separate components (discs) for a release.

        my $resourceGroups = $release->findnodes('ReleaseDetailsByTerritory/ResourceGroup/ResourceGroup');

        my %discResources; # hash of disc resources for this release; the hash keys are the disc numbers

        foreach my $resourceGroup (@$resourceGroups) {
            my $discNo = _getNodeValue( $resourceGroup, 'SequenceNumber' );

            # The content items define which sound recording(s) are used for a release.
            # We'll store this information in the 'discData' hash associated with the disc.
            # The keys to the hash are the track sequence numbers.
            #
            my $contentItems = $resourceGroup->findnodes('ResourceGroupContentItem');
            my %discData;

            foreach my $contentItem (@$contentItems) {
                my $resourceRef   = _getNodeValue( $contentItem, 'ReleaseResourceReference' );
                my $seqNo         = _getNodeValue( $contentItem, 'SequenceNumber' );
                $discData{$seqNo} = $resourceRef;
            } # contentItem loop

            $discResources{$discNo} = \%discData;

            print "\n";
        } # resourceGroup loop

         $ddexRelease{disc_data} = \%discResources;
        push @ddexReleases, \%ddexRelease;

    } # release loop

    $ddexData->{album_data} = \@ddexReleases;

} # _parseDDEX

###
1;    #
###