#!/usr/bin/perl

use strict;

use lib '/app/tools/common/lib';

use Common::Util qw(trimspaces clean normalize_upc normalize_isrc normalize_date);
use Common::RSDB;
use Getopt::Std;

my ( $clientID, $catalogFile ) = _get_args();

my $rsdb = Common::RSDB->new( client_id => $clientID );
my $dbh = $rsdb->DBH();

#printf("operating on %s\n", $rsdb->DBName());

my $remap = _get_remap();

my @headers = ( [
        "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-name",     "catalog-id",     "upc",            "album-id",
        "release-date", "upc-alt",      "album-custom-1", "album-custom-2", "album-custom-3", "track-name",
        "disc-no",      "disc-name",    "track-no",       "track-minutes",  "track-seconds",  "track-artist",
        "isrc",         "track-id",     "genre",          "track-custom-1", "track-custom-2", "track-custom-3"
    ],
);

open CATALOG, $catalogFile || die "Can't open catalog file '$catalogFile': $!\n";

print "\nExamining file header...\n";
my $firstLine = <CATALOG>;
my $format    = _get_format_from_header($firstLine);
print "using format $format\n";

print "Reading existing catalog data...\n";
my $catalog = getCatalog($dbh);
print "Loading import file...\n\n";
print "========== Potential data issues ==========\n";

my $updates = {};

while ( my $record = <CATALOG> ) {
    my @fields;
    my $albumName;
    my $albumArtist;
    my $labelName;
    my $locationID;
    my $catalogID;
    my $upc;
    my $clientAlbumID;
    my $releaseDate;
    my $upcAlt;
    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 $trackCustom1;
    my $trackCustom2;
    my $trackCustom3;

    chomp $record;

    $record =~ s/\x85/.../g;

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

    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 ) {
        (
            $albumName,    $albumArtist, $labelName,  $catalogID,     $upc,        $clientAlbumID, $releaseDate,  $upcAlt,
            $companyID,    $vendorID,    $locationID, $trackName,     $discNumber, $discName,      $trackNumber,  $trackMinutes,
            $trackSeconds, $trackArtist, $isrc,       $clientTrackID, $genre,      $trackCustom1,  $trackCustom2, $trackCustom3
        ) = undefspaces(@fields);
    } else {
        die("How did I get here?\n");
    }

    $isrc        = normalize_isrc($isrc);
    $upc         = normalize_upc($upc);
    $upcAlt      = normalize_upc($upcAlt);
    $releaseDate = normalize_date($releaseDate);

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

    if ($duration) {
        my @time = split /:/, $duration;
        my $hours = scalar @time == 3 ? shift @time : 0;
        $duration = $hours * 3600 + $time[0] * 60 + $time[1];
    } else {
        $duration = ( $trackMinutes * 60 ) + $trackSeconds;
    }

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

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

        $trackArtist ||= "(unknown)";
        $albumArtist ||= "(unknown)";

        # use any alternate spelling, correction, etc, from remap table
        $albumArtist = ( exists $remap->{artist}->{ lc $albumArtist } ) ? $remap->{artist}->{ lc $albumArtist } : $albumArtist;
        $trackArtist = ( exists $remap->{artist}->{ lc $trackArtist } ) ? $remap->{artist}->{ lc $trackArtist } : $trackArtist;
        $albumName   = ( exists $remap->{album}->{ lc $albumName } )    ? $remap->{album}->{ lc $albumName }    : $albumName;

        if ( !exists( $updates->{albums}->{$clientAlbumID} ) ) {
            $updates->{albums}->{$clientAlbumID}->{albumName}   = $albumName;
            $updates->{albums}->{$clientAlbumID}->{releaseDate} = $releaseDate;
            $updates->{albums}->{$clientAlbumID}->{albumArtist} = $albumArtist;
            $updates->{albums}->{$clientAlbumID}->{labelName}   = $labelName;
            $updates->{albums}->{$clientAlbumID}->{catalogID}   = $catalogID;
            $updates->{albums}->{$clientAlbumID}->{upc}         = $upc;
            $updates->{albums}->{$clientAlbumID}->{upcAlt}      = $upcAlt;
            $updates->{albums}->{$clientAlbumID}->{companyID}   = $companyID;
            $updates->{albums}->{$clientAlbumID}->{vendorID}    = $vendorID;
            $updates->{albums}->{$clientAlbumID}->{locationID}  = $locationID;
        } else {
            if ( lc( $updates->{albums}->{$clientAlbumID}->{albumName} ) ne lc($albumName) ) {
                my $initial = $updates->{albums}->{$clientAlbumID}->{albumName};
                print("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 ( !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}->{custom1}   		= $trackCustom1;
            #$updates->{tracks}->{$clientTrackID}->{custom2}   		= $trackCustom2;
            #$updates->{tracks}->{$clientTrackID}->{custom3}   		= $trackCustom3;

            $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discName} = $discName;
            $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{numberOfTracks}++;
        } else {
            print "Duplicate track: album ID=$clientAlbumID, track ID=$clientTrackID\n";
        }
    } else {
        print "Incomplete import record: $record\n";
    }
}

