#!/usr/bin/perl
#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------

use strict;
use Getopt::Std;
use IO::File;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Util qw(trimspaces clean normalize_upc normalize_isrc);
use Common::RSDB;
use Common::RSApp;
use Common::Assert;
use Common::TextProgressBar;

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

use constant kFileFormatXML => 999;
my @gHeaders = ( [
        "album-name", "album-artist", "label",    "catalog-id",   "upc",  "album-id", "track-name", "disc-no",
        "disc-name",  "track-no",     "duration", "track-artist", "isrc", "track-id"
    ],
    [
        "album-name", "album-artist", "label",    "catalog-id",   "upc",  "album-id", "track-name", "disc-no",
        "disc-name",  "track-no",     "duration", "track-artist", "isrc", "track-id", "genre"
    ],
    [
        "album-name", "album-artist", "label",    "catalog-id",   "upc",  "album-id", "track-name", "disc-no",
        "disc-name",  "track-no",     "duration", "track-artist", "isrc", "track-id", "genre",      "upc"
    ],
    [
        "album-name", "album-artist",  "label-name",    "label-id",     "catalog-id", "upc",
        "album-id",   "company-id",    "location-id",   "track-name",   "disc-no",    "disc-name",
        "track-no",   "track-minutes", "track-seconds", "track-artist", "isrc",       "track-id",
        "genre"
    ],
    [
        "album-name", "album-artist",  "label",         "location-id",  "catalog-id", "upc",
        "album-id",   "company-id",    "vendor-id",     "track-name",   "disc-no",    "disc-name",
        "track-no",   "track-minutes", "track-seconds", "track-artist", "isrc",       "track-id",
        "genre"
    ],
    [
        "album-name", "album-artist",     "label",            "location-id",         "catalog-id",  "upc",
        "album-id",   "company-id",       "vendor-id",        "track-name",          "disc-no",     "disc-name",
        "track-no",   "track-minutes",    "track-seconds",    "track-artist",        "isrc",        "track-id",
        "genre",      "track-product-id", "album-product-id", "physical-product-id", "rs-track-id", "rs-album-id"
    ],
    [
        "album-name",       "album-artist",           "label",                  "location-id",
        "catalog-id",       "upc",                    "album-id",               "company-id",
        "vendor-id",        "track-name",             "disc-no",                "disc-name",
        "track-no",         "track-minutes",          "track-seconds",          "track-artist",
        "isrc",             "track-id",               "genre",                  "track-product-id",
        "album-product-id", "physical-cd-product-id", "physical-lp-product-id", "rs-track-id",
        "rs-album-id"
    ],
    [
        "album-name",       "album-artist",           "label",                  "location-id",
        "catalog-id",       "upc",                    "album-id",               "company-id",
        "vendor-id",        "track-name",             "disc-no",                "disc-name",
        "track-no",         "track-minutes",          "track-seconds",          "track-artist",
        "isrc",             "track-id",               "genre",                  "track-product-id",
        "album-product-id", "physical-cd-product-id", "physical-lp-product-id", "physical-cas-product-id",
        "rs-track-id",      "rs-album-id"
    ],
    [
        "album-name",              "album-artist",     "label",                  "location-id",
        "catalog-id",              "upc",              "release-date",           "album-id",
        "company-id",              "vendor-id",        "track-name",             "disc-no",
        "disc-name",               "track-no",         "track-minutes",          "track-seconds",
        "track-artist",            "isrc",             "track-id",               "genre",
        "track-product-id",        "album-product-id", "physical-cd-product-id", "physical-lp-product-id",
        "physical-cas-product-id", "rs-track-id",      "rs-album-id"
    ]
);

# reporting flags and variable
#
use constant kQuiet   => 0;
use constant kNormal  => 1;
use constant kVerbose => 2;
use constant kDebug   => 3;
my $gReportLevel = kVerbose;

use constant kProgressQuanta => 100;

# Parse the command-line.
#
my ( $clientID, $catalogFile ) = parseCommandLine();

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

# Scan the catalog file, and build the data structure of updates.
#
my %updates;
scanCatalogFileForUpdates( $catalogFile, \%updates );

# Commit any necessary updates.
#
processUpdates( \%updates );

#
# --- Subroutines
#

sub parseCommandLine {
    my %opt;
    getopts( 'c:f:', \%opt );

    my $catalogFile = $opt{f};
    my $clientID    = $opt{c};

    unless ( $clientID =~ /^\d+$/ && $clientID > 0 ) {
        die usage("You must specify a valid client ID");
    }

    unless ($catalogFile) {
        die usage("You must specify a catalog file to import");
    }

    unless ( -e $catalogFile ) {
        die usage("The specified catalog file does not exist");
    }

    return ( $clientID, $catalogFile );
}

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 -c <clientID> -f <catalogFile>]\n";
    return $text;
}

sub scanCatalogFileForUpdates {
    my ( $catalogFilePath, $updates ) = @_;
    assert($catalogFilePath);
    assert($updates);

    # Open the catalog file for reading.
    #
    my $catalogFile = IO::File->new($catalogFile) || die "Can't open catalog file '$catalogFile': $!\n";

    # Examine the first line - This should contain the field headers.
    # And will tell us whether we're looking at a tab-seperated file, or XML.
    #
    my $format = determineFileType($catalogFile);

    if ( kFileFormatXML == $format ) {
        readXMLCatalogFile( $catalogFile, $updates );
    } else {
        print "FORMAT = $format\n";

        # !!! Probably want to seed this read method with the field definitions.
        #
        readTabSeperatedCatalogFile( $catalogFile, $updates, $format );
    }

    # Done with the catalog file, close it.
    #
    $catalogFile->close();
}

