#! /usr/bin/perl

use strict vars;
use DBI;
use Time::ParseDate;

### Config params ###
my $curDbName = 'pvsadmin';
my $curDbHost = '209.18.106.124';
my $curDbUser = 'web';
my $curDbPass = 'XXXXXXX';
my $curDbPort = '5432';

my $oldDbName = 'pvsadmin';
my $oldDbHost = '209.18.106.125';
my $oldDbUser = 'web';
my $oldDbPass = 'XXXXXXX';
my $oldDbPort = '5432';
### End config ###

my $curdbh = DBI->connect("dbi:Pg:dbname=$curDbName;
                               host=$curDbHost;
                               port=$curDbPort",
                               $curDbUser,
                               $curDbPass,
                               { AutoCommit => 1 }) or die $DBI::errstr;

my $olddbh = DBI->connect("dbi:Pg:dbname=$oldDbName;
                               host=$oldDbHost;
                               port=$oldDbPort",
                               $oldDbUser,
                               $oldDbPass,
                               { AutoCommit => 0 }) or die $DBI::errstr;

# Check for API keys that are in old but NOT in current DB (these will need
# to be added)

my $getKeys = "SELECT * FROM misc_userapi WHERE key <> ''";
my $curkeys = $curdbh->selectall_hashref($getKeys, 'key')
    or die $DBI::errstr;
=pod
foreach my $key(keys %$curkeys) {
    print "$key\t$curkeys->{$key}->{'last_payment'}\n";
}
=cut

my $oldkeys = $olddbh->selectall_hashref($getKeys, 'key')
    or die $DBI::errstr;
=pod
foreach my $key(keys %$oldkeys) {
    print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
}
=cut

print "Keys in old DB that aren't in current DB:\n";
my $missingKeys = {};
foreach my $key(keys %$oldkeys) {
    if(!exists $curkeys->{$key}) {
        $missingKeys->{$key} = 1;
        print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
    }
}
exit if(keys %$missingKeys <= 0);

print "Fetching data from old DB:\n";
my $sth1 = $olddbh->prepare("SELECT * FROM misc_userapi WHERE key = ?");
my $sth2 = $olddbh->prepare("SELECT * FROM misc_userprofile WHERE user_api_id = ?");
my $sth3 = $olddbh->prepare("SELECT * FROM auth_user WHERE id = ?");

my $ith3 = $curdbh->prepare("INSERT INTO auth_user VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?) RETURNING id");
my $ith1 = $curdbh->prepare("INSERT INTO misc_userapi VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING user_api_id");
my $ith2 = $curdbh->prepare("INSERT INTO misc_userprofile VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING id");

foreach my $key(keys %$missingKeys) {
    $| = 1;
    # Manual processing here...
    if($key =~ /^ca8e386|65a5717|f5d978e|bd6030c|7645dd5|0cca24d|f2efa0|562b937|8e91361|a43db30|4716a4b/)
    {
        print "Manually processing $key...\n";
       
        # Get old records
        $sth1->execute($key) or die $DBI::errstr;
        my $sth1ref = $sth1->fetch();
        $sth2->execute($sth1ref->[0]) or die $DBI::errstr;
        my $sth2ref = $sth2->fetch();
        $sth3->execute($sth2ref->[1]) or die $DBI::errstr;
        my $sth3ref = $sth3->fetch();
        my $email = $sth3ref->[4];
        print "email: $email...";

        # Insert new API record, get that new user_api_id
        my $ith1 = $curdbh->prepare("INSERT INTO misc_userapi VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING user_api_id");
        my @ith1_bind_vals = @{$sth1ref}[1..scalar @{$sth1ref}-1];
        $ith1->execute(@ith1_bind_vals) or die $DBI::errstr;
        my $user_api_id = $ith1->fetch()->[0];

        # Get user_id on current db
        my $sth3cur = $curdbh->prepare("SELECT id FROM auth_user WHERE email = ?");
        $sth3cur->execute($email);
        my $user_id = $sth3cur->fetch()->[0];

        # Update misc_userprofile with new user_api_id using
        # user_id as the lookup key
        my $ith2cur = $curdbh->prepare("UPDATE misc_userprofile SET user_api_id = ? WHERE user_id = ?");
        $ith2cur->execute($user_api_id, $user_id);
        print "done!\n";
        next;
    }

    # Go ahead and copy records over, no existing records
    print "Inserting record for key: $key...";
    $sth1->execute($key) or die $DBI::errstr;
    my $sth1ref = $sth1->fetch();
    $sth2->execute($sth1ref->[0]) or die $DBI::errstr;
    my $sth2ref = $sth2->fetch();
    $sth3->execute($sth2ref->[1]) or die $DBI::errstr;
    my $sth3ref = $sth3->fetch();

    my @ith3_bind_vals = @{$sth3ref}[1..scalar @{$sth3ref}-1];
    $ith3->execute(@ith3_bind_vals) or die $DBI::errstr;
    my $user_id = $ith3->fetch()->[0];
    print "new user_id: $user_id...";
    my @ith1_bind_vals = @{$sth1ref}[1..scalar @{$sth1ref}-1];
    $ith1->execute(@ith1_bind_vals) or die $DBI::errstr;
    my $user_api_id = $ith1->fetch()->[0];
    print "new user_api_id: $user_api_id...";
    $sth2ref->[1] = $user_id;
    $sth2ref->[11] = $user_api_id;
    my @ith2_bind_vals = @{$sth2ref}[1..scalar @{$sth2ref}-1];
    $ith2->execute(@ith2_bind_vals);'
    print "done!\n";
}

