package Support::Implementation::Util;

use Exporter 'import';
our @EXPORT_OK = qw(_isValidDateFmt _normalizeDate);

use lib '/app/tools/sale_import/lib';
use Import::Date;

use Spreadsheet::ParseExcel;

#---------------------------------------------------
# _isValidDateFmt - return true if the supplied date
# is in the format YYYY-MM-DD, false otherwise
#---------------------------------------------------
sub OLD__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 _isValidDateFmt {
   my($s) = @_;
   my $retval;
   my $err;
   my $year;
   my $month;
   my $day;

   my $decodedDate = Common::Client::Current()->Locale()->dateTimeFormat()->parseDate($s);
   my($_y, $_m, $_d) = split('-',$decodedDate);
   my $_s = sprintf("%04d-%02d-%02d", $_y, $_m, $_d);

   if ( $s =~ m/(\d+)-(\d+)-(\d+)/ )
   {
      report("DEBUG:_isValidDate: dateString contains hyphens");
      ($year,$month,$day) = split("-",$s);
   }
   elsif ( $s =~ m/(\d+)\/(\d+)\/(\d+)/ )
   {
      report("DEBUG:_isValidDate: dateString contains slashes");
      ($year,$month,$day) = split("/",$s);
   }
   elsif( $s =~ /\d\d\d\d\d/ )
   {
      my $date = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $s );
      report("DEBUG:_isValidDate: Excel code dateString parsed as '$date'");
      ($year,$month,$day) = split("-",$date);
   }

   if ( $year ) {
      report("DEBUG:_isValidDate: dateString($s) year($year) month($month) day($day)");
      if ( $year =~ /\D/   ||
           $month =~ /\D/  ||
           $day =~ /\D/ ) {
         report("_isValidDate: non-digit in year/mm/dd");
         $err=1;
      }
      if ( $year < 1900 || $year > 2100 ) {
         report("_isValidDate: year '$year' out of bounds");
         $err=1;
      }
      if ( $month < 1 || $month > 12 ) {
         report("_isValidDate: month '$month' out of bounds");
         $err=1;
      }
      if ( $day < 1 || $day > 31 ) { # NB: I know we're not checking for things like 6/31 ...
         report("_isValidDate: day '$day' out of bounds");
         $err=1;
      }
      $retval = (!$err) ? 1 : undef;
   }
   return $retval;
}

