#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use Date::Calc qw(Days_in_Month);
use POSIX qw(strftime);
use File::Basename;
use Getopt::Long;

use Net::Amazon::S3;
use Net::Amazon::S3::Client;
use Net::Amazon::S3::Bucket;

use lib '/app/tools/raptor/lib';
use Raptor::Config;
use Raptor::Tracker::Job::ImportOrchardSalesFile;

use lib '/app/tools/rps/lib';
use RPS::File::File;

use lib '/app/tools/data_classes/lib';
use File::File;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Util qw( clean );
use Common::Log;
use Common::DB::Item::OrchardClient;
use Common::DB::Item::OrchardReport;
use Common::OrchardFetcher::OrchardPeriod;
use Common::Amazon::Config;

my $prog = basename($0);

my %args;
parseCommandLine( \%args );

# By default, we'll process the entire accounting statement as received from
# The Orchard.  If the "-t" option is present, then all downloaded reports
# will be truncated to 100 lines before we start importing into RPS.
# THIS OPTION IS FOR TESTING ONLY.
#
my $clientID = $args{clientID};
my $truncate = $args{truncate};

my $logLevel = $args{logLevel};
Log->init( level => 'debug' );
Log->setLogLevel($logLevel) if ($logLevel);

my @orchardClients;

# We need a reference to RSApp in order to connect to RSCOMMON
#
my $appSingleton = Common::RSApp->new( clientID => 202 );    # 202 = RSTEST
my $cdbo         = Common::RSApp::GetCommonDB();
my $dbh          = $cdbo->DBH;

# Get the Orchard client info from RSCOMMON ...
#
my %clientInfo = ();
my $coll       = Common::DB::Item::OrchardClient->GetAll();
while ( $coll->hasNext() ) {
    my $c = $coll->next;

    next if( $c->status == 0 ); # skip clients with integration disabled

    $clientInfo{ $c->client_id }{ $c->account_id }{user_id}       = $c->user_id;
    $clientInfo{ $c->client_id }{ $c->account_id }{status}        = $c->status;
    $clientInfo{ $c->client_id }{ $c->account_id }{scan_schedule} = $c->scan_schedule;
    $clientInfo{ $c->client_id }{ $c->account_id }{start_period}  = $c->start_period;
}

my @clients;
if ( !$clientID ) {  
    if ( Common::RSApp::IsProductionServer() ) {  

        # connect to wherever RSTEST is and grab the client DBs from there
        #my $appSingleton = Common::RSApp->new( clientID => 202 );
        my $dbo          = Common::RSApp::GetClientDB();

        my $sql = "SHOW DATABASES LIKE 'C_%'";
        my $sth = $dbo->DoCmd($sql);
        while( my($dbname) = $sth->fetchrow_array() ) {  
            my $id = Common::RSDB::DBNameToClientID($dbname);

            push @clients, $id  if ( exists $clientInfo{ $id } );
        }    

    # let's try the API
    } else {
        # this is a QA/dev box -- grab all of the local databases
        my @_clients = Common::RSDB::GetLocalClientIDs();
        foreach my $id (@_clients) {
            push @clients, $id  if ( exists $clientInfo{ $id } );
        }
    }    
} else {
    push @clients, $clientID;
}

# Scan S3 for active sales reports

my %periodMap;
my $sql = "SELECT period_id, year, quarter, month FROM orchard_period";
my $sth = $cdbo->DoCmd($sql);
while ( my ( $periodID, $year, $quarter, $month ) = $sth->fetchrow_array() ) {
    $periodMap{$periodID} = join( '-', $year, $quarter, $month );
}

my %quarterMap = (
    '1' => 'Q1', '2' => 'Q2', '3' => 'Q3', '4' => 'Q4'
);
my %monthMap = (
    '1' => 'Jan', '2' => 'Feb', '3' => 'Mar', '4' => 'Apr',
    '5' => 'May', '6' => 'Jun', '7' => 'Jul', '8' => 'Aug',
    '9' => 'Sep', '10' => 'Oct', '11' => 'Nov', '12' => 'Dec',
);

