package Support::Implementation::MCPSLicenseReserveTemplate;
# Change history:
# 6/8/10 - Initial revision per FB11971.
# 11/29/10 - Added support for 'price' column per FB12801.
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 Support::Implementation::ExcelReader;
use Support::Implementation::TabDelimitedReader;
use Support::Implementation::SearchUtil;
use Support::Implementation::ImplementationUtil qw( report appendString checkMissingColumn printNull );


use RPS::DB::Item::McpsLicenseRetention;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductPrice;

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
#
# 6/4/10: gTemplateHeader is obsolete?
#-----------------------------------------------------------------------
my %gTemplateHeader = (
   "Client Product ID" => 0,  # A

   "Units Held 1"      => 1,  # B
   "Price Level 1"     => 2,  # C
   "Price 1"           => 3,  # D

   "Units Held 2"      => 4,  # E
   "Price Level 2"     => 5,  # F
   "Price 2"           => 6,  # G

   "Units Held 3"      => 7,  # H
   "Price Level 3"     => 8,  # I
   "Price 3"           => 9,  # J

   "Units Held 4"      => 10, # K
   "Price Level 4"     => 11, # L
   "Price 4"           => 12, # M
);

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


#----------------------------------
# excount keeps track of exceptions
#----------------------------------
my %gExCount;

#--------------------------------------
# gCount keeps track of entities created
#--------------------------------------
my %gCount;

#-------------------------------
# Reference to SearchUtil object
#-------------------------------
my $gSearchObj;

#-------------------------------------------------------------------
# gPriceLevelIDMap - Maps uppercase price level name to internal ID.
# gPriceLevelNameMap - Maps ID to price level name.
# Initialized in processData().
#-------------------------------------------------------------------
my %gPriceLevelIDMap;
my %gPriceLevelNameMap;

my $gDefaultPayorID;
my %gPayorMap = (); # maps names to ID

my $clientID;
my $execMode;

