#!/usr/bin/perl

use DBI;
use strict;

my $dbh = DBI->connect("dbi:mysql:database=mythconverg:host=localhost", 'root', '')
            or die "Cannot connect to database: $!\n\n";

print "Getting recordings information..\n";

my $sth = $dbh->prepare("SELECT chanid,starttime,title,subtitle, (unix_timestamp(endtime) - unix_timestamp(starttime))/60/60 as length from recorded");
$sth->execute();

my %recorded;

while (my $row = $sth->fetchrow_hashref()) {
        $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'title'} = $row->{'title'}.": ".$row->{'subtitle'};
        $recorded{$row->{'chanid'}}->{$row->{'starttime'}}->{'length'} = $row->{'length'};
}

print "Done.\n";
print "Getting seek table..\n";

$sth = $dbh->prepare("SELECT chanid,starttime from recordedseek");
$sth->execute();

print "Done.\n";

my $rownum;
my $repnum;

while (my $row = $sth->fetchrow_hashref()) {
        my $chanid = $row->{'chanid'};
        my $starttime = $row->{'starttime'};

        if (!($rownum % 10000)) {
                $repnum++;
                print "Processed ".(10000*$repnum)." rows\n";
        }

        if (!defined($recorded{$chanid}->{$starttime})) {
                my $sql = "DELETE from recordedseek WHERE chanid = $chanid AND starttime = '$starttime'";

                print "--> $sql\n";

                $dbh->do($sql);
        } else {
                $recorded{$chanid}->{$starttime}->{'entries'}++;
        }

        $rownum++;
}

my %sorted;

foreach my $chanid (keys %recorded) {
        foreach my $starttime (keys %{$recorded{$chanid}}) {
                my $entries = $recorded{$chanid}->{$starttime}->{'entries'};

                $sorted{$entries}->{'length'} = $recorded{$chanid}->{$starttime}->{'length'};
                $sorted{$entries}->{'title'} = $recorded{$chanid}->{$starttime}->{'title'};
        }
}

foreach my $entries (sort numerically keys %sorted) {
        my $title = $sorted{$entries}->{'title'};
        my $length = $sorted{$entries}->{'length'};

        my $average = $entries / $length;

        print "$title ->\t$entries ($average/hour average)\n";
}

sub numerically {
        $a <=> $b;
}

print "Done!\n"