#!/usr/bin/perl

# configure
use constant DB       => './schedule.db';
use constant DSN      => 'dbi:mysql:c4l11:avery.infomotions.com';
use constant PASSWORD => 'nobody';
use constant USERNAME => 'nobody';

# require
use DBI;
use DateTime;
use DateTime::Format::MySQL;
use strict;

# initialize
my $dbh   = DBI->connect( DSN, USERNAME, PASSWORD );
my $sql   = '';
my $tweets = '';
my $irc   = '';

open SCHEDULE, ' < ' . DB or die "Can't open " . DB . ": $!\n";
while ( <SCHEDULE> ) {
	
	# parse record
	chop;
	my ( $name, $start, $end ) = split /\t/, $_;
	
	# build the queries and do the work
	$sql    = "SELECT COUNT( irc_id ) FROM irc WHERE datetime >= '$start' AND datetime <= '$end'";
	$irc    = ( $dbh->selectrow_array( $sql ) )[ 0 ];
	$sql    = "SELECT COUNT( tweet_id ) FROM tweets WHERE datetime >= '$start' AND datetime <= '$end'";
	$tweets = ( $dbh->selectrow_array( $sql ) )[ 0 ];
	print "$name\t$irc\t$tweets\n";
	
}

# clean up
close SCHEDULE;
$dbh->disconnect;
exit;