package BookPub::Import::Importer::Audible::Version8;

use strict;
use warnings;

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

use lib '/app/tools/sale_import/lib/';
use Import::ValidationError;

use lib '/app/tools/bookpub/lib';
use base 'BookPub::Import::Importer';


my %sheetsWithData = (
    ws4v_sales_details     => 'WS4V',
    non_ws4v_sales_details => 'NONWS4V',
    promotional_details    => 'PROMO'
);

my @saleTypes = qw/alc alop al/;

sub _fieldMap {
    my $self = shift;

    my %map = (
        ws4v_sales_details => {
            alc => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'H',
                rListPrice           => 'G',
                rRevenue             => 'I',
                isFree               => 'I',
            },
            alop => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'J',
                rListPrice           => 'G',
                rRevenue             => 'K',
                isFree               => 'K',
            },
            al => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'L',
                rListPrice           => 'G',
                rRevenue             => 'M',
                isFree               => 'M',
            }
        },
        non_ws4v_sales_details => {
            alc => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'H',
                rListPrice           => 'G',
                rRevenue             => 'I',
                isFree               => 'I',
            },
            alop => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'J',
                rListPrice           => 'G',
                rRevenue             => 'K',
                isFree               => 'K',
            },
            al => {
                purchaseType         => 'G',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'L',
                rListPrice           => 'G',
                rRevenue             => 'M',
                isFree               => 'M',
            }
        },
        promotional_details => {
            alc => {
                dateBegin            => 'H',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'I',
                rListPrice           => 'G',
                rRevenue             => 'J',
                isFree               => 'J',
            },
            alop => {
                dateBegin            => 'H',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'K',
                rListPrice           => 'G',
                rRevenue             => 'L',
                isFree               => 'L',
            },
            al => {
                dateBegin            => 'H',
                rAuthor              => 'E',
                rTitle               => 'D',
                rIsbn13              => 'F',
                countryCode          => 'B',
                serviceProductID     => 'C',
                rUnits               => 'M',
                rListPrice           => 'G',
                rRevenue             => 'N',
                isFree               => 'N',
            }
        },
    );

    if ( exists $map{$self->_sheetName} && exists $map{$self->_sheetName}{$self->_saleType} ) {
        return $map{$self->_sheetName}{$self->_saleType}
    }

    return {};
}

sub _unverifiedFieldMap {
    my $self = shift;

    my %map = (
        ws4v_sales_details => {
            alc => {
                headerLevel1       => 'I1',   # for ALC sale type (Audible Non-Member Cash)
                headerLevel2       => 'I2',   # for ALC sale type (Audible Non-Member Cash)
                rUnits_1           => 'J',    # for ALOP sale type
                rUnits_2           => 'L',    # for AL sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'K',    # for ALOP sale type
                rRevenue_2         => 'M',    # for AL sale type
                grandTotalPaid     => 'Q',
            },
            alop => {
                headerLevel1       => 'K1',   # for ALOP sale type (Audible Member Cash )
                headerLevel2       => 'K2',   # for ALOP sale type (Audible Member Cash )
                rUnits_1           => 'H',    # for ALC sale type
                rUnits_2           => 'L',    # for AL sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'I',    # for ALC sale type
                rRevenue_2         => 'M',    # for AL sale type
                grandTotalPaid     => 'Q',
            },
            al => {
                headerLevel1       => 'M1',   # for AL sale type (Audible Member Credit )
                headerLevel2       => 'M2',   # for AL sale type (Audible Member Credit )
                rUnits_1           => 'H',    # for ALC sale type
                rUnits_2           => 'J',    # for ALOP sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'I',    # for ALC sale type
                rRevenue_2         => 'K',    # for ALOP sale type
                grandTotalPaid     => 'Q',
            },
        },
        non_ws4v_sales_details => {
            alc => {
                headerLevel1       => 'I1',   # for ALC sale type (Audible Non-Member Cash)
                headerLevel2       => 'I2',   # for ALC sale type (Audible Non-Member Cash)
                rUnits_1           => 'J',    # for ALOP sale type
                rUnits_2           => 'L',    # for AL sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'K',    # for ALOP sale type
                rRevenue_2         => 'M',    # for AL sale type
                grandTotalPaid     => 'Q',
            },
            alop => {
                headerLevel1       => 'K1',   # for ALOP sale type (Audible Member Cash )
                headerLevel2       => 'K2',   # for ALOP sale type (Audible Member Cash )
                rUnits_1           => 'H',    # for ALC sale type
                rUnits_2           => 'L',    # for AL sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'I',    # for ALC sale type
                rRevenue_2         => 'M',    # for AL sale type
                grandTotalPaid     => 'Q',
            },
            al => {
                headerLevel1       => 'M1',   # for AL sale type (Audible Member Credit )
                headerLevel2       => 'M2',   # for AL sale type (Audible Member Credit )
                rUnits_1           => 'H',    # for ALC sale type
                rUnits_2           => 'J',    # for ALOP sale type
                grandTotalQuantity => 'P',
                rRevenue_1         => 'I',    # for ALC sale type
                rRevenue_2         => 'K',    # for ALOP sale type
                grandTotalPaid     => 'Q',
            },
        },
        promotional_details => {
            alc => {
                headerLevel1       => 'I1',   # for ALC sale type (Audible Non-Member Cash)
                headerLevel2       => 'I2',   # for ALC sale type (Audible Non-Member Cash)
                rUnits_1           => 'K',    # for ALOP sale type
                rUnits_2           => 'M',    # for AL sale type
                grandTotalQuantity => 'O',
                rRevenue_1         => 'L',    # for ALOP sale type
                rRevenue_2         => 'N',    # for AL sale type
                grandTotalPaid     => 'P',
            },
            alop => {
                headerLevel1       => 'K1',   # for ALOP sale type (Audible Member Cash )
                headerLevel2       => 'K2',   # for ALOP sale type (Audible Member Cash )
                rUnits_1           => 'I',    # for ALC sale type
                rUnits_2           => 'M',    # for AL sale type
                grandTotalQuantity => 'O',
                rRevenue_1         => 'J',    # for ALC sale type
                rRevenue_2         => 'N',    # for AL sale type
                grandTotalPaid     => 'P',
            },
            al => {
                headerLevel1       => 'M1',   # for AL sale type (Audible Member Credit )
                headerLevel2       => 'M2',   # for AL sale type (Audible Member Credit )
                rUnits_1           => 'I',    # for ALC sale type
                rUnits_2           => 'K',    # for ALOP sale type
                grandTotalQuantity => 'O',
                rRevenue_1         => 'J',    # for ALC sale type
                rRevenue_2         => 'L',    # for ALOP sale type
                grandTotalPaid     => 'P',
            },
        },
    );

    if ( exists $map{$self->_sheetName} && exists $map{$self->_sheetName}{$self->_saleType} ) {
        return $map{$self->_sheetName}{$self->_saleType}
    }

    return {};
}

