package RPS::Import::Moderati;

use strict;


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

use lib '/app/tools/common/lib';
use Common::Util qw(normalize_upc normalize_isrc normalize_date);

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

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

use constant DATE_ROW => 3;
use constant DATE_COL => 3;

my %field_map = (
                 1 => {
                        'vendorid' => 4,
                        'track'    => 5,
                        'artist'   => 6,
                        'isrc'     => 8,
                        'units'    => 13,
                        'price'    => 21,
                 },
                 2 => {
                        'vendorid' => 4,
                        'track'    => 5,
                        'artist'   => 6,
                        'isrc'     => 9,
                        'units'    => 15,
                        'price'    => 25,
                 },
                 3 => {
                        'service'  => 0,
                        'vendorid' => 4,
                        'track'    => 5,
                        'artist'   => 6,
                        'format'   => 7,
                        'isrc'     => 9,
                        'upc'      => 10,
                        'units'    => 15,
                        'retail'   => 16,
                        'price'    => 23,
                 },
                 4 => {
                        'service'  => 0,
                        'vendorid' => 3,
                        'track'    => 4,
                        'artist'   => 5,
                        'units'    => 8,
                        'retail'   => 13,
                        'price'    => 22,
                 },
                 5 => {
                        service     => 1,
                        vendorid    => 5,
                        track       => 6,
                        artist      => 7,
                        isrc        => 10,
                        units       => 16,
                        price       => 24,
                 },
                 6 => {
                    service     => 0,
                    vendorid    => 4,
                    track       => 5,
                    artist      => 6,
                    format      => 7,
                    isrc        => 9,
                    upc         => 10,
                    units       => 11,
                    retail      => 16,
                    price       => 23,
                 },
                 7 => {
                    service     => 0,
                    vendor_id   => 4,
                    track       => 5,
                    artist      => 6,
                    units       => 10,
                    retail       => 13,
                    price       => 22,
                 },    
                 8 => {
                    service     => 0,
                    vendorid    => 4,
                    track       => 5,
                    artist      => 6,
                    format      => 7,
                    isrc        => 10,
                    upc         => 11,
                    units       => 12,
                    retail      => 17,
                    price       => 24,
                 },                            
);


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

	my $lines = $args{lines};
    my $fileObj = $args{file};
    my $version = $self->_version();
    my $fileName = $fileObj->OrigFileName;
    #my $fileName = $args{OrigFileName}; # for cmd-line testing
	return undef unless ($lines);

    my $offsets = $field_map{$version};
    my ($dateBegin,$dateEnd);

    if ( $version == 5 || $version == 8 )
    {
        ($dateBegin, $dateEnd) = $self->_getDates($lines->[3]->[4]);
    }
    else
    {
        ($dateBegin, $dateEnd) = $self->_getDates($fileName) if ($version!=5);
    }
    
	unless ($dateBegin && $dateEnd)
	{
        $self->errstr("couldn't get dates");
        print STDERR "date cell: ".$lines->[DATE_ROW]->[DATE_COL]."\n";
		return 0;
	}

    $self->_setDefaultFormat($fileName);
    my $billToID = 0; # only used for WMG

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

        my @billTo = RPS::Import::WMG::Util::getWirelessBillto($self->{fileServiceID});
        unless ($billTo[0])
        {
            $self->warning();
            print STDERR "unknown billto mapping: $self->{fileServiceID}\n";
        }
        $billToID = $billTo[0];

        $fileObj->Atomic(1);
        $fileObj->Save();
    }

	my $linenum = 1;
    my %errors;

	foreach my $line (@$lines)
	{
		my $vendor_id	= $line->[$offsets->{vendorid}];
		my $artist		= $line->[$offsets->{artist}];
        $artist =~ s/\s*\(Warner Music.*?\)//; # useless decoration
		my $track		= $line->[$offsets->{track}];
        $track =~ s/\s*-\s*2X$//; # useless decoration
		my $isrc    	= defined $offsets->{isrc} ? normalize_isrc($line->[$offsets->{isrc}]) : '';
		my $upc    	    = defined $offsets->{upc} ? normalize_upc($line->[$offsets->{upc}]) : '';
		my ($units)		= $line->[$offsets->{units}] =~ m/^(\-?\d+)$/;
		my $total_price = $self->numeric($line->[$offsets->{price}]);


		if ($units && $total_price && $artist && $track)
		{
	    		    
            my ($formatType, $formatName, $serviceID, $retail);
            if ($version =~ /^3|4|5|6|7|8$/)
            {
                ($formatType, $formatName) = $self->_getFormat($line->[$offsets->{format}]);
                my $service = $self->_cleanupServiceName($line->[$offsets->{service}]);
                substr($formatName,0,0) = 'brew ' if ($service =~ m/brew/i);
                $retail = $self->numeric( $line->[$offsets->{retail}] );

                unless ($serviceID = $self->getServiceID($service) || $errors{$service})
                {
                    $self->errstr('unknown service ('.$linenum . ') '.$service);
                    print STDERR "unknown service $service\n";
                    $errors{$service} = 1;
                }
            }
            else
            {
                ($formatType, $formatName) = $self->_getFormat($track);
            }

		    my $price = $total_price / $units;
            my $ppdRate = $retail ? $price / $retail : undef;
            my $priceType;

            if ($self->{clientID} == RPS::Import::Importer::WMG && $serviceID) # warner
            {
                unless (RPS::Import::WMG::Util::getWirelessSellto($serviceID, $billToID) || $errors{$serviceID})
                {
                    $self->warning();
                    print STDERR "unknown sellto mapping: $serviceID ($billToID)\n";
                    $errors{$serviceID} = 1;
                }

                unless ($priceType = RPS::Import::WMG::Util::getWirelessPriceType($formatName))
                {
                    unless ($errors{$formatName})
                    {
                        $self->warning();
                        print STDERR "unknown format mapping: $formatName\n";
                        $errors{$formatName} = 1;
                    }
                }
            }

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

            $sale->serviceID($serviceID);
			$sale->productType(RPS::File::Sale::TYPE_TRACK);
			$sale->formatType($formatType);
			$sale->dateBegin($dateBegin);
			$sale->dateEnd($dateEnd);
            $sale->serviceProductID($vendor_id);
			$sale->upc($upc);
			$sale->isrc($isrc);
			$sale->artistName($artist);
			$sale->trackName($track);
			$sale->units($units);
			$sale->price($price);
			$sale->wholesaleRate($ppdRate);
			$sale->retail($retail);
			$sale->lineNum($linenum);
			$sale->priceType($priceType);
			$sale->countryCode('US');

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

	return $linenum;
}

#
# Private methods
#

sub _getDates
{
    my $self = shift;
    my $date  = shift;
    $date =~ s/\s//g;

    if($date =~ /(\d\d\d\d)\D+(\d+)\D+(\d+)\D+(\d\d\d\d)\D+(\d+)\D+(\d+)/ )
    { # YYYY-MM-DD to YYYY-MM-DD
        my $dateBegin = $1."-".sprintf("%02d",$2)."-".sprintf("%02d",$3);
        my $dateEnd = $4."-".sprintf("%02d",$5)."-".sprintf("%02d",$6);
        return ($dateBegin,$dateEnd);
    }
    elsif($date =~ /Q(\d)(\d{2})/)
    {
        my $year = 2000 + $2;
        my $qtr = $1;
        return $self->_quarterDates( $qtr, $year );
    }
    elsif($date =~ /(\d{4})Q(\d)/)
    {
        my $year = $1;
        my $qtr  = $2;
        return $self->_quarterDates( $qtr, $year );
    }
    elsif($date =~ /(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)/)
    {
        $date =~ m/(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)/;
        my $dateBegin = "20".$3."-".sprintf("%02d",$1)."-".sprintf("%02d",$2);
        my $dateEnd = "20".$6."-".sprintf("%02d",$4)."-".sprintf("%02d",$5);
        return ($dateBegin,$dateEnd);
    }
}

sub _quarterDates
{
    my $self = shift;
    my $qtr  = shift;
    my $year = shift;

    my ($dateBegin,$dateEnd);
    if ($qtr == 1)
    {
        $dateBegin = $year."-01-01";
        $dateEnd   = $year."-03-".Days_in_Month($year, 3);
    }
    elsif ($qtr == 2)
    {
        $dateBegin = $year."-04-01";
        $dateEnd   = $year."-06-".Days_in_Month($year, 6);
    }
    elsif ($qtr == 3)
    {
        $dateBegin = $year."-07-01";
        $dateEnd   = $year."-09-".Days_in_Month($year, 9);
    }
    elsif ($qtr == 4)
    {
        $dateBegin = $year."-10-01";
        $dateEnd   = $year."-12-".Days_in_Month($year, 12);
    }
    else
    {
        return undef;
    }
    return ($dateBegin, $dateEnd);
}

sub _setDefaultFormat
{
    my $self = shift;
    my $fileName = lc $_[0];

    # Due to format mapping and retiring in FB1324, the default format type is now ringtone
    #
    ($self->{defaultFormatType}, $self->{defaultFormatName}) = 
        (RPS::File::Sale::FORMAT_RINGTONE, 'ringtone');
}

sub _getFormat
{
    my $self = shift;
    my $track = lc $_[0];

    return
        $track =~ m/\(\s*voice\s*ring/ ? (RPS::File::Sale::FORMAT_RINGTONE, 'voiceringer') : # was FORMAT_VOICERINGER (FB1324)
        ($self->{defaultFormatType}, $self->{defaultFormatName});
}

sub _cleanupServiceName
{
    my $self = shift;
    my $svc = lc $_[0];
    if ($svc =~ /modtones/)
    {
        $svc =~ s/www\.modtones\.com\s*-\s*//;
        $svc =~ s/\s*-\s*modtones.*$//;
        $svc = "att";
    }
    elsif ($svc =~ /at&t/) {
        $svc =~ s/at\&t.*$/at\&t/;
        $svc = "att";
    }
    $svc =~ s/'d/d/; # amp'd

    if ($svc =~ s/^u\.s\.\s*/us /) {}
    elsif ($svc =~ s/mobile\s*espn/espn/) {}
    elsif ($svc =~ s/\s\(cingular//) {}
    elsif ($svc =~ s/\dmy.*$//) {}
    elsif ($svc =~ s/\svzw.*$//) {}
    elsif ($svc =~ s/at\&t.*$/att/) {}
    elsif ($svc =~ s/at&t\s+mmode/mmode/) {}
    elsif ($svc =~ s/\s*-\s*.*?brand$//) {}
    elsif ($svc =~ s/\s*wallpaper//) {}
    elsif ($svc =~ s/^.+on-deck\W+(\w+)/$1/) {}
    elsif ($svc =~ s/\W+on-deck$//) {}
    elsif ($svc =~ s/_dj$//) {}
    elsif ($svc =~ s/\sringbacks//i) {}

    return $svc;
}

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