sub _normalizeDate {
   my($dstr) = @_;

   $dstr =~ s/\x{a0}//g;
   $dstr =~ s/\s//g;

   if ( $dstr =~ /^(\d{5})$/ ) {
      my $_dt = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $dstr );
      report("_normalizeDate:  Converted msft date $dstr to $_dt");
      $dstr = $_dt;
   }
   elsif( $dstr =~ /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/ ) # MM-DD-YYYY
   {
      my($m,$d,$y) = ($1,$2,$3);
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   elsif( $dstr =~ /^(\d{4})-(\d{2})-(\d{2})$/ ) # YYYY-MM-DD
   {
      my($y,$m,$d) = ($1,$2,$3);
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   else {
      #die "_normalizeDate: unable to process '$dstr'\n";
      report("ERROR: _normalizeDate: unable to normalize '$dstr'");
   }
   return $dstr;

}

sub _isInteger {
   my($v) = @_;
   return ( $v and $v =~ m/^\d*$/ ) ? 1 : undef;
}

sub _findTrack {
   my(%args) = @_;
   my $trackID;
   my ($albumID,$errflag) = _findAlbum(%args);
   if ( ! $albumID ) {
      my $albumName = $args{album_name} || "";
      my $catNo = $args{catalog_number} || "";
      report("   _findTrack: album($albumName) cat#($catNo) not found, skipping track");
      $errflag = kAlbumNotFound;
   } else {
      my $trackName = $args{track_name};
      $trackName =~ s/^\s+//; # remove leading spaces
      $trackName =~ s/\s+$//; # remove trailing spaces

      my $isrc = (defined $args{isrc}) ? $args{isrc} : "";
      report("   _findTrack: looking for track($trackName) isrc($isrc) on albumID($albumID)");


      my $duplicate; # set if track title is not unique
      if ( !$isrc ) {
         # No ISRC; see if track title is a duplicate
         my $cleanTitle = clean_name_catalog($trackName);
         my $sql = "SELECT track_id FROM track "
            . "WHERE album_id = $albumID AND "
            #. "( title = ". $dbo->DBQuote($trackName) . " OR title_clean LIKE '$cleanTitle%' ) ";
            . "( title = ". $dbo->DBQuote($trackName) . " OR title_clean = '$cleanTitle' ) ";
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows > 1 ) {
            report("_findTrack:DEBUG: duplicate title detected: sql = $sql");
            $errflag = kDuplicateTrackTitle;
            $duplicate = 1;
         }
      }

      if ( ! $duplicate ) {
         my $tColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
         while( $tColl->hasNext() ) {
            my $trackObj = $tColl->next();

            if ( $isrc ) {
               $isrc = trimspaces($isrc);
               my $masterID = $trackObj->master_id;
               my $mObj = RPS::DB::Item::Master->Lookup( master_id => $masterID );
               my $zzIsrc = $mObj->isrc;

               my $t1       = lc clean_name_catalog($trackName);
               my $oldClean = lc clean($trackName);  # this is deprecated, but some old catalog used this
               my $t2       = lc $trackObj->title_clean;

               my $actualTitle = $trackObj->title;

               # kludge for New World
               #$t2 =~ s/__/_/g if ( $clientID == 138 );

               $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless

               # kludge for Nettwerk; remove trailing "_" from clean title
               $t2 =~ s/_$// if ( $clientID == 51 );

               # kludge for EGR; remove trailing "_" from clean title
               $t2 =~ s/_$// if ( $clientID == 183 );

               # kludge for VP Records; remove trailing "_" from clean title (FB13163)
               #$t2 =~ s/_$// if ( $clientID == 306 );
               if ( $clientID == 306 )
               {
                  $t1       =~ s/_$//;
                  $oldClean =~ s/_$//;
                  $t2       =~ s/_$//;
               }

               if ( defined $zzIsrc ) {
                   report("_findTrack:2: comparing titles [$t1] to [$t2], isrc [$isrc] to [$zzIsrc]", kDebug );
               } else {
                   report("_findTrack:2: comparing titles [$t1] to [$t2], isrc [$isrc] to [--NOT DEFINED--] (master $masterID)", kDebug );
               }

               # The ISRC and title must match
               # If the RS track title matches but doesn't have an ISRC, then allow
               # the match.
               #
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) &&
                   (($t1 eq $t2) || ($oldClean eq $t2) || ( lc $actualTitle eq lc $trackName )) )
               {
                  $trackID = $trackObj->track_id;
                  last;
               }

               # Compare the raw track titles
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) &&
                  ( $trackName eq $trackObj->title ) )
               {
                  $trackID = $trackObj->track_id;
                  last;
               }
            } else {
               my $t1       = lc clean_name_catalog($trackName);
               my $oldClean = lc clean($trackName);  # this is deprecated, but some old catalog used this
               my $t2       = lc $trackObj->title_clean;
               my $t2NewClean = lc clean_name_catalog($trackObj->title_clean);

               my $_id = $trackObj->track_id;
               my $_dc = $trackObj->date_created;

               # kludge for New World
               $t2 =~ s/__/_/g if ( $clientID == 138 );

               $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless

               # kludge for Nettwerk; remove trailing "_" from clean title
               $t2 =~ s/_$//;

               # kludge for VP Records; remove trailing "_" from clean title (FB13163)
               $t2 =~ s/_$// if ( $clientID == 306 );

               report("_findTrack: comparing [$t1] to [$t2]", kDebug );

               #if ( $t1 eq $t2 || $oldClean eq $t2 ) {
               if ( $t1 eq $t2 || $oldClean eq $t2 || $t1 eq $t2NewClean) {
                  $trackID = $trackObj->track_id;
                  last;
               }

               # Compare the raw track titles
               if ( $trackName eq $trackObj->title ) {
                  $trackID = $trackObj->track_id;
                  last;
               }


               # XXX kludge for CMH (RSD-4793)
               if ( $trackName =~ /\.\.\./ ) { # check for three dots (ellipsis)
                  my $_trackName = $trackName;
                  $_trackName =~ s/\.\.\./\x{2026}/;  # horizontal ellipsis
#                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }
               if ( $trackName =~ /'/ ) {  # check for apostrophe
                  my $_trackName = $trackName;
                  $_trackName =~ s/'/\x{2019}/;  # RIGHT SINGLE QUOTATION MARK
#                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }
               if ( $trackName =~ /&/ ) { # check for ampersand
                  my $_trackName = $trackName;
                  $_trackName =~ s/&/and/;
#                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }

            }
         }#track loop

         $errflag = kTrackNotFound if( !$trackID );
      }


   }
   return ($trackID, $errflag);
}# _findTrack

