#!/usr/bin/perl

use constant 'TIMEZONE' => 'America/Los_Angeles';

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

use DateTime;
use DateTime::Format::MySQL;

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

my $errors;
my $email_body;

eval {
    my $dbh  = Common::Session::getdbh('admindb');
    my $href = $dbh->selectall_hashref(
        qq{
SELECT
	xx.d_db_id AS id,
	xx.hostname,
	xx.db_name,
	xx.db_backup,
	max(sys.backup_datetime) backedup,
	TIMESTAMPDIFF(HOUR, max(sys.backup_datetime), NOW()) hoursago
FROM
	sys_db_backup sys
	JOIN (
		SELECT
			d_db.*, aws.hostname
		FROM
			d_db
		JOIN
			d_db_host dbh ON dbh.d_db_host_id=d_db.d_db_host_id
		JOIN
			d_aws_host aws ON aws.d_aws_host_id=dbh.d_aws_host_id
			LEFT JOIN sys_db_backup bu ON bu.d_db_id = d_db.d_db_id
				AND bu.backup_datetime > NOW() - INTERVAL 30 HOUR
		WHERE
			d_db.db_backup = 1
			AND bu.d_db_id IS NULL) xx ON xx.d_db_id = sys.d_db_id
GROUP BY
	sys.d_db_id
ORDER BY
	xx.db_name
}, 'id'
    );
    if ( defined $href and scalar keys %{$href} ) {
        foreach my $id ( keys %{$href} ) {
            push @{$errors}, $href->{$id};
        }
    } else {
        logMessage( 'info', 'All database have backups within 30 hours' );
    }
};
if ($@) {
    $email_body .= "<pre>$@</pre>\n";
}

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

if ( defined $email_body ) {
    my $amz_mail = Common::Amazon::Mail->new('html');
    $amz_mail->to( 'sysadmin@royaltyshare.com' );
    $amz_mail->cc( $amz_mail->list->system );
    $amz_mail->subject('CRON: DB BACKUP CHECK ERROR');
    $amz_mail->body($email_body);
    $amz_mail->sendit;
}

sub days_since {
    my $row  = shift;
    my $then = DateTime::Format::MySQL->parse_datetime( $row->backup_datetime );
    my $now  = DateTime->now;
    $now->set_time_zone(TIMEZONE);
    my $delta = $now->delta_days($then)->in_units('days');
    $delta;
}

__END__
        undef,
        {
            'select' =>
              [ 'd_db_id', { 'max' => 'sys_db_backup_id' }, { 'max' => 'backup_datetime' }, \'DATEDIFF(NOW(),MAX(backup_datetime))' ],
            'as' => [
                qw/
                  d_db_id
                  sys_db_backup_id
                  backup_datetime
                  ds
                  /
            ],
            'group_by' => [qw/d_db_id/]
        }