package RPS::Import::VidZoneDigital;

use strict;

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

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

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

my %field_map = (
    1 => {
        isrc    => 0,  # A - ISRC
        artist  => 1,  # B - ARTIST
        track   => 2,  # C - TITLE
        price   => 9,  # J - VALUE
        format  => 6,  # G - CONTENT TYPE
        units   => 8,  # I - QUANTITY
        label   => 10, # K
    },
);

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

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

#public methods

sub _importLines
{
    my $self = shift;
    my %args = @_;
    my $fileObj = $args{file};
    my $version = $args{file}->VersionNum();
    my $fileName = $args{file}->OrigFileName();
    my $offsets = $field_map{$version};
    my $sheet_count = 0;
    return undef unless (defined $args{sheets} && defined $version);

    my $sheet_names = $args{sheet_names};

    my $countryCode;

    # We store all sales as GBP, however some prices may need to be converted
    # to GBP using conversion rate information from the summary tab.
    #
    my $currency = "GBP";

    my $linenum;

    # %conversion holds conversion rates found on the summary sheet.
    # The key will be a valid country code.
    #
    my %conversion;

    my( $dateBegin,$dateEnd ) = _getDates($fileName);

    unless ($dateBegin && $dateEnd) {
        print STDERR "Couldn't find dates from:".$fileName."\n";
        $self->errstr("Couldn't find dates from:".$fileName);
        return undef;
    }

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

        #--------------------------------------------------------------------
        #
        # The summary sheet contains currency conversion rate information,
        # and must occur before the sales detail sheet(s).  This will allow
        # the conversion rate to be used when processing the detail sheet(s).
        #
        #--------------------------------------------------------------------
        if($sheet_names->[$sheet_count] =~ /summary/i)
        {
            my ($area_column, $conversion_rate_column);
            foreach my $line(@$sheet)
            {
                if( !defined $area_column )
                {
                    my $x = 0;
                    foreach my $column(@$line)
                    {
                        $area_column = $x if($column =~ /TERRITORY/i); 
                        $conversion_rate_column = $x if($column =~ /CONVERSION RATE/i);
                        $x++;
                    }
                }
                else
                {
                    my $territory       = lc $line->[$area_column];
                    my $conversionRate  = $line->[$conversion_rate_column];

                    if( $area_column ne "" && $territory ne "" && $territory !~ /^(territory|total)$/i )
                    {

                        my $countryCode = _getCountryCode($territory);
                        if( !$countryCode )
                        {
                            $self->errstr("Invalid country $territory");
                            return undef;
                        }

                        if( $countryCode ne "" && $conversionRate ne "" )
                        {
                            my $_conversionRate;

                            if( $conversionRate ne "n/a" )
                            {
                                $_conversionRate = $conversionRate;
                            }
                            else
                            {
                                $_conversionRate = 1;
                            }

                            $conversion{$countryCode} = $_conversionRate;

                            #print STDERR "DEBUG: stored terr($countryCode) conv($_conversionRate)\n";
                        }
                    }
                    #else { print STDERR "DEBUG: area_column($area_column) conversion_rate_column($conversion_rate_column)\n"; }

                }
            }

        }

        #--------------------------------------------------------------------
        #
        # Process the detail sheet(s).
        #
        #--------------------------------------------------------------------
        else
        {
            $linenum = 1;

            # Get the territory from the sheet name.
            #
            my $territory;
            if( $sheet_names->[$sheet_count] =~ m/Sales (.+)/i )
            {
                $territory = lc $1;
            }


            $countryCode = _getCountryCode($territory);
            if( !$countryCode )
            {
                $self->errstr("Invalid country $territory");
                return undef;
            }

            my $conversionRate = $conversion{$countryCode};

            foreach my $line (@$sheet)
            {
                my $isrc   = $line->[$offsets->{'isrc'}];
                my $artist = $line->[$offsets->{'artist'}];
                my $title  = $line->[$offsets->{'track'}];
                my $units  = $line->[$offsets->{'units'}];
                my $price  = $line->[$offsets->{'price'}];
                my $label  = $line->[$offsets->{'label'}];

                my $format;
                my $mediaType = RPS::DB::Item::MediaType::kMediaTypeAudio;

                if( $line->[$offsets->{format}] =~ /download/i )
                {
                    $format = RPS::File::Sale::FORMAT_DOWNLOAD;
                }
                elsif( $line->[$offsets->{format}] =~ /tone/i )
                {
                    $format = RPS::File::Sale::FORMAT_RINGTONE;
                }
                elsif( $line->[$offsets->{format}] =~ /stream/i )
                {
                    $format = RPS::File::Sale::FORMAT_STREAM;
                }

                $price       /= $units if($units != 0);


                if ($units && $artist && $artist ne "ARTIST" && $title && $title ne "TITLE")
                {

                    # Apply the conversion rate to the price if needed
                    #
                    $price *= $conversionRate if( $conversionRate != 1 );

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

                    $sale->productType(RPS::File::Sale::TYPE_TRACK);
                    $sale->formatType($format);
                    $sale->isrc($isrc);
                    $sale->dateBegin($dateBegin);
                    $sale->dateEnd($dateEnd);
                    $sale->currencyCode($currency);
                    $sale->countryCode($countryCode);
                    $sale->artistName($artist);
                    $sale->trackName($title);
                    $sale->labelName($label) if($label ne "");
                    $sale->units($units);
                    $sale->price($price);
                    $sale->lineNum($linenum);

                    $self->_insertSale(sale => $sale);
                }
                #else { print STDERR "skip s:".$sheet_names->[$sheet_count]." l:".$linenum." u: $units p: $price a: $artist t: $title\n"; }
                $linenum++;
            }
        }
        $sheet_count++;
    }
    return $linenum;
}

