package RPS::Import::Caroline::v37;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Country;

use Date::Calc qw(Days_in_Month Decode_Month);

use lib '/app/tools/rps/lib';
use RPS::Import::ServiceMap;
use Date::Calc qw(Days_in_Month Add_Delta_Days Decode_Month);

use parent qw/RPS::Import::MapFaceBase RPS::Import::ImporterMixed/;

use constant RE_YYYY_MM => qr/(\d{4})(\d{2})/;

sub physical { 2 }

sub _fieldMapExtended {
    {
        digital => {
            option1 => {    # Digital Sales - Send
                labelName   => 'A',
                serviceID   => 'I',
                formatType  => 'H',
                artistName  => 'B',
                upc         => 'C',
                albumName   => 'D',
                isrc        => 'E',
                trackName   => 'F',
                units       => 'M',
                price       => 'L',
                mediaType   => 'H',
            },
            option2 => {    # AIF
                labelName    => 'B',
                serviceID    => 'H',
                countryCode  => 'I',
                dateBegin    => 'F',
                dateEnd      => 'F',
                productType  => 'O',
                formatType   => 'Q',
                artistName   => 'D',
                upc          => 'K',
                isrc         => 'M',
                trackName    => 'L',
                units        => 'S',
                currencyCode => 'X',
                price        => 'X',
                mediaType    => 'Q',
            },
            option3 => {    # AIF
                labelName    => 'B',
                serviceID    => 'H',
                countryCode  => 'I',
                dateBegin    => 'F',
                dateEnd      => 'F',
                productType  => 'S',
                formatType   => 'U',
                artistName   => 'D',
                upc          => 'O',
                albumName    => 'P',
                isrc         => 'M',
                trackName    => 'N',
                units        => 'W',
                currencyCode => 'AB',
                price        => 'AB',
                mediaType    => 'U',
            },
            option4 => {    # Domestic Licensing
                labelName   => 'B',
                serviceID   => 'K',
                productType => 'H',
                formatType  => 'M',
                artistName  => 'C',
                upc         => 'F',
                albumName   => 'G',
                isrc        => 'H',
                trackName   => 'I',
                units       => 'P',
                price       => 'P',
            },
            option5 => {    # AIF Alt3
                labelName    => 'B',
                serviceID    => 'H',
                countryCode  => 'I',
                dateBegin    => 'F',
                dateEnd      => 'F',
                productType  => 'Q',
                formatType   => 'S',
                artistName   => 'D',
                upc          => 'K',
                albumName    => 'L',
                isrc         => 'M',
                trackName    => 'N',
                units        => 'U',
                currencyCode => 'Z',
                price        => 'Z',
                mediaType    => 'S',
            },
        },
        physical => {
            option1 => {    # Physical Sales - Send
                labelName      => 'A',
                productType    => 'F',
                artistName     => 'B',
                upc            => 'C',
                albumName      => 'D',
                sales          => 'M',
                salesRevenue   => 'I',
                returns        => 'N',
                returnsRevenue => 'K',
                totalRevenue   => 'L',
            },
            option2 => {    # Physical Sales - Send (an alternative header)
                labelName      => 'A',
                productType    => 'F',
                artistName     => 'B',
                upc            => 'C',
                albumName      => 'D',
                sales          => 'L',
                salesRevenue   => 'I',
                returns        => 'M',
                returnsRevenue => 'J',
                totalRevenue   => 'K',
            },
            option3 => {    # Physical Sales (RSD-11982)
                labelName    => 'B',
                productType  => 'N',
                artistName   => 'C',
                upc          => 'F',
                albumName    => 'G',
                totalRevenue => 'U',
            },
        },
    };
}

sub _unverifiedFieldMapExtended {
    {
        digital => {
            option4 => { # Domestic Licensing
                _price_exp => 'Q',
            }
        },
        physical => {
            option1 => {    # Physical Sales - Send
                _netUnits => 'O',
            },
            option2 => {    # Physical Sales - Send
                _netUnits => 'N',
            },
            option3 => {    # Physical Sales (RSD-11982)
                _netUnits => 'X',
            },
        }
    };
}

