# -*- cperl -*-
use strict;
use warnings;
use Smart::Comments;
use lib 'classes';
use Celaenito::DB;

### cleaning job_queue up...
my ($num_job_queue) = DB->selectrow_array(q{SELECT COUNT(*) FROM job_queue});
my $sth = DB->prepare(q{SELECT urlid FROM job_queue});
$sth->execute;
for (1 .. $num_job_queue) { ### Cleaning [===[%]   ]
    my $row = $sth->fetch;
    my $urlid = $row->[0];

    my ($exists) = DB->selectrow_array(
	q{SELECT COUNT(*) FROM target_url WHERE urlid = ?}, undef, $urlid);
    if ($exists) {
	#print "job_queue: URL $urlid still exists.\n";
    }
    else {
	print "job_queue: URL $urlid is orphan.\n";
	DB->do(q{DELETE FROM job_queue WHERE urlid = ?}, undef, $urlid);
    }
}

### cleaning job_log up...
my ($num_job_log) = DB->selectrow_array(q{SELECT COUNT(*) FROM job_log});
$sth = DB->prepare(q{SELECT urlid FROM job_log});
$sth->execute;
for (1 .. $num_job_log) { ### Cleaning [===[%]   ]
    my $row = $sth->fetch;
    my $urlid = $row->[0];

    my ($exists) = DB->selectrow_array(
	q{SELECT COUNT(*) FROM target_url WHERE urlid = ?}, undef, $urlid);
    if ($exists) {
	#print "job_log: URL $urlid still exists.\n";
    }
    else {
	print "job_log: URL $urlid is orphan.\n";
	DB->do(q{DELETE FROM job_log WHERE urlid = ?}, undef, $urlid);
    }
}

### cleaning versions up...
my ($num_versions) = DB->selectrow_array(q{SELECT COUNT(*) FROM versions});
$sth = DB->prepare(q{SELECT urlid FROM versions});
$sth->execute;
for (1 .. $num_versions) { ### Cleaning [===[%]   ]
    my $row = $sth->fetch;
    my $urlid = $row->[0];

    my ($exists) = DB->selectrow_array(
	q{SELECT COUNT(*) FROM target_url WHERE urlid = ?}, undef, $urlid);
    if ($exists) {
	#print "versions: URL $urlid still exists.\n";
    }
    else {
	print "versions: URL $urlid is orphan.\n";
	DB->do(q{DELETE FROM versions WHERE urlid = ?}, undef, $urlid);
    }
}

### cleaning pieces_info up...
my ($num_pieces_info) = DB->selectrow_array(q{SELECT COUNT(*) FROM pieces_info});
$sth = DB->prepare(q{SELECT pieceid, refcount FROM pieces_info});
$sth->execute;
for (1 .. $num_pieces_info) { ### Cleaning [===[%]   ]
    my $row = $sth->fetch;
    my $pieceid = $row->[0];
    my $refcount = $row->[1];

    my ($count) = DB->selectrow_array(
	q{SELECT COUNT(*) FROM versions WHERE pieceid = ?}, undef, $pieceid);
    if ($count) {
	#print "pieces_info: piece $pieceid still exists.\n";
	if ($count != $refcount) {
	    print "pieces_info: piece $pieceid has an incorrect refcount.\n";
	    print "             updating from $refcount to $count...\n";
	    DB->do(q{UPDATE pieces_info SET refcount = ? WHERE pieceid = ?}, undef, $count, $pieceid);
	}
    }
    else {
	print "pieces_info: piece $pieceid is orphan.\n";
	DB->do(q{DELETE FROM pieces_info WHERE pieceid = ?}, undef, $pieceid);
    }
}

### cleaning pieces_storage up...
my ($num_pieces_storage) = DB->selectrow_array(q{SELECT COUNT(*) FROM pieces_storage});
$sth = DB->prepare(q{SELECT pieceid FROM pieces_storage});
$sth->execute;
for (1 .. $num_pieces_storage) { ### Cleaning [===[%]   ]
    my $row = $sth->fetch;
    my $pieceid = $row->[0];

    my ($exists) = DB->selectrow_array(
	q{SELECT COUNT(*) FROM pieces_info WHERE pieceid = ?}, undef, $pieceid);
    if ($exists) {
	#print "pieces_storage: piece $pieceid still exists.\n";
    }
    else {
	print "pieces_storage: piece $pieceid is orphan.\n";
	DB->do(q{DELETE FROM pieces_storage WHERE pieceid = ?}, undef, $pieceid);
    }
}