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;