#!/usr/bin/perl

###############################################################################
## Copyright (c)  2009-2012 SUSE LINUX Products GmbH, Nuernberg, Germany.
###############################################################################

use strict;
use warnings;
use Getopt::Long;
use SMT::Utils;
use File::Basename;
use File::Temp;
use SMT::JobQueue;
use SMT::Job::Constants;

if( ! SMT::Utils::dropPrivileges() )
{
    print STDERR __("Unable to drop privileges. Abort!\n");
    exit 1;
}

my $vblevel = LOG_ERROR|LOG_WARN|LOG_INFO1;
my $logfile = "/var/log/smt/smt-jobqueuecleanup.log";
my $debug   = 0;
my $help    = 0;
my $mail    = 0;
my $quiet   = 0;
my $mailtempfile = undef;


Getopt::Long::Configure( 'no_auto_abbrev');
my $optres = GetOptions ("debug|d"     => \$debug,
                         "verboselevel|v=i" => \$vblevel,
                         "logfile|L=s" => \$logfile,
                         "mail|m"      => \$mail,
                         "help|h"      => \$help,
                         "quiet|q"     => \$quiet
                        );


if($help || !$optres)
{
    print basename($0) . " [--logfile file] [--debug] [--mail]\n\n";
    print __("Clean up the SMT JobQueue and remove invalid or obsoleted jobs.")."\n";
    print "\n";
    print __("Options:")."\n";
    print "--mail -m                 ".__("Send output as e-mail to the administrators defined in reportEmail in smt.conf.")."\n";
    print "                          ".__("The output on stdout and stderr will be suppressed.")."\n";
    print "--quiet -q                ".__("suppress output")."\n";
    print "--debug -d                ".__("enable debug mode")."\n";
    print "--verboselevel -v <level> ".__("set the verbose level")."\n";
    print "--logfile -L <file>       ".__("Path to logfile")."\n";
    exit 0;
}

$vblevel = LOG_ERROR|LOG_WARN|LOG_INFO1|LOG_INFO2|LOG_DEBUG|LOG_DEBUG2 if($debug);


# open the logfile
my $LOG = SMT::Utils::openLog($logfile);

# read smt config
my $cfg = undef;
eval {  $cfg = SMT::Utils::getSMTConfig();  };
if( $@ || ! defined $cfg )
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_ERROR, sprintf(__("Cannot read the SMT configuration file: %s"), $@));
    die( __("Cannot read the SMT configuration file: %s"));
}

# connect to database
my $dbh = undef;
$dbh = SMT::Utils::db_connect();
if( ! $dbh )
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_ERROR, __("Cannot connect to database"));
    die( __("Cannot connect to database"));
}
my $res = undef;


if($mail)
{
  my $dir = File::Temp::tempdir("smt-XXXXXXXX", CLEANUP => 1, TMPDIR => 1);
  $mailtempfile = "$dir/mail";
  open(MAILTEMPFILE, "> $mailtempfile") or die "Cannot open file:$!";
  open(STDOUT, ">& MAILTEMPFILE") or die "Cannot dup:$!";
  open(STDERR, ">& MAILTEMPFILE") or die "Cannot dup:$!";
  select STDERR; $| = 1;      # make unbuffered
  select STDOUT; $| = 1;      # make unbuffered
  select MAILTEMPFILE; $| = 1;        # make unbuffered
}

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

sub deleteJobAndChildren($$;$);

sub deleteJobAndChildren($$;$)
{
    my $jobID = shift || return undef;
    my $clientID = shift || return undef;
    my $cleanResults = shift || 1;
    my $resJ;
    my $resR;

    # get the children of the job that is going to be deleted
    my $getChildJobs = " SELECT ID from JobQueue where GUID_ID = ? and PARENT_ID = ? ";
    my $childJobs = $dbh->selectcol_arrayref($getChildJobs, {}, ( $clientID, $jobID ));

    # delete the job first and the children later to prevent loops
    my $deleteJob = ' DELETE FROM JobQueue where ID = ? and GUID_ID = ? ';
    $resJ = $dbh->do($deleteJob, {}, ($jobID, $clientID));
    $resJ = ($resJ =~ /^0E0$/) ? 0:$resJ;

    if ($cleanResults)
    {
        my $deleteRes = ' DELETE FROM JobResults where JOB_ID = ? and CLIENT_ID = ? ';
        $resR = $dbh->do($deleteRes, {}, ($jobID, $clientID));
        $resR = ($resR =~ /^0E0$/) ? 0:$resR;
    }

    if (UNIVERSAL::isa($childJobs, 'ARRAY') && scalar @$childJobs)
    {
        foreach my $child (@$childJobs) {
            $resJ += deleteJobAndChildren($child, $clientID, $cleanResults);
        }
    }
    return $resJ >= $resR ? $resJ : $resR;
}



