package RPS::Sale::File::EMI::YouTubeMonthly;

use strict;
use Data::Dumper;

#use warnings;

use Devel::Peek;
use Encode;

use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;

use Text::CSV_XS;
use Date::Calc qw(Add_Delta_YMD Add_Delta_Days);

# built in perl func
use File::Copy;

use lib '/app/tools/sale_import/lib';
use lib '/app/tools/rps/lib';
use RPS::Import::Service;

use lib '/app/tools/common/lib';
use Common::Util;
use Common::Log;
use Common::File::UTF16;
use Common::File::UTF8;
use Common::File::Latin1;
use Common::UTF8;
use Spreadsheet::XLSX;

use Common::File::Tied::Parsed;

use lib '/app/tools/data_classes/lib';
use RPS::File::File;
use Client::Service;
use Client::Client;

use base 'RPS::Sale::File';

sub _getSheetIterators {
    my ( $self, $path ) = @_;

    my $sheets;
    my $sheetNames;

    my $fileExtension;
    if ( $path =~ m/(\.[a-z]+)$/ ) {
        $fileExtension = $1;
    }

    if ( -B $path && $fileExtension =~ /.xlsx?/ ) {
        die "I have no intention of supporting Excel files at this time...";

        # !!! But if I did, it would look something like this...
        #
        Common::Log::Print("ParseFull: reading excel file");

        my $sheet_data = $self->_read_excel_file() || return undef;

        $sheets     = $sheet_data->[0];
        $sheetNames = $sheet_data->[1];

        unless ( ref( $sheets->[0] ) eq 'ARRAY' ) {
            $self->errstr('Failed to read excel file');
            return undef;
        }
    } else {
        $sheets = [];
        my $file;
        if ( Common::File::UTF8->IsValid($path) ) {
            $file = Common::File::UTF8->new($path);
        } elsif ( Common::File::UTF16->IsValid($path) ) {
            $file = Common::File::UTF16->new($path);
        } elsif ( ( -T $path && $fileExtension ne '.pdf' ) || $fileExtension eq '.csv' ) {

            # !!! Note that I'm not sure we can rely on the stuff in a 'latin1' file to
            # !!! actually _be_ latin1.    We may have to inline calls to Common::UTF8::Encode in that class.
            #
            $file = Common::File::Latin1->new($path);
        } else {
            $self->errstr('File not identified');
            return;
        }

        $sheets->[0] = $self->_rowArrayRefFromFile($file);
    }

    return ( $sheets, $sheetNames );
}