close(CATALOG);

print "========== End potential issues  ==========\n\n";
print "Analyzing changes...\n";
my $updateStatus = checkUpdates( $updates, $catalog );

if ( %{$updateStatus} ) {
    printf("\n         Inserts  Updates\n");
    printf( "Labels   %6d    %6d\n", $updateStatus->{labels}->{inserts},  $updateStatus->{labels}->{updates} );
    printf( "Artists  %6d    %6d\n", $updateStatus->{artists}->{inserts}, $updateStatus->{artists}->{updates} );
    printf( "Albums   %6d    %6d\n", $updateStatus->{albums}->{inserts},  $updateStatus->{albums}->{updates} );
    printf( "Discs    %6d    %6d\n", $updateStatus->{discs}->{inserts},   $updateStatus->{discs}->{updates} );

    #printf("Tracks   %6d    %6d\n\n", $updateStatus->{tracks}->{inserts}, $updateStatus->{tracks}->{updates});

    my $answer;
    while ( !$answer ) {
        print STDERR "Update catalog? (y/n) ";
        $answer = <STDIN>;
        chomp($answer);
    }

    if ( lc($answer) eq 'y' ) {
        print "Applying updates...\n";
        processUpdates( $dbh, $catalog );
        print "Updates complete\n";
    } else {
        print "No updates performed\n";
        exit(1);
    }
} else {
    print "Nothing to do\n";
}

print "Finished!\n";

exit;

#/////////////////////////////////////////////////

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

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

    die _usage("You must specify a readable catalog file to import") unless ( $opt{f} && -r $opt{f} );

    return ( $opt{c}, $opt{f} );
}

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

sub _get_remap {
    my $sth_remap = $dbh->prepare("SELECT remap_type, lower(input_value), remap_value FROM import_products_remap");
    $sth_remap->execute();
    my $remap = {};

    while ( my ( $remap_type, $input_value, $remap_value ) = $sth_remap->fetchrow_array() ) {
        $remap->{$remap_type}->{$input_value} = $remap_value;
    }
    $sth_remap->finish();

    return $remap;
}

sub _get_format_from_header {
    my $firstLine = shift;

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

    my $match       = 0;
    my $formatIndex = 0;

    while ( $formatIndex < scalar @headers ) {
        my $i = 0;

        while ( $i < scalar @fields ) {
            last if ( $fields[$i] !~ m/$headers[$formatIndex]->[$i]/i );
            $match++;
            $i++;
        }

        last if ( $match == scalar @fields );

        $formatIndex++;
        $match = 0;
    }

    die "Unrecognized file format. Header:\n$firstLine\n" unless $match;

    return $formatIndex;
}

