#!/usr/bin/perl

#provided as-is by in 2017 by RogerThePhoneGuy.com 
#This script reads a file of "key/value" pairs, and logs them to a database. If the value was changed or deleted, it logs that too
#meant to be used with keyval.php, which shows this data
#assumes there's a local mysql connection

use strict;
use DBI;

my $db;

sub db_open { 
	my $dsn = "DBI:mysql:avaya:localhost";
	my $user = "scraper";
	my $pass = "crafty1876";
	while (!($db)) {
		$db = DBI->connect($dsn, $user, $pass);
		sleep 1;
	}
}

sub db_close { #check
	if($db) {
		$db->disconnect();
		sleep 1;
	}
	undef($db);
}

sub generic_replace { 
	my ($table, $fields, $values) = (@_);
	my $sql = "replace into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to replace with $sql\n";
	}
	$sth->finish();
}

sub generic_create { 
	my ($table, $fields, $values) = (@_);
	my $sql = "insert into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to insert with $sql\n";
	}
	$sth->finish();
}

sub generic_read {
	my ($select, $table, $where) = (@_);
	my @datarow;
	my $sql = "select $select from $table where $where limit 1";
	my $sth = $db->prepare($sql);
	if($sth->execute()) {
		@datarow = $sth->fetchrow_array();
	} else {
		print "unable to execute $sql\n";
	}
	$sth->finish();
	return $datarow[0];
}

sub generic_update {
	my ($table, $set, $where) = (@_);
	my $sql = "update $table set $set where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

sub generic_delete {
	my ($table, $where) = (@_);
	my $sql = "delete from $table where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

db_open();

my $counter = 0;
generic_update("avaya_keyvalues","record_status=9","active=1");
open SCRAPED, $ARGV[0];
while (my $line = <SCRAPED>) {
	chomp($line);

	my ($key,$value) = split(/\t/,$line);
	my $id = generic_read("id", "avaya_keyvalues","active=1 and key_name='$key'") + 0;
	my $currvalue = generic_read("key_value", "avaya_keyvalues","active=1 and key_name='$key'");
	if($id > 0) {
		if($value ne $currvalue) {
			$value =~ s/'/\\'/g;
			generic_update("avaya_keyvalues","active=0,record_status=2,updated_on=now()","id=$id");
			my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
			my $values = "null,1,'$key','$value',1,now(),now(),null";
			generic_create("avaya_keyvalues", $fields, $values);	
		} else {
			generic_update("avaya_keyvalues","active=1,record_status=1","id=$id");
		}
	} else {
		$value =~ s/'/\\'/g;
		my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
		my $values = "null,1,'$key','$value',1,now(),now(),null";
		generic_create("avaya_keyvalues", $fields, $values);
	}
	if($key =~ /\.station\.(\d+)\.status/) {
		my $station = $1;
		my $name = '';
		if($value =~ /^(.+?) \(ip=/) {
			$name = $1;
		} elsif($value =~ /^(.+) \(unregistered/) {
			$name = $1;
		}
		$name =~ s/'/\\'/g;
		generic_replace("avaya_display_name","station,name","'$station','$name'");
	}
}
close SCRAPED;
generic_update("avaya_keyvalues","active=0,record_status=3,deactivated_on=now()","record_status=9");

my $create = "
drop table if exists avaya_keyvalues;
create table avaya_keyvalues (
	id integer auto_increment primary key,
	active integer,
	key_name varchar(256),
	key_value varchar(256),
	record_status integer,
	created_on datetime,
	updated_on datetime,
	deactivated_on datetime
);

drop table if exists avaya_display_name;
create table avaya_display_name (
station varchar(12) NOT NULL,
name varchar(32),
PRIMARY KEY (station)
);
";

#my @count = generic_read("count(*)","callrecord","1=1");
#print "count is " . $count[0] . "\n";
db_close();