sub _rowArrayRefFromFile {
    my ( $self, $file ) = @_;

    # So, I have a file object.  That's good!
    # I'll need to wrap a class around it that provides the right interface.
    #

    my @fileLines;
    tie @fileLines, 'Common::File::Tied::Parsed', $file;

    return \@fileLines;
}

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

    $self->_init();

    my $client_id = $args{client_id};
    my $file_obj  = $args{file};

    if ( !$client_id ) {
        $self->errstr("Client ID not specified");
        return undef;
    }

    if ( !$file_obj ) {
        $self->errstr("File object not provided");
        return undef;
    }

    my $path_to_import_file = $args{file_path} || $file_obj->FileDir . "/" . $file_obj->FileName;
    $self->{path} = $path_to_import_file;

    if ( !-e $path_to_import_file ) {
        $self->errstr("File $path_to_import_file does not exist");
        return undef;
    }

    Common::Log::Print("ParseFull: reading file: $path_to_import_file");

    # JPK - Going to change the model here.
    # What we're going to do is identify the file, then instantiate
    # a 'Row Iterator' object for each 'sheet'.  For now, since we're just
    # working on text files, there will be only one sheet.
    #
    # In order to support all the legacy importers, this iterator will support
    # a tied array reference interface.    I'll figure out the details later...
    #
    # It's pretty dumb that we need to support both this 'sheets_aref' and 'aref_lines_array'.
    # The new factory method that returns the row iterators will return essentially the sheets_aref.
    #
    my ( $sheets_aref, $sheetnames_aref ) = $self->_getSheetIterators($path_to_import_file);
    my $aref_lines_array = $sheets_aref->[0];

    Common::Log::Print("ParseFull: determining service/version");
    if ( $args{service_id} && $args{version} ) {
        print STDERR "ParseFull: forcing to service $args{service_id} : $args{version}\n";
        $self->{service_id}  = $args{service_id};
        $self->{version_num} = $args{version};
    } elsif ( $self->PreParse( $sheets_aref, $file_obj->OrigFileName ) ) {
        print STDERR "ParseFull: PreParse determined file service is $self->{service_id}, version $self->{version_num}\n";
    } else {
        $self->errstr('File not identified');
        return undef;
    }

    Client::Service::AddService( client_id => $client_id, service_id => $self->{service_id} );
    $file_obj->ServiceID( $self->{service_id} );
    $file_obj->VersionNum( $self->{version_num} );
    $file_obj->TypeID( $self->{type_id} );

    ## indicate if it's a physical sales file
    ## clean this later... maybe some flag in the Client::Service
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_NAVARRE );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_FAITHWORKS );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_ECHO );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_DTTHIRDPARTY );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_DTARTISTDIRECT );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_DTINTERNATIONAL );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_BMG );

    #$file_obj->Physical(1) if ($self->{service_id} == Client::Service::DSP_RYKODISC);
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_DOCK );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_FUSION );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_ROOTSY );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_ROUGHTRADEDIST );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_RED );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_KOCHENTCANADA );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_HARMONIAMUNDI );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_FONTANA );

    #$file_obj->Physical(1) if ($self->{service_id} == Client::Service::DSP_PROPERUK);
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_KOCH_PHYSICAL );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_LOOKOUTDIRECT );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_LMMGSALES );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_PIAS_PHYSICAL );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_BMG_COLUMBIAHOUSE );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_SANCT_HISTORICAL );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_ADAPHYS );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_BMG_SONY );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_SHELLSHOCK );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_CAROLINE );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_DOWNLOADCENTRIC );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_WELK );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_OUTSIDEMUSIC );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_SDROUTSIDESALES );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_TOUCHNGOPHYS );
    $file_obj->Physical(1) if ( $self->{service_id} == Client::Service::DSP_NAXOS_PHYSICAL );

    $file_obj->Save();
    sleep(4);

    print STDERR "calling importer...\n";

    my %constructionArgs = (
        service_id  => $file_obj->ServiceID,
        version_num => $file_obj->VersionNum,
    );
    if ( $args{sale_object_class} ) {
        $constructionArgs{sale_object_class} = $args{sale_object_class};
    }

    my $importer = RPS::Import::Service->new(%constructionArgs);

    if ( $importer && $importer->physical ) {
        $file_obj->Physical(1);
        $file_obj->Save();
    }

    # if we parsed an excel file then
    # we will have an arrayref or arrayrefs (ie. sheets)
    # so pass those as a 'sheets' param in leui of a 'lines' param

    my %importArgs = (
        client_id   => $client_id,
        file        => $file_obj,
        lines       => $aref_lines_array,
        sheets      => $sheets_aref,
        sheet_names => $sheetnames_aref,
    );

    my $result = $importer->Import(%importArgs);

    $self->errstr( $importer->errstr() );
    return $result;
}

