#!/usr/bin/perl
use HTTP::Date;
use strict;
use DBI;
use Config::IniFiles;
use Mail::Sendmail;

#how to run this script:
#first variable defines the organization. All for all organizations. Individual organizations have to be separated by comma like CORMP,CAROCOOPS. 

my $orga=$ARGV[0];
my $sensor=$ARGV[1];

#check EST or EDT
my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
my $myhour=4;
if($isdst==0){$myhour=5;}

my $cfg=Config::IniFiles->new( -file => '/home/xeniaprod/config/site.ini');
my $ip=$cfg->val('site','ip');
my $db_ref="db_xenia";
my $db_name=$cfg->val($db_ref,'dbname');
my $db_user=$cfg->val($db_ref,'user');
my $db_passwd=$cfg->val($db_ref,'pass');

my $email_server=$cfg->val('site','email_server');
my $email_sender=$cfg->val('site','email_sender');
my $email_to_secooradm=$cfg->val('site','email_to_secooradm');


my $dbh=DBI->connect("dbi:Pg:dbname=$db_name","$db_user","$db_passwd");
if (!defined $dbh) {die "Cannot connect to database!\n";}

#first initize the variable
 my @archive_sql;
 my @status_sql;
 my $email_content="";

 my ($sql,$sth);
 $sql="";

my @temp_organization;
if(lc($orga) ne "all"){
 if($orga gt ""){
     @temp_organization=split(/,/,$orga);
     for(my $i=0;$i<@temp_organization;$i++){
         my $temp=lc($temp_organization[$i]);
         $temp_organization[$i]=lc($temp_organization[$i]);
         if($i==0){
             $sql="(organization.short_name='$temp'";
         }
         else {
             $sql=$sql. " or organization.short_name='$temp'";
         }
     }
     if(@temp_organization>0){
         $sql=$sql.") and ";
     }
 }
}
#do all orga and send email to defined orga
$sql="";

my %latest_status;
my $r_latest_status=\%latest_status;

#now retrieve the lateste measurement timestamp for each sensor and platform
 $sql = qq{ select
      organization.short_name
      ,organization.row_id
      ,organization.email_tech
      ,platform.row_id
      ,platform.platform_handle       
      ,sensor.short_name   
      ,sensor.row_id
      ,extract('epoch' from m_date)
      ,extract('epoch' from current_timestamp)
      ,to_char(m_date,'YYYY-MM-DD HH24:MI:DD')
      ,multi_obs.qc_level
      from sensor
      left join platform on platform.row_id=sensor.platform_id
      left join organization on organization.row_id=platform.organization_id
      left join multi_obs on sensor.row_id=multi_obs.sensor_id and m_date>(now()-interval '7 hours') and m_value is not null
      where
       $sql sensor.s_order=1 and platform.platform_handle not like '%radar' 
       and sensor.active<3 
       order by sensor.platform_id,sensor.row_id,multi_obs.m_date desc; };  

       #$sql sensor.s_order=1 and platform.platform_handle not like '%radar' 
       #$sql sensor.s_order=1 and platform.platform_handle like 'carocoops%' 
   my $sth = $dbh->prepare($sql);
   $sth->execute();
 
   my $previous_platformid="";
   my $i=0;

 
   while (my ($temp_orga,$temp_orgaid,$temp_email,$temp_platformid,$temp_platformhandle,$temp_sensorname,$temp_sensorid,$temp_date,$temp_current,$m_date,$qc_level) = $sth->fetchrow_array) {
#        if($qc_level==1){;}
#        else {
        if($temp_platformid ne $previous_platformid){
          
           my $temp;
               $temp=($temp_current-$temp_date)/3600;
               $temp=$temp+$myhour;
               $latest_status{operator_list}{$temp_orga}{id}=$temp_orgaid;
               $latest_status{operator_list}{$temp_orgaid}{name}=$temp_orga;
               $latest_status{operator_list}{$temp_orgaid}{email}=$temp_email;
                        
               $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{age}=$temp;
               $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{platform_handle}=$temp_platformhandle;
               $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{m_date}=$m_date;
        }
        
        #now check sensor
        if (!defined $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{sensorid}{$temp_sensorid}) { 
                    $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{sensorid}{$temp_sensorid}{sensorname}=$temp_sensorname;
                    $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{sensorid}{$temp_sensorid}{m_date}=$m_date;
                    my $temp;
                    $temp=($temp_current-$temp_date)/3600;
                    $temp=$temp+$myhour;
                    if($temp<$latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{age}){
                        $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{age}=$temp;
                        $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{m_date}=$m_date;
                    }
#                    print "$temp_sensorid $m_date\n";
                    $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{sensorid}{$temp_sensorid}{age}=$temp;
        }
         
        $previous_platformid=$temp_platformid;
        $i++;
#      }
   }#end of while loop
   $sth->finish();

