package Support::Implementation::LicenseAdjustmentTemplate;
#
# 6/15/10 -- Initial release (based on LicenseTemplate.pm)
#
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::Util qw( clean trimspaces);

#use RPS::License::TrackLicense;
#use RPS::License::ReserveLiquidation;
use RPS::License::BaseTrackLicense;
use RPS::License::US::PublicDomain;
use RPS::License::US::TrackLicense;
use RPS::License::US::TrackLicenseWithLiquidationSchedule;

use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::Master;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::Publisher;
use RPS::DB::Item::Payor;
use RPS::DB::Item::Region;
use RPS::DB::Item::RegionCountryMap;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::PendingTransaction;
use RPS::Finance::PendingTransaction;
use RPS::DB::Item::FinanceAccount;
use RPS::Finance::Account;

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

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

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

use constant kAlbumNotFound     => 100;
use constant kTrackNotFound     => 101;
use constant kPublisherNotFound => 102;
use constant kNonUniqueAlbumName => 102;
use constant kProductTypeNotFound   => 99999;

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"                      => 0,  # A
   "Artist Name"                => 1,  # B
   "Album Name"                 => 2,  # C
   "Catalog #"                  => 3,  # D
   "trk#"                       => 4,  # E
   "Track Name"                 => 5,  # F
   "Track Duration"             => 6,  # G
   "Track Mech. Exempt"         => 7,  # H
   "License ID"                 => 8,  # I
   "Controlled Comp."           => 9,  # J
   "Product Type"               => 10, # K
   "Cross"                      => 11, # L
   "Region"                     => 12, # M
   "Share%"                     => 13, # N
   "Payor"                      => 14, # O
   "Publisher Name"             => 15, # P
   "Pub. Direct?"               => 16, # Q
   "Admin Name"                 => 17, # R
   "Through HFA?"               => 18, # S
   "Advance"                    => 19, # T
   "Issuer Lic."                => 20, # U
   "Status"                     => 21, # V
   "Rate Basis"                 => 22, # W
   "Lock Date"                  => 23, # X
   "Rate Type"                  => 24, # Y
   "Penny Rate"                 => 25, # Z
   "Rate%"                      => 26, # AA
   "\%Sales"                    => 27, # AB
   "Res%"                       => 28, # AC
   "P1"                         => 29, # AD
   "P2"                         => 30, # AE
   "P3"                         => 31, # AF
   "P4"                         => 32, # AG
   "P5"                         => 33, # AH
   "P6"                         => 34, # AI
   "P7"                         => 35, # AJ
   "P8"                         => 36, # AK
   "Date Sent"                  => 37, # AL
   "Date Received"              => 38, # AM
   "Date Issued"                => 39, # AN
   "Term Start"                 => 40, # AO
   "Term End"                   => 41, # AP
   "Pkg.%"                      => 42, # AQ
   "Free%"                      => 43, # AR
   "Misc%"                      => 44, # AS
   "Pending Transaction Memo"   => 45, # AT
   "Pending Transaction Amount" => 46, # AU
   "Date Created"               => 47, # AV
   "Email"                      => 48, # AW  XXX - Non-unique key!!!
   "Date Created"               => 49, # AX
   "Email"                      => 50, # AY  XXX - Non-unique key!!!
);

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

