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