package Metadata::Exporter::Excel;
use strict;

use Spreadsheet::WriteExcel;

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

use base qw( Metadata::Exporter );

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

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

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

    my $criteria = $args{criteria};

    $self->{_criteria} = ref($criteria) ? $criteria : ($criteria);

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

    return $self;
}

sub export {
    my $self = shift;
    my $outfile = shift || '-';

    assert($self);

    $self->{_workbook} = Spreadsheet::WriteExcel->new($outfile)
      || die "Unable to write to outfile";

    $self->_export();
}

sub export_mod_perl {
    my $self = shift;

    tie *XLS, 'Apache';
    binmode(XLS);

    $self->{_workbook} = Spreadsheet::WriteExcel->new( \*XLS );

    $self->_export();
}

# Don't forget to binmode STDOUT before printing the result.
sub export_to_var {
    my $self = shift;

    open my $fh, '>', \my $str or die "Failed to open filehandle: $!";

    $self->{_workbook} = Spreadsheet::WriteExcel->new($fh);

    $self->_export();

    return $str;
}

sub _export {
    my $self = shift;

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

    my $worksheet = $self->{_workbook}->add_worksheet();
    $worksheet->add_write_handler( qr[\w], \&store_string_widths );

    ###############################################################################
    #
    # Add a handler to store the width of the longest string written to a column.
    # We use the stored width to simulate an autofit of the column widths.
    #
    # You should do this for every worksheet you want to autofit.
    #
    $worksheet->add_write_handler( qr[\w], \&store_string_widths );

    $self->_write_header($worksheet);
    $self->_write_body($worksheet);

    $self->{_workbook}->close();
}

sub _get_header_index {
    my $self      = shift;
    my @headerMap = $self->_exportColumns();

    my %index;
    my $column = 0;

    for ( my $i = 0 ; $i < @headerMap - 1 ; $i += 2 ) {
        Common::Log::Debug("HEADER: $headerMap[$i]");
        $index{ $headerMap[ $i + 1 ] } = $column;
        $column++;
    }

    return \%index;
}

sub _write_header {
    my $self        = shift;
    my $ws          = shift;
    my %headerMap   = $self->_exportColumns();
    my $headerIndex = $self->_get_header_index();
    my %newMap      = reverse %headerMap;

    my $format = $self->{_workbook}->add_format();
    $format->set_bold();

    assert($self);
    assert($ws);

    foreach ( keys(%$headerIndex) ) {
        s/_/-/g;
        $ws->write_string( 0, $headerIndex->{$_}, $newMap{$_}, $format );
    }
}

sub _write_body {
    my $self = shift;
    my $ws   = shift;

    my $i = 1;
    my $row;
    my $errors;
    my $trackErrors;
    my $albumErrors;
    my $fieldError;
    my $status;
    my $message;
    my %headerMap   = $self->_exportColumns();
    my $headerIndex = $self->_get_header_index();
    my $cleanKey;
    my $dashKey;

    my $dbo = Common::RSApp::GetClientDB;

    my $error = $self->{_workbook}->add_format();
    $error->set_bg_color('red');

    my $warning = $self->{_workbook}->add_format();
    $warning->set_bg_color('yellow');

    my $duplicate = $self->{_workbook}->add_format();
    $duplicate->set_bg_color('cyan');

    my $format_type;

    my $tracks = Metadata::DB::Item::ContentImportData->GetAllTracks( criteria => $self->{_criteria} );

    while ( $tracks->hasNext() ) {

        $row = $tracks->next();

        foreach ( keys(%$headerIndex) ) {
            $cleanKey = $_;
            $cleanKey =~ s/-/_/g;

            #Common::Log::Debug( "IT: $_ ROW: $i COL: $headerIndex->{$_} VAL: $row->{$cleanKey}");
            $ws->write_string( $i, $headerIndex->{$_}, $row->{$cleanKey} )
              if ( defined( $row->{$cleanKey} ) );
        }

        $trackErrors = Metadata::DB::Item::ContentImportDataStatus->Search( content_import_data_id => $row->content_import_data_id );

        $albumErrors =
          Metadata::DB::Item::ContentImportDataStatus->GetAlbumErrors( content_import_data_id => $row->content_import_data_id );

        foreach $errors ( ( $trackErrors, $albumErrors ) ) {
            while ( $errors->hasNext() ) {

                $fieldError = $errors->next();

                if ( $fieldError->status && $fieldError->status == 9 ) {
                    $format_type = $error;
                } elsif ( $fieldError->status && $fieldError->status == 8 ) {
                    $format_type = $error;
                } elsif ( $fieldError->status && $fieldError->status == 7 ) {
                    $format_type = $duplicate;
                } elsif ( $fieldError->status && $fieldError->status == 6 ) {
                    $format_type = $warning;
                } else {
                    $format_type = undef;
                }

                $cleanKey = $fieldError->ref_column;
                $dashKey  = $fieldError->ref_column;
                $cleanKey =~ s/-/_/g;
                $dashKey =~ s/_/-/g;
                $ws->write( $i, $headerIndex->{$dashKey}, $row->{$cleanKey}, $format_type );
                $ws->write_comment( $i, $headerIndex->{$dashKey}, $fieldError->message() );
            }
        }

        $i++;
    }

    autofit_columns($ws);

}

