#!/usr/bin/perl
#
# Steve Poirier - <A HREF="mailto:steve@inet-technologies.com">steve@inet-technologies.com</A>
# edited by Tim Allwine <A HREF="mailto:tallwine@oreilly.com">tallwine@oreilly.com</A>
# to use with RT2 and MySQL
# edited by Tony Aiuto <tony@ics.com> to deal with Links

use strict;
my $ticketnbr = 0;

BEGIN { push @INC, "/usr/local/share/rt2/lib/"; do "/usr/local/share/rt2/etc/config.pm"; }
use RT;
(my $x=RT::Handle->new)->Connect;

my $dbh = $x->dbh() or die "Couldn't connect to RT database";

my $sql = q{SELECT id FROM Tickets where Status=?};
my $sth = $dbh->prepare($sql);
$sth->execute('dead');

# proceed with deletion

my $d_sth = $dbh->prepare('SELECT id FROM Transactions where Ticket=?');
my $stid = $dbh->prepare('DELETE FROM Attachments WHERE TransactionId=?');
my $sttrans = $dbh->prepare( 'DELETE FROM Transactions WHERE Ticket=?');
my $stwatch = $dbh->prepare( 'DELETE FROM Watchers WHERE Value=?');
my $stobjects =$dbh->prepare( 'DELETE FROM ObjectKeywords WHERE ObjectId=?');
my $stlinks1 = $dbh->prepare( 'DELETE FROM Links WHERE LocalBase=?');
my $stlinks2 = $dbh->prepare( 'DELETE FROM Links WHERE LocalTarge=?');
my $stticket = $dbh->prepare( 'DELETE FROM Tickets WHERE id=?');

open MAIL, '|mail -s "dead tickets" ME@MYCOMPANY.com' or die $!;

print MAIL $sth->rows()," dead tickets were removed.\n\n";

while ( my ($ticketid) = $sth->fetchrow_array() )  {
    #print "\nticket $ticketid is dead. Deleting:  ";
    $d_sth->execute($ticketid);

# delete Attachments for this ticket
    #print "Attachments. ";
    while ( my ($transid) = $d_sth->fetchrow_array() )  {
	$stid->execute($transid);
    }

# delete Ticket in Transactions table
    #print "Transactions. ";
    $sttrans->execute($ticketid);

# delete Ticket in ticket in Watchers table
    #print "Watchers. ";
    $stwatch->execute($ticketid);

# delete Ticket in ObjectKeywords table
    #print "ObjectKeywords. ";
    $stobjects->execute($ticketid);

# delete Ticket from the Links table
    #print "Links1. ";
    $stlinks1->execute($ticketid);
    #print "Links2. ";
    $stlinks1->execute($ticketid);

# delete Ticket from the Tickets table
    #print "Ticket. ";
    $stticket->execute($ticketid);

    $ticketnbr++;
}
#print "\nDone. Deleted $ticketnbr tickets.\n\n";

exit;