#
# Private methods
#

sub _getDates {
    my $passed_date = shift;
    my ($month,$monthName,$emonth,$year,$qtr,$dateBegin,$dateEnd);
    $monthName = $passed_date;
    if($passed_date =~ m/(\d{4}) (\d+)\-(\d+)/) {
        $year = $1;
        $month  = $2;
        $emonth = $3;
    }
    elsif($passed_date =~ m/(\d{4}) (\d+)\.xls/) {
        $year = $1;
        $month = $2;
    }
    elsif($passed_date =~ m/(\w+)_(\d{4})/) {
        $year = $2;
        $monthName = $1;
    }
    elsif($passed_date =~ m/ (\w+) (\d{4})\.xls/) {
        $year = $2;
        $monthName = $1; 
    }
    $monthName =~ s/^.*_//g;
    $monthName = "jan" if($monthName =~ /jan/i);
    $monthName = "feb" if($monthName =~ /feb/i);
    $monthName = "mar" if($monthName =~ /mar/i);
    $monthName = "apr" if($monthName =~ /apr/i);
    $monthName = "may" if($monthName =~ /may/i);
    $monthName = "jun" if($monthName =~ /jun/i);
    $monthName = "jul" if($monthName =~ /jul/i);
    $monthName = "aug" if($monthName =~ /aug/i);
    $monthName = "sep" if($monthName =~ /sep/i);
    $monthName = "oct" if($monthName =~ /oct/i);
    $monthName = "nov" if($monthName =~ /nov/i);
    $monthName = "dec" if($monthName =~ /dec/i);
    $month = Decode_Month($monthName) if($month eq "" && $monthName ne "");
    if($month ne "" & $year ne "") {
        # Don't forget to 0-pad the month...
        #
        $month = sprintf("%02d", $month);
        $emonth = sprintf("%02d", $emonth) if ($emonth ne "");

        $dateBegin = $year."-".$month."-01";
        $dateEnd = $year."-".$month."-".sprintf("%02d",Days_in_Month($year,$month));
        $dateEnd = $year."-".$emonth."-".sprintf("%02d",Days_in_Month($year,$emonth)) if($emonth ne "");
        return ($dateBegin,$dateEnd);
    }
    else {
        return undef;
    }
}

sub _getCountryCode {
    my($territory) = @_;

    # %countryMap contains mappings of non-standard country names
    # to a valid country codes.
    #
    my %countryMap = (
       'roi' => 'IE', # Republic of Ireland => Ireland
    );

    my $countryName = $countryMap{$territory} || $territory;

    my $countryObj  = Common::Country->Get( $countryName );

    my $countryCode = $countryObj->alpha2() if( $countryObj );

    return $countryCode;
}

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