package RPS::Import::Caroline::v16;

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 parent  qw/RPS::Import::MapFaceBase RPS::Import::ImporterMixed/;

sub physical { 2 }

sub _fieldMapExtended {
    {
        digital => {
            option1 => {
                labelName   => 'B',
                serviceID   => 'V',
                dateBegin   => 'T',
                dateEnd     => 'U',
                productType => 'I',
                formatType  => 'E',
                upc         => 'M',
                albumName   => 'L',
                isrc        => 'Q',
                trackName   => 'O',
                units       => 'X',
                price       => 'W',
                mediaType   => 'E',
            },
         },
        physical => {
            # Physcial sales
            # A header with 55 fields and the '\d{6} MTD Physical Financial Net Sales' => AZ
            option1 => {
                labelName       => 'I',
                dateBegin       => 'B4',
                dateEnd         => 'B4',
                productType     => 'AF',
                clientProductID => 'R',
                artistName      => 'M',
                upc             => 'S',
                albumName       => 'N',
                channel         => 'Z',
                priceLevel      => 'AL',
                wholesalePrice  => 'AN',
                totalRevenue    => 'AZ',
            },
            # Physcial sales (alt2)
            # A header with 54 fields and the 'MTD Physical Financial Net Sales' => AY
            option2 => {
                labelName       => 'I',
                dateBegin       => 'B4',
                dateEnd         => 'B4',
                productType     => 'AF',
                clientProductID => 'R',
                artistName      => 'M',
                upc             => 'S',
                albumName       => 'N',
                channel         => 'Z',
                priceLevel      => 'AL',
                wholesalePrice  => 'AN',
                totalRevenue    => 'AY',
            },
            # Physcial sales (alt3)
            # A header with 53 fields and the 'MTD Physical Financial Net Sales' => AY
            option3 => {
                labelName       => 'I',
                dateBegin       => 'B4',
                dateEnd         => 'B4',
                productType     => 'AF',
                clientProductID => 'R',
                artistName      => 'M',
                upc             => 'S',
                albumName       => 'N',
                channel         => 'Z',
                priceLevel      => 'AL',
                wholesalePrice  => 'AM',
                totalRevenue    => 'AX',
            },
            # Physcial sales (alt4)
            # A header with 53 fields and the 'MTD Physical Financial Net Sales' => AZ
            option4 => {
                labelName       => 'AQ',
                dateBegin       => 'B4',
                dateEnd         => 'B4',
                productType     => 'AD',
                clientProductID => 'P',
                artistName      => 'K',
                upc             => 'Q',
                albumName       => 'L',
                channel         => 'X',
                priceLevel      => 'AJ',
                wholesalePrice  => 'AL',
                totalRevenue    => 'AZ',
            },
            # Physcial sales (alt5)
            # A header with 55 fields and the 'MTD Physical Financial Net Sales' => AZ
            option5 => {
                labelName       => 'AQ',
                dateBegin       => 'B4',
                dateEnd         => 'B4',
                productType     => 'AD',
                clientProductID => 'P',
                artistName      => 'K',
                upc             => 'Q',
                albumName       => 'L',
                channel         => 'X',
                priceLevel      => 'AJ',
                wholesalePrice  => 'AL',
                totalRevenue    => 'AZ',
            },
        },
    };
}

sub _unverifiedFieldMapExtended {
    {
        digital => {
            option1 => {
                _albumArtist => 'K',
                _trackArtist => 'N',
                _sales_type  => 'G',
            },
        },
        physical => {
            # Physcial sales
            # A header with 55 fields and the '\d{6} MTD Physical Financial Net Sales' => AZ
            option1 => {
                _altDateBegin  => 'B5',
                _altDateEnd    => 'B5',
                _netUnits      => 'AT',
            },
            # Physcial sales (alt2)
            # A header with 54 fields and the 'MTD Physical Financial Net Sales' => AY
            option2 => {
                _altDateBegin  => 'B5',
                _altDateEnd    => 'B5',
                _netUnits      => 'AT',
                _priceCodeDesc => 'AH'
            },
            # Physcial sales (alt3)
            # A header with 53 fields and the 'MTD Physical Financial Net Sales' => AY
            option3 => {
                _altDateBegin  => 'B5',
                _altDateEnd    => 'B5',
                _netUnits      => 'AS',
                _priceCodeId   => 'AG',
            },
            # Physcial sales (alt4)
            # A header with 53 fields and the 'MTD Physical Financial Net Sales' => AZ
            option4 => {
                _altDateBegin  => 'B5',
                _altDateEnd    => 'B5',
                _netUnits      => 'AT',
                _priceCodeDesc => 'AF',
            },
            # Physcial sales (alt5)
            # A header with 55 fields and the 'MTD Physical Financial Net Sales' => AZ
            option5 => {
                _altDateBegin  => 'B5',
                _altDateEnd    => 'B5',
                _netUnits      => 'AT',
                _priceCodeDesc => 'AF',
            },
        },
    };
}

