#!/usr/bin/perl

use strict;
use warnings;
use lib "/opt/pos/lib";
use POSIX qw( strftime );
use Data::Dumper;
use File::Slurp qw( read_file write_file append_file );
use File::Path qw( make_path );
use DBI;
use IO::Socket::INET;
use App::POS::Config;
use App::POS::Check;
use App::POS::Machine::Server;
use App::POS::Utils qw( date_based_on_closing_hour log area_nextid );

my %prev_product_lotes = ();

# apt-get install -y libdbd-mysql-perl

my $loop = 5;

while (1) {
  log("/opt/pos/ipesa.log", ">> START");

  my $cfg = App::POS::Config->new( file => "/opt/pos/etc/config.ini" );
  my $info = $cfg->load_fixed();

  my $machine = undef;

  if (exists $info->{STATION_NUMBER} && $info->{STATION_NUMBER} > 0) {
    $machine = App::POS::Machine::Station->new( config => $cfg );
  }
  else {
    $machine = App::POS::Machine::Server->new( config => $cfg );
  }

  my $login = $info->{IPESA_IPX_USER} || 'integrator';
  my $passw = $info->{IPESA_IPX_PASSWORD} || 'Ipesa_2020';

  my $ips = $info->{IPESA_IPX_IP} || '';

  if (! $ips) {
    die "config.ini - No IPs defined in param IPESA_IPX_IP\n";
  }

  my $db = $machine->database();

  #my $recs = $db->select(
  #  "SELECT id, handy_plu, sell_vat1 FROM products WHERE deleted = 0 AND handy_plu <> '' AND handy_plu <> ? AND handy_plu <> ?",
  #  '--', 0
  #);

  #my %valid_products = map { $_->{id} => { plu => $_->{handy_plu}, vat => $_->{sell_vat1} } } @$recs;

  my @ips = split /,/, $ips;

  foreach my $ip (@ips) {
    # my $did = sync_tickets($info, $machine, \%valid_products, $ip, $login, $passw);
    my $did = sync_tickets($info, $machine, $ip, $login, $passw);
  }

  log("/opt/pos/ipesa.log", ">> FINISH\n");

  sleep $loop;
}