sub determineFileType {
    my ($file) = @_;
    assert($file);

    # Read the first line from the file.
    #
    my $line = <$file>;
    die "ERROR - empty catalog file!\n" unless $line;
    chomp $line;

    my @fields = split( "\t", $line );
    @fields = trimquotes(@fields);
    @fields = escapequotes(@fields);

    report("Examining file header...");

    # First, see if this file contains XML - should be easy to tell.
    #
    if ( $line =~ m/\^<catalog\>/ ) {
        report("  File is XML");
        return kFileFormatXML;
    }

    # Try to match the fields to one of our known header configurations.
    #
    my $match;
    my $formats = scalar @gHeaders;

    my $format = 0;
    while ( $format < $formats && !$match ) {
        my $fields = @fields;
        $match = 1;
        my $i;
        for ( $i = 0 ; $i < $fields ; $i++ ) {
            if ( !defined $gHeaders[$format]->[$i] ) {
                $match = 0;
                last;
            }

            if ( $fields[$i] !~ m/$gHeaders[$format]->[$i]/i ) {
                $match = 0;
                last;
            }
        }
        if ( !$match ) {
            $format++;
        }
    }
    if ( !$match ) {
        die "Unrecognized file format. Header:\n$line\n";
    }

    report("  File is tab-delimited");
    report( "  format = $format", kDebug );

    return $format;
}

sub readXMLCatalogFile {
    my ( $file, $updates ) = @_;
    assert($file);
    assert($updates);

    die "--- NOT YET IMPLEMENTED---\n";
}

