package RPS::Import::NineSquaredVarTab;

use strict;

use Date::Calc qw(Days_in_Month);
use Spreadsheet::ParseExcel;

use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date);
use Common::Consts;

use lib '/app/tools/data_classes/lib';

#private constants
use constant FIELD_STARTDATE => 10;
use constant FIELD_ENDDATE   => 17;

use constant FIELD_VENDORID   => 1;
use constant FIELD_PRODUCT    => 2;
use constant FIELD_PLATFORM   => 5;
use constant FIELD_ISRC       => 12;
use constant FIELD_SERVICE    => 20;
use constant FIELD_ARTIST     => 24;
use constant FIELD_TITLE      => 25;
use constant FIELD_SALEPRICE  => 26;
use constant FIELD_UNITS      => 27;
use constant FIELD_TOTALPRICE => 30;

use lib '/app/tools/rps/lib';
require RPS::Import::WMG::Util;
use RPS::File::Sale;
use RPS::Import::Importer;
use base 'RPS::Import::Importer';

#public methods

sub _importLines {
    my $self = shift;
    my %args = @_;

    #
    # Variable-tab 9 Squared file with amazingly consistant
    # headers in each tab.
    #
    my $retval      = undef;
    my $sheets      = $args{sheets};
    my $fileObj     = $args{file};
    my $sheet_names = $args{sheet_names};

    if ( $self->{clientID} == 21 )    # warner
    {
        $fileObj->Atomic(1);
        $fileObj->Save();
        print STDERR "set to atomic\n";
    }

    unless ($sheets) {
        print STDERR "sheet problem\n";
        return undef;
    }
    my $sheet_num = 0;

    foreach my $sheet (@$sheets) {

        #print STDERR "sheet is ".$sheet_names->[$sheet_num]."\n";
        if ( $sheet_names->[$sheet_num] eq "Summary" ) {
            $sheet_num++;
            next;
        }

        my $dateBegin;
        my $dateEnd;
        my $header_matched = 0;

        my $linenum = 1;
        foreach my $line (@$sheet) {
            if ( !$header_matched ) {
                $header_matched = matchHeader($line);
                next if ($header_matched);
            }

            if ( $linenum == 2 ) {
                my $start_date_raw = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $line->[FIELD_STARTDATE] );
                my $end_date_raw   = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $line->[FIELD_ENDDATE] - 1 );

                if ( $start_date_raw =~ /^\d\d\d\d-\d\d-\d\d$/ && $end_date_raw =~ /^\d\d\d\d-\d\d-\d\d$/ ) {
                    $dateBegin = $start_date_raw;
                    $dateEnd   = $end_date_raw;
                }

                #print STDERR "dates: $dateBegin and $dateEnd\n";

            } elsif ( $dateBegin && $dateEnd && $header_matched ) {
                my $prodtype = RPS::File::Sale::TYPE_TRACK;
                my ( $format, $priceType, $service_id );
                if ( $self->{clientID} == 21 ) {    # warner
                    next if ( $line->[FIELD_PLATFORM] eq "" || $line->[FIELD_PRODUCT] eq "" );
                    my $provider_type = $line->[FIELD_PLATFORM] . " " . $line->[FIELD_PRODUCT];
                    $format    = RPS::Import::WMG::Util::getWirelessFormatTypeID($provider_type);
                    $priceType = RPS::Import::WMG::Util::getWirelessPriceType($provider_type);
                    unless ( $format && $priceType ) {
                        $self->errstr("unknown format: $provider_type");
                        return undef;
                    }
                    my $service_name = $line->[FIELD_SERVICE];
                    $service_name = "9 SQUARED"           if ( $service_name eq "test" );
                    $service_name = "mTat2 Brew Motorola" if ( $service_name eq "mTat2 Brew Motorola/Alltel" );
                    $service_id   = RPS::Import::Importer->getServiceID($service_name);
                    unless ($service_id) {
                        $self->errstr("unknown service: $service_name");
                        return undef;
                    }
                    my @billTo = RPS::Import::WMG::Util::getWirelessBillto( $self->{fileServiceID} );

                    #my @billTo = RPS::Import::WMG::Util::getWirelessBillto(20);
                    my @sellTo = RPS::Import::WMG::Util::getWirelessSellto( $service_id, $billTo[0] );
                    unless ( $billTo[0] && $sellTo[0] ) {
                        $self->errstr( "no billto/sellto mapping for " . $self->{fileServiceID} . ": $service_name" );
                        return undef;
                    }
                } else {
                    $format = RPS::File::Sale::FORMAT_RINGTONE;
                }

                ##next;  #### DEBUG TESTS ONLY

                my $vendorID    = $line->[FIELD_VENDORID];
                my $artist      = $line->[FIELD_ARTIST];
                my $track       = $line->[FIELD_TITLE];
                my $isrc        = $line->[FIELD_ISRC];
                my $units       = $line->[FIELD_UNITS];
                my $retail      = $line->[FIELD_SALEPRICE];
                my $total_price = $line->[FIELD_TOTALPRICE];
                my $price       = $total_price / $units if ( $units > 0 );
                my $ppdRate     = ( defined $retail && $retail != 0 ) ? ( $price / $retail ) : '';

                if ( $units && $price && $artist && $track ) {
                    my $sale = RPS::Import::SaleRec->new();

                    $sale->productType($prodtype);
                    $sale->formatType($format);
                    $sale->dateBegin($dateBegin);
                    $sale->dateEnd($dateEnd);
                    $sale->serviceProductID($vendorID);
                    $sale->isrc($isrc);
                    $sale->artistName($artist);
                    $sale->trackName($track);
                    $sale->units($units);
                    $sale->price($price);
                    $sale->retail($retail)         if ( $self->{clientID} == 21 );
                    $sale->priceType($priceType)   if ( $self->{clientID} == 21 );
                    $sale->wholesaleRate($ppdRate) if ( $self->{clientID} == 21 );
                    $sale->serviceID($service_id)  if ( $self->{clientID} == 21 );
                    $sale->lineNum($linenum);

                    $self->_insertSale( sale => $sale );
                }

                #else { print STDERR "skipping u: $units , p: $price , a: $artist , t: $track\n"; }
            }
            $linenum++;
        }    # foreach line
        $retval += $linenum;
        $sheet_num++;
    }    # foreach sheet

    return $retval;
}

#
# Private methods
#

sub matchHeader {
    my $line = shift;

    # verify the column headings are as we expect them
    my $artist = $line->[FIELD_ARTIST];
    my $track  = $line->[FIELD_TITLE];
    my $units  = $line->[FIELD_UNITS];
    my $price  = $line->[FIELD_ISRC];

    if (   $artist =~ /author/i
        && $track =~ /product/i
        && $units =~ /units/i
        && $price =~ /isrc/i ) {
        return 1;
    }
    return 0;
}

###
1;    # Play nicely.
###