sub sync_tickets {
  my $cfg = shift;
  my $machine = shift;
  # my $valid_products = shift;
  my $ip = shift;
  my $login = shift;
  my $passwd = shift;

  my $db = $machine->database();

  $cfg->{IPESA_IPX_BARCODE_PREFIX} ||= '28';
  $cfg->{IPESA_IPX_MISC_PRODUCT} ||= 0;

  my $misc_prod_id = 0;
  my $misc_prod_vat = 0;
  my $misc_prod_name = '';

  if ($cfg->{IPESA_IPX_MISC_PRODUCT}) {
    my $prod = $db->select(
      "SELECT id, sell_vat1, name FROM products WHERE deleted = 0 AND id = ?",
      $cfg->{IPESA_IPX_MISC_PRODUCT},
    );

    if (scalar @$prod) {
      $misc_prod_id = $prod->[0]->{id};
      $misc_prod_vat = $prod->[0]->{sell_vat1};
      $misc_prod_name = $prod->[0]->{name};
    }
  }

  my %info;
  
  # Database configuration
  my $dbname = 'ipesa';
  my $host = $ip;
  my $port = 3306;
  my $username = $login;
  my $password = $passwd;

#=pod
  # Data Source Name (DSN)
  my $dsn = "DBI:mysql:database=$dbname;host=$host;port=$port;mysql_connect_timeout=1";

  my $dbh;

  eval {
    # Connect to the database
    $dbh = DBI->connect($dsn, $username, $password, {
        RaiseError => 1,
        AutoCommit => 1,
    }) or die "Could not connect to database: $DBI::errstr";
  };

  if ($@) {
    $DBI::errstr ||= '';
    log("/opt/pos/ipesa.log", ">> ERROR - conecting to mariadb - $DBI::errstr - $@");
    return 0;
  }

  log("/opt/pos/ipesa.log", ">> connected to the database successfully - $ip");

  #
  # handle tickets reset daily
  #

  my $datetime = strftime("%Y-%m-%d %H:%M:%S", localtime);
  my $business_date = date_based_on_closing_hour($datetime, $cfg->{END_BUSINESS_HOUR}.":00");
  my @da = split /\-/, $business_date;
  my $dir = $machine->dir_server() . "/server/data/ipesa_tickets_reset/$ip/$da[0]/$da[1]/$da[2]";

  if (! -d $dir) {
    log("/opt/pos/ipesa.log", "############ TICKETS RESET!!!!!");
    make_path($dir);

    my $sql;
    my $sth;

    $sql = "INSERT INTO commands (command) VALUES ('hardreset')";
    $sth = $dbh->prepare($sql);
    $sth->execute();

    $sql = "INSERT INTO barcodes_received (ID, type, barcode, description) VALUES (?, ?, ?, ?)";
    $sth = $dbh->prepare($sql);
    $sth->execute(0, 'EAN', $cfg->{IPESA_IPX_BARCODE_PREFIX} . 'BEEEEJJJJJ', 'PT-CERT TALOES');

    $sql = "INSERT INTO barcodes_received (ID, type, barcode, description) VALUES (?, ?, ?, ?)";
    $sth = $dbh->prepare($sql);
    $sth->execute(1, 'EAN', '26KKKKKGGGGG', 'PT-CERT PESO');

    $sql = "INSERT INTO barcodes_received (ID, type, barcode, description) VALUES (?, ?, ?, ?)";
    $sth = $dbh->prepare($sql);
    $sth->execute(2, 'EAN', '27KKKKKIIIII', 'PT-CERT PRECO');
  }

  log("/opt/pos/ipesa.log", ">> sync_tickets - ip: $ip");

  # Prepare and execute a query
  my $sql = "SELECT * FROM tickets WHERE (Upper(destino) = 'S' OR Upper(destino) = 'C') AND anulado = '0'";
  my $sth = $dbh->prepare($sql);
  $sth->execute();

  # Fetch and display results
  while (my $row = $sth->fetchrow_hashref) {
    $row->{seccao} ||= 0;
    $row->{correlativo} ||= 0;
    push @{$info{$row->{seccao}}->{$row->{correlativo}}}, $row;
  }
#=cut

=pod
  my $cnt = read_file('debug');
  my $VAR1;
  eval "$cnt";
  %info = %$VAR1;
=cut

  my %tickets;

  foreach my $seccao (sort keys %info) {
    foreach my $ticket (sort keys %{$info{$seccao}}) {
      my $lines = $info{$seccao}->{$ticket};
      foreach my $l (@$lines) {
        if ($l->{modooperativa} == 9) {
          # print STDERR "HEADER - $ticket - $l->{plu}!!\n";
          $tickets{$l->{seccao}}->{$l->{correlativo}} = {
            ticketid => $l->{correlativo},
            barcode  => $l->{plu},
            seccao   => $seccao,
            vendor   => $l->{vendedor},
            total    => $l->{unidade},
            products => [],
          };

#=pod
          # mark ticket as read
          _mark_ticket_read($dbh, $l->{correlativo}, $seccao);
#=cut

        } else {
          next unless $l->{codplu} * 1;

          my $prod_id = 0; 
          my $prod_vat = 0; 
          my $prod_name = '';

          # print STDERR Dumper($l);

          my $prod = $db->select(
            "SELECT name, sell_vat1 FROM products WHERE deleted = 0 AND id = ?",
            $l->{codplu},
          );

          # product id exists
          if (scalar @$prod) {
            $prod_id = $l->{codplu};
            $prod_vat = $prod->[0]->{sell_vat1};
            $prod_name = $prod->[0]->{name};
          }
          # if prod id doesn't exists and we have a diversos configured, use it
          else {
            if ($misc_prod_id) {
              $prod_id = $misc_prod_id;
              $prod_vat = $misc_prod_vat;
              $prod_name = $misc_prod_name;
            } else {
              my $prod = $db->select(
                "SELECT id, name, sell_vat1 FROM products WHERE deleted = 0 AND handy_plu = ?",
                $l->{plu} * 1,
              );

              if (scalar @$prod) {
                $prod_id = $prod->[0]->{id};
                $prod_vat = $prod->[0]->{sell_vat1};
                $prod_name = $prod->[0]->{name};
              }
            }
          }

          if (! $prod_id) {
            _send_to_ui('ALERT', 'PRODUTO INEXISTENTE' . ' - ' . $l->{codplu} . " / " . $l->{plu_name} . "<P>ATIVE 'DIVERSOS'");
          } else {
            push @{$tickets{$l->{seccao}}->{$l->{correlativo}}->{products}}, {
              product      => $prod_id,
              product_name => $prod_name,
              quantity     => $l->{unidade} / 1000,
              price        => $l->{preco} / 100,
              linetotal    => $l->{importe} / 100,
              discount     => $l->{discount_value},
              vendor       => $l->{vendedor},
              vat          => $prod_vat / 100,
              lote         => $l->{lote},
            }
          }
        }
      }
    }
  }

  if (! scalar %tickets) {
    log("/opt/pos/ipesa.log", "### NAO EXISTEM TICKETS...");
  }

  my $area = $cfg->{IPESA_IPX_AREA} || 0;
  my $area_dir = "/opt/pos/server/data/areas/$area";

  foreach my $seccao (sort keys %tickets) {
    foreach my $ticket (sort keys %{$tickets{$seccao}}) {
      my $ref = $tickets{$seccao}->{$ticket};
      # my $ticket_id = $seccao + 100 . sprintf("%05d", $ticket);

      # we can use one digit only for the section
      my( $seccao_single ) = $seccao =~ /^(.)/;
      my $ticket_id = $seccao_single . sprintf("%04d", $ticket * 1);
      my $ticket_id_w_date = $ticket_id . strftime("%m%d", localtime);

=pod
      my $exists_query = qq{
        SELECT sh.id, sh.fo_doc_number, TO_CHAR(sh.date_closed, 'HH24:MI') AS hour, sh.clerk_close_name,
          COALESCE(sha1.key_value, '0') AS doc_canceled,
          sha2.key_value AS check_name, sha3.key_value AS in_training_mode
        FROM sales_headers sh
        LEFT JOIN sales_headers_aux sha1 ON (sh.id = sha1.header AND sha1.key_name = 'canceled')
        LEFT JOIN sales_headers_aux sha2 ON (sh.id = sha2.header AND sha2.key_name = 'check_name')
        LEFT JOIN sales_headers_aux sha3 ON (sh.id = sha3.header AND sha3.key_name = 'in_training_mode')
        WHERE sh.doc_type IN (1, 5, 90, 7) AND sh.business_date = '$business_date' AND
          sha2.key_value ILIKE '$ticket_id%' AND COALESCE(sha1.key_value, '0') = '0'
        ORDER BY sh.id DESC
      };

      my $rec = $db->select($exists_query);

      # ticket was made today already
      if (scalar @$rec) {
        log("/opt/pos/ipesa.log", ">> TICKET $ticket_id EXISTS ALREADY IN $business_date, WILL IGNORE");
        next;
      }
=cut

      $ref->{products} ||= [];
      next unless scalar @{$ref->{products}};

      #print STDERR "#### ticket_id: $ticket_id, check_id: $ticket_id - $cfg->{END_BUSINESS_HOUR}\n";
      #print STDERR Dumper($ref);

      my $new_id = area_nextid($area_dir);
      # my $o = App::POS::Check->new( file => $area_dir . "/" . $ticket_id );
      my $o = App::POS::Check->new( file => $area_dir . "/" . $new_id );

      my $datetime = strftime("%Y-%m-%d %H:%M:%S", localtime);
      my $business_date = date_based_on_closing_hour($datetime, $cfg->{END_BUSINESS_HOUR}.":00");

      my @header = $o->generate_empty_header(
        $datetime,
        $business_date,
        $ref->{vendor},
        $area,
        0
        # $p->{station}
      );

      my @products = ();
      my $i = 0;

      foreach my $p (@{$ref->{products}}) {
        my $datetime = strftime("%Y-%m-%d %H:%M:%S", localtime);
        my( $date, $hour ) = $datetime =~ /^(.*?) (.*?)$/;
        my $gbi = strftime("%Y%m%d%H%M%S", localtime) . rand();
        $gbi =~ s/\.//g;

        $p->{lote} ||= "";
        # print STDERR "############### $p->{product} - $p->{lote}\n";

        # update
        if (
          ! exists $prev_product_lotes{$p->{product}} ||
          $prev_product_lotes{$p->{product}} ne $p->{lote}
        ) {
          $prev_product_lotes{$p->{product}} = $p->{lote};
          my $tmp = $db->select(qq{
            SELECT product_id, field_name
            FROM products_extra_data
            WHERE product_id = ? AND LOWER(field_name) = ?
          }, $p->{product}, 'lote');

          if (! scalar @$tmp) {
            $db->do(qq{
              INSERT INTO products_extra_data (
                product_id, field_name, field_value
              ) VALUES (
                ?, ?, ?
              )
            }, $p->{product}, 'lote', $p->{lote});
          }
          else {
            $db->do(qq{
              UPDATE products_extra_data
              SET field_value = ?
              WHERE LOWER(field_name) = ? AND product_id = ?
            }, $p->{lote}, 'lote', $p->{product});
          }

          # reload gambas cache for new lote to be used
          write_file("/opt/pos/server/data/tmp/data_refresh#0.txt", 1);

          log("/opt/pos/ipesa.log", "PRODUCT LOTE - product: $p->{product}, lote: $p->{lote}");
        }

        push @products, [
          $i,
          $p->{product},              # product id
          $hour,
          $p->{quantity} * 1,
          $p->{price} * 1,
          $p->{discount} * 1,
          $p->{vat},
          $p->{vendor},
          "",                         # Order group description (Primeros, Secundos, etc)
          "",                         # Group / dept where product was registered. Can be null if product was registered after a search, for example
          1,                          # Operation Type
          "",                         # Discount Txt
          0,                          # station
          $p->{lote},                 # Text Extra / Menu / Notas - EXTRA | MENU
          0,                          # Inside Menu (0 / 1)
          1,                          # Was holded already
          1,                          # Price used on the sale
          $business_date,
          $gbi,
        ];

        $i++;
      }

      my %data = (
        name     => $ticket_id_w_date,
        header   => \@header,
        details  => \@products,
        footers  => [],
        products_extra_data  => {},
        extra_data  => { ipesa_ticket => 1 },
      );

      my $dir_ipesa_tickets = $machine->server_dir_data() . "/" . 'ipesa_tickets';
      make_path($dir_ipesa_tickets) unless -d $dir_ipesa_tickets;

      my $file_ticket = $dir_ipesa_tickets . '/' . $ticket_id;

      append_file($file_ticket, "$new_id=$business_date\n");

      $o->set_data(\%data);

      $o->save();

      #my $station = 0;
      #$o->hold($station);
    }
  }

  return 1;
}

