package RPS::Import::Universal::v23;

use strict;
use warnings;

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

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

use constant RE_MMM_YYYY => qr/\(?([a-z]{3})[a-z]{0,6}\s?(?:20)?(\d{2})\)?/i;
use constant RE_YYYY_MM   => qr/(\d{4})(\d{2})/;


sub physical { 2 }

sub _fieldMapExtended {
    {
        digital => {
            option1 => {    # Digital Sales, Digaital Sales from v63 (Column1 => C)
                labelName   => 'B',
                serviceID   => 'D',
                dateBegin   => 'O',
                dateEnd     => 'O',
                productType => 'F',
                formatType  => 'C',
                upc         => 'I',
                albumName   => 'H',
                isrc        => 'N',
                trackName   => 'K',
                units       => 'P',
                price       => 'O',
                mediaType   => 'C',
            },
            # !!! ATTENTION !!! Also 'Digital Sales' but without col. M ('Column1') - related to the v45
            # but if the sales file contains many tabs related to v23 - it will work as the current version.
            # Columns isrc, price, units shifted left by one cell
            option3 => {
                labelName   => 'B',
                serviceID   => 'D',
                dateBegin   => 'O',
                dateEnd     => 'O',
                productType => 'F',
                formatType  => 'C',
                upc         => 'I',
                albumName   => 'H',
                isrc        => 'M',
                trackName   => 'K',
                units       => 'O',
                price       => 'N',
                mediaType   => 'C',
            },
            option2 => {    # Canadian Digital or an alias Digital
                labelName   => 'D',
                dateBegin   => 'B',
                dateEnd     => 'B',
                productType => 'H',
                formatType  => 'J',
                artistName  => 'E',
                upc         => 'F',
                albumName   => 'G',
                isrc        => 'H',
                trackName   => 'I',
                units       => 'L',
                price       => 'K',
                mediaType   => 'J',
            },
            option4 => {    # Digital Sales (alternative header) from v63
                labelName   => 'A',
                serviceID   => 'E',
                dateBegin   => 'O',
                dateEnd     => 'O',
                productType => 'G',
                formatType  => 'D',
                upc         => 'J',
                albumName   => 'I',
                isrc        => 'N',
                trackName   => 'L',
                units       => 'P',
                price       => 'O',
                mediaType   => 'D',
            },
            option5 => {    # Sound Exchange
                labelName   => 'A',
                artistName  => 'B',
                upc         => 'C',
                albumName   => 'D',
                isrc        => 'E',
                trackName   => 'F',
            },
            option6 => {    # AIF
                labelName    => 'B',
                serviceID    => 'G',
                countryCode  => 'H',
                dateBegin    => 'F',
                dateEnd      => 'F',
                productType  => 'L',
                formatType   => 'N',
                artistName   => 'D',
                isrc         => 'J',
                trackName    => 'K',
                units        => 'P',
                currencyCode => 'Y',
                price        => 'Y',
                mediaType    => 'N',
            },
        },
        mixed => {
            option1 => {    # AIF mixed alternative OR AIF alternative (3)
                labelName    => 'B',
                serviceID    => 'H',
                countryCode  => 'I',
                dateBegin    => 'F',
                dateEnd      => 'F',
                productType  => 'O',
                formatType   => 'Q',
                artistName   => 'D',
                isrc         => 'K',
                trackName    => 'L',
                units        => 'S',
                channel      => 'Q',
                currencyCode => 'X',
                totalRevenue => 'X',
                price        => 'X',
                mediaType    => 'Q',
            },
        },
        physical => {
            option1 => {    # Physical Sales or an alias Physical
                labelName    => 'B',
                dateBegin    => 'Q',
                dateEnd      => 'Q',
                productType  => 'H',
                artistName   => 'C',
                upc          => 'E',
                albumName    => 'D',
                priceLevel   => 'J',
                totalRevenue => 'Q',
            },
            option2 => {    # Canadian physical or an alias Physical
                labelName    => 'D',
                dateBegin    => 'B',
                dateEnd      => 'B',
                productType  => 'J',
                artistName   => 'E',
                upc          => 'F',
                albumName    => 'G',
                sales        => 'R',
                returns      => 'S',
                totalRevenue => 'Q',
            },
        },
    };
}

