package RPS::Import::Hudson;

use strict;

use lib '/app/tools/common/lib';
use Common::Client;

use Date::Calc qw(Days_in_Month);
use Spreadsheet::ParseExcel;

use lib '/app/tools/data_classes/lib';

#private constants
use constant FIELD_DATEROW	=> 4;
use constant FIELD_DATECOL	=> 0;

use constant FIELD_CONVRATE		=> 6;

use constant FIELD_VENDORID		=> 0;
use constant FIELD_TRACK		=> 1;
use constant FIELD_ARTIST		=> 2;
use constant FIELD_LABEL		=> 3;
use constant FIELD_UNITS		=> 4;
use constant FIELD_PPD_RATE		=> 6;
use constant FIELD_PRICE	    => 7;

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

#public methods

sub _importLines
{
	my $self = shift;
	my %args = @_;

	my $retval = undef;
	my $sheets = $args{sheets};

    return undef unless ($sheets);

    my $priceType = '';
    if ($self->{clientID} == RPS::Import::Importer::WMG) # warner
    {
        # check for warner specific stuff
        require RPS::Import::WMG::Util;

        my ($billTo, $sellTo, $priceType) = RPS::Import::WMG::Util::getWirelessInfo($self->{fileServiceID}, 'mastertone');
        unless ($billTo && $sellTo)
        {
            $self->warning();
            print STDERR "unknown billto/sellto mapping: $self->{fileServiceID}\n";
        }

        unless ($priceType)
        {
            $self->warning();
            print STDERR "unknown format mapping: mastertone\n";
        }

        my $fileObj = $args{file};
        $fileObj->Atomic(1);
        $fileObj->Save();
    }

	foreach my $sheet (@$sheets)
	{
        next unless ($sheet->[0]->[0] =~ m/^ROYALTY REPORT/i);

		my ($dateBegin, $dateEnd) = $self->_getDates($sheet->[FIELD_DATEROW]->[FIELD_DATECOL]);
        unless ($dateBegin && $dateEnd) {
            $self->errstr("couldn't get dates");
            print STDERR "date: $sheet->[FIELD_DATEROW]->[FIELD_DATECOL]\n";
            return undef;
        }

        my ($countryCode, $currencyCode, $conversionRate) = $self->_getCountryRates($sheet);
        unless ($countryCode && $currencyCode && ($currencyCode eq 'USD' || $conversionRate)) {
            $self->errstr("couldn't get currency info");
            return undef;
        }



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

			my $prodtype 	= RPS::File::Sale::TYPE_TRACK;
			my $format		= RPS::File::Sale::FORMAT_RINGTONE; # was FORMAT_MASTERTONE (FB1324).
			my $mediaType	= RPS::DB::Item::MediaType::kMediaTypeAudio;

			my $vendorID	= $line->[FIELD_VENDORID];
			my $artist		= $line->[FIELD_ARTIST];
			my $track		= $line->[FIELD_TRACK];
			my $label		= $line->[FIELD_LABEL];
			my $units		= $line->[FIELD_UNITS];
            my $ppdRate     = $line->[FIELD_PPD_RATE];
			my $total_price	= $line->[FIELD_PRICE];
			my $price       = $total_price / $units if($units != 0);
            $price          *= -1 if($price<0);


            # These sales have been converted to USD in the sales file.
            # We need to apply that conversion rate to the price.
            # If this client is not a base 'USD' client, then we will
            # _also_ need them to enter the conversion rate to convert
            # these US Dollars to their currency later.
            #
            if ($conversionRate > 0)
            {
                $price *= $conversionRate;
            }

            if ($units && $price && $track)
            {
                my $retail = $ppdRate ? $price / $ppdRate : '';
				my $sale = RPS::Import::SaleRec->new();

				$sale->productType($prodtype);
				$sale->formatType($format);
				$sale->mediaType($mediaType);
				$sale->dateBegin($dateBegin);
				$sale->dateEnd($dateEnd);
				$sale->serviceProductID($vendorID);
				$sale->artistName($artist);
				$sale->trackName($track);
				$sale->labelName($label);
				$sale->units($units);
				$sale->price($price);
				$sale->countryCode($countryCode);
				$sale->currencyCode($currencyCode);
                $sale->wholesaleRate($ppdRate);
                $sale->retail($retail);
                $sale->priceType($priceType);
				$sale->lineNum($linenum);

                #$args{dumper}($sale); # for cmd-line testing
				$self->_insertSale(sale => $sale);
            } # else { print STDERR "skip: u: $units p: $price t: $track\n"; }
			$linenum++;
		}
		$retval += $linenum;
	}

	return $retval;
}

#
# Private methods
#

sub _getDates {
    my $self = shift;
    my $date = shift;
    my ($qtr, $year);


    if ($date =~ m/Q(\d)(?: \([A-Z][a-z]{2})? (\d{4})/)
    {
        ($qtr, $year) = ($1, $2);
    }
    elsif ($date =~ m/Q(\d).*?(\d{4})/)
    {
        ($qtr, $year) = ($1, $2);
    }
    return unless ($qtr && $year);

    my $month = ($qtr * 3);
    return (
        sprintf("%d-%02d-01", $year, $month-2),
        sprintf("%d-%02d-%d", $year, $month, Days_in_Month($year, $month))
    );
}

sub _getCountryRates {
    my $self  = shift;
    my $lines = shift;

    foreach my $line (@$lines) {
        my $cell = $line->[FIELD_CONVRATE];
        if ($cell =~ m/^Canada --\> USA Conversion Rate of (\d+\.\d+)$/ || $cell =~ m/^Canada --\> US conversion at (\d+\.\d+)$/) {
            return ('CA','USD',$1);

#            if ('USD' eq Common::Client::Locale()->currencyFormat()->currencyCode())
#            {
#                return ('CA','USD',$1);
#            }
#            else
#            {
#                return ('CA','USD', 0);
#            }
        }
    }
    return ('US','USD',1);
}


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