#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;

use Opals::Context;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

use Opals::Circulation qw(
    circ_cancelHoldReserve
    );
use Opals::User qw(
    user_currentUser
);
use Time::localtime;
my $rs={};
my $request = _getRequest();
my $cgi = CGI->new;
my $ssId = $cgi->cookie('globalSessionID');

if(defined $request && $ssId ne ''){
    if($request->{'op'} eq 'del'){
        my ($deleted,$notDeleted) =delUser($dbh);
        $rs={status=>1,deleted=>$deleted,notDeleteable=>$notDeleted};
    }
    elsif($request->{'op'} eq 'update'){
        updateUser($dbh,$request);
        $rs={status=>1};
    }
    
}

print $cgi->header(
        -type    => 'text/plain',
        -charset => 'utf-8'
    );

print  to_json($rs) ;
#------------------------------------------------------------------------------
sub delUser{
    my ($dbh)=@_;
    my $ssName=$request->{'ssName'};
    my ($errCode, $myCookie, $curUser) = user_currentUser($dbh, $cgi);
    $dbh->do("update opl_sessionVar set note='NOTDELETEABLE' where var=? && ssId= ? && rid=?",undef,$ssName,$ssId,$curUser->{'uid'});
    
    # SET note=NOTDELETEABLE when  user balance>0
    $dbh->do(<<_SQL_);
    update opl_sessionVar s inner join (select uid from opl_charge group by uid having sum(balance)>0) b on s.rid=b.uid    
    set s.note="NOTDELETEABLE" 
_SQL_

    # SET note=NOTDELETEABLE when user loan  some books
 $dbh->do(<<_SQL_);
    update opl_sessionVar s inner join opl_loan l on l.uid=s.rid
    set note="NOTDELETEABLE" where l.dateReturn is null
_SQL_
    
   
    # TEXTBOOK::: SET note=NOTDELETEABLE when  user balance>0
    $dbh->do(<<_SQL_);
    update opl_sessionVar s inner join 
        (select t.uid, t.tid, t.balance from tb_transactions t inner join 
            (select uid,max(tid)tid from tb_transactions group by uid) s  on s.tid=t.tid 
        ) b on s.rid=b.uid   
    set note="NOTDELETEABLE" where b.balance>0
_SQL_
    # TEXTBOOK::: SET note=NOTDELETEABLE when user loan  some books
 $dbh->do(<<_SQL_);
    update opl_sessionVar s inner join tb_loan l on l.uid=s.rid
    set note="NOTDELETEABLE" where l.dateReturn is null
_SQL_
    # EQUIPMENT::: SET note=NOTDELETEABLE when user loan some equipment
 $dbh->do(<<_SQL_);
    update opl_sessionVar s inner join eq_loan l on l.uid=s.rid
    set note="NOTDELETEABLE" where l.dateReturn is null
_SQL_

    # MOVE users to be delete to opl_delUser   
    $dbh->do(<<_SQL_);
    insert into opl_delUser select u.* from opl_user u inner join opl_sessionVar s on s.rid=u.uid 
    where (s.note is null || s.note <>'NOTDELETEABLE') && s.var='$ssName' && s.ssId= '$ssId'
_SQL_


    my $tm = localtime;
    my $todayStr = sprintf("%04d-%02d-%02d", $tm->year +1900, ($tm->mon)+1, $tm->mday);
    # CANCEL  Hold   
    my $sth_hold=$dbh->prepare("select  h.idReserve from opl_hold h inner join opl_reserve r using(idReserve) 
                     inner join opl_sessionVar s  on r.uid=s.rid 
              where  (s.note is null || s.note <>'NOTDELETEABLE') && s.var='$ssName' && s.ssId= '$ssId'  && 
                     h.dateExpiry>now() && h.dateLoan is null && h.dateCancel is null") ;
    $sth_hold->execute();
    while(my ($id)=$sth_hold->fetchrow_array){
        circ_cancelHoldReserve($dbh,$id,$todayStr);
    }

    # CANCEL Reserve   
    my $sql_reserve=$dbh->prepare("select  r.idReserve from opl_reserve r inner join opl_sessionVar s  on r.uid=s.rid 
             where   (s.note is null || s.note <>'NOTDELETEABLE')  && s.var='$ssName' && s.ssId= '$ssId' && 
                     r.dateExpiry>now() && r.numCopyReserve>0 && r.dateCancel is null "); 
    $sql_reserve->execute();
    while(my ($id)=$sql_reserve->fetchrow_array){
        circ_cancelHoldReserve($dbh,$id,$todayStr);
    }
   
    # DELETE users   
    $dbh->do(<<_SQL_);
    delete from opl_user where uid in(select rid from  opl_sessionVar s  
    where (s.note is null || s.note <>'NOTDELETEABLE') && s.var='$ssName' && s.ssId= '$ssId')
_SQL_

   my ($deleted)=$dbh->selectrow_array("select count(*) from opl_sessionVar where var='$ssName' && ssId='$ssId' && (note is null || note='')");
   my ($notDeleted)=$dbh->selectrow_array("select count(*) from opl_sessionVar where var='$ssName' && ssId='$ssId' &&  note='NOTDELETEABLE'");
   return($deleted,$notDeleted);
}
#------------------------------------------------------------------------------
sub updateUser{
    my ($dbh,$request)=@_;
    my @updateFields=();
    my $ssName=$request->{'ssName'};
    my @valArr;
    foreach my $field (@{$request->{'newData'}}){
        push @updateFields, $field->{"name"} ." = ?";
        push @valArr,$field->{'val'};
    }
    my $sqlFieldSet=join(", ",@updateFields);
    my $sth =$dbh->prepare("update opl_user u inner join opl_sessionVar s on u.uid=s.rid && s.ssid='$ssId' && var='$ssName'  set $sqlFieldSet ");
    $sth->execute(@valArr);
}

#------------------------------------------------------------------------------
sub _getRequest{
    my $request =undef;
    if ($ENV{'REQUEST_METHOD'} eq "POST") {
        my $json ="";
        while (<STDIN>) {
            $json .= $_;
        }
         $request = decode_json($json) if($json ne ""); 
    }
   
    return $request;
}

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