#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Storable;
use Getopt::Long;
use Config::INI::Reader;

## For quick sanity checks.
#use diagnostics;
#use Data::Dumper;
#use YAML;

# db connections
# This can be done differently - this
# is just how I do it...
unshift @INC, '/usr/local/perl/lib';
require mydb::DB;

# Can we perform deletes - lets always start off w/ a NO
# and explicitly trigger a yes...
# 0 for NO
# 1 for Yes
my $deletes = 0;

# some sub refs
my $fetch     = \&fetch;
my $dprint    = \&debug;
my $timestamp = \&timestamp;
my $sendmail  = \&sendmail;

#Process comand line options.
my ( $config, $store, $logprefix, $debug );
my $opts = GetOptions("config=s"    => \$config,
                      "store"       => \$store,
                      "logprefix=s" => \$logprefix,
                      "debug"       => \$debug);

#warn $INC{'DBI.pm'} if $debug;

## start the log
my $LOG_DIR = '/tmp';
my $LOG = ( $logprefix ) ? 
    $logprefix . "_$config-" . $timestamp->('log') : 
    "$config-" . $timestamp->('log');

open my $LF, '>', "$LOG_DIR/$LOG" or
    die "unable to open $LOG_DIR/$LOG: $!\n";

# Start an exceptions file as well
open my $EF, '>', "$LOG_DIR/$LOG.err" or
    die "unable to open $LOG_DIR/$LOG.err: $!\n";

print $LF scalar(localtime), "\n\n";

# Process table load ini file
my $inidir = 'Configs/';
my ( @rmaporder, @lmaporder, $rkey, $lkey, $rtable, $ltable, %defaults
+ );
my ( $source, $target );
my $colcount = 0;