sub _unverifiedFieldMapExtended {
    {
        digital => {
            option1 => {   # Digital Sales
                _albumArtist => 'G',
                _trackArtist => 'J',
                _confusedCol => 'M',
            },
            option3 => {   # Digital Sales (SHIFTED)
                _albumArtist => 'G',
                _trackArtist => 'J',
            },
            option4 => {   # Digital Sales (alternative header) from v63
                _colMonth    => 'B',
                _albumArtist => 'H',
                _trackArtist => 'K',
                _salesType   => 'F',
            },
            option5 => {    # Sound Exchange
                _digital     => 'G',
                _license     => 'H',
            },
            option6 => {    # AIF
                _label_group => 'X',
            },
        },
        mixed => {
            option1 => {    # AIF mixed alternative OR AIF (3)
                _sublic_company_id => 'G',
                _netUnits          => 'S',
            },
        },
        physical => {
            option1 => {    # Physical Sales or an alias Physical
                _netUnits => 'M',
            },
            option2 => {    # Canadian physical or an alias Physical
                _netUnits => 'T',
            },
        },
    };
}

sub _headerIdentifierExtended {
    {
        digital => {
            option1 => {    # Digital Sales, Digital Sales v63
                _confusedCol => qr/^(?:Column1|C|Configuration)$/i,
            },
            option3 => {    # Digital Sales (SHIFTED)
                productType => 'Sold As',
            },
            option2 => {    # Canadian Digital or Digital "#1"
                productType => 'ISRC',
            },
            option4 => {    # Digital Sales (alternative header) from v63
                _colMonth => 'Month',
            },
            option5 => {    # Sound Exchange
                upc => 'UPC',
            },
            option6 => {    # AIF
                _label_group => 'Label group',
            },
        },
        mixed => {
            option1 => {    # AIF alternate
                _sublic_company_id => 'Sublic Company ID',
            },
        },
        physical => {
            option1 => {    # Physical Sales
                productType => 'Configuration',
            },
            option2 => {    # Canadian physical or Physical "#2"
                _netUnits => 'Net Quantity',
            },
        },
    };
}

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 %mapping = (
    'digital sales' => {
        countryCode => 'US',
        currencyCode => 'USD',
        productType  => {
            'track'          => RPS::File::Sale::TYPE_TRACK,
            'complete album' => RPS::File::Sale::TYPE_ALBUM,
        },
        types => {
            '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,
            },
        },
    },
    'sound exchange' => {
        countryCode => 'US',
        currencyCode => 'USD',
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'aif' => {
        productType => {
            'evideo' => RPS::File::Sale::TYPE_TRACK,
            'etrack' => RPS::File::Sale::TYPE_TRACK,
            'elp'    => RPS::File::Sale::TYPE_ALBUM,
            'eaahd'  => RPS::File::Sale::TYPE_ALBUM,
            'espmt'  => RPS::File::Sale::TYPE_TRACK,
            'esp'    => RPS::File::Sale::TYPE_TRACK,
            '5cd'    => RPS::File::Sale::TYPE_CD,
            '12lp33' => RPS::File::Sale::TYPE_LP,
        },
        types => {
            'allocation-streams (audio/video)' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                #mediaType => extra rules below
            },
            'cloud service' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'broadcasting & public perf-label share only' => {
                formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
                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,
            },
            'permanent download' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'subscription' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            '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,
            },
            'allocation-streams (audio/video)' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'allocation-download' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                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,
            },
            'broadcast vod' => {
                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,
            },
            '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,
            },
            'icloud channel' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'internet broadcast radio' => {
                formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
                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 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,
            },
            '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,
            },
            'portable subscriptions' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            '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,
            },
            'programmed video streaming' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeVideo,
            },
            'streaming (subscription)' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                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,
            },
            'sub user generated streams (umg video)' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeVideo,
            },
            'subscriptions' => {
                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 ota permanent downloads (audio)' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'subscription programmed streaming' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'subscription streaming by third party' => {
                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,
            },
            'sub user generated streams (umg audio)' => {
                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,
            },
            '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 stream video portable – 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,
            },
            '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,
            },
        }
    },
    # 'canadian digital' or an alias 'digital'
    'canadian digital' => {
        countryCode => 'CA',
        currencyCode => 'CAD',
        productType  => {
            '#'     => RPS::File::Sale::TYPE_ALBUM,
            '_any_' => RPS::File::Sale::TYPE_TRACK,
        },
        types => {
            'audio cloud services - subscr' => {
                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,
            },
            'audio track stream portable - subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'audio track stream webcast' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'audio track stream webcast - subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'audio track stream webcast portable' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'audio track stream webcast portable - su' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital allocations streams (audio)' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital aud track stream -subscr ltdcat' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital online aud album permanent' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital online aud track permanent' => {
                formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital online aud track stream' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital online aud track stream - subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'digital online vid track stream' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
            },
            'telco breakage - audio subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'user generated content stream - subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'user generated content stream track' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
            },
            'video cloud services - subscr' => {
                formatType => RPS::File::Sale::FORMAT_STREAM,
                mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
            },
            'video stream video portable - subscr' => {
                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,
            },
        },
    },
    # 'physical sales' or an alias 'physical'
    'physical sales' => {
        countryCode  => 'US',
        currencyCode => 'USD',
        productType  => {
            'compact disc' => RPS::File::Sale::TYPE_CD,
            'album'        => RPS::File::Sale::TYPE_LP,
            'single'       => RPS::File::Sale::TYPE_LP5,
        },
    },
    # 'canadian physical' or an alias 'physical'
    'canadian physical' => {
        countryCode  => 'CA',
        currencyCode => 'CAD',
        productType  => {
            'blu-ray disc'                => RPS::File::Sale::TYPE_BLURAY,
            'cd album'                    => RPS::File::Sale::TYPE_CD,
            'cd album plus dvd video'     => RPS::File::Sale::TYPE_DVD_CD_SET,
            'dvd video album ntsc'        => RPS::File::Sale::TYPE_DVD,
            'mc album'                    => RPS::File::Sale::TYPE_CASS,
            'vinyl album 10" 33 rpm'      => RPS::File::Sale::TYPE_LP,
            'vinyl album 10" 45 rpm'      => RPS::File::Sale::TYPE_LP,
            'vinyl album 12" 33 rpm'      => RPS::File::Sale::TYPE_LP,
            'vinyl maxisingle 12" 33 rpm' => RPS::File::Sale::TYPE_LP,
            'vinyl single 7" 33 rpm'      => RPS::File::Sale::TYPE_LP5,
            'vinyl single 7" 45 rpm'      => RPS::File::Sale::TYPE_LP5,
            'vinyl single 12" 33 rpm'     => RPS::File::Sale::TYPE_LP5,
        },
    },
);