##############################################################################
#
# Simuluate Excel's autofit for colums widths.
#
#
# Excel provides a function called Autofit (Format->Columns->Autofit) that
# adjusts column widths to match the length of the longest string in a column.
# Excel calculates these widths at run time when it has access to information
# about string lengths and font information. This function is *not* a feature
# of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.
#
# However, we can make an attempt to simulate it by keeping track of the
# longest string written to each column and then adjusting the column widths
# prior to closing the file.
#
# We keep track of the longest strings by adding a handler to the write()
# function. See add_handler() in the S::WE docs for more information.
#
# The main problem with trying to simulate Autofit lies in defining a
# relationship between a string length and its width in a arbitrary font and
# size. We use two aproaches below. The first is a simple direct relationship
# obtrained by trial and error. The second is a slightly more sophisticated
# method using an external module. For more complicated applications you will
# probably have to work out your own methods.
#
# reverse(''), May 2006, John McNamara, jmcnamara@cpan.org
#

###############################################################################
###############################################################################
#
# Functions used for Autofit.
#

###############################################################################
#
# Adjust the column widths to fit the longest string in the column.
#
sub autofit_columns {

    my $worksheet = shift;
    my $col       = 0;

    for my $width ( @{ $worksheet->{__col_widths} } ) {
        $worksheet->set_column( $col, $col, $width ) if $width;
        $col++;
    }
}

###############################################################################
#
# The following function is a callback that was added via add_write_handler()
# above. It modifies the write() function so that it stores the maximum
# unwrapped width of a string in a column.
#
sub store_string_widths {

    my $worksheet = shift;
    my $col       = $_[1];
    my $token     = $_[2];

    # Ignore some tokens that we aren't interested in.
    return if not defined $token;       # Ignore undefs.
    return if $token eq '';             # Ignore blank cells.
    return if ref $token eq 'ARRAY';    # Ignore array refs.
    return if $token =~ /^=/;           # Ignore formula

    # Ignore numbers
    return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;

    # Ignore various internal and external hyperlinks. In a real scenario
    # you may wish to track the length of the optional strings used with
    # urls.
    return if $token =~ m{^[fh]tt?ps?://};
    return if $token =~ m{^mailto:};
    return if $token =~ m{^(?:in|ex)ternal:};

    # We store the string width as data in the Worksheet object. We use
    # a double underscore key name to avoid conflicts with future names.
    #
    my $old_width    = $worksheet->{__col_widths}->[$col];
    my $string_width = string_width($token);

    if ( not defined $old_width or $string_width > $old_width ) {

        # You may wish to set a minimum column width as follows.
        #return undef if $string_width < 10;

        $worksheet->{__col_widths}->[$col] = $string_width;
    }

    # Return control to write();
    return undef;
}

###############################################################################
#
# Very simple conversion between string length and string width for Arial 10.
# See below for a more sophisticated method.
#
sub string_width {

    return 0.9 * length $_[0];
}

###############################################################################
#
# This function uses an external module to get a more accurate width for a
# string. Note that in a real program you could "use" the module instead of
# "require"-ing it and you could make the Font object global to avoid repeated
# initialisation.
#
# Note also that the $pixel_width to $cell_width is specific to arial. For
# other fonts you should calculate appropriate relationships. A future verison
# of S::WE will provide a way of specifying column widths in pixels instead of
# cell units in order to simplify this conversion.
#
#sub string_width {
#
#    #require Font::TTFMetrics;
#
#    my $arial        = Font::TTFMetrics->new('c:\windows\fonts\arial.ttf');
#
#    my $font_size    = 10;
#    my $dpi          = 96;
#    my $units_per_em = $arial->get_units_per_em();
#    my $font_width   = $arial->string_width($_[0]);
#
#    # Convert to pixels as per TTFMetrics docs.
#    my $pixel_width  = 6 + $font_width *$font_size *$dpi /(72 *$units_per_em);
#
#    # Add extra pixels for border around text.
#    $pixel_width  += 6;
#
#    # Convert to cell width (for Arial) and for cell widths > 1.
#    my $cell_width   = ($pixel_width -5) /7;
#
#    return $cell_width;
#
#}

1;