#!/usr/bin/perl
# This script analyzes all Vector-integrated RPS clients, checks open catalog imports
# and reports the number of days since the import started.  If an import is taking
# more than a day then it's likely stalled and should be addressed.
#
# We also check for unprocessed (pending) DDEX XML files sitting in each client's
# ingestion directory under /app/shared/ddex_import.  If the number of files continues
# to grow without any active imports, this could signal an internal DDEX import issue
# that needs addressing.
#
# To get the list of Vector-integrated clients, we pull the list of clients from the
# /app/admin/orchardimport script (located on rpsapp05).
#
# Why 'orchardimport' instead of RSCOMMON.orchard_client?
#   The RSCOMMON.orchard_client ddex_source/ddex_dest fields (used for copying files from
#   S3 to the RPS DDEX ingestion directory) are currently _only_ on awstaging (the production
#   orchard_client table does not have these columns).  This script is intended to be run on
#   rpsapp05, which has no visibilty into awstaging, hence the use of 'orchardimport'.
#
# Usage:
#   ./catalog_report.pl                -- analyze catalog import for Vector clients and email report
#   ./catalog_report.pl -c clientID    -- analyze catalog import for  a specific client
#   ./catalog_report.pl -a             -- analyze catalog import for all clients
#   ./catalog_report.pl -n -d          -- view a detailed report of all errors (without generating an email):
#   ./catalog_report.pl -n -d -f somefile  -- same as above, but use 'somefile' instead of 'orchardimport'
#
# Options:
#   "-c clientID"   analyze specified clientID
#   "-n"  suppress the outgoing email (use this for testing)
#   "-d"  will include reason(s) an import is stalled (may be verbose; use with "-n" to
#         prevent large emails)
#   "-a"  analyze all RPS clients, not just those with Vector integration
#   "-f somefile"   use 'somefile' instead of /app/admin/orchardimport
#
use strict;

use Sys::Hostname;
use Getopt::Std;

use Data::Dumper;

$| = 1;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Email;

use constant kOrchardImport => '/app/admin/orchardimport';  # rpsapp05; accessible by root


my %opt;
getopts('c:anf:d', \%opt);

binmode STDOUT, ":utf8";

my $clientID   = $opt{c};  # optional: scan specified clientID
my $allClients = $opt{a};  # optional: scan all RPS clients
my $noEmail    = $opt{n};  # suppress outgoing email if testing from command line
my $showErrors = $opt{d};  # show import errors

my $inputFile  = $opt{f} || kOrchardImport;

my $appSingleton = Common::RSApp->new( clientID => 0 );
my $cdbo         = Common::RSApp::GetCommonDB();


# Get the set of client data from StaticList.yml
#
my %clientMap = Common::RSDB::StaticList::clientDB();


# Get the client name info from RSCOMMON
#
my %clientName;
my $sql = "SELECT client_id, client_name, client_name_clean, web_alias FROM client";
my $sth = $cdbo->DoCmd($sql);
while( my( $id, $name, $nameClean, $webAlias ) = $sth->fetchrow_array() ) {

    my $vhost = (defined $webAlias) ? $webAlias : $nameClean;

    $clientName{$id}{name}  = $name;
    $clientName{$id}{vhost} = $vhost;
}


