#!/usr/bin/perl -w
use strict;
use vars qw(
    $VERSION
    $DEBUG
    $lockFile
    $counter
    );

BEGIN {
   unshift(@INC,'/opt/software/current/lib/perl');
}

$VERSION = '1';
$DEBUG=1;

use appLib; # internal module, used for logging, etc.
use DBI;

$| = 1;
appLib::toolInit();
my $dbh = DBI->connect("dbi:mysql:db","user","password") or die "failed: $DBI::errstr";


my $drone = $ENV{ROLE};
$drone =~ s/db\d+$//;

# get test digests
# stuff in array

my $all_digests = $dbh->selectcol_arrayref("select testDigest from testDB t, clients c where t.clientID=c.clientID and c.drone != '$drone' and t.testType != 'Administrative' and t.clientID !='edutest'");

unless (scalar @$all_digests > 0) { die "No foreign tests to process!"; }
$DEBUG && print "all digests: ".scalar @$all_digests."\n";

my ($avg, $delta);
my $stop = 60*60*3; # 60s/m * 60m/h * 3h
my $now = time;
my $start = time;
my $i = 0;
my $chunk = 1000 
my (@status, @digests);
my ($count, $sth, $done);

do
{
        my $first = $i;
        my $last = ($i+=$chunk) < scalar @$all_digests ? $i : scalar @$all_digests;
        @digests = @{$all_digests}[$first..($last-1)];
        $count = 0;
        # del from affected tables
        my $digest_str = join "','", @digests;
        my $repCats = $dbh->selectcol_arrayref("select repCatID from testDBToMCQDB where testDigest in ('$digest_str')");
        $sth = $dbh->prepare("delete from testDBRepCats where repCatID = ?");
        $done = $sth->execute_array({ ArrayTupleStatus => \@status },$repCats);
        grep {$count++ if $_} @status;
        print "$count / $done digests processed for testDBRepCats\n";

        $count = 0;
        $sth = $dbh->prepare("delete from testDBToMCQDB where testDigest = ?");
        $done = $sth->execute_array({ ArrayTupleStatus => \@status },\@digests);
        grep {$count++ if $_} @status;
        print "$count / $done digests processed for testDBToMCQDB\n";

        $count = 0;
        $sth = $dbh->prepare("delete from testDBRepCatTarget where testDigest = ?");
        $done = $sth->execute_array({ ArrayTupleStatus => \@status },\@digests);
        grep {$count++ if $_} @status;
        print "$count / $done digests processed for testDBRepCatTarget\n";

        $count = 0;
        $sth = $dbh->prepare("delete from testDB where testDigest = ?");
        $done = $sth->execute_array({ ArrayTupleStatus => \@status },\@digests);
        grep {$count++ if $_} @status;
        print "$count / $done digests processed for testDB\n";

        $delta = time - $now;
        $avg += $delta;
        $avg /= 2 if $avg > $delta;
        $now = time;
        $DEBUG && print "delta: $delta\tavg: $avg\ttotal: ".($now-$start)."\tdigests: ".scalar @digests."\n";
}
while ((scalar @digests) == $chunk && ($now-$start) < ($stop-$avg));  # we haven't exceeded max time and we've probably
                                                                      # got time for another iteration