#!/usr/bin/perl

use strict;

use Getopt::Std;

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 %importStatus = (
    labels  => { updates => 0, inserts => 0 },
    artists => { updates => 0, inserts => 0 },
    albums  => { updates => 0, inserts => 0, nochange => 0 },
    discs   => { updates => 0, inserts => 0 },
    tracks  => { updates => 0, inserts => 0, nochange => 0 },
);

my %label     = ();
my %artist    = ();
my %album     = ();
my %track     = ();
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: $_\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(
                row       => \%row,
                label_id  => $label{$labelName}{ID},
                artist_id => $artist{$albumArtist}{ID},
              )
              : $ALBUM_ID++;

            unless ( $album{$albumID}{ID} ) {
                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} ) {
            print "missing required id's for $trackID\n";
            $LINENUM++;
            next;
        }

        $album{$albumID}{discs}{$discNumber}{tracks}++,
          $album{$albumID}{tracks}{$trackID} = $EXE
          ? _insert_track(
            row       => \%row,
            disc_id   => $album{$albumID}{discs}{$discNumber}{ID},
            label_id  => $label{$labelName}{ID},
            artist_id => $artist{$trackArtist}{ID},
          )
          : $TRACK_ID++;

        $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{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{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{productInsert} =
      $DBH->prepare("INSERT INTO product (product_type, owner_id, date_created) VALUES (?,?,NOW())") || 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{artistSearchClean} = $DBH->prepare("SELECT * FROM artist WHERE artist_name_clean=?")          || print $DBH->errstr;
        $sth{artistUpdate}      = $DBH->prepare("UPDATE artist SET artist_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{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{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;

    my $albumID = $DBH->{mysql_insertid};
    unless ( _insert_product( PROD_ALBUM, $albumID ) ) {
        print "failed to create product record for $albumID\n";
        return 0;
    }

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

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;

    my $trackID = $DBH->{mysql_insertid};
    unless ( _insert_product( PROD_TRACK, $trackID ) ) {
        print "failed to create product record for $trackID\n";
        return 0;
    }

    return $trackID;
}

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

    $STH{productInsert}->execute( $type, $id ) || 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);

    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)
        [
            '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',
            'disk-no',      'disk-name',    'track-no',       'track-minutes',  'track-seconds',  'track-artist',
            'isrc',         'track-id',     'genre',          'track-custom-1', 'track-custom-2', 'track-custom-3'
        ],
    );

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

    while ( $formatIndex < scalar @header_formats ) {
        my $i = 0;
        while ( $i < scalar @cols ) {
            last if ( $cols[$i] !~ m/$header_formats[$formatIndex]->[$i]/i );
            $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)
      : '';

}