#--------------------------------------------------------
# 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("LicenseAdjustmentTemplate::_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.  Once it's been
# read, try to parse it and create track licenses.
#--------------------------------------------------------------
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->isExcel2003( $fileName ) ) {
      print("LicenseAdjustmentTemplate::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("LicenseAdjustmentTemplate::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};

   #my @errorList = ();

   my $numNewLicenses = 0;
   my $numExceptions = 0;
   my $numTrackExceptions = 0;
   my $numAlbumExceptions = 0;
   my $numLockDateExceptions = 0;
   my $numPublisherNotFoundExceptions = 0;
   my $numPublisherMismatchExceptions = 0;
   my $numRateTypeExceptions = 0;
   my $numPayorExceptions = 0;
   my $numProductTypeExceptions = 0;
   my $numRateBasisExceptions = 0;
   my $numRegionExceptions = 0;
   my $numZeroShareExceptions = 0;


   #----------------------------------
   # excount keeps track of exceptions
   #----------------------------------
   my %excount = (
      license_exists          => 0,
      track_not_found         => 0,
      album_not_found         => 0,
      zero_share              => 0,
      share_warning           => 0,
      publisher_name_mismatch => 0,
      publisher_not_found     => 0,
      bad_publisherid         => 0,
      payor_not_found         => 0,
      missing_rate_type       => 0,
      missing_product_type    => 0,
      missing_rate_basis      => 0,
      missing_lock_date       => 0,
      region_not_found        => 0,
      bad_date_format         => 0,
      invalid_digital_region  => 0,
      mechanical_exempt       => 0,
      isrc_too_long           => 0,
      isrc_too_short          => 0,
      missing_publisher_name  => 0,
   );

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

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

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid               = $row->{rowid};
      my $labelName           = $row->{'Label'};              # A
      my $artistName          = $row->{'Artist Name'};        # B
      my $albumName           = $row->{'Album Name'};         # C
      my $catalogNumber       = $row->{'Catalog #'};          # D
      my $trackNumber         = $row->{'trk#'};               # E
      my $trackName           = $row->{'Track Name'};         # F
      my $trackDuration       = $row->{'Track Duration'};     # G
      my $trackMechExempt     = $row->{'Track Mech. Exempt'}; # H
      my $rsLicenseID         = $row->{'License ID'};         # I
      my $controlledComp      = $row->{'Controlled Comp.'};   # J
      my $productType         = $row->{'Product Type'};       # K
      my $crossed             = $row->{'Cross'};              # L
      my $region              = $row->{'Region'};             # M
      my $share               = $row->{'Share%'};             # N
      my $payor               = $row->{'Payor'};              # O
      my $publisherName       = $row->{'Publisher Name'};     # P
      my $publisherDirect     = $row->{'Pub. Direct?'};       # Q
      my $adminName           = $row->{'Admin Name'};         # R
      my $throughHFA          = $row->{'Through HFA?'};       # S
      my $advance             = $row->{'Advance'};            # T
      my $issuerLicenseID     = $row->{'Issuer Lic.'};        # U
      my $status              = $row->{'Status'};             # V
      my $rateBasis           = $row->{'Rate Basis'};         # W
      my $lockDate            = $row->{'Lock Date'};          # X
      my $rateType            = $row->{'Rate Type'};          # Y
      my $pennyRate           = $row->{'Penny Rate'};         # Z
      my $ratePercentage      = $row->{'Rate%'};              # AA
      my $percentOfSales      = $row->{'%Sales'};             # AB
      my $reservePercent      = $row->{'Res%'};               # AC
      my $p1                  = $row->{'P1'};                 # AD
      my $p2                  = $row->{'P2'};                 # AE
      my $p3                  = $row->{'P3'};                 # AF
      my $p4                  = $row->{'P4'};                 # AG
      my $p5                  = $row->{'P5'};                 # AH
      my $p6                  = $row->{'P6'};                 # AI
      my $p7                  = $row->{'P7'};                 # AJ
      my $p8                  = $row->{'P8'};                 # AK
      my $licenseDateSent     = $row->{'Date Sent'};          # AL
      my $licenseDateReceived = $row->{'Date Received'};      # AM
      my $licenseDateIssued   = $row->{'Date Issued'};        # AN
      my $licenseTermStart    = $row->{'Term Start'};         # AO
      my $licenseTermEnd      = $row->{'Term End'};           # AP
      my $packaging           = $row->{'Pkg.%'};              # AQ
      my $freeGoods           = $row->{'Free%'};              # AR
      my $miscDeduction       = $row->{'Misc%'};              # AS
      my $transactionMemo     = $row->{'Pending Transaction Memo'};   # AT
      my $transactionAmount   = $row->{'Pending Transaction Amount'}; # AU
      my $dateCreated         = $row->{'Date Created'};       # AV
      my $createdBy           = $row->{'Email'};              # AW
      my $dateModified        = $row->{'Date Modified'};      # AX
      my $modifiedBy          = $row->{'Email'};              # AY  TODO: This won't work; keys must be _unique_

#      my $isrc                = $row->{'isrc'};
#      my $publicDomain        = $row->{'public-domain'};
#      my $rsPublisherID       = $row->{'royaltyshare-publisher-id#'};
#      my $comments            = $row->{'comments'};
#      my $balance             = $row->{'license-opening-balance'};

#      my $rsTrackID           = $row->{'royaltyshare-track-id'};
#      my $rsAlbumID           = $row->{'royaltyshare-album-id'};


      # Check for obsolete column names
      my $altRSTrackID        = $row->{'rs-track-id'};
      die("ERROR: found column named 'rs-track-id', expecting 'royaltyshare-track-id'") if ( $altRSTrackID );

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

      $publisherName =~ s/\s*$// if ( $publisherName );
      $publisherName =~ s/^\s*// if ( $publisherName );
      $albumName =~ s/\s*$// if ( $albumName );
      $albumName =~ s/^\s*// if ( $albumName );
      #$isrc =~ s/\s*$// if ( $isrc );
      #$isrc =~ s/^\s*// if ( $isrc );
      $catalogNumber =~ s/\s*$// if ( $catalogNumber );
      $catalogNumber =~ s/^\s*// if ( $catalogNumber );

      $transactionAmount =~ s/\$//;

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

      #----------------------------------------------------
      # warningCode will hold zero or more warning messages
      #----------------------------------------------------
      my $warningCode;

      # Get the client's currency format
      my $sql = "SELECT country_code FROM client WHERE client_id=$clientID";
      my $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)");
      }


      #-------------------------
      # Verify the RS license ID
      #-------------------------
      my $tlObj; # set to RPS license object if licenseID is valid
      if ( !$rsLicenseID ) {
         report("Missing licenseID");
         _appendString( $errorCode, "Missing licenseID");
         ++$excount{missing_licenseid};
      } else {

         $tlObj = RPS::DB::Item::TrackLicense->Lookup(
            track_license_id => $rsLicenseID
         );

         if ( !$tlObj ) {
            report("licenseID($rsLicenseID) is invalid!!!");
            _appendString( $errorCode, "Invalid licenseID");
            ++$excount{invalid_licenseid};

         }
      }

      #-------------------------
      # Make sure we have a memo
      #-------------------------
      if ( !$transactionMemo ) {
         report("Missing transaction memo");
         _appendString( $errorCode, "Missing memo");
         ++$excount{missing_memo};
      }

      #----------------------------
      # Make sure we have an amount
      #----------------------------
      if ( !$transactionAmount ) {
         report("Missing transaction amount");
         _appendString( $errorCode, "Missing amount");
         ++$excount{missing_amount};
      }

      next if ( $errorCode );

      #---------------------------
      # Create the finance account
      #---------------------------
      my $financeAccountID = _createFinanceAccount(
         type_code     => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
         currency_code => $denomination,
         license_id    => $rsLicenseID,
      );

      if ( $financeAccountID ) {

         # NB: execMode is not-needed here; we won't have a financeAccountID
         # unless the finance account was created, and that won't happen if
         # execMode isn't enabled.

         $tlObj->finance_account_id($financeAccountID);
         $tlObj->save();
         report("  Set finance_account_id($financeAccountID) "
               . "on licenseID($rsLicenseID)");

         # Create the pending transaction

         my $ptID = _createPendingTransaction(
            finance_account_id => $financeAccountID,
            amount             => $transactionAmount,
            type_code          => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
            currency_code      => $denomination,
            memo               => $transactionMemo,
         );
      }

   }# 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);
   }
   report("sanity check: total licenses created = $numNewLicenses");
}#_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};
      next if ( !$v ); # strip blank columns

      #----------------------------------------------------------
      # 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("EXCEPTION:\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 $warningCode = $row->{'warning-code'};
      my $licenseID = $row->{'rs-license-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};
         next if ( !$v ); # strip blank columns
         my $val = ($row->{$v}) ? $row->{$v} : '';

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

         push @obuf, $val;
      }

      my $importStatus;
      my $ecString;

      if ( $errorCode ) {
         $importStatus = "__FAIL__";
         $ecString = ($errorCode) ? $errorCode : '';
      } else {
         $importStatus = "license($licenseID)";
         $ecString = ($warningCode) ? $warningCode : '';
      }

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

      report($zzbuf);
   }
}

