#!/usr/bin/perl

use strict;

use Getopt::Std;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Product;

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

use constant RECORD_INSERT => 'I';
use constant RECORD_UPDATE => 'U';

use constant PROD_LABEL  => 'L';
use constant PROD_ARTIST => 'R';
use constant PROD_ALBUM  => 'A';
use constant PROD_TRACK  => 'T';

use constant CHANGE_NAME     => 'name';
use constant CHANGE_DATE     => 'release date';
use constant CHANGE_UPC      => 'upc';
use constant CHANGE_UPCALT   => 'upc alt';
use constant CHANGE_ARTIST   => 'artist';
use constant CHANGE_LABEL    => 'label';
use constant CHANGE_CATALOG  => 'catalog id';
use constant CHANGE_DISC     => 'disc';
use constant CHANGE_TRACKNUM => 'track number';
use constant CHANGE_DURATION => 'duration';
use constant CHANGE_ISRC     => 'isrc';
use constant CHANGE_GENRE    => 'genre';
use constant CHANGE_CUSTOM1  => 'custom1';
use constant CHANGE_CUSTOM2  => 'custom2';
use constant CHANGE_CUSTOM3  => 'custom3';

$|++;
select STDERR;

my ( $CLIENT_ID, $CATALOG_FILE, $EXE, $INITIAL ) = _get_args();

my $rsdb = Common::RSDB->new( client_id => $CLIENT_ID );
die("target tables are not empty") if ( $INITIAL && _has_rows() );

my $DBH   = $rsdb->DBH();
my %STH   = _init_sth();
my %CACHE = ();

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

# if not updating, we need artificial id's
# my ($LABEL_ID, $ARTIST_ID, $ALBUM_ID, $DISC_ID, $TRACK_ID) = (1,1,1,1,1,1);
my ( $LABEL_ID, $ARTIST_ID, $ALBUM_ID, $PRODUCT_ID, $TRACK_ID, $MASTER_ID, $SONG_ID ) = ( 1, 1, 1, 1, 1, 1, 1, 1 );

my %importStatus = (
    labels  => { updates => 0, inserts => 0 },
    artists => { updates => 0, inserts => 0 },
    albums  => { updates => 0, inserts => 0, nochange => 0 },

    # discs   => { updates => 0, inserts => 0 },
    products => { updates => 0, inserts => 0 },
    tracks   => { updates => 0, inserts => 0, nochange => 0 },
);

my %label   = ();
my %artist  = ();
my %album   = ();
my %track   = ();
my %product = ();

# my %albumDisc = ();

# clear out the log table
$DBH->do('TRUNCATE TABLE meta_update');

my $LINENUM = 2;
print "processing import file...\n\n";
open CATALOG, $CATALOG_FILE || die "Can't open catalog file '$CATALOG_FILE': $!\n";
my $firstLine = <CATALOG>;
my @fields    = _get_fields_from_header($firstLine);