sub PreParse {
    my $self      = shift;
    my $sheets    = shift;
    my $file_name = shift;

    $self->_init();

    my $rules = $self->_file_match_rules();

    my $service_id_found  = 0;
    my $version_num_found = 0;
  RULE:
    foreach my $rule (@$rules) {
        my $service_id  = $rule->{service};
        my $version_num = $rule->{version};
        my $row_limit   = $rule->{limit} || 80;

        Common::Log::Debug("======== SERVICE: $service_id, VERSION: $version_num ===========================");

        if ( $rule->{file_name} ) {
            my $fname_match = $rule->{file_name};
            if ( $file_name !~ m/$fname_match/i ) {
                next RULE;
            }
        }

        # default to the first sheet
        my $data_sheet_index = $rule->{sheet} || 0;
        my $i = 0;
      SHEET:
        foreach my $sheet (@$sheets) {
            if ( ref($sheet) ne 'ARRAY' or ( $data_sheet_index ne 'any' and $data_sheet_index != $i ) ) {
                $i++;
                next SHEET;
            }

            my $data = $rule->{lines};

            if ( $rule->{match_on_any_row} ) {

                # look for header match on any of the first $row_limit rows
                my $limit = scalar @$sheet;
                $limit = $row_limit if ( $limit > $row_limit );

              OFFSET:
                for ( my $offset = 0 ; $offset < $limit ; $offset++ ) {
                    for ( my $row = 0 ; $row < scalar @$data ; $row++ ) {
                        for ( my $col = 0 ; $col < scalar @{ $data->[$row] } ; $col++ ) {
                            Common::Log::Debug(
                                "HEADER: row(" . ( $row + $offset ) . ") col($col) = '" . $sheet->[ $row + $offset ][$col] . "'" );
                            if ( defined $data->[$row][$col] ) {
                                Common::Log::Debug("HEADER: ($sheet->[$row+$offset][$col]), DATA: ($data->[$row][$col])");
                                if ( $sheet->[ $row + $offset ][$col] =~ m/$data->[$row][$col]/i ) {
                                    $service_id_found  = $service_id;
                                    $version_num_found = $version_num;
                                } else {
                                    $service_id_found  = 0;
                                    $version_num_found = 0;
                                    next OFFSET;
                                }
                            }
                        }
                    }
                    last OFFSET if ($service_id_found);
                }
            } else {
                for ( my $row = 0 ; $row < scalar @$data ; $row++ ) {
                    for ( my $col = 0 ; $col < scalar @{ $data->[$row] } ; $col++ ) {
                        Common::Log::Debug( "HEADER: row($row) col($col) = '" . $sheet->[$row][$col] . "'" );
                        if ( defined $data->[$row][$col] ) {
                            Common::Log::Debug("HEADER: ($sheet->[$row][$col]), DATA: ($data->[$row][$col])");
                            if ( $sheet->[$row][$col] =~ m/$data->[$row][$col]/i ) {
                                $service_id_found  = $service_id;
                                $version_num_found = $version_num;
                            } else {
                                $service_id_found  = 0;
                                $version_num_found = 0;
                                $i++;
                                next SHEET;
                            }
                        }
                    }
                }
            }

            if ($service_id_found) {
                $self->{service_id}  = $service_id_found;
                $self->{version_num} = $version_num_found;
                return $service_id_found;
            }
            $i++;
        }
    }

    $self->errstr("Unrecognized file format");
    return undef;
}

sub _read_utf16_file {
    my $self      = shift;
    my %args      = @_;
    my $utf16File = $args{file};
    my $lines     = $utf16File->read();

    $self->{delimiter} = Common::Util::GetDelimiter( join( "\n", @$lines ) );

    if ( $self->{delimiter} ) {
        return $self->_read_text_file( %args, lines => $lines );
    } else {
        $self->errstr('Can not determine delimeter');
        return ();
    }
}

