#!/usr/bin/perl
#
# Documentation http://wiki.five9.com/twiki/bin/view/Main/VerintAgentSyncInterface
#
## 

use Data::Dumper;
use DBI;
use POSIX;
use Sys::Syslog;
use XML::LibXML;
use strict;
use utf8;
use Crypt::AuthEnc::GCM qw(gcm_decrypt_verify);
use Net::SFTP::Foreign;
use File::Basename;
use File::Path qw(make_path);
use URI;
#use warnings; 
#use diagnostics;

my $VERSION = '13.0.1';

# Support agent level sync for Verint packages & permissions
# as usual with Verint, it simply pile of hardcoded constants :(
# 
# See Java counterpart - /clients-api/source/java/com/five9/verint/VerintPackage.java
my $CALL_RECORDING = 1;
my $SCREEN_RECORDING = 2;
my $QUALITY_MONITORING = 3;
my $ANALYTICS_DRIVEN_QUALITY = 4;
my $SPEECH_ANALYTICS = 5;
my $WORKFORCE_MANAGEMENT = 6;
my $PERFORMANCE_MANAGEMENT = 7;
my $ADVANCED_DESKTOP_ANALYTICS = 8;

# Map packages to permission names in Verint
my %VerintPackages = (
    $CALL_RECORDING => "Agent_Call_Recording",
    $SCREEN_RECORDING => "Agent_Screen_Recording",
    $QUALITY_MONITORING => "Agent_Quality_Monitoring",
    $ANALYTICS_DRIVEN_QUALITY => "Agent_Analytics_Driven_Quality",
    $SPEECH_ANALYTICS => "Agent_Speech_Analytics",
    $WORKFORCE_MANAGEMENT => "Agent_Workforce_Management",
    $PERFORMANCE_MANAGEMENT => "Agent_Performance_Management",
    $ADVANCED_DESKTOP_ANALYTICS => "Agent_Advanced_Desktop_Analytics"
);

# Map packages to tenant features
my %VerintPackages2TenantFeatures = (
    $CALL_RECORDING => 14,
    $SCREEN_RECORDING => 56,
    $QUALITY_MONITORING => 113,
    $ANALYTICS_DRIVEN_QUALITY => 114,
    $SPEECH_ANALYTICS => 115,
    $WORKFORCE_MANAGEMENT => 53,
    $PERFORMANCE_MANAGEMENT => 117,
    $ADVANCED_DESKTOP_ANALYTICS => 118
);

#
# import static com.five9.cc.interfaces.VerintDataSourceIdent.SCREEN_RECORDING_CODE
#
# Screen recording datasource name is autogenerated using pattern DS_${VerintCode}_S
# where Verint Code is configured per domain, and stored in domain.wfm_verint_code,
# and "_S" is a hardcoded constant.
# 
my $SCREEN_RECORDING_CODE = "S";

#
# import static com.five9.cc.interfaces.VERINT_ENABLE_SCREEN_RECORDING
#
# Screen recording can be enabled/disabled per user. Corresponding flag 
# in user_cust.flags3 table or user_profile.flags
#
my $VERINT_ENABLE_SCREEN_RECORDING = 168;

# import static com.five9.cc.interfaces.UserAttributes.SEND_TO_VERINT
#
# Corresponding flag in user_cust.flags3 or user_profile.flags
#
my $SEND_TO_VERINT = 172;

# 
# Disable users deleted from VCC based on Agent Information Export file
# in case multiple files are available, the one with latest modification time is used
# 
# Default value: none (users are not disabled)
# Example: /verint/spool/wins_all_*.xml
# 
# 
my $aieFilePatternProperty = "com.five9.verint.VerintSync.aieFilePattern"; 

# 
# Agent Information Export file is considered expired if it is too old and users are not disabled. 
# 
# Default value : 86400 (1 day)
# 
my $aieExpirationSecondsProperty = "com.five9.verint.VerintSync.aieExpirationSeconds";
my $aieExpirationSecondsDefault = 86400; # 1 day

#
# Cleanup old AIE files.
#
# Default: 7 days
# 
my $aieRetentionDaysProperty = "com.five9.verint.VerintSync.aieRetentionDays";
my $aieRetentionDaysDefault = 7;

# 
# Protection against incomplete Agent Information Export file, 
# it is considered completed if has not been modified during some time. 
# 
# Default value : 60 (1 minute)
# 
my $aieTooRecentSecondsProperty = "com.five9.verint.VerintSync.aieTooRecentSeconds";
my $aieTooRecentSecondsDefault = 60; # 1 minute

#
# Send notification on failure.
#
# Default: undefined (notifications disable)
# 
my $errorMailToProperty = "com.five9.verint.VerintSync.errorMailTo";

# 
# Protection against UserName still being in use by a terminated employee,
# EmployeeID is appended to deleted logins , i.e. aaa => aaa-123_AB1234 
# 
# Possible values: 0 | 1
# Default value : 0 (do not rename)
# 
my $renameDisabledLoginsProperty = "com.five9.verint.VerintSync.renameDisabledLogins";
my $renameDisabledLoginsDefault = 0; 

#
# Shell command to export file into windows network share.
#
# Default value: none (export is disabled)
# Example:
# 
#  com.five9.verint.VerintSync.smb=smbclient -W Lab --user=login%password "//vdbdev.five9lab.com/ASI Import"
#
my $smbProperty = "com.five9.verint.VerintSync.smb";

#
# Folder to store Agent Sync XML files to.
# If not specified, xml files are temporary written into /tmp
# and removed once exported to "ASI Import" network share.
#
# Spool can be specified per account as well, for example 
# com.five9.verint.VerintSync.spool.UN-120152=/mnt/vrntusers/PROSPER
#
# If spool is specified per account, organization is ommited by default
# just in case, it still can be included, for example  
# com.five9.verint.VerintSync.spool.UN-123456.withorg=1
#
# Default: undef
#
my $spoolProperty = "com.five9.verint.VerintSync.spool";

# 
# Sync roles, i.e. Agent/Admin/Supervisor
#
# Roles can be specified per account as well, for example 
# com.five9.verint.VerintSync.syncRoles.UN-120152=0
#
# Default: true
#
my $syncRolesProperty = "com.five9.verint.VerintSync.syncRoles";
my $syncRolesDefault = 1;

# 
# Sendmail location to send notification with errors and warnings via email
# 
# Default: /usr/lib/sendmail -t -oi -oem
# 
my $sendmailProperty = "com.five9.verint.VerintSync.sendmail";
my $sendmailDefault = "/usr/lib/sendmail -t -oi -oem";

#
# Timezone can be specified per tenant, i.e.
#   com.five9.verint.VerintSync.timeZoneName.UN-120152=America/Chicago
#
# Default: America/Los_Angeles
#
my $timeZoneNameProperty = "com.five9.verint.VerintSync.timeZoneName";
my $timeZoneNameDefault = "America/Los_Angeles";

# 
# WeekStartDay can be specified per tenant as well, i.e.
#  com.five9.verint.VerintSync.weekStartDay.UN-120152=2
#
# 1=Sun
# 2=Mon
# 3=Tue
# 4=Wed
# 5=Thu
# 6=Fri
# 7=Sat
#
# Default: undefined (WeekStartDay is not added into agent sync xml)
#
my $weekStartDayProperty = "com.five9.verint.VerintSync.weekStartDay";
my $weekStartDayDefault = undef;

#
# Offset, in minutes, of day boundary from local time midnight. Normally 0, max value is 1440
#
# Can be specified per tenant, i.e.
#   com.five9.verint.VerintSync.dayBoundaryOffset.UN-120152=6
#
# Default: undefined (DayBoundaryOffset is not added into agent sync xml)
#
my $dayBoundaryOffsetProperty = "com.five9.verint.VerintSync.dayBoundaryOffset";
my $dayBoundaryOffsetDefault = undef;

# undef (default) - on, 0 - off, 1 - on
my $skillCacheProperty = "com.five9.verint.VerintSync.agentSkillCacheOn";

# if there are more changed emploees in -diff mode than threshold, the -diff mode is ignored
my $diffThresholdProperty = "com.five9.verint.VerintSync.diffThreshold";
my $diffThresholdDefault  = 100000;
my $diffThreshold  = $diffThresholdDefault;

# default is 8443
my $verintProxyHttpsPortProperty = "com.five9.verint.VerintSync.verintProxyHttpsPort";

# default is '-k'. It means insecure, so it is needed for production to configure certificate e.g. '--cacert my-ca.crt' 
my $curlOptionsProperty = "com.five9.verint.VerintSync.curlOptions";

# default is '/home/five9inf/ssl' - folder for certificates, used for nas passwords decryption
my $certFolderProperty = "com.five9.verint.VerintSync.certFolder";

# default is 'StrictHostKeyChecking no' - it is not fit for production
my $sftpOptionsProperty = "com.five9.verint.VerintSync.sftpOptions";

my $tmpFolderProperty = "com.five9.verint.VerintSync.tmpFolder";
my $tmpFolderDefault = '/tmp/asisync/';

# Location of configuration files. Order is important!
my @configDefault = (
  '/home/five9inf/vcc/conf/*.properties', 
  '/etc/vcc/*.properties',
  ( defined $ENV{'HOME'} ? $ENV{'HOME'} : "." ) . '/.VerintSync.properties',
  './VerintSync.properties'
);

# runtime properties
my %properties;

# select property_name, property_value from vcc_config
my %vccConfig;

# parameters in command line
my %commandLine;        

# select id, name from role
my %five9AppRole;

# roles can not be created automatically,
# collect all roles to be reported as warning 
my %aieMissedRoles;

# screen recording datasources shall be created manually
my %aieMissedScreenRecordingDataSources;

# Agent Information Export XML related runtime
# instead of parsing XML every time, read it once, prepare in-memory hash
# and reuse it for each domain    
my $aieXmlParseError = undef;
my %aieCache;

my %agentSkillHash;
my %agentSkillHashChanged;

my %asiNases;
my %dataKeys;

# entry point
main(@ARGV);

exit;

# trim whitespaces from beginig and end 
sub trim {
    my ($s) = @_;
    $s =~ s/^\s*(.*?)\s*$/$1/ if defined $s;
    return $s;
}

# return value similar to $node->findvalue("./tag") 
# but ignores namespaces and case
sub getXmlValue {
    my ($node, $tag) = @_;
    
    if (! defined($node)) {
        return undef;
    }
    
    foreach my $childNode ($node->childNodes()) {
        # print $childNode->nodeType . " : " . $childNode->nodeName . "\n";
        if ($childNode->nodeName eq $tag) {
            foreach my $textNode ($childNode->childNodes()) {
                if ($textNode->nodeType == XML_TEXT_NODE) {
                    return $textNode->nodeValue;
                }
            }
            return "";
        }
    }
    return undef;    
}

sub findXmlNode {
    my ($node, $tag) = @_;
    
    if (! defined($node)) {
        return undef;
    }
    
    foreach my $childNode ($node->childNodes()) {
        if ($childNode->nodeName eq $tag) {
            return $childNode;
        }
    }
    return undef;
}

# compare two xml nodes,
# return true if nodes are the same
sub diff {
    my ($n1, $n2) = @_;

    if (!defined($n1) && !defined($n2)) {
        return 0;
    } elsif (defined($n1) && !defined($n2)) {
        return 1;
    } elsif (! defined($n1) && defined($n2)) {
        return 1;
    }

    print $n1->nodeType . " " . $n1->nodeName . " " . $n1->nodeValue . "\n";
    print $n2->nodeType . " " . $n2->nodeName . " " . $n2->nodeValue . "\n";
        
    if ($n1->nodeName ne $n2->nodeName) {
        return 1;
    }
    if ($n1->nodeType ne $n2->nodeType) {
        return 1;
    }
    
    $n1 = $n1->firstChild();
    if ($n1->nodeType == 3 && $n2->nodeName eq "#text" && $n1->nodeValue =~ /^\s+$/) {
        $n1->nextNonBlankSibling();
    }
    $n2 = $n2->firstChild();
    if ($n2->nodeType == 3 && $n2->nodeName eq "#text" && $n2->nodeValue =~ /^\s+$/) {
        $n2->nextNonBlankSibling();
    }

    while ($n1 || $n2) {
        if (diff($n1, $n2)) {
            return 1;
        }
        $n1 = $n1->nextNonBlankSibling();
        $n2 = $n2->nextNonBlankSibling();
    }
    
    return 0;
}

# compare Employee prepared for ASI with Employee exported from AIE
# return true 1 if different, 0 if matches
sub isEmployeeDifferent {
    my ($Employee, $aieEmployeeHash) = @_;
    
    if (! defined($Employee) && ! defined($aieEmployeeHash)) {
        return 0;
    }
    
    if (! defined($Employee) || ! defined($aieEmployeeHash)) {
        return 1;
    }
    
    if ($aieEmployeeHash->{"IsSupervisor"} ne getXmlValue($Employee, "IsSupervisor")) {
        syslog 'debug', 'isEmployeeDifferent() IsSupervisor';
        return 1;
    }
    
    if ($aieEmployeeHash->{"FirstName"} ne getXmlValue($Employee, "FirstName")) {
        syslog 'debug', 'isEmployeeDifferent() FirstName';
        return 1;
    }

    if ($aieEmployeeHash->{"LastName"} ne getXmlValue($Employee, "LastName")) {
        syslog 'debug', 'isEmployeeDifferent() LastName';
        return 1;
    }

    if ($aieEmployeeHash->{"EndDate"} ne getXmlValue($Employee, "EndDate")) {
        syslog 'debug', 'isEmployeeDifferent() EndDate';
        return 1;
    }
    
    my $AdditionalInfo = findXmlNode($Employee, "AdditionalInfo");
    if (! defined $AdditionalInfo) {
        syslog 'debug', 'isEmployeeDifferent() AdditionalInfo';
        return 1;
    }
    # email
    if ($aieEmployeeHash->{"PersonContact"} 
           ne getXmlValue(findXmlNode(findXmlNode($AdditionalInfo, "Person"), "PersonContact"), "Value") ) {
        syslog 'debug', 'isEmployeeDifferent() PersonContact';
        return 1;
    }
    # EmployeeDataSource
    my $EmployeeDataSource = findXmlNode($AdditionalInfo, "EmployeeDataSource"); 
    if (defined($EmployeeDataSource)) {
        my $DataSourceName = getXmlValue($EmployeeDataSource, "DataSourceName");
        my $ExternalEmpIdent = getXmlValue($EmployeeDataSource, "ExternalEmpIdent");
        if (! defined($aieEmployeeHash->{"EmployeeDataSource"}->{$DataSourceName})) {
            syslog 'debug', 'isEmployeeDifferent() EmployeeDataSource';
            return 1;
        }
        if ($aieEmployeeHash->{"EmployeeDataSource"}->{$DataSourceName}->{"ExternalEmpIdent"} ne $ExternalEmpIdent) {
            syslog 'debug', 'isEmployeeDifferent() EmployeeDataSource ExternalEmpIdent';
            return 1;
        }
    }
    
    # EmployeeDataSourceList _S
    # EmployeeDataSourceList _FTCI_ADHERENCE
    foreach my $EmployeeDataSourceList ($AdditionalInfo->childNodes()) {
        if ($EmployeeDataSourceList->nodeName() ne "EmployeeDataSourceList") {
            next;
        }
        my $DataSourceName = getXmlValue($EmployeeDataSourceList, "DataSourceName");
        my $ExternalEmpIdent = getXmlValue(findXmlNode($EmployeeDataSourceList, "EmployeeDataSourceInfo"), "ExternalEmpIdent");
        my $aieVal = $aieEmployeeHash->{"EmployeeDataSource"}->{$DataSourceName}->{"ExternalEmpIdent"};
        my $same = (defined($ExternalEmpIdent) && defined($aieVal) && $aieVal eq $ExternalEmpIdent) || 
                    (!defined($ExternalEmpIdent) && !defined($aieVal));
                    
        # special handling for ExternalEmpIdent == "" - this syntax explicitly unasign employee from datasource
        if ((! $same) && ($ExternalEmpIdent ne "")) {
            syslog 'debug', 'isEmployeeDifferent() EmployeeDataSourceList DataSourceName=%s ExternalEmpIdent=%s', $DataSourceName, defined($ExternalEmpIdent)? $ExternalEmpIdent: "undef" ;
            return 1;
        }
    }
    
    # EmployeeRole
    my $EmployeeRole = findXmlNode($AdditionalInfo, "EmployeeRole");
    if (defined($EmployeeRole)) {
        my $userName = getXmlValue($EmployeeRole, "UserName");
        if ($userName ne $aieEmployeeHash->{"UserName"}) {
            syslog 'debug', 'isEmployeeDifferent() UserName';
            return 1;
        }
        
        if (! defined($aieEmployeeHash->{"Role"})) {
            syslog 'debug', 'isEmployeeDifferent() aieEmployeeRole';
            return 1;
        }
        foreach my $Role ($EmployeeRole->childNodes()) {
            if ($Role->nodeName() ne "Role") {
                next;
            }
            my $roleName = getXmlValue($Role, "Name");
            if (! defined($aieEmployeeHash->{"Role"}->{$roleName})) {
                syslog 'debug', 'isEmployeeDifferent() Role %s', $roleName;
                return 1;
            }
        }
        if (defined(findXmlNode(findXmlNode($EmployeeRole, "SyncMethod"), "snapshot"))) {
            # snapshot
            # syslog 'debug', 'isEmployeeDifferent() EmployeeRole snapshot';
            foreach my $roleName (keys %{$aieEmployeeHash->{"Role"}}) {
                my $f = 0;
                foreach my $Role ($EmployeeRole->childNodes()) {
                    if ($Role->nodeName() ne "Role") {
                        next;
                    }
                    if ($roleName eq getXmlValue($Role, "Name")) {
                        $f = 1;
                        last;
                    }
                }
                if (! $f) {
                    syslog 'debug', 'isEmployeeDifferent() aieRole %s', $roleName;
                    return 1;
                }
            }
        }
    }

    return 0;
}