if ( $config )
{
    my $configuration = Config::INI::Reader->read_file("$inidir/$confi
+g");

    # Valid Items are keys columns defaults and tables
    $rkey   = $configuration->{'keys'}->{'rkey'};
    $lkey   = $configuration->{'keys'}->{'lkey'};
    $rtable = $configuration->{'tables'}->{'rtable'};
    $ltable = $configuration->{'tables'}->{'ltable'};

    # Be careful here if its not set lets re-set this to 0 
    $deletes = $configuration->{'configs'}->{'deletes'} || 0;

    for ( keys %{ $configuration->{ 'columns' } } )
    {
        push @rmaporder, $_;
        push @lmaporder, $configuration->{'columns'}->{$_};
        $colcount++;
    }

    for ( keys %{ $configuration->{'default'} } )
    {
        $defaults{$_} = $configuration->{'default'}->{$_};
    }
}
else
{
    die "usage: $0 --config=config.ini OPTIONAL: --logprefix=test -s -
+d \n";
}

## End of ini file processing

$dprint->(qq{\@rmaporder = @rmaporder});
$dprint->(qq{\@lmaporder = @lmaporder});

print $LF "remote table => $rtable\nremote key => $rkey\n";
print $LF "local table => $ltable\nlocal key => $lkey\n";

# recylce $config for logfile naming 
# minus the .ini
$config =~ s!\..*!! ;

# Hold the errors for reporting...
my @ERRS;

$dprint->(qq{initializing DB connections});

## Local - Informix Server
my $ldbh            = myDB->Iproduction();
$ldbh->{ChopBlanks} = "true";
$ldbh->{PrintError} = 0;
$ldbh->{RaiseError} = 0;
# End on Local DB connection

## REMOTE - SQL server
my $rdbh             =  myDB->Sstage();
$rdbh->{ChopBlanks} = "true";
$rdbh->{PrintError}  = 0;
$rdbh->{RaiseError}  = 0;
$rdbh->{AutoCommit} =  1;
# End of Remote DB connection

# remote query 
my $rfieldlist = join",", @rmaporder;
my $rquery = qq{select $rfieldlist from $rtable};

# local query 
my $lfieldlist = join",", @lmaporder;
my $lquery = qq{select $lfieldlist from $ltable};

$dprint->(qq{Remote query = $rquery});
$dprint->(qq{Local query = $lquery});

# prepare and execute our sql query on the 
# local DB
$dprint->(qq{prepare});
my $lsth = $ldbh->prepare($lquery) or errpt('FATAL', $ldbh->errstr(), 
+'NA', $lquery);
$lsth->execute() or errpt ( 'FATAL', $ldbh->errstr(), 'NA', $lquery );

# prepare and execute our sql query on the 
# remote DB
$dprint->(qq{execute});
my $rsth = $rdbh->prepare($rquery) or errpt('FATAL', $rdbh->errstr(), 
+'NA', $rquery);
$rsth->execute() or errpt ( 'FATAL', $rdbh->errstr(), 'NA', $rquery );

my %RemoteDB;
my %LocalDB;

# hashes to hold the initial db slurp
my %rdb;
my %ldb;
my $counter = 0;

$dprint->(qq{Binding row data locally});

# Bind columns to hashes
for ( @lmaporder )
{
    $counter++;
    $ldb{$_} = undef;
    $lsth->bind_col( $counter, \$ldb{$_} );
}

$dprint->(qq{local counter = $counter});
$dprint->(qq{Binding row data remotely});

$counter = 0;
for ( @rmaporder )
{
    $counter++;
    $rdb{$_} = undef;
    $rsth->bind_col( $counter, \$rdb{$_} );
}

$dprint->(qq{remote counter = $counter});

# Fetch the data and hold in these  hashes
$dprint->(qq{Fetching remote data});
%RemoteDB = $fetch->($rsth, \%rdb, $rkey);
$dprint->(qq{fetching local data});
%LocalDB  = $fetch->($lsth, \%ldb, $lkey);

store [\%RemoteDB, %rdb, \%LocalDB, \%ldb],  "$LOG_DIR/$LOG.loc"
    if $store;

my @rinsert_keys;
my @rinspect_keys;
my @rdelete_keys;

# Populate arrays with keys
#
# @rinsert_keys are keys that exist locally
# and DO NOT exist remotely. These will always
# be inserts
for ( keys %LocalDB )
{
    push @rinsert_keys, $_ if ( ! exists $RemoteDB{$_} );
}

# @rinspect_keys represent rows of data
# where the keys are the same remotely
# and locally - this is a dumping 
# ground which we will further inspect
# the data later on
for ( keys %LocalDB )
{
    push @rinspect_keys, $_ if ( exists $RemoteDB{$_} );
}

# @rdelete_keys represent rows of data where
# the key exists remotely but not locally
# these just get deleted.
for ( keys %RemoteDB )
{
    push @rdelete_keys, $_ if ( ! exists $LocalDB{$_} ); 
}

# Insert Routines
print $LF "\nINFO: +++ There are ", scalar(@rinsert_keys), " to insert
+\n";
for my $keys (@rinsert_keys)
{
    my @VALUES;
    
    for my $count ( 0 .. $#lmaporder )
    {
        push @VALUES, $LocalDB{$keys}{$lmaporder[$count]}; 
    }

    my $values = join',',@VALUES;

    my $query = "INSERT into $rtable ($rfieldlist) VALUES ($values)";

    print $LF "INSERT: $keys QUERY: $query\n";
    
    my $rsth = $rdbh->prepare($query) or 
        errpt("INSERT", $rdbh->errstr(), $keys, $query);
    $rsth->execute() or errpt("INSERT", $rdbh->errstr(), $keys, $query
+);
}      

# Inspect/Update Routines
print $LF "\nINFO: +++ There are ", scalar(@rinspect_keys), " to INSPE
+CT\n";
for my $keys (@rinspect_keys)
{
    my @UPDATESET;
    my @WHERE;
    my $UPDATE;

    # Inspect the actual column data, if its different push it onto 
    # @UPDATESET array for later processing
    for my $count ( 0 .. $#lmaporder )
    {
        print $LF "INSPECT $keys: Local $lmaporder[$count] = $LocalDB{
+$keys}{$lmaporder[$count]}\n";
        print $LF "INSPECT $keys: Remote $rmaporder[$count] = $RemoteD
+B{$keys}{$rmaporder[$count]}\n";
        push @UPDATESET, "$rmaporder[$count] = $LocalDB{$keys}{$lmapor
+der[$count]}"
          unless ( $LocalDB{$keys}{$lmaporder[$count]} eq $RemoteDB{$k
+eys}{$rmaporder[$count]} );
    }
    

    $UPDATE = join', ', @UPDATESET;

    # If there is anything here, goto work...
    if ( scalar(@UPDATESET) > 0 )
    { 
        my $query = "UPDATE $rtable SET $UPDATE WHERE $rkey = $RemoteD
+B{$keys}{$rkey}";
        print $LF "UPDATE: $keys QUERY=$query\n";
        my $rsth = $rdbh->prepare($query) or 
          errpt ("UPDATE", $rdbh->errstr(), $keys, $query); 

        $rsth->execute() or errpt ("UPDATE", $rdbh->errstr(), $keys, $
+query);
    }
    else
    {
        print $LF "INSPECT: $keys Nothing to do\n";
        next;
    }

}

# Delete Routines
print $LF "\nINFO: +++ There are ", scalar(@rdelete_keys), " to delete
+\n";
for my $keys ( @rdelete_keys )
{
    my $delstat = ( $deletes == 0 ) ? 'off' : 'on';

    print $LF "DELETE: deletes are set to $delstat\n";

    last if ( $deletes == 0 );
    
    my $query = "DELETE FROM $rtable WHERE $rkey = $RemoteDB{$keys}{$r
+key}";
    print $LF "DELETE: $keys\nDELETE: $query\n";
    my $rsth=$rdbh->prepare($query) or errpt("DELETE", $rdbh->errstr()
+, $keys, $query);
    $rsth->execute() or errpt("DELETE", $rdbh->errstr(), $keys, $query
+);
}

