package RPS::Import::Moderati;

use strict;

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

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

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

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

use constant DATE_ROW => 3;
use constant DATE_COL => 3;

my %field_map = (
    1 => {
        'vendorid' => 4,
        'track'    => 5,
        'artist'   => 6,
        'isrc'     => 8,
        'units'    => 13,
        'price'    => 21,
    },
    2 => {
        'vendorid' => 4,
        'track'    => 5,
        'artist'   => 6,
        'isrc'     => 9,
        'units'    => 15,
        'price'    => 25,
    },
    3 => {
        'service'  => 0,
        'vendorid' => 4,
        'track'    => 5,
        'artist'   => 6,
        'format'   => 7,
        'isrc'     => 9,
        'upc'      => 10,
        'units'    => 15,
        'retail'   => 16,
        'price'    => 23,
    },
    4 => {
        'service'  => 0,
        'vendorid' => 3,
        'track'    => 4,
        'artist'   => 5,
        'units'    => 8,
        'retail'   => 13,
        'price'    => 22,
    },
    5 => {
        service  => 1,
        vendorid => 5,
        track    => 6,
        artist   => 7,
        isrc     => 10,
        units    => 16,
        price    => 24,
    },
    6 => {
        service  => 0,
        vendorid => 4,
        track    => 5,
        artist   => 6,
        format   => 7,
        isrc     => 9,
        upc      => 10,
        units    => 11,
        retail   => 16,
        price    => 23,
    },
    7 => {
        service   => 0,
        vendor_id => 4,
        track     => 5,
        artist    => 6,
        units     => 10,
        retail    => 13,
        price     => 22,
    },
    8 => {
        service  => 0,
        vendorid => 4,
        track    => 5,
        artist   => 6,
        format   => 7,
        isrc     => 10,
        upc      => 11,
        units    => 12,
        retail   => 17,
        price    => 24,
    },
);

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

    my $lines    = $args{lines};
    my $fileObj  = $args{file};
    my $version  = $self->_version();
    my $fileName = $fileObj->OrigFileName;

    #my $fileName = $args{OrigFileName}; # for cmd-line testing
    return undef unless ($lines);

    my $offsets = $field_map{$version};
    my ( $dateBegin, $dateEnd );

    if ( $version == 5 || $version == 8 ) {
        ( $dateBegin, $dateEnd ) = $self->_getDates( $lines->[3]->[4] );
    } else {
        ( $dateBegin, $dateEnd ) = $self->_getDates($fileName) if ( $version != 5 );
    }

    unless ( $dateBegin && $dateEnd ) {
        $self->errstr("couldn't get dates");
        print STDERR "date cell: " . $lines->[DATE_ROW]->[DATE_COL] . "\n";
        return 0;
    }

    $self->_setDefaultFormat($fileName);
    my $billToID = 0;    # only used for WMG

    if ( $self->{clientID} == RPS::Import::Importer::WMG )    # warner
    {
        # check for warner specific stuff
        require RPS::Import::WMG::Util;

        my @billTo = RPS::Import::WMG::Util::getWirelessBillto( $self->{fileServiceID} );
        unless ( $billTo[0] ) {
            $self->warning();
            print STDERR "unknown billto mapping: $self->{fileServiceID}\n";
        }
        $billToID = $billTo[0];

        $fileObj->Atomic(1);
        $fileObj->Save();
    }

    my $linenum = 1;
    my %errors;

    foreach my $line (@$lines) {
        my $vendor_id = $line->[ $offsets->{vendorid} ];
        my $artist    = $line->[ $offsets->{artist} ];
        $artist =~ s/\s*\(Warner Music.*?\)//;    # useless decoration
        my $track = $line->[ $offsets->{track} ];
        $track =~ s/\s*-\s*2X$//;                 # useless decoration
        my $isrc = defined $offsets->{isrc} ? normalize_isrc( $line->[ $offsets->{isrc} ] ) : '';
        my $upc  = defined $offsets->{upc}  ? normalize_upc( $line->[ $offsets->{upc} ] )   : '';
        my ($units) = $line->[ $offsets->{units} ] =~ m/^(\-?\d+)$/;
        my $total_price = $self->numeric( $line->[ $offsets->{price} ] );

        if ( $units && $total_price && $artist && $track ) {

            my ( $formatType, $formatName, $serviceID, $retail );
            if ( $version =~ /^3|4|5|6|7|8$/ ) {
                ( $formatType, $formatName ) = $self->_getFormat( $line->[ $offsets->{format} ] );
                my $service = $self->_cleanupServiceName( $line->[ $offsets->{service} ] );
                substr( $formatName, 0, 0 ) = 'brew ' if ( $service =~ m/brew/i );
                $retail = $self->numeric( $line->[ $offsets->{retail} ] );

                unless ( $serviceID = $self->getServiceID($service) || $errors{$service} ) {
                    $self->errstr( 'unknown service (' . $linenum . ') ' . $service );
                    print STDERR "unknown service $service\n";
                    $errors{$service} = 1;
                }
            } else {
                ( $formatType, $formatName ) = $self->_getFormat($track);
            }

            my $price = $total_price / $units;
            my $ppdRate = $retail ? $price / $retail : undef;
            my $priceType;

            if ( $self->{clientID} == RPS::Import::Importer::WMG && $serviceID )    # warner
            {
                unless ( RPS::Import::WMG::Util::getWirelessSellto( $serviceID, $billToID ) || $errors{$serviceID} ) {
                    $self->warning();
                    print STDERR "unknown sellto mapping: $serviceID ($billToID)\n";
                    $errors{$serviceID} = 1;
                }

                unless ( $priceType = RPS::Import::WMG::Util::getWirelessPriceType($formatName) ) {
                    unless ( $errors{$formatName} ) {
                        $self->warning();
                        print STDERR "unknown format mapping: $formatName\n";
                        $errors{$formatName} = 1;
                    }
                }
            }

            my $sale = RPS::Import::SaleRec->new();

            $sale->serviceID($serviceID);
            $sale->productType(RPS::File::Sale::TYPE_TRACK);
            $sale->formatType($formatType);
            $sale->dateBegin($dateBegin);
            $sale->dateEnd($dateEnd);
            $sale->serviceProductID($vendor_id);
            $sale->upc($upc);
            $sale->isrc($isrc);
            $sale->artistName($artist);
            $sale->trackName($track);
            $sale->units($units);
            $sale->price($price);
            $sale->wholesaleRate($ppdRate);
            $sale->retail($retail);
            $sale->lineNum($linenum);
            $sale->priceType($priceType);
            $sale->countryCode('US');

            #$args{dumper}($sale);  ## for cmd-line testing
            $self->_insertSale( sale => $sale );
        }    #else { print STDERR "skip: u: $units p: $total_price a: $artist t: $track ($linenum)\n"; }
        $linenum++;
    }

    return $linenum;
}

