#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib '/app/tools/common/lib';
use Common::Session;
use Common::Amazon::Mail;

my $errors;
my $email_body;

eval {
    my $sql = (qq{ SELECT *, TIMESTAMPDIFF(HOUR,date_finished,NOW()) hoursago FROM agg_sale_log ORDER BY log_id DESC LIMIT 1 });

    my $dbh = Common::Session::getdbh('db01');
    my $dbx = Common::Session::getdbx('admindb');
    my $dbs = [ $dbx->resultset('Report::DDb')->search( { 'calc_stat' => 1 }, { 'order_by' => 'db_name' } )->all ];
    foreach my $db ( @{$dbs} ) {
        $dbh->do( 'USE ' . $db->db_name );

        my $fileCount = $dbh->selectcol_arrayref('SELECT count(*) FROM file')->[0];

        my $row = $dbh->selectall_hashref( $sql, 'log_id' );
        foreach my $id ( keys %{$row} ) {
            my $data = $row->{$id};
			$data->{'db_name'} = $db->db_name;
            my $message = sprintf( "%-32s date: %18s rebuilt: %2d", $db->db_name, $data->{'date_finished'}, $data->{'tables_rebuilt'} );
            logMessage( 'info', $message ) if $data->{'tables_rebuilt'};

            if ( $data->{'hoursago'} > 24 ) {
                logMessage( 'error', $db->db_name . " is $data->{'hoursago'} hours old -- something's wrong" );
                push @{$errors}, $data;
            }

            if ( $data->{'total_units'} == 0 && $fileCount > 0 ) {
                logMessage( 'error', $db->db_name . " stats include $data->{'total_units'} total units -- something may be wrong" );
                push @{$errors}, $data;
            }

        }
    }

};
if ($@) {
    logMessage( 'fatal', $@ );
    $email_body = $@;
}

if ($errors) {
    $email_body .= <<'!';
<table border="1"><tr>
<th>database</th><th>lastStats</th><th>hoursAgo</th><th>totalUnits</th>
</tr>
!
    foreach my $row ( @{$errors} ) {
        $email_body .= "<tr>";
        $email_body .= "<td>" . $row->{'db_name'} . "</td>";
        $email_body .= "<td>" . $row->{'date_finished'} . "</td>";
        if ($row->{'hoursago'} > 24) {
            $email_body .= "<td align=\"right\" style=\"color:red\">$row->{'hoursago'}</td>";
        } else {
            $email_body .= "<td align=\"right\">$row->{'hoursago'}</td>";
        }
        if ($row->{'total_units'} == 0) {
            $email_body .= "<td align=\"right\" style=\"color:red\">$row->{'total_units'}</td>";
        } else {
            $email_body .= "<td align=\"right\">$row->{'total_units'}</td>";
        }
        $email_body .= "</tr>";
    }
    $email_body .= "</table>\n";
}

if ( defined $email_body ) {
    my $amz_mail = Common::Amazon::Mail->new('html');
    $amz_mail->to( $amz_mail->list->system );
    $amz_mail->subject('CRON: NIGHTLY STATS CHECK ERROR');
    $amz_mail->body($email_body);
    $amz_mail->sendit;
}