#! /usr/bin/perl
use strict;
use DBI;
use Getopt::Std;

my %options;
getopts( "h:u:p:d:", \%options );


#
# ==== Update the queue_log table with a given partial queue_log file ==
# $Id: queuePartialUpdater.pl,v 1.5 2013/06/18 09:20:03 lenz-mobile Exp $
# ======================================================================
# (c) Loway 2009 - http://www.loway.ch
#
#
# usage:
# queuePartialUpdater.pl [flags] /my/queue_log/file partition_name /my/queue_log/logfile
#
# Known flags:
#  -h host       : host name
#  -d database   : database name
#  -u user       : user name
#  -p password   : password
#
#
#
# ATTENTION  ATTENTION  ATTENTION  ATTENTION  ATTENTION  ATTENTION  
# If this file does not seem to work from the shell, do a 
#        dos2unix queuePartialUpdater.pl
# to set things right.
# ATTENTION  ATTENTION  ATTENTION  ATTENTION  ATTENTION  ATTENTION  
#

# CUSTOMIZATION PREFERENCES

my $mysql_host = $options{h} || "127.0.0.1";
my $mysql_db   = $options{d} || "queuemetrics";
my $mysql_user = $options{u} || "queuemetrics";
my $mysql_pass = $options{p} || "javadude";

my $qloaderbin = "./qloader.pl";

my $logfile    = "queuepartialupdater.log";

# DO NOT TOUCH ANYTHING BELOW HERE
# ----------------------------------------------------------------------


my $QueueFile = $ARGV[0] || "/var/log/asterisk/queue_log";
my $Partition = $ARGV[1] || "P001";
my $ImportLog = $ARGV[2] || "/var/log/asterisk/qloader.log";

# --- Main thread ------------------------------------------------------

#Trace Start
syslog( "QueueMetrics Partial Queue Database Updater - " . '$Revision: 1.5 $ ' );

#Open the queue file and find the timerange
my ($MinTs, $MaxTs) = findMinAndMaxTsFromQueueLog($QueueFile);

#Connect to the database
my $dberr ="";
my $dbh = DBI->connect("DBI:mysql:dbname=$mysql_db;host=$mysql_host;", $mysql_user, $mysql_pass,
       {
       	RaiseError => 1,
       	PrintError => 1,
       	ShowErrorStatement => 1,
       	HandleError => \&errDb,
       	mysql_auto_reconnect=>1
	    }
) || sysdie( "Could not connect to database O: $DBI::errstr" );

#Remove all entries into the timeperiod for the specified partition
deleteRowsInWindowTime( $dbh, $Partition, $MinTs, $MaxTs );

#Move all more recent entries in a temporary partition
moveToTemporaryEvents( $dbh, $Partition, $MaxTs );

#Call the normal qLoader in order to fill the timerange with the new queue log
my $Command = "$qloaderbin -h $mysql_host -d $mysql_db -u $mysql_user -p $mysql_pass $QueueFile $Partition $ImportLog 1";
syslog("Calling $Command");
my $Result = `$Command`;

#Move all temporary entries back to the normal partition
moveToRealEvents( $dbh, $Partition, $MaxTs );

# Optimize the table
execQuery( $dbh, "OPTIMIZE TABLE queue_log" );

#Close the database connection
$dbh->disconnect();

#Trace End
syslog( "QueueMetrics Partial Queue Database Updater - terminating " );


# =================================================================
# 
#                      Internal functions
#
# =================================================================

# Find the minimum and maximum timestamp into a queue log file
sub findMinAndMaxTsFromQueueLog {
        my ($QueueFileName) = @_;

        # Open the Queue Filename then read it all
        open F, $QueueFileName or sysdie ("$! $QueueFileName");

        my $MinTs = 0;
        my $MaxTs = 0;

        while (<F>) {
                chomp;
                my ($tst, @Other) = (split /\|/, $_);

                $MinTs = $tst if (($MinTs > $tst) || ($MinTs == 0));
                $MaxTs = $tst if ($MaxTs < $tst);
        }
        
        close F;

        return ($MinTs, $MaxTs);
}

# Delete all database rows included in the selected period and partition
sub deleteRowsInWindowTime {
	my ( $dbh, $Partition, $MinTs, $MaxTs) = @_;
	
	my $q_Partition = myQuote( $dbh, $Partition );
	
	my $SQL = "DELETE FROM queue_log WHERE `partition` = $q_Partition AND time_id >= $MinTs AND time_id <= $MaxTs";
	remove( $dbh, $SQL );

	syslog("Removing items from time id $MinTs to $MaxTs for partition $Partition");
}