#-------------------------------------------------------------
# _createFinanceAccount -- creates a finance account
#-------------------------------------------------------------
sub _createFinanceAccount {
   my(%a) = @_;
   my $typeCode     = $a{type_code};
   my $currencyCode = $a{currency_code};
   my $licenseID    = $a{license_id};
   assert($typeCode);
   assert($currencyCode);
   assert($licenseID);

   my $financeAccountID;

   if ( $execMode ) {
      my %args = (
         currency_code => $currencyCode,
         type_code     => $typeCode,
         description   => "account for track license $licenseID",
      );
      my $o = RPS::DB::Item::FinanceAccount->Create( %args );
      $o->save();
      $financeAccountID = $o->finance_account_id;
      report("_createFinanceAccount: Created finance_acccount $financeAccountID "
         . Dumper(\%args) );
   }
   return $financeAccountID;
}

sub _createPendingTransaction {
   my(%a) = @_;

   my $financeAccountID = $a{finance_account_id};
   my $amount           = $a{amount};
   my $typeCode         = $a{type_code};
   my $currencyCode     = $a{currency_code};
   my $memo             = $a{memo};
   assert($financeAccountID);
   assert($amount);
   assert($typeCode);
   assert($currencyCode);
   assert($memo);

   my $pendingTransactionID;

   if ( $execMode ) {
      my %args = (
         finance_account_id => $financeAccountID,
         currency_code      => $currencyCode,
         type_code          => $typeCode,
         memo               => $memo,
         amount             => $amount,
      );
      my $o = RPS::DB::Item::PendingTransaction->Create( %args );
      $o->save();
      $pendingTransactionID = $o->pending_transaction_id;
      report("_createPendingTransaction: Created pending_transaction $pendingTransactionID "
         . Dumper(\%args) );
   }
   return $pendingTransactionID;

}#_createPendingTransaction

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 report {
   my($text, $level) = @_;
   $level = kNormal unless $level;
   if ( $level <= $gReportLevel ) {
      print $text . "\n";
   }
}

1;