#
# Private methods
#

sub _getDates {
    my $self = shift;
    my $date = shift;
    $date =~ s/\s//g;

    if ( $date =~ /(\d\d\d\d)\D+(\d+)\D+(\d+)\D+(\d\d\d\d)\D+(\d+)\D+(\d+)/ ) {    # YYYY-MM-DD to YYYY-MM-DD
        my $dateBegin = $1 . "-" . sprintf( "%02d", $2 ) . "-" . sprintf( "%02d", $3 );
        my $dateEnd   = $4 . "-" . sprintf( "%02d", $5 ) . "-" . sprintf( "%02d", $6 );
        return ( $dateBegin, $dateEnd );
    } elsif ( $date =~ /Q(\d)(\d{2})/ ) {
        my $year = 2000 + $2;
        my $qtr  = $1;
        return $self->_quarterDates( $qtr, $year );
    } elsif ( $date =~ /(\d{4})Q(\d)/ ) {
        my $year = $1;
        my $qtr  = $2;
        return $self->_quarterDates( $qtr, $year );
    } elsif ( $date =~ /(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)/ ) {
        $date =~ m/(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)\D+(\d+)/;
        my $dateBegin = "20" . $3 . "-" . sprintf( "%02d", $1 ) . "-" . sprintf( "%02d", $2 );
        my $dateEnd   = "20" . $6 . "-" . sprintf( "%02d", $4 ) . "-" . sprintf( "%02d", $5 );
        return ( $dateBegin, $dateEnd );
    }
}

sub _quarterDates {
    my $self = shift;
    my $qtr  = shift;
    my $year = shift;

    my ( $dateBegin, $dateEnd );
    if ( $qtr == 1 ) {
        $dateBegin = $year . "-01-01";
        $dateEnd = $year . "-03-" . Days_in_Month( $year, 3 );
    } elsif ( $qtr == 2 ) {
        $dateBegin = $year . "-04-01";
        $dateEnd = $year . "-06-" . Days_in_Month( $year, 6 );
    } elsif ( $qtr == 3 ) {
        $dateBegin = $year . "-07-01";
        $dateEnd = $year . "-09-" . Days_in_Month( $year, 9 );
    } elsif ( $qtr == 4 ) {
        $dateBegin = $year . "-10-01";
        $dateEnd = $year . "-12-" . Days_in_Month( $year, 12 );
    } else {
        return undef;
    }
    return ( $dateBegin, $dateEnd );
}

sub _setDefaultFormat {
    my $self     = shift;
    my $fileName = lc $_[0];

    # Due to format mapping and retiring in FB1324, the default format type is now ringtone
    #
    ( $self->{defaultFormatType}, $self->{defaultFormatName} ) =
      ( RPS::File::Sale::FORMAT_RINGTONE, 'ringtone' );
}

sub _getFormat {
    my $self  = shift;
    my $track = lc $_[0];

    return $track =~ m/\(\s*voice\s*ring/ ? ( RPS::File::Sale::FORMAT_RINGTONE, 'voiceringer' ) :    # was FORMAT_VOICERINGER (FB1324)
      ( $self->{defaultFormatType}, $self->{defaultFormatName} );
}

sub _cleanupServiceName {
    my $self = shift;
    my $svc  = lc $_[0];
    if ( $svc =~ /modtones/ ) {
        $svc =~ s/www\.modtones\.com\s*-\s*//;
        $svc =~ s/\s*-\s*modtones.*$//;
        $svc = "att";
    } elsif ( $svc =~ /at&t/ ) {
        $svc =~ s/at\&t.*$/at\&t/;
        $svc = "att";
    }
    $svc =~ s/'d/d/;    # amp'd

    if    ( $svc =~ s/^u\.s\.\s*/us / )        { }
    elsif ( $svc =~ s/mobile\s*espn/espn/ )    { }
    elsif ( $svc =~ s/\s\(cingular// )         { }
    elsif ( $svc =~ s/\dmy.*$// )              { }
    elsif ( $svc =~ s/\svzw.*$// )             { }
    elsif ( $svc =~ s/at\&t.*$/att/ )          { }
    elsif ( $svc =~ s/at&t\s+mmode/mmode/ )    { }
    elsif ( $svc =~ s/\s*-\s*.*?brand$// )     { }
    elsif ( $svc =~ s/\s*wallpaper// )         { }
    elsif ( $svc =~ s/^.+on-deck\W+(\w+)/$1/ ) { }
    elsif ( $svc =~ s/\W+on-deck$// )          { }
    elsif ( $svc =~ s/_dj$// )                 { }
    elsif ( $svc =~ s/\sringbacks//i )         { }

    return $svc;
}

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