package Support::Implementation::ImplementationUtil;
# 7/22/10 - Added date2MySQL
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::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);

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

@EXPORT = qw();
#@EXPORT_OK = qw(_findTrack _findAlbum report _appendString kAlbumNotFound kNonUniqueAlbumName kTrackNotFound);
@EXPORT_OK = qw( report appendString checkMissingColumn printNull date2MySQL);
@ISA = 'Exporter';

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

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

binmode STDOUT, ":utf8";

#---------------------------------------------------
# _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;
}

#---------------------------------------------
# Name: _findTrack
# Description: find an RPS track
# Arguments: A hash with the following keys:
#   album_name => $albumName (required)
#   catalog_number => $catalogNumber (optional)
#   track_name => $trackName (required)
#   isrc => $isrc (optional)
#---------------------------------------------
sub _findTrack {
   my($dbo, %args) = @_;
   my $trackID;
   my ($albumID,$errflag) = _findAlbum($dbo, %args);
   if ( ! $albumID ) {
      report("   _findTrack: album 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 $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 ( $isrc && $zzIsrc && '' ne $isrc && $isrc eq $zzIsrc ) {
               $trackID = $trackObj->track_id;
               last;
            }
         } else {
            my $t1 = lc clean($trackName);
            my $t2 = lc $trackObj->title_clean;

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

report("_findTrack: comparing [$t1] to [$t2]");
#report("_findTrack: comparing [$trackName] to [". $trackObj->title . "]");
            #if ( (lc $trackName) eq (lc $trackObj->title) ) {
            #if ( (lc clean($trackName)) eq (lc $trackObj->title_clean) ) {
            if ( $t1 eq $t2 ) {
               $trackID = $trackObj->track_id;
               last;
            }
         }
      }
      $errflag = kTrackNotFound if( !$trackID );
   }
   return ($trackID, $errflag);
}# _findTrack


#---------------------------------------------
# Name: _findAlbum
# Description: find an RPS album
# Arguments: A hash with the following keys:
#   album_name => $albumName (required)
#   catalog_number => $catalogNumber (optional)
#---------------------------------------------
sub _findAlbum {
   my($dbo, %args) = @_;
   my $catNo = $args{catalog_number} || "";
   my $albumName = $args{album_name};
   my $errflag;
   my $albumID;
   #report("DEBUG: _findAlbum: args = ".Dumper(\%args) );
   if ( $albumName ) {
      my %a = (
         title => $albumName,
      );
      $a{catalog_number} = $catNo if ( $catNo );
      #report("   _findAlbum: looking for CAT#($catNo) title($albumName)");

      my $cleanAlbumTitle = clean($albumName);
      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=". $dbo->DBQuote($cleanAlbumTitle);
      #$sql .= " AND catalog_number = " . $dbo->DBQuote($catNo) if ( $catNo );

      my $sql = "SELECT album.* FROM album WHERE title_clean LIKE '$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 ( !$errflag ) {
         my $aObj = $albumColl->next();
         $albumID = $aObj->album_id;
         report("   _findAlbum: found albumID($albumID)");
      }
   }
   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};

   # hack -- need an external way of transforming
   # region names
   $regionName = "United States" if ( $args{name} eq 'DOMESTIC' );

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

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

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

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

   report("   _getError: 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};
      $map->{$name} = $msg;
      #print "$name has an error:: $e :: $msg\n";
      #return 1;
   }
   #return undef;
}

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 ) {
#      report("_getError: examining k($k)");
      my $v = $licensehash->{$k};
      my $typeOf = ref $v;

      my $hasError = '';

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

#         report("   _getError: checking '$k' for errors");

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

}# _dumpError2

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

#------------------------------------------------------------------
# _checkMissingColumn - checks if a column is missing or not.  Also
# ensures that a required columns (prefixed with an asterisk) has
# data in it.  A string containing error(s) is returned, otherwise
# undef is returned if column is OK.
#------------------------------------------------------------------
sub checkMissingColumn {

   my($colName, $rows, $excount) = @_;
   my $errStr;
   if ( ! exists $rows->{$colName} ) {
      report("_checkMissingColumn -- column '$colName' is missing");
      appendString($errStr, "Column '$colName' not found");

      my $colClean = clean($colName);
      my $key = "column_" . $colClean . "_not_found";
      ++$excount->{$key};
   }

   #------------------------------------------------------------------
   # If the column name starts with an asterisk then it can't be blank
   #------------------------------------------------------------------
   if ( $colName =~ /^\*/ ) {
      my $v = $rows->{$colName};
      # strip out leading spaces
      $v =~ s/^\s*//g;
      if ( '' eq $v ) {
         appendString($errStr, "Column '$colName' can't be blank");
      }
   }
   return $errStr;

}# _checkMissingColumn

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

#----------------------------------------------------------
# date2MySQL - converts a date to MySQL format (YYYY-MM-DD)
#----------------------------------------------------------
sub date2MySQL {

   my($s) = @_;
   my $retval;
   my $err;
   my $year;
   my $month;
   my $day;

   if ( $s =~ m/(\d+)-(\d+)-(\d+)/ ) {
      #report("DEBUG:date2MySQL: dateString contains hyphens");
      ($year,$month,$day) = split("-",$s);
   } elsif ( $s =~ m/(\d+)\/(\d+)\/(\d+)/ ) {
      #report("DEBUG:date2MySQL: dateString contains slashes");
      ($year,$month,$day) = split("/",$s);
   }

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

}#date2MySQL

1;