#!/usr/bin/perl -w
# Check and/or update frequency information for existing U.S. ATSC channels
# in the MythTV database, based on the intformation in the command line.

use Getopt::Long;
use DBI;
use DBD::mysql;

# Change these as needed!!
$host     = 'localhost';
$database = 'mythconverg';
$user     = 'mythtv';
$pass     = 'mythtv';
$dbh = DBI->connect("dbi:mysql:database=$database" .
	":host=$host", $user, $pass)
	or die "Cannot connect to database: $!\n\n";

# Raise errors on any SQL failure.
$dbh->{RaiseError} = 1;
# When creating new frequencies on dtv_multiplex, by default, the fields that don't
# matter for US ATSC get set to NULL. Changing this to 1 will cause it to use the
# dtv_multiplex table defaults.
use constant USE_MPLEX_DEFAULTS => 0;

($help, $source, $updatechannel, $createmplex, $yes) = ();

GetOptions (	"help|h" => \$help,
		"update-channel|u" => \$updatechannel,
		"create-mplex|c" => \$createmplex,
		"source|s=s" => \$source,
		"yes|y" => \$yes)
|| &syntax(-1);
&syntax(0) if ($help);
&syntax(-1) if (@ARGV != 4);

if (defined $source) {
	$sql = "SELECT sourceid FROM videosource WHERE name=?";
	@params = ($source);
} else {
	$sql = "SELECT sourceid FROM videosource";
	@params = ();
}
@a = $dbh->selectall_array($sql, {}, @params);
if (@a == 0) {
	die ((defined $source) ? "Invalid video source name: $source specified!\n" : "No video sources found?\n");
}
die (scalar(@a) . " video sources found. You must use -s\n") if (@a > 1);

$sourceid = $a[0]->[0];

if (USE_MPLEX_DEFAULTS) {
	($mcols, $mvals) = ();
} else {
	$mcols = ', inversion, fec, bandwidth, lp_code_rate, transmission_mode, guard_interval, constellation, hierarchy, hp_code_rate';
	$mvals = ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL';
}
$msql = "INSERT INTO dtv_multiplex
(sourceid, transportid, frequency, modulation, sistandard, networkid, mod_sys$mcols)
VALUES ($sourceid , ?, ?, '8vsb', 'atsc', 0, '1'$mvals)";

($freq, $tid, $channum, $pid) = @ARGV;
%freqmap = get_us_atsc_frequencies();
if (substr($freq, -6) ne '000000') {
	$ok = 0;
	foreach $key (keys %freqmap) {
		if ($freqmap{$key} eq $freq) {
			$freq = $key;
			$ok = 1;
			last;
		}
	}
	die "Invalid frequency: $freq\n" unless($ok);
}
die("Invalid US ATSC frequency?: $freq\n") if (!(defined $freqmap{$freq}));
$itid = get_integer_transport_id($tid);
die("Invalid transport id?: $tid\n") if (!$itid);
$xtid = sprintf('0x%04X', $itid);

if (($updatechannel || $createmplex) and !$yes) {
	@a = ();
	push (@a, 'Update channel') if ($updatechannel);
	push (@a, 'Create dtv_multiplex') if ($createmplex);
	if (!get_yn("WARNING: Continue enabling the following update(s): " . join(', ', @a) . "?")) {
		print "Aborting...\n";
		exit();
	}
}

print "CHECKING: dtv_multiplex: Freq: $freq	TID: $itid ($xtid)\n";

$mplex = read_mplex($freq);

