# 9/26/12: Changed to use clean_name_catalog() instead of clean()
# for catalog-related lookups.
#
# Change History:
#  9/29/15 - Updated _findAlbum to search using title_version if
#    title/catalog doesn't work. FB11437
#  2/26/16 - Search only for non-null title_version if title/cat doesn't work
#  3/19/19 - Fixed duplicate track detection; removed client-specific code
#
package Support::Implementation::SearchUtil;
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_name_catalog 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 Exporter;

#use vars qw(@ISA @EXPORT @EXPORT_OK %NORM_SUBSTITUTIONS );
use vars qw(@ISA @EXPORT @EXPORT_OK);
#
@EXPORT = qw();
@EXPORT_OK = qw(_findTrack _findAlbum report _appendString kAlbumNotFound kNonUniqueAlbumName kTrackNotFound);
@ISA = 'Exporter';
#





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 kNonUniqueCatalogNumber => 103;
use constant kDuplicateTrack         => 104;
use constant kProductTypeNotFound    => 99999;

binmode STDOUT, ":utf8";

my $clientID;
my $execMode;

my $dbo;

my %gAlbumTitleVersionCache;

sub new {
   my( $proto, %args ) = @_;
   #my $proto = shift;
   my $class = ref($proto) || $proto;
   my $self = {};
   print "SearchUtil::new -- proto($proto)\n";
   print "SearchUtil::new -- args: ".Dumper(\%args) . "\n";
   #my $self = {
   #   _clientID => shift,
   #};
   #my $self = bless {}, $class;
   #bless $self, $class;

   bless( $self, $class );
   return $self->_init(%args);
}

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

   $clientID = $args{clientID};
   assert($clientID);
   report("SearchUtil::_init -- clientID = " . $clientID );

   #my $app = Common::RSApp->new( clientID => $clientID );
   $dbo = Common::RSApp::GetClientDB();

   return $self;
}

#---------------------------------------------------
# _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 findTrack {
   my($self, %args) = @_;
   return _findTrack(%args);
}

sub _findTrack {
   my(%args) = @_;
   my $trackID;

   my $errflag;

   my $debug = (defined $args{debug}) ? 1 : 0;

   my $albumID = (defined $args{album_id}) ? $args{album_id} : undef;

   ($albumID,$errflag) = _findAlbum(%args) if ( !$albumID );

   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+// if ( $trackName ); # remove leading spaces
      $trackName =~ s/\s+$// if ( $trackName ); # remove trailing spaces

      my $isrc = (defined $args{isrc}) ? $args{isrc} : "";

      my $prefix = "SearchUtil::_findTrack: albumID($albumID):";

      report("$prefix looking for track(" . _printNull($trackName) . ") "
         . "isrc(". _printNull($isrc) .")") if ( $debug );

      my %seenTrack;

      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;
            #if ( '' ne $isrc && $isrc eq $zzIsrc )

            if ( $trackName && '' ne $trackName ) {
               # If we have a trackName, then both the ISRC and trackName must match
               my $t1 = lc clean_name_catalog($trackName);
               my $t2 = lc $trackObj->title_clean;

               # The ISRC and title must match
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) && ($t1 eq $t2) ) {
                  $trackID = $trackObj->track_id;
                  #last;
                  if ( !exists $seenTrack{$trackName} ) {
                      $seenTrack{$trackName} = 1;
                  } else {
                      report("$prefix albumID($albumID) Duplicate track '$trackName' and isrc '$isrc'");
                      return( 0, kDuplicateTrack );
                  }
               }

            } else {
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) ) {
                  $trackID = $trackObj->track_id;
                  #last;
                  if ( !exists $seenTrack{$isrc} ) {
                      $seenTrack{$isrc} = 1;
                  } else {
                      report("$prefix albumID($albumID) Duplicate isrc '$isrc'");
                      return( 0, kDuplicateTrack );
                  }
               }
            }

         } else {
            my $t1 = lc clean_name_catalog($trackName);
            my $t2 = lc $trackObj->title_clean;

#report("SearchUtil::_findTrack: comparing [$t1] to [$t2]");
report("$prefix comparing [$t1] to [$t2]") if ( $debug );
            if ( $t1 eq $t2 ) {
               $trackID = $trackObj->track_id;
               #last;
               if ( !exists $seenTrack{$trackName} ) {
                   $seenTrack{$trackName} = 1;
               } else {
                   report("$prefix albumID($albumID) Duplicate track '$trackName'");
                   return( 0, kDuplicateTrack );
               }
            }
         }
      }

      $errflag = kTrackNotFound if( !$trackID );
   }
   return ($trackID, $errflag);
}# _findTrack

