package RPS::Import::ADAPhysical::v5;

use strict;


use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date normalize_upc);
use Common::Country;
use Date::Calc qw(Days_in_Month);
use Data::Dumper;

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

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

use base 'RPS::Import::Importer';

use constant kDataNotFound => -1;

sub _fieldMap {
    {
        'sales' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            configuration   => 3,  # D
            discount        => 4,  # E
            artistName      => 8,  # I
            catalogID       => 7,  # H
            upc             => 6,  # G
            albumName       => 9,  # J
            wholesalePrice  => 17, # R
            retailPrice     => 16, # Q
            distFee         => 10, # K
            units           => 11, # L
            revenue         => 12, # M
            netUnits        => 14, # O
            netRevenue      => 13, # N            
        },
        'insound' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            configuration   => 3,  # D
            artistName      => 8,  # I
            catalogID       => 7,  # H
            upc             => 6,  # G
            albumName       => 9,  # J
            wholesalePrice  => 17, # R
            retailPrice     => 16, # Q
            distFee         => 10, # K
            units           => 11, # L
            revenue         => 12, # M
            netUnits        => 14, # O
            netRevenue      => 13, # N            
        },
        'returns' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            configuration   => 3,  # D
            artistName      => 6,  # G
            catalogID       => 5,  # F
            upc             => 4,  # E
            albumName       => 7,  # H
            wholesalePrice  => 15, # P
            retailPrice     => 14, # O
            distFee         => 8,  # I            
            units           => 9,  # J
            revenue         => 10, # K
            netUnits        => 12, # M
            netRevenue      => 11, # L            
        },            
        'international physical' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            countryCode     => 4,  # E
            artistName      => 8,  # I
            upc             => 7,  # H
            albumName       => 9,  # J
            units           => 11, # L  Gross Units
            revenue         => 12, # M
            distFee         => 10, # K            
            netRevenue      => 13, # N  Bill. Amount
        },
        'international physical sales' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            countryCode     => 4,  # E
            configuration   => 6,  # G
            artistName      => 8,  # I
            upc             => 7,  # H
            albumName       => 9,  # J
            units           => 11, # L  Gross Units
            revenue         => 12, # M
            distFee         => 10, # K            
            netRevenue      => 13, # N  Bill. Amount
        },
        'international physical returns' => {
            date_row        => 3,
            date_col        => 0,
            
            label           => 1,  # B
            countryCode     => 4,  # E
            configuration   => 6,  # G
            artistName      => 8,  # I
            upc             => 7,  # H
            albumName       => 9,  # J
            units           => 11, # L  Gross Units
            revenue         => 12, # M
            distFee         => 10, # K            
            netRevenue      => 13, # N  Bill. Amount
        },
        'd2c' => {
            date_row        => 3,
            date_col        => 0,
            
            serviceID       => 3,  # D
            configuration   => 2,  # C
            artistName      => 8,  # I
            upc             => 4,  # E
            albumName       => 9,  # J
            retailPrice     => 11, # L
            units           => 12, # M
            revenue         => 14, # O
            netUnits        => 12, # M
            netRevenue      => 14, # O
        },
    }
}


my %productTypeMap = (
   'a'   => RPS::File::Sale::TYPE_LP,
   'bd'  => RPS::File::Sale::TYPE_BLURAY,
   'bx'  => RPS::File::Sale::TYPE_DVD_CD_SET,
   'c3'  => RPS::File::Sale::TYPE_CD_SIN,
   'cd'  => RPS::File::Sale::TYPE_CD,
   'cm'  => RPS::File::Sale::TYPE_EP, 
   'cs'  => RPS::File::Sale::TYPE_CAS_SIN,
   'cx'  => RPS::File::Sale::TYPE_DVD_CD_SET,
   'dv'  => RPS::File::Sale::TYPE_DVD,
   'dx'  => RPS::File::Sale::TYPE_DVD_CD_SET,
   'ms'  => RPS::File::Sale::TYPE_LP5,
   's'   => RPS::File::Sale::TYPE_LP5,
   'tape cassette' => RPS::File::Sale::TYPE_CASS,
   'tc'            => RPS::File::Sale::TYPE_CASS,
   'vinyl' => RPS::File::Sale::TYPE_LP,
   'cd 5-inch'   => RPS::File::Sale::TYPE_CD_SIN,
   'cd with dvd' => RPS::File::Sale::TYPE_DVD_CD_SET,
   'dvd-video'   => RPS::File::Sale::TYPE_DVD,
   'blu-ray'     => RPS::File::Sale::TYPE_BLURAY,
   'blu-ray with cd' => RPS::File::Sale::TYPE_DVD_CD_SET,
);