sub _headerIdentifierExtended {
    {
        digital => {
            option1 => {
                upc => 'UPC ID',
            },
        },
        physical => {
            # Physcial sales
            # A header with 55 fields and the '\d{6} MTD Physical Financial Net Sales' => AZ
            # this should be at the very beginning
            option1 => {
                _netUnits   => '\d{6} MTD Physical Financial Net Sales',
            },
            # Physcial sales (alt2)
            # A header with 54 fields and the 'MTD Physical Financial Net Sales' => AY
            option2 => {
                _priceCodeDesc => 'Price Code DESC',
            },
            # Physcial sales (alt3)
            # A header with 53 fields and the 'MTD Physical Financial Net Sales' => AY
            option3 => {
                _priceCodeId => 'Price Code ID',
            },
            # Physcial sales (alt4)
            # A header with 54 fields and the 'MTD Physical Financial Net Sales' => AZ
            option4 => {
                _priceCodeDesc => 'Price Code DESC',
            },
            # Physcial sales (alt5)
            # A header with 55 fields and the 'MTD Physical Financial Net Sales' => AZ
            option5 => {
                _priceCodeDesc => 'Price Code DESC',
            },
        },
    };
}

# NOTE! Key names starting with "!!!" have additional conditions
my %typesMap = (
    '!!!broadcast linear programming' => {
        formatType => RPS::File::Sale::FORMAT_PERFORMANCE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
        additional => [
            'AD SUPPORTED VIDEO STREAMING (NO CPRT)',
            'SUB USER GENERATED STREAMS (UMG VIDEO)'
        ],
    },
    '!!!not applicable' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
        additional => [
            'DIGITAL ALLOCATIONS STREAMS'
        ]
    },
    'ad supported streams' => {
        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,
    },
    'conditional mastertone' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ephemeral plays' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'film non-music video rental' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'fixed plays' => {
        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,
    },
    'long form music video rental' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'mobile audio streams' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'mobile video streaming' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'non-music video sell-thru' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    'ota permanent downloads' => {
        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,
    },
    'permanent downloads promo' => {
        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 (non-royalty bearing)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'programmed streaming-capped (non-royalty bearing)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ringbacks' => {
        formatType => RPS::File::Sale::FORMAT_RINGTONE,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ringback - no copyright' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'ringbacks (no cprt liability)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeAudio,
    },
    'standard mobile ringsounds' => {
        formatType => RPS::File::Sale::FORMAT_RINGTONE,
        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 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,
    },
    '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 downloads' => {
        formatType => RPS::File::Sale::FORMAT_DOWNLOAD,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
    '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)' => {
        formatType => RPS::File::Sale::FORMAT_STREAM,
        mediaType  => RPS::DB::Item::MediaType::kMediaTypeVideo,
    },
);

sub countryCode  { 'US' }
sub currencyCode { 'USD' }

sub serviceID {
    my $self = shift;

    return Client::Service::DSP_CAROLINE if $self->dataType =~ /physical/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 )
}

sub saleDates {
    my $self = shift;

    if ( $self->dataType =~ /physical/i ) {
        return $self->{_dateBegin}, $self->{_dateEnd};
    }

    my ($date_begin, $date_end, $month, $year);
    $date_begin = $self->_getByFieldName("dateBegin");

    if ($date_begin =~ /^(\d\d)-(\d\d)-(\d{4})$/) { # DD-MM-YYYY
        ($year, $month) = ($3, $2);
    }
    elsif ($date_begin =~ /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/) { # DD/MM/YYYY
        ($year, $month) = ($3, $2);
    }
    elsif ($date_begin =~ /^(\d{4})-(\d\d)-(\d\d)$/) { # YYYY-MM-DD
        ($year, $month) = ($1, $2);
    } elsif ( $date_begin =~ /^\d{5}$/ ) {
        return $self->_getDates( date_begin => $date_begin, type => "msft" );
    }

    $date_begin = sprintf( "%04d-%02d-%02d", $year, $month, 1 );
    $date_end = sprintf( "%04d-%02d-%02d", $year, $month, Days_in_Month( $year, $month ) );

    return ($date_begin, $date_end);
}

