#!/usr/bin/perl
use strict;

use Data::Dumper;
use Getopt::Std;
use POSIX qw(ceil);
use POSIX ":sys_wait_h";
use Sys::Hostname;
use Carp;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::Assert;
use Common::TextProgressBar;
use Common::Timer;
use Common::RSDB;
use Common::Util;
use Common::DB::Item::Service;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::MechanicalCarryover;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::Track;
use RPS::DB::Item::TrackContract;
use RPS::DB::Item::Master;
use RPS::DB::Item::Album;

#use RPS::DB::Item::License;
use RPS::DB::Item::LicenseReserve;
use RPS::DB::Item::LicenseReserveLiquidation;
use RPS::DB::Item::StatRate;
use RPS::DB::Item::ProductTrack;
use RPS::DB::Item::Song;

use RPS::DB::Item::Publisher;
use RPS::DB::Item::Product;
use RPS::DB::Item::ControlledComposition;

use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::MechanicalRun;
use RPS::DB::Item::MechanicalStatement;
use RPS::DB::Item::MechanicalStatementItem;
use RPS::DB::Item::MechanicalStatementItem;
use RPS::DB::Item::MechanicalStatementTrack;
use RPS::DB::Item::MechanicalStatementLicense;
use RPS::DB::Item::MechanicalStatementAdjustmentItem;
use RPS::DB::Item::SaleMechanicalStatementItemMap;

use RPS::License::TrackLicense;
use RPS::License::TrackLicenseList;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::ReserveLiquidation;

use constant kRunFile => '/tmp/integration_alter_run_id';

# I want this script to do it all, which means:
# - Copies over tables from raptor01
# - Sets up the RAPTOR_* version of the database
# - Does the required alters.
#
# - Not going to be able to use the DB::Item stuff, since that latches on to a particular
# - database, and we need to be able to move stuff between dbs...
#

my $gVerbosityLevel = 1;
my $gDropOldColumns = 0;

# Parse the command-line options.
#
my %options;
_parseCommandLine( \%options );

my @clientIDs;
if ( $options{clientID} ) {
    push @clientIDs, $options{clientID};
} else {
    @clientIDs = _getAllAvailableClientIDs();
}

my $runID;
my $recoverFlag;

# If there is a run file hanging around, get the run from that.
#
if ( -e kRunFile ) {
    open RUNFILE, kRunFile;
    my $line = <RUNFILE>;
    close RUNFILE;
    chomp $line;
    $runID       = $line;
    $recoverFlag = 1;
}

if ( !$runID ) {
    $runID = time;
}

open RUNFILE, "> " . kRunFile;
print RUNFILE "$runID\n";
_report( "token: $runID", 4 );
close RUNFILE;

# !!! I want to make this a little more efficient (and have to enter my password less).
# So I'm going to have it batch up the extraction
#
_transferDBs( $runID, $recoverFlag, @clientIDs );

# Convert RSCOMMON first
#
#my $userIDMap = _convertRSCommon();

foreach my $clientID (@clientIDs) {
    _alterAlbum($clientID);
    _alterArtist($clientID);
    _alterProduct($clientID);
    _alterTrack($clientID);
    _alterTrackLicense($clientID);
    _alterArtistContract($clientID);
    _alterPayor($clientID);
    _alterMechanicalStatement($clientID);
    _alterArtistRoyaltyStatement($clientID);
    _createNewTables($clientID);
    _copyMissingTables($clientID);
    _mergeFile($clientID);
    _mergeSale($clientID);

    # !!! disabling the user id update for the moment.
    #
    #    _updateUserIDs($clientID, $userIDMap);

    _createAggTables($clientID);

    _cleanup($clientID);
}

unlink kRunFile;

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

sub _mergeFile {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    _report("copying over file table");
    if ( 0 == system( "mysqldump -uroot $dbName" . "_RAPTOR file > /tmp/db_transfer" ) ) {
        system("mysql -uroot $dbName < /tmp/db_transfer");
    } else {
        die "Error! unable to mysqldump file table";
    }
}

sub _mergeSale {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    _report("preserving sale status codes");
    if ( 0 != system("/usr/bin/perl /app/tools/rps/bin/royalty/preserve_sale_status -c $clientID > /tmp/sale_status") ) {
        die "Error preserving sale status";
    }

    _report("copying over sale table");
    if ( 0 != system( "mysqldump -uroot $dbName" . "_RAPTOR sale > /tmp/db_transfer" ) ) {
        die "Error! unable to mysqldump sale table";
    }

    if ( 0 != system("mysql -uroot $dbName < /tmp/db_transfer") ) {
        die "Error! unable to copy sale table";
    }

    # alter sale table
    #
    _report("altering sale table");
    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the status columns
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE sale ADD COLUMN mechanical_royalty_status tinyint(3) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE sale ADD COLUMN artist_royalty_status tinyint(3) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # restore status codes
    #
    if ( 0 != system("/usr/bin/perl /app/tools/rps/bin/royalty/restore_sale_status -c $clientID < /tmp/sale_status") ) {
        die "Error restoring sale status";
    }
}