sub readTabSeperatedCatalogFile {
    my ( $file, $updates, $format ) = @_;
    assert($file);
    assert($updates);
    assert($format);

    report("Processing catalog file");

    my $recordCount = 0;

    # Process the file line by line
    #
    while ( my $record = <$file> ) {
        my @fields;
        my $albumName;
        my $albumArtist;
        my $labelName;
        my $locationID;
        my $catalogID;
        my $upc;
        my $releaseDate;
        my $clientAlbumID;
        my $companyID;
        my $vendorID;
        my $trackName;
        my $discNumber;
        my $discName;
        my $trackNumber;
        my $duration;
        my $trackMinutes;
        my $trackSeconds;
        my $trackArtist;
        my $isrc;
        my $clientTrackID;
        my $genre;
        my $upcAlt;
        my $trackProductID;
        my $albumProductID;
        my $physicalCDProductID;
        my $physicalLPProductID;
        my $physicalCassetteProductID;
        my $clientTrackProductID;
        my $clientAlbumProductID;

        $recordCount++;

        if ( 0 == $recordCount % kProgressQuanta ) {
            report( "  record $recordCount ... ", kVerbose );
        }

        # Split the line up into fields.
        #
        chomp $record;
        @fields = split( "\t", $record );
        @fields = trimquotes(@fields);
        @fields = escapequotes(@fields);

        # Parse the fields, based on the expected field order.
        #
        if ( $format == 0 ) {
            (
                $albumName,  $albumArtist, $labelName,   $catalogID, $upc,         $clientAlbumID, $trackName,
                $discNumber, $discName,    $trackNumber, $duration,  $trackArtist, $isrc,          $clientTrackID
            ) = undefspaces(@fields);
        } elsif ( $format == 1 ) {
            (
                $albumName, $albumArtist, $labelName, $catalogID,   $upc,  $clientAlbumID, $trackName, $discNumber,
                $discName,  $trackNumber, $duration,  $trackArtist, $isrc, $clientTrackID, $genre
            ) = undefspaces(@fields);
        } elsif ( $format == 2 ) {
            (
                $albumName, $albumArtist, $labelName, $catalogID,   $upc,  $clientAlbumID, $trackName, $discNumber,
                $discName,  $trackNumber, $duration,  $trackArtist, $isrc, $clientTrackID, $genre,     $upcAlt
            ) = undefspaces(@fields);
        } elsif ( $format == 3 ) {
            (
                $albumName,    $albumArtist, $labelName, $vendorID,      $catalogID, $upc,         $clientAlbumID,
                $companyID,    $locationID,  $trackName, $discNumber,    $discName,  $trackNumber, $trackMinutes,
                $trackSeconds, $trackArtist, $isrc,      $clientTrackID, $genre
            ) = undefspaces(@fields);
        } elsif ( $format == 4 ) {
            (
                $albumName,    $albumArtist, $labelName, $locationID,    $catalogID, $upc,         $clientAlbumID,
                $companyID,    $vendorID,    $trackName, $discNumber,    $discName,  $trackNumber, $trackMinutes,
                $trackSeconds, $trackArtist, $isrc,      $clientTrackID, $genre
            ) = undefspaces(@fields);
        } elsif ( $format == 5 ) {

            # Note - clientAlbumProductID is coming from the 'albumID' field,
            # and clientTrackProductID is coming from the 'trackID' field.
            # I'm not positive that this mapping is correct, or even
            # meaningful, but I do believe that the original values _are_ just
            # client-specified meta-data... so, it may make sense.
            # In any event, we need _something_ to put in the
            # 'product_code' column in the RPS product table, so this is
            # as good as anything...
            #
            (
                $albumName,      $albumArtist,          $labelName,            $locationID,   $catalogID,
                $upc,            $clientAlbumProductID, $companyID,            $vendorID,     $trackName,
                $discNumber,     $discName,             $trackNumber,          $trackMinutes, $trackSeconds,
                $trackArtist,    $isrc,                 $clientTrackProductID, $genre,        $trackProductID,
                $albumProductID, $physicalCDProductID,  $clientTrackID,        $clientAlbumID
            ) = undefspaces(@fields);
        } elsif ( $format == 6 ) {
            (
                $albumName,      $albumArtist,          $labelName,            $locationID,    $catalogID,
                $upc,            $clientAlbumProductID, $companyID,            $vendorID,      $trackName,
                $discNumber,     $discName,             $trackNumber,          $trackMinutes,  $trackSeconds,
                $trackArtist,    $isrc,                 $clientTrackProductID, $genre,         $trackProductID,
                $albumProductID, $physicalCDProductID,  $physicalLPProductID,  $clientTrackID, $clientAlbumID
            ) = undefspaces(@fields);
        } elsif ( $format == 7 ) {

            # added physicalCassetteProductID
            #
            (
                $albumName,      $albumArtist,          $labelName,            $locationID,
                $catalogID,      $upc,                  $clientAlbumProductID, $companyID,
                $vendorID,       $trackName,            $discNumber,           $discName,
                $trackNumber,    $trackMinutes,         $trackSeconds,         $trackArtist,
                $isrc,           $clientTrackProductID, $genre,                $trackProductID,
                $albumProductID, $physicalCDProductID,  $physicalLPProductID,  $physicalCassetteProductID,
                $clientTrackID,  $clientAlbumID
            ) = undefspaces(@fields);
        } else {

            # added $releaseDate after $upc -jff- (case 958)
            #
            (
                $albumName,                 $albumArtist,    $labelName,            $locationID,
                $catalogID,                 $upc,            $releaseDate,          $clientAlbumProductID,
                $companyID,                 $vendorID,       $trackName,            $discNumber,
                $discName,                  $trackNumber,    $trackMinutes,         $trackSeconds,
                $trackArtist,               $isrc,           $clientTrackProductID, $genre,
                $trackProductID,            $albumProductID, $physicalCDProductID,  $physicalLPProductID,
                $physicalCassetteProductID, $clientTrackID,  $clientAlbumID
            ) = undefspaces(@fields);
        }

        $isrc = normalize_isrc($isrc);
        $upc  = normalize_upc($upc);
        $trackNumber =~ s/^(\d+)\D+.*$/$1/;    # seems to be a popular number to decorate... 12a, 12(1), etc.
        $labelName ||= "(unknown)";

        if ($duration) {
            my ( $mins, $secs ) = $duration =~ m/(\d+):(\d+)/;
            $duration = ( $mins * 60 ) + $secs;
        } else {
            $duration = ( $trackMinutes * 60 ) + $trackSeconds;
        }

        $discNumber ||= 1;
        $discNumber = int($discNumber);

        #		if ($clientAlbumID && $clientTrackID)
        #		{
        if ( !$trackArtist && $albumArtist && $albumArtist !~ /^various\s+artists?$/i ) {
            $trackArtist = $albumArtist;
        } elsif ( !$albumArtist && $trackArtist && $trackArtist !~ /^various\s+artists?$/i ) {
            $albumArtist = $trackArtist;
        }

        $trackArtist ||= "(unknown)";
        $albumArtist ||= "(unknown)";
        if ($clientAlbumID) {
            if ( !exists( $updates->{albums}->{$clientAlbumID} ) ) {
                $updates->{albums}->{$clientAlbumID}->{albumName}            = $albumName;
                $updates->{albums}->{$clientAlbumID}->{albumArtist}          = $albumArtist;
                $updates->{albums}->{$clientAlbumID}->{labelName}            = $labelName;
                $updates->{albums}->{$clientAlbumID}->{catalogID}            = $catalogID;
                $updates->{albums}->{$clientAlbumID}->{upc}                  = $upc;
                $updates->{albums}->{$clientAlbumID}->{releaseDate}          = $releaseDate;
                $updates->{albums}->{$clientAlbumID}->{upcAlt}               = $upcAlt;
                $updates->{albums}->{$clientAlbumID}->{companyID}            = $companyID;
                $updates->{albums}->{$clientAlbumID}->{vendorID}             = $vendorID;
                $updates->{albums}->{$clientAlbumID}->{locationID}           = $locationID;
                $updates->{albums}->{$clientAlbumID}->{albumProductID}       = $albumProductID;
                $updates->{albums}->{$clientAlbumID}->{physicalCDProductID}  = $physicalCDProductID;
                $updates->{albums}->{$clientAlbumID}->{physicalLPProductID}  = $physicalLPProductID;
                $updates->{albums}->{$clientAlbumID}->{physicalCasProductID} = $physicalCassetteProductID;
                $updates->{albums}->{$clientAlbumID}->{clientAlbumProductID} = $clientAlbumProductID;
            } else {
                if ( $updates->{albums}->{$clientAlbumID}->{albumName} ne $albumName ) {
                    my $initial = $updates->{albums}->{$clientAlbumID}->{albumName};

                    report("Album name varies: album ID=$clientAlbumID, initial=$initial, this=$albumName\n");

                    #if all we have is blanks so far, use the new album name
                    if ( !$initial ) {
                        $updates->{albums}->{$clientAlbumID}->{albumName} = $albumName;
                    }
                }
            }
        }

        if ($clientTrackID) {
            if ( !exists( $updates->{tracks}->{$clientTrackID} ) ) {
                $updates->{tracks}->{$clientTrackID}->{clientAlbumID}        = $clientAlbumID;
                $updates->{tracks}->{$clientTrackID}->{trackName}            = $trackName;
                $updates->{tracks}->{$clientTrackID}->{discNumber}           = $discNumber;
                $updates->{tracks}->{$clientTrackID}->{trackNumber}          = $trackNumber;
                $updates->{tracks}->{$clientTrackID}->{duration}             = $duration;
                $updates->{tracks}->{$clientTrackID}->{trackArtist}          = $trackArtist;
                $updates->{tracks}->{$clientTrackID}->{isrc}                 = $isrc;
                $updates->{tracks}->{$clientTrackID}->{genreName}            = $genre;
                $updates->{tracks}->{$clientTrackID}->{trackProductID}       = $trackProductID;
                $updates->{tracks}->{$clientTrackID}->{clientTrackProductID} = $clientTrackProductID;

                $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discName} = $discName;
                $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{numberOfTracks}++;
            } else {
                report("Duplicate track: album ID=$clientAlbumID, track ID=$clientTrackID\n");
            }
        }

        #		else
        #		{
        #			report("Incomplete import record: $record\n");
        #		}

    }
    report("  $recordCount records processed");
}