sub _headerIdentifierExtended {
    {
       digital => {
            option1 => {    # Digital Sales - Send
                isrc => 'ISRC',
            },
            option2 => {    # AIF
                isrc => 'Compilation ISRC',
            },
            option3 => {    # AIFv2
                upc => 'SUPC',
            },
            option4 => {    # Domestic Licensing
                units => 'Domestic Licensing Income',
            },
            option5 => {    # AIFv3
                upc => 'UPC',
            },
        },
        physical => {
            option1 => {    # Physical Sales - Send
                _netUnits => 'Net Units',
            },
            option2 => {    # Physical Sales - Send (an alternative header)
                _netUnits => 'Net Units',
            },
            option3 => {    # Physical Sales (RSD-11982)
                _netUnits => 'Overall Result',
            }
        },
    };
}

my %months = (
    jan => 1, feb => 2, mar => 3, apr => 4, may => 5, jun => 6,
    jul => 7, aug => 8, sep => 9, oct => 10, nov => 11, dec => 12,
);

my $hDigitalSalesTypes = {
    'ad supported streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ad supported video streaming (no cprt)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'ephemeral plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'fixed plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'jukebox' => {
        formatType => RPS::File::Sale::FORMAT_JUKEBOX,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'icloud channel' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'kiosk' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'permanent downloads' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub client plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub device plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub programmed plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub video dvc plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'programmed streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'programmed video streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'programmed streaming (soundexchange administered)' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'sub user generated streams (umg audio)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription limited catalog' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription programmed streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'tethered/conditional downloads' => {
        formatType => RPS::File::Sale::FORMAT_TETHERED,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'user generated streams (umg audio / user video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'video fixed play' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video streams (sponsorship – no cprt)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'audio stream true up' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'audio subscription true up' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    # hyphen
    'video stream video portable - subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    # dash
    'video stream video portable – subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'digital online vid track stream - subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video stream webcasting - subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'broadcast vod' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'internet broadcast radio' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription ota permanent downloads (audio)' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'sub user generated streams (umg video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'subscription limited catalog' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'video downloads' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
};

my $hAIFSalesTypes = {
    'ad supported streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ad supported video streaming (no cprt)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'ephemeral plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'fixed plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'jukebox' => {
        formatType => RPS::File::Sale::FORMAT_JUKEBOX,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'kiosk' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'permanent downloads' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub client plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub device plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub programmed plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable sub video dvc plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'programmed streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'programmed streaming (soundexchange administered)' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'sub user generated streams (umg audio)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription limited catalog' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription programmed streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'tethered/conditional downloads' => {
        formatType => RPS::File::Sale::FORMAT_TETHERED,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'user generated streams (umg audio / user video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'video fixed play' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'icloud channel' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'audio stream true up' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'audio subscription true up' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'video stream video portable – subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'digital online vid track stream - subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video stream webcasting - subscr' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'broadcast vod' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'internet broadcast radio' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscription ota permanent downloads (audio)' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'sub user generated streams (umg video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'programmed video streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'video streams (sponsorship – no cprt)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'subscription streaming by third party' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'streaming (subscription)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'permanent download (subscription)' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'permanent download (subscription) (subscription)' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'permanent download' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'portable subscriptions' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'subscriptions' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'user generated streams (umg audio/subscr)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'user generated streams (umg audio/user-video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'cloud service' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'allocation-streams (audio/video)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'broadcasting & public perf-label share only' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'allocation-download' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'broadcasting and public perf-incld artist share' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'videoclip income-incld artist share' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'broadcsting & public perf label share only' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'broadcsting & public perf artist share only' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'artist engagement/superchat' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
};

my $hDomesticLicensingSalesTypes = {
    'business establishment services' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
    },
    'royalties' => {
        formatType => undef,  # error out
    },
    'sdars ephemeral' => {
        formatType => RPS::File::Sale::FORMAT_EPHEMERAL,
    },
    'satellite radio' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
    },
    'television promo spot' => {
        formatType => 'error',
    },
    'cabsat ephemeral' => {
        formatType => RPS::File::Sale::FORMAT_EPHEMERAL,
    },
    'cable radio & subscription services' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
    },
    'preexisting services' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
    }
};

# type|option => sheet_alias => mapped_sheet_name
my %sheetAliases = (
    'digital|option1' => {
        'digital sales - send' => 'digital sales - send',
    },
    'digital|option2' => {
        'aif' => 'aif',
    },
    'digital|option4' => {
        'domestic licensing' => 'domestic licensing',
    },
    'physical|option1' => {
        'physical sales - send' => 'physical sales - send',
    },
    'physical|option3' => {
        'physical sales' => 'physical sales',
    },
);

