package RPS::Import::NugsRevised;

#Created from specs in Fog Bugz Case 9654
# !!! JPK- What?  That case is all about the RSFormat importer...

use strict;

use Date::Calc qw(Days_in_Month Decode_Month);

use lib '/app/tools/common/lib';
#use Common::Util;
use Common::Util qw(normalize_date normalize_isrc);
use Common::Consts qw(%COUNTRY_CODE %CODE_TO_COUNTRY);

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

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

#my %field_map = (
#    1 =>
#    {
#        label              => 0,
#        service_id         => 1,
#        country            => 3,
#        date_begin         => 4,
#        date_end           => 5,
#        product_type       => 6,
#        format             => 7,
#        service_product_id => 9,
#        client_product_id  => 10,
#        artist             => 11,
#        upc                => 13,
#        album              => 14,
#        isrc               => 15,
#        track_number       => 16,
#        track              => 17,
#        units              => 18,
#        currency_code      => 19,
#        free               => 22,
#        price              => 24,
#        media_type         => 25,

#    },
#);
my %fieldMap = (
digital => {
            single => {
                date_row               => 5,
                date_col               => 0,
                service_product_id     => 0,
                title                  => 1,
                units                  => 3,
                returns                => 7,
                price                  => 11,
            },
            multiple => {
                date_row               => 5,
                date_col               => 0,
                service_product_id     => 1,
                title                  => 2,
                units                  => 4,
                returns                => 8,
                price                  => 11,
            },
        },
physical => {
            single => {
                date_row           => 5,
                date_col           => 0,
                service_product_id => 0,
                title              => 1,
                retail_price       => 2,
                sales              => 3,
                sales_revenue      => 4,
                returns            => 7,
                returns_revenue    => 8,
                shipping_fee       => 9,
                service_fee        => 10,
                total_revenue      => 11,
            },
             multiple => {
                date_row           => 5,
                date_col           => 0,
                service_product_id => 1,
                title              => 2,
                sales              => 4,
                sales_revenue      => 5,
                returns            => 8,
                returns_revenue    => 9,
                shipping_fee       => 10,
                service_fee        => 11,
                total_revenue      => 12,
            },
         },
);