my $dbo;
my $cdbo;
my $dbh;

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


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

   report("PublisherTemplate::_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();
   $dbh = $dbo->DBH;
   $cdbo = Common::RSApp::GetCommonDB();

   my $fileName = $self->name;

   $gSearchObj = Support::Implementation::SearchUtil->new(
      clientID => $clientID,
   );

   if( $self->isExcel2003( $fileName ) ) {
      print("MCPSLicenseReserveTemplate::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("MCPSLicenseReserveTemplate::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};

   #----------------------------------------
   # Reset the exception and entity counters
   #----------------------------------------
   #%gExCount = ();
   #%gCount = ();

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

   #my %gPriceLevelIDMap; # maps uppercase price level name to internal ID
   $sql = "SELECT price_level_id, name FROM price_level";
   $sth = $cdbo->DoCmd($sql);
   while( my($id, $name) = $sth->fetchrow_array() ) {
      $gPriceLevelIDMap{uc $name} = $id;
      $gPriceLevelNameMap{$id} = $name;
   }

   #---------------------------------
   # 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)");
   }

   foreach my $row (@$rows) {

      #======================================================================
      # Read in the column information.
      #
      # IMPORTANT: Except for 'rowid', the hash names _MUST_ match the column
      # names in the template.
      #======================================================================
      my $rowid            = $row->{'rowid'};
      my $clientProductID  = $row->{'Client Product ID'}; # A

      my $unitsHeld1       = $row->{'Units Held 1'};      # B
      my $priceLevel1      = $row->{'Price Level 1'};     # C
      my $price1           = $row->{'Price 1'};           # D

      my $unitsHeld2       = $row->{'Units Held 2'};      # E
      my $priceLevel2      = $row->{'Price Level 2'};     # F
      my $price2           = $row->{'Price 2'};           # G

      my $unitsHeld3       = $row->{'Units Held 3'};      # H
      my $priceLevel3      = $row->{'Price Level 3'};     # I
      my $price3           = $row->{'Price 3'};           # J

      my $unitsHeld4       = $row->{'Units Held 4'};      # K
      my $priceLevel4      = $row->{'Price Level 4'};     # L
      my $price4           = $row->{'Price 4'};           # M
      #my $periodsCompleted   = $row->{''}; # J

      my $errorCode;

      # Remove trailing spaces
      $clientProductID  =~ s/\s*$//g;
      $unitsHeld1       =~ s/\s*$//g if ( $unitsHeld1 );
      $unitsHeld2       =~ s/\s*$//g if ( $unitsHeld2 );
      $unitsHeld3       =~ s/\s*$//g if ( $unitsHeld3 );
      $unitsHeld4       =~ s/\s*$//g if ( $unitsHeld4 );
      $priceLevel1      =~ s/\s*$//g if ( $priceLevel1 );
      $priceLevel2      =~ s/\s*$//g if ( $priceLevel2 );
      $priceLevel3      =~ s/\s*$//g if ( $priceLevel3 );
      $priceLevel4      =~ s/\s*$//g if ( $priceLevel4 );

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


      #--------------------------------------------------------------
      #
      # Validate the clientProductID information.  It should uniquely
      # uniquely match the product.product_code field.
      #
      #--------------------------------------------------------------
      my $errFlag;
      my $productID; # This will contain the RS product ID

      my $sql = "SELECT product_id FROM product "
         . "WHERE product_code='$clientProductID'";
      my $sth = $dbo->DoCmd($sql);
      if ( $sth->rows == 0 ) {
         appendString( $errorCode, "Product ID not found");
         ++$gExCount{invalid_productid};
      } elsif( $sth->rows > 1 ) {
         appendString( $errorCode, "Product ID not unique");
         report("EXCEPTION: productCode($clientProductID) is not unique\n");
         ++$gExCount{productid_not_unique};
      } else {
         ($productID) = $sth->fetchrow_array();
      }

      #-----------------------------------------------
      # Find the MCPS license attached to the product.
      # Also get the retention_category from the license.
      #-----------------------------------------------
      my $licenseID;
      my $retentionCategory;

      if ( $productID ) {
         my $sql = "SELECT mcps_license_id, retention_category "
            . "FROM mcps_license "
            . "WHERE product_id=$productID";
         my $sth = $dbo->DoCmd($sql);

         if ( $sth->rows == 0 ) {
            appendString( $errorCode, "No MCPS license");
            ++$gExCount{no_mcps_license};
         } elsif( $sth->rows > 1 ) {
            #die("multiple mcps licenses?!\n");
            report("EXCEPTION: multiple mcps licenses for productID($productID)?!\n");
            appendString( $errorCode, "Multiple MCPS licenses");
            ++$gExCount{multiple_mcps_licenses};
         } else {
            ($licenseID, $retentionCategory) = $sth->fetchrow_array();
         }
      }



      #
      # Validate the units
      #
      my $e1 = _isValidUnits($unitsHeld1);
      appendString( $errorCode, "Units1 - $e1") if ( $e1 );

      my $e2 = _isValidUnits($unitsHeld2);
      appendString( $errorCode, "Units2 - $e2") if ( $e2 );

      my $e3 = _isValidUnits($unitsHeld3);
      appendString( $errorCode, "Units3 - $e3") if ( $e3 );

      my $e4 = _isValidUnits($unitsHeld4);
      appendString( $errorCode, "Units4 - $e4") if ( $e4 );



      #
      # Validate the price level
      #
      appendString( $errorCode, "Price level 1 invalid")
         if ( $priceLevel1 and (! exists $gPriceLevelIDMap{uc $priceLevel1}) );

      appendString( $errorCode, "Price level 2 invalid")
         if ( $priceLevel2 and (! exists $gPriceLevelIDMap{uc $priceLevel2}) );

      appendString( $errorCode, "Price level 3 invalid")
         if ( $priceLevel3 and (! exists $gPriceLevelIDMap{uc $priceLevel3}) );

      appendString( $errorCode, "Price level 4 invalid")
         if ( $priceLevel4 and (! exists $gPriceLevelIDMap{uc $priceLevel3}) );


      # Make sure the price exists on the product
      #
      my $e1 = _isValidPrice($price1) if ( $price1 );
      appendString( $errorCode, "Price1 - $e1") if ( $e1 );

      my $e2 = _isValidPrice($price2) if ( $price2 );
      appendString( $errorCode, "Price2 - $e2") if ( $e2 );

      my $e3 = _isValidPrice($price3) if ( $price3 );
      appendString( $errorCode, "Price3 - $e3") if ( $e3 );

      my $e4 = _isValidPrice($price4) if ( $price4 );
      appendString( $errorCode, "Price4 - $e4") if ( $e4 );

      #=======================================
      # If there are any errors, we're done...
      #=======================================
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         next;
      }

      my %unitMap;
      $unitMap{1} = $unitsHeld1 if ( $unitsHeld1 );
      $unitMap{2} = $unitsHeld2 if ( $unitsHeld2 );
      $unitMap{3} = $unitsHeld3 if ( $unitsHeld3 );
      $unitMap{4} = $unitsHeld4 if ( $unitsHeld4 );

      my %priceLevelIDMap;
      $priceLevelIDMap{1} = $gPriceLevelIDMap{uc $priceLevel1} if ( $priceLevel1 );
      $priceLevelIDMap{2} = $gPriceLevelIDMap{uc $priceLevel2} if ( $priceLevel2 );
      $priceLevelIDMap{3} = $gPriceLevelIDMap{uc $priceLevel3} if ( $priceLevel3 );
      $priceLevelIDMap{4} = $gPriceLevelIDMap{uc $priceLevel4} if ( $priceLevel4 );

      my %priceMap;
      $priceMap{1} = $price1 if ( $price1 );
      $priceMap{2} = $price2 if ( $price2 );
      $priceMap{3} = $price3 if ( $price3 );
      $priceMap{4} = $price4 if ( $price4 );

      my %productPriceMap;

      my %errMap;

      foreach my $period (keys %unitMap) {

         # if a price was specified then use it, otherwise do a lookup
         # based on the price level
         if( $priceMap{$period} ) {
            $productPriceMap{$period} = $priceMap{$period};
         } else {

            # Check if we're missing any product prices
            my $priceLevelID = $priceLevelIDMap{$period};
            if ( $priceLevelID ) {
               my $sql = "SELECT p.retail, p.wholesale, p.ppd "
                  . "FROM product_price pp "
                  . "JOIN price p USING(price_id) "
                  . "WHERE pp.product_id=$productID "
                  . "AND pp.price_level_id=$priceLevelID "
                  ;
               my $sth = $dbo->DoCmd($sql);

               if ( $sth->rows == 0 ) {

                  #appendString( $errorCode, "Missing ". $gPriceLevelNameMap{$priceLevelID} ." price" );
                  my $c = "Missing ". $gPriceLevelNameMap{$priceLevelID} ." price";
                  $errMap{$c} = 1;

               } else {
                  while( my($retail, $wholesale, $ppd) = $sth->fetchrow_array() ) {
                     report("DEBUG: productID($productID) retail($retail) wholesale($wholesale) ppd($ppd)");


                     #$productPriceMap{$period} = $ppd; # TODO - ppd => MOS

                     #-------------------------------------------------------------------
                     # Per Ryan, if a user enters a historical retention through the UI,
                     # they are _not_ prompted for a price, and we default it to the
                     # wholesale price when creating the mcps_license_retention. - 7/1/10
                     #-------------------------------------------------------------------
                     $productPriceMap{$period} = $wholesale;
                  }
               }

            }
         }
      }
      foreach my $err (keys %errMap) {
         appendString( $errorCode, $err );
      }
     
      #=======================================
      # If there are any errors, we're done... (this time I mean it)
      #=======================================
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         next;
      }




      foreach my $period (keys %unitMap) {

         #------------------------------------
         # Otherwise create the reserve!!!
         #------------------------------------
         my %args = (
            license_id         => $licenseID,
            period_id          => $period,
            units              => $unitMap{$period},
            price_level_id     => $priceLevelIDMap{$period},
            #price              => $productPriceMap{$period},
            retention_category => $retentionCategory,
         );

         #
         # If the price is specified for a given period in the template, then
         # use it when creating the reserve.  Otherwise we'll get the price
         # associated with the product price level.
         #
         if ( $priceMap{$period} ) {
            $args{price} = $priceMap{$period};
         } else {
            $args{price} = $productPriceMap{$period};
         }

         my($reserveID, $err) = _createMCPSLicenseReserve( %args );

         if ( $err ) {
            $row->{'error-code'} = $err;
         } else  {

            my $cur = $row->{'rs-reserve-id'};
            appendString( $cur, $reserveID);

            report("  DEBUG: created reserve $reserveID") if ( $reserveID );
            $row->{'rs-reserve-id'} = $cur;
         }
      }
   }

   #------------------------------------------------------------
   # 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 %gExCount) {
      my $v = $gExCount{$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 %gCount) {
      my $v = $gCount{$c};
      printf("%30s %d\n", $c, $v);
   }

}#_processData