my %mapping = (
    'digital sales - send' => {
        countryCode => 'US',
        currencyCode => 'USD',
        types => $hDigitalSalesTypes,
    },
    'aif' => {
        types => $hAIFSalesTypes,
    },
    'domestic licensing' => {
        countryCode  => 'US',
        currencyCode => 'USD',
        types => $hDomesticLicensingSalesTypes,
    },
    'physical sales - send' => {
        countryCode  => 'US',
        currencyCode => 'USD',
        productType  => {
            'cd album'                => RPS::File::Sale::TYPE_CD,
            'cd album plus dvd video' => RPS::File::Sale::TYPE_CD,
            'vinyl album 12" 33'      => RPS::File::Sale::TYPE_LP,
            'vinyl album 12" 33 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl album 10" 33 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl album 12" 45 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl single 12" 33rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl single 12" 33 rpm' => RPS::File::Sale::TYPE_LP,
            'cd plus dvd bonus'       => RPS::File::Sale::TYPE_DVD_CD_SET,
            'dvd video album ntsc'    => RPS::File::Sale::TYPE_DVD,
            'cd ep'                   => RPS::File::Sale::TYPE_EP,
        },
    },
    'physical sales' => {
        countryCode  => 'US',
        currencyCode => 'USD',
        productType  => {
            'cd album'                => RPS::File::Sale::TYPE_CD,
            'combi-pack'              => RPS::File::Sale::TYPE_CD,
            'vinyl album 12" 33 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl album 12" 45 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl album 10" 33 rpm'  => RPS::File::Sale::TYPE_LP,
            'vinyl single 7" 33 rpm'  => RPS::File::Sale::TYPE_LP5,
            'vinyl single 12" 33 rpm' => RPS::File::Sale::TYPE_LP5,
            'mc album'                => RPS::File::Sale::TYPE_CASS,
            'cd album plus dvd video' => RPS::File::Sale::TYPE_DVD_CD_SET,
        }
    }
);


sub _processHeader {
    my ($self, %args) = @_;

    $self->SUPER::_processHeader();

    if ( $self->sheetname =~ /^AIF$/i ) {
        if ( $self->dataTypeOption eq 'option3' ) { # AIFv2
            $self->{_countryCode} = "US";
            my $currency = $self->_getByFieldName('currencyCode') || '';
            if ($currency =~ /\((\w{3})\)$/i) {
                $self->{_currencyCode} = $1;
            }
        }
    }

    if ( $self->{_dateBegin} && $self->{_dateEnd} ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    }

    if ( $self->sheetname =~ /^(?:Digital|Physical) Sales - Send|Physical Sales$/i ) {
        ($self->{_dateBegin}, $self->{_dateEnd}) = $self->_getPeriod();
        print STDERR "HHH => '$self->{_dateBegin}', '$self->{_dateEnd}'\n";
    }

    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        ($self->{_dateBegin}, $self->{_dateEnd}) = $self->_getPeriod();
    }

    return 1;
}

sub _getPeriod {
    my $self = shift;

    my ($start, $end);

    my $dateBegin = lc( $self->_getByFieldName('dateBegin') || '' );
    my $dateEnd   = lc( $self->_getByFieldName('dateEnd')   || '' );


    if ( $self->sheetname =~ /^(?:Digital|Physical) Sales - Send|Domestic Licensing|Physical Sales$/i ) {
        if ( $self->filename =~ RE_YYYY_MM ) {
            $start = sprintf "%04d-%02d-%02d", $1, $2, 1;
            $end = sprintf "%04d-%02d-%02d", $1, $2, Days_in_Month( $1, $2 );
        }
    }

    return $start, $end if $start && $end;
}

sub saleDates {
    my $self = shift;

    my $dateBegin = lc( $self->_getByFieldName('dateBegin') || '' );

    if ( $self->sheetname =~ /^(?:Digital|Physical) Sales - Send|Domestic Licensing|Physical Sales$/i ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    } elsif ( $self->sheetname =~ /^AIF$/i ) {
        # YYYYMM
        if ( $dateBegin =~ RE_YYYY_MM ) {
            return
                sprintf("%04d-%02d-%02d", $1, $2, 1),
                sprintf "%04d-%02d-%02d", $1, $2, Days_in_Month( $1, $2 );
        }
    }

    $self->fail("Unable to determine sale period for sheetname: " . $self->sheetname);
}

