#! /usr/bin/perl

# $Id: delicious_tweetdeck_groups.pl,v 1.0 2009/03/04 01:35:21 subbarao Exp $
# Update TweetDeck groups from del.icio.us 

use Net::Delicious;
use Log::Dispatch::Screen;
use DBI;
use File::Glob ':glob';

use strict;

my $debug;
$ARGV[0] eq '-d' and $debug = 1, shift;
@ARGV > 0 or die "usage: $0 [-d] delicious_username:password\n";

my $sqlitefile;
if ($^O =~ /linux/i) {
	$sqlitefile= bsd_glob("$ENV{HOME}/.appdata/TweetDeck*/Local Store/td_*.db");
}
elsif ($^O =~ /mswin32/i) {
	$sqlitefile = bsd_glob("$ENV{APPDATA}\\TweetDeck*\\Local Store\\td_*.db");
}
my (%groups, %gcids);

my ($delusername, $delpasswd) = split(/:/, $ARGV[0], 2);
my $del = Net::Delicious->new(
	{user => $delusername, pswd => $delpasswd, debug => $debug});

# Workaround for Net::Delicious:all_posts() not supporting a tags argument:
# $del->all_posts({tag => 'twitter'}) 
$del->config('delicious_posts_all.tag' => '');
# Get all twitter group memberships from del.icio.us
my $res = $del->_execute_method("delicious.posts.all", {tag => 'twitter'})
	or die "Error calling posts/all\n";
my $posts = $del->_getresults($res, 'post');
foreach my $post ($del->_buildresults('Post', $posts)) {
	my $username = $post->href; $username =~ s#^http://twitter.com/## or next;
	foreach my $tag (split(' ', $post->tags)) { 
		$groups{$tag}{$username} = 1 unless $tag eq 'twitter';
	}
}

my $dbh = DBI->connect("DBI:SQLite:dbname=$sqlitefile", '', '');

my $columns = $dbh->selectall_hashref('select * from columns', 'cID');
foreach my $cid (sort keys %$columns) {
	# Exclude all non-group columns
	if ($columns->{$cid}{cType} > 0 && $columns->{$cid}{cType} != 999) {
		delete $columns->{$cid}; 
		next;
	}
	# Look for corresponding groups that already exist
	my $cname = $columns->{$cid}{cName}; $cname =~ s/^Group: //;
	if (exists $groups{$cname}) {
		$gcids{$cname} = $cid;
		delete $columns->{$cid};
		next;
	}
}

# Assign new groups in the columns table
my @cids = sort keys %$columns;
my $sth = $dbh->prepare("update columns set cName=?, ctype=? where cID=?");
foreach my $group (sort keys %groups) {
	next if exists $gcids{$group};
	last if !defined($gcids{$group} = shift @cids);
	$sth->execute("Group: $group", 0, $gcids{$group});
}

# Clear any stale groups in the columns table
$sth = $dbh->prepare("update columns set cName='',ctype='' where cID=?");
foreach my $cid (@cids) { $sth->execute($cid) }

# Set up userid hash map
my %userids = map @$_, 
	@{$dbh->selectall_arrayref('select fScreenName,fUserID from friends')};

# Rebuild the groups table
$dbh->do('delete from groups');
$sth = $dbh->prepare('insert into groups (gID, gCID, gUserID) values (?,?,?)');
my $gid = 1;
foreach my $group (keys %groups) {
	foreach my $username (keys %{$groups{$group}}) {
		$sth->execute($gid++, $gcids{$group}, $userids{$username});
	}
}