package RPS::Import::ADA::ADABase;

use strict;


use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date normalize_upc);
use Date::Calc qw(Days_in_Month);
use Data::Dumper;
use Common::Country;

use lib '/app/tools/data_classes/lib';
use Client::Service;

use lib '/app/tools/rps/lib';
use RPS::File::Sale;
use RPS::Import::ServiceMap;

use base 'RPS::Import::Importer';

use constant kDataNotFound => -1;

my %mediaCodeMap = (
   'emdalbum' => {
      product_type => RPS::File::Sale::TYPE_ALBUM,
      format_type  => RPS::File::Sale::FORMAT_DOWNLOAD,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
   'emdalbumstreaming' => {
      product_type => RPS::File::Sale::TYPE_ALBUM,
      format_type  => RPS::File::Sale::FORMAT_STREAM,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },

   'emdtrack' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_DOWNLOAD,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
   'emdtrackstreaming' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_STREAM,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
   'evd' => {
      product_type => RPS::File::Sale::TYPE_TRACK,  # default; may be overriden if UPC is set
      format_type  => RPS::File::Sale::FORMAT_DOWNLOAD, # was FORMAT_VIDEO (FB1324)
      media_type   => RPS::DB::Item::MediaType::kMediaTypeVideo,
   },
   'evdstreaming' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_STREAM, # was FORMAT_VIDEOSTREAM (FB1324)
      media_type   => RPS::DB::Item::MediaType::kMediaTypeVideo,
   },

   # Ringtones
   'emastertone' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_RINGTONE,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
   'eringback' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_RINGTONE,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
   'usergenmastertone' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_RINGTONE,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },

   'usergenmastertonestreaming' => {
      product_type => RPS::File::Sale::TYPE_TRACK,
      format_type  => RPS::File::Sale::FORMAT_STREAM,
      media_type   => RPS::DB::Item::MediaType::kMediaTypeAudio,
   },
);

#
# Private methods
#