my $i = 0;
while (<CATALOG>) {
    chomp;
    my @cols = split /\t/;
    @cols = trimquotes(@cols);
    @cols = escapequotes(@cols);

    my %row = ();
    @row{@fields} = undefspaces(@cols);

    unless ( $row{clientAlbumID} ) {
        print "Incomplete record (client-album-id): $_\n";
        $LINENUM++;
        next;
    }

    unless ( $row{catalogID} ) {
        print "Incomplete record (catalog-id): $_\n";
        $LINENUM++;
        next;
    }

    # label
    $row{labelName} ||= '(unknown)';
    my $labelName = lc( $row{labelName} );

    unless ($INITIAL) {
        if ( my $current = _load_current( 'label', \%row ) ) {
            $label{$labelName}{ID} = $current->{label_id};
            $importStatus{labels}{updates}++ if ( _update_label( $current, \%row ) );
        }
    }

    unless ( exists $label{$labelName} ) {
        $label{$labelName}{ID} = $EXE ? _insert_label( \%row ) : $LABEL_ID++;
        unless ( $label{$labelName}{ID} ) {
            print "failed to insert new label: $row{$labelName} ($LINENUM)\n";
            $LINENUM++;
            next;
        }
        $importStatus{labels}{inserts}++;
        _save_change(
            action    => RECORD_INSERT,
            prod_type => PROD_LABEL,
            owner_id  => $label{$labelName}{ID},
            new_value => $row{labelName},
        );
    }

    # artist (album)
    $row{albumArtist} = $row{trackArtist} if ( !$row{albumArtist} && $row{trackArtist} );
    $row{albumArtist} ||= '(unknown)';
    my $albumArtist = lc( $row{albumArtist} );

    unless ($INITIAL) {
        if ( my $current = _load_current( 'album_artist', \%row ) || _load_current( 'album_artist_clean', \%row ) ) {
            $artist{$albumArtist}{ID} = $current->{artist_id};
            $importStatus{artists}{updates}++ if ( _update_artist( $current, \%row ) );
        }
    }

    unless ( exists $artist{$albumArtist} ) {
        $artist{$albumArtist}{ID} = $EXE ? _insert_artist( 'album', \%row ) : $ARTIST_ID++;
        unless ( $artist{$albumArtist}{ID} ) {
            print "failed to insert new artist: $row{albumArtist} ($LINENUM)\n";
            $LINENUM++;
            next;
        }
        $importStatus{artists}{inserts}++;
        _save_change(
            action    => RECORD_INSERT,
            prod_type => PROD_ARTIST,
            owner_id  => $artist{$albumArtist}{ID},
            new_value => $row{albumArtist},
        );
    }

    # artist (track)
    $row{trackArtist} = $row{albumArtist} if ( !$row{trackArtist} && $row{albumArtist} );
    $row{trackArtist} ||= '(unknown)';
    my $trackArtist = lc( $row{trackArtist} );

    if ( $trackArtist && $trackArtist ne $albumArtist ) {
        unless ($INITIAL) {
            if ( my $current = _load_current( 'track_artist', \%row ) || _load_current( 'track_artist_clean', \%row ) ) {
                $artist{$trackArtist}{ID} = $current->{artist_id};
                $importStatus{artists}{updates}++ if ( _update_artist( $current, \%row ) );
            }
        }

        unless ( exists $artist{$trackArtist} ) {
            $artist{$trackArtist}{ID} = $EXE ? _insert_artist( 'track', \%row ) : $ARTIST_ID++;
            unless ( $artist{$trackArtist}{ID} ) {
                print "failed to insert new artist: $row{trackArtist} ($LINENUM)\n";
                $LINENUM++;
                next;
            }
            $importStatus{artists}{inserts}++;
            _save_change(
                action    => RECORD_INSERT,
                prod_type => PROD_ARTIST,
                owner_id  => $artist{$trackArtist}{ID},
                new_value => $row{trackArtist},
            );
        }
    }

    # album
    $row{upc}         = normalize_upc( $row{upc} ) || "";
    $row{upcAlt}      = normalize_upc( $row{upcAlt} );
    $row{releaseDate} = normalize_date( $row{releaseDate} );
    $row{discNumber}  = int( $row{discNumber} );
    $row{discNumber} ||= 1;
    $row{discName}   ||= '';

    my $albumID = $row{clientAlbumID};
    if ( exists $album{$albumID} ) {

        # albumname is the only attribute we check for variance
        if ( $album{$albumID}{albumName} ne $row{albumName} ) {
            print "albumName varies - aid: $albumID, initial: $album{$albumID}{albumName}, this: $row{albumName}\n";
            unless ( $album{$albumID}{albumName} ) {
                print "update albumName to $row{albumName}\n";
                $album{$albumID}{albumName} = $row{albumName};
            }
        }
    } else {
        unless ($INITIAL) {
            if ( my $current = _load_current( 'album', \%row ) ) {
                $album{$albumID}{albumName} = $row{albumName};
                $album{$albumID}{ID}        = $current->{album_id};
                $album{$albumID}{discCount} = $current->{number_of_discs};
                if (
                    my ( $change, $orig, $new ) = _update_album(
                        current   => $current,
                        row       => \%row,
                        label_id  => $label{$labelName}{ID},
                        artist_id => $artist{$albumArtist}{ID},
                        line_num  => $LINENUM,
                    )
                  ) {
                    $importStatus{albums}{updates}++;
                    _save_change(
                        action     => RECORD_UPDATE,
                        prod_type  => PROD_ALBUM,
                        owner_id   => $album{$albumID}{ID},
                        changed    => $change,
                        orig_value => $orig,
                        new_value  => $new,
                    );
                } else {
                    $importStatus{albums}{nochange}++;
                }
            }
        }

        unless ( exists $album{$albumID}{ID} ) {
            $album{$albumID}{albumName} = $row{albumName};
            $album{$albumID}{discCount} = 1;

            # $album{$albumID}{ID} = $EXE ? _insert_album(
            my $newAlbumID = $EXE
              ? _insert_album(
                row       => \%row,
                label_id  => $label{$labelName}{ID},
                artist_id => $artist{$albumArtist}{ID},
              )
              : $ALBUM_ID++;

            if ($newAlbumID) {
                $album{$albumID}{ID} = $newAlbumID;
                my ( $newProductID, $newProductIDcd ) =
                  $EXE ? _insert_product( PROD_ALBUM, $newAlbumID, \%row ) : ( $PRODUCT_ID++, $PRODUCT_ID++ );

                if ( $newProductID && $newProductIDcd ) {
                    $album{$albumID}{productID}   = $newProductID;
                    $album{$albumID}{productIDcd} = $newProductIDcd;
                } else {
                    print "failed to insert new product: $albumID, $newAlbumID ($LINENUM)\n";
                    $LINENUM++;
                    next;
                }
            } else {
                print "failed to insert new album: $albumID ($LINENUM)\n";
                $LINENUM++;
                next;
            }

            $importStatus{albums}{inserts}++;
            _save_change(
                action    => RECORD_INSERT,
                prod_type => PROD_ALBUM,
                owner_id  => $album{$albumID}{ID},
                new_value => $row{albumName},
            );
        }
    }

    # album_disc
    my $discNumber = int( $row{discNumber} );

    #    unless ($INITIAL)
    #    {
    #        if (my $current = _load_current('disc', \%row, $album{$albumID}{ID}))
    #        {
    #            $album{$albumID}{discs}{$discNumber}{ID} = $current->{disc_id};
    #            $album{$albumID}{discs}{$discNumber}{trackCount} = $current->{number_of_tracks};
    #            $importStatus{discs}{updates}++ if (_update_disc($current, \%row));
    #        }
    #    }
    #
    #    unless (exists $album{$albumID}{discs}{$discNumber})
    #    {
    #        $album{$albumID}{discs}{$discNumber}{trackCount} = 1;
    #        # $album{$albumID}{discs}{$discNumber}{ID} = $EXE ? _insert_album_disc(\%row, $album{$albumID}{ID},) : $DISC_ID++;
    #        unless ($album{$albumID}{discs}{$discNumber}{ID})
    #        {
    #            print "failed to insert new album_disc: $albumID $discNumber ($LINENUM)\n";
    #            $LINENUM++;
    #            next;
    #        }
    #        $importStatus{discs}{inserts}++;
    #    }

    # track
    my $trackID = $row{clientTrackID};
    unless ($trackID) {
        print "missing clientTrackID, must be album only\n";
        $LINENUM++;
        next;
    }

    $row{isrc} = normalize_isrc( $row{isrc} );
    $row{trackNumber} =~ s/^0//;
    $row{trackNumber} =~ s/^(\d+)\D+.*$/$1/;    # seems to be a popular number to decorate... 12a, 12(1), etc.

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

    unless ($INITIAL) {
        if ( my $current = _load_current( 'track', \%row ) ) {
            if ( exists $album{$albumID}{tracks}{$trackID} ) {
                print "Duplicate track: albumID=$albumID, trackID=$trackID ($LINENUM)\n";
                $LINENUM++;
                next;
            }

            $album{$albumID}{tracks}{$trackID} = $current->{track_id};
            if (
                my ( $change, $orig, $new ) = _update_track(
                    current => $current,
                    row     => \%row,

                    # disc_id   => $album{$albumID}{discs}{$discNumber}{ID},
                    label_id  => $label{$labelName}{ID},
                    artist_id => $artist{$trackArtist}{ID},
                )
              ) {
                $importStatus{tracks}{updates}++;
                _save_change(
                    action     => RECORD_UPDATE,
                    prod_type  => PROD_TRACK,
                    owner_id   => $album{$albumID}{tracks}{$trackID},
                    changed    => $change,
                    orig_value => $orig,
                    new_value  => $new,
                );
            } else {
                $importStatus{tracks}{nochange}++;
            }
        }
    }

    unless ( exists $album{$albumID}{tracks}{$trackID} ) {

        # unless ($album{$albumID}{discs}{$discNumber}{ID} && $artist{$trackArtist}{ID} && $label{$labelName}{ID})
        unless ( $artist{$trackArtist}{ID} && $label{$labelName}{ID} ) {
            print "missing required id's for $trackID\n";
            $LINENUM++;
            next;
        }

        $album{$albumID}{discs}{$discNumber}{tracks}++,

          # $album{$albumID}{tracks}{$trackID} = $EXE ? _insert_track(
          my $newTrackID = $EXE
          ? _insert_track(
            row => \%row,

            # disc_id   => $album{$albumID}{discs}{$discNumber}{ID},
            album_id  => $album{$albumID}{ID},
            label_id  => $label{$labelName}{ID},
            artist_id => $artist{$trackArtist}{ID},
          )
          : $TRACK_ID++;

        if ($newTrackID) {
            $album{$albumID}{tracks}{$trackID} = $newTrackID;

            # Create new digital product track
            #
            my ( $newProductID, undef ) = $EXE ? _insert_product( PROD_TRACK, $newTrackID, \%row ) : ( $PRODUCT_ID++, undef );

            # Create new product_track
            #
            if ($EXE) {

                # digital product
                #
                _insert_product_track(
                    product_id  => $album{$albumID}{productID},
                    track_id    => $newTrackID,
                    disc_number => $discNumber,
                    disc_track  => $row{trackNumber},
                );

                # cd product
                #
                _insert_product_track(
                    product_id  => $album{$albumID}{productIDcd},
                    track_id    => $newTrackID,
                    disc_number => $discNumber,
                    disc_track  => $row{trackNumber},
                );
            }
        }

        $importStatus{tracks}{inserts}++;
        _save_change(
            action    => RECORD_INSERT,
            prod_type => PROD_TRACK,
            owner_id  => $album{$albumID}{tracks}{$trackID},
            new_value => $row{trackName},
        );
    } elsif ($INITIAL) {
        print "Duplicate track: albumID=$albumID, trackID=$trackID ($LINENUM)\n";
    }

    $LINENUM++;
}
close(CATALOG);