sub serviceID {
    my $self = shift;

    if ( $self->sheetname =~ /^Physical Sales - Send|Physical Sales$/i ) {
        return Client::Service::DSP_CAROLINE;
    }

    if ( $self->sheetname =~ /^(?:Digital Sales - Send)$/i ) {
        my $service = lc( $self->_getByFieldName('serviceID') || '' );
        return Client::Service::DSP_CAROLINE if $service =~ /^All Other Licensees$/i;

        my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service};
        return $serviceID ? $serviceID : $self->fail("Unknown service: $service");
    }

    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        my $service = lc( $self->_getByFieldName('serviceID') || '' );
        my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service};
        return $serviceID ? $serviceID : $self->fail("Unknown service: $service");
    }

    if ( $self->sheetname =~ /^AIF$/i ) {
        my $service = lc( $self->_getByFieldName('serviceID') || '' );
        my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service};
        return $serviceID if $serviceID;

        return $self->handleError( "Unknown service: '$service'", RPS::DB::Item::SaleImportError::kErrorService );
    }

    $self->fail("Unable to determine service.");
}

sub countryCode {
    my $self = shift;

    my $sheetName = lc $self->sheetname;

    if ( exists $mapping{$sheetName} && exists $mapping{$sheetName}{countryCode} ) {
        return $mapping{$sheetName}{countryCode};
    } else {
        return $self->{_countryCode} if $self->{_countryCode};

        my $name = $self->_getByFieldName('countryCode') || '';
        return 'PS' if $name =~ /^PALESTINIAN TERRITORY, OCCUPI/i;
        return 'MV' if $name =~ /^MALIDIVES/i;
        return 'CI' if $name =~ /^IVORY COAST/i;
        return 'LA' if $name =~ /^LAOS/i;
        return 'VC' if $name =~ /^SAINT VINCENT AND THE GRENADIN/i;
        return 'KN' if $name =~ /^ST. KITTS-NEVIS-ANGUILLA/;
        return 'VI' if $name =~ /^VIRGIN ISLANDS UNITED STATES/i;

        my $oCountry = Common::Country->Get($name);
        return $oCountry->alpha2() if $oCountry;
    }

    $self->fail("Unable to determine country code.");
}


sub currencyCode {
    my $self = shift;

    my $sheetName = lc $self->sheetname;

    if ( exists $mapping{$sheetName}{currencyCode} && exists $mapping{$sheetName}{currencyCode} ) {
        return $mapping{$sheetName}{currencyCode};
    } elsif ( $sheetName =~ /^AIF$/i  ) {
        return $self->{_currencyCode};
    }

    $self->fail("Unable to determine country code.");
}

sub productType {
    my $self = shift;

    my $sheetName = lc $self->sheetname;
    my $type      = lc( $self->_getByFieldName('productType') || '' );

    if ( exists $mapping{$sheetName}{productType}{$type} && $mapping{$sheetName}{productType}{$type} ) {
        return $mapping{$sheetName}{productType}{$type};
    } elsif ( $sheetName =~ /^Digital Sales - Send$/i ) {
        my $isrc = $self->_getByFieldName('isrc') || '';
        my $mediaType = $self->_getByFieldName('mediaType') || '';

        return RPS::File::Sale::TYPE_TRACK if $isrc && $isrc ne '#';                # E
        return RPS::File::Sale::TYPE_TRACK if $mediaType && $mediaType =~ /track/i; # H
        return RPS::File::Sale::TYPE_ALBUM if $mediaType && $mediaType =~ /album/i; # H

        $self->fail("Unable to determine product type from isrc: '$isrc' and media_type: '$mediaType'");
    } elsif ( $sheetName =~ /^AIF$/i ) {
        return RPS::File::Sale::TYPE_TRACK if $type =~ /^E(?:VIDEO|TRACK|SPMT)$/i;
        return RPS::File::Sale::TYPE_ALBUM if $type =~ /^(?:ELP|ESP)$/i;
    } elsif ( $sheetName =~ /^Domestic Licensing$/i ) {
        return $self->_getByFieldName('isrc') eq '#'
            ? RPS::File::Sale::TYPE_ALBUM
            : RPS::File::Sale::TYPE_TRACK;
    }

    $self->fail("Unable to determine product type from: '$type'");
}

