#!/usr/bin/perl -w
# Add a new U.S. ATSC channel to the MythTV database, based on the information
# 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;

($help, $source, $commfree, $invisible, $xmltvid) = ();
$xmltvid = $icon = '';

GetOptions (	"help|h" => \$help,
		"commfree|c" => \$commfree,
		"invisible|n" => \$invisible,
		"xmltvid|x=s" => \$xmltvid,
		"icon|i=s" => \$icon,
		"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];

($freq, $channum, $callsign, $serviceid) = @ARGV;

$name = $callsign;

%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}));
$freqid = $freqmap{$freq};
die("Invalid channum format: $channum\n") unless ($channum =~ /^(\d+)[\.-_](\d+)$/);
($atsc_major_chan, $atsc_minor_chan) = ($1, $2);
$chan = read_channel($channum);
die("Channel $channum Already exists!\n") if (keys %$chan);
$mplex = read_mplex($freq);
die("No dtv_multiplex data for: $freq! (Can be added using mythtv-us-atsc-freq-check)\n") unless (keys %$mplex);
$mplexid = $mplex->{'mplexid'};
die "serviceid: $serviceid must be a whole number.\n" unless ($serviceid =~ /^\d+$/);

# NOTE: While the order of the keys in the following %defaults and %data hashes
# don't retain the order in which they are assigned here, their keys and values
# are in fact in sync...so the resulting SQL INSERT and it's parameters work
# correctly. The arbitrary order of the column names in the SQL is irrelevant.

# All our defaults. Comments indicate which are actually table defaults.
%defaults = (
	'sourceid'=>$sourceid,
	'finetune'=>0,
	'videofilters'=>'',
	'recpriority'=>0,	# table default
	'contrast'=>32768,	# table default
	'brightness'=>32768,	# table default
	'colour'=>32768,	# table default
	'hue'=>32768,		# table default
	'tvformat'=>'ATSC',
	'useonairguide'=>1,
	'tmoffset'=>0,		# table default
	'last_record'=>'0000-00-00 00:00:00',
	'default_authority'=>'',
	'iptvid'=>undef,		# table default
);

$defcols = join(', ', keys %defaults);
@defparams = values %defaults;

if (!$yes and !get_yn("Insert new channel: $channum ($callsign)?")) {
	print "Aborting...\n";
	exit();
}

@a = $dbh->selectrow_array('SELECT MAX(chanid) FROM channel');
die "Unable to get current maximum chanid from channel table?\n" unless(scalar (@a) == 1);
$chanid = $a[0] + 1;

%data = (
	'chanid'=>$chanid,
	'channum'=>$channum,
	'callsign'=>$callsign,
	'name'=>$name,
	'freqid'=>$freqid,
	'mplexid'=>$mplexid,
	'serviceid'=>$serviceid,
	'atsc_major_chan'=>$atsc_major_chan,
	'atsc_minor_chan'=>$atsc_minor_chan,
	'commmethod'=>$commfree ? -2 : -1,
	'xmltvid'=>$xmltvid,
	'icon'=>$icon,
	'visible'=> $invisible ? 0 : 1,
);
$datacols = join(', ', keys %data);
@params = values %data;
push(@params, @defparams);

$sql = "INSERT INTO channel
($datacols, $defcols)
VALUES (" . sql_placeholders(\@params) . ")";

$rows = $dbh->do($sql, {}, @params) or die $dbh->errstr;
die ("Failed to insert channel?\n") unless ($rows);

print <<CHANNEL
New channel added!:

chanid: $chanid
channum: $channum
name: $name
freqid: $freqid
mplexid: $mplexid
maj: $atsc_major_chan
min: $atsc_minor_chan
commethod: $data{'commmethod'}

CHANNEL
;

# 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;
}

# Get SQL placeholders for an array or hash reference.
sub sql_placeholders {
	my ($params) = @_;
	my $r = ref($params);
	my $c = undef;
	if ($r eq 'ARRAY') {
		$c = scalar(@$params);
	} elsif ($r eq 'HASH') {
		$c = scalar(%$params);
	} else {
		return undef;
	}
	return join(', ', (('?') x $c));
}

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: Add a new U.S. ATSC channel to the
MythTV database based on the pameters specified.

Syntax: $0 [options] frequency channum callsign programid

This will create a new channel for the above parameters. See the options
below for overrides to some other default values. The frequency and
programid values can be obtained, for example, from a the scan option
of the hdhomerun_config command:

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

The frequency can be specified either as the actual frequency or the number
of the US broadcast channel. Note however that this currently won't add any
transports for new frequencies to the dtv_multiplex table, and the script
will exit with an error if the frequency doesn't exist. New frequencies can
be added using the mythtv-us-atsc-freq-check script with the -c option.

The channum should be in a format with the ATSC major number and ATSC minor
number separated with either '.', '-', or '_' depending which you use. For
example 7.3, 7-3, or 7_3. The new channel will use the corresponding ATSC
major and minor numbers.


Options:
	-h, --help		Print this output.
	-c, --commfree		Channel is commercial free.
	-x, --xmltvid		The xmltvid if it's known and we want to have
				the guide data updated.
	-i, --icon		The icon name if it's being downloaded and put
				under the mythtv ~/.mythtv/channels.
	-n, --invisible		Create channel with visible set to 0.
	-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 2.3 DABL 3

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);
}