sub _findAlbum {
   my(%args) = @_;
   my $catNo = $args{catalog_number} || "";
   my $albumName = $args{album_name};
   my $errflag;
   my $albumID;
   if ( $albumName ) {
      my %a = (
         title => $albumName,
      );
      $a{catalog_number} = $catNo if ( $catNo );
      #report("   _findAlbum: looking for CAT#($catNo) title($albumName)");

      my $cleanAlbumTitle = clean_name_catalog($albumName);
      my $_catNo = (defined $catNo ) ? $catNo : '--NOT SPECIFIED--';
      report("   _findAlbum: looking for CAT#($_catNo) title($albumName) titleClean($cleanAlbumTitle)");

      #-------------------------------------------------------------------
      # Warning: if a title has a trailing space in the database, then the
      # clean version will have a trailing underscore.  We use LIKE to try
      # and prevent this from not finding the album.
      #-------------------------------------------------------------------

      #my $sql = "SELECT album.* FROM album WHERE title_clean LIKE '$cleanAlbumTitle%' ";

      #my $sql = "SELECT album.* FROM album WHERE "
      #   . "( title = ". $dbo->DBQuote($albumName) . " OR title_clean LIKE '$cleanAlbumTitle%' ) ";

      my $sql = "SELECT album.* FROM album WHERE "
         . "( title = ". $dbo->DBQuote($albumName) . " OR title_clean = '$cleanAlbumTitle' ) ";
      $sql .= " AND catalog_number = " . $dbo->DBQuote($catNo) if ( $catNo );

      my $albumColl = RPS::DB::Item::Album->GetAll( $sql );

      if ( $albumColl->size() == 0 ) {
         $errflag = kAlbumNotFound;
         report("   _findAlbum: album not found: title($albumName) cat#($_catNo)");
      } elsif ( $albumColl->size() > 1 ) {
         report("   _findAlbum: duplicate album detected: title($albumName) cat#($_catNo)");
         $errflag = kNonUniqueAlbumName;
      }

      if ( !$errflag ) {
         my $aObj = $albumColl->next();
         $albumID = $aObj->album_id;
         report("   _findAlbum: found albumID($albumID)");
      }
   }
   else
   {
      # no album name, maybe we can use the catalog number
      if( $catNo )
      {
         my $sql = "SELECT album.* FROM album WHERE catalog_number = ".  $dbo->DBQuote($catNo);
         my $albumColl = RPS::DB::Item::Album->GetAll( $sql );
         $errflag = kAlbumNotFound if ( $albumColl->size() == 0 );
         $errflag = kNonUniqueCatalogNumber if ( $albumColl->size() > 1 );
         if ( !$errflag ) {
            my $aObj = $albumColl->next();
            $albumID = $aObj->album_id;
            report("   _findAlbum: found albumID($albumID) using catalog#");
         }

      }
   }
   return ($albumID, $errflag);
}# _findAlbum