# JPK - All this knowledge concerning how to parse individual lines will end up abstracted
# away into the 'Common::File::Tied::LineParser' class.
#
sub _read_text_file {
    my $self = shift;
    my %args = @_;

    $self->{file_type} = 't';

    my $csv = Text::CSV_XS->new( { binary => 1 } );    # actually means to allow characters outside of non-ASCII range
    $csv = Text::CSV_XS->new( { binary => 1, escape_char => "\\" } ) if ( $args{clean_quotes} == 1 && $self->{possible_quote} == 1 );
    my $alternateCSV = Text::CSV_XS->new( { allow_loose_quotes => 1, escape_char => '' } );

    my $semicolonCSV = Text::CSV_XS->new( { binary => 1, sep_char => ';' } );
    $semicolonCSV = Text::CSV_XS->new( { binary => 1, sep_char => ';', escape_char => "\\" } )
      if ( $args{clean_quotes} == 1 && $self->{possible_quote} == 1 );
    my $alternateSemicolonCSV = Text::CSV_XS->new( { allow_loose_quotes => 1, escape_char => '', sep_char => ';' } );

    my @header;

    if ( $self->{delimiter} ) {
        my $lines = $args{lines};
        my $limit = $args{limit} || scalar @$lines;

        my $lineNum = 1;

        for ( my $i = 0 ; $i < $limit ; $i++ ) {
            my $line = $lines->[$i];

            # Make sure the incoming text is properly encoded as UTF8.
            # JPK - In theory, our UTF8::Encode method will work on just about
            # any string we give it.  At least it should work on strings that
            # are UTF8 already (but are not properly flagged), or latin1.
            #
            # !!! In theory, if we set the incoming binmode properly, we do NOT have to do this.
            #     So, if the Common::File class gets it right, this becomes unnecessary.
            #
            $lines->[$i] = Common::UTF8::Encode($line);

            # There shouldn't be a trailing CR on a line, but for some
            # UTF8 files this is not the case. Remove the trailing CR
            # (if present) to prevent $cvs->parse from failing.
            # See Case 13763 for more information.

            # The 'if' here is somewhat pointless
            #
            #            $lines->[$i] =~ s/\x0d$// if ( $line =~ m/\x0d$/ );
            $lines->[$i] =~ s/\x0d$//;

            # Replace any non-tab (0x09) control characters with '?'
            #
            $lines->[$i] =~
s/(\x00|\x01|\x02|\x03|\x04|\x05|\x06|\x07|\x08|\x0a|\x0b|\x0c|\x0d|\x0e|\x0f|\x10|\x11|\x12|\x13|\x14|\x15|\x16|\x17|\x18|\x19|\x1a|\x1b|\x1c|\x1d|\x1e|\x1f)/\?/g;

            my @row;
            if ( $self->{delimiter} eq ',' ) {

                # The "Faithworks" dualtone sales files have some wierd format where
                # some fields have ="data" format even though their csv files that
                # are not '"' qualified. The crude hack below cleans this up.
                $lines->[$i] =~ s/="/"/g;

                # some files have a single " which confuses the parse method
                my @commaCount = $lines->[$i] =~ m/[^\\]"/g;

                #$lines->[$i] =~ s/"// if (scalar @commaCount == 1);

                # a rather gross thing we have to do for CSV files with spaces around the commas.
                # the big drawback is that it get's rid of spaces within quoted strings.
                # we could deal with the quoted fields, but then i forget the point of using
                # the Text::CSV_XS library...
                $lines->[$i] =~ s/\s*,\s*/,/g;

                my $test_quote = $lines->[$i];
                $test_quote =~ s/\",\"//;
                $test_quote =~ s/^\"//;
                $test_quote =~ s/\"$//;
                $self->{possible_quote} = 1 if ( $test_quote =~ /\"/ && !$args{clean_quotes} );

                if ( $args{clean_quotes} == 1 && $self->{possible_quote} == 1 ) {
                    if ( $lines->[$i] !~ /\"/ ) {
                        $lines->[$i] =~ s/,/\t/g;
                    } else {
                        if ( $lines->[$i] =~ /^\"/ && $lines->[$i] =~ /\"$/ && $lines->[$i] =~ /\",\"/ ) {
                            $lines->[$i] =~ s/^\"//;
                            $lines->[$i] =~ s/\"$//;
                            $lines->[$i] =~ s/\",\"/\t/g;
                            $lines->[$i] =~ s/\"/\\\"/g;
                        } else {
                            $lines->[$i] =~ s/\'/\\\'/g;
                            my $subSwitch   = 0;
                            my $workingLine = $lines->[$i];
                            my @comaSubs;
                            my $comaSubsCount = 0;
                            while ( $subSwitch < 1 ) {
                                $workingLine =~ m/(\"[^\"]*\")/;
                                $comaSubs[$comaSubsCount] = $1;
                                my $subField = "ddd" . $comaSubsCount . "bbb";
                                $workingLine =~ s/(\"[^\"]*\")/$subField/;
                                $comaSubs[$comaSubsCount] =~ s/^\"//;
                                $comaSubs[$comaSubsCount] =~ s/\"$//;
                                if ( $workingLine !~ /\"[^\"]*\"/ ) {
                                    $workingLine =~ s/,/\t/g;
                                    $subSwitch = 1;
                                }
                                $comaSubsCount++;
                            }
                            for ( my $x = 0 ; $x < @comaSubs ; $x++ ) {
                                my $subField  = "ddd" . $x . "bbb";
                                my $realField = $comaSubs[$x];
                                $workingLine =~ s/$subField/$realField/;
                            }
                            $lines->[$i] = $workingLine;
                        }
                    }
                    if ( $csv->parse( $lines->[$i] ) ) {
                        @row = split( "\t", $lines->[$i] );
                    } elsif ( $alternateCSV->parse( $lines->[$i] ) ) {
                        @row = split( "\t", $lines->[$i] );
                    } else {
                        $self->errstr("Double quotes must be escaped at line $lineNum");
                        return undef;
                    }
                } else {
                    if ( $csv->parse( $lines->[$i] ) ) {
                        @row = Common::Util::trimquotes( $self->_cleanup( $csv->fields() ) );
                    } elsif ( $alternateCSV->parse( $lines->[$i] ) ) {
                        @row = Common::Util::trimquotes( $self->_cleanup( $alternateCSV->fields() ) );
                    } else {
                        $self->errstr("error parsing file at line $lineNum");
                        return undef;
                    }
                }
            } elsif ( $self->{delimiter} eq ';' ) {

                my $test_quote = $lines->[$i];
                $test_quote =~ s/\";\"//;
                $test_quote =~ s/^\"//;
                $test_quote =~ s/\"$//;
                $self->{possible_quote} = 1 if ( $test_quote =~ /\"/ && !$args{clean_quotes} );

                if ( $args{clean_quotes} == 1 && $self->{possible_quote} == 1 ) {
                    if ( $lines->[$i] !~ /\"/ ) {
                        $lines->[$i] =~ s/;/\t/g;
                    } else {
                        if ( $lines->[$i] =~ /^\"/ && $lines->[$i] =~ /\"$/ && $lines->[$i] =~ /\";\"/ ) {
                            $lines->[$i] =~ s/^\"//;
                            $lines->[$i] =~ s/\"$//;
                            $lines->[$i] =~ s/\";\"/\t/g;
                            $lines->[$i] =~ s/\"/\\\"/g;
                        } else {
                            $lines->[$i] =~ s/\'/\\\'/g;
                            my $subSwitch   = 0;
                            my $workingLine = $lines->[$i];
                            my @comaSubs;
                            my $comaSubsCount = 0;
                            while ( $subSwitch < 1 ) {
                                $workingLine =~ m/(\"[^\"]*\")/;
                                $comaSubs[$comaSubsCount] = $1;
                                my $subField = "ddd" . $comaSubsCount . "bbb";
                                $workingLine =~ s/(\"[^\"]*\")/$subField/;
                                $comaSubs[$comaSubsCount] =~ s/^\"//;
                                $comaSubs[$comaSubsCount] =~ s/\"$//;
                                if ( $workingLine !~ /\"[^\"]*\"/ ) {
                                    $workingLine =~ s/;/\t/g;
                                    $subSwitch = 1;
                                }
                                $comaSubsCount++;
                            }
                            for ( my $x = 0 ; $x < @comaSubs ; $x++ ) {
                                my $subField  = "ddd" . $x . "bbb";
                                my $realField = $comaSubs[$x];
                                $workingLine =~ s/$subField/$realField/;
                            }
                            $lines->[$i] = $workingLine;
                        }
                    }
                    if ( $semicolonCSV->parse( $lines->[$i] ) ) {
                        @row = split( "\t", $lines->[$i] );
                    } elsif ( $alternateSemicolonCSV->parse( $lines->[$i] ) ) {
                        @row = split( "\t", $lines->[$i] );
                    } else {
                        $self->errstr("Double quotes must be escaped at line $lineNum");
                        return undef;
                    }
                } else {
                    if ( $semicolonCSV->parse( $lines->[$i] ) ) {
                        @row = Common::Util::trimquotes( $self->_cleanup( $semicolonCSV->fields() ) );
                    } elsif ( $alternateSemicolonCSV->parse( $lines->[$i] ) ) {
                        @row = Common::Util::trimquotes( $self->_cleanup( $alternateSemicolonCSV->fields() ) );
                    } else {
                        $self->errstr("error parsing file at line $lineNum");
                        return undef;
                    }
                }
            } else {
                @row = Common::Util::trimquotes( $self->_cleanup( split( $self->{delimiter}, $lines->[$i] ) ) );
            }

            $lineNum++;

            push @header, \@row;
        }

        if ( $args{clean_quotes} == 1 && $self->{possible_quote} == 1 ) {
            $self->{possible_quote} = 0;
            $args{clean_quotes} = 0;
        }
    }

    return \@header;
}

1;