#!/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 $tableName = 'IDM2SIS_OSOBY';

# define service
my $service_name = "vsup_kos";

# 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 $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'} = $parts[1];
	$dataByKeys->{$parts[0]}->{'OSB_ID'} = (($parts[2] ne '') ? $parts[2] : undef); # only students have KOS_ID
	$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]}->{'PHONE'} = (($parts[14] ne '') ? $parts[14] : undef);
	$dataByKeys->{$parts[0]}->{'EMAIL_PRIV'} = (($parts[15] ne '') ? $parts[15] : undef);
	$dataByKeys->{$parts[0]}->{'PHONE_PRIV'} = (($parts[16] ne '') ? $parts[16] : undef);
	$dataByKeys->{$parts[0]}->{'CARD_BAR_CODE'} = (($parts[17] ne '') ? $parts[17] : undef);
	$dataByKeys->{$parts[0]}->{'CARD_CHIP_CODE'} = (($parts[18] ne '') ? $parts[18] : undef);
	$dataByKeys->{$parts[0]}->{'CARD_PHOTO'} = (($parts[19] ne '') ? $parts[19] : undef);
}
close FILE;

my $dbh = DBI->connect("dbi:Oracle://$db_machine:$db_port/$db_name", $username, $password,{ RaiseError=>1, AutoCommit=>0, LongReadLen=>65536, ora_charset => 'AL32UTF8'}) 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 = $dataByKeys->{$key}->{'UCO'};
	my $OSB_ID = $dataByKeys->{$key}->{'OSB_ID'};
	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 $PHONE = $dataByKeys->{$key}->{'PHONE'};
	my $EMAIL_PRIV = $dataByKeys->{$key}->{'EMAIL_PRIV'};
	my $PHONE_PRIV = $dataByKeys->{$key}->{'PHONE_PRIV'};

	my $IDCARD_CHIP = $dataByKeys->{$key}->{'CARD_CHIP_CODE'};
	my $IDCARD_BARCODE = $dataByKeys->{$key}->{'CARD_BAR_CODE'};
	my $IDCARD_PHOTO = $dataByKeys->{$key}->{'CARD_PHOTO'};

	# There is
	my $personExists = $dbh->prepare(qq{select 1 from $tableName where UCO=? and TYP_ZAZN=?});
	$personExists->execute($UCO, $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
		my $recordAreEquals;
		if ($TYP_ZAZN eq "S") {

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

			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";
			}

			# CARD PHOTO is not checked here, since we can't pass CLOB to WHERE clause.

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

		} elsif ($TYP_ZAZN eq "P") {

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

			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 ($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";
			}

			$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 $tableName SET USERNAME=? , EMAIL_SKOLNI=? , IDCARD_CHIP=? , IDCARD_BARCODE=? , ZMENENO_KDY=sysdate WHERE UCO=? and TYP_ZAZN=?});
				$updatePerson->execute(
					$LOGIN,
					$EMAIL,
					$IDCARD_CHIP,
					$IDCARD_BARCODE,
					$UCO,
					$TYP_ZAZN);

				solveClob($UCO, $IDCARD_PHOTO);

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

			} elsif ($TYP_ZAZN eq "P") {

				# UPDATE TEACHER
				my $updatePerson = $dbh->prepare(qq{UPDATE $tableName SET USERNAME=? , JMENO=? , PRIJMENI=? , ROD_PRIJMENI=? , ROD_CISLO=? , POHLAVI=? , TITUL_PRED=? , TITUL_ZA=? , EMAIL_SKOLNI=? , TEL_SKOLNI=? , EMAIL_SOUKR=? , TEL_SOUKR=? , FUNKCE=? , ZMENENO_KDY=sysdate WHERE UCO=? and TYP_ZAZN=?});
				$updatePerson->execute(
					$LOGIN,
					$FIRST_NAME,
					$LAST_NAME,
					$BIRTH_LAST_NAME,
					$BIRTH_NUMBER,
					$GENDER,
					$TITLE_BEFORE,
					$TITLE_AFTER,
					$EMAIL,
					$PHONE,
					$EMAIL_PRIV,
					$PHONE_PRIV,
					$JOB,
					$UCO,
					$TYP_ZAZN);
				if($DEBUG == 1) { print "UPDATING EXISTING RECORD: $key\n"; }
				$foundAndUpdated++;

			}

		} else {

			if ($TYP_ZAZN eq "S") {

				# students no change -> check photo change
				my $result = solveClob($UCO, $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 {

				# teachers - no change
				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 $tableName (OSB_ID, UCO, TYP_ZAZN, USERNAME, EMAIL_SKOLNI, IDCARD_CHIP , IDCARD_BARCODE, FOTO , ZMENENO_KDY) VALUES (?,?,?,?,?,?,?,?,sysdate)});
			$insertPerson->bind_param(1, $OSB_ID);
			$insertPerson->bind_param(2, $UCO);
			$insertPerson->bind_param(3, $TYP_ZAZN);
			$insertPerson->bind_param(4, $LOGIN);
			$insertPerson->bind_param(5, $EMAIL);
			$insertPerson->bind_param(6, $IDCARD_CHIP);
			$insertPerson->bind_param(7, $IDCARD_BARCODE);
			$insertPerson->bind_param(8, $IDCARD_PHOTO, SQL_LONGVARCHAR);
			$insertPerson->execute();

		} elsif ($TYP_ZAZN eq "P") {

			# INSERT TEACHER
			my $insertPerson = $dbh->prepare(qq{INSERT INTO $tableName (OSB_ID, UCO, TYP_ZAZN, USERNAME, JMENO, PRIJMENI, ROD_PRIJMENI, ROD_CISLO, POHLAVI, TITUL_PRED, TITUL_ZA, EMAIL_SKOLNI, TEL_SKOLNI, EMAIL_SOUKR, TEL_SOUKR, FUNKCE, ZMENENO_KDY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)});
			$insertPerson->execute(
				$OSB_ID,
				$UCO,
				$TYP_ZAZN,
				$LOGIN,
				$FIRST_NAME,
				$LAST_NAME,
				$BIRTH_LAST_NAME,
				$BIRTH_NUMBER,
				$GENDER,
				$TITLE_BEFORE,
				$TITLE_AFTER,
				$EMAIL,
				$PHONE,
				$EMAIL_PRIV,
				$PHONE_PRIV,
				$JOB
			);

		}

	}
}

