package Opals::Mail;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    mail_send
    
    mail_enqueueMail
    mail_sendQueuedMail
    mail_getEmailByUid
    mail_getEmailByEid
    mail_getEmailByStatus
    mail_getContEmailByEid
    mail_sendMails
    mail_validateEmail
);
use Opals::Date qw(
    date_text
    date_DHM_text
);
# Version number
$VERSION   = 0.01;      


#use utf8;
use strict;
use MIME::Lite;

use Opals::Context;
#use Opals::_Some_Module_;


#===============================================================================
sub mail_send {
    my ($preference, $to, $subject, $data,$eTxtContent) = @_;

    my $msg = MIME::Lite->new(
        From    => $preference->{'emailAddress'},
        To      => $to,
        Subject => $subject,
        Data    => "$eTxtContent"
    );

    my $part = MIME::Lite->new(
        Type    => 'text/html',
        Data    => $data
    );
    $part->attr('content-type.charset' => 'UTF8');
    $msg->attach($part);

    my $retVal;
    if ($preference->{'emailSmtp'}) {
        if ($preference->{'emailUser'}) {
            $retVal = $msg->send(
                'smtp',
                $preference->{'emailSmtp'},
                AuthUser => $preference->{'emailUser'},
                AuthPass => $preference->{'emailPass'}
            );
        }
        else {
            $retVal = $msg->send(
                'smtp',
                $preference->{'emailSmtp'}
            ) or $retVal =0;
            
        }
    }
    else {
        $retVal = $msg->send;
    }

    return $retVal;
}

#===============================================================================
sub mail_validateEmail{
    my ($email,$checkHost)=@_;
    if ($email !~ m/(@.*@)|(\.\.)|(@\.)|(\.@)|(^\.)/ &&
        $email =~ m/^.+\@((\[?)[a-zA-Z0-9\-\.]+\.([a-zA-Z]{2,3}|[0-9]{1,3})(\]?))$/) {
        return (defined $checkHost && $checkHost ==1)?validateHost($1):1;
    }
    else {
        return 0;
    }
}
#===============================================================================
sub validateHost{
    my ($host)=@_;
    my $validHost=1;
    my $res = Net::DNS::Resolver->new();
    my $err=0;
    my @mx = mx($res, $host)
       or $validHost=0;

   return  $validHost;

}

#===============================================================================
sub mail_enqueueMail{
#   $email={email=>'someone@abc.com',subject=>'mysubject',content=>'mycontent'}

    my($dbh,$uid,$email)=@_;

    my $sqlRequest=(<<_SQL_);
insert into opl_emailRequest set 
   email = ?,
   uid   = ?,
   reqType=?,
   requestDate = now() 
_SQL_

   my $sqlDetail=(<<_SQL_);
insert into opl_emailDetails set
  eid      = ?, 
  subject  = ?,
  eContent = ?,
  eBarcodes = ?,
  eTxtContent =?
_SQL_
    
    my $sthRequest  = $dbh->prepare($sqlRequest);
    my $sthDetail   = $dbh->prepare($sqlDetail);
    my $eid = 0;
    $sthRequest->execute($email->{'email'},$uid,$email->{'reqType'});
    $eid = $dbh->{'mysql_insertid'};
    my $emails =  $sthDetail->execute($eid,$email->{'subject'},$email->{'content'},$email->{'barcodes'},$email->{'txtContent'});

    $sthRequest->finish;
    $sthDetail->finish;
    
    return $email eq '0E0'?0:1;


}
#===============================================================================
sub mail_sendQueuedMail{
    my($dbh,$pref)=@_;
    my $emailList=getContEmail($dbh);
    foreach my $email(@$emailList){
        my $status='sent';
        if(mail_validateEmail($email->{'email'})){        
            if(!mail_send($pref,$email->{'email'}, $email->{'subject'}, $email->{'eContent'},$email->{'eTxtContent'})){
                $status='fail';
            }
        }
        else{
            $status='rejected';
        }
        updateEmailQueue($dbh,$email->{'eid'},$status);
    }
}
#===============================================================================
sub mail_sendMails{
    my($dbh,$pref,$emailList)=@_;
    foreach my $email(@$emailList){
        my $status='sent';
        if(mail_validateEmail($email->{'email'},1)){
            if(!mail_send($pref,$email->{'email'}, $email->{'subject'}, $email->{'eContent'},$email->{'eTxtContent'})){
                $status='fail';
            }
        }
        else{
            $status='rejected';
        }

        updateEmailQueue($dbh,$email->{'eid'},$status);
    }
}
#===============================================================================
sub updateEmailQueue{
    my ($dbh,$eid,$status)=@_;
    if($status eq 'sent'){
        if( $dbh->do("select sendDate from  opl_emailRequest where eid=$eid  && (sendDate ='' or sendDate is null)")){
            $dbh->do("update opl_emailRequest set status ='$status',sendDate =now()   where eid=$eid ");
        }
        else{
            $dbh->do("update opl_emailRequest set status ='$status' , retryCount =retryCount +1 where eid=$eid ");
        }
    }
    else{
        $dbh->do("update opl_emailRequest set status ='$status' , retryCount =retryCount +1 where eid=$eid ");
    }
}
#===============================================================================
sub getContEmail{
    my ($dbh) =@_;
    my $sth = $dbh->prepare(<<_STH_);
select  r.eid,email,subject,eContent,eTxtContent
from    opl_emailRequest r inner join opl_emailDetails d using(eid)       
where   (r.status = 'waiting' || r.status = 'rejected') 
        && ( retryCount =0 || (retryCount < 6 && sendDate + INTERVAL 3600 SECOND <=now())) 
order by eid 
_STH_
    
    my @eList=();
    $sth->execute();
    while (my $e = $sth->fetchrow_hashref) {       
        push @eList, $e ;
    }
    $sth->finish;
    return \@eList;

}
#===============================================================================
sub mail_getContEmailByEid{
    my ($dbh,$eid) =@_;
    my $sth = $dbh->prepare(<<_STH_);
select  r.eid,email,subject,eContent,eTxtContent
from    opl_emailRequest r inner join opl_emailDetails d using(eid)       
where   r.eid  in ($eid) 
order by r.eid
_STH_
    
    my @eList=();
    $sth->execute();
    my $disabled = 0;
    while (my $e = $sth->fetchrow_hashref) {
        $disabled = 0;
        if($e->{'status'} eq 'rejected'){
            $e->{'status'} = 'Error. Invalid email address';
            $disabled =1;
        }
        $e->{'disabled'} = $disabled;
        push @eList, $e ;
    }
    $sth->finish;
    return \@eList;

}

