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



my $gVerbosityLevel = 1;

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

my $clientID = $options{clientID};

_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($clientID);

_alterUser($userIDMap, $newUsers);

_renameRSCOMMON_RAPTOR();

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

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 _alterAddress
{
    my ($clientID) = @_;

    _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
    #
    $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 _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})
    {
        _usage();
        exit(1);
    }
    $settings->{clientID} = $opt{c};

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