#!/usr/bin/perl
use strict;
use warnings FATAL => 'all';
use DBI;
use DBI qw(:sql_types);
use Getopt::Long qw(:config no_ignore_case);
use Data::Dumper;
use ScriptLock;
binmode STDOUT, ":utf8";

sub solveClob;

my $username;
my $password;
my $table_osoby = 'ex_idm2is_osoby';

# define service
my $service_name = "vsup_is";

# 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)) {
	print "Can't get config data from config file.\n";
	exit 14;
}

#Main Structure
my $dataByKeys = {};

open FILE, $service_file or die "Could not open $service_file: $!";
binmode FILE, ":utf8";
while(my $line = <FILE>) {
	my @parts = split /\t/, $line;
	chomp(@parts);
	$dataByKeys->{$parts[0]}->{'UCO_PERUN'} = $parts[1];
	$dataByKeys->{$parts[0]}->{'UCO_IS'} = (($parts[2] ne '') ? $parts[2] : undef); # only students have OSB_ID_IS
	$dataByKeys->{$parts[0]}->{'LOGIN'} = $parts[3];
	$dataByKeys->{$parts[0]}->{'EMAIL'} = $parts[4];
	$dataByKeys->{$parts[0]}->{'TYP_ZAZN'} = $parts[5];
	$dataByKeys->{$parts[0]}->{'TITLE_BEFORE'} = (($parts[6] ne '') ? $parts[6] : undef);
	$dataByKeys->{$parts[0]}->{'FIRST_NAME'} = (($parts[7] ne '') ? $parts[7] : undef);
	$dataByKeys->{$parts[0]}->{'LAST_NAME'} = (($parts[8] ne '') ? $parts[8] : undef);
	$dataByKeys->{$parts[0]}->{'TITLE_AFTER'} = (($parts[9] ne '') ? $parts[9] : undef);
	$dataByKeys->{$parts[0]}->{'BIRTH_LAST_NAME'} = (($parts[10] ne '') ? $parts[10] : undef);
	$dataByKeys->{$parts[0]}->{'BIRTH_NUMBER'} = (($parts[11] ne '') ? $parts[11] : undef);
	$dataByKeys->{$parts[0]}->{'GENDER'} = (($parts[12] ne '') ? $parts[12] : undef);
	$dataByKeys->{$parts[0]}->{'JOB'} = (($parts[13] ne '') ? $parts[13] : undef);
	$dataByKeys->{$parts[0]}->{'VZTAH_DO'} = (($parts[14] ne '') ? $parts[14] : undef);
	$dataByKeys->{$parts[0]}->{'PHONE'} = (($parts[15] ne '') ? $parts[15] : undef);
	$dataByKeys->{$parts[0]}->{'EMAIL_PRIV'} = (($parts[16] ne '') ? $parts[16] : undef);
	$dataByKeys->{$parts[0]}->{'PHONE_PRIV'} = (($parts[17] ne '') ? $parts[17] : undef);
	$dataByKeys->{$parts[0]}->{'CARD_PHOTO'} = (($parts[18] ne '') ? $parts[18] : undef);
}
close FILE;

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

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