# Get the set of Vector clients
my %vectorClient;
open my $file,  "<", "$inputFile" or die "$inputFile: $!";
while (my $record = <$file>) {

    chomp $record;
    next if ( $record =~ /^#/ );

    # Each line in orchardimport has the following format:
    #
    # /app/tools/rps/bin/misc/ddex_import.pl -c 410 -d /app/shared/ddex_import/sgd
    #                                           ^^^                            ^^^
    #                                         clientID                     clientName
    #
    if ( $record =~ /.* -c (\d+) -d (.*)$/ ) {
        my $_clientID = $1;
        my $ddexDir   = $2;

        # Count number of XML files in the client's ingestion queue. These
        # are the files yet to be imported.
        #
        my $count=0;
        if ( -e $ddexDir ) {
            opendir(my $dh, $ddexDir) or die "opendir($ddexDir): $!";
            while (my $de = readdir($dh)) {
                next if $de =~ /^\./ or $de !~ /\.xml$/;
                $count++;
            }
            closedir($dh);
        } else {
            print "ERROR: clientID $_clientID ddex directory '$ddexDir' not found !!!\n";
        }
        $vectorClient{$_clientID} = $count;

    } else {
        print "ERROR: unable to parse '$record' !!!\n";
    }
}# while
close $file;


my @clients;
if ( $clientID ) {
    push @clients, $clientID;
} else {

    foreach my $_clientID ( sort { $a cmp $b } keys %clientMap ) {
        my $_host   = $clientMap{$_clientID}{host_id};
        my $_dbname = $clientMap{$_clientID}{db_name};
        my $_server = $clientMap{$_clientID}{server};

        # We only want to look at RPS clients
        if( $_server =~ /^rpsdb/ && $_dbname =~ /^C_/ ) {
            push @clients, $_clientID if ( exists $vectorClient{$_clientID} || $allClients || $clientID );
        }
    }

}

my @report;
foreach my $_clientID (@clients) {

    my $name  = $clientName{$_clientID}{name};
    my $vhost = $clientName{$_clientID}{vhost};

    my $appSingleton = Common::RSApp->new_temporary( clientID => $_clientID );
    my $dbo          = Common::RSApp::GetClientDB();

    my $sql = qq/
        SELECT
          content_import_id,
          store_date,
          reject_date,
          date_created,
          DATEDIFF(NOW(), date_created) AS 'days'
        FROM content_import
        ORDER BY 1 DESC LIMIT 1
        /;
    my $sth = $dbo->DoCmd($sql);
    if ( $sth->rows > 0 ) {
        while ( my( $importID, $storeDate, $rejectDate, $dateCreated, $daysActive ) = $sth->fetchrow_array() ) {
            if ( !$storeDate && !$rejectDate ) {
                my $url = "https://$vhost.royaltyshare.com/rps/distribution";

                my $count  = $vectorClient{$_clientID};
                my $suffix = ($count != 1) ? 'files' : 'file';
                my $nFiles = $count . ' ' . $suffix;

                my $decorator = ($daysActive == 1 ) ? "day" : "days";

                push @report, "$url - content_import_id $importID ($daysActive $decorator, " . $nFiles. ", started $dateCreated)";

                if ( $showErrors ) {
                    my $sql2 = qq/
                        SELECT
                            (cd.content_import_data_id - (SELECT MIN(content_import_data_id) FROM content_import_data) + 1 ) AS "line-number",
                            cs.ref_column,
                            cs.type,
                            cs.message
                            FROM content_import ci
                            JOIN content_import_data cd ON (cd.content_import_id = ci.content_import_id)
                            JOIN content_import_data_status cs ON( cs.content_import_data_id = cd.content_import_data_id)
                        /;
                    my $sth2 = $dbo->DoCmd($sql2);
                    while( my($lineNumber, $refColumn, $errType, $errMessage) = $sth2->fetchrow_array() ) {
                        push @report, "  line($lineNumber) $refColumn $errMessage";
                    }
                    push @report, '';
                }

            }
            else {
                # If there's a store and reject date, then there isn't an active import and any DDEX files sitting in
                # the ingestion directory are pending.  Normally pending files are processed (and removed) during the
                # daily DDEX ingestion, however it's possible for the DDEX ingestion to error out in such a way that
                # prevents a catalog import from dying in a way that we can detect via the store and rejection date
                # method utilized above.
                #
                # Hence, if the number of unprocessed files continues to grow and no new catalog (or catalog updates)
                # is occurring, then you should check for a DDEX-related ingestion issue (see ddex_import.pl).

                my $url = "https://$vhost.royaltyshare.com/rps/distribution";

                my $count  = $vectorClient{$_clientID};
                my $suffix = ($count != 1) ? 'files' : 'file';

                if ( $count > 0 ) {
                    push @report, "$url - No active import found, $count unprocessed $suffix detected\n";
                }
            }
        }
    }
}

if ( @report ) {

    #my $to      = 'sysadmin@royaltyshare.com';
    my $to      = 'esongalia@theorchard.com, cbreindel@theorchard.com, jgasson@theorchard.com';
    my $from    = 'do-not-reply@royaltyshare.com';
    my $subject = "RPS Open Catalog Imports";

    my $body;

    $body .= "The following client(s) have unprocessed DDEX data:\n\n";
    foreach my $r (@report) {
        $body .= "$r\n";
    }
    $body .= "\n####";

    if ( $noEmail ) {
        print "$body\n";
    } else {
        Common::Email->SendAWS(
            to      => $to,
            from    => $from,
            subject => $subject,
            body    => $body
        );
    }

} else {
    print "No open RPS catalog import(s) found\n";
}