#!/usr/bin/perl

# the IP address of your TiVO (must be running tyserver)
$tivoAddress = "10.1.2.3";

# database settings
$dbhost = "localhost";
$dbname = "tivo2podcast";
$dbuser = "tivo2podcast";
$dbpass = "password";

$tyls = "/usr/local/bin/tyls";

$verbose = 0;

#########################################################################

use DBI;

@episodes = qx{$tyls -t $tivoAddress};

# Frontline       The Alternative Fix     Tues    11/29   9:00 pm 2628563
# The Office      E-Mail Surveillance     Tues    11/22   9:30 pm 2618193

$dbh = DBI->connect( "dbi:mysql:dbname=$dbname", $dbuser, $dbpass )
    or die "Cannot connect to database: $DBI::errstr";

foreach $episode ( @episodes ) {
    ( $program, $episode, $day, $date, $time, $fsid ) = split /\t/, $episode;
    if ( $episode eq "not a episode" ) {
	$episode = $program;

    }

    print "$day $date $time - $program - $episode\n" if ( $verbose );

    $sth = $dbh->prepare ( "select programid from programs where name = '" . dbescape( $program ) . "'" )
	or die "Cannot prepare open SQL: $DBI::errstr";
    $sth->execute or die "Cannot execute open SQL: $DBI::errstr";
    @row = $sth->fetchrow_array;

    $programid = 0;
    if ( $row[0] eq "" ) {
	print "  Looks like we don't have that program in the database yet. Adding it.\n" if ( $verbose );
	$sth = $dbh->prepare ( "insert into programs ( datecreated, name, podcastify ) values ( now( ), '" . dbescape( $program ) . "', 'f' )" )
	    or die "Cannot prepare open SQL: $DBI::errstr";
	$sth->execute or die "Cannot execute open SQL: $DBI::errstr";
	$sth = $dbh->prepare ( "select programid from programs where name = '" . dbescape( $program ) . "'" )
	    or die "Cannot prepare open SQL: $DBI::errstr";
	$sth->execute or die "Cannot execute open SQL: $DBI::errstr";
	@row = $sth->fetchrow_array;
	$programid = $row[0];

    }
    else {
	$programid = $row[0];

    }

    #print "programid = $programid\n" if ( $verbose );

    $sth = $dbh->prepare ( "select episodeid from episodes where programid = $programid and fsid = $fsid" )
        or die "Cannot prepare open SQL: $DBI::errstr";
    $sth->execute or die "Cannot execute open SQL: $DBI::errstr";
    @row = $sth->fetchrow_array;

    if ( $row[0] eq "" ) {
	print "  Doesn't look like we have this episode for $program. Adding it.\n" if ( $verbose );
	$sth = $dbh->prepare ( "insert into episodes ( datecreated, programid, name, airdate, fsid, grabbed ) values ( now( ), $programid, '" 
			       . dbescape( $episode ) . "', '" . dbescape( "$time $day $date" ) . "', $fsid, 'f' )" )
	    or die "Cannot prepare open SQL: $DBI::errstr";
	$sth->execute or die "Cannot execute open SQL: $DBI::errstr";
	$sth = $dbh->prepare ( "select episodeid from episodes where name = '" . dbescape( $episode ) . "'" )
	    or die "Cannot prepare open SQL: $DBI::errstr";
	$sth->execute or die "Cannot execute open SQL: $DBI::errstr";
	@row = $sth->fetchrow_array;
	$episodeid = $row[0];

    }
    else {
	$episodeid = $row[0];

    }

    #print "episodeid = $episodeid\n\n" if ( $verbose );

}

sub dbescape {
    my $text = pop @_;

    $text =~ s/'/\\'/g;

    return $text;

}