sub getCatalog {
    my $dbh = shift;
    my %catalog;

    my $sql = "SELECT label_id, label_name FROM label";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $labelID, $labelName ) = $sth->fetchrow_array() ) {
        my $lowerLabelName = lc($labelName);

        $catalog{labels}->{$lowerLabelName}->{labelID}   = $labelID;
        $catalog{labels}->{$lowerLabelName}->{labelName} = $labelName;

        $catalog{labelIDs}->{$labelID} = $lowerLabelName;
    }
    $sth->finish();

    $sql = "SELECT artist_id, artist_name FROM artist";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $artistID, $artistName ) = $sth->fetchrow_array() ) {
        my $lowerArtistName = lc($artistName);
        $catalog{artists}->{$lowerArtistName}->{artistID}   = $artistID;
        $catalog{artists}->{$lowerArtistName}->{artistName} = $artistName;

        $catalog{artistIDs}->{$artistID} = $lowerArtistName;
    }
    $sth->finish();

    $sql =
        "SELECT client_album_id, album_id, album_name, release_date, upc, upc_alt, artist_id, "
      . "label_id, catalog_id, company_id, vendor_id, location_id, number_of_discs "
      . "FROM album";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while (
        my (
            $clientAlbumID, $albumID,   $albumName, $releaseDate, $upc,        $upcAlt, $artistID,
            $labelID,       $catalogID, $companyID, $vendorID,    $locationID, $numberOfDiscs
        )
        = $sth->fetchrow_array()
      ) {
        $catalog{albums}->{$clientAlbumID}->{albumID}       = $albumID;
        $catalog{albums}->{$clientAlbumID}->{albumName}     = $albumName;
        $catalog{albums}->{$clientAlbumID}->{releaseDate}   = $releaseDate;
        $catalog{albums}->{$clientAlbumID}->{upc}           = $upc;
        $catalog{albums}->{$clientAlbumID}->{upcAlt}        = $upcAlt;
        $catalog{albums}->{$clientAlbumID}->{artistID}      = $artistID;
        $catalog{albums}->{$clientAlbumID}->{labelID}       = $labelID;
        $catalog{albums}->{$clientAlbumID}->{catalogID}     = $catalogID;
        $catalog{albums}->{$clientAlbumID}->{companyID}     = $companyID;
        $catalog{albums}->{$clientAlbumID}->{vendorID}      = $vendorID;
        $catalog{albums}->{$clientAlbumID}->{locationID}    = $locationID;
        $catalog{albums}->{$clientAlbumID}->{numberOfDiscs} = $numberOfDiscs;

        $catalog{albumIDs}->{$albumID} = $clientAlbumID;
    }
    $sth->finish();

    $sql = "SELECT client_album_id, disc_id, disc_name, disc_number, number_of_tracks "
      . "FROM album_disc, album WHERE album_disc.album_id=album.album_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $clientAlbumID, $discID, $discName, $discNumber, $numberOfTracks ) = $sth->fetchrow_array() ) {
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discID}         = $discID;
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discName}       = $discName;
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{numberOfTracks} = $numberOfTracks;

        $catalog{discIDs}->{$discID}->{clientAlbumID} = $clientAlbumID;
        $catalog{discIDs}->{$discID}->{discNumber}    = $discNumber;
    }
    $sql =
        "SELECT client_track_id, track_id, track_name, disc_id, artist_id, "
      . "label_id, track_number, duration, isrc, genre_name, custom_1, custom_2, custom_3 "
      . "FROM track";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while (
        my (
            $clientTrackID, $trackID, $trackName, $discID,  $artistID, $labelID, $trackNumber,
            $duration,      $isrc,    $genreName, $custom1, $custom2,  $custom3
        )
        = $sth->fetchrow_array()
      ) {
        $catalog{tracks}->{$clientTrackID}->{trackID}     = $trackID;
        $catalog{tracks}->{$clientTrackID}->{trackName}   = $trackName;
        $catalog{tracks}->{$clientTrackID}->{discID}      = $discID;
        $catalog{tracks}->{$clientTrackID}->{artistID}    = $artistID;
        $catalog{tracks}->{$clientTrackID}->{labelID}     = $labelID;
        $catalog{tracks}->{$clientTrackID}->{trackNumber} = $trackNumber;
        $catalog{tracks}->{$clientTrackID}->{duration}    = $duration;
        $catalog{tracks}->{$clientTrackID}->{isrc}        = $isrc;
        $catalog{tracks}->{$clientTrackID}->{genreName}   = $genreName;
        $catalog{tracks}->{$clientTrackID}->{custom1}     = $custom1;
        $catalog{tracks}->{$clientTrackID}->{custom2}     = $custom2;
        $catalog{tracks}->{$clientTrackID}->{custom3}     = $custom3;
    }
    $sth->finish();

    return \%catalog;
}

