#!/usr/bin/perl
use strict;
use warnings FATAL => 'all';
use DBI;
use Getopt::Long qw(:config no_ignore_case);
use Data::Dumper;
use Encode qw(encode);
use ScriptLock;

my $username;
my $password;
my $tableName = 'idm2rav_osoby';

# define service
my $service_name = "vsup_rav";

# GEN folder location
my $facility_name = $ARGV[0];
chomp($facility_name);
my $service_files_base_dir="../gen/spool";
my $service_files_dir="$service_files_base_dir/$facility_name/$service_name";
my $service_file = "$service_files_dir/$service_name.csv";

# propagation destination
my $destination = $ARGV[1];
chomp($destination);

# create service lock
my $lock = ScriptLock->new($facility_name . "_" . $service_name . "_" . $destination);
($lock->lock() == 1) or die "Unable to get lock, service propagation was already running.";

# parse destination
my ($db_machine,$db_port,$db_name) = split(/:/, $destination);

# load authz
my $configPath = "/etc/perun/services/$service_name/$db_name";
open FILE, $configPath or die "Could not open config file $configPath: $!";
while(my $line = <FILE>) {
	if($line =~ /^username: .*/) {
		$username = ($line =~ m/^username: (.*)$/)[0];
	} elsif($line =~ /^password: .*/) {
		$password = ($line =~ m/^password: (.*)$/)[0];
	}
}

if(!defined($password) || !defined($username) || !defined($tableName)) {
	print "Can't get config data from config file.\n";
	exit 14;
}

#Main Structure
my $dataByUco = {};

open FILE, $service_file or die "Could not open $service_file: $!";
while(my $line = <FILE>) {
	my @parts = split /\t/, $line;
	chomp(@parts);
	$dataByUco->{$parts[0]}->{'LOGIN'} = $parts[1];
	$dataByUco->{$parts[0]}->{'EMAIL'} = $parts[2];
	$dataByUco->{$parts[0]}->{'FIRST_NAME'} = (($parts[3] ne '') ? $parts[3] : undef);
	$dataByUco->{$parts[0]}->{'LAST_NAME'} = (($parts[4] ne '') ? $parts[4] : undef);
	$dataByUco->{$parts[0]}->{'TITLE_BEFORE'} = (($parts[5] ne '') ? $parts[5] : undef);
	$dataByUco->{$parts[0]}->{'TITLE_AFTER'} = (($parts[6] ne '') ? $parts[6] : undef);
	$dataByUco->{$parts[0]}->{'PHONE'} = (($parts[7] ne '') ? $parts[7] : undef);
	$dataByUco->{$parts[0]}->{'CARD_BARCODE'} = (($parts[8] ne '') ? $parts[8] : undef);
	$dataByUco->{$parts[0]}->{'CARD_CHIP_NUMBER'} = (($parts[9] ne '') ? $parts[9] : undef);
	$dataByUco->{$parts[0]}->{'EXPIRATION'} = (($parts[10] ne '') ? $parts[10] : undef);
}
close FILE;

my $dbh = DBI->connect("dbi:mysql:$db_name:$db_machine:$db_port", $username, $password,{ RaiseError=>1, AutoCommit=>0, mysql_enable_utf8 => 1}) or die "Connect to database $db_name Error!\n";

my $DEBUG=0;

#statistic and information variables
my $inserted = 0;
my $foundAndUpdated = 0;
my $foundAndSkipped = 0;