my %clientToAccountIDMap;  # maps RPS clientIDs to their Orchard accountIDs     {clientID} -> [ accountID, [accountID]]
my %accountToClientIDMap;  # maps Orchard accountIDs to their associated RPS clientID.  1-to-1
my $sql = "SELECT account_id, client_id, start_period, scan_schedule FROM orchard_client WHERE status = 2";  # assumes Orchard account belongs to ONLY one client
my $sth = $cdbo->DoCmd($sql);
while ( my ( $accountID, $clientID, $startPeriod, $schedule ) = $sth->fetchrow_array() ) {
    die("Account $accountID assigned to more than one RPS client !!!") if ( exists $accountToClientIDMap{$accountID} );
    # Record the RPS client, start period and schedule for the Orchard account
    $accountToClientIDMap{$accountID} = join( '-', $clientID, $startPeriod, $schedule );

    # keep track of all the accounts tied to the RPS client
    push @{$clientToAccountIDMap{$clientID}}, $accountID;
}


Log->debug( "###### Setting up S3 connection ..." );

# Setup S3 connection
#
my ( $access, $secret, $region ) = get_aws_creds('orch-transfer');    # See Common/Amazon/Config.yml

my $s3 = Net::Amazon::S3->new(
    aws_access_key_id     => $access,
    aws_secret_access_key => $secret,
    retry                 => 1,
);
my $response;

my $s3bucket = 'snowflake-sme-abacus-export';

Log->debug( "Scanning bucket ..." );

# Get the bucket containing the Abacus files
#
my $bucket = $s3->bucket($s3bucket);

print "### Using S3 '$s3bucket' bucket ...\n";

# Query the bucket.  We limit the search to the patch starting with "export/" to avoid
# having to look at anything else that may be in the bucket.
#
$response = $s3->list_bucket_all( { bucket => $s3bucket, prefix => 'export/3' } ) or die $s3->err . ": " . $s3->errstr;

my $nkeys = scalar @{ $response->{keys} };
print "  >> Found $nkeys key(s)\n";


# The only way to detect if there's an issue is if no keys are present.  If
# that's the case then assume that our credentials (Common/Amazon/Config.yml)
# are out of date.
if ( !exists $response->{keys} ) {
    die("NO BUCKET KEYS (FILES) FOUND -- CHECK YOUR BUCKET CREDENTIALS !!!");
}

my %vendorMap; # hash of vendorIDs to vendor names

my %vendorCount;
my $skippedVendors = 0;
my $numlookedat    = 0;  # nmber of keys looked at

Log->info(">>> Scanning S3 bucket for available files ...");

# Scan the S3 bucket.  The goal here is to create a hash containing the listing of the
# period/vendor/contract files in S3.
#
my %abacusMap;