sub productType {
    my $self = shift;

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

    if ( $self->dataType =~ /physical/i ) {
        return RPS::File::Sale::TYPE_CD         if $type =~ /^(?:CD|CO)$/i;
        return RPS::File::Sale::TYPE_DVD_CD_SET if $type =~ /^(?:DX|DZ)$/i;
        return RPS::File::Sale::TYPE_DBL_CD     if $type =~ /^CF$/i;
        return RPS::File::Sale::TYPE_CD_SIN     if $type =~ /^S5?$/i;
        return RPS::File::Sale::TYPE_CASS       if $type =~ /^CT$/i;
        return RPS::File::Sale::TYPE_LP         if $type =~ /^LP$/i;
        return RPS::File::Sale::TYPE_LP5        if $type =~ /^ST$/i;
        return RPS::File::Sale::TYPE_DVD        if $type =~ /^DV$/i;

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

    if (
        $type =~ /^(?:track|VIDEO DOWNLOADS|UNKNOWN|FULL TRACK DOWNLOADS|OTHER DIGITAL)$/i ||
        $type =~ /^ONLINE - (?:AUDIO STREAMING|SUBSCRIPTIONS|TRACKS|VIDEO STREAMING|VIDEOS)$/i
    ) {
        return RPS::File::Sale::TYPE_TRACK;
    } elsif ( $type =~ /^bundles$/i ) {
        return ( $self->SUPER::isrc =~ /^[a-z]{2}.+/i ) ? RPS::File::Sale::TYPE_TRACK : RPS::File::Sale::TYPE_ALBUM;
    } elsif ( $type =~ /^(?:complete album|ONLINE - ALBUMS)$/i ) {
        return RPS::File::Sale::TYPE_ALBUM;
    } elsif ( $type =~ /^NOT APPLICABLE$/i ) {
        return RPS::File::Sale::TYPE_TRACK if $self->SUPER::isrc =~ /^[a-z]{2}.+/i;
    }

    return $self->handleError( "Unable to determine product_type from '$type'", RPS::DB::Item::SaleImportError::kErrorProductType );
}

sub formatType {
    my $self = shift;

    my $type = lc( $self->_getByFieldName('formatType') || '' );
    if ( $type =~ /mobile images/i ) {
        return $self->handleError( "Unsupported format_type '$type'", RPS::DB::Item::SaleImportError::kErrorNonqualifying );
    }
    return $typesMap{$type}{formatType} if exists $typesMap{$type}{formatType};

    # RSD-3925 - an additional rule
    my $extraType = '!!!' . $type;
    if ( exists $typesMap{$extraType} ) {
        my $salesType = $self->_getByFieldName('_sales_type') || '';
        return $typesMap{$extraType}{formatType} if grep { /^\Q$salesType\E$/i } @{ $typesMap{$extraType}{additional} };
    }

    return $self->handleError( "Unable to determine format_type from '$type", RPS::DB::Item::SaleImportError::kErrorFormatType );
}

sub mediaType {
    my $self = shift;

    return if $self->dataType =~ /physical/i;

    my $type = lc( $self->_getByFieldName('mediaType') || '' );
    return $typesMap{$type}{mediaType} if exists $typesMap{$type}{mediaType};

    # RSD-3925 - an additional rule
    my $extraType = '!!!' . $type;
    if ( exists $typesMap{$extraType} ) {
        my $salesType = $self->_getByFieldName('_sales_type') || '';
        return $typesMap{$extraType}{mediaType} if grep { /^\Q$salesType\E$/i } @{ $typesMap{$extraType}{additional} };
    }

    return $self->handleError( "Unable to determine media_type from '$type", RPS::DB::Item::SaleImportError::kErrorMediaType );
}

sub artistName {
    my $self = shift;

    return $self->_getByFieldName('artistName') if $self->dataType =~ /physical/i;

    return $self->productType eq RPS::File::Sale::TYPE_ALBUM
        ? $self->_getByFieldName('_albumArtist')
        : $self->_getByFieldName('_trackArtist');
}

sub channel {
    my $self = shift;

    return if $self->dataType !~ /physical/i;

    my $channel = $self->_getByFieldName('channel') || '';
    return RPS::File::Sale::CHANNEL_RETAIL   if $channel =~ /^(?:11|29|41|51|53|91|56|87)$/;
    return RPS::File::Sale::CHANNEL_DIRECT   if $channel =~ /^54$/;
    return RPS::File::Sale::CHANNEL_MILITARY if $channel =~ /^21$/;

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

sub priceLevel {
    my $self = shift;

    return if $self->dataType !~ /physical/i;

    my $level = $self->_getByFieldName('priceLevel') || '';

    return RPS::File::Sale::PLEVEL_FULL    if $level =~ /^(?:REGULAR FRONTLINE|SINGLE)$/i;
    return RPS::File::Sale::PLEVEL_MID     if $level =~ /^(?:MIDLINE)$/i;
    return RPS::File::Sale::PLEVEL_BUDGET  if $level =~ /^(?:BUDGET)$/i;
    return RPS::File::Sale::PLEVEL_UNKNOWN if $level =~ /^(?:UNKNOWN\-?)$/i;

    $self->fail("Unable to determine price_level from: '$level'.");
}

sub free {
    my $self = shift;

    my $units   = $self->units || $self->netUnits;
    my $revenue = $self->price || $self->totalRevenue;

    return $units && !$revenue ? 1 : 0;
}

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

    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       {
    my $self = shift;

    my $netUnits = $self->_getByFieldName('_netUnits') || 0;
    return $self->totalRevenue >= 0 ? abs $netUnits : abs($netUnits) * -1;
}

sub totalRevenue       { $_[0]->_getByFieldName('totalRevenue') || 0 }
sub sales              { $_[0]->netUnits     > 0 ? $_[0]->netUnits         : 0 }
sub salesRevenue       { $_[0]->totalRevenue > 0 ? $_[0]->totalRevenue     : 0 }
sub returns            { $_[0]->netUnits     < 0 ? abs $_[0]->netUnits     : 0 }
sub returnsRevenue     { $_[0]->totalRevenue < 0 ? abs $_[0]->totalRevenue : 0 }

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

sub _isValidSaleRecord {
    if ( $_[0]->dataType =~ /physical/i ) {
        return ($_[0]->_getByFieldName('albumName') || $_[0]->_getByFieldName('artistName')) &&
               ($_[0]->_getByFieldName('_netUnits') || $_[0]->_getByFieldName('totalRevenue'));
    }
    return $_[0]->_getByFieldName('albumName') || $_[0]->_getByFieldName('artistName');
}

sub _processSheet {
    my ($self, $sheet, $sheetnum) = @_;

    if ( $self->sheetname =~ /^Statement$/i ) {
        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 $mainDateCell = lc $sheet->[3][1];    # B4 cell
        my $altDateCell  = lc $sheet->[4][1];    # B5 cell
        ( my $dateString ) = grep { $_ =~ /^FOR 1 MONTH ENDING/i } $mainDateCell, $altDateCell;

        if ( $dateString =~ / ([a-z]{3})[a-z]*? (\d{1,2}),? (\d{4})$/i ) {
            $self->{_dateBegin} = sprintf "%04d-%02d-%02d", $3, $months{$1}, 1;
            $self->{_dateEnd}   = sprintf "%04d-%02d-%02d", $3, $months{$1}, Days_in_Month($3, $months{$1});
            return;
        }
    }

    my $headerFound = $self->SUPER::_processSheet( $sheet, $sheetnum );

    # skip tabs
    if ( $self->sheetname =~ /^(?:SoundExchange|Digital (?:expenses|Royalties)|Liplog)$/i ) {
        Common::Log::Print( "Skip sheet: " . $self->sheetname );
    }

    if ( !$headerFound && $self->sheetname =~ /^(?:(?:Physical|Digital) Sales)$/i ) {
        $self->fail( "Unable to find header for '$self->{sheetname}' sheet" );
    }

    if ( $headerFound && $self->sheetname !~ /^(?:(?:Physical|Digital) Sales)$/i ) {
        $self->fail( "Unknown sheet '$self->{sheetname}' with predefined title" );
    }

    return $headerFound;
}


1;