#!/usr/bin/perl
use strict;
use warnings;
use FindBin qw( $Bin );
use lib "/opt/pos/lib";
use POSIX qw( strftime );
use Data::Dumper;
use File::Path qw( make_path remove_tree );
use File::Copy qw( copy move );
use DBI;
use App::POS::DB::Postgres;
use App::POS::Config;

#
# 2019-08-19 - used to fix dupplicated gb_identifiers in check lines.
# this came from a paytransferorvoid bug
#

my $from = shift // "";
my $to = shift // "";

die "Usage: $0 <from> <to>\n" unless $from && $to;

my $DATABASE = 'pos';
my$dsn='DBI:Pg:dbname='.$DATABASE.';host=127.0.0.1';
my$user='root';
my$passwd='';
my$db=App::POS::DB::Postgres->new(dbh=>DBI->connect($dsn,$user,$passwd,{}));

fix_table_dup_ids("sales_headers");
fix_table_dup_ids("sales_details");

my $dup_recs = $db->select(qq{
  SELECT gb_identifier, COUNT(*)
  FROM sales_details
  WHERE business_date >= ? AND business_date <= ?
  GROUP BY gb_identifier HAVING COUNT(*) > 1;
}, $from, $to);

exit unless scalar @$dup_recs;

print STDERR scalar(@$dup_recs) . " dupplicated gb_identifier in sales_details\n";

foreach my $r (@$dup_recs) {
  print STDERR "#### gb_identifier: ".$r->{gb_identifier}."\n";

  my $c = 0;

  my $recs = $db->select(
    "SELECT id FROM sales_details WHERE gb_identifier = ? AND business_date >= ? AND business_date <= ?",
    $r->{gb_identifier}, $from, $to,
  );

  foreach my $rec (@$recs) {
    $db->do("UPDATE sales_details SET gb_identifier = ? WHERE id = ?", $r->{gb_identifier}.$c, $rec->{id});
    $c++;
  }
}

sub fix_table_dup_ids {
  my( $table_name ) = @_;

  my $dup_ids = $db->select(qq{
    SELECT id, COUNT(*)
    FROM $table_name
    WHERE business_date >= ? AND business_date <= ?
    GROUP BY id HAVING COUNT(*) > 1;
  }, $from, $to);

  return unless scalar @$dup_ids;

  print STDERR "Table $table_name has dupplicate ids, will fix it...\n";

  print STDERR "  Alter table $table_name to add id2 field\n";
  $db->do("ALTER TABLE $table_name ADD COLUMN id2 SERIAL PRIMARY KEY");

  print STDERR "  Delete dupplicates\n";
  $db->do(qq{
    DELETE FROM $table_name WHERE business_date >= ? AND business_date <= ? AND id2 NOT IN (
      SELECT DISTINCT ON (gb_identifier) id2
      FROM $table_name
      WHERE business_date >= ? AND business_date <= ?
    )
  }, $from, $to, $from, $to);

  print STDERR "  Drop id2 field from table $table_name\n";
  $db->do("ALTER TABLE $table_name DROP COLUMN id2");

  print STDERR "Done...\n";
}