sub checkUpdates {
    my $updates = shift;
    my $catalog = shift;

    my %updateStatus;

    foreach my $clientAlbumID ( sort keys %{ $updates->{albums} } ) {
        my $albumName     = $updates->{albums}->{$clientAlbumID}->{albumName};
        my $releaseDate   = $updates->{albums}->{$clientAlbumID}->{releaseDate};
        my $albumArtist   = $updates->{albums}->{$clientAlbumID}->{albumArtist};
        my $catalogID     = $updates->{albums}->{$clientAlbumID}->{catalogID};
        my $upc           = $updates->{albums}->{$clientAlbumID}->{upc};
        my $upcAlt        = $updates->{albums}->{$clientAlbumID}->{upcAlt};
        my $companyID     = $updates->{albums}->{$clientAlbumID}->{companyID};
        my $vendorID      = $updates->{albums}->{$clientAlbumID}->{vendorID};
        my $locationID    = $updates->{albums}->{$clientAlbumID}->{locationID};
        my $labelName     = $updates->{albums}->{$clientAlbumID}->{labelName};
        my $numberOfDiscs = scalar keys %{ $updates->{albums}->{$clientAlbumID}->{discs} };

        my $album;
        if ( exists $catalog->{albums}->{$clientAlbumID} ) {
            $album = $catalog->{albums}->{$clientAlbumID};

            if (   $album->{albumName} ne $albumName
                || $catalog->{labelIDs}->{ $album->{labelID} } ne lc($labelName)
                || $catalog->{artistIDs}->{ $album->{artistID} } ne lc($albumArtist)
                || $album->{catalogID} ne $catalogID
                || $album->{releaseDate} ne $releaseDate
                || $album->{upc} ne $upc
                || $album->{upcAlt} ne $upcAlt
                || $album->{companyID} ne $companyID
                || $album->{vendorID} ne $vendorID
                || $album->{locationID} ne $locationID
                || $album->{numberOfDiscs} != $numberOfDiscs ) {
                $album->{update} = 1;
                $updateStatus{albums}->{updates}++;
            }
        } else {
            $catalog->{albums}->{$clientAlbumID}->{insert} = 1;
            $album = $catalog->{albums}->{$clientAlbumID};
            $updateStatus{albums}->{inserts}++;
        }

        if ( $album->{insert} || $album->{update} ) {
            if ( !exists $catalog->{artists}->{ lc($albumArtist) } ) {
                $catalog->{artists}->{ lc($albumArtist) }->{insert}     = 1;
                $catalog->{artists}->{ lc($albumArtist) }->{artistName} = $albumArtist;
                $updateStatus{artists}->{inserts}++;
            }

            if ( !exists $catalog->{labels}->{ lc($labelName) } ) {
                $catalog->{labels}->{ lc($labelName) }->{insert}    = 1;
                $catalog->{labels}->{ lc($labelName) }->{labelName} = $labelName;
                $updateStatus{labels}->{inserts}++;
            }

            $album->{albumName}     = $albumName;
            $album->{releaseDate}   = $releaseDate;
            $album->{upc}           = $upc;
            $album->{upcAlt}        = $upcAlt;
            $album->{artistID}      = undef;
            $album->{albumArtist}   = $albumArtist;
            $album->{labelID}       = undef;
            $album->{labelName}     = $labelName;
            $album->{catalogID}     = $catalogID;
            $album->{companyID}     = $companyID;
            $album->{vendorID}      = $vendorID;
            $album->{locationID}    = $locationID;
            $album->{numberOfDiscs} = $numberOfDiscs;
        }

        foreach my $discNumber ( sort keys %{ $updates->{albums}->{$clientAlbumID}->{discs} } ) {

            my $discName       = $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discName};
            my $numberOfTracks = $updates->{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{numberOfTracks};

            if ( exists $album->{discs}->{$discNumber} ) {
                if (   $album->{discs}->{$discNumber}->{discName} ne $discName
                    || $album->{discs}->{$discNumber}->{numberOfTracks} != $numberOfTracks ) {
                    $album->{discs}->{$discNumber}->{update}         = 1;
                    $album->{discs}->{$discNumber}->{discName}       = $discName;
                    $album->{discs}->{$discNumber}->{numberOfTracks} = $numberOfTracks;
                    $updateStatus{discs}->{updates}++;
                }
            } else {
                $album->{discs}->{$discNumber}->{insert}         = 1;
                $album->{discs}->{$discNumber}->{discName}       = $discName;
                $album->{discs}->{$discNumber}->{numberOfTracks} = $numberOfTracks;
                $updateStatus{discs}->{inserts}++;
            }
        }
    }

    foreach my $clientTrackID ( sort keys %{ $updates->{tracks} } ) {
        my $clientAlbumID = $updates->{tracks}->{$clientTrackID}->{clientAlbumID};
        my $trackName     = $updates->{tracks}->{$clientTrackID}->{trackName};
        my $discNumber    = $updates->{tracks}->{$clientTrackID}->{discNumber};
        my $trackNumber   = $updates->{tracks}->{$clientTrackID}->{trackNumber};
        my $duration      = $updates->{tracks}->{$clientTrackID}->{duration};
        my $trackArtist   = $updates->{tracks}->{$clientTrackID}->{trackArtist};
        my $isrc          = $updates->{tracks}->{$clientTrackID}->{isrc};
        my $genreName     = $updates->{tracks}->{$clientTrackID}->{genreName};
        my $custom1       = $updates->{tracks}->{$clientTrackID}->{custom1};
        my $custom2       = $updates->{tracks}->{$clientTrackID}->{custom2};
        my $custom3       = $updates->{tracks}->{$clientTrackID}->{custom3};
        my $labelName     = $updates->{albums}->{$clientAlbumID}->{labelName};

        my $track;
        if ( exists $catalog->{tracks}->{$clientTrackID} ) {
            $track = $catalog->{tracks}->{$clientTrackID};

            if (   $track->{trackName} ne $trackName
                || $catalog->{labelIDs}->{ $track->{labelID} } ne lc($labelName)
                || $catalog->{discIDs}->{ $track->{discID} }->{clientAlbumID} ne $clientAlbumID
                || $catalog->{discIDs}->{ $track->{discID} }->{discNumber} != $discNumber
                || $track->{trackNumber} != $trackNumber
                || $track->{duration} != $duration
                || $catalog->{artistIDs}->{ $track->{artistID} } ne lc($trackArtist)
                || $track->{isrc} ne $isrc
                || $track->{genreName} ne $genreName
                || $track->{custom1} ne $custom1
                || $track->{custom2} ne $custom2
                || $track->{custom3} ne $custom3 ) {
                $track->{update} = 1;
                $updateStatus{tracks}->{updates}++;
            }
        } else {
            $catalog->{tracks}->{$clientTrackID}->{insert} = 1;
            $track = $catalog->{tracks}->{$clientTrackID};
            $updateStatus{tracks}->{inserts}++;
        }

        if ( $track->{insert} || $track->{update} ) {
            if ( !exists $catalog->{artists}->{ lc($trackArtist) } ) {
                $catalog->{artists}->{ lc($trackArtist) }->{insert}     = 1;
                $catalog->{artists}->{ lc($trackArtist) }->{artistName} = $trackArtist;
                $updateStatus{artists}->{inserts}++;
            }

            # don't need to check for label as that's handled as part of the album

            $track->{trackName}     = $trackName;
            $track->{discID}        = undef;
            $track->{clientAlbumID} = $clientAlbumID;
            $track->{discNumber}    = $discNumber;
            $track->{artistID}      = undef;
            $track->{trackArtist}   = $trackArtist;
            $track->{labelID}       = undef;
            $track->{trackNumber}   = $trackNumber;
            $track->{duration}      = $duration;
            $track->{isrc}          = $isrc;
            $track->{genreName}     = $genreName;
            $track->{custom1}       = $custom1;
            $track->{custom2}       = $custom2;
            $track->{custom3}       = $custom3;
        }
    }

    return \%updateStatus;
}