# type|option => sheet_alias => mapped_sheet_name
my %sheetAliases = (
    'digital|option1' => {
        'digital sales' => 'digital sales',
    },
    'digital|option2' => {
        'canadian digital' => 'canadian digital',
        'digital'          => 'canadian digital',   # alias
    },
    'digital|option5' => {
        'sound exchange' => 'sound exchange',
    },
    'digital|option6' => {
        'aif' => 'aif',
    },
    'digital|option7' => {
        'aif' => 'aif',
    },
    'physical|option1' => {
        'physical sales' => 'physical sales',
        'physical'       => 'physical sales',       # alias
    },
    'physical|option2' => {
        'canadian physical' => 'canadian physical',
        'physical'          => 'canadian physical', # alias
    },
    'mixed|option1' => { # AIF mixed with alternative header
        'aif' => 'aif',
    }

);

sub sheetname {
    my $self = shift;

    my $sheetName = $self->SUPER::sheetname;

    my $typeOption = $self->dataType . "|" . $self->dataTypeOption;
    my $sheetAlias = lc $sheetName;
    if ( exists $sheetAliases{$typeOption}{$sheetAlias} ) {
        return $sheetAliases{$typeOption}{$sheetAlias};
    }

    return $sheetName;
}

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

    return unless $self->{_headerFound};

    if ( $self->{_dateBegin} && $self->{_dateEnd} ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    }
    elsif ( $self->sheetname =~ /^(?:Digital|Physical) Sales$/i ) {
        ($self->{_dateBegin}, $self->{_dateEnd}) = $self->_getPeriod();
    }
    elsif ( $self->sheetname =~ /^Sound Exchange$/i ) {
        my ($y, $m) = $self->filename =~ /[_ ](\d{4})(\d{2})[ _]/;
        $self->{_dateBegin} = sprintf "%04d-%02d-%02d", $y, $m, 1;
        $self->{_dateEnd}   = sprintf "%04d-%02d-%02d", $y, $m, Days_in_Month($y, $m);
    }
    elsif ( $self->sheetname =~ /^AIF$/i ) {
        ($self->{_aif_currency_code}) = $self->_getByFieldName('currencyCode') =~ /.*\((\w{3})\).*/;
    }

    return 1;
}

