#!/usr/bin/perl 
my $scriptname;
BEGIN {
    $scriptname = `basename $0`;
    chomp $scriptname;

    if (!$ENV{'PERL5LIB'} || !$ENV{'OPALS_CONF'}) {
        exit 1;
    }
}


use strict;
use Opals::Context;
use JSON;
use DBI;
use CGI;
use Text::CSV_XS;
use Opals::Date qw(
    date_parse
);
use Digest::SHA qw(
    sha1_hex
);
use Net::FTP;


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }
my ($DB) = $dbh->selectrow_array('select database()');

END {
    if ($dbh) {
        $dbh->disconnect();
    }
}


my $csv = Text::CSV_XS->new({ binary => 1 });

my ($DB) = $dbh->selectrow_array('select database()');

my ($uData,$fSignature,$fSrc) = getUserData($dbh);
my $sql_update="";
my $sql_insert="";
my $tm =time_now();
if(defined $uData){
    
    my $fieldMap    =getFieldMap($dbh,@$uData[0]);
    my $userFMap    =getImportFieldMap_user($dbh,$fieldMap);
    my $guardianFMap=getImportFieldMap_guardian($dbh,$fieldMap);

    my @uFields=qw ();
    foreach my $f(keys %$userFMap){
        push  @uFields,"$f=? ";
    } 
    my $iFieldStr =join(",",@uFields);

    if(scalar(@uFields)>2){
        $sql_update="update opl_user set $iFieldStr,status=1,idimport=0,modified=now() where uid=?";
        $sql_insert="insert into opl_user set $iFieldStr ,categoryCode=?,status=1,idimport=0,created=now(),modified=now()";

        splice @$uData,0,1;
        my ($categorycode) =$dbh->selectrow_array("select val from opl_preference where var='defCategory'");
        my ($countNew,$countUpdate)=(0,0);
        foreach my $row (@$uData){
            $row =~ s/^\s+//g;
            next if($row eq '');
            $csv->parse($row);
            my @fields = $csv->fields();
            my ($uid,$iType)=importUser($dbh,$userFMap,\@fields,$categorycode);
            if(defined $uid && scalar(@$guardianFMap)>0){ 
                $dbh->do("delete from opl_guardian where uid=?",undef,$uid);
                foreach my $g(@$guardianFMap){
                    importGuardian($dbh,$uid,$g,\@fields);
                }
            }
            $iType==1?$countNew++:$countUpdate++;
        }
        $dbh->do("replace into opl_preference set var='lastUserFileSignature',val=? ,gid=4,hidden=1",undef,$fSignature);
        printf "%s   SITE:%s   SOURCE:%s  TOTAL IMPORT:%d   TOTAL UPDATE:%d\n",$tm, $DB ,$fSrc,$countNew,$countUpdate;
    }
    else{
        printf "%s   SITE:%s   SOURCE:%s  ERROR in preference setting; check the user import field map to make sure the fields are correctly mapped\n";

    }
    
}
elsif(defined $fSignature &&   defined $fSrc){
    printf "%s   SITE:%s   SOURCE:%s  NO CHANGE SINCE LAST UPDATE\n",$tm, $DB ,$fSrc;

}

#------------------------------------------------------------------------------
sub importUser{
    my ($dbh,$userFMap,$data,$categorycode)=@_;
    my $uInfo=getDataFields($userFMap,$data);
    my $uid=getUid($dbh,$userFMap,$data);
    my $iType=0; #0:update, 1:insert new
    if(defined $uid && $uid>1){
        $dbh->do($sql_update,undef,@$uInfo,$uid);
        $iType=0;
    }
    else{
        $dbh->do($sql_insert,undef,@$uInfo,$categorycode);
        $uid=$dbh->{'mysql_insertid'};
        $iType=1;
    }
    return ($uid,$iType);
}

#------------------------------------------------------------------------------

sub importGuardian{
    my ($dbh,$uid,$gfieldMap,$data)=@_;
    my @fields=("uid = $uid");
    foreach my $f(keys %$gfieldMap){
        push @fields,"$f =? ";
    }
    my $gFieldStr= join (",",@fields);
    my $gInfo=getDataFields($gfieldMap,$data);

    $dbh->do("insert into opl_guardian set $gFieldStr ",undef,@$gInfo); 
}

#------------------------------------------------------------------------------
sub getUid{
    my ($dbh,$uFieldMap,$data)=@_;
    my $uid=undef;
    if(defined $uFieldMap->{'sid'}){
        my $sid=@$data[$uFieldMap->{'sid'}];
        if(defined $sid && $sid ne''){
            ($uid)=$dbh->selectrow_array("select uid from opl_user where sid=?",undef,$sid);
        }
    }
    if(!defined $uid && defined $uFieldMap->{'userbarcode'}){
        my $ubc=@$data[$uFieldMap->{'userbarcode'}];
        if(defined $ubc && $ubc ne''){
            ($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$ubc);
        }
    }
    return $uid;
}