sub processUpdates {
    my ($updates) = @_;

    my %artistNameIDHash;

    report("inserting data");

    # Create album entries
    #
    my $count = 0;

    my %addedAlbumIDs;
    my %updatedAlbumIDs;
    my %skippedAlbumIDs;

    my %addedTrackIDs;
    my %updatedTrackIDs;
    my %skippedTrackIDs;

    my $albums   = $updates->{albums};
    my @albumIDs = keys %$albums;
    foreach my $albumID (@albumIDs) {

        # report("ALBUM DATA $albumID: " . Dumper($albums->{$albumID}));

        # Before we create the album entry, we first have to
        # - create the label (if necessary)
        # - create the artist (if necessary)
        #

        $count++;
        if ( 0 == $count % kProgressQuanta ) {
            report( "  album $count ...", kVerbose );
        }

        # Does this label exist already?
        #
        my $labelName = $albums->{$albumID}{labelName};
        my $labelDBItem = RPS::DB::Item::Label->Lookup( label_name => $labelName );
        if ( !$labelDBItem ) {
            $labelDBItem = RPS::DB::Item::Label->Create(
                label_name       => $labelName,
                label_name_clean => clean($labelName),
            );
            $labelDBItem->save();
        }
        my $labelID = $labelDBItem->label_id;

        # Does this artist exist already?
        #
        my $artistName = $albums->{$albumID}{albumArtist};
        my $artistDBItem = RPS::DB::Item::Artist->Lookup( name => $artistName );
        if ( !$artistDBItem ) {
            $artistDBItem = RPS::DB::Item::Artist->Create(
                name       => $artistName,
                name_clean => clean($artistName),
            );
            $artistDBItem->save();
        }
        my $artistID = $artistDBItem->artist_id;

        # We'll also create a Composer entry for every Artist.
        #
        # Scott requested that we do not use the artist name in the composer.
        # Composer is most likely to be different, so we'll leave it blank. -jff-
        #
        #my $composerDBItem = RPS::DB::Item::Composer->Lookup(name => $artistName);
        #if (! $composerDBItem)
        #{
        #$composerDBItem = RPS::DB::Item::Composer->Create(name => $artistName);
        #$composerDBItem->save();
        #}
        #my $composerID = $composerDBItem->composer_id;
        my $composerID;

        # Save this for later - easier on the database.
        #
        #        $artistNameIDHash{$artistName} = { artistID => $artistID, composerID => $composerID };

        # See if this album exists already.
        # If it does, then we are doing an update... which proceeds
        # a bit differently than creating an album.
        #
        my $album = RPS::DB::Item::Album->Lookup( album_id => $albumID );
        if ($album) {
            report("... album exists, updating");

            # check catalog number, title, artist_id, label_id.
            #
            my $changed = 0;
            if ( $albums->{$albumID}{catalogID} ne $album->catalog_number ) {
                $album->catalog_number( $albums->{$albumID}{catalogID} );
                $changed = 1;
            }
            if ( $albums->{$albumID}{albumName} ne $album->title ) {
                $album->title( $albums->{$albumID}{albumName} );
                $changed = 1;
            }
            if ( $artistID ne $album->artist_id ) {
                $album->artist_id($artistID);
                $changed = 1;
            }
            if ( $labelID ne $album->label_id ) {
                $album->label_id($labelID);
                $changed = 1;
            }
            if ($changed) {
                $album->save();
                $updatedAlbumIDs{$albumID} = 1;
            } else {
                $skippedAlbumIDs{$albumID} = 1;
            }

            # See if the products have changed.
            # (XXX) right now, just checking the upc.
            #
            my $digitalProductID = $albums->{$albumID}{albumProductID};
            if ($digitalProductID) {
                my $digProduct = RPS::DB::Item::Product->Lookup( product_id => $digitalProductID );
                if ($digProduct) {
                    if ( $digProduct->upc_ean ne $albums->{$albumID}{upc} ) {
                        $digProduct->upc_ean( $albums->{$albumID}{upc} );
                        $digProduct->save();
                    }
                } else {
                    my $newProduct = RPS::DB::Item::Product->Create(
                        product_id        => $digitalProductID,
                        asset_id          => $albumID,
                        upc_ean           => $albums->{$albumID}{upc},
                        release_date      => $albums->{$albumID}{releaseDate},
                        product_type_id   => RPS::DB::Item::Product::kProductTypeDigital,    # 3 == digital album
                        product_status_id => 1,                                              # 1 == active
                             # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                        product_code => $albums->{$albumID}{clientAlbumProductID} . '-D',    # per Scott's request
                    );
                    $newProduct->save();
                }
            }

            # Perhaps we need to create the physical product for this album?
            # jpk - need to do this for both lp and cd products.
            # (XXX) Need to do cassette, too...
            #
            my $cdPhysicalID = $albums->{$albumID}{physicalCDProductID};
            if ($cdPhysicalID) {
                my $albumPhysProduct = RPS::DB::Item::Product->Lookup( product_id => $cdPhysicalID );
                if ($albumPhysProduct) {
                    if ( $albumPhysProduct->upc_ean ne $albums->{$albumID}{upc} ) {
                        $albumPhysProduct->upc_ean( $albums->{$albumID}{upc} );
                        $albumPhysProduct->save();
                    }
                } else {
                    my $newPhysProduct = RPS::DB::Item::Product->Create(
                        product_id        => $cdPhysicalID,
                        asset_id          => $albumID,
                        upc_ean           => $albums->{$albumID}{upc},
                        release_date      => $albums->{$albumID}{releaseDate},
                        product_type_id   => RPS::DB::Item::Product::kProductTypeCD,    # 2 == cd
                        product_status_id => 1,                                         # 1 == active
                             # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                        product_code => $albums->{$albumID}{clientAlbumProductID} . '-CD',    # per Scott's request
                    );
                    $newPhysProduct->save();
                }
            }

            my $lpPhysicalID = $albums->{$albumID}{physicalLPProductID};
            if ($lpPhysicalID) {
                my $albumPhysProduct = RPS::DB::Item::Product->Lookup( product_id => $lpPhysicalID );
                if ($albumPhysProduct) {
                    if ( $albumPhysProduct->upc_ean ne $albums->{$albumID}{upc} ) {
                        $albumPhysProduct->upc_ean( $albums->{$albumID}{upc} );
                        $albumPhysProduct->save();
                    }
                } else {
                    my $newPhysProduct = RPS::DB::Item::Product->Create(
                        product_id        => $lpPhysicalID,
                        asset_id          => $albumID,
                        upc_ean           => $albums->{$albumID}{upc},
                        release_date      => $albums->{$albumID}{releaseDate},
                        product_type_id   => RPS::DB::Item::Product::kProductTypeLP,    # 1 == lp
                        product_status_id => 1,                                         # 1 == active
                             # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                        product_code => $albums->{$albumID}{clientAlbumProductID} . '-LP',    # per Scott's request
                    );
                    $newPhysProduct->save();
                }
            }

            my $casPhysicalID = $albums->{$albumID}{physicalCasProductID};
            if ($casPhysicalID) {
                my $casPhysProduct = RPS::DB::Item::Product->Lookup( product_id => $casPhysicalID );
                if ($casPhysProduct) {
                    if ( $casPhysProduct->upc_ean ne $albums->{$albumID}{upc} ) {
                        $casPhysProduct->upc_ean( $albums->{$albumID}{upc} );
                        $casPhysProduct->save();
                    }
                } else {
                    my $newPhysProduct = RPS::DB::Item::Product->Create(
                        product_id        => $casPhysicalID,
                        asset_id          => $albumID,
                        upc_ean           => $albums->{$albumID}{upc},
                        release_date      => $albums->{$albumID}{releaseDate},
                        product_type_id   => RPS::DB::Item::Product::kProductTypeLP,    # 1 == cassette
                                                                                        # !!! We don't _have_ a cassette product type...
                        product_status_id => 1,                                         # 1 == active
                             # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                        product_code => $albums->{$albumID}{clientAlbumProductID} . '-CAS',
                    );
                    $newPhysProduct->save();
                }
            }

            # Mark this album so that we don't try and create it's product later.
            #
            #            $albums->{$albumID}{skip} = 1;

            next;
        }

        # report("... creating new album");

        # Now we can create the album.
        #
        $addedAlbumIDs{$albumID} = 1;
        my $newAlbum = RPS::DB::Item::Album->Create(
            album_id       => $albumID,
            catalog_number => $albums->{$albumID}{catalogID},
            title          => $albums->{$albumID}{albumName},
            title_clean    => clean( $albums->{$albumID}{albumName} ),
            artist_id      => $artistID,
            label_id       => $labelID,
            status         => 1,                                         # 1=active
        );
        $newAlbum->save();

        # Now create a corresponding digital product (for the album).
        # (XXX) So, there's an excellent chance we may have used this productID
        # the last time we did an import (for one of the non-digital products).
        # This means we need to first check to see if that product already exists.
        # If it _does_, then we have to 'move' the old product out of the way...
        #
        #        moveProductIfNecessary($albums->{$albumID}{albumProductID});

        if ( $albums->{$albumID}{albumProductID} ) {
            my $newDigProduct = RPS::DB::Item::Product->Create(
                product_id        => $albums->{$albumID}{albumProductID},
                asset_id          => $albumID,
                upc_ean           => $albums->{$albumID}{upc},
                release_date      => $albums->{$albumID}{releaseDate},
                product_type_id   => RPS::DB::Item::Product::kProductTypeDigital,    # 3 == digital
                product_status_id => 1,                                              # 1 == active
                     # product_price_id => 1,     # 1 == full (the default) # don't set this at all -jff-
                product_code => $albums->{$albumID}{clientAlbumProductID} . '-D',    # per Scott's request
            );
            $newDigProduct->save();
        }

        # Now create the physical products for the album
        # - Only do this when we _have_ ids..
        #
        if ( $albums->{$albumID}{physicalCDProductID} ) {
            my $newPhysProduct = RPS::DB::Item::Product->Create(
                product_id        => $albums->{$albumID}{physicalCDProductID},
                asset_id          => $albumID,
                upc_ean           => $albums->{$albumID}{upc},
                release_date      => $albums->{$albumID}{releaseDate},
                product_type_id   => RPS::DB::Item::Product::kProductTypeCD,     # 2 == cd
                product_status_id => 1,                                          # 1 == active
                     # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                product_code => $albums->{$albumID}{clientAlbumProductID} . '-CD',    # per Scott's request
            );
            $newPhysProduct->save();
        }
        if ( $albums->{$albumID}{physicalLPProductID} ) {
            my $newPhysProduct = RPS::DB::Item::Product->Create(
                product_id        => $albums->{$albumID}{physicalLPProductID},
                asset_id          => $albumID,
                upc_ean           => $albums->{$albumID}{upc},
                release_date      => $albums->{$albumID}{releaseDate},
                product_type_id   => RPS::DB::Item::Product::kProductTypeLP,          # 1 == lp
                product_status_id => 1,                                               # 1 == active
                     # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                product_code => $albums->{$albumID}{clientAlbumProductID} . '-LP',    # per Scott's request
            );
            $newPhysProduct->save();
        }
        if ( $albums->{$albumID}{physicalCasProductID} ) {
            my $newPhysProduct = RPS::DB::Item::Product->Create(
                product_id        => $albums->{$albumID}{physicalCasProductID},
                asset_id          => $albumID,
                upc_ean           => $albums->{$albumID}{upc},
                release_date      => $albums->{$albumID}{releaseDate},
                product_type_id   => RPS::DB::Item::Product::kProductTypeLP,          # 1 == cassette
                product_status_id => 1,                                               # 1 == active
                     # product_price_id => 1,     # 1 == full (the default) # don't set this -jff-
                product_code => $albums->{$albumID}{clientAlbumProductID} . '-CAS',
            );
            $newPhysProduct->save();
        }

        # JPK - Going to create product_track entries later, when processing the tracks.
        #
    }

    report( "  "
          . scalar( keys %addedAlbumIDs )
          . "  albums inserted, "
          . scalar( keys %updatedAlbumIDs )
          . " albums updated, "
          . scalar( keys %skippedAlbumIDs )
          . " albums skipped" );

    $count = 0;

    # Create/update track entries
    #
    my $tracks = $updates->{tracks};
    my @trackIDs;
    @trackIDs = keys %$tracks if $tracks;
    foreach my $trackID (@trackIDs) {
        $count++;
        if ( 0 == $count % kProgressQuanta ) {
            report( "  track $count ...", kVerbose );
        }

        # !!! Note that I hacked the parser to put our id in this field
        #
        my $trackAlbumID = $tracks->{$trackID}{clientAlbumID};

        # Deference the artist name, and fetch the artist and composer ids.
        #
        my $trackArtistName = $tracks->{$trackID}{trackArtist};

        # Does this artist exist already?
        #
        my $artistDBItem = RPS::DB::Item::Artist->Lookup( name => $trackArtistName );
        if ( !$artistDBItem ) {
            $artistDBItem = RPS::DB::Item::Artist->Create( name => $trackArtistName );
            $artistDBItem->save();
        }
        my $trackArtistID = $artistDBItem->artist_id;
        my $trackComposerID;

        # Is this a new track, or are we updating metadata?
        #
        my $track = RPS::DB::Item::Track->Lookup( track_id => $trackID );
        if ($track) {

            # See if the track title or track number changed...
            #
            my $changed       = 0;
            my $masterChanged = 0;
            if ( $track->title ne $tracks->{$trackID}{trackName} ) {
                $track->title( $tracks->{$trackID}{trackName} );
                $changed = 1;
            }
            if ( $track->track_number != $tracks->{$trackID}{trackNumber} ) {
                $track->track_number( $tracks->{$trackID}{trackNumber} );
                $changed = 1;
            }
            if ( $track->artist_id != $trackArtistID ) {
                $track->artist_id($trackArtistID);
                $changed = 1;
            }
            if ($changed) {
                $track->save();
            }

            # Fetch the Master data, and see if anything (like duration) has been changed.
            #
            my $master = RPS::DB::Item::Master->Lookup( master_id => $track->master_id );
            if ($master) {
                if ( $master->title ne $tracks->{$trackID}{trackName} ) {
                    $master->title( $tracks->{$trackID}{trackName} );
                    $masterChanged = 1;
                }
                if ( $master->isrc ne $tracks->{$trackID}{isrc} ) {
                    $master->isrc( $tracks->{$trackID}{isrc} );
                    $masterChanged = 1;
                }
                if ( $master->duration != $tracks->{$trackID}{duration} ) {
                    $master->duration( $tracks->{$trackID}{duration} );
                    $masterChanged = 1;
                }
                if ($masterChanged) {
                    $master->save();
                }
            }

            # JPK - this time, we may need to create ProductTrack entries that map this
            # track to the physical product id.
            # - note that there are _2_ different physical products now, cd and lp...
            # !!! make that 3 - we've got cassettes, too
            #
            if ( $albums->{$trackAlbumID}{physicalCDProductID} ) {
                my $physicalProductTrack = RPS::DB::Item::ProductTrack->Lookup(
                    product_id => $albums->{$trackAlbumID}{physicalCDProductID},
                    track_id   => $trackID,
                );
                if ( !$physicalProductTrack ) {
                    my $cdProductTrack = RPS::DB::Item::ProductTrack->Create(
                        product_id  => $albums->{$trackAlbumID}{physicalCDProductID},
                        track_id    => $trackID,
                        disc_number => $tracks->{$trackID}{discNumber},
                        disc_track  => $tracks->{$trackID}{trackNumber},
                    );
                    $cdProductTrack->save();
                }
            }

            if ( $albums->{$trackAlbumID}{physicalLPProductID} ) {
                my $physicalProductTrack = RPS::DB::Item::ProductTrack->Lookup(
                    product_id => $albums->{$trackAlbumID}{physicalLPProductID},
                    track_id   => $trackID,
                );
                if ( !$physicalProductTrack ) {
                    my $lpProductTrack = RPS::DB::Item::ProductTrack->Create(
                        product_id  => $albums->{$trackAlbumID}{physicalLPProductID},
                        track_id    => $trackID,
                        disc_number => $tracks->{$trackID}{discNumber},
                        disc_track  => $tracks->{$trackID}{trackNumber},
                    );
                    $lpProductTrack->save();
                }
            }

            if ( $albums->{$trackAlbumID}{physicalCasProductID} ) {
                my $physicalProductTrack = RPS::DB::Item::ProductTrack->Lookup(
                    product_id => $albums->{$trackAlbumID}{physicalCasProductID},
                    track_id   => $trackID,
                );
                if ( !$physicalProductTrack ) {
                    my $casProductTrack = RPS::DB::Item::ProductTrack->Create(
                        product_id  => $albums->{$trackAlbumID}{physicalCasProductID},
                        track_id    => $trackID,
                        disc_number => $tracks->{$trackID}{discNumber},
                        disc_track  => $tracks->{$trackID}{trackNumber},
                    );
                    $casProductTrack->save();
                }
            }

            # I also need to take a look at the ProductTrack entry, to possibly
            # update the disc_track and disc_number fields.
            #
            my $productTracks = RPS::DB::Item::ProductTrack->GetByTrackID($trackID);
            while ( my $productTrack = $productTracks->next() ) {
                my $productTrackChanged = 0;

                # jpk - The question is, do I assume the same structure for this track
                # across all products?   I think I have to...
                #
                if ( $productTrack->disc_number != $tracks->{$trackID}{discNumber} ) {
                    $productTrackChanged = 1;
                    $productTrack->disc_number( $tracks->{$trackID}{discNumber} );
                }
                if ( $productTrack->disc_track != $tracks->{$trackID}{trackNumber} ) {
                    $productTrackChanged = 1;
                    $productTrack->disc_track( $tracks->{$trackID}{trackNumber} );
                }
                if ($productTrackChanged) {
                    $productTrack->save();
                    $changed = 1;
                }
            }

            if ( $changed || $masterChanged ) {
                $updatedTrackIDs{$trackID} = 1;
            } else {
                $skippedTrackIDs{$trackID} = 1;
            }

            # Mark this track so we don't create spurious products around it later.
            #
            #            $tracks->{$trackID}{skip} = 1;
            next;
        }

        # We need to create a Master for this track, because that's where track length is stored.
        # I guess that means that we want to create a Song for this track, too.
        # We'll just do our best, and see what happens.
        #

        # Scott requested that we do not use the artist name in the composer.
        # Composer is most likely to be different, so we'll leave it blank. -jff-
        #
        $addedTrackIDs{$trackID} = 1;

        my $newSong = RPS::DB::Item::Song->Create(
            title => $tracks->{$trackID}{trackName},

            # composer_id => $trackComposerID,
        );
        $newSong->save();
        my $trackSongID = $newSong->song_id;

        my $newMaster = RPS::DB::Item::Master->Create(
            song_id   => $trackSongID,
            title     => $tracks->{$trackID}{trackName},
            artist_id => $trackArtistID,
            isrc      => $tracks->{$trackID}{isrc},
            duration  => $tracks->{$trackID}{duration},
        );
        $newMaster->save();
        my $trackMasterID = $newMaster->master_id;

        my $newTrack = RPS::DB::Item::Track->Create(
            track_id    => $trackID,
            title       => $tracks->{$trackID}{trackName},
            title_clean => clean( $tracks->{$trackID}{trackName} ),
            album_id    => $trackAlbumID,
            artist_id   => $trackArtistID,
            track_order => $tracks->{$trackID}{trackNumber},
            master_id   => $trackMasterID,
        );
        $newTrack->save();

        # We may have to move an existing product out of the way...
        # This may happen if we created some additional non-digital products
        # the last time we imported, and they've subsequently added some products
        # to Raptor...
        #
        #        moveProductIfNecessary($tracks->{$trackID}{trackProductID});

        # Now create a corresponding digital product for this track.
        #
        my $newProduct = RPS::DB::Item::Product->Create(
            product_id        => $tracks->{$trackID}{trackProductID},
            asset_id          => $trackID,
            product_type_id   => RPS::DB::Item::Product::kProductTypeDigitalTrack,    # 4 == digital track
            product_status_id => 1,                                                   # 1 == active
                 # product_price_id => 1,     # 1 == full (the default) -jff-
            product_code => $tracks->{$trackID}{clientTrackProductID},
        );
        $newProduct->save();

        # Create a product_track for the digital album product.
        # (This needs to map tracks to the _album_ product), _not_ the digital track product.
        #
        my $albumProductID       = $albums->{$trackAlbumID}{albumProductID};
        my $physicalCDProductID  = $albums->{$trackAlbumID}{physicalCDProductID};
        my $physicalLPProductID  = $albums->{$trackAlbumID}{physicalLPProductID};
        my $physicalCasProductID = $albums->{$trackAlbumID}{physicalCasProductID};
        if ( !$albumProductID ) {
            report("ERROR!  no albumProductID for album $trackAlbumID");
            report( Dumper($albums) );
            assert(0);
        }
        my $digProductTrack = RPS::DB::Item::ProductTrack->Create(
            product_id  => $albumProductID,
            track_id    => $trackID,
            disc_number => $tracks->{$trackID}{discNumber},
            disc_track  => $tracks->{$trackID}{trackNumber},
        );
        $digProductTrack->save();

        if ($physicalCDProductID) {
            my $physProductTrack = RPS::DB::Item::ProductTrack->Create(
                product_id  => $physicalCDProductID,
                track_id    => $trackID,
                disc_number => $tracks->{$trackID}{discNumber},
                disc_track  => $tracks->{$trackID}{trackNumber},
            );
            $physProductTrack->save();
        }
        if ($physicalLPProductID) {
            my $physProductTrack = RPS::DB::Item::ProductTrack->Create(
                product_id  => $physicalLPProductID,
                track_id    => $trackID,
                disc_number => $tracks->{$trackID}{discNumber},
                disc_track  => $tracks->{$trackID}{trackNumber},
            );
            $physProductTrack->save();
        }
        if ($physicalCasProductID) {
            my $physProductTrack = RPS::DB::Item::ProductTrack->Create(
                product_id  => $physicalCasProductID,
                track_id    => $trackID,
                disc_number => $tracks->{$trackID}{discNumber},
                disc_track  => $tracks->{$trackID}{trackNumber},
            );
            $physProductTrack->save();
        }
    }

    report( "  "
          . scalar(%addedTrackIDs)
          . " addedCount tracks inserted, "
          . scalar(%updatedTrackIDs)
          . " tracks updated, "
          . scalar(%skippedTrackIDs)
          . " tracks skipped" );

    report( " updated album ids: " . Dumper( keys %updatedAlbumIDs ) );
    report( " updated track ids: " . Dumper( keys %updatedTrackIDs ) );
}