###############################################################################
# Cleanup JobQueue

SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  "Starting daily SMT JobQueue cleanup" , 0, 1 );
my $maxDays = $cfg->val('JOBQUEUE', 'maxFinishedJobAge') || 8;
$maxDays = 8 unless ($maxDays =~ /^\d+$/);

### DELETE OLD FINISHED JOBS (first) ###
# keep persistent jobs and jobs that are flagged cacheresult
my $delOldJobsSQL = " DELETE FROM JobQueue where DATE_ADD(FINISHED, INTERVAL ? DAY) < NOW() AND PERSISTENT = 0 AND CACHERESULT = 0 ";
$res = $dbh->do($delOldJobsSQL, {}, $maxDays);
SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted finished jobs: %s", ($res =~ /^0E0$/) ? "0":$res) , $quiet? 0:1 , 1 );


### DELETE OLD JOBS that have JobResults (they are kept longer) ###
my $tooOldCacheresultJobs = " SELECT JOB_ID, CLIENT_ID FROM JobResults where DATE_ADD(CHANGED, INTERVAL (? * 5) DAY) < NOW() or ( CHANGED IS NULL and  DATE_ADD(CREATED, INTERVAL (? * 50) DAY) < NOW() ) ";
my $cachejobs = $dbh->selectall_arrayref($tooOldCacheresultJobs, {Slice => {}}, ($maxDays, $maxDays));

if ( UNIVERSAL::isa($cachejobs, 'ARRAY') )
{
    my $delCount=0;
    foreach my $href (@$cachejobs) {
        next unless (UNIVERSAL::isa($href, 'HASH') && defined $href->{JOB_ID} && defined $href->{CLIENT_ID});
        $delCount += deleteJobAndChildren($href->{JOB_ID}, $href->{CLIENT_ID}, 1);
    }
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted jobs with jobresults (upstream jobs): %s", $delCount , $quiet? 0:1 , 1 ));
}
else
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Error when searching for old jobs with cached results."));
}

### DELETE TOO OLD JOBS
my $tooOldJobs = " SELECT ID, GUID_ID FROM JobQueue where DATE_ADD(CREATED, INTERVAL (? * 50) DAY) < NOW() and (RETRIEVED IS NULL or DATE_ADD(RETRIEVED, INTERVAL (? * 50) DAY) < NOW() ) and (EXPIRES IS NULL or DATE_ADD(EXPIRES, INTERVAL (? * 50) DAY) < NOW() ) ";
my $oldjobs = $dbh->selectall_arrayref($tooOldJobs, {Slice => {}}, ($maxDays, $maxDays, $maxDays));

if ( UNIVERSAL::isa($oldjobs, 'ARRAY') )
{
    my $delCount=0;
    foreach my $href (@$oldjobs) {
        next unless (UNIVERSAL::isa($href, 'HASH') && defined $href->{ID} && defined $href->{GUID_ID});
        $delCount += deleteJobAndChildren($href->{ID}, $href->{GUID_ID}, 1);
    }
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted old unprocessed jobs: %s", $delCount , $quiet? 0:1 , 1 ));
}
else
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Error when searching for old jobs with cached results."));
}



### DELETE JOB COOKIES (second) ###
my $delOldJobCookiesSQL = " DELETE FROM JobQueue where TYPE = 0 and DATE_ADD(CREATED, INTERVAL 1 DAY) < NOW() ";
$res = $dbh->do($delOldJobCookiesSQL);
SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted job cookies: %s", ($res =~ /^0E0$/) ? "0":$res) , $quiet? 0:1 , 1 );



### CHECK FOR UNSUPPORTED JOB TYPES (third) ###
my @alltypes = ();
my $jobtypes = SMT::Job::Constants::JOB_TYPE;
$jobtypes = {} unless UNIVERSAL::isa($jobtypes, 'HASH');
foreach my $_t ( keys %{$jobtypes} )
{
  push (@alltypes, $_t) if $_t =~ m/^\d+$/;
}
my $questionmarks = " ?," x scalar @alltypes;
$questionmarks =~ s/,$//;
my $delUnsupportedJobsSQL = " DELETE FROM JobQueue where TYPE not in ( ".$questionmarks." ) ";
$res = $dbh->do($delUnsupportedJobsSQL, undef, @alltypes);
SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted unsupported jobs: %s", ($res =~ /^0E0$/) ? "0":$res) , $quiet? 0:1 , 1 );