# 
sub verintQuote {
    my ($s, $minLength, $maxLength) = @_;
    
    my $r = $s;

    if (defined($minLength)) {
        if (! defined($r)) {
            $r = "";
        }
        if(length($r) < $minLength) {
            for (my $i = length($r); $i<$minLength; $i++) {
                $r .= "-";
            }
        }
    }
    
    # Strip out all special characters except "&" which should be replaced with "and"
    $r =~ s/\&/ and /g;
    $r =~ s/[\[\]\<\>\"\&\!\?\.\,]//g;
    
    # Remove/strip out characters beyond 50 characters
    if (defined($maxLength)) {
        $r = substr($r, 0, $maxLength);
    }
        
    return $r;
}

# read all the properties values and store it in global %properties hash 
sub loadProperties {
    my ($config) = @_;
    foreach my $conf (@$config) {
        foreach my $file (glob($conf)) {
            my $fh;
            if ( open $fh, "<", $file ) {
                syslog 'debug', "loadProperties() loading %s", $file;
                while (my $s = <$fh>) {
                    $s =~ s/\r?\n$//;
                    if ($s =~ /^\s*[\#\;]/) {
                        next;
                    }
                    if ($s =~ /^([^=]+)\s*=\s*(.*)\s*$/) {
                        $properties{$1} = $2;
                    }
                }
            } else {
                syslog 'debug', "loadProperties() Can not open %s : %s", $file, $!;
                # warn "Can not open $file : $!";
            }
        }
    }
    # print Dumper(\%properties);
}

sub decryptString {
    my($str, $keyFile) = @_;
    $str =~ s/\s+//g;
    if(!defined($str)) {
        syslog 'err', 'encrypted-password is not defined';
        return undef;
    }
    if(!defined($keyFile)) {
        syslog 'err', 'master-key is not defined';
        return undef;
    }
    my $decrypted = `echo -n '$str'|openssl base64 -d -A|openssl pkeyutl -decrypt -inkey '$keyFile' -pkeyopt rsa_padding_mode:oaep 2>/dev/null`;
    if($? > 255) {
        syslog 'err', 'openssl returned error: ' . ($? >> 8);
        return undef;
    }
    return $decrypted;
}

sub decryptPass {
    my ($keyFile, $ckey, $cpass, $aad) = @_;
    my $key = decryptString($ckey, $keyFile);
    if(!$key) {
        syslog 'err', 'cannot decrypt key';
        return undef;
    }
    my $cpass_dec = `echo -n '$cpass'|openssl base64 -d -A`;
    my $hex = (unpack 'H*', $cpass_dec);
    my $n = (length $hex) - 24 - 32;
    my ($iv, $txt, $tag) = unpack "(H24)(H$n)(H32)", $cpass_dec;
    my $adata = sprintf "%016x", $aad;

    my $pt = gcm_decrypt_verify('AES', $key , (pack 'H*', $iv), (pack 'H*', $adata), (pack 'H*', $txt), (pack 'H*', $tag));
    if (!$pt) {
        syslog 'err', 'cannot decrypt password';
    } 
    return $pt;
}

sub getMainDBConnection {
    my $conn;
    for my $MainDB (qw/MainDBRep MainDB/) {
        my $connectionUrl = $properties{"vcc.ds.$MainDB.connection-url"};
        my $username = $properties{"vcc.ds.$MainDB.username"};
        $username = $properties{"vcc.db.username"} unless($username);
        my $password = $properties{"vcc.ds.$MainDB.encrypted-password"};
        $password = $properties{"vcc.db.encrypted-password"} unless($password);
        my $keyFile = $properties{"vcc.ds.$MainDB.master-key"};
        $keyFile = $properties{"vcc.crypto.master-key"} unless($keyFile);
        next unless(defined($connectionUrl));
        next unless(defined($username));
        next unless (defined($password));
        next unless (defined($keyFile));
        $password = decryptString($password, $keyFile);
        return undef unless(defined($password));
        syslog 'debug', 'getMainDBConnection() connectionUrl=%s' , $connectionUrl;
        if( $connectionUrl =~ /^\s*jdbc\s*:\s*([^:]+)\s*:(loadbalance\s*:)?\s*\/\/\s*([^:\/\s\?,]+)(\s*:\s*(\d+))?[^\/\s\?]*\s*\/\s*([^\?]+)(\s*\?\s*(.*))?\s*$/) {
            my %connParms = (
                'driver' => defined($1) ? $1 : '',
                'db-host' => defined($3) ? $3 : 'localhost',
                'db-port' => defined($5) ? $5 : '3306',
                'db-name' => defined($6) ? $6 : '',
                'connection-params' => defined($8) ? $8 : ''
            );

            my $dsn = 'DBI:mysql'.
                  ':database='.$connParms{'db-name'}.
                  ':host='.$connParms{'db-host'}.
                  ':port='.$connParms{'db-port'};
            syslog 'debug', 'getMainDBConnection() dsn=%s' , $dsn;
            eval {
               $conn = DBI->connect($dsn, $username, $password, { RaiseError => 1 , mysql_enable_utf8 => 1 });
               my $sth = $conn->prepare("set names 'utf8' collate 'utf8_unicode_ci'");
               $sth->execute();
               $sth->finish();
            };
            if ($@) {
                syslog 'warning', "main() Unable connect to %s", $dsn;
                warn "Unable connect to $dsn";
                next;
            } else {
                last;
            }

        } else {
            syslog 'warning', "main() Unable to parse connection-url: %s", $connectionUrl;
            warn "Unable to parse connection-url: $connectionUrl";
            next;
        }
        
    }
    return $conn;
}

sub getCustomerDBConnection {
    my ($tenant, $isRW) = @_;
    my $conn;

    my $username;
    my $password;
    my $keyFile;
    for my $MainDB (qw/MainDBRep MainDB/) {
        $username = $properties{"vcc.ds.$MainDB.username"};
        $username = $properties{"vcc.db.username"} unless($username);
        $password = $properties{"vcc.ds.$MainDB.encrypted-password"};
        $password = $properties{"vcc.db.encrypted-password"} unless($password);
        $keyFile = $properties{"vcc.ds.$MainDB.master-key"};
        $keyFile = $properties{"vcc.crypto.master-key"} unless($keyFile);
        if (defined($username) && defined($password) && defined($keyFile)) {
            last;
        }
    }

    $password = decryptString($password, $keyFile);
    return undef unless(defined($password));

    my $dsn = 'DBI:mysql'.
                ':database='.$tenant->{'db_name'}.
                ':host='.($isRW? $tenant->{'rwdb_host'}: $tenant->{'db_host'}).
                ':port='.($isRW? $tenant->{'rwdb_port'}: $tenant->{'db_port'});
    syslog 'debug', 'getCustomerDBConnection() dsn=%s isRW=%b' , $dsn, $isRW;
    
    $conn = DBI->connect($dsn, $username, $password, { RaiseError => 1, mysql_enable_utf8 => 1 });
    
    my $sth = $conn->prepare("set names 'utf8' collate 'utf8_unicode_ci'");
    $sth->execute();
    $sth->finish();
    
    return $conn;
}

##
# Select p.id, p.name, db_server.host, db_server.port, db_name from tenant. 
#
# return a reference to a hash
#
##
sub fetchTenants {
    my ($conn) = @_;
    my %tenants;
    
    my $href;
    
    my $whereDomain = (defined($commandLine{"domain-ids"}) ? (" and domain.id in (".$commandLine{"domain-ids"}.")") :
            " and (wfm.tenant_feature_fk is not null or cr.tenant_feature_fk is not null or txt.tenant_feature_fk is not null) ");
            
    $whereDomain .= defined($commandLine{"env-ids"}) ? " and env.id in (".$commandLine{"env-ids"}.") ": "";

    my $sth = $conn->prepare(
        "select 
              t.id as tenantId, t.name as tenantName, 
              domain.id as domainId, domain.name as domainName, 
              ifnull(rep_db_server.host, db_server.host) as db_host, 
              ifnull(rep_db_server.port, db_server.port) as db_port, 
              db_server.host as rwdb_host, 
              db_server.port as rwdb_port, 
              t.db_name, 
              domain.wfm_verint_code,
              extents.num_agents,
              wfm.tenant_feature_fk is not null as WFM_VERINT,
              cr.tenant_feature_fk  is not null as VERINT_CALL_RECORDING,
              sr.tenant_feature_fk  is not null as QM_VERINT_SCREEN_RECORDING, 
              txt.tenant_feature_fk is not null as TEXT_REC, 
              account.num,
              domain.wfm_verint_flags & (1<<5) as VERINT_AGENT_LEVEL_PACKAGES,
              domain.wfm_verint_flags & (1<<7) as VERINT_ASI,
              account.id as accountId,
              env.id as envId,
              env.enabled as envEnabled,
              env.pdc_asi_nas_fk as pdcNas,
              env.bdc_asi_nas_fk as bdcNas,
              env.export_template as aieTemplate,
              domain.verint_fdow as fdow
            from tenant t
              inner join account on t.account_fk=account.id
              inner join extents on extents.account_fk=account.id
              inner join partition_domains on partition_domains.partition_fk=t.id
              inner join domain on partition_domains.domain_fk=domain.id
              inner join data_source on data_source.name=t.ds_name
              inner join db_server on data_source.primary_db_server_fk=db_server.id
              left join data_source rep_data_source on rep_data_source.name=concat('Rep', t.ds_name)
              left join db_server rep_db_server on rep_data_source.primary_db_server_fk=rep_db_server.id
              left join partition_tenant_features cr on cr.partition_fk=t.id and cr.tenant_feature_fk=14 
              left join partition_tenant_features wfm on wfm.partition_fk=t.id and wfm.tenant_feature_fk=53 
              left join partition_tenant_features sr on sr.partition_fk=t.id and sr.tenant_feature_fk=56 
              left join partition_tenant_features txt on txt.partition_fk=t.id and txt.tenant_feature_fk=126
              left join verint_env env on env.id=domain.verint_env_fk
        where t.flags & 1 = 0 /* enabled */ and domain.flags & (1<<17) = 0 /* enabled */ "
        . $whereDomain
    );
    $sth->execute();
    my $tenant;
    my $isNasUse = 0;
    while (defined($tenant = $sth->fetchrow_hashref()) ) {
        $tenants{$tenant->{"tenantId"}} = $tenant;
        my %tenantFeatures;
        $tenants{$tenant->{"tenantId"}}->{"tenantFeatures"} = {};
        my $st = $conn->prepare("select tenant_feature_fk from partition_tenant_features where partition_fk=?");
        $st->execute($tenant->{"tenantId"});
        while (defined($href = $st->fetchrow_hashref()) ) {
            $tenantFeatures{$href->{"tenant_feature_fk"}} = 1;
        }
        $st->finish();
        $tenants{$tenant->{"tenantId"}}->{"tenantFeatures"} = \%tenantFeatures;
        
        if(isNasEnabled($tenant)) {
            $isNasUse = 1;
            $tenant->{"isPdc"} = isPdc($conn, $tenant->{"domainId"});
            my @tenantVerintProxies;
            my $st2 = $conn->prepare("SELECT h.name FROM provider p
                                        INNER JOIN host h ON h.provider_fk=p.id
                                        INNER JOIN host_host_roles hhr ON hhr.host_fk=h.id
                                        INNER JOIN host_roles hr ON hr.id=hhr.host_role_fk AND hr.name='verint-proxy'
                                        INNER JOIN host h2 ON h2.provider_fk=p.id
                                        INNER JOIN domain_host dh ON dh.host_fk=h2.id
                                        INNER JOIN domain d ON dh.id=
                                          IF(d.bkp_domain_host_fk IS NULL OR ? ,
                                          d.domain_host_fk, d.bkp_domain_host_fk) AND d.id=?");
            $st2->execute($tenant->{"isPdc"}, $tenant->{"domainId"});
            while (defined($href = $st2->fetchrow_hashref()) ) {
               push @tenantVerintProxies, $href->{"name"};
            }
            $st2->finish();
            $tenants{$tenant->{"tenantId"}}->{"verintProxies"} = \@tenantVerintProxies;
        }
    }
    $sth->finish();
    if ($isNasUse) {
        loadAsiNases($conn);
        loadDataKeys($conn);
    }
    return \%tenants;
}

sub loadAsiNases {
     my ($conn) = @_;
     my $href;
     my $st = $conn->prepare(
     "SELECT n.id, n.ftp_username, n.ftp_dnsname, 
             n.mount_point, n.root_path, sv.data_key_fk, sv.ciphertext
          FROM nas n  
          INNER JOIN secret_vault sv ON n.secret_fk=sv.id 
          WHERE n.nas_type = 0 and (n.flags & (1<<22) != 0)");
     $st->execute();
     %asiNases = ();
     while (defined($href = $st->fetchrow_hashref()) ) {
           $asiNases{$href->{"id"}} = $href;
     }
     $st->finish();
}

sub loadDataKeys {
     my ($conn) = @_;
     my $href;
     my $st = $conn->prepare("select id, key_ciphertext, master_key_name from data_key");
     $st->execute();
     %dataKeys = ();
     while (defined($href = $st->fetchrow_hashref()) ) {
           $dataKeys{$href->{"id"}} = $href;
     }
     $st->finish();
}

sub isPdc {
    my ($conn, $dId) = @_;
    my $href;
    my $rt = 1;
    my $st = $conn->prepare("SELECT (SELECT IF(ot.name LIKE '%TO_PRIMARY%' OR o.timestamp_ms IS NULL, NULL, -1)
                                              FROM ha_operations o
                                              INNER JOIN ha_operation_domains od ON od.operation_fk=o.id
                                              INNER JOIN ha_operation_types ot ON ot.ordinal=o.ha_operation_type_fk
                                              WHERE od.domain_fk=?
                                              ORDER BY o.timestamp_ms DESC
                                              LIMIT 1) is null as isPdc");
    $st->execute($dId);
    if( defined($href = $st->fetchrow_hashref()) ) {
               $rt = $href->{"isPdc"};
    }
    $st->finish();
    return $rt;
}
#
# Check if recent Agent Information Export is available,
# and parse file contents into XML document 
#
# Return a reference to document or undef if error happened.
# 
sub parseAieXml {
    my ($tenant) = @_;
    my $aieFilePattern = getAIEPattern($tenant);
    if (!$aieFilePattern) {
        syslog 'err', 'parseAieXml() aieFilePattern is undef';
        return;
    }
    my $key4cache = $aieFilePattern;
    my $ts = time();
    my $tsTooRecent = $ts - getProperty($aieTooRecentSecondsProperty, $aieTooRecentSecondsDefault);
    if (isNasEnabled($tenant)) {
        my $nasId = $tenant->{"isPdc"} ? $tenant->{"pdcNas"} : $tenant->{"bdcNas"};
        $key4cache .= " nasId: " . $nasId;
        if (exists $aieCache{$key4cache}) {
            return ($aieCache{$key4cache}[0], $aieCache{$key4cache}[1]);
        }
        my $nas = $asiNases{$nasId};
        my $dk = $dataKeys{$nas->{"data_key_fk"}};
        my $keyFile = sprintf "%s/%s.pem", getProperty($certFolderProperty, '/home/five9inf/ssl'), $dk->{"master_key_name"};
        my $pass = decryptPass($keyFile, $dk->{"key_ciphertext"}, $nas->{"ciphertext"}, $nasId);
        my $locFolder = getLocalFolder4Nas($nasId);
        make_path($locFolder);
        loadAieFromNas($nas->{"ftp_dnsname"}, $nas->{"ftp_username"}, $pass, $nas->{"mount_point"}, $nas->{"root_path"}, $aieFilePattern, $locFolder);
        $aieFilePattern = $locFolder . '*';
        $tsTooRecent = $ts+1; #doest not check if someone writes in file. 
    } else {
        if (exists $aieCache{$key4cache}) {
            return ($aieCache{$key4cache}[0], $aieCache{$key4cache}[1]);
        }
    }
    my $aieNewestFile = undef;

    syslog 'debug', 'parseAieXml() aieFilePattern = %s', $aieFilePattern;
    my $tsExpiration = $ts - getProperty($aieExpirationSecondsProperty, $aieExpirationSecondsDefault);
    my $tsTooOld = $ts - getProperty($aieRetentionDaysProperty, $aieRetentionDaysDefault) * 86400;
    if ($tsTooOld >= $ts) {
        $tsTooOld = 0; # zero or negative numbers means disabled
    }
    my $aieNewestMtime = 0;
    foreach my $aieFile (glob($aieFilePattern)) {
        my $mtime = (stat($aieFile))[9];
        if ($mtime > $tsTooRecent) {
            syslog 'debug', 'parseAieXml() aieFile = %s is modified %d sec ago, skipped', $aieFile, $ts - $mtime;
            next;
        } elsif ($mtime > $tsExpiration) {
            if ( ($mtime > $aieNewestMtime) && (-s $aieFile) ) {
                $aieNewestMtime = $mtime;
                $aieNewestFile = $aieFile;
            }
        } elsif ($mtime < $tsTooOld) {
            syslog 'debug', 'parseAieXml() aieFile = %s deleted', $aieFile;
            unlink($aieFile);
        }
    }
    my @a = parseFile($aieNewestFile);
    $aieCache{$key4cache} = \@a;
    return ($aieCache{$key4cache}[0], $aieCache{$key4cache}[1]);
}

sub getLocalFolder4Nas {
    my ($nasId) = @_;   
    return sprintf "%sexport/%d/", getProperty($tmpFolderProperty, $tmpFolderDefault), $nasId;
}

sub parseFile {
    my ($aieNewestFile) = @_;
    my $aieHashParsed;
    my $aieXmlUsernamesParsed;
    if (defined($aieNewestFile)) {
        my $aieXml;
        eval {
            syslog 'debug', 'parseFile() %s has non-zero size, going to check deleted users', $aieNewestFile;
            my $parser = XML::LibXML->new();
            $aieXml = $parser->parse_file($aieNewestFile);
            ($aieHashParsed, $aieXmlUsernamesParsed) = convertAieXmlToHash($aieXml);
            $aieXml = undef; # try to release memory
        };
        if ($@) {
            $aieXmlParseError = sprintf('Can not parse file %s : %s', $aieNewestFile, $@);
            syslog 'debug', 'parseFile() %s', $aieXmlParseError;
        }
    } else {
        $aieXmlParseError = 'Agent Information Export is not available, EndDate can not be set for inactive or disabled Employees, FTCI_ADHERENCE will not be generated';
        syslog 'debug', 'parseFile() %s', $aieXmlParseError;
    }
    return ($aieHashParsed, $aieXmlUsernamesParsed);
}

sub loadAieFromNas {
    my ($hostname, $user, $pass, $mnt, $root, $pattern, $localFolder) = @_;
    
    my $url = URI->new($hostname);
    if ($url->scheme() ne 'sftp') {
        syslog 'err', 'loadAieFromNas(): ftp is not supported: host %s', $hostname;
        return;
    }
    my $host = $url->host(); # . ($url->port() ? ":".$url->port() : "");
    my $sftp = Net::SFTP::Foreign->new(
        host     =>  $host,
        user     =>  $user,
        password =>  $pass,
        more => [-o => getProperty($sftpOptionsProperty, 'StrictHostKeyChecking no')]
    );
    
    if ($sftp->error) {
        syslog 'err', 'loadAieFromNas(): cannot conect to sftp nas %s: %s', $host, $sftp->error;
        $sftp->disconnect();
        return;
    }
    
    if (!$sftp->setcwd($mnt.$root, check => 0)){
        syslog 'err', 'loadAieFromNas(): cannot set remote dir to %s on nas %s: %s', $mnt.$root, $host, $sftp->error;
        $sftp->disconnect();
        return;
    }
    
    my $ts = time();
    my $tsExpiration = $ts - getProperty($aieExpirationSecondsProperty, $aieExpirationSecondsDefault);
    my $tsTooRecent = $ts - getProperty($aieTooRecentSecondsProperty, $aieTooRecentSecondsDefault);
    my $freshMT = 0;
    my $freshFile;
    $sftp->glob($pattern, ordered => 1, wanted=>sub {
        my $fn = $_[1]->{filename};
        my $mt = $_[1]->{a}->mtime;
        if ($mt > $freshMT && $mt > $tsExpiration && $mt < $tsTooRecent) {
            $freshMT = $mt;
            $freshFile = $fn;
        }
    });
    
    if (!$freshFile) {
        syslog 'err', 'loadAieFromNas(): No files found using pattern %s on %s nas: %s', $pattern, $host, $sftp->error;
        $sftp->disconnect();
        return;
    }

    my $localFile = $localFolder.basename($freshFile);
    if ( -s $localFile) {
        syslog 'debug', 'loadAieFromNas(): local file %s exists. Do not download it again from %s', $localFile, $host;
        $sftp->disconnect();
        return $localFile;
    }
    
    $sftp->get($freshFile, $localFile);
    if ($sftp->error) {
        syslog 'err', 'loadAieFromNas(): error while %s file download from %s: %s ', $freshFile, $host, $sftp->error;
        $sftp->disconnect();
        return;
    }
    
    $sftp->disconnect();
    syslog 'debug', 'loadAieFromNas(): local file %s has been downloaded from %s', $localFile, $host;
    return $localFile;
}
# Instead of full scan over XML, read it once and converto into 
# a hash with only needed fields to reduce memory consumption
sub convertAieXmlToHash {
    my ($aieXml) = @_;
    
    if (! defined($aieXml)) {
        return undef;
    }
    
    my $aieHash = {};
    my $userNameHash = {};
    
        foreach my $aieListNode ($aieXml->documentElement()->childNodes()) {
            my $nodeName = $aieListNode->nodeName;
            syslog 'debug', 'convertAieXmlToHash() nodeName %s', $nodeName;            
            if ($nodeName eq "OrganizationExport") {
                if (! defined($aieHash->{"OrganizationExport"})) {
                    $aieHash->{"OrganizationExport"} = {};
                }
                foreach my $aieOrganization ($aieListNode->childNodes()) {
                    if ($aieOrganization->nodeName() ne "Organization") {
                        next;
                    }
                    my $name = getXmlValue($aieOrganization, "Name");
                    if (defined($name)) {
                        $aieHash->{"OrganizationExport"}->{$name} = {};
                        $aieHash->{"OrganizationExport"}->{$name}->{"ParentOrg"} = getXmlValue($aieOrganization, "ParentOrg");
                    }
                }
            } elsif ($nodeName eq "SkillList") {
                if (! defined($aieHash->{"SkillList"})) {
                    $aieHash->{"SkillList"} = {};
                }
                foreach my $aieSkill ($aieListNode->childNodes()) {
                    if ($aieSkill->nodeName() ne "Skill") {
                        next;
                    }
                    my $name = getXmlValue($aieSkill, "Name");
                    if (defined($name)) {
                        $aieHash->{"SkillList"}->{$name} = {};
                        $aieHash->{"SkillList"}->{$name}->{"Description"} = getXmlValue($aieSkill, "Description");
                    }
                }
            } elsif ($nodeName eq "EmployeeList" && defined($aieListNode->getAttributeNode("OrganizationName"))) {
                my $orgTs = time();
                my $empCounter = 0;
                my $orgName = $aieListNode->getAttributeNode("OrganizationName")->nodeValue;

                # syslog 'debug', 'convertAieXmlToHash() EmployeeList OrganizationName %s', $orgName;
                
                foreach my $aieEmployee ($aieListNode->childNodes()) {
                    if ($aieEmployee->nodeName() ne "Employee") {
                        next;
                    }
                    $empCounter ++;
                    my $employeeNumber = getXmlValue($aieEmployee, "EmployeeNumber");
                    if (! defined($employeeNumber)) {
                        next;
                    }
                    $employeeNumber =~ /_(\w+?)$/;
                    my $verintCode = $1;

                    my $userName = getXmlValue($aieEmployee, "UserName");
                    if (defined($userName)) {
                        $userNameHash->{$verintCode}->{$userName} = $employeeNumber;
                    }

                    if (!defined($aieHash->{"EmployeeList"}->{$verintCode})) {
                       $aieHash->{"EmployeeList"}->{$verintCode} = {};
                    }
                    my $hashEmpl = $aieHash->{"EmployeeList"}->{$verintCode}; 
                    $hashEmpl->{$employeeNumber} = {};
                    $hashEmpl->{$employeeNumber}->{"OrganizationName"} = $orgName;
                    $hashEmpl->{$employeeNumber}->{"StartDate"} = getXmlValue($aieEmployee, "StartDate");
                    $hashEmpl->{$employeeNumber}->{"EndDate"} = getXmlValue($aieEmployee, "EndDate");
                    $hashEmpl->{$employeeNumber}->{"UserName"} = $userName;
                    $hashEmpl->{$employeeNumber}->{"IsSupervisor"} = getXmlValue($aieEmployee, "IsSupervisor");
                    my $aiePerson = findXmlNode($aieEmployee, "Person");
                    if (defined($aiePerson)) {
                        $hashEmpl->{$employeeNumber}->{"FirstName"} = getXmlValue($aiePerson, "FirstName");
                        $hashEmpl->{$employeeNumber}->{"LastName"} = getXmlValue($aiePerson, "LastName");
                        $hashEmpl->{$employeeNumber}->{"PersonContact"} = getEmailFromPerson($aiePerson);
                    }
                    foreach my $aieEmployeeNode ($aieEmployee->childNodes()) {
                        my $aieEmployeeNodeName = $aieEmployeeNode->nodeName();
                        if ($aieEmployeeNodeName eq "EmployeeDataSource" && $commandLine{"incremental"}) {
                            # optimization - EmployeeDataSource is used in incremental mode only
                            if (! defined($hashEmpl->{$employeeNumber}->{"EmployeeDataSource"} )) {
                                $hashEmpl->{$employeeNumber}->{"EmployeeDataSource"} = {};
                            }
                            my $employeeDataSourceName = getXmlValue($aieEmployeeNode, "DataSourceName");
                            if (defined($employeeDataSourceName)) {
                                $hashEmpl->{$employeeNumber}->{"EmployeeDataSource"}->{$employeeDataSourceName} = {};
                                $hashEmpl->{$employeeNumber}->{"EmployeeDataSource"}->{$employeeDataSourceName}->{"ExternalEmpIdent"}
                                   = getXmlValue($aieEmployeeNode, "ExternalEmpIdent");
                            }
                        } elsif ($aieEmployeeNodeName eq "EmployeeRole") {
                            foreach my $aieEmployeeRole ($aieEmployeeNode->childNodes()) {
                                if (! defined($hashEmpl->{$employeeNumber}->{"Role"})) {
                                    $hashEmpl->{$employeeNumber}->{"Role"} = {};
                                }
                                if ($aieEmployeeRole->nodeName() eq "Role") {
                                    my $RoleName = getXmlValue($aieEmployeeRole, "RoleName");
                                    if (defined($RoleName)) {
                                        $hashEmpl->{$employeeNumber}->{"Role"}->{$RoleName} = {};
                                        $hashEmpl->{$employeeNumber}->{"Role"}->{$RoleName}->{"Scope"} = getXmlValue($aieEmployeeRole, "Scope");
                                        $hashEmpl->{$employeeNumber}->{"Role"}->{$RoleName}->{"ScopeName"} = getXmlValue($aieEmployeeRole, "ScopeName");
                                        my $org = getXmlValue(findXmlNode($aieEmployeeRole, "ScopeType"), "organization");
                                        if ($org && $org eq "true") {
                                            $hashEmpl->{$employeeNumber}->{"Role"}->{$RoleName}->{"ScopeType"} = "organization";
                                        }
                                    }
                                }
                            }
                        } 
                    }
                }
                
                syslog 'debug', 'convertAieXmlToHash() EmployeeList OrganizationName %s, empCounter = %d, parsed in %s sec', $orgName, $empCounter, (time()-$orgTs);
                
            # } elsif ($aieListNode->nodeName eq "GroupList") { # not used
            } elsif ($nodeName eq "RoleList") {
                if (! defined($aieHash->{"RoleList"})) {
                    $aieHash->{"RoleList"} = {};
                }
                foreach my $aieRole ($aieListNode->childNodes()) {
                    if ($aieRole->nodeName() ne "Role") {
                        next;
                    }
                    my $name = getXmlValue($aieRole, "RoleName");
                    if (defined($name)) {
                        $aieHash->{"RoleList"}->{$name} = {};
                        $aieHash->{"RoleList"}->{$name}->{"Description"} = getXmlValue($aieRole, "Description");
                    }
                }
            # } elsif ($aieListNode->nodeName eq "PrivilegeList") { # not used
            } elsif ($nodeName eq "DataSourceList") {
                if (! defined($aieHash->{"DataSourceList"})) {
                    $aieHash->{"DataSourceList"} = {};
                }
                foreach my $aieDataSource ($aieListNode->childNodes()) {
                    if ($aieDataSource->nodeName() ne "DataSource") {
                        next;
                    }
                    my $name = getXmlValue($aieDataSource, "Name");
                    if (defined($name)) {
                        $aieHash->{"DataSourceList"}->{$name} = {};
                        $aieHash->{"DataSourceList"}->{$name}->{"Type"} = getXmlValue($aieDataSource, "Type"); 
                    }
                }
            }
        }    
    
    return ($aieHash, $userNameHash);
}

# generate part of XML to unassign roles for an employee 
sub deleteRoles {
    my ($dom, $EmployeeNumber, $firstName, $lastName, $userName, $employeeRolesDelete) = @_;
    my $Employee = $dom->createElement("Employee");
    $Employee
        ->appendChild($dom->createElement("FirstName"))
            ->appendText($firstName);
    $Employee
        ->appendChild($dom->createElement("LastName"))
           ->appendText($lastName);
    $Employee
        ->appendChild($dom->createElement("EmployeeNumber"))
            ->appendText($EmployeeNumber);
    my $AdditionalInfo = $Employee
        ->appendChild($dom->createElement("AdditionalInfo"));
    
    my $EmployeeRole = $AdditionalInfo
        ->appendChild($dom->createElement("EmployeeRole"));
    $EmployeeRole
        ->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("delete"));
    $EmployeeRole
        ->appendChild($dom->createElement("UserName"))
            ->appendText($userName);
    foreach my $EmployeeRoleRole (@{$employeeRolesDelete}) {
            $EmployeeRole
                ->appendChild($EmployeeRoleRole);
    }
    return $Employee;
}

#
# Generates unique login for deleted user
#
sub getDeletedUserName {
    my ($aieUserName, $EmployeeNumber) = @_;
    
    if (! defined($aieUserName)) {
        return undef;
    }
    
    if (index($aieUserName, "-" . $EmployeeNumber) > 0) {
        return $aieUserName;
    }
    my $deletedUserName = $aieUserName;
    my $l = length($deletedUserName . "-" . $EmployeeNumber);
    if ($l >= 50) {
        # trim extra chars
        $deletedUserName = substr($aieUserName, 0, length($aieUserName) - ($l - 50) - 1); 
    }
    $deletedUserName .= "-" . $EmployeeNumber;
    return $deletedUserName;
}

#
# Generates xml file for one tenant from db and uploads it to ASI Import folder
#
# The xml file may be full spanshot or incremental diff.
#
# Incremental diff is generated if
# - either full todays snapshot is already available in spool
# - or todays Agent Information Export xml file is available
# 
# In case both todays full snapshot and Agent Information Export xml are available,
# the most recent is used to generate diff. 
# 
sub syncTenantViaDB {
    my ($tenant) = @_;

    syslog 'debug', 'syncTenantViaDB() entering, account.num = %s, domainId = %d, domainName = %s, wfm_verint_code = %s',
           $tenant->{"num"}, $tenant->{"domainId"}, $tenant->{"domainName"}, $tenant->{"wfm_verint_code"};
    
    my $spool = getSpool($tenant);
    # check spool per domain first
    if (!defined($spool)) {
        # if it is common spool, organization is included
        syslog 'info', 'syncTenantViaDB() spool is empty, consider user sync is disabled for the domain, common spool is not supported account.num = %s, domainId = %d, domainName = %s, wfm_verint_code = %s',
           $tenant->{"num"}, $tenant->{"domainId"}, $tenant->{"domainName"}, $tenant->{"wfm_verint_code"};
        return;
    } elsif ($spool =~ /^\s*$/) {
        # if spool defined, but is empty, ignore organization completely
        syslog 'info', 'syncTenantViaDB() spool is defined but empty, consider user sync is disabled for the domain, account.num = %s, domainId = %d, domainName = %s, wfm_verint_code = %s',
           $tenant->{"num"}, $tenant->{"domainId"}, $tenant->{"domainName"}, $tenant->{"wfm_verint_code"};
        return;
    }
    
    my $withOrg = getProperty($spoolProperty.".".$tenant->{"num"}.".withorg");
    
    # check if recent Agent Information Export is available
    # to enable incremental mode  
    my $tsXml = time();
    my ($aieHash, $userNameHash) = parseAieXml($tenant);
    syslog 'debug', 'syncTenantViaDB() aieXml parsed in ' . (time() - $tsXml) . " sec";

    #print Dumper($aieHash)."\n\n\n";
    #print Dumper($userNameHash)."\n\n\n";
    

    my $renameDisabledLogins = getProperty($renameDisabledLoginsProperty, $renameDisabledLoginsDefault);

    my $dom = XML::LibXML::Document->new('1.0',"utf-8");
    my $root = $dom->createElement("root");
    $dom->setDocumentElement($root);

    # one more workaround against ASI limitations - unassign roles 
    # see US31527: Verint ASI XML File - Campaign Scope
    my %EmployeesDeleteRoles; 
    my $domDelete = XML::LibXML::Document->new('1.0',"utf-8");

    my %RoleInfos;
    my $isSyncRolesEnabled = getProperty($syncRolesProperty.".".$tenant->{"num"});
    if (!defined($isSyncRolesEnabled)) {
        $isSyncRolesEnabled = getProperty($syncRolesProperty, $syncRolesDefault);
    }
    if ($isSyncRolesEnabled) {
        my @roles = qw/Admin Supervisor Reporting/;
        if (!$tenant->{"VERINT_AGENT_LEVEL_PACKAGES"}) {
            push @roles, 'Agent';
        }
        for my $role (@roles) {
            my $roleName = $role."_".$tenant->{"wfm_verint_code"};
            my $RoleInfo = $dom->createElement("RoleInfo");
            $RoleInfo
                ->appendChild($dom->createElement("Name"))
                    ->appendText($roleName);
            $RoleInfos{$roleName} = $RoleInfo;
        }
    }
    
    my $dataSourceName = "DS_".$tenant->{"wfm_verint_code"};
    
    my %DataSources;
    
    my $DataSource = $dom->createElement("DataSource");
    
    $DataSource 
        ->appendChild($dom->createElement("Name"))
            ->appendText($dataSourceName);
    $DataSource
        ->appendChild($dom->createElement("TimeZoneName"))
            ->appendText("America/Los_Angeles");
    $DataSource
        ->appendChild($dom->createElement("isActive"))
            ->appendText("true");
    $DataSources{$dataSourceName} = $DataSource; 

    my %Organizations;
    
    my $organizationName = verintQuote($tenant->{"tenantName"}, 0, 43)
            . "_" . $tenant->{"wfm_verint_code"};
    
    my $Organization = $dom->createElement("Organization");
    
    my $timeZoneName = trim(getProperty($timeZoneNameProperty.".".$tenant->{"num"}, undef));
    if (! defined($timeZoneName)) {
        $timeZoneName = trim(getProperty($timeZoneNameProperty, $timeZoneNameDefault));
    }
    my $weekStartDay = getWeekStartDay($tenant);  
    
    my $dayBoundaryOffset = trim(getProperty($dayBoundaryOffsetProperty.".".$tenant->{"num"}, undef));
    if (! defined($dayBoundaryOffset)) {
        $dayBoundaryOffset = trim(getProperty($dayBoundaryOffsetProperty, $dayBoundaryOffsetDefault));
    }
    
    $Organization
        ->appendChild($dom->createElement("Name"))
            ->appendText($organizationName);
    $Organization
        ->appendChild($dom->createElement("TimeZoneName"))
            ->appendText($timeZoneName);
    if (defined($weekStartDay)) {
        $Organization
            ->appendChild($dom->createElement("WeekStartDay"))
                ->appendText($weekStartDay);
    }
    if (defined($dayBoundaryOffset)) {
        $Organization
            ->appendChild($dom->createElement("DayBoundaryOffset"))
                ->appendText($dayBoundaryOffset);
    }
    $Organization
        ->appendChild($dom->createElement("NumberOfSeats"))
            ->appendText($tenant->{"num_agents"});
    
    $Organizations{$organizationName} = $Organization;
    
    # skills and users from database
    my $conn = getCustomerDBConnection($tenant, 0);
    my $connRW;
    my $sth;
    my $href;
    my $isSkillCacheEnabled = isSkillCacheOn($tenant->{"num"});
    if ($isSkillCacheEnabled) {
       syslog 'debug', 'syncTenantViaDB() $isSkillCacheEnabled=1 for domainId='.$tenant->{"domainId"};
       $connRW = getCustomerDBConnection($tenant, 1);
       initAgentSkillTable($connRW);
    }

    # screen recording datasource name is defined only if table verint_data_source_ident exists in db
    my $isVerintDataSourceIdentTableExists = isTableExist($conn, 'verint_data_source_ident');
    my $screenRecordingDataSourceName = $isVerintDataSourceIdentTableExists? $dataSourceName."_".$SCREEN_RECORDING_CODE: undef;
     
    my $screenRecordingDataSourceExistsInAie = 0;
    if ($isVerintDataSourceIdentTableExists && defined($aieHash)) {
        # screen recording datasource shall be entered manually, so check if 
        # it is already exists and send notification if needs to be created
        if (defined($aieHash) && defined($aieHash->{"DataSourceList"}) && defined($aieHash->{"DataSourceList"}->{$screenRecordingDataSourceName})) {
            $screenRecordingDataSourceExistsInAie = 1; # found
            syslog 'info', 'syncTenantViaDB() Screen Recording DataSource %s for organization %s exists in AIE', $screenRecordingDataSourceName, $organizationName;
        } else {
            if ($tenant->{"QM_VERINT_SCREEN_RECORDING"}) {
                syslog 'warning', 'syncTenantViaDB() Screen Recording DataSource %s for organization %s not found in AIE, need to be created manually', $screenRecordingDataSourceName, $organizationName;
                if (defined($aieMissedScreenRecordingDataSources{$organizationName})) {
                    $aieMissedScreenRecordingDataSources{$organizationName} .= " , " . $screenRecordingDataSourceName;
                } else {
                    $aieMissedScreenRecordingDataSources{$organizationName} = $screenRecordingDataSourceName;
                }
            } else {
                syslog 'info', 'syncTenantViaDB() Screen Recording DataSource %s for organization %s not found in AIE', $screenRecordingDataSourceName, $organizationName;
            }
        } 
    }

    my $ftciAdherenceDataSourceName; 
    if (defined($aieHash) && $tenant->{"WFM_VERINT"}) {
        $ftciAdherenceDataSourceName = $dataSourceName."_FTCI_ADHERENCE";
        if (defined($aieHash) && defined($aieHash->{"DataSourceList"}) && defined($aieHash->{"DataSourceList"}->{$ftciAdherenceDataSourceName})) {
            syslog 'info', 'syncTenantViaDB() DataSource %s for organization %s exists in AIE', $ftciAdherenceDataSourceName, $organizationName;
        } else {
            syslog 'warning', 'syncTenantViaDB() DataSource %s for organization %s not found in AIE, need to be created manually', $ftciAdherenceDataSourceName, $organizationName;
            if (defined($aieMissedScreenRecordingDataSources{$organizationName})) {
                $aieMissedScreenRecordingDataSources{$organizationName} .= " , " . $ftciAdherenceDataSourceName;
            } else {
                $aieMissedScreenRecordingDataSources{$organizationName} = $ftciAdherenceDataSourceName;
            }
            $ftciAdherenceDataSourceName = undef; # undef to not generate
        }
    }
    
    my $textDataSourceName; 
    if (defined($aieHash) && $tenant->{"TEXT_REC"}) {
        $textDataSourceName = $dataSourceName."_TEXT";
        if (defined($aieHash) && defined($aieHash->{"DataSourceList"}) && defined($aieHash->{"DataSourceList"}->{$textDataSourceName})) {
            syslog 'info', 'syncTenantViaDB() DataSource %s for organization %s exists in AIE', $textDataSourceName, $organizationName;
        } else {
            syslog 'warning', 'syncTenantViaDB() DataSource %s for organization %s not found in AIE, need to be created manually', $textDataSourceName, $organizationName;
            if (defined($aieMissedScreenRecordingDataSources{$organizationName})) {
                $aieMissedScreenRecordingDataSources{$organizationName} .= " , " . $textDataSourceName;
            } else {
                $aieMissedScreenRecordingDataSources{$organizationName} = $textDataSourceName;
            }
            $textDataSourceName = undef; # undef to not generate
        }
    }
    
    # skills
    my %Skills; # skillName is key
     
    $sth = $conn->prepare(
        "select 
             user_group.name, 
             ifnull(user_group.description,'') as description 
         from user_group
           inner join domain_user_groups on domain_user_groups.user_group_fk=user_group.id
         where domain_user_groups.domain_fk=?"
    );
    $sth->execute($tenant->{"domainId"});
    while (defined($href = $sth->fetchrow_hashref()) ) {
        my $skillName = verintQuote($href->{"name"}."_".$tenant->{"wfm_verint_code"}, 0, 49);
        my $Skill = $dom->createElement("Skill");
        $Skill
            ->appendChild($dom->createElement("Name"))
                ->appendText($skillName);
        $Skill
            ->appendChild($dom->createElement("Description"))
                ->appendText($href->{"description"});
                
        $Skills{$skillName} = $Skill;
    }
    $sth->finish();

    my $ts = time();
    my $formatedCurrentTime = getFormatedTime($ts);
    my $recentlyCreatedEmployeeTs = $ts - getProperty($aieExpirationSecondsProperty, $aieExpirationSecondsDefault);

    # users from database
    my %Employees; # EmployeeNumber is the key
    
    # a helper hash to keep all inactive Employees
    # in case AIE XML exists but not incomplete,
    # do not touch EndDate for inactive employees.
    # as a side effect, newly created inactive employees may be never synced,
    # but in production risk of overcharging in case reseting EndDate for all inactive
    # overweight risk of few employees are not disabled on time   
    my %inactiveEmployees;  
    
    my $cmdIdent = "";
    if ($isVerintDataSourceIdentTableExists) {
        # optimization - instead individual query per user, fetch all at once
        $cmdIdent = ",
            (SELECT ident.external_emp_ident FROM verint_data_source_ident ident WHERE u.id=ident.user_fk AND ident.data_source_code=?) as screen_recording_ident,
            u.flags3 & (1<<(?-128)) as VERINT_ENABLE_SCREEN_RECORDING,
            user_profile.flags as user_profile_flags
        ";
    }
    
    my $cmdAgentPackages = "";
    my %tenantFeatures;
    my $whereSendToVerint = "";
    if ($tenant->{"VERINT_AGENT_LEVEL_PACKAGES"}) {
        # select packages enabled on account level if applicable
        %tenantFeatures = %{$tenant->{"tenantFeatures"}}; 
        syslog 'debug', 'syncTenantViaDB() tenantFeatures ' . join(', ', keys(%tenantFeatures));

        # there are hardcoded constants and it is really overcomplicated 
        $cmdAgentPackages = ",
            if(u.user_profile_fk is null, 
                (select count(verint_package_user.verint_package_fk) 
                   from verint_package_user 
                     where verint_package_user.user_fk = u.id 
                       and verint_package_user.verint_package_fk = " . $CALL_RECORDING . "),
                (select count(verint_package_user_profile.verint_package_fk) 
                   from verint_package_user_profile 
                     where verint_package_user_profile.user_profile_fk = u.user_profile_fk 
                       and verint_package_user_profile.verint_package_fk = " . $CALL_RECORDING . ")
              ) as AGENT_LEVEL_CALL_RECORDING,
            if(u.user_profile_fk is null, 
              (select count(verint_package_user.verint_package_fk) 
                 from verint_package_user 
                   where verint_package_user.user_fk = u.id 
                     and verint_package_user.verint_package_fk = " . $SCREEN_RECORDING . "),
              (select count(verint_package_user_profile.verint_package_fk) 
                 from verint_package_user_profile 
                   where verint_package_user_profile.user_profile_fk = u.user_profile_fk 
                     and verint_package_user_profile.verint_package_fk = " . $SCREEN_RECORDING . ")
            ) as AGENT_LEVEL_SCREEN_RECORDING
        ";
        $whereSendToVerint = " and if(u.user_profile_fk is null, u.flags3 & (1<<($SEND_TO_VERINT-128)), substring(user_profile.flags, $SEND_TO_VERINT+1, 1)) > 0"; 
    }
    
    my $cmdUserCust = "select 
            u.id, u.name as name, 
            u.first_name as first_name, 
            u.last_name as last_name,
            u.full_name as full_name, 
            u.domain_fk,
            u.email,
            u.flags & (1<<15) as IS_ACTIVE_FLAG,
            (select min(pwd_set_time) from pwd_history where pwd_history.user_fk=u.id) as min_pwd_set_time, 
            user_profile.id as user_profile_id,
            if(from_unixtime(start_date div 1000) <= now(), convert_tz(from_unixtime(start_date div 1000), 'System', 'GMT'), null) as start_date
            $cmdIdent
            $cmdAgentPackages
        from user_cust u 
          left join user_profile on user_profile.id=u.user_profile_fk
        where u.domain_fk=? $whereSendToVerint";
    $sth = $conn->prepare($cmdUserCust);
    if ($isVerintDataSourceIdentTableExists) {
        $sth->execute($SCREEN_RECORDING_CODE, $VERINT_ENABLE_SCREEN_RECORDING, $tenant->{"domainId"});
    } else {
        $sth->execute($tenant->{"domainId"});
    }
    while (defined($href = $sth->fetchrow_hashref()) ) {
        if (defined($commandLine{"user"}) && $href->{"id"} != $commandLine{"user"} ) {
            next;
        }
        my $Employee = $dom->createElement("Employee");
        my $EmployeeNumber = $href->{"id"};
        
        $EmployeeNumber .= "_" . $tenant->{"wfm_verint_code"};

        # EmployeeNumber is limited no more than 20 chars max, 
        # while ExternalEmpIdent can be up to 128 chars,
        # so a bit complex transformations:
        # 1. cut out extra zeroes if exists, i.e. 3000000000000052442_060771 => 3-0000052442_060771
        # 2. if zeroes do not exist, cut digits from the beggining i.e. 12345678901234567890_123456 => -901234567890_123456  
        
        if (length($EmployeeNumber) > 20) {
            $EmployeeNumber =~ s/00000000/-/;
        }
        if (length($EmployeeNumber) > 20) {
            $EmployeeNumber = "-" . substr($EmployeeNumber, -19);
        }
        
        if ( ( ! $href->{"IS_ACTIVE_FLAG"} ) && ( ! defined($aieHash) ) ) {
            syslog 'debug', 'syncTenantViaDB() %s %s is inactive and AIE xml is not available, skip.', $EmployeeNumber, $href->{"name"};
            next;
        }
        
        my $ExternalEmpIdent = "";
        
        if ($tenant->{"VERINT_AGENT_LEVEL_PACKAGES"} == 0 
           || ($tenantFeatures{$VerintPackages2TenantFeatures{$CALL_RECORDING}} && $href->{"AGENT_LEVEL_CALL_RECORDING"} ))  
        {
            $ExternalEmpIdent = $href->{"id"} . "_" . $tenant->{"wfm_verint_code"};
        }
        my $firstName = verintQuote($href->{"first_name"}, 1, 50);
        my $lastName = verintQuote($href->{"last_name"}, 1, 50);
        
        $Employee
            ->appendChild($dom->createElement("FirstName"))
                ->appendText($firstName);
        $Employee
            ->appendChild($dom->createElement("LastName"))
               ->appendText($lastName);
        $Employee
            ->appendChild($dom->createElement("EmployeeNumber"))
                ->appendText($EmployeeNumber);
        if (defined($href->{"start_date"})) {
            $Employee
                ->appendChild($dom->createElement("StartDate"))
                    ->appendText($href->{"start_date"});
        }
        if ($href->{"IS_ACTIVE_FLAG"}) {
            $Employee
                ->appendChild($dom->createElement("EndDate"));
        } else {
            # do not touch EndDate for inactive employees if AIE not available
            $Employee
                ->appendChild($dom->createElement("EndDate"))
                    ->appendText($formatedCurrentTime);
            # see aieXml part below with enddate properly updated, i.e. only once
            # special case for recently created users. Set EndDate even if AIE not available
            if ( (! defined($href->{"min_pwd_set_time"})) 
                    || ($href->{"min_pwd_set_time"} < $recentlyCreatedEmployeeTs*1000) ) {
                $inactiveEmployees{$EmployeeNumber} = $href->{"name"};
            }
        }
        my $AdditionalInfo = $Employee
            ->appendChild($dom->createElement("AdditionalInfo"));
        
        # legacy mode with one EmployeeDataSource
        my $EmployeeDataSourceCallRecording = $AdditionalInfo
            ->appendChild($dom->createElement("EmployeeDataSourceList"));
        $EmployeeDataSourceCallRecording->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("snapshot"));
        $EmployeeDataSourceCallRecording
            ->appendChild($dom->createElement("DataSourceName"))
                ->appendText($dataSourceName);
        $EmployeeDataSourceCallRecording
            ->appendChild($dom->createElement("EmployeeDataSourceInfo"))
                ->appendChild($dom->createElement("ExternalEmpIdent"))
                    ->appendText($ExternalEmpIdent);

        if ($isVerintDataSourceIdentTableExists) {
            # check for screen recording
            my $screenRecordingIdent = undef;
            my $screenRecordingEnabled = 0;

            $screenRecordingIdent = $href->{"screen_recording_ident"}; 
            if ($tenant->{"VERINT_AGENT_LEVEL_PACKAGES"}) {
                $screenRecordingEnabled = $href->{"AGENT_LEVEL_SCREEN_RECORDING"};
            } else {
                if(defined($href->{"user_profile_flags"})) {
                    # controlled via user-profile
                    $screenRecordingEnabled = substr($href->{"user_profile_flags"}, $VERINT_ENABLE_SCREEN_RECORDING, 1);
                } else {
                    # per-user
                    $screenRecordingEnabled = $href->{"VERINT_ENABLE_SCREEN_RECORDING"};
                }
            }
            if ($tenant->{"QM_VERINT_SCREEN_RECORDING"}) {
                # screen recording is enabled per tenant
                if ($screenRecordingEnabled) {
                    if ( ! defined($screenRecordingIdent) || $screenRecordingIdent eq "") {
                        syslog 'debug', 'syncTenantViaDB() screenRecording is enabled for userId = %d but verint_data_source_ident is not entered', $href->{"id"};
                        $screenRecordingIdent = "";
                    } else {
                        syslog 'debug', 'syncTenantViaDB() screenRecording is enabled for userId = %d , ident = %s', $href->{"id"}, $screenRecordingIdent;
                    }
                } else {
                    # syslog 'debug', 'syncTenantViaDB() screenRecording is disabled for userId = %d ', $href->{"id"};
                    $screenRecordingIdent = "";
                }                
            } else {
                # screen recording is disabled per tenant, try to do a cleanup
                if (defined($screenRecordingIdent)) {
                    # screen recording is explicitly deleted if there is a row in verint_data_source_ident 
                    # but whole screen recording is disabled tenant-wide
                    syslog 'debug', 'syncTenantViaDB() screenRecording is disabled tenant-wide for userId = %d but verint_data_source_ident is entered', $href->{"id"};
                    $screenRecordingIdent = "";
                } elsif ($screenRecordingDataSourceExistsInAie ) {
                    # DE24276: Verint: User can be Screen recorded if it is disabled for account
                    # reset manually entered values but make it via "safeway" - only if screen recording datasource exists,
                    # to refrain from case with some domains do not need screen recording datasource at all
                    $screenRecordingIdent = "";
                }
            }
            if (defined($screenRecordingIdent)) {
                my $EmployeeDataSourceList = $AdditionalInfo
                    ->appendChild($dom->createElement("EmployeeDataSourceList"));
                $EmployeeDataSourceList->appendChild($dom->createElement("SyncMethod"))
                    ->appendChild($dom->createElement("snapshot"));
                $EmployeeDataSourceList
                    ->appendChild($dom->createElement("DataSourceName"))
                        ->appendText($screenRecordingDataSourceName);
                $EmployeeDataSourceList
                    ->appendChild($dom->createElement("EmployeeDataSourceInfo"))
                        ->appendChild($dom->createElement("ExternalEmpIdent"))
                            ->appendText($screenRecordingIdent);
            }
        }
        
        # DS_<verint code>_FTCI_ADHERENCE
        if (defined($ftciAdherenceDataSourceName)) {
            my $ExternalEmpIdentFTCI = $href->{"id"} . "_" . $tenant->{"wfm_verint_code"};
            my $EmployeeDataSourceListFTCI = $AdditionalInfo
                ->appendChild($dom->createElement("EmployeeDataSourceList"));
            $EmployeeDataSourceListFTCI->appendChild($dom->createElement("SyncMethod"))
                ->appendChild($dom->createElement("snapshot"));
            $EmployeeDataSourceListFTCI
                ->appendChild($dom->createElement("DataSourceName"))
                    ->appendText($ftciAdherenceDataSourceName);
            $EmployeeDataSourceListFTCI
                ->appendChild($dom->createElement("EmployeeDataSourceInfo"))
                    ->appendChild($dom->createElement("ExternalEmpIdent"))
                        ->appendText($ExternalEmpIdentFTCI);
        }

        # DS_<verint code>_TEXT
        if (defined($textDataSourceName)) {
            my $ExternalEmpIdentText = $href->{"id"} . "_" . $tenant->{"wfm_verint_code"};
            my $EmployeeDataSourceListTEXT = $AdditionalInfo
                ->appendChild($dom->createElement("EmployeeDataSourceList"));
            $EmployeeDataSourceListTEXT->appendChild($dom->createElement("SyncMethod"))
                ->appendChild($dom->createElement("snapshot"));
            $EmployeeDataSourceListTEXT
                ->appendChild($dom->createElement("DataSourceName"))
                    ->appendText($textDataSourceName);
            $EmployeeDataSourceListTEXT
                ->appendChild($dom->createElement("EmployeeDataSourceInfo"))
                    ->appendChild($dom->createElement("ExternalEmpIdent"))
                        ->appendText($ExternalEmpIdentText);
        }
        
        # do not sync really long emails
        if (defined($href->{"email"}) && length($href->{"email"}) < 100 ) { 
            my $Person = $AdditionalInfo
                ->appendChild($dom->createElement("Person"));
            my $PersonContact = $Person
                ->appendChild($dom->createElement("PersonContact"));
            $PersonContact
                ->appendChild($dom->createElement("Value"))
                   ->appendText($href->{"email"});
            $PersonContact
                 ->appendChild($dom->createElement("ContactType"))
                    ->appendChild($dom->createElement("email"));
        }
        
        for my $cmd (
                # two independent queries instead of union :)
                "select 
                    user_group.name as Skill, 
                    ifnull(user_skill_level.level, 1) as level 
                 from user_cust
                    inner join user_group_users on user_group_users.user_fk=user_cust.id 
                    inner join user_group on user_group_users.user_group_fk=user_group.id
                    left join user_skill_level on user_skill_level.user_fk=user_cust.id and user_skill_level.user_group_fk=user_group.id
                 where user_cust.id=? 
                   and user_cust.user_profile_fk is null /* users without profile */",
                "select 
                    user_group.name as Skill,
                    ifnull(user_skill_level.level, 1) as level
                 from user_cust
                    inner join user_profile on user_profile.id=user_cust.user_profile_fk
                    inner join user_profile_user_groups on user_profile_user_groups.user_profile_fk=user_profile.id
                    inner join user_group on user_group.id=user_profile_user_groups.user_group_fk
                    left join user_skill_level on user_skill_level.user_fk=user_cust.id and user_skill_level.user_group_fk=user_group.id
                where user_cust.id=? 
                  and user_cust.user_profile_fk is not null /* users with profile */"
        ) {
            my $sth1 = $conn->prepare($cmd);
            $sth1->execute($href->{"id"});
            my $skillref;
            while (defined($skillref = $sth1->fetchrow_hashref()) ) {
                my $startTime = $isSkillCacheEnabled ? 
                    compareSkills($EmployeeNumber, $skillref->{"Skill"}, $skillref->{"level"}, $formatedCurrentTime): undef;
                addToSkillAssignment($dom, $AdditionalInfo, $tenant->{"wfm_verint_code"}, $skillref->{"Skill"}, $skillref->{"level"}, $startTime);
            }
            $sth1->finish();
        }
        
        if ($isSkillCacheEnabled) {
            #add deleted skills to the SkillAssignment with EndDate equal current time
            for my $skill ( keys %{ $agentSkillHash{$EmployeeNumber} } ) {
                for my $level ( keys %{$agentSkillHash{$EmployeeNumber}{$skill}} ) {
                    my $startTime = $agentSkillHash{$EmployeeNumber}{$skill}{$level};
                    if ($startTime) {
                        addToSkillAssignment($dom, $AdditionalInfo, $tenant->{"wfm_verint_code"}, $skill, $level, $startTime, $formatedCurrentTime);
                    }
                }
            }
     
        }
        
        #my $EmployeeDataSource = $AdditionalInfo 
        #    ->appendChild($dom->createElement("EmployeeDataSource"));
        #$EmployeeDataSource
        #    ->appendChild($dom->createElement("DataSourceName"))
        #        ->appendText("DS_".$tenant->{"wfm_verint_code"});
        
        if (1) { # to preserve formatting
            my %employeeRoles;
            
            my $isAgent = 0;
            my $isSupervisor = 0; 
            
            my $sth1;
            if (defined($href->{"user_profile_id"})) {
                my $cmd = "select role_fk from user_profile_roles where user_profile_fk=?"; 
                $sth1 = $conn->prepare($cmd);
                $sth1->execute($href->{"user_profile_id"});
            } else {
                my $cmd = "select role_fk from user_roles where user_fk=?";
                $sth1 = $conn->prepare($cmd);
                $sth1->execute($href->{"id"});
            }
            my $role_fk_ref;
            while (defined($role_fk_ref = $sth1->fetchrow_hashref()) ) {
                my $roleName = $five9AppRole{$role_fk_ref->{"role_fk"}};
                my $employeeRoleRoleName;
                if ($roleName eq "DomainAdmin") {
                    $employeeRoleRoleName = "Admin"."_".$tenant->{"wfm_verint_code"};
                } elsif ($roleName eq "DomainSupervisor") {
                    $isSupervisor = 1;
                    $employeeRoleRoleName = "Supervisor"."_".$tenant->{"wfm_verint_code"}; 
                } elsif ($roleName eq "Agent") {
                    $isAgent = 1;
                    if (! $tenant->{"VERINT_AGENT_LEVEL_PACKAGES"} ) {
                        $employeeRoleRoleName = "Agent"."_".$tenant->{"wfm_verint_code"};
                    }
                } elsif ($roleName eq "Reporting") {
                    $employeeRoleRoleName = "Reporting"."_".$tenant->{"wfm_verint_code"};
                }
                if (defined($employeeRoleRoleName)) {
                    my $EmployeeRoleRole = $dom->createElement("Role");
                        
                    $EmployeeRoleRole->appendChild($dom->createElement("Name"))
                        ->appendText($employeeRoleRoleName);
                    my $Scope = $EmployeeRoleRole
                        ->appendChild($dom->createElement("Scope"));
                    $Scope->setAttribute("UserCurrentOrg", "true");
                    $Scope
                        ->appendChild($dom->createElement("Organization"))
                            ->appendText("");
                    $employeeRoles{$employeeRoleRoleName} = $EmployeeRoleRole;
                }
            }
            $sth1->finish();
            
            if ($isSyncRolesEnabled && $tenant->{"VERINT_AGENT_LEVEL_PACKAGES"} && $isAgent) {
                if (defined($href->{"user_profile_id"})) {
                    my $cmd = "select verint_package_fk from verint_package_user_profile where user_profile_fk=?"; 
                    $sth1 = $conn->prepare($cmd);
                    $sth1->execute($href->{"user_profile_id"});
                } else {
                    my $cmd = "select verint_package_fk from verint_package_user where user_fk=?";
                    $sth1 = $conn->prepare($cmd);
                    $sth1->execute($href->{"id"});
                }
                my $verint_package_fk_ref;
                while (defined($verint_package_fk_ref = $sth1->fetchrow_hashref()) ) {
                    my $package_fk = $verint_package_fk_ref->{"verint_package_fk"};
                    if (! defined($tenantFeatures{$VerintPackages2TenantFeatures{$package_fk}}) ) {
                        # skip permission if it is not allowed on account level
                        next;
                    }
                    my $employeeRoleRoleName = $VerintPackages{$package_fk};
                    # syslog 'debug', 'verint_package_fk=' . $verint_package_fk_ref->{"verint_package_fk"};
                    # syslog 'debug', 'employeeRoleRoleName='.$employeeRoleRoleName;
                    if (defined($employeeRoleRoleName)) {
                        $employeeRoleRoleName = $employeeRoleRoleName."_".$tenant->{"wfm_verint_code"};
                        my $EmployeeRoleRole = $dom->createElement("Role");
                            
                        $EmployeeRoleRole->appendChild($dom->createElement("Name"))
                            ->appendText($employeeRoleRoleName);
                        my $Scope = $EmployeeRoleRole
                            ->appendChild($dom->createElement("Scope"));
                        $Scope->setAttribute("UserCurrentOrg", "true");
                        $Scope
                            ->appendChild($dom->createElement("Organization"))
                                ->appendText("");
                        $employeeRoles{$employeeRoleRoleName} = $EmployeeRoleRole;
                    }
                }
                $sth1->finish();
            }
            
            $Employee
                ->appendChild($dom->createElement("IsSupervisor"))
                    ->appendText($isSupervisor?"true":"false");
            
            my $userName = $href->{"name"};
            # $userName = verintQuote($href->{"name"}, 1, 50);
            $userName =~ s/\&/ and /g;
            $userName =~ s/[\[\]\<\>\"\&\!\?\,]//g;
            $userName = substr($userName, 0, 50);
            
            syslog 'debug', $EmployeeNumber . " " . $userName . " role(s): " . join(" ", keys(%employeeRoles));
            
            # instead of snapshot, roles are are added via snapshot_without_delete
            # to preserve roles entered manually. Unfortunately, 
            # EmployeeRole can be entered only once, so writing additional xml
            # file to delete only roles unassigned via VCC admin 
            # <Role>
            #   <RoleName>Admin_AK0013</RoleName>
            #   <ScopeID>4</ScopeID>
            #   <Scope>2</Scope>
            #   <ScopeName>ABC_Support</ScopeName>
            #   <ScopeType>
            #     <organization>true</organization>
            #    </ScopeType>
            #  </Role>
            
            if ($isSyncRolesEnabled && defined($aieHash)) {
                my @employeeRolesDelete;
                # instead of full scan for every employee, scan once and use in-memory hash
                my $aieEmployeeHash = $aieHash->{"EmployeeList"}->{$tenant->{"wfm_verint_code"}}->{$EmployeeNumber};
                if (defined($aieEmployeeHash) && defined($aieEmployeeHash->{"Role"})) {
                    syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s found in AIE, checking roles snapshot', $EmployeeNumber;
                    my @possibleRoles = keys(%RoleInfos);
                    if ($tenant->{"VERINT_AGENT_LEVEL_PACKAGES"}) {
                        foreach my $packageRole (values(%VerintPackages)) {
                            push @possibleRoles, $packageRole."_".$tenant->{"wfm_verint_code"};
                        }
                        #add Agent role to form delete role file for transition case from usual mode to package one 
                        push @possibleRoles, "Agent_".$tenant->{"wfm_verint_code"};
                    } 

                    
                    foreach my $role (@possibleRoles) {
                        if (!defined($employeeRoles{$role}) && defined($aieEmployeeHash->{"Role"}->{$role})) {
                            # at this point we need to delete role
                            syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s roles snapshot RoleName %s will be deleted', $EmployeeNumber, $role;
                            my $EmployeeRoleRole = $dom->createElement("Role");
                                
                            $EmployeeRoleRole->appendChild($dom->createElement("Name"))
                                ->appendText($role);
                            my $Scope = $EmployeeRoleRole
                                ->appendChild($dom->createElement("Scope"));
                            $Scope->setAttribute("UserCurrentOrg", "false");
                            $Scope
                                ->appendChild($dom->createElement("Organization"))
                                    ->appendText(""); # $organizationName
                            push @employeeRolesDelete, $EmployeeRoleRole;
                            
                            # ASI XML can't delete more than one role at once (is it a bug or feature?)
                            # so deleting only one per employee
                            last;
                        }
                    }
                } else {
                    syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s roles snapshot not found in AIE', $EmployeeNumber;
                }
                if (scalar(@employeeRolesDelete) > 0) {
                    $EmployeesDeleteRoles{$EmployeeNumber} = deleteRoles($domDelete, $EmployeeNumber, $firstName, $lastName, $userName, \@employeeRolesDelete);
                }
            }
            
            # Workaround for employees had previously terminated and re-created with the same name and usernames
            # in two steps, since it is not possible to do in single XML
            #  - add uniq id to old login
            #  - only after old login renamed, add new roles
            my $empNumFromNameHash = $userNameHash->{$tenant->{"wfm_verint_code"}}->{$userName};
            if (defined($empNumFromNameHash) && ($empNumFromNameHash ne $EmployeeNumber)) {
                syslog 'debug', 'syncTenantViaDB() EmployeeID %s UserName %s is already in use by %s , organization %s, roles are not added',
                    $EmployeeNumber, $userName, $empNumFromNameHash, $organizationName;
                if (! $renameDisabledLogins) {
                    if (! defined($tenant->{"warning"})) {
                        $tenant->{"warning"} = sprintf("%s (domain.id=%d, num=%s, verint_code=%s) \r\n", 
                                            $tenant->{"domainName"}, $tenant->{"domainId"}, $tenant->{"num"}, $tenant->{"wfm_verint_code"});
                    }
                    $tenant->{"warning"} .= sprintf('  EmployeeID %s UserName %s is already in use by %s , organization %s, roles are not added', 
                        $EmployeeNumber, $userName, $empNumFromNameHash, $organizationName) . "\r\n";
                }
            } elsif ($isSyncRolesEnabled && (scalar(keys(%employeeRoles)) > 0)) {
                my $EmployeeRole = $AdditionalInfo
                    ->appendChild($dom->createElement("EmployeeRole"));
                $EmployeeRole
                    ->appendChild($dom->createElement("SyncMethod"))
                        ->appendChild($dom->createElement("snapshot_without_delete"));
                $EmployeeRole
                    ->appendChild($dom->createElement("UserName"))
                        ->appendText($userName); 
                foreach my $EmployeeRoleRole (values(%employeeRoles)) {
                        $EmployeeRole
                            ->appendChild($EmployeeRoleRole);
                }
            }
        }
        
        $Employees{$EmployeeNumber} = $Employee;
        
    }
    $sth->finish();
    
    if ($isSkillCacheEnabled) {
        cleanAgentSkillHash();
    }
    
    # include number of errors
    if (isTableExist($conn, 'verint_spool')) {
        my $spoolTableCount = 0;
        $sth = $conn->prepare("SELECT count(id) as c FROM verint_spool");
        $sth->execute();
        if (defined($href = $sth->fetchrow_hashref())) {
            $spoolTableCount = $href->{"c"};
        }
        $sth->finish();
        if ($spoolTableCount > 0) {
            if (! defined($tenant->{"warning"})) {
                $tenant->{"warning"} = sprintf("%s (domain.id=%d, num=%s, verint_code=%s) \r\n", $tenant->{"domainName"}, $tenant->{"domainId"}, $tenant->{"num"}, $tenant->{"wfm_verint_code"});
            } else {
                $tenant->{"warning"} .= "\r\n";
            }

            $tenant->{"warning"} .= sprintf( "  Files with warnings or errors %s : \r\n", ($spoolTableCount > 10? "( " . $spoolTableCount . " total )": ""));

            
            $sth = $conn->prepare("SELECT host,file,error FROM verint_spool order by error_ts desc limit 10");
            $sth->execute();
            while (defined($href = $sth->fetchrow_hashref())) {
                $tenant->{"warning"} .= sprintf("    %s:%s (%s)\r\n", $href->{"host"}, $href->{"file"}, $href->{"error"});            
            }
            $sth->finish();
        }
    }
    
    $conn->disconnect();

    if ($isSkillCacheEnabled) {
        updateAgentSkillTable($connRW);
        $connRW->disconnect();
    }

    my $emploeesCount = keys %Employees;
    my @nonChangedEmpl = ();       
    
    # generate notifications and check for disabled employees 
    # before filtering our incremental employees
    my %disabledEmployees; 
    if (defined($aieHash)) {
        
        my @possibleRoles = keys(%RoleInfos);
        if ($tenant->{"VERINT_AGENT_LEVEL_PACKAGES"}) {
            while ((my $k, my $v) = (each (%VerintPackages))) {       
                if (defined($tenantFeatures{$VerintPackages2TenantFeatures{$k}})) {
                    push @possibleRoles, $v."_".$tenant->{"wfm_verint_code"};
                }
            }
        } 
        # Roles
        for my $roleName (@possibleRoles) {
            if (defined($aieHash->{"RoleList"}) && defined($aieHash->{"RoleList"}->{$roleName})) {
                syslog 'debug', 'syncTenantViaDB() Role %s found in AIE', $roleName;
            } else {
                syslog 'warning', 'syncTenantViaDB() Role %s for organization %s not found in AIE, need to be created manually', $roleName, $organizationName;
                push @{$aieMissedRoles{$organizationName}} , $roleName ;
            }
        }
       
        # Employees
        if (defined($aieHash->{"EmployeeList"}) && defined($aieHash->{"EmployeeList"}->{$tenant->{"wfm_verint_code"}})) {
            my $pattern = '[\d-]+_' . $tenant->{"wfm_verint_code"} . '$';
            foreach my $EmployeeNumber (keys %{$aieHash->{"EmployeeList"}->{$tenant->{"wfm_verint_code"}}}) {
                if ($EmployeeNumber !~ $pattern) {
                    syslog 'debug', 'syncTenantViaDB() aieEmployeeNumber %s does not match %s pattern, skipped', $EmployeeNumber, $pattern;
                    next;
                }
                my $aieEmployeeHash = $aieHash->{"EmployeeList"}->{$tenant->{"wfm_verint_code"}}->{$EmployeeNumber};

                my $aieEndDate = $aieEmployeeHash->{"EndDate"};
                if (defined($Employees{$EmployeeNumber})) {
                    delete $inactiveEmployees{$EmployeeNumber};
                    # do not reset EndDate 
                    my $EndDate = getXmlValue($Employees{$EmployeeNumber}, "EndDate") ;
                    if (defined($aieEndDate) && ($aieEndDate ne "") && defined($EndDate) && ($EndDate ne "") ) {
                        my $EndDateNode = findXmlNode($Employees{$EmployeeNumber}, "EndDate");
                        $EndDateNode->removeChildNodes();
                        $EndDateNode->appendText($aieEndDate);
                    }
                    syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s found, comparing', $EmployeeNumber;
                    if (! $commandLine{"incremental"}) {
                        next;
                    }
                    if (isEmployeeDifferent($Employees{$EmployeeNumber}, $aieEmployeeHash) || 
                        ($isSkillCacheEnabled && isEmployeeSkillDiff($EmployeeNumber))) {
                        syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s is different', $EmployeeNumber;
                    } else {
                        syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s is not changed, skipped', $EmployeeNumber;
                        push @nonChangedEmpl, $EmployeeNumber;
                    }
                    next;
                }
                # at this point we need to disable emploee, but if not only one user is being synced  
                if (defined($commandLine{"user"})) {
                    next;
                }
                # rename username to contain unique ID so login can be reused
                my $aieUserName = $aieEmployeeHash->{"UserName"};
                my $deletedUserName;
                if ($renameDisabledLogins) {
                    $deletedUserName = getDeletedUserName($aieUserName, $EmployeeNumber);
                } else {
                    $deletedUserName = $aieUserName;
                }
                if (defined($aieEndDate) 
                    && ( (! defined($aieUserName)) || $aieUserName eq $deletedUserName)  ) 
                {
                    next;
                }
                my $endDate = $aieEndDate;
                if (! defined($endDate)) {
                    $endDate = getFormatedTime(time());
                    syslog 'debug', 'syncTenantViaDB() EmployeeNumber %s in AIE, set endDate = %s', $EmployeeNumber, $endDate;
                }
                my $Employee = $dom->createElement("Employee");
                $Employee
                    ->appendChild($dom->createElement("EmployeeNumber"))
                        ->appendText($EmployeeNumber);
                $Employee
                    ->appendChild($dom->createElement("EndDate"))
                        ->appendText($endDate);
                if (defined($aieEmployeeHash->{"FirstName"})) {
                    $Employee
                        ->appendChild($dom->createElement("FirstName"))
                            ->appendText($aieEmployeeHash->{"FirstName"});
                }
                if (defined($aieEmployeeHash->{"LastName"})) {
                    $Employee
                        ->appendChild($dom->createElement("LastName"))
                           ->appendText($aieEmployeeHash->{"LastName"});
                }
                
                if (defined($aieUserName) && ($deletedUserName ne $aieUserName)) {
                    # rename username to contain unique ID so login can be reused
                    my $AdditionalInfo = $Employee
                        ->appendChild($dom->createElement("AdditionalInfo"));
                    my $EmployeeRole = $AdditionalInfo
                        ->appendChild($dom->createElement("EmployeeRole"));
                    $EmployeeRole
                        ->appendChild($dom->createElement("SyncMethod"))
                            ->appendChild($dom->createElement("snapshot"));
                            # snapshot_without_delete
                    $EmployeeRole
                        ->appendChild($dom->createElement("UserName"))
                            ->appendText($deletedUserName);
                }
                
                $Employees{$EmployeeNumber} = $Employee;
            }
        }
        
        # do not disable inactive employees if they are not found in AIE XML, but generate warning instead
        foreach my $EmployeeNumber ( keys(%inactiveEmployees)) {
            #if (! defined($tenant->{"warning"})) {
            #    $tenant->{"warning"} = "" . $tenant->{"domainName"} . " (domain.id=" . $tenant->{"domainId"}
            #              . ", num=" . $tenant->{"domainId"} . ", code=" . $tenant->{"wfm_verint_code"} . ") \r\n";
            #}
            # $tenant->{"warning"} .= sprintf('    EmployeeID %s UserName %s is inactive but not found in AIE, EndDate will not be set.', $EmployeeNumber, $inactiveEmployees{$EmployeeNumber}) . "\r\n";
            syslog 'debug', sprintf('EmployeeID %s UserName %s is inactive but not found in AIE, EndDate will not be set.', $EmployeeNumber, $inactiveEmployees{$EmployeeNumber});
            delete $Employees{$EmployeeNumber};
        }
        
    }

    if (defined($aieHash) && $commandLine{"incremental"} && ($emploeesCount - (scalar @nonChangedEmpl)) <= $diffThreshold) {    
        syslog 'debug', 'syncTenantViaDB() incremental mode is enabled';
        
        for my $empl (@nonChangedEmpl) {
            delete $Employees{$empl};
        }
        
        # DataSource
        foreach my $dataSourceName (keys(%DataSources)) {
            if (defined($aieHash->{"DataSourceList"}) && defined($aieHash->{"DataSourceList"}->{$dataSourceName})) {
                syslog 'debug', 'syncTenantViaDB() DataSource %s found in AIE, skipped', $dataSourceName;
                delete $DataSources{$dataSourceName};
            }
        }

        # Organization
        foreach my $orgName (keys(%Organizations)) {
            if (defined($aieHash->{"OrganizationExport"}) && defined($aieHash->{"OrganizationExport"}->{$orgName})) {
                syslog 'debug', 'syncTenantViaDB() Organization %s found in AIE, skipped', $orgName;
                delete $Organizations{$orgName};
            }
        }

        # Skills
        if (!isEmplSkillsChanged()) { #add SkillList to apply changes of skills inside Employees
            for my $skillName (keys (%Skills)) {
                if (defined($aieHash->{"SkillList"}) && defined($aieHash->{"SkillList"}->{$skillName})) {
                    syslog 'debug', 'syncTenantViaDB() Skill %s found in AIE, skipped', $skillName;
                    delete $Skills{$skillName};
                }
            }
        }
        
        # Roles
        for my $roleName (keys(%RoleInfos)) {
            if (defined($aieHash->{"RoleList"}) && defined($aieHash->{"RoleList"}->{$roleName})) {
                syslog 'debug', 'syncTenantViaDB() Role %s found in AIE, skipped', $roleName;
                delete($RoleInfos{$roleName});
            }
        }
        
    }
    # compile XML document
    if (scalar(keys(%DataSources)) > 0) {
        my $DataSourceList = $root->appendChild(
            $dom->createElement("DataSourceList")
        );
        
        $DataSourceList->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("snapshot_without_delete"));
    
        foreach my $DataSource ( values(%DataSources) ) {
            $DataSourceList->appendChild(
                $DataSource
            );
        }
    }

    if (scalar(keys(%Organizations)) > 0) {
        my $OrganizationList = $root->appendChild(
            $dom->createElement("OrganizationList")
        );
        $OrganizationList->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("snapshot_without_delete"));
        foreach my $Organization(values(%Organizations)) {
            $OrganizationList->appendChild($Organization);
        }
    }

    if (scalar(keys(%RoleInfos)) > 0) {
        my $RoleList = $root->appendChild(
            $dom->createElement("RoleList")
        );
        $RoleList->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("snapshot_without_delete"));
        foreach my $RoleInfo (values(%RoleInfos)) {
            $RoleList->appendChild($RoleInfo);
        }
    }

    if (scalar(keys(%Skills)) > 0) {
        my $SkillList = $dom->createElement("SkillList");
          
        $SkillList
            ->appendChild($dom->createElement("OrganizationName"))
                ->appendText($organizationName);
        $SkillList
            ->appendChild($dom->createElement("SyncMethod"))
                ->appendChild($dom->createElement("snapshot_without_delete"));
        $root->appendChild($SkillList);
        
        foreach my $Skill ( values(%Skills)) {
            $SkillList->appendChild($Skill);
        }
    }

    if (scalar(keys(%Employees)) > 0) {    
        my $EmployeeList = $root->appendChild(
            $dom->createElement("EmployeeList")
        );
        if ($withOrg) {
            $EmployeeList
                ->appendChild($dom->createElement("OrganizationName"))
                    ->appendText($organizationName);
        } else {
            syslog 'debug', 'syncTenantViaDB() domainId = %s, skipping organization in EmployeeList', $tenant->{"domainId"};
        }
        $EmployeeList
            ->appendChild($dom->createElement("SyncMethod"))
                ->appendChild($dom->createElement("snapshot_without_delete"));
    
        $EmployeeList
            ->appendChild($dom->createElement("LookupKey"))
                ->appendChild($dom->createElement("key"))
                    ->appendChild($dom->createElement("name"))
                        ->appendText("EmployeeNumber");
        foreach my $Employee (values %Employees) {
            $EmployeeList
                ->appendChild($Employee);
        }
    }
    
    if (scalar(keys(%EmployeesDeleteRoles)) > 0) {
        writeRolesDelete($tenant, $domDelete, $withOrg, $organizationName, \%EmployeesDeleteRoles);
    }

    if (! $root->hasChildNodes()) {
        syslog 'debug', 'syncTenantViaDB() nothing changed, domainId = %d, domainName = %s, wfm_verint_code = %s',
               $tenant->{"domainId"}, $tenant->{"domainName"}, $tenant->{"wfm_verint_code"};
        return;
    }
    
    my $filename = sprintf "D%d_%s_%d.xml", $tenant->{"domainId"}, $tenant->{"wfm_verint_code"}, time();
    writeXmlFile($tenant, $filename, $dom);
    
}

