package Metadata::Parser::Excel;
use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw(ExcelFmt);

use lib '/app/tools/common/lib';
use Common::Log;
use Common::RSApp;
use Common::Assert;
use Common::UTF8;
use Data::Dumper;

use Unicode::String;
use Unicode::Map8;

use base qw( Metadata::Parser );

use constant DATE_FORMAT => 'yyyy-mm-dd';

# Excel automagically translates some characters with autocorrect.  This map will
# Translate them back.
use constant CHARACTER_MAP => (
    0x00 => '',
    0xA0 => '',
    0x13 => "-",
    0x18 => "'",
    0x19 => "'",
    0x1A => '...',
    0x1C => '"',
    0x1D => '"',
);

use constant UCS2_CHARACTER_MAP => (
    0x00A0 => '',
    0x2013 => 0x2D,    # -
    0x2018 => 0x27,    # '
    0x2019 => 0x27,    # '
    0x201C => 0x22,    # "
    0x201D => 0x22,    # "
    0x2026 => 0x1A,    # ...
);

sub _init {
    my ( $self, %args ) = @_;

    $self->SUPER::_init(%args);

    assert( $self->{_infile} );

    Common::Log::Debug("   Initializing Metadata::Parser::Excel Object");

    return $self;
}

sub open {
    my ( $class, %args ) = @_;
    my $self = bless {}, $class;
    $self->_init(%args);

    Common::Log::Debug("Opening Excel Spreadsheet: $self->{_infile}");

    eval {
        $self->{_excel} = Spreadsheet::ParseExcel::Workbook->Parse( $self->{_fh} )
          unless ( $self->{_excel} );
        my $sheet = $self->{_excel}->{Worksheet}[0];
        $sheet->{MaxRow} ||= $sheet->{MinRow} + 1;
        $self->{_row_count} = $sheet->{MaxRow};
    };
    if ($@) {
        Common::Log::Print("Failed to parse Excel file: $@");
        return undef;
    }

    return $self;
}

sub parse {
    my $self = shift;
    assert($self);

    $self->{_errors} = ();
    $self->{_data}   = undef;

    Common::Log::Debug("Parsing Excel Spreadsheet: $self->{_infile}");

    # Ensure the workbook has at least one worksheet.  If it doesn't, then
    # there is no reason to continute with any other tests.
    return undef unless ( $self->_validate_sheet() );

    if ( $self->_validate_headers() ) {
        if ( $self->_import_data() ) {
            $self->_store_import_summary();
            $self->_store_import();
        }
    }
    return $self->{_errors} ? undef : $self->{_data};
}

sub _validate_sheet {
    my $self  = shift;
    my $sheet = $self->{_excel}->{Worksheet}[0];

    Common::Log::Debug("- Validate excel workbook has worksheets");

    unless ( defined $sheet ) {
        push( @{ $self->{_errors} }, "Workbook contains no worksheets" );
        return undef;
    }

    return 1;
}

sub _validate_headers {
    my $self = shift;
    my ( $sheet, $row, $col, $cell );
    my @cols;

    assert($self);
    assert( $self->{_columns} );

    # Grab the first row of the first sheet.
    $sheet = $self->{_excel}->{Worksheet}[0];
    $row   = $sheet->{MinRow};

    $sheet->{MaxCol} ||= $sheet->{MinCol};

    # Validate each column in the spreadsheet is valid
    foreach $col ( $sheet->{MinCol} .. $sheet->{MaxCol} ) {
        $cell = $sheet->{Cells}[$row][$col];
        push( @cols, $cell->{Val} );
    }

    return $self->_validate_header_row(@cols);
}