sub _findPublisher {
   my(%args) = @_;
   my $publisherID;
   my $name = $args{publisher_name};
   $name =~ s/\s*$//g;
   $name =~ s/^\s*//g;
   my $pObj = RPS::DB::Item::Publisher->Lookup(
      publisher_name => $name,
   );
   # TODO: Should check for duplicate publisher names..
   if ( $pObj ) {
      $publisherID = $pObj->publisher_id;
   }
   return $publisherID;
}

sub _findPayor {
   my(%args) = @_;
   my $payorID;
   my $pObj = RPS::DB::Item::Payor->Lookup(
      name => $args{payor_name},
   );
   if ( $pObj ) {
      $payorID = $pObj->payor_id;
   }
   return $payorID;
}

sub _findRegion {
   my(%args) = @_;
   my $regionID;
   my $regionName = $args{name};

   my $rObj = RPS::DB::Item::Region->Lookup(
      name => $regionName,
   );
   if ( $rObj ) {
      $regionID = $rObj->region_id;
   }
   return $regionID;
}

#--------------------------------------------------------
# For 'ALL' or 'D' licenses, the region must be US or CA.
# This routine checks the specified region and sees if it
# contains any other countries besides US and/or CA
#--------------------------------------------------------
sub _validDigitalRegion {
   my($regionID) = @_;
   my $valid = 1;
   my $coll = RPS::DB::Item::RegionCountryMap->GetByRegionID($regionID);
   while($valid && $coll->hasNext() ) {
      my $rcObj = $coll->next();
      my $cc = uc $rcObj->country_code;
      $valid = 0 if ( $cc ne 'US' && $cc ne 'CA' );
   }
   return $valid;

}
#-----------------------------------------------------------
# _getCrossedID -- returns a value that can be used to set a
# license's cross_collateralized field
#-----------------------------------------------------------
sub _getCrossedID {
   my(%args) = @_;
   my $crossed = $args{crossed};
   my $crossedID = 0; # no - default
   if ( $crossed && $crossed =~ /^y/i ) {
      $crossedID = 1; 
   }
   return $crossedID;
}

#----------------------------------------------------------
# _getStatusID -- returns a value that can be used to set a
# license's 'inactive' field
#----------------------------------------------------------
sub _getStatusID {
   my(%args) = @_;
   my $status = $args{inactive};
   my $statusID = 0; # active - default
   if ( $status && $status =~ /^i/i ) {
      $statusID = 1; 
   }
   return $statusID;
}

#----------------------------------------------------------
# _getPublisherDirect -- returns a value that can be used
# to set a license's 'publisher_direct' field.
#----------------------------------------------------------
sub _getPublisherDirect {
   my(%args) = @_;
   my $pdflag = $args{publisher_direct};
   my $statusID = 0; # default is "not publisher direct"
   if ( $pdflag && $pdflag =~ /^y/i ) {
      $statusID = 1;  # publisher direct
   }
   return $statusID;
}

#--------------------------------------------------------------
# _getRateBasis -- returns a rate basis identifier based on the
# supplied text string
#--------------------------------------------------------------
sub _getRateBasis {
   my(%args) = @_;
   my $rateBasis = $args{rate_basis};
   my $rateBasisID;
   if ( !$rateBasis || ($rateBasis && $rateBasis =~ /^S/i ) ) {
      $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisSale; # Default
   } elsif ( $rateBasis && $rateBasis =~ /^L/i ) {
      $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisLock;
   }
   return $rateBasisID;
}

#-----------------------------------------------------------------
# _getRateType -- returns an RPS rate type identifier based on the
# supplied rate type string.
# If a rate type isn't found, then an undefined rate type ID will
# be returned to the caller.
#-----------------------------------------------------------------
sub _getRateType {
   my(%args) =@_;
   my $rateType = $args{rate_type};
   my $rateTypeID = undef;

   # Note: the 1.0 spec defines S, M and P as valid rate types
   # I'm including F(ULL), M(IN), P(ENNY) for now.
   if ( $rateType && '' ne $rateType ) {
      if ( $rateType =~ /^p/i ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypePenny;

      } elsif( ($rateType =~ /^s/i) || ($rateType =~ /^f/i) ||
               ($rateType =~ /ring/i) || ($rateType =~ /r/i ) ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull;

      } elsif( $rateType =~ /^m/i ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeMinimum;
      }
   }

   return $rateTypeID;
}