# for each incoming UCO
foreach my $uco (sort keys %$dataByUco) {

	my $JMENO = $dataByUco->{$uco}->{'FIRST_NAME'};
	my $PRIJMENI = $dataByUco->{$uco}->{'LAST_NAME'};
	my $LOGIN = $dataByUco->{$uco}->{'LOGIN'};
	my $EMAIL = $dataByUco->{$uco}->{'EMAIL'};
	my $TITUL_PRED = $dataByUco->{$uco}->{'TITLE_BEFORE'};
	my $TITUL_ZA = $dataByUco->{$uco}->{'TITLE_AFTER'};
	my $TELEFON = $dataByUco->{$uco}->{'PHONE'};
	my $IDCARD_BARCODE = $dataByUco->{$uco}->{'CARD_BARCODE'};
	my $IDCARD_CHIP = $dataByUco->{$uco}->{'CARD_CHIP_NUMBER'};
	my $PLATNOST_DO = $dataByUco->{$uco}->{'EXPIRATION'};

	# check if person exists in RAV
	my $personExists = $dbh->prepare(qq{select 1 from $tableName where UCO=?});
	$personExists->execute($uco);

	if($personExists->fetch) {

		if($DEBUG == 1) { print "FIND: $uco\n"; }

		# we need to know if these two records are without changes, if yes, skip them

		my $select = "SELECT 1 from $tableName where UCO=? and LOGIN=?";
		my @params = ($uco, $LOGIN);

		if ($JMENO) {
			$select = $select . " and JMENO=?";
			push(@params, $JMENO);
		} else {
			$select = $select . " and JMENO is NULL";
		}
		if ($PRIJMENI) {
			$select = $select . " and PRIJMENI=?";
			push(@params, $PRIJMENI);
		} else {
			$select = $select . " and PRIJMENI is NULL";
		}
		if ($TITUL_PRED) {
			$select = $select . " and TITUL_PRED=?";
			push(@params, $TITUL_PRED);
		} else {
			$select = $select . " and TITUL_PRED is NULL";
		}
		if ($TITUL_ZA) {
			$select = $select . " and TITUL_ZA=?";
			push(@params, $TITUL_ZA);
		} else {
			$select = $select . " and TITUL_ZA is NULL";
		}
		if ($EMAIL) {
			$select = $select . " and EMAIL=?";
			push(@params, $EMAIL);
		} else {
			$select = $select . " and EMAIL is NULL";
		}
		if ($TELEFON) {
			$select = $select . " and TELEFON=?";
			push(@params, $TELEFON);
		} else {
			$select = $select . " and TELEFON is NULL";
		}
		if ($IDCARD_CHIP) {
			$select = $select . " and IDCARD_CHIP=?";
			push(@params, $IDCARD_CHIP);
		} else {
			$select = $select . " and IDCARD_CHIP is NULL";
		}
		if ($IDCARD_BARCODE) {
			$select = $select . " and IDCARD_BARCODE=?";
			push(@params, $IDCARD_BARCODE);
		} else {
			$select = $select . " and IDCARD_BARCODE is NULL";
		}
		if ($PLATNOST_DO) {
			$select = $select . " and PLATNOST_DO=?";
			push(@params, $PLATNOST_DO);
		} else {
			$select = $select . " and PLATNOST_DO is NULL";
		}

		my $recordAreEquals = $dbh->prepare($select);
		$recordAreEquals->execute(@params);

		if(!$recordAreEquals->fetch) {

			my $updatePerson = $dbh->prepare(qq{UPDATE $tableName SET JMENO=? , PRIJMENI=? , TITUL_PRED=? , TITUL_ZA=? , EMAIL=? , TELEFON=? , IDCARD_CHIP=? , IDCARD_BARCODE=? , PLATNOST_DO=? WHERE UCO=?});
			$updatePerson->execute($JMENO, $PRIJMENI, $TITUL_PRED, $TITUL_ZA, $EMAIL, $TELEFON, $IDCARD_CHIP , $IDCARD_BARCODE, $PLATNOST_DO, $uco);
			if($DEBUG == 1) { print "UPDATING EXISTING PERSON: $uco\n"; }
			$foundAndUpdated++;

		} else {

			if($DEBUG == 1) { print "SKIP PERSON: $uco\n"; }
			$foundAndSkipped++;

		}

	} else {

		if($DEBUG == 1) { print "INSERT NEW PERSON: $uco\n"; }
		$inserted++;
		# we will do insert
		my $insertPerson = $dbh->prepare(qq{INSERT INTO $tableName (UCO, LOGIN, JMENO, PRIJMENI, TITUL_PRED, TITUL_ZA, EMAIL, TELEFON, IDCARD_CHIP, IDCARD_BARCODE, PLATNOST_DO) VALUES (?,?,?,?,?,?,?,?,?,?,?)});
		$insertPerson->execute($uco, $LOGIN, $JMENO, $PRIJMENI, $TITUL_PRED, $TITUL_ZA, $EMAIL, $TELEFON, $IDCARD_CHIP, $IDCARD_BARCODE, $PLATNOST_DO);

	}

}

commit $dbh;
$dbh->disconnect();

# print info about operations
print "=======================================\n";
print "Inserted:\t$inserted\n";
print "Updated:\t$foundAndUpdated\n";
print "Skipped:\t$foundAndSkipped\n";
print "=======================================\n";

$lock->unlock();
