#! /usr/local/bin/perl

use strict;
use warnings;
use DBI;
use MyConfig;
use DBD::mysql;
use Data::Dumper;
use POSIX qw (strftime);
use Net::SNMP qw (:snmp);
use Time::HiRes qw ( time usleep gettimeofday tv_interval );

$|=1; #flush every time the program
$\="\n"; #Debug process
$|=1;

my ($community,$id,$public,$interfaces,$dsn,$port,$ip,$num_interfaces,$j,$oid,$in_av_bitrate,$out_av_bitrate,$oid_time_new,$dsn_db,$checkExist,$query,$result,$update_time);

my %config = getconfig();

my $DATABASE      = 'test';
my $HOST          = 'localhost';
my $MYSQLPORT     = '3306';
my $USERNAME      = 'root';
my $PASSWD        = '';
my $DEVICE_TABLE  = 'Interfaces';
my $DEVICES       = 'Devices';
my $in_community  = 'public';
my $in_port       = '161';
my $in_IP         = '';
my $in_interfaces = '3 5';

my $dbh = DBI->connect("dbi:mysql::$HOST:$MYSQLPORT", 
		       $USERNAME, 
		       $PASSWD, 
		       { 'PrintError' => 1, 'RaiseError' => 1 } 
    ) or die "Could not connect to ". $HOST .": ". $DBI::errstr ."\n";

my $databases = $dbh->do("SHOW DATABASES LIKE '".$DATABASE."'") or die "Error: " .dbh->errstr. "\n";

if ($databases eq 1) {
    printf "Database: ". $DATABASE ." exists not creating: ". $DATABASE ."\n";
}
else {
    printf "Database: ". $DATABASE ." does not exist creating: ". $DATABASE ."\n";
    $checkExist = $dbh->prepare("CREATE DATABASE IF NOT EXISTS `".$DATABASE."`") or die "Could not create the: ". $DATABASE ." error: ". $dbh->errstr ."\n";

    if (!$checkExist->execute) {
	die "Error: ". $checkExist->errstr ."\n";
    }
}

$dbh->disconnect();

$dbh = DBI->connect("dbi:mysql:$DATABASE:$HOST:$MYSQLPORT", 
		    $USERNAME, 
		    $PASSWD, 
		    { 'PrintError' => 1, 'RaiseError' => 1 } 
    ) or die "Could not connect to ". $HOST .": ". $DBI::errstr ."\n";

my $tables = $dbh->do("SHOW TABLES FROM `".$DATABASE."` WHERE Tables_in_".$DATABASE." LIKE '".$DEVICE_TABLE."'") or die "Error: " .dbh->errstr. "\n";

if ($tables eq 1) {
    printf "Table: ". $DEVICE_TABLE ." exists not creating: ". $DEVICE_TABLE ."\n";
}

else {
    printf "Table: ". $DEVICE_TABLE ." does not exist creating: ". $DEVICE_TABLE ."\n";

# timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `".$DEVICE_TABLE."` (
                                 `id` int(11) NOT NULL AUTO_INCREMENT,
                                 `interface_id` int(11) NOT NULL DEFAULT '0',
                                 `IP` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
                                 `in_bitRate` int(11) NOT NULL DEFAULT '0',
                                 `bitrate_av_in` int(11) NOT NULL DEFAULT '0',
                                 `out_bitRate` int(11) NOT NULL DEFAULT '0',
                                 `bitrate_av_out` int(11) NOT NULL DEFAULT '0',
                                 `avg_in_bitRate` int(11) NOT NULL DEFAULT '0',
                                 `avg_out_bitRate` int(11) NOT NULL DEFAULT '0',
                                 `first_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
                                 `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                 `sysUpTime` int(11) NOT NULL DEFAULT '0',
                                 `last_in_octets` int(11) NOT NULL DEFAULT '0',
                                 `last_out_octets` int(11) NOT NULL DEFAULT '0',
                                 `cummulative_in` int(11) NOT NULL DEFAULT '0',
                                 `cummulative_out` int(11) NOT NULL DEFAULT '0',
                                 `counter` int(11) NOT NULL DEFAULT '0',
                                 PRIMARY KEY (`id`),
                                 UNIQUE KEY `interface_id` (`interface_id`,`IP`)
                               ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1 ;");

     if (!$checkExist->execute) {
	die "Error: ". $checkExist->errstr ."\n";
    }
    $checkExist->finish();
}