sub _getProductTypeID {
   my(%args) = @_;
   my $cfg = $args{product_type};
   #return RPS::DB::Item::ProductType::kAllProducts if ( $cfg =~ /^all$/i );
   return kProductTypeNotFound if ( !$cfg );
   return undef if ( $cfg =~ /^all$/i || $cfg =~ /^all products$/i );

   return RPS::DB::Item::ProductType::kAllDigitalProducts if ( ($cfg =~ /^alld$/i) || ($cfg =~ /^all-d$/i) ||
      ($cfg =~ /^all digital$/i) || ($cfg =~ /^all(?:-|\s)digital(?: products)?$/i) ); # All digital

   return RPS::DB::Item::ProductType::kAllPhysicalProducts if ( ($cfg =~ /^allp$/i) ||
      ($cfg =~ /^all-p$/i) || ($cfg =~ /^all physical(?: products)?$/i ) );

   return RPS::DB::Item::Product::kProductTypeLP if ( $cfg =~ /^lp$/i );
   return RPS::DB::Item::Product::kProductTypeCD if ( $cfg =~ /^cd$/i );
   return RPS::DB::Item::Product::kProductTypeDigital if ( $cfg =~ /^da$/i || $cfg =~ /^digital album$/i );
   return RPS::DB::Item::Product::kProductTypeDigitalTrack if ( $cfg =~ /^dt$/i );
   return RPS::DB::Item::Product::kProductTypeVHS if ( $cfg =~ /^vhs$/i );
   return RPS::DB::Item::Product::kProductTypeCass if ( $cfg =~ /^cass$/i );
   return RPS::DB::Item::Product::kProductTypeEP if ( $cfg =~ /^ep$/i );
   return RPS::DB::Item::Product::kProductTypeDVD if ( $cfg =~ /^dvd$/i );
   return RPS::DB::Item::Product::kProductTypeCDSingle if ( $cfg =~ /^cd5$/i );
   return RPS::DB::Item::Product::kProductTypeCassSingle if ( $cfg =~ /^cas5$/i );
   return RPS::DB::Item::Product::kProductTypeDVDCDSet if ( $cfg =~ /^dvdcd$/i );
   return RPS::DB::Item::Product::kProductTypeDblCD if ( $cfg =~ /^cd2$/i );
   return RPS::DB::Item::Product::kProductTypeRingtone if ( $cfg =~ /^ring$/i || $cfg =~ /^rt$/i );
   return RPS::DB::Item::Product::kProductTypeLP5 if ( $cfg =~ /^lp5$/i );
   return kProductTypeNotFound;
}

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 _getError {
   my($licensehash, $errormap) = @_;
   #-------------------------------------------------------
   # The supplied hash is assumed to be a hash of form
   # elements.  Check each one for an error...
   #-------------------------------------------------------
   foreach my $k (sort { $a cmp $b } keys %$licensehash ) {

      my $v = $licensehash->{$k};
      my $typeOf = ref $v;

      my $hasError = '';

      if ( (ref $v) =~ /Common::FormObject::Scalar/ ||
           (ref $v) eq "Common::FormObject::DateTime" ) {

         if ( $v->_hasError() ) {
            my %xmlParams = $v->getXMLParams();
            my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE";
            my $e = $xmlParams{e};
            $errormap->{$k} = $msg;
         }
      }
   }

}# _getError2

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

   report("   _reportError: checking '$name' for errors");
   if ($obj && $obj->_hasError()) {

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

sub _numberTooSmall {
   my($n) = @_;
   #return if ( $n );
   return ($n && ($n < 0.0001)) ? 1 : 0;
}

1;