sub _mark_ticket_read {
  my( $dbh, $correlativo, $seccao ) = @_;

  my $sth = $dbh->prepare(qq{
    UPDATE tickets
    SET destino = ?
    WHERE correlativo = ? AND seccao = ? AND
      (Upper(destino) = 'S' OR Upper(destino) = 'C')
  });

  $sth->execute('F', $correlativo, $seccao);
}

sub _find_check_nextid {
  my $dir = shift;
  my $id = 1;

  my $f = $dir."/nextid";

  if (-e $f) {
    my $tmpid = read_file($dir."/nextid");
    $tmpid =~ s/\n//g;
    $tmpid =~ s/\r//g;
    $tmpid ||= 1;
    $tmpid++;
    $id = $tmpid;
  }

  write_file($f, "$id\n");

  return $id;
}

sub _send_to_ui {
  my( $action, $value ) = @_;
  $action ||= '';
  return unless $action && defined $value;

  my $sock = IO::Socket::INET->new(
    PeerAddr => '127.0.0.1',
    PeerPort => '8088',
    Proto => 'tcp',
    Timeout => 5,
  );

  if (defined $sock && $sock) {
    print $sock "#$action#$value\n" if defined $sock && $sock;
    $sock->close();
  }
  else {
    print STDERR "_send_to_ui - $action - error connecting to port 8088\n";
  }
}