sub _readClientTrackIDFromRaptor {
    my ($clientID) = @_;
    assert($clientID);

    my %map;

    # Can't use a fancy db abstraction layer safely, so we'll query this the old fashioned way
    #
    my $dbName = Common::RSDB::ClientIDToDBName($clientID);
    $dbName .= '_RAPTOR';
    my $dbo = new Common::RSDB( client_id => 999, db_name => $dbName );
    my $sql = "select track_id, client_track_id, genre_name, track_name_clean from track";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $map{ $hr->{track_id} } = {
            clientTrackID  => $hr->{client_track_id},
            genreName      => $hr->{genre_name},
            trackNameClean => $hr->{track_name_clean},
        };
    }

    return \%map;
}

sub _createNewTables {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Create the 'payor' table
    #
    #my $sql = "
    #CREATE TABLE `payor` (
    #  `payor_id` int(10) unsigned NOT NULL auto_increment,
    #  `name` varchar(255) NOT NULL default '',
    #  `street_address` text NOT NULL,
    #  `city` varchar(80) NOT NULL default '',
    #  `state_province` varchar(80) NOT NULL default '',
    #  `country_code` char(2) NOT NULL default 'US',
    #  `postal_code` varchar(15) NOT NULL default '',
    #  `email` varchar(80) NOT NULL default '',
    #  `phone_number` varchar(30) NOT NULL default '',
    #  `fax_number` varchar(30) NOT NULL default '',
    #  `cycle_code` int(3) unsigned NOT NULL default '0',
    #  `begin_month_code` int(3) unsigned NOT NULL default '0',
    #  `comments` text NOT NULL,
    #  `tax_id` varchar(30) NOT NULL default '',
    #  `client_payor_id` varchar(255) NOT NULL default '',
    #  `inactive` tinyint(1) unsigned NOT NULL default '0',
    #  `is_default` tinyint(1) unsigned NOT NULL default '0',
    #  `date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
    #  `created_by` int(10) unsigned NOT NULL default '0',
    #  `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    #  `modified_by` int(10) unsigned NOT NULL default '0',
    #  PRIMARY KEY  (`payor_id`),
    #  KEY `name` (`name`)
    #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    #";
    #
    #    my $dbo = Common::RSApp::GetClientDB();
    #    if (!$dbo->DoCmd($sql))
    #    {
    #        _report("error " . $dbo->LastErrorStr());
    #        return;
    #    }

    my $sql = "
CREATE TABLE `command_log` (
  `command_log_id` int(10) unsigned NOT NULL auto_increment,
  `command_name` varchar(64) NOT NULL default '',
  `command_id` int(10) unsigned NOT NULL,
  `command` text,
  `date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
  `created_by` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (`command_log_id`),
  KEY `command_name_id` (`command_name`, `command_id`),
  KEY `created_by` (`created_by`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
";
    my $dbo = Common::RSApp::GetClientDB();
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
}

sub _alterMechanicalStatement {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the payor_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE mechanical_statement ADD COLUMN payor_id int(10) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
}

sub _alterArtistRoyaltyStatement {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the payor_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE artist_royalty_statement ADD COLUMN payor_id int(10) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
}

sub _alterPayor {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the payor_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE payor ADD COLUMN inactive tinyint(3) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE payor ADD COLUMN client_payor_id varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE payor ADD COLUMN is_default tinyint(3) unsigned not null default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
}

sub _alterArtistContract {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the payor_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE new_artist_contract ADD COLUMN payor_id int(10) unsigned NOT NULL default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Make it a key
    #
    $sql = 'ALTER TABLE new_artist_contract ADD KEY `payor_id` (`payor_id`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

}

sub _alterTrackLicense {
    my ($clientID) = @_;
    assert($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the payor_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE track_license ADD COLUMN payor_id int(10) unsigned NOT NULL default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Make it a key
    #
    $sql = 'ALTER TABLE track_license ADD KEY `payor_id` (`payor_id`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Add the percentage_of_sales column
    #
    $sql = "ALTER TABLE track_license ADD COLUMN percentage_of_sales decimal(8,4) unsigned default 100 after rate_percentage";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

}

sub _alterTrack {
    my ($clientID) = @_;
    assert($clientID);

    my $clientTrackIDMap = _readClientTrackIDFromRaptor($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the client_track_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE track ADD COLUMN client_track_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Make client_track_id a key
    #
    $sql = 'ALTER TABLE track ADD KEY `client_track_id` (`client_track_id`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Add genre_name column
    #
    $sql = 'ALTER TABLE track ADD COLUMN genre_name varchar(32)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    $sql = 'ALTER TABLE track ADD COLUMN title_clean varchar(255)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = 'ALTER TABLE track ADD COLUMN title_alt varchar(255)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = 'ALTER TABLE track ADD COLUMN title_alt_clean varchar(255)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Add the custom columns
    #
    $sql = "ALTER TABLE track ADD COLUMN custom_1 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE track ADD COLUMN custom_2 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE track ADD COLUMN custom_3 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Fill in client_track_id and genre_name
    #
    while ( my ( $trackID, $raptorInfoHash ) = each(%$clientTrackIDMap) ) {
        my $clientTrackID = $raptorInfoHash->{clientTrackID};
        my $genreName     = $raptorInfoHash->{genreName};
        my $cleanName     = $raptorInfoHash->{trackNameClean};

        $sql =
            "UPDATE track SET client_track_id = "
          . $dbo->DBQuote($clientTrackID)
          . ", genre_name = "
          . $dbo->DBQuote($genreName)
          . ", title_clean = "
          . $dbo->DBQuote($cleanName)
          . " WHERE track_id = "
          . $trackID;
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
    }

    # Change 'track_number' to 'track_order'
    #
    $sql = 'ALTER TABLE track CHANGE COLUMN track_number track_order tinyint(3) unsigned DEFAULT NULL';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Create some additional indicies
    #
    $sql = "create index artist_id on track (artist_id)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }
    $sql = "create index title_clean on track (title_clean)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }
    $sql = "create fulltext index track_title_text on track (title)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }

}

sub _createAggTables {
    my ($clientID) = @_;
    _report("building agg tables");

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    # I think I may want to truncate them first?
    #
    foreach my $tableName (
        'agg_file_summary',   'agg_m_artist_sales',  'agg_m_sales',        'agg_m_summary',
        'agg_q_artist_sales', 'agg_q_product_sales', 'agg_q_sales',        'agg_q_summary',
        'agg_q_top_products', 'agg_sale_log',        'agg_y_artist_sales', 'agg_y_product_sales',
        'agg_y_sales',        'agg_y_summary',       'agg_sale_stage',     'agg_m_product_sales'
      ) {
        system("mysql -uroot $dbName -e 'truncate table $tableName'");
    }
    system("/app/tools/stats/bin/build_sale_aggregates.pl -c $clientID");
}

sub _copyMissingTables {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    foreach my $tableName (
        'format',             'meta_update',           'pattern_level',      'period',
        'product_input_map',  'product_pattern_match', 'agg_file_summary',   'agg_m_artist_sales',
        'agg_m_sales',        'agg_m_summary',         'agg_q_artist_sales', 'agg_q_product_sales',
        'agg_q_sales',        'agg_q_summary',         'agg_q_top_products', 'agg_sale_log',
        'agg_y_artist_sales', 'agg_y_product_sales',   'agg_y_sales',        'agg_y_summary',
        'agg_sale_stage',     'agg_m_product_sales'
      ) {
        _report("copying over $tableName");
        if ( 0 == system( "mysqldump -uroot $dbName" . "_RAPTOR $tableName > /tmp/db_transfer" ) ) {
            system("mysql -uroot $dbName < /tmp/db_transfer");
        } else {

            # Assume error because table does not exist - create it.
            # - I just have the schema for meta_update for now...
            #
            if ( 'meta_update' eq $tableName ) {
                open TEMPSQL, "/tmp/tmp.sql";
                print TEMPSQL "CREATE TABLE `meta_update` ("
                  . "`action` char(1) NOT NULL default '',"
                  . "`product_type` char(1) NOT NULL default '',"
                  . "`owner_id` int(10) unsigned NOT NULL default '0',"
                  . "`changed` varchar(20) default NULL,"
                  . "`orig_value` varchar(255) default NULL,"
                  . "`new_value` varchar(255) default NULL,"
                  . "`line_num` int(10) unsigned default NULL"
                  . ") ENGINE=MyISAM DEFAULT CHARSET=latin1;\n";
                close TEMPSQL;
                system("mysql -uroot $dbName < /tmp/tmp.sql");
            }
        }
    }
}

sub _transferDBs {
    my ( $runID, $recoverFlag, @clientIDs ) = @_;

    my $transDir = "DBTRANSFER_$runID";
    if ( !$recoverFlag ) {
        my @commands;
        push @commands, "cd /tmp";
        push @commands, "mkdir $transDir";

        #        push @commands, "mysqldump -uroot RSCOMMON > $transDir/RSCOMMON";

        foreach my $clientID (@clientIDs) {
            my $dbName = Common::RSDB::ClientIDToDBName($clientID);
            push @commands, "mysqldump -uroot $dbName > $transDir/$dbName";
        }

        push @commands, "tar czvf $transDir.tz $transDir";

        my $bigOleCommand = join( ";", @commands );
        _report("\n+++ Compressing remote databases +++\n");
        system("ssh raptor01 '$bigOleCommand'");
    }

    _report("\n+++ Transfering databases +++\n");
    my $transferStatus = system("rsync --timeout=60 -P -e ssh raptor01:/tmp/$transDir.tz /tmp/$transDir.tz");
    _report( "transfer status: $transferStatus", 2 );
    die "ERROR - transfer failed" if ( 0 != $transferStatus );

    _report("\n+++ Uncompressing databases +++\n");
    system("cd /tmp;tar xzvf $transDir.tz");

    _report("\n+++ Creating local copies of the raptor databases+++\n");
    if (0) {
        my $dbName    = "RSCOMMON";
        my $newDBName = "RSCOMMON_RAPTOR";
        system("mysql -uroot -e 'create database $newDBName'");
        system("mysql -uroot $newDBName < /tmp/$transDir/$dbName");
    }

    foreach my $clientID (@clientIDs) {
        my $dbName    = Common::RSDB::ClientIDToDBName($clientID);
        my $newDBName = "$dbName" . "_RAPTOR";
        system("mysql -uroot -e 'create database $newDBName'");
        system("mysql -uroot $newDBName < /tmp/$transDir/$dbName");
    }
}

sub _cleanup {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    $dbName .= '_RAPTOR';
    `mysql -uroot -e 'drop database $dbName'`;
}

sub _updateUserIDs {
    my ( $clientID, $userIDMap ) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    my $dbo = new Common::RSDB( client_id => $clientID );

    my @tables = (
        'new_artist_contract', 'album',              'expense',        'artist_payee',
        'finance_account',     'master',             'mechanical_run', 'mechanical_statement',
        'product',             'publisher',          'song',           'track',
        'track_license',       'artist_royalty_run', 'payor'
    );

    foreach my $tableName (@tables) {
        my $keyName = $tableName . "_id";
        if ( 'new_artist_contract' eq $tableName ) {
            $keyName = 'artist_contract_id';
        }

        # Not all tables have both created_by and modified_by.
        # So I'll select everything so I can be smart about what I try and change.
        #
        my $sql = "SELECT * from $tableName";
        my $sth = $dbo->DoCmd($sql);
        while ( my $hr = $sth->fetchrow_hashref() ) {
            my $keyValue = $hr->{$keyName};

            # not going for speed here, obviously...
            #
            if ( $hr->{created_by} && $userIDMap->{ $hr->{created_by} } ) {
                my $newUserID = $userIDMap->{ $hr->{created_by} };
                $sql = "UPDATE $tableName SET created_by=$newUserID WHERE $keyName=$keyValue";
                $dbo->DoCmd($sql);
            }
            if ( $hr->{modified_by} && $userIDMap->{ $hr->{modified_by} } ) {
                my $newUserID = $userIDMap->{ $hr->{modified_by} };
                $sql = "UPDATE $tableName SET modified_by=$newUserID WHERE $keyName=$keyValue";
                $dbo->DoCmd($sql);
            }
        }
    }
}

sub _convertRSCommon {
    _report("converting RSCOMMON");

    #    _transferRaptorRSCOMMON();

    # need to merge the user table first, and build the raptor->rps id mapping table.
    # Then have to _merge_ the phone, address, user_access and user_logins tables

    my ( $userIDMap, $newUsers ) = _mergeUser();

    _mergeAddress( $userIDMap, $newUsers );

    _alterAddress();

    _alterUser( $userIDMap, $newUsers );

    _alterClient();

    _renameRSCOMMON_RAPTOR();

    return $userIDMap;
}

sub _renameRSCOMMON_RAPTOR {
    `mysqldump -uroot RSCOMMON_RAPTOR > /tmp/RSCOMMON_RAPTOR`;
    `mysql -uroot -e 'drop database RSCOMMON_RAPTOR'`;
    `mysql -uroot RSCOMMON < /tmp/RSCOMMON_RAPTOR`;
}

sub _alterUser {
    my ( $userIDMap, $newUsers ) = @_;

    my $dbo_raptor = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON_RAPTOR' );
    my $dbo_rps    = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON' );

    # Make a new user id map, which we'll use later when iterating through the table.
    #
    my %newUserMap;
    foreach my $newUserData (@$newUsers) {
        my $rpsUserID = $newUserData->{user_id};
        my $newID     = $userIDMap->{$rpsUserID};
        $newUserMap{$newID} = 1;
    }

    # Ditch the phone_id column, replace with phone1,phone2,phone3
    #
    my $sql = "ALTER TABLE user ADD COLUMN phone1 varchar(32) default NULL";
    if ( !$dbo_raptor->DoCmd($sql) ) {
        _report( "error " . $dbo_raptor->LastErrorStr() );
        return;
    }

    $sql = "ALTER TABLE user ADD COLUMN phone2 varchar(32) default NULL";
    if ( !$dbo_raptor->DoCmd($sql) ) {
        _report( "error " . $dbo_raptor->LastErrorStr() );
        return;
    }

    $sql = "ALTER TABLE user ADD COLUMN phone3 varchar(32) default NULL";
    if ( !$dbo_raptor->DoCmd($sql) ) {
        _report( "error " . $dbo_raptor->LastErrorStr() );
        return;
    }

    # Now iterate through the user table and populate phone1.
    # (I happen to know there these is no more than 1 phone per user... so I can cheat a bit)
    #
    my $sth = $dbo_raptor->DoCmd("SELECT * from user");
    while ( my $hr = $sth->fetchrow_hashref() ) {
        if ( $hr->{phone_id} ) {
            if ( $newUserMap{ $hr->{user_id} } ) {

                # Get the phone record from RPS
                #
                my $pSTH = $dbo_rps->DoCmd( "SELECT phone_number from phone where phone_id=" . $hr->{phone_id} );
                my $pHR  = $pSTH->fetchrow_hashref();
                if ($pHR) {
                    my $sql = "UPDATE user SET phone1=" . $dbo_raptor->DBQuote( $pHR->{phone_number} ) . " WHERE user_id=" . $hr->{user_id};
                    $dbo_raptor->DoCmd($sql);
                }
            } else {
                my $pSTH = $dbo_raptor->DoCmd( "SELECT phone_number from phone where phone_id=" . $hr->{phone_id} );
                my $pHR  = $pSTH->fetchrow_hashref();
                if ($pHR) {
                    my $sql = "UPDATE user SET phone1=" . $dbo_raptor->DBQuote( $pHR->{phone_number} ) . " WHERE user_id=" . $hr->{user_id};
                    $dbo_raptor->DoCmd($sql);
                }
            }
        }
    }
}

sub _mergeAddress {
    my ( $userIDMap, $newUsers ) = @_;

    my $dbo_raptor = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON_RAPTOR' );
    my $dbo_rps    = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON' );

    # Here's the trick - unlike user, we don't have a unique field we can use to determine whether
    # this address is 'new' or not.
    #
    # I don't really _care_ about users that have _moved_. I only care about _new_ users... I will need
    # to bring their addresses over.  The rest, honestly, are irrelevant.
    #
    # Going to also need to update the address_id field for these new users to reflect the new address ids.
    #

    # Bring over addresses from RPS that belong to new users (re-mapping the user_id field along the way).
    #
    foreach my $rpsUser (@$newUsers) {
        my $rpsUserID = $rpsUser->{user_id};
        my $newID     = $userIDMap->{$rpsUserID};

        my $sth = $dbo_rps->DoCmd("SELECT * from address where user_id=$rpsUserID");
        my $newPrimaryID;
        while ( my $hr = $sth->fetchrow_hashref() ) {
            my $oldAddressID = $hr->{address_id};
            my $sql          = "INSERT INTO address SET";
            $sql .= " user_id=$newID";
            $sql .= ",address_type_id=" . $hr->{address_type_id};
            $sql .= ",address1=" . $dbo_raptor->DBQuote( $hr->{address1} ) if ( $hr->{address1} );
            $sql .= ",address2=" . $dbo_raptor->DBQuote( $hr->{address2} ) if ( $hr->{address2} );
            $sql .= ",city=" . $dbo_raptor->DBQuote( $hr->{city} ) if ( $hr->{city} );
            $sql .= ",state=" . $dbo_raptor->DBQuote( $hr->{state} ) if ( $hr->{state} );
            $sql .= ",province=" . $dbo_raptor->DBQuote( $hr->{province} ) if ( $hr->{province} );
            $sql .= ",postal_code=" . $dbo_raptor->DBQuote( $hr->{postal_code} ) if ( $hr->{postal_code} );
            $sql .= ",country=" . $dbo_raptor->DBQuote( $hr->{country} ) if ( $hr->{country} );
            $sql .= ",date_created=" . $dbo_raptor->DBQuote( $hr->{date_created} ) if ( $hr->{date_created} );
            $sql .= ",date_modified=" . $dbo_raptor->DBQuote( $hr->{date_modified} ) if ( $hr->{date_modified} );

            $dbo_raptor->DoCmd($sql);
            my $newAddressID = $dbo_raptor->LastInsertID();

            # Was this address the user's primary?
            #
            if ( $rpsUser->{address_id} == $oldAddressID ) {
                $newPrimaryID = $newAddressID;
            }
        }

        if ($newPrimaryID) {
            $dbo_rps->DoCmd("UPDATE user set address_id=$newPrimaryID where user_id=$newID");
        }
    }
}

sub _transferRaptorRSCOMMON {

    my $dbName = 'RSCOMMON';
    _report( "dbName : $dbName", 4 );

    # So, first we'll need to tell the remote host to dump the raptor database.
    #
    _report("-- extracting database $dbName on raptor01");
    my $cmd = "rm -f /tmp/$dbName*;mysqldump -uroot $dbName > /tmp/$dbName;gzip /tmp/$dbName";
    `ssh raptor01 '$cmd'`;

    # Now we copy it over to here.
    #
    _report("-- copying database dump to local host");
    `rm -f /tmp/$dbName*`;
    `scp -o 'TCPKeepAlive yes' raptor01:/tmp/$dbName.gz /tmp/$dbName.gz`;

    # Expand it.
    #
    _report("-- uncompressing data");
    `gunzip /tmp/$dbName.gz`;

    # Create the new raptor db on the local host.
    #
    my $newDBName = $dbName . "_RAPTOR";
    _report("-- importing data into new database $newDBName");
    `mysql -uroot -e 'create database $newDBName'`;
    `mysql -uroot $newDBName < /tmp/$dbName`;
}

sub _mergeUser {
    my $dbo_raptor = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON_RAPTOR' );
    my $dbo_rps    = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON' );

    my %map;
    my @newUsers;

    # Iterate through the RPS version of user.
    # We're looking to find both new users, and
    # users that have different ids.
    #
    my $searchSQL = "SELECT * from user";
    my $searchSTH = $dbo_rps->DoCmd($searchSQL);
    while ( my $hr = $searchSTH->fetchrow_hashref() ) {
        my $email     = $hr->{email};
        my $raptorSTH = $dbo_raptor->DoCmd( "SELECT user_id from user where email=" . $dbo_raptor->DBQuote($email) );
        my $raptorHR  = $raptorSTH->fetchrow_hashref();
        if ( !$raptorHR || !$raptorHR->{user_id} ) {

            #new user = insert at the end (later)
            #
            push @newUsers, $hr;
        } elsif ( $raptorHR->{user_id} != $hr->{user_id} ) {

            # same user, different id
            #
            $map{ $hr->{user_id} } = $raptorHR->{user_id};
            _report( "user " . $hr->{user_id} . " exists in Raptor as id " . $raptorHR->{user_id}, 4 );
        }
    }

    # Create entries for all the new users
    # Going to have to re-map primary_contact_ids for these that are being moved.
    # I believe I can limit this to just these that have been moved.
    #
    my @needToAdjustContactIDs;
    _report( "have to create " . scalar @newUsers . " users", 4 );
    foreach my $data (@newUsers) {

        # Raptor is reasonably full, so don't worry about trying to re-use the id.
        # Just take this new user onto the back of the line.
        #
        my $sql = "INSERT INTO user SET";
        $sql .= " email=" . $dbo_rps->DBQuote( $data->{email} );
        $sql .= ",password=" . $dbo_rps->DBQuote( $data->{password} ) if ( defined $data->{password} );
        $sql .= ",first_name=" . $dbo_rps->DBQuote( $data->{first_name} );
        $sql .= ",last_name=" . $dbo_rps->DBQuote( $data->{last_name} );
        $sql .= ",address_id=" . $data->{address_id} if ( defined $data->{address_id} );
        $sql .= ",phone_id=" . $data->{phone_id} if ( defined $data->{phone_id} );
        $sql .= ",primary_contact_id=" . $data->{primary_contact_id} if ( defined $data->{primary_contact_id} );
        $sql .= ",secondary_contact_id=" . $data->{secondary_contact_id} if ( defined $data->{secondary_contact_id} );
        $sql .= ",disabled=" . $data->{disabled};
        $sql .= ",date_created=" . $dbo_rps->DBQuote( $data->{date_created} ) if ( defined $data->{date_created} );
        $sql .= ",date_modified=" . $dbo_rps->DBQuote( $data->{date_modified} ) if ( defined $data->{date_modified} );

        $dbo_raptor->DoCmd($sql);
        my $newID = $dbo_raptor->LastInsertID();
        _report( " moved " . $data->{user_id} . " to $newID", 4 );
        $map{ $data->{user_id} } = $newID;
        push @needToAdjustContactIDs, $newID;
    }

    _report( "map: - " . Dumper( \%map ), 4 );

    # Now adjust the contact ids for these that have moved.
    #
    foreach my $id (@needToAdjustContactIDs) {
        my $sql = "select primary_contact_id, secondary_contact_id from user where user_id=$id";
        my $sth = $dbo_raptor->DoCmd($sql);
        my $hr  = $sth->fetchrow_hashref();
        my $newPrimary;
        my $newSecondary;
        if ( $hr->{primary_contact_id} > 0 ) {

            # assuming if they have 1, they have both
            #
            $newPrimary   = $map{ $hr->{primary_contact_id} };
            $newPrimary   = $hr->{primary_contact_id} unless $newPrimary;
            $newSecondary = $map{ $hr->{secondary_contact_id} };
            $newSecondary = $hr->{secondary_contact_id} unless $newSecondary;
            $dbo_raptor->DoCmd("UPDATE user set primary_contact_id=$newPrimary, secondary_contact_id=$newSecondary where user_id=$id");
        }
    }

    return ( \%map, \@newUsers );
}

sub _updateHRToDB {
    my ( $hr, $dbo, $tableName, $keyColumn, $keyValue ) = @_;

    my ( @keys, @values );

    foreach my $key ( keys %$hr ) {
        next if ( $key eq $keyColumn );
        push @values, $hr->{$key};

        push @keys, "$key=?";
    }
    my $setClause = join( ',', @keys );

    my $sql = "INSERT INTO $tableName SET $setClause WHERE $keyColumn = $keyValue";
    $dbo->DoCmdWithPlaceholders( $sql, \@values );
}

sub _alterClient {
    _report("altering address table");

    # Replacing the two seperate state and province columns with a single state_province table.
    #
    my $dbo = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON_RAPTOR' );

    my $sql = "ALTER TABLE client ADD COLUMN royalty_period_mechanical char(1) default 'Q'";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE client ADD COLUMN royalty_period_artist char(1) default 'Q'";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE client ADD COLUMN hfa_manufacturer_code varchar(20) default NULL";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE client ADD COLUMN use_net_for_mechanical_deductions tinyint(1) default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE client ADD COLUMN subtract_stat_rate_from_net_revenue tinyint(1) default 0";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Now populate these new columns
    #
    my $dboRPS = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON' );
    $sql = "SELECT * from client";
    my $sth = $dboRPS->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {

        my @updates;
        push @updates, "royalty_period_mechanical=" . $dbo->DBQuote( $hr->{royalty_period_mechanical} )
          if defined $hr->{royalty_period_mechanical};
        push @updates, "royalty_period_artist=" . $dbo->DBQuote( $hr->{royalty_period_artist} ) if defined $hr->{royalty_period_artist};
        push @updates, "hfa_manufacturer_code=" . $dbo->DBQuote( $hr->{hfa_manufacturer_code} ) if defined $hr->{hfa_manufacturer_code};
        push @updates, "use_net_for_mechanical_deductions=" . $hr->{use_net_for_mechanical_deductions}
          if defined $hr->{use_net_for_mechanical_deductions};
        push @updates, "subtract_stat_rate_from_net_revenue=" . $hr->{subtract_stat_rate_from_net_revenue}
          if defined $hr->{subtract_stat_rate_from_net_revenue};

        if ( scalar @updates ) {
            $sql = "UPDATE client SET " . join( ',', @updates ) . " WHERE client_id=" . $hr->{client_id};

            $dbo->DoCmd($sql);
        }
    }

}

sub _alterAddress {
    _report("altering address table");

    # Replacing the two seperate state and province columns with a single state_province table.
    #
    my $dbo = new Common::RSDB( client_id => 999, db_name => 'RSCOMMON_RAPTOR' );

    my $sql = "ALTER TABLE address ADD COLUMN state_province varchar(80) default NULL";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    $sql = "ALTER TABLE address ADD COLUMN street_address varchar(80) default NULL";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # populate the state_province
    #
    $sql = "SELECT address_id,state,province from address";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $sp = $hr->{state};
        if ( !$sp ) {
            $sp = $hr->{province};
        }

        if ($sp) {
            $dbo->DoCmd( "UPDATE address set state_province =" . $dbo->DBQuote($sp) . " WHERE address_id=" . $hr->{address_id} );
        }
    }

    # populate the street_address column by concatenating address1 and address2
    #
    $sql = "SELECT address_id,address1,address2 from address";
    $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $newAddress = $hr->{address1};
        if ( $newAddress && $hr->{address2} ) {
            $newAddress .= ', ' . $hr->{address2};
        }

        $dbo->DoCmd( "UPDATE address set street_address=" . $dbo->DBQuote($newAddress) . " WHERE address_id=" . $hr->{address_id} );
    }

    # Drop the old columns
    # !!! SKIP THIS STEP FOR NOW !!!
    #
    if ($gDropOldColumns) {
        $sql = "ALTER TABLE address DROP COLUMN address1";
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }

        $sql = "ALTER TABLE address DROP COLUMN address2";
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
        $sql = "ALTER TABLE address DROP COLUMN state";
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
        $sql = "ALTER TABLE address DROP COLUMN province";
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
    }
}

sub _getAllAvailableClientIDs {

    # First, get the list of _possible_ ids.
    #
    my @allIDs = Common::RSDB::GetAllClientIDs();

    # Now, we want to see which of these clients is actually present on this machine.
    #
    my %dbNames;
    `mysql -uroot -e 'show databases' > /tmp/DBLIST`;
    open DBLIST, "/tmp/DBLIST" or die "ERROR - unable to open /tmp/DBLIST : $!";
    while ( my $line = <DBLIST> ) {
        chomp $line;
        $dbNames{$line} = 1;
    }
    close DBLIST;

    # Match these up with an id
    #
    my @foundIDs;
    foreach my $id (@allIDs) {
        my $name = Common::RSDB::ClientIDToDBName($id);
        foreach my $dbName ( keys %dbNames ) {
            if ( $name eq $dbName ) {
                push @foundIDs, $id;
                last;
            }
        }
    }

    return @foundIDs;
}

sub _deleteDBs {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);

    `rm -f /tmp/$dbName*`;
    `mysql -uroot -e 'drop database $dbName'`;
    $dbName .= '_RAPTOR';
    `mysql -uroot -e 'drop database $dbName'`;
}

sub _getUPCAlt {
    my ($clientID) = @_;

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);
    $dbName .= '_RAPTOR';

    my %map;
    my $dbo = new Common::RSDB( client_id => $clientID, db_name => $dbName );

    my $sql = "SELECT album_id, upc_alt from album";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $map{ $hr->{album_id} } = $hr->{upc_alt};
    }

    return \%map;
}

sub _alterProduct {
    my ($clientID) = @_;

    _report("altering product table");

    my $upcMap = _getUPCAlt($clientID);

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

    # First, we'll add the new column.
    #
    _report("adding asset_id column");

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE product ADD COLUMN asset_id int(10) unsigned default NULL";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE product ADD COLUMN upc_alt varchar(25) default NULL";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Now we'll move through the products, setting asset_id and upc_alt to the proper value.
    #
    $dbo = Common::RSApp::GetClientDB();
    $sql = "SELECT product_id, product_type_id, track_id, album_id from product";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $productID = $hr->{product_id};
        my $upcAlt;
        my $assetID;
        if ( 4 == $hr->{product_type_id} ) {
            $assetID = $hr->{track_id};
        } else {
            $assetID = $hr->{album_id};
            $upcAlt  = $upcMap->{$assetID};
        }

        if ( 0 == $assetID ) {
            _report( "ERROR - no asset id: record: " . Dumper($hr) );
        }

        $sql = "UPDATE product SET asset_id=$assetID, upc_alt=" . $dbo->DBQuote($upcAlt) . " WHERE product_id=$productID";
        $dbo->DoCmd($sql);
    }

    # Last, drop track_id and album_id
    #
    _report("dropping album_id");
    $sql = "ALTER TABLE product DROP COLUMN album_id";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    _report("dropping track_id");
    $sql = "ALTER TABLE product DROP COLUMN track_id";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    _report("product table has been altered");
}

sub _readClientArtistIDFromRaptor {
    my ($clientID) = @_;
    assert($clientID);

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);
    $dbName .= '_RAPTOR';

    my %map;
    my $dbo = new Common::RSDB( client_id => $clientID, db_name => $dbName );

    # Can't use a fancy db abstraction layer safely, so we'll query this the old fashioned way
    #
    my $sql = "select artist_id, client_artist_id, artist_name_clean from artist";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $map{ $hr->{artist_id} } = {
            clientArtistID => $hr->{client_artist_id},
            nameClean      => $hr->{artist_name_clean},
        };
    }

    return \%map;
}

sub _alterArtist {
    my ($clientID) = @_;
    assert($clientID);

    _report("altering artist table");

    my $clientArtistIDMap = _readClientArtistIDFromRaptor($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE artist ADD COLUMN client_artist_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    $sql = 'ALTER TABLE artist ADD KEY `client_artist_id` (`client_artist_id`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    $sql = 'ALTER TABLE artist ADD COLUMN name_clean varchar(255)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    while ( my ( $artistID, $raptorInfoHash ) = each(%$clientArtistIDMap) ) {
        my $clientArtistID = $raptorInfoHash->{clientArtistID};
        my $nameClean      = $raptorInfoHash->{nameClean};

        $sql =
            "UPDATE artist SET client_artist_id = "
          . $dbo->DBQuote($clientArtistID)
          . ", name_clean = "
          . $dbo->DBQuote($nameClean)
          . " WHERE artist_id = "
          . $artistID;
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
    }

    $sql = "create index name_clean on artist (name_clean)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "create fulltext index artist_name_text on artist (name)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

}

sub _alterAlbum {
    my ($clientID) = @_;
    assert($clientID);

    _report("altering album table");
    my $clientAlbumIDMap = _readClientAlbumIDFromRaptor($clientID);

    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # Add the client_album_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE album ADD COLUMN client_album_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN company_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN vendor_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN location_id varchar(50)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN title_clean varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    $sql = 'ALTER TABLE album ADD KEY `client_album_id` (`client_album_id`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = 'ALTER TABLE album ADD KEY `title_clean` (`title_clean`)';
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # Add the custom columns
    #
    $sql = "ALTER TABLE album ADD COLUMN custom_1 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN custom_2 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN custom_3 varchar(255)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );
        return;
    }

    # I need to set the title_clean field.   To insure consistency, I'll derive it
    # from the current title.
    #
    $sql = "SELECT album_id, title from album";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref ) {
        $clientAlbumIDMap->{ $hr->{album_id} }{titleClean} = Common::Util::clean_name( $hr->{title} );
    }

    while ( my ( $albumID, $raptorData ) = each(%$clientAlbumIDMap) ) {
        my $clientAlbumID = $raptorData->{clientAlbumID};
        my $companyID     = $raptorData->{companyID};
        my $vendorID      = $raptorData->{vendorID};
        my $locationID    = $raptorData->{locationID};
        my $upcAlt        = $raptorData->{upcAlt};
        my $titleClean    = $raptorData->{titleClean};

        $sql =
            "UPDATE album SET client_album_id = "
          . $dbo->DBQuote($clientAlbumID)
          . ", company_id = "
          . $dbo->DBQuote($companyID)
          . ", vendor_id = "
          . $dbo->DBQuote($vendorID)
          . ", location_id = "
          . $dbo->DBQuote($locationID)
          . ", title_clean = "
          . $dbo->DBQuote($titleClean)
          . " WHERE album_id = "
          . $albumID;
        if ( !$dbo->DoCmd($sql) ) {
            _report( "error " . $dbo->LastErrorStr() );
            return;
        }
    }

    # Attempt to create indexes - if these fail, it's probably because
    # they already exist (so don't bail on error).
    #
    $sql = "create index artist_id on album (artist_id)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }
    $sql = "create index title_clean on album (title_clean)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }
    $sql = "create fulltext index album_title_text on album (title)";
    if ( !$dbo->DoCmd($sql) ) {
        _report( "error " . $dbo->LastErrorStr() );

        #        return;
    }
}

sub _transferRaptorDB {
    my ($clientID) = @_;
    assert($clientID);

    # We'll need to know the name of the database.
    #
    my $dbName = Common::RSDB::ClientIDToDBName($clientID);
    _report( "dbName : $dbName", 4 );

    # So, first we'll need to tell the remote host to dump the raptor database.
    #
    _report("-- extracting database $dbName on raptor01");
    my $cmd = "rm -f /tmp/$dbName*;mysqldump -uroot $dbName > /tmp/$dbName;gzip /tmp/$dbName";
    `ssh raptor01 '$cmd'`;

    # Now we copy it over to here.
    #
    _report("-- copying database dump to local host");
    `rm -f /tmp/$dbName*`;
    `scp -o 'TCPKeepAlive yes' raptor01:/tmp/$dbName.gz /tmp/$dbName.gz`;

    # Expand it.
    #
    _report("-- uncompressing data");
    `gunzip /tmp/$dbName.gz`;

    # Create the new raptor db on the local host.
    #
    my $newDBName = $dbName . "_RAPTOR";
    _report("-- importing data into new database $newDBName");
    `mysql -uroot -e 'create database $newDBName'`;
    `mysql -uroot $newDBName < /tmp/$dbName`;
}

sub _readClientAlbumIDFromRaptor {
    my ($clientID) = @_;
    assert($clientID);

    my $dbName = Common::RSDB::ClientIDToDBName($clientID);
    $dbName .= '_RAPTOR';

    my %map;
    my $dbo = new Common::RSDB( client_id => $clientID, db_name => $dbName );

    # Can't use a fancy db abstraction layer safely, so we'll query this the old fashioned way
    #
    my $sql = "select album_id, client_album_id, company_id, vendor_id, location_id from album";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        $map{ $hr->{album_id} } = {
            clientAlbumID => $hr->{client_album_id},
            companyID     => $hr->{company_id},
            vendorID      => $hr->{vendor_id},
            locationID    => $hr->{location_id},
        };
    }

    return \%map;
}

sub _usage {
    print "\nusage: $0 -c client_id [-V]\n";
    print "\n";
    print "Arguments:\n";
    print "\t-c <client_id>\t\tThe client_id of the client to process\n";
    print "\t-V <N>\t\t\tVerbosity level - 0 means no output\n";
}

sub _report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}

# Pass dates in 'yyyy-mm-dd' format.
#
sub _parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 'dc:V:', \%opt );

    if ( $opt{d} ) {
        $settings->{deleteFirst} = 1;
    }

    if ( $opt{c} ) {
        $settings->{clientID} = $opt{c};
    }

    if ( defined $opt{V} ) {
        $gVerbosityLevel = $opt{V};
    }
}