#! /usr/bin/perl
use DBI;
use XMLTV;

$database = "tvguide";
$hostname = "localhost";
$user = "*****";
$password = "*****";

$driver = "mysql";
$dsn = "DBI:$driver:database=$database;host=$hostname;port=$port";


my $xmlfile = "/home/mckeehan/xmltv/guide.xml" ;
print "Reading $xmlfile..." ;
my $data = XMLTV::parsefile($xmlfile);
my ($encoding, $credits, $ch, $progs) = @$data;
my $langs = [ 'en', 'fr' ];

print 'source of listings is: ', $credits->{'source-info-name'}, "\n" if defined $credits->{'source-info-name'};

print "\nConnecting to $dsn...\n" ;
$dbh = DBI->connect($dsn, $user, $password);

die "mysqld is down" unless $dbh;

$rows_affected = $dbh->do("DELETE from channel");
$sth = $dbh->prepare("INSERT INTO channel (channelnum, id,display_name, iconurl) VALUES (?,?,?,?)");
print "\nInserting channels..." ;

foreach (values %$ch) {
#	printf "%s\n", $_->{'icon'}[0]->{'src'};
	my ($text, $lang) = @{XMLTV::best_name($langs, $_->{'display-name'})};
	my $channelnum = $_->{id};
	$channelnum =~ s/^(.*) .*/$1/g;
        $sth->execute( $channelnum, $_->{id}, $text, $_->{'icon'}[0]->{'src'} );
}

print "done.\n\nReading programs...";

$rows_affected = $dbh->do("DELETE from programme");
$sth = $dbh->prepare("INSERT INTO programme (title,sub_title,desc_str,date_str,category,start_str,stop_str,channel,star_rating) VALUES (?,?,?,?,?,?,?,?,?)");
print "\nInserting programs..." ;

foreach (@$progs) {
	my $std = $_->{start};
	my $spd = $_->{stop};
	$std =~ s/^(....)(..)(..)(..)(..)(..).*/$1\/$2\/$3 $4:$5:$6/g ;
	$spd =~ s/^(....)(..)(..)(..)(..)(..).*/$1\/$2\/$3 $4:$5:$6/g ;
#	$std =~ s/ 00:/ 12:/g;
#	$spd =~ s/ 00:/ 12:/g;
	$sth->execute( $_->{title}[0][0], $_->{"sub-title"}[0][0],$_->{desc}[0][0], $_->{"date"}[0][0], $_->{category}[0][0],$std, $spd, $_->{channel}, $_->{"star-rating"}[0] );
}

$rows_affected = $dbh->do("UPDATE programme SET start_date = start_str");
$rows_affected = $dbh->do("UPDATE programme SET stop_date = stop_str");
$rows_affected = $dbh->do("UPDATE programme SET stop_date = DATE_ADD( start_date, INTERVAL 1 HOUR ) WHERE stop_str IS NULL");
$rows_affected = $dbh->do("UPDATE programme SET stop_date = DATE_ADD( start_date, INTERVAL 1 HOUR ) WHERE stop_str IS NULL");

$rows_affected = $dbh->do("DELETE from stoptimes");
$rows_affected = $dbh->do("INSERT INTO stoptimes SELECT programme.uniquenum, min(p2.start_date) FROM programme LEFT OUTER JOIN programme p2 ON programme.channel = p2.channel AND programme.start_date < p2.start_date WHERE programme.stop_str IS NULL GROUP BY programme.uniquenum");

$sth = $dbh->prepare("SELECT programme.uniquenum, min(p2.start_date) FROM programme LEFT OUTER JOIN programme p2 ON programme.channel = p2.channel AND programme.start_date < p2.start_date WHERE programme.stop_str IS NULL GROUP BY programme.uniquenum");

$sth->execute();
while ( my @row = $sth->fetchrow_array ) {
	$rows_affected = $dbh->do("UPDATE programme set stop_date = '" . @row[1] . "' WHERE uniquenum = " . @row[0] );
}

$rows_affected = $dbh->do("UPDATE programme SET channel_no = channel");
$rows_affected = $dbh->do("UPDATE programme SET length = (time_to_sec(stop_date) - time_to_sec(start_date)) / 60" );
$rows_affected = $dbh->do("UPDATE programme SET length = (time_to_sec(stop_date) - time_to_sec(start_date) + 86400 ) / 60 WHERE time_to_sec(stop_date) < time_to_sec(start_date)" );

#------ start problems with channel configuration
$rows_affected = $dbh->do("UPDATE programme SET channel = left( right(channel,length(channel) -1) , instr( channel, '.zap2it.com') -2)" );

$rows_affected = $dbh->do("UPDATE programme SET channel_no = channel" );

$rows_affected = $dbh->do("UPDATE programme set channel = ucase(concat(concat( left( channel, length( channel_no )),  ' ') , right(channel, length(channel) - length(channel_no) ) ))" );

$rows_affected = $dbh->do("UPDATE channel SET id = left( right(id,length(id) -1) , instr( id, '.zap2it.com') -2)" );

$rows_affected = $dbh->do("UPDATE channel SET channelnum = id" );

$rows_affected = $dbh->do("update channel set id = ucase(concat(concat( left( id, length( channelnum )),  ' ') , right(id, length(id) - length(channelnum) ) ))" );
#------ end problems with channel configuration
$rows_affected = $dbh->do("UPDATE programme SET category = 'wjm' where title like 'American Chopper'" );
$rows_affected = $dbh->do("UPDATE programme SET category = 'wjm' where title like 'Jackie Chan%'" );

$rows_affected = $dbh->do("analyze table channel" );
$rows_affected = $dbh->do("analyze table channellike" );
$rows_affected = $dbh->do("analyze table hatedcategories" );
$rows_affected = $dbh->do("analyze table hatedprograms" );
$rows_affected = $dbh->do("analyze table programme" );
$rows_affected = $dbh->do("analyze table programmeweight" );
# DATEDIFF(mi, start_date, stop_date)") ;
print "done.\n";