package RPS::Import::BMGSonyPhys2;

use strict;

use Date::Calc qw(Days_in_Month Decode_Month);

use lib '/app/tools/common/lib';
use Common::Consts qw(%CURRENCY_TO_COUNTRY);
use lib '/app/tools/data_classes/lib';

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

use constant {
    CATALOG_NUM             => 0,
    ARTIST_TITLE            => 1,
    PROD_TYPE               => 2,
    UNITS_SALES             => 3,
    UNITS_RETURNS           => 4,
    REVENUE_SALES           => 5,
    REVENUE_DISCOUNT        => 6,
    REVENUE_RETURNS         => 7,
    REVENUE_TOTAL           => 9, # should be sum of cols 5,7
    PPD                     => 10,
};

my %PROD_TYPES =
(
    IACB => RPS::File::Sale::TYPE_CD,
    IACF => RPS::File::Sale::TYPE_CD,
    IACM => RPS::File::Sale::TYPE_CD,
    IADZ => RPS::File::Sale::TYPE_DVD,
    IAEZ => RPS::File::Sale::TYPE_DVD,
    ISCZ => RPS::File::Sale::TYPE_CD,
    IVDZ => RPS::File::Sale::TYPE_DVD,
    IVEZ => RPS::File::Sale::TYPE_DVD,
    LACB => RPS::File::Sale::TYPE_CD,
    LACF => RPS::File::Sale::TYPE_CD,
    LACM => RPS::File::Sale::TYPE_CD,
    LAEZ => RPS::File::Sale::TYPE_DVD,
    LVEZ => RPS::File::Sale::TYPE_DVD,
);