#update and insert new
foreach my $key (sort keys %$dataByKeys) {

	my $UCO_PERUN = $dataByKeys->{$key}->{'UCO_PERUN'};
	my $UCO_IS = $dataByKeys->{$key}->{'UCO_IS'};  # OSB_ID_IS
	my $LOGIN = $dataByKeys->{$key}->{'LOGIN'};
	my $EMAIL = $dataByKeys->{$key}->{'EMAIL'};
	my $TYP_ZAZN = $dataByKeys->{$key}->{'TYP_ZAZN'};

	my $TITLE_BEFORE = $dataByKeys->{$key}->{'TITLE_BEFORE'};
	my $FIRST_NAME = $dataByKeys->{$key}->{'FIRST_NAME'};
	my $LAST_NAME = $dataByKeys->{$key}->{'LAST_NAME'};
	my $TITLE_AFTER = $dataByKeys->{$key}->{'TITLE_AFTER'};

	my $BIRTH_LAST_NAME = $dataByKeys->{$key}->{'BIRTH_LAST_NAME'};
	my $BIRTH_NUMBER = $dataByKeys->{$key}->{'BIRTH_NUMBER'};
	my $GENDER = $dataByKeys->{$key}->{'GENDER'};
	my $JOB = $dataByKeys->{$key}->{'JOB'};
	my $VZTAH_DO = $dataByKeys->{$key}->{'VZTAH_DO'};

	my $PHONE = $dataByKeys->{$key}->{'PHONE'};
	my $EMAIL_PRIV = $dataByKeys->{$key}->{'EMAIL_PRIV'};
	my $PHONE_PRIV = $dataByKeys->{$key}->{'PHONE_PRIV'};

	my $IDCARD_PHOTO = $dataByKeys->{$key}->{'CARD_PHOTO'};

	# Construct BIRTH_DATE from BIRTH_NUMBER
	my $BIRTH_DATE = undef;
	if (defined $BIRTH_NUMBER and length($BIRTH_NUMBER) > 0) {

		my $YY = substr($BIRTH_NUMBER, 0, 2);
		my $MM = substr($BIRTH_NUMBER, 2, 2);
		my $DD = substr($BIRTH_NUMBER, 4, 2);

		# fix woman`s birth number month
		if (substr($MM, 0, 1) eq "5") {
			$MM = "0" . substr($BIRTH_NUMBER, 3, 1);
		}
		if (substr($MM, 0, 1) eq "6") {
			$MM = "1" . substr($BIRTH_NUMBER, 3, 1);
		}

		if ($YY < 22) {
			# assume young students 2000+
			$BIRTH_DATE = "20" . $YY . "-" . $MM . "-" . $DD;
		} else {
			# assume older employees 1922+
			$BIRTH_DATE = "19" . $YY . "-" . $MM . "-" . $DD;
		}

	}


	# There is
	my $personExists = $dbh->prepare(qq{select 1 from $table_osoby where UCO_PERUN=? and TYP_ZAZN=?});
	$personExists->execute($UCO_PERUN, $TYP_ZAZN);

	if($personExists->fetch) {
		if($DEBUG == 1) { print "FIND: $key\n"; }

		# we need to know if these two records are without changes, if yes, skip them
		# CARD PHOTO is not checked here, since we can't pass CLOB to WHERE clause.
		my $recordAreEquals;
		if ($TYP_ZAZN eq "S") {

			# S = students

			my $select = "SELECT 1 from $table_osoby where UCO_PERUN=? and USERNAME=? and EMAIL_SKOLNI=? and TYP_ZAZN=?";
			my @params = ($UCO_PERUN, $LOGIN, $EMAIL, $TYP_ZAZN);

			if ($UCO_IS) {
				$select = $select . " and UCO_IS=?";
				push(@params, $UCO_IS);
			} else {
				$select = $select . " and UCO_IS is NULL";
			}

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

		} else {

			# P / Z = teachers / employees

			my $select = "SELECT 1 from $table_osoby where UCO_PERUN=? and USERNAME=? and EMAIL_SKOLNI=? and TYP_ZAZN=?";
			my @params = ($UCO_PERUN, $LOGIN, $EMAIL, $TYP_ZAZN);

			if ($UCO_IS) {
				$select = $select . " and UCO_IS=?";
				push(@params, $UCO_IS);
			} else {
				$select = $select . " and UCO_IS is NULL";
			}
			if ($FIRST_NAME) {
				$select = $select . " and JMENO=?";
				push(@params, $FIRST_NAME);
			} else {
				$select = $select . " and JMENO is NULL";
			}
			if ($LAST_NAME) {
				$select = $select . " and PRIJMENI=?";
				push(@params, $LAST_NAME);
			} else {
				$select = $select . " and PRIJMENI is NULL";
			}
			if ($BIRTH_LAST_NAME) {
				$select = $select . " and ROD_PRIJMENI=?";
				push(@params, $BIRTH_LAST_NAME);
			} else {
				$select = $select . " and ROD_PRIJMENI is NULL";
			}
			if ($BIRTH_NUMBER) {
				$select = $select . " and ROD_CISLO=?";
				push(@params, $BIRTH_NUMBER);
			} else {
				$select = $select . " and ROD_CISLO is NULL";
			}
			if ($BIRTH_DATE) {
				$select = $select . " and DATUM_NAR=?";
				push(@params, $BIRTH_DATE);
			} else {
				$select = $select . " and DATUM_NAR is NULL";
			}
			if ($GENDER) {
				$select = $select . " and POHLAVI=?";
				push(@params, $GENDER);
			} else {
				$select = $select . " and POHLAVI is NULL";
			}
			if ($TITLE_BEFORE) {
				$select = $select . " and TITUL_PRED=?";
				push(@params, $TITLE_BEFORE);
			} else {
				$select = $select . " and TITUL_PRED is NULL";
			}
			if ($TITLE_AFTER) {
				$select = $select . " and TITUL_ZA=?";
				push(@params, $TITLE_AFTER);
			} else {
				$select = $select . " and TITUL_ZA is NULL";
			}
			if ($PHONE) {
				$select = $select . " and TEL_SKOLNI=?";
				push(@params, $PHONE);
			} else {
				$select = $select . " and TEL_SKOLNI is NULL";
			}
			if ($EMAIL_PRIV) {
				$select = $select . " and EMAIL_SOUKR=?";
				push(@params, $EMAIL_PRIV);
			} else {
				$select = $select . " and EMAIL_SOUKR is NULL";
			}
			if ($PHONE_PRIV) {
				$select = $select . " and TEL_SOUKR=?";
				push(@params, $PHONE_PRIV);
			} else {
				$select = $select . " and TEL_SOUKR is NULL";
			}
			if ($JOB) {
				$select = $select . " and FUNKCE=?";
				push(@params, $JOB);
			} else {
				$select = $select . " and FUNKCE is NULL";
			}
			if ($VZTAH_DO) {
				$select = $select . " and VZTAH_DO=?";
				push(@params, $VZTAH_DO);
			} else {
				$select = $select . " and VZTAH_DO is NULL";
			}

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

		}

		if(!$recordAreEquals->fetch) {

			if ($TYP_ZAZN eq "S") {

				# UPDATE STUDENT (without photo - solved later)
				my $updatePerson = $dbh->prepare(qq{UPDATE $table_osoby SET UCO_IS=? , USERNAME=? , EMAIL_SKOLNI=? , ZMENENO_KDY=now() WHERE UCO_PERUN=? and TYP_ZAZN=?});
				$updatePerson->execute(
					$UCO_IS,
					$LOGIN,
					$EMAIL,
					$UCO_PERUN,
					$TYP_ZAZN);

				solveClob($UCO_PERUN, $TYP_ZAZN, $IDCARD_PHOTO);

				if($DEBUG == 1) { print "UPDATING EXISTING RECORD: $key\n"; }
				$foundAndUpdated++;

			} else {

				# UPDATE TEACHERS and EMPLOYEES
				my $updatePerson = $dbh->prepare(qq{UPDATE $table_osoby SET UCO_IS=? , USERNAME=? , JMENO=? , PRIJMENI=? , ROD_PRIJMENI=? , ROD_CISLO=? , DATUM_NAR=?, POHLAVI=? , TITUL_PRED=? , TITUL_ZA=? , EMAIL_SKOLNI=? , TEL_SKOLNI=? , EMAIL_SOUKR=? , TEL_SOUKR=? , FUNKCE=? , VZTAH_DO=? , ZMENENO_KDY=now() WHERE UCO_PERUN=? and TYP_ZAZN=?});
				$updatePerson->execute(
					$UCO_IS,
					$LOGIN,
					$FIRST_NAME,
					$LAST_NAME,
					$BIRTH_LAST_NAME,
					$BIRTH_NUMBER,
					$BIRTH_DATE,
					$GENDER,
					$TITLE_BEFORE,
					$TITLE_AFTER,
					$EMAIL,
					$PHONE,
					$EMAIL_PRIV,
					$PHONE_PRIV,
					$JOB,
					$VZTAH_DO,
					$UCO_PERUN,
					$TYP_ZAZN);

				solveClob($UCO_PERUN, $TYP_ZAZN, $IDCARD_PHOTO);

				if($DEBUG == 1) { print "UPDATING EXISTING RECORD: $key\n"; }
				$foundAndUpdated++;

			}

		} else {

			# basic attributes were same, check also photo

			my $result = solveClob($UCO_PERUN, $TYP_ZAZN, $IDCARD_PHOTO);
			if ($result == 1) {
				if($DEBUG == 1) { print "UPDATING EXISTING RECORD: $key\n"; }
				$foundAndUpdated++;
			} else {
				if($DEBUG == 1) { print "SKIP RECORD: $key\n"; }
				$foundAndSkipped++;
			}

		}

	} else {

		if($DEBUG == 1) { print "INSERT NEW RECORD: $key\n"; }
		$inserted++;
		# we will do insert

		if ($TYP_ZAZN eq "S") {

			# INSERT STUDENT
			my $insertPerson = $dbh->prepare(qq{INSERT INTO $table_osoby (UCO_IS, UCO_PERUN, TYP_ZAZN, USERNAME, EMAIL_SKOLNI, FOTO , ZMENENO_KDY) VALUES (?,?,?,?,?,?,'now()')});
			$insertPerson->bind_param(1, $UCO_IS);
			$insertPerson->bind_param(2, $UCO_PERUN);
			$insertPerson->bind_param(3, $TYP_ZAZN);
			$insertPerson->bind_param(4, $LOGIN);
			$insertPerson->bind_param(5, $EMAIL);
			$insertPerson->bind_param(6, $IDCARD_PHOTO, SQL_LONGVARCHAR);
			$insertPerson->execute();

		} else {

			# INSERT TEACHER and EMPLOYEE
			my $insertPerson = $dbh->prepare(qq{INSERT INTO $table_osoby (UCO_IS, UCO_PERUN, TYP_ZAZN, USERNAME, JMENO, PRIJMENI, ROD_PRIJMENI, ROD_CISLO, DATUM_NAR, POHLAVI, TITUL_PRED, TITUL_ZA, EMAIL_SKOLNI, TEL_SKOLNI, EMAIL_SOUKR, TEL_SOUKR, FUNKCE, VZTAH_DO, FOTO, ZMENENO_KDY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'now()')});
			$insertPerson->bind_param(1, $UCO_IS);
			$insertPerson->bind_param(2, $UCO_PERUN);
			$insertPerson->bind_param(3, $TYP_ZAZN);
			$insertPerson->bind_param(4, $LOGIN);
			$insertPerson->bind_param(5, $FIRST_NAME);
			$insertPerson->bind_param(6, $LAST_NAME);
			$insertPerson->bind_param(7, $BIRTH_LAST_NAME);
			$insertPerson->bind_param(8, $BIRTH_NUMBER);
			$insertPerson->bind_param(9, $BIRTH_DATE);
			$insertPerson->bind_param(10, $GENDER);
			$insertPerson->bind_param(11, $TITLE_BEFORE);
			$insertPerson->bind_param(12, $TITLE_AFTER);
			$insertPerson->bind_param(13, $EMAIL);
			$insertPerson->bind_param(14, $PHONE);
			$insertPerson->bind_param(15, $EMAIL_PRIV);
			$insertPerson->bind_param(16, $PHONE_PRIV);
			$insertPerson->bind_param(17, $JOB);
			$insertPerson->bind_param(18, $VZTAH_DO);
			$insertPerson->bind_param(19, $IDCARD_PHOTO, SQL_LONGVARCHAR);
			$insertPerson->execute();

		}

	}
}

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