if (!keys %$mplex) {
	print "NO dtv_multiplex record for frequency: $freq\n";
	if ($createmplex) {
		print "\nCREATING dtv_multiplex for $freq...\n";
		$rows = $dbh->do($msql, {}, ($itid, $freq)) or die $dbh->errstr;
		print "\tOK: affected rows: $rows\n";
		$mplex = read_mplex($freq);
		die("ERROR: No mplex found for: $freq after insert?\n") if (!keys %$mplex);
	}
} else {
	print "FOUND: mplexid: $mplex->{'mplexid'} transportid: $mplex->{'transportid'} frequency: $mplex->{'frequency'}\n";
	if ($mplex->{'transportid'} != $itid) {
		$word = $mplex->{'transportid'} ? $mplex->{'transportid'} : 'None';
		print "WARNING!!! TID MISMATCH in current dtv_multiplex ($word). Does NOT match $itid\n";
		print "NOTE that such a mismatch does not appear to affect ATSC at all.\n";
	}
}
print "\nCHECKING channel channum: $channum\n";
$chan = read_channel($channum);
die("Channel $channum NOT found!\n") if (!keys %$chan);
print_channel($chan, $mplex, $freq, $pid);
die("Can't update channel with no mplex.\n") if ($updatechannel and !keys(%$mplex) and !$createmplex);
if ($updatechannel) {
	print "\nUPDATING channel info...\n";
	$rows = $dbh->do('UPDATE channel SET freqid=?, mplexid=?, serviceid=? WHERE chanid=?',
		{}, ($freqmap{$freq}, $mplex->{'mplexid'}, $pid, $chan->{'chanid'})) or die $dbh->errstr;
	if ($rows) {
		print "\tOK: affected rows: $rows\n";
	} else {
		print "\tWARNING! No rows affected?\n";
	}
	$chan = read_channel($channum);
	print_channel($chan, $mplex, $freq, $pid);
}

# Read an mplex record
sub read_mplex {
	my ($freq) = @_;
	my $h = $dbh->selectrow_hashref('SELECT mplexid, transportid, frequency FROM dtv_multiplex WHERE frequency=?', {}, ($freq));
	return $h;
}

# Read a channel record
sub read_channel {
	my ($channum) = @_;
	my $h = $dbh->selectrow_hashref('SELECT chanid, channum, freqid, callsign, mplexid, serviceid FROM channel WHERE channum=?', {}, ($channum));
	return $h;
}

# Print channel data and compare to new params
sub print_channel {
	my ($chan, $mplex, $freq, $pid) = @_;
	print "CHANNEL INFO:\n";
	foreach $col (keys %$chan) {
		print "\t$col: $chan->{$col}\n";
	}
	print "\n";
	print ((($chan->{'freqid'} ne $freqmap{$freq}) ? 'DIFFERING' : 'Matching') . " DB freq: $chan->{'freqid'} New: $freqmap{$freq}\n");
	$newmplex = keys(%$mplex) ? $mplex->{'mplexid'} : 'None';
	print ((($newmplex ne $chan->{'mplexid'}) ? 'DIFFERING' : 'Matching') . " DB mplexid: $chan->{'mplexid'} New: $newmplex\n");
	print ((($pid ne $chan->{'serviceid'}) ? 'DIFFERING' : 'Matching') . " DB serviceid $chan->{'serviceid'} New: $pid\n");
}


# Get a transport id either in hex or integer format.
sub get_integer_transport_id {
	my ($inputtid) = @_;
	$itid = undef;
	if ($inputtid =~ /^\d+$/) {
		$itid = $inputtid;
	} elsif ($inputtid =~ /^0x[0-9a-fA-F]{1,4}$/) {
		$itid = hex($inputtid);
	}
	return $itid;
}


sub get_integer_freqid {
	my ($inputfreqid) = @_;
	$freqid = undef;
	if (substr($inputfreqid, -6) == '000000') {
		$a = get_us_atsc_frequencies();
		$freqid = (defined $a{$inputfreqid}) ? $a{$inputfreqid} : undef;
	} elsif ($inputfreqid =~ /^\d+$/ && $inputfreqid) {
		$freqid = $inputfreqid;
	}
	return $freqid;
}