sub formatType {
    my $self = shift;

    my $sheetName = lc $self->sheetname;
    my $type      = lc( $self->_getByFieldName('formatType') || '' );

    if ( exists $mapping{$sheetName}{types}{$type}{formatType} && $mapping{$sheetName}{types}{$type}{formatType} ) {
        if ( $mapping{$sheetName}{types}{$type}{formatType} eq 'error' ) {
            $self->fail("Unable to determine format_type from: '$type'\n");
        }
        return $mapping{$sheetName}{types}{$type}{formatType};
    }

    return $self->handleError( "Unknown format_type: '$type'.", RPS::DB::Item::SaleImportError::kErrorFormatType );
}

sub mediaType {
    my $self = shift;

    return if $self->dataType =~ /physical/i;
    return if !RPS::Import::Importer->isDigitalProduct( $self->productType );

    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        return RPS::DB::Item::MediaType::kMediaTypeAudio;
    }

    my $sheetName = lc $self->sheetname;
    my $type      = lc( $self->_getByFieldName('mediaType') || '' );

    if ( exists $mapping{$sheetName}{types}{$type}{mediaType} && $mapping{$sheetName}{types}{$type}{mediaType} ) {
        return $mapping{$sheetName}{types}{$type}{mediaType};
    }

    return $self->handleError( "Unknown media_type: '$type'.", RPS::DB::Item::SaleImportError::kErrorMediaType );
}

sub upc {
    my $self = shift;

    my $upc = $self->_getByFieldName('upc') || '';
    $upc =~ s/^(?:00)?(.*)$/$1/;

    if ( $self->sheetname =~ /^AIF$/i && $self->productType eq RPS::File::Sale::TYPE_TRACK ) {
        return;
    }

    return $upc;
}

sub isrc {
    my $self = shift;

    my $isrc = $self->_getByFieldName('isrc') || '';
    $isrc =~ s/^(?:5)?(.*)$/$1/;

    if ( $self->sheetname =~ /^AIF$/i && $self->productType eq RPS::File::Sale::TYPE_ALBUM ) {
        return;
    }

    return $isrc;
}

sub channel    {
    return RPS::File::Sale::CHANNEL_RETAIL;
}

sub priceLevel {
    if ( shift->sheetname =~ /^Physical Sales$/i ) {
        return RPS::File::Sale::PLEVEL_UNKNOWN;
    }

    return RPS::File::Sale::PLEVEL_FULL;
}

# digital
sub price {
    my $self = shift;

    my $price = $self->_getByFieldName('price') || 0;
    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        my $price_exp = $self->_getByFieldName('_price_exp') || 0;

        $self->fail("Income and Expence both prices are defined") if ($price_exp && $price);

        return $price ? abs($price) : $price_exp;
    }

    return abs($price)
}
sub unitPrice { abs shift->SUPER::unitPrice }
sub units {
    my $self = shift;

    my $price = $self->_getByFieldName('price') || 0;
    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        my $price_exp = $self->_getByFieldName('_price_exp') || 0;

        $self->fail("Income and Expence both prices are defined") if ($price_exp && $price);

        return $price ? 1 : ($price_exp ? -1 : 0);
    }

    my $units = $self->_getByFieldName('units') || 0;
    if ( $price > 0 ) {
        $units = $units ? abs($units) : 1;
    } elsif ( $price < 0 ) {
       $units = $units ? -1 * abs($units) : -1;
    }

    return $units;
}

# physical
sub netUnits {
    my $self = shift;

    if ( $self->sheetname =~ /^Physical Sales$/i ) {
        return $self->_getByFieldName('_netUnits') || 0;
    }

    # 'Physical Sales - Send' tab
    my $sales   = abs($self->_getByFieldName('sales')   || 0);
    my $returns = abs($self->_getByFieldName('returns') || 0);
    my $units   = $sales - $returns;

    if ( $self->totalRevenue > 0 ) {
        $units = $units ? abs($units) : 1;
    } elsif ( $self->totalRevenue < 0 ) {
        $units = $units ? -1 * abs($units) : -1;
    }

    return $units;
}
sub totalRevenue   { $_[0]->_getByFieldName('totalRevenue') || 0 }
sub salesRevenue   { $_[0]->totalRevenue > 0 ? $_[0]->totalRevenue     : 0 }
sub returnsRevenue { $_[0]->totalRevenue < 0 ? abs $_[0]->totalRevenue : 0 }