#
# Private methods
#

sub _importLines
{
    my $self = shift;
    my %args = @_;
    my $fileObj = $args{file};
    my $version = $args{file}->VersionNum();
    my $fileName = $args{file}->OrigFileName();

    my $offsets = $self->_fieldMap;

    my $sheet_count = 0;
    my ($linenum,$serviceID);

    return undef unless (defined $args{sheets} && defined $version);

    my $sheet_names = $args{sheet_names};

    foreach my $sheet(@{ $args{sheets} })
    {

        my $sheetName = $sheet_names->[$sheet_count];

        my $sheetIndex = lc $sheetName;

        if ($sheetName ne 'Sheet3')
        {
            if ( ! exists $offsets->{ $sheetIndex } )
            {
                print STDERR "ADABase: Illegal sheet '$sheetName'\n";
                $self->errstr("Sheet '$sheetName' is not valid");
                return undef;
            }
        }

        my $data = $offsets->{$sheetIndex};

        # Get the date information from the top of each sheet
        #
        my $dateRow = $data->{date_row};
        my $dateCol = $data->{date_col};

        my ($dateBegin, $dateEnd) = $self->_getDates( $sheet->[$dateRow][$dateCol] );

        # Sometimes the date is one row up.
        if (! $dateBegin)
        {
            ($dateBegin, $dateEnd) = $self->_getDates( $sheet->[$dateRow - 1][$dateCol] );
       }

        # And sometimes it's one row down.
        if (! $dateBegin)
        {
            ($dateBegin, $dateEnd) = $self->_getDates( $sheet->[$dateRow + 1][$dateCol] );
        }


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

            my $sn = $sheet_count + 1; # sheet number (1-based)
            my $lineInfo = "Sheet $sn, line $linenum";

            # Use configuration column if available, otherwise default to CD
            #
            my $cfg = ( exists $data->{configuration} ) ? $line->[$data->{configuration}] : 'CD';

            # Use countryCode column if available, otherwise default to US
            #
            my $countryCode = ( exists $data->{countryCode} ) ? $line->[$data->{countryCode}] : 'US';

            my $catalogNumber = $line->[$data->{catalogID}];
            my $units    = $line->[$data->{units}];
            my $price    = $line->[$data->{price}];
            my $label    = $line->[$data->{label}];
            my $upc      = $line->[$data->{upc}];
            my $artist   = $line->[$data->{artistName}];
            my $title    = $line->[$data->{albumName}];     
            my $wholesale = $line->[$data->{wholesalePrice}];
            my $retail    = $line->[$data->{retailPrice}];  
            my $netUnits  = $line->[$data->{netUnits}];
            my $netRevenue = $line->[$data->{netRevenue}];   
            my $distFee    = $line->[$data->{distFee}];       
            my $discount   = ( exists $data->{discount} ) ? $line->[$data->{discount}] : 0;
            
            my $units   = $line->[$data->{units}];       
            my $revenue = $line->[$data->{revenue}];           
            $revenue *= (1 - $distFee);
            
            my $grossUnits;
            my $grossRevenue;
            my $returnUnits;
            my $returnRevenue;
            
            if ($revenue < 0)
            {
                $returnRevenue = $revenue * -1;
                if ($units < 0)
                {
                    $units *= -1;
                }
                $returnUnits = $units;   
            }
            else
            {
                $grossRevenue = $revenue;
                $grossUnits = $units;   
            }      
            
            # Skip header line
            if ( ($label && $label =~ /^label$/i) or ($units && $units =~ /units/i))
            {
                $linenum++;
                next;
            }

            if ( ($units || $revenue) && $artist && $title )
            {
                my $free = ( 100 == $discount ) ? 1 : undef;

                if( !$free )
                {
                    if( !$units && $revenue )
                    {
                       $self->errstr("Found revenue without units in $lineInfo");
                       return undef;
                    }
                    elsif( $units && !$revenue )
                    {
                       $self->errstr("Found units without revenue in $lineInfo");
                       return undef;
                    }
                }

                my $serviceID;
                if( exists $data->{serviceID} )
                {
                    my $_sname = $line->[$data->{serviceID}];
                    $serviceID = $self->getServiceID(lc ($_sname)) || $RPS::Import::ServiceMap::SERVICE_ALIASES{ lc $_sname };
                    if( !$serviceID )
                    {
                        $self->errstr("$lineInfo: Invalid service '$_sname'") if( !$serviceID );
                        return undef;
                    }
                }

                my $productType = $productTypeMap{ lc $cfg };
                if ( !$productType )
                {
                   print STDERR "Can't determine product type from '$cfg' in line $linenum";
                   $self->errstr("Can't determine product type from '$cfg' in line $linenum");
                   return undef;
                }                   

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

                $sale->free(1) if( $free );

                $sale->returns($returnUnits);
                $sale->returnsRevenue($returnRevenue);

                $sale->sales($grossUnits);
                $sale->salesRevenue($grossRevenue);
                
                $sale->totalRevenue($netRevenue);

                
                $sale->wholesalePrice($wholesale);
                $sale->retailPrice($retail);                

                $sale->productType($productType);
                $sale->dateBegin($dateBegin);
                $sale->dateEnd($dateEnd);
                $sale->labelName($label);
                $sale->albumName($title);
                $sale->serviceProductID($catalogNumber);

                $sale->upc(Common::Util::normalize_upc($upc)) if( $upc );

                $sale->artistName($artist);

                $sale->channel(RPS::File::Sale::CHANNEL_RETAIL);
                $sale->priceLevel(RPS::File::Sale::PLEVEL_FULL);
                $sale->countryCode($countryCode);
                $sale->currencyCode('USD');                            
                
                $sale->lineNum($linenum);

                $self->_insertSale(sale => $sale);

            }
            #else { print STDERR "skip s: $sheetIndex l:".$linenum." a:".$artist." t:".$title." u:".$units." p:".$price."\n"; }

            $linenum++;
        }
        $sheet_count++;
    }

    return $linenum;
}


sub _getDates
{
    my $self = shift;
    my $date = shift;
    my ($month,$year,$dateBegin,$dateEnd);
    if($date =~ /through (\w*) (\d{4})/) {
        $year = $2;
    } elsif( $date =~ /(\w*) (\d{4})/ ) {
        $year = $2;
    }
    $month = "01" if($date =~ /jan/i);
    $month = "02" if($date =~ /feb/i);
    $month = "03" if($date =~ /mar/i);
    $month = "04" if($date =~ /apr/i);
    $month = "05" if($date =~ /may/i);
    $month = "06" if($date =~ /jun/i);
    $month = "07" if($date =~ /jul/i);
    $month = "08" if($date =~ /aug/i);
    $month = "09" if($date =~ /sep/i);
    $month = "10" if($date =~ /oct/i);
    $month = "11" if($date =~ /nov/i);
    $month = "12" if($date =~ /dec/i);
    if($month ne "" && $year ne "") {
        $dateBegin = $year."-".$month."-01";
        $dateEnd = $year."-".$month."-".Days_in_Month($year,$month);
        return ($dateBegin, $dateEnd);
    }
    return undef;
}

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