#!/usr/bin/perl 

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


use Opals::Context;

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

use JSON;


my $dbh = Opals::Context->dbh();
my @RS_TBL_FIELD_MAP=(
    {rsType=>'library',table=>"pf_bookRs", fields=>[qw (pfId groupId rid title author pubName pubPlace pubDate callNumber isbn commentNote)]},
    {rsType=>'web',    table=>"pf_webRs",  fields=>[qw (pfId groupId title author description url)]},
    {rsType=>'file',   table=>"pf_file",   fields=>[qw (pfId groupId title fName fRealName description url)]}
);
my $tblChg=[
            ["opl_user",    "circStats",        "alter table opl_user add circStats text"],
            ["opl_user",    "keepLoanHistory",  "alter table opl_user add keepLoanHistory tinyint(1) unsigned DEFAULT '1'"],
            ["opl_user",    "noticeByEmail",    "alter table opl_user add noticeByEmail tinyint(4) DEFAULT '1' "],
            ["opl_item",    "circStats",        "alter table opl_item add  circStats text"],
            ["opl_genre",   "iconUrl",          "alter table opl_genre add iconUrl varchar(255) DEFAULT NULL"],
            ["pf_record",   "iconUrl",          "alter table pf_record add iconUrl varchar(255) DEFAULT NULL"],
            ["opl_category", "maxOverdue",      "alter table opl_category add maxOverdue int(10) unsigned NOT NULL DEFAULT '0'"],
            ["opl_category", "accBalThreshold", "alter table opl_category add accBalThreshold float(10,2) DEFAULT '0.00'"],
            ["opl_category", "keepLoanHistory", "alter table opl_category add keepLoanHistory tinyint(4) DEFAULT '0'"],
            ["pf_bookRs",   "groupId",          "alter table pf_bookRs add `groupId` tinyint(3) unsigned DEFAULT '1' after pfId"],
            ["pf_bookRs",   "commentNote",      "alter table pf_bookRs add `commentNote` text"],
            ["pf_webRs",    "groupId",          "alter table pf_webRs add `groupId` tinyint(3) unsigned DEFAULT '1' after pfId"]
        ];
        

my $sth=$dbh->prepare("select xml from pf_record order by pfId ");
$sth->execute();
while( my ($xml)=$sth->fetchrow_array){
    my $pfRec =parsePfXml($xml);
    updatePfRecFields($pfRec);
    save($dbh,$pfRec);
}
############################################################

sub updatePfRecFields{
    my ($pfRec)=@_;
    my @introArr=();
    foreach my $intro(@{$pfRec->{'intro'}}){
        push  @introArr,$intro->{'item'};
    }
    $pfRec->{'introduction'}=join "\n",@introArr;
    delete $pfRec->{'intro'};
    my @subject=();
    foreach my $sbj(@{$pfRec->{'subject'}}){
        push  @subject,$sbj->{'item'};
    }
    $pfRec->{'subject'}=\@subject;
    $pfRec->{'introduction'}=join "\n",@introArr;
    delete $pfRec->{'intro'};

    foreach my $lRs (@{$pfRec->{'libraryRsList'}}){
        $lRs->{"rsList"}=$lRs->{"bookList"};
        delete $lRs->{"bookList"};
    }
    foreach my $lRs (@{$pfRec->{'internetRsList'}}){
        $lRs->{"rsList"}=$lRs->{"webList"};
        delete $lRs->{"webList"};
        foreach my $rs(@{$lRs->{"rsList"}}){
            $rs->{'description'} =$rs->{'description_webRs'};
            delete $rs->{"description_webRs"};
        }

    }
    $pfRec->{'webRsList'}=$pfRec->{'internetRsList'};
    delete $pfRec->{'internetRsList'};
    foreach my $lRs (@{$pfRec->{'wSheetRsList'}}){
        $lRs->{"rsList"}=$lRs->{"wsList"};
        delete $lRs->{"wsList"};
        foreach my $rs(@{$lRs->{"rsList"}}){
            $rs->{'description'} =$rs->{'description_wsRs'};
            $rs->{'url'} =$rs->{'file_url'};
            delete $rs->{"description_wsRs"};
            delete $rs->{'file_url'};
        }
    }
    $pfRec->{'fileRsList'}=$pfRec->{'wSheetRsList'};
    delete $pfRec->{'wSheetRsList'};
     
}