### CHECK PARENTS (fourth) ###

# get all dependent jobs older than one day
my $getParentDataSQL = " SELECT ID, GUID_ID, PARENT_ID from JobQueue where PARENT_ID IS NOT NULL AND DATE_ADD(CREATED, INTERVAL 1 DAY) < NOW() ";
my $pData = $dbh->selectall_hashref($getParentDataSQL, ["GUID_ID", "ID"] );

# get reference data from JobQueue to compare the filtered data with (bnc#520701)
my $JobQ = SMT::JobQueue->new({ 'dbh' => $dbh});
my $refData = $JobQ->getJobsInfo({ 'ID' => '', 'GUID' => '', 'PARENT_ID' => '' });

my @delBrokenParentsWhere = ();

foreach my $gid ( keys %{$pData} )
{
     foreach my $jid ( keys %{${$pData}{$gid}} )
     {
         if ( defined ${$pData}{$gid}{$jid}{PARENT_ID} )
         {
             # compare the found parent job with the reference data (bnc#520701)
             if ( not exists ${$refData}{$gid}{ ${$pData}{$gid}{$jid}{PARENT_ID} } )
             {
                 push(@delBrokenParentsWhere, " ( ID = $jid AND GUID_ID = ${$pData}{$gid}{$jid}{GUID_ID} ) ");
             }
         }
    }
}

my $whereStr = '';
$whereStr = join(" OR ", @delBrokenParentsWhere );

if ( defined $whereStr && $whereStr ne '' )
{
    my $delBrokenParentsSQL = " DELETE FROM JobQueue where $whereStr" ;
    $res = $dbh->do($delBrokenParentsSQL);
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number of deleted jobs with broken parent IDs: %s", ($res =~ /^0E0$/) ? "0":$res) , $quiet? 0:1 , 1 );
}
else
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  "Number of deleted jobs with broken parent IDs is: 0" , $quiet? 0:1 , 1 );
}




### CHECK PERSISTENT JOBS FOR PROPER TIMELAG [> 1 minute] (last) ###

# make all job info hashes look consistent (like JobQueue would return them) to prevent bugs like (bnc#520701)
my $getTooPersistentJobs = " SELECT  j.GUID_ID, c.GUID, j.ID, j.TIMELAG from JobQueue j LEFT JOIN Clients c ON (j.GUID_ID = c.ID) where j.PERSISTENT = 1 AND ( j.TIMELAG < '00:01:00'  OR  j.TIMELAG IS NULL ) ";
my $persData = $dbh->selectall_hashref($getTooPersistentJobs, ["GUID_ID", "ID"] );

my $numPersData = keys %{$persData} || 0;
if ( $numPersData > 0 )
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Number persistent jobs with a timelag smaller than 1 minute: %s", $numPersData ) , $quiet? 0:1,1 );

    foreach my $gid ( keys %{$persData} )
    {
        foreach my $jid (keys %{${$persData}{$gid}} )
        {
            SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  sprintf("Job ID (%s) for GUID (%s) has a timelag of (%s).", $jid, ${$persData}{$gid}{$jid}{GUID}, ${$persData}{$gid}{$jid}{TIMELAG} || 0 ) , $quiet? 0:1 ,1);
        }
    }
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  "This is just a warning. If the timelag for these jobs is not intended to be so small please change the jobs using the smt-job command." , $quiet? 0:1 ,1);
}
else
{
    SMT::Utils::printLog($LOG, $vblevel, LOG_INFO1,  "No persistent jobs with a timelag smaller than 1 minute found. Good." , $quiet? 0:1,1 );
}


### SEND MAIL ###

if ($mail)
{
  close (STDERR);
  close (STDOUT);
  close (MAILTEMPFILE);
  my $body = "";

  # we want to send only a mail, if there is something to send.
  if( -s $mailtempfile )
  {
    open(MAIL, "< $mailtempfile") and do
    {
      while(<MAIL>)
      {
        $body .= $_;
      }
      close MAIL;
    };
    my $datestring = POSIX::strftime("%Y-%m-%d %H:%M", localtime);
    my $subject = sprintf("SMT JobQueue Cleanup $datestring (%s)", SMT::Utils::getFQDN());
    SMT::Utils::sendMailToAdmins($subject, $body);
  }
}