print "\n\n";
print "nochange albums: $importStatus{albums}{nochange}\n";
print "nochange tracks: $importStatus{tracks}{nochange}\n";

map {
    print "$_ inserts: $importStatus{$_}{inserts}\n";
    print "$_ updates: $importStatus{$_}{updates}\n";
} qw(labels artists discs albums tracks);

#if (1 && $EXE)
#{
#print "update disc and track counts...\n";
#
#if ($INITIAL)
#{
#while (my ($a,$v) = each %album)
#{
#my @discs = ref($v->{discs}) ? keys %{ $v->{discs} } : ();
#my $discCount = scalar @discs;
#            $STH{discCountUpdate}->execute($discCount, $v->{ID}) if ($discCount > 1);
#            foreach my $disc(@discs)
##            {
##                my $trackCount = $v->{discs}{$disc}{tracks};
##                $STH{trackCountUpdate}->execute($trackCount, $v->{discs}{$disc}{ID}) if ($trackCount > 1);
##            }
#}
#}
#else
#{
#while (my ($a,$v) = each %album)
#{
#my $discCount = _get_disc_count($v->{ID});
#$STH{discCountUpdate}->execute($discCount, $v->{ID}) if ($discCount != $v->{discCount});
##print "update discCount($discCount, $v->{discCount}, $v->{ID})\n" if ($discCount != $v->{discCount});
#
#my @discs = ref($v->{discs}) ? keys %{ $v->{discs} } : ();
#foreach my $discNum(@discs)
#{
#my $disc = $v->{discs}{$discNum};
#my $trackCount = _get_track_count($disc->{ID});
#$STH{trackCountUpdate}->execute($trackCount, $disc->{ID}) if ($trackCount != $disc->{trackCount});
##print "update trackCount($trackCount, $disc->{trackCount}, $disc->{ID})\n" if ($trackCount != $disc->{trackCount});
#}
#}
#}
#}

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