for my $key ( @{ $response->{keys} } ) {
    my $s3path = $key->{key};

    my $_lastModified = $key->{last_modified};
    my $lastModified; # this gets pre-pended to the RPS filename

    # Example lastModified:  2025-05-21T 14:56:20.000Z    (ISO 8601 format?)
    #  Convert this to a RPS filename-friendly format:   20250525_1456

    if ( $_lastModified =~ /^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})/ ) {
        my ($year, $month, $day, $hour, $min, $sec) = ( $1, $2, $3, $4, $5, $6 );
        $lastModified = $year . $month . $day;
    } else {
        die("Unable to decode last modified: $_lastModified");
    }

    # We only want to look at CSV files in the bucket.  They're compressed, so look
    # for a ".gz" extension.
    # Note: the files are really in TSV format; we'll address this when we transform
    # the cryptic Snowflake filename into a human-readable name for RPS.

    my $filename = basename $s3path;

    if ( $s3path =~ /^export\/(\d+)\/(\d+)\/(\d+)\/(.+)\.csv\.gz$/i ) {
        $numlookedat++;

        my( $period, $vendorID, $contractID, $_file ) = ($1, $2, $3);

        #--------------------------------------------
        # Do we have the vendor info for this period?
        #--------------------------------------------
        if ( keys(%vendorMap) == 0 ) {
            my $localVendorFile = '/tmp/vendors.csv';
            my $vendorFile      = "export/$period/vendors.csv";
            print "  ### Copying S3 $vendorFile to $localVendorFile\n";
            # Grab the file from S3 and then parse it

            my $_response = $bucket->get_key_filename( $vendorFile, 'GET', $localVendorFile );
            open my $fh, '<', $localVendorFile or die "Could not open $localVendorFile $!";
            while( my $line = <$fh> )  {   
                chomp $line;
                if( $line =~ /(\d+),(.*)/ ) {
                    $vendorMap{$1}{name}       = $2;
                    $vendorMap{$1}{clean_name} = clean($2);
                }
            }
            close $fh;
        }


        # Process the path.  Ultimately we're going to copy the S3 file (which is in a
        # non-friendly format) to an RPS-friendly file name so that we can import it.
        # 

        my( $year, $quarter, $monthVal ) = split('-', $periodMap{$period});

        my $monthStr   = $monthMap{$monthVal} . $year;

        my $vendorName = $vendorMap{$vendorID}{clean_name};

        #  The old Workstation-based files are named in the following way:
        #
        #  Created Date / Period Date / "Full Report” / Vendor Name /File formatting
        #  e.g
        #  20250619_May2025_fullreport_black_river_entertainment_US.txt (edited) 
        #
        #  For the new Abacus-based files, the following structure is utilized:
        #  Created Date / Period Date / "Full Report”/ Vendor Name/ Vendor ID / Contract ID
        #  e.g
        #  20250619_May2025_fullreport_black_river_entertainment_22240_44887.txt
        #
        my $_clientID;
        my $schedule;
        my $startPeriod;
        my $rpsFilename;

        # Generate the RPS-friendly filename.  We only do this if the Ochard vendorID is
        # associated with an RPS client.
        #
        if ( exists $accountToClientIDMap{$vendorID} ) {
            my $_map = $accountToClientIDMap{$vendorID};
            ( $_clientID, $startPeriod, $schedule ) = split('-', $_map );

            # NOTE: The Abacus files are generated with a csv suffix, however they are really tab-delimited.
            # We'll change the suffix on the RPS filename.
            #
            $rpsFilename = $lastModified . '_' . $monthStr . '_fullreport_' . $vendorName . '_' . $vendorID .'_'. $contractID . '.tsv.gz';

        }

        if ( $_clientID ) {
            #print "D: path($s3path)  lastModified($lastModified)\n";
            #print "   file($filename) --> rpsFilename($rpsFilename)\n";
            $vendorCount{processed}++;
            push @{$abacusMap{$period}{$vendorID}}, { contract_id => $contractID, source => $s3path, target => $rpsFilename };
        } else {
            print "   path($s3path) -- vendor($vendorID) not defined in orchard_client ... skipping\n";
            $skippedVendors++;
            push @{$vendorCount{skipped}}, $vendorID;
        }
        $vendorCount{total}++;

        print "   path($s3path) -> file($rpsFilename)\n" if ( $rpsFilename );

    } # s3path processing
} # bucket key loop

# At this point, abacusMap contains a list of all the S3 files that are eligible for import.
# For each period, we maintain a list of hashes containing file information for each vendor.
# We keep track of the source path (where to find the file in S3), the workstation contract
# information (contract_id) and the target RPS filename.
#

print "#### Skipped $skippedVendors vendor(s) from bucket\n";
print ">> Looked at $numlookedat key(s) out of $nkeys\n";
print "D: vendorCount ". Dumper(\%vendorCount) . "\n";

Log->info(">>> Finished scanning S3 -- Processing RPS clients ...");


my $config     = Raptor::Config->new();
my $scriptPath = $config->get('sale_import_script_dir');

my $currentDate = strftime "%Y-%m-%d", localtime;
my ( $currentYear, $currentMonth, $currentDay ) = split( '-', $currentDate );

my %periodInfo = Common::OrchardFetcher::OrchardPeriod::getPeriodHash($currentYear);

Log->info("$prog: ####### Scanning Orchard accounts on $currentDate");

undef $appSingleton;