END {
    $curdbh->disconnect();
    $olddbh->disconnect();
}

#! /usr/bin/perl

use strict;
use DBI;
use Time::ParseDate;

### Config params ###
my $curDbName = 'pvsadmin';
my $curDbHost = '209.18.106.124';
my $curDbUser = 'web';
my $curDbPass = 'XXXXXX';
my $curDbPort = '5432';

my $oldDbName = 'pvsadmin';
my $oldDbHost = '209.18.106.125';
my $oldDbUser = 'web';
my $oldDbPass = 'XXXXXXX';
my $oldDbPort = '5432';
### End config ###

my $curdbh = DBI->connect("dbi:Pg:dbname=$curDbName;
                               host=$curDbHost;
                               port=$curDbPort",
                               $curDbUser,
                               $curDbPass,
                               { AutoCommit => 1 }) or die $DBI::errstr;

my $olddbh = DBI->connect("dbi:Pg:dbname=$oldDbName;
                               host=$oldDbHost;
                               port=$oldDbPort",
                               $oldDbUser,
                               $oldDbPass,
                               { AutoCommit => 0 }) or die $DBI::errstr;

# Check for API keys that are in old but NOT in current DB (these will need
# to be added)

my $getKeys = "SELECT user_api_id, key, last_payment FROM misc_userapi WHERE key <> ''";
my $curkeys = $curdbh->selectall_hashref($getKeys, 'key')
    or die $DBI::errstr;
=pod
foreach my $key(keys %$curkeys) {
    print "$key\t$curkeys->{$key}->{'last_payment'}\n";
}
=cut

my $oldkeys = $olddbh->selectall_hashref($getKeys, 'key')
    or die $DBI::errstr;
=pod
foreach my $key(keys %$oldkeys) {
    print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
}
=cut

# Check for API keys in current DB with registration dates LESS THAN the
# corresponding keys in the old DB (these will need to be updated)

print "Keys in current DB with registration dates earlier than those in old DB:\n";
my $cursth = $curdbh->prepare("UPDATE misc_userapi SET last_payment=? WHERE key=?");
foreach my $key(keys %$curkeys) {
    my $curkeyTS = parsedate($curkeys->{$key}->{'last_payment'});
    my $oldkeyTS = parsedate($oldkeys->{$key}->{'last_payment'});
    if(defined($curkeyTS) && $curkeyTS < $oldkeyTS) {
        print "$key\t$curkeys->{$key}->{'last_payment'}\t$oldkeys->{$key}->{'last_payment'}\n";
        print "Updating...";
        if($cursth->execute($oldkeys->{$key}->{'last_payment'}, $key) == 1) {
            print "done!\n";
        }
        else {
            print "failed!\n";
        }
    }
}

END {
    $curdbh->disconnect();
    $olddbh->disconnect();
}