sub writeRolesDelete {
    my ($tenant, $dom, $withOrg, $organizationName, $EmployeesDeleteRoles) = @_; 

    my $root = $dom->createElement("root");
    $dom->setDocumentElement($root);

    my $EmployeeList = $root->appendChild(
        $dom->createElement("EmployeeList")
    );
    if ($withOrg) {
        $EmployeeList
            ->appendChild($dom->createElement("OrganizationName"))
                ->appendText($organizationName);
    } else {
        syslog 'debug', 'writeRolesDelete() domainId = %s, skipping organization in EmployeeList', $tenant->{"domainId"};
    }
    $EmployeeList
        ->appendChild($dom->createElement("SyncMethod"))
            ->appendChild($dom->createElement("snapshot_without_delete"));
    
    $EmployeeList
        ->appendChild($dom->createElement("LookupKey"))
            ->appendChild($dom->createElement("key"))
                ->appendChild($dom->createElement("name"))
                    ->appendText("EmployeeNumber");
    foreach my $Employee (values %{$EmployeesDeleteRoles}) {
        $EmployeeList
            ->appendChild($Employee);
    }

    my $filename = sprintf "D%d_%s_%d-deleteRoles.xml", $tenant->{"domainId"}, $tenant->{"wfm_verint_code"}, time();
    writeXmlFile($tenant, $filename, $dom);
}