sub _importLines
{
    my $self = shift;
    my %args = @_;
    my $fileObj = $args{file};
    my $version = $args{file}->VersionNum();
    my $fileName = $args{file}->OrigFileName();

    my $offsets = $self->_fieldMap;

    my $sheet_count = 0;
    my ($linenum,$serviceID);

    return undef unless (defined $args{sheets} && defined $version);

    my $sheet_names = $args{sheet_names};

    # Check if the sales file has a global currency code, distribution fee or dates.
    #
    my ($currencyCode,$err) = $self->_getGlobalCurrencyCode( \%args );

    if ( !$currencyCode && $err == kDataNotFound )
    {
        $self->errstr("Unable to find currency code");
        return undef;
    }

    my($distFee,$err) = $self->_getGlobalDistributionFee( \%args );
    if ( !$distFee && $err == kDataNotFound )
    {
        $self->errstr("Unable to find distribution fee");
        return undef;
    }

    my($gDateBegin,$gDateEnd,$err) = $self->_getGlobalDates( \%args );
    if ( !$gDateBegin && $err == kDataNotFound )
    {
        $self->errstr("Unable to find dates");
        return undef;
    }


    foreach my $sheet(@{ $args{sheets} })
    {

        my $sheetName = $sheet_names->[$sheet_count];

        my $sheetIndex = $self->_normalizeSheetName( lc $sheetName );

        if ( ! exists $offsets->{ $sheetIndex } )
        {
            print STDERR "ADABase: Illegal sheet '$sheetName'\n";
            $self->errstr("Sheet '$sheetName' is not valid");
            return undef;
        }

        my $data = $offsets->{$sheetIndex};

        # If sheet has been flagged as not having any sales detail,
        # then no need to process it.
        #
        if ( exists $data->{no_detail} ) {
            $sheet_count++;
            next;
        }

        my $dateBegin;
        my $dateEnd;
        my $dateRow;
        my $dateCol;        

        if ($gDateBegin && $gDateEnd)
        {
            $dateBegin = $gDateBegin;
            $dateEnd = $gDateEnd;
        }
        else
        {
            #
            # Get the date information from the top of each sheet
            #
            $dateRow = $data->{date_row};
            $dateCol = $data->{date_col};
    
            ($dateBegin, $dateEnd) = $self->_getDates( $sheet->[$dateRow][$dateCol] );
    
            if ( !$dateBegin )
            {
                # Look for a row that looks like 'through MMMM YYYY' or 'MMMM YYYY'
                # We scan the first 5 lines since some files have 'extra' lines
                # in the header which makes the date_row inconsistent.
                #
                for( my $row = 0; $row < 5; $row++ )
                {
                    my $rowdata = $sheet->[$row][$dateCol];
                    if( $rowdata =~ /through (\w*) (\d{4})/ ||
                        $rowdata =~ /^(\w*) (\d{4})$/ )
                    {
                        my $_date = "$1 $2";
                        ($dateBegin, $dateEnd) = $self->_getDates( $_date );
                        last;
                    }
                }
            }


            if ( !$dateBegin )
            {
                # this is sketchy, but if the current date field looks like a date but doesn't
                # have the year, try to look for the year in the following line (FB13338)
                my $_date = $sheet->[$dateRow][$dateCol];
                my $nextRow = $sheet->[$dateRow+1][$dateCol];
                my $year;
                if( $nextRow =~ /Reported (\w*) (\d{4})/)
                {
                    $year = $2;
                    $_date .= " $year";
                    ($dateBegin, $dateEnd) = $self->_getDates( $_date );
                }
            }
        }

        unless ($dateBegin && $dateEnd) {
            $self->errstr("Sheet $sheetIndex: Couldn't get dates from: ". $sheet->[$dateRow][$dateCol] );
            print STDERR "Sheet $sheetIndex: Couldn't get dates from: ". $sheet->[$dateRow][$dateCol]."\n";
            return undef;
        }

        # Check for any column shifting; this can happen if one of the other worksheets
        # (aside from the one used for file identification) has been updated such that
        # the columns no longer match what was initially defined in the fieldMap.
        #
        $linenum = 1;
        foreach my $line(@{ $sheet })
        {
            my $sn = $sheet_count + 1; # sheet number (1-based)
            my $lineInfo = "Sheet $sn, line $linenum";

            my $label = $line->[$data->{label}] if(defined $data->{label}); 
            if($label && $sheetName =~ /^wmi$/i) # header row on WMI sheet...
            {
                $data->{clientProductID} = _getColumn('Product Identifier', $line);
                $data->{productTypeCode} = _getColumn('Product ID Type Code', $line);
                $data->{units} = _getColumn('Monthly Units', $line);
                $data->{price} = _getColumn('Revenue due Label', $line);

                last;
            }
        }

        $linenum = 1;
        foreach my $line(@{ $sheet })
        {

            my $sn = $sheet_count + 1; # sheet number (1-based)
            my $lineInfo = "Sheet $sn, line $linenum";

            my $serviceName = $line->[$data->{serviceName}];

            if ( !$serviceName || '' eq $serviceName ||
                 $serviceName =~ /^(vendor|dsp_name|dsp name)$/i  )
            {
                $linenum++; # skip header or blank lines
                next;
            }

            my $artist          = $line->[$data->{artistName}] if( defined $data->{artistName} );
            my $product_code    = $line->[$data->{product_code}] if( defined $data->{product_code} );

            my $title           = $line->[$data->{title}];

            my $units           = $line->[$data->{units}];
            $units              =~ s/,//g;
            # Case 15612 - we need to strip the comma out of the number.
            # It seems really odd that this is suddenly a problem...
            
            my $price           = $line->[$data->{price}];
            my $label           = $line->[$data->{label}] if(defined $data->{label}); 
            

            my $upc  = ( defined $data->{upc}  ) ? $line->[$data->{upc}]  : undef;
            my $isrc = ( defined $data->{isrc} ) ? $line->[$data->{isrc}] : undef;


            if ( $units && $units < 0 && $price && $price > 0 )
            {
                $self->errstr("$lineInfo: Negative units with revenue");
                #print STDERR "$lineInfo: Negative units with revenue, u($units) p($price)\n";
                return undef;
            }

            if ( !$units && $price && $price < 0 )
            {
                #$self->errstr("$lineInfo: Negative revenue, no units");
                #print STDERR "$lineInfo: Negative revenue, no units, u($units) p($price)\n";
                #return undef;
                
                # We're just going to skip this line if it has negative revenue and no units.
                $linenum++;
                next;
            }

            if( $units!=0 )
            {
                $price  /= ($units*-1) if($units<0);
                $price  /= $units if($units>0);
                if ($price < 0)
                {
                    if ($units > 0)
                    {
                        $units *= -1;
                    }
                    $price  *= -1;
                }
		        $price  -= ($price * $distFee) if ( $distFee );
            }

            my $productType;
            my $formatType;
            my $mediaType;

            my $mediaCode = $line->[ $data->{format_type} ];

            if ( $mediaCode )
            {
                $formatType  = $mediaCodeMap{ lc $mediaCode }->{ format_type };
                $productType = $mediaCodeMap{ lc $mediaCode }->{ product_type };
                $mediaType   = $mediaCodeMap{ lc $mediaCode }->{ media_type };

                if ( !$formatType && !$productType && !$mediaType )
                {
                    $self->errstr("$lineInfo: Unknown media code '$mediaCode'");
                    #print STDERR "$lineInfo: Unknown media code '$mediaCode'\n";
                    return undef;
                }
            }
            else
            {
                $self->errstr("$lineInfo: Media Code not found");
                #print STDERR "$lineInfo: Media Code not found\n";
                return undef;
            }

            #$serviceName =~ s/ DIGITAL SERVICES INC//i;
            #$serviceName =~ s/\.com//i;

            if(defined $data->{serviceName})
            {
                $serviceName = $self->_normalizeService($serviceName);

                $serviceID = $self->getServiceID(lc ($serviceName)) || $RPS::Import::ServiceMap::SERVICE_ALIASES{ lc $serviceName };
                
                if (!$serviceID)
                {
                    # Trying to get the Korean names to match up...
                    my $serviceNameCopy = $serviceName;
                    $serviceNameCopy =~ s/(.|\n)/sprintf("%02lx", ord $1)/eg;  
                    $serviceID = $RPS::Import::ServiceMap::SERVICE_ALIASES{ lc $serviceNameCopy }; 
                }
            }
            
            if ( defined $data->{productTypeCode} )
            {
                my $_productTypeCode = $line->[ $data->{productTypeCode} ];
    
                if ( $_productTypeCode =~ /upc/i )
                {
                    $upc = $line->[ $data->{clientProductID} ];
                    $productType = RPS::File::Sale::TYPE_ALBUM if( $mediaCode =~ /^evd$/i ); # FB16678
                }
                elsif( $_productTypeCode =~ /isrc/i )
                {
                    $isrc = $line->[ $data->{clientProductID} ];
                }
                elsif( $_productTypeCode ) 
                {
                    $self->errstr("$lineInfo: Unknown product type code '$_productTypeCode'");
                    return undef;
                }           
            }

            if ($productType && $artist && $artist ne "Artist" && $title && $title ne "Title")
            {
                if(defined $data->{serviceName}) {
                    unless ($serviceID) {

                        if ( $serviceName =~ /[[:^ascii:]]/ ) {
                            # Case 15436 - defaulting service names with incompatible characters to ADA
                            $serviceID = Client::Service::DSP_ADA;
                        } else {
                            $self->errstr("$lineInfo: Couldn't find service ID for: ".$serviceName);
                            return undef;
                        }                  
                    }
                }

                my $sale = RPS::Import::SaleRec->new();

                $sale->productType($productType);
                $sale->formatType($formatType);
                $sale->dateBegin($dateBegin);
                $sale->dateEnd($dateEnd);
                $sale->labelName($label) if(defined $data->{label} && $label ne "");

                if( $productType eq RPS::File::Sale::TYPE_ALBUM)
                {
                    $sale->albumName($title);
                }
                else
                {
                    $sale->trackName($title);
                    $sale->isrc(Common::Util::normalize_isrc($isrc)) if( $isrc );
                }

                $sale->upc(Common::Util::normalize_upc($upc)) if( $upc );

                $sale->serviceID($serviceID) if( $serviceName );
                $sale->artistName($artist);
                $sale->units($units);
                $sale->price($price);

                my $countryCode;
                if(defined $data->{country_code} && "" ne $data->{country_code} )
                {
                    my $countryName = $line->[$data->{country_code}];
            	    my $country = Common::Country->Get( $countryName );
            		if ($country) {
            			$countryCode = $country->alpha2();
            		} else {
            			$countryCode = $countryName;
            		}                          
                } 
                else
                {
                    $countryCode = 'US';   
                }              
                $sale->countryCode($countryCode);                            
                $sale->currencyCode($currencyCode) if ( $currencyCode );                            
                
                $sale->mediaType($mediaType);
                $sale->lineNum($linenum);
                
                my $free = $price == 0 ? 1 : 0;
                $sale->free($free);

                $self->_insertSale(sale => $sale);
            }
            else { print STDERR "skip s: $sheetIndex l:".$linenum." a:".$artist." t:".$title." u:".$units." p:".$price."\n"; }
            $linenum++;
        }
        $sheet_count++;
    }

    return $linenum;
}