# now retrieve the previous status for each platform
 $sql="";
 $sql = qq{ select organization_id,platform_id,status,to_char(begin_date,'YYYY-MM-DD HH24:MI:SS'),extract('epoch' from begin_date),extract('epoch' from current_timestamp),platform_handle,row_entry_date,author,reason from platform_status where status<3  order by organization_id,platform_id;}; 
 $sth = $dbh->prepare($sql);
 $sth->execute();
 #print "$sql\n";
 while (my ($temp_orgaid,$temp_platformid,$platform_status,$latest_date,$temp_date,$temp_current,$temp_platformhandle,$row_entry_date,$author,$reason) = $sth->fetchrow_array) {  
       #check the age of the lastest end_date           
      my $temp;
      $temp=($temp_current-$temp_date)/3600;
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_status}=$platform_status;
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_entrydate}=$row_entry_date;
      #JTC print "$temp_orgaid:$temp_platformid:$platform_status\n";
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_mdate}=$latest_date;
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_age}=$temp;
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_author}=$author;
      $latest_status{operator_list}{$temp_orgaid}{platform_list}{$temp_platformid}{previous_reason}=$reason;
#      if($temp_platformhandle eq "nerrs.gtmpcwq.wq"){print "$row_entry_date $platform_status**orga:$temp_orgaid**plat:$temp_platformid \n";}
 }


 #now compare previous status and current_status and decide whether to send email;
 foreach my $operatorid (sort keys %{$r_latest_status->{operator_list}}) {
    my $orga_name=$latest_status{operator_list}{$operatorid}{name};
    foreach my $platformid (sort keys %{$r_latest_status->{operator_list}{$operatorid}{platform_list}}) {
        my ($age,$status,$m_date);
        #Check whether the platform report any data in the past 7 hours
        my $platform=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{platform_handle};
        if(substr($platform,-5) eq "radar"){next;}

       
        if(!defined $latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{m_date}){
         
            #no data reported in the past 7 hours from this platform
            $status=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_status};
           my $previous_entrydate=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_entrydate};
            my $previous_age=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_age};
            my $previous_mdate=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_mdate};
      #if($platform eq "nerrs.gtmpcwq.wq"){print "$platform $platformid $previous_entrydate $status ***\n";}
            if(($previous_age>8760)&&($status==2)){
                  push(@status_sql,"update platform_status set status=4,row_update_date=current_timestamp where platform_id=$platformid");
                  push(@status_sql,"update platform set active=4,row_update_date=current_timestamp where row_id=$platformid");
                  
                  push(@archive_sql,"update platform_status_archive set status=4,row_update_date=current_timestamp where platform_id=$platformid and begin_date='$previous_mdate'");
            }
            if(!defined $status){
#                  print "$platform $previous_entrydate $previous_status\n";
                  push(@status_sql,"delete from platform_status where platform_id=$platformid");
                  push(@status_sql,"insert into platform_status(platform_handle,platform_id,row_entry_date,status,oranization_id) values ('$platform',$platformid,current_timestamp,0,$operatorid)");
                  push(@status_sql,"update platform set active=0,row_update_date=current_timestamp where row_id=$platformid");
                  push(@archive_sql,"insert into platform_status_archive(platform_id,row_entry_date,status) values ($platformid,current_timestamp,0)");
            }
        }
        else {
           # there are data reported from this platform for the past 7 hours
        #if($platform eq "nccoos.array.radar"){print "I am here no age";} 
           #check whether data is more than 4 hours old
           $age=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{age};
           $m_date=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{m_date};
           my $temp_date="null";
           if($m_date gt "1990-12-01 01:01:01"){$temp_date="'$m_date'";} 
           #print "$platform $m_date\n";
           my $previous_status=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_status};
		#print "jcdebug2:$age:$operatorid:$platformid:$previous_status:\n";
           my $previous_mdate=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_mdate};
           my $previous_author=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_author};
           my $previous_reason=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{previous_reason};
                                      
           if($age>4){
               #data are more than 4 hours old
               #sensor_status
               #check if this status is the same as the previous status, do nothing if they are the same, send email and update the platform_status, platform_status_archive table if they are not the same
               if(!defined $previous_status){
                    if(($ARGV[0] =~/all/i)||(grep $_ eq $orga_name,@temp_organization)){
                         $email_content .= "$platform has not reported data for the past 4 hours\n";
                    }

                   if($temp_date ne "null"){
                      push(@status_sql,"insert into platform_status (platform_id,organization_id,row_entry_date,begin_date,status,platform_handle) values ($platformid,$operatorid,current_timestamp,$temp_date,2,'$platform')");
                      push(@status_sql,"update platform set active=2,row_update_date=current_timestamp where row_id=$platformid");
                      push(@archive_sql,"insert into platform_status_archive (platform_id,organization_id,row_entry_date,begin_date,status) values ($platformid,$operatorid,current_timestamp,$temp_date,2)");
                   }
               }
           }
           else {
               # data are less than 4 hours old
               $status=1; 
	#JTC	if (!(defined $previous_status)) { print "not_def:$platformid\n"; }
               if((defined $previous_status)&&($previous_status!=1)){         
               #if($previous_status!=$status){
                    #resumed reporting
                    if(($ARGV[0] =~/all/i)||(grep $_ eq $orga_name,@temp_organization)){
                        $email_content .= "$platform resumed reporting to SECOORA database.\n ";;
                    }
		#print "jcdebug1\n";
                    push(@status_sql,"delete from platform_status where platform_id=$platformid");
                    push(@status_sql,"update platform set active=$status,row_update_date=current_timestamp where row_id=$platformid");

                    if($previous_mdate){
                        push(@archive_sql,"update platform_status_archive set end_date=$temp_date,row_update_date=current_timestamp,author='$previous_author',reason='$previous_reason' where platform_id=$platformid and begin_date='$previous_mdate'");
                    }
                    else {
                        push(@archive_sql,"update platform_status_archive set end_date=$temp_date,row_update_date=current_timestamp,author='$previous_author',reason='$previous_reason' where platform_id=$platformid and row_id in(select max(row_id) from platform_status_archive where platform_id=$platformid)");
                    }
               }
               #if(!defined $previous_status){
               #     if(($ARGV[0] =~/all/i)||(grep $_ eq $orga_name,@temp_organization)){
               #         $email_content .= "$platform resumed reporting to SECOORA database.\n ";;
               #     }
                    #push(@status_sql,"update platform set active=1,row_update_date=current_timestamp where row_id=$platformid");
               #}                            
           }#end of age<4
        }#end of age<7
    }#end of platform
 } #end of organization

 #send email if platform status change
 if($email_content ne ""){
      my $subject="[secoora_auto_alert] SECOORA Platform Status Update";
      $email_content .="\n Click http://$ip/xenia/status/platformStatus.php?member=1 to view or update platform/sensor status\n";
      sendAlarm($email_content,$subject,$email_to_secooradm);
 }
 #update apltform_status_archive table
 for(my $a=0;$a<@archive_sql;$a++){
     $sth = $dbh->prepare( $archive_sql[$a] );
     $sth->execute();
     $sth->finish();
#     print "$archive_sql[$a]\n";
 }
 #update platform_status table
 for(my $a=0;$a<@status_sql;$a++){
     $sth = $dbh->prepare( $status_sql[$a] );
     $sth->execute();
     $sth->finish();
     #print "$status_sql[$a]\n";
 }


 $email_content="";
 my @sensor_sql; 
 my @sensorarchive_sql;

 my ($operatorid,$previous_orga,$orga_email,$orga_emailcontent,$orga_name);
 foreach $operatorid (sort keys %{$r_latest_status->{operator_list}}) {
     if((!$previous_orga)||($operatorid!=$previous_orga)){
        if($orga_email gt ""){
            if($orga_emailcontent ne ""){
                my $subject="[secoora_auto_alert] SECOORA Platform Sensor Status Update";
                $orga_emailcontent .="\nClick http://$ip/xenia/status/platformStatus.php?member=1&&provider=$operatorid to view and update platform/sensor status.\n";
                sendAlarm($orga_emailcontent,$subject,$orga_email);
                $orga_emailcontent="";
            }
         }  
        $orga_name=$latest_status{operator_list}{$operatorid}{name};
        $orga_email=$latest_status{operator_list}{$operatorid}{email};
        $previous_orga=$operatorid;
     }
     foreach my $platformid (sort keys %{$r_latest_status->{operator_list}{$operatorid}{platform_list}}) {
       
        my $platform=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{platform_handle};

        if(substr($platform,-5) eq "radar"){next;} 
        #find current sensor status
        foreach my $sensorid (sort keys %{$r_latest_status->{operator_list}{$operatorid}{platform_list}{$platformid}{sensorid}}) {
            #now retrieve the previous sensir status
            $sql = qq{ select status,to_char(begin_date,'YYYY-MM-DD HH24:MI:SS'),extract('epoch' from begin_date),extract('epoch' from current_timestamp),row_entry_date,author,reason from sensor_status where sensor_id=$sensorid;}; 
            $sth = $dbh->prepare($sql);
            $sth->execute();
            my $previous_status;
            my $previous_entrydate;
            my $previous_mdate;
            my $previous_author;
            my $previous_reason;
            while (my @row1 = $sth->fetchrow_array) {  
                $previous_status=$row1[0];
                $previous_mdate=$row1[1];
                $previous_entrydate=$row1[4];
                $previous_author=$row1[5];
                $previous_reason=$row1[6];
                my $temp=($row1[3]-$row1[2])/3600;
                if(!defined $latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{sensorid}{$sensorid}{m_date}){
                   if($row1[0]==2){
                       #older than one year, change status to archival
                       if($temp>8760){
                            push(@sensor_sql,"update sensor_status set status=4,row_update_date=current_timestamp where sensor_id=$sensorid");
                            push(@sensor_sql,"update sensor set active=4,row_update_date=current_timestamp where row_id=$sensorid");
                            push(@sensorarchive_sql,"update sensor_status_archive set status=4,row_update_date=current_timestamp where sensor_id=$sensorid and begin_date='$row1[1]'");
                   
                       }
                   }               
                }
            }
            $sth->finish();
            my $temp_age=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{sensorid}{$sensorid}{age};
            my $temp_name=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{sensorid}{$sensorid}{sensorname};
            my $m_date=$latest_status{operator_list}{$operatorid}{platform_list}{$platformid}{sensorid}{$sensorid}{m_date};
            my $temp_date="null";
            if($m_date gt "1990-01-01 12:01:02"){$temp_date="'$m_date'";}
            if(($temp_age<4)&&($temp_age>-150)){
                 
                 if((defined $previous_status)&&($previous_status!=1)){
                    if(($ARGV[0] =~/all/i)||(grep $_ eq $orga_name,@temp_organization)){
                      $email_content .="$platform $temp_name resumed reporting to SECOORA database.\n";    
                      $orga_emailcontent .="$platform $temp_name resumed reporting to SECOORA database.\n";}   
                      if($previous_mdate){ 
                         push(@sensorarchive_sql,"update sensor_status_archive set row_update_date=current_timestamp, end_date=$temp_date,author='$previous_author',reason='$previous_reason' where sensor_id=$sensorid and begin_date='$previous_mdate'"); 
                       }
                       else {
                         push(@sensorarchive_sql,"update sensor_status_archive set row_update_date=current_timestamp, end_date=$temp_date, author='$previous_author', reason='$previous_reason' where sensor_id=$sensorid and row_id in (select max(row_id) from sensor_status_archive where sensor_id=$sensorid)"); 
                       }
                       push(@sensor_sql," delete from sensor_status where sensor_id=$sensorid"); 
                       push(@sensor_sql,"update sensor set active=1,row_update_date=current_timestamp where row_id=$sensorid"); 
                 }                   
            }
            if($temp_age>=4){
                 if(!defined $previous_status){
                    if(($ARGV[0] =~/all/i)||(grep $_ eq $orga_name,@temp_organization)){
                       $email_content .="$platform $temp_name stopped reporting to SECOORA database.\n";    
                       $orga_emailcontent .="$platform $temp_name stopped reporting to SECOORA database.\n";}    
                       if($temp_date ne "null"){
                          push(@sensorarchive_sql,"insert into sensor_status_archive(platform_id,sensor_id,status,begin_date,row_entry_date) values($platformid,$sensorid,2,$temp_date,current_timestamp)"); 
                          push(@sensor_sql,"delete from sensor_status where sensor_id=$sensorid"); 
                          push(@sensor_sql,"insert into sensor_status(platform_id,sensor_id,status,begin_date,row_entry_date,sensor_name) values($platformid,$sensorid,2,$temp_date,current_timestamp,'$temp_name')"); 
                          push(@sensor_sql,"update sensor set active=2,row_update_date=current_timestamp where row_id=$sensorid"); 
                       }
                 }  
            }
            if(!defined $temp_age){

                 if(!defined $previous_entrydate){
                       push(@sensorarchive_sql,"insert into sensor_status_archive(platform_id,sensor_id,status,row_entry_date) values($platformid,$sensorid,0,current_timestamp)"); 
                       push(@sensor_sql,"insert into sensor_status(platform_id,sensor_id,status,row_entry_date,sensor_name) values($platformid,$sensorid,0,current_timestamp,'$temp_name')"); 
                       push(@sensor_sql,"update sensor set active=0,row_update_date=current_timestamp where row_id=$sensorid"); 
                 }  
            }            
         }#end of sensor_id
        
    }#end of platform
 } #end of organization

