package Support::Implementation::LabelPayeeTemplate;
# 3/12/10 - When checking for duplicate payees, we look
# at the payee name and the client account id.
# 5/4/10 - Added logic to set currency code for client
# 5/20/10 - Initial Release (copied from ArtistPayeeTemplate.pm)
# 10/24/12 - Updated to reflect current label_payee schema
# (specifically, the street_address_2 and street_address_3 fields
#  that supplement the street_address field).
# 4/24/17 - Move payor check s.t. it's validated during non-exec mode
# 4/25/17 - Fix errorCode reporting; make payor case-insensitive
use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use IO::File;
use Data::Dumper;
use Date::Calc;

use Spreadsheet::ParseExcel;

use Common::Assert;
use Common::UTF8;
use Common::RSApp;
use Common::Consts;
use Common::CurrencyFormat;
use Common::Util qw( clean trimspaces);

use RPS::DB::Item::LabelPayee;
use RPS::DB::Item::LabelPayeeAccount;
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PendingTransaction;

use Support::Implementation::ExcelReader;
use Support::Implementation::Excel2007Reader;
use Support::Implementation::TabDelimitedReader;

use base 'Support::Implementation::Template';

use constant kQuiet  => 0;
use constant kNormal => 1;
use constant kDebug  => 3;
my $gReportLevel = kNormal;

binmode STDOUT, ":utf8";

#-----------------------------------------------------------------------
# templateHeader maps column names to their default column number.
# The actual column number-header name map is stored in columnMap.
# Note(s):
# 1) Column names are case sensitive
# 2) Spaces are ignored; you must manually remove spaces from the names
#    stored in templateHeader
#-----------------------------------------------------------------------
my %gTemplateHeader = (
   "label-payee-name"  => 0,  # A
   "client-account-no" => 1,  # B
   "status"            => 2,  # C
   "address-1"         => 3,  # D
   "address-2"         => 4,  # E
   "address-3"         => 5,  # F
   "address-4"         => 6,  # G
   "address-5"         => 7,  # H
   "city"              => 8,  # I
   "state-province"    => 9,  # J
   "zip-postal"        => 10, # K
   "country"           => 11, # L
   "e-mail"            => 12, # M
   "phone"             => 13, # N
   "fax"               => 14, # O
   "tax-id"            => 15, # P
   "comments"          => 16, # Q
   "payor-name"        => 17, # R
   "minimum-payment"   => 18, # S
   "opening-balance"   => 19, # T
);

#--------------------------------------------------------------
# Maps column #'s to a unique key (headername).  The reverse of
# templateHeader, but with actual column #.
#--------------------------------------------------------------
my %gColumnMap = ();

#-------------------------------------------
# Maps status codes to their internal values
#-------------------------------------------
my %gStatusMap = (
   "I" => 0,
   "A" => 1,
   "H" => 2,
);

#--------------------------------------------------------
# gHeaderDisplayed is a flag that we set if we've already
# displayed the template header during an exceptions dump
#--------------------------------------------------------
my $gHeaderDisplayed;

my $clientID;
my $execMode;

my $dbo;
my $cdbo;

sub new {
   my ($class, %args) = @_;
   my $self = bless {}, $class;
   return $self->_init(%args);
}


sub _init {
   my( $self, %args ) = @_;

   report("LabelPayeeTemplate::_init -- args = ". Dumper(\%args));

   $self->SUPER::_init(%args);

   $execMode = $self->exec_mode if ( $self->exec_mode );

   return $self;
}

sub parseHeader {
   my $self = shift;
}

#----------------------------------------------
# Read-in all of the template data into memory.
#----------------------------------------------
sub loadMemory {
   my $self = shift;

   $clientID = $self->client_id;
   my $app = Common::RSApp->new(clientID => $clientID);

   $dbo = Common::RSApp::GetClientDB();
   $cdbo = Common::RSApp::GetCommonDB();

   my $fileName = $self->name;

   if( $self->isExcel2007( $fileName ) )
   {
      print("LabelPayeeTemplate::loadMemory -- loading Excel2k7 $fileName into memory\n");

      # Read in the header
      my %data;
      my $reader = Support::Implementation::Excel2007Reader->new(
         filename => $fileName,
         data => \%data,
         header => \%gTemplateHeader,
         columnmap => \%gColumnMap
      );
      $reader->scanExcelFile;

      # Try and parse it...
      _processData(\%data);
   }
   elsif( $self->isExcel2003( $fileName ) ) {
      print("LabelPayeeTemplate::loadMemory -- loading Excel2k3 $fileName into memory\n");

      # Read in the header
      my %data;
      my $reader = Support::Implementation::ExcelReader->new(
         filename => $fileName,
         data => \%data,
         header => \%gTemplateHeader,
         columnmap => \%gColumnMap
      );
      $reader->scanExcelFile;

      # Try and parse it...
      _processData(\%data);
   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("LabelPayeeTemplate::loadMemory -- processing tab-delimited file");

      my %data;
      my $reader = Support::Implementation::TabDelimitedReader->new(
         filename => $fileName,
         data => \%data,
         header => \%gTemplateHeader,
         columnmap => \%gColumnMap
      );

      $reader->scanTabbedFile;
      _processData(\%data);
   }
}

