#!/usr/bin/perl
# Script to identify a data template to import.  If the template can be identified,
# a makefile and the relevant import script will be copied to the current directory.
#
# Usage:  rps_dataimport.pl -c clientID -f templatefile [-i importID] [-s serviceID -v versionID]
#
# where
#   clientID is the RPS client identifier that you want to import data for, and
#   templatefile is the Excel template (xls or xlsx) containing data for the import.
#   importID is a data_import_id (optional)
#
# The bulk-import templates are _mostly_ standardized, although you occasionally
# get one that's been manually edited to the point where it can't be recognized.
# For example, a column name may be a little off or it's missing a leading asterisk
# (the "*" is supposed to mean the column is required).  You can send the template
# back for correction, or manually change it to what it should be and move on.
# Sometimes a client will add columns, but those are typically for data updates,
# not imports, and are beyond the scope of this script.
#
# IMPORTANT: Before running this script, create a directory to stage your import,
# and a subdirectory inside of that directory with a datestamp.  E.g.,
#
#   cd ~/fb
#   mkdir rsd9999_rstest_import_artist_payees
#   cd rsd9999_rstest_import_artist_payees
#   mkdir 01mar19
#   cd 01mar19
#
# Then naming convention is to use the FogBugz or Jira ticket number, followed
# by a client name, followed by some sort of descrption of what's inside the
# directory.  So the above would be an artist payee import ticket (RSD-9999)
# for RSTEST.  The directory name isn't parsed, so you may sometimes see variants
# of the same thing (e.g. "import_artist_payees" and "import_artist_payee_template"
# are synonymous).
#
# For this next step, you'll need the RPS clientID.  If you need to, use rpsdb
# (e.g., "rpsdb rstest" to find the ID).  Or look in Common/RSDB/StaticList.yml.
#
#   rps_dataimport.pl -c 202 -f mytestfile.xlsx
# 
# If you see a 'SUCCESS' message, you should then have the makefile and import
# script copied to your local directory.  If you don't see SUCCESS, then see the
# 'manual search' section, below).  Assuming you have the makefile and import
# script, do the following:
#
#   make test
#
# Make sure no gross errors occur (look at the read-only log file, debug_ro.txt).
# If there is anything other an 100% errors, go ahead and run the import:
#
#   make import
#
# This will run the import script in execute mode by adding the "-e" option.
# Once the import is complete, copy the exceptions file back to your PC and attach
# it to the Jira case.
#
use strict;

use Sys::Hostname;
use Getopt::Std;
use File::Copy;
use File::Basename;

use Data::Dumper;
use POSIX qw/strftime/;

$| = 1;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Util qw( clean );

use lib '/app/tools/support/lib';
use Support::DB::Item::DataImport;;
use Support::DataImport::RPSDataImport;

my %opt;
getopts('c:f:ei:s:v:', \%opt);

binmode STDOUT, ":utf8";

use Spreadsheet::XLSX;

use constant kImportRoot  => '/app/tools/support/lib/Support/DataImport/ImportTemplates';
use constant kReadOnlyLog => 'debug_ro.txt';
use constant kExecuteLog  => 'debug.txt';

use constant kArtistPayeeTemplate            => 1;
use constant kArtistPayeeExpenseTemplate     => 2;
use constant kArtistPayeeTransactionTemplate => 3;

use constant kArtistContractTermsTemplate    => 4;
use constant kArtistContractProductsTemplate => 5;
use constant kLicenseIncomeTypesTemplate     => 6;
use constant kLicensingIncomeTemplate        => 7;

use constant kUSPublisherPayeeTemplate       => 8;
use constant kUSPublisherPayeeTransactions   => 9;
use constant kUSMechanicalLicenseTemplate    => 10;

use constant kCAPublisherPayeeTemplate       => 11;
use constant kCAMechanicalLicenseTemplate    => 12;

use constant kLabelPayeeTransactions         => 13;
use constant kAddAdditionalContractTerms       => 14;
use constant kArtistHistoricalReserves       => 15;

use constant kPriceTable                     => 16;
use constant kProductPrices                  => 17;
use constant kUSLicenseBalances              => 18;
use constant kUSMechanicalHistoricalReserves => 19;
use constant kArtistReserveAdjustments       => 20;

my %gImportType = (
    kArtistPayeeTemplate()            => 'Artist Payee',
    kArtistPayeeExpenseTemplate()     => 'Artist Payee Expense',
    kArtistPayeeTransactionTemplate() => 'Artist Payee Transaction',
    kArtistContractTermsTemplate()    => 'Artist Contract Terms',
    kArtistContractProductsTemplate() => 'Artist Contract Products',
    kLicenseIncomeTypesTemplate()     => 'License Income Types',
    kLicensingIncomeTemplate()        => 'Licensing Income',
    kUSPublisherPayeeTemplate()       => 'US Publisher Payee',
    kUSPublisherPayeeTransactions()   => 'US Publisher Payee Transactions',
    kUSMechanicalLicenseTemplate()    => 'US Mechanical License',
    kCAPublisherPayeeTemplate()       => 'CA Publisher Payee',
    kCAMechanicalLicenseTemplate()    => 'CA Mechanical License',
    kLabelPayeeTransactions()         => 'Label Payee Transactions',
    kAddAdditionalContractTerms()       => 'Additional Contract Terms',
    kArtistHistoricalReserves()       => 'Artist Historical Reserves',
    kPriceTable()                     => 'Price Table',
    kProductPrices()                  => 'Product Prices',
    kUSLicenseBalances()              => 'US License Balances',
    kUSMechanicalHistoricalReserves() => 'US License Historical Reserves',
    kArtistReserveAdjustments()       => 'Artist Reserve Adjustments',
);