#Info about operations
print "=======================================\n";
print "Newly inserted:   \t$inserted\n";
print "Found and skiped: \t$foundAndSkipped\n";
print "Found and updated:\t$foundAndUpdated\n";
print "=======================================\n";

$lock->unlock();

#
# Solve managing CLOB for photos on ID cards
#
# Params: $UCO_PERUN, $TYP_ZAZN, $FOTO
#
sub solveClob() {

	my $UCO_PERUN = shift;
	my $TYP_ZAZN = shift;
	my $FOTO = shift;

	# Check CARD PHOTO -> CLOB data
	my $selectClob = $dbh->prepare(qq{SELECT foto FROM $table_osoby WHERE UCO_PERUN=? and TYP_ZAZN=?});
	$selectClob->execute($UCO_PERUN, $TYP_ZAZN);
	my $result = $selectClob->fetchrow_hashref;
	my $clobData = $result->{"foto"};

	if ((defined $clobData and defined $FOTO)) {
		# both defined - check
		unless ($clobData eq $FOTO) {
			# photo changed - update
			my $updateCard = $dbh->prepare(qq{UPDATE $table_osoby SET foto=? , ZMENENO_KDY=now() WHERE UCO_PERUN=? and TYP_ZAZN=?});
			$updateCard->bind_param( 1, $FOTO, SQL_LONGVARCHAR);
			$updateCard->bind_param( 2, $UCO_PERUN);
			$updateCard->bind_param( 3, $TYP_ZAZN);
			$updateCard->execute();
			return 1;
		}
	} elsif ((!defined $clobData) and (!defined $FOTO)) {
		# both undef - skip
		return 0;
	} else {
		# one defined, other is not -> update
		my $updateCard = $dbh->prepare(qq{UPDATE $table_osoby SET foto=? , ZMENENO_KDY=now() WHERE UCO_PERUN=? and TYP_ZAZN=?});
		$updateCard->bind_param( 1, $FOTO, SQL_LONGVARCHAR);
		$updateCard->bind_param( 2, $UCO_PERUN);
		$updateCard->bind_param( 3, $TYP_ZAZN);
		$updateCard->execute();
		return 1;
	}

	return 0;

}
