#!/usr/bin/perl

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

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

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

my $sql = (
    qq{
SELECT
	COUNT(DISTINCT a.album_id),
	COUNT(DISTINCT t.track_id)
FROM
	album a
LEFT JOIN
	track t USING (album_id)
WHERE 1
	AND a.status=1
}
);

## add or change email list here
my $recipients = [ qw/
    cbreindel@theorchard.com
    cheryl@royaltyshare.com
    jgasson@theorchard.com
    Royaltyshareinvoices@theorchard.com
    onunez@theorchard.com
    oscar@royaltyshare.com
/ ];

my $contents   = "RS Customer Name, RS Customer ID, Active Album Count, Active Track Count\n";

my $data;
my $clients = Common::Session::getclients;
foreach my $host ( sort keys %{ $clients->by_host } ) {
    my $dbh = Common::DB::Connect->connect($host);
    foreach my $client ( sort @{ $clients->by_host->{$host} } ) {
        next if $client->is_test;
        next if $client->client_type ne 'RPS';
        eval {
            $dbh->do( "USE " . $client->dbname );
            my $rows = $dbh->selectall_arrayref($sql);
            if ($rows) {
                my $name = $client->get_client->client_name;
                my $id   = $client->get_client->client_id;
                foreach my $row ( @{$rows} ) {
                    my $line = csv_build( ( $name, $id, @{$row} ) );
                    $data->{ lc($name) } = $line;
                }
            }
        };
        if ($@) {
            logMessage( 'error', $@ );
        }
    }
}

foreach my $k ( sort keys %{$data} ) {
    $contents .= $data->{$k} . "\n";
}

my $date     = DateTime->now->ymd('');
my $filename = "/tmp/Active_AlbumTrack_Count_By_Customer_${date}.csv";
my $fh       = FileHandle->new( $filename, 'w' );
$fh->print($contents);
$fh->close;

eval {
    my $mail = Common::Amazon::Mail->new;
    $mail->to($recipients);
    $mail->cc('sysadmin@royaltyshare.com');
    $mail->subject("Active_AlbumTrack_Count_By_Customer_${date}");
    $mail->body(
"\nThe attached file provides a list, by customer, of current active albums and tracks in their website at the time the report was generated.\n"
    );
    $mail->sendit( [$filename] );
};
if ($@) {
    logMessage( 'error', $@ );
}

unlink $filename;