my $filename  = $opt{f};
my $clientID  = $opt{c};
my $execMode  = $opt{e};

my $importID  = $opt{i};
my $serviceID = $opt{s};
my $versionID = $opt{v};

die( usage("Missing arguments") ) unless ($clientID && ($filename) || $importID);

my $dbhost = $Common::RSDB::CLIENT_DB{$clientID}{server};
my $dbname = $Common::RSDB::CLIENT_DB{$clientID}{db_name};
my $dbuser = $Common::RSDB::CLIENT_DB{$clientID}{username};
my $dbpass = $Common::RSDB::CLIENT_DB{$clientID}{password};

#print strftime('%Y-%m-%d',localtime);
my $timestamp = lc strftime('%d%b%y_%H%M',localtime); # DDMMMYY_HHmm

my $appSingleton = Common::RSApp->new( clientID => $clientID );
my $dbo  = Common::RSApp::GetClientDB();
my $cdbo = Common::RSApp::GetCommonDB();
my $dbh  = $dbo->DBH;

my $cleanName;
my $sql = "SELECT client_name_clean, web_alias FROM client WHERE client_id=$clientID";
my $sth = $cdbo->DoCmd($sql);

my($_name, $_alias) = $sth->fetchrow_array();
$cleanName = ($_alias) ? $_alias : $_name;

my %gImportTypeNames; # map of human-readable import types to import typeIDs
foreach my $id ( keys { %gImportType } ) {
    my $s = $gImportType{$id};
    my $cleanName = clean($s);
    $gImportTypeNames{$cleanName} = $id;
}

my $excel;

if ( $importID ) {
    my $import = Support::DB::Item::DataImport->Lookup( import_id => $importID );
    die("ERROR: invalid data_import.import_id $importID specified !!!") if ( !$import );
    die("ERROR: data_import.import_id $importID is not a child import !!!") if ( 0 == $import->parent_id );
    $serviceID = $import->service_id;
    $versionID = $import->version_num;
    print "D: importID $importID --> service($serviceID) version($versionID)\n";
} else {

    if ( $filename =~ /.xlsx$/i ) {
        $excel = Spreadsheet::XLSX->new($filename);
    } elsif ( $filename =~ /.xls$/i ) {
        my $oExcel = new Spreadsheet::ParseExcel;
        $excel = $oExcel->Parse($filename);
    } else {
        die("ERROR - Only Excel .xls/.xlsx templates are supported !!!");
    }
}

our %dispatch = Support::DataImport::RPSDataImport::getDispatch;

my $importType;
my $importVersion;

if ( $serviceID ) {
    $importType    = $serviceID;
    $importVersion = $versionID;
} else {

    # Keep it simple -- only process the first sheet
    foreach my $sheet (@{$excel->{Worksheet}})
    {
        my $st = Support::DataImport::RPSDataImport->PreParse($filename);

        if ( $st->{errstr} ) {
            print "*** FILE NOT IDENTIFIED\n";
            _dumpRow( sheet => $sheet, row => 0 );
        } else {
            $importType    = $st->{service_id};
            $importVersion = $st->{version_num};
        }

        last;
    }

    if ( scalar @{$excel->{Worksheet}} > 1 ) {
        print "\nWARNING: Multiple sheets detected, but only the first one was looked at !!!\n";
    }


}

if ( exists $dispatch{$importType} ) {

    print "FYI: dispatch entry found for importType $importType, calling _transform\n";

    _transform(
       client_id   => $clientID,
       clean_name  => $cleanName,
       dbuser      => $dbuser,
       dbpass      => $dbpass,
       dbhost      => $dbhost,
       filename    => $filename,
       timestamp   => $timestamp,
       import_id   => $importID,
       service_id  => $importType,
       version_num => $importVersion,
    );
}
else {
    print "ERROR: No dispatch entry found for importType $importType !!!\n";
}


###
### ---    Subroutines Below    ---
###

sub CodeName {
    my $serviceID = shift;

    my $codeName = $dispatch{$serviceID}{codeName};
    return undef unless $codeName;

    return $codeName;
}