# Rename partition events with timestamp greather than the specified to a temporary value
sub moveToTemporaryEvents {
	my ( $dbh, $Partition, $MaxTs ) = @_;
	
	my $q_Partition = myQuote ( $dbh, $Partition );
	my $q_PartitionTemp = myQuote ( $dbh, $Partition . "_QmQPU_Tmp" );
	
	my $SQL = "UPDATE queue_log SET `partition` = $q_PartitionTemp WHERE `partition` = $q_Partition AND time_id > $MaxTs";
	execQuery( $dbh, $SQL );

	syslog("Moving items with timestamp greater than $MaxTs for partition $Partition");
}

# Rename temporary partition events with timestamp greather than the specified to a normal run value
sub moveToRealEvents {
	my ( $dbh, $Partition, $MaxTs ) = @_;
	
	my $q_Partition = myQuote ( $dbh, $Partition );
	my $q_PartitionTemp = myQuote ( $dbh, $Partition . "_QmQPU_Tmp" );
	
	my $SQL = "UPDATE queue_log SET `partition` = $q_Partition WHERE `partition` = $q_PartitionTemp AND time_id > $MaxTs";
	execQuery( $dbh, $SQL );

	syslog("Restoring items with timestamp greater than $MaxTs into partition $Partition");
}

# =================================================================
# 
#                      DB handling functions
#
# =================================================================

sub execQuery {
	my ( $dbh, $sql ) = @_;
	runSafeQuery( $dbh, $sql, 0 );
}

sub scalarQuery {
	my ( $dbh, $sql ) = @_;
	runSafeQuery( $dbh, $sql, 1 );
}

sub fetchQuery {
        my ( $dbh, $sql ) = @_;
	runSafeQuery( $dbh, $sql, 3 );
}

sub runSafeQuery {
	# returnScalar: 
	# 0: nulla
	# 1: scalare
	# 2: n. rows affected
	# 3: $sth 

	my ( $dbh, $query, $returnScalar ) = @_;
	my $retval = 0;

	my $sth = $dbh->prepare($query);
	$sth->execute();
	
	
	if ( $returnScalar == 1 ) {
		($retval) = $sth->fetchrow_array;
		$sth->finish();
	} elsif ( $returnScalar == 2 ) {
		$retval = $sth->rows;
		$sth->finish();
	} elsif ( $returnScalar == 3 ) {
		$retval = $sth;
	}

	#print "Q: $query \n--> risultato: $retval\n";

	return $retval;
}

sub myQuote {
	my ($dbo, $v) = @_;

	if (length($v) == 0 ) {
		return "''";
	} else {
		return $dbo->quote( trim($v) );
	}
}

sub updateOrInsert() {
	# se l'update è su 0 righe, faccio la insert
	my ( $dbh, $sqlUpdate, $sqlInsert ) = @_;
	
	my $nRows = runSafeQuery( $dbh, $sqlUpdate, 2 );
	if ( $nRows == 0 ) {
		runSafeQuery( $dbh, $sqlInsert, 0 );
	};
}

sub insert() {
	my ( $dbh, $sqlInsert ) = @_;

	runSafeQuery ( $dbh, $sqlInsert, 0 );
};

sub remove() {
	my ( $dbh, $sqlDelete ) = @_;
	
	runSafeQuery ( $dbh, $sqlDelete, 0 ); 
}

# =================================================================
# 
#                      Other useful functions
#
# =================================================================

sub trim {
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}

sub errDb {
	$dberr = 1;
	my ( $msg, $h, $ret ) = @_;
	syserr( "---ERROR FOUND--" );
	syserr( "Error type: " . $h->{Type} );
	syserr( " Statement: " . $h->{Statement} );
	syserr( "     Error: " . $h->errstr );
	die();
}



# =================================================================
# 
#                      Error logging
#
# =================================================================


sub syslog {
	my ($s) = @_;
	syslogger( " ", $s );
}

sub syserr {
	my ($s) = @_;
	syslogger( "E", $s );
}

sub syslogger {
	my ($l, $s) = @_;
	print "$l|$s\n";
	open EL, ">>$logfile" or die "$! $logfile";
	print EL "$l|" . (scalar localtime) . "|$s\n";
	close EL;
}

sub sysdie {
	my ($s) = @_;
	syslogger( "E", $s );
	die( $s );
}