#!/usr/bin/perl
#
# prime_chan_update.pl
# (C) Copyright 2011 Kirk Bocek
# t004 (at) kbocek.com - Comments & Feedback Welcome
# Update the MythTV virtual -> physical channel maps of a Clear QAM
# recorder using the built-in virtual channel tuning of an HD Homerun 
# Prime HDHR3-CC or HDHR6-CC.
#
# WARNING - DO NOT USE THIS SCRIPT UNLESS YOU ARE FAMILIAR WITH MYSQL 
# AND THE MYTHTV DATABASE STRUCTURE. This script directly manipulates
# MythTV tables. This behavior is strongly discouraged by the MythTV 
# devs and this script is neither endorsed nor vetted by them. Use at
# your own risk.
#
# With that caveat in place, I am fairly confident that the manipulations
# of this script are mild. I touch only two tables: 'channel', to re-tune
# only *existing* channels, and 'dtv_multiplex', to insert missing
# frequencies if necessary.
#
# Tune a virtual channel on a Prime, extract the physical channel and
# program number. Check and update Clear QAM channel mappings with 
# this information. Note that this only works if your Clear QAM source
# is using the *same* virtual channel numbers as your Prime.
#
# This only uses tuner 2 on your Prime as this tends to be the last
# tuner that Myth uses. It will check if the tuner is in use and
# will fail after a timeout period if the tuner remains in use.
#
# 20111015 - First version.
# 20111017 - Fixed comments mentioning Classic HDHRs. Added more
# 	error messages. Added $writeswitch. Changed getting 
# 	program number from /tuner2/streaminfo to /tuner2/program
# 	Removed one of the sleep commands
# 	Fixed dtv_multiplex search
# 20111019 - Added code to insert missing dtv_multiplex records.
# 	Stregthened warnings in comments.
#
# Thank you Bill Lash & his cat Ozzy for their help in getting this
# script up and running.
#
use strict;
use DBI;

#
# Configuration Info, customize this for your setup:
#
# $primeid is the network id of your HDHR3-CC or HDHR6-CC.
#
# !IMPORTANT! $targsourceid must be set correctly! Otherwise the
# wrong channels will be changed.
# $targsourceid is the sourceid in the channels table that will be
# updated with the information extracted from the HDHR-CC. This should
# be the source being used by your Clear QAM tuner. If you're not sure
# what this number should be, take a look at the videosource table 
# for the description of each of your sources.
#
# @chans is the list of channels to be checked on the Prime and updated on
# in the source used by your Clear QAM tuner.
#
# $email is an email address to receive update messages. An email is sent
# only if something is changed or $debug is set. Make sure email is 
# properly configured on your system. Leave this blank to disable.
#
# $timeout is the number of times the script will check tuner2 for 
# availablity before failing. 1 second delay between tries.
#
# %prog is the full paths of external programs used by this script.
#
# $debug - display and email non-error messages. Normally, set this to 0.
#
# $writeswitch - If set to 0, nothing will be written to the database.
#
my $primeid = '12345678';
my $targsourceid = 2;
my @chans = qw/ 9 10 702 703 704 705 706 707 709 711 712 713 714 715 716 717 722 /;
my $email = '';
my $timeout = 10;
my %prog = (
	'hrconfig' => '/full/path/to/your/hdhomerun_config',
	'mail' => '/bin/mail',
);
my $debug = 0;
my $writeswitch = 1;

#Database Setup
my $dbserver = "Database Server Hostname";
my $database = "mythconverg";
my $user = "username";
my $pwd = "password";

#
# Work Begins Here
#
my $mailtext = undef;
my $model = `$prog{hrconfig} $primeid get /sys/hwmodel`;
chomp $model;
die "Device is not an HDHR Prime\n"
	unless $model eq 'HDHR3-CC' or $model eq 'HDHR6-CC';

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$dbserver",
    $user,$pwd,{'RaiseError' => 1})
    or die "Cannot connect: " . $DBI::errstr;