sub _headerIdentifier { rTitle => 'Title' }


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,
);

sub rServiceName {
    return BookPub::Tracker::Service::GetDefaultServiceName(
        service_id => shift->serviceID
    );
}

sub _useIsSaleRec      { 1 }
sub priceType          { 'rrp' }
sub productType        { BookPub::DB::Item::Sale::kProductTypeAudioBook }
sub rPurchaseType      { uc( shift->_saleType ) }
sub rListPriceCurrency { shift->currencyCode }

sub dateBegin {
    my $self = shift;

    if ( $self->_sheetName !~ /^promotional_details$/ ) {
        return $self->{_dateBegin};
    }

    my $dateBegin = $self->_getByFieldName('dateBegin') || '';
    # 10-Oct-2023
    if ( my($d, $m, $y) = $dateBegin =~ /(\d{1,2})-(\w{3,9})-(\d{4})/ ) {
        $m = substr($m, 0, 3);
        $m = $months{lc $m};
        return sprintf("%04d-%02d-%02d", $y, $m, $d);
    }

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

sub dateEnd {
    my $self = shift;

    if ( $self->_sheetName !~ /^promotional_details$/ ) {
        return $self->{_dateBegin};
    }

    return $self->dateBegin();
}

sub purchaseType {
    my $self = shift;

    if ( $self->_sheetName !~ /^promotional_details$/ ) {
        return BookPub::DB::Item::Sale::kPurchaseTypeSubscriptionDL;
    }

    return BookPub::DB::Item::Sale::kPurchaseTypePromotional;
}

sub rUnits {
    my $self = shift;

    my $rUnits      = $self->_getByFieldName('rUnits')             || 0;
    my $rUnits1     = $self->_getByFieldName('rUnits_1')           || 0;
    my $rUnits2     = $self->_getByFieldName('rUnits_2')           || 0;
    my $rUnitsTotal = $self->_getByFieldName('grandTotalQuantity') || 0;

    my $sum = sprintf "%.8f", $rUnits + $rUnits1 + $rUnits2;
    $rUnitsTotal = sprintf "%.8f", $rUnitsTotal;

    if ( $sum != $rUnitsTotal ) {
        $self->fail("Sum of rUnits per sale type does not equal Grand Total Quantity");
    }

    return $rUnits;
}

sub rRevenue {
    my $self = shift;

    my $rRevenue      = $self->_getByFieldName('rRevenue')       || 0;
    my $rRevenue1     = $self->_getByFieldName('rRevenue_1')     || 0;
    my $rRevenue2     = $self->_getByFieldName('rRevenue_2')     || 0;
    my $rRevenueTotal = $self->_getByFieldName('grandTotalPaid') || 0;

    my $sum = sprintf "%.8f", $rRevenue + $rRevenue1 + $rRevenue2;
    $rRevenueTotal = sprintf "%.8f", $rRevenueTotal;

    if ($sum != $rRevenueTotal ) {
        $self->fail("Sum of rRevenue per sale type does not equal Grand Total Royalty Paid");
    }

    return $rRevenue;
}

sub currencyCode {
    my $self = shift;

    my $clientID = Common::RSApp::GetClientID();

    return 'GBP' if $clientID == 355;  # HUK
    return 'AUD' if $clientID == 348;  # MMAU
    return 'GBP' if $clientID == 359;  # MMUK
}

sub isFree {
    my $self = shift;

    my $rUnits = $self->_getByFieldName('rUnits')     || 0;
    my $rRevenue = $self->_getByFieldName('rRevenue') || 0;

    return !$rRevenue && $rUnits ? 'Y' : 'N';
}

sub rTransactionCategory {
    my $self = shift;

    my $headerLevel1 = $self->_getByFieldName('headerLevel1') || 0;
    my $headerLevel2 = $self->_getByFieldName('headerLevel2') || 0;

    return $headerLevel1 . ' & ' . $headerLevel2;
}

sub rComments {
    my $self = shift;

    return $sheetsWithData{$self->_sheetName};
}


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

    if ( $self->sheetname =~ /^Summary$/i ) {
        my $limit = 50;
        foreach my $aRow ( @$sheet ) {
            # searching for the date value in column B (actually B4)
            my $salePeriod = $$aRow[1] || '';
            if ( my ($m, $y) = $salePeriod =~ /^(\w{3,9})\-(\d{4})$/ ) {
                $m = substr($m, 0, 3);
                $m = $months{lc $m};
                $self->{_dateBegin} = sprintf("%04d-%02d-%02d", $y, $m, 1);
                last;
            }
            unless (--$limit) {
                $self->fail("Unable to determine dateBegin and dateEnd.");
                last;
            }
        }
    }

    if ( Common::RSApp::GetClientID() !~ /^(?:355|348|359)$/) {    # HUK | MMAU | MMUK
        $self->fail("The currency for this client must be confirmed.");
    }

    if ( exists $sheetsWithData{ $self->_sheetName } ) {
        # initial position with sales type 'alc'
        $self->{_sale_type} = $saleTypes[0];
        $self->SUPER::_processSheet($sheet, $sheetnum);
    }

    return;
}