commit $dbh;
$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 on students IDCARDS
#
# Params: $UCO, $IDCARD_PHOTO
#
sub solveClob() {

	my $UCO = shift;
	my $IDCARD_PHOTO = shift;

	# Check CARD PHOTO -> CLOB data of Students
	my $selectClob = $dbh->prepare(qq{SELECT FOTO from $tableName WHERE UCO=? and TYP_ZAZN='S'});
	$selectClob->execute($UCO);
	my $result = $selectClob->fetchrow_hashref;
	my $clobData = $result->{"FOTO"};

	if ((defined $clobData and defined $IDCARD_PHOTO)) {
		# both defined - check
		unless ($clobData eq $IDCARD_PHOTO) {
			# photo changed - update
			my $updatePerson = $dbh->prepare(qq{UPDATE $tableName SET FOTO=? , ZMENENO_KDY=sysdate WHERE UCO=? and TYP_ZAZN='S'});
			$updatePerson->bind_param( 1, $IDCARD_PHOTO, SQL_LONGVARCHAR);
			$updatePerson->bind_param( 2, $UCO);
			$updatePerson->execute();
			return 1;
		}
	} elsif ((!defined $clobData) and (!defined $IDCARD_PHOTO)) {
		# both undef - skip
		return 0;
	} else {
		# one defined, other is not -> update
		my $updatePerson = $dbh->prepare(qq{UPDATE $tableName SET FOTO=? , ZMENENO_KDY=sysdate WHERE UCO=? and TYP_ZAZN='S'});
		$updatePerson->bind_param( 1, $IDCARD_PHOTO, SQL_LONGVARCHAR);
		$updatePerson->bind_param( 2, $UCO);
		$updatePerson->execute();
		return 1;
	}

	return 0;

}
