#!/usr/bin/perl

use strict;
use warnings;

use DateTime;
use Text::CSV::Easy(qw/csv_build/);

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

my ( $opt, $usage ) = clopt(
    [ 'nomail|n'    => 'Do not mail file - mainly for testing' ],
    [ 'verbose|v'   => 'Output lines to STDOUT' ],
    [ 'outfile|o=s' => 'Filename for CSV file' ],
    [ 'help|?|h'    => "print usage message and exit" ]
);
if ( $opt->help ) {
    print $usage->text;
    exit 0;
}

my $date   = DateTime->now->ymd('');
my $header = csv_build( ( 'Client ID', 'Client Name', 'URL', 'UserEmail', 'Username', 'LastLogin', 'DaysSince' ) );
my $lines  = "Last Login,$date\n$header\n";
print "$lines" if $opt->verbose;

my $dbh  = Common::Session::getdbh('rscommon');
my $rows = $dbh->selectall_arrayref(&sql);

foreach my $r ( @{$rows} ) {
    my ( $id, $email, $username, $last_login, $days_since ) = @{$r};
    my $client = Common::RSDB::Client->new($id);

    next if $client->is_test;
    next if $client->password eq '' or not defined $client->password;

    my $clientUrl  = $client->db->url;
    my $clientName = $client->db->client_name;

    my $line = csv_build( ( $id, $clientName, $clientUrl, $email, $username, $last_login, $days_since ) );
    print "$line\n" if $opt->verbose;
    $lines .= "$line\n";
}

my $name     = "/tmp/LastLoginByClient_${date}.csv";
my $filename = defined $opt->outfile ? $opt->outfile : $name;
my $fh       = FileHandle->new( $filename, 'w' );
$fh->print($lines);
$fh->close;

if ( $opt->nomail ) {
    print "\nEnd no mail sent\n" if $opt->verbose;
} else {
    my $mail = Common::Amazon::Mail->new;
	my $body =<<"!";
The attached report has the last session login and days since for clients. This only includes clients who have had a session within the last year. Clients who haven't created a session within a year are filtered out of this report - assuming they're no longer clients.

!
    $mail->to( ( 'jgetter@theorchard.com' ) );
    $mail->subject("CRON: Last Login by Client Week of $date");
    $mail->body("\n$body");
    $mail->sendit( [$filename] );
    print "\nEnd mail sent\n" if $opt->verbose;
}

unlink $filename;

sub sql {
    my $sql = (
        qq{
SELECT * FROM (
	SELECT
		c.client_id,
		u.email,
		CONCAT_WS(' ',u.first_name,u.last_name) username,
		MAX(s.last_access) last_login,
		ABS(TIMESTAMPDIFF(DAY,NOW(),MAX(s.last_access))) days_since
	FROM
		RSCOMMON.`session` s
	JOIN
		RSCOMMON.`client` c USING(client_id)
	JOIN
		RSCOMMON.`user` u ON u.user_id=s.active_user_id
	GROUP BY s.client_id
) xx
WHERE 1
	AND xx.days_since < 360
ORDER BY 
	xx.days_since DESC
}
    );
    $sql;
}