sub _importLines
{
    my $self = shift;
    my %args = @_;
    my $summarySheet = $args{sheets}->[0]; # will it always b sheet 0?
    my $dataSheet = $args{sheets}->[2]; # will it always b sheet 2?
    my $fileObj = $args{file};
    $fileObj->Physical(1);

    my $summary = $self->_getFileSummary($summarySheet);
    unless ($summary->{dateBegin} && $summary->{dateEnd}) {
        $self->errstr("couldn't get dates");
        return undef;
    }
    unless ($summary->{rate})
    {
        $self->errstr("couldn't get rate");
        return undef;
    }
    unless ($summary->{currency})
    {
        $self->errstr("couldn't get currency");
        return undef;
    }
    unless ($summary->{country} = $CURRENCY_TO_COUNTRY{$summary->{currency}})
    {
        $self->errstr("couldn't map currency to country");
        return undef;
    }

    my $sumNetSales = 0;
    my %errors = ();
    my $lineNum = 1;
    foreach my $line (@$dataSheet)
    {
        my $unitsSales      = $line->[UNITS_SALES];
        my $unitsReturns    = $line->[UNITS_RETURNS];

        my $revenueSales    = $line->[REVENUE_SALES];
        my $revenueReturns  = $self->numeric($line->[REVENUE_RETURNS]);
        my $revenueDiscount = $line->[REVENUE_DISCOUNT];
        my $revenueTotal    = $line->[REVENUE_TOTAL];
        my $ppd             = $line->[PPD];

        $revenueReturns *= -1 if (defined $revenueReturns);

        my $catNum = $line->[CATALOG_NUM];
        my $artTitle = $line->[ARTIST_TITLE];

        next unless ($artTitle && defined $revenueReturns);

        my ($artist, $title) = $self->_parseDescription($artTitle);

        my $config = uc $line->[PROD_TYPE];
        my $prodType = $PROD_TYPES{$config} || '';

        unless ($prodType || $errors{$config})
        {
            $self->errstr('unknown format');
            print STDERR "unknown config: $config line: $lineNum\n";
            $errors{$config} = 1;
            return undef;
        }

        $sumNetSales += $revenueTotal;

        if (abs($revenueTotal - ($revenueSales - $revenueReturns)) > 1 && $title ne "")
        {
            print STDERR $lineNum . " " .$revenueTotal . " != ". $revenueSales ."-". $revenueReturns."\n";
            $self->errstr('mismatched total line:'.$lineNum);
            return undef;
        }

        my $sales =
            (($revenueSales == 0 && $revenueDiscount == 0) || $revenueSales + $revenueDiscount == 0) ? 0 :
            int($unitsSales * ($revenueSales / ($revenueSales + $revenueDiscount)) + 0.5);
        #print STDERR "returns: $unitsReturns\n";
        my $returns = $unitsReturns == 0 ? $unitsReturns : int($unitsReturns * ($revenueReturns / ($unitsReturns * $ppd)) + 0.5);

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

        $sale->productType($prodType);
        $sale->dateBegin($summary->{dateBegin});
        $sale->dateEnd($summary->{dateEnd});
        $sale->clientProductID($catNum);
        $sale->artistName($artist);
        $sale->albumName($title);
        $sale->sales($sales);
        $sale->salesRevenue($revenueSales);
        $sale->returns($returns);
        $sale->returnsRevenue($revenueReturns);
        $sale->totalRevenue($revenueTotal);
        $sale->channel(RPS::File::Sale::CHANNEL_RETAIL);
        $sale->priceLevel(RPS::File::Sale::PLEVEL_FULL);
        $sale->countryCode($summary->{country});
        $sale->currencyCode($summary->{currency});
        $sale->lineNum($lineNum);

        $self->_insertSale(sale => $sale);
        #$args{dumper}($sale);

    }
    continue {
        $lineNum++;
    }

    if (abs($sumNetSales - $summary->{netSales}) > 1)
    {
        #$self->errstr('total sales mismatch');
        #return 0;
    }

    return $lineNum;
}

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

    my ($month, $year, $netSales, $netPayable, $currency);
    foreach my $row(@$lines)
    {
        if (!$month && $row->[0] =~ m/^(\w+)\s+(\d+)/)
        {
            ($month, $year) = ($1, $2);
        }

        if (!$netSales && lc($row->[0]) =~ m/^net sales/)
        {
            $netSales = $self->_getFirstNumericColumn($row, 1);
        }

        if (!$netPayable && lc($row->[0]) =~ m/^net amount payable to/)
        {
            $netPayable = $self->_getFirstNumericColumn($row, 1);
            next;
        }

        # the same label shows up again and has the currency code
        if ($netPayable && lc($row->[0]) =~ m/^net amount payable to/)
        {
            foreach my $cell(@$row)
            {
                if ($cell =~ m/^[A-Z]{3}/)
                {
                    $currency = $cell;
                    last;
                }
            }
            last;
        }
    }

    return undef unless ($year && $month);

    $month = Decode_Month($month) unless ($month =~ m/\d+/);
    $year += 2000 unless ($year > 1999);
    $netSales ||= 1; # in case it's not set so we don't divide by 0

    return {
        dateBegin   => sprintf("%d-%02d-01", $year, $month),
        dateEnd     => sprintf("%d-%02d-%d", $year, $month, Days_in_Month($year, $month)),
        netSales    => $netSales,
        rate        => $netPayable / $netSales,
        currency    => uc $currency,
    };
}

sub _getFirstNumericColumn
{
    my $self = shift;
    my $row = shift;
    my $start = shift || 0;

    for (my $i=$start; $i <= scalar @$row; $i++)
    {
        my $numeric = $self->numeric($row->[$i]);
        return $numeric if (defined $numeric);
    }

    return undef;
}

sub _parseDescription
{
    my $self = shift;
    my $description = shift;

    $description =~ s/\s*\b\d?dvd-?\s*//i;
    $description =~ s/\s*\dcd(-|\s+|\b)//i;

    $description =~ s/^(.+?)\///;
    my $artist = $1;
    $artist =~ s/^(.+),(.+)$/$2 $1/;
    $artist =~ s/^\s+//;
    $artist =~ s/\s+$//;

    $description =~ s/^\s+//;
    $description =~ s/\s+$//;

    return ($artist, $description);
}

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