sub sales {
    my $self = shift;

    return $self->netUnits > 0 ? $self->netUnits : 0;
}

sub returns {
    my $self = shift;

    return $self->netUnits < 0 ? abs $self->netUnits : 0;
}

sub _isValidSaleRecord {
    my $self = shift;

    my $labelName =  $self->_getByFieldName('labelName') || '';
    my $upc = $self->_getByFieldName('upc') || '';

    return 0 if $labelName =~ /^Label$/i;

    if ( $self->dataType =~ /physical/i ) {
        return $upc
            && ( $self->_getByFieldName('albumName') || $self->_getByFieldName('artistName') )
            && ( $self->_getByFieldName('_netUnits') || $self->_getByFieldName('totalRevenue') );
    }

    my $price = $self->_getByFieldName('price') || 0;
    if ( $self->sheetname =~ /^Domestic Licensing$/i ) {
        return if $price =~ /^Domestic Licensing Income$/i;
        return if !$self->price && !$self->units;
    }

    if ( $self->sheetname =~ /aif/i ) {
        return ( $self->_getByFieldName('isrc') || $self->_getByFieldName('upc') )
            || ( $self->_getByFieldName('albumName') || $self->_getByFieldName('artistName') );
    }

    return $self->_getByFieldName('isrc')
        && ( $self->_getByFieldName('albumName') || $self->_getByFieldName('artistName') );
}

my @sheetsToSkip = (
    '_com.sap.ip.bi.xl.hiddensheet',
    '99003042',
    'API',
    'Backup for 99100013 &amp; 99127121',
    'Co-Op',
    'CoOp',
    'Data Pivot',
    'Data',
    'DCC',
    'Digital Expenses',
    'Digital Revenue',
    'Doc # 99127121_Amount $ 33148.6',
    'Doc # 99127121_Amount $ 331486',
    'EGR',
    'Famehouse',
    'FRT',
    'GCH - AIF AIN',
    'JDL AIF Reclass to JD2',
    'JDL RCL TO JDL 01 19 (sxc)',
    'LipLog',
    'Manufacturing',
    'Marketing',
    'MFG',
    'MLP',
    'OH UME',
    'OSP',
    'Other Handling Fee',
    'Other Handling Fees',
    'RDW - AIJ',
    'RDW - PMJ',
    'Reserve',
    'Reserves',
    'Sheet3',
    'Sheet4',
    'SOP',
    'SR - Commercial MFG',
    'SR - Egratis',
    'SR - MOLP',
    'SR - SOP US001',
    'SR API',
    'SR - API',
    'SR - Refurbishment',
    'Statement',
    'Statement - Send Version',
    'U-M007385740-AD-WB-NR',
    'UME',
    'UML',
    'UMS',
    'VAS',
    'Pandora Expense',
    'COOP',
    'SR - Scrap',
    'Artist Digital Royalties',
    'SX Direct Passthrough',
    'MKT CO-Op Support',
);

sub _processSheet {
    my $self = shift;

    # skip sheets from the sheetsToSkip list
    my @skipSheets = grep { $self->{sheetname} =~ /^\Q$_\E$/i } @sheetsToSkip;
    if ( @skipSheets ) {
        print STDERR "Skip sheet '$self->{sheetname}'.\n";
        return;
    }

    # throw an error for unsupported sheet names
    my @supportedSheets = grep { $self->{sheetname} =~ /^\Q$_\E$/i } map { keys %$_ } values %sheetAliases;
    unless ( @supportedSheets ) {
        $self->fail( "Unsupported sheet '$self->{sheetname}'" );
    }

    # throw an error for supported sheets but changed format of the header
    my $headerFound = $self->SUPER::_processSheet(@_);
    unless ( $headerFound ) {
        $self->fail( "Unable to determine title for sheet '$self->{sheetname}'" );
    }

    return $headerFound;
}

# mapFace
sub _mapFaceServiceID   { 'serviceID' }
sub _mapFaceCountryCode {}
sub _mapFaceProductType {}
sub _mapFaceFormatType  { 'formatType' }
sub _mapFaceMediaType   { 'mediaType' }


1;