#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use List::UtilsBy qw/max_by/;

use Getopt::Long;
use Pod::Usage;

use Spreadsheet::ParseExcel;
use Excel::Writer::XLSX;

my $opts;
$opts->{comp} = shift @ARGV;
$opts->{with} = shift @ARGV;

GetOptions( 'output|o=s'   => \$opts->{out},
            'colmap|c=s'   => \$opts->{col},
            'format|f=s'   => \$opts->{format},
            'help|h'       => \$opts->{help},
            'man|m'        => \$opts->{man}
         ) or pod2usage({ -verbose => 0, -output => \*STDERR,
            -msg => "$0 no parameter found.\n
            Use --help for more options.\n"
         } );

if ($opts->{man}) {
   pod2usage( -verbose => 2 );
} elsif ($opts->{help} or !defined($opts->{comp}) or !defined($opts->{with})) {
   pod2usage( -verbose => 0, -output => \*STDERR,
               -msg => "$0 [options]" );
}

my $workbook = Excel::Writer::XLSX->new($opts->{'out'} // 'out.xlsx');
my $worksheet = $workbook->add_worksheet();
my $fmt = $workbook->add_format(color => $opts->{format} // 'blue');

my @cols = map {/(\d+):(\d+)/ ? [$1, $2] : die "Malformed pair: $_"} split(' ', $opts->{col}) if $opts->{col};

my ($comp, $comp_parm) = xltree($opts->{comp});
my ($with, $with_parm) = xltree($opts->{with});

$worksheet->write_col(0, 0, $comp);

if (@cols) {
   foreach my $col (@cols) {
      colcmp($worksheet, $comp, $with, $comp_parm, $with_parm, $col);
   }
} else {
   foreach my $col ($comp_parm->[2] .. $comp_parm->[3]) {
      colcmp($worksheet, $comp, $with, $comp_parm, $with_parm, $col);
   }
}

$workbook->close();

sub xltree { # from excel file, return array $data structure and an array of $row_min, $row_max, $col_min, $col_max;
   my ($file) = @_;

   my $parser     = Spreadsheet::ParseExcel->new();
   my $workbook   = $parser->parse($file);

   if (!defined($workbook)) {
      die "Problem with $file: " . $parser->error() . ".\n";
   }

   my $worksheet = $workbook->worksheet(0);

   my ($row_min, $row_max) = $worksheet->row_range();
   my ($col_min, $col_max) = $worksheet->col_range();

   my $data;

   for my $row ($row_min .. $row_max) {
      for my $col ($col_min .. $col_max) {
         my $cell = $worksheet->get_cell($row, $col);
         $data->[$row][$col] = defined($cell) ? $cell->unformatted : " ";
      }
   }

   return $data, [$row_min, $row_max, $col_min, $col_max];
}

sub colcmp {
   my ($worksheet, $comp, $with, $comp_parm, $with_parm, $col) = @_;

   for my $comp_row ($comp_parm->[0] .. $comp_parm->[1]) {
      my @row_i;
      for my $with_row ($with_parm->[0] .. $with_parm->[1]) {
         $row_i[$with_row] = grep { $_ ~~ [split(" ", $with->[$with_row][$col->[1]])] }
               split(" ", $comp->[$comp_row][$col->[0]]);
      }
      my $max_match = max_by { $row_i[$_] } 0 .. $#row_i;
      my @wmap = map { $with->[$max_match][$col->[1]] =~ /$_/ ? ($fmt, "$_ ") : "$_ " }
                  split(" ", $comp->[$comp_row][$col->[0]]);
      $worksheet->write_rich_string($comp_row, $col->[0], @wmap);
   }
}