sub _processLine {
    my $self = shift;

    unless ( $self->_isSaleRec ) {
        Common::Log::Print( " -- Ignore line sheet: " . $self->sheetname . " line: " . $self->linenum );
        $self->{_linesIgnored}++;
        return;
    }

    $self->_saleType('');
    foreach my $saleType ( @saleTypes ) {
        $self->_saleType($saleType);
        if ( $self->rRevenue || $self->rUnits ) {
            $self->_saveLine();
        }
    }
    $self->{_linesImported}++;
}

sub _getByFieldName {
    my ($self, $field) = @_;

    my $line      = $self->line;
    my $sheetName = $self->_sheetName || '';
    my $saleType  = $self->_saleType  || '';

    assert( $field, "Field required" );
    assert( $line,  "Line not set" );

    my $loc = eval { $self->_fieldMap->{$field} || $self->_unverifiedFieldMap->{$field} };

    if ( defined($loc) && $loc =~ m/ ^ (?:.*\!)? [A-Z][A-Z]? \d+ $ /ix ) {
        my $cacheField = '__' . join('_', $sheetName, $saleType, $field);
        return $self->{$cacheField} if exists $self->{$cacheField};
        return $self->{$cacheField} = $self->cell($loc);
    }

    my $offset = $self->_getOffset($field);

    return defined($offset) ? $line->[$offset] : undef;
}

sub _isSaleRec {
    my $self = shift;

    my $rIsbn13 = $self->_getByFieldName('rIsbn13') || '';
    return if $rIsbn13 =~ /(?:Total By Earner|Grand Total|Subtotal)/i;

    my $purchaseType = $self->_getByFieldName('purchaseType') || '';
    return if $purchaseType =~ /^promo$/i;

    return 1;
}


# local functions
sub _sheetName {
    my $self = shift;

    my $sheetName = lc( $self->sheetname || '' );
    $sheetName =~ s/[\s\-]/_/g;

    return $sheetName || '';
}

sub _saleType {
    my ($self, $val) = @_;

    if ( defined $val ) {
        $self->{_sale_type} = $val;
    }

    return $self->{_sale_type} || '';
}


1;