#public methods

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

    my $fileName = $args{file}->OrigFileName();

    if($fileName =~ /digital/i ){
        return _importDigital($self, @_);
    }
    elsif($fileName =~ /physical/i ){
        return _importPhysical($self, @_);
    }
    else{
        $self->errstr("Could not determine product type from filename: $fileName");
        print STDERR "Could not determine product type from filename: $fileName\n";
        return undef;
    }

    #my %args = @_;

    #return undef unless ($lines);

    #my $lines = $args{lines};
    #my $version = $args{file}->VersionNum();
    #my $fileName = $args{file}->OrigFileName();
    #my @sheets = @{ $args{sheets} };

    #my $linenum = 1;

    #my $offsets = $field_map{$version};
    #my %errors = ();

}

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

    my $lines = $args{lines};
    my $version = $args{file}->VersionNum();
    my $fileName = $args{file}->OrigFileName();
    my @sheets = @{ $args{sheets} };
    my $sheet_names = $args{sheet_names};
    my $sheet_count = 0;

    return undef unless ($lines);

    my $linenum = 1;

    #my $offsets = $field_map{$version};
    my %errors = ();

    my $currencyCode = "USD";
    my $countryCode  = "US";
    my $format       = RPS::File::Sale::FORMAT_DOWNLOAD;
    my $mediaType    = RPS::DB::Item::MediaType::kMediaTypeAudio;

    foreach my $sheet(@{ $args{sheets} }) {
        $linenum = 1;
        my $sheet_name = $sheet_names->[$sheet_count];
        my $offsets;


        #print STDERR "Processing sheet: $sheet_name\n";

        if($sheet_name !~ /total/i) {
            if($sheet_name =~ /single/i){

                $offsets = $fieldMap{"digital"}{"single"};

            }
            elsif($sheet_name =~ /package/i){

                $offsets = $fieldMap{"digital"}{"multiple"};

                my $price_col = _getPriceCol($sheet);

                if($price_col){
                    $offsets->{price} = _getPriceCol($sheet);
                    #print STDERR "Got price column: $price_col\n";
                }
                else{
                    $self->errstr("Could not get 'NET REVENUE' column for package tab");

                    print STDERR "Could not get 'NET REVENUE' column for package tab\n";

                    return undef;

                }

            }
            else{

                $self->errstr("Could not determine tab type(single or multiple): ".
                $sheet_names->[$sheet_count]);

                print STDERR "Could not determine tab type(single or multiple): ".
                $sheet_names->[$sheet_count]."\n";

                return undef;
            }

            my ($dateBegin, $dateEnd);
            my $f_date = $sheet->[$offsets->{date_row}]->[$offsets->{date_col}];

            if($f_date =~ /^report period:\D*(\d{1,2})\D(\d{4})$/i){
                my $month = sprintf("%02d",$1);
                my $year = $2;

                ($dateBegin, $dateEnd) =
                $self->_getDates(date_begin=>"$year-$month-01", type=>"iso");
            }

            unless($dateBegin && $dateEnd){
                if ($fileName =~ /(\d)Q(\d{4})/) {
                    my $quarter = "q" . $1 . " " . $2;
                    ($dateBegin, $dateEnd) =
                    $self->_getDates(date_begin=>$quarter, type=>"quarter");                                
                } else {               
                    ($dateBegin, $dateEnd) =
                    $self->_getDates(date_begin=>$fileName, type=>"text");
                }
            }

            unless($dateBegin && $dateEnd){
                $self->errstr("Could not get dates from: $f_date");

                print STDERR "Could not get dates from: $f_date\n";

                return undef;
            }

            foreach my $line (@$sheet) {
                my $serviceProductID = $line->[$offsets->{service_product_id}];
                my $title = $line->[$offsets->{title}];
                my $units = $line->[$offsets->{units}];
                my $returns = $line->[$offsets->{returns}];
                my $price = $line->[$offsets->{price}];

                my $productType;
                #$product    = RPS::File::Sale::TYPE_ALBUM if($price > .99 && $sales_type eq "digital");
                $title =~ /^([A-Za-z]+).*/;
                my $shortCode = $1;

                if($shortCode && ($shortCode eq lc $shortCode)){
                    #print STDERR "Found short code album: $shortCode line num: $linenum\n";
                    $productType = RPS::File::Sale::TYPE_TRACK;
                }
                elsif($shortCode && ($shortCode eq uc $shortCode)){
                    #print STDERR "Found short code track: $shortCode line num: $linenum\n";
                    $productType = RPS::File::Sale::TYPE_ALBUM;
                }

                if($serviceProductID && $title &&
                $units =~ /\d+/ && $price =~ /\d+/ && $title !~ /total/i){

                    unless($productType){
                        $self->errstr("Could not process short code from: ".
                        "$title at line number: $linenum");

                        print STDERR "Could not process short code from: ".
                        "$title at line number: $linenum\n";

                        return undef;
                    }

                    my $sale = RPS::Import::SaleRec->new();
                    $sale->serviceProductID($serviceProductID);
                    $sale->albumName($title) if($productType eq RPS::File::Sale::TYPE_ALBUM);
                    $sale->trackName($title) if($productType eq RPS::File::Sale::TYPE_TRACK);

                    $sale->dateBegin($dateBegin);
                    $sale->dateEnd($dateEnd);
                    $sale->countryCode($countryCode);
                    $sale->currencyCode($currencyCode);
                    $sale->productType($productType);
                    $sale->formatType($format);
                    $sale->mediaType($mediaType);
                    $sale->lineNum($linenum);

                    if(($units == 0) && ($price != 0)){

                        if($price < 0){
                            $sale->units(-1);
                            $sale->price(abs $price);
                            $self->_insertSale(sale => $sale);

                            $sale->units(1);
                            $sale->price(0);
                            $self->_insertSale(sale => $sale);
                        }
                        elsif($price > 0){
                            $sale->units(1);
                            $sale->price($price);
                            $self->_insertSale(sale => $sale);

                            $sale->units(-1);
                            $sale->price(0);
                            $self->_insertSale(sale => $sale);
                        }
                    }
                    else{

                        if(($units > 0) && ($price > 0)){
                            $price /= $units;
                        }
                        elsif(($units > 0) && ($price < 0)){
                            $price = (abs $price)/$units;
                            $units *= -1;
                        }
                        elsif(($units < 0) && ($price > 0)){
                            $price = $price / (abs $units);
                        }
                        elsif(($units < 0) && ($price < 0)){
                            $price = (abs $price) / (abs $units);
                        }

                        $sale->units($units);
                        $sale->free(1) if($price == 0);
                        $sale->price($price) if($price != 0);
                         #$args{dumper}($sale);  ## for cmd-line testing
                        $self->_insertSale(sale => $sale);
                    }
                }

                $linenum++;
            }


        }
        $sheet_count++;
    }

    return $linenum;
}

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

    my $lines = $args{lines};
    my $version = $args{file}->VersionNum();
    my $fileName = $args{file}->OrigFileName();
    my $fileObj = $args{file};
    my @sheets = @{ $args{sheets} };
    my $sheet_names = $args{sheet_names};
    my $sheet_count = 0;

    return undef unless ($lines);

    my $linenum = 1;

    #my $offsets = $field_map{$version};
    my %errors = ();

    my $currencyCode = "USD";
    my $countryCode  = "US";
    my $productType = RPS::File::Sale::TYPE_CD;
    my $mediaType   = RPS::DB::Item::MediaType::kMediaTypeAudio;
    my $channel     = RPS::File::Sale::CHANNEL_MAILORDER;
    my $priceLevel  = RPS::File::Sale::PLEVEL_FULL;

    foreach my $sheet(@{ $args{sheets} }) {
        $linenum = 1;
        my $sheet_name = $sheet_names->[$sheet_count];
        my $offsets;

        #print STDERR "Processing sheet: $sheet_name\n";

        if($sheet_name !~ /total/i) {
            if($sheet_name =~ /single/i){

                $offsets = $fieldMap{"physical"}{"single"};

            }
            elsif($sheet_name =~ /cd sales/i){
                $offsets = $fieldMap{"physical"}{"single"};
            }
            elsif($sheet_name =~ /package/i){

                $offsets = $fieldMap{"physical"}{"multiple"};

                my $price_col = _getPriceCol($sheet);

                if($price_col){
                    $offsets->{price} = _getPriceCol($sheet);
                    #print STDERR "Got price column: $price_col\n";
                }
                else{
                    $self->errstr("Could not get 'NET REVENUE' column for package tab");

                    print STDERR "Could not get 'NET REVENUE' column for package tab\n";

                    return undef;

                }

            }
            else{

                $self->errstr("Could not determine tab type(single or multiple): ".
                $sheet_names->[$sheet_count]);

                print STDERR "Could not determine tab type(single or multiple): ".
                $sheet_names->[$sheet_count]."\n";

                return undef;
            }

            my ($dateBegin, $dateEnd);
            my $f_date = $sheet->[$offsets->{date_row}]->[$offsets->{date_col}];

            if($f_date =~ /^report period:\D*(\d{1,2})\D(\d{4})$/i){
                my $month = sprintf("%02d",$1);
                my $year = $2;

                ($dateBegin, $dateEnd) =
                $self->_getDates(date_begin=>"$year-$month-01", type=>"iso");
            }

            unless($dateBegin && $dateEnd){
                if ($fileName =~ /(\d)Q(\d{4})/) {
                    my $quarter = "q" . $1 . " " . $2;
                    ($dateBegin, $dateEnd) =
                    $self->_getDates(date_begin=>$quarter, type=>"quarter");                                
                } else {               
                    ($dateBegin, $dateEnd) =
                    $self->_getDates(date_begin=>$fileName, type=>"text");
                }
            }

            unless($dateBegin && $dateEnd){
                $self->errstr("Could not get dates from: $f_date");

                print STDERR "Could not get dates from: $f_date\n";

                return undef;
            }

            foreach my $line (@$sheet) {
                my $serviceProductID = $line->[$offsets->{service_product_id}];
                my $title = $line->[$offsets->{title}];
                my $sales = $line->[$offsets->{sales}];
                my $salesRevenue = $line->[$offsets->{sales_revenue}];
                my $returns = $line->[$offsets->{returns}];
                my $returnsRevenue = $line->[$offsets->{returns_revenue}];
                my $retailPrice = $line->[$offsets->{retail_price}];

                my $shippingFee = $line->[$offsets->{shipping_fee}]
                if(defined $offsets->{shipping_fee});

                my $serviceFee  = $line->[$offsets->{service_fee}]
                if(defined $offsets->{service_fee});
                my $f_totalRevenue = $line->[$offsets->{total_revenue}];

                $salesRevenue -= $shippingFee if(defined $offsets->{shipping_fee});
                $salesRevenue -= $serviceFee  if(defined $offsets->{service_fee});

                my $totalRevenue = $salesRevenue - $returnsRevenue;

                #print STDERR "Line number: $linenum Total revenue: $f_totalRevenue ".
                #"calculated revenue: $totalRevenue\n";


                if($serviceProductID && $title &&
                $sales =~ /\d+/ && $totalRevenue =~ /\d+/ &&
                $title !~ /total/i){

                    # !!! I don't know why we're doing this check, or if this is even valid.
                    #
#                    if($f_totalRevenue != $totalRevenue){
#                        $self->errstr("Total revenue in file $f_totalRevenue ".
#                        "does not match calculated total revenue $totalRevenue ".
#                        "at line number: $linenum");
#
#                        print STDERR "Total revenue in file $f_totalRevenue".
#                        "does not match calculated total revenue $totalRevenue ".
#                        "at line number: $linenum\n";
#                        return undef;
#                    }

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

                    $sale->dateBegin($dateBegin);
                    $sale->dateEnd($dateEnd);
                    $sale->countryCode($countryCode);
                    $sale->currencyCode($currencyCode);
                    $sale->productType($productType);
                    $sale->mediaType($mediaType);
                    $sale->retailPrice($retailPrice);

                    $sale->channel($channel);
                    $sale->priceLevel($priceLevel);
                    $sale->sales($sales);
                    $sale->salesRevenue($salesRevenue);
                    $sale->returns($returns);
                    $sale->returnsRevenue($returnsRevenue);
                    $sale->totalRevenue($totalRevenue);
                    $sale->lineNum($linenum);
                    $self->_insertSale(sale => $sale);
                }
                #else{print STDERR
                #"linenum: $linenum Svc prod ID: $serviceProductID title: ".
                #"$title sales: $sales total revenue: $totalRevenue\n"}


                $linenum++;
            }


        }
        $sheet_count++;
    }

    $fileObj->Physical(1);
    $fileObj->Save();

    return $linenum;
}


sub _getPriceCol{
    my $sheet = shift;

    foreach my $line (@$sheet) {
        my @line_array = @$line;
        my $line_size = @line_array;

        foreach my $col(0..($line_size - 1)){
            if($line_array[$col] =~ /net revenue/i){
                return $col;
            }
        }
    }

    return undef;

}

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