sub _getColumn {
    my($colName, $line) = @_;
    #for(my $col=0; $col < 20; $col++)
    for(my $col=0; $col <= @$line; $col++)
    {
        return $col if( $line->[$col] =~ /^$colName$/i );
    }
}

sub _normalizeSheetName {
    my $self = shift;
    my $sname = shift;
    my $name;

    $name = $sname;
    if ( $sname =~ /^d-downloads$/i )
    {
        $name = "digital";
    }
    elsif ( $sname =~ /^d-streams$/i )
    {
        $name = "streams";
    }
    elsif ( $sname =~ /^d-wireless$/i )
    {
        $name = "wireless";
    }
    elsif ( $sname =~ /^d-intl$/i )
    {
        $name = "wmi";
    }
    return $name;
}

sub _getGlobalCurrencyCode
{
    my $self = shift;

    # override if you need to get a global currency code from a specific sheet

    return(undef,undef);
}

sub _getGlobalDistributionFee
{
    my $self = shift;

    # override if you need to get a global distribution fee from a specific sheet

    return(undef,undef);
}

sub _getGlobalDates
{
    my $self = shift;

    # override if you need to get global dates from a specific sheet

    return(undef,undef);
}

sub _getDates
{
    my $self = shift;
    my $date = shift;
    my ($month,$year,$dateBegin,$dateEnd);
    if($date =~ /through (\w*) (\d{4})/) {
        $year = $2;
    } elsif( $date =~ /(\w*) (\d{4})/ ) {
        $year = $2;
    }
    $month = "01" if($date =~ /jan/i);
    $month = "02" if($date =~ /feb/i);
    $month = "03" if($date =~ /mar/i);
    $month = "04" if($date =~ /apr/i);
    $month = "05" if($date =~ /may/i);
    $month = "06" if($date =~ /jun/i);
    $month = "07" if($date =~ /jul/i);
    $month = "08" if($date =~ /aug/i);
    $month = "09" if($date =~ /sep/i);
    $month = "10" if($date =~ /oct/i);
    $month = "11" if($date =~ /nov/i);
    $month = "12" if($date =~ /dec/i);
    if($month ne "" && $year ne "") {
        $dateBegin = $year."-".$month."-01";
        $dateEnd = $year."-".$month."-".Days_in_Month($year,$month);
        return ($dateBegin, $dateEnd);
    }
    return undef;
}

sub _normalizeService
{
    my $self = shift;
    my $name = shift;

    return $1 if( $name =~ /^(apple family monthly trial)/i );
    return $1 if( $name =~ /^(apple music) /i );
    return $1 if( $name =~ /^(deezer)/i );
    return "Apple Music" if( $name =~ /^(individual|family) mont(?:h)?ly /i);
    return $1 if( $name =~ /^(spotify se) /i );
    return $1 if( $name =~ /^(tencent technology) /i );
    
    return "KKBOX HONG KONG LIMITED" if($name =~ /^kkbox /i);
    return $name;
}

###
1;# Play nicely.
###