sub writeXmlFile {
    my ($tenant, $filename, $dom) = @_;

    my $spool = getSpool($tenant);
    $spool =~ s/\/*$/\//; #add trailing slash
    if (isNasEnabled($tenant)){
        make_path($spool);
    }
    # prepare file
    my $localFilename = $spool.$filename;
    syslog 'debug', 'writeXmlFile() localFilename = %s', $localFilename;
    open my $out, '>:raw', $localFilename.".tmp" || die $!;
    print $out $dom->toString(1);
    close $out;
    rename $localFilename.".tmp", $localFilename || die $!;
    
    sendFileViaSmb($localFilename, $filename);
    sendFile2VerintProxy($tenant, $localFilename, $filename);
        
    if (! defined($spool)) {
        # delete temporary file
        syslog 'debug', 'writeXmlFile() spool is not defined, rm %s', $localFilename;
        unlink($localFilename);
    }
    
}

sub loadVccConfig {
    my ($conn) = @_;
    
    my $sth = $conn->prepare(
        "select property_name, property_value from vcc_config"
    );
    $sth->execute();
    my $href;
    while (defined($href = $sth->fetchrow_hashref()) ) {
        $vccConfig{$href->{"property_name"}} = $href->{"property_value"};
    }
    $sth->finish();
    
}

sub loadRoles {
    my ($conn) = @_;
    
    my $sth = $conn->prepare(
        "select id, name from role"
    );
    $sth->execute();
    my $href;
    while (defined($href = $sth->fetchrow_hashref()) ) {
        $five9AppRole{$href->{"id"}} = $href->{"name"};
    }
    $sth->finish();
    
}

#
# Fetch all tenants with WFM_VERINT flag enables,
# and sync each tenant  
#
# return ignored tenants, i.e. WFM_VERINT flag is set but wfm_verint_code is not specified
# 
sub syncViaDB {
    # connect to database
    my $conn = getMainDBConnection();
    
    if (! defined($conn)) {
        syslog 'err', "syncViaDB() Can not connect to MainDB";
        die "Can not connect to MainDB";
    }
    
    # read configuration from vcc_config
    loadVccConfig($conn);
    
    # read roles
    loadRoles($conn);
    
    # read all domains info and disconnect from MainDB
    my $tenants = fetchTenants($conn);
    
    # do not keep maindb connection open
    $conn->disconnect();
    
    if ($commandLine{"incremental"}) {
        $diffThreshold = getProperty($diffThresholdProperty, $diffThresholdDefault);
        syslog 'debug', 'syncViaDB() incremental mode diffThreshold='.$diffThreshold;
    }
    
    foreach my $tenant(values %$tenants) {
        if (! defined($tenant->{"wfm_verint_code"})) {
            syslog 'warning', "syncViaDB() wfm_verint_code is not defined for domain.id=%d (%s) , ignoring", $tenant->{"domainId"}, $tenant->{"domainName"};
            next;
        }
        
        if (!defined($commandLine{"force-asi"}) && !$tenant->{"VERINT_ASI"}) {
            syslog 'warning', "syncViaDB() ASI is disabled for domain.id=%d (%s) , ignoring", $tenant->{"domainId"}, $tenant->{"domainName"};
            next;
        }
        eval {
            syncTenantViaDB($tenant);
        };
        if($@) {
            my $err = $@;
            syslog 'err', "syncTenantViaDB(domain.id=%d) returned error: %s", $tenant->{"domainId"}, $err;
            $tenant->{"err"} = $err;
        }
    }
    
    return $tenants;
}

#
# Check property in follow order:
#  1. system properties first
#  2. VCC config
#  3. default hardcoded value 
# 
sub getProperty {
    my ($propertyKey, $propertyDefault) = @_;
    my $propertyValue = $properties{$propertyKey};
    if (! defined($propertyValue)) {
        $propertyValue = $vccConfig{$propertyKey};
    }
    if (! defined($propertyValue)) {
        $propertyValue = $propertyDefault;
    }
    return $propertyValue;
}

# main function - entry point
sub main {
    my @ARGV = @_;
    my $err;
    my $warning = "";
    my $tenants;
    my $startTs = time();
    
    openlog "VerintSync.pl", 'ndelay,pid', 'local7';
    eval {
        syslog 'debug', 'main() entering';
        syslog 'debug', 'main() pwd = %s, $0 = %s', getcwd(), $0;

        my $config = \@configDefault;

        for (my $i = 0 ; $i<scalar(@ARGV); $i++) {
            if ($ARGV[$i] =~ /^\-\-domain[_\-]?ids?\=(.+)/i) {
                my $domainIds = $1;
                # print "domainIds=$domainIds\n";
                if (defined($commandLine{"domain-ids"})) {
                    $commandLine{"domain-ids"} .= ",".$domainIds; 
                } else {
                    $commandLine{"domain-ids"} = $domainIds;
                }
            } elsif ($ARGV[$i] eq "-d") {
                if (defined($commandLine{"domain-ids"})) {
                    $commandLine{"domain-ids"} .= ",".$ARGV[$i+1]; 
                } else {
                    $commandLine{"domain-ids"} = $ARGV[$i+1];
                }
                $i++;
            } elsif ($ARGV[$i] =~ /^\-\-env[_\-]?ids?\=(.+)/i) {
                my $ids = $1;
                if (defined($commandLine{"env-ids"})) {
                    $commandLine{"env-ids"} .= ",".$ids; 
                } else {
                    $commandLine{"env-ids"} = $ids;
                }
            } elsif ($ARGV[$i] eq "-env") {
                if (defined($commandLine{"env-ids"})) {
                    $commandLine{"env-ids"} .= ",".$ARGV[$i+1]; 
                } else {
                    $commandLine{"env-ids"} = $ARGV[$i+1];
                }
                $i++;
            } elsif ($ARGV[$i] eq "--no-smb") {
                $commandLine{"no-smb"} = 1;
            } elsif ($ARGV[$i] =~ /^\-\-incremental/i || $ARGV[$i] =~ /^\-\-diff/i) {
                $commandLine{"incremental"} = 1;
            } elsif ($ARGV[$i] =~ /^\-\-defaults[_\-]?file\=(.+)/i) {
                $config = [$1];
            } elsif ($ARGV[$i] =~ /^\-\-user\=(.+)/i) {
                $commandLine{"user"} = $1;
            } elsif ($ARGV[$i] eq "-u") {
                $commandLine{"user"} = $ARGV[$i+1];
                $i++;
            } elsif ($ARGV[$i] =~ /^\-\-stdout/i) {
                $commandLine{"stdout"} = 1;
            } elsif ($ARGV[$i] eq "--force-asi") {
                $commandLine{"force-asi"} = 1;
            } 
        }        
        
        loadProperties($config);
        
        # syncViaDB returns tenants along with errors  
        # so they can be reported 
        
        $tenants = syncViaDB();
        
    };
    
    if($@) {
        $err = $@;
        print "error: ". $err; 
        syslog 'err', "%s", "main() returned error: $err";
    }
    
    if (ref($tenants) eq "HASH") {
        my $warning_no_wfm_code = "";
        foreach my $tenant(values %$tenants) {
            if ($tenant->{"err"}) {
                $err .= sprintf ("domain.id=%d  returned error: %s\r\n", $tenant->{"domainId"}, $tenant->{"err"});
            }
            if (! defined($tenant->{"wfm_verint_code"})) {
                $warning_no_wfm_code .= sprintf("%15d %s\r\n", $tenant->{"domainId"}, $tenant->{"domainName"});
            }
            if (defined($tenant->{"warning"})) {
                $warning .= $tenant->{"warning"} . "\r\n";
            }
        }
        if($warning_no_wfm_code ne "") {
            $warning .= "Warning: tenant(s) without Verint Code\r\n"
                     . "      domain.id domain.name\r\n"
                     . $warning_no_wfm_code;
        }
    }
    
    if (scalar(keys(%aieMissedRoles)) > 0) {
        $warning .= "Warning: role(s) do not exist in organizations\r\n"
                  . "      OrganizationName : RoleName\r\n";
        foreach my $o (keys(%aieMissedRoles)) {
            foreach my $r (@{$aieMissedRoles{$o}}) {
                $warning .= sprintf("  %15s : %s\r\n", $o, $r);
            }
        }
    }
    
    if(scalar(keys(%aieMissedScreenRecordingDataSources)) > 0) {
        $warning .= "Warning: screen recording datasource(s) do not exist\r\n"
                  . "      OrganizationName : DataSource\r\n";
        foreach my $o (keys(%aieMissedScreenRecordingDataSources)) {
            $warning .= sprintf("  %15s : %s\r\n", $o, $aieMissedScreenRecordingDataSources{$o});
        }
    }
    
    if ($err || ($warning ne "") || defined($aieXmlParseError)) {
        my $s;
        my $mailto = getProperty($errorMailToProperty);
        if ( ! $commandLine{"stdout"} && defined $mailto ) {
            my $hostname = `hostname`;
            chomp($hostname);
            $s .= "To: " . $mailto . "\n";
            $s .= "Subject: $hostname:$0 ".($err?"failed":"warning")." for ASI or WFM \n";
            $s .= "Content-Type: text/plain\n";
            $s .= "\n";
        }
        if ($err) {
            $s .= "Error: " . $err . "\r\n\r\n";
        }
    
        if (defined($aieXmlParseError)) {
            $s .= "Warning: " . $aieXmlParseError . "\r\n\r\n";
        }
        
        $s .= $warning;
        
        $s .= "\r\n\r\n\r\n";
        
        if ($commandLine{"stdout"}) {
            print $s;
        } elsif ( defined $mailto ) {
            my $sendmail = getProperty($sendmailProperty, $sendmailDefault);
            
            if ( open MAIL, "| " . $sendmail ) {
                print MAIL $s;
                close MAIL;
            } else {
                syslog 'err', "main() error sending mail: %s", $!;
            }
        }
    }
    syslog 'debug', 'main() exiting. Elapsed ' . (time()-$startTs) . " sec";
    closelog;
}

sub initAgentSkillTable {
    my ($c) = @_;
    %agentSkillHash = ();
    %agentSkillHashChanged = ();
    if (isTableExist($c, 'verint_asi_agent_skills')) {
        %agentSkillHash = createAgentSkillHash($c);
    } else {
        createASIAgentSkillTable($c);
    }
}

sub createAgentSkillHash {
    my ($c) = @_;
    syslog 'debug', 'createAgentSkillHash()';
    my %h; 
    my $href;
    
    my $sth = $c->prepare("SELECT * FROM verint_asi_agent_skills");
    $sth->execute();
    while (defined($href = $sth->fetchrow_hashref()) ) {
        $h{$href->{'user'}}{$href->{'skill'}}{$href->{'level'}} = $href->{'start_time'};
    } 
    $sth->finish();
    return %h;
}

sub isTableExist {
    my ($conn, $table) = @_;
    my $found = 0;

    my $sth = $conn->prepare("SHOW TABLES LIKE '$table'");
    $sth->execute();
    if (defined($sth->fetchrow_hashref()) ) {
        syslog 'debug', 'isTableExist() ' . $table .' table detected in database';
        $found = 1;
    } else {
        syslog 'debug', 'isTableExist() ' . $table .' no table in database';
    }
    $sth->finish();
    return $found;
}

sub createASIAgentSkillTable {
    syslog 'debug', 'createASIAgentSkillTable()';
    my ($conn) = @_;
    $conn->do("CREATE TABLE `verint_asi_agent_skills` (
                                      `id` bigint(20) NOT NULL AUTO_INCREMENT,
                                      `user` varchar(255) NOT NULL,
                                      `skill` varchar(255) NOT NULL,
                                      `level` bigint(20) NOT NULL DEFAULT 1,
                                      `start_time` varchar(255) NOT NULL DEFAULT '',
                                      PRIMARY KEY (`id`),
                                      UNIQUE KEY `user_skill_level_verint_asi_agent_skills_key` (`user`,`skill`,`level`)
                                    ) ENGINE=InnoDB");
}

sub compareSkills {
    my ($user, $skill, $level, $time) = @_;
    if (0 == scalar (keys %agentSkillHash)) { # first launch
        #add to 'changed hash' to store it in the end of the script execution
        $agentSkillHashChanged{$user}{$skill}{$level} = $time;
        return $time;
    }
    my $start_time = $agentSkillHash{$user}{$skill}{$level};
    if (defined($start_time)) { #same data as in cache table from previous script launch
        delete $agentSkillHash{$user}{$skill}{$level}; 
        return $start_time;
    } else { #new|changed 
        delete $agentSkillHash{$user}{$skill}{$level};
        $agentSkillHashChanged{$user}{$skill}{$level} = $time;
        return $time;
    }
}

sub getFormatedTime {
    my ($ts) = @_;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($ts);
    return sprintf("%d-%02d-%02d %02d:%02d:%02d", $year+1900, $mon+1, $mday, $hour, $min, $sec);
}

sub updateAgentSkillTable {
    my ($conn) = @_;
    syslog 'debug', 'updateAgentSkillTable()';
    my $insert = "INSERT INTO `verint_asi_agent_skills` (`user`, `skill`, `level`, `start_time`) VALUES ";
    my $delete = "DELETE FROM `verint_asi_agent_skills` WHERE (`user`, `skill`, `level`) IN (";
    
    my @delValArray = formValues("('%s','%s','%d')", %agentSkillHash);
    my @insValArray = formValues("('%s','%s','%d','%s')", %agentSkillHashChanged);
 
    for my $delVal (@delValArray) {
        syslog 'debug', 'updateAgentSkillTable() delete from skill cache: '. $delVal;
        my $sth = $conn->prepare($delete.$delVal.")");
        $sth->execute();
        $sth->finish();
    }
    
    for my $insVal (@insValArray) {
        syslog 'debug', 'updateAgentSkillTable() insert into skill cache: '. $insVal;
        my $sth = $conn->prepare($insert.$insVal);
        $sth->execute();
        $sth->finish();
    }
}

sub formValues {
    my ($format, %h) = @_;
    my @retarray = ();
    my $batchSize = 1000;
    my $ret = '';
    my $first = 1;
    my $count = 0; 
    for my $h1 ( keys %h ) {
        for my $h2 ( keys %{ $h{$h1} } ) {
            for my $h3 ( keys %{$h{$h1}{$h2}} ) {
                $ret.=($first? "": ",") . sprintf($format, $h1, $h2, $h3, $h{$h1}{$h2}{$h3});
                $first = 0;
                ++$count;
                if ($count > $batchSize) {
                       push @retarray, $ret;
                       $first = 1;
                       $count = 0;
                       $ret = '';
                }
            }
        }
    }
    if ($count > 0) {
        push @retarray, $ret;
    }
    
    return @retarray;
}

sub isEmployeeSkillDiff {
    my ($emplNumber) = @_;  

    if (defined $agentSkillHashChanged{$emplNumber}) {
        syslog 'debug', 'isEmployeeSkillDiff() new|changed skill';
        return 1;
    }
    if (defined $agentSkillHash{$emplNumber}) {
        syslog 'debug', 'isEmployeeSkillDiff() removed skills: '. (join ", ", keys %{$agentSkillHash{$emplNumber}});
        return 1;
    }
    return 0;
}
sub isSkillCacheOn {
    my ($num) = @_;
    my $dval = getProperty($skillCacheProperty.".".$num);
    if (defined ($dval)) {
        return $dval;
    }
    return getProperty($skillCacheProperty, 1);
}

sub cleanAgentSkillHash() {
    syslog 'debug', 'cleanAgentSkillHash() ';
    for my $h1 ( keys %agentSkillHash ) {
        for my $h2 ( keys %{ $agentSkillHash{$h1} } ) {
                if ( scalar( keys (%{$agentSkillHash{$h1}{$h2}}) == 0) ) {
                       delete $agentSkillHash{$h1}{$h2};
                }
        }
        if (scalar( keys (%{$agentSkillHash{$h1}})) == 0 ) {
            delete $agentSkillHash{$h1};
        }
    }
}
sub addToSkillAssignment {
    my ($dom, $AdditionalInfo, $verintCode, $skillName, $level, $startTime, $endTime) = @_;
    my $SkillAssignment = $AdditionalInfo->appendChild($dom->createElement("SkillAssignment"));
    $SkillAssignment->appendChild($dom->createElement("Skill"))->appendText(verintQuote($skillName."_".$verintCode, 0, 49));
    $SkillAssignment->appendChild($dom->createElement("SkillClassification"))->appendChild($dom->createElement("primary"))->appendText("true");
    $SkillAssignment->appendChild($dom->createElement("Proficiency"))->appendText("1.0");
    $SkillAssignment->appendChild($dom->createElement("Priority"))->appendText($level);
    $SkillAssignment->appendChild($dom->createElement("Reserve"))->appendText("true");
    if ($startTime) {
       $SkillAssignment->appendChild($dom->createElement("StartTime"))->appendText($startTime);
    }
    if ($endTime) {
       $SkillAssignment->appendChild($dom->createElement("EndTime"))->appendText($endTime);
    }
}
sub isEmplSkillsChanged {
        return scalar(keys(%agentSkillHashChanged)) > 0 || scalar(keys(%agentSkillHash)) > 0 
}
sub isNasEnabled {
    my ($tenant) = @_;
    return $tenant->{"envEnabled"} && ($tenant->{"pdcNas"} || $tenant->{"bdcNas"})
}
sub getSpool {
    my ($tenant) = @_;
    return isNasEnabled($tenant) ? getProperty($tmpFolderProperty, $tmpFolderDefault).$tenant->{"wfm_verint_code"}: getProperty($spoolProperty.".".$tenant->{"num"});
}
sub getAIEPattern {
    my ($tenant) = @_;
    return $tenant->{"envEnabled"} ? $tenant->{"aieTemplate"} : getProperty($aieFilePatternProperty, undef);
}
sub getWeekStartDay {
    my ($tenant) = @_;
    if ($tenant->{"envEnabled"}) {
        return $tenant->{"fdow"};
    }
    my $weekStartDay = trim(getProperty($weekStartDayProperty.".".$tenant->{"num"}, undef));
    if (! defined($weekStartDay)) {
        $weekStartDay = trim(getProperty($weekStartDayProperty, $weekStartDayDefault));
    }
    return $weekStartDay;
}
sub sendFileViaSmb  {
    my ($localFilename, $remoteFilename) = @_;

    my $smb = getProperty($smbProperty);
    
    if (defined($commandLine{"no-smb"})) {
        print "local file " . $localFilename . " , option --no-smb specified, skip smb\n";
        syslog 'debug', 'sendFileViaSmb() option --no-smb specified, skip smb';
    } elsif (! defined($smb)) {
        syslog 'debug', 'sendFileViaSmb() com.five9.verint.VerintSync.smb is not defined'; 
    } else {
        my $smbMasked = $smb;
        $smbMasked =~ s/\-\-user\S+/--user=[hidden]/;
        syslog 'debug', 'sendFileViaSmb() com.five9.verint.VerintSync.smb = %s', $smbMasked ; 
        my $cmd = $smb . " -c \"put $localFilename $remoteFilename.tmp;rename $remoteFilename.tmp $remoteFilename\" 2>&1";
        my $res = `$cmd`;
        syslog 'debug', 'sendFileViaSmb() smb put output: %s', $res ;
    }
}
sub sendFile2VerintProxy {
    my ($tenant, $localFilename, $filename) = @_;
    
    if (isNasEnabled($tenant)) {
        my $proxies = $tenant->{"verintProxies"};
        if (scalar(@{$proxies}) > 0) {
            foreach my $pxy (@{$proxies}) {
                if (sendFileViaHttps($tenant, $pxy, $localFilename, $filename)) {
                    syslog 'debug', 'sendFile2VerintProxy() delete file after succesfull delivery to verint-proxy, rm %s', $localFilename;
                    unlink($localFilename);
                    return;
                }
            }
        } else {
            syslog 'debug', 'sendFile2VerintProxy(): No verint proxies have been found for domain = %d', $tenant->{"domainId"};
        }
    } else {
        syslog 'debug', 'sendFile2VerintProxy(): Nas is not enabled for domain = %d', $tenant->{"domainId"};
    }
}

sub sendFileViaHttps {
    my ($tenant, $pxy, $localFilename, $filename) = @_;
    my $port = getProperty($verintProxyHttpsPortProperty, 8443);
    my $url = sprintf('https://%s:%d/vcc-verint-proxy/domain/%d/asi/%s', $pxy, $port, $tenant->{"domainId"}, $filename);
    
    my $curlOpts = getProperty($curlOptionsProperty, '-k');
    my $cmd = sprintf('curl %s -s -o /dev/null/ -w "%%{http_code}" -X POST --data-binary @%s %s', $curlOpts, $localFilename, $url);
    syslog 'debug', 'sendFileViaHttp(): %s ', $cmd;
    my $res = `$cmd`;
    syslog 'debug', 'sendFileViaHttps() curl put output: %s', $res ;
    return $res eq 200;
}

sub getEmailFromPerson {
    my ($aiePerson) =  @_;

    foreach my $childNode ($aiePerson->childNodes()) {
        if ($childNode->nodeName eq 'PersonContact') {
            foreach my $contactType ($childNode->childNodes()) {
                if ($contactType->nodeName eq 'ContactType') {
                   foreach my $email ($contactType->childNodes()) {
                       if ($email->nodeName eq 'Email') {
                           return getXmlValue($childNode, "Value");
                       }
                   }
                }
            }
        }
    }
    return undef;
}
### end of file