__END__
$VAR1 = {
          'dia' => '23',
          'discount_value' => '0.00000',
          'modotara' => '0',
          'modoticket' => '0',
          'mes' => '04',
          'tax_value' => '31.60163',
          'plu' => '004393',
          'ficha' => '000028',
          'reservado' => '000000',
          'importe' => '0000000169',
          'vendor_name' => 'Ruben Ribeiro Da SIlva Magalhaes',
          'lote' => '00',
          'tax_percent' => '2300',
          'anulado' => '0',
          'plu_name' => 'SUMO COMPAL GRF VITAL ANANAS\\COCO 200ML',
          'seccao' => '2',
          'discount_type' => '',
          'family_name' => 'Bebidas',
          'ano' => '24',
          'correlativo' => 15,
          'tax_descr' => 'IVA 23%',
          'hora' => '17',
          'vendedor' => 1,
          'destino' => 'S',
          'id_ticketline' => 7362,
          'minuto' => '01',
          'parent' => undef,
          'teminal' => '46',
          'codplu' => '011020',
          'discount' => '',
          'family_id' => '10',
          'unidade' => '00001000',
          'preco' => '00000169',
          'modooperativa' => '2',
          'pagamento' => '0',
          'noperacoes' => '00000',
          'tara' => '000000'
        };

