package RPS::Import::Arvato;

use strict;

use Date::Calc qw(Days_in_Month);

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

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

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

my %field_map = (
    1 => {
        sheet_name          => 'detail',
        possible_country    => 0,
        date_year           => 1,
        date_month          => 2,
        service_product_id  => 3,
        track               => 4,
        artist              => 5,
        units               => 8,
        price               => 13,
    },
    2 => {
        sheet_name          => 'tabelle1',
        possible_country    => 17,
        start_date          => 3,
        end_date            => 4,
        isrc                => 12,
        upc                 => 13,
        service_product_id  => 10,       
        track               => 15,
        artist              => 14,
        units               => 6,
        price               => 23,
    },
    3 => {
        sheet_name          => 'detail',
        possible_country    => 5,
        date_year           => 0,
        date_month          => 1,
        service_product_id  => 2,
        track               => 3,
        artist              => 4,
        units               => 7,
        price               => 12,
        conversion_sheet    => 'kumuliert',
        conversion_field    => 6,
    },
    4 => {
        sheet_name          => 'detail',
        service_product_id  => 2,
        track               => 3,
        artist              => 4,
        units               => 7,
        price               => 12,
    },
    5 => {
        sheet_name          => 'cumulated',
        service_product_id  => 0,
        track               => 2,
        artist              => 1,
        units               => 4,
        price               => 6,
    },
    6 => {
        sheet_name          => 'cumulated',
        service_product_id  => 0,
        track               => 2,
        artist              => 1,
        units               => 4,
        price               => 6,
    },
    7 => {
        sheet_name          => 'cumulated',
        track               => 2,
        artist              => 1,
        units               => 4,
        price               => 6,
    },
    8 => {
        sheet_name          => 'cumulated',
        service_product_id  => 0,
        track               => 2,
        artist              => 1,
        units               => 4,
        price               => 6,
    },
    9 => {
        sheet_name          => 'tabelle1',
        possible_country    => 17,
        start_date          => 3,
        end_date            => 4,
        isrc                => 12,
        upc                 => 13,
        service_product_id  => 10,
        track               => 15,
        artist              => 14,
        units               => 6,
        price               => 21,
    },
    10 => {
        sheet_name          => 'tabelle1',
        possible_country    => 17,
        start_date          => 3,
        end_date            => 4,
        isrc                => 12,
        upc                 => 13,
        service_product_id  => 10,
        track               => 15,
        artist              => 14,
        units               => 6,
        price               => 23,
    },
);

my %delivery_formats = (
    'Download'              => RPS::File::Sale::FORMAT_DOWNLOAD,
    'Stream'                => RPS::File::Sale::FORMAT_STREAM,
    'Limited Download'      => RPS::File::Sale::FORMAT_DOWNLOAD,
    'Over the Air Download' => RPS::File::Sale::FORMAT_DOWNLOAD,
    'Ringtone'              => RPS::File::Sale::FORMAT_RINGTONE,
    'Ringback'              => RPS::File::Sale::FORMAT_RINGBACK,
    'Over the Air Download' => RPS::File::Sale::FORMAT_DOWNLOAD,
    'Burn'                  => RPS::File::Sale::FORMAT_DOWNLOAD,
    'Other'                 => RPS::File::Sale::FORMAT_STREAM,
    'Play'                  => RPS::File::Sale::FORMAT_STREAM,
    'Realtone'              => RPS::File::Sale::FORMAT_RINGTONE,
);

my @data_delivery_formats = ('Download','Stream','Burn','Limited Download','Other','Play');

#
# public methods
#

