#!/usr/local/bin/perl -w

use strict;
use DBI;
use File::Tail;

# Create File::Tail object
my $file;
$file=File::Tail->new("/opt/app/superspy/tmp/commLogger.log");

my $line;
while (defined($line=$file->read))
{
        chomp($line);
        # print "$line\n";
        
        # Create DB connection
		my $db_handle = DBI->connect("dbi:mysql:database=P0TA6E01;host=myhost02.fcc.hide.com:3306;user=xxxxxxxx;password=xxxxxxxx") || die "Couldn't connect to database: $DBI::errstr\n";

        if($line =~ /^AlertCmd~.*~.*~-reload.*/){next;}

        if($line =~ /^AlertCmd~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $cmnd = $3;
                my $client_host = $4;
                my $client_user = $5;
                my $svr_host = $6;
                $svr_host =~ s/\..*//g;
                my $sql = "INSERT INTO ALERTS (TSTAMP,CMND_ID,CMND,CLIENTHOST,CLIENTUSER,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$cmnd\',\'$client_host\',\'$client_user\',\'$svr_host\')\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^SendFiltered~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $state = $5;
                my $svr_host = $6;
                $svr_host =~ s/\..*//g;
                my $sql = "INSERT INTO FILTERED (TSTAMP,CMND_ID,ALERT_ID,SEND_ID,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_id\',\'$send_id\',\'$state\',\'$svr_host\')\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^SendOffDuty~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $state = $5;
                my $svr_host = $6;
                $svr_host =~ s/\..*//g;
                my $sql = "INSERT INTO FILTERED (TSTAMP,CMND_ID,ALERT_ID,SEND_ID,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_id\',\'$send_id\',\'$state\',\'$svr_host\')\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^SendStarted~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $user = $5;
                my $destination = $6;
                my $pin = $7;
                my $to = $8;
                my $message = $9;
                my $state = $10;
                my $svr_host = $11;
                $svr_host =~ s/\..*//g;
                my $address;
                if($pin eq ""){$address = $to;}else{$address = $pin;}
                my $sql = "INSERT INTO SENDS (START_TIME,CMND_ID,ALERT_ID,SEND_ID,USER,DESTINATION,ADDRESS,MESSAGE,STATE,SVR_HOST,ACTIVE) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_id\',\'$send_id\',\'$user\',\'$destination\',\'$address\',\'$message\',\'$state\',\'$svr_host\',\'TRUE\')\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^SendCompleted~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $state = $5;
                my $svr_host = $6;
                $svr_host =~ s/\..*//g;
                my $sql = "UPDATE SENDS SET END_TIME=\'$tstamp\',STATE=\'$state\',ACTIVE=\'FALSE\' WHERE CMND_ID=\'$cmnd_id\' AND ALERT_ID=\'$alert_id\' AND SEND_ID=\'$send_id\' AND SVR_HOST=\'$svr_host\'\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^SendCleared~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $state = $5;
                my $svr_host = $6;
                $svr_host =~ s/\..*//g;
                my $sql = "UPDATE SENDS SET END_TIME=\'$tstamp\',STATE=\'$state\',ACTIVE=\'FALSE\' WHERE CMND_ID=\'$cmnd_id\' AND ALERT_ID=\'$alert_id\' AND SEND_ID=\'$send_id\' AND SVR_HOST=\'$svr_host\'\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }

        if($line =~ /^AlertError~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){
                my $tstamp = $1;
                my $cmnd_id = $2;
                my $alert_id = $3;
                my $send_id = $4;
                my $destination = $5;
                my $message = $6;
                my $state = $7;
                my $svr_host = $8;
                $svr_host =~ s/\..*//g;
                my $sql = "INSERT INTO ERRORS (TSTAMP,CMND_ID,ALERT_ID,SEND_ID,DESTINATION,MESSAGE,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_id\',\'$send_id\',\'$destination\',\'$message\',\'$state\',\'$svr_host\')\;\n";
                my $statement = $db_handle->prepare($sql)
                	or die "Couldn't prepare query '$sql': $DBI::errstr\n";
        		$statement->execute()
                	or die "Couldn't execute query '$sql': $DBI::errstr\n";
        }
        
        $statement->finish();
		$db_handle->disconnect();

}