foreach my $_clientID ( @clients ) {

    # Need the singleton so that we can import files on the client's behalf
    #
    my $appSingleton = Common::RSApp->new( clientID => $_clientID );
    my $dbo = Common::RSApp::GetClientDB();

    if ( !exists $clientInfo{$_clientID} ) {
        print "Client $_clientID not configured for Orchard sales integration .. skipping\n";
        next;
    }

    my $accountInfo = $clientInfo{$_clientID};

    foreach my $accountID ( sort { $a <=> $b } keys %$accountInfo ) {

        print "   ----- checking account $accountID\n";
        my $userID      = $clientInfo{$_clientID}{$accountID}{user_id};
        my $status      = $clientInfo{$_clientID}{$accountID}{status};
        my $schedule    = $clientInfo{$_clientID}{$accountID}{scan_schedule};
        my $startPeriod = $clientInfo{$_clientID}{$accountID}{start_period};
        if ( $status == Common::DB::Item::OrchardClient::kStatusIgnore ) {
            Log->info("fetch_abacus.pl: --- Skipping client $_clientID  account $accountID  user $userID  status $status  schedule $schedule");
            next;
        } else {
            Log->info("fetch_abacus.pl: --- Scanning client $_clientID  account $accountID  user $userID  status $status  schedule $schedule");
        }

        foreach my $id ( sort { $a <=> $b } keys %periodInfo ) {
            my $period     = $periodInfo{$id}{period};
            my $periodName = $periodInfo{$id}{period_name};
            my $startDate  = $periodInfo{$id}{start_date};
            my $endDate    = $periodInfo{$id}{end_date};


            next if ( $startDate gt $currentDate );
            next if ( $schedule == 1 && $period =~ /,/ );    # skip quarter period if schedule is monthly
            next if ( $schedule == 2 && $period !~ /,/ );    # skip month period if schedule is quarterly

            if ( $period =~ /,/ ) {
                my ( $p0, $p1, $p2 ) = split( ',', $period );
                next if ( $p0 < $startPeriod );
                next if ( $p0 < 318 );     # Abacus reports start at period 318
            } else {
                next if ( $period < $startPeriod );
                next if ( $period < 318 ); # Abacus reports start at period 318
            }


            if ( $currentDate ge $startDate && $currentDate le $endDate ) {

                # Skip the period if the current date falls within the period
                #
                # Note: just because a date falls past a period's end date doesn't
                # mean that you will be able to successfully query or generate a report
                # for the period.  The period must be enabled or booked by the Orchard
                # in order for the accounting API to successfully perform operations
                # against it.
                #
                next;
            }

            # Setup the command to scan S3 for an Abacus sales report for the vendor/period
            #
            my $cmd = "nice $scriptPath/fetch_abacus_report.pl -c $_clientID -p $period -a $accountID -u $userID";

            $cmd .= " -t"           if ($truncate);
            $cmd .= " -l $logLevel" if ($logLevel);

            # Check if we have a local report entry for the period.  These get created
            # when we first start looking for the report, and get updated as the report
            # is processed by the ingestion process.  An RPS file_id is assigned to the
            # report once we have a file ready to import.  If we don't have a report
            # entry or if we have one without a file_id then we'll query OWS for the
            # report.
            #

            if ( exists $abacusMap{$period}{$accountID} ) {

                my @abacusFiles = @{$abacusMap{$period}{$accountID}};
                my $nfiles = scalar @abacusFiles;

                Log->info("### Client $_clientID: Scanning abacusMap for account($accountID) period($period) ... (n = $nfiles)");

                foreach my $f (@abacusFiles) {  # check the files available in S3

                    my $source     = $f->{source};
                    my $target     = $f->{target};
                    my $contractID = $f->{contract_id};

                    # Check OrchardReport to see if we already know about the file.  If we haven't downloaded it,
                    # or if we have but haven't uploaded it into the client's site then call fetch_abacus_report.pl
                    # to get the file into the client's file table.
                    # Once fetch_abacus_report.pl returns then we'll queue up an import job for the file.
                    #
                    my $report = Common::DB::Item::OrchardReport->Lookup(
                        client_id => $_clientID, account_id => $accountID, period => $period, contract_id => $contractID
                    );

                    if ( !$report || ( $report && !$report->file_id ) ) {
                        print ">> Copying S3 $source to $target for client $_clientID account $accountID (period $period)\n";

                        my $s3Path = $source;

                        $cmd .= " --contract $contractID -s $s3Path -f $target";
                        print ">> fetch_abacus.pl: Calling CMD = $cmd\n";

                        # Call fetch_abacus_report
                        # This will copy the report from S3, unzip it, and then create an orchard_report entry for it.
                        # It will also upload the file into the client's site (e.g., generate a file_id).
                        `$cmd`;
                    }

                    # Verify that the file was fetched and if so, import it
                    #
                    my $report = Common::DB::Item::OrchardReport->Lookup(
                        client_id => $_clientID, account_id => $accountID, period => $period, contract_id => $contractID
                    );

                    if ( $report && $report->file_id > 0 ) {

                        my $reportID = $report->report_id;
                        my $st       = $report->status;
                        my $fileID   = $report->file_id;


                        if ( !$fileID ) {
                            my $st = $report->status;
                            Log->warn( "fetch_abacus.pl: $_clientID : No fileID found for reportID $reportID "
                                  . "[period $period status=$st ("
                                  . Common::DB::Item::OrchardReport->printStatus($st)
                                  . ")]" );
                            next;
                        }

                        if ( $st == Common::DB::Item::OrchardReport::kFileImportReady ) {
                            my $jobArgs = Raptor::Tracker::Job::ImportOrchardSalesFile->new( fileID => $fileID );
                            my $job     = $jobArgs->enqueue();
                            my $jobID   = $jobArgs->{JobID};

                            Log->debug( "fetch_abacus.pl: created import job $jobID : " . Dumper($jobArgs) );
                            print ">>    Period $period: c($_clientID) a($accountID) contract$contractID): Importing $target (fileID $fileID)\n";

                            $report->status(Common::DB::Item::OrchardReport::kFileQueued);
                            $report->job_id($jobID);
                            $report->save();
                        } else {
                            Log->info( "fetch_abacus.pl: $_clientID : Skipping import job for reportID $reportID "
                                  . "[period $period status=$st ("
                                  . Common::DB::Item::OrchardReport->printStatus($st)
                                  . ")]" );

                            # Has the file status changed since we queued up the import job?
                            # Update the report status if so.  Setting the status to kFileImported
                            # just means that the import job finished; the actual import may (or may
                            # not) have any errors.
                            #
                            if ( $st == Common::DB::Item::OrchardReport::kFileQueued ) {
                                my $file = RPS::File::File->new( file_id => $fileID );

                                if ( $file->FileStatus == File::File::STATUS_OPEN ||
                                     $file->FileStatus == File::File::STATUS_CLOSED ) {
                                    $report->status(Common::DB::Item::OrchardReport::kFileImported);
                                    $report->notes( "file $fileID imported " . $file->Records  . " row(s) with status " . $file->FileStatus );
                                    $report->save();
                                }
                                elsif ( $file->FileStatus == File::File::STATUS_INVALID ) {
                                    $report->status(Common::DB::Item::OrchardReport::kFileImported);
                                    $report->notes( "file $fileID errored with status " . $file->Notes );
                                    $report->save();
                                }
                                elsif ( $file->FileStatus == File::File::STATUS_PROCESSING ) {
                                    # Import is in progress.  Update the number of records
                                    $report->notes( "file $fileID in progress ..." );
                                    $report->save();
                                }

                            }

                            # Since the file is queued, we can now delete or archive the original
                            # S3 file.  This will prevent us from trying to look at the file the next
                            # time we scan the S3 bucket.  Note that if the file has an orchard_report
                            # entry and an RPS file_id then we won't call fetch_abacus_report.pl.
                            my $destPath = (dirname $source) . '/' . $target;
                            $destPath =~ s/export/archive/;

                            my $cmd = 'aws s3 cp --profile=orch-transfer '
                                . 's3://'. $s3bucket . '/' . $source . ' '
                                . 's3://'. $s3bucket . '/' . $destPath;
                            print "ARCHIVE:\t$cmd\n";


                        }

                    }

                } # Abacus file report loop for vendor/period

            } else {
                Log->info("### Client $_clientID: Scanning abacusMap for account($accountID) period($period) ... (n = 0)");
            }

        }    # period loop

    }    # account loop

}    # client loop
Log->info("### Done scanning clients");

sub parseCommandLine {
    my ($a) = @_;
    my $clientID;
    my $truncate;
    my $logLevel;
    die if (
        !GetOptions(
            'c|client'     => \$clientID,
            't|truncate'   => \$truncate,
            'l|loglevel=s' => \$logLevel,
        )
    );
    $a->{clientID} = $clientID;
    $a->{truncate} = $truncate;
    $a->{logLevel} = $logLevel;
}

1;