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