sub findAlbum {
   my($self,%args) = @_;
   return _findAlbum(%args);
}

sub _findAlbum {
   my(%args) = @_;
   my $catNo = $args{catalog_number} || "";
   my $albumName = $args{album_name};
   my $debug = (defined $args{debug}) ? 1 : 0;
   my $errflag;
   my $albumID;

   $catNo =~ s/^\s*//g;
   $catNo =~ s/\s*$//g;
   $albumName =~ s/^\s*//g if ( $albumName );
   $albumName =~ s/\s*$//g if ( $albumName );

   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);
      report("   _findAlbum: looking for CAT#($catNo) title($albumName) titleClean($cleanAlbumTitle)");

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

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

      $errflag = kAlbumNotFound if ( $albumColl->size() == 0 );
      $errflag = kNonUniqueAlbumName if ( $albumColl->size() > 1 );

      if( $albumColl->size() == 0 )
      {
          # Check if the template title matches the album title + title_version
          # (this is what the UI catalog page shows)
          # Note: I'm adding this section in due to testing with Case 11437.  In that case,
          # the template album titles have the title_version info as part of the album title,
          # thus causing any album lookup by title alone to fail.  The wonky thing is that
          # title from the template has a NBSP (char 160 or xA0) in it:
          #
          #     Some album title\xA0(version stuff)
          #
          # This has to be manually stripped otherwise clean_name_catalog won't work as expected.

          if( (keys %gAlbumTitleVersionCache) == 0 )
          {
              my $sql = "SELECT album_id, title, title_version FROM album WHERE title_version IS NOT NULL";
              my $sth = $dbo->DoCmd($sql);
              while( my( $_albumID, $_title, $_titleVersion) = $sth->fetchrow_array() )
              {
                  my $rkey = $_title . ' (' . ($_titleVersion || ''). ')';
                  my $ckey = clean_name_catalog($rkey);
#                  report("D:_findAlbum: [id $_albumID]  rkey($rkey) --> ckey($ckey)"); # XXX
                  $gAlbumTitleVersionCache{$ckey} = $_albumID;
              }
          };

          my $_aKey = $albumName;
          $_aKey =~ s/\xA0/ /;
          my $_cleanAlbumName = clean_name_catalog($_aKey);

          $albumID = (exists $gAlbumTitleVersionCache{$_cleanAlbumName}) ?  $gAlbumTitleVersionCache{$_cleanAlbumName} : undef;

          report("   _findAlbum: found album($_cleanAlbumName)\n\talbumID $albumID with title_version") if( $albumID ); # XXX
          undef $errflag if( $albumID );
      }

      if ( !$errflag ) {
         if( !$albumID ) {
             my $aObj = $albumColl->next();
             $albumID = $aObj->album_id;
         }
         report("   _findAlbum: found albumID($albumID)");
      }

   } elsif( $catNo && "" ne $catNo ) {
      report("DEBUG:SearchUtil::_findAlbum -- searching via catalog number '$catNo'");

      my $sql = "SELECT album.* FROM album WHERE catalog_number =  '$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)");
      }
      

   } else {
      $errflag = kAlbumNotFound;
   }
   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 => $args{publisher_name},
      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;
}

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) ) {
         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull;
      } elsif( $rateType =~ /^m/i ) {
         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeMinimum;
      }
   } else {
      $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull; # Default
   }

   assert($rateTypeID);
   #report("_getRateType: rateType($rateType) ---> id($rateTypeID)");
   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 );
   return RPS::DB::Item::ProductType::kAllPhysicalProducts if ( ($cfg =~ /^allp$/i) || ($cfg =~ /^all-p$/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 =~ /^d$/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 kProductTypeNotFound;
}

sub _printNull {
   my($s) = @_;
   return ($s) ? $s : "NULL";
}

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;