# Main Loop - Do each of the channels
MAIN: while (@chans) {
	my $curchan = shift @chans;
	my $loctime = $timeout;
	my $status = undef;
	STATUS: while ($loctime) {
		$status = `$prog{hrconfig} $primeid get /tuner2/status`;
		last STATUS if $status =~  /ch=none/;
		sleep 1;
		$loctime--;
	}
	unless ($status =~ /ch=none/) {
		$mailtext .= "PrimeUpdate script failed.\n";
		$mailtext .= "Tuner 2 is busy: timed out on channel $curchan.\n";
		last MAIN;
	}


	`$prog{hrconfig} $primeid set /tuner2/vchannel $curchan`;
	sleep 1;
	my $progid = undef;
	my $chaninfo = undef;
	$progid = `$prog{hrconfig} $primeid get /tuner2/program`;
	$chaninfo = `$prog{hrconfig} $primeid get /tuner2/channel`;
	`$prog{hrconfig} $primeid set /tuner2/vchannel 0`;
	chomp $progid;
	chomp $chaninfo;
	unless ($progid and $chaninfo) {
		$mailtext .= "Error tuning channel $curchan on device $primeid\n";
		$mailtext .= "Frequency and program number were not returned.\n";
		last MAIN;
	}

	print "Channel $curchan\n";
	$mailtext .= "\nChannel $curchan:\n" if $debug;

	my ($tmp,$freq) = split(':',$chaninfo);
	#Get dtv_multiplex record for frequency
	my $mplexid = undef;
	$mplexid = $dbh->selectrow_array(
			"SELECT mplexid from dtv_multiplex 
			WHERE frequency = $freq and sourceid = $targsourceid;");
	unless ($mplexid) {
		# If $mplexid comes back empty, this frequency is missing from
		# dtv_multiplex. Add new frequency.
		# See http://www.mythtv.org/wiki/Dtv_multiplex_table
		#
		my $res = "Database update not performed.\n";
		$res = $dbh->do("INSERT INTO dtv_multiplex SET
			sourceid = $targsourceid,
			frequency = $freq,
			modulation = 'qam_256',
			sistandard = 'atsc';") if $writeswitch;
		$mplexid = $dbh->selectrow_array("SELECT last_insert_id();");
		unless ($res and $mplexid) {
			$mailtext .= "Inserting new frequency $freq for source $targsourceid failed.\n";
			$mailtext .= "DBI::errstr = " . $DBI::errstr . "\n";
			last MAIN;
		}
		$mailtext .= "Added new mplexid $mplexid for $freq into dtv_multiplex table.\n";
		$mailtext .= "DB Insert returned: $res\n";
	}
	$mailtext .= "From Prime: Freq is $freq, Program ID is $progid\n" if $debug;
	$mailtext .= "From dtv_multiplex: mplexid for $freq is $mplexid\n" if $debug;

	#Get current channel setting in target sourceid
	my ($dbmplex,$dbprog) = (undef,undef);
	($dbmplex,$dbprog) = $dbh->selectrow_array(
		"SELECT mplexid, serviceid from channel where
		sourceid = $targsourceid and
		channum = $curchan;");
	unless ($dbmplex and $dbprog) {
		$mailtext .= "Channel $curchan was not found in channel database for \n";
		$mailtext .= "sourceid $targsourceid. Make sure your Prime and Clear QAM\n";
		$mailtext .= "channels are the same.\n";
		last MAIN;
	}
	$mailtext .= "From channel db: Source $targsourceid, " .
		"mplexid is $dbmplex ProgID is $dbprog\n" if $debug;

	if ($progid != $dbprog or $mplexid != $dbmplex ) {
		#Channel has moved, update channel database
		my $res = "Update Not Executed.\n";
		$res = $dbh->do(
			"UPDATE channel 
				set mplexid = $mplexid, serviceid = $progid
				WHERE
				channum = $curchan and sourceid = $targsourceid;"
			) if $writeswitch;
		$mailtext .= 
			"Channel $curchan updated to mplexid $mplexid, service $progid\n";
		$mailtext .= "DB Update returned: $res\n";
	}
}

print $mailtext;

# Send Email
if ($email and $mailtext) {
	open FH,"|$prog{mail} -s PrimeUpdateScript $email";
	print FH $mailtext;
	close FH;
}

