#! /usr/bin/perl -w

use DBI;
use XML::Simple;

my $infile = `curl -basic --user USERNAME:PASSWORD http://twitter.com/statuses/replies.xml`;

$xml = new XML::Simple (KeyAttr=>[]);
$data = $xml->XMLin($infile);

foreach $item (@{$data->{status}}) {
	$statusid = $item->{id};
	$text =     $item->{text};
	$userid =   $item->{user}{id};
	$username = $item->{user}{name};
	$handle   = $item->{user}{screen_name};
	
	$text =~ s/\@YOURBOTNAME\S?//gi;
	$text =~ s/'/''/g;
	
	my $dbh = DBI->connect("DB CONNECT STRING");
	
	my $checkid = "select count(*) from lyrics where status_id = '$statusid'";
	my $sth_check = $dbh->prepare($checkid);
	$rv = $sth_check->execute;
	
	$lyric = $sth_check->fetchrow_array();
	
	if ($lyric < 1) {
		my $insert = "insert into lyrics(status_id,text,user_id,user_name,handle,posted)
			values('$statusid','$text','$userid','$username','$handle','0')";
		
		$sth_insert = $dbh->prepare($insert);
		$rv = $sth_insert->execute;
		
		$sth_insert->finish();
	} 
	
	$sth_check->finish();
	$dbh->disconnect();
}

$dbh = DBI->connect("DB CONNECT STRING");

my $postq = "select status_id,handle,text from lyrics where posted = 0 order by lyric_id";
my $sth_postq = $dbh->prepare($postq);
$rv = $sth_postq->execute;

my %unposted = ();
$postcounter = "1";
while (($statusid,$handle,$text) = $sth_postq->fetchrow_array()) {
	
	$unposted{ $postcounter }{'id'} = $statusid;
	$unposted{ $postcounter }{'handle'} = $handle;
	$unposted{ $postcounter }{'text'} = $text;
	
	$postcounter++;
}

$sth_postq->finish();
$dbh->disconnect();

$postcounter = "1";

foreach $item (keys (%unposted)) {
	if ($postcounter <15) {
		
		$statusid = $unposted{ $item }{'id'};
		$handle = $unposted{ $item }{'handle'};
		$text = $unposted{ $item }{'text'};
		
		my $tweet = "Via \@".$handle.": ".$text;
		$tweet =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
		
		my $dbh = DBI->connect("DB CONNECT STRING");
	
		my $update = "update lyrics set posted = '1' where status_id = '$statusid'";
		my $sth_update = $dbh->prepare($update);
		$rv = $sth_update->execute;
		
		$sth_update->finish();
		$dbh->disconnect();
		
		`curl -basic --user USERNAME:PASSWORD --data status=$tweet http://twitter.com/statuses/update.xml`;
		
		$postcounter++;
		sleep 59;
	} else {
		exit;
	}
}
exit;