#!/usr/bin/perl

# by Paul Makepeace, http://paulm.com/ Aug 2004

use warnings;
use strict;

use CGI;
use DBI;
use Data::Dumper;
use Template;

my @dsn = qw(dbi:mysql:urban_tapestries tomcat ventolin);
my $rdf_out = '/home/wwwut/trial.urbantapestries.net/rss';
my $base_url = 'http://trial.urbantapestries.net/rss/index.rdf.cgi';
my $subscribe_html = 'subscribe.tt';

my %sql = (
	threads => qq{
		SELECT count(pocket_id) as num_pockets, t.thread_id, t.name, t.owner
		  FROM thread t JOIN pocket pk ON pk.thread = t.thread_id
		 GROUP BY t.name
		 ORDER BY t.name
	},
	authors => qq{
		SELECT count(pocket_id) as num_pockets, p.username, p.forename, p.surname
		  FROM person p JOIN pocket pk ON pk.owner = p.username
		 GROUP BY p.username
		 ORDER BY p.username
	}
);

my %sth;

my $dbh = DBI->connect(@dsn, { RaiseError => 1, AutoCommit => 1 })
	or die $DBI::errstr;
foreach my $sql (keys %sql) {
	$sth{$sql} = $dbh->prepare($sql{$sql});
	$sth{$sql}->execute();
}

my $params = {
	base_url => $base_url,
	authors => $sth{authors}->fetchall_arrayref({}),
	threads => $sth{threads}->fetchall_arrayref({}),
};

print CGI::header();
my $template = Template->new();
$template->process($subscribe_html, $params)
	or die $template->error;