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.
###