#!/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 lib '/app/tools/metadata/lib';
use Validate;
use Metadata::Importer;
use Metadata::DB::Item::ContentImport;
use Metadata::DB::Item::ContentImportData;


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 ) )
    {
        die("<<< An error occurred while processing file $file");
    }
    else
    {
        print STDERR "<<< File $file was successfully processed\n";
    }

    print STDERR "\n";
}


exit(0);

#
#  ... Subroutines below ...
#

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 );
    my $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 "ERROR: UPC $fileUPC found on album $albumID, cat($catNo), productID($productID) type($typeID)\n";
        }

        # 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;
        }

        print STDERR "ERROR: Catalog upload of $filename aborted - UPC already imported\n";
        return undef;
    }
    else
    {

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

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

}# processFile

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

    #============================================================================
    # 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);



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


    my %gTrackData;

    # 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)

        $gTrackData{$resourceRef}{isrc}     = $isrc;
        $gTrackData{$resourceRef}{title}    = $title;
        $gTrackData{$resourceRef}{duration} = $duration;
        $gTrackData{$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

        $gTrackData{$resourceRef}{isrc}     = $isrc;
        $gTrackData{$resourceRef}{title}    = $title;
        $gTrackData{$resourceRef}{duration} = $duration;
        $gTrackData{$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');
            my $artist      = _getNodeValue($release, 'ReleaseDetailsByTerritory/DisplayArtistName');
            $gTrackData{$resourceRef}{artist} = $artist;           
        }
    }


    # 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++;


    # Parse the DDEX XML
    #

    #   ReleaseList/Release contains the release(s) that are built using the
    #   sound recording info in the ResourceList/SoundRecording section.
    #   For importing purposes, we're going to treat the 'Album' release type
    #   as an RPS digital album.
    #
    #   Now, we're just interested in Album-y things
    $releases = $dom->findnodes('/ern:NewReleaseMessage/ReleaseList/Release');
    foreach my $release (@$releases)
    {
        my $releaseType   = _getNodeValue($release, 'ReleaseType');

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

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



        # 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 $resourceGroups = $release->findnodes('ReleaseDetailsByTerritory/ResourceGroup/ResourceGroup');

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

            # The content items define which sound recording(s) are used for a release.
            #

            my $contentItems = $resourceGroup->findnodes('ResourceGroupContentItem');

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

                my $title = $gTrackData{$resourceRef}{title};
                my $isrc = $gTrackData{$resourceRef}{isrc};
                my $mediaType = $gTrackData{$resourceRef}{mediaType};
                my $artist = $gTrackData{$resourceRef}{artist};

                # Get the duration and convert it to minutes/seconds
                #
                my $duration = $gTrackData{$resourceRef}{duration};
                $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


                # 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+//;


                # 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++;
            }

            print "\n";
        }
    }

    $workbook->close();

} # 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


# 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;
}


###
1;#
###