#------------------------------------------------------------------------------
sub getDataFields{
    my($fieldIndexMap,$fields)=@_;
    my @uData=();

    
    foreach my $f(keys %$fieldIndexMap){
        my $i=$fieldIndexMap->{$f};
        my $val=$$fields[$i];
        $val=~ s/<NEWLINE>/\r/g;
        if($f eq 'gender'){
            $val=getGenderVal($val);
        }
        elsif($f eq 'birthday' || $f eq 'addrExpiry_alt' || $f eq 'expirydate'){
            $val=Opals::Date::date_parse($val);
        }
        
        push  @uData,$val;
    }
    return \@uData;
}

#------------------------------------------------------------------------------
sub getUserData{
    my($dbh)=@_;
    my $data=undef;
    my ($uData,$fSignature,$fSrc)=(undef,undef,undef);
    
    my ($url)=$dbh->selectrow_array("select val from opl_preference where var='userFileFtp_url'");
    my ($username)=$dbh->selectrow_array("select val from opl_preference where var='userFileFtp_username'");
    my ($password)=$dbh->selectrow_array("select val from opl_preference where var='userFileFtp_password'");
    my ($userFilePath)=$dbh->selectrow_array("select val from opl_preference where var='userCsvFileLocation'");
    my ($lastFileSigature)     =$dbh->selectrow_array("select val from opl_preference where var='lastUserFileSignature'");
    my ($overwrite)            =$dbh->selectrow_array("select val from opl_preference where var='userDataOverwriten'");

    if(defined $url && $url =~ m/^[s]?ftp:\/\//i){
        $fSrc = $url ;
        ($uData,$fSignature) =getFTP_file($url,$username,$password);
    }
    elsif(defined $userFilePath && -f $userFilePath){
        $fSrc = $userFilePath;
        ($uData,$fSignature) =getImportData($userFilePath);
    }
   
    if(defined $uData && ($fSignature ne $lastFileSigature || $overwrite eq '1')){
        $data=$uData;
    }
    return ($data,$fSignature,$fSrc);
}
#------------------------------------------------------------------------------
#
sub getFTP_file{
    my($url,$username,$password)=@_;
    my($userData,$signature)=(undef,undef);
    if($url != m/^ftp:\/\/|sftp:\/\//g){
        $url="ftp://$url";
    }
    my $part=parseUrl($url);
    my $scheme  = $part->{'scheme'};
    my $host    = $part->{'host'};
    my $path    = $part->{'path'};
    my $file    = $part->{'file'};
    my $tmpFile= undef;;    
    if(defined $scheme){
        my $opals_root = get_opalsRoot($ENV{'OPALS_CONF'});
        $tmpFile = "/tmp/$file";#`mktemp`;
        system "\rm -f $tmpFile";
        #chomp $tmpFile;
        my $script=undef;
        if($scheme eq 'ftp'){
            $script="wget -O $tmpFile ftp://$username:$password\@$host$path$file";
        }
        elsif($scheme eq 'sftp'){
            my $sftpUrl ="$host:$path$file";
            $script = "$opals_root/script/cron/sftp.exp $sftpUrl $username $password $tmpFile";
        }
        #print "$script\n";
        if(defined $script){
        system $script;
            if(-f "$tmpFile"){
                system($script);
                ($userData,$signature)=getImportData($tmpFile);
            }
        }
        
    }
    return ($userData,$signature);
}
#------------------------------------------------------------------------------

sub getFTP_file_bk{
    my($url,$username,$password)=@_;
    my($userData,$signature)=(undef,undef);
    if($url != m/^ftp:\/\/|sftp:\/\//g){
        $url="ftp://$url";
    }
    my $part=parseUrl($url);

    my $scheme  = $part->{'scheme'};
    my $host    = $part->{'host'};
    my $path    = $part->{'path'};
    my $file    = $part->{'file'};
    my $tmpFile=undef ;    

    if(defined $scheme){
        if(lc($scheme) eq 'ftp'){
            my $ftp = Net::FTP->new($host, Debug =>0 )
                or die "$tm $DB :: Cannot connect to $host: $@";
            $ftp->login($username,$password)
                or die "$tm Cannot login ", $ftp->message;
            $ftp->cwd($path)
                or die "$tm $DB :: Cannot change working directory ", $ftp->message;
            $ftp->get($file,"/tmp/$file")
                or die "$tm $DB :: get failed ", $ftp->message;
            $ftp->quit;
        }
        elsif(lc($scheme) eq 'sftp'){
           my $script=" $host:$path/$file  $password $tmpFile";
           system($script);
        }
        
    }
    if(-f "/tmp/$file"){
        ($userData,$signature)=getImportData("/tmp/$file");
    }
    return ($userData,$signature);
}

#------------------------------------------------------------------------------