sub escapequotes {
    my @string = @_;

    for (@string) {
        s/\"\"/"/g;
    }

    return wantarray ? @string : $string[0];
}

sub trimquotes {
    my @string = trimspaces(@_);

    for (@string) {
        if (m/^\".*\"$/) {
            s/^\"//;
            s/\"$//;
        }
    }

    return wantarray ? @string : $string[0];
}

sub undefspaces {
    my @string = trimspaces(@_);

    for (@string) {
        if ( $_ eq '' ) {
            $_ = undef;
        }
    }

    return wantarray ? @string : $string[0];
}

sub report {
    my ( $text, $level ) = @_;

    $level = kNormal unless $level;

    if ( $level <= $gReportLevel ) {
        print STDERR $text . "\n";
    }
}

sub moveProductIfNecessary {
    my ($productID) = @_;
    assert($productID);

    my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );
    return unless $product;

    # Copy this product (yielding a new product with a new id), then
    # nuke the old product.
    #
    my $copy = RPS::DB::Item::Product->Copy( dbItem => $product );
    $copy->save();
    my $newID = $copy->product_id;
    assert($newID);

    $product->delete();

    # Now update all the tables that had used the old product ID to use the new one instead.
    #
    changeProductIDInCollection( RPS::DB::Item::ArtistContractTermReserve->GetByProductID($productID), $newID );
    changeProductIDInCollection( RPS::DB::Item::BonusTrack->GetByProductID($productID),                $newID );
    changeProductIDInCollection( RPS::DB::Item::LicenseReserve->GetByProductID($productID),            $newID );
    changeProductIDInCollection( RPS::DB::Item::MechanicalStatementItem->GetByProductID($productID),   $newID );
    changeProductIDInCollection( RPS::DB::Item::ProductTrack->GetTracksByProductID($productID),        $newID );
}

sub changeProductIDInCollection {
    my ( $collection, $newProductID ) = @_;

    return unless $collection;

    while ( my $dbItem = $collection->next() ) {
        $dbItem->product_id($newProductID);
        $dbItem->save();
    }
}

sub getCDProductIDFromAlbumID {
    my ($trackAlbumID) = @_;

    my $c = RPS::DB::Item::Product->GetProductsByAlbumID( $trackAlbumID, RPS::DB::Item::Product::kProductTypeCD );
    if ( !$c ) {
        report("ERROR - no cd product id for album id $trackAlbumID");
        assert(0);
    }

    my $product = $c->next();
    if ( !$product ) {
        report("ERROR - no cd product id for album id $trackAlbumID");
        assert(0);
    }

    return $product->product_id;
}