if($orga_emailcontent gt ""){
   if($orga_email gt ""){
      my $subject="[secoora_auto_alert] SECOORA Platform Sensor Status Update";
      $orga_emailcontent .="\nClick http://$ip/xenia/status/platformStatus.php?member=1&&provider=$operatorid to view and update platform/sensor status.\n";
      sendAlarm($orga_emailcontent,$subject,$orga_email);
   }
}

if(lc($sensor) eq "sensor"){
 if($email_content ne ""){
      my $subject="[secoora_auto_alert] SECOORA Platform Sensor Status Update";
      $email_content .="\nClick http://$ip/xenia/status/platformStatus.php?member=1 to view and update platform/sensor status.\n";
      sendAlarm($email_content,$subject,$email_to_secooradm);
 }
}
 #update sensor_status table
 for(my $a=0;$a<@sensor_sql;$a++){
     $sth = $dbh->prepare( $sensor_sql[$a] );
     $sth->execute();
     $sth->finish();
#     print "$sensor_sql[$a]\n";
 }
 #update sensor_status_archive table
 for(my $a=0;$a<@sensorarchive_sql;$a++){
     $sth = $dbh->prepare( $sensorarchive_sql[$a] );
     $sth->execute();
     $sth->finish();
#     print "$sensorarchive_sql[$a]\n";
 }
 

sub sendAlarm {
 
  my ($content,$subject,$to)= @_;

# $content="I am testing\n";
# $subject="testing\n"; 
  my %mail=(
             smtp => $email_server,
             to    =>"$to",
             from  =>$email_sender,
             subject => "$subject",
             message => "Automated messages created by SECOORA data processing system. \n $content"
          );

   if(!sendmail(%mail)) {
        print "Mail not send out $Mail::Sendmail::error\n";
   }

}