sub get_opalsRoot{
    my($opals_conf)=@_;
    my $vhost_conf = 
            `grep -R "$opals_conf\$" /etc/httpd/conf.d/opals | cut -d: -f1`;
    chomp $vhost_conf;
    my $opals_root = '/' .
            `cat $vhost_conf | sed -re 's/#.+\$//' | grep OPALS_ROOT | cut -d/ -f2,3`;
        chomp $opals_root;
    return   $opals_root;   

}

#------------------------------------------------------------------------------
sub getImportData{
    my($fp)=@_;
    my @userData=();
    my $dataStr="";    
    open DATA,"<$fp"; 
    while(<DATA>){
        $dataStr .=$_;
        push @userData,$_;
    }
    my $signature = sha1_hex($dataStr);
    return (\@userData,$signature);
     
}
#------------------------------------------------------------------------------
sub parseUrl{
  my $url = shift;
  if( $url =~ m/^((http[s]?|ftp|sftp):\/)?\/?([^:\/\s]+)((\/\w+)*\/)([\w\-\.]+[^#?\s]+)(.*)?(#[\w\-]+)?$/){
    
    my %parts;
    $parts{'scheme'} = (defined $2) ? $2 : "";
    $parts{'host'} = (defined $3) ? $3 : "";
    $parts{'path'} = (defined $4) ? $4 : "";
    $parts{'file'} = (defined $6) ? $6 : "";
    $parts{'query'} = (defined $7) ? $7 : "";

    return \%parts;
  }

}
#------------------------------------------------------------------------------
sub getImportFieldMap_user{
    my($dbh,$fieldMap)=@_;
    my $ret={};
    # get opl_user table fields
    my $sth = $dbh->prepare( "SELECT * FROM opl_user LIMIT 1" );  
    $sth->execute();
    my $fieldNames = $sth->{NAME};
    #

    foreach my $f(@$fieldNames){
        if(defined $fieldMap->{$f}){
            $ret->{$f}=$fieldMap->{$f}->{"index"};
        }
        else{
            #$ret->{$f}=-1;
        }
    }
    return $ret;
}

#------------------------------------------------------------------------------
sub getImportFieldMap_guardian{
    my($dbh,$fieldMap)=@_;
    my $ret=[];
     # get opl_guardian table fields
    my $sth = $dbh->prepare( "SELECT * FROM opl_guardian LIMIT 1" );  
    $sth->execute();
    my $fieldNames = $sth->{NAME};
    #

    foreach my $gIdx qw(g0 g1 g2){
        next if(!defined $fieldMap->{$gIdx});
        my $fm={};
        foreach my $f(keys %{$fieldMap->{$gIdx}}){
            foreach my $f (@$fieldNames){
                if(defined $fieldMap->{$gIdx}->{$f}){
                    $fm->{$f}=$fieldMap->{$gIdx}->{$f}->{"index"};
                }
            }
        }
        push @$ret,$fm;
    }
    return $ret;
}


#------------------------------------------------------------------------------
sub getGenderVal{
    my($genderStr)=@_;
    my $val=undef;
    if($genderStr =~ m/^0$/ ||
           $genderStr =~ m/^f$/i ||
           $genderStr =~ m/^female$/i ){
           $val=0;
        }
    elsif ($genderStr =~ m/^1$/ ||
            $genderStr =~ m/^m$/i ||
            $genderStr =~ m/^male$/i ){
            $val=1;
    }
    
    return $val;

}
#------------------------------------------------------------------------------
sub getFieldMap {
    my($dbh,$hdStr)=@_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
    $csv->parse($hdStr);
    my @fields=$csv->fields();
    my $fMap={};
    my ($fm)=$dbh->selectrow_array("select val from opl_preference where var='iumap'");
    return undef if(!defined $fm || $fm eq'');
    
    my $fmObj;
    if($fm =~ m/^{/){
        $fmObj=decode_json($fm);
    }
    else{
        my @arr=  split(",",$fm);
        my $len= scalar(@arr);
        $fmObj={};
        if($len >1 && $len%2==0){
            for(my $i=0;$i<$len;$i+=2){
               $fmObj->{@arr[$i]}=@arr[$i+1];
            }
        }
    }
    foreach my $f(keys %$fmObj){
        for(my $i=0; $i<scalar(@fields);$i++){
            if($fmObj->{$f} eq @fields[$i]){
                $fMap->{$f}={index=>$i};
            }
            elsif($f =~ m/^g[012]$/){
                foreach my $gf (keys %{$fmObj->{$f}}){
                    if($fmObj->{$f}->{$gf} eq  @fields[$i]){
                        $fMap->{$f}->{$gf}={index=>$i};
                    }
                }
            }
            

        }
    }
    return $fMap; 
}
#------------------------------------------------------------------------------
sub time_now {
    my $tn = `date +'%Y-%m-%d %H:%M:%S'`;
    chomp $tn;

    return $tn;
}

#------------------------------------------------------------------------------