#-------------------------------------------------------------------
# _processData is where the real work is done.  It takes the generic
# information stored in the supplied array of hashes and decodes it.
# In this case, it assumes that the supplied data contains license
# data.
#-------------------------------------------------------------------
sub _processData {
   my($data) = @_;
   my $rows = $data->{rows};

   #----------------------------------
   # excount keeps track of exceptions
   #----------------------------------
   my %excount = (
      payee_exists => 0,
      unknown_country => 0,
   );

   #--------------------------------------
   # count keeps track of entities created
   #--------------------------------------
   my %count = (
      new_payees => 0,
   );

   #------------------------
   # Setup payor information
   #------------------------
   my $gDefaultPayorID;
   my %gPayorMap = (); # maps names to ID
   my $sql = "SELECT payor_id,name,is_default FROM payor";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$name,$isDefault) = $sth->fetchrow_array() ) {
      $gPayorMap{lc $name} = $id;
      if ( $isDefault ) {
         $gDefaultPayorID = $id;
      }
   }
   if ( not defined $gDefaultPayorID ) {
      die("No default payor setup for client");
   }

   #---------------------------------
   # Get the client's currency format
   #---------------------------------
   $sql = "SELECT country_code FROM client WHERE client_id=$clientID";
   $sth = $cdbo->DoCmd($sql);
   my($cCode) = $sth->fetchrow_array();
   my $currencyFormat = new Common::CurrencyFormat( countryCode => $cCode );
   my $denomination = $currencyFormat->currencyCode();

   if ( !$denomination ) {
      die("ERROR: Unable to find currency denomination for client($clientID)");
   }

   #-----------------------------------------
   # First pass -- check for duplicate payees
   #-----------------------------------------

   # duplicatePayeeMap -- if a payee name + client account no appears in
   # here, then the payee appears more than once in the template
   my %duplicatePayeeMap;

   my %seenPayee;
   foreach my $row (@$rows) {
      #my $payeeName       = $row->{'label-payee-name'};
      my $seenKey = join("\t", $row->{'label-payee-name'},
         _nullCheck( $row->{'client-account-no'} ) );
      if ( exists $seenPayee{$seenKey} ) {
         $duplicatePayeeMap{$seenKey}  = 1;
      }
      $seenPayee{$seenKey} = 1;
   }

   #-----------------------------
   # Loop over each row (payee)
   #-----------------------------
   foreach my $row (@$rows) {

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid           = $row->{'rowid'};
      my $payeeName       = $row->{'label-payee-name'};
      my $clientAccountNo = $row->{"client-account-no"};
      my $status          = $row->{'status'};
      my $address1        = $row->{'address-1'};
      my $address2        = $row->{'address-2'};
      my $address3        = $row->{'address-3'};
      my $address4        = $row->{'address-4'};
      my $address5        = $row->{'address-5'};
      my $city            = $row->{'city'};
      my $stateProvince   = $row->{'state-province'};
      my $zipPostal       = $row->{'zip-postal'};
      my $country         = $row->{'country'};
      my $email           = $row->{'e-mail'};
      my $phone           = $row->{'phone'};
      my $fax             = $row->{'fax'};
      my $taxid           = $row->{'tax-id'};
      my $comments        = $row->{'comments'};
      my $payorName       = $row->{'payor-name'};
      my $minPayment      = $row->{'minimum-payment'};
      my $balance         = $row->{'opening-balance'};

      report("#### row($rowid) ".Dumper(\%$row));

      #-----------------------------------------------
      # errorCode will hold one or more error messages
      #-----------------------------------------------
      my $errorCode;

      my %args = ();
      if ( defined $payeeName ) { $args{name} = $payeeName; }
      if ( defined $clientAccountNo ) { $args{client_account_id} = $clientAccountNo; }
      #if ( defined $status ) { $args{status} = $gStatusMap{$status}; }
      $args{status} = $gStatusMap{ uc substr($status, 0, 1) } if ( defined $status );


      # The template currently has five address lines, however internally we
      # only support three.  To accomodate this, the first two lines are stored
      # in their own location, and lines three through five are stored in the
      # third location. -ES 10/24/12
      #
      if ( defined $address1 ) { $args{street_address}   = $address1; }
      if ( defined $address2 ) { $args{street_address_2} = $address2; }

      my $streetAddress;
      if ( defined $address3 ) { $streetAddress = $address3; }
      if ( defined $address4 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address4" : "$address4"; }
      if ( defined $address5 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address5" : "$address5"; }

      if ( defined $streetAddress ) { $args{street_address_3} = $streetAddress; }


      if ( defined $city ) { $args{city} = $city; }
      if ( defined $stateProvince ) { $args{state_province} = $stateProvince; }
      if ( defined $zipPostal ) { $args{postal_code} = $zipPostal; }

      if ( defined $country ) {
         $country =~ s/^\s*//g;
         $country =~ s/\s*$//g;
         if ( $country =~ m/^[a-z][a-z]$/i ) {
            $args{country_code} = $country;
         } elsif( '' eq $country ) {
            $args{country_code} = "US"; # DEFAULT
         } else {
            $country = lc $country;
            # common mis-spellings
            $country =~ s/brasil/brazil/g;
            $country =~ s/austalia/australia/g;
            my $countryCode = $Common::Consts::COUNTRY_CODE{ $country };

            if ( !$countryCode ) {
               _appendString( $errorCode, "Unknown country");
               ++$excount{unknown_country};
            } else {
               $args{country_code} = $Common::Consts::COUNTRY_CODE{ $country };
            }
         }
      }
      if ( defined $email ) { $args{email} = $email; }
      if ( defined $phone ) { $args{phone_number} = $phone; }
      if ( defined $fax ) { $args{fax_number} = $fax; }
      if ( defined $taxid ) { $args{tax_id} = $taxid; }
      #if ( defined $comments ) { $args{comments} = $comments; }
      $args{comments} = (defined $comments) ? $comments : " ";


      #------------------------------------------------------
      # Does the payee appear more than once in the template?
      #------------------------------------------------------
      my $seenKey = join("\t", $payeeName,
         _nullCheck( $clientAccountNo ) );

      if ( $duplicatePayeeMap{$seenKey} ) {
         _appendString( $errorCode, "Duplicate payee");
         ++$excount{duplicate_payee};
      }

      #------------------------------------------------------------------
      # If there are any errors at this point, stop processing this payee
      #------------------------------------------------------------------
#      if ( $errorCode ) {
#         $row->{'error-code'} = $errorCode;
#         next;
#      }

      my $payeeID;

      # To determine if a payee exists, just search on its name
      my %searchArgs = (
         name => $payeeName,
      );

      $searchArgs{client_account_id} = $clientAccountNo
         if ( defined $clientAccountNo );

      #my $labelPayeeObj = RPS::DB::Item::LabelPayee->Lookup(%args);
      my $labelPayeeObj = RPS::DB::Item::LabelPayee->Lookup(%searchArgs);
      if ( not defined $labelPayeeObj ) {
         if ( $execMode ) {
            $labelPayeeObj = RPS::DB::Item::LabelPayee->Create(%args);
            $labelPayeeObj->save();
            $payeeID = $labelPayeeObj->label_payee_id;
            report("Created label_payee $payeeID : ".Dumper(\%args));
            ++$count{new_payees};
            $row->{'rs-payee-id'} = $payeeID;
         } else {
            report("Non-exec mode, skipped label_payee: ".Dumper(\%args));
            $row->{'rs-payee-id'} = 0;
         }
      } else {
         $payeeID = $labelPayeeObj->label_payee_id;
         report("EXISTS: label_payee '$payeeName' (id=$payeeID)");
         _appendString( $errorCode, "Payee exists");
         ++$excount{payee_exists};
      }

      #---------------------------------------------------------------------------
      # Optional: Setup label_payee_account, finance_account, pending_transaction
      # But first, make sure we have a valid payor.
      #---------------------------------------------------------------------------
      my $payorID;
      if ( not defined $payorName ) {
         $payorID = $gDefaultPayorID;
      } else {
         $payorID = $gPayorMap{lc $payorName};
      }

      if ( not defined $payorID ) {
         # This should be caught during template validation
         #die("  Shouldn't get here -- payorID undefined for payor '$payorName'");
         report("PAYOR_NOT_FOUND: payor '$payorName'");
         _appendString( $errorCode, "Payor not found");
         ++$excount{payor_not_found};
      }

      #------------------------------------------------------------------
      # If there are any errors at this point, stop processing this payee
      #------------------------------------------------------------------
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         next;
      }

      # If we don't have a payeeID at this point then we can skip the
      # the remainder of the line.
      #
      if ( not defined $payeeID ) {
         next;
      }

      #----------------------------------------------------------------------
      # If we have a min payment or opening balance, create a finance account
      # if it doesn't already exist.
      #----------------------------------------------------------------------
      my $financeAccountID;

      if( ($minPayment && '' ne $minPayment) ||
          ($balance && '' ne $balance) ) {

         #--------------------------
         # Setup the finance account
         #--------------------------
         my %faArgs = (
            description => "account for label payee $payeeID payor $payorID",
            type_code => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
            currency_code => $denomination, # 5/4/10
         );
         my $faObj = RPS::DB::Item::FinanceAccount->Lookup(%faArgs);
         if ( not defined $faObj ) {
            if ( $execMode ) {
               $faObj = RPS::DB::Item::FinanceAccount->Create(%faArgs);
               $faObj->save();
               $financeAccountID = $faObj->finance_account_id;
               report("   Created finance_account $financeAccountID : ".Dumper(\%faArgs));
            } else {
               report("   Non-exec mode, skipped finance_account for payee $payeeID, payor $payorID");
            }
         } else {
            $financeAccountID = $faObj->finance_account_id;
            report("EXISTS: finance_account $financeAccountID");
         }

         if ( not defined $financeAccountID ) {
            report("   No financeAccountID, skipping LabelPayeeAccount");
            next;
         }

      }

      #---------------------------------------------------
      # The balance may or may not have 'extra' characters
      #---------------------------------------------------
      if ( $balance ) {
         $balance =~ s/\$//;
         $balance =~ s/,//;
         if ( $balance =~ m/\(.*\)/ ) {
            $balance =~ s/\(//;
            $balance =~ s/\)//;
            $balance *= -1;
         }
      }

      #----------------------------------------------------
      # If there's a balance, setup the pending transaction
      #----------------------------------------------------
      if ( $balance and '' ne $balance) {

         #------------------------------
         # Setup the pending transaction
         #------------------------------
         my %ptArgs = (
            finance_account_id => $financeAccountID,
            amount => $balance,
            memo => "opening balance",
            type_code => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
            currency_code => $denomination, # 5/4/10
         );
         my $ptID;
         my $ptObj = RPS::DB::Item::PendingTransaction->Lookup(%ptArgs);
         if ( not defined $ptObj ) {
            if ( $execMode ) {
               $ptObj = RPS::DB::Item::PendingTransaction->Create(%ptArgs);
               $ptObj->save();
               $ptID = $ptObj->pending_transaction_id;
               report("   Created pending_transaction $ptID : ".Dumper(\%ptArgs));
            } else {
               report("   Non-exec mode, skipping pending_transaction");
            }
         } else {
            $ptID = $ptObj->pending_transaction_id;
            report("EXISTS: pending_transaction $ptID");
         }

      }

      #-------------------------------
      # Setup the label payee account
      #-------------------------------
      if ( $financeAccountID &&
          ($minPayment && '' ne $minPayment) ||
          ($balance && '' ne $balance) ) {
         my %args = (
            label_payee_id => $payeeID,
            payor_id => $payorID,
            finance_account_id => $financeAccountID,
         );
         if ( defined $minPayment ) {
            $minPayment =~ s/\$//;
            $minPayment =~ s/,//;
            $args{min_payment} = $minPayment;
         }
         my $apObj = RPS::DB::Item::LabelPayeeAccount->Lookup(%args);
         if ( not defined $apObj ) {
            if ( $execMode ) {
               $apObj = RPS::DB::Item::LabelPayeeAccount->Create(%args);
               $apObj->save();
               report("   Created label_payee_account : ".Dumper(\%args));
            } else {
               report("   Non-exec mode, skipping label_payee_account for payee $payeeID, finance_account $financeAccountID");
            }
         } else {
            report("EXISTS: label_payee_account exists for payee $payeeID, finance_account $financeAccountID");
         }
      }


      #------------------------------------------------------------
      # If we have any errors at this point then set the errorCode.
      #------------------------------------------------------------
      $row->{'error-code'} = $errorCode;
   }#row loop



   #------------------------------------------------------------
   # Dump out the errors
   # TODO: Need to properly propagate the errors back to the user
   #------------------------------------------------------------
   _showExceptions( $rows );

   #-----------------------
   # Show import statistics
   #-----------------------
   report("##### S U M M A R Y #####");
   my $totalExceptions = 0;
   my $totalRows = (scalar @$rows);

   foreach my $c (keys %excount) {
      my $v = $excount{$c};
      printf("%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf("%30s %6d\n", "Total Rows", $totalRows );
   report(" ");
   
   foreach my $c (keys %count) {
      my $v = $count{$c};
      printf("%30s %d\n", $c, $v);
   }
}#_processData

#--------------------------------------------------------------------------
# _showExceptions was originally intended to _just_ show the template lines
# that exceptioned out.  It's been modified to output both exception and
# non-exception lines.  Exception message(s) will be placed in the error
# code column.  If a line is imported successfully, then the resulting
# licenseID will be stored in the import status column ("licenseID(###)"),
# otherwise this column will contain the string "__FAILED__".
#--------------------------------------------------------------------------
sub _showExceptions {
   my ( $rows ) = @_;

   #---------------------------
   # Build the exception header
   #---------------------------
   my @header;
   for( my $i = 0; $i < (keys %gColumnMap); $i++ ) {
      # Get the key at the specified column
      my $v = $gColumnMap{$i};

      #----------------------------------------------------------
      # Do _not_ push the "Error Code" or "import-status" columns
      # if they were in the original template.  We'll re-create
      # them on the fly.
      #----------------------------------------------------------
      next if ( ("Error Code" eq $v) || ("import-status") eq $v );

      push @header, $v;
   }

   report("STATUS:\t".join("\t", @header, "Error Code", "import-status"));

   #--------------------------------------
   # Now dump out the rows that had errors
   #--------------------------------------
   foreach my $row (@$rows) {
      my $rowid = $row->{rowid};
      my $errorCode = $row->{'error-code'};
      my $payeeID = $row->{'rs-payee-id'};

      #----------------------------------------
      # Output the row data in the proper order
      #----------------------------------------
      my @obuf;
      for( my $i = 0; $i < (keys %gColumnMap); $i++ ) {
         # Get the key at the specified column
         my $v = $gColumnMap{$i};
         my $val = ($row->{$v}) ? $row->{$v} : '';

         #----------------------------------------------------------
         # Do _not_ push the "Error Code" or "import-status" columns
         # if they were in the original template.
         #----------------------------------------------------------
         next if ( ("Error Code" eq $v) || ("import-status") eq $v );

         push @obuf, $val;
      }

      my $importStatus = ($errorCode) ? "__FAIL__" : "payee($payeeID)";

      my $ecString = ($errorCode) ? $errorCode : '';

      my $zzbuf = join("\t", "STATUS:", @obuf, $ecString, $importStatus);

      report($zzbuf);
   }
}#_showExceptions

#---------------------------------------------------
# _isValidDateFmt - return true if the supplied date
# is in the format YYYY-MM-DD, false otherwise
#---------------------------------------------------
sub _isValidDateFmt {
   my($dstr) = @_;
   my($yr,$mo,$dy) = split("-",$dstr);
   my $st = 1; # valid unless we detect otherwise

   $st = 0 if ( !$yr || !$mo || !$dy );
   $st = 0 if ( $mo && ( $mo !~ /^\d+$/ ));
   $st = 0 if ( $yr && ( $yr !~ /^\d+$/ ));
   $st = 0 if ( $dy && ( $dy !~ /^\d+$/ ));
   $st = 0 if ( length($yr) != 4 );
   return $st;
}

sub _appendString {
   my($str,$v) = @_;

   if ( $str ) {
      my $cur = $str;
      my $newstring = "$cur; $v";
      $_[0] = $newstring;
   } else {
      $_[0] = $v;
   }
   return;
die("str($str) v($v)") if ( !$str );

   my $cur = $str;
   my $prefix = "$cur; $v";
   $_[0] = ($str) ? $prefix : $v;
}# _appendString

sub _reportError {
   my ($name, $obj) = @_;

   report("   _reportError: checking '$name' for errors");
   if ($obj && $obj->_hasError()) {
      #my ($e, $msg) = $obj->getError();
      #print "$name has an error: $e : $msg\n";

      my %xmlParams = $obj->getXMLParams();
      my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE";
      my $e = $xmlParams{e};
      print "$name has an error:: $e :: $msg\n";
      return 1;
   }
   return undef;
}

sub _nullCheck {
   my($a) = @_;
   return('') if (!$a);
   return $a;
}

sub report {
   my($text, $level) = @_;
   $level = kNormal unless $level;
   if ( $level <= $gReportLevel ) {
      print $text . "\n";
   }
}

1;