#####################################################
#


sub save{
    my ($dbh,$pfRec) = @_;
    my $pfId=$pfRec->{'pfId'};
    if($pfRec->{'title'} eq ''){
        return 0;
    }
    my $sth_bookRs=$dbh->prepare("update pf_bookRs set isbn=? where title=? && author=? && pfId=? && isbn is null");
    foreach my $rs(@{$pfRec->{"libraryRsList"}}){
        foreach my $item(@{$rs->{'rsList'}}){
            if(!defined $item->{'rid'} || $item->{'rid'} eq '' ){
                $sth_bookRs->execute($item->{'isbnList'},$item->{'title'},$item->{'author'},$pfId);
            }
        }

    }
}
#======================================================================
sub parsePfXml{
    my ($xml) =@_;
    my $ret={};
    $xml =~ s/<([a-zA-Z_0-9]+)\/>/<$1><\/$1>/g;
    my $tmpXml="$xml";
    my $fields=[{name=>'pfId',          rep=>0}, #rep: 1=>repeatable, 0=>non-repeatable
                {name=>'title',         rep=>0},
                {name=>'author' ,       rep=>0},
                {name=>'audience' ,     rep=>0},
                {name=>'createdDate',   rep=>0},
                {name=>'subject',       rep=>1},
                {name=>'intro',         rep=>1}
              ];
    foreach my $f(@$fields){
	   $tmpXml =~ s/<$f->{'name'} \/>/<$f->{'name'}><\/$f->{'name'}>/g;
    }
    $tmpXml =~ s/<libraryRs>(.*)<\/libraryRs>//;
    $tmpXml =~ s/<internetRs>(.*)<\/internetRs>//;
    $tmpXml =~ s/<wSheetRs>(.*)<\/wSheetRs>//;
    foreach my $f(@$fields){
        if($f->{'rep'}==1){
            $ret->{$f->{'name'}}=[] ;
        }
        else{
            $ret->{$f->{'name'}}="";
        }
       if(!$f->{'rep'} && $tmpXml =~ m/<$f->{'name'}>(.*?)<\/$f->{'name'}>/s){
           $ret->{$f->{'name'}}=escapeXML($1);           
       }
       else{
           while($tmpXml =~ m/<$f->{'name'}>(.*?)<\/$f->{'name'}>(.*)/s){
                $tmpXml=$2;
                push @{$ret->{$f->{'name'}}},{item=>escapeXML($1)};
           }
       }
    }
  
#for resource fields 
    my $rs=undef;
    foreach my $rsName(qw(libraryRs internetRs wSheetRs)){
        $tmpXml=$xml;
        my $count=0;
        while($tmpXml =~ m/<$rsName>(.*?)<\/$rsName>(.*)/s){
            next if($1 eq '');
            if($rsName eq 'libraryRs'){
                $rs=_getLibRs($1);
            }
            elsif($rsName eq 'internetRs'){
                $rs= _getWebRs($1);
            }
            elsif($rsName eq 'wSheetRs'){
                $rs= _getWorksheetRs($1);
            }
            $rs->{"rsId"}="$rsName" ."_"  . $count++;
            push @{$ret->{$rsName . "List"}},$rs if(defined $rs);
            $tmpXml=$2;    
        }
    }
    
    return $ret;
}
#======================================================================
sub  _getWebRs{
    my ($xml) =@_;
    my $webRs=undef;
    my $tmpXml=$xml;
    $tmpXml =~ s/<internet>(.*)<\/internet>//;
    if($tmpXml =~ m/<header>(.*?)<\/header>/s){
        $webRs->{'header'} =escapeXML($1) if($1 ne'');
    }
    if($tmpXml =~ m/<description>(.*?)<\/description>/s){
        $webRs->{'description'} =escapeXML($1) if($1 ne'');
    }
 
    $tmpXml=$xml;
    while($tmpXml =~ m/<internet>(.*?)<\/internet>(.*)/s){
        my $web =_getWebItem($1);
        if(defined $web){
            $webRs->{'webList'} =[] if(!defined $webRs->{'webList'});
            push @{$webRs->{'webList'}},$web ;
        }
        $tmpXml=$2;  
    }
    return $webRs; 

}
#======================================================================
sub _getWorksheetRs{
    my ($xml) =@_;
    my $wsRs=undef;
    my $tmpXml=$xml;
    $tmpXml =~ s/<worksheet>(.*)<\/worksheet>//;
    $wsRs->{'header'} ="";
    if($tmpXml =~ m/<header>(.*?)<\/header>/s){
        $wsRs->{'header'} =escapeXML($1) if($1 ne'');
    }
    $wsRs->{'description'} ="";
    if($tmpXml =~ m/<description>(.*?)<\/description>/s){
        $wsRs->{'description'} =($1) if($1 ne'');
    }
    $tmpXml=$xml;
    while($tmpXml =~ m/<worksheet>(.*?)<\/worksheet>(.*)/s){
        my $ws = _getWsItem($1);
        if(defined $ws){
            $wsRs->{'wsList'} =[] if(!defined $wsRs->{'wsList'});    
            push @{$wsRs->{'wsList'}},$ws;
        }
        $tmpXml=$2;  
    }
    return $wsRs;
}

