#!/usr/bin/perl

use strict;
use warnings;

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

use lib '/app/tools/common/lib';
use Common::Client;
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',
        'Payees set to Manual',
        'Payees set to Online',
        'Invites Sent',
        'Waiting on Confirmation',
        'Confirmed',
        'Additional Statement Recipient Invites Sent',
        '(ASR) Waiting on Confirmation',
        '(ASR) Confirmed',
        'Note',
    )
);
my $lines = "Artist Payee Portal Status,$date\n$header\n";
print "$lines" if $opt->verbose;

my $dbx = Common::Session::getdbx('admindb');

eval {
    foreach my $clientID ( sort { $a <=> $b } keys %Common::RSDB::CLIENT_DB ) {

        my $ddb = $dbx->resultset('Report::DDb')->search( { 'client_id' => $clientID } )->first;
        next unless defined $ddb;
        next if $ddb->db_status ne 'Active';

        my $client = Common::RSDB::Client->new($clientID);

        next if $client->is_test;
        next if $client->dbname !~ m/^C_/;
        next if $client->password eq '' or not defined $client->password;

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

        if ( $client->client_cache->type_mask & Common::Client::kPayeePortal ) {
            my $row = $client->dbh->selectall_arrayref(&sql);

            my ( $numManual, $numOnline, $numInvited, $numConfirmed ) = @{ $row->[0] };

            my $note               = undef;
            my $numUnconfirmed     = 0;
            my $numAddlUnconfirmed = 0;
            my $numAddlInvited     = 0;
            my $numAddlConfirmed   = 0;

            if ($numInvited) {
                $numUnconfirmed = $numInvited - $numConfirmed;

                my $addlRow = $client->dbh->selectall_arrayref(&addlSql);
                ( $numAddlInvited, $numAddlConfirmed ) = @{ $addlRow->[0] };

                if ( not defined $numAddlInvited ) {
                    $numAddlInvited = 0;
                    $numAddlConfirmed = 0;
                }

                if ($numAddlInvited) {
                    $numAddlUnconfirmed = $numAddlInvited - $numAddlConfirmed;
                }
            } elsif ( not defined $numInvited ) {
                ( $numManual, $numOnline, $numInvited, $numConfirmed, $numUnconfirmed, $numAddlInvited, $numAddlConfirmed, $numAddlUnconfirmed ) = (qw/'- '- '- '- '- '- '- '-/);
                my $cnt = $client->dbh->selectcol_arrayref('SELECT count(*) from artist_payee')->[0];
                $note = $cnt ? 'Check artist_payee table' : 'No rows in artist_payee table';
            }

            my @data = ( $clientID, $clientName, $clientUrl, $numManual, $numOnline, $numInvited, $numUnconfirmed, $numConfirmed, $numAddlInvited, $numAddlUnconfirmed, $numAddlConfirmed,$note );
            my $line = csv_build(@data);
            print "$line\n" if $opt->verbose;
            $lines .= "$line\n";
        }
    }
};
if ($@) {
    my $err;
}

my $name     = "/tmp/artistPayeePortalStatusByClient_${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;
    $mail->to( 'sysadmin@royaltyshare.com' );
    $mail->cc( 'onunez@theorchard.com' );
    $mail->subject("CRON: Artist Payee Status by Customer Week of $date");
    $mail->body("\nThe attached report is artist payee portal status by customer");
    $mail->sendit( [$filename] );
    print "\nEnd mail sent\n" if $opt->verbose;
}

unlink $filename;

sub sql {
    my $sql = (
        qq{
SELECT
	SUM(IF(distribution=0,1,0)) manualPayees,
	SUM(IF(distribution=1,1,0)) onlinePayees,
	SUM(IF(distribution=1 AND date_invited IS NOT NULL,1,0)) allInvited,
	SUM(IF(distribution=1 AND date_confirmed IS NOT NULL,1,0)) allConfirmed
FROM
	artist_payee
}
    );
    $sql;
}

sub addlSql {
    my $sql = (
        qq{
SELECT
	SUM(IF(date_invited IS NOT NULL,1,0)) allAddlInvited,
	SUM(IF(date_confirmed IS NOT NULL,1,0)) allAddlConfirmed
FROM
	artist_payee_statement_recipient
}
    );
    $sql;
}