#!/usr/bin/perl

#binmode(STDOUT, ":utf8");
#use utf8;
use strict;
use DBI;

my $dbh = dbhTarget($ARGV[0]);
if ( !$dbh )
{
    print "Cannot connect to Opals database";
    exit 0;
}

#my $querydup = $dbh->prepare("select uid, barcode, dateLoan from opl_loan group by barcode, uid, dateLoan having count(*)>1 order by dateLoan");
my $querydup = $dbh->prepare("select idloan, uid, barcode, dateLoan from opl_loan group by dateLoan, barcode having count(*)>1 order by dateLoan");

$querydup->execute();

my $queryls = $dbh->prepare("select idloan, uid, dateReturn from opl_loan where dateLoan=? && barcode=?");
my $querydel = $dbh->prepare("delete from opl_loan where idloan=?");

my $count = 0;
my $countRemoval = 0;
while ( my $rec = $querydup->fetchrow_hashref ) {
    print "UID: $rec->{'uid'}\t\t$rec->{'barcode'}\t$rec->{'dateLoan'}\n";

    $queryls->execute($rec->{'dateLoan'}, $rec->{'barcode'});

    my @arrDup = ();
    my $isReturned = 0;
    while ( my $recdup = $queryls->fetchrow_hashref ) {
        print "UID: $rec->{'uid'}\t#$recdup->{'idloan'}\t$rec->{'barcode'}\t$rec->{'dateLoan'}\t";#$recdup->{'dateReturn'}\n";
#        if ( !$recdup->{'dateReturn'} ) {
            print "Not returned\n";
            push @arrDup, $recdup->{'idloan'};
#            $querydel->execute($recdup->{'idloan'});
#            $count1++;
#            last;
#        }
#        else {
#            print "$recdup->{'dateReturn'}\n";
#            $isReturned = 1;
#        }
    }

#    if (!$isReturned) {
        @arrDup = reverse @arrDup;
        pop @arrDup;
        @arrDup = reverse @arrDup;
#    }
    print "Remove LID \t#", join('# ', @arrDup), "\n";
    if($ARGV[1] eq 'delete') {
        foreach my $lidDup (@arrDup) {
            $querydel->execute($lidDup);
            $countRemoval++;
        }
    }
    
    $count++;
}

$querydel->finish;
$queryls->finish;
$querydup->finish;
print "Duplicate:\t$count\nRemoval:\t$countRemoval\n";

###########################################################
sub dbhTarget 
{
    my ($configfile) =@_;
    my ($db_driver, $db_name, $db_host, $db_port, $db_user, $db_password);
#    my $configfile = '';
#    print "Enter the config filename of Opals: ";
#    $configfile = <STDIN>;
    my $cfgTarget = LoadConfig($configfile);
    return if ( !$cfgTarget );

    $db_driver   = $cfgTarget->{'db_driver'} || 'mysql';
    $db_name     = $cfgTarget->{'db_name'};
    $db_host     = $cfgTarget->{'db_host'};
    $db_port     = $cfgTarget->{'db_port'}   || '3306';
    $db_user     = $cfgTarget->{'db_user'};
    $db_password = $cfgTarget->{'db_password'};

    my $dsn = "dbi:$db_driver:$db_name:$db_host:$db_port";

    return DBI->connect($dsn, $db_user, $db_password);
}


###########################################################
sub LoadConfig 
{
    my ($configFile) = @_;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) 
    {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;
    
    return $config;
}