#!/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};
    }
}