sub _get_args {
    my %opt;
    getopts( 'c:f:ui', \%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}, $opt{u}, $opt{i} );
}

sub _usage {
    my $errstr = shift;
    my $text = $errstr ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 -c <clientID> -f <catalogFile> [-i (initial load) -u (execute updates/inserts)\n";
    return $text;
}

sub _init_sth {
    my %sth = ();
    $sth{labelInsert} =
      $DBH->prepare("INSERT INTO label (label_name, label_name_clean, date_created) VALUES(?,?,NOW())") || print $DBH->errstr;

# $sth{artistInsert} = $DBH->prepare("INSERT INTO artist (artist_name, artist_name_clean, date_created) VALUES (?,?,NOW())") || print $DBH->errstr;
    $sth{artistInsert} = $DBH->prepare("INSERT INTO artist (name, name_clean, date_created) VALUES (?,?,NOW())") || print $DBH->errstr;

# $sth{albumInsert} = $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 (?,?,?,?,?,?,?,?,?,?,?,?,1,NOW())") || print $DBH->errstr;
    $sth{albumInsert} = $DBH->prepare(
"INSERT INTO album (catalog_number, client_album_id, title, title_clean, artist_id, label_id, company_id, vendor_id, location_id, date_created) VALUES (?,?,?,?,?,?,?,?,?,NOW())"
    ) || print $DBH->errstr;

# $sth{discInsert} = $DBH->prepare("INSERT INTO album_disc (album_id, disc_name, disc_name_clean, disc_number, number_of_tracks, date_created) VALUES (?,?,?,?,1,NOW())") || print $DBH->errstr;

# $sth{trackInsert} = $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())") || print $DBH->errstr;
    $sth{trackInsert} = $DBH->prepare(
"INSERT INTO track (album_id, master_id, client_track_id, title, title_clean, artist_id, genre_name, track_order, custom_1, custom_2, custom_3, date_created) VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW())"
    ) || print $DBH->errstr;

    #
    # new inserts
    #
    $sth{songInsert} = $DBH->prepare("INSERT INTO song (title, date_created) VALUES (?,NOW())") || print $DBH->errstr;
    $sth{masterInsert} =
      $DBH->prepare("INSERT INTO master (song_id, title, artist_id, isrc, duration, date_created) VALUES (?,?,?,?,?,NOW())")
      || print $DBH->errstr;

# $sth{productInsert} = $DBH->prepare("INSERT INTO product (product_type, owner_id, date_created) VALUES (?,?,NOW())") || print $DBH->errstr;
    $sth{productInsert} = $DBH->prepare(
"INSERT INTO product (asset_id, product_type_id, product_code, upc_ean, upc_alt, release_date, product_status_id, date_created) VALUES (?,?,?,?,?,?,?,NOW())"
    ) || print $DBH->errstr;
    $sth{productTrackInsert} =
      $DBH->prepare("INSERT INTO product_track (product_id, track_id, disc_number, disc_track, is_bonus_track) VALUES (?,?,?,?,?)")
      || print $DBH->errstr;

    # $sth{discCountSearch} = $DBH->prepare("SELECT count(*) FROM album_disc WHERE album_id=?");
    # $sth{discCountUpdate} = $DBH->prepare("UPDATE album SET number_of_discs=? WHERE album_id=?");
    $sth{trackCountSearch} = $DBH->prepare("SELECT count(*) FROM track WHERE disc_id=?");

    # $sth{trackCountUpdate} = $DBH->prepare("UPDATE album_disc SET number_of_tracks=? WHERE disc_id=?");

    $sth{metaInsert} = $DBH->prepare("INSERT INTO meta_update (action, product_type, owner_id, new_value, line_num) values(?,?,?,?,?)");

    unless ($INITIAL) {
        $sth{labelSearch} = $DBH->prepare("SELECT * FROM label WHERE label_name=?") || print $DBH->errstr;
        $sth{labelUpdate} = $DBH->prepare("UPDATE label SET label_name=?, label_name_clean=? WHERE label_id=?") || print $DBH->errstr;

        # $sth{artistSearch} = $DBH->prepare("SELECT * FROM artist WHERE artist_name=?") || print $DBH->errstr;
        $sth{artistSearch} = $DBH->prepare("SELECT * FROM artist WHERE name=?") || print $DBH->errstr;

        # $sth{artistSearchClean} = $DBH->prepare("SELECT * FROM artist WHERE artist_name_clean=?") || print $DBH->errstr;
        $sth{artistSearchClean} = $DBH->prepare("SELECT * FROM artist WHERE name_clean=?") || print $DBH->errstr;

        # $sth{artistUpdate} = $DBH->prepare("UPDATE artist SET artist_name_clean=? WHERE artist_id=?") || print $DBH->errstr;
        $sth{artistUpdate} = $DBH->prepare("UPDATE artist SET name_clean=? WHERE artist_id=?") || print $DBH->errstr;

        $sth{albumSearch} = $DBH->prepare("SELECT * FROM album WHERE client_album_id=?") || print $DBH->errstr;

# $sth{albumUpdate} = $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=? WHERE album_id=?") || print $DBH->errstr;
        $sth{albumUpdate} = $DBH->prepare(
"UPDATE album SET title=?, title_clean=?, artist_id=?, label_id=?, catalog_number=?, company_id=?, vendor_id=?, location_id=? WHERE album_id=?"
        ) || print $DBH->errstr;

        # $sth{discSearch} = $DBH->prepare("SELECT * FROM album_disc WHERE album_id=?") || print $DBH->errstr;
        # $sth{discUpdate} = $DBH->prepare("UPDATE album_disc SET album_id=?, disc_number=? WHERE disc_id=?") || print $DBH->errstr;

        $sth{trackSearch} = $DBH->prepare("SELECT * FROM track WHERE client_track_id=?") || print $DBH->errstr;

# $sth{trackUpdate} = $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=?") || print $DBH->errstr;
        $sth{trackUpdate} =
          $DBH->prepare("UPDATE track SET title=?, title_clean=?, artist_id=?, custom_1=?, custom_2=?, custom_3=? WHERE track_id=?")
          || print $DBH->errstr;

        $sth{metaUpdate} = $DBH->prepare(
            "INSERT INTO meta_update (action, product_type, owner_id, changed, orig_value, new_value, line_num) values(?,?,?,?,?,?,?)");
    }

    return %sth;
}

sub _insert_label {
    my $labelName = shift->{labelName};
    $STH{labelInsert}->execute( $labelName, clean($labelName) ) || print $DBH->errstr;

    return $DBH->{mysql_insertid};
}

sub _insert_artist {
    my $type = shift;
    my $artistName = $type eq 'album' ? shift->{albumArtist} : shift->{trackArtist};
    $STH{artistInsert}->execute( $artistName, clean($artistName) ) || print $DBH->errstr;

    return $DBH->{mysql_insertid};
}

sub _insert_album {
    my %args = @_;
    my $row  = $args{row};

    #    $STH{albumInsert}->execute(
    #        $row->{clientAlbumID},
    #        $row->{albumName},
    #        clean($row->{albumName}),
    #        $row->{releaseDate},
    #        $row->{upc},
    #        $row->{upcAlt},
    #        $args{artist_id},
    #        $args{label_id},
    #        $row->{catalogID},
    #        $row->{albumCustom1},
    #        $row->{albumCustom2},
    #        $row->{albumCustom3},
    #    ) || print $DBH->errstr;

    $STH{albumInsert}->execute(
        $row->{catalogID}, $row->{clientAlbumID}, $row->{albumName},    clean( $row->{albumName} ), $args{artist_id},
        $args{label_id},   $row->{albumCustom1},  $row->{albumCustom2}, $row->{albumCustom3},
    ) || print $DBH->errstr;

    #    unless (_insert_product(PROD_ALBUM, $albumID))
    #    {
    #        print "failed to create product record for $albumID\n";
    #        return 0;
    #    }

    my $albumID = $DBH->{mysql_insertid};
    return $albumID;
}

sub _insert_album_disc {
    my ( $row, $album_id ) = @_;
    $STH{discInsert}->execute( $album_id, $row->{discName}, clean( $row->{discName} ), $row->{discNumber}, ) || print $DBH->errstr;

    return $DBH->{mysql_insertid};
}

#
# MERGE
#
sub _insert_song {
    my %args = @_;
    my $row  = $args{row};

    # $sth{songInsert} = $DBH->prepare("INSERT INTO song (title, date_created) VALUES (?,NOW())") || print $DBH->errstr;
    #
    $STH{songInsert}->execute( $row->{trackName} ) || print $DBH->errstr;

    my $songID = $DBH->{mysql_insertid};
    return $songID;
}

#
# MERGE
#
sub _insert_master {
    my %args = @_;
    my $row  = $args{row};

    # create song first
    #
    my $newSongID = $EXE ? _insert_song( row => $row ) : $SONG_ID++;

# $sth{masterInsert} = $DBH->prepare("INSERT INTO master (song_id, title, artist_id, isrc, duration, date_recorded, date_created) VALUES (?,?,?,?,?,?,NOW())") || print $DBH->errstr;

    $STH{masterInsert}->execute( $newSongID, $row->{trackName}, $args{artist_id}, $row->{isrc}, $row->{duration}, ) || print $DBH->errstr;

    my $masterID = $DBH->{mysql_insertid};
    return $masterID;
}

sub _insert_track {
    my %args = @_;
    my $row  = $args{row};

    #    $STH{trackInsert}->execute(
    #        $row->{clientTrackID},
    #        $row->{trackName},
    #        clean($row->{trackName}),
    #        $args{disc_id},
    #        $args{artist_id},
    #        $args{label_id},
    #        $row->{trackNumber},
    #        $row->{duration},
    #        $row->{isrc},
    #        $row->{genre},
    #        $row->{trackCustom1},
    #        $row->{trackCustom2},
    #        $row->{trackCustom3},
    #    ) || print $DBH->errstr;

    # create master next
    #
    my $newMasterID = _insert_master(
        row       => $row,
        artist_id => $args{artist_id},
    );

    $STH{trackInsert}->execute(
        $args{album_id},            $newMasterID,         $row->{clientTrackID}, $row->{trackName},
        clean( $row->{trackName} ), $args{artist_id},     $row->{genre},         $row->{trackNumber},
        $row->{trackCustom1},       $row->{trackCustom2}, $row->{trackCustom3},
    ) || print $DBH->errstr;

    #    unless (_insert_product(PROD_TRACK, $trackID))
    #    {
    #        print "failed to create product record for $trackID\n";
    #        return 0;
    #    }

    my $trackID = $DBH->{mysql_insertid};
    return $trackID;
}

sub _insert_product {
    my ( $type, $assetID, $row ) = @_;
    return undef unless ( $type =~ m/^(A|T)$/ && $assetID =~ m/^\d+$/ );

    my $productType = ( $type eq 'A' ) ? RPS::DB::Item::Product::kProductTypeDigital : RPS::DB::Item::Product::kProductTypeDigitalTrack;

    # $STH{productInsert}->execute($type, $id) || print $DBH->errstr;
    $STH{productInsert}->execute(
        $assetID,
        $productType,
        $row->{clientAlbumID} . '-D',
        $row->{upc},
        $row->{upcAlt},
        $row->{releaseDate},
        1,    # product_status_id
    ) || print $DBH->errstr;

    my $digProductID = $DBH->{mysql_insertid};
    my $cdProductID;
    if ( $type eq 'A' ) {
        $STH{productInsert}->execute(
            $assetID,
            RPS::DB::Item::Product::kProductTypeCD,
            $row->{clientAlbumID} . '-CD',
            $row->{upc},
            $row->{upcAlt},
            $row->{releaseDate},
            1,    # product_status_id
        ) || print $DBH->errstr;
        $cdProductID = $DBH->{mysql_insertid};
    }

    return ( $digProductID, $cdProductID );
}

#
# MERGE: create product_tracks, the table that tells us what tracks are included on a given product
#
sub _insert_product_track {
    my (%args) = @_;

    # product_id => $album{$albumID}{productID},
    # track_id   => $newTrackID,
    # disc_number => $discNumber,
    # disc_track  => $row{trackNumber

# $sth{productTrackInsert} = $DBH->prepare("INSERT INTO product_track (product_id, track_id, disc_number, disc_track, is_bonus_track) VALUES (?,?,?,?,?)") || print $DBH->errstr;
    $STH{productTrackInsert}->execute( $args{product_id}, $args{track_id}, $args{disc_number}, $args{disc_track}, 0, )
      || print $DBH->errstr;

    return $DBH->{mysql_insertid};
}

sub _update_label {
    my ( $current, $row ) = @_;

    # we currently have no way to identify/update existing labels
    return 0;
}

sub _update_artist {
    my ( $current, $row ) = @_;

    # we currently have no way to identify/update existing artists
    return 0;
}

sub _update_album {
    my %args    = @_;
    my $current = $args{current};
    my $row     = $args{row};

    my ( $change, $orig, $new ) =
        lc( $current->{album_name} ) ne lc( $row->{albumName} )     ? ( CHANGE_NAME,    $current->{album_name},   $row->{albumName} )
      : $current->{release_date} ne $row->{releaseDate}             ? ( CHANGE_DATE,    $current->{release_date}, $row->{releaseDate} )
      : lc( $current->{upc} ) ne lc( $row->{upc} )                  ? ( CHANGE_UPC,     $current->{upc},          $row->{upc} )
      : lc( $current->{upc_alt} ) ne lc( $row->{upcAlt} )           ? ( CHANGE_UPCALT,  $current->{upc_alt},      $row->{upcAlt} )
      : $current->{artist_id} ne $args{artist_id}                   ? ( CHANGE_ARTIST,  $current->{artist_id},    $args{artist_id} )
      : $current->{label_id} ne $args{label_id}                     ? ( CHANGE_LABEL,   $current->{label_id},     $args{label_id} )
      : lc( $current->{catalog_id} ) ne lc( $row->{catalogID} )     ? ( CHANGE_CATALOG, $current->{catalog_id},   $row->{catalogID} )
      : lc( $current->{company_id} ) ne lc( $row->{albumCustom1} )  ? ( CHANGE_CUSTOM1, $current->{company_id},   $row->{albumCustom1} )
      : lc( $current->{vendor_id} ) ne lc( $row->{albumCustom2} )   ? ( CHANGE_CUSTOM2, $current->{vendor_id},    $row->{albumCustom2} )
      : lc( $current->{location_id} ) ne lc( $row->{albumCustom3} ) ? ( CHANGE_CUSTOM3, $current->{location_id},  $row->{albumCustom3} )
      :                                                               undef;
    return unless $change;

    $EXE && $STH{albumUpdate}->execute(
        $row->{albumName},    clean( $row->{albumName} ), $row->{releaseDate},  $row->{upc},
        $row->{upcAlt},       $args{artist_id},           $args{label_id},      $row->{catalogID},
        $row->{albumCustom1}, $row->{albumCustom2},       $row->{albumCustom3}, $current->{album_id}
    );

    return ( $change, $orig, $new );
}

sub _update_disc {
    my ( $current, $row ) = @_;

    # we currently have no way to identify/update existing discs
    return 0;
}

sub _update_track {
    my %args    = @_;
    my $current = $args{current};
    my $row     = $args{row};

    my ( $change, $orig, $new ) =
        lc( $current->{track_name} ) ne lc( $row->{trackName} )  ? ( CHANGE_NAME,     $current->{track_name},   $row->{trackName} )
      : $current->{disc_id} != $args{disc_id}                    ? ( CHANGE_DISC,     $current->{disc_id} != $args{disc_id} )
      : $current->{artist_id} != $args{artist_id}                ? ( CHANGE_ARTIST,   $current->{artist_id},    $args{artist_id} )
      : $current->{label_id} != $args{label_id}                  ? ( CHANGE_LABEL,    $current->{label_id},     $args{label_id} )
      : $current->{track_number} != $row->{trackNumber}          ? ( CHANGE_TRACKNUM, $current->{track_number}, $row->{trackNumber} )
      : $current->{duration} != $row->{duration}                 ? ( CHANGE_DURATION, $current->{duration},     $row->{duration} )
      : lc( $current->{isrc} ) ne lc( $row->{isrc} )             ? ( CHANGE_ISRC,     $current->{isrc},         $row->{isrc} )
      : lc( $current->{genre_name} ) ne lc( $row->{genre} )      ? ( CHANGE_GENRE,    $current->{genre_name},   $row->{genre} )
      : lc( $current->{custom_1} ) ne lc( $row->{trackCustom1} ) ? ( CHANGE_CUSTOM1,  $current->{custom_1},     $row->{trackCustom1} )
      : lc( $current->{custom_2} ) ne lc( $row->{trackCustom2} ) ? ( CHANGE_CUSTOM2,  $current->{custom_2},     $row->{trackCustom2} )
      : lc( $current->{custom_3} ) ne lc( $row->{trackCustom3} ) ? ( CHANGE_CUSTOM3,  $current->{custom_3},     $row->{trackCustom3} )
      :                                                            undef;
    return unless $change;

    $EXE && $STH{trackUpdate}->execute(
        $row->{trackName},    clean( $row->{trackName} ), $args{disc_id}, $args{artist_id}, $args{label_id},
        $row->{trackNumber},  $row->{duration},           $row->{isrc},   $row->{genre},    $row->{trackCustom1},
        $row->{trackCustom2}, $row->{trackCustom3},       $current->{track_id}
    );

    return ( $change, $orig, $new );
}

sub _save_change {
    my %args = @_;

    if ( $args{action} eq RECORD_INSERT ) {
        $STH{metaInsert}->execute( $args{action}, $args{prod_type}, $args{owner_id}, $args{new_value}, $LINENUM );
        return $STH{metaInsert}->rows();
    } elsif ( $args{action} eq RECORD_UPDATE ) {
        $STH{metaUpdate}
          ->execute( $args{action}, $args{prod_type}, $args{owner_id}, $args{changed}, $args{orig_value}, $args{new_value}, $LINENUM );
        return $STH{metaUpdate}->rows();
    } else {
        print "unknown action '$args{action}'";
        return 0;
    }
}

sub _has_rows {
    my $count = 0;

    #    map {
    #        $count += $rsdb->DoCmd("select count(*) from $_")->fetchrow_arrayref()->[0]
    #    } qw(label artist album album_disc track product);
    map { $count += $rsdb->DoCmd("select count(*) from $_")->fetchrow_arrayref()->[0] } qw(label artist album track product);

    return $count;
}

sub _load_current {
    my ( $type, $row, $other ) = @_;

    my ( $sth, $arg );

    if ( $type eq 'label' ) {
        $sth = $STH{labelSearch};
        $arg = $row->{labelName};
    } elsif ( $type eq 'album_artist' ) {
        $sth = $STH{artistSearch};
        $arg = $row->{albumArtist};
    } elsif ( $type eq 'album_artist_clean' ) {
        $sth = $STH{artistSearchClean};
        $arg = clean( $row->{albumArtist} );
    } elsif ( $type eq 'track_artist' ) {
        $sth = $STH{artistSearch};
        $arg = $row->{trackArtist};
    } elsif ( $type eq 'track_artist_clean' ) {
        $sth = $STH{artistSearchClean};
        $arg = clean( $row->{trackArtist} );
    } elsif ( $type eq 'album' ) {
        $sth = $STH{albumSearch};
        $arg = $row->{clientAlbumID};
    } elsif ( $type eq 'track' ) {
        $sth = $STH{trackSearch};
        $arg = $row->{clientTrackID};
    } elsif ( $type eq 'disc' )    # this one is treated slightly different
    {
        my $match_count = 0;
        my $match;

        $STH{discSearch}->execute($other);
        while ( my $disc = $STH{discSearch}->fetchrow_hashref() ) {
            if ( $disc->{disc_number} == $row->{discNumber} ) {
                $match_count++;
                $match = $disc;
            }
        }

        return $match if ( $match_count == 1 );
        if ( $match_count > 1 ) {
            print "multiple disc matches for $other ($row->{discNumber})\n";
            return undef;
        }
        return undef;
    }

    #print STDERR "arg; $arg\n";
    my $key = lc($arg);

    unless ( $CACHE{$type}{$key} ) {
        $sth->execute($arg);
        return undef unless $sth->rows();

        if ( $sth->rows() > 1 ) {
            print "multiple $type matches for $arg\n";
            return undef;
        }

        return $sth->fetchrow_hashref() if ( $type eq 'track' );    # no need to cache tracks
        $CACHE{$type}{$key} = $sth->fetchrow_hashref();
    }

    #else { print STDERR "cache hit\n"; }

    return $CACHE{$type}{$key};
}

sub _get_disc_count {
    my $album_id = shift;
    $STH{discCountSearch}->execute($album_id);
    return $STH{discCountSearch}->fetchrow_arrayref()->[0];
}

sub _get_track_count {
    my $disc_id = shift;
    $STH{trackCountSearch}->execute($disc_id);
    return $STH{trackCountSearch}->fetchrow_arrayref()->[0];
}

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 _get_fields_from_header {
    my $header = shift;

    chomp($header);
    my @cols = split /\t/, $header;
    @cols = trimquotes(@cols);
    @cols = escapequotes(@cols);

    my @header_formats = (

        # format 0
        [
            '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',      'track-no',     'track-minutes',  'track-seconds',  'track-artist',   'isrc',
            'track-id',     'genre',        'track-custom-1', 'track-custom-2', 'track-custom-3'
        ],

        # format 1 (disc-name added)

        # fixed typo (?)
        # disk-no => disc-no
        # disk-name => disc-name
        # -jff-
        [
            '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'
        ],

        #### formats from old importer

        # format 2
        [
            'album-name', 'album-artist', 'label',    'catalog-id',   'upc',  'album-id', 'track-name', 'disc-no',
            'disc-name',  'track-no',     'duration', 'track-artist', 'isrc', 'track-id'
        ],

        # format 3
        [
            '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'
        ],

        # format 4
        [
            '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'
        ],

        # format 5
        [
            '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'
        ],

        # format 6
        [
            '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'
        ],
    );

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

    while ( $formatIndex < scalar @header_formats ) {
        my $i = 0;
        while ( $i < scalar @cols ) {
            if ( $cols[$i] !~ m/$header_formats[$formatIndex]->[$i]/i ) {
                print STDERR $cols[$i] . " != " . $header_formats[$formatIndex]->[$i] . "\n";
                last;
            }
            $match++;
            $i++;
        }
        last if ( $match == scalar @cols );
        $formatIndex++;
        $match = 0;
    }

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

    return $formatIndex == 0
      ? qw(albumName albumArtist labelName catalogID upc clientAlbumID releaseDate upcAlt albumCustom1 albumCustom2 albumCustom3 trackName discNumber trackNumber trackMinutes trackSeconds trackArtist isrc clientTrackID genre trackCustom1 trackCustom2 trackCustom3)
      : $formatIndex == 1
      ? qw(albumName albumArtist labelName catalogID upc clientAlbumID releaseDate upcAlt albumCustom1 albumCustom2 albumCustom3 trackName discNumber discName trackNumber trackMinutes trackSeconds trackArtist isrc clientTrackID genre trackCustom1 trackCustom2 trackCustom3)
      : $formatIndex == 2
      ? qw(albumName albumArtist labelName catalogID upc clientAlbumID trackName discNumber discName trackNumber trackDuration trackArtist isrc clientTrackID)
      : $formatIndex == 3
      ? qw(albumName albumArtist labelName catalogID upc clientAlbumID trackName discNumber discName trackNumber trackDuration trackArtist isrc clientTrackID genre)
      : $formatIndex == 4
      ? qw(albumName albumArtist labelName catalogID upc clientAlbumID trackName discNumber discName trackNumber trackDuration trackArtist isrc clientTrackID genre upcAlt)
      : $formatIndex == 5
      ? qw(albumName albumArtist labelName labelID catalogID upc clientAlbumID albumCustom1 albumCustom2 trackName discNumber discName trackNumber trackMinutes trackSeconds trackArtist isrc clientTrackID genre)
      : $formatIndex == 6
      ? qw(albumName albumArtist labelName albumCustom3 catalogID upc clientAlbumID albumCustom1 albumCustom2 trackName discNumber discName trackNumber trackMinutes trackSeconds trackArtist isrc clientTrackID genre)
      : '';

}