sub _import_data {
    my $self = shift;
    assert($self);

    my ( $sheet, $row, $col, $cell );
    my %column_indexes;
    my $data = ();
    my $record;
    my $upc;
    my @rowdata;
    my $catno_index        = 0;
    my $release_date_index = 0;
    my $celldata;

    $self->{_data} = undef;

    Common::Log::Debug("- Begin data conversion from excel");
    $sheet = $self->{_excel}->{Worksheet}[0];

    Common::Log::Debug("   - Get column indexes");
    $row = $sheet->{MinRow};

    $sheet->{MaxCol} ||= $sheet->{MinCol};
    foreach $col ( $sheet->{MinCol} .. $sheet->{MaxCol} ) {
        $cell = $sheet->{Cells}[$row][$col];

        $celldata = $self->_read_cell_value($cell);

        # Trim spaces
        $celldata = lc($celldata);

        $column_indexes{$col} = $celldata;

        $release_date_index = $col
          if ( $celldata && $celldata =~ /release-date/i );

        $catno_index = $col
          if ( $celldata && $celldata =~ /catalog-no/i );
    }

    Common::Log::Debug("   - Reading row data");
    $sheet->{MaxRow} ||= $sheet->{MinRow} + 1;
    foreach $row ( $sheet->{MinRow} + 1 .. $sheet->{MaxRow} ) {
        $sheet->{MaxCol} ||= $sheet->{MinCol};

        next if ( $self->_is_empty_row( $sheet, $row, $catno_index ) );

        $record = {};
        foreach $col ( $sheet->{MinCol} .. $sheet->{MaxCol} ) {
            $cell     = $sheet->{Cells}[$row][$col];
            $celldata = undef;

            # If we are pulling from the release-date field then try and format data
            # if( $cell && $cell->{Type} ne 'Date' ) {
            if ( $release_date_index && $col == $release_date_index ) {

                $celldata = $cell->Value() if ($cell);

                if ( !defined($celldata) || $celldata !~ /^\d{4}-\d{2}-\d{2}$/ ) {

                    # Try and pull the raw data then format it to our date format
                    $celldata = ExcelFmt( DATE_FORMAT, $cell->{Val} );
                }

                elsif ( !defined($celldata) || length($celldata) == 0 ) {

                    # If ExcelFmt failed then just store the raw value
                    $celldata = $cell->{Val};
                }

            } elsif ($cell) {

                # Grab the formatted value
                #$celldata = $cell->Value();

                #Grab the unformatted value;
                $celldata = $self->_read_cell_value($cell);
            }

            # Don't store 0 length strings
            $celldata = undef
              if ( defined($celldata) && length($celldata) == 0 );

            $record->{ $column_indexes{$col} } = $celldata
              if ( $column_indexes{$col} );

            # Check to see if there are any unknown Unicode conversions by looking for
            # the default character, 0x1B
            if ( $celldata =~ /\x1B/ ) {
                push( @{ $self->{_errors} }, "Invalid characters detected" );

                my @ascii_character_numbers = unpack( "C*", $celldata );
                Common::Log::Print("Invalid character detected: $celldata: @ascii_character_numbers");

                return undef;
            }
        }

        $record->{'line-number'} = $row + 1;

        push( @$data, $record );
    }

    $self->{_data} = $data;

    if ($data) {
        return 1;
    } else {
        my $errstr = "File contains no data";
        push( @{ $self->{_errors} }, $errstr );
        Common::Log::Print("ERROR: $errstr");

        return;
    }
}

sub _read_cell_value {
    my $self = shift;
    my $cell = shift || return;

    my @filters;
    my $celldata = $cell->{Val};

    # Add filter to trim whitespace from the front and back of a string.
    push( @filters, ( qr/^\s*/ => '' ) );
    push( @filters, ( qr/\s*$/ => '' ) );
    push( @filters, ( qr/\s+/  => ' ' ) );

    return unless ( $cell->{Val} );

    # If ucs2 (UTF16 LE) convert and filter UCS2 first
    # jpk - the only code I've ever seen is 'ucs2', but we might as well be explicit with this test.
    #
    if ( 'ucs2' eq $cell->{Code} ) {
        my %translation_map = (UCS2_CHARACTER_MAP);
        Common::Log::Debug( Dumper $cell );

        $celldata = $self->_filter_ucs2( $cell->{Val}, \%translation_map );
    } else {

        # Alas, we don't know whether we've got latin1 or utf8, or plain ascii.
        # We _want_ UTF8 - So we'll use Common::UTF8::Encode, which will set the
        # utf8 flag and convert latin1 to utf8 if necessary.
        #
        $celldata = Common::UTF8::Encode($celldata);
    }

    # add additional filter rules for latin8
    my ( $key_pattern, $value_pattern );
    my %translation_map = (CHARACTER_MAP);
    while ( my ( $k, $v ) = each %translation_map ) {
        $k = sprintf( "%x", $k );
        push( @filters, ( qr/\x$k/ => $v ) );
    }

    return $self->_filter( $celldata, @filters );
}

sub _is_empty_row {
    my ( $self, $sheet, $row, $catno_index ) = @_;
    my $cell;

    assert( defined($self) && defined($sheet) && defined($row) && defined($catno_index) );

    # If the catalog number is set this isn't an empty row.
    $cell = $sheet->{Cells}[$row][$catno_index];

    if ( $cell && $cell->{Val} && length( $cell->{Val} ) ) {
        return undef;
    }

    # Check the first 5 columns for content
    for ( my $i = 0 ; $i < 5 ; $i++ ) {
        $cell = $sheet->{Cells}[$row][$i];

        # If a cell if found with data we are done, this isn't an empty row.
        if ( $cell && $cell->{Val} && length( $cell->{Val} ) ) {
            return undef;
        }
    }

    Common::Log::Debug("Empty Row Detected: Row #$row");

    # All tests failed. This is an empty row
    return 1;
}

1;