#!/usr/bin/perl

use strict;
use warnings;
use Data::Dumper;
use Text::Iconv;
use File::Slurp qw( read_file );
use Spreadsheet::XLSX;
use Spreadsheet::ParseExcel::Simple;

my $file = shift;
die "Usage: $0 <file>\n" unless defined $file;
die "Can't find file: $file\n" unless -f $file;

my $cf = '/opt/pos/server/data/areas/card_sets.txt';
die "Can't file card sets file...\n" unless -e $cf;

my @lines = read_file($cf);
die "No lines in card sets file...\n" unless scalar @lines;

my $first = $lines[0];
chop($first);

my @first_line = split /#/, $first;
die "Invalid first line in card sets...\n" unless scalar @first_line >= 6;

my $is_black_list = $first_line[5] || 0;

my @rows = xls_read($file);
my $blacklist_str = "";
my %did_codes = ();

foreach my $sheet (@rows) {
  foreach my $tab (@$sheet) {
    $tab ||= [];
    next unless scalar @$tab;
    my $prod = $tab->[0];
    next unless $prod =~ /^\d+$/;
    $blacklist_str .= "$prod,";
    $did_codes{$prod} = 1;
  }
}

die "Didn't found blacklisted products in xls...\n" unless $blacklist_str;

chop($blacklist_str);

my $first_line_to_write = "";

if ($is_black_list) {
  $first_line[0] = $blacklist_str;
  $first_line_to_write = join("#", @first_line) . "\n";
}
else {
  $first_line_to_write = $blacklist_str . "###0##1#######0#0#0\n";
  unshift @lines, $first_line_to_write;
}

open(F, "> $cf");

for (my $i=0; $i<scalar(@lines); $i++) {
  if ($i==0) {
    print F $first_line_to_write;
  }
  else {
    my $l = $lines[$i];
    my @a = split /#/, $l;
    # if we've a separate card that was manually marked as blacklist and
    # it came from xls, remove that line from the card_sets file
    if (@a >= 6) {
      $a[5] ||= 0;
      next if $a[5] && exists($did_codes{$a[0]});
    }
    print F "$l";
  }
}

close(F);

print "OK\n";

#
# functions
#

sub xls_read {
  my $f = shift;

  my $converter = Text::Iconv->new("utf-8", "windows-1251");

  my $xls;

  eval {
    $xls = Spreadsheet::XLSX->new($f, $converter);
  };

  if ($@) {
    $xls = Spreadsheet::ParseExcel::Simple->read($f);
  }

  my @rows = ();

  if (ref($xls) eq 'Spreadsheet::XLSX') {
    foreach my $sheet (@{$xls->{Worksheet}}) {
      $sheet->{MaxRow} ||= $sheet->{MinRow};

      foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        $sheet->{MaxCol} ||= $sheet->{MinCol};

        my @cur_row = ();

        foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
          my $cell = $sheet->{Cells}[$row][$col];
          push @cur_row, $cell->{Val};
        }

        push @rows, \@cur_row;
      }
    }
  }
  else {
    foreach my $sheet ($xls->sheets) {
      while ($sheet->has_data) {
        my @data = $sheet->next_row;
        push @rows, \@data;
      }
    }
  }

  return \@rows;
}