#===============================================================================
sub mail_getEmailByUid{
    my ($dbh,$uid,$sortBy,$sortDir,$pNum,$pagesize) =@_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
    my $sql = <<_STH_;
    select  distinct u.uid,u.firstname,u.lastname,u.grade,
        r.requestDate,r.sendDate ,r.status status,r.retryCount,
        d.subject,d.eBarcodes,d.eTxtContent,r.eid, r.email
from    opl_emailRequest r inner join opl_emailDetails d using(eid) 
        inner join opl_user u on u.uid=r.uid      
where   r.uid in ($uid)
_STH_

    if(defined $sortBy && $sortBy ne ''  ){
        $sql .= "order by $sortBy $sortDir ";
    }
    $sql .= " limit   $pageoffset, $pagesize ";
    my $sth = $dbh->prepare($sql);

    my @eList=();
    $sth->execute();

    my $disabled = 0;
    while (my $e = $sth->fetchrow_hashref) {
        #$e->{'requestDate'} = date_text($e->{'requestDate'},0);
        #$e->{'sendDate'}    = date_text($e->{'sendDate'},0)if( $e->{'sendDate'});
        $e->{'requestDate'} = date_DHM_text($e->{'requestDate'},0);
        $e->{'sendDate'}    = date_DHM_text($e->{'sendDate'},0)if( $e->{'sendDate'});
        if($e->{'email'} eq ''){
            $disabled = 0;
            my $email=getGuardianEmail($dbh,$e->{'uid'});
            $e->{'email'} = $email->{'email'};
                if($e->{'status'} eq 'rejected'){
                    $e->{'status'} = 'Error. Invalid email address';
                    $disabled =1; 
                }
        }
        $e->{'disabled'} = $disabled;
        push @eList, $e ;
    }

    $sth->finish;
    return \@eList;

}
#===============================================================================
sub mail_getEmailByStatus{
    my ($dbh,$status,$sortBy,$sortDir,$pNum,$pagesize) =@_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }

my $sql = <<_STH_;
select  distinct u.uid,u.firstname,u.lastname,u.grade,
        r.eid,r.requestDate,r.sendDate ,r.status status,r.retryCount,
        d.subject,d.eBarcodes,d.eContent,d.eTxtContent,r.eid, r.email
from    opl_emailRequest r inner join opl_emailDetails d using(eid) 
        inner join opl_user u on u.uid=r.uid      
_STH_

    if(defined $status && $status ne '' && $status ne 'all'  ){
        $sql .= "where   r.status = '$status' ";
    }
    if(defined $sortBy && $sortBy ne ''  ){
        $sql .= "order by $sortBy $sortDir";
    }
    $sql .= " limit   $pageoffset, $pagesize ";
    my @eList=();
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $disabled = 0;
    while (my $e = $sth->fetchrow_hashref) {
        $disabled = 0;
        #$e->{'requestDate'} = date_text($e->{'requestDate'},0);
        #$e->{'sendDate'}    = date_text($e->{'sendDate'},0)if( $e->{'sendDate'} );
        $e->{'requestDate'} = date_DHM_text($e->{'requestDate'},0);
        $e->{'sendDate'}    = date_DHM_text($e->{'sendDate'},0)if( $e->{'sendDate'} );
        if($e->{'email'} eq ''){
            my $email=getGuardianEmail($dbh,$e->{'uid'});
            $e->{'email'} = $email->{'email'};
        }
        if($e->{'status'} eq 'rejected'){
            $e->{'status'} = 'Error. Invalid email address';
            $disabled =1; 
        }

        $e->{'disabled'} = $disabled;
        push @eList, $e ;
    }
    $sth->finish;
    return \@eList;

}

#===============================================================================
sub mail_getEmailByEid{
    my ($dbh,$eid) =@_;
    my $sth = $dbh->prepare(<<_STH_);
select  distinct r.eid,email,subject,eContent,eTxtContent
from    opl_emailRequest r inner join opl_emailDetails d using(eid)       
where   eid = ? 
order by eid 
_STH_
    
    my @eList=();
    $sth->execute($eid);
    while (my $e = $sth->fetchrow_hashref) {       
        push @eList, $e ;
    }
    $sth->finish;
    return \@eList;

}

#===============================================================================
sub getGuardianEmail{
    my ($dbh,$uid)=@_;
    my $email={};
    my $sth=$dbh->prepare("select gemail email from opl_guardian where uid=$uid limit 1");
    $sth->execute();
    $email =$sth->fetchrow_hashref;
    $sth->finish;
    return $email;	
    
}

#sub mail_{
#    my () = @_;
#}


1;