sub _importLines
{
    my $self = shift;
    my %args = @_;
    return undef unless ($args{sheets});
    my $version = $self->_version();
    my $fileName = $args{file}->OrigFileName();
    #my $fileName = $args{OrigFileName}; # cmd-line testing
    my $format = RPS::File::Sale::FORMAT_DOWNLOAD;
    my $sheet_count = 0;
    my $sheet_names = $args{sheet_names};
    my $was_album_title = 0;
    my $sfr_conversion = 1;
    my ($linenum,%errors,$passed_format,$album,$dateBegin,$dateEnd,$total_columns,$fee_percentage,$x,$y);
    foreach my $sheet(@{ $args{sheets} }) {
        $linenum = 1;
        my $conversion_sheet = $field_map{$version}{conversion_sheet};
        foreach my $line(@$sheet) {
            if($sheet_names->[$sheet_count] =~ /$conversion_sheet/i && $linenum==1) {
                my @cols = @$line;
                for(my $c=0;$c<@cols;$c++) {
                    $field_map{$version}{conversion_field} = $c if($line->[$c] =~ /factor/i);
                }
            }
            if($sheet_names->[$sheet_count] =~ /$conversion_sheet/i && $linenum==1 && $line->[$field_map{$version}{conversion_field}] =~ /factor/i) {
                $sfr_conversion = $line->[$field_map{$version}{conversion_field}];
                $sfr_conversion =~ s/^.*Factor //;
                $sfr_conversion =~ s/\;.*$//;
                $sfr_conversion =~ s/\,/./;
            }
            $linenum++;
        }
        $sheet_count++;
    }
    $sheet_count = 0;
    foreach my $sheet(@{ $args{sheets} }) {
        $linenum = 1;
        foreach my $line (@$sheet) {
            my $test_sheet = $field_map{$version}{sheet_name};
            my $conversion_sheet = $field_map{$version}{conversion_sheet};
            if($sheet_names->[$sheet_count] =~ /$test_sheet/i && $line->[$field_map{$version}{track}] ne "" && $line->[$field_map{$version}{track}] !~ /title|tune|articlename/i) {
                
                my ($dateBegin, $dateEnd,$isrc,$upc,$country,$service_product_id);
                if ($version =~ /^(1|3)$/)
                {
                    ($dateBegin, $dateEnd) = _get_dates($line->[$field_map{$version}{date_year}],$line->[$field_map{$version}{date_month}], $version);
                }
                elsif ($version =~ /^(2|9|10)$/)
                {
                    ($dateBegin, $dateEnd) = _get_dates($line->[$field_map{$version}{start_date}],$line->[$field_map{$version}{end_date}], $version);
                }
                elsif ($version > 3)
                {
                    ($dateBegin, $dateEnd) = _get_dates($fileName,'xx',$version);
                }
                
                unless ($dateBegin && $dateEnd) {
                    $self->errstr("couldn't get dates");
                    return undef;
                }

                my $artist              = $line->[$field_map{$version}{artist}];
                my $track               = $line->[$field_map{$version}{track}];
                my $price;
                if(($version==2)&&($dateBegin eq "2007-06-01")) {
                    $price          = $line->[$field_map{$version}{price}+3];
                }
                else {
                    $price           = $line->[$field_map{$version}{price}];
                }
                my $units               = $line->[$field_map{$version}{units}];
                $price                  /= $units if($price>0 && $units>0);
                $price *= $sfr_conversion;
                $service_product_id     = $line->[$field_map{$version}{service_product_id}] if exists $field_map{$version}{service_product_id};
                $country                = _find_country($line->[$field_map{$version}{possible_country}],$fileName) if ($version<4);
                $country                = _find_country('xx',$fileName) if ($version>3);
                $country                = "GB" if ($country eq "");
                $isrc                   = $line->[$field_map{$version}{isrc}] if exists $field_map{$version}{isrc};
                $upc                    = $line->[$field_map{$version}{upc}] if exists $field_map{$version}{upc};
                
                my $sale = RPS::Import::SaleRec->new();
                $sale->productType(RPS::File::Sale::TYPE_TRACK) if($upc eq "");
                $sale->productType(RPS::File::Sale::TYPE_ALBUM) if($upc ne "");
                $sale->isrc($isrc);
                $sale->upc($upc);
                $sale->trackName($track);
                $sale->formatType($format);
                $sale->dateBegin($dateBegin);
                $sale->dateEnd($dateEnd);
                $sale->artistName($artist);
                $sale->price($price);
                $sale->units($units);
                $sale->serviceProductID($service_product_id);
                $sale->currencyCode('EUR');
                $sale->countryCode($country);
                $sale->lineNum($linenum);
                #$args{dumper}($sale); ## for cmd-line testing
                $self->_insertSale(sale => $sale);
            }
            $linenum++;
        }
        $sheet_count++;
    }
    return $linenum;
}

#
# Private methods
#

sub _get_dates
{
    my $pass_1 = shift;
    my $pass_2 = shift;
    my $this_version = shift;

    if ($this_version =~ /^(1|3)$/) {
        return (sprintf("%04d-%02d-01", $pass_1, $pass_2), sprintf("%04d-%02d-%02d", $pass_1, $pass_2, Days_in_Month($pass_1, $pass_2)));
    }
    elsif ($this_version =~ /^(2|9|10)$/) {
        my($syear,$smon,$sday) = split(/\D/,$pass_1);
        my($eyear,$emon,$eday) = split(/\D/,$pass_2);

        # some versions have a euro style date
        if ($sday > 31)
        {
            my ($syr, $eyr) = ($sday, $eday);
            ($sday, $eday) = ($syear, $eyear);
            ($syear, $eyear) = ($syr, $eyr);
        }
        return (sprintf("%04d-%02d-01", $syear,$smon,$sday), sprintf("%04d-%02d-%02d", $eyear,$emon,$eday));
    }
    elsif($this_version>3) {
        $pass_1 =~ s/^.*_//g;
        my ($start_date,$end_date) = split("-",$pass_1);
        $start_date =~ s/^.*_//g;
        $end_date =~ s/\.xls//;
        my ($sday,$smon) = split(/\./,$start_date);
        my ($eday,$emon,$eyear) = split(/\./,$end_date);
        $eyear += 2000 if ($eyear<200);
        return (sprintf("%04d-%02d-01", $eyear,$smon,$sday), sprintf("%04d-%02d-%02d", $eyear,$emon,$eday));
    }
}

sub _find_country {
    my $passed_country = shift;
    my $file_name = shift;
    my $possible_country = $passed_country . "_" . $file_name;
    my $found_country = "";
    $found_country = "DE" if ($possible_country =~ /proximus/i);
    $found_country = "GB" if ($possible_country =~ /UK/);
    $found_country = "DE" if ($possible_country =~ /DE/i);
    $found_country = "CH" if ($possible_country =~ /swiss/i);
    $found_country = "AT" if ($possible_country =~ /AT/);
    $found_country = "ES" if ($possible_country =~ /ES/);
    $found_country = "NL" if ($possible_country =~ /NL/);
    return $found_country;
}

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