#======================================================================
sub  _getLibRs{
    my ($xml) =@_;
    my $libRs=undef;
    my $tmpXml=$xml;
    $tmpXml =~ s/<book>(.*)<\/book>//;
    if($tmpXml =~ m/<header>(.*?)<\/header>/s){
        $libRs->{'header'} =escapeXML($1) if($1 ne'');
    }
    if($tmpXml =~ m/<description>(.*?)<\/description>/s){
        $libRs->{'description'} =($1) if($1 ne'');
    }
    $tmpXml=$xml;
    while($tmpXml =~ m/<book>(.*?)<\/book>(.*)/s){
        my $book =_getBookItem($1);
        if(defined $book){
            $libRs->{'bookList'} =[] if(!defined $libRs->{'bookList'});    
            push @{$libRs->{'bookList'}},$book;
        }
        $tmpXml=$2;  
    }
    return $libRs; 

}
#======================================================================
sub _getBookItem{
   my ($xml) =@_;
   my $ret=undef;
   my @fields =qw(title author pubName pubPlace pubDate isbnList callNumber rid commentNote);
   foreach my $f(@fields){
       if($xml =~ m/<$f>(.*?)<\/$f>/s){
           $ret->{$f}=escapeXML($1) if($1 ne '');
       }
   }
   return $ret;
}
#======================================================================
sub _getWebItem{
   my ($xml) =@_;
   my $ret=undef;
   my @fields =qw(title author url description);
   my $fVal="";
   foreach my $f(@fields){
       $fVal="";
       if($xml =~ m/<$f>(.*?)<\/$f>/s){
           $fVal= $1;
           my $fName =($f eq 'description')?"$f" ."_webRs":$f;
           if($f eq 'url' && $fVal !~ /^((http|https|ftp):\/\/)/ ){
              $fVal ="http://" . $fVal; 
           }
           $fVal=escapeXML($fVal) if($f ne 'description');
           $ret->{$fName}=$fVal if($fVal ne '');
       }
   }
  return $ret;
}
#======================================================================
sub _getWsItem{
   my ($xml) =@_;
   my $ret=undef;
   
   my @fields =qw(title description fileName fRealName file_url );
   my $imgIcon='shim';
   foreach my $f(@fields){
       my $fName =($f eq 'description')?"$f" ."_wsRs":$f;
       $ret->{$fName}=""; 
       if($xml =~ m/<$f>(.*?)<\/$f>/s){
           $ret->{$fName}=$1 if($1 ne'');
       }
   }
  return $ret;
}
############################################################
sub escapeXML{
    my($str) =@_;
    $str =~ s/&amp;/&/g;
    $str =~ s/&gt;/>/g;
    $str =~ s/&lt;/</g;
    $str =~ s/&quot;/"/g;
    $str =~ s/&apos;/'/g;
    return $str;
}