sub _getPeriod {
    my $self = shift;

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

    if ( $dateBegin =~ RE_MMM_YYYY && exists $months{$1} ) {
        $start = sprintf "20%02d-%02d-%02d", $2, $months{$1}, 1;
    }
    if ( $dateEnd =~ RE_MMM_YYYY && exists $months{$1} ) {
        $end = sprintf "20%02d-%02d-%02d", $2, $months{$1}, Days_in_Month( $2, $months{$1} );
    }

    # AIF
    if ( $dateBegin =~ RE_YYYY_MM ) {
        $start = sprintf "%04d-%02d-%02d", $1, $2, 1;
    }
    if ( $dateEnd =~ RE_YYYY_MM ) {
        $end = sprintf "%04d-%02d-%02d", $1, $2, Days_in_Month( $1, $2 );
    }

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

sub saleDates {
    my $self = shift;

    if ( $self->sheetname =~ /^Canadian (?:Digital|Physical)$/i ) {
        return $self->_getPeriod();
    } elsif ( $self->sheetname =~ /^(?:Digital|Physical) Sales$/i ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    } elsif ( $self->sheetname =~ /^Sound Exchange$/i ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    } elsif ( $self->sheetname =~ /^AIF$/i ) {
        return $self->_getPeriod();
    }

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

sub serviceID {
    my $self = shift;

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

    if ( $self->sheetname =~ /^(?:Physical Sales|Canadian (?:Physical|Digital))$/i ) {
        return Client::Service::DSP_UNIVERSAL;
    } elsif ( $self->sheetname =~ /^(?:Digital Sales|AIF)$/i ) {
        my $serviceID = $self->getServiceID($service) || $RPS::Import::ServiceMap::SERVICE_ALIASES{$service};
        return $serviceID if $serviceID;
    } elsif ( $self->sheetname =~ /^Sound Exchange$/i ) {
        return Client::Service::DSP_UNIVERSAL;
    }

    $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;
    my $country = "";
    if ( exists $mapping{$sheetName}{countryCode} && exists $mapping{$sheetName}{countryCode} ) {
        return $mapping{$sheetName}{countryCode};
    }

    if ( $self->sheetname =~ /^AIF$/i ) {
        $country = $self->_getByFieldName('countryCode') || '';
        my $obj     = Common::Country->Get($country);
        return $obj->alpha2 if $obj;
    }

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

sub currencyCode {
    my $self = shift;

    my $sheetName = lc $self->sheetname;
    if ( exists $mapping{$sheetName}{currencyCode} && exists $mapping{$sheetName}{currencyCode} ) {
        return $mapping{$sheetName}{currencyCode};
    }

    if ( $self->sheetname =~ /^AIF$/i ) {
        return $self->{_aif_currency_code};
    }

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

sub productType {
    my $self = shift;

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

    if ( $sheetName =~ /^Canadian Digital$/i ) {
        return exists $mapping{$sheetName}{productType}{$type} && $mapping{$sheetName}{productType}{$type}
            ? $mapping{$sheetName}{productType}{$type}
            : $mapping{$sheetName}{productType}{_any_};
    }

    if ( exists $mapping{$sheetName}{productType}{$type} && $mapping{$sheetName}{productType}{$type} ) {
        return $mapping{$sheetName}{productType}{$type};
    }
    # RSD-3891 - an additional case for 'Digital Sales' tab
    elsif ( $sheetName =~ /^Digital Sales$/i ) {
        return $self->isrc ? RPS::File::Sale::TYPE_TRACK : RPS::File::Sale::TYPE_ALBUM;
    }
    elsif ( $sheetName =~ /^Sound Exchange$/i ) {
        return $self->isrc && $self->trackName ? RPS::File::Sale::TYPE_TRACK : RPS::File::Sale::TYPE_ALBUM;
    }

    $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 ( $self->sheetname =~ /^Digital Sales$/i && $type =~ /^broadcast linear programming$/i ) {
        # There is an additional rule for the 'Digital Sales Alt' sheet
        if ( $self->dataTypeOption eq 'option4' ) {
            my $salesType = $self->_getByFieldName('_salesType') || '';
            return RPS::File::Sale::FORMAT_PERFORMANCE if $salesType =~ /^ad supported video streaming \(no cprt\)$/i;
        } else {
            return RPS::File::Sale::FORMAT_PERFORMANCE;
        }

        return RPS::File::Sale::FORMAT_PERFORMANCE
    }

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

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

sub mediaType {
    my $self = shift;

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

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

    if ( $self->sheetname =~ /^Digital Sales$/i && $type =~ /^broadcast linear programming$/i ) {
        # There is an additional rule for the 'Digital Sales Alt' sheet
        if ( $self->dataTypeOption eq 'option4' ) {
            my $salesType = $self->_getByFieldName('_salesType') || '';
            return RPS::DB::Item::MediaType::kMediaTypeVideo if $salesType =~ /^ad supported video streaming \(no cprt\)$/i;
        } else {
            return RPS::DB::Item::MediaType::kMediaTypeVideo;
        }
    }

    if ( $self->sheetname =~ /^AIF$/i && $type =~ /^allocation\-streams \(audio\/video\)$/i ) {
        if ( $self->dataTypeOption eq 'option1' ) {
            my $productType = $self->_getByFieldName('productType') || '';
            return RPS::DB::Item::MediaType::kMediaTypeAudio if $productType =~ /^ETRACK$/i;
            return RPS::DB::Item::MediaType::kMediaTypeVideo if $productType =~ /^EVIDEO$/i;
        }
    }

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

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

sub artistName {
    my $self = shift;

    my $artistName = $self->_getByFieldName('artistName') || '';
    if ( $self->sheetname =~ /^Digital Sales$/i ) {
        return $self->productType eq RPS::File::Sale::TYPE_ALBUM
            ? $self->_getByFieldName('_albumArtist')
            : $self->_getByFieldName('_trackArtist');
    } elsif ( $self->sheetname =~ /^Physical Sales$/i ) {
        $artistName =~ s/(.*),(.*)/$2 $1/;
    }

    return $artistName;
}

sub upc {
    my $self = shift;

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

    return $upc;
}

sub isrc {
    my $self = shift;

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

    return $isrc;
}

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

sub priceLevel {
    my $self = shift;

    if ( $self->sheetname =~ /^Physical Sales$/i ) {
        my $level = $self->_getByFieldName('priceLevel') || '';
        return RPS::File::Sale::PLEVEL_BUDGET  if $level =~ /^BUDGET$/i;
        return RPS::File::Sale::PLEVEL_MID     if $level =~ /^MIDLINE$/i;
        return RPS::File::Sale::PLEVEL_FULL    if $level =~ /^REGULAR FRONTLINE$/i;
        $self->fail("Unable to determine price_level from: '$level'.");
    }

    return RPS::File::Sale::PLEVEL_UNKNOWN;
}

# digital
sub price {
    my $self = shift;

    if ( $self->sheetname =~ /^Sound Exchange$/i ) {
        my $digital = $self->_getByFieldName('_digital') || 0;
        my $license = $self->_getByFieldName('_license') || 0;
        return $digital + $license;
    }

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

    if ( $self->sheetname =~ /^Sound Exchange$/i ) {
        return $self->price <=> 0;
    }

    my $units = $self->_getByFieldName('units') || 0;
    my $price = $self->_getByFieldName('price') || 0;

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

    return $units;
}

# physical
sub netUnits           { $_[0]->_getByFieldName('_netUnits')    || 0 }
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->dataTypeOption eq 'option1'
        ? ( $self->netUnits > 0 ? $self->netUnits : 0 )
        : abs( $self->_getByFieldName('sales') || 0 );
}

sub returns {
    my $self = shift;

    return $self->dataTypeOption eq 'option1'
        ? ( $self->netUnits < 0 ? abs $self->netUnits : 0 )
        : abs( $self->_getByFieldName('returns') || 0 );
}


sub _isValidSaleRecord {
    my $self = shift;

    my $upc = $self->_getByFieldName('upc') || '';
    # skip multiline header
    return if $upc =~ /UPC/i;

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

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

my @sheetsToSkip = (
    'Statement',
    'Reserves',
    'Reserve',
    'Other Handling Fee',
    'SOP',
    'OSP',
    'Manufacturing',
    'Digital Revenue',
    'Digital Expenses',
    'LipLog',
    'Marketing',
    'CoOp',
    'Sheet3',
    'Sheet4',
    'Data Pivot',
    'Data',
    'Famehouse',
    'MLP',
    'FRT',
    'EGR',
    'VAS',
    'API',
    'MFG',
    '99003042',
    'UMS',
    'Backup for 99100013 &amp; 99127121',
    'JDL RCL TO JDL 01 19 (sxc)',
    'U-M007385740-AD-WB-NR',
    'Co-Op',
    'Doc # 99127121_Amount $ 331486',
    'Doc # 99127121_Amount $ 33148.6',
    'JDL AIF Reclass to JD2'
);

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;
}

sub _mapFaceProductType { }
sub _mapFaceServiceID   { 'serviceID' }
sub _mapFaceCountryCode { }
sub _mapFaceFormatType  { }
sub _mapFaceMediaType   { }

1;