sub _isValidUnits {
   my($units) = @_;
   my $retval;
   my $err;

   if ( $units and $units !~ m/\d*/ ) {
      appendString( $err, "units must be integer");
      ++$gExCount{units_must_be_integer};
   } elsif( $units and $units < 0 ) {
      appendString( $err, "negative units");
      ++$gExCount{negative_units};
   }
   return $err;
}

sub _isValidPrice {
   my($price) = @_;
   my $err;
   #return ( $v and $v =~ m/^\d*\.?\d*$/ ) ? 1 : undef;
   if ( $price and $price !~ m/^\d*\.?\d*$/ ) {
      appendString( $err, "invalid price");
      ++$gExCount{invalid_price};
   }
   return $err;
}

#--------------------------------------------------------------
# _isValidDVDCategory: Check if the specified category is valid
# Returns: 1 if category is valid, undef otherwise.
#--------------------------------------------------------------
sub _isValidDVDCategory {
   my($c) = @_;
   my $retval;
   if ( (lc $c eq "a" ) or
        (lc $c eq "b" ) or
        (lc $c eq "c" ) or
        (lc $c eq "avp" ) ) {
      $retval = 1;
   }
   return $retval;
}

#
# _createMCPSLicenseReserve
#
sub _createMCPSLicenseReserve {
   my( %args ) = @_;
   
   my $licenseID         = $args{license_id};
   my $periodID          = $args{period_id};
   my $priceLevelID      = $args{price_level_id};
   my $units             = $args{units};
   my $retentionCategory = $args{retention_category};
   my $price             = $args{price};

   assert($licenseID);
   assert($periodID);
   assert($priceLevelID);
   assert($units);
   assert($retentionCategory);
   assert($price);

   my $reserveID; # set if we create a reserve
   my $err; # set if we can't create a reserve

   #------------------------
   # MCPS Arguments
   #------------------------
   my %a = (
      mcps_license_id    => $licenseID,
      period_id          => $periodID,
      price_level_id     => $priceLevelID,
      units_held         => $units,
      retention_category => $retentionCategory,
      price              => $price,
   );

   my $o = RPS::DB::Item::McpsLicenseRetention->Lookup( %a );
   if ( $o ) {
      my $id = $o->mcps_license_id;
      report("EXISTS: mcps_license_retention $id");
      $err = "Reserve exists";
   } else {
      if ( $execMode ) {
         $o = RPS::DB::Item::McpsLicenseRetention->Create( %a );
         $o->save();
         $reserveID = $o->mcps_license_retention_id;
         report("   Created mcps_license_retention $reserveID : ". Dumper(\%a));
         ++$gCount{mcps_license};
      } else {
         report("   Non-exec mode, skipped mcps_license_retention : ". Dumper(\%a));
      }
   }

   return($reserveID, $err);

} #_createMCPSLicenseRetention

#--------------------------------------------------------------------------
# _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
# payeeID will be stored in the import status column ("payeeID(###)"),
# 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 and their status
   #---------------------------------------
   foreach my $row (@$rows) {
      my $rowid = $row->{rowid};
report("dumping row($rowid)");

      my $errorCode = $row->{'error-code'};
      my $reserveID = $row->{'rs-reserve-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;
      }

      $reserveID = "NULL" if ( !$reserveID );

      my $importStatus;
      if ( $errorCode ) {
         $importStatus = "__FAIL__";
      } else {
         $importStatus = "reserve($reserveID)";
      }
      #my $importStatus = ($errorCode) ? "__FAIL__" : "payee($payeeID)";

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

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

      report($zzbuf);
   }
}#_showExceptions

#----------------------------------------------------------------------
# _normalizeDate convert the template date into a MySQL-compatible date
#----------------------------------------------------------------------
sub _normalizeDate {
   my($dt) = @_;
   my($month,$day,$year) = split("/",$dt);
   return join("-", $year, $month, $day);
}

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

1;