package RPS::Import::ADA;

use strict;


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

use lib '/app/tools/data_classes/lib';
use Client::Service;

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

my %field_map = (
    1 => {
        date_row        => 2,
        date_col        => 0,
        upc             => { 0 =>  3, },
        product_code_type => { 0 =>  4, },
        product_code    => { 0 =>  5, },
        artist          => { 0 =>  6, },
        track           => { 0 =>  7, },
        units           => { 0 =>  9, },
        price           => { 0 => 11, },          
    },  
    
    2 => {
        date_row        => 1,
        date_col        => 0, 
        label           => 0,
        format_type     => 2,
        product_code_type    => {
            digital         => 6,
            wmi             => 8,
            streams         => 6,
            },
        serviceName     => 3,
        upc             => {
            digital         => 4,
            streams         => 4,
        },
        country_code    => {
            wmi             => 4,
        },
        product_code    => {
            digital         => 5,
            wmi             => 7,
            streams         => 5,
            },
        artist          => {
            digital         => 7,
            wmi             => 5,
            streams         => 7,
            },
        track           => {
            digital         => 8,
            wmi             => 6,
            streams         => 8,
            },
        units           => {
            digital         => 11,
            wmi             => 11,
            streams         => 10,
            },
        price           => {
            digital         => 13,
            wmi             => 13,
            streams         => 12,
        },
    },

    3 => {
        date_row        => 1,
        date_col        => 0,
        service_name    => 0,
        format_type     => 2,
        product_code    => { 0  => 9, },        
        product_code_type => { 0  => 10, },
        country_code    => { 0  => 4, },
        artist          => { 0  => 5, },
        track           => { 0  => 6, },
        units           => { 0  => 13,},
        price           => { 0  => 15,},
    },
    4 => {
        date_row        => 1,
        date_col        => 0,
        service_name    => 3,
        format_type     => 2,
        label           => 0,
        product_code_type => { 0  => 6, },
        upc             => { 0  => 4, },
        product_code    => { 0  => 5, },
        artist          => { 0  => 7, },
        track           => { 0  => 8, },
        units           => { 0  => 10,},
        price           => { 0  => 12,},
    },
    5 => {
        date_row        => 2,
        date_col        => 0,
        service_name    => 0,
        format_type     => 2,
        label           => 0,
        product_code_type => { 0  => 8, },
        product_code    => { 0  => 7, },
        country_code    => { 0  => 4, },
        artist          => { 0  => 5, },
        track           => { 0  => 6, },
        units           => { 0  => 11,},
        price           => { 0  => 13,},
    },
    # v6 is similar to v2, just a new tab (tab #1 'wireless')
    6 => {
        date_row        => 1,
        date_col        => 0, 
        label           => 0,
        format_type     => 2,
        product_code_type => {
            digital         => 6,
            wireless        => 6,
            wmi             => 8,
            streams         => 6,
            },
        serviceName     => 3,
        upc             => {
            digital         => 4,
            wireless        => 4,
            streams         => 4,
        },
        country_code    => {
            wmi             => 4,
        },
        product_code    => {
            digital         => 5,
            wireless        => 5,
            wmi             => 7,
            streams         => 5,
            },
        artist          => {
            digital         => 7,
            wireless        => 7,
            wmi             => 5,
            streams         => 7,
            },
        track           => {
            digital         => 8,
            wireless        => 8,
            wmi             => 6,
            streams         => 8,
            },
        units           => {
            digital         => 11,
            wireless        => 11,
            wmi             => 11,
            streams         => 10,
            },
        price           => {
            digital         => 13,
            wireless        => 13,
            wmi             => 13,
            streams         => 12,
        },
    },
    # v16 is similar to v6, with an extra column on the WMI tab; the other tabs
    # are identical.
    16 => {
        date_row        => 1,
        date_col        => 0, 
        label           => 0,
        format_type     => 2,
        product_code_type => {
            digital         => 6,
            wireless        => 6,
            wmi             => 9,
            streams         => 6,
            },
        serviceName     => 3,
        upc             => { # FIRST REL UPC
            digital         => 4,
            wireless        => 4,
            streams         => 4,
            wmi             => 7,
        },
        isrc            => { # Product Identifier
            digital         => 5,
            wireless        => 5,
            streams         => 5,
            wmi             => 8,
        },
        country_code    => {
            wmi             => 4,
        },
        product_code    => {  # Product Identifier
            digital         => 5,
            wireless        => 5,
            wmi             => 8,
            streams         => 5,
            },
        artist          => {
            digital         => 7,
            wireless        => 7,
            wmi             => 5,
            streams         => 7,
            },
        track           => {
            digital         => 8,
            wireless        => 8,
            wmi             => 6,
            streams         => 8,
            },
        units           => {
            digital         => 11,
            wireless        => 11,
            wmi             => 12,  # was column 11 in v6
            streams         => 10,
            },
        price           => {
            digital         => 13,
            wireless        => 13,
            wmi             => 13,
            streams         => 12,
        },
    },
);