sub _transform {
    my ( %args ) = @_;

    my $clientID      = $args{client_id};
    my $cleanName     = $args{clean_name};
    my $dbuser        = $args{dbuser};
    my $dbpass        = $args{dbpass};
    my $dbhost        = $args{dbhost};
    my $filename      = $args{filename};
    my $timestamp     = $args{timestamp};
    my $importID      = $args{import_id};
    my $importType    = $args{service_id};
    my $importVersion = $args{version_num};


    my $_filename = $filename;

        my $baseDir = kImportRoot . '/' . $dispatch{$importType}{base};

        # import wrapper script; if no path provided then will be pulled from base directory
        my $script     =  $dispatch{$importType}{script};

        # target script name
        my $scriptTarget = basename $script;

        # full path to import wrapper
        my $scriptSource;
        if ( $script =~ /^\// ) {
           $scriptSource = $script;
        } else {
           $scriptSource = "$baseDir/$script";
        }

        my $makefile = "$baseDir/makefile.in";
        open my $fh, '<', $makefile or die "error opening $makefile $!";
        my $makedata = do { local $/; <$fh> };
        $makedata =~ s/__CLIENTID__/$clientID/;
        $makedata =~ s/__NAME__/$cleanName/;
        $makedata =~ s/__DBUSER__/$dbuser/;
        $makedata =~ s/__DBPASS__/$dbpass/;
        $makedata =~ s/__DBHOST__/$dbhost/;
        $makedata =~ s/__INPUTFILE__/$_filename/;
        $makedata =~ s/__TIMESTAMP__/$timestamp/;

        # __IMPORTID__ will either be an importID or importer type flag
        if ( $importID ) {
            $makedata =~ s/__IMPORTID__/-i $importID/;
            $makedata =~ s/__POSTARGS__/-i $importID/;
        } else {
            $makedata =~ s/__IMPORTID__/-s $importType/;
            $makedata =~ s/__POSTARGS__//; # not used if make is run manually
        }

        if ( $importType == kLicensingIncomeTemplate ) {
            # These are special; check if we're adding to an existing license income import
            my $cur = `basename \`pwd\``;
            chomp $cur;

            # Look for a pre-existing import by checking the parent directory for another
            # directory aside from the current directory.  We look for a makefile in the
            # most recently updated directory and (try to) the LICENSEFILE info from the
            # makefile.  This allows us to differentiate between the template containing license
            # income lines (INPUTFILE) and the RPS file name (LICENSEFILE).
            #
            my $otherdir = `ls -rtd ../* | grep -v $cur | tail -1`;
            chomp $otherdir;
            if ( -d $otherdir ) {
                if ( $otherdir =~ /(\.\.\/.+)$/ ) {
                    my $odir = $1;
                    my $ofile;
                    if ( -e "$odir/makefile" ) {
                        $ofile = `grep "LICENSEFILE=" $odir/makefile`;
                    } elsif( -e "$odir/makefile.gz" ) {
                        $ofile = `gzip -dc $odir/makefile.gz | grep "LICENSEFILE="`;
                    } else {
                        die("Unable to find makefile in odir '$odir' !!!");
                    }
                    $ofile =~ s/LICENSEFILE=//;
                    $ofile =~ s/\r\n//;
                    $ofile =~ s/^"//;
                    $ofile =~ s/"$//;
                    chomp $ofile;

                    if ( $ofile ) {
                        $makedata =~ s/__LICENSEFILE__/$ofile/;
                    } else {
                        # Shouldn't happen, unless there are extra directories for this case
                        die("Unable to find LiCENSEFILE info from earlier license income import, odir($odir) ofile($ofile)");
                    }
                } else {
                    # Shouldn't get here, but let's error out if we do
                    die("Error detected while handling license income from otherdir '$otherdir'...");
                }
            } else {
                # no previous makefile, treat as a new import
                $makedata =~ s/__LICENSEFILE__/$_filename/;
            }
        }

#        if ( ! -e "$scriptTarget" ) {
#            # If the template is too big (?), then this may result in wonkiness which prevents
#            # an eval from working (unable to create new shell? out of memory?)
#            #
#            copy( $scriptSource, $scriptTarget ) or die "ERROR: copy failed: $1";
#            print "ERROR: failed to copy '$scriptSource' to '$scriptTarget' !!!\n" if ( ! -e $scriptTarget );
#       }

        my $_makefile = "makefile";
        if ( ! -e $_makefile ) {
            open my $fh, '>', "$_makefile" or die "error opening $_makefile $!";
            print $fh $makedata;
            close $fh;
        } else {
            print "file $_makefile exists !!!\n";
        }
}

sub _dumpRow {
    my ( %opt ) = @_;
    my $sheet = $opt{sheet};
    my $row   = $opt{row};

    my @values;
    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol})
    {
        my $cell = $sheet->{Cells}[$row][$col];
        my $val  = $cell->{Val};
        print "D: r($row) c($col): val($val)\n";
        push @values, $val;
    }
    print "\n" . join(', ', map { "'" . $_ . "'" } @values ) . "\n";

}# _dumpRow

sub usage {
    my $e = shift;
    return "ERROR: $e\nUsage: rps_dataimport.pl -c clientID -f templateToImport\n";
}