sub get_us_atsc_frequencies {
	return (
		'57000000'=>2,
		'63000000'=>3,
		'69000000'=>4,
		'79000000'=>5,
		'85000000'=>6,
		'177000000'=>7,
		'183000000'=>8,
		'189000000'=>9,
		'195000000'=>10,
		'201000000'=>11,
		'207000000'=>12,
		'213000000'=>13,
		'473000000'=>14,
		'479000000'=>15,
		'485000000'=>16,
		'491000000'=>17,
		'497000000'=>18,
		'503000000'=>19,
		'509000000'=>20,
		'515000000'=>21,
		'521000000'=>22,
		'527000000'=>23,
		'533000000'=>24,
		'539000000'=>25,
		'545000000'=>26,
		'551000000'=>27,
		'557000000'=>28,
		'563000000'=>29,
		'569000000'=>30,
		'575000000'=>31,
		'581000000'=>32,
		'587000000'=>33,
		'593000000'=>34,
		'599000000'=>35,
		'605000000'=>36,
		'611000000'=>37,
		'617000000'=>38,
		'623000000'=>39,
		'629000000'=>40,
		'635000000'=>41,
		'641000000'=>42,
		'647000000'=>43,
		'653000000'=>44,
		'659000000'=>45,
		'665000000'=>46,
		'671000000'=>47,
		'677000000'=>48,
		'683000000'=>49,
		'689000000'=>50,
		'695000000'=>51,
		'701000000'=>52,
		'707000000'=>53,
		'713000000'=>54,
		'719000000'=>55,
		'725000000'=>56,
		'731000000'=>57,
		'737000000'=>58,
		'743000000'=>59,
		'749000000'=>60,
		'755000000'=>61,
		'761000000'=>62,
		'767000000'=>63,
		'773000000'=>64,
		'779000000'=>65,
		'785000000'=>66,
		'791000000'=>67,
		'797000000'=>68,
		'803000000'=>69,
	);
}

sub get_yn {
	my ($question) = @_;
	my ($yn) = ();
	while (!(defined $yn)) {
		print "$question (y/n)?: ";
		$yn = <STDIN>;
		chomp $yn;
		$yn = undef if ($yn ne 'n' and $yn ne 'y');
	}
	return ($yn eq 'y');
}

sub syntax {
	my ($estat) = @_;
	print <<SYN
$0: Checks and/or updates US ATSC frequency
information for a specified channel, with the ability to create new
frequencies on dtv_multiplex.

Syntax: $0 [options] frequency transportid channum programid

When run without either the -c or -u options, this ONLY compares the
existing specified channel in the database to the parameters specified,
and displays what differs and what matches. The channum field is
intended to be the channum name of the existing channel in the MythTV
database. The other fields above can be obtained, for example, from
a the scan option of the hdhomerun_config command:

hdhomerun_config <id> scan <tuner> [<filename>]

From the following output:

SCANNING: 599000000 (us-bcast:35)
LOCK: 8vsb (ss=92 snq=98 seq=100)
TSID: 0x07D5
PROGRAM 1: 4.1 WNBC
PROGRAM 2: 4.2 COZI-TV
PROGRAM 3: 47.1 WNJU-HD
PROGRAM 4: 47.2 TeleX

Your existing channel 4.1 could be compared to the above with:

myth-us-atsc-freq-check 599000000 0x07D5 4.1 1

The frequency can be specified either as the actual frequency or the number
of the US broadcast channel. The transportid can be either in a hex format
(for example 0x07D5) or as an integer as it's stored on the dtv_multiplex
table. Note that transportid on the dtv_mutiplex table appears to have no
affect on ATSC functionality at all. The programid (the MythTV serviceid
on the channel) is the multiplex PROGRAM in the above output.

Options:
	-h, --help		Print this output.
	-u, --update-channel	Update channel with new freqid,
				mplexid, and serviceid (programid).
	-c, --create-mplex	Create new dtv_multiplex record if needed.
	-s, --source <source>	The name of the video source. Only
				required if you have more than one.
	-y, --yes		Auto answer 'y' to warning.

Example:
$0 605000000 0x07D5 4.1 5

WARNING: This comes with NO Guarantees or Warranties whatsoever. If you
choose to use this it's on a strict use at your own risk basis.

SYN
;
	exit($estat);
}