#
# 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 = $field_map{$version};
    my $sheet_count = 0;
    my ($linenum,$serviceID);

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

    my $sheet_names = $args{sheet_names};
    
    # Some tabs don't have the date, so we'll declare this out here so that it can persist.
    #
    my ($dateBegin, $dateEnd);

    foreach my $sheet(@{ $args{sheets} })
    {
        my $sheet_name = lc($sheet_names->[$sheet_count]);
        
        # Version 1 is the only one to have multiple tabs that are not named according 
        # to the convention used in versions 2 and 6.  The columns are all the same, so we
        # can just use the same offsets for all of the tabs.
        # !!! Actually, versions 3-5 do not have consistent sheet names.  Since they only have one sheet,
        # !!! let's do the same thing for those.
        if ($version == 1 || $version == 3 || $version == 4 || $version == 5)
        {
            $sheet_name = 0;   
        }
        
        #
        # Extract the date from row 2 of each sheet (see FB13053).
        # If the date isn't found, examine the individual lines (below).
        #
        my ($tabDateBegin, $tabDateEnd) = $self->_getDates($sheet->[$offsets->{date_row}][$offsets->{date_col}]);
        
        if ($tabDateBegin)
        {
            $dateBegin = $tabDateBegin;
            $dateEnd   = $tabDateEnd;
        }

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


            if ( defined $offsets->{serviceName} && ('DSP Name' eq $line->[$offsets->{serviceName}] ) )
            {
                $linenum++;
                next;
            }

            if ( !$dateBegin )
            {
                ($dateBegin, $dateEnd) = $self->_getDates( $line->[$offsets->{date_col}] );
            }

            my $format_type     = RPS::File::Sale::FORMAT_DOWNLOAD;
            my $product_type    = RPS::File::Sale::TYPE_TRACK;
            my $mediaType       = RPS::DB::Item::MediaType::kMediaTypeAudio;

            my $artist          = $line->[$offsets->{artist}->{$sheet_name}];
            
            my $product_code    = $line->[$offsets->{product_code}->{$sheet_name}] if(defined $offsets->{product_code}->{$sheet_name});
            my $product_code_type = $line->[$offsets->{product_code_type}->{$sheet_name}] if(defined $offsets->{product_code_type}->{$sheet_name});
            
            # Find the UPC and ISRC.  Depending on the importer version, this can be:
            #
            #    a) From a combination field (product_code) that requires another
            #       field (product_code_type) to determine if the combo field contains
            #       an ISRC or UPC, or
            #    b) From separate UPC and ISRC fields.
            #
            if( defined $offsets->{upc}->{$sheet_name} )
            {
                $upc = $line->[$offsets->{upc}->{$sheet_name}];
            }
            else
            {
                $upc = $product_code if( lc($product_code_type) eq 'upc' );
            }

            if( defined $offsets->{isrc}->{$sheet_name} )
            {
                $isrc = $line->[$offsets->{isrc}->{$sheet_name}];
            }
            else
            {
                $isrc = $product_code if( lc($product_code_type) eq 'isrc' );
            }

            my $track           = $line->[$offsets->{track}->{$sheet_name}];
            my $units           = $line->[$offsets->{units}->{$sheet_name}];
            my $price           = $line->[$offsets->{price}->{$sheet_name}];
            my $label           = $line->[$offsets->{label}] if(defined $offsets->{label});
            if($units!=0) {
                $price              /= ($units*-1) if($units<0);
                $price              /= $units if($units>0);
                $price              *= -1 if($price<0);
            }
            $format_type        = RPS::File::Sale::FORMAT_STREAM if($sheet_name =~ /stream/i || $line->[$offsets->{format_type}] =~ /stream/i);

            if($line->[$offsets->{format_type}] =~ /evd/i)
            {
                if( $sheet_name =~ /stream/i)
                {
                    $format_type = RPS::File::Sale::FORMAT_STREAM; # was FORMAT_VIDEOSTREAM (FB1324)
                }
                else
                {
                    $format_type = RPS::File::Sale::FORMAT_DOWNLOAD; # was FORMAT_VIDEO (FB1324)
                }
                $mediaType = RPS::DB::Item::MediaType::kMediaTypeVideo;
            }


            if($line->[$offsets->{format_type}] =~ /evdstreaming/i)
            {
                $format_type = RPS::File::Sale::FORMAT_VIDEOSTREAM; # was FORMAT_VIDEOSTREAM (FB1324)
                $mediaType   = RPS::DB::Item::MediaType::kMediaTypeVideo;
            }

            $format_type        = RPS::File::Sale::FORMAT_RINGTONE if($line->[$offsets->{format_type}] =~ /tone/i);
            $format_type        = RPS::File::Sale::FORMAT_RINGTONE if($line->[$offsets->{format_type}] =~ /wallpaper/i);
            $format_type        = RPS::File::Sale::FORMAT_RINGTONE if($line->[$offsets->{format_type}] =~ /mastertone/i);
            $format_type        = RPS::File::Sale::FORMAT_RINGTONE if($line->[$offsets->{format_type}] =~ /ringback/i);
            $product_type       = RPS::File::Sale::TYPE_ALBUM if($line->[$offsets->{format_type}] =~ /album/i);
            

            $isrc = "" if( $product_type eq RPS::File::Sale::TYPE_ALBUM );

            my $serviceName;
            if(defined $offsets->{serviceName})
            {
                $serviceName = $line->[$offsets->{serviceName}];
                $serviceID   = $self->getServiceID(lc ($serviceName)) || $RPS::Import::ServiceMap::SERVICE_ALIASES{ lc $serviceName };
            }

            if ($units && $product_type && $price && $artist && $artist ne "Artist" && $track && $track ne "Title")
            {
#                print STDERR "units: $units\n";
                unless ($dateBegin && $dateEnd) {
                    $self->errstr("Couldn't get dates from: ". $sheet->[$offsets->{date_row}][$offsets->{date_col}]);
                    print STDERR "Couldn't get dates from: ". $sheet->[$offsets->{date_row}][$offsets->{date_col}]."\n";
                    return undef;
                }
                if(defined $offsets->{serviceName}) {
                    unless ($serviceID) {

                        my $sn = $sheet_count + 1;
                        if ( $serviceName =~ /[[:^ascii:]]/ ) {
                            $serviceID = Client::Service::DSP_ADA;
                            #$self->errstr("Sheet $sn, line $linenum: Service name contains incompatible characters");
                        } else {
                            $self->errstr("Sheet $sn, line $linenum: Couldn't find service ID for: ".$serviceName);
                            return undef;
                        }
                        #return undef;
                    }
                }
                my $sale = RPS::Import::SaleRec->new();

                $sale->productType($product_type);
                $sale->formatType($format_type);
                $sale->dateBegin($dateBegin);
                $sale->dateEnd($dateEnd);
                $sale->labelName($label) if(defined $offsets->{label} && $label ne "");
                if($product_type eq RPS::File::Sale::TYPE_ALBUM) {
                    $sale->albumName($track);
                }
                else {
                    $sale->trackName($track);
                    $sale->isrc(Common::Util::normalize_isrc($isrc)) if($isrc ne "");
                }
                $sale->upc(Common::Util::normalize_upc($upc)) if($upc ne "");
                $sale->serviceID($serviceID) if(defined $offsets->{serviceName});
                $sale->artistName($artist);
                $sale->units($units);
                $sale->price($price);

                my $countryCode;
                if(defined $offsets->{country_code}->{$sheet_name} && $line->[$offsets->{country_code}->{$sheet_name}] ne "")
                {
                    $countryCode = $line->[$offsets->{country_code}->{$sheet_name}];
                } 
                else
                {
                    $countryCode = 'US';   
                }              
                $sale->countryCode($countryCode);                            
                
                $sale->mediaType($mediaType);
                $sale->lineNum($linenum);

                $self->_insertSale(sale => $sale);
            }
            #else { print STDERR "skip s:".$sheet_names->[$sheet_count]." l:".$linenum." a:".$artist." t:".$track." 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;
    }
    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.
###