sub processUpdates {
    my $dbh     = shift;
    my $catalog = shift;

    my $sthInsert        = $dbh->prepare( "INSERT INTO label (label_name, label_name_clean, date_created) " . "VALUES (?, ?, NOW())" );
    my $sthUpdate        = $dbh->prepare( "UPDATE label SET label_name=?, label_name_clean=? " . "WHERE label_id=?" );
    my $sthProductInsert = $dbh->prepare( "INSERT INTO product (product_type, owner_id, date_created) " . "VALUES (?, ?, NOW())" );

    foreach my $lowerLabelName ( sort keys %{ $catalog->{labels} } ) {
        my $label = $catalog->{labels}->{$lowerLabelName};
        if ( $label->{insert} ) {
            $sthInsert->execute( $label->{labelName}, clean( $label->{labelName} ) );
            $label->{labelID} = $dbh->{mysql_insertid};
        } elsif ( $label->{update} ) {
            $sthUpdate->execute( $label->{labelName}, clean( $label->{labelName} ), $label->{labelID} );
        }
    }

    $sthInsert->finish();
    $sthUpdate->finish();

    $sthInsert = $dbh->prepare("INSERT INTO artist (artist_name, artist_name_clean, date_created) VALUES (?, ?, NOW())");

    foreach my $lowerArtistName ( sort keys %{ $catalog->{artists} } ) {
        my $artist = $catalog->{artists}->{$lowerArtistName};
        if ( $artist->{insert} ) {

            $sthInsert->execute( $artist->{artistName}, clean( $artist->{artistName} ) );
            $artist->{artistID} = $dbh->{mysql_insertid};
        }
    }

    $sthInsert->finish();

    $sthInsert =
      $dbh->prepare( "INSERT INTO album (client_album_id, album_name, album_name_clean, release_date, upc, upc_alt, "
          . "artist_id, label_id, catalog_id, company_id, vendor_id, location_id, number_of_discs, date_created) "
          . "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())" );
    $sthUpdate =
      $dbh->prepare( "UPDATE album SET album_name=?, album_name_clean=?, release_date=?, upc=?, upc_alt=?, artist_id=?, "
          . "label_id=?, catalog_id=?, company_id=?, vendor_id=?, location_id=?, number_of_discs=? "
          . "WHERE album_id=?" );
    my $sthDiscInsert =
      $dbh->prepare( "INSERT INTO album_disc (album_id, disc_name, disc_name_clean, "
          . "disc_number, number_of_tracks, date_created) "
          . "VALUES (?, ?, ?, ?, ?, NOW())" );
    my $sthDiscUpdate = $dbh->prepare( "UPDATE album_disc SET disc_name=?, disc_name_clean=?, number_of_tracks=? " . "WHERE disc_id=?" );

    foreach my $clientAlbumID ( sort keys %{ $catalog->{albums} } ) {
        my $album = $catalog->{albums}->{$clientAlbumID};
        if ( $album->{insert} ) {
            $album->{labelID} = $catalog->{labels}->{ lc( $album->{labelName} ) }->{labelID};

            $sthInsert->execute(
                $clientAlbumID,                                                   $album->{albumName},
                clean( $album->{albumName} ),                                     $album->{releaseDate},
                $album->{upc},                                                    $album->{upcAlt},
                $catalog->{artists}->{ lc( $album->{albumArtist} ) }->{artistID}, $album->{labelID},
                $album->{catalogID},                                              $album->{companyID},
                $album->{vendorID},                                               $album->{locationID},
                $album->{numberOfDiscs}
            );
            $album->{albumID} = $dbh->{mysql_insertid};

            #$sthProductInsert->execute('A', $album->{albumID});
            if (   $clientID == 9
                || $clientID == 11
                || $clientID == 14
                || $clientID == 26
                || $clientID == 31 ) {
                $sthProductInsert->execute( '2', $album->{albumID} );
            }
        } elsif ( $album->{update} ) {
            $album->{labelID} = $catalog->{labels}->{ lc( $album->{labelName} ) }->{labelID};

            $sthUpdate->execute(
                $album->{albumName},   clean( $album->{albumName} ),
                $album->{releaseDate}, $album->{upc},
                $album->{upcAlt},      $catalog->{artists}->{ lc( $album->{albumArtist} ) }->{artistID},
                $album->{labelID},     $album->{catalogID},
                $album->{companyID},   $album->{vendorID},
                $album->{locationID},  $album->{numberOfDiscs},
                $album->{albumID}
            );
        }

        foreach my $discNumber ( sort keys %{ $album->{discs} } ) {
            my $disc = $album->{discs}->{$discNumber};
            if ( $disc->{insert} ) {
                $sthDiscInsert->execute( $album->{albumID}, $disc->{discName}, clean( $disc->{discName} ),
                    $discNumber, $disc->{numberOfTracks} );
                $disc->{discID} = $dbh->{mysql_insertid};
            } elsif ( $disc->{update} ) {
                $sthDiscUpdate->execute( $disc->{discName}, clean( $disc->{discName} ), $disc->{numberOfTracks}, $disc->{discID} );
            }
        }
    }

    $sthDiscInsert->finish();
    $sthDiscUpdate->finish();
    $sthInsert->finish();
    $sthUpdate->finish();

    $sthInsert =
      $dbh->prepare( "INSERT INTO track (client_track_id, track_name, track_name_clean, disc_id, "
          . "artist_id, label_id, track_number, duration, isrc, genre_name, custom_1, custom_2, custom_3, date_created) "
          . "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())" );
    $sthUpdate =
      $dbh->prepare( "UPDATE track SET track_name=?, track_name_clean=?, disc_id=?, "
          . "artist_id=?, label_id=?, track_number=?, duration=?, isrc=?, genre_name=?, custom_1=?, custom_2=?, custom_3=? "
          . "WHERE track_id=?" );

    foreach my $clientTrackID ( sort keys %{ $catalog->{tracks} } ) {
        my $track = $catalog->{tracks}->{$clientTrackID};
        if ( $track->{insert} ) {
            $sthInsert->execute(
                $clientTrackID,
                $track->{trackName},
                clean( $track->{trackName} ),
                $catalog->{albums}->{ $track->{clientAlbumID} }->{discs}->{ $track->{discNumber} }->{discID},
                $catalog->{artists}->{ lc( $track->{trackArtist} ) }->{artistID},
                $catalog->{albums}->{ $track->{clientAlbumID} }->{labelID},
                $track->{trackNumber},
                $track->{duration},
                $track->{isrc},
                $track->{genreName},
                $track->{custom1},
                $track->{custom2},
                $track->{custom3}
            );
            $track->{trackID} = $dbh->{mysql_insertid};

            #$sthProductInsert->execute('T', $track->{trackID});
        } elsif ( $track->{update} ) {
            $sthUpdate->execute(
                $track->{trackName},
                clean( $track->{trackName} ),
                $catalog->{albums}->{ $track->{clientAlbumID} }->{discs}->{ $track->{discNumber} }->{discID},
                $catalog->{artists}->{ lc( $track->{trackArtist} ) }->{artistID},
                $catalog->{albums}->{ $track->{clientAlbumID} }->{labelID},
                $track->{trackNumber},
                $track->{duration},
                $track->{isrc},
                $track->{genreName},
                $track->{custom1},
                $track->{custom2},
                $track->{custom3},
                $track->{trackID}
            );
        }
    }

    $sthProductInsert->finish();
    $sthInsert->finish();
    $sthUpdate->finish();
}

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