$tables = $dbh->do("SHOW TABLES FROM `".$DATABASE."` WHERE Tables_in_".$DATABASE." LIKE '".$DEVICES."'") or die "Error: " .dbh->errstr. "\n";

if ($tables eq 1) {
    printf "Table: ". $DEVICES ." exists not creating: ". $DEVICES ."\n";
}
else {
    printf "Table: ". $DEVICES ." does not exist creating: ". $DEVICES ."\n";
    $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `". $DEVICES ."` (
                                `id` int(11) NOT NULL AUTO_INCREMENT,
                                `IP` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
                                `PORT` int(11) NOT NULL,
                                `COMMUNITY` tinytext COLLATE latin1_bin NOT NULL,
                                `INTERFACES` text COLLATE latin1_bin NOT NULL,
                                PRIMARY KEY (`id`)
                               ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 ;");

    if (!$checkExist->execute) {
	die "Error: ". $checkExist->errstr ."\n";
    }
    $checkExist->finish();

    $checkExist = $dbh->prepare("INSERT IGNORE INTO `". $DEVICES ."` (`IP`,`PORT`,`COMMUNITY`,`INTERFACES`) VALUES ('".$in_IP."','".$in_port."','".$in_community."','ALL') ");

    if (!$checkExist->execute) {
	die "Error: ". $checkExist->errstr ."\n";
    }
    $checkExist->finish();
    
    $checkExist = $dbh->prepare("INSERT IGNORE INTO `". $DEVICES ."` (`IP`,`PORT`,`COMMUNITY`,`INTERFACES`) VALUES ('".$in_IP."','".$in_port."','".$in_community."','".$in_interfaces."') ");

    if (!$checkExist->execute) {
	die "Error: ". $checkExist->errstr ."\n";
    }
    $checkExist->finish();
}


$oid= $oid_time_new = $in_av_bitrate = $out_av_bitrate = $num_interfaces = 0;

my $counter = 1;
my $number = 0;

# Select and Extract/retrieve data from table.
my $sth = $dbh->prepare("SELECT * FROM `".$DEVICES."`") || die "SQL Error: $dbh::errstr\n";

if (!$sth->execute) {
    die "Error: ". $sth->errstr ."\n";
}
# Check if there are data inside the rows of the table
if ($sth->rows > 0) {
    while (my $ref = $sth->fetchrow_hashref()) {
	$id = $ref->{'id'};
	$ip = $ref->{'IP'};
	$port = $ref->{'PORT'};
	$community = $ref->{'COMMUNITY'};
	$interfaces = $ref->{'INTERFACES'};
	#print("This is the IP: ".$ip."!\n");
	
	if ($interfaces eq 'ALL') {
	    printf "The ID: %s has interfaces set to ALL proceeding to next ID.\n", $id;
	    next;
	}

	program ();

    } # End of While loop
} # End of if loop
else {
    # If rows return empty exit
    printf "The table: ".$DEVICES." is empty, user need to enter data. Exit!!!\n";
    exit 1;
} # End of else loop

sub program {
    my $if_inOctets = '1.3.6.1.2.1.2.2.1.10';
    my $if_outOctets = '1.3.6.1.2.1.2.2.1.16';
    my $sysUpTime = '1.3.6.1.2.1.1.3.0';
    
    # Initiate snmp session
    my ($session, $error)= Net::SNMP->session(
	-hostname    => $ip || 'localhost',
	-community   => $community || 'public',
	-nonblocking => 0,
	-port      => $port,
	-version   => 'snmpv1',
	-translate => 0,
	);

    if (!defined ($session)) {
	printf "ERROR: Failed to queue get request for host '%s': %s!\n", $ip, $error;
	exit 1;
    }

    my (@bitrate_in,@bitrate_out,@memory,@in_oid,@out_oid,@request) = ();

    @memory = split (' ',$interfaces);
    for ($j = 1 ; $j <= scalar (@memory) ; $j++) {
	$result = $j - 1;
	$in_oid[$j-1] = ($if_inOctets.".".$memory[$j-1]);
	$out_oid[$j-1] = ($if_outOctets.".".$memory[$j-1]);
    }

    #printf Dumper(\@memory);
    push (@request, (@in_oid,@out_oid,$sysUpTime));
    #printf Dumper(\@request);
    
    $num_interfaces = scalar (@memory);
    #printf "This is the number of Interfaces: ".$num_interfaces."\n";
    my (@cummulative_in) = (0) x $num_interfaces;
    my (@cummulative_out) = (0) x $num_interfaces;
    my (@new_in_oid_loop) = (0) x $num_interfaces;
    my (@new_out_oid_loop) = (0) x $num_interfaces;
    my (@bitrate_av_in) = (0) x $num_interfaces;
    my (@bitrate_av_out) = (0) x $num_interfaces;   
    my (@old_in_oid_loop) = (0) x $num_interfaces;
    my (@old_out_oid_loop) = (0) x $num_interfaces;
    my (@in_av_bitrate) = (0) x $num_interfaces;
    my (@out_av_bitrate) = (0) x $num_interfaces;
    my $oid_time_old = 0;
    
    #$oid = $session->get_request(
    #-varbindlist => [$sysUpTime],
    #);

    $oid = $session->get_request(
	-varbindlist => \@request ,
	);

    # In case of an error print the error
    if (!defined ($oid)) {
	printf "Problem: with session get request: %s.\n", $session->error();
	$session->close();
	exit 1;
    }

    #printf "The sysUpTime for host '%s' is %s.\n",
    #$session->hostname(), $oid->{$sysUpTime};
    #print("This is Dumper before Loop\n");
    #print Dumper($oid);
    #exit();

    for ($j=1; $j <= $num_interfaces; $j++) {
	print Dumper($oid);
	$new_in_oid_loop[$j-1] = $oid->{$in_oid[$j-1]};
	#printf("This is the in new_in_oid_loop: %s and interface: %s!\n",$new_in_oid_loop[$j-1],$memory[$j-1]);
	$new_out_oid_loop[$j-1] = $oid->{$out_oid[$j-1]};
	#printf("This is the in new_out_oid_loop: %s and interface: %s!\n",$new_out_oid_loop[$j-1],$memory[$j-1]);
	$oid_time_new = $oid->{$sysUpTime};
	#printf("This is the sysuptime: %s!\n",$oid_time_new);

	my $database_hash = $dbh->prepare("SELECT * FROM `".$DEVICE_TABLE."` WHERE `interface_id` = $memory[$j-1]") || die "SQL Error: $dbh::errstr\n";
	$database_hash->execute() || die "SQL Error: $dbh::errstr\n";

	if ($database_hash->fetch()) {
	    my $database_hash = $dbh->prepare("SELECT * FROM `".$DEVICE_TABLE."` WHERE `interface_id` = $memory[$j-1]") || die "SQL Error: $dbh::errstr\n";
	    $database_hash->execute() || die "SQL Error: $dbh::errstr\n";

	    while (my $database_ref = $database_hash->fetchrow_hashref()) {
		$old_in_oid_loop[$j-1] = $database_ref->{'last_in_octets'};
		$old_out_oid_loop[$j-1] = $database_ref->{'last_out_octets'};
		$bitrate_av_in[$j-1] = $database_ref->{'bitrate_av_in'};
		$bitrate_av_out[$j-1] = $database_ref->{'bitrate_av_out'};
		$oid_time_old = $database_ref->{'sysUpTime'};
		$counter = $database_ref->{'counter'};
		$counter++;
		
		if ($new_in_oid_loop[$j-1] >= $old_in_oid_loop[$j-1]) {
		    $bitrate_in[$j-1] = 800*($new_in_oid_loop[$j-1]-$old_in_oid_loop[$j-1])/($oid_time_new - $oid_time_old);
		    $bitrate_av_in[$j-1] = $bitrate_av_in[$j-1] + $bitrate_in[$j-1];
		    $bitrate_out[$j-1] = 800*($new_out_oid_loop[$j-1]-$old_out_oid_loop[$j-1])/($oid_time_new - $oid_time_old);
		    $bitrate_av_out[$j-1] = $bitrate_av_out[$j-1] + $bitrate_out[$j-1];
		    $in_av_bitrate[$j-1] = $bitrate_av_in[$j-1]/$counter;
		    $out_av_bitrate[$j-1] = $bitrate_av_out[$j-1]/$counter;
		} #end of if loop
		else {
		    #$bitrate_in[$j-1] = 800*(((2**32)-1-$old_in_oid_loop[$j-1])+$new_in_oid_loop[$j-1])/($oid_time_new - $oid_time_old);
		    #$bitrate_av_in[$j-1] = $bitrate_av_in[$j-1] + $bitrate_in[$j-1];
		    #$bitrate_out[$j-1] = 800*(((2**32)-1-$old_out_oid_loop[$j-1])+$new_out_oid_loop[$j-1])/($oid_time_new - $oid_time_old);
		    #$bitrate_av_out[$j-1] = $bitrate_av_out[$j-1] + $bitrate_out[$j-1];
		    #$in_av_bitrate[$j-1] = $bitrate_av_in[$j-1]/$counter;
		    #$out_av_bitrate[$j-1] = $bitrate_av_out[$j-1]/$counter;
		} #end of else loop
		#printf("This is the update part!\n");
		printf("This is in: %s!\n",$new_in_oid_loop[$j-1]);
		printf("This is out: %s!\n",$new_out_oid_loop[$j-1]);
		my $update_statement = $dbh->prepare("UPDATE `".$DEVICE_TABLE."` SET `IP`='".$ip."',`in_bitRate`='".$bitrate_in[$j-1]."',`bitrate_av_in`='".$bitrate_av_in[$j-1]."',`out_bitRate`='".$bitrate_out[$j-1]."',`bitrate_av_out`='".$bitrate_av_out[$j-1]."',`avg_in_bitRate`='".$in_av_bitrate[$j-1]."',`avg_out_bitRate`='".$out_av_bitrate."',`last_update_time`= FROM_UNIXTIME(NOW()),`sysUpTime`='".$oid_time_new."',`last_in_octets`='".$new_in_oid_loop[$j-1]."',`last_out_octets`='".$new_out_oid_loop[$j-1]."',`cummulative_in`= `cummulative_in` + '".$new_in_oid_loop[$j-1]."',`cummulative_out`= `cummulative_out` + '".$new_in_oid_loop[$j-1]."',`counter`='".$counter."' WHERE `IP` = '$ip' AND `interface_id` = '$memory[$j-1]'") || die "SQL Error: ". $dbh::errstr ."\n";
		
		$update_statement->execute() || die "SQL Error: ". $dbh::errstr ."\n";
	    }# End of while condition
	} #end of if condition
	else {
	    #printf("This is the Insert part!\n");
	    #printf("Before insert new_out_oid_loop: %s and interface: %s!\n",$oid->{$out_oid[$j-1]},$memory[$j-1]);
	    my $insert_statement = $dbh->prepare("INSERT IGNORE INTO `".$DEVICE_TABLE."` (`interface_id`,`IP`,`first_update_time`,`last_update_time`,`sysUpTime`,`last_in_octets`,`last_out_octets`,`cummulative_in`,`cummulative_out`,`counter`) VALUES ('".$memory[$j-1]."','".$ip."',FROM_UNIXTIME(NOW()),FROM_UNIXTIME(NOW()),'".$oid_time_new."','".$new_in_oid_loop[$j-1]."','".$new_out_oid_loop[$j-1]."','".$new_in_oid_loop[$j-1]."','".$new_out_oid_loop[$j-1]."','".$counter."')") || die "SQL Error: ". $dbh::errstr ."\n";
	    $insert_statement->execute() || die "SQL Error: ". $dbh::errstr ."\n";
	} # end of else condition (INSERT)
    } #end of for loop
    $session->close();

} # end of subprogram
$sth->finish();
# Disconnect
$dbh->disconnect();