unlink ("$LOG_DIR/$LOG.err") if -s "$LOG_DIR/$LOG.err";

#### Local Subs ####

sub fetch
{
    my $sth    = shift;
    my $dbhash = shift;
    my $key    = shift;
    my %DB;
    while ( $sth->fetchrow_hashref )
    {
        # Ensure that ALL data is properly quoted
        # using the dbi->quote method
        $DB{$dbhash->{$key}}{$_} 
             = $rdbh->quote($dbhash->{$_}) for keys %$dbhash 
    }

    return %DB or errpt ( 'FATAL', 'Unable to return data from fetch',
+ 'NA', 'NA' ); 
}

sub debug
{
    warn "@_\n" if $debug
}

sub errpt
{
    my $TYPE   = shift;
    my $DB_ERR = shift;
    my $KEY    = shift;
    my $QUERY  = shift;

    my $ERR    = "$TYPE key $KEY: $DB_ERR";

    push @ERRS, $ERR;

    print $LF "$TYPE FAILURE: key = $KEY $DB_ERR\n$QUERY\n";

    return 0 unless ($TYPE eq 'FATAL');

    return 1;

}

sub timestamp
{
    $_ = shift || "timestamp";
    my ( $sec, $min, $hour,
    $mday, $mon, $year,
    $wday, $yday ) = localtime();
    
    $mon++;
    $year += 1900;
    
    my $log = sprintf "%4i%02i%02i%04i", $year,$mon,$mday, $$;
    my $timestamp = sprintf "%4i-%02i-%02i %02i:%02i:%02i  ",
        $year, $mon, $mday, $hour, $min, $sec;
    
    my $return = ( $_ eq "timestamp" ) ? $timestamp : $log;
    
    return $return;
}